数据库

Schema

database/schema.js记录了当前版本的schema，同时它也是创建数据库时所使用的代码。
截至v0.6.0-rc.3，Schema如下

const createSchema = () => knex.schema
  .createTable('t_circle', (table) => {
    table.increments(); // id自增列(INTEGER 类型)，会被用作主键 [社团id]
    table.string('name').notNullable(); // VARCHAR 类型 [社团名称]
  })
  .createTable('t_work', (table) => {
    table.increments(); // id自增列(INTEGER 类型)，会被用作主键 [音声id]
    table.string('root_folder').notNullable(); // VARCHAR 类型 [根文件夹别名]
    table.string('dir').notNullable(); // VARCHAR 类型 [相对存储路径]
    table.string('title').notNullable(); // VARCHAR 类型 [音声名称]
    table.integer('circle_id').notNullable(); // INTEGER 类型 [社团id]
    table.boolean('nsfw').notNullable(); // BOOLEAN 类型
    table.string('release').notNullable();  // VARCHAR 类型 [贩卖日 (YYYY-MM-DD)]

    table.integer('dl_count').notNullable(); // INTEGER 类型 [售出数]
    table.integer('price').notNullable(); // INTEGER 类型 [价格]
    table.integer('review_count').notNullable(); // INTEGER 类型 [评论数量]
    table.integer('rate_count').notNullable(); // INTEGER 类型 [评价数量]
    table.float('rate_average_2dp').notNullable(); // FLOAT 类型 [平均评价]
    table.text('rate_count_detail').notNullable(); // TEXT 类型 [评价分布明细]
    table.text('rank'); // TEXT 类型 [历史销售业绩]
    
    table.foreign('circle_id').references('id').inTable('t_circle'); // FOREIGN KEY 外键
    table.index(['circle_id', 'release', 'dl_count', 'review_count', 'price', 'rate_average_2dp']); // INDEX 索引
  })
  .createTable('t_tag', (table) => {
    table.increments(); // id自增列(INTEGER 类型)，会被用作主键 [标签id]
    table.string('name').notNullable(); // VARCHAR 类型 [标签名称]
  })
  .createTable('t_va', (table) => {
    table.string('id'); // UUID v5, 基于name生成的固定值
    table.string('name').notNullable(); // VARCHAR 类型 [声优名称]
    table.primary('id');
  })
  .createTable('r_tag_work', (table) => {
    table.integer('tag_id');
    table.integer('work_id');
    table.foreign('tag_id').references('id').inTable('t_tag'); // FOREIGN KEY 外键
    table.foreign('work_id').references('id').inTable('t_work'); // FOREIGN KEY 外键
    table.primary(['tag_id', 'work_id']); // PRIMARY KEYprimary 主键
  })
  .createTable('r_va_work', (table) => {
    table.string('va_id');
    table.integer('work_id');
    table.foreign('va_id').references('id').inTable('t_va').onUpdate('CASCADE').onDelete('CASCADE'); // FOREIGN KEY 外键
    table.foreign('work_id').references('id').inTable('t_work').onUpdate('CASCADE').onDelete('CASCADE'); // FOREIGN KEY 外键
    table.primary(['va_id', 'work_id']); // PRIMARY KEYprimary 主键
  })
  .createTable('t_user', (table) => {
    table.string('name').notNullable();
    table.string('password').notNullable();
    table.string('group').notNullable(); // USER ADMIN guest
    table.primary(['name']); // PRIMARY KEYprimary 主键
  })
  .createTable('t_review', (table) => {
    table.string('user_name').notNullable();
    table.string('work_id').notNullable();
    table.integer('rating'); // 用户评分1-5
    table.string('review_text'); // 用户评价文字
    table.timestamps(true, true); // 时间戳created_at, updated_at
    table.string('progress'); // ['marked', 'listening', 'listened', 'postponed', null]
    table.foreign('user_name').references('name').inTable('t_user').onDelete('CASCADE'); // FOREIGN KEY 
    table.foreign('work_id').references('id').inTable('t_work').onDelete('CASCADE'); // FOREIGN KEY 
    table.primary(['user_name', 'work_id']); // PRIMARY KEY
  })

