{"id":3372,"date":"2023-08-10T11:00:47","date_gmt":"2023-08-10T18:00:47","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/surface-duo\/?p=3372"},"modified":"2024-01-03T16:22:52","modified_gmt":"2024-01-04T00:22:52","slug":"android-openai-chatgpt-13","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/surface-duo\/android-openai-chatgpt-13\/","title":{"rendered":"Dynamic Sqlite queries with OpenAI chat functions"},"content":{"rendered":"

\n Hello prompt engineers,\n<\/p>\n

\n Previous blogs explained how to add droidcon session favorites to a database and also cache the embedding vectors in a database \u2013 but what if we stored everything in a database and then let the model query it directly?\n<\/p>\n

\n The OpenAI Cookbook examples repo includes a section on how to call functions with model generated arguments<\/a>, which includes a python demo of a function that understands a database schema and generates SQL that is executed to answer questions from the chat. There\u2019s also a natural language to SQL<\/a> demo that demonstrates the model\u2019s understanding of SQL.\n<\/p>\n

\n In this post, we\u2019ll implement the model generated arguments function call in Kotlin, using the droidcon SF 2023 conference schedule data as the source.\n<\/p>\n

\n \"PhoneFigure 1: JetchatAI droidcon SF demo answering questions with dynamically generated and executed SQL<\/em>\n<\/p>\n

SQLite on Android<\/h2>\n

\n Just like the previous posts on storing favorites and vectors, we\u2019ll use the Android Sqlite APIs described in the the SQLite training documentation<\/a>. Unlike those functions, however, we\u2019ll be using the rawQuery()<\/code> function rather than building up \u201cstrongly typed\u201d queries because the SQL will be generated by the model.\n<\/p>\n

\n As the example<\/a> explains, this can be dangerous in production applications, as the model is not perfectly reliable at generating valid SQL, so there\u2019s a possibility that it\u2019ll generate code that won\u2019t run (or an even more remote possibility that it could generate destructive queries). It could also expose data you don\u2019t want the user to see (from system tables, internal tables, or data they shouldn\u2019t have access to).\n<\/p>\n

\n In our use case, we\u2019ll only be executing against a read-only database API, so there\u2019s no risk of data loss, and there\u2019s only a single-user\u2019s favorites stored so no privacy concerns either.\n<\/p>\n

Creating the database<\/h2>\n

\n See the data\/DroidconDatabase.kt<\/strong><\/a> file for the complete database schema and data loading functionality. Since this is a sample focused on AI and not SQL, you\u2019ll notice the schema and code is somewhat simplistic. In a production application, you would spend more time on a robust data strategy.\n<\/p>\n

Creating an SQL query function<\/h2>\n

\n We\u2019ll start by creating the call function class, using the template from the save favorites<\/a> blog post. You can view the result in the functions\/AskDatabaseFunction.kt<\/strong><\/a> file, and the descriptive properties are shown in Figure 2:\n<\/p>\n

class AskDatabaseFunction {\r\n    companion object {\r\n        fun name(): String {\r\n            return \"askDatabase\"\r\n        }\r\n        fun description(): String {\r\n            return \"Answer user questions about conference sessions like what room sessions are presented in. Output should be a fully formed SQL query.\"\r\n        }\r\n        \/\/ ...<\/pre>\n
Figure 2: Starting point for the AskDatabaseFunction<\/code><\/em>\n<\/p>\n
\n  Now we need to figure out what the parameters are, and then how to handle the function when called.\n<\/p>\n
Teaching the model about a schema<\/h2>\n
\n  Exposing the table schema to the chat model via a function can enable it to generate its own queries. The ideal way to achieve this is by inferring the actual database schema and feeding it to the model as a function parameter. Because we have a simple data structure in the droidcon demo, it was quick and easy to build a \u2018proof of concept\u2019 by hardcoding the schema.\n<\/p>\n
\n  Figure 3 shows the hardcoded schema description along with prompt prefix and suffix that guides the model in how to interpret it.\n<\/p>\n
fun params(): Parameters {\r\n    val params = Parameters.buildJsonObject {\r\n        put(\"type\", \"object\")\r\n        putJsonObject(\"properties\") {\r\n            putJsonObject(\"query\") {\r\n                put(\"type\", \"string\")\r\n                put(\"\"\"\r\n  SQL query extracting info to answer the user's question.\r\n  SQL should be written using this database schema:\r\n  Table: sessions\r\n  Columns: session_id, speaker, role, location_id, date, time, subject, description\r\n  Table: favorites\r\n  Columns: session_id, is_favorite\r\n  Table: locations\r\n  Columns: location_id, description\r\n  The query should be returned in plain text, not in JSON.\"\"\".trimIndent()\r\n                )\r\n            }\r\n        }\r\n        putJsonArray(\"required\") {\r\n            add(\"query\")\r\n        }\r\n    }\r\n    return params\r\n}<\/pre>\n
Figure 3: Create a hardcoded schema to test the function and the SQL generated by the model<\/em>\n<\/p>\n
\n  The class is complete enough to do some debugging \u2013 add the following code to the chatCompletionRequest<\/code> and you can use logcat<\/em> or debugging to view the SQL that the model generates in response to your questions.\n<\/p>\n
function {\r\n      name = AskDatabaseFunction.name()\r\n      description = AskDatabaseFunction.description()\r\n      parameters = AskDatabaseFunction.params()\r\n}<\/pre>\n
Figure 4: Adding the hardcoded function params allows us to test the model\u2019s responses<\/em>\n<\/p>\n
\n  If you plan to manage your database schema carefully, you could probably get away with shipping a hardcoded schema like this. It would also make it easy to omit some tables or columns, preventing the model from exposing some data.\n<\/p>\n
Teaching the model with the latest schema<\/h2>\n
\n  To fully implement the OpenAI cookbook example we need to dynamically generate the schema using system tables. This will make the code less fragile and prevent a hardcoded schema from getting \u2018out of sync\u2019 with any changes made in DroidconDatabase.kt<\/strong>. By dynamically generating the tables and columns each time, the model will always be getting the latest schema that reflects exactly what\u2019s present in Sqlite.\n<\/p>\n
\n  To generate the schema, we need to query sqlite_master<\/code> and then the table_info<\/code> to get the table and column names, then format them into a structure like the hardcoded version shown above. Figure 3 shows the function that uses Sqlite system tables and outputs the schema shape as text:\n<\/p>\n
fun generateSimpleSchema(): String {\r\n    val db = readableDatabase\r\n    var out = \"\"\r\n    val tableCursor = db.rawQuery(\"SELECT name FROM sqlite_master WHERE type='table'\", null)\r\n    with(tableCursor) {\r\n        while (moveToNext()) {\r\n            val tableName = getString(0)  \/\/ table name\r\n            out += \"Table: $tableName\\nColumns: \"\r\n            val columnCursor = db.rawQuery(\"PRAGMA table_info('$tableName');\", null)\r\n            var needComma = false\r\n            with(columnCursor) {\r\n                while (moveToNext()) {\r\n                    val columnName = getString(1)  \/\/ column name\r\n                    if (needComma) out += \", \" else needComma = true\r\n                    out += \"$columnName\"\r\n                }\r\n            }\r\n            columnCursor.close()\r\n            out += \"\\n\\n\"\r\n        }\r\n    }\r\n    tableCursor.close()\r\n    return out\r\n}<\/pre>\n
Figure 5: Use the system tables to generate the database schema<\/em>\n<\/p>\n
\n  To use the dynamically generated schema, call db.generateSimpleSchema()<\/code> in  the params<\/code> method and use the result in the query.\n<\/p>\n
\n  Now that the model has access to the latest database schema, all that remains is to execute the queries that it generates.\n<\/p>\n
Dynamic SQL results<\/h2>\n
\n  As noted above, we\u2019re showing a lot of trust in the model by executing its SQL queries directly in our database. The code is shown in Figure 6 \u2013 notice the first line creates a read-only access to the database and then we execute the raw query directly. The rest of the function is just string formatting to return the columns in a specific format \u2013 in this case mimicking the OpenAI demo which uses a layout like this: [('abc', 1),('def', 2)]<\/code>:\n<\/p>\n
fun function(dbHelper: DroidconDbHelper, query: String): String {\r\n    val db = dbHelper.readableDatabase\r\n    val cursor = db.rawQuery(query,null)  \/\/ execute the query!\r\n    var rowCount = 0\r\n    var out = \"\"\r\n    var needOuterComma = false\r\n    with(cursor) {\r\n        out += \"[\"\r\n        var needComma = false\r\n        while (moveToNext()) {\r\n            if (needOuterComma) out += \",\" else needOuterComma = true\r\n            out += \"(\"\r\n            for (i in 0 until cursor.columnCount) {\r\n                if (needComma) out += \",\" else needComma = true\r\n                out += when (getType(i)) {\r\n                    FIELD_TYPE_INTEGER ->\r\n                        getString(i)\r\n                    else -> {\r\n                        \"'${getString(i)}'\"\r\n                    }\r\n                }\r\n            }\r\n            out += \")\"\r\n            rowCount++\r\n        }\r\n        out += \"]\"\r\n    }\r\n    cursor.close()\r\n    return if (out == \"\") {\r\n        \"0 rows affected\"\r\n    } else {\r\n        out\r\n    }\r\n}<\/pre>\n
Figure 6: Chat function class that can execute SQL directly on the local database<\/em>\n<\/p>\n
\n  The model seems to have no trouble understanding the output format and matching the tuples with the query that it created, so it can generate responses that correctly interpret complex return datasets.\n<\/p>\n
How the model uses SQL querying capabilities<\/h2>\n
\n  The droidcon SF chat demo already has a couple of mechanisms for interacting with conference data:\n<\/p>\n