commit dc439bc998178171652b3dd7153b780350fe1431
Author: Nikias Bassen <nik...@gmx.li>
Date: Sun Dec 6 17:43:53 2009 +0100
exec post process commands provided by the device
This patch makes use of the
com.apple.mobile.iTunes.SQLMusicLibraryPostProcessCommands
lockdownd request (or key in the SysInfoExtended for Nano 5G) that holds
a list of SQL operations to be executed after the SQLite database files
have been populated with the necessary data.
This patch also removes some static SQL code from itdb_sqlite_queries.h
that is now handled by the post process commands.
src/itdb_sqlite.c | 258 ++++++++++++++++++++++++++++++++++++++++++---
src/itdb_sqlite_queries.h | 50 +---------
2 files changed, 242 insertions(+), 66 deletions(-)
---
diff --git a/src/itdb_sqlite.c b/src/itdb_sqlite.c
index 39ce064..e2bba10 100644
--- a/src/itdb_sqlite.c
+++ b/src/itdb_sqlite.c
@@ -32,6 +32,9 @@
#include <glib/gstdio.h>
#include <sqlite3.h>
+#include <libiphone/libiphone.h>
+#include <libiphone/lockdown.h>
+
#include "itdb.h"
#include "itdb_private.h"
#include "itdb_sqlite_queries.h"
@@ -352,6 +355,12 @@ leave:
return res;
}
+static void free_key_val_strings(gpointer key, gpointer value, gpointer
user_data)
+{
+ g_free(key);
+ g_free(value);
+}
+
static int mk_Library(Itdb_iTunesDB *itdb,
GHashTable *album_ids, GHashTable *artist_ids,
const char *outpath)
@@ -441,9 +450,8 @@ static int mk_Library(Itdb_iTunesDB *itdb,
"bpm,relative_volume,eq_preset,radio_stream_status,genre_id,album_pid,artist_pid,composer_pid,title,artist,album,"
"album_artist,composer,sort_title,sort_artist,sort_album,sort_album_artist,sort_composer,title_order,artist_order,"
"album_order,genre_order,composer_order,album_artist_order,album_by_artist_order,series_name_order,comment,grouping,"
-
"description,description_long,title_section_order,artist_section_order,album_section_order,album_artist_section_order,"
-
"composer_section_order,genre_section_order,series_name_section_order) "
-
"VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);",
-1, &stmt_item, NULL)) {
+ "description,description_long) "
+
"VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);",
-1, &stmt_item, NULL)) {
fprintf(stderr, "[%s] sqlite3_prepare error: %s\n", __func__,
sqlite3_errmsg(db));
goto leave;
}
@@ -871,20 +879,6 @@ static int mk_Library(Itdb_iTunesDB *itdb,
/* description_long */
/* TODO libgpod doesn't know about it */
sqlite3_bind_null(stmt_item, ++idx);
- /* title_section_order */
- sqlite3_bind_null(stmt_item, ++idx);
- /* artist_section_order */
- sqlite3_bind_null(stmt_item, ++idx);
- /* album_section_order */
- sqlite3_bind_null(stmt_item, ++idx);
- /* album_artist_section_order */
- sqlite3_bind_null(stmt_item, ++idx);
- /* composer_section_order */
- sqlite3_bind_null(stmt_item, ++idx);
- /* genre_section_order */
- sqlite3_bind_null(stmt_item, ++idx);
- /* series_name_section_order */
- sqlite3_bind_null(stmt_item, ++idx);
res = sqlite3_step(stmt_item);
if (res != SQLITE_DONE) {
@@ -1249,6 +1243,233 @@ leave:
return res;
}
+static void run_post_process_commands(Itdb_iTunesDB *itdb, const char
*outpath, const char *uuid)
+{
+ plist_t plist_node = NULL;
+ plist_t ppc_dict = NULL;
+ const gchar *basedb = "Library.itdb";
+ const gchar *otherdbs[] = {"Dynamic.itdb", "Extras.itdb", "Genius.itdb",
"Location.itdb", NULL};
+ int res;
+ sqlite3 *db = NULL;
+
+ if (itdb_device_is_iphone_family(itdb->device)) {
+ /* get SQL post process commands via lockdown (iPhone/iPod Touch) */
+ lockdownd_client_t client = NULL;
+ iphone_device_t phone = NULL;
+ iphone_error_t ret = IPHONE_E_UNKNOWN_ERROR;
+ lockdownd_error_t lockdownerr = LOCKDOWN_E_UNKNOWN_ERROR;
+
+ ret = iphone_device_new(&phone, uuid);
+ if (ret != IPHONE_E_SUCCESS) {
+ printf("[%s] ERROR: Could not find device with uuid %s, is it
plugged in?\n", __func__, uuid);
+ goto leave;
+ }
+
+ if (LOCKDOWN_E_SUCCESS != lockdownd_client_new(phone, &client)) {
+ printf("[%s] ERROR: Could not connect to device's lockdownd!\n",
__func__);
+ iphone_device_free(phone);
+ goto leave;
+ }
+
+ lockdownerr = lockdownd_get_value(client,
"com.apple.mobile.iTunes.SQLMusicLibraryPostProcessCommands", NULL,
&plist_node);
+ lockdownd_client_free(client);
+ iphone_device_free(phone);
+
+ if (lockdownerr == LOCKDOWN_E_SUCCESS) {
+ ppc_dict = plist_node;
+ } else {
+ if (plist_node) {
+ plist_free(plist_node);
+ plist_node = NULL;
+ }
+ }
+ } else if (itdb->device->sysinfo_extended != NULL) {
+ /* try to get SQL post process commands via sysinfo_extended */
+ gchar *dev_path = itdb_get_device_dir (itdb->device->mountpoint);
+ if (dev_path) {
+ const gchar *p_sysinfo_ex[] = {"SysInfoExtended", NULL};
+ gchar *sysinfo_ex_path = itdb_resolve_path (dev_path, p_sysinfo_ex);
+ g_free(dev_path);
+ if (sysinfo_ex_path) {
+ /* open plist file */
+ char *xml_contents = NULL;
+ gsize xml_length = 0;
+ if (g_file_get_contents(sysinfo_ex_path, &xml_contents,
&xml_length, NULL)) {
+ plist_from_xml(xml_contents, xml_length, &plist_node);
+ if (plist_node) {
+ /* locate specific key */
+ ppc_dict = plist_dict_get_item(plist_node,
"com.apple.mobile.iTunes.SQLMusicLibraryPostProcessCommands");
+ }
+ }
+ if (xml_contents) {
+ g_free(xml_contents);
+ }
+ g_free(sysinfo_ex_path);
+ }
+ }
+ }
+
+ if (ppc_dict) {
+ plist_dict_iter iter = NULL;
+ plist_t sql_cmds = NULL;
+ plist_t user_ver_cmds = NULL;
+
+ printf("[%s] Getting SQL post process commands\n", __func__);
+
+ sql_cmds = plist_dict_get_item(ppc_dict, "SQLCommands");
+ user_ver_cmds = plist_dict_get_item(ppc_dict, "UserVersionCommandSets");
+
+ if (sql_cmds && user_ver_cmds) {
+ /* we found the SQLCommands and the UserVersionCommandSets keys */
+ char *key = NULL;
+ unsigned long int maxver = 0;
+ plist_t curnode = user_ver_cmds;
+ plist_t subnode = NULL;
+
+ user_ver_cmds = NULL;
+
+ /* now look for numbered subkey in the UserVersionCommandsSets */
+ plist_dict_new_iter(curnode, &iter);
+ if (iter) {
+ plist_dict_next_item(curnode, iter, &key, &subnode);
+ while (subnode) {
+ unsigned long int intval = strtoul(key, NULL, 0);
+ if ((intval > 0) && (intval > maxver)) {
+ user_ver_cmds = subnode;
+ maxver = intval;
+ }
+ subnode = NULL;
+ free(key);
+ key = NULL;
+ plist_dict_next_item(curnode, iter, &key, &subnode);
+ }
+ free(iter);
+ iter = NULL;
+ }
+ if (user_ver_cmds) {
+ /* found numbered key (usually '8', for Nano 5G it is '9') */
+ curnode = user_ver_cmds;
+ /* now get the commands array */
+ user_ver_cmds = plist_dict_get_item(curnode, "Commands");
+ if (user_ver_cmds && (plist_get_node_type(user_ver_cmds) ==
PLIST_ARRAY)) {
+ /* We found our array with the commands to execute, now
+ * make a hashmap for the SQLCommands to find them faster
+ * when we actually execute them in correct order. */
+ GHashTable *sqlcmd_map = g_hash_table_new(g_str_hash,
g_str_equal);
+ if (sqlcmd_map) {
+ char *val = NULL;
+ gchar *dbf = NULL;
+ gchar *attach_str = NULL;
+ char *errmsg = NULL;
+ guint32 i = 0, cnt = 0, ok_cnt = 0;
+
+ plist_dict_new_iter(sql_cmds, &iter);
+ if (iter) {
+ plist_dict_next_item(sql_cmds, iter, &key,
&subnode);
+ while (subnode) {
+ if (plist_get_node_type(subnode) ==
PLIST_STRING) {
+ plist_get_string_val(subnode, &val);
+ g_hash_table_insert(sqlcmd_map, key, val);
+ val = NULL;
+ } else {
+ printf("[%s] WARNING: ignoring non-string
value for key '%s'\n", __func__, key);
+ free(key);
+ }
+ subnode = NULL;
+ key = NULL;
+ plist_dict_next_item(sql_cmds, iter, &key,
&subnode);
+ }
+ free(iter);
+ iter = NULL;
+ }
+
+ /* open Library.itdb first */
+ dbf = g_build_filename(outpath, basedb, NULL);
+
+ if (SQLITE_OK != sqlite3_open((const char*)dbf, &db)) {
+ fprintf(stderr, "Error opening database '%s':
%s\n", dbf, sqlite3_errmsg(db));
+ g_free(dbf);
+ goto leave;
+ }
+ g_free(dbf);
+
+ /* now attach other required database files */
+ i = 0;
+ while (otherdbs[i]) {
+ errmsg = NULL;
+ dbf = g_build_filename(outpath, otherdbs[i], NULL);
+ attach_str = g_strdup_printf("ATTACH DATABASE '%s'
AS '%s';", dbf, otherdbs[i]);
+ g_free(dbf);
+ res = sqlite3_exec(db, attach_str, NULL, NULL,
&errmsg);
+ g_free(attach_str);
+ if (res != SQLITE_OK) {
+ printf("[%s] WARNING: Could not attach database
'%s': %s\n", __func__, otherdbs[i], errmsg);
+ }
+ if (errmsg) {
+ free(errmsg);
+ }
+ i++;
+ }
+
+ cnt = plist_array_get_size(user_ver_cmds);
+ printf("[%s] Running %d post process commands now\n",
__func__, cnt);
+
+ sqlite3_exec(db, "BEGIN;", NULL, NULL, NULL);
+ for (i=0; i<cnt; i++) {
+ subnode = plist_array_get_item(user_ver_cmds, i);
+ plist_get_string_val(subnode, &key);
+ if (key) {
+ val = g_hash_table_lookup(sqlcmd_map, key);
+ if (val) {
+ char *errmsg = NULL;
+ if (SQLITE_OK == sqlite3_exec(db, val,
NULL, NULL, &errmsg)) {
+ /*printf("[%s] executing '%s': OK",
__func__, key);*/
+ ok_cnt++;
+ } else {
+ printf("[%s] ERROR when executing '%s':
%s\n", __func__, key, errmsg);
+ }
+ if (errmsg) {
+ sqlite3_free(errmsg);
+ }
+ } else {
+ printf("[%s] value for '%s' not found in
hashmap!\n", __func__, key);
+ }
+ free(key);
+ key = NULL;
+ }
+ }
+ g_hash_table_foreach(sqlcmd_map, free_key_val_strings,
NULL);
+ g_hash_table_destroy(sqlcmd_map);
+
+ printf("[%s] %d out of %d post process commands
successfully executed\n", __func__, ok_cnt, cnt);
+ /* TODO perhaps we want to roll back when an error has
occured ? */
+ sqlite3_exec(db, "COMMIT;", NULL, NULL, NULL);
+ } else {
+ printf("[%s]: Error: could not create hash table!\n",
__func__);
+ }
+ } else {
+ printf("[%s]: Error fetching commands array\n", __func__);
+ }
+ } else {
+ printf("[%s]: Error fetching user version command set\n",
__func__);
+ }
+ } else {
+ printf("[%s]: Error fetching post process commands from device!\n",
__func__);
+ }
+ }
+
+ printf("[%s] done.\n", __func__);
+
+leave:
+ if (db) {
+ sqlite3_close(db);
+ }
+ if (plist_node) {
+ plist_free(plist_node);
+ }
+
+}
+
static int cbk_calc_sha1_one_block (FILE *f, unsigned char sha1[20])
{
const guint BLOCK_SIZE = 1024;
@@ -1387,6 +1608,9 @@ static int build_itdb_files(Itdb_iTunesDB *itdb,
if (mk_Locations(itdb, outpath, uuid) != 0) {
err++;
}
+
+ run_post_process_commands(itdb, outpath, uuid);
+
if (!mk_Locations_cbk(itdb, outpath)) {
err++;
}
diff --git a/src/itdb_sqlite_queries.h b/src/itdb_sqlite_queries.h
index f50d093..c5f6741 100644
--- a/src/itdb_sqlite_queries.h
+++ b/src/itdb_sqlite_queries.h
@@ -50,7 +50,7 @@ static const char Library_create[] =
"BEGIN TRANSACTION;" \
"CREATE TABLE version_info (id INTEGER PRIMARY KEY, major INTEGER,
minor INTEGER, compatibility INTEGER DEFAULT 0, update_level INTEGER DEFAULT 0,
platform INTEGER DEFAULT 0);" \
"CREATE TABLE db_info (pid INTEGER NOT NULL, primary_container_pid
INTEGER, media_folder_url TEXT, audio_language INTEGER, subtitle_language
INTEGER, bib BLOB, rib BLOB, PRIMARY KEY (pid));" \
- "CREATE TABLE item (pid INTEGER NOT NULL, revision_level INTEGER,
media_kind INTEGER DEFAULT 0, is_song INTEGER DEFAULT 0, is_audio_book INTEGER
DEFAULT 0, is_music_video INTEGER DEFAULT 0, is_movie INTEGER DEFAULT 0,
is_tv_show INTEGER DEFAULT 0, is_ringtone INTEGER DEFAULT 0, is_voice_memo
INTEGER DEFAULT 0, is_rental INTEGER DEFAULT 0, is_podcast INTEGER DEFAULT 0,
date_modified INTEGER DEFAULT 0, date_backed_up INTEGER DEFAULT 0, year INTEGER
DEFAULT 0, content_rating INTEGER DEFAULT 0, content_rating_level INTEGER
DEFAULT 0, is_compilation INTEGER, is_user_disabled INTEGER DEFAULT 0,
remember_bookmark INTEGER DEFAULT 0, exclude_from_shuffle INTEGER DEFAULT 0,
artwork_status INTEGER, artwork_cache_id INTEGER DEFAULT 0, start_time_ms REAL
DEFAULT 0, stop_time_ms REAL DEFAULT 0, total_time_ms REAL DEFAULT 0,
total_burn_time_ms REAL, track_number INTEGER DEFAULT 0, track_count INTEGER
DEFAULT 0, disc_number INTEGER DEFAULT 0, disc_count INTEGER DEFAULT 0, bpm
INTEGER DEFAULT 0, relative_volume INTEGER, eq_preset TEXT, radio_stream_status
TEXT, genius_id INTEGER DEFAULT 0, genre_id INTEGER DEFAULT 0, category_id
INTEGER DEFAULT 0, album_pid INTEGER DEFAULT 0, artist_pid INTEGER DEFAULT 0,
composer_pid INTEGER DEFAULT 0, title TEXT, artist TEXT, album TEXT,
album_artist TEXT, composer TEXT, sort_title TEXT, sort_artist TEXT, sort_album
TEXT, sort_album_artist TEXT, sort_composer TEXT, title_order INTEGER,
artist_order INTEGER, album_order INTEGER, genre_order INTEGER, composer_order
INTEGER, album_artist_order INTEGER, album_by_artist_order INTEGER,
series_name_order INTEGER, comment TEXT, grouping TEXT, description TEXT,
description_long TEXT, in_songs_collection INTEGER NOT NULL DEFAULT 0,
title_blank INTEGER NOT NULL DEFAULT 0, artist_blank INTEGER NOT NULL DEFAULT
0, album_artist_blank INTEGER NOT NULL DEFAULT 0, album_blank INTEGER NOT NULL
DEFAULT 0, composer_blank INTEGER NOT NULL DEFAULT 0, grouping_blank INTEGER
NOT NULL DEFAULT 0, title_section_order BLOB, artist_section_order BLOB,
album_section_or
BLOB, album_artist_section_order BLOB, composer_section_order BLOB,
genre_section_order BLOB, series_name_section_order BLOB, PRIMARY KEY (pid));" \
+ "CREATE TABLE item (pid INTEGER NOT NULL, revision_level INTEGER,
media_kind INTEGER DEFAULT 0, is_song INTEGER DEFAULT 0, is_audio_book INTEGER
DEFAULT 0, is_music_video INTEGER DEFAULT 0, is_movie INTEGER DEFAULT 0,
is_tv_show INTEGER DEFAULT 0, is_ringtone INTEGER DEFAULT 0, is_voice_memo
INTEGER DEFAULT 0, is_rental INTEGER DEFAULT 0, is_podcast INTEGER DEFAULT 0,
date_modified INTEGER DEFAULT 0, date_backed_up INTEGER DEFAULT 0, year INTEGER
DEFAULT 0, content_rating INTEGER DEFAULT 0, content_rating_level INTEGER
DEFAULT 0, is_compilation INTEGER, is_user_disabled INTEGER DEFAULT 0,
remember_bookmark INTEGER DEFAULT 0, exclude_from_shuffle INTEGER DEFAULT 0,
artwork_status INTEGER, artwork_cache_id INTEGER DEFAULT 0, start_time_ms REAL
DEFAULT 0, stop_time_ms REAL DEFAULT 0, total_time_ms REAL DEFAULT 0,
total_burn_time_ms REAL, track_number INTEGER DEFAULT 0, track_count INTEGER
DEFAULT 0, disc_number INTEGER DEFAULT 0, disc_count INTEGER DEFAULT 0, bpm
INTEGER DEFAULT 0, relative_volume INTEGER, eq_preset TEXT, radio_stream_status
TEXT, genius_id INTEGER DEFAULT 0, genre_id INTEGER DEFAULT 0, category_id
INTEGER DEFAULT 0, album_pid INTEGER DEFAULT 0, artist_pid INTEGER DEFAULT 0,
composer_pid INTEGER DEFAULT 0, title TEXT, artist TEXT, album TEXT,
album_artist TEXT, composer TEXT, sort_title TEXT, sort_artist TEXT, sort_album
TEXT, sort_album_artist TEXT, sort_composer TEXT, title_order INTEGER,
artist_order INTEGER, album_order INTEGER, genre_order INTEGER, composer_order
INTEGER, album_artist_order INTEGER, album_by_artist_order INTEGER,
series_name_order INTEGER, comment TEXT, grouping TEXT, description TEXT,
description_long TEXT, PRIMARY KEY (pid));" \
"CREATE TABLE avformat_info (item_pid INTEGER NOT NULL, sub_id INTEGER
NOT NULL DEFAULT 0, audio_format INTEGER, bit_rate INTEGER DEFAULT 0,
sample_rate REAL DEFAULT 0, duration INTEGER, gapless_heuristic_info INTEGER,
gapless_encoding_delay INTEGER, gapless_encoding_drain INTEGER,
gapless_last_frame_resynch INTEGER, analysis_inhibit_flags INTEGER,
audio_fingerprint INTEGER, volume_normalization_energy INTEGER, PRIMARY KEY
(item_pid,sub_id));" \
"CREATE TABLE video_info (item_pid INTEGER NOT NULL,
has_alternate_audio INTEGER, has_subtitles INTEGER, characteristics_valid
INTEGER, has_closed_captions INTEGER, is_self_contained INTEGER, is_compressed
INTEGER, is_anamorphic INTEGER, season_number INTEGER, audio_language INTEGER,
audio_track_index INTEGER, audio_track_id INTEGER, subtitle_language INTEGER,
subtitle_track_index INTEGER, subtitle_track_id INTEGER, series_name TEXT,
sort_series_name TEXT, episode_id TEXT, episode_sort_id INTEGER, network_name
TEXT, extended_content_rating TEXT, movie_info TEXT, PRIMARY KEY (item_pid));" \
"CREATE TABLE video_characteristics (item_pid INTEGER, sub_id INTEGER
DEFAULT 0, track_id INTEGER, height INTEGER, width INTEGER, depth INTEGER,
codec INTEGER, frame_rate REAL, percentage_encrypted REAL, bit_rate INTEGER,
peak_bit_rate INTEGER, buffer_size INTEGER, profile INTEGER, level INTEGER,
complexity_level INTEGER, UNIQUE (item_pid,sub_id,track_id));" \
@@ -69,60 +69,12 @@ static const char Library_create[] =
"CREATE TABLE location_kind_map (id INTEGER NOT NULL, kind TEXT NOT
NULL, PRIMARY KEY (id), UNIQUE (kind));" \
"CREATE TABLE genre_map (id INTEGER NOT NULL, genre TEXT NOT NULL,
genre_order INTEGER DEFAULT 0, PRIMARY KEY (id), UNIQUE (genre));" \
"CREATE TABLE category_map (id INTEGER NOT NULL, category TEXT NOT
NULL, PRIMARY KEY (id), UNIQUE (category));" \
- "CREATE TABLE ext_item_view_membership (item_pid INTEGER PRIMARY KEY,
movie_mbr INTEGER, movie_rental_mbr INTEGER );" \
- "ANALYZE sqlite_master;" \
- "INSERT INTO \"sqlite_stat1\"
VALUES('ext_item_view_membership','ext_item_view_membership_idx_movie_rental_mbr','1
1');" \
- "INSERT INTO \"sqlite_stat1\"
VALUES('ext_item_view_membership','ext_item_view_membership_idx_movie_mbr','1
1');" \
- "INSERT INTO \"sqlite_stat1\" VALUES('item','item_idx_composer','1
1');" \
- "INSERT INTO \"sqlite_stat1\" VALUES('item','item_idx_album_artist','1
1');" \
- "INSERT INTO \"sqlite_stat1\" VALUES('item','item_idx_album','1 1');" \
- "INSERT INTO \"sqlite_stat1\" VALUES('item','item_idx_artist','1 1');" \
- "INSERT INTO \"sqlite_stat1\" VALUES('item','item_idx_title','1 1');" \
- "INSERT INTO \"sqlite_stat1\" VALUES('item','item_idx_genre_id','1
1');" \
- "INSERT INTO \"sqlite_stat1\"
VALUES('item','item_idx_is_compilation','1 1');" \
- "INSERT INTO \"sqlite_stat1\" VALUES('item','item_idx_media_kind','1
1');" \
- "INSERT INTO \"sqlite_stat1\"
VALUES('avformat_info','avformat_info_idx_audio_format','1 1');" \
- "INSERT INTO \"sqlite_stat1\"
VALUES('avformat_info','sqlite_autoindex_avformat_info_1','1 1 1');" \
- "INSERT INTO \"sqlite_stat1\"
VALUES('item_to_container','itc_idx_item_pid','1 1');" \
- "INSERT INTO \"sqlite_stat1\"
VALUES('item_to_container','itc_idx_container_pid','1 1');" \
- "INSERT INTO \"sqlite_stat1\"
VALUES('item_to_container','itc_idx_physical_order','1 1');" \
- "INSERT INTO \"sqlite_stat1\"
VALUES('location_kind_map','sqlite_autoindex_location_kind_map_1','1 1');" \
- "CREATE TABLE ml_database_status (key TEXT, value TEXT, PRIMARY
KEY(key));" \
- "INSERT INTO \"ml_database_status\"
VALUES('iTunesCommandsExecuted','8');" \
- "CREATE TRIGGER update_item_in_songs_collection AFTER UPDATE OF
media_kind, is_rental ON item BEGIN UPDATE item SET in_songs_collection =
((item.media_kind&1) AND ((item.media_kind&2)=0 AND item.is_rental=0)) WHERE
item.pid = new.pid; END;" \
- "CREATE TRIGGER update_item_title_blank AFTER UPDATE OF title ON item
BEGIN UPDATE item SET title_blank = (title = '' OR title IS NULL) WHERE
item.pid = new.pid; END;" \
- "CREATE TRIGGER update_item_artist_blank AFTER UPDATE OF artist ON item
BEGIN UPDATE item SET artist_blank = (artist = '' OR artist IS NULL) WHERE
item.pid = new.pid; END;" \
- "CREATE TRIGGER update_item_album_artist_blank AFTER UPDATE OF
album_artist ON item BEGIN UPDATE item SET album_artist_blank = (album_artist =
'' OR album_artist IS NULL) WHERE item.pid = new.pid; END;" \
- "CREATE TRIGGER update_item_album_blank AFTER UPDATE OF album ON item
BEGIN UPDATE item SET album_blank = (album = '' OR album IS NULL) WHERE
item.pid = new.pid; END;" \
- "CREATE TRIGGER update_item_composer_blank AFTER UPDATE OF composer ON
item BEGIN UPDATE item SET composer_blank = (composer = '' OR composer IS NULL)
WHERE item.pid = new.pid; END;" \
- "CREATE TRIGGER update_item_grouping_blank AFTER UPDATE OF grouping ON
item BEGIN UPDATE item SET grouping_blank = (grouping = '' OR grouping IS NULL)
WHERE item.pid = new.pid; END;" \
- "CREATE TRIGGER update_is_kind_of_field AFTER UPDATE OF is_song,
is_audio_book, is_music_video, is_movie, is_tv_show, is_ringtone, is_podcast,
is_rental ON item BEGIN " \
- "INSERT OR REPLACE INTO ext_item_view_membership(item_pid, movie_mbr,
movie_rental_mbr) VALUES(new.pid, (new.is_movie AND (NOT new.is_rental) AND
(NOT new.is_podcast)), (new.is_movie AND new.is_rental));" \
- "END;" \
"CREATE TRIGGER insert_item AFTER INSERT ON item BEGIN " \
- "INSERT OR IGNORE INTO ext_item_view_membership(item_pid, movie_mbr,
movie_rental_mbr) VALUES(new.pid, 0, 0);" \
- "UPDATE item SET in_songs_collection = ((new.media_kind&1) AND
((new.media_kind&2)=0 AND new.is_rental=0)), title_blank = (title = '' OR title
IS NULL), artist_blank = (artist = '' OR artist IS NULL), album_blank = (album
= '' OR album IS NULL), album_artist_blank = (album_artist = '' OR album_artist
IS NULL), composer_blank = (composer = '' OR composer IS NULL), grouping_blank
= (grouping = '' OR grouping IS NULL) WHERE item.pid = new.pid;" \
"UPDATE item SET is_song=((new.media_kind&1)!=0),
is_audio_book=((new.media_kind&8)!=0), is_music_video=((new.media_kind&32)!=0),
is_movie=((new.media_kind&2)!=0), is_tv_show=((new.media_kind&64)!=0),
is_ringtone=((new.media_kind&16384)!=0), is_podcast=((new.media_kind&4)!=0),
is_rental=((new.media_kind&32768)!=0) WHERE pid = new.pid;" \
"END;" \
"CREATE TRIGGER update_item_media_kind AFTER UPDATE OF media_kind ON
item BEGIN " \
"UPDATE item SET is_song=((new.media_kind&1)!=0),
is_audio_book=((new.media_kind&8)!=0), is_music_video=((new.media_kind&32)!=0),
is_movie=((new.media_kind&2)!=0), is_tv_show=((new.media_kind&64)!=0),
is_ringtone=((new.media_kind&16384)!=0), is_podcast=((new.media_kind&4)!=0),
is_rental=((new.media_kind&32768)!=0) WHERE pid = new.pid;" \
"END;" \
- "CREATE INDEX item_idx_media_kind ON item (media_kind);" \
- "CREATE INDEX item_idx_is_compilation ON item (is_compilation);" \
- "CREATE INDEX avformat_info_idx_audio_format ON avformat_info
(audio_format);" \
- "CREATE INDEX itc_idx_physical_order ON item_to_container
(physical_order);" \
- "CREATE INDEX itc_idx_container_pid ON item_to_container
(container_pid);" \
- "CREATE INDEX itc_idx_item_pid ON item_to_container (item_pid);" \
- "CREATE INDEX video_info_idx_season_number ON video_info
(season_number);" \
- "CREATE INDEX video_info_idx_episode_sort_id ON video_info
(episode_sort_id);" \
- "CREATE INDEX item_idx_genre_id ON item (genre_id);" \
- "CREATE INDEX ext_item_view_membership_idx_movie_mbr ON
ext_item_view_membership (movie_mbr);" \
- "CREATE INDEX ext_item_view_membership_idx_movie_rental_mbr ON
ext_item_view_membership (movie_rental_mbr);" \
- "CREATE INDEX item_idx_title ON item (title);" \
- "CREATE INDEX item_idx_artist ON item (artist);" \
- "CREATE INDEX item_idx_album ON item (album);" \
- "CREATE INDEX item_idx_album_artist ON item (album_artist);" \
- "CREATE INDEX item_idx_composer ON item (composer);" \
"COMMIT;";
/** creation statement for 'Locations.itdb' */
------------------------------------------------------------------------------
Throughout its 18-year history, RSA Conference consistently attracts the
world's best and brightest in the field, creating opportunities for Conference
attendees to learn about information security's most important issues through
interactions with peers, luminaries and emerging and established companies.
http://p.sf.net/sfu/rsaconf-dev2dev
_______________________________________________
gtkpod-cvs2 mailing list
gtkpod-cvs2@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/gtkpod-cvs2