打开程序时若检测到数据库不存在，则运行createSchema()。除此之外，从网页发起扫描，或终端中运行npm run scan，均会首先运行createSchema()，当第一个表已经存在时则跳过创建。

连接参数

database/db.js中为主程序的连接参数。

const knex = require('knex')({
  client: 'sqlite3', // 数据库类型
  useNullAsDefault: true,
  connection: { // 连接参数
    filename: path.join(databaseFolderDir, 'db.sqlite3'),
  },
  acquireConnectionTimeout: 40000, // 连接计时器
  pool: {
    afterCreate: (conn, cb) => {
      conn.run('PRAGMA foreign_keys = ON', cb)
    }
  }
});

Knex对于SQLite默认max pool size = 1，原因是Knex开发者认为对于SQLite pool size大于1时会有一些问题。写程序时并不需要考虑这一点，Knex会将所有数据库指令serialize。如果开发时遇到以下著名问题：

Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?

基本上都是由于代码不正确导致的。具体来讲，无法为transaction取得连接从而按照acquireConnectionTimeout的设置5秒超时。本程序acquireConnectionTimeout改为了40秒，但建议开发期间调低这一数值以排查阻塞错误。函数调用层数比较多时应当特别注意transaction，如果遇到问题可以复用Knex transaction provider，具体见Knex文档中关于tranaction的说明。具体到本项目而言，这个commit是一个修复阻塞、复用transaction provider例子。

这个issue对于理解这类问题比较有帮助，特别注意这里及其回复。

另外一个文件database/knexfile.js只用于程序自带升级系统和knex-migrate。

迁移

本程序采用采用大幅修改过的knex-migrate。数据库迁移文件位于database/migrations内，升级时自动逐个运行新增的迁移文件。每完成一个迁移文件，就在数据库中的knex_migrations表中记录完成的迁移文件。这一做法与knex自己的migration API是一致的，knex_migrations表的字段也是一致的。

目前运行迁移时没有开启foreign key，因此请注意ON DELETE ON UPDATE等语句在迁移期间无效，需要手动维护一致性。迁移的数据库连接是与主程序相独立的，迁移结束时会自行关闭连接。每一个迁移文件都处在一个transcation内（在这里控制），因此抛出错误会自动回滚并取消当前迁移文件。Knex会将nested transaction自动转换为SAVEPOINT，这一点内部细节在写迁移文件时一般不需要考虑。请注意SQLite不支持在transaction内部修改PRAGMA，因此在迁移文件内开关外键是无效的。（先前的版本写的时候没有发现这一点，不过并不会造成问题。）

此外，首次运行程序时没有数据库，初始化时会自动在knex_migrations中标记当前版本的所有迁移文件为已完成，跳过迁移，这样就避免了冲突。

使用源码运行的开发者可以使用knex-migrate your_migration_name down回滚一次数据库迁移。具体详见knex-migrate文档。由于knexfile.js是用于程序自带的升级系统，而程序是从app.js运行的，所以filename字段的路径是./sqlite/db.sqlite3。如果手动运行knex-migrate，可以cd到database目录并将这个路径改为../sqlite/db.sqlite3，然后执行knex-migrate your_migration_name your_command，或者手动指定cwd，knexfile，migrations等参数。

注意SQLite不支持绝大多数ALTER TABLE语句，写数据库迁移文件前请仔细阅读官方文档中关于ALTER TABLE的说明。

调试

Knex的调试方法是在环境变量中指定DEBUG=knex:query，即可看到非常详细的全过程。写SQL可以预先在DB Browser for SQLite等软件中调试。