本次问题讨论1.md

锁表原因？

我想了一下最可能造成此次锁表的原因。你是程序在线的时候执行的knex-migrate up吗？那样这次是有可能锁死。 如果你cherry-pick了 935681b14f4f41a6af27977d5cc1cae6c2f5817a 这个是为了解决之前打错字的情况，也为了这次你执行knex-migrate up能正确运行而不至于直接挂在前面的错误文件上。

当你执行knex-migrate的时候，它会读取knex_migrations表，对比没有执行过的migration文件，然后执行并把完成的migration记录文件名到这个表里面。

也就是说本次你应该是看到执行了两个migration：
20210223200240_drop_not_null_constraints.js
20210304101412_fix_view_userMetatdata.js

然后前者20210223200240_drop_not_null_constraints.js是个什么东西呢，为了DROP t_work表里面大量多余的NOT NULL constraints，这个是上游写的bug。 然而SQLite非常麻烦的地方在于它不支持绝大多数ALTER TABLE，导致无论用什么ORM或者Query Builder最后都是得自己手写大段重建整个表的语句，具体你可以看那个文件里做了什么。 这个非常繁琐的步骤也是符合SQLite文档的：https://sqlite.org/lang_altertable.html

然而如果重建过程中或者以后导致数据库锁死，那就只有重启了。

不过现在应该不需要做什么，数据库应该是正常的。

数据库迁移的补充解释

关于Kikoeru内部的数据库迁移逻辑，请看这篇文档。
如果你看到knex_migrations表里面有两个只差一个字母的文件：

20210223200240_drop_not_null_constrains.js
20210223200240_drop_not_null_constraints.js

并不会造成问题，但是不放心你可以删掉20210223200240_drop_not_null_constrains.js这个记录。具体原因不解释了，其实是你fork unstable上线的时候我还有没测试完的草稿……

关于你提的VIEW问题

VIEW本身不存储数据，它是完全等价于subquery的。 执行

EXPLAIN QUERY PLAN SELECT * FROM userMetadata
	WHERE user_name = 'admin'
	ORDER BY userRating DESC, "release" DESC, id DESC;

和

EXPLAIN QUERY PLAN SELECT * FROM (SELECT t_work.id,
      t_work.title,
      json_object('id', t_work.circle_id, 'name', t_circle.name) AS circleObj,
      t_work.release,
      t_work.review_count,
      t_work.dl_count,
      t_work.nsfw,
      userrate.userRating,
      userrate.review_text,
      userrate.progress,
      userrate.updated_at,
      json_object('vas', json_group_array(json_object('id', t_va.id, 'name', t_va.name))) AS vaObj,
      userrate.user_name
    FROM t_work
    JOIN t_circle on t_circle.id = t_work.circle_id
    JOIN r_va_work on r_va_work.work_id = t_work.id
    join t_va on t_va.id = r_va_work.va_id
    JOIN (
        SELECT t_review.work_id,
          t_review.rating AS userRating,
          t_review.review_text,
          t_review.progress,
          strftime('%Y-%m-%d %H-%M-%S', t_review.updated_at, 'localtime') AS updated_at,
          t_review.user_name
        FROM t_review
          JOIN t_work on t_work.id = t_review.work_id
        ) AS userrate
    ON userrate.work_id = t_work.id
    GROUP BY t_work.id, userrate.user_name
    ) AS subquery
    WHERE user_name = 'admin'
    ORDER BY userRating DESC, "release" DESC, id DESC;

可以看到QUERY PLAN是完全一致的。

我看主页加载的时候回请求两次 works 接口，是重复了吗
Works 的 this.reset() 被触发了一次

不过这个问题不重要，现在我看到works的请求等待时间在3-4秒，静态视图已经创建成功

需不需要我这边脱敏的生产数据库做测试？

还可以更快，就是把静态视图直接转换成一个同名的大表，不过有个不小的缺陷是你每次添加作品都得自己跑SQL手动更新这个大表。还有就是，你的SQLite数据库大小可能会翻倍，增加的大小约等于t_work表。不过倒是可以不用改后端代码了。

• 创建：

CREATE TABLE staticMetadata_new (
  id INTEGER,
  title TEXT,
  circle_id INTEGER,
  name TEXT,
  circleObj TEXT,
  nsfw boolean,
  "release" TEXT,
  dl_count INTEGER,
  price INTEGER,
  review_count INTEGER,
  rate_count INTEGER,
  rate_average_2dp REAL,
  rate_count_detail TEXT,
  "rank" TEXT,
  vaObj TEXT,
  tagObj TEXT,
  PRIMARY KEY(id)
);

