sqflite 1.3.1

  • Readme
  • Changelog
  • Example
  • Installing
  • 100

sqflite #

pub package Build Status codecov

Flutter的 SQLite插件. 支持iOS,Android和MacOS.

  • 支持交易和批次
  • 打开期间自动版本管理
  • 插入/查询/更新/删除查询的助手
  • 在iOS和Android的后台线程中执行的数据库操作

Web is not supported.

Getting Started #

在flutter项目中添加依赖项:

dependencies:
  ...
  sqflite: ^1.3.0

要获得Flutter入门方面的帮助,请查看在线文档 .

Usage example #

Import sqflite.dart

import 'package:sqflite/sqflite.dart';

Opening a database #

SQLite数据库是文件系统中由路径标识的文件. 如果是相对路径,则该路径相对于getDatabasesPath()获得的路径,该路径是Android上的默认数据库目录和iOS上的documents目录.

var db = await openDatabase('my_db.db');

有一个基本的迁移机制可以处理打开期间的架构更改.

许多应用程序使用一个数据库,并且永远不需要关闭它(当应用程序终止时,它将关闭). 如果要释放资源,可以关闭数据库.

await db.close();

Raw SQL queries #

演示代码以执行Raw SQL查询

// Get a location using getDatabasesPath
var databasesPath = await getDatabasesPath();
String path = join(databasesPath, 'demo.db');

// Delete the database
await deleteDatabase(path);

// open the database
Database database = await openDatabase(path, version: 1,
    onCreate: (Database db, int version) async {
  // When creating the db, create the table
  await db.execute(
      'CREATE TABLE Test (id INTEGER PRIMARY KEY, name TEXT, value INTEGER, num REAL)');
});

// Insert some records in a transaction
await database.transaction((txn) async {
  int id1 = await txn.rawInsert(
      'INSERT INTO Test(name, value, num) VALUES("some name", 1234, 456.789)');
  print('inserted1: $id1');
  int id2 = await txn.rawInsert(
      'INSERT INTO Test(name, value, num) VALUES(?, ?, ?)',
      ['another name', 12345678, 3.1416]);
  print('inserted2: $id2');
});

// Update some record
int count = await database.rawUpdate(
    'UPDATE Test SET name = ?, value = ? WHERE name = ?',
    ['updated name', '9876', 'some name']);
print('updated: $count');

// Get the records
List<Map> list = await database.rawQuery('SELECT * FROM Test');
List<Map> expectedList = [
  {'name': 'updated name', 'id': 1, 'value': 9876, 'num': 456.789},
  {'name': 'another name', 'id': 2, 'value': 12345678, 'num': 3.1416}
];
print(list);
print(expectedList);
assert(const DeepCollectionEquality().equals(list, expectedList));

// Count the records
count = Sqflite
    .firstIntValue(await database.rawQuery('SELECT COUNT(*) FROM Test'));
assert(count == 2);

// Delete a record
count = await database
    .rawDelete('DELETE FROM Test WHERE name = ?', ['another name']);
assert(count == 1);

// Close the database
await database.close();

对SQL的基本信息在这里 .

SQL helpers #

使用助手的示例

final String tableTodo = 'todo';
final String columnId = '_id';
final String columnTitle = 'title';
final String columnDone = 'done';

class Todo {
  int id;
  String title;
  bool done;

  Map<String, dynamic> toMap() {
    var map = <String, dynamic>{
      columnTitle: title,
      columnDone: done == true ? 1 : 0
    };
    if (id != null) {
      map[columnId] = id;
    }
    return map;
  }

  Todo();

  Todo.fromMap(Map<String, dynamic> map) {
    id = map[columnId];
    title = map[columnTitle];
    done = map[columnDone] == 1;
  }
}

class TodoProvider {
  Database db;

  Future open(String path) async {
    db = await openDatabase(path, version: 1,
        onCreate: (Database db, int version) async {
      await db.execute('''
create table $tableTodo ( 
  $columnId integer primary key autoincrement, 
  $columnTitle text not null,
  $columnDone integer not null)
''');
    });
  }

