Archive for February, 2012

Doghouse Stats

Tuesday, February 21st, 2012

Alex again here with some Doghouse stats. The Doghouse, our new data repository for the Pedia programs, is growing nicely:

We are nearing the 100,000 mark for DVDs in our database. This is a great milestone.
We have more than 140,000 books already, awesome.
61,000 CDs, guess people don’t use those too much anymore.
But only 6,000 games. Where are the gamers out there? Or maybe there just aren’t that many games to begin with?

Here’s where it gets interesting:

Directors in our database: 23,000. Wow, that’s a lot of directors.
People in movie credits: about 360,000. Ouch! This includes actors as well as other jobs that usually appear in credits at the end of a movie.
Geeky fact: we have a table that associates the credits with the movies and this table has 1,612,713 entries in it. That’s over One Million and a Half (capitals awarded for such a big number).

There have been 500,000 contributions from users like you and 200,000 have been matched to entries already contributed. The search rate is looking quite good with 80% matching on DVDs. But we are looking at raising that number every day.

Ok, right now you’re asking yourself, why is he boring me with all these numbers and stats? Is there nothing better on YouTube? The reason is simple: all this is only possible thanks to your contributions. The people who have taken the time and effort to submit good data and the moderators who are painstakingly and lovingly checking and fixing entries. And, of course, the great efforts of the wonderful people here at Bruji *self-pat on the back* and maybe a dog biscuit.

Seriously though, without user contribution we wouldn’t have a database for us to all benefit from. But, as the old adage goes, “garbage in, garbage out”. The data is only as good as the entries that are submitted. So if you’re submitting to the Doghouse, please try to have as good and reliable data as possible, this will benefit us all and make the database grow faster since our moderators will have a bit less work and can concentrate on more productive activities like watching Monty Python videos.

And if you search and can’t find something, be sure to contribute to the Doghouse after you’ve added it to your database (preferably including the UPC at the back of the item).

Interesting search facts:

One of the most failed search terms was “Pirates of the carribean”, notice the misspelling, it should be “Caribbean“. Also “Real Steal” which should be “Steel”. So if the movie you are searching for is not there, check your spelling and try again. If you’re not sure of the spelling and don’t have IMDb handy, try different spellings or just one word “Pirates”, though this might produce too many hits, in which case, try a combination of the title and a main actor (remember to set the search window limit to “None”): “pirates johnny depp”. Or title and director: “pirates gore” (for Gore Verbinski). Above all, have fun, don’t get frustrated, it’s a database not a mind-reading app (yet).

Speaking of fun, want to watch a movie with Johnny Depp in it and not sure which one? Use DVDpedia to get all the Johnny Depp movies (set the limit to Credits) and sort the search by Release. You’ll discover a bunch of movies you probably didn’t know or forgot existed. Or you’re a fan of David Cronenberg (and who isn’t)? Same deal: limit to Director, search “David Cronenberg” and start watching from your favourite source (if available).

Right, I’m off to the dungeon, have fun with the Pedias.

SQLite GUI tools

Monday, February 13th, 2012

Hello, I am Alex. For the past year I’ve been chained in a dungeon, fed on a steady diet of MySQL and PHP. [Ed. note: We do let him out once a month to play with some CSS.] I built the Doghouse infrastructure with these technologies. Recently I started learning a wonderful and beautiful programming language called Ruby. If you have any interest whatsoever in programming languages I really recommend giving Ruby a try (more on that in a future post). I’m currently building the future Doghouse moderator section with Ruby On Rails. Working with Rails and the pedias I have had a chance to look more closely at SQLite, so I thought I’d write a short post about it.

I have a new-found respect for SQLite.

As you know, SQLite is the database ‘engine’ on which the pedias are based. It is Apple’s default database schema. Being a Mysql user for a number of years now I wasn’t really interested in this seemingly less capable new-comer. But, lately, I’ve had a lof of dealings with Ruby on Rails, whose default database engine is also SQLite and have found I really like it for certain tasks.

For those of you who are only interested in a Graphical Editor to be able to manipulate your pedia data, skip to the GUI tools below.

It’s not for every project, especially ones that require a lot of inserts or updates to your database. But it’s perfect for portable applications and web sites that rely mostly on reading and not writing data. I read somewhere that the rule of thumb is that if your database takes less than 100,000 (100K) hits a day, then you’re find going with SQLite. This is, obviously, a big simplification, but not a bad starting point.

