Board Game Play Tracking Using SQLite Part 2: Building DB

Part 1: Overview is here. I have my sketch of what I think the database schema should look like, so I am hopefully ready to start building the database. After it is built, we’ll try to populate it with Python. To create a SQLite database, open the command prompt (cmd+r then type ‘cmd’). Next, type: [text] sqlite3 boardgameplays [/text] Next, we’ll create the tables from our schema. [sql] CREATE TABLE games( game_id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(50) NOT NULL); CREATE TABLE locations( location_id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(50) NOT NULL); CREATE TABLE players( player_id INTEGER PRIMARY KEY AUTOINCREMENT, firstname VARCHAR(50) NOT NULL, lastname VARCHAR(50)); CREATE TABLE scores( score_id INTEGER PRIMARY KEY AUTOINCREMENT, player_id INTEGER NOT NULL, play_id INTEGER NOT NULL, score INTEGER NOT NULL); CREATE TABLE plays( play_id INTEGER PRIMARY KEY AUTOINCREMENT, game_id INTEGER NOT NULL, date DATETIME NOT NULL, duration INTEGER NOT NULL, location_id INTEGER NOT NULL); CREATE TABLE playersplays( playerplays_id INTEGER PRIMARY KEY AUTOINCREMENT, play_id INTEGER NOT NULL, player_id INTEGER NOT NULL, winner INTEGER(0,1)); [/sql] Before I try to get Python to put data into this database, I want to put some plays in manually to make sure things are working how I want. I want to pull the data directly from BoardGameGeek, so I’ll go to the website and look at some of my play data to put it in manually. Right away, I see that I wish I had a column for a player’s BGG account. To alter the players table, I use: [sql] ALTER TABLE players ADD COLUMN bgg VARCHAR(50); [/sql] The resulting schema (shown by using ‘.schema players’) is: [sql] CREATE TABLE players( player_id INTEGER PRIMARY KEY AUTOINCREMENT, firstname VARCHAR(50) NOT NULL, lastname VARCHAR(50), bgg VARCHAR(50)); [/sql] Next, I start adding players to the database that were part of this game (since there are no players currently in the database, I don’t have to worry about looking to see if a current player exists!): [sql] CREATE TABLE players( player_id INTEGER PRIMARY KEY AUTOINCREMENT, firstname VARCHAR(50) NOT NULL, lastname VARCHAR(50), bgg VARCHAR(50)); [/sql] I did this for the four players that were part of this game. Next, I wanted to add the game. Again, I realized I wanted to add a column to a table. In this case, I want to add the unique game ID that BGG uses to distinguish the games in its own database. After doing this, I add the game and the play: [sql] INSERT INTO games VALUES (NULL, ‘Betrayal at House on the Hill’, 10547); INSERT INTO plays VALUES (NULL, 1, ‘2016-01-01’, 91, 1); [/sql] Next, I wanted to add the scores. I realized that the scores shouldn’t be their own table; rather, they should be in the playersplays table. I needed to add another column to this table but SQLite doesn’t allow you to add the constraint ‘NOT NULL’ to a new column in an existing table. I think this is because if you have a table with data in it and then add a new NOT NULL column, the existing rows would have a value of NULL. However, I realized this mistake before I added any data to the table and SQLite still would not let me add the column with the NOT NULL constraint. My next idea was to add the column without that constraint and then change the datatype and constraint after the fact. However, SQLite doesn’t allow you to change the datatype of a column. The only way I could add a new column with the NOT NULL constraint was to create a new table with a different name (I chose ‘temp’) that had all of the columns that I wanted (so, all of the original columns in addition to this new ‘scores’ column), copy the data from the original table (playersplays) into it if any, delete the original table, then rename the temporary table to the original name. It isn’t hard to do this but it is frustrating coming from MySQL where something like this would be very simple. I think the takeaway here is that with SQLite, I need to really think about the tables in my database and how they will interact with each other. MySQL is a lot more forgiving and easy to work with in this regard. The final schema for the playersplays table is: [sql] CREATE TABLE “playersplays”( play_id INTEGER NOT NULL, player_id INTEGER NOT NULL, score INTEGER NOT NULL, winner INTEGER(0,1), PRIMARY KEY (play_id, player_id)); [/sql] Notice the primary key is a combination of both the play id and the player id – this ensures that no play will have multiple entries for the same player. The final schema is shown below. I used SchemaCrawler to generate the graphic. It apparently has an issue with many to many relationships, so the connection between the playersplays table and the plays table is not shown. The play_id in both tables should be linked. Next, I began to add my next few plays of 2016 to the database (the data for this is here). Right away, I realized that prohibiting multiple players from being in the same play was an issue. Sometimes we play with someone that I think we won’t play with again and I don’t necessarily want to track their personal scores. In these cases, I enter them into BoardGameGeek as ‘Anonymous’. The second game I played this year had 5 total players, 2 of which were anonymous. For now, I will simply add another anonymous player into my database with the first name ‘Anonymous’ and the last name ‘2’. I don’t know if I will like this solution in the long term but it seems reasonable for now. After manually inputting all of the information for my first 5 plays of the year, I wanted to test out the JOIN capabilities of SQLite. A quick join to see the date, game name, duration, winner, and winning score for each game is: [sql] SELECT plays.date, games.name, plays.duration, locations.name, players.firstname, playersplays.score FROM playersplays JOIN plays ON playersplays.play_id = plays.play_id JOIN games ON plays.game_id = games.game_id JOIN locations JOIN players ON playersplays.player_id = players.player_id WHERE playersplays.winner = 1; [/sql] This returns: date|name|duration|name|firstname|score 2016-01-01|Betrayal at House on the Hill|91|Rhonda’s House|Eli|0 2016-01-01|Incan Gold|16|Rhonda’s House|Anonymous|51 2016-01-01|Incan Gold|12|Rhonda’s House|Anonymous|24 2016-01-01|Dead Man’s Draw|15|Rhonda’s House|Allison|41 2016-01-01|12 Days of Christmas|37|Rhonda’s House|Troy|5 Overall, I’m liking SQLite but I currently prefer MySQL. MySQL seems more forgiving but I like that SQLite makes me really think about what I want to do. The next thing I want to do is figure out how to add data to this database using Python. After that, we will try to get Python to pull the data from BGG and populate this database automatically! As always, if you have any questions or comments feel free contact me or comment below.