  Future<Todo> insert(Todo todo) async {
    todo.id = await db.insert(tableTodo, todo.toMap());
    return todo;
  }

  Future<Todo> getTodo(int id) async {
    List<Map> maps = await db.query(tableTodo,
        columns: [columnId, columnDone, columnTitle],
        where: '$columnId = ?',
        whereArgs: [id]);
    if (maps.length > 0) {
      return Todo.fromMap(maps.first);
    }
    return null;
  }

  Future<int> delete(int id) async {
    return await db.delete(tableTodo, where: '$columnId = ?', whereArgs: [id]);
  }

  Future<int> update(Todo todo) async {
    return await db.update(tableTodo, todo.toMap(),
        where: '$columnId = ?', whereArgs: [todo.id]);
  }

  Future close() async => db.close();
}

Read results #

假设有以下读取结果:

List<Map<String, dynamic>> records = await db.query('my_table');

生成的地图项为只读

// get the first record
Map<String, dynamic> mapRead = records.first;
// Update it in memory...this will throw an exception
mapRead['my_column'] = 1;
// Crash... `mapRead` is read-only

如果要在内存中进行修改,则需要创建一个新地图:

// get the first record
Map<String, dynamic> map = Map<String, dynamic>.from(mapRead);
// Update it in memory now
map['my_column'] = 1;

Transaction #

不使用数据库,而仅在事务中使用Transaction对象访问数据库

await database.transaction((txn) async {
  // Ok
  await txn.execute('CREATE TABLE Test1 (id INTEGER PRIMARY KEY)');
  
  // DON'T  use the database object in a transaction
  // this will deadlock!
  await database.execute('CREATE TABLE Test2 (id INTEGER PRIMARY KEY)');
});

如果回调不引发错误,则提交事务. 如果抛出错误,则交易被取消. 因此,回滚事务的一种方法是引发异常.

Batch support #

为了避免在Dart和本机代码之间进行乒乓球,可以使用Batch

batch = db.batch();
batch.insert('Test', {'name': 'item'});
batch.update('Test', {'name': 'new_item'}, where: 'name = ?', whereArgs: ['item']);
batch.delete('Test', where: 'name = ?', whereArgs: ['item']);
results = await batch.commit();

获得每个操作的结果是有成本的(插入的ID以及更新和删除的更改数),尤其是在执行额外SQL请求的Android上. 如果您不关心结果而担心大批量的性能,则可以使用

await batch.commit(noResult: true);

警告,在事务处理期间,直到事务提交后,才会提交批处理

await database.transaction((txn) async {
  var batch = txn.batch();
  
  // ...
  
  // commit but the actual commit will happen when the transaction is committed
  // however the data is available in this transaction
  await batch.commit();
  
  //  ...
});

默认情况下,批处理在遇到错误后立即停止(通常会还原未提交的更改). 您可以忽略错误,以便即使一个操作失败也可以运行并落实每个成功的操作:

await batch.commit(continueOnError: true);

Table and column names #

通常,最好避免对实体名称使用SQLite关键字. 如果使用以下任何名称:

"add","all","alter","and","as","autoincrement","between","case","check","collate","commit","constraint","create","default","deferrable","delete","distinct","drop","else","escape","except","exists","foreign","from","group","having","if","in","index","insert","intersect","into","is","isnull","join","limit","not","notnull","null","on","or","order","primary","references","select","set","table","then","to","transaction","union","unique","update","using","values","when","where"

帮手将逃脱名字,即

db.query('table')

