3 * Copyright 2012 Samsung Electronics Co., Ltd
5 * Licensed under the Flora License, Version 1.0 (the "License");
6 * you may not use this file except in compliance with the License.
7 * You may obtain a copy of the License at
9 * http://floralicense.org/license/
11 * Unless required by applicable law or agreed to in writing, software
12 * distributed under the License is distributed on an "AS IS" BASIS,
13 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14 * See the License for the specific language governing permissions and
15 * limitations under the License.
18 #include "db-define.h"
19 #include "db-schema.h"
22 /**********************************************************************
23 ******************define, struct ,typedef, union, enum, global val *************************************
24 ***********************************************************************/
25 #define QUERY_MAXLEN (4096)
27 #define TEXT(s, n) (char *)sqlite3_column_text(s, n)
28 #define INT(s, n) sqlite3_column_int(s, n)
31 #define ADLIST_ADD_AFTER(place, elem) {\
32 if ((place) == NULL) {\
34 (place)->next = (place)->prev = NULL;\
36 (elem)->next = (place)->next;\
37 (elem)->prev = (place);\
38 (place)->next = (elem);\
39 if ((elem)->next != NULL)\
40 (elem)->next->prev = (elem);\
44 #define ADLIST_GET_FIRST(list, result) {\
45 if ((list) != NULL) {\
46 for ( ; (list)->prev != NULL; (list) = (list)->prev) ;\
50 /**********************************************************************
51 ******************Local function declear, extern function declear*************************************
52 ***********************************************************************/
54 /**********************************************************************
55 ******************Global val , static global val*************************************
56 ***********************************************************************/
58 static const char *empty_str = "";
59 /**********************************************************************
60 ******************Local function ref*************************************
61 ***********************************************************************/
64 static char *_s(char *str)
67 retv_if(str == NULL, (char *)empty_str);
78 static int _exec(sqlite3 * db, char * query)
83 retvm_if(db == NULL, -1, "DB handler is NULL");
85 rc = sqlite3_exec(db, query, NULL, 0, &errmsg);
86 if (rc != SQLITE_OK) {
87 DB_INFO("Query: [%s]", query);
88 DB_INFO_RED("SQL error: %s\n", errmsg);
96 static int _create_table(sqlite3 * db)
100 rc = _exec(db, CREATE_ALARM_TABLE);
101 retv_if(rc == -1, -1);
107 static int _get_ad(sqlite3 * db, int cid, struct alarm_data * ad)
110 char query[QUERY_MAXLEN] = { 0, };
114 snprintf(query, sizeof(query), "select "
115 "magic, alarm_mgr_id, enable, missed, author, name, "
116 "stime, atime, etime, sdate, edate, timezone, "
117 "repeat_once, repeat_every, repeat_weekly, "
118 "snooze_enable, snooze_min, snooze_times, count,"
119 "type, tone, volume, auto_power_on "
120 "from alarm where id = %d", cid);
121 rc = sqlite3_prepare(db, query, -1, &stmt, NULL);
122 retvm_if(rc != SQLITE_OK, -1,
123 "DB: SQLITE_OK _get_ad(). Get data error");
124 retvm_if(stmt == NULL, -1, "DB: _get_ad(). Get data error");
126 rc = sqlite3_step(stmt);
127 if (rc == SQLITE_ROW) {
129 ad->_magic = INT(stmt, idx++);
130 ad->alarm_mgr_id = INT(stmt, idx++);
131 ad->enable = INT(stmt, idx++);
132 ad->missed = INT(stmt, idx++);
133 ad->author = INT(stmt, idx++);
134 strncpy(ad->name, _s(TEXT(stmt, idx++)), sizeof(ad->name) - 1);
135 ad->stime = INT(stmt, idx++);
136 ad->atime = INT(stmt, idx++);
137 ad->etime = INT(stmt, idx++);
138 ad->sdate = INT(stmt, idx++);
139 ad->edate = INT(stmt, idx++);
140 strncpy(ad->timezone, _s(TEXT(stmt, idx++)),
141 sizeof(ad->timezone) - 1);
143 ad->repeat_once = INT(stmt, idx++);
144 ad->repeat_every = INT(stmt, idx++);
145 ad->repeat_weekly = INT(stmt, idx++);
147 ad->snooze_enable = INT(stmt, idx++);
148 ad->snooze_min = INT(stmt, idx++);
149 ad->snooze_times = INT(stmt, idx++);
150 ad->count = INT(stmt, idx++);
152 ad->type = INT(stmt, idx++);
153 strncpy(ad->tone, _s(TEXT(stmt, idx++)), sizeof(ad->tone) - 1);
154 ad->volume = INT(stmt, idx++);
155 ad->auto_power_on = INT(stmt, idx++);
157 retvm_if(1, -1, "Alarm data %d does not exist", cid);
159 rc = sqlite3_finalize(stmt);
164 static int _get_ad_by_author(sqlite3 * db, int cid, struct alarm_data * ad,
168 char query[QUERY_MAXLEN] = { 0, };
172 snprintf(query, sizeof(query), "select "
173 "magic, alarm_mgr_id, enable, missed, author, name, "
174 "stime, atime, etime, sdate, edate, timezone, "
175 "repeat_once, repeat_every, repeat_weekly, "
176 "snooze_enable, snooze_min, snooze_times, count,"
177 "type, tone, volume, auto_power_on "
178 "from alarm where id = ( select id = %d from alarm where author = %d)",
180 rc = sqlite3_prepare(db, query, -1, &stmt, NULL);
181 retvm_if(rc != SQLITE_OK, -1,
182 "DB: SQLITE_OK _get_ad_by_author(). Get data error");
183 retvm_if(stmt == NULL, -1, "DB: _get_ad_by_author(). Get data error");
185 rc = sqlite3_step(stmt);
186 if (rc == SQLITE_ROW) {
188 ad->_magic = INT(stmt, idx++);
189 ad->alarm_mgr_id = INT(stmt, idx++);
190 ad->enable = INT(stmt, idx++);
191 ad->missed = INT(stmt, idx++);
192 ad->author = INT(stmt, idx++);
193 strncpy(ad->name, _s(TEXT(stmt, idx++)), sizeof(ad->name) - 1);
194 ad->stime = INT(stmt, idx++);
195 ad->atime = INT(stmt, idx++);
196 ad->etime = INT(stmt, idx++);
197 ad->sdate = INT(stmt, idx++);
198 ad->edate = INT(stmt, idx++);
199 strncpy(ad->timezone, _s(TEXT(stmt, idx++)),
200 sizeof(ad->timezone) - 1);
202 ad->repeat_once = INT(stmt, idx++);
203 ad->repeat_every = INT(stmt, idx++);
204 ad->repeat_weekly = INT(stmt, idx++);
206 ad->snooze_enable = INT(stmt, idx++);
207 ad->snooze_min = INT(stmt, idx++);
208 ad->snooze_times = INT(stmt, idx++);
209 ad->count = INT(stmt, idx++);
211 ad->type = INT(stmt, idx++);
212 strncpy(ad->tone, _s(TEXT(stmt, idx++)), sizeof(ad->tone) - 1);
213 ad->volume = INT(stmt, idx++);
214 ad->auto_power_on = INT(stmt, idx++);
216 retvm_if(1, -1, "Alarm data %d does not exist", cid);
218 rc = sqlite3_finalize(stmt);
223 static inline void _make_qry_i_ad(char *query, int len, struct alarm_data *ad)
225 snprintf(query, len, "insert into alarm ("
226 "magic, alarm_mgr_id, enable, missed, author, name, "
227 "stime, atime, etime, sdate, edate, timezone, "
228 "repeat_once, repeat_every, repeat_weekly, "
229 "snooze_enable, snooze_min, snooze_times, count,"
230 "type, tone, volume, auto_power_on) values ( "
231 "\"%d\", \"%d\", \"%d\", \"%d\", \"%d\", \"%s\", "
232 "\"%d\", \"%d\", \"%d\", \"%d\", \"%d\", \"%s\", "
233 "\"%d\", \"%d\", \"%d\", "
234 "\"%d\", \"%d\", \"%d\", \"%d\","
235 "\"%d\", \"%s\", \"%d\", \"%d\")",
236 ad->_magic, ad->alarm_mgr_id, ad->enable, ad->missed,
237 ad->author, _s(ad->name), (int)ad->stime, (int)ad->atime,
238 (int)ad->etime, (int)ad->sdate, (int)ad->edate,
239 _s(ad->timezone), ad->repeat_once, ad->repeat_every,
240 ad->repeat_weekly, ad->snooze_enable, ad->snooze_min,
241 ad->snooze_times, ad->count, ad->type, _s(ad->tone),
242 ad->volume, ad->auto_power_on);
246 static inline void _make_qry_u_ad(char *query, int len, struct alarm_data *ad)
248 snprintf(query, len, "update alarm set "
249 "alarm_mgr_id = \"%d\", enable = \"%d\", missed = \"%d\",author = \"%d\", name = \"%s\", "
250 "stime = \"%d\", atime = \"%d\", etime = \"%d\", sdate = \"%d\", edate = \"%d\", timezone = \"%s\", "
251 "repeat_once = \"%d\", repeat_every = \"%d\", repeat_weekly = \"%d\", "
252 "snooze_enable = \"%d\", snooze_min = \"%d\", snooze_times = \"%d\", count = \"%d\","
253 "type = \"%d\", tone = \"%s\", volume = \"%d\", auto_power_on = \"%d\" "
255 ad->alarm_mgr_id, ad->enable, ad->missed, ad->author,
256 _s(ad->name), (int)ad->stime, (int)ad->atime, (int)ad->etime,
257 (int)ad->sdate, (int)ad->edate, _s(ad->timezone),
258 ad->repeat_once, ad->repeat_every, ad->repeat_weekly,
259 ad->snooze_enable, ad->snooze_min, ad->snooze_times, ad->count,
260 ad->type, _s(ad->tone), ad->volume, ad->auto_power_on, ad->id);
264 static inline void _make_qry_u_enable(char *query, int len, int id, bool enable)
266 snprintf(query, len, "update alarm set "
267 "enable = \"%d\" where id = %d", enable, id);
271 static inline void _make_qry_u_snooze(char *query, int len, int id, bool enable)
273 snprintf(query, len, "update alarm set "
274 "snooze_enable = \"%d\" where id = %d", enable, id);
277 /**********************************************************************
278 ******************Global function ref*************************************
279 ***********************************************************************/
282 sqlite3 *db_init(char *root)
287 rc = db_util_open(root, &db, 0); // sqlite3_open(root, &db);
288 printf("db_util_open, rc=%d\n", rc);
290 DB_INFO_RED("Can't open database: %s", sqlite3_errmsg(db));
291 db_util_close(db); // sqlite3_close(db);
295 rc = _create_table(db);
296 printf("_create_table, rc=%d\n", rc);
298 DB_INFO_RED("Can't create table: %s", sqlite3_errmsg(db));
299 db_util_close(db); // sqlite3_close(db);
306 void db_fini(sqlite3 *db)
309 db_util_close(db); // sqlite3_close(db);
314 int insert_data(sqlite3 *db, struct alarm_data *ad)
317 char query[QUERY_MAXLEN] = { 0, };
319 retvm_if(!db, -1, "DB handler is NULL\n");
320 retvm_if(!ad, -1, "alarm data is NULL\n");
321 retvm_if(!MAGIC_VALUE_CHECK(ad->_magic), -1,
322 "alarm data is error, ad->_magic=%d,ALARM_DB_MAGIC_VALUE=%d\n",
323 ad->_magic, ALARM_DB_MAGIC_VALUE);
325 _make_qry_i_ad(query, sizeof(query), ad);
326 rc = _exec(db, query);
327 retv_if(rc == -1, rc);
329 ad->id = (int)sqlite3_last_insert_rowid(db);
334 int update_data(sqlite3 *db, struct alarm_data *ad)
337 char query[QUERY_MAXLEN] = { 0, };
339 retvm_if(!db, -1, "DB handler is NULL\n");
340 retvm_if(!ad, -1, "alarm data is NULL\n");
341 retvm_if(!MAGIC_VALUE_CHECK(ad->_magic), -1,
342 "alarm data is error, ad->_magic=%d,ALARM_DB_MAGIC_VALUE=%d\n",
343 ad->_magic, ALARM_DB_MAGIC_VALUE);
345 _make_qry_u_ad(query, sizeof(query), ad);
346 rc = _exec(db, query);
347 retv_if(rc == -1, rc);
353 int update_enable(sqlite3 *db, int id, bool enable)
356 char query[QUERY_MAXLEN] = { 0, };
358 retvm_if(db == NULL, -1, "DB handler is NULL\n");
359 _make_qry_u_enable(query, sizeof(query), id, enable);
360 rc = _exec(db, query);
361 retv_if(rc == -1, rc);
367 int update_snooze(sqlite3 *db, int id, bool enable)
370 char query[QUERY_MAXLEN] = { 0, };
372 retvm_if(db == NULL, -1, "DB handler is NULL\n");
374 _make_qry_u_snooze(query, sizeof(query), id, enable);
375 rc = _exec(db, query);
376 retv_if(rc == -1, rc);
382 int remove_data(sqlite3 *db, int id)
385 char query[QUERY_MAXLEN] = { 0, };
387 retvm_if(db == NULL, -1, "DB handler is NULL\n");
388 snprintf(query, sizeof(query), "delete from alarm where id = %d", id);
389 rc = _exec(db, query);
390 retv_if(rc == -1, rc);
396 int remove_data_all(sqlite3 *db)
399 char query[QUERY_MAXLEN] = { 0, };
401 retvm_if(db == NULL, -1, "DB handler is NULL\n");
403 snprintf(query, sizeof(query), "delete from alarm");
404 rc = _exec(db, query);
406 retv_if(rc == -1, rc);
412 int get_data(sqlite3 *db, int cid, struct alarm_data *ad)
415 //char query[QUERY_MAXLEN] = {0, };
417 retvm_if(db == NULL, -1, "DB handler is NULL\n");
418 retvm_if(cid < 1, -1, "Invalid argument: alarm id is NULL\n");
419 retvm_if(ad == NULL, -1, "alarm data is NULL\n");
421 rc = _get_ad(db, cid, ad);
422 retv_if(rc == -1, rc);
429 int get_data_by_author(sqlite3 *db, int cid, struct alarm_data *ad, char author)
432 // char query[QUERY_MAXLEN] = {0, };
434 retvm_if(db == NULL, -1, "DB handler is NULL\n");
435 retvm_if(cid < 1, -1, "Invalid argument: alarm id is NULL\n");
436 retvm_if(ad == NULL, -1, "alarm data is NULL\n");
438 rc = _get_ad_by_author(db, cid, ad, author);
439 retv_if(rc == -1, rc);
446 struct alarm_data_list *get_data_list_all(sqlite3 *db)
450 char query[QUERY_MAXLEN];
451 struct alarm_data_list *adl = NULL;
452 struct alarm_data_list *t;
453 struct alarm_data_list *first;
457 retvm_if(db == NULL, adl, "DB handler is NULL");
459 snprintf(query, sizeof(query), "select "
460 "id, magic, alarm_mgr_id, enable, missed, author, name, "
461 "stime, atime, etime, sdate, edate, timezone, "
462 "repeat_once, repeat_every, repeat_weekly, "
463 "snooze_enable, snooze_min, snooze_times, count, "
464 "type, tone, volume, auto_power_on " "from alarm order by id");
465 rc = sqlite3_prepare(db, query, -1, &stmt, NULL);
466 retvm_if(rc != SQLITE_OK, adl,
467 "DB: get_data_list_all(). Get data error");
468 retvm_if(stmt == NULL, adl, "DB: get_data_list_all(). Get data error");
470 rc = sqlite3_step(stmt);
471 while (rc == SQLITE_ROW) {
473 t = (struct alarm_data_list *)
474 malloc(sizeof(struct alarm_data_list));
475 retvm_if(!t, adl, "malloc null");
476 t->ad.id = INT(stmt, idx++);
477 t->ad._magic = INT(stmt, idx++);
478 t->ad.alarm_mgr_id = INT(stmt, idx++);
479 t->ad.enable = INT(stmt, idx++);
480 t->ad.missed = INT(stmt, idx++);
481 t->ad.author = INT(stmt, idx++);
482 strncpy(t->ad.name, _s(TEXT(stmt, idx++)),
483 sizeof(t->ad.name) - 1);
484 t->ad.stime = INT(stmt, idx++);
485 t->ad.atime = INT(stmt, idx++);
486 t->ad.etime = INT(stmt, idx++);
487 t->ad.sdate = INT(stmt, idx++);
488 t->ad.edate = INT(stmt, idx++);
489 strncpy(t->ad.timezone, _s(TEXT(stmt, idx++)),
490 sizeof(t->ad.timezone) - 1);
491 t->ad.repeat_once = INT(stmt, idx++);
492 t->ad.repeat_every = INT(stmt, idx++);
493 t->ad.repeat_weekly = INT(stmt, idx++);
494 t->ad.snooze_enable = INT(stmt, idx++);
495 t->ad.snooze_min = INT(stmt, idx++);
496 t->ad.snooze_times = INT(stmt, idx++);
497 t->ad.count = INT(stmt, idx++);
498 t->ad.type = INT(stmt, idx++);
499 strncpy(t->ad.tone, _s(TEXT(stmt, idx++)),
500 sizeof(t->ad.tone) - 1);
501 t->ad.volume = INT(stmt, idx++);
502 t->ad.auto_power_on = INT(stmt, idx++);
504 ADLIST_ADD_AFTER(adl, t);
505 rc = sqlite3_step(stmt);
507 rc = sqlite3_finalize(stmt);
509 ADLIST_GET_FIRST(adl, first);
514 struct alarm_data_list *get_data_list_by_author(sqlite3 *db, char author)
518 char query[QUERY_MAXLEN];
519 struct alarm_data_list *adl = NULL;
520 struct alarm_data_list *t;
521 struct alarm_data_list *first;
525 retvm_if(db == NULL, adl, "DB handler is NULL");
527 snprintf(query, sizeof(query), "select "
528 "id, magic, alarm_mgr_id, enable, missed, name, "
529 "stime, atime, etime, sdate, edate, timezone, "
530 "repeat_once, repeat_every, repeat_weekly, "
531 "snooze_enable, snooze_min, snooze_times, count, "
532 "type, tone, volume, auto_power_on "
533 "from alarm where author = %d order by id", author);
535 rc = sqlite3_prepare(db, query, -1, &stmt, NULL);
536 retvm_if(rc != SQLITE_OK, adl, "DB: Get data list error");
537 retvm_if(stmt == NULL, adl, "DB: Get data list error");
539 rc = sqlite3_step(stmt);
540 while (rc == SQLITE_ROW) {
542 t = (struct alarm_data_list *)
543 malloc(sizeof(struct alarm_data_list));
544 retvm_if(!t, adl, "malloc null");
545 t->ad.id = INT(stmt, idx++);
546 t->ad._magic = INT(stmt, idx++);
547 t->ad.alarm_mgr_id = INT(stmt, idx++);
548 t->ad.enable = INT(stmt, idx++);
549 t->ad.missed = INT(stmt, idx++);
550 strncpy(t->ad.name, _s(TEXT(stmt, idx++)),
551 sizeof(t->ad.name) - 1);
552 t->ad.stime = INT(stmt, idx++);
553 t->ad.atime = INT(stmt, idx++);
554 t->ad.etime = INT(stmt, idx++);
555 t->ad.sdate = INT(stmt, idx++);
556 t->ad.edate = INT(stmt, idx++);
557 strncpy(t->ad.timezone, _s(TEXT(stmt, idx++)),
558 sizeof(t->ad.timezone) - 1);
559 t->ad.repeat_once = INT(stmt, idx++);
560 t->ad.repeat_every = INT(stmt, idx++);
561 t->ad.repeat_weekly = INT(stmt, idx++);
562 t->ad.snooze_enable = INT(stmt, idx++);
563 t->ad.snooze_min = INT(stmt, idx++);
564 t->ad.snooze_times = INT(stmt, idx++);
565 t->ad.count = INT(stmt, idx++);
566 t->ad.type = INT(stmt, idx++);
567 strncpy(t->ad.tone, _s(TEXT(stmt, idx++)),
568 sizeof(t->ad.tone) - 1);
569 t->ad.volume = INT(stmt, idx++);
570 t->ad.auto_power_on = INT(stmt, idx++);
572 t->ad.author = author;
573 ADLIST_ADD_AFTER(adl, t);
574 rc = sqlite3_step(stmt);
576 rc = sqlite3_finalize(stmt);
577 ADLIST_GET_FIRST(adl, first);
582 int get_num_of_enable(sqlite3 *db)
586 char query[QUERY_MAXLEN] = { 0, };
587 int num_of_enable = 0;
589 snprintf(query, sizeof(query),
590 "select count(*) from alarm where enable = 1");
592 rc = sqlite3_prepare(db, query, -1, &stmt, NULL);
593 retvm_if(rc != SQLITE_OK, -1,
594 "DB: get_num_of_enable(). Get data error");
595 retvm_if(stmt == NULL, -1, "DB: get_num_of_enable(). Get data error");
597 rc = sqlite3_step(stmt);
598 if (rc == SQLITE_ROW)
599 num_of_enable = INT(stmt, 0);
600 rc = sqlite3_finalize(stmt);
602 return num_of_enable;
606 int get_last_id(sqlite3 *db)
608 return (int)sqlite3_last_insert_rowid(db);
612 int get_last_id_by_author(sqlite3 *db, char author)
616 char query[QUERY_MAXLEN] = { 0, };
619 snprintf(query, sizeof(query),
620 "select max(id) from alarm where author = %d", author);
622 rc = sqlite3_prepare(db, query, -1, &stmt, NULL);
623 retvm_if(rc != SQLITE_OK, -1,
624 "DB: get_last_id_by_author(). Get data error");
625 retvm_if(stmt == NULL, -1,
626 "DB: get_last_id_by_author(). Get data error");
628 rc = sqlite3_step(stmt);
629 if (rc == SQLITE_ROW)
631 rc = sqlite3_finalize(stmt);
637 int get_number_of_data_by_author(sqlite3 *db, char author)
641 char query[QUERY_MAXLEN] = { 0, };
644 snprintf(query, sizeof(query),
645 "select count(1) from alarm where author = %d", author);
647 rc = sqlite3_prepare(db, query, -1, &stmt, NULL);
648 retvm_if(rc != SQLITE_OK, -1,
649 "DB: get_last_id_by_author(). Get data error");
650 retvm_if(stmt == NULL, -1,
651 "DB: get_last_id_by_author(). Get data error");
653 rc = sqlite3_step(stmt);
654 if (rc == SQLITE_ROW)
656 rc = sqlite3_finalize(stmt);
662 int get_power_onoff_by_author(sqlite3 *db, char author)
666 char query[QUERY_MAXLEN] = { 0, };
669 snprintf(query, sizeof(query),
670 "select auto_power_on from alarm where author=%d", author);
672 rc = sqlite3_prepare(db, query, -1, &stmt, NULL);
673 retvm_if(rc != SQLITE_OK, -1,
674 "DB: get_poweron_by_author(). Get data error");
675 retvm_if(stmt == NULL, -1,
676 "DB: get_poweron_by_author(). Get data error");
678 rc = sqlite3_step(stmt);
679 if (rc == SQLITE_ROW)
681 rc = sqlite3_finalize(stmt);