Flutter SQLite数据库操作插件to_sqlite的使用

Flutter SQLite数据库操作插件to_sqlite的使用

简介

to_sqlite 是一个命令行工具，用于将CSV文件转换为SQLite数据库，并生成相应的模型类。它可以帮助开发者快速创建和管理SQLite数据库，特别是在Flutter项目中。

安装

要全局安装此工具，请运行以下命令：

dart pub global activate to_sqlite

入门指南

• 前提条件: Dart SDK (版本 ^3.4.3 或更高)

使用方法

安装完成后，您可以使用 toSqlite --{options} {file/path.ext} 命令来传递必要的CSV文件或可选的配置文件 config.json，以自定义CSV数据如何转换为SQLite数据库。

什么是 config.json？

config.json 文件是一个配置文件，允许您控制CSV文件中的数据如何转换为SQLite数据库。它包含有关如何结构化SQLite表、列名、数据类型以及其他影响最终输出的设置信息。使用 config.json 是可选的，但它为您提供更高的灵活性来管理数据库。

命令选项

生成 config.json

命令: generate_config --filePath (-f)

• 参数: 配置JSON文件的路径。
• 示例:

  toSqlite generate_config -f ./config/config.json
  

生成 Database.sqlite

命令: generate_db --filePath (-f)

• 参数: 要转换为SQLite的CSV文件路径。如果没有提供配置文件，工具将自动从CSV推断表结构。

• 示例:

  toSqlite generate_db -f ./data/myfile.csv
  

  默认情况下，config.json 文件会在您运行 generate_db 命令时自动生成。

  或者，您可以传递配置文件来自定义数据库的生成方式：

  toSqlite generate_db -f ./config/config.json
  

生成模型类

命令: generate_class --filePath (-f)

• 参数: 基于CSV或Config.json生成模型类。
• 示例:

  toSqlite generate_class -f ./data/myfile.csv
  

  或

  toSqlite generate_class -f config.json
  

config.json 的结构

以下是 config.json 文件的示例结构：

{
  /// Path to the CSV file that will be used for creating the SQLite table.
  "csv": "assets/test.csv",

  /// Path where the SQLite file will be created.
  /// Optional: If not provided, the SQLite file will be created in the same directory as the config or CSV file.
  "output_path": "assets",

  /// Name of the table to be created in the SQLite database.
  /// Optional: By default, it will use the name of the CSV file.
  "table_name": "test",

  /// If a nullable value is encountered and type inference is enabled,
  /// this flag decides whether to fallback to String type for that value.
  /// Example: If a column has null values and type inference is enabled,
  /// setting this to true will default the type to String for safety.
  "fallback_to_string_on_nullable_value": false,

  /// Enables automatic type inference based on the CSV data.
  /// If set to true, the package will analyze the values in each column
  /// and infer the appropriate data type (e.g., `int`, `String`).
  /// Useful for automating data type assignment without manually defining all types.
  "enable_type_inference": true,

  /// Name for default ID column which will auto increment on each insertion
  /// to the corresponding table.
  ///
  /// Its optional field if this is not specified in the config
  /// the tool won't create any ID column.
  "auto_incrementing_id_column_name": "auto_incrementing_id",

  /// Defines the columns for the SQLite table.
  /// Each object in this array corresponds to a column definition, containing attributes
  /// like the column name, data type, whether it's nullable, and other properties.
  "table_columns": [
    {
      /// The name of the column in the SQLite table.
      "name": "id",

      /// The data type of the column. Can be `int`, `String`, etc.
      /// The type should match the expected data from the CSV.
      "type": "int",

      /// Specifies whether the column can contain null values.
      /// If false, the column will require a non-null value for each row.
      "nullable": false,

      /// Default value for the column. If no value is provided during insertion,
      /// this value will be used. In this case, it's set to null (meaning no default).
      "default": null,

      /// Specifies whether this column is the primary key of the table.
      /// A primary key uniquely identifies each record in the table.
      "primaryKey": true,

      /// Defines foreign key relationships for the column.
      /// In this case, the "id" column references the "id" column from the "departments" table.
      /// If this column contains a value, that value must exist in the referenced column of the referenced table, ensuring referential integrity.
      "foreign_keys": [
        {
          /// Name of the table that the foreign key references.
          "table": "departments",

          /// Name of the column in the referenced table.
          "column": "id"
        }
      ]
    }
  ]
}

常见问题

在基于Linux的系统上，您可能会遇到以下错误：

sqlite3: error while loading shared libraries: libsqlite3.so.0: cannot open shared object file: No such file or directory

此错误通常是由于系统上未默认安装SQLite。要解决此问题，您可以使用以下命令安装SQLite：

sudo apt-get install sqlite3 libsqlite3-dev

安装完成后，再次尝试运行您的命令，问题应该得到解决。

完整示例Demo

以下是一个完整的示例，展示了如何使用 to_sqlite 工具生成SQLite数据库和模型类。

1. 创建CSV文件
   在项目的 assets 文件夹中创建一个名为 test.csv 的文件，内容如下：

   id,name,age
   1,Alice,30
   2,Bob,25
   3,Charlie,35
   

2. 生成 config.json
   运行以下命令生成 config.json 文件：

   toSqlite generate_config -f ./assets/test.csv
   

3. 生成SQLite数据库
   运行以下命令生成SQLite数据库：

   toSqlite generate_db -f ./assets/test.csv
   

4. 生成模型类
   运行以下命令生成模型类：

   toSqlite generate_class -f ./assets/test.csv
   

5. 在Flutter项目中使用生成的SQLite数据库
   在Flutter项目中，您可以使用 sqflite 插件来读取和操作生成的SQLite数据库。以下是一个简单的示例代码：

   import 'package:flutter/material.dart';
   import 'package:sqflite/sqflite.dart';
   import 'package:path_provider/path_provider.dart';
   import 'dart:io';
   import 'dart:async';
   
   void main() async {
     WidgetsFlutterBinding.ensureInitialized();
     final dbPath = await getDatabasesPath();
     final db = await openDatabase('$dbPath/test.db');
     runApp(MyApp(db: db));
   }
   
   class MyApp extends StatelessWidget {
     final Database db;
   
     const MyApp({Key? key, required this.db}) : super(key: key);
   
     @override
     Widget build(BuildContext context) {
       return MaterialApp(
         title: 'Flutter SQLite Demo',
         theme: ThemeData(
           primarySwatch: Colors.blue,
         ),
         home: MyHomePage(db: db),
       );
     }
   }
   
   class MyHomePage extends StatefulWidget {
     final Database db;
   
     const MyHomePage({Key? key, required this.db}) : super(key: key);
   
     @override
     _MyHomePageState createState() => _MyHomePageState();
   }
   
   class _MyHomePageState extends State<MyHomePage> {
     List<Map<String, dynamic>> _data = [];
   
     @override
     void initState() {
       super.initState();
       _loadData();
     }
   
     Future<void> _loadData() async {
       final data = await widget.db.query('test');
       setState(() {
         _data = data;
       });
     }
   
     @override
     Widget build(BuildContext context) {
       return Scaffold(
         appBar: AppBar(
           title: Text('Flutter SQLite Demo'),
         ),
         body: ListView.builder(
           itemCount: _data.length,
           itemBuilder: (context, index) {
             final item = _data[index];
             return ListTile(
               title: Text(item['name']),
               subtitle: Text('Age: ${item['age']}'),
             );
           },
         ),
       );
     }
   }