app/src/main/kotlin/space/subread/dictionary/Dictionaries.kt (8892 bytes)
1 package space.subread.dictionary 2 3 import android.content.Context 4 import android.database.sqlite.SQLiteDatabase 5 import android.database.sqlite.SQLiteOpenHelper 6 import androidx.core.database.sqlite.transaction 7 import space.subread.dictionary.core.DictionaryIndex 8 import space.subread.dictionary.core.DictionarySink 9 import space.subread.dictionary.core.StoredTerm 10 import space.subread.dictionary.core.Tag 11 import space.subread.dictionary.core.Term 12 import space.subread.dictionary.core.TermMeta 13 import space.subread.dictionary.core.TermSource 14 import space.subread.dictionary.core.YomitanZip 15 import java.io.InputStream 16 17 /** One imported dictionary. `terms` counts the term rows and the meta rows: a pitch or frequency dictionary has meta rows alone. */ 18 data class DictionaryInfo(val id: Long, val title: String, val revision: String, val position: Int, val enabled: Boolean, val terms: Int) 19 20 /** A frequency or pitch entry, with the title of the dictionary it comes from. */ 21 data class StoredMeta(val dictionaryTitle: String, val meta: TermMeta) 22 23 /** 24 * The imported dictionaries, in one SQLite database. The terms of every dictionary are in one 25 * table, so a lookup is one query. The rows keep the fields of the Yomitan format as they are. 26 */ 27 class Dictionaries(context: Context, name: String = "dictionaries.db") : SQLiteOpenHelper(context, name, null, 1), TermSource { 28 29 override fun onCreate(db: SQLiteDatabase) { 30 db.execSQL("CREATE TABLE dictionaries (id INTEGER PRIMARY KEY, title TEXT NOT NULL UNIQUE, revision TEXT NOT NULL, position INTEGER NOT NULL, enabled INTEGER NOT NULL DEFAULT 1)") 31 db.execSQL( 32 "CREATE TABLE terms (id INTEGER PRIMARY KEY, dictionary INTEGER NOT NULL, expression TEXT NOT NULL, reading TEXT NOT NULL, " + 33 "definition_tags TEXT NOT NULL, rules TEXT NOT NULL, score REAL NOT NULL, glossary TEXT NOT NULL, sequence INTEGER NOT NULL, term_tags TEXT NOT NULL)", 34 ) 35 db.execSQL("CREATE INDEX terms_expression ON terms(expression)") 36 db.execSQL("CREATE INDEX terms_reading ON terms(reading)") 37 db.execSQL("CREATE INDEX terms_dictionary ON terms(dictionary)") 38 db.execSQL("CREATE TABLE meta (id INTEGER PRIMARY KEY, dictionary INTEGER NOT NULL, expression TEXT NOT NULL, mode TEXT NOT NULL, data TEXT NOT NULL)") 39 db.execSQL("CREATE INDEX meta_expression ON meta(expression)") 40 db.execSQL("CREATE INDEX meta_dictionary ON meta(dictionary)") 41 } 42 43 override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) = Unit 44 45 fun list(): List<DictionaryInfo> = readableDatabase.rawQuery( 46 "SELECT d.id, d.title, d.revision, d.position, d.enabled, " + 47 "(SELECT COUNT(*) FROM terms t WHERE t.dictionary = d.id) + (SELECT COUNT(*) FROM meta m WHERE m.dictionary = d.id) " + 48 "FROM dictionaries d ORDER BY d.position", 49 null, 50 ).use { c -> 51 buildList { 52 while (c.moveToNext()) add(DictionaryInfo(c.getLong(0), c.getString(1), c.getString(2), c.getInt(3), c.getInt(4) != 0, c.getInt(5))) 53 } 54 } 55 56 fun hasTitle(title: String): Boolean = readableDatabase.rawQuery("SELECT 1 FROM dictionaries WHERE title = ?", arrayOf(title)).use { it.moveToFirst() } 57 58 /** 59 * Imports the banks of a zip, in one transaction: a failure half way leaves nothing. The 60 * progress callback gets the number of rows so far, terms and meta, from the import thread. 61 */ 62 fun import(index: DictionaryIndex, zip: InputStream, progress: (Int) -> Unit): DictionaryInfo { 63 require(!hasTitle(index.title)) { "exists" } 64 return writableDatabase.transaction { 65 val db = this 66 val position = (db.rawQuery("SELECT MAX(position) FROM dictionaries", null).use { if (it.moveToFirst()) it.getInt(0) else 0 }) + 1 67 val id = db.compileStatement("INSERT INTO dictionaries (title, revision, position, enabled) VALUES (?, ?, ?, 1)").run { 68 bindString(1, index.title) 69 bindString(2, index.revision) 70 bindLong(3, position.toLong()) 71 executeInsert() 72 } 73 val termInsert = db.compileStatement( 74 "INSERT INTO terms (dictionary, expression, reading, definition_tags, rules, score, glossary, sequence, term_tags) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", 75 ) 76 val metaInsert = db.compileStatement("INSERT INTO meta (dictionary, expression, mode, data) VALUES (?, ?, ?, ?)") 77 var count = 0 78 YomitanZip.readBanks(zip, object : DictionarySink { 79 override fun term(term: Term) { 80 termInsert.bindLong(1, id) 81 termInsert.bindString(2, term.expression) 82 // A term with no reading is read as it is written. The stored reading is the text a kana lookup finds. 83 termInsert.bindString(3, term.reading.ifEmpty { term.expression }) 84 termInsert.bindString(4, term.definitionTags) 85 termInsert.bindString(5, term.rules) 86 termInsert.bindDouble(6, term.score) 87 termInsert.bindString(7, term.glossary) 88 termInsert.bindLong(8, term.sequence) 89 termInsert.bindString(9, term.termTags) 90 termInsert.executeInsert() 91 if (++count % 1000 == 0) progress(count) 92 } 93 94 override fun termMeta(meta: TermMeta) { 95 metaInsert.bindLong(1, id) 96 metaInsert.bindString(2, meta.expression) 97 metaInsert.bindString(3, meta.mode) 98 metaInsert.bindString(4, meta.data) 99 metaInsert.executeInsert() 100 if (++count % 1000 == 0) progress(count) 101 } 102 103 // The tag bank gives the notes of a tag. The pop-up shows the tag names alone. 104 override fun tag(tag: Tag) = Unit 105 }) 106 DictionaryInfo(id, index.title, index.revision, position, true, count) 107 } 108 } 109 110 fun delete(id: Long) { 111 writableDatabase.transaction { 112 delete("terms", "dictionary = ?", arrayOf(id.toString())) 113 delete("meta", "dictionary = ?", arrayOf(id.toString())) 114 delete("dictionaries", "id = ?", arrayOf(id.toString())) 115 } 116 } 117 118 fun setEnabled(id: Long, enabled: Boolean) { 119 writableDatabase.execSQL("UPDATE dictionaries SET enabled = ? WHERE id = ?", arrayOf<Any>(if (enabled) 1 else 0, id)) 120 } 121 122 /** Moves a dictionary one place up in the order. The first one stays. */ 123 fun moveUp(id: Long) { 124 val all = list() 125 val i = all.indexOfFirst { it.id == id } 126 if (i <= 0) return 127 val db = writableDatabase 128 db.execSQL("UPDATE dictionaries SET position = ? WHERE id = ?", arrayOf<Any>(all[i - 1].position, id)) 129 db.execSQL("UPDATE dictionaries SET position = ? WHERE id = ?", arrayOf<Any>(all[i].position, all[i - 1].id)) 130 } 131 132 override fun terms(texts: Collection<String>): List<StoredTerm> { 133 val db = readableDatabase 134 val out = ArrayList<StoredTerm>() 135 // SQLite takes a limited number of parameters in one statement. 136 for (chunk in texts.chunked(400)) { 137 val marks = chunk.joinToString(",") { "?" } 138 val args = (chunk + chunk).toTypedArray() 139 db.rawQuery( 140 "SELECT t.expression, t.reading, t.definition_tags, t.rules, t.score, t.glossary, t.sequence, t.term_tags, d.id, d.title, d.position " + 141 "FROM terms t JOIN dictionaries d ON d.id = t.dictionary " + 142 "WHERE d.enabled = 1 AND (t.expression IN ($marks) OR t.reading IN ($marks))", 143 args, 144 ).use { c -> 145 while (c.moveToNext()) { 146 val term = Term(c.getString(0), c.getString(1), c.getString(2), c.getString(3), c.getDouble(4), c.getString(5), c.getLong(6), c.getString(7)) 147 out.add(StoredTerm(c.getLong(8), c.getString(9), c.getInt(10), term)) 148 } 149 } 150 } 151 return out 152 } 153 154 /** The frequency and pitch entries of an expression, from the enabled dictionaries in their order. */ 155 fun meta(expression: String): List<StoredMeta> = readableDatabase.rawQuery( 156 "SELECT m.expression, m.mode, m.data, d.title FROM meta m JOIN dictionaries d ON d.id = m.dictionary " + 157 "WHERE d.enabled = 1 AND m.expression = ? ORDER BY d.position", 158 arrayOf(expression), 159 ).use { c -> 160 buildList { while (c.moveToNext()) add(StoredMeta(c.getString(3), TermMeta(c.getString(0), c.getString(1), c.getString(2)))) } 161 } 162 163 fun anyEnabled(): Boolean = readableDatabase.rawQuery("SELECT 1 FROM dictionaries WHERE enabled = 1", null).use { it.moveToFirst() } 164 }