Zenn
Open4

「sqflite」の勉強スレ

Flutter
SQL
Dart
Ryo24Ryo24

https://github.com/tekartik/sqflite/blob/master/sqflite/doc/opening_db.md

DBのパス取得

iOS&Androidのローカルパスを取得するなら、getDatabasesPath でOK!

DBのパス取得
var databasesPath = await getDatabasesPath();
var path = join(databasesPath, dbName);

// Make sure the directory exists
try {
  await Directory(databasesPath).create(recursive: true);
} catch (_) {}

DBを開く

SQLiteのDBは、ファイルの一種類。
getDatabasesPath ()は相対パスを取得でき、

  • iOS : Documetディレクトリ
  • Android : Defaultディレクトリ
データベースを開く
var db = await openDatabase('my_db.db');

読み-書き

構成

onConfigure は、最初に呼び出すオプションのコールバック。
カスケードの削除など、DBの初期化を実行する。

onConfigure
_onConfigure(Database db) async {
  // Add support for cascade delete
  await db.execute("PRAGMA foreign_keys = ON");
}

var db = await openDatabase(path, onConfigure: _onConfigure);

データのプリロード

初回起動時にDBをプリロードするべき。

  • 既存のSQLiteをインポートし、データが存在するかチェック
  • onCreate時にデータを入れる
_onCreate(Database db, int version) async {
  // Database is created, create the table
  await db.execute(
    "CREATE TABLE Test (id INTEGER PRIMARY KEY, value TEXT)");
  // populate data
  await db.insert(...);
}

// Open the database, specifying a version and an onCreate callback
var db = await openDatabase(path,
    version: 1,
    onCreate: _onCreate);

マイグレーション

バージョン管理としてgetVersion とsetVersion があるが、DBのスキーマを変更などは開く時にマイグレーションを実行しなければならない。

  • onCreate : DBが存在しない場合、呼び出される。
  • onUpgrade : onCreateが定義されていない場合、oldVersion の値が0のため代わりに呼び出される。また新しいバージョンが現在のバージョンより高い場合、呼び出される。
  • onDowngrade : 新しいバージョンが現在のバージョンより低い場合、呼び出される。DBのバージョンを常にインクリメントすることで、回避するようにする。

これら3つのコールバックは、DBのバージョンが設定される直前、トランザクション内で呼び出される。

_onCreate(Database db, int version) async {
  // Database is created, create the table
  await db.execute(
    "CREATE TABLE Test (id INTEGER PRIMARY KEY, value TEXT)");
}

_onUpgrade(Database db, int oldVersion, int newVersion) async {
  // Database version is updated, alter the table
  await db.execute("ALTER TABLE Test ADD name TEXT");
}

// Special callback used for onDowngrade here to recreate the database
var db = await openDatabase(path,
  version: 1,
  onCreate: _onCreate,
  onUpgrade: _onUpgrade,
  onDowngrade: onDatabaseDowngradeDelete);

ダウングレード

