src/com/morphoss/acal/database/AcalDBHelper.java

   1 /*
   2  * Copyright (C) 2011 Morphoss Ltd
   3  *
   4  * This program is free software: you can redistribute it and/or modify
   5  * it under the terms of the GNU General Public License as
   6  * published by the Free Software Foundation, either version 3 of the
   7  * License, or (at your option) any later version.
   8  *
   9  * This program is distributed in the hope that it will be useful,
  10  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12  * GNU General Public License for more details.
  13  *
  14  * You should have received a copy of the GNU General Public License
  15  * along with this program.  If not, see <http://www.gnu.org/licenses/>.
  16  *
  17  */
  18
  19 package com.morphoss.acal.database;
  20
  21 import android.content.Context;
  22 import android.database.sqlite.SQLiteDatabase;
  23 import android.database.sqlite.SQLiteOpenHelper;
  24 import android.util.Log;
  25
  26 import com.morphoss.acal.providers.Servers;
  27
  28 /**
  29  * <p>
  30  * This class is responsible for maintaining, creating and upgrading our
  31  * database. MUST be used by any other class that needs to access the
  32  * database directly.
  33  * </p>
  34  *
  35  * @author Morphoss Ltd
  36  *
  37  */
  38 public class AcalDBHelper extends SQLiteOpenHelper {
  39
  40         public static final String TAG = "AcalDBHelper";
  41
  42         /**
  43          * The name of the database, which will be stored in:
  44          *    /data/data/com.morphoss.acal/databases/[DB_NAME].db
  45          */
  46         public static final String DB_NAME = "acal";
  47
  48         /**
  49          * The version of this database. Used to determine if an upgrade is required.
  50          */
  51         public static final int DB_VERSION = 19;
  52
  53
  54
  55         /**
  56          * <p>The dav_server Table as stated in the specification.</p>
  57          */
  58         public static final String DAV_SERVER_TABLE_SQL =
  59                         "CREATE TABLE dav_server ("
  60                                 +"_id INTEGER PRIMARY KEY AUTOINCREMENT"
  61                                 +",friendly_name TEXT"
  62                                 +",supplied_user_url TEXT"
  63                                 +",supplied_path TEXT"
  64                                 +",use_ssl BOOLEAN"
  65                                 +",hostname TEXT"
  66                                 +",port INTEGER"
  67                                 +",principal_path TEXT"
  68                                 +",auth_type INTEGER"
  69                                 +",username TEXT"
  70                                 +",password TEXT"
  71                                 +",has_srv BOOLEAN"
  72                                 +",has_wellknown BOOLEAN"
  73                                 +",has_caldav BOOLEAN"
  74                                 +",has_multiget BOOLEAN"
  75                                 +",has_sync BOOLEAN"
  76                                 +",active BOOLEAN"
  77                                 +",last_checked DATETIME"
  78                                 +",use_advanced BOOLEAN"
  79                                 +",prepared_config TEXT"
  80                                 +",UNIQUE(use_ssl,hostname,port,principal_path,username)"
  81                         +")";
  82
  83         /**
  84          * <p>The dav_path_set table holds the paths which are collections containing
  85          * the collections we are <em>really</em> interested in.  We use this for
  86          * holding the responses to calendar-home-set, addressbook-home-set and
  87          * principal-collection-set properties retrieved from the server.</p>
  88          */
  89         public static final String DAV_PATH_SET_TABLE_SQL =
  90                         "CREATE TABLE dav_path_set ("
  91                                 +"_id INTEGER PRIMARY KEY AUTOINCREMENT"
  92                                 +",server_id INTEGER REFERENCES dav_server(_id)"
  93                                 +",set_type INT"
  94                                 +",path TEXT"
  95                                 +",collection_tag TEXT"
  96                                 +",last_checked DATETIME"
  97                                 +",needs_sync BOOLEAN"
  98                                 +",UNIQUE(server_id, set_type, path)"
  99                         +");";
 100
 101         /**
 102          * <p>The dav_collection holds information about the collections which we
 103          * synchronise with the server.</p>
 104          */
 105         public static final String DAV_COLLECTION_TABLE_SQL =
 106                         "CREATE TABLE dav_collection ("
 107                                 +"_id INTEGER PRIMARY KEY AUTOINCREMENT"
 108                                 +",server_id INTEGER REFERENCES dav_server(_id)"
 109                                 +",collection_path TEXT"
 110                                 +",displayname TEXT"
 111                                 +",holds_events BOOLEAN"
 112                                 +",holds_tasks BOOLEAN"
 113                                 +",holds_journal BOOLEAN"
 114                                 +",holds_addressbook BOOLEAN"
 115                                 +",active_events BOOLEAN"
 116                                 +",active_tasks BOOLEAN"
 117                                 +",active_journal BOOLEAN"
 118                                 +",active_addressbook BOOLEAN"
 119                                 +",last_synchronised DATETIME"
 120                                 +",needs_sync BOOLEAN"
 121                                 +",sync_token TEXT"
 122                                 +",collection_tag TEXT"
 123                                 +",default_timezone TEXT"
 124                                 +",colour TEXT"
 125                                 +",use_alarms BOOLEAN"
 126                                 +",max_sync_age_wifi INTEGER"
 127                                 +",max_sync_age_3g INTEGER"
 128                                 +",is_writable BOOLEAN"
 129                                 +",is_visible BOOLEAN"
 130                                 +",sync_metadata BOOLEAN"
 131                                 +",UNIQUE(server_id,collection_path)"
 132                         +");";
 133
 134         /**
 135          * <p>The dav_resource stores the resources (vevents, vtodos, vjournals & vcards)</p>
 136          */
 137         public static final String DAV_RESOURCE_TABLE_SQL =
 138                         "CREATE TABLE dav_resource ("
 139                                 +"_id INTEGER PRIMARY KEY AUTOINCREMENT"
 140                                 +",collection_id INTEGER REFERENCES dav_collection(_id)"
 141                                 +",name TEXT"
 142                                 +",etag TEXT"
 143                                 +",last_modified DATETIME"
 144                                 +",content_type TEXT"
 145                                 +",data BLOB"
 146                                 +",needs_sync BOOLEAN"
 147                                 +",earliest_start NUMERIC"
 148                                 +",latest_end NUMERIC"
 149                                 +",effective_type TEXT"
 150                                 +",UNIQUE(collection_id,name)"
 151                         +");";
 152
 153
 154         /**
 155          * <p>Some indexes</p>
 156          */
 157         public static final String EVENT_INDEX_SQL =
 158                 "CREATE UNIQUE INDEX event_select_idx ON dav_resource ( effective_type, collection_id, latest_end, _id );";
 159         public static final String TODO_INDEX_SQL =
 160                 "CREATE UNIQUE INDEX todo_select_idx ON dav_resource ( effective_type, collection_id, _id );";
 161
 162
 163         /**
 164          * The pending_change, containing the fully constructed resource we want
 165          * to PUT to the server when we can.
 166          *
 167          * In the event of a local CREATE pending the 'old_data' blob will be NULL.
 168          * In the event of a local DELETE pending the 'new_data' blob will be NULL.
 169          *
 170          * The SHOULD only be one pending_change active for a resource, and multiple
 171          * changes SHOULD be merged where that is possible (i.e. where the status
 172          * does not indicate it has already been submitted to the server and we are
 173          * merely waiting to see it back again...
 174          */
 175         public static final String PENDING_CHANGE_TABLE_SQL =
 176                         "CREATE TABLE pending_change ("
 177                         +"_id INTEGER PRIMARY KEY AUTOINCREMENT"
 178                                 +",collection_id INTEGER REFERENCES dav_collection(_id)"
 179                                 +",resource_id INTEGER REFERENCES dav_resource(_id)"
 180                                 +",old_data BLOB"
 181                                 +",new_data BLOB"
 182                                 +",uid TEXT"
 183                                 +",UNIQUE(collection_id,resource_id)"
 184                         +");";
 185
 186
 187         /**
 188          * A Table for storing data pertinent to the Show Upcoming Widget.
 189          * Introduced into version 13.
 190          */
 191         public static final String SHOW_UPCOMING_WIDGET_TABLE_SQL =
 192                 "CREATE TABLE show_upcoming_widget_data ("
 193                 +"_id INTEGER PRIMARY KEY AUTOINCREMENT"
 194                         +",resource_id INTEGER REFERENCES dav_resource(_id)"
 195                         +",etag TEXT"
 196                         +",colour INTEGER"
 197                         +",dtstart NUMERIC"
 198                         +",dtend NUMERIC"
 199                         +",summary TEXT"
 200                 +");";
 201
 202
 203         /**
 204          * Used for caching event data
 205          */
 206         public static final String RESOURCE_CACHE_TABLE_SQL =
 207                 "CREATE TABLE event_cache ("
 208                 +"_id INTEGER PRIMARY KEY AUTOINCREMENT"
 209                         +",resource_id INTEGER REFERENCES dav_resource(_id)"
 210                         +",resource_type TEXT"
 211                         +",recurrence_id TEXT"
 212                         +",collection_id NUMERIC"
 213                         +",summary TEXT"
 214                         +",location TEXT"
 215                         +",dtstart NUMERIC"
 216                         +",dtend NUMERIC"
 217                         +",completed BOOLEAN"
 218                         +",dtstartfloat BOOLEAN"
 219                         +",dtendfloat BOOLEAN"
 220                         +",completedfloat BOOLEAN"
 221                         +",flags INTEGER"
 222                 +");";
 223
 224         public static final String RESOURCE_CACHE_META_TABLE_SQL =
 225                 "CREATE TABLE event_cache_meta ("
 226                 +"_id INTEGER PRIMARY KEY AUTOINCREMENT"
 227                         +",dtstart NUMERIC"
 228                         +",dtend NUMERIC"
 229                         +",count INTEGER"
 230                         +",closed BOOLEAN"
 231                 +");";
 232
 233         public static final String ALARM_TABLE_SQL =
 234                 "CREATE TABLE alarms ("
 235                 +"_id INTEGER PRIMARY KEY AUTOINCREMENT"
 236                         +",ttf NUMERIC"
 237                         +",rid NUMERIC"
 238                         +",rrid TEXT"
 239                         +",state NUMERIC"
 240                         +", blob TEXT"
 241                 +");";
 242
 243         public static final String ALARM_META_TABLE_SQL =
 244                 "CREATE TABLE alarm_meta ("
 245                 +"_id INTEGER PRIMARY KEY AUTOINCREMENT"
 246                         +",closed BOOLEAN"
 247                 +");";
 248
 249         private Context context;
 250
 251         /**
 252          * Visible single argument constructor. Calls super with default values.
 253          *
 254          * @param context The context in which this DB will be used.
 255          * @author Morphoss Ltd
 256          */
 257         public AcalDBHelper (Context context) {
 258                 super (context, DB_NAME+".db", null, DB_VERSION);
 259                 this.context = context;
 260         }
 261
 262         /**
 263          * <p>
 264          * Called when database is first instantiated. Creates default schema. Will add
 265          * test data if Constants.CREATE_TEST_ENTRIES is true.
 266          * </p>
 267          *
 268          * @see android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite.SQLiteDatabase)
 269          * @author Morphoss Ltd
 270          */
 271         @Override
 272         public void onCreate(SQLiteDatabase db) {
 273                 db.beginTransaction();
 274                 // Create Database:
 275                 db.execSQL(DAV_SERVER_TABLE_SQL);
 276                 db.execSQL(DAV_PATH_SET_TABLE_SQL);
 277                 db.execSQL(DAV_COLLECTION_TABLE_SQL);
 278                 db.execSQL(DAV_RESOURCE_TABLE_SQL);
 279                 db.execSQL(PENDING_CHANGE_TABLE_SQL);
 280
 281                 db.execSQL(EVENT_INDEX_SQL);
 282                 db.execSQL(TODO_INDEX_SQL);
 283
 284                 db.execSQL(RESOURCE_CACHE_TABLE_SQL);
 285                 db.execSQL(RESOURCE_CACHE_META_TABLE_SQL);
 286
 287                 db.execSQL(SHOW_UPCOMING_WIDGET_TABLE_SQL);
 288                 db.execSQL(ALARM_TABLE_SQL);
 289                 db.execSQL(ALARM_META_TABLE_SQL);
 290
 291                 db.setTransactionSuccessful();
 292                 db.endTransaction();
 293         }
 294
 295         /**
 296          * <p>
 297          * Executed if exiting DB version does not match the one defined by DB_VERSION.
 298          * Currently drops all but the dav_server table and recreates them.  It will
 299          * eventually do a rescan of the servers after that, re-discovering collections
 300          * and rebuilding our local cache.
 301          * </p>
 302          *
 303          * @see android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite.SQLiteDatabase, int, int)
 304          * @author Morphoss Ltd
 305          */
 306         @Override
 307         public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 308                 // We drop tables in the reverse order to avoid constraint issues
 309                 db.beginTransaction();
 310
 311                 if ( oldVersion == 9 ) {
 312                         db.execSQL("ALTER TABLE dav_collection ADD COLUMN sync_metadata BOOLEAN");
 313                         oldVersion++;
 314                 }
 315
 316                 if ( oldVersion == 10 ) {
 317                         db.execSQL("ALTER TABLE dav_server ADD COLUMN use_advanced BOOLEAN");
 318                         db.execSQL("ALTER TABLE dav_server ADD COLUMN prepared_config TEXT");
 319                         oldVersion++;
 320                 }
 321                 if ( oldVersion == 11 ) {
 322                         db.execSQL("ALTER TABLE dav_resource ADD COLUMN effective_type TEXT");
 323                         db.execSQL("UPDATE dav_resource SET effective_type = 'VCARD' WHERE lower(data) LIKE 'begin:vcard';");
 324                         db.execSQL("UPDATE dav_resource SET effective_type = 'VEVENT' WHERE lower(data) LIKE 'begin:vevent';");
 325                         db.execSQL("UPDATE dav_resource SET effective_type = 'VJOURNAL' WHERE lower(data) LIKE 'begin:vjournal';");
 326                         db.execSQL("UPDATE dav_resource SET effective_type = 'VTODO' WHERE lower(data) LIKE 'begin:vtodo';");
 327                         db.execSQL(EVENT_INDEX_SQL);
 328                         db.execSQL(TODO_INDEX_SQL);
 329                         oldVersion++;
 330                 }
 331                 if ( oldVersion == 12 ) {
 332                         db.execSQL("PRAGMA writable_schema = 1");
 333                         db.execSQL("UPDATE SQLITE_MASTER SET SQL = '"+DAV_SERVER_TABLE_SQL+"' WHERE name = '"+Servers.DATABASE_TABLE+"'");
 334                         db.execSQL("PRAGMA writable_schema = 0");
 335                         oldVersion++;
 336                 }
 337
 338                 if ( oldVersion == 13 ) {
 339                         db.execSQL(SHOW_UPCOMING_WIDGET_TABLE_SQL);
 340                         oldVersion++;
 341                 }
 342
 343                 if (oldVersion == 14) {
 344                         db.execSQL("DROP TABLE show_upcoming_widget_data");
 345                         db.execSQL(SHOW_UPCOMING_WIDGET_TABLE_SQL);
 346                         oldVersion++;
 347                 }
 348
 349                 if (oldVersion == 15) {
 350                         db.execSQL(RESOURCE_CACHE_TABLE_SQL);
 351                         db.execSQL(RESOURCE_CACHE_META_TABLE_SQL);
 352                         oldVersion++;
 353                 }
 354
 355                 if (oldVersion == 16) {
 356                         db.execSQL("DROP TABLE event_cache");
 357                         db.execSQL(RESOURCE_CACHE_TABLE_SQL);
 358                         oldVersion++;
 359                 }
 360
 361                 if (oldVersion == 17) {
 362                         db.execSQL("DROP TABLE pending_change");
 363                         db.execSQL(PENDING_CHANGE_TABLE_SQL);
 364                         oldVersion++;
 365                 }
 366                 if (oldVersion == 18) {
 367                         db.execSQL(ALARM_TABLE_SQL);
 368                         db.execSQL(ALARM_META_TABLE_SQL);
 369                         oldVersion++;
 370                 }
 371
 372                 if ( oldVersion != newVersion ) {
 373                         // Fallback to try and drop all tables, except the server table and
 374                         // then recreate them.
 375                         try {
 376                                 // Drop all the tables except the dav_server one.
 377                                 db.execSQL("DROP TABLE event_cache_meta");
 378                                 db.execSQL("DROP TABLE event_cache");
 379                                 db.execSQL("DROP TABLE show_upcoming_widget_data");
 380                                 db.execSQL("DROP TABLE dav_path_set");
 381                                 db.execSQL("DROP TABLE dav_collection");
 382                                 db.execSQL("DROP TABLE dav_resource");
 383                                 db.execSQL("DROP TABLE pending_change");
 384
 385                                 // Recreate the tables we just dropped.
 386                                 db.execSQL(DAV_PATH_SET_TABLE_SQL);
 387                                 db.execSQL(DAV_COLLECTION_TABLE_SQL);
 388                                 db.execSQL(DAV_RESOURCE_TABLE_SQL);
 389                                 db.execSQL(PENDING_CHANGE_TABLE_SQL);
 390                                 db.execSQL(EVENT_INDEX_SQL);
 391                                 db.execSQL(TODO_INDEX_SQL);
 392                                 db.execSQL(RESOURCE_CACHE_TABLE_SQL);
 393                                 db.execSQL(RESOURCE_CACHE_META_TABLE_SQL);
 394                                 db.execSQL(SHOW_UPCOMING_WIDGET_TABLE_SQL);
 395                                 db.setTransactionSuccessful();
 396                         }
 397                         catch( Exception e ) {
 398                                 Log.e(TAG, "Database error recreating database", e);
 399                         }
 400                 }
 401                 else {
 402                         db.setTransactionSuccessful();
 403                 }
 404
 405                 db.endTransaction();
 406
 407         }
 408
 409         @Override
 410         public void onOpen(SQLiteDatabase db) {
 411                 super.onOpen(db);
 412
 413         //      Log.e(TAG,"Database opened: ");
 414         //      Thread.dumpStack();
 415         }
 416
 417
 418         public SQLiteDatabase getAcalReadableDatabase() {
 419                 try {
 420                         return SQLiteDatabase.openDatabase(context.getDatabasePath(DB_NAME+".db").toString(), null,
 421                                         SQLiteDatabase.OPEN_READONLY | SQLiteDatabase.NO_LOCALIZED_COLLATORS);
 422                 }
 423                 catch( Exception e) {
 424                         Log.w(TAG,Log.getStackTraceString(e));
 425                 }
 426                 return getReadableDatabase();
 427         }
 428
 429
 430         public SQLiteDatabase getAcalWritableDatabase() {
 431                 try {
 432                         return SQLiteDatabase.openDatabase(context.getDatabasePath(DB_NAME+".db").toString(), null,
 433                                         SQLiteDatabase.OPEN_READWRITE | SQLiteDatabase.NO_LOCALIZED_COLLATORS);
 434                 }
 435                 catch( Exception e) {
 436                         Log.w(TAG,Log.getStackTraceString(e));
 437                 }
 438                 return getReadableDatabase();
 439         }
 440
 441 }