将等同于在表名周围手动添加双引号(在此处令人困惑地命名为table

db.rawQuery('SELECT * FROM "table"');

However in any other raw statement (including orderBy, where, groupBy), make sure to escape the name properly using double quote. For example see below where the column name group is not escaped in the columns argument, but is escaped in the where argument.

db.query('table', columns: ['group'], where: '"group" = ?', whereArgs: ['my_group']);

Supported SQLite types #

尚未对值进行有效性检查,因此请避免使用不受支持的类型https://www.sqlite.org/datatype3.html

DateTime不是受支持的SQLite类型. 我个人将它们存储为int(millisSinceEpoch)或字符串(iso8601)

bool不是受支持的SQLite类型. 使用INTEGER以及0和1值.

有关支持的类型的更多信息,请参见此处 .

INTEGER #

  • 飞镖类型: int
  • 支持的值:-2 ^ 63到2 ^ 63-1

REAL #

  • 飞镖类型: num

TEXT #

  • 飞镖类型: String

BLOB #

  • 飞镖类型: Uint8List

Current issues #

  • 由于事务在SQLite(线程)中的工作方式,因此不支持并发读写事务. 当前所有呼叫均已同步,并且交易冻结是互斥的. 我认为支持并发访问的一种基本方法是多次打开数据库,但是它只能在iOS上使用,因为Android重用了相同的数据库对象. 我还认为本机线程可能是将来的潜在解决方案,但是在android中,在事务中访问另一个线程中的数据库时会被阻止...
  • 目前,INTEGER限制为-2 ^ 63至2 ^ 63-1(尽管Android支持更大的整数)

More #

1.3.1 #

  • 添加databaseFactory设置器以更改默认的sqflite工厂.

1.3.0+2 #

  • 添加sqflite_common依赖项

1.2.2+1 #

  • 修复FMDB导入上的iOS警告
  • 支持pedantic 1.9
  • 在调试模式下检查参数(仅打印错误)

1.2.1 #

  • 支持Android嵌入v2
  • 添加私人混音
  • 支持iOS / MacOS增量构建

1.2.0 #

  • 添加MacOS支持

1.1.8 #

  • 热重启后支持deleteDatabase . 现有的单实例数据库在删除之前已关闭

1.1.7+3 #

  • 凹凸颤抖/飞镖依赖版本(1.9.1 / 2.5.0)
  • 修复Android上已打开的数据库的热启动和热启动
  • 添加代码文档,代码覆盖率和构建标志
  • 修复ios示例构建

1.1.6+5 #

  • 在Android上的后台线程中打开数据库.
  • 以只读方式打开损坏的数据库时,防止在Android上删除数据库.
  • Fix hot restart ROLLBACK warning
  • 修复iOS上的索引参数绑定

1.1.5 #

  • databaseExists添加为顶级功能
  • 处理databaseExistsdeleteDatabase相对路径
  • 通过从本机环境中恢复数据库并执行ROLLBACK来防止SQLITE_BUSY错误,从而支持在iOS和Android上进行事务处理时进行热重启
  • 如果在事务中,请在关闭之前执行ROLLBACK以防止SQLITE_BUSY错误

1.1.4 #

  • 使所有数据库操作在iOS上的单独线程中发生

1.1.3 #

  • 修复使用隔离时iOS上的死锁问题

1.1.2 #

  • 现在,Sqflite现在在Android上默认使用具有后台线程优先级的线程处理程序

1.1.1 #

  • 使用mixin并将非抖动代码提取到sqlite_api.dart
  • 弃用仅在内部使用的SqfliteOptions

1.1.0 #

  • 零钱 . 从已弃用的原始Android支持库迁移到AndroidX. 这不应该导致任何功能上的变化,但是如果使用原始插件的Android应用程序也需要迁移,则这些应用程序也必须进行迁移 .

    您可能会说,thay版本应提高到2.0.0,但这只是一个工具问题,代码未更改. 这是flutter插件中所做更改的副本

1.0.0 #

  • 将0.13.0版本升级为1.0.0
  • 删除不推荐使用的API(applyBatch,apply)

0.13.0 #

  • 为批处理添加对continueOrError支持

0.12.0 #

  • 添加iOS目标C前缀以防止冲突
  • 在iOS上,创建数据库的目录(如果不存在)

0.11.2 #

  • 添加Database.isOpen ,一旦关闭Database.isOpen ,它将变为false

0.11.1 #

  • 添加Sqlflite.hex以允许对Blob字段进行查询

0.11.0 #

  • 添加getDatabasesPath用作创建数据库的基本位置
  • 警告:默认情况下,数据库现在是单个实例(基于path ),要使用旧行为,请在打开数据库时使用singleInstance = false
  • dart2稳定支持

0.10.0 #

  • 准备1.0
  • 删除不推荐使用的方法(重入交易)
  • Add Transaction.batch
  • 向开发人员显示警告以防止死锁

0.9.0 #

  • 支持内存数据库( :memory: path)
  • 支持单实例
  • 新的数据库工厂,用于处理新选项

0.8.9 #

  • 升级到SDK 27

0.8.8 #

  • 允许测试约束异常

0.8.6 #

  • 更好的SQL错误报告
  • 捕获Android本机错误
  • 删除数据库失败时不再打印错误

0.8.4 #

  • 使用openReadOnlyDatabase添加只读支持

0.8.3 #

  • 允许使用Transaction.applyBatch在事务期间运行批处理
  • 恢复Batch.commit以在事务外部使用

0.8.2 #

  • 尽管已经在事务中,但允许在打开期间创建嵌套事务

0.8.1 #

  • 新的Transaction机制未使用区域(目前仍支持旧的机制)
  • 开始使用Batch.apply而不是Batch.commit
  • 弃用Database.inTransactionDatabase.synchronized以便不再使用区域

0.7.1 #

  • add Batch.query, Batch.rawQuery and Batch.execute
  • 将查询结果打包为列/行而不是列表

0.7.0 #

  • 添加对--preview-dart-2

0.6.2+1 #

  • 在pubspec.yaml中添加更长的描述

0.6.2 #

  • 修复travis警告

0.6.1 #

  • 将Flutter SDK约束添加到pubspec.yaml

0.6.0 #

  • 添加对onConfigure支持以允许数据库配置

0.5.0 #

  • 在插入/更新/查询/删除中需要时转义表和列名称
  • 在新的sql.dart中导出ConflictAlgorithm,escapeName,unescapeName

0.4.0 #

  • 添加对批处理的支持(插入/更新/删除)

0.3.1 #

  • 删除临时并发实验

0.3.0 #

2018/01/04

  • 零钱 . 升级到Gradle 4.1和Android Studio Gradle插件3.0.1. 较早的Flutter项目也需要升级其Gradle设置才能使用此版本的插件. 说明可以在这里找到.

0.2.4 #

  • 对同步的依赖关系更新为> = 1.1.0

0.2.3 #

  • 使Android在与调用者相同的线程中发送响应,以防止发生错误时的意外行为

0.2.2 #

  • 修复Android上未经检查的警告

0.2.0 #

  • 将NSOperationQueue用于iOS上的所有数据库操作
  • 将ThreadHandler用于Android上的所有数据库操作

0.0.3 #

  • 添加异常处理

0.0.2 #

  • 根据Razvan Lung的建议添加sqlite助手

0.0.1 #

  • 初步实验

example/lib/main.dart

import 'dart:async';

import 'package:flutter/material.dart';
import 'package:flutter/services.dart';
import 'package:sqflite/sqflite.dart';
import 'package:sqflite_example/batch_test_page.dart';
import 'package:sqflite_example/deprecated_test_page.dart';
import 'package:sqflite_example/exception_test_page.dart';
import 'package:sqflite_example/exp_test_page.dart';
import 'package:sqflite_example/manual_test_page.dart';
import 'package:sqflite_example/src/dev_utils.dart';

import 'model/main_item.dart';
import 'open_test_page.dart';
import 'raw_test_page.dart';
import 'slow_test_page.dart';
import 'src/main_item_widget.dart';
import 'todo_test_page.dart';
import 'type_test_page.dart';

void main() {
  runApp(MyApp());
}

/// Sqflite test app
class MyApp extends StatefulWidget {
  // This widget is the root of your application.

  @override
  _MyAppState createState() => _MyAppState();
}

/// Simple test page.
const String testRawRoute = '/test/simple';

/// Open test page.
const String testOpenRoute = '/test/open';

/// Slow test page.
const String testSlowRoute = '/test/slow';

/// Type test page.
const String testTypeRoute = '/test/type';

/// Batch test page.
const String testBatchRoute = '/test/batch';

/// `todo` example test page.
const String testTodoRoute = '/test/todo';

/// Exception test page.
const String testExceptionRoute = '/test/exception';

/// Manual test page.
const String testManualRoute = '/test/manual';

/// Experiment test page.
const String testExpRoute = '/test/exp';

/// Deprecated test page.
const String testDeprecatedRoute = '/test/deprecated';

class _MyAppState extends State<MyApp> {
  var routes = <String, WidgetBuilder>{
    '/test': (BuildContext context) => MyHomePage(),
    testRawRoute: (BuildContext context) => RawTestPage(),
    testOpenRoute: (BuildContext context) => OpenTestPage(),
    testSlowRoute: (BuildContext context) => SlowTestPage(),
    testTodoRoute: (BuildContext context) => TodoTestPage(),
    testTypeRoute: (BuildContext context) => TypeTestPage(),
    testManualRoute: (BuildContext context) => ManualTestPage(),
    testBatchRoute: (BuildContext context) => BatchTestPage(),
    testExceptionRoute: (BuildContext context) => ExceptionTestPage(),
    testExpRoute: (BuildContext context) => ExpTestPage(),
    testDeprecatedRoute: (BuildContext context) => DeprecatedTestPage(),
  };

  @override
  Widget build(BuildContext context) {
    return MaterialApp(
        title: 'Sqflite Demo',
        theme: ThemeData(
          // This is the theme of your application.
          //
          // Try running your application with 'flutter run'. You'll see
          // the application has a blue toolbar. Then, without quitting
          // the app, try changing the primarySwatch below to Colors.green
          // and then invoke 'hot reload' (press 'r' in the console where
          // you ran 'flutter run', or press Run > Hot Reload App in IntelliJ).
          // Notice that the counter didn't reset back to zero -- the application
          // is not restarted.
          primarySwatch: Colors.blue,
        ),
        home: MyHomePage(title: 'Sqflite Demo Home Page'),
        routes: routes);
  }
}

/// App home menu page.
class MyHomePage extends StatefulWidget {
  /// App home menu page.
  MyHomePage({Key key, this.title}) : super(key: key) {
    _items.add(
        MainItem('Raw tests', 'Raw SQLite operations', route: testRawRoute));
    _items.add(MainItem('Open tests', 'Open onCreate/onUpgrade/onDowngrade',
        route: testOpenRoute));
    _items
        .add(MainItem('Type tests', 'Test value types', route: testTypeRoute));
    _items.add(MainItem('Batch tests', 'Test batch operations',
        route: testBatchRoute));
    _items.add(
        MainItem('Slow tests', 'Lengthy operations', route: testSlowRoute));
    _items.add(MainItem(
        'Todo database example', 'Simple Todo-like database usage example',
        route: testTodoRoute));
    _items.add(MainItem('Exp tests', 'Experimental and various tests',
        route: testExpRoute));
    _items.add(MainItem('Exception tests', 'Tests that trigger exceptions',
        route: testExceptionRoute));
    _items.add(MainItem('Manual tests', 'Tests that requires manual execution',
        route: testManualRoute));
    _items.add(MainItem('Deprecated test',
        'Keeping some old tests for deprecated functionalities',
        route: testDeprecatedRoute));

    // Uncomment to view all logs
    //Sqflite.devSetDebugModeOn(true);
  }

  final List<MainItem> _items = [];

  /// Page title.
  final String title;

  @override
  _MyHomePageState createState() => _MyHomePageState();
}

String _debugAutoStartRouteName;

/// (debug) set the route to start with.
String get debugAutoStartRouteName => _debugAutoStartRouteName;

/// Deprecated to avoid calls
@deprecated
set debugAutoStartRouteName(String routeName) =>
    _debugAutoStartRouteName = routeName;

class _MyHomePageState extends State<MyHomePage> {
  String _platformVersion = 'Unknown';

  int get _itemCount => widget._items.length;

  @override
  void initState() {
    super.initState();
    initPlatformState();
  }

  // Platform messages are asynchronous, so we initialize in an async method.
  Future initPlatformState() async {
    String platformVersion;
    // Platform messages may fail, so we use a try/catch PlatformException.
    try {
      // ignore: deprecated_member_use
      platformVersion = await Sqflite.platformVersion;
    } on PlatformException {
      platformVersion = 'Failed to get platform version';
    }

    // If the widget was removed from the tree while the asynchronous platform
    // message was in flight, we want to discard the reply rather than calling
    // setState to update our non-existent appearance.
    if (!mounted) return;

    setState(() {
      _platformVersion = platformVersion;
    });

    print('running on: ' + _platformVersion);

    // Use it to auto start a test page
    if (debugAutoStartRouteName != null) {
      // only once

      // await Navigator.of(context).pushNamed(testExpRoute);
      // await Navigator.of(context).pushNamed(testRawRoute);
      var future = Navigator.of(context).pushNamed(debugAutoStartRouteName);
      // ignore: deprecated_member_use_from_same_package
      debugAutoStartRouteName = null;
      await future;
      // await Navigator.of(context).pushNamed(testExceptionRoute);
    }
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
        appBar: AppBar(
          title:
              Center(child: Text('Sqflite demo', textAlign: TextAlign.center)),
        ),
        body:
            ListView.builder(itemBuilder: _itemBuilder, itemCount: _itemCount));
  }

  //new Center(child: new Text('Running on: $_platformVersion\n')),

  Widget _itemBuilder(BuildContext context, int index) {
    return MainItemWidget(widget._items[index], (MainItem item) {
      Navigator.of(context).pushNamed(item.route);
    });
  }
}

