1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
|
package it.niedermann.owncloud.notes.persistence.migration;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
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
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);
}
}
}
|
