diff options
Diffstat (limited to 'app/src/main/java/it/niedermann/nextcloud/deck/persistence/sync/adapters/db/DeckDatabase.java')
| -rw-r--r-- | app/src/main/java/it/niedermann/nextcloud/deck/persistence/sync/adapters/db/DeckDatabase.java | 256 |
1 files changed, 253 insertions, 3 deletions
diff --git a/app/src/main/java/it/niedermann/nextcloud/deck/persistence/sync/adapters/db/DeckDatabase.java b/app/src/main/java/it/niedermann/nextcloud/deck/persistence/sync/adapters/db/DeckDatabase.java index 0e86557fa..a491946ed 100644 --- a/app/src/main/java/it/niedermann/nextcloud/deck/persistence/sync/adapters/db/DeckDatabase.java +++ b/app/src/main/java/it/niedermann/nextcloud/deck/persistence/sync/adapters/db/DeckDatabase.java @@ -1,8 +1,11 @@ package it.niedermann.nextcloud.deck.persistence.sync.adapters.db; import android.content.Context; +import android.content.SharedPreferences; import android.database.Cursor; +import android.graphics.Color; +import androidx.annotation.ColorInt; import androidx.annotation.NonNull; import androidx.preference.PreferenceManager; import androidx.room.Database; @@ -12,6 +15,7 @@ import androidx.room.TypeConverters; import androidx.room.migration.Migration; import androidx.sqlite.db.SupportSQLiteDatabase; +import it.niedermann.android.util.ColorUtil; import it.niedermann.nextcloud.deck.DeckLog; import it.niedermann.nextcloud.deck.api.LastSyncUtil; import it.niedermann.nextcloud.deck.model.AccessControl; @@ -28,10 +32,16 @@ import it.niedermann.nextcloud.deck.model.Label; import it.niedermann.nextcloud.deck.model.Permission; import it.niedermann.nextcloud.deck.model.Stack; import it.niedermann.nextcloud.deck.model.User; +import it.niedermann.nextcloud.deck.model.appwidgets.StackWidgetModel; import it.niedermann.nextcloud.deck.model.enums.DBStatus; import it.niedermann.nextcloud.deck.model.ocs.Activity; import it.niedermann.nextcloud.deck.model.ocs.comment.DeckComment; import it.niedermann.nextcloud.deck.model.ocs.comment.Mention; +import it.niedermann.nextcloud.deck.model.ocs.projects.JoinCardWithProject; +import it.niedermann.nextcloud.deck.model.ocs.projects.OcsProject; +import it.niedermann.nextcloud.deck.model.ocs.projects.OcsProjectResource; +import it.niedermann.nextcloud.deck.model.relations.UserInBoard; +import it.niedermann.nextcloud.deck.model.relations.UserInGroup; import it.niedermann.nextcloud.deck.model.widget.singlecard.SingleCardWidgetModel; import it.niedermann.nextcloud.deck.persistence.sync.SyncWorker; import it.niedermann.nextcloud.deck.persistence.sync.adapters.db.dao.AccessControlDao; @@ -49,9 +59,15 @@ import it.niedermann.nextcloud.deck.persistence.sync.adapters.db.dao.JoinCardWit import it.niedermann.nextcloud.deck.persistence.sync.adapters.db.dao.LabelDao; import it.niedermann.nextcloud.deck.persistence.sync.adapters.db.dao.MentionDao; import it.niedermann.nextcloud.deck.persistence.sync.adapters.db.dao.PermissionDao; -import it.niedermann.nextcloud.deck.persistence.sync.adapters.db.dao.SingleCardWidgetModelDao; import it.niedermann.nextcloud.deck.persistence.sync.adapters.db.dao.StackDao; import it.niedermann.nextcloud.deck.persistence.sync.adapters.db.dao.UserDao; +import it.niedermann.nextcloud.deck.persistence.sync.adapters.db.dao.UserInBoardDao; +import it.niedermann.nextcloud.deck.persistence.sync.adapters.db.dao.UserInGroupDao; +import it.niedermann.nextcloud.deck.persistence.sync.adapters.db.dao.projects.JoinCardWithOcsProjectDao; +import it.niedermann.nextcloud.deck.persistence.sync.adapters.db.dao.projects.OcsProjectDao; +import it.niedermann.nextcloud.deck.persistence.sync.adapters.db.dao.projects.OcsProjectResourceDao; +import it.niedermann.nextcloud.deck.persistence.sync.adapters.db.dao.widgets.SingleCardWidgetModelDao; +import it.niedermann.nextcloud.deck.persistence.sync.adapters.db.dao.widgets.StackWidgetModelDao; @Database( entities = { @@ -73,9 +89,15 @@ import it.niedermann.nextcloud.deck.persistence.sync.adapters.db.dao.UserDao; DeckComment.class, Mention.class, SingleCardWidgetModel.class, + StackWidgetModel.class, + OcsProject.class, + OcsProjectResource.class, + JoinCardWithProject.class, + UserInGroup.class, + UserInBoard.class, }, exportSchema = false, - version = 15 + version = 23 ) @TypeConverters({DateTypeConverter.class}) public abstract class DeckDatabase extends RoomDatabase { @@ -175,8 +197,204 @@ public abstract class DeckDatabase extends RoomDatabase { } }; - public static final RoomDatabase.Callback ON_CREATE_CALLBACK = new RoomDatabase.Callback() { + private static final Migration MIGRATION_15_16 = new Migration(15, 16) { + @Override + public void migrate(SupportSQLiteDatabase database) { + database.execSQL("CREATE TABLE `StackWidgetModel` (`appWidgetId` INTEGER PRIMARY KEY, `accountId` INTEGER, `stackId` INTEGER, `darkTheme` INTEGER CHECK (`darkTheme` IN (0,1)) NOT NULL, " + + "FOREIGN KEY(`accountId`) REFERENCES `Account`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE, " + + "FOREIGN KEY(`stackId`) REFERENCES `Stack`(`localId`) ON UPDATE NO ACTION ON DELETE CASCADE )"); + database.execSQL("CREATE INDEX `index_StackWidgetModel_stackId` ON `StackWidgetModel` (`stackId`)"); + database.execSQL("CREATE INDEX `index_StackWidgetModel_accountId` ON `StackWidgetModel` (`accountId`)"); + } + }; + + private static final Migration MIGRATION_16_17 = new Migration(16, 17) { + @Override + public void migrate(SupportSQLiteDatabase database) { + database.execSQL("CREATE TABLE `OcsProject` (`localId` INTEGER PRIMARY KEY AUTOINCREMENT, `accountId` INTEGER NOT NULL, `id` INTEGER, `name` TEXT NOT NULL, `status` INTEGER NOT NULL, `lastModified` INTEGER, `lastModifiedLocal` INTEGER)"); + database.execSQL("CREATE UNIQUE INDEX `index_OcsProject_accountId_id` ON `OcsProject` (`accountId`, `id`)"); + database.execSQL("CREATE INDEX `index_project_accID` ON `OcsProject` (`accountId`)"); + database.execSQL("CREATE INDEX `index_OcsProject_id` ON `OcsProject` (`id`)"); + database.execSQL("CREATE INDEX `index_OcsProject_lastModifiedLocal` ON `OcsProject` (`lastModifiedLocal`)"); + + database.execSQL("CREATE TABLE `OcsProjectResource` (`localId` INTEGER PRIMARY KEY AUTOINCREMENT, `accountId` INTEGER NOT NULL, `id` INTEGER, `name` TEXT, `status` INTEGER NOT NULL, `lastModified` INTEGER, `lastModifiedLocal` INTEGER, `projectId` INTEGER NOT NULL, `type` TEXT , `link` TEXT , `path` TEXT, `iconUrl` TEXT , `previewAvailable` INTEGER, `mimetype` TEXT, FOREIGN KEY(`projectId`) REFERENCES `OcsProject`(`localId`) ON UPDATE NO ACTION ON DELETE CASCADE)"); + database.execSQL("CREATE INDEX `index_projectResource_accID` ON `OcsProjectResource` (`accountId`)"); + database.execSQL("CREATE INDEX `index_projectResource_projectId` ON `OcsProjectResource` (`projectId`)"); + database.execSQL("CREATE UNIQUE INDEX `index_OcsProjectResource_accountId_id` ON `OcsProjectResource` (`accountId`, `id`, `projectId`)"); + database.execSQL("CREATE INDEX `index_OcsProjectResource_id` ON `OcsProjectResource` (`id`)"); + database.execSQL("CREATE INDEX `index_OcsProjectResource_lastModifiedLocal` ON `OcsProjectResource` (`lastModifiedLocal`)"); + + database.execSQL("CREATE TABLE `JoinCardWithProject` (`status` INTEGER NOT NULL, `projectId` INTEGER NOT NULL, `cardId` INTEGER NOT NULL, PRIMARY KEY (`projectId`, `cardId`), FOREIGN KEY(`cardId`) REFERENCES `Card`(`localId`) ON UPDATE NO ACTION ON DELETE CASCADE, FOREIGN KEY(`projectId`) REFERENCES `OcsProject`(`localId`) ON UPDATE NO ACTION ON DELETE CASCADE)"); + database.execSQL("CREATE INDEX `index_JoinCardWithProject_projectId` ON `JoinCardWithProject` (`projectId`)"); + database.execSQL("CREATE INDEX `index_JoinCardWithProject_cardId` ON `JoinCardWithProject` (`cardId`)"); + } + }; + private static final Migration MIGRATION_17_18 = new Migration(17, 18) { + @Override + public void migrate(SupportSQLiteDatabase database) { + // https://github.com/stefan-niedermann/nextcloud-deck/issues/435 + database.execSQL("ALTER TABLE `Account` ADD `etag` TEXT"); + } + }; + private static final Migration MIGRATION_18_19 = new Migration(18, 19) { + @Override + public void migrate(SupportSQLiteDatabase database) { + // https://github.com/stefan-niedermann/nextcloud-deck/issues/619 + database.execSQL("DROP INDEX `index_OcsProjectResource_accountId_id`"); + database.execSQL("ALTER TABLE `OcsProjectResource` ADD `idString` TEXT"); + database.execSQL("CREATE UNIQUE INDEX `index_OcsProjectResource_accountId_id` ON `OcsProjectResource` (`accountId`, `id`, `idString`, `projectId`)"); + } + }; + private static final Migration MIGRATION_19_20 = new Migration(19, 20) { + @Override + public void migrate(SupportSQLiteDatabase database) { + // https://github.com/stefan-niedermann/nextcloud-deck/issues/492 + // https://github.com/stefan-niedermann/nextcloud-deck/issues/631 + database.execSQL("CREATE TABLE `UserInGroup` (`groupId` INTEGER NOT NULL, `memberId` INTEGER NOT NULL, " + + "primary KEY(`groupId`, `memberId`), " + + "FOREIGN KEY(`groupId`) REFERENCES `User`(`localId`) ON UPDATE NO ACTION ON DELETE CASCADE, " + + "FOREIGN KEY(`memberId`) REFERENCES `User`(`localId`) ON UPDATE NO ACTION ON DELETE CASCADE)"); + database.execSQL("CREATE UNIQUE INDEX `unique_idx_group_member` ON `UserInGroup` (`groupId`, `memberId`)"); + database.execSQL("CREATE INDEX `index_UserInGroup_groupId` ON `UserInGroup` (`groupId`)"); + database.execSQL("CREATE INDEX `index_UserInGroup_memberId` ON `UserInGroup` (`memberId`)"); + + database.execSQL("CREATE TABLE `UserInBoard` (`userId` INTEGER NOT NULL, `boardId` INTEGER NOT NULL, " + + "primary KEY(`userId`, `boardId`), " + + "FOREIGN KEY(`userId`) REFERENCES `User`(`localId`) ON UPDATE NO ACTION ON DELETE CASCADE, " + + "FOREIGN KEY(`boardId`) REFERENCES `Board`(`localId`) ON UPDATE NO ACTION ON DELETE CASCADE)"); + database.execSQL("CREATE UNIQUE INDEX `unique_idx_user_board` ON `UserInBoard` (`userId`, `boardId`)"); + database.execSQL("CREATE INDEX `index_UserInBoard_userId` ON `UserInBoard` (`userId`)"); + database.execSQL("CREATE INDEX `index_UserInBoard_boardId` ON `UserInBoard` (`boardId`)"); + } + }; + + private static final Migration MIGRATION_20_21 = new Migration(20, 21) { + @Override + public void migrate(SupportSQLiteDatabase database) { + // https://github.com/stefan-niedermann/nextcloud-deck/issues/556 + String suffix = "_new"; + { + String tableName = "Account"; + database.execSQL("CREATE TABLE `" + tableName + suffix + "` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` TEXT NOT NULL, `userName` TEXT NOT NULL, `url` TEXT NOT NULL, " + + "`color` INTEGER NOT NULL DEFAULT 0, `textColor` INTEGER NOT NULL DEFAULT 0, `serverDeckVersion` TEXT NOT NULL DEFAULT '0.6.4', `maintenanceEnabled` INTEGER NOT NULL DEFAULT 0, `etag` TEXT)"); + Cursor cursor = database.query("select * from `" + tableName + "`"); + while (cursor.moveToNext()) { + String colorAsString1 = cursor.getString(4); // color + String colorAsString2 = cursor.getString(5); // textColor + + @ColorInt Integer color1 = null; + @ColorInt Integer color2 = null; + try { + color1 = Color.parseColor(ColorUtil.INSTANCE.formatColorToParsableHexString(colorAsString1)); + color2 = Color.parseColor(ColorUtil.INSTANCE.formatColorToParsableHexString(colorAsString2)); + } catch (Exception e) { + color1 = Color.GRAY; + color2 = Color.GRAY; + } + database.execSQL("Insert into `" + tableName + suffix + "` VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", new Object[]{ + cursor.getLong(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), + color1, color2, cursor.getString(6), cursor.getInt(7), cursor.getString(8)}); + + } + + + database.execSQL("DROP TABLE `" + tableName + "`"); + database.execSQL("ALTER TABLE `" + tableName + suffix + "` RENAME TO `" + tableName + "`"); + database.execSQL("CREATE UNIQUE INDEX `index_Account_name` ON `" + tableName + "` (`name`)"); + database.execSQL("UPDATE SQLITE_SEQUENCE SET seq = (select max(id) from " + tableName + ") WHERE name = ?", new Object[]{tableName}); + } + { + String tableName = "Board"; + database.execSQL("CREATE TABLE `" + tableName + suffix + "` (`localId` INTEGER PRIMARY KEY AUTOINCREMENT, `accountId` INTEGER NOT NULL, `id` INTEGER, `status` INTEGER NOT NULL, " + + "`lastModified` INTEGER, `lastModifiedLocal` INTEGER, `title` TEXT, `ownerId` INTEGER NOT NULL, `color` INTEGER, " + + "`archived` INTEGER NOT NULL, `shared` INTEGER NOT NULL, `deletedAt` INTEGER, `permissionRead` INTEGER NOT NULL, " + + "`permissionEdit` INTEGER NOT NULL, `permissionManage` INTEGER NOT NULL, `permissionShare` INTEGER NOT NULL, " + + "FOREIGN KEY(`ownerId`) REFERENCES `User`(`localId`) ON UPDATE NO ACTION ON DELETE SET NULL )"); + Cursor cursor = database.query("select * from `" + tableName + "`"); + while (cursor.moveToNext()) { + String colorAsString1 = cursor.getString(8); // color + + @ColorInt Integer color1 = null; + try { + color1 = Color.parseColor(ColorUtil.INSTANCE.formatColorToParsableHexString(colorAsString1)); + } catch (Exception e) { + color1 = Color.GRAY; + } + database.execSQL("Insert into `" + tableName + suffix + "` VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", new Object[]{ + cursor.getLong(0), cursor.getLong(1), cursor.getLong(2), cursor.getInt(3), + cursor.getLong(4), cursor.getLong(5), cursor.getString(6), cursor.getLong(7), color1, + cursor.getInt(9), cursor.getInt(10), cursor.getInt(11), cursor.getInt(12), + cursor.getInt(13), cursor.getInt(14), cursor.getInt(15) + }); + + } + + + database.execSQL("DROP TABLE `" + tableName + "`"); + database.execSQL("ALTER TABLE `" + tableName + suffix + "` RENAME TO `" + tableName + "`"); + database.execSQL("CREATE INDEX `index_Board_accountId` ON `" + tableName + "` (`accountId`)"); + database.execSQL("CREATE UNIQUE INDEX `index_Board_accountId_id` ON `" + tableName + "` (`accountId`, `id`)"); + database.execSQL("CREATE INDEX `index_Board_id` ON `" + tableName + "` (`id`)"); + database.execSQL("CREATE INDEX `index_Board_ownerId` ON `" + tableName + "` (`ownerId`)"); + database.execSQL("CREATE INDEX `index_Board_lastModifiedLocal` ON `" + tableName + "` (`lastModifiedLocal`)"); + database.execSQL("UPDATE SQLITE_SEQUENCE SET seq = (select max(id) from " + tableName + ") WHERE name = ?", new Object[]{tableName}); + } + { + String tableName = "Label"; + database.execSQL("CREATE TABLE `" + tableName + suffix + "` (`localId` INTEGER PRIMARY KEY AUTOINCREMENT, `accountId` INTEGER NOT NULL, `id` INTEGER, `status` INTEGER NOT NULL, " + + "`lastModified` INTEGER, `lastModifiedLocal` INTEGER, `title` TEXT, `color` INTEGER NOT NULL DEFAULT 0, `boardId` INTEGER NOT NULL, " + + "FOREIGN KEY(`boardId`) REFERENCES `Board`(`localId`) ON UPDATE NO ACTION ON DELETE CASCADE )"); + Cursor cursor = database.query("select * from `" + tableName + "`"); + while (cursor.moveToNext()) { + String colorAsString1 = cursor.getString(7); // color + + @ColorInt Integer color1 = null; + try { + color1 = Color.parseColor(ColorUtil.INSTANCE.formatColorToParsableHexString(colorAsString1)); + } catch (Exception e) { + color1 = Color.GRAY; + } + database.execSQL("Insert into `" + tableName + suffix + "` VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", new Object[]{ + cursor.getLong(0), cursor.getLong(1), cursor.getLong(2), cursor.getInt(3), + cursor.getLong(4), cursor.getLong(5), cursor.getString(6), color1, cursor.getLong(8)}); + + } + + + database.execSQL("DROP TABLE `" + tableName + "`"); + database.execSQL("ALTER TABLE `" + tableName + suffix + "` RENAME TO `" + tableName + "`"); + database.execSQL("CREATE UNIQUE INDEX `index_Label_accountId_id` ON `" + tableName + "` (`accountId`, `id`)"); + database.execSQL("CREATE INDEX `index_Label_boardId` ON `" + tableName + "` (`boardId`)"); + database.execSQL("CREATE INDEX `index_Label_accountId` ON `" + tableName + "` (`accountId`)"); + database.execSQL("CREATE UNIQUE INDEX `idx_label_title_unique` ON `" + tableName + "` (`boardId`, `title`)"); + database.execSQL("CREATE INDEX `index_Label_id` ON `" + tableName + "` (`id`)"); + database.execSQL("CREATE INDEX `index_Label_lastModifiedLocal` ON `" + tableName + "` (`lastModifiedLocal`)"); + database.execSQL("UPDATE SQLITE_SEQUENCE SET seq = (select max(id) from " + tableName + ") WHERE name = ?", new Object[]{tableName}); + } + } + }; + + private static final Migration MIGRATION_22_23 = new Migration(22, 23) { + @Override + public void migrate(SupportSQLiteDatabase database) { + // https://github.com/stefan-niedermann/nextcloud-deck/issues/359 + database.execSQL("ALTER TABLE `Account` ADD `boardsEtag` TEXT"); + database.execSQL("ALTER TABLE `Board` ADD `etag` TEXT"); + database.execSQL("ALTER TABLE `Stack` ADD `etag` TEXT"); + database.execSQL("ALTER TABLE `Card` ADD `etag` TEXT"); + database.execSQL("ALTER TABLE `Label` ADD `etag` TEXT"); + database.execSQL("ALTER TABLE `AccessControl` ADD `etag` TEXT"); + database.execSQL("ALTER TABLE `Attachment` ADD `etag` TEXT"); + database.execSQL("ALTER TABLE `User` ADD `etag` TEXT"); + database.execSQL("ALTER TABLE `DeckComment` ADD `etag` TEXT"); + database.execSQL("ALTER TABLE `Activity` ADD `etag` TEXT"); + database.execSQL("ALTER TABLE `OcsProject` ADD `etag` TEXT"); + database.execSQL("ALTER TABLE `OcsProjectResource` ADD `etag` TEXT"); + } + }; + + public static final RoomDatabase.Callback ON_CREATE_CALLBACK = new RoomDatabase.Callback() { @Override public void onCreate(@NonNull SupportSQLiteDatabase db) { super.onCreate(db); @@ -216,6 +434,26 @@ public abstract class DeckDatabase extends RoomDatabase { .apply(); } }) + .addMigrations(MIGRATION_15_16) + .addMigrations(MIGRATION_16_17) + .addMigrations(MIGRATION_17_18) + .addMigrations(MIGRATION_18_19) + .addMigrations(MIGRATION_19_20) + .addMigrations(MIGRATION_20_21) + .addMigrations(new Migration(21, 22) { + @Override + public void migrate(@NonNull SupportSQLiteDatabase database) { + // https://github.com/stefan-niedermann/nextcloud-deck/issues/715 + final SharedPreferences.Editor lastSyncPref = context.getApplicationContext().getSharedPreferences("it.niedermann.nextcloud.deck.last_sync", Context.MODE_PRIVATE).edit(); + Cursor cursor = database.query("select id from `Account`"); + while (cursor.moveToNext()) { + lastSyncPref.remove("lS_" + cursor.getLong(0)); + } + cursor.close(); + lastSyncPref.apply(); + } + }) + .addMigrations(MIGRATION_22_23) .fallbackToDestructiveMigration() .addCallback(ON_CREATE_CALLBACK) .build(); @@ -256,4 +494,16 @@ public abstract class DeckDatabase extends RoomDatabase { public abstract MentionDao getMentionDao(); public abstract SingleCardWidgetModelDao getSingleCardWidgetModelDao(); + + public abstract StackWidgetModelDao getStackWidgetModelDao(); + + public abstract OcsProjectDao getOcsProjectDao(); + + public abstract OcsProjectResourceDao getOcsProjectResourceDao(); + + public abstract JoinCardWithOcsProjectDao getJoinCardWithOcsProjectDao(); + + public abstract UserInGroupDao getUserInGroupDao(); + + public abstract UserInBoardDao getUserInBoardDao(); }
\ No newline at end of file |