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       val db = getDatabase(dbName)
28       val results = queries.map { sqlQuery ->
29         val sql = sqlQuery.sql
30         val bindArgs = convertParamsToStringArray(sqlQuery.args)
31         try {
32           if (isSelect(sql)) {
33             doSelectInBackgroundAndPossiblyThrow(sql, bindArgs, db)
34           } else { // update/insert/delete
35             if (readOnly) {
36               SQLitePluginResult(EMPTY_ROWS, EMPTY_COLUMNS, 0, 0, ReadOnlyException())
37             } else {
38               doUpdateInBackgroundAndPossiblyThrow(sql, bindArgs, db)
39             }
40           }
41         } catch (e: Throwable) {
42           SQLitePluginResult(EMPTY_ROWS, EMPTY_COLUMNS, 0, 0, e)
43         }
44       }
45       return@AsyncFunction pluginResultsToPrimitiveData(results)
46     }
47 
48     AsyncFunction("close") { dbName: String ->
49       DATABASES
50         .remove(dbName)
51         ?.close()
52     }
53 
54     Function("closeSync") { dbName: String ->
55       DATABASES
56         .remove(dbName)
57         ?.close()
58     }
59 
60     AsyncFunction("deleteAsync") { dbName: String ->
61       if (DATABASES.containsKey(dbName)) {
62         throw OpenDatabaseException(dbName)
63       }
64       val dbFile = File(pathForDatabaseName(dbName))
65       if (!dbFile.exists()) {
66         throw DatabaseNotFoundException(dbName)
67       }
68       if (!dbFile.delete()) {
69         throw DeleteDatabaseException(dbName)
70       }
71     }
72   }
73 
74   // do a update/delete/insert operation
75   private fun doUpdateInBackgroundAndPossiblyThrow(
76     sql: String,
77     bindArgs: Array<String?>?,
78     db: SQLiteDatabase
79   ): SQLitePluginResult {
80     return db.compileStatement(sql).use { statement ->
81       if (bindArgs != null) {
82         for (i in bindArgs.size downTo 1) {
83           val args = bindArgs[i - 1]
84           if (args != null) {
85             statement.bindString(i, args)
86           } else {
87             statement.bindNull(i)
88           }
89         }
90       }
91       if (isInsert(sql)) {
92         val insertId = statement.executeInsert()
93         val rowsAffected = if (insertId >= 0) 1 else 0
94         SQLitePluginResult(EMPTY_ROWS, EMPTY_COLUMNS, rowsAffected, insertId, null)
95       } else if (isDelete(sql) || isUpdate(sql)) {
96         val rowsAffected = statement.executeUpdateDelete()
97         SQLitePluginResult(EMPTY_ROWS, EMPTY_COLUMNS, rowsAffected, 0, null)
98       } else {
99         // in this case, we don't need rowsAffected or insertId, so we can have a slight
100         // perf boost by just executing the query
101         statement.execute()
102         EMPTY_RESULT
103       }
104     }
105   }
106 
107   // do a select operation
108   private fun doSelectInBackgroundAndPossiblyThrow(
109     sql: String,
110     bindArgs: Array<String?>,
111     db: SQLiteDatabase
112   ): SQLitePluginResult {
113     return db.rawQuery(sql, bindArgs).use { cursor ->
114       val numRows = cursor.count
115       if (numRows == 0) {
116         return EMPTY_RESULT
117       }
118       val numColumns = cursor.columnCount
119       val columnNames = cursor.columnNames
120       val rows: Array<Array<Any?>> = Array(numRows) { arrayOfNulls(numColumns) }
121       var i = 0
122       while (cursor.moveToNext()) {
123         val row = rows[i]
124         for (j in 0 until numColumns) {
125           row[j] = getValueFromCursor(cursor, j, cursor.getType(j))
126         }
127         rows[i] = row
128         i++
129       }
130       SQLitePluginResult(rows, columnNames, 0, 0, null)
131     }
132   }
133 
134   private fun getValueFromCursor(cursor: Cursor, index: Int, columnType: Int): Any? {
135     return when (columnType) {
136       Cursor.FIELD_TYPE_FLOAT -> cursor.getDouble(index)
137       Cursor.FIELD_TYPE_INTEGER -> cursor.getLong(index)
138       Cursor.FIELD_TYPE_BLOB ->
139         // convert byte[] to binary string; it's good enough, because
140         // WebSQL doesn't support blobs anyway
141         String(cursor.getBlob(index))
142 
143       Cursor.FIELD_TYPE_STRING -> cursor.getString(index)
144       else -> null
145     }
146   }
147 
148   @Throws(IOException::class)
149   private fun pathForDatabaseName(name: String): String {
150     val directory = File("${context.filesDir}${File.separator}SQLite")
151     ensureDirExists(directory)
152     return "$directory${File.separator}$name"
153   }
154 
155   @Throws(IOException::class)
156   private fun getDatabase(name: String): SQLiteDatabase {
157     var database: SQLiteDatabase? = null
158     val path = pathForDatabaseName(name)
159     if (File(path).exists()) {
160       database = DATABASES[name]
161     }
162     if (database == null) {
163       DATABASES.remove(name)
164       database = SQLiteDatabase.openOrCreateDatabase(path, null)
165       DATABASES[name] = database
166     }
167     return database!!
168   }
169 
170   internal class SQLitePluginResult(
171     val rows: Array<Array<Any?>>,
172     val columns: Array<String?>,
173     val rowsAffected: Int,
174     val insertId: Long,
175     val error: Throwable?
176   )
177 
178   private class ReadOnlyException : Exception("could not prepare statement (23 not authorized)")
179 }
180