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