Setting Up SQLite on Windows 10
I recently wrote a post on rating teams in Heroes of the Storm and used a regular CSV to store all the data for my ratings. To be honest, that worked perfectly fine although I have been hoping to do some more advanced analysis, particularly looking into rating players. While a CSV is pretty easy to write to, I wanted to be able to store information in a way that would scale up as time went on. I also happen to be learning SQL at work, so this seems like the normal thing to do.
I am not all that familiar with any of the three major open source databases (SQLite, MySQL/MariaDB, and PostgreSQL). While I actually do run a FreeNAS server at home, I purposefully did not open up any SSH or FTP access to outside outside my home network. This means I wanted a server-less database, which SQLite is. For sharing purposes, I also like being able to just share one file, which is just how SQLite works. I wanted something fast and have no need for multi-user authentication. I'm sure I will probably regret using SQLite one day, but for now it should work fine. Worst case, I can always migrate it to MySQL, like the Ghost blogging platform did awhile back.
It was pretty easy to install. I followed the basic ideas at SQLite.org, created a
C:\\SQLite3 folder and stuck the all the dll and exe files inside it. In windows 10, it's pretty easy to add a folder to your PATH variable. Just
- Search the word PATH
- Click 'Edit environment variables for your account' (or 'Edit the system environment variables')
- Click on 'Environment Variables' if you need to
- Select either the Path variable under user or system variables
- Click edit
- Click new
- Type in your directory (in my case
You don't even need to reboot in Windows 10! (It's the little things in life)
Installing DB Browser for SQLite
Some people may actually prefer to use the command line to create, manage, and update their databases but I am definitely not hardcore enough for that. DB Browser for SQLite is an open source tool to "create, design, and edit database files compatible with SQLite." It doesn't play super well with my computer's high-dpi screen but it's still usable. I used DB Browser for SQLite to import my CSV file as the table
match_basic. I also created a table for Player details and a table to assign hero classes to specific heroes.
More is coming
I know this post seems a little light, but I'm planning on adding Scrapy to scrape out all of this information online and put it in the SQLite database I just created. Eventually I would love to automate all of this so my home server scans once or twice a week for any updates. For the moment though, I'm setting up Scrapy to handle transcription for me. The next update should be an interesting one (to me at least).
Cover Photo part of the SQLite documentation, which has been released by author D. Richard Hipp to the public domain. SVG conversion by Mike Toews. Via Wikimedia.