Flutter数据库管理插件sqfentity的使用

Flutter数据库管理插件sqfentity的使用

简介

sqfEntity 是一个基于 sqflite 插件的库，它让你可以轻松快速地构建和执行 SQL 命令，并且提供了类似于 .NET 的 Entity Framework 的流式方法。此外，sqfEntity 还可以生成带验证和特殊控件（如下拉列表、日期选择器、复选框等）的添加/编辑表单。

开始前的帮助

如果你觉得这个项目帮助你减少了开发时间，请不要忘记在页面右上角点击星标。也可以通过赞助来支持我的不眠之夜。

新功能

sqfEntity 最新版本增加了 formTables 参数，用于生成添加/编辑/列表视图控制器，并添加了以下特殊控件：

• 验证器（必填字段、数据类型正则表达式）
• 相关表的下拉列表控件
• 布尔字段的复选框
• 日期时间字段的日期时间选择器
• 日期字段的日期选择器

入门指南

这个项目是一个基于 sqfEntity ORM 的数据库应用起点。一些文件包括：

1. main.dart                      : 启动文件，包含使用sqfEntity的示例方法
2. model/controller.dart          : 主控制器，从应用程序主页面提供访问创建的表单视图（可以修改）
3. model/model.dart               : 定义并修改你的数据库模型（可以修改）
4. model/model.g.dart             : 示例生成模型供示例使用（不要手动修改）
5. model/model.g.view.dart        : 示例生成表单视图供示例使用（不要手动修改）
6. model/view.list.dart           : 列出保存的表项的视图（可以修改）
7. model/view.detail.dart         : 查看所选项目的详细信息的视图（可以修改）
8. sample_advanced_form/*.dart    : 显示如何在运行时筛选toList()的示例小部件
9. assets/chinook.sqlite          : 示例数据库，如果你想使用现有的数据库或从数据库创建模型
10. app.dart                       : 显示创建模型的示例应用
11. LICENSE.txt                   : 查看此文件了解许可条款

依赖项

注意：如果你不想使用表单生成器属性，则不需要 flutter_datetime_picker 库。

dependencies:
  flutter_datetime_picker: ^1.2.8  
  sqfentity: ^1.2.3
  sqfentity_gen: ^1.2.3

dev_dependencies:
  build_runner: ^1.6.5
  build_verify: ^1.1.0

Android上的sqlcipher

现在默认情况下，当在发布模式下构建APK时，Flutter启用了代码缩减。因此，你需要添加以下ProGuard规则到文件 android/app/proguard-rules.pro。如果该文件不存在，请创建它：

-keep class net.sqlcipher.** { *; }

创建一个新的数据库模型

首先，你需要：

1. 将以下两个文件复制到你的 /lib/model 文件夹：

   • view.list.dart
   • view.detail.dart

2. 将以下文件复制到你的 /lib/tools 文件夹：

   • helper.dart

3. 在 lib/model/ 文件夹中创建你的 model.dart 文件以定义你的模型，并导入必要的包：

import 'dart:convert';
import 'dart:typed_data';
import 'package:flutter_datetime_picker/flutter_datetime_picker.dart';
import 'package:http/http.dart' as http;
import 'package:flutter/material.dart';
import 'package:sqfentity/sqfentity.dart';
import 'package:sqfentity_gen/sqfentity_gen.dart';
import '../tools/helper.dart';
import 'view.list.dart';

1. 添加以下声明，以便为文件创建部分：

part 'model.g.dart';
part 'model.g.view.dart'; // 如果你不使用表单生成器属性，则不需要这部分

定义表对象

我们的模型文件已经准备好使用。定义表如下所示：

// 定义 'tableCategory' 常量作为 SqfEntityTable，表示 category 表。
const tableCategory = SqfEntityTable(
  tableName: 'category',
  primaryKeyName: 'id',
  primaryKeyType: PrimaryKeyType.integer_auto_incremental,
  useSoftDeleting: true,
  modelName: null,
  fields: [
    SqfEntityField('name', DbType.text),
    SqfEntityField('isActive', DbType.bool, defaultValue: true),
  ]
);

// 如果 'useSoftDeleting' 为 true，则生成器引擎会在表上创建名为 "isDeleted" 的字段。
// 当项目被删除时，该字段值更改为 "1"（不硬删除）
// 这样可以在删除后恢复项目。
// 如果 'modelName'（类名）为 null，则 EntityBase 使用 TableName 代替 modelName。

// 定义 'tableProduct' 常量作为 SqfEntityTable，表示 product 表。
const tableProduct = SqfEntityTable(
  tableName: 'product',
  primaryKeyName: 'id',
  primaryKeyType: PrimaryKeyType.integer_auto_incremental,
  useSoftDeleting: true,
  fields: [
    SqfEntityField('name', DbType.text),
    SqfEntityField('description', DbType.text),
    SqfEntityField('price', DbType.real, defaultValue: 0),
    SqfEntityField('isActive', DbType.bool, defaultValue: true),
    SqfEntityFieldRelationship(
        parentTable: tableCategory,
        relationType: RelationType.ONE_TO_MANY,
        deleteRule: DeleteRule.CASCADE,
        defaultValue: 0), // 关联类别 ID 的关系列
    SqfEntityField('rownum', DbType.integer,
        sequencedBy:
            seqIdentity /* 示例：将列链接到序列 */),
    SqfEntityField('imageUrl', DbType.text)
);

