provider/download-provider-db.c

   1 /*
   2  * Copyright (c) 2013 Samsung Electronics Co., Ltd All Rights Reserved
   3  *
   4  * Licensed under the Apache License, Version 2.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
   7  *
   8  * http://www.apache.org/licenses/LICENSE-2.0
   9  *
  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.
  15  */
  16
  17 //#include <string.h>
  18 //#include <errno.h>
  19 #include <stdio.h>
  20 #include <stdlib.h> // alloc
  21 //#include <unistd.h> // unlink
  22
  23 #include <sqlite3.h>
  24
  25 #include "download-provider.h"
  26 #include "download-provider-db-defs.h"
  27 #include "download-provider-db.h"
  28 #include "download-provider-log.h"
  29 #include "download-provider-utils.h"
  30
  31
  32 static void __basic_property(sqlite3 *handle)
  33 {
  34         if (sqlite3_exec(handle, "PRAGMA journal_mode=PERSIST;", 0, 0, 0) != SQLITE_OK)
  35                 TRACE_ERROR("check property journal_mode:PERSIST");
  36         if (sqlite3_exec(handle, "PRAGMA foreign_keys=ON;", 0, 0, 0) != SQLITE_OK)
  37                 TRACE_ERROR("check property foreign_keys:ON");
  38 }
  39
  40 static void __dp_finalize(sqlite3_stmt *stmt)
  41 {
  42         if (stmt != 0) {
  43                 if (sqlite3_finalize(stmt) != SQLITE_OK) {
  44                         sqlite3 *handle = sqlite3_db_handle(stmt);
  45                         TRACE_ERROR("sqlite3_finalize:%s", sqlite3_errmsg(handle));
  46                 }
  47         }
  48 }
  49
  50 static int __check_integrity(sqlite3 *handle)
  51 {
  52         sqlite3_stmt *stmt = NULL;
  53
  54         if (handle == 0) {
  55                 TRACE_ERROR("failed to check handle");
  56                 return -1;
  57         }
  58
  59         TRACE_INFO("");
  60         int ret = sqlite3_prepare_v2(handle, "PRAGMA integrity_check", -1, &stmt, NULL);
  61         if (ret != SQLITE_OK && ret != SQLITE_BUSY) {
  62                 TRACE_ERROR("failed to check integrity:%s", sqlite3_errmsg(handle));
  63                 return -1;
  64         } else {
  65                 ret = sqlite3_step(stmt);
  66                 if (ret == SQLITE_ROW) {
  67                         const char *ret_val = (const char *)sqlite3_column_text(stmt, 0);
  68                         TRACE_INFO("ret_val: %s", ret_val);
  69                         if (ret_val && strcmp(ret_val, "ok") == 0) {
  70                                 return 0;
  71                         } else {
  72                                 TRACE_ERROR("failed to check integrity");
  73                                 return -1;
  74                         }
  75                 } else {
  76                         return 0;
  77                 }
  78         }
  79         return 0;
  80 }
  81
  82 static int __check_table(sqlite3 *handle, char *table)
  83 {
  84         sqlite3_stmt *stmt = NULL;
  85
  86         if (handle == 0 || table == NULL) {
  87                 TRACE_ERROR("check handle or table:%s", table);
  88                 return -1;
  89         }
  90
  91         char *query = sqlite3_mprintf("SELECT name FROM sqlite_master WHERE type='table' AND name='%s'", table);
  92         if (query == NULL) {
  93                 TRACE_ERROR("failed to make query statement");
  94                 return -1;
  95         }
  96         int ret = sqlite3_prepare_v2(handle, query, -1, &stmt, NULL);
  97         sqlite3_free(query);
  98         int result = 0;
  99         if (ret != SQLITE_OK) {
 100                 TRACE_ERROR("sqlite3_prepare:%s", sqlite3_errmsg(handle));
 101                 result = -1;
 102         }
 103         if (result == 0 && sqlite3_step(stmt) != SQLITE_ROW) {
 104                 TRACE_DEBUG("not found table:%s", table);
 105                 result = -1;
 106         }
 107         __dp_finalize(stmt);
 108         return result;
 109 }
 110
 111 static int __rebuild_client_tables(sqlite3 *handle)
 112 {
 113         int ret = SQLITE_OK;
 114
 115         if (__check_table(handle, DP_TABLE_LOGGING) < 0) {
 116                 ret = sqlite3_exec(handle, DP_SCHEMA_LOGGING, 0, 0, 0);
 117                 if (ret == SQLITE_OK)
 118                         ret = sqlite3_exec(handle, DP_SCHEMA_LOGGING_INDEX, 0, 0, 0);
 119         }
 120         if (ret == SQLITE_OK && __check_table(handle, DP_TABLE_DOWNLOAD) < 0)
 121                 ret = sqlite3_exec(handle, DP_SCHEMA_DOWNLOAD, 0, 0, 0);
 122         if (ret == SQLITE_OK && __check_table(handle, DP_TABLE_REQUEST) < 0)
 123                 ret = sqlite3_exec(handle, DP_SCHEMA_REQUEST, 0, 0, 0);
 124         if (ret == SQLITE_OK && __check_table(handle, DP_TABLE_HEADERS) < 0)
 125                 ret = sqlite3_exec(handle, DP_SCHEMA_HEADER, 0, 0, 0);
 126         if (ret == SQLITE_OK && __check_table(handle, DP_TABLE_NOTIFICATION) < 0)
 127                 ret = sqlite3_exec(handle, DP_SCHEMA_NOTIFICATION, 0, 0, 0);
 128         if (ret != SQLITE_OK) {
 129                 TRACE_ERROR("create tables:%d error:%s", ret, sqlite3_errmsg(handle));
 130                 return -1;
 131         }
 132         return 0;
 133 }
 134
 135 static int __rebuild_client_manager_tables(sqlite3 *handle)
 136 {
 137         int ret = SQLITE_OK;
 138         if (__check_table(handle, DP_TABLE_CLIENTS) < 0)
 139                 ret = sqlite3_exec(handle, DP_SCHEMA_CLIENTS, 0, 0, 0);
 140         if (ret != SQLITE_OK) {
 141                 TRACE_ERROR("create tables:%d error:%s", ret, sqlite3_errmsg(handle));
 142                 return -1;
 143         }
 144         return 0;
 145 }
 146
 147 static int __db_open(sqlite3 **handle, char *database, int *errorcode)
 148 {
 149         if (sqlite3_open_v2(database, handle, SQLITE_OPEN_READWRITE, NULL) != SQLITE_OK) {
 150                 int sqlerrcode = sqlite3_errcode(*handle);
 151                 TRACE_ERROR("database open error(%d):%s", sqlerrcode, sqlite3_errmsg(*handle));
 152                 if (sqlerrcode == SQLITE_CORRUPT) { // remove & re-create
 153                         TRACE_SECURE_INFO("unlink [%s]", database);
 154                         unlink(database);
 155                         sqlerrcode = SQLITE_CANTOPEN;
 156                 }
 157                 if (sqlerrcode == SQLITE_CANTOPEN) {
 158                         *handle = 0;
 159                         // create empty database
 160                         if (sqlite3_open(database, handle) != SQLITE_OK) {
 161                                 sqlerrcode = sqlite3_errcode(*handle);
 162                                 TRACE_SECURE_ERROR("failed to create %s error:%d", database, sqlerrcode);
 163                                 TRACE_ERROR("database new error(%d):%s", sqlerrcode, sqlite3_errmsg(*handle));
 164                                 if (sqlerrcode == SQLITE_FULL || sqlerrcode == SQLITE_CANTOPEN) { // can not create temporary file for connection
 165                                         *errorcode = DP_ERROR_NO_SPACE; // DP_ERROR_OUT_OF_MEMORY
 166                                 } else {
 167                                         *errorcode = DP_ERROR_DISK_BUSY;
 168                                         unlink(database);
 169                                 }
 170                                 *handle = 0;
 171                                 return -1;
 172                         }
 173                 } else {
 174                         TRACE_ERROR("can not handle this error(%d):%s", sqlerrcode, sqlite3_errmsg(*handle));
 175                         *errorcode = DP_ERROR_OUT_OF_MEMORY;
 176                         *handle = 0;
 177                         return -1;
 178                 }
 179         }
 180         __basic_property(*handle);
 181         return 0;
 182 }
 183
 184 int dp_db_check_connection(void *handle)
 185 {
 186         if (handle == 0) {
 187                 TRACE_ERROR("connection handler is null");
 188                 return -1;
 189         }
 190         int phighwater = 0;
 191         int pcur = 0;
 192         int ret = sqlite3_db_status(handle, SQLITE_DBSTATUS_STMT_USED, &pcur, &phighwater, 0);
 193         if (ret != SQLITE_OK) {
 194                 TRACE_INFO("sql(%p) error:%d, used memory:%d, highwater:%d", handle, ret, pcur, phighwater);
 195                 return -1;
 196         }
 197         return 0;
 198 }
 199
 200 int dp_db_open_client_manager(void **handle, int *errorcode)
 201 {
 202         if (*handle == 0) {
 203                 char *database = sqlite3_mprintf("%s/%s", DATABASE_DIR, DP_DBFILE_CLIENTS);
 204                 if (database == NULL) {
 205                         TRACE_ERROR("failed to make clients database file path");
 206                         *errorcode = DP_ERROR_OUT_OF_MEMORY;
 207                         return -1;
 208                 }
 209                 if (__db_open((sqlite3 **)handle, database, errorcode) < 0) {
 210                         TRACE_ERROR("failed to open clients database file");
 211                         *handle = 0;
 212                 } else {
 213                         if (__check_integrity(*handle) < 0) {
 214                                 // recovery database file
 215                                 TRACE_ERROR("Failed to check integrity: %s", database);
 216                                 unlink(database);
 217                                 *handle = 0;
 218                                 if (__db_open((sqlite3 **)handle, database, errorcode) < 0) {
 219                                         TRACE_ERROR("failed to open clients database file");
 220                                         *handle = 0;
 221                                 }
 222                         }
 223
 224                         // whenever open new handle, check all tables. it's simple
 225                         if (*handle && __rebuild_client_manager_tables(*handle) < 0) {
 226                                 *errorcode = DP_ERROR_NO_SPACE;
 227                                 dp_db_close(*handle);
 228                                 *handle = 0;
 229                         }
 230                 }
 231                 sqlite3_free(database);
 232         }
 233         return *handle ? 0 : -1;
 234 }
 235
 236 static char *__dp_db_get_client_db_path(char *pkgname)
 237 {
 238         if (pkgname == NULL)
 239                 return NULL;
 240         return sqlite3_mprintf("%s/clients/.%s", DATABASE_DIR, pkgname);
 241 }
 242
 243 // 0 : remove, -1: error or skip by diff_time
 244 int dp_db_remove_database(char *pkgname, long now_time, long diff_time)
 245 {
 246         // get file name
 247         char *database = __dp_db_get_client_db_path(pkgname);
 248         if (database == NULL) {
 249                 TRACE_ERROR("failed to make db file path");
 250                 return -1;
 251         }
 252         int result = -1;
 253         // get modified time of database file.
 254         long modified_time = dp_get_file_modified_time(database);
 255         if (modified_time >= now_time) {
 256                 TRACE_ERROR("check system timezone %ld vs %ld", modified_time, now_time);
 257         } else if ((now_time - modified_time) > diff_time) {
 258                 char *database_journal = sqlite3_mprintf("%s-journal", database);
 259                 if (database_journal == NULL) {
 260                         TRACE_ERROR("failed to make db journal file path");
 261                 } else {
 262                         if (dp_remove_file(database_journal) < 0) {
 263                                 TRACE_ERROR("failed to remove db journal file path");
 264                         } else {
 265                                 if (dp_remove_file(database) < 0)
 266                                         TRACE_ERROR("failed to remove db file path");
 267                                 else
 268                                         result = 0;
 269                         }
 270                         sqlite3_free(database_journal);
 271                 }
 272         }
 273         sqlite3_free(database);
 274         return result;
 275 }
 276
 277 int dp_db_open_client_v2(void **handle, char *pkgname)
 278 {
 279         char *database = __dp_db_get_client_db_path(pkgname);
 280         if (database == NULL) {
 281                 TRACE_ERROR("failed to make db file path");
 282                 return -1;
 283         }
 284         if (sqlite3_open_v2(database, (sqlite3 **)handle, SQLITE_OPEN_READWRITE, NULL) != SQLITE_OK) {
 285                 int errorcode = sqlite3_errcode(*handle);
 286                 TRACE_ERROR("error(%d):%s", errorcode, sqlite3_errmsg(*handle));
 287                 *handle = 0;
 288                 sqlite3_free(database);
 289                 return -1;
 290         }
 291         sqlite3_free(database);
 292         __basic_property(*handle);
 293         return 0;
 294 }
 295
 296 int dp_db_open_client(void **handle, char *pkgname, int *errorcode)
 297 {
 298         if (*handle == 0) {
 299                 char *database = __dp_db_get_client_db_path(pkgname);
 300                 if (database == NULL) {
 301                         TRACE_ERROR("failed to make db file path");
 302                         *errorcode = DP_ERROR_OUT_OF_MEMORY;
 303                         return -1;
 304                 }
 305                 if (__db_open((sqlite3 **)handle, database, errorcode) < 0) {
 306                         TRACE_SECURE_ERROR("failed to open %s", database);
 307                         *handle = 0;
 308                 } else {
 309                         // whenever open new handle, check all tables. it's simple
 310                         if (__rebuild_client_tables(*handle) < 0) {
 311                                 *errorcode = DP_ERROR_NO_SPACE;
 312                                 dp_db_close(*handle);
 313                                 *handle = 0;
 314                         }
 315                 }
 316                 sqlite3_free(database);
 317         }
 318         return *handle ? 0 : -1;
 319 }
 320
 321 void dp_db_close(void *handle)
 322 {
 323         if (handle != 0) {
 324                 // remove empty page of db
 325                 //sqlite3_exec(handle, "VACUUM;", 0, 0, 0);
 326                 if (sqlite3_close((sqlite3 *)handle) != SQLITE_OK)
 327                         TRACE_ERROR("check sqlite close");
 328         }
 329 }
 330
 331 void dp_db_reset(void *stmt)
 332 {
 333         if (stmt != 0) {
 334                 sqlite3_stmt *stmtp = stmt;
 335                 sqlite3_clear_bindings(stmtp);
 336                 if (sqlite3_reset(stmtp) != SQLITE_OK) {
 337                         sqlite3 *handle = sqlite3_db_handle(stmtp);
 338                         TRACE_ERROR("reset:%s", sqlite3_errmsg(handle));
 339                 }
 340         }
 341 }
 342
 343 void dp_db_finalize(void *stmt)
 344 {
 345         __dp_finalize((sqlite3_stmt *)stmt);
 346 }
 347
 348 int dp_db_get_errorcode(void *handle)
 349 {
 350         if (handle == 0) {
 351                 TRACE_ERROR("check connection handle");
 352                 return DP_ERROR_DISK_BUSY;
 353         }
 354         int errorcode = sqlite3_errcode((sqlite3 *)handle);
 355         if (errorcode == SQLITE_FULL) {
 356                 TRACE_ERROR("SQLITE_FULL-NO_SPACE");
 357                 return DP_ERROR_NO_SPACE;
 358         } else if (errorcode == SQLITE_TOOBIG ||
 359                         errorcode == SQLITE_LOCKED || errorcode == SQLITE_BUSY) {
 360                 TRACE_ERROR("DISK_BUSY %s", sqlite3_errmsg((sqlite3 *)handle));
 361                 return DP_ERROR_DISK_BUSY;
 362         }
 363         return DP_ERROR_NONE;
 364 }
 365
 366
 367 #define DP_DB_PARAM_NULL_CHECK do {\
 368         if (handle == 0) {\
 369                 TRACE_ERROR("check connection handle");\
 370                 return -1;\
 371         } \
 372 } while (0)
 373
 374 #define DP_DB_BUFFER_NULL_CHECK(buffer) do {\
 375         if (buffer == NULL) {\
 376                 TRACE_ERROR("check available memory");\
 377                 return -1;\
 378         } \
 379 } while (0)
 380
 381 #define DP_DB_BASIC_EXCEPTION_CHECK do {\
 382         if (errorcode != SQLITE_OK) {\
 383                 if ((*error = dp_db_get_errorcode(handle)) == DP_ERROR_NONE)\
 384                 *error = DP_ERROR_INVALID_PARAMETER;\
 385                 __dp_finalize(stmt);\
 386                 return -1;\
 387         } \
 388 } while (0)
 389
 390 #define DP_DB_WRITE_STEP_EXCEPTION_CHECK do {\
 391         errorcode = sqlite3_step(stmt);\
 392         __dp_finalize(stmt);\
 393         if (errorcode != SQLITE_DONE) {\
 394                 if ((*error = dp_db_get_errorcode(handle)) == DP_ERROR_NONE)\
 395                 *error = DP_ERROR_INVALID_PARAMETER;\
 396                 return -1;\
 397         } \
 398 } while (0)
 399
 400 int dp_db_get_ids(void *handle, const char *table, char *idcolumn, int *ids, const char *where, const int limit, char *ordercolumn, char *ordering, int *error)
 401 {
 402         *error = DP_ERROR_INVALID_PARAMETER;
 403         DP_DB_PARAM_NULL_CHECK;
 404         int errorcode = SQLITE_OK;
 405         int rows_count = 0;
 406         sqlite3_stmt *stmt = NULL;
 407
 408         char *order_query = NULL;
 409         if (ordercolumn != NULL)
 410                 order_query = sqlite3_mprintf("ORDER BY %s %s", ordercolumn, (ordering == NULL ? "ASC" : ordering));
 411
 412         if (idcolumn == NULL)
 413                 idcolumn = DP_DB_COL_ID;
 414
 415         char *query = sqlite3_mprintf("SELECT %s FROM %s %s %s LIMIT ?", idcolumn, table, (where == NULL ? "" : where), (order_query == NULL ? "" : order_query));
 416         DP_DB_BUFFER_NULL_CHECK(query);
 417         errorcode = sqlite3_prepare_v2(handle, query, -1, &stmt, NULL);
 418         //TRACE_DEBUG("debug query:%s", query);
 419         sqlite3_free(query);
 420         if (order_query != NULL)
 421                 sqlite3_free(order_query);
 422         DP_DB_BASIC_EXCEPTION_CHECK;
 423
 424         errorcode = sqlite3_bind_int(stmt, 1, limit);
 425         DP_DB_BASIC_EXCEPTION_CHECK;
 426
 427         *error = DP_ERROR_NONE;
 428         while ((errorcode = sqlite3_step(stmt)) == SQLITE_ROW) {
 429                 if (sqlite3_column_type(stmt, 0) == SQLITE_INTEGER) {
 430                         int columnvalue = sqlite3_column_int(stmt, 0);
 431                         //TRACE_DEBUG("id(%d):%d", rows_count, columnvalue);
 432                         ids[rows_count++] = columnvalue;
 433                 }
 434         }
 435         __dp_finalize(stmt);
 436         return rows_count;
 437 }
 438
 439 int dp_db_get_crashed_ids(void *handle, const char *table, int *ids, const int limit, int *error)
 440 {
 441         // make where.
 442         //get ids if state is QUEUED, CONNECTING or DOWNLOADING with auto_download
 443         char *where = sqlite3_mprintf("WHERE %s IS 1 AND (%s IS %d OR %s IS %d OR %s IS %d)",
 444                         DP_DB_COL_AUTO_DOWNLOAD,
 445                         DP_DB_COL_STATE, DP_STATE_DOWNLOADING,
 446                         DP_DB_COL_STATE, DP_STATE_CONNECTING,
 447                         DP_DB_COL_STATE, DP_STATE_QUEUED);
 448         if (where != NULL) {
 449                 int rows_count = dp_db_get_ids(handle, table, DP_DB_COL_ID, ids, where, limit, NULL, NULL, error);
 450                 sqlite3_free(where);
 451                 return rows_count;
 452         }
 453         *error = DP_ERROR_OUT_OF_MEMORY;
 454         return -1;
 455 }
 456
 457
 458 int dp_db_check_duplicated_int(void *handle, const char *table, const char *column, const int value, int *error)
 459 {
 460         *error = DP_ERROR_INVALID_PARAMETER;
 461         DP_DB_PARAM_NULL_CHECK;
 462         int errorcode = SQLITE_OK;
 463         int count = 0;
 464         sqlite3_stmt *stmt = NULL;
 465         char *query = sqlite3_mprintf("SELECT count(*) FROM %s WHERE %s IS ?", table, column);
 466         DP_DB_BUFFER_NULL_CHECK(query);
 467         //TRACE_DEBUG("debug query:%s", query);
 468         errorcode = sqlite3_prepare_v2(handle, query, -1, &stmt, NULL);
 469         sqlite3_free(query);
 470         DP_DB_BASIC_EXCEPTION_CHECK;
 471
 472         errorcode = sqlite3_bind_int(stmt, 1, value);
 473         DP_DB_BASIC_EXCEPTION_CHECK;
 474
 475         *error = DP_ERROR_NONE;
 476         errorcode = sqlite3_step(stmt);
 477         if (errorcode == SQLITE_ROW)
 478                 count = sqlite3_column_int(stmt, 0);
 479         else
 480                 count = 0;
 481
 482         __dp_finalize(stmt);
 483         return count;
 484 }
 485
 486 int dp_db_check_duplicated_string(void *handle, const int id, const char *table, const char *column, int is_like, const char *value, int *error)
 487 {
 488         *error = DP_ERROR_INVALID_PARAMETER;
 489         DP_DB_PARAM_NULL_CHECK;
 490         int errorcode = SQLITE_OK;
 491         int count = 0;
 492         sqlite3_stmt *stmt = NULL;
 493
 494         char *id_check = NULL;
 495         if (id >= 0)
 496                 id_check = sqlite3_mprintf("AND %s IS ?", DP_DB_COL_ID);
 497
 498         char *query = NULL;
 499         if (is_like > 0)
 500                 query = sqlite3_mprintf("SELECT count(*) FROM %s WHERE %s LIKE ? %s", table, column, (id_check == NULL ? "" : id_check));
 501         else
 502                 query = sqlite3_mprintf("SELECT count(*) FROM %s WHERE %s %s ? %s", table, column, (is_like == 0 ? "IS" : "IS NOT"), (id_check == NULL ? "" : id_check));
 503         if (id_check != NULL)
 504                 sqlite3_free(id_check);
 505         DP_DB_BUFFER_NULL_CHECK(query);
 506         //TRACE_DEBUG("debug query:%s", query);
 507         errorcode = sqlite3_prepare_v2(handle, query, -1, &stmt, NULL);
 508         sqlite3_free(query);
 509         DP_DB_BASIC_EXCEPTION_CHECK;
 510
 511         errorcode = sqlite3_bind_text(stmt, 1, value, -1, SQLITE_STATIC);
 512         DP_DB_BASIC_EXCEPTION_CHECK;
 513         if (id >= 0) {
 514                 errorcode = sqlite3_bind_int(stmt, 2, id);
 515                 DP_DB_BASIC_EXCEPTION_CHECK;
 516         }
 517
 518         *error = DP_ERROR_NONE;
 519         errorcode = sqlite3_step(stmt);
 520         if (errorcode == SQLITE_ROW)
 521                 count = sqlite3_column_int(stmt, 0);
 522         else
 523                 count = 0;
 524
 525         __dp_finalize(stmt);
 526         return count;
 527 }
 528
 529 int dp_db_update_client_info(void *handle, const char *pkgname, const char *smack, const int uid, const int gid, int *error)
 530 {
 531         *error = DP_ERROR_INVALID_PARAMETER;
 532         DP_DB_PARAM_NULL_CHECK;
 533         if (pkgname == NULL) {
 534                 TRACE_ERROR("check pkgname");
 535                 return -1;
 536         }
 537
 538         int is_update = dp_db_check_duplicated_string(handle, -1, DP_TABLE_CLIENTS, DP_DB_COL_PACKAGE, 0, pkgname, error);
 539         if (is_update < 0) {
 540                 if ((*error = dp_db_get_errorcode(handle)) == DP_ERROR_NONE)
 541                         *error = DP_ERROR_DISK_BUSY;
 542                 return -1;
 543         }
 544
 545         int errorcode = SQLITE_OK;
 546         sqlite3_stmt *stmt = NULL;
 547         char *query = NULL;
 548
 549         if (is_update == 0)
 550                 query = sqlite3_mprintf(
 551                                 "INSERT INTO %s (%s, %s, %s, %s, %s, %s, %s) VALUES (?, ?, %d, %d, 0, DATETIME('NOW'), DATETIME('NOW'))",
 552                                 DP_TABLE_CLIENTS, DP_DB_COL_SMACK_LABEL, DP_DB_COL_PACKAGE, DP_DB_COL_UID,
 553                                 DP_DB_COL_GID, DP_DB_COL_REQUEST_COUNT,
 554                                 DP_DB_COL_CREATE_TIME, DP_DB_COL_ACCESS_TIME, uid, gid);
 555         else
 556                 query = sqlite3_mprintf("UPDATE %s SET %s = ?, %s = %d, %s = %d, %s = DATETIME('NOW') WHERE %s IS ?",
 557                                 DP_TABLE_CLIENTS, DP_DB_COL_SMACK_LABEL, DP_DB_COL_UID,
 558                                 uid, DP_DB_COL_GID, gid, DP_DB_COL_ACCESS_TIME, DP_DB_COL_PACKAGE);
 559         DP_DB_BUFFER_NULL_CHECK(query);
 560         //TRACE_DEBUG("debug query:%s", query);
 561         errorcode = sqlite3_prepare_v2(handle, query, -1, &stmt, NULL);
 562         sqlite3_free(query);
 563         DP_DB_BASIC_EXCEPTION_CHECK;
 564
 565         if (smack != NULL) {
 566                 errorcode = sqlite3_bind_text(stmt, 1, smack, -1, SQLITE_STATIC);
 567                 DP_DB_BASIC_EXCEPTION_CHECK;
 568         }
 569         errorcode = sqlite3_bind_text(stmt, 2, pkgname, -1, SQLITE_STATIC);
 570         DP_DB_BASIC_EXCEPTION_CHECK;
 571
 572         *error = DP_ERROR_NONE;
 573         DP_DB_WRITE_STEP_EXCEPTION_CHECK;
 574         return 0;
 575 }
 576
 577 int dp_db_get_client_property_string(void *handle, const char *pkgname, const char *column, unsigned char **value, unsigned *length, int *error)
 578 {
 579         *error = DP_ERROR_INVALID_PARAMETER;
 580         DP_DB_PARAM_NULL_CHECK;
 581         if (pkgname == NULL || column == NULL || value == NULL || length == NULL) {
 582                 TRACE_ERROR("check materials for query");
 583                 return -1;
 584         }
 585
 586         int errorcode = SQLITE_OK;
 587         sqlite3_stmt *stmt = NULL;
 588         char *query = sqlite3_mprintf("SELECT %s FROM %s WHERE %s IS ? LIMIT 1", column, DP_TABLE_CLIENTS, DP_DB_COL_PACKAGE);
 589         DP_DB_BUFFER_NULL_CHECK(query);
 590         //TRACE_DEBUG("debug query:%s", query);
 591         errorcode = sqlite3_prepare_v2(handle, query, -1, &stmt, NULL);
 592         sqlite3_free(query);
 593         DP_DB_BASIC_EXCEPTION_CHECK;
 594
 595         errorcode = sqlite3_bind_text(stmt, 1, pkgname, -1, SQLITE_STATIC);
 596         DP_DB_BASIC_EXCEPTION_CHECK;
 597
 598         *error = DP_ERROR_NONE;
 599         errorcode = sqlite3_step(stmt);
 600         *length = 0;
 601         if (errorcode == SQLITE_ROW) {
 602                 int data_type = sqlite3_column_type(stmt, 0);
 603                 if (data_type == SQLITE_TEXT) {
 604                         int getbytes = sqlite3_column_bytes(stmt, 0);
 605                         if (getbytes > 0) {
 606                                 unsigned char *getstr = (unsigned char *)calloc(getbytes + 1, sizeof(unsigned char));
 607                                 if (getstr != NULL) {
 608                                         memcpy(getstr, sqlite3_column_text(stmt, 0), getbytes * sizeof(unsigned char));
 609                                         getstr[getbytes] = '\0';
 610                                         *value = getstr;
 611                                         *length = getbytes;
 612                                 } else {
 613                                         TRACE_ERROR("check available system memory");
 614                                         *error = DP_ERROR_OUT_OF_MEMORY;
 615                                 }
 616                         } else {
 617                                 TRACE_DEBUG("no data");
 618                                 *error = DP_ERROR_NO_DATA;
 619                         }
 620                 } else {
 621                         TRACE_ERROR("check column type:%d", data_type);
 622                         *error = DP_ERROR_NO_DATA;
 623                 }
 624         } else if (errorcode == SQLITE_DONE) {
 625                 TRACE_DEBUG("no data");
 626                 *error = DP_ERROR_NO_DATA;
 627         } else {
 628                 if ((*error = dp_db_get_errorcode(handle)) == DP_ERROR_NONE)
 629                         *error = DP_ERROR_ID_NOT_FOUND;
 630         }
 631         __dp_finalize(stmt);
 632         if (*error != DP_ERROR_NO_DATA && *error != DP_ERROR_NONE)
 633                 return -1;
 634         return 0;
 635 }
 636
 637 int dp_db_new_logging(void *handle, const int id, const int state, const int errorvalue, int *error)
 638 {
 639         *error = DP_ERROR_INVALID_PARAMETER;
 640         DP_DB_PARAM_NULL_CHECK;
 641         if (id <= 0) {
 642                 TRACE_ERROR("check id:%d", id);
 643                 return -1;
 644         }
 645         int errorcode = SQLITE_OK;
 646         sqlite3_stmt *stmt = NULL;
 647
 648         char *query = sqlite3_mprintf("INSERT INTO %s (%s, %s, %s, %s, %s) VALUES (?, ?, ?, DATETIME('now'), DATETIME('now'))",
 649                         DP_TABLE_LOGGING, DP_DB_COL_ID, DP_DB_COL_STATE,
 650                         DP_DB_COL_ERRORCODE, DP_DB_COL_CREATE_TIME, DP_DB_COL_ACCESS_TIME);
 651         DP_DB_BUFFER_NULL_CHECK(query);
 652         //TRACE_DEBUG("debug query:%s", query);
 653         errorcode = sqlite3_prepare_v2(handle, query, -1, &stmt, NULL);
 654         sqlite3_free(query);
 655         DP_DB_BASIC_EXCEPTION_CHECK;
 656
 657         errorcode = sqlite3_bind_int(stmt, 1, id);
 658         DP_DB_BASIC_EXCEPTION_CHECK;
 659         errorcode = sqlite3_bind_int(stmt, 2, state);
 660         DP_DB_BASIC_EXCEPTION_CHECK;
 661         errorcode = sqlite3_bind_int(stmt, 3, errorvalue);
 662         DP_DB_BASIC_EXCEPTION_CHECK;
 663
 664         *error = DP_ERROR_NONE;
 665         DP_DB_WRITE_STEP_EXCEPTION_CHECK;
 666         return 0;
 667 }
 668
 669 int dp_db_update_logging(void *handle, const int id, const int state, const int errorvalue, int *error)
 670 {
 671         *error = DP_ERROR_INVALID_PARAMETER;
 672         DP_DB_PARAM_NULL_CHECK;
 673         if (id <= 0) {
 674                 TRACE_ERROR("check id:%d", id);
 675                 return -1;
 676         }
 677         int errorcode = SQLITE_OK;
 678         sqlite3_stmt *stmt = NULL;
 679
 680         char *query = sqlite3_mprintf("UPDATE %s SET %s = ?, %s = ?, %s = DATETIME('now') WHERE %s = ?",
 681                         DP_TABLE_LOGGING, DP_DB_COL_STATE, DP_DB_COL_ERRORCODE,
 682                         DP_DB_COL_ACCESS_TIME, DP_DB_COL_ID);
 683         DP_DB_BUFFER_NULL_CHECK(query);
 684         //TRACE_DEBUG("debug query:%s", query);
 685         errorcode = sqlite3_prepare_v2(handle, query, -1, &stmt, NULL);
 686         sqlite3_free(query);
 687         DP_DB_BASIC_EXCEPTION_CHECK;
 688
 689         errorcode = sqlite3_bind_int(stmt, 1, state);
 690         DP_DB_BASIC_EXCEPTION_CHECK;
 691         errorcode = sqlite3_bind_int(stmt, 2, errorvalue);
 692         DP_DB_BASIC_EXCEPTION_CHECK;
 693         errorcode = sqlite3_bind_int(stmt, 3, id);
 694         DP_DB_BASIC_EXCEPTION_CHECK;
 695
 696         *error = DP_ERROR_NONE;
 697         DP_DB_WRITE_STEP_EXCEPTION_CHECK;
 698         return 0;
 699 }
 700
 701 // 0:integer, 1:bigint, 2:string, 3:blob
 702 int dp_db_replace_property(void *handle, const int id, const char *table, const char *column, const void *value, const unsigned length, const unsigned valuetype, int *error)
 703 {
 704         *error = DP_ERROR_INVALID_PARAMETER;
 705         DP_DB_PARAM_NULL_CHECK;
 706         if (id <= 0) {
 707                 TRACE_ERROR("check id:%d", id);
 708                 return -1;
 709         }
 710         if (table == NULL || column == NULL || value == NULL) {
 711                 TRACE_ERROR("check materials for query id:%d", id);
 712                 return -1;
 713         }
 714
 715         int is_update = dp_db_check_duplicated_int(handle, table, DP_DB_COL_ID, id, error);
 716         if (is_update < 0) {
 717                 if ((*error = dp_db_get_errorcode(handle)) == DP_ERROR_NONE)
 718                         *error = DP_ERROR_DISK_BUSY;
 719                 return -1;
 720         }
 721
 722         int errorcode = SQLITE_OK;
 723         sqlite3_stmt *stmt = NULL;
 724         char *query = NULL;
 725
 726         if (is_update == 0)
 727                 query = sqlite3_mprintf("INSERT INTO %s (%s, %s) VALUES (?, ?)", table, column, DP_DB_COL_ID);
 728         else
 729                 query = sqlite3_mprintf("UPDATE %s SET %s = ? WHERE %s IS ?", table, column, DP_DB_COL_ID);
 730         DP_DB_BUFFER_NULL_CHECK(query);
 731         //TRACE_DEBUG("debug query:%s", query);
 732         errorcode = sqlite3_prepare_v2(handle, query, -1, &stmt, NULL);
 733         sqlite3_free(query);
 734         DP_DB_BASIC_EXCEPTION_CHECK;
 735
 736         if (valuetype == 0) {
 737                 int *cast_value = (int *)value;
 738                 errorcode = sqlite3_bind_int(stmt, 1, *cast_value);
 739         } else if (valuetype == 1) {
 740                 sqlite_int64 *cast_value = (sqlite_int64 *)value;
 741                 errorcode = sqlite3_bind_int64(stmt, 1, *cast_value);
 742         } else if (valuetype == 2) {
 743                 errorcode = sqlite3_bind_text(stmt, 1, (char *)value, -1, SQLITE_STATIC);
 744         } else if (valuetype == 3) {
 745                 errorcode = sqlite3_bind_blob(stmt, 1, value, (int)length, NULL);
 746         } else {
 747                 TRACE_ERROR("invalid type:%d", valuetype);
 748                 __dp_finalize(stmt);
 749                 return -1;
 750         }
 751         DP_DB_BASIC_EXCEPTION_CHECK;
 752         errorcode = sqlite3_bind_int(stmt, 2, id);
 753         DP_DB_BASIC_EXCEPTION_CHECK;
 754
 755         *error = DP_ERROR_NONE;
 756         DP_DB_WRITE_STEP_EXCEPTION_CHECK;
 757         return 0;
 758 }
 759
 760 int dp_db_get_property_string(void *handle, const int id, const char *table, const char *column, unsigned char **value, unsigned *length, int *error)
 761 {
 762         *error = DP_ERROR_INVALID_PARAMETER;
 763         DP_DB_PARAM_NULL_CHECK;
 764         if (id <= 0) {
 765                 TRACE_ERROR("check id:%d", id);
 766                 return -1;
 767         }
 768         if (table == NULL || column == NULL || value == NULL || length == NULL) {
 769                 TRACE_ERROR("check materials for query id:%d", id);
 770                 return -1;
 771         }
 772
 773         int errorcode = SQLITE_OK;
 774         sqlite3_stmt *stmt = NULL;
 775         char *query = sqlite3_mprintf("SELECT %s FROM %s WHERE %s = ? LIMIT 1", column, table, DP_DB_COL_ID);
 776         DP_DB_BUFFER_NULL_CHECK(query);
 777         //TRACE_DEBUG("debug query:%s", query);
 778         errorcode = sqlite3_prepare_v2(handle, query, -1, &stmt, NULL);
 779         sqlite3_free(query);
 780         DP_DB_BASIC_EXCEPTION_CHECK;
 781
 782         errorcode = sqlite3_bind_int(stmt, 1, id);
 783         DP_DB_BASIC_EXCEPTION_CHECK;
 784
 785         *error = DP_ERROR_NONE;
 786         errorcode = sqlite3_step(stmt);
 787         *length = 0;
 788         if (errorcode == SQLITE_ROW) {
 789                 int data_type = sqlite3_column_type(stmt, 0);
 790                 if (data_type == SQLITE_TEXT) {
 791                         int getbytes = sqlite3_column_bytes(stmt, 0);
 792                         if (getbytes > 0) {
 793                                 unsigned char *getstr = (unsigned char *)calloc(getbytes + 1, sizeof(unsigned char));
 794                                 if (getstr != NULL) {
 795                                         memcpy(getstr, sqlite3_column_text(stmt, 0), getbytes * sizeof(unsigned char));
 796                                         getstr[getbytes] = '\0';
 797                                         *value = getstr;
 798                                         *length = getbytes;
 799                                 } else {
 800                                         TRACE_ERROR("check available system memory");
 801                                         *error = DP_ERROR_OUT_OF_MEMORY;
 802                                 }
 803                         } else {
 804                                 TRACE_DEBUG("no data");
 805                                 *error = DP_ERROR_NO_DATA;
 806                         }
 807                 } else if (data_type == SQLITE_BLOB) {
 808                         int getbytes = sqlite3_column_bytes(stmt, 0);
 809                         if (getbytes > 0) {
 810                                 unsigned char *getstr = (unsigned char *)calloc(getbytes, sizeof(unsigned char));
 811                                 if (getstr != NULL) {
 812                                         memcpy(getstr, sqlite3_column_blob(stmt, 0), getbytes * sizeof(unsigned char));
 813                                         *value = getstr;
 814                                         *length = getbytes;
 815                                 } else {
 816                                         TRACE_ERROR("check available system memory");
 817                                         *error = DP_ERROR_OUT_OF_MEMORY;
 818                                 }
 819                         } else {
 820                                 TRACE_DEBUG("no data");
 821                                 *error = DP_ERROR_NO_DATA;
 822                         }
 823                 } else {
 824                         //TRACE_ERROR("check column type:%d", data_type);
 825                         *error = DP_ERROR_NO_DATA;
 826                 }
 827         } else if (errorcode == SQLITE_DONE) {
 828                 TRACE_DEBUG("no data");
 829                 *error = DP_ERROR_NO_DATA;
 830         } else {
 831                 if ((*error = dp_db_get_errorcode(handle)) == DP_ERROR_NONE)
 832                         *error = DP_ERROR_ID_NOT_FOUND;
 833         }
 834         __dp_finalize(stmt);
 835         if (*error != DP_ERROR_NO_DATA && *error != DP_ERROR_NONE)
 836                 return -1;
 837         return 0;
 838 }
 839
 840 int dp_db_get_property_int(void *handle, const int id, const char *table, const char *column, void *value, int *error)
 841 {
 842         *error = DP_ERROR_INVALID_PARAMETER;
 843         DP_DB_PARAM_NULL_CHECK;
 844         if (id <= 0) {
 845                 TRACE_ERROR("check id:%d", id);
 846                 return -1;
 847         }
 848         if (table == NULL || column == NULL || value == NULL) {
 849                 TRACE_ERROR("check materials for query id:%d", id);
 850                 return -1;
 851         }
 852
 853         int errorcode = SQLITE_OK;
 854         sqlite3_stmt *stmt = NULL;
 855         char *query = sqlite3_mprintf("SELECT %s FROM %s WHERE %s = ? LIMIT 1", column, table, DP_DB_COL_ID);
 856         DP_DB_BUFFER_NULL_CHECK(query);
 857         //TRACE_DEBUG("debug query:%s", query);
 858         errorcode = sqlite3_prepare_v2(handle, query, -1, &stmt, NULL);
 859         sqlite3_free(query);
 860         DP_DB_BASIC_EXCEPTION_CHECK;
 861
 862         errorcode = sqlite3_bind_int(stmt, 1, id);
 863         DP_DB_BASIC_EXCEPTION_CHECK;
 864
 865         *error = DP_ERROR_NONE;
 866         errorcode = sqlite3_step(stmt);
 867         if (errorcode == SQLITE_ROW) {
 868                 int data_type = sqlite3_column_type(stmt, 0);
 869                 if (data_type == SQLITE_INTEGER) {
 870                         int recv_int = sqlite3_column_int(stmt, 0);
 871                         int *pvalue = value;
 872                         *pvalue = recv_int;
 873                 } else if (data_type == SQLITE_FLOAT) {
 874                         unsigned long long recv_int = sqlite3_column_int64(stmt, 0);
 875                         unsigned long long *pvalue = value;
 876                         *pvalue = recv_int;
 877                 } else {
 878                         TRACE_ERROR("check column type:%d", data_type);
 879                         *error = DP_ERROR_NO_DATA;
 880                 }
 881         } else if (errorcode == SQLITE_DONE) {
 882                 TRACE_DEBUG("no data");
 883                 *error = DP_ERROR_NO_DATA;
 884         } else {
 885                 if ((*error = dp_db_get_errorcode(handle)) == DP_ERROR_NONE)
 886                         *error = DP_ERROR_ID_NOT_FOUND;
 887         }
 888         __dp_finalize(stmt);
 889         if (*error != DP_ERROR_NO_DATA && *error != DP_ERROR_NONE)
 890                 return -1;
 891         return 0;
 892 }
 893
 894 int dp_db_unset_property_string(void *handle, const int id, const char *table, const char *column, int *error)
 895 {
 896         *error = DP_ERROR_INVALID_PARAMETER;
 897         DP_DB_PARAM_NULL_CHECK;
 898         if (id <= 0) {
 899                 TRACE_ERROR("check id:%d", id);
 900                 return -1;
 901         }
 902         if (table == NULL || column == NULL) {
 903                 TRACE_ERROR("check materials for query id:%d", id);
 904                 return -1;
 905         }
 906
 907         int is_update = dp_db_check_duplicated_int(handle, table, DP_DB_COL_ID, id, error);
 908         if (is_update < 0) {
 909                 if ((*error = dp_db_get_errorcode(handle)) == DP_ERROR_NONE)
 910                         *error = DP_ERROR_DISK_BUSY;
 911                 return -1;
 912         } else if (is_update == 0) {
 913                 *error = DP_ERROR_ID_NOT_FOUND;
 914                 return -1;
 915         }
 916
 917         int errorcode = SQLITE_OK;
 918         sqlite3_stmt *stmt = NULL;
 919         char *query = sqlite3_mprintf("UPDATE %s SET %s = NULL WHERE %s IS ?", table, column, DP_DB_COL_ID);
 920
 921         DP_DB_BUFFER_NULL_CHECK(query);
 922         //TRACE_DEBUG("debug query:%s", query);
 923         errorcode = sqlite3_prepare_v2(handle, query, -1, &stmt, NULL);
 924         sqlite3_free(query);
 925         DP_DB_BASIC_EXCEPTION_CHECK;
 926         errorcode = sqlite3_bind_int(stmt, 1, id);
 927         DP_DB_BASIC_EXCEPTION_CHECK;
 928
 929         *error = DP_ERROR_NONE;
 930         DP_DB_WRITE_STEP_EXCEPTION_CHECK;
 931         return 0;
 932 }
 933
 934 // "DELETE FROM %s WHERE %s NOT IN (SELECT %s FROM %s ORDER BY %s %s LIMIT %d)"
 935 int dp_db_delete(void *handle, const int id, const char *table, int *error)
 936 {
 937         *error = DP_ERROR_INVALID_PARAMETER;
 938         DP_DB_PARAM_NULL_CHECK;
 939         if (id <= 0) {
 940                 TRACE_ERROR("check id:%d", id);
 941                 return -1;
 942         }
 943         if (table == NULL) {
 944                 TRACE_ERROR("check materials for query id:%d", id);
 945                 return -1;
 946         }
 947
 948         int is_update = dp_db_check_duplicated_int(handle, table, DP_DB_COL_ID, id, error);
 949         if (is_update < 0) {
 950                 if ((*error = dp_db_get_errorcode(handle)) == DP_ERROR_NONE)
 951                         *error = DP_ERROR_DISK_BUSY;
 952                 return -1;
 953         } else if (is_update == 0) {
 954                 *error = DP_ERROR_ID_NOT_FOUND;
 955                 return -1;
 956         }
 957
 958         int errorcode = SQLITE_OK;
 959         sqlite3_stmt *stmt = NULL;
 960         char *query = sqlite3_mprintf("DELETE FROM %s WHERE %s IS ?", table, DP_DB_COL_ID);
 961
 962         DP_DB_BUFFER_NULL_CHECK(query);
 963         //TRACE_DEBUG("debug query:%s", query);
 964         errorcode = sqlite3_prepare_v2(handle, query, -1, &stmt, NULL);
 965         sqlite3_free(query);
 966         DP_DB_BASIC_EXCEPTION_CHECK;
 967         errorcode = sqlite3_bind_int(stmt, 1, id);
 968         DP_DB_BASIC_EXCEPTION_CHECK;
 969
 970         *error = DP_ERROR_NONE;
 971         DP_DB_WRITE_STEP_EXCEPTION_CHECK;
 972         return 0;
 973 }
 974
 975 int dp_db_new_header(void *handle, const int id, const char *field, const char *value, int *error)
 976 {
 977         *error = DP_ERROR_INVALID_PARAMETER;
 978         DP_DB_PARAM_NULL_CHECK;
 979         if (id <= 0) {
 980                 TRACE_ERROR("check id:%d", id);
 981                 return -1;
 982         }
 983         if (field == NULL) {
 984                 TRACE_ERROR("check field:%s", field);
 985                 return -1;
 986         }
 987         int errorcode = SQLITE_OK;
 988         sqlite3_stmt *stmt = NULL;
 989
 990         char *query = sqlite3_mprintf("INSERT INTO %s (%s, %s, %s) VALUES (?, ?, ?)",
 991                         DP_TABLE_HEADERS, DP_DB_COL_ID, DP_DB_COL_HEADER_FIELD,
 992                         DP_DB_COL_HEADER_DATA);
 993         DP_DB_BUFFER_NULL_CHECK(query);
 994         //TRACE_DEBUG("debug query:%s", query);
 995         errorcode = sqlite3_prepare_v2(handle, query, -1, &stmt, NULL);
 996         sqlite3_free(query);
 997         DP_DB_BASIC_EXCEPTION_CHECK;
 998
 999         errorcode = sqlite3_bind_int(stmt, 1, id);
1000         DP_DB_BASIC_EXCEPTION_CHECK;
1001         errorcode = sqlite3_bind_text(stmt, 2, (char *)field, -1, SQLITE_STATIC);
1002         DP_DB_BASIC_EXCEPTION_CHECK;
1003         errorcode = sqlite3_bind_text(stmt, 3, (char *)value, -1, SQLITE_STATIC);
1004         DP_DB_BASIC_EXCEPTION_CHECK;
1005
1006         *error = DP_ERROR_NONE;
1007         DP_DB_WRITE_STEP_EXCEPTION_CHECK;
1008         return 0;
1009 }
1010
1011 int dp_db_update_header(void *handle, const int id, const char *field, const char *value, int *error)
1012 {
1013         *error = DP_ERROR_INVALID_PARAMETER;
1014         DP_DB_PARAM_NULL_CHECK;
1015         if (id <= 0) {
1016                 TRACE_ERROR("check id:%d", id);
1017                 return -1;
1018         }
1019         int errorcode = SQLITE_OK;
1020         sqlite3_stmt *stmt = NULL;
1021
1022         char *query = sqlite3_mprintf("UPDATE %s SET %s = ? WHERE %s IS ? AND %s IS ?",
1023                         DP_TABLE_HEADERS, DP_DB_COL_HEADER_DATA,
1024                         DP_DB_COL_ID, DP_DB_COL_HEADER_FIELD);
1025         DP_DB_BUFFER_NULL_CHECK(query);
1026         //TRACE_DEBUG("debug query:%s", query);
1027         errorcode = sqlite3_prepare_v2(handle, query, -1, &stmt, NULL);
1028         sqlite3_free(query);
1029         DP_DB_BASIC_EXCEPTION_CHECK;
1030
1031         errorcode = sqlite3_bind_text(stmt, 1, (char *)value, -1, SQLITE_STATIC);
1032         DP_DB_BASIC_EXCEPTION_CHECK;
1033         errorcode = sqlite3_bind_int(stmt, 2, id);
1034         DP_DB_BASIC_EXCEPTION_CHECK;
1035         errorcode = sqlite3_bind_text(stmt, 2, (char *)field, -1, SQLITE_STATIC);
1036         DP_DB_BASIC_EXCEPTION_CHECK;
1037
1038         *error = DP_ERROR_NONE;
1039         DP_DB_WRITE_STEP_EXCEPTION_CHECK;
1040         return 0;
1041 }
1042
1043 int dp_db_get_header_value(void *handle, const int id, const char *field, unsigned char **value, unsigned *length, int *error)
1044 {
1045         *error = DP_ERROR_INVALID_PARAMETER;
1046         DP_DB_PARAM_NULL_CHECK;
1047         if (id <= 0) {
1048                 TRACE_ERROR("check id:%d", id);
1049                 return -1;
1050         }
1051         if (field == NULL || value == NULL || length == NULL) {
1052                 TRACE_ERROR("check materials for query id:%d", id);
1053                 return -1;
1054         }
1055
1056         int errorcode = SQLITE_OK;
1057         sqlite3_stmt *stmt = NULL;
1058         char *query = sqlite3_mprintf("SELECT %s FROM %s WHERE %s IS ? AND %s IS ? LIMIT 1", DP_DB_COL_HEADER_DATA, DP_TABLE_HEADERS, DP_DB_COL_ID, DP_DB_COL_HEADER_FIELD);
1059         DP_DB_BUFFER_NULL_CHECK(query);
1060         //TRACE_DEBUG("debug query:%s", query);
1061         errorcode = sqlite3_prepare_v2(handle, query, -1, &stmt, NULL);
1062         sqlite3_free(query);
1063         DP_DB_BASIC_EXCEPTION_CHECK;
1064
1065         errorcode = sqlite3_bind_int(stmt, 1, id);
1066         DP_DB_BASIC_EXCEPTION_CHECK;
1067         errorcode = sqlite3_bind_text(stmt, 2, (char *)field, -1, SQLITE_STATIC);
1068         DP_DB_BASIC_EXCEPTION_CHECK;
1069
1070         *error = DP_ERROR_NONE;
1071         errorcode = sqlite3_step(stmt);
1072         *length = 0;
1073         if (errorcode == SQLITE_ROW) {
1074                 int data_type = sqlite3_column_type(stmt, 0);
1075                 if (data_type == SQLITE_TEXT) {
1076                         int getbytes = sqlite3_column_bytes(stmt, 0);
1077                         if (getbytes > 0) {
1078                                 unsigned char *getstr = (unsigned char *)calloc(getbytes + 1, sizeof(unsigned char));
1079                                 if (getstr != NULL) {
1080                                         memcpy(getstr, sqlite3_column_text(stmt, 0), getbytes * sizeof(unsigned char));
1081                                         getstr[getbytes] = '\0';
1082                                         *value = getstr;
1083                                         *length = getbytes;
1084                                 } else {
1085                                         TRACE_ERROR("check available system memory");
1086                                         *error = DP_ERROR_OUT_OF_MEMORY;
1087                                 }
1088                         } else {
1089                                 TRACE_DEBUG("no data");
1090                                 *error = DP_ERROR_NO_DATA;
1091                         }
1092                 } else {
1093                         TRACE_ERROR("check column type:%d", data_type);
1094                         *error = DP_ERROR_NO_DATA;
1095                 }
1096         } else if (errorcode == SQLITE_DONE) {
1097                 TRACE_DEBUG("no data");
1098                 *error = DP_ERROR_NO_DATA;
1099         } else {
1100                 if ((*error = dp_db_get_errorcode(handle)) == DP_ERROR_NONE)
1101                         *error = DP_ERROR_ID_NOT_FOUND;
1102         }
1103         __dp_finalize(stmt);
1104         if (*error != DP_ERROR_NO_DATA && *error != DP_ERROR_NONE)
1105                 return -1;
1106         return 0;
1107 }
1108
1109 // not supprot blob as column & value for additional condition
1110 int dp_db_cond_delete(void *handle, const int id, const char *table, const char *column, const void *value, const unsigned valuetype, int *error)
1111 {
1112         *error = DP_ERROR_INVALID_PARAMETER;
1113         DP_DB_PARAM_NULL_CHECK;
1114         if (id <= 0) {
1115                 TRACE_ERROR("check id:%d", id);
1116                 return -1;
1117         }
1118         if (table == NULL || column == NULL || value == NULL) {
1119                 TRACE_ERROR("check materials for query id:%d", id);
1120                 return -1;
1121         }
1122
1123         int errorcode = SQLITE_OK;
1124         sqlite3_stmt *stmt = NULL;
1125         char *query = sqlite3_mprintf("DELETE FROM %s WHERE %s IS ? AND %s IS ?", table, DP_DB_COL_ID, column);
1126
1127         DP_DB_BUFFER_NULL_CHECK(query);
1128         //TRACE_DEBUG("debug query:%s", query);
1129         errorcode = sqlite3_prepare_v2(handle, query, -1, &stmt, NULL);
1130         sqlite3_free(query);
1131         DP_DB_BASIC_EXCEPTION_CHECK;
1132         errorcode = sqlite3_bind_int(stmt, 1, id);
1133         DP_DB_BASIC_EXCEPTION_CHECK;
1134
1135         if (valuetype == 0) {
1136                 int *cast_value = (int *)value;
1137                 errorcode = sqlite3_bind_int(stmt, 2, *cast_value);
1138         } else if (valuetype == 1) {
1139                 sqlite_int64 *cast_value = (sqlite_int64 *)value;
1140                 errorcode = sqlite3_bind_int64(stmt, 2, *cast_value);
1141         } else if (valuetype == 2) {
1142                 errorcode = sqlite3_bind_text(stmt, 2, (char *)value, -1, SQLITE_STATIC);
1143         }
1144         DP_DB_BASIC_EXCEPTION_CHECK;
1145
1146         *error = DP_ERROR_NONE;
1147         DP_DB_WRITE_STEP_EXCEPTION_CHECK;
1148         return 0;
1149 }
1150
1151 int dp_db_get_cond_ids(void *handle, const char *table, const char *getcolumn, const char *column, const int value, int *ids, const int limit, int *error)
1152 {
1153         *error = DP_ERROR_INVALID_PARAMETER;
1154         DP_DB_PARAM_NULL_CHECK;
1155         int errorcode = SQLITE_OK;
1156         int rows_count = 0;
1157         sqlite3_stmt *stmt = NULL;
1158         char *query = sqlite3_mprintf("SELECT %s FROM %s WHERE %s IS ?", getcolumn, table, column);
1159         DP_DB_BUFFER_NULL_CHECK(query);
1160         //TRACE_DEBUG("debug query:%s", query);
1161         errorcode = sqlite3_prepare_v2(handle, query, -1, &stmt, NULL);
1162         sqlite3_free(query);
1163         DP_DB_BASIC_EXCEPTION_CHECK;
1164
1165         errorcode = sqlite3_bind_int(stmt, 1, value);
1166         DP_DB_BASIC_EXCEPTION_CHECK;
1167
1168         *error = DP_ERROR_NONE;
1169         while ((errorcode = sqlite3_step(stmt)) == SQLITE_ROW) {
1170                 if (sqlite3_column_type(stmt, 0) == SQLITE_INTEGER) {
1171                         int columnvalue = sqlite3_column_int(stmt, 0);
1172                         //TRACE_DEBUG("id(%d):%d", rows_count, columnvalue);
1173                         ids[rows_count++] = columnvalue;
1174                 }
1175         }
1176         __dp_finalize(stmt);
1177         return rows_count;
1178 }
1179
1180 int dp_db_get_cond_string(void *handle, const char *table, char *wherecolumn, const int wherevalue, const char *getcolumn, unsigned char **value, unsigned *length, int *error)
1181 {
1182         *error = DP_ERROR_INVALID_PARAMETER;
1183         DP_DB_PARAM_NULL_CHECK;
1184         if (table == NULL || getcolumn == NULL || value == NULL || length == NULL) {
1185                 TRACE_ERROR("check materials for query");
1186                 return -1;
1187         }
1188
1189         if (wherecolumn == NULL)
1190                 wherecolumn = DP_DB_COL_ID;
1191
1192         int errorcode = SQLITE_OK;
1193         sqlite3_stmt *stmt = NULL;
1194         char *query = sqlite3_mprintf("SELECT %s FROM %s WHERE %s = ? LIMIT 1", getcolumn, table, wherecolumn);
1195         DP_DB_BUFFER_NULL_CHECK(query);
1196         //TRACE_DEBUG("debug query:%s", query);
1197         errorcode = sqlite3_prepare_v2(handle, query, -1, &stmt, NULL);
1198         sqlite3_free(query);
1199         DP_DB_BASIC_EXCEPTION_CHECK;
1200
1201         errorcode = sqlite3_bind_int(stmt, 1, wherevalue);
1202         DP_DB_BASIC_EXCEPTION_CHECK;
1203
1204         *error = DP_ERROR_NONE;
1205         errorcode = sqlite3_step(stmt);
1206         *length = 0;
1207         if (errorcode == SQLITE_ROW) {
1208                 int data_type = sqlite3_column_type(stmt, 0);
1209                 if (data_type == SQLITE_TEXT) {
1210                         int getbytes = sqlite3_column_bytes(stmt, 0);
1211                         if (getbytes > 0) {
1212                                 unsigned char *getstr = (unsigned char *)calloc(getbytes + 1, sizeof(unsigned char));
1213                                 if (getstr != NULL) {
1214                                         memcpy(getstr, sqlite3_column_text(stmt, 0), getbytes * sizeof(unsigned char));
1215                                         getstr[getbytes] = '\0';
1216                                         *value = getstr;
1217                                         *length = getbytes;
1218                                 } else {
1219                                         TRACE_ERROR("check available system memory");
1220                                         *error = DP_ERROR_OUT_OF_MEMORY;
1221                                 }
1222                         } else {
1223                                 TRACE_DEBUG("no data");
1224                                 *error = DP_ERROR_NO_DATA;
1225                         }
1226                 } else if (data_type == SQLITE_BLOB) {
1227                         int getbytes = sqlite3_column_bytes(stmt, 0);
1228                         if (getbytes > 0) {
1229                                 unsigned char *getstr = (unsigned char *)calloc(getbytes, sizeof(unsigned char));
1230                                 if (getstr != NULL) {
1231                                         memcpy(getstr, sqlite3_column_blob(stmt, 0), getbytes * sizeof(unsigned char));
1232                                         *value = getstr;
1233                                         *length = getbytes;
1234                                 } else {
1235                                         TRACE_ERROR("check available system memory");
1236                                         *error = DP_ERROR_OUT_OF_MEMORY;
1237                                 }
1238                         } else {
1239                                 TRACE_DEBUG("no data");
1240                                 *error = DP_ERROR_NO_DATA;
1241                         }
1242                 } else {
1243                         TRACE_ERROR("check column type:%d", data_type);
1244                         *error = DP_ERROR_NO_DATA;
1245                 }
1246         } else if (errorcode == SQLITE_DONE) {
1247                 TRACE_DEBUG("no data");
1248                 *error = DP_ERROR_NO_DATA;
1249         } else {
1250                 if ((*error = dp_db_get_errorcode(handle)) == DP_ERROR_NONE)
1251                         *error = DP_ERROR_ID_NOT_FOUND;
1252         }
1253         __dp_finalize(stmt);
1254         if (*error != DP_ERROR_NO_DATA && *error != DP_ERROR_NONE)
1255                 return -1;
1256         return 0;
1257 }
1258
1259 int dp_db_limit_rows(void *handle, const char *table, int limit, int *error)
1260 {
1261         *error = DP_ERROR_INVALID_PARAMETER;
1262         DP_DB_PARAM_NULL_CHECK;
1263         if (table == NULL) {
1264                 TRACE_ERROR("check materials for query");
1265                 return -1;
1266         }
1267         if (limit < 0) {
1268                 TRACE_ERROR("check limitation:%d", limit);
1269                 return -1;
1270         }
1271
1272         int errorcode = SQLITE_OK;
1273         sqlite3_stmt *stmt = NULL;
1274         char *query = sqlite3_mprintf("DELETE FROM %s WHERE %s NOT IN (SELECT %s FROM %s ORDER BY %s ASC LIMIT ?)", table, DP_DB_COL_ID, DP_DB_COL_ID, table, DP_DB_COL_CREATE_TIME);
1275         DP_DB_BUFFER_NULL_CHECK(query);
1276         //TRACE_DEBUG("debug query:%s", query);
1277         errorcode = sqlite3_prepare_v2(handle, query, -1, &stmt, NULL);
1278         sqlite3_free(query);
1279         DP_DB_BASIC_EXCEPTION_CHECK;
1280
1281         errorcode = sqlite3_bind_int(stmt, 1, limit);
1282         DP_DB_BASIC_EXCEPTION_CHECK;
1283
1284         // apply "ON DELETE CASCADE"
1285
1286         *error = DP_ERROR_NONE;
1287         DP_DB_WRITE_STEP_EXCEPTION_CHECK;
1288         return 0;
1289 }
1290
1291 int dp_db_limit_time(void *handle, const char *table, int hours, int *error)
1292 {
1293         *error = DP_ERROR_INVALID_PARAMETER;
1294         DP_DB_PARAM_NULL_CHECK;
1295         if (table == NULL) {
1296                 TRACE_ERROR("check materials for query");
1297                 return -1;
1298         }
1299         if (hours <= 0) {
1300                 TRACE_ERROR("check limit time:%d", hours);
1301                 return -1;
1302         }
1303
1304         int errorcode = SQLITE_OK;
1305         sqlite3_stmt *stmt = NULL;
1306         char *query = sqlite3_mprintf("DELETE FROM %s WHERE %s < DATETIME('now','-%d hours')", table, DP_DB_COL_CREATE_TIME, hours);
1307         DP_DB_BUFFER_NULL_CHECK(query);
1308         //TRACE_DEBUG("debug query:%s", query);
1309         errorcode = sqlite3_prepare_v2(handle, query, -1, &stmt, NULL);
1310         sqlite3_free(query);
1311         DP_DB_BASIC_EXCEPTION_CHECK;
1312         *error = DP_ERROR_NONE;
1313         DP_DB_WRITE_STEP_EXCEPTION_CHECK;
1314         return 0;
1315 }
1316
1317 int dp_db_get_http_headers_list(void *handle, int id, char **headers, int *error)
1318 {
1319         int errorcode = SQLITE_OK;
1320         int headers_index = 0;
1321         sqlite3_stmt *stmt = NULL;
1322         *error = DP_ERROR_NONE;
1323         DP_DB_PARAM_NULL_CHECK;
1324
1325         if (id <= 0) {
1326                 TRACE_ERROR("[CHECK ID]");
1327                 *error = DP_ERROR_INVALID_PARAMETER;
1328                 return -1;
1329         }
1330
1331         errorcode =
1332                 sqlite3_prepare_v2(handle,
1333                                 "SELECT header_field, header_data FROM header WHERE id = ?",
1334                                 -1, &stmt, NULL);
1335
1336         DP_DB_BASIC_EXCEPTION_CHECK;
1337
1338         errorcode = sqlite3_bind_int(stmt, 1, id);
1339
1340         DP_DB_BASIC_EXCEPTION_CHECK;
1341
1342         while ((errorcode = sqlite3_step(stmt)) == SQLITE_ROW) {
1343                 int buffer_length = 0;
1344                 char *header_field = (char *)(sqlite3_column_text(stmt, 0));
1345                 char *header_data = (char *)(sqlite3_column_text(stmt, 1));
1346
1347                 // REF : http://www.w3.org/Protocols/rfc2616/rfc2616-sec4.html#sec4.2
1348                 buffer_length = strlen(header_field) + strlen(header_data) + 1;
1349                 char *headers_buffer = calloc(buffer_length + 1, sizeof(char));
1350                 if (headers_buffer == NULL) {
1351                         TRACE_ERROR("[CALLOC] headers_buffer");
1352                         continue;
1353                 }
1354                 int len = snprintf(headers_buffer, buffer_length + 1,
1355                                 "%s:%s", header_field, header_data);
1356                 if (len <= 0) {
1357                         free(headers_buffer);
1358                         continue;
1359                 } else {
1360                         headers_buffer[len] = '\0';
1361                 }
1362                 headers[headers_index++] = headers_buffer;
1363         }
1364
1365         __dp_finalize(stmt);
1366
1367         return headers_index;
1368 }
1369
1370 int dp_db_get_max_download_id(void *handle, const char *table, int *pvalue, int *error)
1371 {
1372         TRACE_DEBUG("");
1373         int errorcode = SQLITE_OK;
1374         sqlite3_stmt *stmt = NULL;
1375         *error = DP_ERROR_NONE;
1376         DP_DB_PARAM_NULL_CHECK;
1377
1378         char *query = sqlite3_mprintf("SELECT MAX(%s) FROM %s", DP_DB_COL_ID, table);
1379         DP_DB_BUFFER_NULL_CHECK(query);
1380         //TRACE_DEBUG("debug query:%s", query);
1381         errorcode = sqlite3_prepare_v2(handle, query, -1, &stmt, NULL);
1382         DP_DB_BASIC_EXCEPTION_CHECK;
1383
1384         errorcode = sqlite3_step(stmt);
1385         if (errorcode == SQLITE_ROW) {
1386                 int data_type = sqlite3_column_type(stmt, 0);
1387                 if (data_type == SQLITE_INTEGER) {
1388                         int recv_int = sqlite3_column_int(stmt, 0);
1389                         *pvalue = recv_int;
1390                 } else if (data_type == SQLITE_NULL) {
1391                         /* table has no any entry */
1392                         TRACE_DEBUG("table has no entry");
1393                         *error = DP_ERROR_NO_DATA;
1394                 } else {
1395                         TRACE_ERROR("check column type:%d", data_type);
1396                         *error = DP_ERROR_INVALID_PARAMETER;
1397                 }
1398         } else if (errorcode == SQLITE_DONE) {
1399                 TRACE_DEBUG("no data");
1400                 *error = DP_ERROR_NO_DATA;
1401         } else {
1402                 if ((*error = dp_db_get_errorcode(handle)) == DP_ERROR_NONE) {
1403                         TRACE_ERROR("ERROR :: unknown");
1404                         *error = DP_ERROR_DISK_BUSY;
1405                 }
1406         }
1407         __dp_finalize(stmt);
1408         if (*error != DP_ERROR_NONE)
1409                 return -1;
1410         return 0;
1411 }
1412
1413