INSERT INTO staticMetadata_new SELECT * FROM staticMetadata;
DROP VIEW IF EXISTS staticMetadata;
ALTER TABLE staticMetadata_new RENAME TO staticMetadata;

• 更新

INSERT OR IGNORE INTO staticMetadata
    SELECT baseQueryWithVA.*,
      json_object('tags', json_group_array(json_object('id', t_tag.id, 'name', t_tag.name))) AS tagObj
    FROM (
      SELECT baseQuery.*,
        json_object('vas', json_group_array(json_object('id', t_va.id, 'name', t_va.name))) AS vaObj
      FROM (
        SELECT t_work.id, 
          t_work.title,
          t_work.circle_id,
          t_circle.name,
          json_object('id', t_work.circle_id, 'name', t_circle.name) AS circleObj,
          t_work.nsfw,
          t_work.release,
          t_work.dl_count,
          t_work.price,
          t_work.review_count,
          t_work.rate_count,
          t_work.rate_average_2dp,
          t_work.rate_count_detail,
          t_work.rank
        FROM t_work
        JOIN t_circle ON t_circle.id = t_work.circle_id
      ) AS baseQuery
      JOIN r_va_work ON r_va_work.work_id = baseQuery.id
      JOIN t_va ON t_va.id = r_va_work.va_id
      GROUP BY baseQuery.id
    ) AS baseQueryWithVA
    LEFT JOIN r_tag_work ON r_tag_work.work_id = baseQueryWithVA.id
    LEFT JOIN t_tag ON t_tag.id = r_tag_work.tag_id
    GROUP BY baseQueryWithVA.id

• 还原成视图

DROP TABLE staticMetadata;

CREATE VIEW staticMetadata AS
    SELECT baseQueryWithVA.*,
      json_object('tags', json_group_array(json_object('id', t_tag.id, 'name', t_tag.name))) AS tagObj
    FROM (
      SELECT baseQuery.*,
        json_object('vas', json_group_array(json_object('id', t_va.id, 'name', t_va.name))) AS vaObj
      FROM (
        SELECT t_work.id, 
          t_work.title,
          t_work.circle_id,
          t_circle.name,
          json_object('id', t_work.circle_id, 'name', t_circle.name) AS circleObj,
          t_work.nsfw,
          t_work.release,
          t_work.dl_count,
          t_work.price,
          t_work.review_count,
          t_work.rate_count,
          t_work.rate_average_2dp,
          t_work.rate_count_detail,
          t_work.rank
        FROM t_work
        JOIN t_circle ON t_circle.id = t_work.circle_id
      ) AS baseQuery
      JOIN r_va_work ON r_va_work.work_id = baseQuery.id
      JOIN t_va ON t_va.id = r_va_work.va_id
      GROUP BY baseQuery.id
    ) AS baseQueryWithVA
    LEFT JOIN r_tag_work ON r_tag_work.work_id = baseQueryWithVA.id
    LEFT JOIN t_tag ON t_tag.id = r_tag_work.tag_id
    GROUP BY baseQueryWithVA.id

我看主页加载的时候回请求两次 works 接口，是重复了吗
Works 的 this.reset() 被触发了一次

要正确地解决主页问题需要换控件重写，不过最近大概是没空了。最主要的问题是：由于你的站点资源非常多，需要的是像Twitter那样的Timeline marker，也就是说点进任何一个作品、返回以后都能回到原来的位置；并且要可以向上向下两个方向无限滚动。现在的问题是无论点进哪个作品，返回，都会直接跳回顶部，这非常不方便。
或者也可以直接重写成传统的分页。我觉得这种比较合理，因为页数信息对用户来说是有意义的。
要改的地方有点多，最近不是很有时间。

需不需要我这边脱敏的生产数据库做测试？

我觉得目前应该不需要。

现在这种滚动模式确实对用户很不友好，每次想看只能从头往下拉，有的时候想要跳到具体某一页只能靠不停下拉，有一部分作品始终处于中间位置很难被用户翻到

刚刚一位某+坛友兼b站up主二州牧联系了我，他是一位b站远古音声区up主，也是我的asmr启蒙人。

他希望能够参与kikoeru的开发，我跟他说了一下我fork的项目地址和大佬的某+ id，也许我们可以详聊？

edit: 可能是我理解错了, 二州牧大佬似乎是想找我探讨资源整理问题

我想最需要的大概是按照REST规范重构API。现在像之前写的offload还是补丁套补丁再套补丁。还有很多别的问题：

• 封面/api/cover应该返回的不是封面本身而是封面地址。
• 之前提到的无法利用的注入漏洞
• 路由匹配也非常随意

等过两天打算写一个API草稿。