// 如果此表（产品）是父表（类别）的子表，必须在字段中声明 SqfEntityFieldRelationship 列，以便进行对象关系映射。
// 可以选择以下规则之一：CASCADE, NO ACTION, SET NULL, SET DEFAULT VALUE
// 更多信息请参见：[这里](https://www.mssqltips.com/sqlservertip/2365/sql-server-foreign-key-update-and-delete-rules/)

// 定义 'tableTodo' 常量作为 SqfEntityTable，表示 todos 表。
const tableTodo = SqfEntityTable(
  tableName: 'todos',
  primaryKeyName: 'id',
  useSoftDeleting: false,
  primaryKeyType: PrimaryKeyType.integer_unique,
  defaultJsonUrl:
      'https://jsonplaceholder.typicode.com/todos', // 可选：用于与网络 URL 的 JSON 数据同步

  // 声明字段
  fields: [
    SqfEntityField('userId', DbType.integer),
    SqfEntityField('title', DbType.text),
    SqfEntityField('completed', DbType.bool, defaultValue: false)
]);

// 添加序列示例
const seqIdentity = SqfEntitySequence(
  sequenceName: 'identity',
  // maxValue:  10000, /* 可选，默认是最大整数 (9.223.372.036.854.775.807) */
  // modelName: 'SQEidentity', 
                      /* 可选。当 modelName 为空时，SqeEntity 会自动设置为 sequenceName */
  // cycle : false,   /* 可选。默认是 false； */
  // minValue = 0;    /* 可选。默认是 0 */
  // incrementBy = 1; /* 可选。默认是 1 */
  // startWith = 0;   /* 可选。默认是 0 */
);

添加表对象到dbModel

@SqfEntityBuilder(myDbModel)
const myDbModel = SqfEntityModel(
    modelName: 'MyDbModel', // 可选
    databaseName: 'sampleORM.db',
    password: null, // 如果你想使用加密数据库，可以设置密码
                    // 更多信息：[这里](https://github.com/sqlcipher/sqlcipher)

    // 将定义的表放入表列表。
    databaseTables: [tableCategory, tableProduct, tableTodo],
     // 如果你想使用表单生成器属性，可以定义表来生成添加/编辑视图表单
    formTables: [tableProduct, tableCategory, tableTodo],
    // 将定义的序列放入序列列表。
    sequences: [seqIdentity],
    bundledDatabasePath:
        null // 'assets/sample.db' // 这个值是可选的。当 bundledDatabasePath 为空时，EntityBase 会在初始化数据库时创建新的数据库
);

生成模型文件

在终端窗口中运行以下命令：

flutter pub run build_runner build --delete-conflicting-outputs

运行命令后，请检查 lib/model/model.g.dart 和 lib/model/model.g.view.dart（如果定义了 formTables 参数）

附加现有的SQLite数据库

bundledDatabasePath 是可选的。当 bundledDatabasePath 为空时，EntityBase 会在初始化数据库时创建新的数据库。

如何导入现有数据库并自动生成模型？

步骤 1:

将你的现有数据库复制到 /assets 文件夹中（在这个样本中，我们复制了 chinook.sqlite 数据库），并在 pubspec.yaml 中定义你的资产数据库：