onDowngradeでonDatabaseDowngradeDelete を使う。これは、DBを削除し、onCreate`を呼び出してDBを作成する。

開いた後のコールバック

onOpenはDBのバージョン設定後、openDatabase が呼び出される前に実行する。

onOpen
_onOpen(Database db) async {
  // Database is open, print its version
  print('db version ${await db.getVersion()}');
}

var db = await openDatabase(
  path,
  onOpen: _onOpen,
);

読み取り専用で開く

openReadOnlyDatabase
// open the database in read-only mode
var db = await openReadOnlyDatabase(path);

破損時の処理

iOSとAndroidでは、破損の扱いが異なり、既存の挙動を崩さずに整合性を取る方法は確立していない。

  • iOS : DBへの最初のアクセスで失敗
  • Android : 既存のファイルが削除

DBが有効なのか、読み取り専用で開いてバージョンを確認する方法がある。これをトップレベルの関数にする前に、動作を検証するためにもっと多くのテストが必要である。

DBが有効化確認する
/// Check if a file is a valid database file
///
/// An empty file is a valid empty sqlite file
Future<bool> isDatabase(String path) async {
  Database db;
  bool isDatabase = false;
  try {
    db = await openReadOnlyDatabase(path);
    int version = await db.getVersion();
    if (version != null) {
      isDatabase = true;
    }
  } catch (_) {} finally {
    await db?.close();
  }
  return isDatabase;
}

DBのロック問題を予防する

  • DBを開くのは1度だけにする！
  • デフォルトでは、単一のインスタンスとしてひらかれる（singleInstance: true）。
  • singleInstance: falseで同じデータベースを何度も開くと、（最低でもAndroidでは）問題が発生する可能性がある。
Androidのエラー文
android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5)

ヘルパークラスを実装する

複数回呼ばれたら、何度も開く
class Helper {
  final String path;
  Helper(this.path);
  Database _db;

  Future<Database> getDb() async {
    if (_db == null) {
      _db = await openDatabase(path);
    }
    return _db;
  }
}

この実装だとgetDb()を複数回呼び出すことができ、非同期処理のためレースコンディションが起きる可能性を秘めている。そのため、次の方法で修正する。

複数回呼ばれた場合でも、一度しか開かない
class Helper {
  final String path;
  Helper(this.path);
  Future<Database> _db;

  Future<Database> getDb() {
    _db ??= _initDb();
    return _db;
  }

  // Guaranteed to be called only once.
  Future<Database> _initDb() async {
    final db = await openDatabase(this.path);
    // do "tons of stuff in async mode"
    return db;
  }
}

同期処理で実装し、??= で代入先の変数がnullの場合のみ代入する。これにより、同時アクセスを防ぎ、初期化時(null)のみDBを開くようにする。（サンプルコードは Future<Database> _db;になっているが、 Future<Database>? _db;の方が良いと思う）

例外の解決

例外が発生した場合

  • トラブルシューティングを確認
  • DB作成用のディレクトリが存在することを確認
  • パスがファイルを指しているか確認
  • open callbacks (onCreate/onUpgrade/onConfigure/onOpen)で予期される例外を処理する
Ryo24Ryo24

https://github.com/tekartik/sqflite/blob/master/sqflite/doc/migration_example.md

マイグレーションの例

DBのスキーマ以降の例

  • 既存のテーブルにカラムを追加
  • テーブルを追加

1st version

最初はCompanyテーブルとnameカラムを作成する。

/// Create tables
void _createTableCompanyV1(Batch batch) {
  batch.execute('DROP TABLE IF EXISTS Company');
  batch.execute('''CREATE TABLE Company (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
)''');
}

// First version of the database
db = await factory.openDatabase(path,
    options: OpenDatabaseOptions(
        version: 1,
        onCreate: (db, version) async {
          var batch = db.batch();
          _createTableCompanyV1(batch);
          await batch.commit();
        },
        onDowngrade: onDatabaseDowngradeDelete));

2nd version

例えば、Companyエンティティを参照する新しいEmployee テーブルを追加したいとする。また、Company エンティティに新しくDescription を追加する。
新しいDBの作成はonCreateで処理し、スキーマのマイグレートはonUpgrade で処理する。また、外部キーの制約をしたいので、onConfigureでアクセスを設定する。

/// Let's use FOREIGN KEY constraints
Future onConfigure(Database db) async {
  await db.execute('PRAGMA foreign_keys = ON');
}

/// Create Company table V2
void _createTableCompanyV2(Batch batch) {
  batch.execute('DROP TABLE IF EXISTS Company');
  batch.execute('''CREATE TABLE Company (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    description TEXT
)''');
}

/// Update Company table V1 to V2
void _updateTableCompanyV1toV2(Batch batch) {
  batch.execute('ALTER TABLE Company ADD description TEXT');
}

/// Create Employee table V2
void _createTableEmployeeV2(Batch batch) {
  batch.execute('DROP TABLE IF EXISTS Employee');
  batch.execute('''CREATE TABLE Employee (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    companyId INTEGER,
    FOREIGN KEY (companyId) REFERENCES Company(id) ON DELETE CASCADE
)''');
}

// 2nd version of the database
db = await factory.openDatabase(path,
    options: OpenDatabaseOptions(
        version: 2,
        onConfigure: onConfigure,
        onCreate: (db, version) async {
          var batch = db.batch();
          // We create all the tables
          _createTableCompanyV2(batch);
          _createTableEmployeeV2(batch);
          await batch.commit();
        },
        onUpgrade: (db, oldVersion, newVersion) async {
          var batch = db.batch();
          if (oldVersion == 1) {
            // We update existing table and create the new tables
            _updateTableCompanyV1toV2(batch);
            _createTableEmployeeV2(batch);
          }
          await batch.commit();
        },
        onDowngrade: onDatabaseDowngradeDelete));
Ryo24Ryo24

Opening a databaseで出たメソッド調べ

getDatabasesPath function

デフォルトのDBのパスを取得。

  • Android : data/data/
  • iOS and MacOS : Document

iOSで使う場合、path_providerでパスを取得推奨。

join function

複数のパスを結合し、一つのパスにする。

Directory class

dart:ioに含まれ、ファイルシステム上のディレクトリを参照する。

create method

ディレクトリが存在しない場合、作成する(既に存在する場合、何もしない)。

  • recursive = false : パスの最後のディレクトリのみが作成
  • recursive = true : 存在しないすべてのパスコンポーネントを作成

openDatabase function

指定されたパスのDBを開く。

  1. onConfigure
  2. onCreate or onUpgrade or onDowngrade
  3. onOpen

この順序でオプションのコールバックが実行される。
onCreate, onUpgrade, onDowngradeは排他的であり、コンテキストに応じて1つしか実行しないが、複数のシナリオをカバーするためにすべて指定することができる(= できる限り指定する)。

openDatabase
Future<Database> openDatabase(
String path,
{int? version,  // 開くDBのスキーマのバージョンを指定
OnDatabaseConfigureFn? onConfigure,  // 外部キーの参照やログの書き込みなど初期化処理を実行
OnDatabaseCreateFn? onCreate,  // openDatabaseを呼び出し前に実行され、DBが存在しない時に実行しDBを作成可能
OnDatabaseVersionChangeFn? onUpgrade,  // versionが以前より高い or onCreateが定義されずDBが存在しない時にonCreateを実行
OnDatabaseVersionChangeFn? onDowngrade,  // 以前のversionが低い時に呼び出される(このシナリオは避けるべき)
OnDatabaseOpenFn? onOpen,  // 最後に呼び出されるコールバック(version設定後、呼び戻る前に実行)
bool readOnly = false,  // trueの場合、他のパラメータは全て無視され、そのまま開く
bool singleInstance = true  // パスに対し、1つのDBを返す
})

version

DBのスキーマのバージョンを指す。
こいつを指定しないとonCreate, onUpgrade, onDowngradeを呼び出せない。

onCreate

openDatabaseを呼び出す前にDBが存在しない場合に呼び出される。
スキーマに従ってDBに必要なテーブルを作成可能。

onUpgrade

  • onCreateが指定されていない
  • DBが既に存在し、versionが前回より高い

以上、いずれかの条件を満たした場合に呼び出す。
onCreateが指定されず、DBを作成する時はoldVersionパラメータを0として実行する。

onDowngrade

versionが前回より低い場合に実行。
これは稀なケースで、新しいversionのコードがDBを作成し、古いversionのコードがそれを操作する場合にのみ実行する。
できる限り、このシナリオは避けて実装する。

onOpen

最後に呼び出されるオプションのコールバック。
DBのversionが設定後、openDatabaseが戻る前に実行される。

readOnly

デフォルトはfalse 。
trueの場合、他のパラメータはすべて無視され、DBはそのまま開く。

singleInstance

デフォルトはtrue 。
与えられたパスに対し、1つのDBインスタンスが返される。その後、同じパスでopenDatabaseを呼び出すと同じインスタンスが返され、その呼び出しに対応するコールバックなどの他の全てのパラメータは破棄される。

execute method

戻り値なしでSQLクエリを実行

insert method

指定したtableにvalues(Map)を挿入し、最後に行のidを返す

openReadOnlyDatabase function

読み取り専用で指定したパスのDBを開く

getVersion method

DBの内部versionを取得

setVersion method

DBの内部バージョンを設定する オープンヘルパーと自動バージョニングのために内部で使用される

onDatabaseDowngradeDelete top-level property

DBが削除され、再度開くことができなくなる。
ダウングレード時に全て削除したいあ場合、onDowngradeで設定する。

Ryo24Ryo24

https://github.com/tekartik/sqflite/blob/master/sqflite/doc/sql.md

SQL

sqfliteはSQLコマンドを実行せず、ネイティブのやつと使い方と類似している(= fmdbとsqliteをラップしているだけ)。

基本的な使い方

execute

executeは、返り値のないコマンド

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

insert

insertは、デーブルにデータを挿入し、レコードの内部idを返すコマンド

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

コンフリクト時のドキュメント

query

queryは、テーブルの内容を読み取り、Mapで返すコマンド

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

結果は読み取り専用のため、項目を変更する場合はクローンを作成しなければならない。

クローンを作成し、変更する
list = List.from(list)
list.add(<String, Object?>{'name': 'some data'});

map = Map.from(map);
map['name'] = 'other';

delete

delete は、テーブルの内容を削除し、行の数を返すコマンド

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

update

updateは、テーブルの内容を更新し、行の数を返すコマンド

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

コンフリクト時のドキュメント

transaction

transactionは、all or nothingシナリオを処理するコマンド。
1つでもコマンドがエラーを吐くと、他の全てのコマンドが元に戻される。

transaction
await db.transaction((txn) async {
  await txn.insert('my_table', {'name': 'my_name'});
  await txn.delete('my_table', where: 'name = ?', whereArgs: ['cat']);
});

注意点

  • 内側のトランザクションオブジェクト(上のコードだとtxn)がトランザクションで使用されていることを確認する(DBオブジェクトを直接使用するとデッドロックが発生する)
  • トランザクション中にエラーを投げると、処理をキャンセルすることができる
  • トランザクション中にエラーを吐くと、アクションはキャンセルされ、トランザクション内の以前のコマンドは元に戻される。
  • トランザクション中は、DBに対する他の同時変更は起こらない
  • トランザクションの内部は一度だけ呼び出され、try-againを処理するかどうかは開発者次第で調整可能

Parameters

純粋なSQL文を実行する場合、いかなる値もsanitizeしてはいけない。その代わり、標準的なSQLiteのバインディング構文を使用する。

// 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)");

?(ブレースホルダ)

*?*は、ブレースホルダとしてSQLiteに認識される。
*?*の数は引数の数と同じではないといけず、引数はサポートされている型ではないといけない。

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

リストの要素は変わる可能性があるため、適切な数を用意する必要がる。

要素分の?を準備
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);

パラメータの位置

?NNNを使用し、パラメータの位置を指定することが可能

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

Androidでは、引数をStringとして結合することに注意してください。ほとんどの場合（where argsで）うまくいきますが、上記の例では、結果は[{'item1': '3', 'item2': '4', 'sum': 7}])となります。将来的には num のインライン化も検討するかもしれません。

NULLの値

NULLは特殊な値です。クエリでNULLをテストする場合、``WHERE my_col = ?', [NULL]ではなく、WHERE my_col IS NULLまたはWHERE my_col IS NOT NULL` を使用する必要がある。

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

活用例

LIKEを使う

nameがTaで始まるものを探す

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

nameにfreeが含まれるものを探す

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

SQLiteのスキーマの情報

SQLiteは、スキーマの情報を格納する[sqlite_master](https://www.sqlite.org/faq.html#q7)テーブルを持っている。

テーブルが存在するかどうかを確認する

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;
}

テーブル名一覧

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;
}