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