flutter:
  assets:
    - assets/chinook.sqlite

步骤 2:

运行带有这些参数的脚本。

指定数据库名称以用于数据库连接：

class BundledDbModel extends SqfEntityModelProvider {}

final bundledDbModel = await convertDatabaseToModelBase(BundledDbModel()
..databaseName = 'chinook.db'
..bundledDatabasePath = 'assets/chinook.sqlite');

步骤 3:

运行此函数将模型转换为注释：

final String modelConstString =
    SqfEntityConverter(bundledDbModel).createConstDatabase();

所有模型都已成功创建并粘贴到剪贴板。

打开 lib/model 文件夹中的 model.dart 并在以下行之后粘贴模型：

part 'model.g.dart';

在终端窗口中运行以下命令：

flutter pub run build_runner build --delete-conflicting-outputs

你的实体模型将在 lib/model/model.g.dart 中创建。

数据库初始化异步方法

当应用程序初始化时，initializeDB() 方法会自动执行。initilizeDb 方法会为你运行 CREATE TABLE/ALTER TABLE ADD COLUMN 查询。

void main(List<String> args) async {
    runSamples();
    // 如果数据库未初始化，检查调试控制台中的警告
}

使用创建的新模型

让我们向 “Category” 表添加一些记录

注意：save() 方法返回添加记录的主键ID

final notebookCategoryId = await Category(name: "Notebooks", isActive: true).save();

// 或另一种定义类别的方式
final ultrabookCategoryId = await Category.withFields("Ultrabooks", true, false).save();

向 “Product” 表添加一些记录

你可以按以下方式添加记录：

final product = Product();
product.name = "Notebook 12\"";
product.description = "128 GB SSD i7";
product.price = 6899;
product.categoryId = notebookCategoryId;
await product.save();

你也可以快速添加记录如下：

await Product.withFields( "Notebook 12\"", "128 GB SSD i7", 6899, true, notebookCategoryId, 0, false).save();
await Product.withFields( "Notebook 12\"", "256 GB SSD i7", 8244, true, notebookCategoryId, 0, false).save();
await Product.withFields( "Notebook 12\"", "512 GB SSD i7", 9214, true, notebookCategoryId, 0, false).save();
await Product.withFields( "Notebook 13\"", "128 GB SSD", 8500, true, notebookCategoryId, 0, false).save();
await Product.withFields( "Notebook 13\"", "256 GB SSD", 9900, true, notebookCategoryId, 0, false).save();
await Product.withFields( "Notebook 13\"", "512 GB SSD", 11000, null, notebookCategoryId, 0, false).save();
await Product.withFields( "Notebook 15\"", "128 GB SSD", 8999, null, notebookCategoryId, 0, false).save();
await Product.withFields( "Notebook 15\"", "256 GB SSD", 10499, null, notebookCategoryId, 0, false).save();
await Product.withFields( "Notebook 15\"", "512 GB SSD", 11999, true, notebookCategoryId, 0, false).save();

await Product.withFields( "Ultrabook 13\"", "128 GB SSD i5", 9954, true, ultrabookCategoryId, 0, false).save();
await Product.withFields( "Ultrabook 13\"", "256 GB SSD i5", 11154, true, ultrabookCategoryId, 0, false).save();
await Product.withFields( "Ultrabook 13\"", "512 GB SSD i5", 13000, true, ultrabookCategoryId, 0, false).save();
await Product.withFields( "Ultrabook 15\"", "128 GB SSD i7", 11000, true, ultrabookCategoryId, 0, false).save();
await Product.withFields( "Ultrabook 15\"", "256 GB SSD i7", 12000, true, ultrabookCategoryId, 0, false).save();
await Product.withFields( "Ultrabook 15\"", "512 GB SSD i7", 14000, true, ultrabookCategoryId, 0, false).save();

示例使用sqf

要运行此语句 “SELECT * FROM PRODUCTS”

尝试以下代码：

final productList = await Product().select().toList();

for (int i = 0; i < productList.length; i++) {
    print(productList[i].toMap());
}

要运行此语句 “SELECT * FROM PRODUCTS WHERE id=5”

有两种方法可以实现：

第一种方法：

var product = await Product().getById(5);

第二种方法：

var product = await Product().select().id.equals(5).toSingle();

