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.
24 #include <media-util.h>
25 #include "media-svc-env.h"
26 #include "media-svc-debug.h"
27 #include "media-svc-error.h"
28 #include "media-svc-util.h"
29 #include "media-svc-db-utils.h"
30 #include "media-util-db.h"
32 static int __media_svc_create_update_media_table(sqlite3 *db_handle);
34 #define MEDIA_DB_SCHEMA "CREATE TABLE IF NOT EXISTS %s (\
35 media_uuid TEXT PRIMARY KEY, \
36 path TEXT NOT NULL UNIQUE, \
37 file_name TEXT NOT NULL, \
40 size INTEGER DEFAULT 0, \
41 added_time INTEGER DEFAULT 0,\
42 modified_time INTEGER DEFAULT 0, \
43 folder_uuid TEXT NOT NULL, \
44 thumbnail_path TEXT, \
46 album_id INTEGER DEFAULT 0, \
57 bitrate INTEGER DEFAULT -1, \
58 bitpersample INTEGER DEFAULT 0, \
59 samplerate INTEGER DEFAULT -1, \
60 channel INTEGER DEFAULT -1, \
61 duration INTEGER DEFAULT -1, \
62 longitude DOUBLE DEFAULT 0, \
63 latitude DOUBLE DEFAULT 0, \
64 altitude DOUBLE DEFAULT 0, \
65 width INTEGER DEFAULT -1, \
66 height INTEGER DEFAULT -1, \
68 orientation INTEGER DEFAULT -1, \
70 played_count INTEGER DEFAULT 0, \
71 last_played_time INTEGER DEFAULT 0, \
72 last_played_position INTEGER DEFAULT 0, \
73 rating INTEGER DEFAULT 0, \
74 favourite INTEGER DEFAULT 0, \
82 is_drm INTEGER DEFAULT 0, \
83 storage_type INTEGER, \
84 timeline INTEGER DEFAULT 0, \
86 sync_status INTEGER DEFAULT 0, \
87 file_name_pinyin TEXT, \
91 album_artist_pinyin TEXT, \
93 composer_pinyin TEXT, \
94 copyright_pinyin TEXT, \
95 description_pinyin TEXT, \
97 provider_pinyin TEXT, \
98 content_name_pinyin TEXT, \
99 category_pinyin TEXT, \
100 location_tag_pinyin TEXT, \
101 age_rating_pinyin TEXT, \
102 keyword_pinyin TEXT, \
103 validity INTEGER DEFAULT 1, \
104 unique(path, file_name) \
107 static int __media_svc_busy_handler(void *pData, int count);
109 static int __media_svc_busy_handler(void *pData, int count)
113 media_svc_debug("media_svc_busy_handler called : %d", count);
118 int _media_svc_connect_db_with_handle(sqlite3 **db_handle)
120 int ret = MEDIA_INFO_ERROR_NONE;
122 media_svc_debug_func();
125 ret = db_util_open(MEDIA_SVC_DB_NAME, db_handle, DB_UTIL_REGISTER_HOOK_METHOD);
127 if (SQLITE_OK != ret) {
129 media_svc_error("error when db open");
131 return MEDIA_INFO_ERROR_DATABASE_CONNECT;
134 /*Register busy handler*/
136 ret = sqlite3_busy_handler(*db_handle, __media_svc_busy_handler, NULL);
138 if (SQLITE_OK != ret) {
141 media_svc_error("[error when register busy handler] %s\n", sqlite3_errmsg(*db_handle));
144 db_util_close(*db_handle);
147 return MEDIA_INFO_ERROR_DATABASE_CONNECT;
151 return MEDIA_INFO_ERROR_DATABASE_CONNECT;
154 return MEDIA_INFO_ERROR_NONE;
157 int _media_svc_disconnect_db_with_handle(sqlite3 *db_handle)
159 int ret = MEDIA_INFO_ERROR_NONE;
161 media_svc_debug_func();
163 ret = db_util_close(db_handle);
165 if (SQLITE_OK != ret) {
166 media_svc_error("Error when db close : %s", sqlite3_errmsg(db_handle));
168 return MEDIA_INFO_ERROR_DATABASE_DISCONNECT;
171 return MEDIA_INFO_ERROR_NONE;
174 int _media_svc_create_media_table(sqlite3 *db_handle, uid_t uid)
176 int ret = MEDIA_INFO_ERROR_NONE;
179 media_svc_debug_func();
181 sql = sqlite3_mprintf(MEDIA_DB_SCHEMA, MEDIA_SVC_DB_TABLE_MEDIA);
183 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
185 ret = _media_svc_sql_query(db_handle, sql, uid);
187 if (ret != SQLITE_OK) {
188 media_svc_error("It failed to create db table (%d)", ret);
189 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
193 sql = sqlite3_mprintf(" CREATE INDEX IF NOT EXISTS media_media_type_idx on %s (media_type); \
194 CREATE INDEX IF NOT EXISTS media_title_idx on %s (title); \
195 CREATE INDEX IF NOT EXISTS media_modified_time_idx on %s (modified_time); \
196 CREATE INDEX IF NOT EXISTS media_provider_idx on %s (provider); \
197 CREATE INDEX IF NOT EXISTS folder_uuid_idx on %s (folder_uuid); \
198 CREATE INDEX IF NOT EXISTS media_album_idx on %s (album); \
199 CREATE INDEX IF NOT EXISTS media_artist_idx on %s (artist); \
200 CREATE INDEX IF NOT EXISTS media_author_idx on %s (author); \
201 CREATE INDEX IF NOT EXISTS media_category_idx on %s (category); \
202 CREATE INDEX IF NOT EXISTS media_composer_idx on %s (composer); \
203 CREATE INDEX IF NOT EXISTS media_content_name_idx on %s (content_name); \
204 CREATE INDEX IF NOT EXISTS media_file_name_idx on %s (file_name); \
205 CREATE INDEX IF NOT EXISTS media_genre_idx on %s (genre); \
206 CREATE INDEX IF NOT EXISTS media_location_tag_idx on %s (location_tag); \
207 CREATE INDEX IF NOT EXISTS media_media_uuid_idx on %s (media_uuid); \
208 CREATE INDEX IF NOT EXISTS media_timeline_idx on %s (timeline); \
209 CREATE INDEX IF NOT EXISTS media_path_idx on %s (path); \
211 MEDIA_SVC_DB_TABLE_MEDIA,
212 MEDIA_SVC_DB_TABLE_MEDIA,
213 MEDIA_SVC_DB_TABLE_MEDIA,
214 MEDIA_SVC_DB_TABLE_MEDIA,
215 MEDIA_SVC_DB_TABLE_MEDIA,
216 MEDIA_SVC_DB_TABLE_MEDIA,
217 MEDIA_SVC_DB_TABLE_MEDIA,
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);
229 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
231 ret = _media_svc_sql_query(db_handle, sql, uid);
233 if (ret != SQLITE_OK) {
234 media_svc_error("It failed to create db table (%d)", ret);
235 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
238 return MEDIA_INFO_ERROR_NONE;
241 int _media_svc_create_folder_table(sqlite3 *db_handle, uid_t uid)
243 int ret = MEDIA_INFO_ERROR_NONE;
246 media_svc_debug_func();
248 sql = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %s (\
249 folder_uuid TEXT PRIMARY KEY, \
250 path TEXT NOT NULL UNIQUE, \
251 name TEXT NOT NULL, \
252 modified_time INTEGER DEFAULT 0, \
254 storage_type INTEGER, \
255 unique(path, name, storage_type) \
257 MEDIA_SVC_DB_TABLE_FOLDER);
259 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
261 ret = _media_svc_sql_query(db_handle, sql, uid);
263 if (ret != SQLITE_OK) {
264 media_svc_error("It failed to create db table (%d)", ret);
265 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
268 /* Create Trigger to remove folder which have no content from folder when media remove from media_table*/
269 sql = sqlite3_mprintf("CREATE TRIGGER IF NOT EXISTS folder_cleanup \
270 DELETE ON %s BEGIN DELETE FROM %s \
271 WHERE (SELECT count(*) FROM %s WHERE folder_uuid=old.folder_uuid)=1 AND folder_uuid=old.folder_uuid;END;",
272 MEDIA_SVC_DB_TABLE_MEDIA, MEDIA_SVC_DB_TABLE_FOLDER, MEDIA_SVC_DB_TABLE_MEDIA);
274 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
276 ret = _media_svc_sql_query(db_handle, sql, uid);
278 if (ret != SQLITE_OK) {
279 media_svc_error("It failed to create trigger (%d)", ret);
280 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
284 sql = sqlite3_mprintf(" CREATE INDEX IF NOT EXISTS folder_folder_uuid_idx on %s (folder_uuid); \
286 MEDIA_SVC_DB_TABLE_FOLDER);
288 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
290 ret = _media_svc_sql_query(db_handle, sql, uid);
292 media_svc_retv_if(ret != MEDIA_INFO_ERROR_NONE, ret);
293 return MEDIA_INFO_ERROR_NONE;
296 int _media_svc_create_playlist_table(sqlite3 *db_handle, uid_t uid)
298 int ret = MEDIA_INFO_ERROR_NONE;
301 media_svc_debug_func();
303 /*Create playlist table*/
304 sql = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %s (\
305 playlist_id INTEGER PRIMARY KEY AUTOINCREMENT, \
306 name TEXT NOT NULL UNIQUE,\
310 MEDIA_SVC_DB_TABLE_PLAYLIST);
312 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
314 ret = _media_svc_sql_query(db_handle, sql, uid);
316 if (ret != SQLITE_OK) {
317 media_svc_error("It failed to create db table (%d)", ret);
318 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
321 /*Create playlist_map table*/
322 sql = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %s (\
323 _id INTEGER PRIMARY KEY AUTOINCREMENT, \
324 playlist_id INTEGER NOT NULL,\
325 media_uuid TEXT NOT NULL,\
326 play_order INTEGER NOT NULL\
328 MEDIA_SVC_DB_TABLE_PLAYLIST_MAP);
330 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
332 ret = _media_svc_sql_query(db_handle, sql, uid);
334 if (ret != SQLITE_OK) {
335 media_svc_error("It failed to create db table (%d)", ret);
336 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
339 /* Create playlist_view*/
340 sql = sqlite3_mprintf(" \
341 CREATE VIEW IF NOT EXISTS playlist_view AS \
342 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, 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 \
343 INNER JOIN playlist_map AS pm \
344 INNER JOIN media AS m \
345 INNER JOIN (SELECT count(playlist_id) as media_count, playlist_id FROM playlist_map group by playlist_id) as cnt_tbl \
346 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) \
348 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, -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 \
349 WHERE playlist_id NOT IN (select playlist_id from playlist_map) \
351 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, -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 \
352 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); \
355 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
357 ret = _media_svc_sql_query(db_handle, sql, uid);
359 media_svc_retv_if(ret != MEDIA_INFO_ERROR_NONE, ret);
361 /* Create Trigger to remove media from playlist_map when media remove from media_table*/
362 sql = sqlite3_mprintf("CREATE TRIGGER IF NOT EXISTS playlist_map_cleanup \
363 DELETE ON %s BEGIN DELETE FROM %s WHERE media_uuid=old.media_uuid;END;",
364 MEDIA_SVC_DB_TABLE_MEDIA, MEDIA_SVC_DB_TABLE_PLAYLIST_MAP);
366 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
368 ret = _media_svc_sql_query(db_handle, sql, uid);
370 if (ret != SQLITE_OK) {
371 media_svc_error("It failed to create trigger (%d)", ret);
372 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
375 /* Create Trigger to remove media from playlist_map when playlist removed from playlist table*/
376 sql = sqlite3_mprintf("CREATE TRIGGER IF NOT EXISTS playlist_map_cleanup_1 \
377 DELETE ON %s BEGIN DELETE FROM %s WHERE playlist_id=old.playlist_id;END;",
378 MEDIA_SVC_DB_TABLE_PLAYLIST, MEDIA_SVC_DB_TABLE_PLAYLIST_MAP);
380 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
382 ret = _media_svc_sql_query(db_handle, sql, uid);
384 if (ret != SQLITE_OK) {
385 media_svc_error("It failed to create trigger (%d)", ret);
386 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
389 return MEDIA_INFO_ERROR_NONE;
392 int _media_svc_create_album_table(sqlite3 *db_handle, uid_t uid)
394 int ret = MEDIA_INFO_ERROR_NONE;
397 media_svc_debug_func();
399 sql = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %s (\
400 album_id INTEGER PRIMARY KEY AUTOINCREMENT, \
404 unique(name, artist) \
406 MEDIA_SVC_DB_TABLE_ALBUM);
408 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
410 ret = _media_svc_sql_query(db_handle, sql, uid);
412 if (ret != SQLITE_OK) {
413 media_svc_error("It failed to create db table (%d)", ret);
414 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
417 /* Create Trigger to remove album when media remove from media_table*/
418 sql = sqlite3_mprintf("CREATE TRIGGER IF NOT EXISTS album_cleanup \
419 DELETE ON %s BEGIN DELETE FROM %s \
420 WHERE (SELECT count(*) FROM %s WHERE album_id=old.album_id)=1 AND album_id=old.album_id;END;",
421 MEDIA_SVC_DB_TABLE_MEDIA, MEDIA_SVC_DB_TABLE_ALBUM, MEDIA_SVC_DB_TABLE_MEDIA);
423 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
425 ret = _media_svc_sql_query(db_handle, sql, uid);
427 if (ret != SQLITE_OK) {
428 media_svc_error("It failed to create trigger (%d)", ret);
429 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
432 return MEDIA_INFO_ERROR_NONE;
435 int _media_svc_create_tag_table(sqlite3 *db_handle, uid_t uid)
437 int ret = MEDIA_INFO_ERROR_NONE;
440 media_svc_debug_func();
443 sql = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %s (\
444 tag_id INTEGER PRIMARY KEY AUTOINCREMENT, \
445 name TEXT NOT NULL UNIQUE, \
448 MEDIA_SVC_DB_TABLE_TAG);
450 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
452 ret = _media_svc_sql_query(db_handle, sql, uid);
454 if (ret != SQLITE_OK) {
455 media_svc_error("It failed to create db table (%d)", ret);
456 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
459 /*Create tag_map table*/
460 sql = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %s (\
461 _id INTEGER PRIMARY KEY AUTOINCREMENT, \
462 tag_id INTEGER NOT NULL,\
463 media_uuid TEXT NOT NULL,\
464 unique(tag_id, media_uuid) \
466 MEDIA_SVC_DB_TABLE_TAG_MAP);
468 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
470 ret = _media_svc_sql_query(db_handle, sql, uid);
472 if (ret != SQLITE_OK) {
473 media_svc_error("It failed to create db table (%d)", ret);
474 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
478 sql = sqlite3_mprintf("\
479 CREATE VIEW IF NOT EXISTS tag_view AS \
481 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, 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 \
482 INNER JOIN tag_map AS tm \
483 INNER JOIN media AS m \
484 INNER JOIN (SELECT count(tag_id) as media_count, tag_id FROM tag_map group by tag_id) as cnt_tbl \
485 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) \
488 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 \
490 NOT IN (select tag_id from tag_map); \
493 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
495 ret = _media_svc_sql_query(db_handle, sql, uid);
497 media_svc_retv_if(ret != MEDIA_INFO_ERROR_NONE, ret);
499 /* Create Trigger to remove media from tag_map when media remove from media_table*/
500 sql = sqlite3_mprintf("CREATE TRIGGER IF NOT EXISTS tag_map_cleanup \
501 DELETE ON %s BEGIN DELETE FROM %s WHERE media_uuid=old.media_uuid;END;",
502 MEDIA_SVC_DB_TABLE_MEDIA, MEDIA_SVC_DB_TABLE_TAG_MAP);
504 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
506 ret = _media_svc_sql_query(db_handle, sql, uid);
508 if (ret != SQLITE_OK) {
509 media_svc_error("It failed to create trigger (%d)", ret);
510 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
513 /* Create Trigger to remove media from tag_map when tag removed from tag table*/
514 sql = sqlite3_mprintf("CREATE TRIGGER IF NOT EXISTS tag_map_cleanup_1 \
515 DELETE ON %s BEGIN DELETE FROM %s WHERE tag_id=old.tag_id;END;",
516 MEDIA_SVC_DB_TABLE_TAG, MEDIA_SVC_DB_TABLE_TAG_MAP);
518 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
520 ret = _media_svc_sql_query(db_handle, sql, uid);
522 if (ret != SQLITE_OK) {
523 media_svc_error("It failed to create trigger (%d)", ret);
524 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
526 return MEDIA_INFO_ERROR_NONE;
529 int _media_svc_create_bookmark_table(sqlite3 *db_handle, uid_t uid)
531 int ret = MEDIA_INFO_ERROR_NONE;
534 media_svc_debug_func();
536 sql = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %s (\
537 bookmark_id INTEGER PRIMARY KEY AUTOINCREMENT, \
538 media_uuid TEXT NOT NULL,\
539 marked_time INTEGER DEFAULT 0, \
540 thumbnail_path TEXT, \
541 unique(media_uuid, marked_time) \
543 MEDIA_SVC_DB_TABLE_BOOKMARK);
545 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
547 ret = _media_svc_sql_query(db_handle, sql, uid);
549 if (ret != SQLITE_OK) {
550 media_svc_error("It failed to create db table (%d)", ret);
551 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
554 /* Create Trigger to remove media from tag_map when media remove from media_table*/
555 sql = sqlite3_mprintf("CREATE TRIGGER IF NOT EXISTS bookmark_cleanup \
556 DELETE ON %s BEGIN DELETE FROM %s WHERE media_uuid=old.media_uuid;END;",
557 MEDIA_SVC_DB_TABLE_MEDIA, MEDIA_SVC_DB_TABLE_BOOKMARK);
559 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
561 ret = _media_svc_sql_query(db_handle, sql, uid);
563 if (ret != SQLITE_OK) {
564 media_svc_error("It failed to create trigger (%d)", ret);
565 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
568 return MEDIA_INFO_ERROR_NONE;
571 int _media_svc_create_custom_table(sqlite3 *db_handle, uid_t uid)
573 int ret = MEDIA_INFO_ERROR_NONE;
576 media_svc_debug_func();
578 sql = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %s (\
579 _id INTEGER PRIMARY KEY AUTOINCREMENT, \
589 MEDIA_SVC_DB_TABLE_CUSTOM);
591 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
593 ret = _media_svc_sql_query(db_handle, sql, uid);
595 if (ret != SQLITE_OK) {
596 media_svc_error("It failed to create db table (%d)", ret);
597 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
600 /* Create Trigger to remove media from tag_map when media remove from media_table*/
601 sql = sqlite3_mprintf("CREATE TRIGGER IF NOT EXISTS custom_cleanup \
602 DELETE ON %s BEGIN DELETE FROM %s WHERE media_uuid=old.media_uuid;END;",
603 MEDIA_SVC_DB_TABLE_MEDIA, MEDIA_SVC_DB_TABLE_CUSTOM);
605 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
607 ret = _media_svc_sql_query(db_handle, sql, uid);
609 if (ret != SQLITE_OK) {
610 media_svc_error("It failed to create trigger (%d)", ret);
611 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
615 sql = sqlite3_mprintf("CREATE INDEX IF NOT EXISTS custom_provider_idx on %s (provider); \
617 MEDIA_SVC_DB_TABLE_CUSTOM);
619 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
621 ret = _media_svc_sql_query(db_handle, sql, uid);
623 if (ret != SQLITE_OK) {
624 media_svc_error("It failed to create db table (%d)", ret);
625 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
628 return MEDIA_INFO_ERROR_NONE;
631 int _media_svc_request_update_db(const char *sql_str, uid_t uid)
633 int ret = MS_MEDIA_ERR_NONE;
635 ret = media_db_request_update_db(sql_str, uid);
640 int _media_svc_sql_query(sqlite3 *db_handle, const char *sql_str, uid_t uid)
644 media_svc_debug("[SQL query] : %s", sql_str);
646 //DB will be updated by Media Server.
647 err = _media_svc_request_update_db(sql_str, uid);
649 return _media_svc_error_convert(err);
651 char *zErrMsg = NULL;
652 media_svc_retvm_if(sql_str == NULL, MEDIA_INFO_ERROR_INVALID_PARAMETER, "sql_str is NULL");
654 err = sqlite3_exec(db_handle, sql_str, NULL, NULL, &zErrMsg);
656 if (SQLITE_OK != err) {
657 media_svc_error("failed to execute [%s], err[%d]", zErrMsg, err);
659 media_svc_debug("query success");
663 sqlite3_free (zErrMsg);
668 int _media_svc_sql_prepare_to_step(sqlite3 *handle, const char *sql_str, sqlite3_stmt** stmt)
672 media_svc_debug("[SQL query] : %s", sql_str);
674 err = sqlite3_prepare_v2(handle, sql_str, -1, stmt, NULL);
675 sqlite3_free((char *)sql_str);
677 if (err != SQLITE_OK) {
678 media_svc_error ("prepare error [%s]", sqlite3_errmsg(handle));
679 return MEDIA_INFO_ERROR_DATABASE_INTERNAL;
682 err = sqlite3_step(*stmt);
683 if (err != SQLITE_ROW) {
684 media_svc_error("Item not found. end of row [%s]", sqlite3_errmsg(handle));
685 SQLITE3_FINALIZE(*stmt);
686 return MEDIA_INFO_ERROR_DATABASE_NO_RECORD;
689 return MEDIA_INFO_ERROR_NONE;
692 int _media_svc_sql_begin_trans(sqlite3 *handle, uid_t uid)
694 int err = MEDIA_INFO_ERROR_NONE;
696 media_svc_debug("========_media_svc_sql_begin_trans");
698 //err = _media_svc_request_update_db("BEGIN IMMEDIATE;");
699 err = media_db_request_update_db_batch_start("BEGIN IMMEDIATE;", uid);
701 return _media_svc_error_convert(err);
703 char *err_msg = NULL;
705 if (SQLITE_OK != sqlite3_exec(handle, "BEGIN IMMEDIATE;", NULL, NULL, &err_msg)) {
706 media_svc_error("Error:failed to begin transaction: error=%s", err_msg);
707 sqlite3_free(err_msg);
708 return MEDIA_INFO_ERROR_DATABASE_INTERNAL;
711 sqlite3_free(err_msg);
716 int _media_svc_sql_end_trans(sqlite3 *handle, uid_t uid)
718 int err = MEDIA_INFO_ERROR_NONE;
720 media_svc_debug("========_media_svc_sql_end_trans");
722 err = media_db_request_update_db_batch_end("COMMIT;", uid);
723 //err = _media_svc_request_update_db("COMMIT;");
724 return _media_svc_error_convert(err);
726 char *err_msg = NULL;
727 if (SQLITE_OK != sqlite3_exec(handle, "COMMIT;", NULL, NULL, &err_msg)) {
728 media_svc_error("Error:failed to end transaction: error=%s", err_msg);
729 sqlite3_free(err_msg);
730 return MEDIA_INFO_ERROR_DATABASE_INTERNAL;
733 sqlite3_free(err_msg);
738 int _media_svc_sql_rollback_trans(sqlite3 *handle, uid_t uid)
740 int err = MEDIA_INFO_ERROR_NONE;
742 media_svc_debug("========_media_svc_sql_rollback_trans");
744 err = _media_svc_request_update_db("ROLLBACK;", uid);
745 return _media_svc_error_convert(err);
747 char *err_msg = NULL;
748 if (SQLITE_OK != sqlite3_exec(handle, "ROLLBACK;", NULL, NULL, &err_msg)) {
749 media_svc_error("Error:failed to rollback transaction: error=%s", err_msg);
750 sqlite3_free(err_msg);
751 return MEDIA_INFO_ERROR_DATABASE_INTERNAL;
754 sqlite3_free(err_msg);
759 int _media_svc_sql_query_list(sqlite3 *handle, GList **query_list, uid_t uid)
762 int length = g_list_length(*query_list);
766 media_svc_debug("query list length : [%d]", length);
768 for (i = 0; i < length; i++) {
769 sql = (char*)g_list_nth_data(*query_list, i);
771 //err = _media_svc_sql_query(handle, sql);
772 err = media_db_request_update_db_batch(sql, uid);
773 //if (err != SQLITE_OK) {
774 // media_svc_error("A query failed in batch");
775 if (err < MS_MEDIA_ERR_NONE) {
776 media_svc_error("media_db_request_update_db_batch failed : %d", err);
783 _media_svc_sql_query_release(query_list);
785 return MEDIA_INFO_ERROR_NONE;
789 void _media_svc_sql_query_add(GList **query_list, char **query)
791 *query_list = g_list_append( *query_list, *query);
794 void _media_svc_sql_query_release(GList **query_list)
797 media_svc_debug("_svc_sql_query_release");
798 g_list_free(*query_list);