2 * Copyright 2012 Samsung Electronics Co., Ltd
4 * Licensed under the Flora License, Version 1.0 (the "License");
5 * you may not use this file except in compliance with the License.
6 * You may obtain a copy of the License at
8 * http://www.tizenopensource.org/license
10 * Unless required by applicable law or agreed to in writing, software
11 * distributed under the License is distributed on an "AS IS" BASIS,
12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13 * See the License for the specific language governing permissions and
14 * limitations under the License.
17 #include "db-define.h"
18 #include "db-schema.h"
21 /**********************************************************************
22 ******************define, struct ,typedef, union, enum, global val *************************************
23 ***********************************************************************/
24 #define QUERY_MAXLEN (4096)
26 #define TEXT(s, n) (char *)sqlite3_column_text(s, n)
27 #define INT(s, n) sqlite3_column_int(s, n)
30 #define ADLIST_ADD_AFTER(place, elem) {\
31 if ((place) == NULL) {\
33 (place)->next = (place)->prev = NULL;\
35 (elem)->next = (place)->next;\
36 (elem)->prev = (place);\
37 (place)->next = (elem);\
38 if ((elem)->next != NULL)\
39 (elem)->next->prev = (elem);\
43 #define ADLIST_GET_FIRST(list, result) {\
44 if ((list) != NULL) {\
45 for ( ; (list)->prev != NULL; (list) = (list)->prev) ;\
49 /**********************************************************************
50 ******************Local function declear, extern function declear*************************************
51 ***********************************************************************/
53 /**********************************************************************
54 ******************Global val , static global val*************************************
55 ***********************************************************************/
57 static const char *empty_str = "";
58 /**********************************************************************
59 ******************Local function ref*************************************
60 ***********************************************************************/
63 static char *_s(char *str)
66 retv_if(str == NULL, (char *)empty_str);
77 static int _exec(sqlite3 * db, char * query)
82 retvm_if(db == NULL, -1, "DB handler is NULL");
84 rc = sqlite3_exec(db, query, NULL, 0, &errmsg);
85 if (rc != SQLITE_OK) {
86 DB_INFO("Query: [%s]", query);
87 DB_INFO_RED("SQL error: %s\n", errmsg);
95 static int _create_table(sqlite3 * db)
99 rc = _exec(db, CREATE_ALARM_TABLE);
100 retv_if(rc == -1, -1);
106 static int _get_ad(sqlite3 * db, int cid, struct alarm_data * ad)
109 char query[QUERY_MAXLEN] = { 0, };
113 snprintf(query, sizeof(query), "select "
114 "magic, alarm_mgr_id, enable, missed, author, name, "
115 "stime, atime, etime, sdate, edate, timezone, "
116 "repeat_once, repeat_every, repeat_weekly, "
117 "snooze_enable, snooze_min, snooze_times, count,"
118 "type, tone, volume, auto_power_on "
119 "from alarm where id = %d", cid);
120 rc = sqlite3_prepare(db, query, -1, &stmt, NULL);
121 retvm_if(rc != SQLITE_OK, -1,
122 "DB: SQLITE_OK _get_ad(). Get data error");
123 retvm_if(stmt == NULL, -1, "DB: _get_ad(). Get data error");
125 rc = sqlite3_step(stmt);
126 if (rc == SQLITE_ROW) {
128 ad->_magic = INT(stmt, idx++);
129 ad->alarm_mgr_id = INT(stmt, idx++);
130 ad->enable = INT(stmt, idx++);
131 ad->missed = INT(stmt, idx++);
132 ad->author = INT(stmt, idx++);
133 strncpy(ad->name, _s(TEXT(stmt, idx++)), sizeof(ad->name) - 1);
134 ad->stime = INT(stmt, idx++);
135 ad->atime = INT(stmt, idx++);
136 ad->etime = INT(stmt, idx++);
137 ad->sdate = INT(stmt, idx++);
138 ad->edate = INT(stmt, idx++);
139 strncpy(ad->timezone, _s(TEXT(stmt, idx++)),
140 sizeof(ad->timezone) - 1);
142 ad->repeat_once = INT(stmt, idx++);
143 ad->repeat_every = INT(stmt, idx++);
144 ad->repeat_weekly = INT(stmt, idx++);
146 ad->snooze_enable = INT(stmt, idx++);
147 ad->snooze_min = INT(stmt, idx++);
148 ad->snooze_times = INT(stmt, idx++);
149 ad->count = INT(stmt, idx++);
151 ad->type = INT(stmt, idx++);
152 strncpy(ad->tone, _s(TEXT(stmt, idx++)), sizeof(ad->tone) - 1);
153 ad->volume = INT(stmt, idx++);
154 ad->auto_power_on = INT(stmt, idx++);
156 retvm_if(1, -1, "Alarm data %d does not exist", cid);
158 rc = sqlite3_finalize(stmt);
163 static int _get_ad_by_author(sqlite3 * db, int cid, struct alarm_data * ad,
167 char query[QUERY_MAXLEN] = { 0, };
171 snprintf(query, sizeof(query), "select "
172 "magic, alarm_mgr_id, enable, missed, author, name, "
173 "stime, atime, etime, sdate, edate, timezone, "
174 "repeat_once, repeat_every, repeat_weekly, "
175 "snooze_enable, snooze_min, snooze_times, count,"
176 "type, tone, volume, auto_power_on "
177 "from alarm where id = ( select id = %d from alarm where author = %d)",
179 rc = sqlite3_prepare(db, query, -1, &stmt, NULL);
180 retvm_if(rc != SQLITE_OK, -1,
181 "DB: SQLITE_OK _get_ad_by_author(). Get data error");
182 retvm_if(stmt == NULL, -1, "DB: _get_ad_by_author(). Get data error");
184 rc = sqlite3_step(stmt);
185 if (rc == SQLITE_ROW) {
187 ad->_magic = INT(stmt, idx++);
188 ad->alarm_mgr_id = INT(stmt, idx++);
189 ad->enable = INT(stmt, idx++);
190 ad->missed = INT(stmt, idx++);
191 ad->author = INT(stmt, idx++);
192 strncpy(ad->name, _s(TEXT(stmt, idx++)), sizeof(ad->name) - 1);
193 ad->stime = INT(stmt, idx++);
194 ad->atime = INT(stmt, idx++);
195 ad->etime = INT(stmt, idx++);
196 ad->sdate = INT(stmt, idx++);
197 ad->edate = INT(stmt, idx++);
198 strncpy(ad->timezone, _s(TEXT(stmt, idx++)),
199 sizeof(ad->timezone) - 1);
201 ad->repeat_once = INT(stmt, idx++);
202 ad->repeat_every = INT(stmt, idx++);
203 ad->repeat_weekly = INT(stmt, idx++);
205 ad->snooze_enable = INT(stmt, idx++);
206 ad->snooze_min = INT(stmt, idx++);
207 ad->snooze_times = INT(stmt, idx++);
208 ad->count = INT(stmt, idx++);
210 ad->type = INT(stmt, idx++);
211 strncpy(ad->tone, _s(TEXT(stmt, idx++)), sizeof(ad->tone) - 1);
212 ad->volume = INT(stmt, idx++);
213 ad->auto_power_on = INT(stmt, idx++);
215 retvm_if(1, -1, "Alarm data %d does not exist", cid);
217 rc = sqlite3_finalize(stmt);
222 static inline void _make_qry_i_ad(char *query, int len, struct alarm_data *ad)
224 snprintf(query, len, "insert into alarm ("
225 "magic, alarm_mgr_id, enable, missed, author, name, "
226 "stime, atime, etime, sdate, edate, timezone, "
227 "repeat_once, repeat_every, repeat_weekly, "
228 "snooze_enable, snooze_min, snooze_times, count,"
229 "type, tone, volume, auto_power_on) values ( "
230 "\"%d\", \"%d\", \"%d\", \"%d\", \"%d\", \"%s\", "
231 "\"%d\", \"%d\", \"%d\", \"%d\", \"%d\", \"%s\", "
232 "\"%d\", \"%d\", \"%d\", "
233 "\"%d\", \"%d\", \"%d\", \"%d\","
234 "\"%d\", \"%s\", \"%d\", \"%d\")",
235 ad->_magic, ad->alarm_mgr_id, ad->enable, ad->missed,
236 ad->author, _s(ad->name), (int)ad->stime, (int)ad->atime,
237 (int)ad->etime, (int)ad->sdate, (int)ad->edate,
238 _s(ad->timezone), ad->repeat_once, ad->repeat_every,
239 ad->repeat_weekly, ad->snooze_enable, ad->snooze_min,
240 ad->snooze_times, ad->count, ad->type, _s(ad->tone),
241 ad->volume, ad->auto_power_on);
245 static inline void _make_qry_u_ad(char *query, int len, struct alarm_data *ad)
247 snprintf(query, len, "update alarm set "
248 "alarm_mgr_id = \"%d\", enable = \"%d\", missed = \"%d\",author = \"%d\", name = \"%s\", "
249 "stime = \"%d\", atime = \"%d\", etime = \"%d\", sdate = \"%d\", edate = \"%d\", timezone = \"%s\", "
250 "repeat_once = \"%d\", repeat_every = \"%d\", repeat_weekly = \"%d\", "
251 "snooze_enable = \"%d\", snooze_min = \"%d\", snooze_times = \"%d\", count = \"%d\","
252 "type = \"%d\", tone = \"%s\", volume = \"%d\", auto_power_on = \"%d\" "
254 ad->alarm_mgr_id, ad->enable, ad->missed, ad->author,
255 _s(ad->name), (int)ad->stime, (int)ad->atime, (int)ad->etime,
256 (int)ad->sdate, (int)ad->edate, _s(ad->timezone),
257 ad->repeat_once, ad->repeat_every, ad->repeat_weekly,
258 ad->snooze_enable, ad->snooze_min, ad->snooze_times, ad->count,
259 ad->type, _s(ad->tone), ad->volume, ad->auto_power_on, ad->id);
263 static inline void _make_qry_u_enable(char *query, int len, int id, bool enable)
265 snprintf(query, len, "update alarm set "
266 "enable = \"%d\" where id = %d", enable, id);
270 static inline void _make_qry_u_snooze(char *query, int len, int id, bool enable)
272 snprintf(query, len, "update alarm set "
273 "snooze_enable = \"%d\" where id = %d", enable, id);
276 /**********************************************************************
277 ******************Global function ref*************************************
278 ***********************************************************************/
281 sqlite3 *db_init(char *root)
286 rc = db_util_open(root, &db, 0); // sqlite3_open(root, &db);
287 printf("db_util_open, rc=%d\n", rc);
289 DB_INFO_RED("Can't open database: %s", sqlite3_errmsg(db));
290 db_util_close(db); // sqlite3_close(db);
294 rc = _create_table(db);
295 printf("_create_table, rc=%d\n", rc);
297 DB_INFO_RED("Can't create table: %s", sqlite3_errmsg(db));
298 db_util_close(db); // sqlite3_close(db);
305 void db_fini(sqlite3 *db)
308 db_util_close(db); // sqlite3_close(db);
313 int insert_data(sqlite3 *db, struct alarm_data *ad)
316 char query[QUERY_MAXLEN] = { 0, };
318 retvm_if(!db, -1, "DB handler is NULL\n");
319 retvm_if(!ad, -1, "alarm data is NULL\n");
320 retvm_if(!MAGIC_VALUE_CHECK(ad->_magic), -1,
321 "alarm data is error, ad->_magic=%d,ALARM_DB_MAGIC_VALUE=%d\n",
322 ad->_magic, ALARM_DB_MAGIC_VALUE);
324 _make_qry_i_ad(query, sizeof(query), ad);
325 rc = _exec(db, query);
326 retv_if(rc == -1, rc);
328 ad->id = (int)sqlite3_last_insert_rowid(db);
333 int update_data(sqlite3 *db, struct alarm_data *ad)
336 char query[QUERY_MAXLEN] = { 0, };
338 retvm_if(!db, -1, "DB handler is NULL\n");
339 retvm_if(!ad, -1, "alarm data is NULL\n");
340 retvm_if(!MAGIC_VALUE_CHECK(ad->_magic), -1,
341 "alarm data is error, ad->_magic=%d,ALARM_DB_MAGIC_VALUE=%d\n",
342 ad->_magic, ALARM_DB_MAGIC_VALUE);
344 _make_qry_u_ad(query, sizeof(query), ad);
345 rc = _exec(db, query);
346 retv_if(rc == -1, rc);
352 int update_enable(sqlite3 *db, int id, bool enable)
355 char query[QUERY_MAXLEN] = { 0, };
357 retvm_if(db == NULL, -1, "DB handler is NULL\n");
358 _make_qry_u_enable(query, sizeof(query), id, enable);
359 rc = _exec(db, query);
360 retv_if(rc == -1, rc);
366 int update_snooze(sqlite3 *db, int id, bool enable)
369 char query[QUERY_MAXLEN] = { 0, };
371 retvm_if(db == NULL, -1, "DB handler is NULL\n");
373 _make_qry_u_snooze(query, sizeof(query), id, enable);
374 rc = _exec(db, query);
375 retv_if(rc == -1, rc);
381 int remove_data(sqlite3 *db, int id)
384 char query[QUERY_MAXLEN] = { 0, };
386 retvm_if(db == NULL, -1, "DB handler is NULL\n");
387 snprintf(query, sizeof(query), "delete from alarm where id = %d", id);
388 rc = _exec(db, query);
389 retv_if(rc == -1, rc);
395 int remove_data_all(sqlite3 *db)
398 char query[QUERY_MAXLEN] = { 0, };
400 retvm_if(db == NULL, -1, "DB handler is NULL\n");
402 snprintf(query, sizeof(query), "delete from alarm");
403 rc = _exec(db, query);
405 retv_if(rc == -1, rc);
411 int get_data(sqlite3 *db, int cid, struct alarm_data *ad)
414 //char query[QUERY_MAXLEN] = {0, };
416 retvm_if(db == NULL, -1, "DB handler is NULL\n");
417 retvm_if(cid < 1, -1, "Invalid argument: alarm id is NULL\n");
418 retvm_if(ad == NULL, -1, "alarm data is NULL\n");
420 rc = _get_ad(db, cid, ad);
421 retv_if(rc == -1, rc);
428 int get_data_by_author(sqlite3 *db, int cid, struct alarm_data *ad, char author)
431 // char query[QUERY_MAXLEN] = {0, };
433 retvm_if(db == NULL, -1, "DB handler is NULL\n");
434 retvm_if(cid < 1, -1, "Invalid argument: alarm id is NULL\n");
435 retvm_if(ad == NULL, -1, "alarm data is NULL\n");
437 rc = _get_ad_by_author(db, cid, ad, author);
438 retv_if(rc == -1, rc);
445 struct alarm_data_list *get_data_list_all(sqlite3 *db)
449 char query[QUERY_MAXLEN];
450 struct alarm_data_list *adl = NULL;
451 struct alarm_data_list *t;
452 struct alarm_data_list *first;
456 retvm_if(db == NULL, adl, "DB handler is NULL");
458 snprintf(query, sizeof(query), "select "
459 "id, magic, alarm_mgr_id, enable, missed, author, name, "
460 "stime, atime, etime, sdate, edate, timezone, "
461 "repeat_once, repeat_every, repeat_weekly, "
462 "snooze_enable, snooze_min, snooze_times, count, "
463 "type, tone, volume, auto_power_on " "from alarm order by id");
464 rc = sqlite3_prepare(db, query, -1, &stmt, NULL);
465 retvm_if(rc != SQLITE_OK, adl,
466 "DB: get_data_list_all(). Get data error");
467 retvm_if(stmt == NULL, adl, "DB: get_data_list_all(). Get data error");
469 rc = sqlite3_step(stmt);
470 while (rc == SQLITE_ROW) {
472 t = (struct alarm_data_list *)
473 malloc(sizeof(struct alarm_data_list));
474 retvm_if(!t, adl, "malloc null");
475 t->ad.id = INT(stmt, idx++);
476 t->ad._magic = INT(stmt, idx++);
477 t->ad.alarm_mgr_id = INT(stmt, idx++);
478 t->ad.enable = INT(stmt, idx++);
479 t->ad.missed = INT(stmt, idx++);
480 t->ad.author = INT(stmt, idx++);
481 strncpy(t->ad.name, _s(TEXT(stmt, idx++)),
482 sizeof(t->ad.name) - 1);
483 t->ad.stime = INT(stmt, idx++);
484 t->ad.atime = INT(stmt, idx++);
485 t->ad.etime = INT(stmt, idx++);
486 t->ad.sdate = INT(stmt, idx++);
487 t->ad.edate = INT(stmt, idx++);
488 strncpy(t->ad.timezone, _s(TEXT(stmt, idx++)),
489 sizeof(t->ad.timezone) - 1);
490 t->ad.repeat_once = INT(stmt, idx++);
491 t->ad.repeat_every = INT(stmt, idx++);
492 t->ad.repeat_weekly = INT(stmt, idx++);
493 t->ad.snooze_enable = INT(stmt, idx++);
494 t->ad.snooze_min = INT(stmt, idx++);
495 t->ad.snooze_times = INT(stmt, idx++);
496 t->ad.count = INT(stmt, idx++);
497 t->ad.type = INT(stmt, idx++);
498 strncpy(t->ad.tone, _s(TEXT(stmt, idx++)),
499 sizeof(t->ad.tone) - 1);
500 t->ad.volume = INT(stmt, idx++);
501 t->ad.auto_power_on = INT(stmt, idx++);
503 ADLIST_ADD_AFTER(adl, t);
504 rc = sqlite3_step(stmt);
506 rc = sqlite3_finalize(stmt);
508 ADLIST_GET_FIRST(adl, first);
513 struct alarm_data_list *get_data_list_by_author(sqlite3 *db, char author)
517 char query[QUERY_MAXLEN];
518 struct alarm_data_list *adl = NULL;
519 struct alarm_data_list *t;
520 struct alarm_data_list *first;
524 retvm_if(db == NULL, adl, "DB handler is NULL");
526 snprintf(query, sizeof(query), "select "
527 "id, magic, alarm_mgr_id, enable, missed, name, "
528 "stime, atime, etime, sdate, edate, timezone, "
529 "repeat_once, repeat_every, repeat_weekly, "
530 "snooze_enable, snooze_min, snooze_times, count, "
531 "type, tone, volume, auto_power_on "
532 "from alarm where author = %d order by id", author);
534 rc = sqlite3_prepare(db, query, -1, &stmt, NULL);
535 retvm_if(rc != SQLITE_OK, adl, "DB: Get data list error");
536 retvm_if(stmt == NULL, adl, "DB: Get data list error");
538 rc = sqlite3_step(stmt);
539 while (rc == SQLITE_ROW) {
541 t = (struct alarm_data_list *)
542 malloc(sizeof(struct alarm_data_list));
543 retvm_if(!t, adl, "malloc null");
544 t->ad.id = INT(stmt, idx++);
545 t->ad._magic = INT(stmt, idx++);
546 t->ad.alarm_mgr_id = INT(stmt, idx++);
547 t->ad.enable = INT(stmt, idx++);
548 t->ad.missed = INT(stmt, idx++);
549 strncpy(t->ad.name, _s(TEXT(stmt, idx++)),
550 sizeof(t->ad.name) - 1);
551 t->ad.stime = INT(stmt, idx++);
552 t->ad.atime = INT(stmt, idx++);
553 t->ad.etime = INT(stmt, idx++);
554 t->ad.sdate = INT(stmt, idx++);
555 t->ad.edate = INT(stmt, idx++);
556 strncpy(t->ad.timezone, _s(TEXT(stmt, idx++)),
557 sizeof(t->ad.timezone) - 1);
558 t->ad.repeat_once = INT(stmt, idx++);
559 t->ad.repeat_every = INT(stmt, idx++);
560 t->ad.repeat_weekly = INT(stmt, idx++);
561 t->ad.snooze_enable = INT(stmt, idx++);
562 t->ad.snooze_min = INT(stmt, idx++);
563 t->ad.snooze_times = INT(stmt, idx++);
564 t->ad.count = INT(stmt, idx++);
565 t->ad.type = INT(stmt, idx++);
566 strncpy(t->ad.tone, _s(TEXT(stmt, idx++)),
567 sizeof(t->ad.tone) - 1);
568 t->ad.volume = INT(stmt, idx++);
569 t->ad.auto_power_on = INT(stmt, idx++);
571 t->ad.author = author;
572 ADLIST_ADD_AFTER(adl, t);
573 rc = sqlite3_step(stmt);
575 rc = sqlite3_finalize(stmt);
576 ADLIST_GET_FIRST(adl, first);
581 int get_num_of_enable(sqlite3 *db)
585 char query[QUERY_MAXLEN] = { 0, };
586 int num_of_enable = 0;
588 snprintf(query, sizeof(query),
589 "select count(*) from alarm where enable = 1");
591 rc = sqlite3_prepare(db, query, -1, &stmt, NULL);
592 retvm_if(rc != SQLITE_OK, -1,
593 "DB: get_num_of_enable(). Get data error");
594 retvm_if(stmt == NULL, -1, "DB: get_num_of_enable(). Get data error");
596 rc = sqlite3_step(stmt);
597 if (rc == SQLITE_ROW)
598 num_of_enable = INT(stmt, 0);
599 rc = sqlite3_finalize(stmt);
601 return num_of_enable;
605 int get_last_id(sqlite3 *db)
607 return (int)sqlite3_last_insert_rowid(db);
611 int get_last_id_by_author(sqlite3 *db, char author)
615 char query[QUERY_MAXLEN] = { 0, };
618 snprintf(query, sizeof(query),
619 "select max(id) from alarm where author = %d", author);
621 rc = sqlite3_prepare(db, query, -1, &stmt, NULL);
622 retvm_if(rc != SQLITE_OK, -1,
623 "DB: get_last_id_by_author(). Get data error");
624 retvm_if(stmt == NULL, -1,
625 "DB: get_last_id_by_author(). Get data error");
627 rc = sqlite3_step(stmt);
628 if (rc == SQLITE_ROW)
630 rc = sqlite3_finalize(stmt);
636 int get_number_of_data_by_author(sqlite3 *db, char author)
640 char query[QUERY_MAXLEN] = { 0, };
643 snprintf(query, sizeof(query),
644 "select count(1) from alarm where author = %d", author);
646 rc = sqlite3_prepare(db, query, -1, &stmt, NULL);
647 retvm_if(rc != SQLITE_OK, -1,
648 "DB: get_last_id_by_author(). Get data error");
649 retvm_if(stmt == NULL, -1,
650 "DB: get_last_id_by_author(). Get data error");
652 rc = sqlite3_step(stmt);
653 if (rc == SQLITE_ROW)
655 rc = sqlite3_finalize(stmt);
661 int get_power_onoff_by_author(sqlite3 *db, char author)
665 char query[QUERY_MAXLEN] = { 0, };
668 snprintf(query, sizeof(query),
669 "select auto_power_on from alarm where author=%d", author);
671 rc = sqlite3_prepare(db, query, -1, &stmt, NULL);
672 retvm_if(rc != SQLITE_OK, -1,
673 "DB: get_poweron_by_author(). Get data error");
674 retvm_if(stmt == NULL, -1,
675 "DB: get_poweron_by_author(). Get data error");
677 rc = sqlite3_step(stmt);
678 if (rc == SQLITE_ROW)
680 rc = sqlite3_finalize(stmt);