\n Note that the version of Sqlite we\u2019ll use on Android does not have any special \u201cvector database\u201d features \u2013 instead, the embedding vectors will just be serialized\/deserialized and stored in a \n The demo app we\u2019re working on is the JetchatAI<\/strong> sample that allows you to query the conference schedule for the droidcon SF 2023<\/em> event. The sample code is available at github.com\/conceptdev\/droidcon-sf-23<\/a>. Don\u2019t forget to choose the droidcon-chat<\/strong> from the top-left menu to test out these features.\n<\/p>\n \n Figure 1: Embeddings with similarity comparisons enable retrieval augmented generation (RAG) chat interactions<\/em>\n<\/p>\n \n The database setup uses the same pattern that we used for the favorites feature<\/a>. The schema and create\/delete scripts for an embeddings table are defined in the data\/DroidconDatabase.kt<\/strong><\/a> file:\n<\/p>\n Figure 2: database schema declaration<\/em>\n<\/p>\n \n The \n There is already an \n As mentioned above, there are no special vector-handling features in the existing version of Sqlite, so the code will treat the vector as a string for data storage and retrieval.\n<\/p>\n \n In Figure 2, you can see the first half of the code snippet has not changed \u2013 it still loops through the conference schedule and generates an embedding for each session via the web API. The second half of the snippet adds the database customization \u2013 first the vector (which is an array of Figure 3: code that generates embedding vectors then inserts into a local database<\/em>\n<\/p>\n \n The function also has a new check \n Once the database has been loaded with embeddings, the existing Figure 4: read the embedding vectors from the database and store in-memory cache<\/em>\n<\/p>\n \n The \n The old version of the code needed to get embeddings each time the app was started. There are around 70 sessions in the droidcon schedule, so that was 70 web API calls every time! Now the web API calls happen once<\/em> and the results are stored in the database, available in each subsequent app restart. The only time they\u2019ll be regenerated is if the database schema changes \u2013 but you can optimize that too by spending some time on database migration strategies (beyond the scope of this post).\n<\/p>\n \n Not only is this cheaper (no more unnecessary embeddings API calls), but it\u2019ll also make the initial conversation startup time much faster.\n<\/p>\n \n We\u2019d love your feedback on this post, including any tips or tricks you\u2019ve learning from playing around with ChatGPT prompts.\n<\/p>\n \n If you have any thoughts or questions, use the\u00a0feedback forum<\/a>\u00a0or message us on\u00a0Twitter @surfaceduodev<\/a>.\n<\/p>\nTEXT<\/code> column. Embedding vector similarity comparisons will continue to be done with the dot<\/code> function defined in DroidconEmbeddingsWrapper.kt<\/strong><\/a>.\n<\/p>\n![\"Chat](\"https:\/\/devblogs.microsoft.com\/surface-duo\/wp-content\/uploads\/sites\/53\/2023\/07\/a-screenshot-of-a-chat-description-automatically-10.png\")
\n<\/p>\nConfigure the database<\/h2>\n
object EmbeddingEntry : BaseColumns {\r\n const val TABLE_NAME = \"embedding\"\r\n const val COLUMN_NAME_SESSIONID = \"session_id\"\r\n const val COLUMN_NAME_VECTOR = \"vector\"\r\n}\r\nprivate const val SQL_CREATE_EMBEDDING_ENTRIES =\r\n \"CREATE TABLE ${DroidconContract.EmbeddingEntry.TABLE_NAME} (\" +\r\n \"${DroidconContract.EmbeddingEntry.COLUMN_NAME_SESSIONID} TEXT PRIMARY KEY,\" +\r\n \"${DroidconContract.EmbeddingEntry.COLUMN_NAME_VECTOR} TEXT)\"\r\nprivate const val SQL_DELETE_EMBEDDING_ENTRIES = \"DROP TABLE IF EXISTS ${DroidconContract.EmbeddingEntry.TABLE_NAME}\"<\/pre>\nDroidconDbHelper<\/code> then takes care of creating the table (don\u2019t forget to update the DATABASE_VERSION<\/code> whenever you make schema changes).\n<\/p>\nStore vectors with SQLite<\/h2>\n
initVectorCache<\/code> function in the DroidconEmbeddingsWrapper.kt<\/strong> file which generates all the vectors in a loop over the droidconSessions<\/code> collection and stores the vectors in-memory in the vectorCache<\/code> variable. The updated code leverages this existing loop to also<\/em> insert each vector into the local database.\n<\/p>\nDouble<\/code> values) gets serialized, and then it is inserted into the database using the dbHelper<\/code> class that has been added to the function:\n<\/p>\n\r\n\/\/ Gets the data repository in write mode\r\nval db = dbHelper.writableDatabase\r\n for (session in DroidconSessionData.droidconSessions) {\r\n val embeddingRequest = EmbeddingRequest(\r\n model = ModelId(Constants.OPENAI_EMBED_MODEL),\r\n input = listOf(session.value)\r\n )\r\n val embedding = openAI.embeddings(embeddingRequest)\r\n val vector = embedding.embeddings[0].embedding.toDoubleArray()\r\n \/\/ add to in-memory cache\r\n vectorCache[session.key] = vector\r\n \/\/ serialize the vector to a string - removed for clarity\r\n \/\/ Create a new map of values, where column names are the keys\r\n val values = ContentValues().apply {\r\n put(DroidconContract.EmbeddingEntry.COLUMN_NAME_SESSIONID, session.key)\r\n put(DroidconContract.EmbeddingEntry.COLUMN_NAME_VECTOR, vectorString)\r\n }\r\n \/\/ Insert the new row, returning the primary key value of the row (would be -1 if error)\r\n val newRowId =\r\n db?.insert(DroidconContract.EmbeddingEntry.TABLE_NAME, null, values)\r\n }<\/pre>\nif (vectorCache.isEmpty())<\/code> so that once the data has been loaded once, it does not need to be re-loaded.\n<\/p>\nRetrieve vectors with SQLite<\/h2>\n
initVectorCache<\/code> function should now be able to use the locally stored embeddings rather than regenerating from the web API. The code to load the embedding vectors is encapsulated in a new loadVectorCache<\/code> function:\n<\/p>\nprivate fun loadVectorCache(dbHelper: DroidconDbHelper): Int {\r\n var rowCount = 0\r\n val db = dbHelper.readableDatabase\r\n val projection = arrayOf(\r\n DroidconContract.EmbeddingEntry.COLUMN_NAME_SESSIONID,\r\n DroidconContract.EmbeddingEntry.COLUMN_NAME_VECTOR)\r\n val cursor = db.query(\r\n DroidconContract.EmbeddingEntry.TABLE_NAME, \/\/ The table to query\r\n projection, \/\/ The array of columns to return \r\n null, null, null, null, null \/\/ unused customizations\r\n )\r\n with(cursor) {\r\n while (moveToNext()) {\r\n val sessionId = getString(getColumnIndexOrThrow(DroidconContract.EmbeddingEntry.COLUMN_NAME_SESSIONID))\r\n val vectorString = getString(getColumnIndexOrThrow(DroidconContract.EmbeddingEntry.COLUMN_NAME_VECTOR))\r\n \/\/ deserialize vector - removed for clarity\r\n \/\/ add to in-memory cache\r\n vectorCache[sessionId] = vector.toDoubleArray()\r\n rowCount++\r\n }\r\n }\r\n cursor.close()\r\n return rowCount\r\n}<\/pre>\ninitVectorCache<\/code> is responsible for loading via the most efficient method \u2013 it will check in-memory first, if the embeddings aren\u2019t cached it will attempt to load from the database. If the database is empty, it will generate new embeddings from the web API and update the database for next time.\n<\/p>\nProfit!<\/h2>\n
Resources and feedback<\/h2>\n