Embedded Files
ｂｙ はちこ
For the anal retentive among you, I wrote a show tracker script to keep track of TV shows, anime, movies, etc. that I watched. Even though there are many applications out there to keep track of books, shows, comics, or all of the above, I needed something that was instant. If required any extra time to start the application, click some buttons, etc, then I probably would forget. And then one day I'd be watching something and some time in the beginning, I'd find it very familiar and realize I'd seen it already. And that it was crap (this has already happened).
I thought someone else might find it useful, or might want some simple examples of how do various things. I used Python for scripting and SQLite as the back-end database.
SQLite is a small server-less SQL database engine, similar to Access but a lot cleaner. With pysqlite, Python can use it in a similar way as pyodbc for ODBC connections to true databases. It's not exactly the same, which causes a few problems, but it is still pretty good. SQLite was how I really learned how to do scripting, so that by 2010 I could be a SQL guru.
Hachiko's Show Tracker is a console application with a small GUI portion to allow auto-completion. When you type, the field will pull up open shows where the beginning matches what you have typed. You can press the up arrow and down arrow to cycle through matches.
The autocompletion is still a bit tetchy. The reason is timing. If you simply do autocompletion, you get the case where if you type very fast, sometimes the cursor focus moves to the end, because you typed the next character because Python finished putting the cursor position in the middle. It also makes it impossible to use arrow keys or backspace correctly. So I added a timer that won't update the completion field until a little bit of time has passed since you last pressed a key. Mostly, this works. But sometimes the wait is longer than expected. I get impatient if I have to wait more than a second, so I added handling of the Tab key. Press Tab to trigger a autocompletion search. Press Tab on a blank field to bring up the last show entered.
To bring up the autocomplete field, call the script without any argument.
Sqlite Manager (for Firefox) bug
Note: If you use the Sqlite Manager extension for Firefox, there is a bug that will prevent you from updating the EPISODES table manually. This bug won't be fixed. But there is a simple workaround, and a (maybe) slightly better workaround.
The simple workaround is run the following code:
DROP TRIGGER ins_EPISODES_EnforceZeroNumberOrSpecial;
DROP TRIGGER upd_EPISODES_EnforceZeroNumberOrSpecial;
CREATE TRIGGER ins_EPISODES_EnforceZeroNumberOrSpecial
BEFORE INSERT ON EPISODES
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'One of ep_number and ep_special must be zero')
WHERE NEW.ep_number > '0' AND NEW.ep_special > '0';
END;
CREATE TRIGGER upd_EPISODES_EnforceZeroNumberOrSpecial
BEFORE UPDATE ON EPISODES
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'One of ep_number and ep_special must be zero')
WHERE NEW.ep_number > '0' AND NEW.ep_special > '0';
END;
The better workaround is to implement it properly, with a check constraint. When I started this, check constraints were parsed but not enforced, so I wrote a trigger. When the devs added enforcement, I never bothered to remove the trigger.. The error message is less informative with a constraint, but it seems cleaner than a pair of triggers. If you already have a DB, though, adding a check constraint is a bit of a pain. You can't just alter the table. You have to rename the existing EPISODES table, create a new one with CHECK(ep_number = 0 OR ep_special = 0) on the end of the create statement, insert your data into the new table, and drop the old table.
I think I'll go with the check constraint in later versions. And when I upgrade to a version with foreign keys, I will be able to get rid of triggers entirely.
Link to bug: http://code.google.com/p/sqlite-manager/issues/detail?id=494
News
[December 05 2012] Does anyone actually use this? I've realized that over the last few years, I've made a few tweaks to the autocompletion here and there, only a couple dozen lines acording to kompare, but it makes a real difference. One of my queries also was quick and dirty and thus was actually wrong. When filling the autocomplete dialog, I did SELECT DISTINCT showname ORDER BY showsurrogate. That's a nono, and the sqlite folks fixed their bug in the last couple years, forcing me to fix mine as well. I am now wondering if it's worth it to test it, build a standalone windows exe (which requires setting up a windows box), etc. Then there's the question of whether or not I want to actually implement foreign keys, when it's working fine with triggers.
[May 02 2010] Today I released the Python version, 2010.03.15. I've used it for the last couple months and haven't noticed any problems.
[Aug 14 2010] Made some changes to the GUI, mainly to allow you to click Esc and exit. I also shortened the wait time. I'll give it some time to see if the second of those changes is going to cause some problems or if it works out in real use. And I need to build the Windows version, which is always a bit of a pain.
In the future: SQLite recently added support for foreign keys. It is still too new for me to add it (it won't be added to Ubuntu until Lucid Lynx, and I plan on waiting until not only I have it, but there's a good chance that others will have it too). But it'll probably come in the future. It won't change the code at all, just the DB schema.
I also would like to add Unicode support at some point. Nothing special had to be done in the Perl version to handle unicode, but Python will wipe out if you try to print it a unicode character without handling it specially.
Code
The code, such as it is, is free for you to download, modify, and distribute, under the GPL (as you would expect). I don't expect this to happen. But I'm not against it. Or you might just want to look at it as sample code. All the selects and updates are simple and consistent, there are some nice tricks in the DB schema, etc. That is also fine.
You may want to take a glance at the comments, which are extensive. Probably too much so, but that's where most of the instructions are.
Help
To use it, just run the script. If you run it by itself, the autocompletion field will appear. If you call it followed by the name of the show, it will search for that show. Then follow the menus provided. For example,
./showTracker.py HP Lovecraft's Dagon
How to install on Ubuntu Linux
(other distros may or may not be similar)
Page updated
Google Sites
Report abuse