SELECT FIELDS, ORDER BY 示例

EXAMPLE 1.2: ORDER BY FIELDS ex: SELECT * FROM PRODUCTS ORDER BY name, price DESC, id 
        -> await Product().select().orderBy("name").orderByDesc("price").orderBy("id").toList()

EXAMPLE 1.3: SELECT SPECIFIC FIELDS ex: SELECT name,price FROM PRODUCTS ORDER BY price DESC 
        -> await Product().select(columnsToSelect: ["name","price"]).orderByDesc("price").toList()

SELECT AND FILTER 示例

EXAMPLE 2.1: EQUALS ex: SELECT * FROM PRODUCTS WHERE isActive=1 
-> await Product().select().isActive.equals(true).toList()

EXAMPLE 2.2: WHERE field IN (VALUES) ex: SELECT * FROM PRODUCTS WHERE ID IN (3,6,9) 
-> await Product().select().id.inValues([3,6,9]).toList()

EXAMPLE 2.3: BRACKETS ex: SELECT TOP 1 * FROM PRODUCTS WHERE price>10000 AND (description LIKE '%256%' OR description LIKE '512%') 
-> await Product().select()
  .price
  .greaterThan(10000)
  .and.startBlock.description.contains("256").or.description.startsWith("512").endBlock
  .toSingle();

EXAMPLE 2.4: BRACKETS 2: SELECT name,price FROM PRODUCTS WHERE price<=10000 AND (description LIKE '%128%' OR description LIKE '%GB') 
-> await Product().select(columnsToSelect:["name","price"])
   .price.lessThanOrEquals(10000)
   .and.startBlock.description.contains("128").or.description.endsWith("GB").endBlock
   .toList();

EXAMPLE 2.5: NOT EQUALS ex: SELECT * FROM PRODUCTS WHERE ID <> 11 
-> await Product().select().id.not.equals(11).toList();
        
EXAMPLE 2.6: GREATERTHEN OR EQUALS, LESSTHAN OR EQUALS ex: SELECT * FROM PRODUCTS WHERE price>=10000 AND price<=13000 
-> await Product().select().price.greaterThanOrEquals(10000).and.price.lessThanOrEquals(13000).toList();        

EXAMPLE 2.7: BETWEEN ex: SELECT * FROM PRODUCTS WHERE price BETWEEN 8000 AND 14000 
-> await Product().select().price.between(8000,14000).orderBy("price").toList();

EXAMPLE 2.8: 'NOT' KEYWORD ex: SELECT * FROM PRODUCTS WHERE NOT id>5 
-> await Product().select().id.not.greaterThan(5).toList();

编写自定义SQL过滤器

EXAMPLE 2.9: WRITING CUSTOM FILTER IN WHERE CLAUSE ex: SELECT * FROM PRODUCTS WHERE id IN (3,6,9) OR price>8000 
-> await Product().select().where("id IN (3,6,9) OR price>8000").toList()

EXAMPLE 2.10: Build filter and query from values from the form
-> await Product().select()
   .price.between(minPrice, maxPrice)
   .and.name.contains(nameFilter)
   .and.description.contains(descFilter)
   .toList()

SELECT WITH DELETED ITEMS (SOFT DELETE WHEN USED)

EXAMPLE 2.11: EXAMPLE 1.13: Select products with deleted items
-> await Product().select(getIsDeleted: true).toList()

EXAMPLE 2.12: Select products only deleted items 
-> await Product().select(getIsDeleted: true).isDeleted.equals(true).toList()

LIMITATION, PAGING

EXAMPLE 3.1: LIMITATION SELECT TOP 3 * FROM PRODUCTS ORDER BY price DESC 
-> await Product().select().orderByDesc("price").top(3).toList()

EXAMPLE 3.2: PAGING: PRODUCTS in 3. page (5 items per page) 
-> await Product().select().page(3,5).toList()

DISTINCT

EXAMPLE 4.1: DISTINCT: SELECT DISTINCT name FROM PRODUCTS WHERE price > 3000 
-> await Product().distinct(columnsToSelect:["name"]).price.greaterThan(3000).toList();

GROUP BY

