Toying with the SickBeard database – SQLite
I’m moving around a bunch of data managed by SickBeard because I’m running out of drive space. While SickBeard nicely allows for mass changes, it isn’t so friendly when it comes to gleaning certain information, such as a list of shows that reside in a common path. SQL to the rescue.
Currently SickBeard uses SQLite as its default database. Head to SQLite’s download page and grab the appropriate copy of the command line shell (in my case, Precompiled Binaries for Windows). Stick it in the install path for SickBeard, where sickbeard.db resides. Because I like to be safe I made a copy of my database, which I named sickbeard_2.db, just in case I did something stupid and broke it.
In a CMD window, path to the aforementioned location and fire up SQLite:
1 | SQLite3.exe |
Open the database with
1 | .open filename.db |
You can list tables simply with
1 | .tables |
We’re after the information found in the tv_shows table. Want to see the columns in that table?
1 | pragma table_info(tv_shows); |
This simple query gets me what I am after, which is all the shows located in the Toons directory:
1 | SELECT location, show_name FROM tv_shows WHERE location LIKE '%Toons%'; |
To dump the data to a file rather than to the screen:
1 2 3 4 | .mode csv .output FILE.csv SELECT location, show_name FROM tv_shows WHERE location LIKE '%Toons%'; .output stdout |
Find FILE.csv in the directory containing SQLite3 and the db.
