2 * Copyright (C) 2010, 2011 Jens Georg <mail@jensge.org>.
3 * Copyright (C) 2013 Intel Corporation
5 * Author: Jens Georg <mail@jensge.org>
7 * This file is part of Rygel.
9 * Rygel is free software; you can redistribute it and/or modify
10 * it under the terms of the GNU Lesser General Public License as published by
11 * the Free Software Foundation; either version 2 of the License, or
12 * (at your option) any later version.
14 * Rygel is distributed in the hope that it will be useful,
15 * but WITHOUT ANY WARRANTY; without even the implied warranty of
16 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17 * GNU Lesser General Public License for more details.
19 * You should have received a copy of the GNU Lesser General Public License
20 * along with this program; if not, write to the Free Software Foundation,
21 * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
24 #include "rygel-media-export-sql-factory.h"
27 G_DEFINE_TYPE (RygelMediaExportSQLFactory, rygel_media_export_sql_factory, G_TYPE_OBJECT)
29 #define RYGEL_MEDIA_EXPORT_SQL_FACTORY_SAVE_META_DATA_STRING \
30 "INSERT OR REPLACE INTO meta_data " \
31 "(size, mime_type, width, height, class, " \
32 "author, album, date, bitrate, " \
33 "sample_freq, bits_per_sample, channels, " \
34 "track, color_depth, duration, object_fk, dlna_profile, genre, disc) VALUES " \
35 "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
37 #define RYGEL_MEDIA_EXPORT_SQL_FACTORY_INSERT_OBJECT_STRING \
38 "INSERT OR REPLACE INTO Object " \
39 "(upnp_id, title, type_fk, parent, timestamp, uri, " \
40 "object_update_id, deleted_child_count, container_update_id) " \
41 "VALUES (?,?,?,?,?,?,?,?,?)"
43 #define RYGEL_MEDIA_EXPORT_SQL_FACTORY_DELETE_BY_ID_STRING \
44 "DELETE FROM Object WHERE upnp_id IN " \
45 "(SELECT descendant FROM closure WHERE ancestor = ?)"
47 #define RYGEL_MEDIA_EXPORT_SQL_FACTORY_ALL_DETAILS_STRING \
48 "o.type_fk, o.title, m.size, m.mime_type, m.width, " \
49 "m.height, m.class, m.author, m.album, m.date, m.bitrate, " \
50 "m.sample_freq, m.bits_per_sample, m.channels, m.track, " \
51 "m.color_depth, m.duration, o.upnp_id, o.parent, o.timestamp, " \
52 "o.uri, m.dlna_profile, m.genre, m.disc, o.object_update_id, " \
53 "o.deleted_child_count, o.container_update_id "
55 #define RYGEL_MEDIA_EXPORT_SQL_FACTORY_GET_OBJECT_WITH_PATH \
56 "SELECT DISTINCT " RYGEL_MEDIA_EXPORT_SQL_FACTORY_ALL_DETAILS_STRING \
58 "JOIN Closure c ON (o.upnp_id = c.ancestor) " \
59 "LEFT OUTER JOIN meta_data m ON (o.upnp_id = m.object_fk) " \
60 "WHERE c.descendant = ? ORDER BY c.depth DESC"
63 * This is the database query used to retrieve the children for a
66 * Sorting is as follows:
67 * - by type: containers first, then items if both are present
68 * - by upnp_class: items are sorted according to their class
69 * - by track: sorted by track
70 * - and after that alphabetically
72 #define RYGEL_MEDIA_EXPORT_SQL_FACTORY_GET_CHILDREN_STRING \
73 "SELECT " RYGEL_MEDIA_EXPORT_SQL_FACTORY_ALL_DETAILS_STRING \
75 "JOIN Closure c ON (o.upnp_id = c.descendant) " \
76 "LEFT OUTER JOIN meta_data m " \
77 "ON c.descendant = m.object_fk " \
78 "WHERE c.ancestor = ? AND c.depth = 1 %s " \
81 #define RYGEL_MEDIA_EXPORT_SQL_FACTORY_GET_OBJECTS_BY_FILTER_STRING_WITH_ANCESTOR \
82 "SELECT DISTINCT " RYGEL_MEDIA_EXPORT_SQL_FACTORY_ALL_DETAILS_STRING \
84 "JOIN Closure c ON o.upnp_id = c.descendant AND c.ancestor = ? " \
85 "LEFT OUTER JOIN meta_data m " \
86 "ON o.upnp_id = m.object_fk %s %s " \
89 #define RYGEL_MEDIA_EXPORT_SQL_FACTORY_GET_OBJECTS_BY_FILTER_STRING \
90 "SELECT DISTINCT " RYGEL_MEDIA_EXPORT_SQL_FACTORY_ALL_DETAILS_STRING \
92 "LEFT OUTER JOIN meta_data m " \
93 "ON o.upnp_id = m.object_fk %s %s " \
96 #define RYGEL_MEDIA_EXPORT_SQL_FACTORY_GET_OBJECT_COUNT_BY_FILTER_STRING_WITH_ANCESTOR \
97 "SELECT COUNT(o.type_fk) FROM Object o " \
98 "JOIN Closure c ON o.upnp_id = c.descendant AND c.ancestor = ? " \
99 "LEFT OUTER JOIN meta_data m " \
100 "ON o.upnp_id = m.object_fk %s"
102 #define RYGEL_MEDIA_EXPORT_SQL_FACTORY_GET_OBJECT_COUNT_BY_FILTER_STRING \
103 "SELECT COUNT(1) FROM meta_data m %s"
105 #define RYGEL_MEDIA_EXPORT_SQL_FACTORY_CHILDREN_COUNT_STRING \
106 "SELECT COUNT(upnp_id) FROM Object WHERE Object.parent = ?"
108 #define RYGEL_MEDIA_EXPORT_SQL_FACTORY_OBJECT_EXISTS_STRING \
109 "SELECT COUNT(1), timestamp, m.size FROM Object " \
110 "JOIN meta_data m ON m.object_fk = upnp_id " \
111 "WHERE Object.uri = ?"
113 #define RYGEL_MEDIA_EXPORT_SQL_FACTORY_GET_CHILD_ID_STRING \
114 "SELECT upnp_id FROM OBJECT WHERE parent = ?"
116 #define RYGEL_MEDIA_EXPORT_SQL_FACTORY_GET_META_DATA_COLUMN_STRING \
117 "SELECT DISTINCT %s AS _column FROM meta_data AS m " \
118 "WHERE _column IS NOT NULL %s ORDER BY _column COLLATE CASEFOLD " \
121 #define RYGEL_MEDIA_EXPORT_SQL_FACTORY_CREATE_META_DATA_TABLE_STRING \
122 "CREATE TABLE meta_data (size INTEGER NOT NULL, " \
123 "mime_type TEXT NOT NULL, " \
124 "dlna_profile TEXT, " \
125 "duration INTEGER, " \
128 "class TEXT NOT NULL, " \
133 "bitrate INTEGER, " \
134 "sample_freq INTEGER, " \
135 "bits_per_sample INTEGER, " \
136 "channels INTEGER, " \
139 "color_depth INTEGER, " \
140 "object_fk TEXT UNIQUE CONSTRAINT " \
141 "object_fk_id REFERENCES Object(upnp_id) " \
142 "ON DELETE CASCADE); "
144 #define RYGEL_MEDIA_EXPORT_SQL_FACTORY_SCHEMA_STRING \
145 "CREATE TABLE schema_info (version TEXT NOT NULL, " \
146 "reset_token TEXT); " \
147 RYGEL_MEDIA_EXPORT_SQL_FACTORY_CREATE_META_DATA_TABLE_STRING \
148 "CREATE TABLE object (parent TEXT CONSTRAINT parent_fk_id " \
149 "REFERENCES Object(upnp_id), " \
150 "upnp_id TEXT PRIMARY KEY, " \
151 "type_fk INTEGER, " \
152 "title TEXT NOT NULL, " \
153 "timestamp INTEGER NOT NULL, " \
156 "object_update_id INTEGER, " \
157 "deleted_child_count INTEGER, " \
158 "container_update_id INTEGER); " \
159 "INSERT INTO schema_info (version) VALUES ('" \
160 RYGEL_MEDIA_EXPORT_SQL_FACTORY_SCHEMA_VERSION "'); "
162 #define RYGEL_MEDIA_EXPORT_SQL_FACTORY_CREATE_CLOSURE_TABLE \
163 "CREATE TABLE closure (ancestor TEXT, descendant TEXT, depth INTEGER)"
165 #define RYGEL_MEDIA_EXPORT_SQL_FACTORY_CREATE_CLOSURE_TRIGGER_STRING \
166 "CREATE TRIGGER trgr_update_closure " \
167 "AFTER INSERT ON Object " \
168 "FOR EACH ROW BEGIN " \
169 "SELECT RAISE(IGNORE) WHERE (SELECT COUNT(*) FROM Closure " \
170 "WHERE ancestor = NEW.upnp_id " \
171 "AND descendant = NEW.upnp_id " \
172 "AND depth = 0) != 0; " \
173 "INSERT INTO Closure (ancestor, descendant, depth) " \
174 "VALUES (NEW.upnp_id, NEW.upnp_id, 0); " \
175 "INSERT INTO Closure (ancestor, descendant, depth) " \
176 "SELECT ancestor, NEW.upnp_id, depth + 1 FROM Closure " \
177 "WHERE descendant = NEW.parent; " \
179 "CREATE TRIGGER trgr_delete_closure " \
180 "AFTER DELETE ON Object " \
181 "FOR EACH ROW BEGIN " \
182 "DELETE FROM Closure WHERE descendant = OLD.upnp_id; " \
185 // these triggers emulate ON DELETE CASCADE
186 #define RYGEL_MEDIA_EXPORT_SQL_FACTORY_CREATE_TRIGGER_STRING \
187 "CREATE TRIGGER trgr_delete_metadata " \
188 "BEFORE DELETE ON Object " \
189 "FOR EACH ROW BEGIN " \
190 "DELETE FROM meta_data WHERE meta_data.object_fk = OLD.upnp_id; " \
193 #define RYGEL_MEDIA_EXPORT_SQL_FACTORY_CREATE_INDICES_STRING \
194 "CREATE INDEX IF NOT EXISTS idx_parent on Object(parent); " \
195 "CREATE INDEX IF NOT EXISTS idx_object_upnp_id on Object(upnp_id); " \
196 "CREATE INDEX IF NOT EXISTS idx_meta_data_fk on meta_data(object_fk); " \
197 "CREATE INDEX IF NOT EXISTS idx_closure on Closure(descendant,depth); " \
198 "CREATE INDEX IF NOT EXISTS idx_closure_descendant on Closure(descendant); " \
199 "CREATE INDEX IF NOT EXISTS idx_closure_ancestor on Closure(ancestor); " \
200 "CREATE INDEX IF NOT EXISTS idx_uri on Object(uri); " \
201 "CREATE INDEX IF NOT EXISTS idx_meta_data_date on meta_data(date); " \
202 "CREATE INDEX IF NOT EXISTS idx_meta_data_genre on meta_data(genre); " \
203 "CREATE INDEX IF NOT EXISTS idx_meta_data_album on meta_data(album); " \
204 "CREATE INDEX IF NOT EXISTS idx_meta_data_artist_album on " \
205 "meta_data(author, album);"
207 #define RYGEL_MEDIA_EXPORT_SQL_FACTORY_EXISTS_CACHE_STRING \
208 "SELECT m.size, o.timestamp, o.uri FROM Object o " \
209 "JOIN meta_data m ON o.upnp_id = m.object_fk"
211 #define RYGEL_MEDIA_EXPORT_SQL_FACTORY_STATISTICS_STRING \
212 "SELECT class, count(1) FROM meta_data GROUP BY class"
214 #define RYGEL_MEDIA_EXPORT_SQL_FACTORY_RESET_TOKEN_STRING \
215 "SELECT reset_token FROM schema_info"
217 #define RYGEL_MEDIA_EXPORT_SQL_FACTORY_MAX_UPDATE_ID_STRING \
218 "SELECT MAX(MAX(object_update_id), MAX(container_update_id)) FROM Object"
221 rygel_media_export_sql_factory_make (RygelMediaExportSQLFactory *self, RygelMediaExportSQLString query) {
222 g_return_val_if_fail (self, NULL);
225 case RYGEL_MEDIA_EXPORT_SQL_STRING_SAVE_METADATA:
226 return RYGEL_MEDIA_EXPORT_SQL_FACTORY_SAVE_META_DATA_STRING;
227 case RYGEL_MEDIA_EXPORT_SQL_STRING_INSERT:
228 return RYGEL_MEDIA_EXPORT_SQL_FACTORY_INSERT_OBJECT_STRING;
229 case RYGEL_MEDIA_EXPORT_SQL_STRING_DELETE:
230 return RYGEL_MEDIA_EXPORT_SQL_FACTORY_DELETE_BY_ID_STRING;
231 case RYGEL_MEDIA_EXPORT_SQL_STRING_GET_OBJECT:
232 return RYGEL_MEDIA_EXPORT_SQL_FACTORY_GET_OBJECT_WITH_PATH;
233 case RYGEL_MEDIA_EXPORT_SQL_STRING_GET_CHILDREN:
234 return RYGEL_MEDIA_EXPORT_SQL_FACTORY_GET_CHILDREN_STRING;
235 case RYGEL_MEDIA_EXPORT_SQL_STRING_GET_OBJECTS_BY_FILTER:
236 return RYGEL_MEDIA_EXPORT_SQL_FACTORY_GET_OBJECTS_BY_FILTER_STRING;
237 case RYGEL_MEDIA_EXPORT_SQL_STRING_GET_OBJECTS_BY_FILTER_WITH_ANCESTOR:
238 return RYGEL_MEDIA_EXPORT_SQL_FACTORY_GET_OBJECTS_BY_FILTER_STRING_WITH_ANCESTOR;
239 case RYGEL_MEDIA_EXPORT_SQL_STRING_GET_OBJECT_COUNT_BY_FILTER:
240 return RYGEL_MEDIA_EXPORT_SQL_FACTORY_GET_OBJECT_COUNT_BY_FILTER_STRING;
241 case RYGEL_MEDIA_EXPORT_SQL_STRING_GET_OBJECT_COUNT_BY_FILTER_WITH_ANCESTOR:
242 return RYGEL_MEDIA_EXPORT_SQL_FACTORY_GET_OBJECT_COUNT_BY_FILTER_STRING_WITH_ANCESTOR;
243 case RYGEL_MEDIA_EXPORT_SQL_STRING_GET_META_DATA_COLUMN:
244 return RYGEL_MEDIA_EXPORT_SQL_FACTORY_GET_META_DATA_COLUMN_STRING;
245 case RYGEL_MEDIA_EXPORT_SQL_STRING_CHILD_COUNT:
246 return RYGEL_MEDIA_EXPORT_SQL_FACTORY_CHILDREN_COUNT_STRING;
247 case RYGEL_MEDIA_EXPORT_SQL_STRING_EXISTS:
248 return RYGEL_MEDIA_EXPORT_SQL_FACTORY_OBJECT_EXISTS_STRING;
249 case RYGEL_MEDIA_EXPORT_SQL_STRING_CHILD_IDS:
250 return RYGEL_MEDIA_EXPORT_SQL_FACTORY_GET_CHILD_ID_STRING;
251 case RYGEL_MEDIA_EXPORT_SQL_STRING_TABLE_METADATA:
252 return RYGEL_MEDIA_EXPORT_SQL_FACTORY_CREATE_META_DATA_TABLE_STRING;
253 case RYGEL_MEDIA_EXPORT_SQL_STRING_TRIGGER_COMMON:
254 return RYGEL_MEDIA_EXPORT_SQL_FACTORY_CREATE_TRIGGER_STRING;
255 case RYGEL_MEDIA_EXPORT_SQL_STRING_TRIGGER_CLOSURE:
256 return RYGEL_MEDIA_EXPORT_SQL_FACTORY_CREATE_CLOSURE_TRIGGER_STRING;
257 case RYGEL_MEDIA_EXPORT_SQL_STRING_INDEX_COMMON:
258 return RYGEL_MEDIA_EXPORT_SQL_FACTORY_CREATE_INDICES_STRING;
259 case RYGEL_MEDIA_EXPORT_SQL_STRING_SCHEMA:
260 return RYGEL_MEDIA_EXPORT_SQL_FACTORY_SCHEMA_STRING;
261 case RYGEL_MEDIA_EXPORT_SQL_STRING_EXISTS_CACHE:
262 return RYGEL_MEDIA_EXPORT_SQL_FACTORY_EXISTS_CACHE_STRING;
263 case RYGEL_MEDIA_EXPORT_SQL_STRING_TABLE_CLOSURE:
264 return RYGEL_MEDIA_EXPORT_SQL_FACTORY_CREATE_CLOSURE_TABLE;
265 case RYGEL_MEDIA_EXPORT_SQL_STRING_STATISTICS:
266 return RYGEL_MEDIA_EXPORT_SQL_FACTORY_STATISTICS_STRING;
267 case RYGEL_MEDIA_EXPORT_SQL_STRING_RESET_TOKEN:
268 return RYGEL_MEDIA_EXPORT_SQL_FACTORY_RESET_TOKEN_STRING;
269 case RYGEL_MEDIA_EXPORT_SQL_STRING_MAX_UPDATE_ID:
270 return RYGEL_MEDIA_EXPORT_SQL_FACTORY_MAX_UPDATE_ID_STRING;
272 g_assert_not_reached ();
276 RygelMediaExportSQLFactory *
277 rygel_media_export_sql_factory_new (void) {
278 return RYGEL_MEDIA_EXPORT_SQL_FACTORY (g_object_new (RYGEL_MEDIA_EXPORT_TYPE_SQL_FACTORY, NULL));
282 rygel_media_export_sql_factory_class_init (RygelMediaExportSQLFactoryClass *klass G_GNUC_UNUSED) {
286 rygel_media_export_sql_factory_init (RygelMediaExportSQLFactory *self G_GNUC_UNUSED) {