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 samplerate INTEGER DEFAULT -1, \
59 channel INTEGER DEFAULT -1, \
60 duration INTEGER DEFAULT -1, \
61 longitude DOUBLE DEFAULT 0, \
62 latitude DOUBLE DEFAULT 0, \
63 altitude DOUBLE DEFAULT 0, \
64 width INTEGER DEFAULT -1, \
65 height INTEGER DEFAULT -1, \
67 orientation INTEGER DEFAULT -1, \
69 played_count INTEGER DEFAULT 0, \
70 last_played_time INTEGER DEFAULT 0, \
71 last_played_position INTEGER DEFAULT 0, \
72 rating INTEGER DEFAULT 0, \
73 favourite INTEGER DEFAULT 0, \
81 is_drm INTEGER DEFAULT 0, \
82 storage_type INTEGER, \
83 timeline INTEGER DEFAULT 0, \
85 sync_status INTEGER DEFAULT 0, \
86 file_name_pinyin TEXT, \
90 album_artist_pinyin TEXT, \
92 composer_pinyin TEXT, \
93 copyright_pinyin TEXT, \
94 description_pinyin TEXT, \
96 provider_pinyin TEXT, \
97 content_name_pinyin TEXT, \
98 category_pinyin TEXT, \
99 location_tag_pinyin TEXT, \
100 age_rating_pinyin TEXT, \
101 keyword_pinyin TEXT, \
102 validity INTEGER DEFAULT 1, \
103 unique(path, file_name) \
106 static int __media_svc_busy_handler(void *pData, int count);
108 static int __media_svc_busy_handler(void *pData, int count)
112 media_svc_debug("media_svc_busy_handler called : %d", count);
117 int _media_svc_connect_db_with_handle(sqlite3 **db_handle)
119 int ret = MEDIA_INFO_ERROR_NONE;
121 media_svc_debug_func();
124 ret = db_util_open(MEDIA_SVC_DB_NAME, db_handle, DB_UTIL_REGISTER_HOOK_METHOD);
126 if (SQLITE_OK != ret) {
128 media_svc_error("error when db open");
130 return MEDIA_INFO_ERROR_DATABASE_CONNECT;
133 /*Register busy handler*/
135 ret = sqlite3_busy_handler(*db_handle, __media_svc_busy_handler, NULL);
137 if (SQLITE_OK != ret) {
140 media_svc_error("[error when register busy handler] %s\n", sqlite3_errmsg(*db_handle));
143 db_util_close(*db_handle);
146 return MEDIA_INFO_ERROR_DATABASE_CONNECT;
150 return MEDIA_INFO_ERROR_DATABASE_CONNECT;
153 return MEDIA_INFO_ERROR_NONE;
156 int _media_svc_disconnect_db_with_handle(sqlite3 *db_handle)
158 int ret = MEDIA_INFO_ERROR_NONE;
160 media_svc_debug_func();
162 ret = db_util_close(db_handle);
164 if (SQLITE_OK != ret) {
165 media_svc_error("Error when db close : %s", sqlite3_errmsg(db_handle));
167 return MEDIA_INFO_ERROR_DATABASE_DISCONNECT;
170 return MEDIA_INFO_ERROR_NONE;
173 int _media_svc_create_media_table(sqlite3 *db_handle, uid_t uid)
175 int ret = MEDIA_INFO_ERROR_NONE;
178 media_svc_debug_func();
180 sql = sqlite3_mprintf(MEDIA_DB_SCHEMA, MEDIA_SVC_DB_TABLE_MEDIA);
182 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
184 ret = _media_svc_sql_query(db_handle, sql, uid);
186 if (ret != SQLITE_OK) {
187 media_svc_error("It failed to create db table (%d)", ret);
188 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
192 sql = sqlite3_mprintf(" CREATE INDEX IF NOT EXISTS media_media_type_idx on %s (media_type); \
193 CREATE INDEX IF NOT EXISTS media_title_idx on %s (title); \
194 CREATE INDEX IF NOT EXISTS media_modified_time_idx on %s (modified_time); \
195 CREATE INDEX IF NOT EXISTS media_provider_idx on %s (provider); \
196 CREATE INDEX IF NOT EXISTS folder_uuid_idx on %s (folder_uuid); \
197 CREATE INDEX IF NOT EXISTS media_album_idx on %s (album); \
198 CREATE INDEX IF NOT EXISTS media_artist_idx on %s (artist); \
199 CREATE INDEX IF NOT EXISTS media_author_idx on %s (author); \
200 CREATE INDEX IF NOT EXISTS media_category_idx on %s (category); \
201 CREATE INDEX IF NOT EXISTS media_composer_idx on %s (composer); \
202 CREATE INDEX IF NOT EXISTS media_content_name_idx on %s (content_name); \
203 CREATE INDEX IF NOT EXISTS media_file_name_idx on %s (file_name); \
204 CREATE INDEX IF NOT EXISTS media_genre_idx on %s (genre); \
205 CREATE INDEX IF NOT EXISTS media_location_tag_idx on %s (location_tag); \
206 CREATE INDEX IF NOT EXISTS media_media_uuid_idx on %s (media_uuid); \
207 CREATE INDEX IF NOT EXISTS media_timeline_idx on %s (timeline); \
208 CREATE INDEX IF NOT EXISTS media_path_idx on %s (path); \
210 MEDIA_SVC_DB_TABLE_MEDIA,
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);
228 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
230 ret = _media_svc_sql_query(db_handle, sql, uid);
232 if (ret != SQLITE_OK) {
233 media_svc_error("It failed to create db table (%d)", ret);
234 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
237 return MEDIA_INFO_ERROR_NONE;
240 int _media_svc_create_folder_table(sqlite3 *db_handle, uid_t uid)
242 int ret = MEDIA_INFO_ERROR_NONE;
245 media_svc_debug_func();
247 sql = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %s (\
248 folder_uuid TEXT PRIMARY KEY, \
249 path TEXT NOT NULL UNIQUE, \
250 name TEXT NOT NULL, \
251 modified_time INTEGER DEFAULT 0, \
253 storage_type INTEGER, \
254 unique(path, name, storage_type) \
256 MEDIA_SVC_DB_TABLE_FOLDER);
258 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
260 ret = _media_svc_sql_query(db_handle, sql, uid);
262 if (ret != SQLITE_OK) {
263 media_svc_error("It failed to create db table (%d)", ret);
264 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
267 /* Create Trigger to remove folder which have no content from folder when media remove from media_table*/
268 sql = sqlite3_mprintf("CREATE TRIGGER IF NOT EXISTS folder_cleanup \
269 DELETE ON %s BEGIN DELETE FROM %s \
270 WHERE (SELECT count(*) FROM %s WHERE folder_uuid=old.folder_uuid)=1 AND folder_uuid=old.folder_uuid;END;",
271 MEDIA_SVC_DB_TABLE_MEDIA, MEDIA_SVC_DB_TABLE_FOLDER, MEDIA_SVC_DB_TABLE_MEDIA);
273 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
275 ret = _media_svc_sql_query(db_handle, sql, uid);
277 if (ret != SQLITE_OK) {
278 media_svc_error("It failed to create trigger (%d)", ret);
279 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
283 sql = sqlite3_mprintf(" CREATE INDEX IF NOT EXISTS folder_folder_uuid_idx on %s (folder_uuid); \
285 MEDIA_SVC_DB_TABLE_FOLDER);
287 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
289 ret = _media_svc_sql_query(db_handle, sql, uid);
291 media_svc_retv_if(ret != MEDIA_INFO_ERROR_NONE, ret);
292 return MEDIA_INFO_ERROR_NONE;
295 int _media_svc_create_playlist_table(sqlite3 *db_handle, uid_t uid)
297 int ret = MEDIA_INFO_ERROR_NONE;
300 media_svc_debug_func();
302 /*Create playlist table*/
303 sql = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %s (\
304 playlist_id INTEGER PRIMARY KEY AUTOINCREMENT, \
305 name TEXT NOT NULL UNIQUE,\
309 MEDIA_SVC_DB_TABLE_PLAYLIST);
311 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
313 ret = _media_svc_sql_query(db_handle, sql, uid);
315 if (ret != SQLITE_OK) {
316 media_svc_error("It failed to create db table (%d)", ret);
317 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
320 /*Create playlist_map table*/
321 sql = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %s (\
322 _id INTEGER PRIMARY KEY AUTOINCREMENT, \
323 playlist_id INTEGER NOT NULL,\
324 media_uuid TEXT NOT NULL,\
325 play_order INTEGER NOT NULL\
327 MEDIA_SVC_DB_TABLE_PLAYLIST_MAP);
329 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
331 ret = _media_svc_sql_query(db_handle, sql, uid);
333 if (ret != SQLITE_OK) {
334 media_svc_error("It failed to create db table (%d)", ret);
335 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
338 /* Create playlist_view*/
339 sql = sqlite3_mprintf(" \
340 CREATE VIEW IF NOT EXISTS playlist_view AS \
341 SELECT p.playlist_id, p.name, 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, timeline, sync_status FROM playlist AS p \
342 INNER JOIN playlist_map AS pm \
343 INNER JOIN media AS m \
344 INNER JOIN (SELECT count(playlist_id) as media_count, playlist_id FROM playlist_map group by playlist_id) as cnt_tbl \
345 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) \
347 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, -1, 0 FROM playlist \
348 WHERE playlist_id NOT IN (select playlist_id from playlist_map) \
350 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, -1, 0 FROM playlist \
351 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); \
354 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
356 ret = _media_svc_sql_query(db_handle, sql, uid);
358 media_svc_retv_if(ret != MEDIA_INFO_ERROR_NONE, ret);
360 /* Create Trigger to remove media from playlist_map when media remove from media_table*/
361 sql = sqlite3_mprintf("CREATE TRIGGER IF NOT EXISTS playlist_map_cleanup \
362 DELETE ON %s BEGIN DELETE FROM %s WHERE media_uuid=old.media_uuid;END;",
363 MEDIA_SVC_DB_TABLE_MEDIA, MEDIA_SVC_DB_TABLE_PLAYLIST_MAP);
365 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
367 ret = _media_svc_sql_query(db_handle, sql, uid);
369 if (ret != SQLITE_OK) {
370 media_svc_error("It failed to create trigger (%d)", ret);
371 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
374 /* Create Trigger to remove media from playlist_map when playlist removed from playlist table*/
375 sql = sqlite3_mprintf("CREATE TRIGGER IF NOT EXISTS playlist_map_cleanup_1 \
376 DELETE ON %s BEGIN DELETE FROM %s WHERE playlist_id=old.playlist_id;END;",
377 MEDIA_SVC_DB_TABLE_PLAYLIST, MEDIA_SVC_DB_TABLE_PLAYLIST_MAP);
379 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
381 ret = _media_svc_sql_query(db_handle, sql, uid);
383 if (ret != SQLITE_OK) {
384 media_svc_error("It failed to create trigger (%d)", ret);
385 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
388 return MEDIA_INFO_ERROR_NONE;
391 int _media_svc_create_album_table(sqlite3 *db_handle, uid_t uid)
393 int ret = MEDIA_INFO_ERROR_NONE;
396 media_svc_debug_func();
398 sql = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %s (\
399 album_id INTEGER PRIMARY KEY AUTOINCREMENT, \
403 unique(name, artist) \
405 MEDIA_SVC_DB_TABLE_ALBUM);
407 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
409 ret = _media_svc_sql_query(db_handle, sql, uid);
411 if (ret != SQLITE_OK) {
412 media_svc_error("It failed to create db table (%d)", ret);
413 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
416 /* Create Trigger to remove album when media remove from media_table*/
417 sql = sqlite3_mprintf("CREATE TRIGGER IF NOT EXISTS album_cleanup \
418 DELETE ON %s BEGIN DELETE FROM %s \
419 WHERE (SELECT count(*) FROM %s WHERE album_id=old.album_id)=1 AND album_id=old.album_id;END;",
420 MEDIA_SVC_DB_TABLE_MEDIA, MEDIA_SVC_DB_TABLE_ALBUM, MEDIA_SVC_DB_TABLE_MEDIA);
422 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
424 ret = _media_svc_sql_query(db_handle, sql, uid);
426 if (ret != SQLITE_OK) {
427 media_svc_error("It failed to create trigger (%d)", ret);
428 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
431 return MEDIA_INFO_ERROR_NONE;
434 int _media_svc_create_tag_table(sqlite3 *db_handle, uid_t uid)
436 int ret = MEDIA_INFO_ERROR_NONE;
439 media_svc_debug_func();
442 sql = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %s (\
443 tag_id INTEGER PRIMARY KEY AUTOINCREMENT, \
444 name TEXT NOT NULL UNIQUE, \
447 MEDIA_SVC_DB_TABLE_TAG);
449 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
451 ret = _media_svc_sql_query(db_handle, sql, uid);
453 if (ret != SQLITE_OK) {
454 media_svc_error("It failed to create db table (%d)", ret);
455 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
458 /*Create tag_map table*/
459 sql = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %s (\
460 _id INTEGER PRIMARY KEY AUTOINCREMENT, \
461 tag_id INTEGER NOT NULL,\
462 media_uuid TEXT NOT NULL,\
463 unique(tag_id, media_uuid) \
465 MEDIA_SVC_DB_TABLE_TAG_MAP);
467 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
469 ret = _media_svc_sql_query(db_handle, sql, uid);
471 if (ret != SQLITE_OK) {
472 media_svc_error("It failed to create db table (%d)", ret);
473 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
477 sql = sqlite3_mprintf("\
478 CREATE VIEW IF NOT EXISTS tag_view AS \
480 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 FROM tag AS t \
481 INNER JOIN tag_map AS tm \
482 INNER JOIN media AS m \
483 INNER JOIN (SELECT count(tag_id) as media_count, tag_id FROM tag_map group by tag_id) as cnt_tbl \
484 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) \
487 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 FROM tag \
489 NOT IN (select tag_id from tag_map); \
492 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
494 ret = _media_svc_sql_query(db_handle, sql, uid);
496 media_svc_retv_if(ret != MEDIA_INFO_ERROR_NONE, ret);
498 /* Create Trigger to remove media from tag_map when media remove from media_table*/
499 sql = sqlite3_mprintf("CREATE TRIGGER IF NOT EXISTS tag_map_cleanup \
500 DELETE ON %s BEGIN DELETE FROM %s WHERE media_uuid=old.media_uuid;END;",
501 MEDIA_SVC_DB_TABLE_MEDIA, MEDIA_SVC_DB_TABLE_TAG_MAP);
503 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
505 ret = _media_svc_sql_query(db_handle, sql, uid);
507 if (ret != SQLITE_OK) {
508 media_svc_error("It failed to create trigger (%d)", ret);
509 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
512 /* Create Trigger to remove media from tag_map when tag removed from tag table*/
513 sql = sqlite3_mprintf("CREATE TRIGGER IF NOT EXISTS tag_map_cleanup_1 \
514 DELETE ON %s BEGIN DELETE FROM %s WHERE tag_id=old.tag_id;END;",
515 MEDIA_SVC_DB_TABLE_TAG, MEDIA_SVC_DB_TABLE_TAG_MAP);
517 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
519 ret = _media_svc_sql_query(db_handle, sql, uid);
521 if (ret != SQLITE_OK) {
522 media_svc_error("It failed to create trigger (%d)", ret);
523 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
525 return MEDIA_INFO_ERROR_NONE;
528 int _media_svc_create_bookmark_table(sqlite3 *db_handle, uid_t uid)
530 int ret = MEDIA_INFO_ERROR_NONE;
533 media_svc_debug_func();
535 sql = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %s (\
536 bookmark_id INTEGER PRIMARY KEY AUTOINCREMENT, \
537 media_uuid TEXT NOT NULL,\
538 marked_time INTEGER DEFAULT 0, \
539 thumbnail_path TEXT, \
540 unique(media_uuid, marked_time) \
542 MEDIA_SVC_DB_TABLE_BOOKMARK);
544 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
546 ret = _media_svc_sql_query(db_handle, sql, uid);
548 if (ret != SQLITE_OK) {
549 media_svc_error("It failed to create db table (%d)", ret);
550 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
553 /* Create Trigger to remove media from tag_map when media remove from media_table*/
554 sql = sqlite3_mprintf("CREATE TRIGGER IF NOT EXISTS bookmark_cleanup \
555 DELETE ON %s BEGIN DELETE FROM %s WHERE media_uuid=old.media_uuid;END;",
556 MEDIA_SVC_DB_TABLE_MEDIA, MEDIA_SVC_DB_TABLE_BOOKMARK);
558 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
560 ret = _media_svc_sql_query(db_handle, sql, uid);
562 if (ret != SQLITE_OK) {
563 media_svc_error("It failed to create trigger (%d)", ret);
564 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
567 return MEDIA_INFO_ERROR_NONE;
570 int _media_svc_create_custom_table(sqlite3 *db_handle, uid_t uid)
572 int ret = MEDIA_INFO_ERROR_NONE;
575 media_svc_debug_func();
577 sql = sqlite3_mprintf("CREATE TABLE IF NOT EXISTS %s (\
578 _id INTEGER PRIMARY KEY AUTOINCREMENT, \
588 MEDIA_SVC_DB_TABLE_CUSTOM);
590 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
592 ret = _media_svc_sql_query(db_handle, sql, uid);
594 if (ret != SQLITE_OK) {
595 media_svc_error("It failed to create db table (%d)", ret);
596 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
599 /* Create Trigger to remove media from tag_map when media remove from media_table*/
600 sql = sqlite3_mprintf("CREATE TRIGGER IF NOT EXISTS custom_cleanup \
601 DELETE ON %s BEGIN DELETE FROM %s WHERE media_uuid=old.media_uuid;END;",
602 MEDIA_SVC_DB_TABLE_MEDIA, MEDIA_SVC_DB_TABLE_CUSTOM);
604 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
606 ret = _media_svc_sql_query(db_handle, sql, uid);
608 if (ret != SQLITE_OK) {
609 media_svc_error("It failed to create trigger (%d)", ret);
610 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
614 sql = sqlite3_mprintf("CREATE INDEX IF NOT EXISTS custom_provider_idx on %s (provider); \
616 MEDIA_SVC_DB_TABLE_CUSTOM);
618 media_svc_retv_if(sql == NULL, MEDIA_INFO_ERROR_OUT_OF_MEMORY);
620 ret = _media_svc_sql_query(db_handle, sql, uid);
622 if (ret != SQLITE_OK) {
623 media_svc_error("It failed to create db table (%d)", ret);
624 return MEDIA_INFO_ERROR_DATABASE_TABLE_OPEN;
627 return MEDIA_INFO_ERROR_NONE;
630 int _media_svc_request_update_db(const char *sql_str, uid_t uid)
632 int ret = MS_MEDIA_ERR_NONE;
634 ret = media_db_request_update_db(sql_str, uid);
639 int _media_svc_sql_query(sqlite3 *db_handle, const char *sql_str, uid_t uid)
643 media_svc_debug("[SQL query] : %s", sql_str);
645 //DB will be updated by Media Server.
646 err = _media_svc_request_update_db(sql_str, uid);
648 return _media_svc_error_convert(err);
650 char *zErrMsg = NULL;
651 media_svc_retvm_if(sql_str == NULL, MEDIA_INFO_ERROR_INVALID_PARAMETER, "sql_str is NULL");
653 err = sqlite3_exec(db_handle, sql_str, NULL, NULL, &zErrMsg);
655 if (SQLITE_OK != err) {
656 media_svc_error("failed to execute [%s], err[%d]", zErrMsg, err);
658 media_svc_debug("query success");
662 sqlite3_free (zErrMsg);
667 int _media_svc_sql_prepare_to_step(sqlite3 *handle, const char *sql_str, sqlite3_stmt** stmt)
671 media_svc_debug("[SQL query] : %s", sql_str);
673 err = sqlite3_prepare_v2(handle, sql_str, -1, stmt, NULL);
674 sqlite3_free((char *)sql_str);
676 if (err != SQLITE_OK) {
677 media_svc_error ("prepare error [%s]", sqlite3_errmsg(handle));
678 return MEDIA_INFO_ERROR_DATABASE_INTERNAL;
681 err = sqlite3_step(*stmt);
682 if (err != SQLITE_ROW) {
683 media_svc_error("Item not found. end of row [%s]", sqlite3_errmsg(handle));
684 SQLITE3_FINALIZE(*stmt);
685 return MEDIA_INFO_ERROR_DATABASE_NO_RECORD;
688 return MEDIA_INFO_ERROR_NONE;
691 int _media_svc_sql_begin_trans(sqlite3 *handle, uid_t uid)
693 int err = MEDIA_INFO_ERROR_NONE;
695 media_svc_debug("========_media_svc_sql_begin_trans");
697 //err = _media_svc_request_update_db("BEGIN IMMEDIATE;");
698 err = media_db_request_update_db_batch_start("BEGIN IMMEDIATE;", uid);
700 return _media_svc_error_convert(err);
702 char *err_msg = NULL;
704 if (SQLITE_OK != sqlite3_exec(handle, "BEGIN IMMEDIATE;", NULL, NULL, &err_msg)) {
705 media_svc_error("Error:failed to begin transaction: error=%s", err_msg);
706 sqlite3_free(err_msg);
707 return MEDIA_INFO_ERROR_DATABASE_INTERNAL;
710 sqlite3_free(err_msg);
715 int _media_svc_sql_end_trans(sqlite3 *handle, uid_t uid)
717 int err = MEDIA_INFO_ERROR_NONE;
719 media_svc_debug("========_media_svc_sql_end_trans");
721 err = media_db_request_update_db_batch_end("COMMIT;", uid);
722 //err = _media_svc_request_update_db("COMMIT;");
723 return _media_svc_error_convert(err);
725 char *err_msg = NULL;
726 if (SQLITE_OK != sqlite3_exec(handle, "COMMIT;", NULL, NULL, &err_msg)) {
727 media_svc_error("Error:failed to end transaction: error=%s", err_msg);
728 sqlite3_free(err_msg);
729 return MEDIA_INFO_ERROR_DATABASE_INTERNAL;
732 sqlite3_free(err_msg);
737 int _media_svc_sql_rollback_trans(sqlite3 *handle, uid_t uid)
739 int err = MEDIA_INFO_ERROR_NONE;
741 media_svc_debug("========_media_svc_sql_rollback_trans");
743 err = _media_svc_request_update_db("ROLLBACK;", uid);
744 return _media_svc_error_convert(err);
746 char *err_msg = NULL;
747 if (SQLITE_OK != sqlite3_exec(handle, "ROLLBACK;", NULL, NULL, &err_msg)) {
748 media_svc_error("Error:failed to rollback transaction: error=%s", err_msg);
749 sqlite3_free(err_msg);
750 return MEDIA_INFO_ERROR_DATABASE_INTERNAL;
753 sqlite3_free(err_msg);
758 int _media_svc_sql_query_list(sqlite3 *handle, GList **query_list, uid_t uid)
761 int length = g_list_length(*query_list);
765 media_svc_debug("query list length : [%d]", length);
767 for (i = 0; i < length; i++) {
768 sql = (char*)g_list_nth_data(*query_list, i);
770 //err = _media_svc_sql_query(handle, sql);
771 err = media_db_request_update_db_batch(sql, uid);
772 //if (err != SQLITE_OK) {
773 // media_svc_error("A query failed in batch");
774 if (err < MS_MEDIA_ERR_NONE) {
775 media_svc_error("media_db_request_update_db_batch failed : %d", err);
782 _media_svc_sql_query_release(query_list);
784 return MEDIA_INFO_ERROR_NONE;
788 void _media_svc_sql_query_add(GList **query_list, char **query)
790 *query_list = g_list_append( *query_list, *query);
793 void _media_svc_sql_query_release(GList **query_list)
796 media_svc_debug("_svc_sql_query_release");
797 g_list_free(*query_list);