server/cal_server_update.c

   1 /*
   2  * Calendar Service
   3  *
   4  * Copyright (c) 2012 - 2015 Samsung Electronics Co., Ltd. All rights reserved.
   5  *
   6  * Licensed under the Apache License, Version 2.0 (the "License");
   7  * you may not use this file except in compliance with the License.
   8  * You may obtain a copy of the License at
   9  *
  10  * http://www.apache.org/licenses/LICENSE-2.0
  11  *
  12  * Unless required by applicable law or agreed to in writing, software
  13  * distributed under the License is distributed on an "AS IS" BASIS,
  14  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  15  * See the License for the specific language governing permissions and
  16  * limitations under the License.
  17  *
  18  */
  19
  20 #include <sqlite3.h>
  21 #include <db-util.h>
  22 #include <stdlib.h>
  23
  24 #include "cal_typedef.h"
  25 #include "cal_internal.h"
  26 #include "cal_db.h"
  27
  28 #define __USER_VERSION 108
  29
  30 /* LCOV_EXCL_START */
  31 static int _cal_server_update_get_db_version(sqlite3 *db, int *version)
  32 {
  33         int ret = CALENDAR_ERROR_NONE;
  34         char query[CAL_DB_SQL_MAX_LEN] = {0};
  35         sqlite3_stmt *stmt = NULL;
  36
  37         snprintf(query, sizeof(query), "PRAGMA user_version;");
  38         ret = sqlite3_prepare_v2(db, query, strlen(query), &stmt, NULL);
  39         if (SQLITE_OK != ret) {
  40                 ERR("sqlite3_prepare_v2() failed[%s]", sqlite3_errmsg(db));
  41                 return CALENDAR_ERROR_DB_FAILED;
  42         }
  43         ret = sqlite3_step(stmt);
  44         if (SQLITE_ROW != ret) {
  45                 ERR("sqlite3_step() failed[%s]", sqlite3_errmsg(db));
  46                 sqlite3_finalize(stmt);
  47                 return CALENDAR_ERROR_DB_FAILED;
  48         }
  49         if (version)
  50                 *version = (int)sqlite3_column_int(stmt, 0);
  51         sqlite3_finalize(stmt);
  52         return CALENDAR_ERROR_NONE;
  53 }
  54
  55 int cal_server_update(void)
  56 {
  57         CAL_FN_CALL();
  58
  59         int ret = CALENDAR_ERROR_NONE;
  60         int old_version = 0;
  61         char *errmsg = NULL;
  62         sqlite3 *__db;
  63         char query[CAL_DB_SQL_MAX_LEN] = {0};
  64
  65         ret = db_util_open(CAL_DB_FILE, &__db, 0);
  66         if (SQLITE_OK != ret) {
  67                 ERR("db_util_open() fail(%d):[%s]", ret, CAL_DB_FILE);
  68                 return CALENDAR_ERROR_DB_FAILED;
  69         }
  70         _cal_server_update_get_db_version(__db, &old_version);
  71         if (__USER_VERSION == old_version) {
  72                 db_util_close(__db);
  73                 __db = NULL;
  74                 return CALENDAR_ERROR_NONE;
  75         }
  76
  77         DBG("[%s] old version(%d)", CAL_DB_FILE, old_version);
  78         if (old_version < 100) {
  79                 /* ----------------------- start modified 2013/08/22
  80                  * added attendee_table(cutype, delegatee_uri, member), alarm_table(summary, action, attach).
  81                  */
  82                 ret = sqlite3_exec(__db, "DROP VIEW event_calendar_attendee_view", NULL, 0, &errmsg);
  83                 if (SQLITE_OK != ret) {
  84                         ERR("sqlite3_exec() failed: DROP VIEW event_calendar_attendee_view(%d) [%s]", ret, errmsg);
  85                         sqlite3_free(errmsg);
  86                 }
  87                 ret = sqlite3_exec(__db, "ALTER TABLE attendee_table ADD COLUMN attendee_cutype INTEGER", NULL, 0, &errmsg);
  88                 if (SQLITE_OK != ret) {
  89                         ERR("sqlite3_exec() failed: ALTER TABLE attendee_table ADD COLUMN attendee_cutype(%d) [%s]", ret, errmsg);
  90                         sqlite3_free(errmsg);
  91                 }
  92                 ret = sqlite3_exec(__db, "ALTER TABLE attendee_table ADD COLUMN attendee_delegatee_uri TEXT", NULL, 0, &errmsg);
  93                 if (SQLITE_OK != ret) {
  94                         ERR("sqlite3_exec() failed: ALTER TABLE attendee_table ADD COLUMN attendee_delegatee_uri(%d) [%s]", ret, errmsg);
  95                         sqlite3_free(errmsg);
  96                 }
  97                 ret = sqlite3_exec(__db, "ALTER TABLE attendee_table ADD COLUMN attendee_member TEXT", NULL, 0, &errmsg);
  98                 if (SQLITE_OK != ret) {
  99                         ERR("sqlite3_exec() failed: ALTER TABLE attendee_table ADD COLUMN attendee_member(%d) [%s]", ret, errmsg);
 100                         sqlite3_free(errmsg);
 101                 }
 102
 103                 ret = sqlite3_exec(__db, "ALTER TABLE alarm_table ADD COLUMN alarm_summary TEXT", NULL, 0, &errmsg);
 104                 if (SQLITE_OK != ret) {
 105                         ERR("sqlite3_exec() failed: ALTER TABLE alarm_table ADD COLUMN alarm_summary(%d) [%s]", ret, errmsg);
 106                         sqlite3_free(errmsg);
 107                 }
 108                 ret = sqlite3_exec(__db, "ALTER TABLE alarm_table ADD COLUMN alarm_action INTEGER", NULL, 0, &errmsg);
 109                 if (SQLITE_OK != ret) {
 110                         ERR("sqlite3_exec() failed: ALTER TABLE alarm_table ADD COLUMN alarm_action(%d) [%s]", ret, errmsg);
 111                         sqlite3_free(errmsg);
 112                 }
 113                 ret = sqlite3_exec(__db, "ALTER TABLE alarm_table ADD COLUMN alarm_attach TEXT", NULL, 0, &errmsg);
 114                 if (SQLITE_OK != ret) {
 115                         ERR("sqlite3_exec() failed: ALTER TABLE alarm_table ADD COLUMN alarm_attach(%d) [%s]", ret, errmsg);
 116                         sqlite3_free(errmsg);
 117                 }
 118                 old_version = 100;
 119                 /* ----------------------- end modified 2013/08/22
 120                 */
 121         }
 122         if (old_version == 100) {
 123                 /* ----------------------- start modified 2013/09/22
 124                  * added schedule_table(freq) for view table parameter.
 125                  */
 126                 ret = sqlite3_exec(__db, "ALTER TABLE schedule_table ADD COLUMN freq INTEGER DEFAULT 0", NULL, 0, &errmsg);
 127                 if (SQLITE_OK != ret) {
 128                         ERR("sqlite3_exec() failed: ALTER TABLE schedule_table ADD COLUMN freq(%d) [%s]", ret, errmsg);
 129                         sqlite3_free(errmsg);
 130                 }
 131                 old_version = 101;
 132                 /* ----------------------- end modified 2013/09/22
 133                 */
 134         }
 135         if (old_version == 101) {
 136                 /* ----------------------- start modified 2014/07/02
 137                  * added trigger depeding on schedule_table
 138                  * added original_event_id in deleted_table to check exception event.
 139                  */
 140
 141                 /* rename trig -> trg */
 142                 ret = sqlite3_exec(__db, "DROP TRIGGER trig_original_mod", NULL, 0, &errmsg);
 143                 if (SQLITE_OK != ret) {
 144                         ERR("DROP TRIGGER trig_original_mod failed(%d:%s)", ret, errmsg);
 145                         sqlite3_free(errmsg);
 146                 }
 147                 ret = sqlite3_exec(__db,
 148                                 "CREATE TRIGGER trg_original_mod AFTER UPDATE OF is_deleted ON schedule_table "
 149                                 " BEGIN "
 150                                 "   DELETE FROM normal_instance_table WHERE event_id = (SELECT rowid FROM schedule_table WHERE original_event_id = old.id);"
 151                                 "   DELETE FROM allday_instance_table WHERE event_id = (SELECT rowid FROM schedule_table WHERE original_event_id = old.id);"
 152                                 "   UPDATE schedule_table SET is_deleted = 1 WHERE original_event_id = old.id;"
 153                                 " END;",
 154                                 NULL, 0, &errmsg);
 155                 if (SQLITE_OK != ret) {
 156                         ERR("CREATE TRIGGER trg_original_mod failed(%d:%s)", ret, errmsg);
 157                         sqlite3_free(errmsg);
 158                 }
 159
 160                 /* rename trg_sch_del -> trg_schedule_del */
 161                 ret = sqlite3_exec(__db, "DROP TRIGGER trg_sch_del", NULL, 0, &errmsg);
 162                 if (SQLITE_OK != ret) {
 163                         ERR("DROP TRIGGER trg_sch_del failed(%d:%s)", ret, errmsg);
 164                         sqlite3_free(errmsg);
 165                 }
 166                 ret = sqlite3_exec(__db,
 167                                 "CREATE TRIGGER trg_schedule_del AFTER DELETE ON schedule_table "
 168                                 "BEGIN "
 169                                 "  DELETE FROM rrule_table WHERE event_id = old.id;"
 170                                 "  DELETE FROM alarm_table WHERE event_id = old.id;"
 171                                 "  DELETE FROM schedule_table WHERE original_event_id = old.id;"
 172                                 "  DELETE FROM normal_instance_table WHERE event_id = old.id;"
 173                                 "  DELETE FROM allday_instance_table WHERE event_id = old.id;"
 174                                 "  DELETE FROM attendee_table WHERE event_id = old.id;"
 175                                 "  DELETE FROM extended_table WHERE record_id = old.id AND record_type = 2;"
 176                                 "  DELETE FROM extended_table WHERE record_id = old.id AND record_type = 3;"
 177                                 "END;",
 178                                 NULL, 0, &errmsg);
 179                 if (SQLITE_OK != ret) {
 180                         ERR("CREATE TRIGGER trg_schedule_del failed(%d:%s)", ret, errmsg);
 181                         sqlite3_free(errmsg);
 182                 }
 183
 184                 /* add trigger */
 185                 ret = sqlite3_exec(__db,
 186                                 "CREATE TRIGGER trg_schedule_del2 AFTER DELETE ON schedule_table "
 187                                 " WHEN old.is_deleted = 0 AND old.calendar_id = (SELECT id FROM calendar_table WHERE id = old.calendar_id) "
 188                                 " BEGIN "
 189                                 "   INSERT INTO deleted_table VALUES(old.id, old.type + 1, old.calendar_id, (SELECT ver FROM version_table) + 1, old.created_ver, old.original_event_id);"
 190                                 " END;",
 191                                 NULL, 0, &errmsg);
 192                 if (SQLITE_OK != ret) {
 193                         ERR("CREATE TRIGGER trg_schedule_del2 failed(%d:%s)", ret, errmsg);
 194                         sqlite3_free(errmsg);
 195                 }
 196
 197                 /* add trigger */
 198                 ret = sqlite3_exec(__db,
 199                                 "CREATE TRIGGER trg_schedule_del3 AFTER DELETE ON schedule_table "
 200                                 " WHEN old.is_deleted = 1 AND old.calendar_id = (SELECT id FROM calendar_table WHERE id = old.calendar_id) "
 201                                 " BEGIN "
 202                                 "   INSERT INTO deleted_table VALUES(old.id, old.type + 1, old.calendar_id, old.changed_ver, old.created_ver, old.original_event_id);"
 203                                 " END;",
 204                                 NULL, 0, &errmsg);
 205                 if (SQLITE_OK != ret) {
 206                         ERR("CREATE TRIGGER trg_schedule_del3 failed(%d:%s)", ret, errmsg);
 207                         sqlite3_free(errmsg);
 208                 }
 209
 210                 /* add field: original_event_id in deleted_table */
 211                 ret = sqlite3_exec(__db, "ALTER TABLE deleted_table ADD COLUMN original_event_id INTEGER", NULL, 0, &errmsg);
 212                 if (SQLITE_OK != ret) {
 213                         ERR("ALTER TABLE deleted_table failed(%d:%s)", ret, errmsg);
 214                         sqlite3_free(errmsg);
 215                 }
 216                 /* ----------------------- end modified 2014/07/02
 217                 */
 218                 old_version = 102;
 219         }
 220         if (old_version == 102) {
 221                 /* ----------------------- start modified 2014/10/24
 222                  * added field is_alldy on schedule_table
 223                  */
 224
 225                 /* add field: is_allday in schedule_table */
 226                 ret = sqlite3_exec(__db, "ALTER TABLE schedule_table ADD COLUMN is_allday INTEGER DEFAULT 0", NULL, 0, &errmsg);
 227                 if (SQLITE_OK != ret) {
 228                         ERR("ALTER TABLE schedule_table failed(%d:%s)", ret, errmsg);
 229                         sqlite3_free(errmsg);
 230                 }
 231                 /* ----------------------- end modified 2014/10/24
 232                 */
 233                 old_version = 103;
 234         }
 235
 236         if (old_version == 103) {
 237                 ret = sqlite3_exec(__db, "DROP TABLE reminder_table", NULL, 0, &errmsg);
 238                 if (SQLITE_OK != ret) {
 239                         ERR("DROP TABLE reminder_table failed(%d:%s)", ret, errmsg);
 240                         sqlite3_free(errmsg);
 241                 }
 242                 old_version = 104;
 243         }
 244         if (old_version == 104) {
 245                 ret = sqlite3_exec(__db, "ALTER TABLE alarm_table ADD COLUMN alarm_utime INTEGER", NULL, 0, &errmsg);
 246                 if (SQLITE_OK != ret) {
 247                         ERR("ALTER TABLE schedule_table failed(%d:%s)", ret, errmsg);
 248                         sqlite3_free(errmsg);
 249                 }
 250                 ret = sqlite3_exec(__db, "ALTER TABLE alarm_table ADD COLUMN alarm_datetime DATE", NULL, 0, &errmsg);
 251                 if (SQLITE_OK != ret) {
 252                         ERR("ALTER TABLE schedule_table failed(%d:%s)", ret, errmsg);
 253                         sqlite3_free(errmsg);
 254                 }
 255                 ret = sqlite3_exec(__db, "DROP VIEW allday_instance_view", NULL, 0, &errmsg);
 256                 if (SQLITE_OK != ret) {
 257                         ERR("sqlite3_exec() failed: DROP VIEW allday_instance_view(%d) [%s]", ret, errmsg);
 258                         sqlite3_free(errmsg);
 259                 }
 260                 ret = sqlite3_exec(__db, "DROP VIEW allday_instance_view_extended", NULL, 0, &errmsg);
 261                 if (SQLITE_OK != ret) {
 262                         ERR("sqlite3_exec() failed: DROP VIEW allday_instance_view_extended(%d) [%s]", ret, errmsg);
 263                         sqlite3_free(errmsg);
 264                 }
 265                 old_version = 105;
 266         }
 267         if (old_version == 105) {
 268                 /* ----------------------- start modified 2016/07/14
 269                  * modify trigger trg_schedule_del
 270                  * to delete alarm, attendee records of exception record
 271                  */
 272                 ret = sqlite3_exec(__db, "DROP trigger trg_schedule_del", NULL, 0, &errmsg);
 273                 if (SQLITE_OK != ret) {
 274                         ERR("sqlite3_exec() Fail(%d)[%s]: DROP trigger trg_schedule_del", ret, errmsg);
 275                         sqlite3_free(errmsg);
 276                 }
 277                 ret = sqlite3_exec(__db,
 278                                 "CREATE TRIGGER trg_schedule_del AFTER DELETE ON schedule_table "
 279                                 "BEGIN "
 280                                 "  DELETE FROM rrule_table WHERE event_id = old.id;"
 281                                 "  DELETE FROM alarm_table WHERE event_id = old.id;"
 282                                 "  DELETE FROM attendee_table WHERE event_id = old.id;"
 283                                 "  DELETE FROM normal_instance_table WHERE event_id = old.id;"
 284                                 "  DELETE FROM allday_instance_table WHERE event_id = old.id;"
 285                                 "  DELETE FROM extended_table WHERE record_id = old.id AND record_type = 2;"
 286                                 "  DELETE FROM extended_table WHERE record_id = old.id AND record_type = 3;"
 287                                 "  DELETE FROM alarm_table WHERE event_id = (SELECT id FROM schedule_table WHERE original_event_id = old.id);"
 288                                 "  DELETE FROM attendee_table WHERE event_id = (SELECT id FROM schedule_table WHERE original_event_id = old.id);"
 289                                 "  DELETE FROM normal_instance_table WHERE event_id = (SELECT id FROM schedule_table WHERE original_event_id = old.id);"
 290                                 "  DELETE FROM allday_instance_table WHERE event_id = (SELECT id FROM schedule_table WHERE original_event_id = old.id);"
 291                                 "  DELETE FROM extended_table WHERE record_id = (SELECT id FROM schedule_table WHERE original_event_id = old.id) AND record_type = 2;"
 292                                 "  DELETE FROM extended_table WHERE record_id = (SELECT id FROM schedule_table WHERE original_event_id = old.id) AND record_type = 3;"
 293                                 "  DELETE FROM schedule_table WHERE original_event_id = old.id;"
 294                                 "END;",
 295                                 NULL, 0, &errmsg);
 296                 if (SQLITE_OK != ret) {
 297                         ERR("CREATE TRIGGER trg_schedule_del failed(%d:%s)", ret, errmsg);
 298                         sqlite3_free(errmsg);
 299
 300                 }
 301                 /* ----------------------- end modified 2016/07/14
 302                 */
 303                 old_version = 106;
 304         }
 305         if (old_version == 106) {
 306                 /* ----------------------- start modified 2016/09/06
 307                  * change journal_mode to WAL
 308                  */
 309                 ret = sqlite3_exec(__db, "PRAGMA journal_mode = WAL", NULL, 0, &errmsg);
 310                 if (SQLITE_OK != ret) {
 311                         ERR("sqlite3_exec() Fail(%d)[%s]: PRAGMA journal_mode = WAL", ret, errmsg);
 312                         sqlite3_free(errmsg);
 313                 }
 314                 /* ----------------------- end modified 2016/09/06
 315                 */
 316                 old_version = 107;
 317         }
 318         if (old_version == 107) {
 319                 /*
 320                  * ----------------------- start modified 2017/04/17
 321                  */
 322
 323                 /* add field: link_count in schedule_table */
 324                 ret = sqlite3_exec(__db, "ALTER TABLE schedule_table ADD COLUMN link_count INTEGER DEFAULT 0", NULL, 0, &errmsg);
 325                 if (SQLITE_OK != ret) {
 326                         ERR("ALTER TABLE schedule_table failed(%d:%s)", ret, errmsg);
 327                         sqlite3_free(errmsg);
 328                 }
 329                 /* add field: link_base_id in schedule_table */
 330                 ret = sqlite3_exec(__db, "ALTER TABLE schedule_table ADD COLUMN link_base_id INTEGER DEFAULT 0", NULL, 0, &errmsg);
 331                 if (SQLITE_OK != ret) {
 332                         ERR("ALTER TABLE schedule_table failed(%d:%s)", ret, errmsg);
 333                         sqlite3_free(errmsg);
 334                 }
 335                 /* add trigger: update record when link_count is modified */
 336                 ret = sqlite3_exec(__db,
 337                                 "CREATE TRIGGER trg_link_mod AFTER UPDATE OF link_count ON schedule_table "
 338                                 " BEGIN "
 339                                 "       UPDATE schedule_table SET link_count = link_count + (SELECT link_count FROM schedule_table WHERE link_base_id = new.id) WHERE id = new.id; " /* base is linked to new base */
 340                                 "       UPDATE schedule_table SET link_base_id = new.id WHERE link_base_id = (SELECT id FROM schedule_table WHERE link_base_id = new.id); " /* new is linked to base */
 341                                 "       UPDATE schedule_table SET link_count = 0 WHERE link_base_id = new.id; " /* set count 0 */
 342                                 " END; ",
 343                                 NULL, 0, &errmsg);
 344                 if (SQLITE_OK != ret) {
 345                         ERR("CREATE TRIGGER trg_link_mod failed(%d:%s)", ret, errmsg);
 346                         sqlite3_free(errmsg);
 347                 }
 348
 349                 /* add trigger: update record when link record is deleted */
 350                 ret = sqlite3_exec(__db,
 351                                 "CREATE TRIGGER trg_link_del BEFORE DELETE ON schedule_table "
 352                                 " BEGIN "
 353                                 "       UPDATE schedule_table SET link_base_id = 0 WHERE link_base_id = old.id; " /* when base record is deleted. */
 354                                 "       UPDATE schedule_table SET link_count = link_count -1 WHERE id = old.link_base_id; " /* when linked record is deleted. */
 355                                 " END; ",
 356                                 NULL, 0, &errmsg);
 357                 if (SQLITE_OK != ret) {
 358                         ERR("CREATE TRIGGER trg_link_mod failed(%d:%s)", ret, errmsg);
 359                         sqlite3_free(errmsg);
 360                 }
 361
 362                  /* ----------------------- end modified 2017/04/17
 363                  */
 364                 old_version = __USER_VERSION;
 365         }
 366
 367         /* drop views */
 368         snprintf(query, sizeof(query), "DROP VIEW IF EXISTS event_view");
 369         ret = sqlite3_exec(__db, query, NULL, 0, &errmsg);
 370         if (SQLITE_OK != ret) {
 371                 ERR("sqlite3_exec() failed(%d) [%s]", ret, errmsg);
 372                 sqlite3_free(errmsg);
 373                 db_util_close(__db);
 374                 return CALENDAR_ERROR_SYSTEM;
 375         }
 376         snprintf(query, sizeof(query), "DROP VIEW IF EXISTS todo_view");
 377         sqlite3_exec(__db, query, NULL, 0, &errmsg);
 378         snprintf(query, sizeof(query), "DROP VIEW IF EXISTS event_book_view");
 379         sqlite3_exec(__db, query, NULL, 0, &errmsg);
 380         snprintf(query, sizeof(query), "DROP VIEW IF EXISTS todo_book_view");
 381         sqlite3_exec(__db, query, NULL, 0, &errmsg);
 382         snprintf(query, sizeof(query), "DROP VIEW IF EXISTS event_calendar_attendee_view");
 383         sqlite3_exec(__db, query, NULL, 0, &errmsg);
 384         snprintf(query, sizeof(query), "DROP VIEW IF EXISTS utime_instance_view");
 385         sqlite3_exec(__db, query, NULL, 0, &errmsg);
 386         snprintf(query, sizeof(query), "DROP VIEW IF EXISTS localtime_instance_view");
 387         sqlite3_exec(__db, query, NULL, 0, &errmsg);
 388         snprintf(query, sizeof(query), "DROP VIEW IF EXISTS utime_instance_view_extended");
 389         sqlite3_exec(__db, query, NULL, 0, &errmsg);
 390         snprintf(query, sizeof(query), "DROP VIEW IF EXISTS localtime_instance_view_extended");
 391         sqlite3_exec(__db, query, NULL, 0, &errmsg);
 392
 393
 394         /* update DB user_version */
 395         snprintf(query, sizeof(query), "PRAGMA user_version = %d", __USER_VERSION);
 396         ret = sqlite3_exec(__db, query, NULL, 0, &errmsg);
 397         if (SQLITE_OK != ret) {
 398                 ERR("sqlite3_exec() failed(%d) [%s]", ret, errmsg);
 399                 sqlite3_free(errmsg);
 400                 db_util_close(__db);
 401                 return CALENDAR_ERROR_SYSTEM;
 402         }
 403         db_util_close(__db);
 404         __db = NULL;
 405
 406         return CALENDAR_ERROR_NONE;
 407 }
 408 /* LCOV_EXCL_STOP */
 409
 410