diff options
author | Stefan Niedermann <info@niedermann.it> | 2020-06-08 13:00:34 +0300 |
---|---|---|
committer | Niedermann IT-Dienstleistungen <stefan-niedermann@users.noreply.github.com> | 2020-06-08 14:55:59 +0300 |
commit | 0c676a65739e35a4ed982cce355245add60fe73f (patch) | |
tree | a022396714c206f56c7a5a3ebba35d37700a5757 /app/src/main | |
parent | dc83340d5572da52384d78cdd4bf8d4be8dbd34c (diff) |
Add some documentation for database migrations
Diffstat (limited to 'app/src/main')
15 files changed, 410 insertions, 374 deletions
diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/AbstractNotesDatabase.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/AbstractNotesDatabase.java index e2061e21..057fb598 100644 --- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/AbstractNotesDatabase.java +++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/AbstractNotesDatabase.java @@ -13,17 +13,16 @@ import it.niedermann.owncloud.notes.persistence.migration.Migration_12_13; import it.niedermann.owncloud.notes.persistence.migration.Migration_13_14; import it.niedermann.owncloud.notes.persistence.migration.Migration_14_15; import it.niedermann.owncloud.notes.persistence.migration.Migration_15_16; +import it.niedermann.owncloud.notes.persistence.migration.Migration_16_17; import it.niedermann.owncloud.notes.persistence.migration.Migration_4_5; import it.niedermann.owncloud.notes.persistence.migration.Migration_5_6; import it.niedermann.owncloud.notes.persistence.migration.Migration_6_7; +import it.niedermann.owncloud.notes.persistence.migration.Migration_7_8; import it.niedermann.owncloud.notes.persistence.migration.Migration_8_9; import it.niedermann.owncloud.notes.persistence.migration.Migration_9_10; import it.niedermann.owncloud.notes.util.DatabaseIndexUtil; -// Protected APIs -@SuppressWarnings("WeakerAccess") abstract class AbstractNotesDatabase extends SQLiteOpenHelper { - private static final String TAG = AbstractNotesDatabase.class.getSimpleName(); private static final int database_version = 17; @NonNull @@ -37,7 +36,6 @@ abstract class AbstractNotesDatabase extends SQLiteOpenHelper { protected static final String table_widget_note_list = "WIDGET_NOTE_LISTS"; protected static final String key_id = "ID"; - protected static final String key_url = "URL"; protected static final String key_account_name = "ACCOUNT_NAME"; protected static final String key_username = "USERNAME"; @@ -163,32 +161,31 @@ abstract class AbstractNotesDatabase extends SQLiteOpenHelper { recreateDatabase(db); return; case 4: - new Migration_4_5(db, oldVersion); + new Migration_4_5(db); case 5: - new Migration_5_6(db, oldVersion); + new Migration_5_6(db); case 6: - new Migration_6_7(db, oldVersion); + new Migration_6_7(db); case 7: - new Migration_7_8(db, oldVersion); + new Migration_7_8(db); case 8: - new Migration_8_9(db, oldVersion, context, this::recreateDatabase, this::notifyWidgets); + new Migration_8_9(db, context, this::recreateDatabase, this::notifyWidgets); case 9: - new Migration_9_10(db, oldVersion); + new Migration_9_10(db); case 10: - new Migration_10_11(oldVersion, context); + new Migration_10_11(context); case 11: - new Migration_11_12(db, oldVersion, context); + new Migration_11_12(db, context); case 12: - new Migration_12_13(db, oldVersion, context); + new Migration_12_13(db, context); case 13: - new Migration_13_14(db, oldVersion, context, this::notifyWidgets); + new Migration_13_14(db, context, this::notifyWidgets); case 14: - new Migration_14_15(db, oldVersion); + new Migration_14_15(db); case 15: - new Migration_15_16(db, oldVersion, context, this::notifyWidgets); - } - if(oldVersion < 17) { - db.execSQL("ALTER TABLE NOTES ADD COLUMN SCROLL_Y INTEGER DEFAULT 0"); + new Migration_15_16(db, context, this::notifyWidgets); + case 16: + new Migration_16_17(db); } } @@ -205,6 +202,5 @@ abstract class AbstractNotesDatabase extends SQLiteOpenHelper { onCreate(db); } - protected abstract void notifyWidgets(); } diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/Migration_7_8.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/Migration_7_8.java deleted file mode 100644 index 44748b98..00000000 --- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/Migration_7_8.java +++ /dev/null @@ -1,28 +0,0 @@ -package it.niedermann.owncloud.notes.persistence; - -import android.database.sqlite.SQLiteDatabase; - -import it.niedermann.owncloud.notes.util.DatabaseIndexUtil; - -public class Migration_7_8 { - public Migration_7_8(SQLiteDatabase db, int oldVersion) { - if (oldVersion < 8) { - final String table_temp = "NOTES_TEMP"; - db.execSQL("CREATE TABLE " + table_temp + " ( " + - "ID INTEGER PRIMARY KEY AUTOINCREMENT, " + - "REMOTEID INTEGER, " + - "STATUS VARCHAR(50), " + - "TITLE TEXT, " + - "MODIFIED INTEGER DEFAULT 0, " + - "CONTENT TEXT, " + - "FAVORITE INTEGER DEFAULT 0, " + - "CATEGORY TEXT NOT NULL DEFAULT '', " + - "ETAG TEXT)"); - DatabaseIndexUtil.createIndex(db, table_temp, "REMOTEID", "STATUS", "FAVORITE", "CATEGORY", "MODIFIED"); - db.execSQL(String.format("INSERT INTO %s(%s,%s,%s,%s,%s,%s,%s,%s,%s) ", table_temp, "ID", "REMOTEID", "STATUS", "TITLE", "MODIFIED", "CONTENT", "FAVORITE", "CATEGORY", "ETAG") - + String.format("SELECT %s,%s,%s,%s,strftime('%%s',%s),%s,%s,%s,%s FROM %s", "ID", "REMOTEID", "STATUS", "TITLE", "MODIFIED", "CONTENT", "FAVORITE", "CATEGORY", "ETAG", "NOTES")); - db.execSQL("DROP TABLE NOTES"); - db.execSQL(String.format("ALTER TABLE %s RENAME TO %s", table_temp, "NOTES")); - } - } -} diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_10_11.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_10_11.java index d09a44e8..82a627aa 100644 --- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_10_11.java +++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_10_11.java @@ -11,20 +11,21 @@ import java.util.Map; import it.niedermann.owncloud.notes.android.DarkModeSetting; public class Migration_10_11 { - public Migration_10_11(int oldVersion, @NonNull Context context) { - if (oldVersion < 11) { - SharedPreferences sharedPreferences = PreferenceManager.getDefaultSharedPreferences(context); - SharedPreferences.Editor editor = sharedPreferences.edit(); - Map<String, ?> prefs = sharedPreferences.getAll(); - for (Map.Entry<String, ?> pref : prefs.entrySet()) { - String key = pref.getKey(); - final String DARK_THEME_KEY = "NLW_darkTheme"; - if ("darkTheme".equals(key) || key.startsWith(DARK_THEME_KEY) || key.startsWith("SNW_darkTheme")) { - Boolean darkTheme = (Boolean) pref.getValue(); - editor.putString(pref.getKey(), darkTheme ? DarkModeSetting.DARK.name() : DarkModeSetting.LIGHT.name()); - } + /** + * Changes the boolean for light / dark mode to {@link DarkModeSetting} to also be able to represent system default value + */ + public Migration_10_11(@NonNull Context context) { + SharedPreferences sharedPreferences = PreferenceManager.getDefaultSharedPreferences(context); + SharedPreferences.Editor editor = sharedPreferences.edit(); + Map<String, ?> prefs = sharedPreferences.getAll(); + for (Map.Entry<String, ?> pref : prefs.entrySet()) { + String key = pref.getKey(); + final String DARK_THEME_KEY = "NLW_darkTheme"; + if ("darkTheme".equals(key) || key.startsWith(DARK_THEME_KEY) || key.startsWith("SNW_darkTheme")) { + Boolean darkTheme = (Boolean) pref.getValue(); + editor.putString(pref.getKey(), darkTheme ? DarkModeSetting.DARK.name() : DarkModeSetting.LIGHT.name()); } - editor.apply(); } + editor.apply(); } } diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_11_12.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_11_12.java index 28d329d5..82a9984e 100644 --- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_11_12.java +++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_11_12.java @@ -5,15 +5,17 @@ import android.database.sqlite.SQLiteDatabase; import androidx.annotation.NonNull; +import it.niedermann.owncloud.notes.model.ApiVersion; import it.niedermann.owncloud.notes.persistence.CapabilitiesWorker; public class Migration_11_12 { - public Migration_11_12(SQLiteDatabase db, int oldVersion, @NonNull Context context) { - if (oldVersion < 12) { - db.execSQL("ALTER TABLE ACCOUNTS ADD COLUMN API_VERSION TEXT"); - db.execSQL("ALTER TABLE ACCOUNTS ADD COLUMN COLOR VARCHAR(6) NOT NULL DEFAULT '000000'"); - db.execSQL("ALTER TABLE ACCOUNTS ADD COLUMN TEXT_COLOR VARCHAR(6) NOT NULL DEFAULT '0082C9'"); - CapabilitiesWorker.update(context); - } + /** + * Adds columns to store the {@link ApiVersion} and the theme colors + */ + public Migration_11_12(@NonNull SQLiteDatabase db, @NonNull Context context) { + db.execSQL("ALTER TABLE ACCOUNTS ADD COLUMN API_VERSION TEXT"); + db.execSQL("ALTER TABLE ACCOUNTS ADD COLUMN COLOR VARCHAR(6) NOT NULL DEFAULT '000000'"); + db.execSQL("ALTER TABLE ACCOUNTS ADD COLUMN TEXT_COLOR VARCHAR(6) NOT NULL DEFAULT '0082C9'"); + CapabilitiesWorker.update(context); } } diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_12_13.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_12_13.java index e51280be..895a59a1 100644 --- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_12_13.java +++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_12_13.java @@ -6,12 +6,15 @@ import android.database.sqlite.SQLiteDatabase; import androidx.annotation.NonNull; import androidx.work.WorkManager; +import it.niedermann.owncloud.notes.model.Capabilities; + public class Migration_12_13 { - public Migration_12_13(SQLiteDatabase db, int oldVersion, @NonNull Context context) { - if (oldVersion < 13) { - db.execSQL("ALTER TABLE ACCOUNTS ADD COLUMN CAPABILITIES_ETAG TEXT"); - WorkManager.getInstance(context.getApplicationContext()).cancelUniqueWork("it.niedermann.owncloud.notes.persistence.SyncWorker"); - WorkManager.getInstance(context.getApplicationContext()).cancelUniqueWork("SyncWorker"); - } + /** + * Adds a column to store the ETag of the server {@link Capabilities} + */ + public Migration_12_13(@NonNull SQLiteDatabase db, @NonNull Context context) { + db.execSQL("ALTER TABLE ACCOUNTS ADD COLUMN CAPABILITIES_ETAG TEXT"); + WorkManager.getInstance(context.getApplicationContext()).cancelUniqueWork("it.niedermann.owncloud.notes.persistence.SyncWorker"); + WorkManager.getInstance(context.getApplicationContext()).cancelUniqueWork("SyncWorker"); } } diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_13_14.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_13_14.java index 9a5d5fdf..d69396d3 100644 --- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_13_14.java +++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_13_14.java @@ -17,61 +17,62 @@ public class Migration_13_14 { private static final String TAG = Migration_13_14.class.getSimpleName(); - public Migration_13_14(SQLiteDatabase db, int oldVersion, @NonNull Context context, @NonNull Runnable notifyWidgets) { - if (oldVersion < 14) { - // #754 Move single note widget preferences to database - db.execSQL("CREATE TABLE WIDGET_SINGLE_NOTES ( " + - "ID INTEGER PRIMARY KEY, " + - "ACCOUNT_ID INTEGER, " + - "NOTE_ID INTEGER, " + - "THEME_MODE INTEGER NOT NULL, " + - "FOREIGN KEY(ACCOUNT_ID) REFERENCES ACCOUNTS(ID), " + - "FOREIGN KEY(NOTE_ID) REFERENCES NOTES(ID))"); + /** + * Move single note widget preferences to database + * https://github.com/stefan-niedermann/nextcloud-notes/issues/754 + */ + public Migration_13_14(@NonNull SQLiteDatabase db, @NonNull Context context, @NonNull Runnable notifyWidgets) { + db.execSQL("CREATE TABLE WIDGET_SINGLE_NOTES ( " + + "ID INTEGER PRIMARY KEY, " + + "ACCOUNT_ID INTEGER, " + + "NOTE_ID INTEGER, " + + "THEME_MODE INTEGER NOT NULL, " + + "FOREIGN KEY(ACCOUNT_ID) REFERENCES ACCOUNTS(ID), " + + "FOREIGN KEY(NOTE_ID) REFERENCES NOTES(ID))"); - final String SP_WIDGET_KEY = "single_note_widget"; - final String SP_ACCOUNT_ID_KEY = "SNW_accountId"; - final String SP_DARK_THEME_KEY = "SNW_darkTheme"; - SharedPreferences sharedPreferences = PreferenceManager.getDefaultSharedPreferences(context); - SharedPreferences.Editor editor = sharedPreferences.edit(); - Map<String, ?> prefs = sharedPreferences.getAll(); - for (Map.Entry<String, ?> pref : prefs.entrySet()) { - final String key = pref.getKey(); - Integer widgetId = null; - Long noteId = null; - Long accountId = null; - Integer themeMode = null; - if (key != null && key.startsWith(SP_WIDGET_KEY)) { - try { - widgetId = Integer.parseInt(key.substring(SP_WIDGET_KEY.length())); - noteId = (Long) pref.getValue(); - accountId = sharedPreferences.getLong(SP_ACCOUNT_ID_KEY + widgetId, -1); - - try { - themeMode = DarkModeSetting.valueOf(sharedPreferences.getString(SP_DARK_THEME_KEY + widgetId, DarkModeSetting.SYSTEM_DEFAULT.name())).getModeId(); - } catch (ClassCastException e) { - //DARK_THEME was a boolean in older versions of the app. We thereofre have to still support the old setting. - themeMode = sharedPreferences.getBoolean(SP_DARK_THEME_KEY + widgetId, false) ? DarkModeSetting.DARK.getModeId() : DarkModeSetting.LIGHT.getModeId(); - } + final String SP_WIDGET_KEY = "single_note_widget"; + final String SP_ACCOUNT_ID_KEY = "SNW_accountId"; + final String SP_DARK_THEME_KEY = "SNW_darkTheme"; + SharedPreferences sharedPreferences = PreferenceManager.getDefaultSharedPreferences(context); + SharedPreferences.Editor editor = sharedPreferences.edit(); + Map<String, ?> prefs = sharedPreferences.getAll(); + for (Map.Entry<String, ?> pref : prefs.entrySet()) { + final String key = pref.getKey(); + Integer widgetId = null; + Long noteId = null; + Long accountId = null; + Integer themeMode = null; + if (key != null && key.startsWith(SP_WIDGET_KEY)) { + try { + widgetId = Integer.parseInt(key.substring(SP_WIDGET_KEY.length())); + noteId = (Long) pref.getValue(); + accountId = sharedPreferences.getLong(SP_ACCOUNT_ID_KEY + widgetId, -1); - ContentValues migratedWidgetValues = new ContentValues(); - migratedWidgetValues.put("ID", widgetId); - migratedWidgetValues.put("ACCOUNT_ID", accountId); - migratedWidgetValues.put("NOTE_ID", noteId); - migratedWidgetValues.put("THEME_MODE", themeMode); - db.insert("WIDGET_SINGLE_NOTES", null, migratedWidgetValues); - } catch (Throwable t) { - Log.e(TAG, "Could not migrate widget {widgetId: " + widgetId + ", accountId: " + accountId + ", noteId: " + noteId + ", themeMode: " + themeMode + "}"); - t.printStackTrace(); - } finally { - // Clean up old shared preferences - editor.remove(SP_WIDGET_KEY + widgetId); - editor.remove(SP_DARK_THEME_KEY + widgetId); - editor.remove(SP_ACCOUNT_ID_KEY + widgetId); + try { + themeMode = DarkModeSetting.valueOf(sharedPreferences.getString(SP_DARK_THEME_KEY + widgetId, DarkModeSetting.SYSTEM_DEFAULT.name())).getModeId(); + } catch (ClassCastException e) { + //DARK_THEME was a boolean in older versions of the app. We thereofre have to still support the old setting. + themeMode = sharedPreferences.getBoolean(SP_DARK_THEME_KEY + widgetId, false) ? DarkModeSetting.DARK.getModeId() : DarkModeSetting.LIGHT.getModeId(); } + + ContentValues migratedWidgetValues = new ContentValues(); + migratedWidgetValues.put("ID", widgetId); + migratedWidgetValues.put("ACCOUNT_ID", accountId); + migratedWidgetValues.put("NOTE_ID", noteId); + migratedWidgetValues.put("THEME_MODE", themeMode); + db.insert("WIDGET_SINGLE_NOTES", null, migratedWidgetValues); + } catch (Throwable t) { + Log.e(TAG, "Could not migrate widget {widgetId: " + widgetId + ", accountId: " + accountId + ", noteId: " + noteId + ", themeMode: " + themeMode + "}"); + t.printStackTrace(); + } finally { + // Clean up old shared preferences + editor.remove(SP_WIDGET_KEY + widgetId); + editor.remove(SP_DARK_THEME_KEY + widgetId); + editor.remove(SP_ACCOUNT_ID_KEY + widgetId); } } - editor.apply(); - notifyWidgets.run(); } + editor.apply(); + notifyWidgets.run(); } } diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_14_15.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_14_15.java index 7abaf133..b025bbaa 100644 --- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_14_15.java +++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_14_15.java @@ -10,73 +10,74 @@ import java.util.Hashtable; import it.niedermann.owncloud.notes.util.DatabaseIndexUtil; public class Migration_14_15 { - public Migration_14_15(SQLiteDatabase db, int oldVersion) { - if (oldVersion < 15) { - // #814 normalize database (move category from string field to own table) - // Rename a tmp_NOTES table. - String tmpTableNotes = String.format("tmp_%s", "NOTES"); - db.execSQL("ALTER TABLE NOTES RENAME TO " + tmpTableNotes); - db.execSQL("CREATE TABLE NOTES ( " + - "ID INTEGER PRIMARY KEY AUTOINCREMENT, " + - "REMOTEID INTEGER, " + - "ACCOUNT_ID INTEGER, " + - "STATUS VARCHAR(50), " + - "TITLE TEXT, " + - "MODIFIED INTEGER DEFAULT 0, " + - "CONTENT TEXT, " + - "FAVORITE INTEGER DEFAULT 0, " + - "CATEGORY INTEGER, " + - "ETAG TEXT," + - "EXCERPT TEXT NOT NULL DEFAULT '', " + - "FOREIGN KEY(CATEGORY) REFERENCES CATEGORIES(CATEGORY_ID), " + - "FOREIGN KEY(ACCOUNT_ID) REFERENCES ACCOUNTS(ID))"); - DatabaseIndexUtil.createIndex(db, "NOTES", "REMOTEID", "ACCOUNT_ID", "STATUS", "FAVORITE", "CATEGORY", "MODIFIED"); - db.execSQL("CREATE TABLE CATEGORIES(" + - "CATEGORY_ID INTEGER PRIMARY KEY AUTOINCREMENT, " + - "CATEGORY_ACCOUNT_ID INTEGER NOT NULL, " + - "CATEGORY_TITLE TEXT NOT NULL, " + - "UNIQUE( CATEGORY_ACCOUNT_ID , CATEGORY_TITLE), " + - "FOREIGN KEY(CATEGORY_ACCOUNT_ID) REFERENCES ACCOUNTS(ID))"); - DatabaseIndexUtil.createIndex(db, "CATEGORIES", "CATEGORY_ID", "CATEGORY_ACCOUNT_ID", "CATEGORY_TITLE"); - // A hashtable storing categoryTitle - categoryId Mapping - // This is used to prevent too many searches in database - Hashtable<String, Integer> categoryTitleIdMap = new Hashtable<>(); - int id = 1; - Cursor tmpNotesCursor = db.rawQuery("SELECT * FROM " + tmpTableNotes, null); - while (tmpNotesCursor.moveToNext()) { - String categoryTitle = tmpNotesCursor.getString(8); - int accountId = tmpNotesCursor.getInt(2); - Log.e("###", accountId + ""); - Integer categoryId; - if (categoryTitleIdMap.containsKey(categoryTitle) && categoryTitleIdMap.get(categoryTitle) != null) { - categoryId = categoryTitleIdMap.get(categoryTitle); - } else { - // The category does not exists in the database, create it. - categoryId = id++; - ContentValues values = new ContentValues(); - values.put("CATEGORY_ID", categoryId); - values.put("CATEGORY_ACCOUNT_ID", accountId); - values.put("CATEGORY_TITLE", categoryTitle); - db.insert("CATEGORIES", null, values); - categoryTitleIdMap.put(categoryTitle, categoryId); - } - // Move the data in tmp_NOTES to NOTES + /** + * Normalize database (move category from string field to own table) + * https://github.com/stefan-niedermann/nextcloud-notes/issues/814 + */ + public Migration_14_15(SQLiteDatabase db) { + // Rename a tmp_NOTES table. + String tmpTableNotes = String.format("tmp_%s", "NOTES"); + db.execSQL("ALTER TABLE NOTES RENAME TO " + tmpTableNotes); + db.execSQL("CREATE TABLE NOTES ( " + + "ID INTEGER PRIMARY KEY AUTOINCREMENT, " + + "REMOTEID INTEGER, " + + "ACCOUNT_ID INTEGER, " + + "STATUS VARCHAR(50), " + + "TITLE TEXT, " + + "MODIFIED INTEGER DEFAULT 0, " + + "CONTENT TEXT, " + + "FAVORITE INTEGER DEFAULT 0, " + + "CATEGORY INTEGER, " + + "ETAG TEXT," + + "EXCERPT TEXT NOT NULL DEFAULT '', " + + "FOREIGN KEY(CATEGORY) REFERENCES CATEGORIES(CATEGORY_ID), " + + "FOREIGN KEY(ACCOUNT_ID) REFERENCES ACCOUNTS(ID))"); + DatabaseIndexUtil.createIndex(db, "NOTES", "REMOTEID", "ACCOUNT_ID", "STATUS", "FAVORITE", "CATEGORY", "MODIFIED"); + db.execSQL("CREATE TABLE CATEGORIES(" + + "CATEGORY_ID INTEGER PRIMARY KEY AUTOINCREMENT, " + + "CATEGORY_ACCOUNT_ID INTEGER NOT NULL, " + + "CATEGORY_TITLE TEXT NOT NULL, " + + "UNIQUE( CATEGORY_ACCOUNT_ID , CATEGORY_TITLE), " + + "FOREIGN KEY(CATEGORY_ACCOUNT_ID) REFERENCES ACCOUNTS(ID))"); + DatabaseIndexUtil.createIndex(db, "CATEGORIES", "CATEGORY_ID", "CATEGORY_ACCOUNT_ID", "CATEGORY_TITLE"); + // A hashtable storing categoryTitle - categoryId Mapping + // This is used to prevent too many searches in database + Hashtable<String, Integer> categoryTitleIdMap = new Hashtable<>(); + int id = 1; + Cursor tmpNotesCursor = db.rawQuery("SELECT * FROM " + tmpTableNotes, null); + while (tmpNotesCursor.moveToNext()) { + String categoryTitle = tmpNotesCursor.getString(8); + int accountId = tmpNotesCursor.getInt(2); + Log.e("###", accountId + ""); + Integer categoryId; + if (categoryTitleIdMap.containsKey(categoryTitle) && categoryTitleIdMap.get(categoryTitle) != null) { + categoryId = categoryTitleIdMap.get(categoryTitle); + } else { + // The category does not exists in the database, create it. + categoryId = id++; ContentValues values = new ContentValues(); - values.put("ID", tmpNotesCursor.getInt(0)); - values.put("REMOTEID", tmpNotesCursor.getInt(1)); - values.put("ACCOUNT_ID", tmpNotesCursor.getInt(2)); - values.put("STATUS", tmpNotesCursor.getString(3)); - values.put("TITLE", tmpNotesCursor.getString(4)); - values.put("MODIFIED", tmpNotesCursor.getLong(5)); - values.put("CONTENT", tmpNotesCursor.getString(6)); - values.put("FAVORITE", tmpNotesCursor.getInt(7)); - values.put("CATEGORY", categoryId); - values.put("ETAG", tmpNotesCursor.getString(9)); - values.put("EXCERPT", tmpNotesCursor.getString(10)); - db.insert("NOTES", null, values); + values.put("CATEGORY_ID", categoryId); + values.put("CATEGORY_ACCOUNT_ID", accountId); + values.put("CATEGORY_TITLE", categoryTitle); + db.insert("CATEGORIES", null, values); + categoryTitleIdMap.put(categoryTitle, categoryId); } - tmpNotesCursor.close(); - db.execSQL("DROP TABLE IF EXISTS " + tmpTableNotes); + // Move the data in tmp_NOTES to NOTES + ContentValues values = new ContentValues(); + values.put("ID", tmpNotesCursor.getInt(0)); + values.put("REMOTEID", tmpNotesCursor.getInt(1)); + values.put("ACCOUNT_ID", tmpNotesCursor.getInt(2)); + values.put("STATUS", tmpNotesCursor.getString(3)); + values.put("TITLE", tmpNotesCursor.getString(4)); + values.put("MODIFIED", tmpNotesCursor.getLong(5)); + values.put("CONTENT", tmpNotesCursor.getString(6)); + values.put("FAVORITE", tmpNotesCursor.getInt(7)); + values.put("CATEGORY", categoryId); + values.put("ETAG", tmpNotesCursor.getString(9)); + values.put("EXCERPT", tmpNotesCursor.getString(10)); + db.insert("NOTES", null, values); } + tmpNotesCursor.close(); + db.execSQL("DROP TABLE IF EXISTS " + tmpTableNotes); } } diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_15_16.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_15_16.java index 27a04456..396f29e1 100644 --- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_15_16.java +++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_15_16.java @@ -18,85 +18,86 @@ public class Migration_15_16 { private static final String TAG = Migration_15_16.class.getSimpleName(); - public Migration_15_16(SQLiteDatabase db, int oldVersion, @NonNull Context context, @NonNull Runnable notifyWidgets) { - if (oldVersion < 16) { - // #832 Move note list widget preferences to database - db.execSQL("CREATE TABLE WIDGET_NOTE_LISTS ( " + - "ID INTEGER PRIMARY KEY, " + - "ACCOUNT_ID INTEGER, " + - "CATEGORY_ID INTEGER, " + - "MODE INTEGER NOT NULL, " + - "THEME_MODE INTEGER NOT NULL, " + - "FOREIGN KEY(ACCOUNT_ID) REFERENCES ACCOUNTS(ID), " + - "FOREIGN KEY(CATEGORY_ID) REFERENCES CATEGORIES(CATEGORY_ID))"); + /** + * Moves note list widget preferences from {@link SharedPreferences} to database + * https://github.com/stefan-niedermann/nextcloud-notes/issues/832 + */ + public Migration_15_16(SQLiteDatabase db, @NonNull Context context, @NonNull Runnable notifyWidgets) { + db.execSQL("CREATE TABLE WIDGET_NOTE_LISTS ( " + + "ID INTEGER PRIMARY KEY, " + + "ACCOUNT_ID INTEGER, " + + "CATEGORY_ID INTEGER, " + + "MODE INTEGER NOT NULL, " + + "THEME_MODE INTEGER NOT NULL, " + + "FOREIGN KEY(ACCOUNT_ID) REFERENCES ACCOUNTS(ID), " + + "FOREIGN KEY(CATEGORY_ID) REFERENCES CATEGORIES(CATEGORY_ID))"); - final String SP_WIDGET_KEY = "NLW_mode"; - final String SP_ACCOUNT_ID_KEY = "NLW_account"; - final String SP_DARK_THEME_KEY = "NLW_darkTheme"; - final String SP_CATEGORY_KEY = "NLW_cat"; + final String SP_WIDGET_KEY = "NLW_mode"; + final String SP_ACCOUNT_ID_KEY = "NLW_account"; + final String SP_DARK_THEME_KEY = "NLW_darkTheme"; + final String SP_CATEGORY_KEY = "NLW_cat"; - SharedPreferences sharedPreferences = PreferenceManager.getDefaultSharedPreferences(context); - SharedPreferences.Editor editor = sharedPreferences.edit(); - Map<String, ?> prefs = sharedPreferences.getAll(); - for (Map.Entry<String, ?> pref : prefs.entrySet()) { - final String key = pref.getKey(); - Integer widgetId = null; - Integer mode = null; - Long accountId = null; - Integer themeMode = null; - Integer categoryId = null; - if (key != null && key.startsWith(SP_WIDGET_KEY)) { - try { - widgetId = Integer.parseInt(key.substring(SP_WIDGET_KEY.length())); - mode = (Integer) pref.getValue(); - accountId = sharedPreferences.getLong(SP_ACCOUNT_ID_KEY + widgetId, -1); + SharedPreferences sharedPreferences = PreferenceManager.getDefaultSharedPreferences(context); + SharedPreferences.Editor editor = sharedPreferences.edit(); + Map<String, ?> prefs = sharedPreferences.getAll(); + for (Map.Entry<String, ?> pref : prefs.entrySet()) { + final String key = pref.getKey(); + Integer widgetId = null; + Integer mode = null; + Long accountId = null; + Integer themeMode = null; + Integer categoryId = null; + if (key != null && key.startsWith(SP_WIDGET_KEY)) { + try { + widgetId = Integer.parseInt(key.substring(SP_WIDGET_KEY.length())); + mode = (Integer) pref.getValue(); + accountId = sharedPreferences.getLong(SP_ACCOUNT_ID_KEY + widgetId, -1); - try { - themeMode = DarkModeSetting.valueOf(sharedPreferences.getString(SP_DARK_THEME_KEY + widgetId, DarkModeSetting.SYSTEM_DEFAULT.name())).getModeId(); - } catch (ClassCastException e) { - //DARK_THEME was a boolean in older versions of the app. We thereofre have to still support the old setting. - themeMode = sharedPreferences.getBoolean(SP_DARK_THEME_KEY + widgetId, false) ? DarkModeSetting.DARK.getModeId() : DarkModeSetting.LIGHT.getModeId(); - } + try { + themeMode = DarkModeSetting.valueOf(sharedPreferences.getString(SP_DARK_THEME_KEY + widgetId, DarkModeSetting.SYSTEM_DEFAULT.name())).getModeId(); + } catch (ClassCastException e) { + //DARK_THEME was a boolean in older versions of the app. We thereofre have to still support the old setting. + themeMode = sharedPreferences.getBoolean(SP_DARK_THEME_KEY + widgetId, false) ? DarkModeSetting.DARK.getModeId() : DarkModeSetting.LIGHT.getModeId(); + } - if (mode == 2) { - final String categoryTitle = sharedPreferences.getString(SP_CATEGORY_KEY + widgetId, null); - Cursor cursor = db.query( - "CATEGORIES", - new String[]{"CATEGORY_ID"}, - "CATEGORY_TITLE = ? AND CATEGORY_ACCOUNT_ID = ? ", - new String[]{categoryTitle, String.valueOf(accountId)}, - null, - null, - null); - if (cursor.moveToNext()) { - categoryId = cursor.getInt(0); - } else { - throw new IllegalStateException("No category id found for title \"" + categoryTitle + "\""); - } - cursor.close(); + if (mode == 2) { + final String categoryTitle = sharedPreferences.getString(SP_CATEGORY_KEY + widgetId, null); + Cursor cursor = db.query( + "CATEGORIES", + new String[]{"CATEGORY_ID"}, + "CATEGORY_TITLE = ? AND CATEGORY_ACCOUNT_ID = ? ", + new String[]{categoryTitle, String.valueOf(accountId)}, + null, + null, + null); + if (cursor.moveToNext()) { + categoryId = cursor.getInt(0); + } else { + throw new IllegalStateException("No category id found for title \"" + categoryTitle + "\""); } - - ContentValues migratedWidgetValues = new ContentValues(); - migratedWidgetValues.put("ID", widgetId); - migratedWidgetValues.put("ACCOUNT_ID", accountId); - migratedWidgetValues.put("CATEGORY_ID", categoryId); - migratedWidgetValues.put("MODE", mode); - migratedWidgetValues.put("THEME_MODE", themeMode); - db.insert("WIDGET_NOTE_LISTS", null, migratedWidgetValues); - } catch (Throwable t) { - Log.e(TAG, "Could not migrate widget {widgetId: " + widgetId + ", accountId: " + accountId + ", mode: " + mode + ", categoryId: " + categoryId + ", themeMode: " + themeMode + "}"); - t.printStackTrace(); - } finally { - // Clean up old shared preferences - editor.remove(SP_WIDGET_KEY + widgetId); - editor.remove(SP_CATEGORY_KEY + widgetId); - editor.remove(SP_DARK_THEME_KEY + widgetId); - editor.remove(SP_ACCOUNT_ID_KEY + widgetId); + cursor.close(); } + + ContentValues migratedWidgetValues = new ContentValues(); + migratedWidgetValues.put("ID", widgetId); + migratedWidgetValues.put("ACCOUNT_ID", accountId); + migratedWidgetValues.put("CATEGORY_ID", categoryId); + migratedWidgetValues.put("MODE", mode); + migratedWidgetValues.put("THEME_MODE", themeMode); + db.insert("WIDGET_NOTE_LISTS", null, migratedWidgetValues); + } catch (Throwable t) { + Log.e(TAG, "Could not migrate widget {widgetId: " + widgetId + ", accountId: " + accountId + ", mode: " + mode + ", categoryId: " + categoryId + ", themeMode: " + themeMode + "}"); + t.printStackTrace(); + } finally { + // Clean up old shared preferences + editor.remove(SP_WIDGET_KEY + widgetId); + editor.remove(SP_CATEGORY_KEY + widgetId); + editor.remove(SP_DARK_THEME_KEY + widgetId); + editor.remove(SP_ACCOUNT_ID_KEY + widgetId); } } - editor.apply(); - notifyWidgets.run(); } + editor.apply(); + notifyWidgets.run(); } } diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_16_17.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_16_17.java new file mode 100644 index 00000000..547cb6c7 --- /dev/null +++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_16_17.java @@ -0,0 +1,15 @@ +package it.niedermann.owncloud.notes.persistence.migration; + +import android.database.sqlite.SQLiteDatabase; + +import androidx.annotation.NonNull; + +public class Migration_16_17 { + /** + * Adds a column to store the current scroll position per note + * https://github.com/stefan-niedermann/nextcloud-notes/issues/227 + */ + public Migration_16_17(@NonNull SQLiteDatabase db) { + db.execSQL("ALTER TABLE NOTES ADD COLUMN SCROLL_Y INTEGER DEFAULT 0"); + } +} diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_4_5.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_4_5.java index 4ad3368a..612ed1cb 100644 --- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_4_5.java +++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_4_5.java @@ -2,10 +2,15 @@ package it.niedermann.owncloud.notes.persistence.migration; import android.database.sqlite.SQLiteDatabase; +import androidx.annotation.NonNull; + import it.niedermann.owncloud.notes.model.DBStatus; public class Migration_4_5 { - public Migration_4_5(SQLiteDatabase db, int oldVersion) { + /** + * Differentiate between local id and remote id + */ + public Migration_4_5(@NonNull SQLiteDatabase db) { db.execSQL("ALTER TABLE NOTES ADD COLUMN REMOTEID INTEGER"); db.execSQL("UPDATE NOTES SET REMOTEID=ID WHERE (REMOTEID IS NULL OR REMOTEID=0) AND STATUS!=?", new String[]{"LOCAL_CREATED"}); db.execSQL("UPDATE NOTES SET REMOTEID=0, STATUS=? WHERE STATUS=?", new String[]{DBStatus.LOCAL_EDITED.getTitle(), "LOCAL_CREATED"}); diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_5_6.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_5_6.java index 412834cd..184a6033 100644 --- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_5_6.java +++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_5_6.java @@ -2,10 +2,13 @@ package it.niedermann.owncloud.notes.persistence.migration; import android.database.sqlite.SQLiteDatabase; +import androidx.annotation.NonNull; + public class Migration_5_6 { - public Migration_5_6(SQLiteDatabase db, int oldVersion) { - if (oldVersion < 6) { - db.execSQL("ALTER TABLE NOTES ADD COLUMN FAVORITE INTEGER DEFAULT 0"); - } + /** + * Adds a column to support marking notes as favorite + */ + public Migration_5_6(@NonNull SQLiteDatabase db) { + db.execSQL("ALTER TABLE NOTES ADD COLUMN FAVORITE INTEGER DEFAULT 0"); } } diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_6_7.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_6_7.java index 3003e7f1..399d6f40 100644 --- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_6_7.java +++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_6_7.java @@ -2,15 +2,18 @@ package it.niedermann.owncloud.notes.persistence.migration; import android.database.sqlite.SQLiteDatabase; +import androidx.annotation.NonNull; + import it.niedermann.owncloud.notes.util.DatabaseIndexUtil; public class Migration_6_7 { - public Migration_6_7(SQLiteDatabase db, int oldVersion) { - if (oldVersion < 7) { - DatabaseIndexUtil.dropIndexes(db); - db.execSQL("ALTER TABLE NOTES ADD COLUMN CATEGORY TEXT NOT NULL DEFAULT ''"); - db.execSQL("ALTER TABLE NOTES ADD COLUMN ETAG TEXT"); - DatabaseIndexUtil.createIndex(db, "NOTES", "REMOTEID", "STATUS", "FAVORITE", "CATEGORY", "MODIFIED"); - } + /** + * Adds columns for category support and ETags + */ + public Migration_6_7(@NonNull SQLiteDatabase db) { + DatabaseIndexUtil.dropIndexes(db); + db.execSQL("ALTER TABLE NOTES ADD COLUMN CATEGORY TEXT NOT NULL DEFAULT ''"); + db.execSQL("ALTER TABLE NOTES ADD COLUMN ETAG TEXT"); + DatabaseIndexUtil.createIndex(db, "NOTES", "REMOTEID", "STATUS", "FAVORITE", "CATEGORY", "MODIFIED"); } } diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_7_8.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_7_8.java new file mode 100644 index 00000000..9a968fe1 --- /dev/null +++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_7_8.java @@ -0,0 +1,28 @@ +package it.niedermann.owncloud.notes.persistence.migration; + +import android.database.sqlite.SQLiteDatabase; + +import androidx.annotation.NonNull; + +import it.niedermann.owncloud.notes.util.DatabaseIndexUtil; + +public class Migration_7_8 { + public Migration_7_8(@NonNull SQLiteDatabase db) { + final String table_temp = "NOTES_TEMP"; + db.execSQL("CREATE TABLE " + table_temp + " ( " + + "ID INTEGER PRIMARY KEY AUTOINCREMENT, " + + "REMOTEID INTEGER, " + + "STATUS VARCHAR(50), " + + "TITLE TEXT, " + + "MODIFIED INTEGER DEFAULT 0, " + + "CONTENT TEXT, " + + "FAVORITE INTEGER DEFAULT 0, " + + "CATEGORY TEXT NOT NULL DEFAULT '', " + + "ETAG TEXT)"); + DatabaseIndexUtil.createIndex(db, table_temp, "REMOTEID", "STATUS", "FAVORITE", "CATEGORY", "MODIFIED"); + db.execSQL(String.format("INSERT INTO %s(%s,%s,%s,%s,%s,%s,%s,%s,%s) ", table_temp, "ID", "REMOTEID", "STATUS", "TITLE", "MODIFIED", "CONTENT", "FAVORITE", "CATEGORY", "ETAG") + + String.format("SELECT %s,%s,%s,%s,strftime('%%s',%s),%s,%s,%s,%s FROM %s", "ID", "REMOTEID", "STATUS", "TITLE", "MODIFIED", "CONTENT", "FAVORITE", "CATEGORY", "ETAG", "NOTES")); + db.execSQL("DROP TABLE NOTES"); + db.execSQL(String.format("ALTER TABLE %s RENAME TO %s", table_temp, "NOTES")); + } +} diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_8_9.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_8_9.java index 201d02c8..31957fc0 100644 --- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_8_9.java +++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_8_9.java @@ -23,104 +23,105 @@ public class Migration_8_9 { private static final String TAG = Migration_8_9.class.getSimpleName(); - public Migration_8_9(SQLiteDatabase db, int oldVersion, @NonNull Context context, @NonNull Consumer<SQLiteDatabase> recreateDatabase, @NonNull Runnable notifyWidgets) { - if (oldVersion < 9) { - // Create accounts table - db.execSQL("CREATE TABLE ACCOUNTS ( " + - "ID INTEGER PRIMARY KEY AUTOINCREMENT, " + - "URL TEXT, " + - "USERNAME TEXT, " + - "ACCOUNT_NAME TEXT UNIQUE, " + - "ETAG TEXT, " + - "MODIFIED INTEGER)"); - DatabaseIndexUtil.createIndex(db, "ACCOUNTS", "URL", "USERNAME", "ACCOUNT_NAME", "ETAG", "MODIFIED"); - - // Add accountId to notes table - db.execSQL("ALTER TABLE NOTES ADD COLUMN ACCOUNT_ID INTEGER NOT NULL DEFAULT 0"); - DatabaseIndexUtil.createIndex(db, "NOTES", "ACCOUNT_ID"); - - // Migrate existing account from SharedPreferences - SharedPreferences sharedPreferences = PreferenceManager.getDefaultSharedPreferences(context); - String username = sharedPreferences.getString("settingsUsername", ""); - String url = sharedPreferences.getString("settingsUrl", ""); - if (!url.isEmpty() && url.endsWith("/")) { - url = url.substring(0, url.length() - 1); - try { - String accountName = username + "@" + new URL(url).getHost(); - - ContentValues migratedAccountValues = new ContentValues(); - migratedAccountValues.put("URL", url); - migratedAccountValues.put("USERNAME", username); - migratedAccountValues.put("ACCOUNT_NAME", accountName); - db.insert("ACCOUNTS", null, migratedAccountValues); - - // After successful insertion of migrated account, set accountId to 1 in each note - ContentValues values = new ContentValues(); - values.put("ACCOUNT_ID", 1); - db.update("NOTES", values, "ACCOUNT_ID = ?", new String[]{"NULL"}); - - // Add FOREIGN_KEY constraint - final String table_temp = "NOTES_TEMP"; - db.execSQL(String.format("ALTER TABLE %s RENAME TO %s", "NOTES", table_temp)); - - db.execSQL("CREATE TABLE NOTES ( " + - "ID INTEGER PRIMARY KEY AUTOINCREMENT, " + - "REMOTEID INTEGER, " + - "ACCOUNT_ID INTEGER, " + - "STATUS VARCHAR(50), " + - "TITLE TEXT, " + - "MODIFIED INTEGER DEFAULT 0, " + - "CONTENT TEXT, " + - "FAVORITE INTEGER DEFAULT 0, " + - "CATEGORY TEXT NOT NULL DEFAULT '', " + - "ETAG TEXT," + - "FOREIGN KEY(ACCOUNT_ID) REFERENCES ACCOUNTS(ID))"); - DatabaseIndexUtil.createIndex(db, "NOTES", "REMOTEID", "ACCOUNT_ID", "STATUS", "FAVORITE", "CATEGORY", "MODIFIED"); - - db.execSQL(String.format("INSERT INTO %s(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ", "NOTES", "ID", "ACCOUNT_ID", "REMOTEID", "STATUS", "TITLE", "MODIFIED", "CONTENT", "FAVORITE", "CATEGORY", "ETAG") - + String.format("SELECT %s,%s,%s,%s,%s,%s,%s,%s,%s,%s FROM %s", "ID", values.get("ACCOUNT_ID"), "REMOTEID", "STATUS", "TITLE", "MODIFIED", "CONTENT", "FAVORITE", "CATEGORY", "ETAG", table_temp)); - db.execSQL(String.format("DROP TABLE %s;", table_temp)); - - AppWidgetManager awm = AppWidgetManager.getInstance(context); - SharedPreferences.Editor editor = sharedPreferences.edit(); - - // Add accountId '1' to any existing (and configured) appwidgets - int[] appWidgetIdsNLW = awm.getAppWidgetIds(new ComponentName(context, NoteListWidget.class)); - int[] appWidgetIdsSNW = awm.getAppWidgetIds(new ComponentName(context, SingleNoteWidget.class)); - - final String WIDGET_MODE_KEY = "NLW_mode"; - final String ACCOUNT_ID_KEY = "NLW_account"; - - for (int appWidgetId : appWidgetIdsNLW) { - if (sharedPreferences.getInt(WIDGET_MODE_KEY + appWidgetId, -1) >= 0) { - editor.putLong(ACCOUNT_ID_KEY + appWidgetId, 1); - } + /** + * Adds an account table for multi account usage in combination with SingleSignOn + */ + public Migration_8_9(@NonNull SQLiteDatabase db, @NonNull Context context, @NonNull Consumer<SQLiteDatabase> recreateDatabase, @NonNull Runnable notifyWidgets) { + // Create accounts table + db.execSQL("CREATE TABLE ACCOUNTS ( " + + "ID INTEGER PRIMARY KEY AUTOINCREMENT, " + + "URL TEXT, " + + "USERNAME TEXT, " + + "ACCOUNT_NAME TEXT UNIQUE, " + + "ETAG TEXT, " + + "MODIFIED INTEGER)"); + DatabaseIndexUtil.createIndex(db, "ACCOUNTS", "URL", "USERNAME", "ACCOUNT_NAME", "ETAG", "MODIFIED"); + + // Add accountId to notes table + db.execSQL("ALTER TABLE NOTES ADD COLUMN ACCOUNT_ID INTEGER NOT NULL DEFAULT 0"); + DatabaseIndexUtil.createIndex(db, "NOTES", "ACCOUNT_ID"); + + // Migrate existing account from SharedPreferences + SharedPreferences sharedPreferences = PreferenceManager.getDefaultSharedPreferences(context); + String username = sharedPreferences.getString("settingsUsername", ""); + String url = sharedPreferences.getString("settingsUrl", ""); + if (!url.isEmpty() && url.endsWith("/")) { + url = url.substring(0, url.length() - 1); + try { + String accountName = username + "@" + new URL(url).getHost(); + + ContentValues migratedAccountValues = new ContentValues(); + migratedAccountValues.put("URL", url); + migratedAccountValues.put("USERNAME", username); + migratedAccountValues.put("ACCOUNT_NAME", accountName); + db.insert("ACCOUNTS", null, migratedAccountValues); + + // After successful insertion of migrated account, set accountId to 1 in each note + ContentValues values = new ContentValues(); + values.put("ACCOUNT_ID", 1); + db.update("NOTES", values, "ACCOUNT_ID = ?", new String[]{"NULL"}); + + // Add FOREIGN_KEY constraint + final String table_temp = "NOTES_TEMP"; + db.execSQL(String.format("ALTER TABLE %s RENAME TO %s", "NOTES", table_temp)); + + db.execSQL("CREATE TABLE NOTES ( " + + "ID INTEGER PRIMARY KEY AUTOINCREMENT, " + + "REMOTEID INTEGER, " + + "ACCOUNT_ID INTEGER, " + + "STATUS VARCHAR(50), " + + "TITLE TEXT, " + + "MODIFIED INTEGER DEFAULT 0, " + + "CONTENT TEXT, " + + "FAVORITE INTEGER DEFAULT 0, " + + "CATEGORY TEXT NOT NULL DEFAULT '', " + + "ETAG TEXT," + + "FOREIGN KEY(ACCOUNT_ID) REFERENCES ACCOUNTS(ID))"); + DatabaseIndexUtil.createIndex(db, "NOTES", "REMOTEID", "ACCOUNT_ID", "STATUS", "FAVORITE", "CATEGORY", "MODIFIED"); + + db.execSQL(String.format("INSERT INTO %s(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ", "NOTES", "ID", "ACCOUNT_ID", "REMOTEID", "STATUS", "TITLE", "MODIFIED", "CONTENT", "FAVORITE", "CATEGORY", "ETAG") + + String.format("SELECT %s,%s,%s,%s,%s,%s,%s,%s,%s,%s FROM %s", "ID", values.get("ACCOUNT_ID"), "REMOTEID", "STATUS", "TITLE", "MODIFIED", "CONTENT", "FAVORITE", "CATEGORY", "ETAG", table_temp)); + db.execSQL(String.format("DROP TABLE %s;", table_temp)); + + AppWidgetManager awm = AppWidgetManager.getInstance(context); + SharedPreferences.Editor editor = sharedPreferences.edit(); + + // Add accountId '1' to any existing (and configured) appwidgets + int[] appWidgetIdsNLW = awm.getAppWidgetIds(new ComponentName(context, NoteListWidget.class)); + int[] appWidgetIdsSNW = awm.getAppWidgetIds(new ComponentName(context, SingleNoteWidget.class)); + + final String WIDGET_MODE_KEY = "NLW_mode"; + final String ACCOUNT_ID_KEY = "NLW_account"; + + for (int appWidgetId : appWidgetIdsNLW) { + if (sharedPreferences.getInt(WIDGET_MODE_KEY + appWidgetId, -1) >= 0) { + editor.putLong(ACCOUNT_ID_KEY + appWidgetId, 1); } + } - for (int appWidgetId : appWidgetIdsSNW) { - if (sharedPreferences.getLong("single_note_widget" + appWidgetId, -1) >= 0) { - editor.putLong("SNW_accountId" + appWidgetId, 1); - } + for (int appWidgetId : appWidgetIdsSNW) { + if (sharedPreferences.getLong("single_note_widget" + appWidgetId, -1) >= 0) { + editor.putLong("SNW_accountId" + appWidgetId, 1); } - - notifyWidgets.run(); - - // Clean up no longer needed SharedPreferences - editor.remove("notes_last_etag"); - editor.remove("notes_last_modified"); - editor.remove("settingsUrl"); - editor.remove("settingsUsername"); - editor.remove("settingsPassword"); - editor.apply(); - } catch (MalformedURLException e) { - Log.e(TAG, "Previous URL could not be parsed. Recreating database..."); - e.printStackTrace(); - recreateDatabase.accept(db); } - } else { - Log.e(TAG, "Previous URL is empty or does not end with a '/' character. Recreating database..."); + + notifyWidgets.run(); + + // Clean up no longer needed SharedPreferences + editor.remove("notes_last_etag"); + editor.remove("notes_last_modified"); + editor.remove("settingsUrl"); + editor.remove("settingsUsername"); + editor.remove("settingsPassword"); + editor.apply(); + } catch (MalformedURLException e) { + Log.e(TAG, "Previous URL could not be parsed. Recreating database..."); + e.printStackTrace(); recreateDatabase.accept(db); } + } else { + Log.e(TAG, "Previous URL is empty or does not end with a '/' character. Recreating database..."); + recreateDatabase.accept(db); } } } diff --git a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_9_10.java b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_9_10.java index 9aec3c9b..98ddc601 100644 --- a/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_9_10.java +++ b/app/src/main/java/it/niedermann/owncloud/notes/persistence/migration/Migration_9_10.java @@ -4,19 +4,23 @@ import android.content.ContentValues; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; +import androidx.annotation.NonNull; + import it.niedermann.owncloud.notes.util.NoteUtil; public class Migration_9_10 { - public Migration_9_10(SQLiteDatabase db, int oldVersion) { - if (oldVersion < 10) { - db.execSQL("ALTER TABLE NOTES ADD COLUMN EXCERPT INTEGER NOT NULL DEFAULT ''"); - Cursor cursor = db.query("NOTES", new String[]{"ID", "CONTENT"}, null, null, null, null, null, null); - while (cursor.moveToNext()) { - ContentValues values = new ContentValues(); - values.put("EXCERPT", NoteUtil.generateNoteExcerpt(cursor.getString(1))); - db.update("NOTES", values, "ID" + " = ? ", new String[]{cursor.getString(0)}); - } - cursor.close(); + /** + * Adds a column to store excerpt instead of regenerating it each time + * https://github.com/stefan-niedermann/nextcloud-notes/issues/528 + */ + public Migration_9_10(@NonNull SQLiteDatabase db) { + db.execSQL("ALTER TABLE NOTES ADD COLUMN EXCERPT INTEGER NOT NULL DEFAULT ''"); + Cursor cursor = db.query("NOTES", new String[]{"ID", "CONTENT"}, null, null, null, null, null, null); + while (cursor.moveToNext()) { + ContentValues values = new ContentValues(); + values.put("EXCERPT", NoteUtil.generateNoteExcerpt(cursor.getString(1))); + db.update("NOTES", values, "ID" + " = ? ", new String[]{cursor.getString(0)}); } + cursor.close(); } } |