[rygel] core: more db schema fixes
- From: Zeeshan Ali Khattak <zeeshanak src gnome org>
- To: svn-commits-list gnome org
- Subject: [rygel] core: more db schema fixes
- Date: Thu, 25 Jun 2009 15:45:55 +0000 (UTC)
commit e5f78a7a8cf6cdc6889425baaa1c51676490a7ca
Author: Jens Georg <mail jensge org>
Date: Thu Jun 4 22:25:27 2009 +0200
core: more db schema fixes
src/rygel/rygel-media-db.vala | 154 +++++++++++++++++++++++++++++-----------
1 files changed, 111 insertions(+), 43 deletions(-)
---
diff --git a/src/rygel/rygel-media-db.vala b/src/rygel/rygel-media-db.vala
index 4861c8e..1cc5083 100644
--- a/src/rygel/rygel-media-db.vala
+++ b/src/rygel/rygel-media-db.vala
@@ -26,7 +26,8 @@ using Gee;
using Sqlite;
public errordomain Rygel.MediaDBError {
- SQLITE_ERROR
+ SQLITE_ERROR,
+ GENERAL_ERROR
}
public enum Rygel.MediaDBObjectType {
@@ -36,7 +37,7 @@ public enum Rygel.MediaDBObjectType {
public class Rygel.MediaDB : Object {
private Database db;
- private const string schema_version = "2";
+ private const string schema_version = "3";
private const string db_schema_v1 =
"BEGIN;" +
"CREATE TABLE Schema_Info (version TEXT NOT NULL); " +
@@ -49,7 +50,6 @@ public class Rygel.MediaDB : Object {
"width INTEGER, " +
"height INTEGER, " +
"class TEXT NOT NULL, " +
- "title TEXT NOT NULL, " +
"author TEXT, " +
"album TEXT, " +
"date TEXT, " +
@@ -59,12 +59,13 @@ public class Rygel.MediaDB : Object {
"channels INTEGER, " +
"track, " +
"color_depth);" +
- "CREATE TABLE Object (id INTEGER PRIMARY KEY AUTOINCREMENT, " +
- "upnp_id TEXT UNIQUE, " +
+ "CREATE TABLE Object (parent TEXT REFERENCES Object(upnp_id), " +
+ "upnp_id TEXT PRIMARY KEY, " +
"type_fk INTEGER REFERENCES Object_Type(id), " +
+ "title TEXT NOT NULL, " +
"metadata_fk INTEGER REFERENCES Meta_Data(id) " +
"ON DELETE CASCADE);" +
- "CREATE TABLE Uri (object_fk INTEGER REFERENCES Object(id), "+
+ "CREATE TABLE Uri (object_fk TEXT REFERENCES Object(upnp_id), "+
"uri TEXT NOT NULL);" +
"INSERT INTO Object_Type (id, desc) VALUES (0, 'Container'); " +
"INSERT INTO Object_Type (id, desc) VALUES (1, 'Item'); " +
@@ -75,18 +76,24 @@ public class Rygel.MediaDB : Object {
private const string META_DATA_INSERT_STRING =
"INSERT INTO Meta_Data " +
"(size, mime_type, width, height, class, " +
- "title, author, album, date, bitrate, " +
+ "author, album, date, bitrate, " +
"sample_freq, bits_per_sample, channels, " +
"track, color_depth, duration) VALUES " +
- "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
+ "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
private const string OBJECT_INSERT_STRING =
- "INSERT INTO Object (upnp_id, type_fk, metadata_fk) " +
- "VALUES (?,?,?);";
+ "INSERT INTO Object (upnp_id, title, type_fk, metadata_fk, parent) " +
+ "VALUES (?,?,?,?,?)";
private const string URI_INSERT_STRING =
- "INSERT INTO Uri (object_fk, uri) VALUES (?,?);";
+ "INSERT INTO Uri (object_fk, uri) VALUES (?,?)";
+ private const string ITEM_GET_STRING =
+ "SELECT size, mime_type, width, height, class, Object.title, author, album, " +
+ "date, bitrate, sample_freq, bits_per_sample, channels, track, " +
+ "color_depth, duration " +
+ "FROM Meta_Data JOIN Object " +
+ "ON Object.metadata_fk = Meta_Data.id WHERE Object.upnp_id = ?";
public MediaDB (string name) {
var rc = Database.open (name, out this.db);
@@ -149,17 +156,40 @@ public class Rygel.MediaDB : Object {
public signal void item_added (string item_id);
+ public void save_object (MediaObject obj) throws Error {
+ if (obj is MediaItem) {
+ save_item ((MediaItem)obj);
+ } else if (obj is MediaContainer) {
+ save_container ((MediaContainer)obj);
+ } else {
+ throw new MediaDBError.GENERAL_ERROR ("Invalid object type");
+ }
+ }
+
+ public void save_container (MediaContainer container) throws Error {
+ var rc = db.exec ("BEGIN");
+ try {
+ save_object (container, -1);
+ rc = db.exec ("COMMIT");
+ } catch (Error error) {
+ rc = db.exec ("ROLLBACK");
+ }
+ }
+
public void save_item (MediaItem item) throws Error {
var rc = db.exec ("BEGIN;");
try {
var id = save_metadata (item);
- id = create_object (item, id);
- save_uris (item, id);
+ save_object (item, id);
+ save_uris (item);
rc = db.exec ("COMMIT;");
if (rc == Sqlite.OK) {
item_added (item.id);
}
} catch (Error error) {
+ warning ("Failed to add item with id %s: %s",
+ item.id,
+ error.message);
rc = db.exec ("ROLLBACK;");
}
}
@@ -176,17 +206,16 @@ public class Rygel.MediaDB : Object {
statement.bind_int (3, item.width);
statement.bind_int (4, item.height);
statement.bind_text (5, item.upnp_class);
- statement.bind_text (6, item.title);
- statement.bind_text (7, item.author);
- statement.bind_text (8, item.album);
- statement.bind_text (9, item.date);
- statement.bind_int (10, item.bitrate);
- statement.bind_int (11, item.sample_freq);
- statement.bind_int (12, item.bits_per_sample);
- statement.bind_int (13, item.n_audio_channels);
- statement.bind_int (14, item.track_number);
- statement.bind_int (15, item.color_depth);
- statement.bind_int64 (16, item.duration);
+ statement.bind_text (6, item.author);
+ statement.bind_text (7, item.album);
+ statement.bind_text (8, item.date);
+ statement.bind_int (9, item.bitrate);
+ statement.bind_int (10, item.sample_freq);
+ statement.bind_int (11, item.bits_per_sample);
+ statement.bind_int (12, item.n_audio_channels);
+ statement.bind_int (13, item.track_number);
+ statement.bind_int (14, item.color_depth);
+ statement.bind_int64 (15, item.duration);
rc = statement.step ();
if (rc == Sqlite.DONE || rc == Sqlite.OK) {
@@ -199,8 +228,7 @@ public class Rygel.MediaDB : Object {
}
}
- private int64 create_object (MediaItem item, int64 metadata_id) throws
- Error {
+ private void save_object (MediaObject item, int64 metadata_id) throws Error {
Statement statement;
var rc = db.prepare_v2 (OBJECT_INSERT_STRING,
@@ -209,12 +237,29 @@ public class Rygel.MediaDB : Object {
null);
if (rc == Sqlite.OK) {
statement.bind_text (1, item.id);
- statement.bind_int (2, MediaDBObjectType.ITEM);
- statement.bind_int64 (3, metadata_id);
- rc = statement.step ();
- if (rc == Sqlite.OK || rc == Sqlite.DONE) {
- return db.last_insert_rowid ();
+ statement.bind_text (2, item.title);
+
+ if (item is MediaItem) {
+ statement.bind_int (3, MediaDBObjectType.ITEM);
+ } else if (item is MediaObject) {
+ statement.bind_int (3, MediaDBObjectType.CONTAINER);
+ } else {
+ throw new MediaDBError.GENERAL_ERROR ("Invalid object type");
+ }
+
+ if (metadata_id == -1) {
+ statement.bind_null (4);
+ } else {
+ statement.bind_int64 (4, metadata_id);
+ }
+
+ if (item.parent == null || item.parent.id == "0") {
+ statement.bind_null (5);
} else {
+ statement.bind_text (5, item.parent.id);
+ }
+ rc = statement.step ();
+ if (rc != Sqlite.OK && rc != Sqlite.DONE) {
throw new MediaDBError.SQLITE_ERROR (db.errmsg ());
}
} else {
@@ -222,7 +267,7 @@ public class Rygel.MediaDB : Object {
}
}
- private void save_uris (MediaItem item, int64 object_id) throws Error {
+ private void save_uris (MediaItem item) throws Error {
Statement statement;
var rc = db.prepare_v2 (URI_INSERT_STRING,
@@ -231,7 +276,7 @@ public class Rygel.MediaDB : Object {
null);
if (rc == Sqlite.OK) {
foreach (var uri in item.uris) {
- statement.bind_int64 (1, object_id);
+ statement.bind_text (1, item.id);
statement.bind_text (2, uri);
rc = statement.step ();
if (rc != Sqlite.OK && rc != Sqlite.DONE) {
@@ -268,18 +313,32 @@ public class Rygel.MediaDB : Object {
}
public MediaItem? get_item (string item_id) {
+ var item = new MediaItem ("", null, "", "");
+ try {
+ fill_object (item_id, ref item);
+ } catch (Error error) {
+ item = null;
+ }
+
+ return item;
+ }
+
+ private void fill_container (string object_id, ref MediaContainer container) {
+
+ }
+
+ private void fill_item (string object_id, ref MediaItem item) {
Statement statement;
- var rc = db.prepare_v2 ("SELECT size, mime_type, width, height, class, title, author, album, date, bitrate, sample_freq, bits_per_sample, channels, track, color_depth, duration from Meta_Data join Object on Object.metadata_fk = Meta_Data.id WHERE Object.upnp_id = ?",
+ var rc = db.prepare_v2 (ITEM_GET_STRING,
-1,
out statement,
null);
if (rc == Sqlite.OK) {
- debug ("Trying to find item with id %s", item_id);
- statement.bind_text (1, item_id);
+ statement.bind_text (1, object_id);
while ((rc = statement.step ()) == Sqlite.ROW) {
- string title = statement.column_text (5);
- string upnp_class = statement.column_text (4);
- var item = new MediaItem (item_id, null, title, upnp_class);
+ item.id = object_id;
+ item.title = statement.column_text (5);
+ item.upnp_class = statement.column_text (4);
item.author = statement.column_text (6);
item.album = statement.column_text (7);
@@ -298,11 +357,20 @@ public class Rygel.MediaDB : Object {
item.width = statement.column_int (2);
item.height = statement.column_int (3);
item.color_depth = statement.column_int (14);
-
- return item;
+ break;
}
}
+ }
- return null;
+ public void fill_object (string object_id, ref MediaObject obj) throws Error {
+ if (obj is MediaItem) {
+ MediaItem item = (MediaItem) obj;
+ fill_item (object_id, ref item);
+ } else if (obj is MediaContainer) {
+ MediaContainer container = (MediaContainer) obj;
+ fill_container (object_id, ref container);
+ } else {
+ throw new MediaDBError.GENERAL_ERROR ("Invalid object type");
+ }
}
-}
+
[
Date Prev][
Date Next] [
Thread Prev][
Thread Next]
[
Thread Index]
[
Date Index]
[
Author Index]