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