From e5dbe982430db4ef426666e6212cabff29af2306 Mon Sep 17 00:00:00 2001 From: Philipp Zabel Date: Wed, 6 Jan 2010 11:35:03 +0100 Subject: [PATCH] IMDb downloader: optimize SQLlite database for the common case The common case being title searches starting at the beginning of the string without case sensitivity (Title LIKE "Abc%"). To make SQLite use the title index in this case, define the indexed columns with the NOCASE collation. Also, let SQLite generate the movie title index automatically by setting the Title column as PRIMARY KEY. This has the side effect that only unique titles can be added to the database (and during the import this will show a few errors that are in the IMDb dataset). --- src/imdb/imdb-plaintext-downloader.vala | 5 ++--- src/imdb/imdb-sqlite.vala | 19 ++++++------------- 2 files changed, 8 insertions(+), 16 deletions(-) diff --git a/src/imdb/imdb-plaintext-downloader.vala b/src/imdb/imdb-plaintext-downloader.vala index e3df558..821c9a2 100644 --- a/src/imdb/imdb-plaintext-downloader.vala +++ b/src/imdb/imdb-plaintext-downloader.vala @@ -79,8 +79,6 @@ class IMDbDownloadServer : Object, IMDbDownloader { if (MOVIES in flags) { description_changed ("Downloading movie list ..."); downloader.download (url + "movies.list.gz", movie_parser); - description_changed ("Creating title index ..."); - sqlite.create_title_index (); } percent_finished = 33; if (GENRES in flags) { @@ -100,7 +98,8 @@ class IMDbDownloadServer : Object, IMDbDownloader { } description_changed ("Creating indices ..."); - sqlite.create_votes_index (); + if (MOVIES in flags) + sqlite.create_votes_index (); if (!cancellable.is_cancelled ()) { stdout.printf ("Download complete.\n"); diff --git a/src/imdb/imdb-sqlite.vala b/src/imdb/imdb-sqlite.vala index b489fdb..d26564b 100644 --- a/src/imdb/imdb-sqlite.vala +++ b/src/imdb/imdb-sqlite.vala @@ -129,19 +129,12 @@ class IMDbSqlite : Object { public int clear () { int rc; - rc = db.exec ("DROP TABLE IF EXISTS Movies; CREATE TABLE Movies (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Votes INTEGER, Genres INTEGER NOT NULL DEFAULT 0); DROP TABLE IF EXISTS Genres; CREATE TABLE Genres (Bit INTEGER PRIMARY KEY, Genre TEXT NOT NULL);", callback, null); - if (rc != Sqlite.OK) { - stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ()); - return 1; - } - - return 0; - } - - public int create_title_index () { - int rc; - - rc = db.exec ("CREATE INDEX MovieTitles ON Movies(Title);", callback, null); + rc = db.exec ( + "DROP TABLE IF EXISTS Movies;" + + "CREATE TABLE Movies (Title TEXT PRIMARY KEY COLLATE NOCASE, Year INTEGER, Rating INTEGER, Votes INTEGER NOT NULL DEFAULT 0, Genres INTEGER NOT NULL DEFAULT 0);" + + "DROP TABLE IF EXISTS Genres;" + + "CREATE TABLE Genres (Bit INTEGER PRIMARY KEY, Genre TEXT NOT NULL);", + callback, null); if (rc != Sqlite.OK) { stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ()); return 1; -- 1.7.9.5