how-to-export-sqlite-tables-to-create-statements

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

How to Export SQLite Tables to CREATE Statements

如何将SQLite表导出为CREATE语句

In this article I will show you how to export all the tables and indexes in a SQLite database to CREATE statements at runtime.

在本文中，我将向你展示如何在运行时将SQLite数据库中的所有表和索引导出为CREATE语句。

Getting started

开始上手

Start by creating a new directory and Flutter project:

mkdir sqlite_introspect
cd sqlite_introspect
flutter create .
flutter pub add sqlite3 mustache_template

This will add the

sqlite3

package which uses FFI to call the native executable and mustache that we will use for templates later.

首先创建一个新目录和Flutter项目：

mkdir sqlite_introspect
cd sqlite_introspect
flutter create .
flutter pub add sqlite3 mustache_template

这会添加

sqlite3

包（它使用FFI调用原生可执行文件）以及我们稍后将用于模板的mustache包。

Creating the database

创建数据库

Creating the database can be done either in memory or based on a local file. For this example we will use in memory:

final Database db = sqlite3.openInMemory();

Don't forget to dispose of the database after use:

db.dispose();

可以在内存中或基于本地文件创建数据库。在本示例中，我们使用内存数据库：

final Database db = sqlite3.openInMemory();

使用完毕后不要忘记释放数据库：

db.dispose();

Defining the template

定义模板

Since we will be using Mustache we can define the variables that we will pass to the template as JSON.

Create a

TableInfo

class that will store the fields and indexes:

class TableInfo {
  final String name;
  final List<Map<String, dynamic>> fields;
  final List<Map<String, dynamic>> indexes;

  TableInfo({
    required this.name,
    required this.fields,
    required this.indexes,
  });

  Map<String, dynamic> toJson() {
    return {
      'name': name,
      'fields': [
        for (var i = 0; i < fields.length; i++)
          {
            'index': i,
            'table': name,
            'isLast': i == fields.length - 1,
            ...fields[i],
          },
      ],
      'indexes': [
        for (var i = 0; i < indexes.length; i++)
          {
            'index': i,
            'table': name,
            'isLast': i == indexes.length - 1,
            ...indexes[i],
          },
      ],
    };
  }
}

Now we can create the Mustache template used to build up the CREATE statements:

const template = '''
{{#tables}}
CREATE TABLE {{name}} (
  {{#fields}}
  {{name}} {{#type}} {{.}}{{/type}}{{#notnull}} NOT NULL{{/notnull}}{{#pk}} PRIMARY KEY{{/pk}}{{#dflt_value}} DEFAULT {{.}}{{/dflt_value}}{{^isLast}},{{/isLast}}
  {{/fields}}
);
{{#indexes}}
CREATE {{#unique}} UNIQUE{{/unique}} {{name}}
ON {{table}}({{#values}} {{name}} {{/values}}{{^isLast}},{{/isLast}});
{{/indexes}}
{{/tables}}
''';

由于我们将使用Mustache，我们可以将传递给模板的变量定义为JSON格式。

创建一个

TableInfo

类来存储字段和索引：

class TableInfo {
  final String name;
  final List<Map<String, dynamic>> fields;
  final List<Map<String, dynamic>> indexes;

  TableInfo({
    required this.name,
    required this.fields,
    required this.indexes,
  });

  Map<String, dynamic> toJson() {
    return {
      'name': name,
      'fields': [
        for (var i = 0; i < fields.length; i++)
          {
            'index': i,
            'table': name,
            'isLast': i == fields.length - 1,
            ...fields[i],
          },
      ],
      'indexes': [
        for (var i = 0; i < indexes.length; i++)
          {
            'index': i,
            'table': name,
            'isLast': i == indexes.length - 1,
            ...indexes[i],
          },
      ],
    };
  }
}

现在我们可以创建用于生成CREATE语句的Mustache模板：

const template = '''
{{#tables}}
CREATE TABLE {{name}} (
  {{#fields}}
  {{name}} {{#type}} {{.}}{{/type}}{{#notnull}} NOT NULL{{/notnull}}{{#pk}} PRIMARY KEY{{/pk}}{{#dflt_value}} DEFAULT {{.}}{{/dflt_value}}{{^isLast}},{{/isLast}}
  {{/fields}}
);
{{#indexes}}
CREATE {{#unique}} UNIQUE{{/unique}} {{name}}
ON {{table}}({{#values}} {{name}} {{/values}}{{^isLast}},{{/isLast}});
{{/indexes}}
{{/tables}}
''';

Exporting the PRAGMA

导出PRAGMA信息

Now we can export the PRAGMA for the database by exporting the list of tables, querying the column information and indexes about each one.

final tables = <TableInfo>[];
// Export table names
final tableNames = db
	.select("SELECT name FROM sqlite_master WHERE type='table';")
	.map((e) => e['name'] as String);
for (final t in tableNames) {
  // Export column information
  final info = db.select('PRAGMA table_info($t);');
  final tbl = TableInfo(name: t, fields: [], indexes: []);
  for (final c in info) {
    tbl.fields.add(c);
  }
  // Export index names
  final indexList = db.select('PRAGMA index_list($t);');
  for (final index in indexList) {
    final name = index['name'] as String;
    // Export index information
    final infos = db.select('PRAGMA index_info($name);');
    final indexValue = {...index, 'values': infos};
    tbl.indexes.add(indexValue);
  }
  tables.add(tbl);
}

现在我们可以通过导出表列表、查询每个表的列信息和索引来导出数据库的PRAGMA信息。

final tables = <TableInfo>[];
// 导出表名
final tableNames = db
	.select("SELECT name FROM sqlite_master WHERE type='table';")
	.map((e) => e['name'] as String);
for (final t in tableNames) {
  // 导出列信息
  final info = db.select('PRAGMA table_info($t);');
  final tbl = TableInfo(name: t, fields: [], indexes: []);
  for (final c in info) {
    tbl.fields.add(c);
  }
  // 导出索引名
  final indexList = db.select('PRAGMA index_list($t);');
  for (final index in indexList) {
    final name = index['name'] as String;
    // 导出索引信息
    final infos = db.select('PRAGMA index_info($name);');
    final indexValue = {...index, 'values': infos};
    tbl.indexes.add(indexValue);
  }
  tables.add(tbl);
}

Rendering the template

渲染模板

Now take the tables we just exported and pass them to the mustache template to render:

final tml = Template(template);
final args = {"tables": tables.map((e) => e.toJson()).toList()};
final str = tml.renderString(args);
print(str);

This will now print out all the tables and indexes as CREATE as valid SQL. 🎉

现在将我们刚刚导出的表传递给mustache模板进行渲染：

final tml = Template(template);
final args = {"tables": tables.map((e) => e.toJson()).toList()};
final str = tml.renderString(args);
print(str);

现在它会将所有表和索引作为有效的CREATE SQL语句打印出来。🎉