· 8 years ago · Feb 19, 2018, 07:38 AM
1package yuku.alkitab.base.storage;
2
3import android.content.ContentValues;
4import android.content.Context;
5import android.database.Cursor;
6import android.database.sqlite.SQLiteDatabase;
7import android.database.sqlite.SQLiteOpenHelper;
8import android.support.v4.util.LongSparseArray;
9import android.util.Log;
10import yuku.afw.App;
11import yuku.afw.storage.Preferences;
12import yuku.alkitab.base.config.VersionConfig;
13import yuku.alkitab.base.model.MVersionDb;
14import yuku.alkitab.base.model.MVersionPreset;
15import yuku.alkitab.base.util.AddonManager;
16import yuku.alkitab.model.util.Gid;
17
18import java.io.File;
19
20public class InternalDbHelper extends SQLiteOpenHelper {
21 public static final String TAG = InternalDbHelper.class.getSimpleName();
22
23 public InternalDbHelper(Context context) {
24 super(context, "AlkitabDb", null, App.getVersionCode());
25 }
26
27 @Override
28 public void onOpen(SQLiteDatabase db) {
29 // db.execSQL("PRAGMA synchronous=OFF");
30 }
31
32 @Override public void onCreate(SQLiteDatabase db) {
33 Log.d(TAG, "@@onCreate");
34
35 createTableMarker(db);
36 createIndexMarker(db);
37 createTableDevotion(db);
38 createIndexDevotion(db);
39 createTableLabel(db);
40 createIndexLabel(db);
41 createTableMarker_Label(db);
42 createIndexMarker_Label(db);
43 createTableProgressMark(db);
44 createIndexProgressMark(db);
45 insertDefaultProgressMarks(db);
46 createTableProgressMarkHistory(db);
47 createIndexProgressMarkHistory(db);
48 createTableReadingPlan(db);
49 createTableReadingPlanProgress(db);
50 createIndexReadingPlanProgress(db);
51 createTableVersion(db);
52 createIndexVersion(db);
53 createTableSyncShadow(db);
54 createIndexSyncShadow(db);
55 createTableSyncLog(db);
56 createIndexSyncLog(db);
57 }
58
59 @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
60 Log.d(TAG, "@@onUpgrade oldVersion=" + oldVersion + " newVersion=" + newVersion);
61
62 // No more support for Bookmark (Bukmak) version 1 table (last published: 2010-06-14)
63 // if (oldVersion <= 23) {
64 // convertFromBookmarkToBookmark2(db);
65 // }
66
67 if (oldVersion <= 50) {
68 // new table Version
69 createTableEdisi(db);
70 createIndexVersion(db);
71 }
72
73 if (oldVersion <= 69) { // 70: 2.0.0
74 // new tables Label and Marker_Label
75 createTableLabel(db);
76 createIndexLabel(db);
77 createTableMarker_Label(db);
78 createIndexMarker_Label(db);
79 }
80
81 if (oldVersion <= 70) { // 71: 2.0.0 too
82 createIndexMarker(db);
83 }
84
85 if (oldVersion > 50 && oldVersion <= 102) { // 103: 2.7.1
86 addShortNameColumnAndIndexToEdisi(db);
87 }
88
89 if (oldVersion <= 126) { // 127: 3.2.0
90 createTableProgressMark(db);
91 createIndexProgressMark(db);
92 insertDefaultProgressMarks(db);
93 createTableProgressMarkHistory(db);
94 createIndexProgressMarkHistory(db);
95 }
96
97 // bug in 137 (3.3.3) where ReadingPlanProgress table is created with a wrong column name.
98 // so that column name is found, drop that table.
99 if (oldVersion >= 137 && oldVersion <= 142) {
100 boolean needDrop = false;
101 final Cursor c = db.rawQuery("pragma table_info(" + Db.TABLE_ReadingPlanProgress + ")", null);
102 if (c != null) {
103 while (c.moveToNext()) {
104 final String name = c.getString(1 /* "name" column */);
105 Log.d(TAG, "column name: " + name);
106 if ("checkedTime".equals(name)) { // this is a bad column name
107 needDrop = true;
108 }
109 }
110 }
111 if (needDrop) {
112 Log.d(TAG, "table need to be dropped: " + Db.TABLE_ReadingPlanProgress);
113 db.execSQL("drop table " + Db.TABLE_ReadingPlanProgress);
114 }
115 }
116
117 if (oldVersion <= 142) { // 143: 3.4.4
118 // (These tables were first introduced in 3.4.0, but because of the above bug, this needs to be recreated)
119 createTableReadingPlan(db);
120 createTableReadingPlanProgress(db);
121 createIndexReadingPlanProgress(db);
122 }
123
124 if (oldVersion < 14000163) { // last version that doesn't use Marker table
125 addGidColumnToLabel(db);
126
127 createTableMarker(db);
128 createIndexMarker(db);
129 createTableMarker_Label(db);
130 createIndexMarker_Label(db);
131
132 convertFromBookmark2ToMarker(db);
133 }
134
135 if (oldVersion < 14000166) { // last version that doesn't use the new Version table
136 createTableVersion(db);
137 createIndexVersion(db);
138 convertFromEdisiToVersion(db);
139 }
140
141 if (oldVersion <= 14000170) { // support for sync starting from 4-beta10
142 createTableSyncShadow(db);
143 createIndexSyncShadow(db);
144 }
145
146 if (oldVersion < 14000173) { // sync logs
147 createTableSyncLog(db);
148 createIndexSyncLog(db);
149 }
150
151 if (oldVersion >= 14000163 && oldVersion < 14000172) {
152 db.execSQL("drop index if exists index_Marker_Label_03");
153 }
154
155 if (oldVersion < 14000200) { // 14000200: v4.1
156 // change Devotion table to new format
157 // ignore old data, no need to migrate
158 db.execSQL("drop table if exists Renungan");
159 createTableDevotion(db);
160 createIndexDevotion(db);
161 }
162 }
163
164 private void createTableMarker(SQLiteDatabase db) {
165 db.execSQL(
166 "create table if not exists " + Db.TABLE_Marker + " (" +
167 "_id integer primary key autoincrement, " +
168 Db.Marker.gid + " text," +
169 Db.Marker.ari + " integer, " +
170 Db.Marker.kind + " integer, " +
171 Db.Marker.caption + " text, " +
172 Db.Marker.verseCount + " integer, " +
173 Db.Marker.createTime + " integer, " +
174 Db.Marker.modifyTime + " integer" +
175 ")"
176 );
177 }
178
179 private void createIndexMarker(SQLiteDatabase db) {
180 db.execSQL("create index if not exists index_Marker_01 on " + Db.TABLE_Marker + " (" + Db.Marker.ari + ")");
181 db.execSQL("create index if not exists index_Marker_02 on " + Db.TABLE_Marker + " (" + Db.Marker.kind + ", " + Db.Marker.ari + ")");
182 db.execSQL("create index if not exists index_Marker_03 on " + Db.TABLE_Marker + " (" + Db.Marker.kind + ", " + Db.Marker.modifyTime + ")");
183 db.execSQL("create index if not exists index_Marker_04 on " + Db.TABLE_Marker + " (" + Db.Marker.kind + ", " + Db.Marker.createTime + ")");
184 db.execSQL("create index if not exists index_Marker_05 on " + Db.TABLE_Marker + " (" + Db.Marker.kind + ", " + Db.Marker.caption + " collate NOCASE)");
185 db.execSQL("create index if not exists index_Marker_06 on " + Db.TABLE_Marker + " (" + Db.Marker.gid + ")");
186 }
187
188 private void createTableDevotion(SQLiteDatabase db) {
189 final StringBuilder sb = new StringBuilder("create table if not exists " + Table.Devotion.tableName() + " ( _id integer primary key ");
190 for (Table.Devotion field: Table.Devotion.values()) {
191 sb.append(',');
192 sb.append(field.name());
193 sb.append(' ');
194 sb.append(field.type.name());
195 if (field.suffix != null) {
196 sb.append(' ');
197 sb.append(field.suffix);
198 }
199 }
200 sb.append(")");
201 db.execSQL(sb.toString());
202 }
203
204 private void createIndexDevotion(SQLiteDatabase db) {
205 db.execSQL("create index if not exists index_Devotion_01 on " + Table.Devotion.tableName() + " (" + Table.Devotion.name + ", " + Table.Devotion.date + ", " + Table.Devotion.dataFormatVersion + ")");
206 db.execSQL("create index if not exists index_Devotion_02 on " + Table.Devotion.tableName() + " (" + Table.Devotion.touchTime + ")");
207 }
208
209 private void createTableEdisi(SQLiteDatabase db) {
210 db.execSQL("create table if not exists Edisi (" +
211 "_id integer primary key autoincrement, " +
212 "shortName text, " +
213 "judul text, " +
214 "jenis text, " +
215 "keterangan text, " +
216 Db.Version.filename + "namafile text, " +
217 "namafile_pdbasal text, " +
218 "aktif integer, " +
219 "urutan integer)");
220 }
221
222 void createTableVersion(SQLiteDatabase db) {
223 db.execSQL("create table if not exists " + Db.TABLE_Version + " (" +
224 "_id integer primary key autoincrement, " +
225 Db.Version.locale + " text," +
226 Db.Version.shortName + " text," +
227 Db.Version.longName + " text," +
228 Db.Version.description + " text," +
229 Db.Version.filename + " text," +
230 Db.Version.preset_name + " text," +
231 Db.Version.modifyTime + " integer," +
232 Db.Version.active + " integer," +
233 Db.Version.ordering + " integer)"
234 );
235 }
236
237 void createIndexVersion(SQLiteDatabase db) {
238 db.execSQL("create index if not exists index_Version_01 on " + Db.TABLE_Version + " (" + Db.Version.ordering + ")");
239 db.execSQL("create index if not exists index_Version_02 on " + Db.TABLE_Version + " (" + Db.Version.active + "," + Db.Version.longName + ")");
240 db.execSQL("create index if not exists index_Version_03 on " + Db.TABLE_Version + " (" + Db.Version.preset_name + ")");
241 }
242
243 void createTableSyncShadow(final SQLiteDatabase db) {
244 final StringBuilder sb = new StringBuilder("create table " + Table.SyncShadow.tableName() + " ( _id integer primary key ");
245 for (Table.SyncShadow field: Table.SyncShadow.values()) {
246 sb.append(',');
247 sb.append(field.name());
248 sb.append(' ');
249 sb.append(field.type.name());
250 if (field.suffix != null) {
251 sb.append(' ');
252 sb.append(field.suffix);
253 }
254 }
255 sb.append(")");
256 db.execSQL(sb.toString());
257 }
258
259 void createIndexSyncShadow(final SQLiteDatabase db) {
260 db.execSQL("create index if not exists index_SyncShadow_01 on " + Table.SyncShadow.tableName() + " (" + Table.SyncShadow.syncSetName + ")");
261 }
262
263 void createTableSyncLog(final SQLiteDatabase db) {
264 final StringBuilder sb = new StringBuilder("create table " + Table.SyncLog.tableName() + " ( _id integer primary key ");
265 for (Table.SyncLog field: Table.SyncLog.values()) {
266 sb.append(',');
267 sb.append(field.name());
268 sb.append(' ');
269 sb.append(field.type.name());
270 if (field.suffix != null) {
271 sb.append(' ');
272 sb.append(field.suffix);
273 }
274 }
275 sb.append(")");
276 db.execSQL(sb.toString());
277 }
278
279 void createIndexSyncLog(final SQLiteDatabase db) {
280 // do not create many indexes for SyncLog
281 db.execSQL("create index if not exists index_SyncLog_01 on " + Table.SyncLog.tableName() + " (" + Table.SyncLog.createTime + ")");
282 }
283
284 private void createTableLabel(SQLiteDatabase db) {
285 db.execSQL("create table if not exists " + Db.TABLE_Label + " (" +
286 "_id integer primary key autoincrement, " +
287 Db.Label.gid + " text," +
288 Db.Label.title + " text, " +
289 Db.Label.ordering + " integer, " +
290 Db.Label.backgroundColor + " text" +
291 ")"
292 );
293 }
294
295 private void createIndexLabel(SQLiteDatabase db) {
296 db.execSQL("create index if not exists index_401 on " + Db.TABLE_Label + " (" + Db.Label.ordering + ")");
297 db.execSQL("create index if not exists index_402 on " + Db.TABLE_Label + " (" + Db.Label.gid + ")");
298 }
299
300 private void createTableMarker_Label(SQLiteDatabase db) {
301 db.execSQL("create table if not exists " + Db.TABLE_Marker_Label + " (" +
302 "_id integer primary key autoincrement, " +
303 Db.Marker_Label.gid + " text," +
304 Db.Marker_Label.marker_gid + " text, " +
305 Db.Marker_Label.label_gid + " text" +
306 ")"
307 );
308 }
309
310 private void createIndexMarker_Label(SQLiteDatabase db) {
311 db.execSQL("create index if not exists index_Marker_Label_01 on " + Db.TABLE_Marker_Label + " (" + Db.Marker_Label.marker_gid + ")");
312 db.execSQL("create index if not exists index_Marker_Label_02 on " + Db.TABLE_Marker_Label + " (" + Db.Marker_Label.label_gid + ")");
313 // unique index index_Marker_Label_03 on Marker_Label (marker_gid, label_gid) is no longer used as of versionCode 14000172
314 db.execSQL("create unique index if not exists index_Marker_Label_04 on " + Db.TABLE_Marker_Label + " (" + Db.Marker_Label.gid + ")");
315 }
316
317 private void createTableProgressMark(SQLiteDatabase db) {
318 db.execSQL("create table if not exists " + Db.TABLE_ProgressMark + " (" +
319 "_id integer primary key autoincrement, " +
320 Db.ProgressMark.preset_id + " integer, " +
321 Db.ProgressMark.caption + " text, " +
322 Db.ProgressMark.ari + " integer, " +
323 Db.ProgressMark.modifyTime + " integer)");
324 }
325
326 private void createIndexProgressMark(SQLiteDatabase db) {
327 db.execSQL("create index if not exists index_601 on " + Db.TABLE_ProgressMark + " (" + Db.ProgressMark.preset_id + ")");
328 }
329
330 private void createTableProgressMarkHistory(SQLiteDatabase db) {
331 db.execSQL("create table if not exists " + Db.TABLE_ProgressMarkHistory + " (" +
332 "_id integer primary key autoincrement, " +
333 Db.ProgressMarkHistory.progress_mark_preset_id + " integer, " +
334 Db.ProgressMarkHistory.progress_mark_caption + " integer, " +
335 Db.ProgressMarkHistory.ari + " integer, " +
336 Db.ProgressMarkHistory.createTime + " integer)");
337 }
338
339 private void createIndexProgressMarkHistory(SQLiteDatabase db) {
340 db.execSQL("create index if not exists index_701 on " + Db.TABLE_ProgressMarkHistory + " (" + Db.ProgressMarkHistory.progress_mark_preset_id + ", " + Db.ProgressMarkHistory.createTime + ")");
341 }
342
343 private void insertDefaultProgressMarks(SQLiteDatabase db) {
344 ContentValues cv = new ContentValues();
345 cv.put(Db.ProgressMark.ari, 0);
346 for (int i = 0; i < 5; i++) {
347 cv.put(Db.ProgressMark.preset_id, i);
348 db.insert(Db.TABLE_ProgressMark, null, cv);
349 }
350 }
351
352 private void createTableReadingPlan(final SQLiteDatabase db) {
353 db.execSQL("create table if not exists " + Db.TABLE_ReadingPlan + " (" +
354 "_id integer primary key autoincrement, " +
355 Db.ReadingPlan.version + " integer, " +
356 Db.ReadingPlan.name + " text, " +
357 Db.ReadingPlan.title + " text, " +
358 Db.ReadingPlan.description + " text, " +
359 Db.ReadingPlan.duration + " integer, " +
360 Db.ReadingPlan.startTime + " integer, " +
361 Db.ReadingPlan.data + " blob)");
362 }
363
364 private void createTableReadingPlanProgress(final SQLiteDatabase db) {
365 db.execSQL("create table if not exists " + Db.TABLE_ReadingPlanProgress + " (" +
366 "_id integer primary key autoincrement, " +
367 Db.ReadingPlanProgress.reading_plan_id + " integer, " +
368 Db.ReadingPlanProgress.reading_code + " integer, " +
369 Db.ReadingPlanProgress.checkTime + " integer)");
370 }
371
372 private void createIndexReadingPlanProgress(SQLiteDatabase db) {
373 db.execSQL("create unique index if not exists index_901 on " + Db.TABLE_ReadingPlanProgress + " (" + Db.ReadingPlanProgress.reading_plan_id + ", " + Db.ReadingPlanProgress.reading_code + ")");
374 }
375
376 // This needs to be kept, for upgrading from version 51-102 to 14000165
377 private void addShortNameColumnAndIndexToEdisi(SQLiteDatabase db) {
378 db.execSQL("alter table Edisi add column shortName text");
379 }
380
381 private void addGidColumnToLabel(SQLiteDatabase db) {
382 db.execSQL("alter table " + Db.TABLE_Label + " add column " + Db.Label.gid + " text");
383
384 // make sure this one matches the one in createIndexLabel()
385 db.execSQL("create index if not exists index_402 on " + Db.TABLE_Label + " (" + Db.Label.gid + ")");
386 }
387
388 /**
389 * Converts Bookmark2 to Marker table
390 * and Bookmark2_Label to Marker_Label table
391 * and add gid to all labels
392 */
393 private void convertFromBookmark2ToMarker(final SQLiteDatabase db) {
394 final String TABLE_Bookmark2 = "Bukmak2";
395 class Bookmark2 {
396 public static final String ari = "ari";
397 public static final String kind = "jenis";
398 public static final String caption = "tulisan";
399 public static final String addTime = "waktuTambah";
400 public static final String modifyTime = "waktuUbah";
401 }
402
403 final String TABLE_Bookmark2_Label = "Bukmak2_Label";
404 class Bookmark2_Label {
405 public static final String bookmark2_id = "bukmak2_id";
406 public static final String label_id = "label_id";
407 }
408
409 // We need to maintain _id to prevent complications with Marker_Label
410 db.beginTransaction();
411 try {
412 final LongSparseArray<String> idToGid_marker = new LongSparseArray<>();
413 final LongSparseArray<String> idToGid_label = new LongSparseArray<>();
414
415 { // Bookmark2 -> Marker
416 final Cursor c = db.query(TABLE_Bookmark2,
417 new String[]{"_id", Bookmark2.ari, Bookmark2.kind, Bookmark2.caption, Bookmark2.addTime, Bookmark2.modifyTime},
418 null, null, null, null, "_id asc"
419 );
420
421 final ContentValues cv = new ContentValues();
422 while (c.moveToNext()) {
423 final long _id = c.getLong(0);
424 final String gid = Gid.newGid();
425
426 idToGid_marker.put(_id, gid);
427
428 cv.put("_id", _id);
429 cv.put(Db.Marker.ari, c.getInt(1));
430 cv.put(Db.Marker.kind, c.getInt(2));
431 cv.put(Db.Marker.caption, c.getString(3));
432 cv.put(Db.Marker.createTime, c.getLong(4));
433 cv.put(Db.Marker.modifyTime, c.getLong(5));
434 cv.put(Db.Marker.verseCount, 1);
435 cv.put(Db.Marker.gid, gid);
436 db.insert(Db.TABLE_Marker, null, cv);
437 }
438
439 c.close();
440 }
441
442 { // add gid to all Labels
443 final String[] args = {null};
444 final Cursor c = db.query(Db.TABLE_Label, new String[]{"_id"}, null, null, null, null, null);
445 final ContentValues cv = new ContentValues();
446 while (c.moveToNext()) {
447 final long _id = c.getLong(0);
448 final String gid = Gid.newGid();
449
450 idToGid_label.put(_id, gid);
451
452 cv.put(Db.Label.gid, gid);
453 args[0] = String.valueOf(_id);
454 db.update(Db.TABLE_Label, cv, "_id = ?", args);
455 }
456 c.close();
457 }
458
459 { // Bookmark2_Label -> Marker_Label
460 final Cursor c = db.query(TABLE_Bookmark2_Label,
461 new String[] {"_id", Bookmark2_Label.bookmark2_id, Bookmark2_Label.label_id},
462 null, null, null, null, "_id asc"
463 );
464 final ContentValues cv = new ContentValues();
465 while (c.moveToNext()) {
466 final long _id = c.getLong(0);
467 final long marker_id = c.getLong(1);
468 final long label_id = c.getLong(2);
469
470 final String marker_gid = idToGid_marker.get(marker_id);
471 final String label_gid = idToGid_label.get(label_id);
472
473 cv.put("_id", _id);
474 cv.put(Db.Marker_Label.gid, Gid.newGid());
475 cv.put(Db.Marker_Label.marker_gid, marker_gid);
476 cv.put(Db.Marker_Label.label_gid, label_gid);
477 db.insert(Db.TABLE_Marker_Label, null, cv);
478 }
479 }
480
481 db.setTransactionSuccessful();
482 } finally {
483 db.endTransaction();
484 }
485 }
486
487 /**
488 * Converts the old version (Edisi) table, to the new Version table.
489 *
490 * This will keep user's added yes file (excluding the preset versions)
491 */
492 private void convertFromEdisiToVersion(final SQLiteDatabase db) {
493 final String TABLE_Edisi = "Edisi";
494 class Edisi {
495 public static final String shortName = "shortName";
496 public static final String title = "judul";
497 public static final String description = "keterangan";
498 public static final String kind = "jenis";
499 public static final String filename = "namafile";
500 // unused: public static final String filename_originalpdb = "namafile_pdbasal";
501 public static final String active = "aktif";
502 public static final String ordering = "urutan";
503 }
504
505 db.beginTransaction();
506 try {
507 Preferences.hold();
508 final ContentValues cv = new ContentValues();
509 int ordering = MVersionDb.DEFAULT_ORDERING_START;
510
511 /**
512 * Automatically add v3 preset versions as {@link yuku.alkitab.base.storage.Db.Version} table rows,
513 * if there are files with the same preset_name as those defined in {@link yuku.alkitab.base.config.VersionConfig}.
514 * In version 3, preset versions are not stored in the database. In version 4, they are.
515 */
516 {
517 final VersionConfig vc = VersionConfig.get();
518 for (final MVersionPreset mv : vc.presets) {
519 final String filename = AddonManager.getVersionPath(mv.preset_name + ".yes");
520 final File yesFile = new File(filename);
521 if (yesFile.exists() && yesFile.canRead()) {
522 cv.clear();
523 cv.put(Db.Version.locale, mv.locale);
524 cv.put(Db.Version.shortName, mv.shortName);
525 cv.put(Db.Version.longName, mv.longName);
526 cv.put(Db.Version.description, mv.description);
527 cv.put(Db.Version.filename, filename);
528 cv.put(Db.Version.preset_name, mv.preset_name);
529 cv.put(Db.Version.modifyTime, (int) (yesFile.lastModified() / 1000L));
530 cv.put(Db.Version.active, Preferences.getBoolean("edisi/preset/" + mv.preset_name + ".yes/aktif", true) ? 1 : 0);
531 cv.put(Db.Version.ordering, ++ordering);
532 db.insert(Db.TABLE_Version, null, cv);
533 }
534 }
535 }
536
537 { // Remove all preferences about active state of preset versions. We don't need them any more.
538 for (String key : Preferences.getAllKeys()) {
539 if (key.startsWith("edisi/preset/") && key.endsWith(".yes/aktif")) {
540 Preferences.remove(key);
541 }
542 }
543 }
544
545 { // Edisi -> Version
546 final Cursor c = db.query(TABLE_Edisi,
547 new String[]{Edisi.shortName, Edisi.title, Edisi.description, Edisi.filename, Edisi.active},
548 null, null, null, null, Edisi.ordering + " asc"
549 );
550 while (c.moveToNext()) {
551 cv.clear();
552 cv.put(Db.Version.locale, (String) null);
553 cv.put(Db.Version.shortName, c.getString(0));
554 cv.put(Db.Version.longName, c.getString(1));
555 cv.put(Db.Version.description, c.getString(2));
556 cv.put(Db.Version.filename, c.getString(3));
557 cv.put(Db.Version.active, c.getInt(4));
558 cv.put(Db.Version.ordering, ++ordering);
559 db.insert(Db.TABLE_Version, null, cv);
560 }
561
562 c.close();
563 }
564
565 db.execSQL("drop table " + TABLE_Edisi);
566
567 db.setTransactionSuccessful();
568 } finally {
569 Preferences.unhold();
570 db.endTransaction();
571 }
572 }
573}