4 * Copyright (c) 2000 - 2011 Samsung Electronics Co., Ltd. All rights reserved.
6 * Contact: Hyunjun Ko <zzoon.ko@samsung.com>, Haejeong Kim <backto.kim@samsung.com>
8 * Licensed under the Apache License, Version 2.0 (the "License");
9 * you may not use this file except in compliance with the License.
10 * You may obtain a copy of the License at
12 * http://www.apache.org/licenses/LICENSE-2.0
14 * Unless required by applicable law or agreed to in writing, software
15 * distributed under the License is distributed on an "AS IS" BASIS,
16 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17 * See the License for the specific language governing permissions and
18 * limitations under the License.
25 #include <media-util.h>
27 #include "media-svc-env.h"
28 #include "media-svc-debug.h"
29 #include "media-svc-util.h"
30 #include "media-svc-db-utils.h"
31 #include "media-util-err.h"
32 #include "media-util-db.h"
33 #include "media-svc-media.h"
35 static int __media_svc_create_update_media_table(sqlite3 *db_handle);
37 #define MEDIA_DB_SCHEMA "CREATE TABLE IF NOT EXISTS %s (\
38 media_uuid TEXT PRIMARY KEY, \
39 path TEXT NOT NULL UNIQUE, \
40 file_name TEXT NOT NULL, \
43 size INTEGER DEFAULT 0, \
44 added_time INTEGER DEFAULT 0,\
45 modified_time INTEGER DEFAULT 0, \
46 folder_uuid TEXT NOT NULL, \
47 thumbnail_path TEXT, \
49 album_id INTEGER DEFAULT 0, \
60 bitrate INTEGER DEFAULT -1, \
61 bitpersample INTEGER DEFAULT 0, \
62 samplerate INTEGER DEFAULT -1, \
63 channel INTEGER DEFAULT -1, \
64 duration INTEGER DEFAULT -1, \
65 longitude DOUBLE DEFAULT 0, \
66 latitude DOUBLE DEFAULT 0, \
67 altitude DOUBLE DEFAULT 0, \
69 fnumber DOUBLE DEFAULT 0, \
70 iso INTEGER DEFAULT -1, \
72 width INTEGER DEFAULT -1, \
73 height INTEGER DEFAULT -1, \
75 orientation INTEGER DEFAULT -1, \
77 played_count INTEGER DEFAULT 0, \
78 last_played_time INTEGER DEFAULT 0, \
79 last_played_position INTEGER DEFAULT 0, \
80 rating INTEGER DEFAULT 0, \
81 favourite INTEGER DEFAULT 0, \
89 is_drm INTEGER DEFAULT 0, \
90 storage_type INTEGER, \
91 timeline INTEGER DEFAULT 0, \
93 sync_status INTEGER DEFAULT 0, \
94 file_name_pinyin TEXT, \
98 album_artist_pinyin TEXT, \
100 composer_pinyin TEXT, \
101 copyright_pinyin TEXT, \
102 description_pinyin TEXT, \
103 author_pinyin TEXT, \
104 provider_pinyin TEXT, \
105 content_name_pinyin TEXT, \
106 category_pinyin TEXT, \
107 location_tag_pinyin TEXT, \
108 age_rating_pinyin TEXT, \
109 keyword_pinyin TEXT, \
110 validity INTEGER DEFAULT 1, \
111 unique(path, file_name) \
114 static int __media_svc_busy_handler(void *pData, int count);
116 static int __media_svc_busy_handler(void *pData, int count)
120 media_svc_debug("media_svc_busy_handler called : %d", count);
125 int _media_svc_connect_db_with_handle(sqlite3 **db_handle)
127 int ret = MS_MEDIA_ERR_NONE;
129 media_svc_debug_func();
132 ret = db_util_open(MEDIA_SVC_DB_NAME, db_handle, DB_UTIL_REGISTER_HOOK_METHOD);
134 if (SQLITE_OK != ret) {
136 media_svc_error("error when db open");
138 return MS_MEDIA_ERR_DB_INTERNAL;
141 /*Register busy handler*/
143 ret = sqlite3_busy_handler(*db_handle, __media_svc_busy_handler, NULL);
145 if (SQLITE_OK != ret) {
148 media_svc_error("[error when register busy handler] %s\n", sqlite3_errmsg(*db_handle));
151 db_util_close(*db_handle);
154 return MS_MEDIA_ERR_DB_INTERNAL;
158 return MS_MEDIA_ERR_DB_INTERNAL;
161 return MS_MEDIA_ERR_NONE;
164 int _media_svc_disconnect_db_with_handle(sqlite3 *db_handle)
166 int ret = MS_MEDIA_ERR_NONE;
168 media_svc_debug_func();
170 ret = db_util_close(db_handle);
172 if (SQLITE_OK != ret) {
173 media_svc_error("Error when db close : %s", sqlite3_errmsg(db_handle));
175 return MS_MEDIA_ERR_DB_INTERNAL;
178 return MS_MEDIA_ERR_NONE;
181 int _media_svc_create_media_table(sqlite3 *db_handle, uid_t uid)
183 int ret = MS_MEDIA_ERR_NONE;
186 media_svc_debug_func();
188 sql = sqlite3_mprintf(MEDIA_DB_SCHEMA, MEDIA_SVC_DB_TABLE_MEDIA);
190 media_svc_retv_if(sql == NULL, MS_MEDIA_ERR_OUT_OF_MEMORY);
192 ret = _media_svc_sql_query(db_handle, sql, uid);
194 if (ret != SQLITE_OK) {
195 media_svc_error("It failed to create db table (%d)", ret);
196 return MS_MEDIA_ERR_DB_INTERNAL;
200 sql = sqlite3_mprintf(" CREATE INDEX IF NOT EXISTS media_media_type_idx on %s (media_type); \
201 CREATE INDEX IF NOT EXISTS media_title_idx on %s (title); \
202 CREATE INDEX IF NOT EXISTS media_modified_time_idx on %s (modified_time); \
203 CREATE INDEX IF NOT EXISTS media_provider_idx on %s (provider); \
204 CREATE INDEX IF NOT EXISTS folder_uuid_idx on %s (folder_uuid); \
205 CREATE INDEX IF NOT EXISTS media_album_idx on %s (album); \
206 CREATE INDEX IF NOT EXISTS media_artist_idx on %s (artist); \
207 CREATE INDEX IF NOT EXISTS media_author_idx on %s (author); \
208 CREATE INDEX IF NOT EXISTS media_category_idx on %s (category); \
209 CREATE INDEX IF NOT EXISTS media_composer_idx on %s (composer); \
210 CREATE INDEX IF NOT EXISTS media_content_name_idx on %s (content_name); \
211 CREATE INDEX IF NOT EXISTS media_file_name_idx on %s (file_name); \
212 CREATE INDEX IF NOT EXISTS media_genre_idx on %s (genre); \
213 CREATE INDEX IF NOT EXISTS media_location_tag_idx on %s (location_tag); \
214 CREATE INDEX IF NOT EXISTS media_media_uuid_idx on %s (media_uuid); \
215 CREATE INDEX IF NOT EXISTS media_timeline_idx on %s (timeline); \
216 CREATE INDEX IF NOT EXISTS media_path_idx on %s (path); \
218 MEDIA_SVC_DB_TABLE_MEDIA,
219 MEDIA_SVC_DB_TABLE_MEDIA,
220 MEDIA_SVC_DB_TABLE_MEDIA,
221 MEDIA_SVC_DB_TABLE_MEDIA,
222 MEDIA_SVC_DB_TABLE_MEDIA,
223 MEDIA_SVC_DB_TABLE_MEDIA,
224 MEDIA_SVC_DB_TABLE_MEDIA,
225 MEDIA_SVC_DB_TABLE_MEDIA,
226 MEDIA_SVC_DB_TABLE_MEDIA,
227 MEDIA_SVC_DB_TABLE_MEDIA,
228 MEDIA_SVC_DB_TABLE_MEDIA,
229 MEDIA_SVC_DB_TABLE_MEDIA,
230 MEDIA_SVC_DB_TABLE_MEDIA,
231 MEDIA_SVC_DB_TABLE_MEDIA,
232 MEDIA_SVC_DB_TABLE_MEDIA,
233 MEDIA_SVC_DB_TABLE_MEDIA,
234 MEDIA_SVC_DB_TABLE_MEDIA);
236 media_svc_retv_if(sql == NULL, MS_MEDIA_ERR_OUT_OF_MEMORY);
238 ret = _media_svc_sql_query(db_handle, sql, uid);
240 if (ret != SQLITE_OK) {
241 media_svc_error("It failed to create db table (%d)", ret);
242 return MS_MEDIA_ERR_DB_INTERNAL;
245 return MS_MEDIA_ERR_NONE;
248 int _media_svc_create_folder_table(sqlite3 *db_handle, uid_t uid)
250 int ret = MS_MEDIA_ERR_NONE;
253 media_svc_debug_func();
255 sql = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %s (\
256 folder_uuid TEXT PRIMARY KEY, \
257 path TEXT NOT NULL UNIQUE, \
258 name TEXT NOT NULL, \
259 modified_time INTEGER DEFAULT 0, \
261 storage_type INTEGER, \
262 unique(path, name, storage_type) \
264 MEDIA_SVC_DB_TABLE_FOLDER);
266 media_svc_retv_if(sql == NULL, MS_MEDIA_ERR_OUT_OF_MEMORY);
268 ret = _media_svc_sql_query(db_handle, sql, uid);
270 if (ret != SQLITE_OK) {
271 media_svc_error("It failed to create db table (%d)", ret);
272 return MS_MEDIA_ERR_DB_INTERNAL;
275 /* Create Trigger to remove folder which have no content from folder when media remove from media_table*/
276 sql = sqlite3_mprintf("CREATE TRIGGER IF NOT EXISTS folder_cleanup \
277 DELETE ON %s BEGIN DELETE FROM %s \
278 WHERE (SELECT count(*) FROM %s WHERE folder_uuid=old.folder_uuid)=1 AND folder_uuid=old.folder_uuid;END;",
279 MEDIA_SVC_DB_TABLE_MEDIA, MEDIA_SVC_DB_TABLE_FOLDER, MEDIA_SVC_DB_TABLE_MEDIA);
281 media_svc_retv_if(sql == NULL, MS_MEDIA_ERR_OUT_OF_MEMORY);
283 ret = _media_svc_sql_query(db_handle, sql, uid);
285 if (ret != SQLITE_OK) {
286 media_svc_error("It failed to create trigger (%d)", ret);
287 return MS_MEDIA_ERR_DB_INTERNAL;
291 sql = sqlite3_mprintf(" CREATE INDEX IF NOT EXISTS folder_folder_uuid_idx on %s (folder_uuid); \
293 MEDIA_SVC_DB_TABLE_FOLDER);
295 media_svc_retv_if(sql == NULL, MS_MEDIA_ERR_OUT_OF_MEMORY);
297 ret = _media_svc_sql_query(db_handle, sql, uid);
299 media_svc_retv_if(ret != MS_MEDIA_ERR_NONE, ret);
300 return MS_MEDIA_ERR_NONE;
303 int _media_svc_create_playlist_table(sqlite3 *db_handle, uid_t uid)
305 int ret = MS_MEDIA_ERR_NONE;
308 media_svc_debug_func();
310 /*Create playlist table*/
311 sql = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %s (\
312 playlist_id INTEGER PRIMARY KEY AUTOINCREMENT, \
313 name TEXT NOT NULL UNIQUE,\
317 MEDIA_SVC_DB_TABLE_PLAYLIST);
319 media_svc_retv_if(sql == NULL, MS_MEDIA_ERR_OUT_OF_MEMORY);
321 ret = _media_svc_sql_query(db_handle, sql, uid);
323 if (ret != SQLITE_OK) {
324 media_svc_error("It failed to create db table (%d)", ret);
325 return MS_MEDIA_ERR_DB_INTERNAL;
328 /*Create playlist_map table*/
329 sql = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %s (\
330 _id INTEGER PRIMARY KEY AUTOINCREMENT, \
331 playlist_id INTEGER NOT NULL,\
332 media_uuid TEXT NOT NULL,\
333 play_order INTEGER NOT NULL\
335 MEDIA_SVC_DB_TABLE_PLAYLIST_MAP);
337 media_svc_retv_if(sql == NULL, MS_MEDIA_ERR_OUT_OF_MEMORY);
339 ret = _media_svc_sql_query(db_handle, sql, uid);
341 if (ret != SQLITE_OK) {
342 media_svc_error("It failed to create db table (%d)", ret);
343 return MS_MEDIA_ERR_DB_INTERNAL;
346 /* Create playlist_view*/
347 sql = sqlite3_mprintf(" \
348 CREATE VIEW IF NOT EXISTS playlist_view AS \
349 SELECT p.playlist_id, p.name, p.thumbnail_path AS p_thumbnail_path, media_count, pm._id as pm_id, pm.play_order, m.media_uuid, path, file_name, media_type, mime_type, size, added_time, modified_time, m.thumbnail_path, description, rating, favourite, author, provider, content_name, category, location_tag, age_rating, keyword, is_drm, storage_type, longitude, latitude, altitude, exposure_time, fnumber, iso, model, width, height, datetaken, orientation, title, album, artist, album_artist, genre, composer, year, recorded_date, copyright, track_num, bitrate, duration, played_count, last_played_time, last_played_position, samplerate, channel, weather, burst_id, timeline, sync_status, bitpersample FROM playlist AS p \
350 INNER JOIN playlist_map AS pm \
351 INNER JOIN media AS m \
352 INNER JOIN (SELECT count(playlist_id) as media_count, playlist_id FROM playlist_map group by playlist_id) as cnt_tbl \
353 ON (p.playlist_id=pm.playlist_id AND pm.media_uuid = m.media_uuid AND cnt_tbl.playlist_id=pm.playlist_id AND m.validity=1) \
355 SELECT playlist_id, name, thumbnail_path, 0, 0, -1, NULL, NULL, -1, -1, -1, -1, -1, NULL, NULL, -1, -1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, -1, -1, 0, -1, -1, -1, NULL, 0, -1, NULL, -1, -1, -1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, -1, -1, -1, -1, -1, -1, NULL, -1, NULL, NULL, -1, 0, 0 FROM playlist \
356 WHERE playlist_id NOT IN (select playlist_id from playlist_map) \
358 SELECT playlist_id, name, thumbnail_path, 0, 0, -1, NULL, NULL, -1, -1, -1, -1, -1, NULL, NULL, -1, -1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, -1, -1, 0, -1, -1, -1, NULL, 0, -1, NULL, -1, -1, -1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, -1, -1, -1, -1, -1, -1, NULL, -1, NULL, NULL, -1, 0, 0 FROM playlist \
359 WHERE playlist_id IN (select pm.playlist_id from playlist_map AS pm INNER JOIN media AS m ON (pm.media_uuid= m.media_uuid) AND m.validity=0); \
362 media_svc_retv_if(sql == NULL, MS_MEDIA_ERR_OUT_OF_MEMORY);
364 ret = _media_svc_sql_query(db_handle, sql, uid);
366 media_svc_retv_if(ret != MS_MEDIA_ERR_NONE, ret);
368 /* Create Trigger to remove media from playlist_map when media remove from media_table*/
369 sql = sqlite3_mprintf("CREATE TRIGGER IF NOT EXISTS playlist_map_cleanup \
370 DELETE ON %s BEGIN DELETE FROM %s WHERE media_uuid=old.media_uuid;END;",
371 MEDIA_SVC_DB_TABLE_MEDIA, MEDIA_SVC_DB_TABLE_PLAYLIST_MAP);
373 media_svc_retv_if(sql == NULL, MS_MEDIA_ERR_OUT_OF_MEMORY);
375 ret = _media_svc_sql_query(db_handle, sql, uid);
377 if (ret != SQLITE_OK) {
378 media_svc_error("It failed to create trigger (%d)", ret);
379 return MS_MEDIA_ERR_DB_INTERNAL;
382 /* Create Trigger to remove media from playlist_map when playlist removed from playlist table*/
383 sql = sqlite3_mprintf("CREATE TRIGGER IF NOT EXISTS playlist_map_cleanup_1 \
384 DELETE ON %s BEGIN DELETE FROM %s WHERE playlist_id=old.playlist_id;END;",
385 MEDIA_SVC_DB_TABLE_PLAYLIST, MEDIA_SVC_DB_TABLE_PLAYLIST_MAP);
387 media_svc_retv_if(sql == NULL, MS_MEDIA_ERR_OUT_OF_MEMORY);
389 ret = _media_svc_sql_query(db_handle, sql, uid);
391 if (ret != SQLITE_OK) {
392 media_svc_error("It failed to create trigger (%d)", ret);
393 return MS_MEDIA_ERR_DB_INTERNAL;
396 return MS_MEDIA_ERR_NONE;
399 int _media_svc_create_album_table(sqlite3 *db_handle, uid_t uid)
401 int ret = MS_MEDIA_ERR_NONE;
404 media_svc_debug_func();
406 sql = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %s (\
407 album_id INTEGER PRIMARY KEY AUTOINCREMENT, \
411 unique(name, artist) \
413 MEDIA_SVC_DB_TABLE_ALBUM);
415 media_svc_retv_if(sql == NULL, MS_MEDIA_ERR_OUT_OF_MEMORY);
417 ret = _media_svc_sql_query(db_handle, sql, uid);
419 if (ret != SQLITE_OK) {
420 media_svc_error("It failed to create db table (%d)", ret);
421 return MS_MEDIA_ERR_DB_INTERNAL;
424 /* Create Trigger to remove album when media remove from media_table*/
425 sql = sqlite3_mprintf("CREATE TRIGGER IF NOT EXISTS album_cleanup \
426 DELETE ON %s BEGIN DELETE FROM %s \
427 WHERE (SELECT count(*) FROM %s WHERE album_id=old.album_id)=1 AND album_id=old.album_id;END;",
428 MEDIA_SVC_DB_TABLE_MEDIA, MEDIA_SVC_DB_TABLE_ALBUM, MEDIA_SVC_DB_TABLE_MEDIA);
430 media_svc_retv_if(sql == NULL, MS_MEDIA_ERR_OUT_OF_MEMORY);
432 ret = _media_svc_sql_query(db_handle, sql, uid);
434 if (ret != SQLITE_OK) {
435 media_svc_error("It failed to create trigger (%d)", ret);
436 return MS_MEDIA_ERR_DB_INTERNAL;
439 return MS_MEDIA_ERR_NONE;
442 int _media_svc_create_tag_table(sqlite3 *db_handle, uid_t uid)
444 int ret = MS_MEDIA_ERR_NONE;
447 media_svc_debug_func();
450 sql = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %s (\
451 tag_id INTEGER PRIMARY KEY AUTOINCREMENT, \
452 name TEXT NOT NULL UNIQUE, \
455 MEDIA_SVC_DB_TABLE_TAG);
457 media_svc_retv_if(sql == NULL, MS_MEDIA_ERR_OUT_OF_MEMORY);
459 ret = _media_svc_sql_query(db_handle, sql, uid);
461 if (ret != SQLITE_OK) {
462 media_svc_error("It failed to create db table (%d)", ret);
463 return MS_MEDIA_ERR_DB_INTERNAL;
466 /*Create tag_map table*/
467 sql = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %s (\
468 _id INTEGER PRIMARY KEY AUTOINCREMENT, \
469 tag_id INTEGER NOT NULL,\
470 media_uuid TEXT NOT NULL,\
471 unique(tag_id, media_uuid) \
473 MEDIA_SVC_DB_TABLE_TAG_MAP);
475 media_svc_retv_if(sql == NULL, MS_MEDIA_ERR_OUT_OF_MEMORY);
477 ret = _media_svc_sql_query(db_handle, sql, uid);
479 if (ret != SQLITE_OK) {
480 media_svc_error("It failed to create db table (%d)", ret);
481 return MS_MEDIA_ERR_DB_INTERNAL;
485 sql = sqlite3_mprintf("\
486 CREATE VIEW IF NOT EXISTS tag_view AS \
488 t.tag_id, t.name, media_count, tm._id as tm_id, m.media_uuid, path, file_name, media_type, mime_type, size, added_time, modified_time, thumbnail_path, description, rating, favourite, author, provider, content_name, category, location_tag, age_rating, keyword, is_drm, storage_type, longitude, latitude, altitude, model, width, height, datetaken, orientation, title, album, artist, album_artist, genre, composer, year, recorded_date, copyright, track_num, bitrate, duration, played_count, last_played_time, last_played_position, samplerate, channel, weather, timeline, sync_status, bitpersample FROM tag AS t \
489 INNER JOIN tag_map AS tm \
490 INNER JOIN media AS m \
491 INNER JOIN (SELECT count(tag_id) as media_count, tag_id FROM tag_map group by tag_id) as cnt_tbl \
492 ON (t.tag_id=tm.tag_id AND tm.media_uuid = m.media_uuid AND cnt_tbl.tag_id=tm.tag_id AND m.validity=1) \
495 tag_id, name, 0, 0, NULL, NULL, -1, -1, -1, -1, -1, NULL, NULL, -1, -1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, -1, -1, 0, -1, -1, -1, -1, -1, -1, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, -1, -1, -1, -1, -1, -1, NULL, -1, NULL, -1, 0, 0 FROM tag \
497 NOT IN (select tag_id from tag_map); \
500 media_svc_retv_if(sql == NULL, MS_MEDIA_ERR_OUT_OF_MEMORY);
502 ret = _media_svc_sql_query(db_handle, sql, uid);
504 media_svc_retv_if(ret != MS_MEDIA_ERR_NONE, ret);
506 /* Create Trigger to remove media from tag_map when media remove from media_table*/
507 sql = sqlite3_mprintf("CREATE TRIGGER IF NOT EXISTS tag_map_cleanup \
508 DELETE ON %s BEGIN DELETE FROM %s WHERE media_uuid=old.media_uuid;END;",
509 MEDIA_SVC_DB_TABLE_MEDIA, MEDIA_SVC_DB_TABLE_TAG_MAP);
511 media_svc_retv_if(sql == NULL, MS_MEDIA_ERR_OUT_OF_MEMORY);
513 ret = _media_svc_sql_query(db_handle, sql, uid);
515 if (ret != SQLITE_OK) {
516 media_svc_error("It failed to create trigger (%d)", ret);
517 return MS_MEDIA_ERR_DB_INTERNAL;
520 /* Create Trigger to remove media from tag_map when tag removed from tag table*/
521 sql = sqlite3_mprintf("CREATE TRIGGER IF NOT EXISTS tag_map_cleanup_1 \
522 DELETE ON %s BEGIN DELETE FROM %s WHERE tag_id=old.tag_id;END;",
523 MEDIA_SVC_DB_TABLE_TAG, MEDIA_SVC_DB_TABLE_TAG_MAP);
525 media_svc_retv_if(sql == NULL, MS_MEDIA_ERR_OUT_OF_MEMORY);
527 ret = _media_svc_sql_query(db_handle, sql, uid);
529 if (ret != SQLITE_OK) {
530 media_svc_error("It failed to create trigger (%d)", ret);
531 return MS_MEDIA_ERR_DB_INTERNAL;
533 return MS_MEDIA_ERR_NONE;
536 int _media_svc_create_bookmark_table(sqlite3 *db_handle, uid_t uid)
538 int ret = MS_MEDIA_ERR_NONE;
541 media_svc_debug_func();
543 sql = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %s (\
544 bookmark_id INTEGER PRIMARY KEY AUTOINCREMENT, \
545 media_uuid TEXT NOT NULL,\
546 marked_time INTEGER DEFAULT 0, \
547 thumbnail_path TEXT, \
548 unique(media_uuid, marked_time) \
550 MEDIA_SVC_DB_TABLE_BOOKMARK);
552 media_svc_retv_if(sql == NULL, MS_MEDIA_ERR_OUT_OF_MEMORY);
554 ret = _media_svc_sql_query(db_handle, sql, uid);
556 if (ret != SQLITE_OK) {
557 media_svc_error("It failed to create db table (%d)", ret);
558 return MS_MEDIA_ERR_DB_INTERNAL;
561 /* Create Trigger to remove media from tag_map when media remove from media_table*/
562 sql = sqlite3_mprintf("CREATE TRIGGER IF NOT EXISTS bookmark_cleanup \
563 DELETE ON %s BEGIN DELETE FROM %s WHERE media_uuid=old.media_uuid;END;",
564 MEDIA_SVC_DB_TABLE_MEDIA, MEDIA_SVC_DB_TABLE_BOOKMARK);
566 media_svc_retv_if(sql == NULL, MS_MEDIA_ERR_OUT_OF_MEMORY);
568 ret = _media_svc_sql_query(db_handle, sql, uid);
570 if (ret != SQLITE_OK) {
571 media_svc_error("It failed to create trigger (%d)", ret);
572 return MS_MEDIA_ERR_DB_INTERNAL;
575 return MS_MEDIA_ERR_NONE;
578 int _media_svc_create_custom_table(sqlite3 *db_handle, uid_t uid)
580 int ret = MS_MEDIA_ERR_NONE;
583 media_svc_debug_func();
585 sql = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %s (\
586 _id INTEGER PRIMARY KEY AUTOINCREMENT, \
596 MEDIA_SVC_DB_TABLE_CUSTOM);
598 media_svc_retv_if(sql == NULL, MS_MEDIA_ERR_OUT_OF_MEMORY);
600 ret = _media_svc_sql_query(db_handle, sql, uid);
602 if (ret != SQLITE_OK) {
603 media_svc_error("It failed to create db table (%d)", ret);
604 return MS_MEDIA_ERR_DB_INTERNAL;
607 /* Create Trigger to remove media from tag_map when media remove from media_table*/
608 sql = sqlite3_mprintf("CREATE TRIGGER IF NOT EXISTS custom_cleanup \
609 DELETE ON %s BEGIN DELETE FROM %s WHERE media_uuid=old.media_uuid;END;",
610 MEDIA_SVC_DB_TABLE_MEDIA, MEDIA_SVC_DB_TABLE_CUSTOM);
612 media_svc_retv_if(sql == NULL, MS_MEDIA_ERR_OUT_OF_MEMORY);
614 ret = _media_svc_sql_query(db_handle, sql, uid);
616 if (ret != SQLITE_OK) {
617 media_svc_error("It failed to create trigger (%d)", ret);
618 return MS_MEDIA_ERR_DB_INTERNAL;
622 sql = sqlite3_mprintf("CREATE INDEX IF NOT EXISTS custom_provider_idx on %s (provider); \
624 MEDIA_SVC_DB_TABLE_CUSTOM);
626 media_svc_retv_if(sql == NULL, MS_MEDIA_ERR_OUT_OF_MEMORY);
628 ret = _media_svc_sql_query(db_handle, sql, uid);
630 if (ret != SQLITE_OK) {
631 media_svc_error("It failed to create db table (%d)", ret);
632 return MS_MEDIA_ERR_DB_INTERNAL;
635 return MS_MEDIA_ERR_NONE;
638 int _media_svc_request_update_db(const char *sql_str, uid_t uid)
640 int ret = MS_MEDIA_ERR_NONE;
642 ret = media_db_request_update_db(sql_str, uid);
647 int _media_svc_sql_query(sqlite3 *db_handle, const char *sql_str, uid_t uid)
649 int ret = MS_MEDIA_ERR_NONE;
651 media_svc_debug("[SQL query] : %s", sql_str);
653 //DB will be updated by Media Server.
654 ret = _media_svc_request_update_db(sql_str, uid);
659 int _media_svc_sql_prepare_to_step(sqlite3 *handle, const char *sql_str, sqlite3_stmt** stmt)
663 media_svc_debug("[SQL query] : %s", sql_str);
665 err = sqlite3_prepare_v2(handle, sql_str, -1, stmt, NULL);
666 sqlite3_free((char *)sql_str);
668 if (err != SQLITE_OK) {
669 media_svc_error ("prepare error %d[%s]", err, sqlite3_errmsg(handle));
670 return MS_MEDIA_ERR_DB_INTERNAL;
673 err = sqlite3_step(*stmt);
674 if (err != SQLITE_ROW) {
675 media_svc_error("Item not found. end of row [%s]", sqlite3_errmsg(handle));
676 SQLITE3_FINALIZE(*stmt);
677 return MS_MEDIA_ERR_DB_NO_RECORD;
680 return MS_MEDIA_ERR_NONE;
683 int _media_svc_sql_begin_trans(sqlite3 *handle, uid_t uid)
685 int ret = MS_MEDIA_ERR_NONE;
687 media_svc_error("========_media_svc_sql_begin_trans");
689 ret = media_db_request_update_db_batch_start("BEGIN IMMEDIATE;", uid);
694 int _media_svc_sql_end_trans(sqlite3 *handle, uid_t uid)
696 int ret = MS_MEDIA_ERR_NONE;
698 media_svc_error("========_media_svc_sql_end_trans");
700 ret = media_db_request_update_db_batch_end("COMMIT;", uid);
705 int _media_svc_sql_rollback_trans(sqlite3 *handle, uid_t uid)
707 media_svc_error("========_media_svc_sql_rollback_trans");
709 return _media_svc_request_update_db("ROLLBACK;", uid);
713 int _media_svc_sql_query_list(sqlite3 *handle, GList **query_list, uid_t uid)
715 int ret = MS_MEDIA_ERR_NONE;
717 int length = g_list_length(*query_list);
720 media_svc_debug("query list length : [%d]", length);
722 for (idx = 0; idx < length; idx++) {
723 sql = (char*)g_list_nth_data(*query_list, idx);
725 //ret = _media_svc_sql_query(handle, sql);
726 ret = media_db_request_update_db_batch(sql, uid);
727 if (ret != MS_MEDIA_ERR_NONE) {
728 media_svc_error("media_db_request_update_db_batch failed : %d", ret);
735 _media_svc_sql_query_release(query_list);
737 return MS_MEDIA_ERR_NONE;
741 void _media_svc_sql_query_add(GList **query_list, char **query)
743 *query_list = g_list_append( *query_list, *query);
746 void _media_svc_sql_query_release(GList **query_list)
749 media_svc_debug("_svc_sql_query_release");
750 g_list_free(*query_list);