EXAMPLE 4.2: GROUP BY WITH SCALAR OR AGGREGATE FUNCTIONS
SELECT name, COUNT(id) AS Count, MIN(price) AS minPrice, MAX(price) AS maxPrice, AVG(price) AS
avgPrice, SUM(price) AS sumPrice FROM PRODUCTS GROUP BY name 
-> await Product()
    .select(columnsToSelect: [
    ProductFields.name.toString(),
    ProductFields.id.count("Count"),
    ProductFields.price.min("minPrice"),
    ProductFields.price.max("maxPrice"),
    ProductFields.price.avg("avgPrice"),
    ProductFields.price.sum("sumPrice"),
  ])
  .groupBy(ProductFields.name.toString() /*也可以使用 .groupBy("name")*/)
  .toListObject();

RELATIONSHIPS

EXAMPLE 7.1: goto Category from Product 

final product = await Product().getById(1);
final category = await product.getCategory();
print(category.toMap());

Results:
{id: 1, name: Notebooks, isActive: true, isDeleted: false}


EXAMPLE 7.2: Products of 'Notebooks Category' listing 

final category = await Category().getById(1);
final productList = await category.getProducts();

for(var product in productList) {
   print(product.toMap());
   }

Results: Products of 'Notebooks' listing 9 matches found:
{id: 1, name: Notebook 12", description: 128 GB SSD i7, price: 6899.0, isActive: true, categoryId: 1, rownum: 1, isDeleted: false}
{id: 2, name: Notebook 12", description: 256 GB SSD i7, price: 8244.0, isActive: true, categoryId: 1, rownum: 2, isDeleted: false}
...

save() 方法用于插入或更新（适用于两者）

await Product(name:"test product").save(); // 插入新记录，如果id为null或等于零
await Product(id:1, name:"test product").save(); // 更新记录

saveAll() 方法用于插入或更新列表（适用于两者）

var productList= List<Product>();
// TO DO.. 添加产品到列表

// 在列表中保存所有产品
final results = await Product().saveAll(productList);
 
print(" List<BoolResult> result of saveAll method is following:");
for (var result in results) {
   print(result.toString());
}

upsertAll() 方法用于插入或更新列表（适用于两者）

注意：upsertAll() 方法比 saveAll() 方法更快。当你确定主键大于零时应使用 upsertAll()。

var productList= List<Product>();
// TO DO.. 添加产品到列表，主键必须大于零

// 在列表中保存所有产品
final results = await Product().upsertAll(productList);
for (var result in results) {
    print(result.toString());  
}

更新多个记录

EXAMPLE 5.1: UPDATE PRODUCT SET isActive=0 WHERE ID>=10

final result = await Product().select().id.greaterThan(10).update({"isActive": 0});
print(result.toString());

删除多个记录

EXAMPLE 6.4: DELETE PRODUCT WHERE ID>17

final result = await Product().select().id.greaterThan(17).delete();
print(result.toString());

从网络同步数据

EXAMPLE 8.2: Fill List from web with Url (JSON data) and saveAll

Step 1: Todo.fromWebUrl("URL",(todosList){}) 方法从网络获取 JSON 数据并加载到 todosList

Step 2: 调用 Todo().upsertAll(todosList) 方法保存本地数据库中的所有数据

todosList = await Todo.fromWebUrl("https://jsonplaceholder.typicode.com/todos");
final results = await Todo().upsertAll(todosList);

// 打印 upsert 结果
for (var res in results) {
  print(res.toString()); 
}

todosList = await Todo().select().top(10).toList();
print(todosList.length.toString() + " matches found\n");
for (var todo in todosList) {
  print(todo.toMap());
}

在数据库上运行原始SQL查询或获取datatable

EXAMPLE 9.1: Execute custom SQL command on database

final sql = "UPDATE product set isActive=1 where isActive=1";
final result = await MyDbModel().execSQL(sql);
print(result.toString());

结果：

flutter: sql command executed successfully

EXAMPLE 9.2: Execute custom SQL command List on database

final sqlList=List<String>();
final result = await MyDbModel().execSQLList(sqlList);
print(result.toString());

结果：

sql command list executed successfuly

EXAMPLE 9.3 Execute custom SQL Query and get datatable -> returns List<Map<String,dynamic>>

await MyDbModel().execDataTable('SELECT name, price FROM product order by price desc LIMIT 5');

结果：（5行）

flutter: {name: Ultrabook 15", price: 14000.0}
flutter: {name: Ultrabook 13", price: 13000.0}
flutter: {name: Ultrabook 15", price: 12000.0}
flutter: {name: Notebook 15", price: 11999.0}
flutter: {name: Ultrabook 13", price: 11154.0}

EXAMPLE 9.4 Execute custom SQL Query and get first col of first row (execute scalar)

await MyDbModel().execScalar('SELECT name FROM product order by price desc');

结果：（1行）

flutter: Ultrabook 15"

示例 10 SqfEntity Sequence Samples

final int currentVal = await IdentitySequence().currentVal();
final int nextVal = await IdentitySequence().nextVal();
final int nextVal2 = await IdentitySequence().nextVal();
final int currentVal2 = await IdentitySequence().currentVal();

// 你可以在任何时候重置序列
final int currentVal3 = await IdentitySequence().reset();

print("""
IdentitySequence().currentVal = $currentVal
IdentitySequence().nextVal = $nextVal
IdentitySequence().nextVal = $nextVal2
IdentitySequence().currentVal = $currentVal2
IdentitySequence().reset = $currentVal3 // 返回起始值
""");

转换任何对象或列表到Json的方法（具有嵌套/相关对象）

EXAMPLE 11.1 单个对象转Json

final product = Product().select().toSingle();
final jsonString = product.toJson();
print(jsonString);

结果是：

flutter: {"id":1,"name":"Notebook 12\"","description":"128 GB SSD i7","price":6899.0,"isActive":true,"categoryId":1,"rownum":1,"imageUrl":"https://raw..","isDeleted":false}

EXAMPLE 11.2 对象列表带嵌套对象转Json

final jsonStringWithChilds =  await Category().select().toJson(); // 选择了所有分类
print(jsonStringWithChilds);

结果是：

flutter: [{"id":1,"name":"Notebooks","isActive":true,"isDeleted":false,"products":[{"id":1,"name":"Notebook 12\"","description":"128 GB SSD i7","price":6899.0,"isActive":1,"categoryId":1,"rownum":1,"imageUrl":"https://raw.githubusercontent.com/hhtokpinar/sqfEntity/master/example/assets/notebook.png","isDeleted":0},{"id":2,"name":"Notebook 12\"","description":"256 GB SSD i7","price":8244.0,"isActive":1,"categoryId":1,"rownum":2,"imageUrl":"https://raw.githubusercontent.com/hhtokpinar/sqfEntity/master/example/assets/notebook.png","isDeleted":0},{"id":3,"name":"Notebook 12\"","description":"512 GB SSD i7","price":9214.0,"isActive":1,"categoryId":1,"rownum":3,"imageUrl":"https://raw.githubusercontent.com/hhtokpinar/sqfEntity/master/example/assets/notebook.png","isDeleted":0}....
.........................

从视图中获取一些数据

final vtracs = await VTrack().select().top(5).toList(); 

结果：

flutter: 5 matches found
flutter: {Name: For Those About To Rock (We Salute You), album: For Those About To Rock We Salute You, media: MPEG audio file, genres: Rock, TrackId: 1}
flutter: {Name: Balls to the Wall, album: Balls to the Wall, media: Protected AAC audio file, genres: Rock, TrackId: 2}
flutter: {Name: Fast As a Shark, album: Restless and Wild, media: Protected AAC audio file, genres: Rock, TrackId: 3}
flutter: {Name: Restless and Wild, album: Restless and Wild, media: Protected AAC audio file, genres: Rock, TrackId: 4}
flutter: {Name: Princess of the Dawn, album: Restless and Wild, media: Protected AAC audio file, genres: Rock, TrackId: 5}

在 main.dart 中查看以下示例以使用样本模型

// SELECT AND ORDER PRODUCTS BY FIELDS
samples1();

// FILTERS: SOME FILTERS ON PRODUCTS
samples2();

// LIMITATIONS: PAGING, TOP X ROW
samples3();

// DISTINCT, GROUP BY with SQL AGGREGATE FUNCTIONS,
samples4();

// UPDATE BATCH, UPDATE OBJECT
samples5();

// DELETE BATCH, DELETE OBJECT
samples6();

// ORM (Object Relational Mapping) SAMPLE
samples7();

// Fill List from the web (JSON)
samples8();

// Run custom raw sql query on database
samples9();