Skip to content

Files

Latest commit

Sep 20, 2023
dc634cb · Sep 20, 2023

History

History
231 lines (170 loc) · 6.79 KB

sql.md

File metadata and controls

231 lines (170 loc) · 6.79 KB

SQL

As sqflite does not do any parsing of SQL commands, its usage is similar to the usage on the native iOS and Android platform so you can refer to their respective documentation as well as the generic sqlite documentation:

The API is relatively close to the Android one. For performance and compatibility reason, cursors are not supported at this time.

It is impossible here to make a full documentation of SQL. Only basic information is given and common pitfalls.

Basic usage

execute

execute is for commands without return values.

// Create a table
await db.execute('CREATE TABLE my_table (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, type TEXT)');

insert

insert is for inserting data into a table. It returns the internal id of the record (an integer).

int recordId = await db.insert('my_table', {'name': 'my_name', 'type': 'my_type'});

See Conflict algorithm for conflict handling.

query

query is for reading a table content. It returns a list of map.

var list = await db.query('my_table', columns: ['name', 'type']);

The list is read-only. If you want to modify the results by adding/deleting items in memory, you need to clone the list:

// This throws an error
list.add(<String, Object?>{'name': 'some data'});
// This works
list = List.from(list)
list.add(<String, Object?>{'name': 'some data'});

Each item (map) of the list is read-only too so you need to clone it if you want to modify the result.

map = list.first;
// This crashes
map['name'] = 'other';
// This works
map = Map.from(map);
map['name'] = 'other';

Query by page

If you perform a query on a huge table, you might want to avoid allocating all the rows at once. There is a basic cursor support where you can specify the buffer size (number of rows cached using a look-ahead buffer)

// Query cursor
var cursor = await db.queryCursor(
  'Product',
  bufferSize: 10,
);
try {
  while (await cursor.moveNext()) {
    var row = cursor.current;
    // ...
  }
} finally {
  // Important don't forget to close the cursor in case any exception is thrown before
  await cursor.close();
}

delete

delete is for deleting content in a table. It returns the number of rows deleted.

var count = await db.delete('my_table', where: 'name = ?', whereArgs: ['cat']);

update

update is for updating content in a table. It returns the number of rows updated.

var count = await db.update('my_table', {'name': 'new cat name'}, where: 'name = ?', whereArgs: ['cat']);

See Conflict algorithm for conflict handling.

transaction

transaction handle the 'all or nothing' scenario. If one command fails (and throws an error), all other commands are reverted.

await db.transaction((txn) async {
  await txn.insert('my_table', {'name': 'my_name'});
  await txn.delete('my_table', where: 'name = ?', whereArgs: ['cat']);
});
  • Make sure to use the inner transaction object - txn in the code above - is used in a transaction (using the db object itself will cause a deadlock),
  • You can throw an error during a transaction to cancel a transaction,
  • When an error is thrown during a transaction, the action is cancelled right away and previous commands in the transaction are reverted,
  • No other concurrent modification on the database (even from an outside process) can happen during a transaction,
  • The inner part of the transaction is called only once, it is up to the developer to handle a try-again loop - assuming it can succeed at some point.

Parameters

When providing a raw SQL statement, you should not attempt to "sanitize" any values. Instead, you should use the standard SQLite binding syntax:

// good
int recordId = await db.rawInsert('INSERT INTO my_table(name, year) VALUES (?, ?)', ['my_name', 2019]);
// bad
int recordId = await db.rawInsert("INSERT INTO my_table(name, year) VALUES ('my_name', 2019)");

The ? character is recognized by SQLite as a placeholder for a value to be inserted.

The number of ? characters must match the number of arguments. Arguments types must be in the list of supported types.

Particulary, lists (expect for blob content) are not supported. A common mistake is to expect to use IN (?) and give a list of values. This does not work. Instead you should list each argument one by one:

var list = await db.rawQuery('SELECT * FROM my_table WHERE name IN (?, ?, ?)', ['cat', 'dog', 'fish']);

Since the list size can change, having the proper number or ? can be solved using the following solution:

List.filled(inArgsCount, '?').join(',')
var inArgs = ['cat', 'dog', 'fish'];
var list = await db.query('my_table',
  where: 'name IN (${List.filled(inArgs.length, '?').join(',')})',
  whereArgs: inArgs);

Parameter position

You can use ?NNN to specify a parameter position:

expect(
  await db.rawQuery(
    'SELECT ?1 as item1, ?2 as item2, ?1 + ?2 as sum', [3, 4]),
    [{'item1': 3, 'item2': 4, 'sum': 7}]);

Be aware that Android binds argument as String. While it works in most cases (in where args), in the example above, the result will be [{'item1': '3', 'item2': '4', 'sum': 7}]);. We might consider inlining num in the future.

NULL value

NULL is a special value. When testing for null in a query you should not do 'WHERE my_col = ?', [null] but use instead WHERE my_col IS NULL or WHERE my_col IS NOT NULL.

var list = await db.query('my_table', columns: ['name'], where: 'type IS NULL');

Examples

Using LIKE

Look for items with name starting with 'Ta':

var list = await db.query('my_table', columns: ['name'], where: 'name LIKE ?', whereArgs: ['Ta%']);

Look for items with name containing with 'free':

var list = await db.query('my_table', columns: ['name'], where: 'name LIKE ?', whereArgs: ['%free%']);

SQLite schema information

SQLite has a sqlite_master table that store schema information:

Check if a table exists

Future<bool> tableExists(DatabaseExecutor db, String table) async {
  var count = firstIntValue(await db.query('sqlite_master',
      columns: ['COUNT(*)'],
      where: 'type = ? AND name = ?',
      whereArgs: ['table', table]));
  return count > 0;
}

List table names

Future<List<String>> getTableNames(DatabaseExecutor db) async {
  var tableNames = (await db
          .query('sqlite_master', where: 'type = ?', whereArgs: ['table']))
      .map((row) => row['name'] as String)
      .toList(growable: false)
        ..sort();
  return tableNames;
}