Use this package as a library

1. Depend on it

将此添加到包的pubspec.yaml文件中:


dependencies:
  sqflite: ^1.3.1

2. Install it

您可以从命令行安装软件包:

使用Flutter:


$ flutter pub get

另外,您的编辑器可能支持flutter pub get . 查看您的编辑器文档以了解更多信息.

3. Import it

现在,在Dart代码中,您可以使用:


import 'package:sqflite/sqflite.dart';
  
Popularity:
描述该程序包相对于其他程序包的受欢迎程度. [更多]
100
Health:
从静态分析得出的代码运行状况. [更多]
100
Maintenance:
反映出包装的整洁和最新状态. [更多]
100
Overall:
以上的加权分数. [更多]
100
了解有关得分的更多信息.

我们在2020年7月11日对该程序包进行了分析,并在下面提供了得分,详细信息和建议. using: 分析已完成,状态使用以下命令 :

  • 飞镖:2.8.4
  • 直到:0.13.14
  • 颤振:1.17.5

Analysis suggestions

软件包不支持Flutter平台Linux

Because:

  • package:sqflite/sqflite.dart声明支持以下平台:android,ios,macos

软件包不支持Flutter平台Web

Because:

  • package:sqflite/sqflite.dart声明支持以下平台:android,ios,macos

软件包不支持Flutter平台窗口

Because:

  • package:sqflite/sqflite.dart声明支持以下平台:android,ios,macos

软件包与SDK dart不兼容

Because:

  • sqflite是需要null的软件包.

Dependencies

Package Constraint Resolved Available
直接依赖
Dart SDK > = 2.7.0 <3.0.0
flutter 0.0.0
path > = 1.5.1 <3.0.0 1.7.0
sqflite_common > = 1.0.1 <3.0.0 1.0.2+1
传递依存关系
collection 1.14.12 1.14.13
meta 1.1.8 1.2.2
sky_engine 0.0.99
synchronized 2.2.0+1
typed_data 1.1.6 1.2.0
vector_math 2.0.8 2.1.0-nullsafety
开发依赖
flutter_test
http >=0.12.0+2
pedantic > = 1.4.0 <3.0.0
process_run >=0.10.7
test_api any

by  ICOPY.SITE