1 // Copyright 2015-present 650 Industries. All rights reserved.
2 package expo.modules.sqlite
3 
4 import android.content.Context
5 import android.database.Cursor
6 import androidx.core.os.bundleOf
7 import expo.modules.kotlin.exception.Exceptions
8 import expo.modules.kotlin.modules.Module
9 import expo.modules.kotlin.modules.ModuleDefinition
10 import io.expo.android.database.sqlite.SQLiteCustomExtension
11 import io.expo.android.database.sqlite.SQLiteDatabase
12 import io.expo.android.database.sqlite.SQLiteDatabaseConfiguration
13 import java.io.File
14 import java.io.IOException
15 import java.util.*
16 
17 private val EMPTY_ROWS = emptyArray<Array<Any?>>()
18 private val EMPTY_COLUMNS = emptyArray<String?>()
19 private val EMPTY_RESULT = SQLiteModule.SQLitePluginResult(EMPTY_ROWS, EMPTY_COLUMNS, 0, 0, null)
20 private val DATABASES: MutableMap<String, SQLiteDatabase?> = HashMap()
21 
22 class SQLiteModule : Module() {
23   private val context: Context
24     get() = appContext.reactContext ?: throw Exceptions.ReactContextLost()
25 
26   override fun definition() = ModuleDefinition {
27     Name("ExpoSQLite")
28 
29     Events("onDatabaseChange")
30 
31     AsyncFunction("exec") { dbName: String, queries: List<Query>, readOnly: Boolean ->
32       return@AsyncFunction execute(dbName, queries, readOnly)
33     }
34 
35     AsyncFunction("execRawQuery") { dbName: String, queries: List<Query>, readOnly: Boolean ->
36       return@AsyncFunction execute(dbName, queries, readOnly, raw = true)
37     }
38 
39     AsyncFunction("close") { dbName: String ->
40       DATABASES
41         .remove(dbName)
42         ?.close()
43     }
44 
45     Function("closeSync") { dbName: String ->
46       DATABASES
47         .remove(dbName)
48         ?.close()
49     }
50 
51     AsyncFunction("deleteAsync") { dbName: String ->
52       if (DATABASES.containsKey(dbName)) {
53         throw OpenDatabaseException(dbName)
54       }
55       val dbFile = File(pathForDatabaseName(dbName))
56       if (!dbFile.exists()) {
57         throw DatabaseNotFoundException(dbName)
58       }
59       if (!dbFile.delete()) {
60         throw DeleteDatabaseException(dbName)
61       }
62     }
63 
64     OnDestroy {
65       DATABASES.values.forEach {
66         it?.rawQuery("SELECT crsql_finalize()", emptyArray())
67       }
68     }
69   }
70 
71   private fun execute(dbName: String, queries: List<Query>, readOnly: Boolean, raw: Boolean = false): List<Any> {
72     val db = getDatabase(dbName)
73     val results = queries.map { sqlQuery ->
74       val sql = sqlQuery.sql
75       val bindArgs = convertParamsToStringArray(sqlQuery.args)
76       try {
77         if (isSelect(sql)) {
78           doSelectInBackgroundAndPossiblyThrow(sql, bindArgs, db)
79         } else { // update/insert/delete
80           if (readOnly) {
81             SQLitePluginResult(EMPTY_ROWS, EMPTY_COLUMNS, 0, 0, ReadOnlyException())
82           } else {
83             if (raw) {
84               doRawUpdate(sql, bindArgs, db)
85             } else {
86               doUpdateInBackgroundAndPossiblyThrow(sql, bindArgs, db)
87             }
88           }
89         }
90       } catch (e: Throwable) {
91         SQLitePluginResult(EMPTY_ROWS, EMPTY_COLUMNS, 0, 0, e)
92       }
93     }
94     return pluginResultsToPrimitiveData(results)
95   }
96 
97   // do a update/delete/insert operation
98   private fun doUpdateInBackgroundAndPossiblyThrow(
99     sql: String,
100     bindArgs: Array<String?>?,
101     db: SQLiteDatabase
102   ): SQLitePluginResult {
103     return db.compileStatement(sql).use { statement ->
104       if (bindArgs != null) {
105         for (i in bindArgs.size downTo 1) {
106           val args = bindArgs[i - 1]
107           if (args != null) {
108             statement.bindString(i, args)
109           } else {
110             statement.bindNull(i)
111           }
112         }
113       }
114       if (isInsert(sql)) {
115         val insertId = statement.executeInsert()
116         val rowsAffected = if (insertId >= 0) 1 else 0
117         SQLitePluginResult(EMPTY_ROWS, EMPTY_COLUMNS, rowsAffected, insertId, null)
118       } else if (isDelete(sql) || isUpdate(sql)) {
119         val rowsAffected = statement.executeUpdateDelete()
120         SQLitePluginResult(EMPTY_ROWS, EMPTY_COLUMNS, rowsAffected, 0, null)
121       } else {
122         // in this case, we don't need rowsAffected or insertId, so we can have a slight
123         // perf boost by just executing the query
124         statement.execute()
125         EMPTY_RESULT
126       }
127     }
128   }
129 
130   private fun doRawUpdate(
131     sql: String,
132     bindArgs: Array<String?>,
133     db: SQLiteDatabase
134   ): SQLitePluginResult {
135     return db.rawQuery(sql, bindArgs).use { cursor ->
136       val numRows = cursor.count
137       if (numRows == 0) {
138         return EMPTY_RESULT
139       }
140 
141       val numColumns = cursor.columnCount
142       val columnNames = cursor.columnNames
143       val rows: Array<Array<Any?>> = Array(numRows) { arrayOfNulls(numColumns) }
144       var i = 0
145       while (cursor.moveToNext()) {
146         val row = rows[i]
147         for (j in 0 until numColumns) {
148           row[j] = getValueFromCursor(cursor, j, cursor.getType(j))
149         }
150         rows[i] = row
151         i++
152       }
153 
154       if (isInsert(sql)) {
155         val rowsAffected = getRowsAffected(db)
156         val insertId = getInsertId(db)
157         SQLitePluginResult(rows, columnNames, rowsAffected, insertId, null)
158       } else if (isDelete(sql) || isUpdate(sql)) {
159         val rowsAffected = getRowsAffected(db)
160         SQLitePluginResult(rows, columnNames, rowsAffected, 0, null)
161       } else {
162         EMPTY_RESULT
163       }
164     }
165   }
166 
167   private fun getRowsAffected(
168     db: SQLiteDatabase,
169   ): Int {
170     val cursor = db.rawQuery("SELECT changes() AS numRowsAffected", null)
171     val rowsAffected = if (cursor.moveToFirst()) {
172       val index = cursor.getColumnIndex("numRowsAffected")
173       cursor.getInt(index)
174     } else {
175       -1
176     }
177     cursor.close()
178     return rowsAffected
179   }
180 
181   private fun getInsertId(
182     db: SQLiteDatabase,
183   ): Long {
184     val cursor = db.rawQuery("SELECT last_insert_rowid() AS insertId", null)
185     val insertId = if (cursor.moveToFirst()) {
186       val index = cursor.getColumnIndex("insertId")
187       cursor.getLong(index)
188     } else {
189       -1
190     }
191     cursor.close()
192     return insertId
193   }
194 
195   // do a select operation
196   private fun doSelectInBackgroundAndPossiblyThrow(
197     sql: String,
198     bindArgs: Array<String?>,
199     db: SQLiteDatabase
200   ): SQLitePluginResult {
201     return db.rawQuery(sql, bindArgs).use { cursor ->
202       val numRows = cursor.count
203       if (numRows == 0) {
204         return EMPTY_RESULT
205       }
206       val numColumns = cursor.columnCount
207       val columnNames = cursor.columnNames
208       val rows: Array<Array<Any?>> = Array(numRows) { arrayOfNulls(numColumns) }
209       var i = 0
210       while (cursor.moveToNext()) {
211         val row = rows[i]
212         for (j in 0 until numColumns) {
213           row[j] = getValueFromCursor(cursor, j, cursor.getType(j))
214         }
215         rows[i] = row
216         i++
217       }
218       SQLitePluginResult(rows, columnNames, 0, 0, null)
219     }
220   }
221 
222   private fun getValueFromCursor(cursor: Cursor, index: Int, columnType: Int): Any? {
223     return when (columnType) {
224       Cursor.FIELD_TYPE_FLOAT -> cursor.getDouble(index)
225       Cursor.FIELD_TYPE_INTEGER -> cursor.getLong(index)
226       Cursor.FIELD_TYPE_BLOB ->
227         // convert byte[] to binary string; it's good enough, because
228         // WebSQL doesn't support blobs anyway
229         String(cursor.getBlob(index))
230 
231       Cursor.FIELD_TYPE_STRING -> cursor.getString(index)
232       else -> null
233     }
234   }
235 
236   @Throws(IOException::class)
237   private fun pathForDatabaseName(name: String): String {
238     val directory = File("${context.filesDir}${File.separator}SQLite")
239     ensureDirExists(directory)
240     return "$directory${File.separator}$name"
241   }
242 
243   @Throws(IOException::class)
244   private fun getDatabase(name: String): SQLiteDatabase {
245     var database: SQLiteDatabase? = null
246     val path = pathForDatabaseName(name)
247     if (File(path).exists()) {
248       database = DATABASES[name]
249     }
250     if (database == null) {
251       DATABASES.remove(name)
252       val config = createConfig(path)
253       database = SQLiteDatabase.openDatabase(config, null, null)
254       addUpdateListener(database)
255       DATABASES[name] = database
256     }
257     return database!!
258   }
259 
260   private fun createConfig(path: String): SQLiteDatabaseConfiguration {
261     val crsqliteExtension = SQLiteCustomExtension("libcrsqlite", "sqlite3_crsqlite_init")
262     return SQLiteDatabaseConfiguration(path, SQLiteDatabase.CREATE_IF_NECESSARY, emptyList(), emptyList(), listOf(crsqliteExtension))
263   }
264 
265   private fun addUpdateListener(database: SQLiteDatabase?) {
266     database?.addUpdateListener { tableName: String, operationType: Int, rowID: Int ->
267       sendEvent(
268         "onDatabaseChange",
269         bundleOf(
270           "tableName" to tableName,
271           "rowId" to rowID,
272           "typeId" to when (operationType) {
273             9 -> SqlAction.DELETE.value
274             18 -> SqlAction.INSERT.value
275             23 -> SqlAction.UPDATE.value
276             else -> SqlAction.UNKNOWN.value
277           }
278         )
279       )
280     }
281   }
282 
283   internal class SQLitePluginResult(
284     val rows: Array<Array<Any?>>,
285     val columns: Array<String?>,
286     val rowsAffected: Int,
287     val insertId: Long,
288     val error: Throwable?
289   )
290 
291   private class ReadOnlyException : Exception("could not prepare statement (23 not authorized)")
292 }
293