@@ -19,6 +19,10 @@ namespace Microsoft.SemanticKernel.Connectors.Sqlite;
1919[ SuppressMessage ( "Security" , "CA2100:Review SQL queries for security vulnerabilities" , Justification = "User input is passed using command parameters." ) ]
2020internal static class SqliteVectorStoreCollectionCommandBuilder
2121{
22+ internal const string DistancePropertyName = "distance" ;
23+
24+ internal static string EscapeIdentifier ( this string value ) => value . Replace ( "'" , "''" ) . Replace ( "\" " , "\" \" " ) ;
25+
2226 public static DbCommand BuildTableCountCommand ( SqliteConnection connection , string tableName )
2327 {
2428 Verify . NotNullOrWhiteSpace ( tableName ) ;
@@ -41,16 +45,16 @@ public static DbCommand BuildCreateTableCommand(SqliteConnection connection, str
4145 {
4246 var builder = new StringBuilder ( ) ;
4347
44- builder . AppendLine ( $ """ CREATE TABLE { ( ifNotExists ? "IF NOT EXISTS " : string . Empty ) } "{ tableName . Replace ( " \" " , " \" \" " ) } " ( "" ") ;
48+ builder . AppendLine ( $ "CREATE TABLE { ( ifNotExists ? "IF NOT EXISTS " : string . Empty ) } \ "{ tableName } \" ( ") ;
4549
46- builder . AppendLine ( string . Join ( ",\n " , columns . Select ( GetColumnDefinition ) ) ) ;
47- builder . Append ( ");" ) ;
50+ builder . AppendLine ( string . Join ( ",\n " , columns . Select ( column => GetColumnDefinition ( column , quote : true ) ) ) ) ;
51+ builder . AppendLine ( ");" ) ;
4852
4953 foreach ( var column in columns )
5054 {
5155 if ( column . HasIndex )
5256 {
53- builder . AppendLine ( $ "CREATE INDEX { ( ifNotExists ? "IF NOT EXISTS " : string . Empty ) } { tableName } _{ column . Name } _index ON { tableName } ( { column . Name } );") ;
57+ builder . AppendLine ( $ "CREATE INDEX { ( ifNotExists ? "IF NOT EXISTS " : string . Empty ) } \" { tableName } _{ column . Name } _index\" ON \" { tableName } \" ( \" { column . Name } \" );") ;
5458 }
5559 }
5660
@@ -70,9 +74,10 @@ public static DbCommand BuildCreateVirtualTableCommand(
7074 {
7175 var builder = new StringBuilder ( ) ;
7276
73- builder . AppendLine ( $ "CREATE VIRTUAL TABLE { ( ifNotExists ? "IF NOT EXISTS " : string . Empty ) } ' { tableName . Replace ( "'" , "''" ) } ' USING { extensionName } (") ;
77+ builder . AppendLine ( $ "CREATE VIRTUAL TABLE { ( ifNotExists ? "IF NOT EXISTS " : string . Empty ) } \" { tableName } \" USING { extensionName } (") ;
7478
75- builder . AppendLine ( string . Join ( ",\n " , columns . Select ( GetColumnDefinition ) ) ) ;
79+ // The vector extension is currently uncapable of handling quoted identifiers.
80+ builder . AppendLine ( string . Join ( ",\n " , columns . Select ( column => GetColumnDefinition ( column , quote : false ) ) ) ) ;
7681 builder . Append ( ");" ) ;
7782
7883 var command = connection . CreateCommand ( ) ;
@@ -84,7 +89,7 @@ public static DbCommand BuildCreateVirtualTableCommand(
8489
8590 public static DbCommand BuildDropTableCommand ( SqliteConnection connection , string tableName )
8691 {
87- string query = $ "DROP TABLE IF EXISTS [ { tableName } ] ;";
92+ string query = $ "DROP TABLE IF EXISTS \" { tableName } \" ;";
8893
8994 var command = connection . CreateCommand ( ) ;
9095
@@ -97,8 +102,9 @@ public static DbCommand BuildInsertCommand(
97102 SqliteConnection connection ,
98103 string tableName ,
99104 string rowIdentifier ,
100- IReadOnlyList < string > columnNames ,
105+ IReadOnlyList < VectorStoreRecordPropertyModel > properties ,
101106 IReadOnlyList < Dictionary < string , object ? > > records ,
107+ bool data ,
102108 bool replaceIfExists = false )
103109 {
104110 var builder = new StringBuilder ( ) ;
@@ -111,11 +117,12 @@ public static DbCommand BuildInsertCommand(
111117 var rowIdentifierParameterName = GetParameterName ( rowIdentifier , recordIndex ) ;
112118
113119 var ( columns , parameters , values ) = GetQueryParts (
114- columnNames ,
120+ properties ,
115121 records [ recordIndex ] ,
116- recordIndex ) ;
122+ recordIndex ,
123+ data ) ;
117124
118- builder . AppendLine ( $ "INSERT{ replacePlaceholder } INTO { tableName } ({ string . Join ( ", " , columns ) } )") ;
125+ builder . AppendLine ( $ "INSERT{ replacePlaceholder } INTO \" { tableName } \" ({ string . Join ( ", " , columns ) } )") ;
119126 builder . AppendLine ( $ "VALUES ({ string . Join ( ", " , parameters ) } )") ;
120127 builder . AppendLine ( $ "RETURNING { rowIdentifier } ;") ;
121128
@@ -130,154 +137,187 @@ public static DbCommand BuildInsertCommand(
130137 return command ;
131138 }
132139
133- public static DbCommand BuildSelectCommand (
140+ public static DbCommand BuildSelectDataCommand < TRecord > (
134141 SqliteConnection connection ,
135142 string tableName ,
136- IReadOnlyList < string > columnNames ,
143+ VectorStoreRecordModel model ,
137144 List < SqliteWhereCondition > conditions ,
138- string ? orderByPropertyName = null )
145+ GetFilteredRecordOptions < TRecord > ? filterOptions = null ,
146+ string ? extraWhereFilter = null ,
147+ Dictionary < string , object > ? extraParameters = null ,
148+ int top = 0 ,
149+ int skip = 0 )
139150 {
140151 var builder = new StringBuilder ( ) ;
141152
142- var ( command , whereClause ) = GetCommandWithWhereClause ( connection , conditions ) ;
153+ var ( command , whereClause ) = GetCommandWithWhereClause ( connection , conditions , extraWhereFilter , extraParameters ) ;
154+
155+ builder . Append ( "SELECT " ) ;
156+ builder . AppendColumnNames ( includeVectors : false , model . Properties ) ;
157+ builder . AppendLine ( $ "FROM \" { tableName } \" ") ;
158+ builder . AppendWhereClause ( whereClause ) ;
143159
144- builder . AppendLine ( $ "SELECT { string . Join ( ", " , columnNames ) } ") ;
145- builder . AppendLine ( $ "FROM { tableName } ") ;
160+ if ( filterOptions is not null )
161+ {
162+ builder . AppendOrderBy ( model , filterOptions ) ;
163+ }
146164
147- AppendWhereClauseIfExists ( builder , whereClause ) ;
148- AppendOrderByIfExists ( builder , orderByPropertyName ) ;
165+ builder . AppendLimits ( top , skip ) ;
149166
150167 command . CommandText = builder . ToString ( ) ;
151168
152169 return command ;
153170 }
154171
155- public static DbCommand BuildSelectLeftJoinCommand (
172+ public static DbCommand BuildSelectLeftJoinCommand < TRecord > (
156173 SqliteConnection connection ,
157- string leftTable ,
158- string rightTable ,
174+ string vectorTableName ,
175+ string dataTableName ,
159176 string joinColumnName ,
160- IReadOnlyList < string > leftTablePropertyNames ,
161- IReadOnlyList < string > rightTablePropertyNames ,
162- List < SqliteWhereCondition > conditions ,
177+ VectorStoreRecordModel model ,
178+ IReadOnlyList < SqliteWhereCondition > conditions ,
179+ bool includeDistance ,
180+ GetFilteredRecordOptions < TRecord > ? filterOptions = null ,
163181 string ? extraWhereFilter = null ,
164182 Dictionary < string , object > ? extraParameters = null ,
165- string ? orderByPropertyName = null )
183+ int top = 0 ,
184+ int skip = 0 )
166185 {
167186 var builder = new StringBuilder ( ) ;
168187
169- List < string > propertyNames =
170- [
171- .. leftTablePropertyNames . Select ( property => $ "{ leftTable } .{ property } ") ,
172- .. rightTablePropertyNames . Select ( property => $ "{ rightTable } .{ property } ") ,
173- ] ;
174-
175188 var ( command , whereClause ) = GetCommandWithWhereClause ( connection , conditions , extraWhereFilter , extraParameters ) ;
176189
177- builder . AppendLine ( $ "SELECT { string . Join ( ", " , propertyNames ) } ") ;
178- builder . AppendLine ( $ "FROM { leftTable } ") ;
179- builder . AppendLine ( $ "LEFT JOIN { rightTable } ON { leftTable } .{ joinColumnName } = { rightTable } .{ joinColumnName } ") ;
190+ builder . Append ( "SELECT " ) ;
191+ builder . AppendColumnNames ( includeVectors : true , model . Properties , vectorTableName , dataTableName ) ;
192+ if ( includeDistance )
193+ {
194+ builder . AppendLine ( $ ", \" { vectorTableName } \" .\" { DistancePropertyName } \" ") ;
195+ }
196+ builder . AppendLine ( $ "FROM \" { vectorTableName } \" ") ;
197+ builder . AppendLine ( $ "LEFT JOIN \" { dataTableName } \" ON \" { vectorTableName } \" .\" { joinColumnName } \" = \" { dataTableName } \" .\" { joinColumnName } \" ") ;
198+ builder . AppendWhereClause ( whereClause ) ;
199+
200+ if ( filterOptions is not null )
201+ {
202+ builder . AppendOrderBy ( model , filterOptions , dataTableName ) ;
203+ }
204+ else if ( includeDistance )
205+ {
206+ builder . AppendLine ( $ "ORDER BY \" { vectorTableName } \" .\" { DistancePropertyName } \" ") ;
207+ }
180208
181- AppendWhereClauseIfExists ( builder , whereClause ) ;
182- AppendOrderByIfExists ( builder , orderByPropertyName ) ;
209+ builder . AppendLimits ( top , skip ) ;
183210
184211 command . CommandText = builder . ToString ( ) ;
185212
186213 return command ;
187214 }
188215
189- internal static DbCommand BuildSelectWhereCommand < TRecord > (
190- VectorStoreRecordModel model ,
216+ public static DbCommand BuildDeleteCommand (
191217 SqliteConnection connection ,
192- int top ,
193- GetFilteredRecordOptions < TRecord > options ,
194- string table ,
195- IReadOnlyList < VectorStoreRecordPropertyModel > properties ,
196- string whereFilter ,
197- Dictionary < string , object > whereParameters )
218+ string tableName ,
219+ IReadOnlyList < SqliteWhereCondition > conditions )
198220 {
199- StringBuilder builder = new ( 200 ) ;
221+ var builder = new StringBuilder ( ) ;
200222
201- var ( command , whereClause ) = GetCommandWithWhereClause ( connection , Array . Empty < SqliteWhereCondition > ( ) , whereFilter , whereParameters ) ;
223+ var ( command , whereClause ) = GetCommandWithWhereClause ( connection , conditions ) ;
202224
203- builder . Append ( "SELECT " ) ;
225+ builder . AppendLine ( $ "DELETE FROM \" { tableName } \" ") ;
226+ builder . AppendWhereClause ( whereClause ) ;
227+
228+ command . CommandText = builder . ToString ( ) ;
229+
230+ return command ;
231+ }
232+
233+ #region private
234+
235+ private static StringBuilder AppendColumnNames ( this StringBuilder builder , bool includeVectors , IReadOnlyList < VectorStoreRecordPropertyModel > properties ,
236+ string ? escapedVectorTableName = null , string ? escapedDataTableName = null )
237+ {
204238 foreach ( var property in properties )
205239 {
206- if ( options . IncludeVectors || property is not VectorStoreRecordVectorPropertyModel )
240+ string ? tableName = escapedDataTableName ;
241+ if ( property is VectorStoreRecordVectorPropertyModel )
242+ {
243+ if ( ! includeVectors )
244+ {
245+ continue ;
246+ }
247+ tableName = escapedVectorTableName ;
248+ }
249+
250+ if ( tableName is not null )
251+ {
252+ builder . AppendFormat ( "\" {0}\" .\" {1}\" ," , tableName , property . StorageName ) ;
253+ }
254+ else
207255 {
208256 builder . AppendFormat ( "\" {0}\" ," , property . StorageName ) ;
209257 }
210258 }
259+
211260 builder . Length -- ; // Remove the trailing comma
212261 builder . AppendLine ( ) ;
262+ return builder ;
263+ }
213264
214- builder . AppendFormat ( "FROM {0}" , table ) . AppendLine ( ) ;
215- builder . AppendFormat ( "WHERE {0}" , whereClause ) . AppendLine ( ) ;
216-
265+ private static StringBuilder AppendOrderBy < TRecord > ( this StringBuilder builder , VectorStoreRecordModel model ,
266+ GetFilteredRecordOptions < TRecord > options , string ? tableName = null )
267+ {
217268 if ( options . OrderBy . Values . Count > 0 )
218269 {
219270 builder . Append ( "ORDER BY " ) ;
220271
221272 foreach ( var sortInfo in options . OrderBy . Values )
222273 {
223- builder . AppendFormat ( "[{0}] {1}," ,
224- model . GetDataOrKeyProperty ( sortInfo . PropertySelector ) . StorageName ,
225- sortInfo . Ascending ? "ASC" : "DESC" ) ;
274+ var storageName = model . GetDataOrKeyProperty ( sortInfo . PropertySelector ) . StorageName ;
275+
276+ if ( tableName is not null )
277+ {
278+ builder . AppendFormat ( "\" {0}\" ." , tableName ) ;
279+ }
280+
281+ builder . AppendFormat ( "\" {0}\" {1}," , storageName , sortInfo . Ascending ? "ASC" : "DESC" ) ;
226282 }
227283
228284 builder . Length -- ; // remove the last comma
229285 builder . AppendLine ( ) ;
230286 }
231287
232- builder . AppendFormat ( "LIMIT {0}" , top ) . AppendLine ( ) ;
233- builder . AppendFormat ( "OFFSET {0}" , options . Skip ) . AppendLine ( ) ;
234-
235- command . CommandText = builder . ToString ( ) ;
236-
237- return command ;
288+ return builder ;
238289 }
239290
240- public static DbCommand BuildDeleteCommand (
241- SqliteConnection connection ,
242- string tableName ,
243- List < SqliteWhereCondition > conditions )
291+ private static StringBuilder AppendLimits ( this StringBuilder builder , int top , int skip )
244292 {
245- var builder = new StringBuilder ( ) ;
246-
247- var ( command , whereClause ) = GetCommandWithWhereClause ( connection , conditions ) ;
248-
249- builder . AppendLine ( $ "DELETE FROM [{ tableName } ]") ;
250-
251- AppendWhereClauseIfExists ( builder , whereClause ) ;
293+ if ( top > 0 )
294+ {
295+ builder . AppendFormat ( "LIMIT {0}" , top ) . AppendLine ( ) ;
296+ }
252297
253- command . CommandText = builder . ToString ( ) ;
298+ if ( skip > 0 )
299+ {
300+ builder . AppendFormat ( "OFFSET {0}" , skip ) . AppendLine ( ) ;
301+ }
254302
255- return command ;
303+ return builder ;
256304 }
257305
258- #region private
259-
260- private static void AppendWhereClauseIfExists ( StringBuilder builder , string ? whereClause )
306+ private static StringBuilder AppendWhereClause ( this StringBuilder builder , string ? whereClause )
261307 {
262308 if ( ! string . IsNullOrWhiteSpace ( whereClause ) )
263309 {
264310 builder . AppendLine ( $ "WHERE { whereClause } ") ;
265311 }
266- }
267312
268- private static void AppendOrderByIfExists ( StringBuilder builder , string ? propertyName )
269- {
270- if ( ! string . IsNullOrWhiteSpace ( propertyName ) )
271- {
272- builder . AppendLine ( $ "ORDER BY { propertyName } ") ;
273- }
313+ return builder ;
274314 }
275315
276- private static string GetColumnDefinition ( SqliteColumn column )
316+ private static string GetColumnDefinition ( SqliteColumn column , bool quote )
277317 {
278318 const string PrimaryKeyIdentifier = "PRIMARY KEY" ;
279319
280- List < string > columnDefinitionParts = [ column . Name , column . Type ] ;
320+ List < string > columnDefinitionParts = [ quote ? $ " \" { column . Name } \" " : column . Name , column . Type ] ;
281321
282322 if ( column . IsPrimary )
283323 {
@@ -342,19 +382,24 @@ private static (DbCommand Command, string WhereClause) GetCommandWithWhereClause
342382 }
343383
344384 private static ( List < string > Columns , List < string > ParameterNames , List < object ? > ParameterValues ) GetQueryParts (
345- IReadOnlyList < string > propertyNames ,
385+ IReadOnlyList < VectorStoreRecordPropertyModel > properties ,
346386 Dictionary < string , object ? > record ,
347- int index )
387+ int index ,
388+ bool data )
348389 {
349390 var columns = new List < string > ( ) ;
350391 var parameterNames = new List < string > ( ) ;
351392 var parameterValues = new List < object ? > ( ) ;
352393
353- foreach ( var propertyName in propertyNames )
394+ foreach ( var property in properties )
354395 {
355- if ( record . TryGetValue ( propertyName , out var value ) )
396+ bool include = property is VectorStoreRecordKeyPropertyModel // The Key column is included in both Vector and Data tables.
397+ || ( data == property is VectorStoreRecordDataPropertyModel ) ; // The Data column is included only in the Data table.
398+
399+ string propertyName = property . StorageName ;
400+ if ( include && record . TryGetValue ( propertyName , out var value ) )
356401 {
357- columns . Add ( propertyName ) ;
402+ columns . Add ( $ " \" { propertyName } \" " ) ;
358403 parameterNames . Add ( GetParameterName ( propertyName , index ) ) ;
359404 parameterValues . Add ( value ?? DBNull . Value ) ;
360405 }
0 commit comments