It’s extremely easy to set up, lightweight and very portable since the whole database consists of a single file and there are no server connections to worry about. It offers all the simplicity of a file-driven database (Excel, CSV file, Access, etc) with none of the shortcomings, namely it allows for relational databases as well as constraints and triggers.

You can find lots of good articles on SQLite usability and Mysql vs SQLite comparisons so I won’t bother, instead I’ll write about the Graphical Tools you can use to manipulate these databases.

GUI Tools

So, on to the most important thing, the GUIs or Graphical Tools for manipulating SQLite databases. Sure, you can use the terminal and issue all sorts of weird commands to manipulate your databases, but c’mon, who doesn’t love a GUI?

Sequel Pro
This is the best tool for Mysql databases on the planet, no exceptions. And it’s free. If you work with Mysql databases, do yourself a favour and get it now. And do us all a favour and donate something to the developers to keep them upgrading this wonderful product.
It doesn’t yet have support for SQLite but it’s in the works. I just had to include it here because it’s so great and I work with it everyday monitoring and tweaking Doghouse performance.

Navicat Premium
This is a capable but very pricey option. Though there is a sale of the Premium Essentials right now in the App Store.

It’s not that great-looking but will do pretty much anything you need and has the advantage of also tackling Mysql databases so if you use both Mysql and SQLite like we do, you only need one tool.

I don’t very much like the interface or the way the tables are laid out or the fact that each table opens up a new window, making for a very cluttered workspace.

Not my tool of choice, but capable.

This is a nice-looking SQLite manager. Much nicer-looking than Navicat, albeit with less features.

The interface is simple and very nice when reading database contents.
However, when editing the database such as adding or altering tables and fields I find the interface strange and confusing. Things are hidden away in weirdly-placed pop-up menus. Almost as if it was initially intended only for reading databases, then the editing features were bolted on as an afterthought and the developer wasn’t quite sure where to put them.

I like it a lot more than Navicat Premium Essentials but, unfortunately, it’s more expensive, almost three times the price (now that Navicat Premium Essentials is on sale).

I think this is a nice app, especially for reading data. If it were a little cheaper I’d buy it, but I’m not paying $30 for something I don’t fully like.

They don’t have a demo version on the App Store (this really pisses me off) but if you hunt around the Internet you can find a 7-day demo on their site:

I think this tool is ok, capable and simple for SQLite databases. The problem is I tried their tool to convert a Mysql database to SQLite called SQLite Migrator and could not get it to work. The SQLite Migrator relies on Apple’s ODBC Administrator app (no longer supplied with OS X, but you can download it separately) and ODBC Administrator in itself is not simple to configure. You first have to find an ODBC driver (from where?) for the connection you want to use, then get that working inside ODBC Administrator, create a new connection with that driver and to the database of your choice, then go back to SQLite Migrator and try to connect to this new connection you’ve created in ODBC Administrator. If that works, then you can (I assume) start dealing with your migration issues.

I’m sorry, but for a tool that costs $50 I expect it to be a little easier to create a connection just to get started. The days of ODBC connectors on the desktop are long gone (there’s a reason Apple doesn’t include this tool anymore, because it was just gathering dust and taking up space in your hard drive) and this is a step in the wrong direction.

So this experience left a really bitter taste in my mouth so I can’t quite like their SQLVue product.

All these tools (except Sequel Pro) seem to suffer from opening a new window for every task. If you are looking at a table structure and want to edit it you get a new window just to add or modify a field. This makes it confusing and cluttered.

SQLite Database Browser
Lastly, there is this tool. It’s free so that’s great news, but it’s buggy, crashy, hideous and hasn’t been update since 2008 as far as I can tell. It is a port of a Windows app and it shows.

For very ocassional use this tool might be OK. But if you want to deal with SQLite databases and keep your sanity, I’d recommend using one of the other alternatives.


Well, it’s tough to say who the winner really is. Until Sequel Pro brings out support for SQLite I think you’re better off going with Base or Navicat Premium Essentials. The latter is┬ánot great, but I think that for $10 (on sale) it’s probably your best option at this point, though I will say that personally I use Base.

I would also like to add that you can use these tools on the Pedias by pointing them to the database at ~/Library/Application Support/Xpedia/Database.Xpd (where X is book, cd, dvd or game). Sqlite is also the backend of choice for any large Core Data program (XML and binary are also options with Core Data) so it might be useful for any number of Mac programs out there based on Core Data.