[rygel] media-export: Move SQL statements into factory
- From: Zeeshan Ali Khattak <zeeshanak src gnome org>
- To: commits-list gnome org
- Cc:
- Subject: [rygel] media-export: Move SQL statements into factory
- Date: Sat, 7 Aug 2010 15:44:06 +0000 (UTC)
commit 4d891c26d00a6fcfb0d657b1a116a6e77f8cc75b
Author: Jens Georg <mail jensge org>
Date: Sat Jul 24 21:18:49 2010 +0300
media-export: Move SQL statements into factory
Seperate the textual SQL stuff from the implementation.
Could be used to exchange the backing database, but currently only done
to make the MediaCache class less cluttered.
src/plugins/media-export/Makefile.am | 1 +
.../rygel-media-export-media-cache-upgrader.vala | 23 +-
.../rygel-media-export-media-cache.vala | 230 +++---------------
.../rygel-media-export-sql-factory.vala | 266 ++++++++++++++++++++
4 files changed, 310 insertions(+), 210 deletions(-)
---
diff --git a/src/plugins/media-export/Makefile.am b/src/plugins/media-export/Makefile.am
index 49fa248..5340b61 100644
--- a/src/plugins/media-export/Makefile.am
+++ b/src/plugins/media-export/Makefile.am
@@ -23,6 +23,7 @@ AM_CFLAGS = $(LIBGUPNP_CFLAGS) \
librygel_media_export_la_SOURCES = rygel-media-export-plugin.vala \
rygel-media-export-database.vala \
rygel-media-export-db-container.vala \
+ rygel-media-export-sql-factory.vala \
rygel-media-export-media-cache.vala \
rygel-media-export-media-cache-upgrader.vala \
rygel-media-export-metadata-extractor.vala \
diff --git a/src/plugins/media-export/rygel-media-export-media-cache-upgrader.vala b/src/plugins/media-export/rygel-media-export-media-cache-upgrader.vala
index 9e2b90c..d3e9183 100644
--- a/src/plugins/media-export/rygel-media-export-media-cache-upgrader.vala
+++ b/src/plugins/media-export/rygel-media-export-media-cache-upgrader.vala
@@ -23,6 +23,8 @@ using Gee;
internal class Rygel.MediaExport.MediaCacheUpgrader {
private unowned Database database;
+ private unowned SQLFactory sql;
+
private const string UPDATE_V3_V4_STRING_2 =
"UPDATE meta_data SET object_fk = " +
"(SELECT upnp_id FROM Object WHERE metadata_fk = meta_data.id)";
@@ -33,8 +35,9 @@ internal class Rygel.MediaExport.MediaCacheUpgrader {
private const string UPDATE_V3_V4_STRING_4 =
"UPDATE Object SET timestamp = 0";
- public MediaCacheUpgrader (Database database) {
+ public MediaCacheUpgrader (Database database, SQLFactory sql) {
this.database = database;
+ this.sql = sql;
}
public bool needs_upgrade (out int current_version) throws Error {
@@ -50,7 +53,7 @@ internal class Rygel.MediaExport.MediaCacheUpgrader {
});
current_version = current_version_temp;
- return current_version < MediaCache.schema_version.to_int ();
+ return current_version < SQLFactory.schema_version.to_int ();
}
public void fix_schema () throws Error {
@@ -72,7 +75,7 @@ internal class Rygel.MediaExport.MediaCacheUpgrader {
database.exec ("DELETE FROM Object WHERE upnp_id IN (" +
"SELECT DISTINCT object_fk FROM meta_data)");
database.exec ("DROP TABLE Meta_Data");
- database.exec (MediaCache.CREATE_META_DATA_TABLE_STRING);
+ database.exec (this.sql.make (SQLString.TABLE_METADATA));
database.commit ();
} catch (Error error) {
database.rollback ();
@@ -84,7 +87,7 @@ internal class Rygel.MediaExport.MediaCacheUpgrader {
public void upgrade (int old_version) {
debug ("Older schema detected. Upgrading...");
- int current_version = MediaCache.schema_version.to_int ();
+ int current_version = SQLFactory.schema_version.to_int ();
while (old_version < current_version) {
if (this.database != null) {
switch (old_version) {
@@ -121,7 +124,7 @@ internal class Rygel.MediaExport.MediaCacheUpgrader {
try {
database.begin ();
database.exec ("ALTER TABLE Meta_Data RENAME TO _Meta_Data");
- database.exec (MediaCache.CREATE_META_DATA_TABLE_STRING);
+ database.exec (this.sql.make (SQLString.TABLE_METADATA));
database.exec ("INSERT INTO meta_data (size, mime_type, " +
"duration, width, height, class, author, album, " +
"date, bitrate, sample_freq, bits_per_sample, " +
@@ -134,7 +137,7 @@ internal class Rygel.MediaExport.MediaCacheUpgrader {
database.exec ("DROP TABLE _Meta_Data");
database.exec (UPDATE_V3_V4_STRING_3);
database.exec (UPDATE_V3_V4_STRING_4);
- database.exec (MediaCache.CREATE_TRIGGER_STRING);
+ database.exec (this.sql.make (SQLString.TRIGGER_COMMON));
database.exec ("UPDATE schema_info SET version = '4'");
database.commit ();
} catch (DatabaseError error) {
@@ -149,12 +152,12 @@ internal class Rygel.MediaExport.MediaCacheUpgrader {
try {
database.begin ();
database.exec ("DROP TRIGGER IF EXISTS trgr_delete_children");
- database.exec (MediaCache.CREATE_CLOSURE_TABLE);
+ database.exec (this.sql.make (SQLString.TABLE_CLOSURE));
// this is to have the database generate the closure table
database.exec ("ALTER TABLE Object RENAME TO _Object");
database.exec ("CREATE TABLE Object AS SELECT * FROM _Object");
database.exec ("DELETE FROM Object");
- database.exec (MediaCache.CREATE_CLOSURE_TRIGGER_STRING);
+ database.exec (this.sql.make (SQLString.TRIGGER_CLOSURE));
database.exec ("INSERT INTO _Object (upnp_id, type_fk, title, " +
"timestamp) VALUES ('0', 0, 'Root', 0)");
database.exec ("INSERT INTO Object (upnp_id, type_fk, title, " +
@@ -179,8 +182,8 @@ internal class Rygel.MediaExport.MediaCacheUpgrader {
database.exec ("ALTER TABLE _Object RENAME TO Object");
// the triggers created above have been dropped automatically
// so we need to recreate them
- database.exec (MediaCache.CREATE_CLOSURE_TRIGGER_STRING);
- database.exec (MediaCache.CREATE_INDICES_STRING);
+ database.exec (this.sql.make (SQLString.TRIGGER_CLOSURE));
+ database.exec (this.sql.make (SQLString.INDEX_COMMON));
database.exec ("UPDATE schema_info SET version = '5'");
database.commit ();
database.exec ("VACUUM");
diff --git a/src/plugins/media-export/rygel-media-export-media-cache.vala b/src/plugins/media-export/rygel-media-export-media-cache.vala
index 874d80f..f4fa83d 100644
--- a/src/plugins/media-export/rygel-media-export-media-cache.vala
+++ b/src/plugins/media-export/rygel-media-export-media-cache.vala
@@ -37,32 +37,6 @@ internal enum Rygel.MediaExport.ObjectType {
ITEM
}
-internal enum Rygel.DetailColumn {
- TYPE,
- TITLE,
- SIZE,
- MIME_TYPE,
- WIDTH,
- HEIGHT,
- CLASS,
- AUTHOR,
- ALBUM,
- DATE,
- BITRATE,
- SAMPLE_FREQ,
- BITS_PER_SAMPLE,
- CHANNELS,
- TRACK,
- COLOR_DEPTH,
- DURATION,
- ID,
- PARENT,
- TIMESTAMP,
- URI,
- DLNA_PROFILE,
- GENRE
-}
-
/**
* Persistent storage of media objects
*
@@ -71,164 +45,11 @@ internal enum Rygel.DetailColumn {
public class Rygel.MediaExport.MediaCache : Object {
private Database db;
private ObjectFactory factory;
- internal const string schema_version = "8";
- internal const string CREATE_META_DATA_TABLE_STRING =
- "CREATE TABLE meta_data (size INTEGER NOT NULL, " +
- "mime_type TEXT NOT NULL, " +
- "dlna_profile TEXT, " +
- "duration INTEGER, " +
- "width INTEGER, " +
- "height INTEGER, " +
- "class TEXT NOT NULL, " +
- "author TEXT, " +
- "album TEXT, " +
- "genre TEXT, " +
- "date TEXT, " +
- "bitrate INTEGER, " +
- "sample_freq INTEGER, " +
- "bits_per_sample INTEGER, " +
- "channels INTEGER, " +
- "track INTEGER, " +
- "color_depth INTEGER, " +
- "object_fk TEXT UNIQUE CONSTRAINT " +
- "object_fk_id REFERENCES Object(upnp_id) " +
- "ON DELETE CASCADE);";
-
- private const string SCHEMA_STRING =
- "CREATE TABLE schema_info (version TEXT NOT NULL); " +
- CREATE_META_DATA_TABLE_STRING +
- "CREATE TABLE object (parent TEXT CONSTRAINT parent_fk_id " +
- "REFERENCES Object(upnp_id), " +
- "upnp_id TEXT PRIMARY KEY, " +
- "type_fk INTEGER, " +
- "title TEXT NOT NULL, " +
- "timestamp INTEGER NOT NULL, " +
- "uri TEXT, " +
- "flags TEXT);" +
- "INSERT INTO schema_info (version) VALUES ('" +
- MediaCache.schema_version + "'); ";
-
- private const string CREATE_CLOSURE_TABLE =
- "CREATE TABLE closure (ancestor TEXT, descendant TEXT, depth INTEGER)";
-
- private const string CREATE_CLOSURE_TRIGGER_STRING =
- "CREATE TRIGGER trgr_update_closure " +
- "AFTER INSERT ON Object " +
- "FOR EACH ROW BEGIN " +
- "INSERT INTO Closure (ancestor, descendant, depth) " +
- "VALUES (NEW.upnp_id, NEW.upnp_id, 0); " +
- "INSERT INTO Closure (ancestor, descendant, depth) " +
- "SELECT ancestor, NEW.upnp_id, depth + 1 FROM Closure " +
- "WHERE descendant = NEW.parent;" +
- "END;" +
-
- "CREATE TRIGGER trgr_delete_closure " +
- "AFTER DELETE ON Object " +
- "FOR EACH ROW BEGIN " +
- "DELETE FROM Closure WHERE descendant = OLD.upnp_id;" +
- "END;";
-
- // these triggers emulate ON DELETE CASCADE
- private const string CREATE_TRIGGER_STRING =
- "CREATE TRIGGER trgr_delete_metadata " +
- "BEFORE DELETE ON Object " +
- "FOR EACH ROW BEGIN " +
- "DELETE FROM meta_data WHERE meta_data.object_fk = OLD.upnp_id; "+
- "END;";
-
- private const string CREATE_INDICES_STRING =
- "CREATE INDEX idx_parent on Object(parent);" +
- "CREATE INDEX idx_meta_data_fk on meta_data(object_fk);" +
- "CREATE INDEX idx_closure on Closure(descendant,depth);";
-
- private const string SAVE_META_DATA_STRING =
- "INSERT OR REPLACE INTO meta_data " +
- "(size, mime_type, width, height, class, " +
- "author, album, date, bitrate, " +
- "sample_freq, bits_per_sample, channels, " +
- "track, color_depth, duration, object_fk, dlna_profile, genre) VALUES " +
- "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
-
- private const string INSERT_OBJECT_STRING =
- "INSERT OR REPLACE INTO Object (upnp_id, title, type_fk, parent, timestamp, uri) " +
- "VALUES (?,?,?,?,?,?)";
-
- private const string DELETE_BY_ID_STRING =
- "DELETE FROM Object WHERE upnp_id IN " +
- "(SELECT descendant FROM closure WHERE ancestor = ?)";
-
- private const string ALL_DETAILS_STRING =
- "o.type_fk, o.title, m.size, m.mime_type, m.width, " +
- "m.height, m.class, m.author, m.album, m.date, m.bitrate, " +
- "m.sample_freq, m.bits_per_sample, m.channels, m.track, " +
- "m.color_depth, m.duration, o.upnp_id, o.parent, o.timestamp, " +
- "o.uri, m.dlna_profile, m.genre ";
-
- private const string GET_OBJECT_WITH_PATH =
- "SELECT DISTINCT " + ALL_DETAILS_STRING +
- "FROM Object o " +
- "JOIN Closure c ON (o.upnp_id = c.ancestor) " +
- "LEFT OUTER JOIN meta_data m ON (o.upnp_id = m.object_fk) " +
- "WHERE c.descendant = ? ORDER BY c.depth DESC";
-
- /**
- * This is the database query used to retrieve the children for a
- * given object.
- *
- * Sorting is as follows:
- * - by type: containers first, then items if both are present
- * - by upnp_class: items are sorted according to their class
- * - by track: sorted by track
- * - and after that alphabetically
- */
- private const string GET_CHILDREN_STRING =
- "SELECT " + ALL_DETAILS_STRING +
- "FROM Object o LEFT OUTER JOIN meta_data m " +
- "ON o.upnp_id = m.object_fk " +
- "WHERE o.parent = ? " +
- "ORDER BY o.type_fk ASC, " +
- "m.class ASC, " +
- "m.track ASC, " +
- "o.title ASC " +
- "LIMIT ?,?";
-
- // The uris are joined in to be able to filter by "ref"
- private const string GET_OBJECTS_BY_FILTER_STRING =
- "SELECT DISTINCT " + ALL_DETAILS_STRING +
- "FROM Object o " +
- "JOIN Closure c ON o.upnp_id = c.descendant AND c.ancestor = ? " +
- "LEFT OUTER JOIN meta_data m " +
- "ON o.upnp_id = m.object_fk %s" +
- "ORDER BY o.parent ASC, " +
- "o.type_fk ASC, " +
- "m.class ASC, " +
- "m.track ASC, " +
- "o.title ASC " +
- "LIMIT ?,?";
-
- // The uris are joined in to be able to filter by "ref"
- private const string GET_OBJECT_COUNT_BY_FILTER_STRING =
- "SELECT COUNT(o.type_fk) FROM Object o " +
- "JOIN Closure c ON o.upnp_id = c.descendant AND c.ancestor = ? " +
- "JOIN meta_data m " +
- "ON o.upnp_id = m.object_fk %s";
-
- private const string CHILDREN_COUNT_STRING =
- "SELECT COUNT(upnp_id) FROM Object WHERE Object.parent = ?";
-
- private const string OBJECT_EXISTS_STRING =
- "SELECT COUNT(upnp_id), timestamp FROM Object WHERE Object.upnp_id = ?";
-
- private const string GET_CHILD_ID_STRING =
- "SELECT upnp_id FROM OBJECT WHERE parent = ?";
-
- private const string GET_META_DATA_COLUMN_STRING =
- "SELECT DISTINCT %s FROM meta_data AS m " +
- "WHERE %s IS NOT NULL %s ORDER BY %s LIMIT ?,?";
+ private SQLFactory sql;
public void remove_by_id (string id) throws DatabaseError {
GLib.Value[] values = { id };
- this.db.exec (DELETE_BY_ID_STRING, values);
+ this.db.exec (this.sql.make (SQLString.DELETE), values);
}
public void remove_object (MediaObject object) throws DatabaseError,
@@ -280,7 +101,7 @@ public class Rygel.MediaExport.MediaCache : Object {
return true;
};
- this.db.exec (GET_OBJECT_WITH_PATH, values, cb);
+ this.db.exec (this.sql.make (SQLString.GET_OBJECT), values, cb);
return parent;
}
@@ -314,7 +135,7 @@ public class Rygel.MediaExport.MediaCache : Object {
int count = 0;
GLib.Value[] values = { container_id };
- this.db.exec (CHILDREN_COUNT_STRING,
+ this.db.exec (this.sql.make (SQLString.CHILD_COUNT),
values,
(statement) => {
count = statement.column_int (0);
@@ -331,7 +152,7 @@ public class Rygel.MediaExport.MediaCache : Object {
GLib.Value[] values = { object_id };
int64 tmp_timestamp = 0;
- this.db.exec (OBJECT_EXISTS_STRING,
+ this.db.exec (this.sql.make (SQLString.EXISTS),
values,
(statement) => {
exists = statement.column_int (0) == 1;
@@ -365,7 +186,9 @@ public class Rygel.MediaExport.MediaCache : Object {
return true;
};
- this.db.exec (GET_CHILDREN_STRING, values, callback);
+ this.db.exec (this.sql.make (SQLString.GET_CHILDREN),
+ values,
+ callback);
return children;
}
@@ -447,7 +270,10 @@ public class Rygel.MediaExport.MediaCache : Object {
return false;
};
- this.db.exec (GET_OBJECT_COUNT_BY_FILTER_STRING.printf (filter),
+ unowned string sql = this.sql.make (
+ SQLString.GET_OBJECT_COUNT_BY_FILTER);
+
+ this.db.exec (sql.printf (filter),
args.values,
callback);
@@ -499,7 +325,8 @@ public class Rygel.MediaExport.MediaCache : Object {
}
};
- this.db.exec (GET_OBJECTS_BY_FILTER_STRING.printf (filter),
+ var sql = this.sql.make (SQLString.GET_OBJECTS_BY_FILTER);
+ this.db.exec (sql.printf (filter),
args.values,
callback);
@@ -507,6 +334,7 @@ public class Rygel.MediaExport.MediaCache : Object {
}
public MediaCache (string name) throws Error {
+ this.sql = new SQLFactory ();
this.open_db (name);
this.factory = new ObjectFactory ();
}
@@ -514,10 +342,10 @@ public class Rygel.MediaExport.MediaCache : Object {
private void open_db (string name) throws Error {
this.db = new Database (name);
int old_version = -1;
- int current_version = schema_version.to_int ();
+ int current_version = SQLFactory.schema_version.to_int ();
try {
- var upgrader = new MediaCacheUpgrader (this.db);
+ var upgrader = new MediaCacheUpgrader (this.db, this.sql);
if (upgrader.needs_upgrade (out old_version)) {
upgrader.upgrade (old_version);
} else if (old_version == current_version) {
@@ -547,7 +375,7 @@ public class Rygel.MediaExport.MediaCache : Object {
});
if (rows == 0) {
debug ("Empty database, creating new schema version %s",
- schema_version);
+ SQLFactory.schema_version);
if (!create_schema ()) {
this.db = null;
@@ -587,7 +415,7 @@ public class Rygel.MediaExport.MediaCache : Object {
item.id,
item.dlna_profile,
item.genre};
- this.db.exec (SAVE_META_DATA_STRING, values);
+ this.db.exec (this.sql.make (SQLString.SAVE_METADATA), values);
}
private void create_object (MediaObject item) throws Error {
@@ -611,7 +439,7 @@ public class Rygel.MediaExport.MediaCache : Object {
(int64) item.modified,
item.uris.size == 0 ? null : item.uris[0]
};
- this.db.exec (INSERT_OBJECT_STRING, values);
+ this.db.exec (this.sql.make (SQLString.INSERT), values);
}
/**
@@ -625,11 +453,11 @@ public class Rygel.MediaExport.MediaCache : Object {
private bool create_schema () {
try {
db.begin ();
- db.exec (SCHEMA_STRING);
- db.exec (CREATE_TRIGGER_STRING);
- db.exec (CREATE_CLOSURE_TABLE);
- db.exec (CREATE_INDICES_STRING);
- db.exec (CREATE_CLOSURE_TRIGGER_STRING);
+ db.exec (this.sql.make (SQLString.SCHEMA));
+ db.exec (this.sql.make (SQLString.TRIGGER_COMMON));
+ db.exec (this.sql.make (SQLString.TABLE_CLOSURE));
+ db.exec (this.sql.make (SQLString.INDEX_COMMON));
+ db.exec (this.sql.make (SQLString.TRIGGER_CLOSURE));
db.commit ();
db.analyze ();
@@ -721,7 +549,7 @@ public class Rygel.MediaExport.MediaCache : Object {
ArrayList<string> children = new ArrayList<string> (str_equal);
GLib.Value[] values = { container_id };
- this.db.exec (GET_CHILD_ID_STRING,
+ this.db.exec (this.sql.make (SQLString.CHILD_IDS),
values,
(statement) => {
children.add (statement.column_text (0));
@@ -905,8 +733,10 @@ public class Rygel.MediaExport.MediaCache : Object {
return true;
};
- var sql = GET_META_DATA_COLUMN_STRING.printf (column, column, filter, column);
- this.db.exec (sql, args.values, callback);
+ var sql = this.sql.make (SQLString.GET_META_DATA_COLUMN);
+ this.db.exec (sql.printf (column, column, filter, column),
+ args.values,
+ callback);
return data;
}
diff --git a/src/plugins/media-export/rygel-media-export-sql-factory.vala b/src/plugins/media-export/rygel-media-export-sql-factory.vala
new file mode 100644
index 0000000..f076ec1
--- /dev/null
+++ b/src/plugins/media-export/rygel-media-export-sql-factory.vala
@@ -0,0 +1,266 @@
+/*
+ * Copyright (C) 2010 Jens Georg <mail jensge org>.
+ *
+ * Author: Jens Georg <mail jensge org>
+ *
+ * This file is part of Rygel.
+ *
+ * Rygel is free software; you can redistribute it and/or modify
+ * it under the terms of the GNU Lesser General Public License as published by
+ * the Free Software Foundation; either version 2 of the License, or
+ * (at your option) any later version.
+ *
+ * Rygel is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ * GNU Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
+ */
+
+internal enum Rygel.MediaExport.DetailColumn {
+ TYPE,
+ TITLE,
+ SIZE,
+ MIME_TYPE,
+ WIDTH,
+ HEIGHT,
+ CLASS,
+ AUTHOR,
+ ALBUM,
+ DATE,
+ BITRATE,
+ SAMPLE_FREQ,
+ BITS_PER_SAMPLE,
+ CHANNELS,
+ TRACK,
+ COLOR_DEPTH,
+ DURATION,
+ ID,
+ PARENT,
+ TIMESTAMP,
+ URI,
+ DLNA_PROFILE,
+ GENRE
+}
+
+internal enum Rygel.MediaExport.SQLString {
+ SAVE_METADATA,
+ INSERT,
+ DELETE,
+ GET_OBJECT,
+ GET_CHILDREN,
+ GET_OBJECTS_BY_FILTER,
+ GET_OBJECT_COUNT_BY_FILTER,
+ GET_META_DATA_COLUMN,
+ CHILD_COUNT,
+ EXISTS,
+ CHILD_IDS,
+ TABLE_METADATA,
+ TABLE_CLOSURE,
+ TRIGGER_CLOSURE,
+ TRIGGER_COMMON,
+ INDEX_COMMON,
+ SCHEMA,
+}
+
+internal class Rygel.MediaExport.SQLFactory : Object {
+ private const string SAVE_META_DATA_STRING =
+ "INSERT OR REPLACE INTO meta_data " +
+ "(size, mime_type, width, height, class, " +
+ "author, album, date, bitrate, " +
+ "sample_freq, bits_per_sample, channels, " +
+ "track, color_depth, duration, object_fk, dlna_profile, genre) VALUES " +
+ "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
+
+ private const string INSERT_OBJECT_STRING =
+ "INSERT OR REPLACE INTO Object (upnp_id, title, type_fk, parent, timestamp, uri) " +
+ "VALUES (?,?,?,?,?,?)";
+
+ private const string DELETE_BY_ID_STRING =
+ "DELETE FROM Object WHERE upnp_id IN " +
+ "(SELECT descendant FROM closure WHERE ancestor = ?)";
+
+ private const string ALL_DETAILS_STRING =
+ "o.type_fk, o.title, m.size, m.mime_type, m.width, " +
+ "m.height, m.class, m.author, m.album, m.date, m.bitrate, " +
+ "m.sample_freq, m.bits_per_sample, m.channels, m.track, " +
+ "m.color_depth, m.duration, o.upnp_id, o.parent, o.timestamp, " +
+ "o.uri, m.dlna_profile, m.genre ";
+
+ private const string GET_OBJECT_WITH_PATH =
+ "SELECT DISTINCT " + ALL_DETAILS_STRING +
+ "FROM Object o " +
+ "JOIN Closure c ON (o.upnp_id = c.ancestor) " +
+ "LEFT OUTER JOIN meta_data m ON (o.upnp_id = m.object_fk) " +
+ "WHERE c.descendant = ? ORDER BY c.depth DESC";
+
+ /**
+ * This is the database query used to retrieve the children for a
+ * given object.
+ *
+ * Sorting is as follows:
+ * - by type: containers first, then items if both are present
+ * - by upnp_class: items are sorted according to their class
+ * - by track: sorted by track
+ * - and after that alphabetically
+ */
+ private const string GET_CHILDREN_STRING =
+ "SELECT " + ALL_DETAILS_STRING +
+ "FROM Object o LEFT OUTER JOIN meta_data m " +
+ "ON o.upnp_id = m.object_fk " +
+ "WHERE o.parent = ? " +
+ "ORDER BY o.type_fk ASC, " +
+ "m.class ASC, " +
+ "m.track ASC, " +
+ "o.title ASC " +
+ "LIMIT ?,?";
+
+ // The uris are joined in to be able to filter by "ref"
+ private const string GET_OBJECTS_BY_FILTER_STRING =
+ "SELECT DISTINCT " + ALL_DETAILS_STRING +
+ "FROM Object o " +
+ "JOIN Closure c ON o.upnp_id = c.descendant AND c.ancestor = ? " +
+ "LEFT OUTER JOIN meta_data m " +
+ "ON o.upnp_id = m.object_fk %s" +
+ "ORDER BY o.parent ASC, " +
+ "o.type_fk ASC, " +
+ "m.class ASC, " +
+ "m.track ASC, " +
+ "o.title ASC " +
+ "LIMIT ?,?";
+
+ // The uris are joined in to be able to filter by "ref"
+ private const string GET_OBJECT_COUNT_BY_FILTER_STRING =
+ "SELECT COUNT(o.type_fk) FROM Object o " +
+ "JOIN Closure c ON o.upnp_id = c.descendant AND c.ancestor = ? " +
+ "JOIN meta_data m " +
+ "ON o.upnp_id = m.object_fk %s";
+
+ private const string CHILDREN_COUNT_STRING =
+ "SELECT COUNT(upnp_id) FROM Object WHERE Object.parent = ?";
+
+ private const string OBJECT_EXISTS_STRING =
+ "SELECT COUNT(upnp_id), timestamp FROM Object WHERE Object.upnp_id = ?";
+
+ private const string GET_CHILD_ID_STRING =
+ "SELECT upnp_id FROM OBJECT WHERE parent = ?";
+
+ private const string GET_META_DATA_COLUMN_STRING =
+ "SELECT DISTINCT %s FROM meta_data AS m " +
+ "WHERE %s IS NOT NULL %s ORDER BY %s LIMIT ?,?";
+
+ internal const string schema_version = "8";
+ internal const string CREATE_META_DATA_TABLE_STRING =
+ "CREATE TABLE meta_data (size INTEGER NOT NULL, " +
+ "mime_type TEXT NOT NULL, " +
+ "dlna_profile TEXT, " +
+ "duration INTEGER, " +
+ "width INTEGER, " +
+ "height INTEGER, " +
+ "class TEXT NOT NULL, " +
+ "author TEXT, " +
+ "album TEXT, " +
+ "genre TEXT, " +
+ "date TEXT, " +
+ "bitrate INTEGER, " +
+ "sample_freq INTEGER, " +
+ "bits_per_sample INTEGER, " +
+ "channels INTEGER, " +
+ "track INTEGER, " +
+ "color_depth INTEGER, " +
+ "object_fk TEXT UNIQUE CONSTRAINT " +
+ "object_fk_id REFERENCES Object(upnp_id) " +
+ "ON DELETE CASCADE);";
+
+ private const string SCHEMA_STRING =
+ "CREATE TABLE schema_info (version TEXT NOT NULL); " +
+ CREATE_META_DATA_TABLE_STRING +
+ "CREATE TABLE object (parent TEXT CONSTRAINT parent_fk_id " +
+ "REFERENCES Object(upnp_id), " +
+ "upnp_id TEXT PRIMARY KEY, " +
+ "type_fk INTEGER, " +
+ "title TEXT NOT NULL, " +
+ "timestamp INTEGER NOT NULL, " +
+ "uri TEXT, " +
+ "flags TEXT);" +
+ "INSERT INTO schema_info (version) VALUES ('" +
+ SQLFactory.schema_version + "'); ";
+
+ private const string CREATE_CLOSURE_TABLE =
+ "CREATE TABLE closure (ancestor TEXT, descendant TEXT, depth INTEGER)";
+
+ private const string CREATE_CLOSURE_TRIGGER_STRING =
+ "CREATE TRIGGER trgr_update_closure " +
+ "AFTER INSERT ON Object " +
+ "FOR EACH ROW BEGIN " +
+ "INSERT INTO Closure (ancestor, descendant, depth) " +
+ "VALUES (NEW.upnp_id, NEW.upnp_id, 0); " +
+ "INSERT INTO Closure (ancestor, descendant, depth) " +
+ "SELECT ancestor, NEW.upnp_id, depth + 1 FROM Closure " +
+ "WHERE descendant = NEW.parent;" +
+ "END;" +
+
+ "CREATE TRIGGER trgr_delete_closure " +
+ "AFTER DELETE ON Object " +
+ "FOR EACH ROW BEGIN " +
+ "DELETE FROM Closure WHERE descendant = OLD.upnp_id;" +
+ "END;";
+
+ // these triggers emulate ON DELETE CASCADE
+ private const string CREATE_TRIGGER_STRING =
+ "CREATE TRIGGER trgr_delete_metadata " +
+ "BEFORE DELETE ON Object " +
+ "FOR EACH ROW BEGIN " +
+ "DELETE FROM meta_data WHERE meta_data.object_fk = OLD.upnp_id; "+
+ "END;";
+
+ private const string CREATE_INDICES_STRING =
+ "CREATE INDEX idx_parent on Object(parent);" +
+ "CREATE INDEX idx_meta_data_fk on meta_data(object_fk);" +
+ "CREATE INDEX idx_closure on Closure(descendant,depth);";
+
+
+ public unowned string make (SQLString query) {
+ switch (query) {
+ case SQLString.SAVE_METADATA:
+ return SAVE_META_DATA_STRING;
+ case SQLString.INSERT:
+ return INSERT_OBJECT_STRING;
+ case SQLString.DELETE:
+ return DELETE_BY_ID_STRING;
+ case SQLString.GET_OBJECT:
+ return GET_OBJECT_WITH_PATH;
+ case SQLString.GET_CHILDREN:
+ return GET_CHILDREN_STRING;
+ case SQLString.GET_OBJECTS_BY_FILTER:
+ return GET_OBJECTS_BY_FILTER_STRING;
+ case SQLString.GET_OBJECT_COUNT_BY_FILTER:
+ return GET_OBJECT_COUNT_BY_FILTER_STRING;
+ case SQLString.GET_META_DATA_COLUMN:
+ return GET_META_DATA_COLUMN_STRING;
+ case SQLString.CHILD_COUNT:
+ return CHILDREN_COUNT_STRING;
+ case SQLString.EXISTS:
+ return OBJECT_EXISTS_STRING;
+ case SQLString.CHILD_IDS:
+ return GET_CHILD_ID_STRING;
+ case SQLString.TABLE_METADATA:
+ return CREATE_META_DATA_TABLE_STRING;
+ case SQLString.TRIGGER_COMMON:
+ return CREATE_TRIGGER_STRING;
+ case SQLString.TRIGGER_CLOSURE:
+ return CREATE_CLOSURE_TRIGGER_STRING;
+ case SQLString.INDEX_COMMON:
+ return CREATE_INDICES_STRING;
+ case SQLString.SCHEMA:
+ return SCHEMA_STRING;
+ case SQLString.TABLE_CLOSURE:
+ return CREATE_CLOSURE_TABLE;
+ default:
+ assert_not_reached ();
+ }
+ }
+}
[
Date Prev][
Date Next] [
Thread Prev][
Thread Next]
[
Thread Index]
[
Date Index]
[
Author Index]
