Memos 数据库启动过程源码分析
因为碰到了 Memos Heatmap 显示统计数据不准确的问题(comment memos 没有被纳入统计),为了理解 GetMemosList
的工作原理需要阅读源码理解清楚 Memos server 端特别是涉及到数据库的逻辑是什么样。这篇文章就是记录一下 Memos 数据库相关的设计和源码分析。
memos 数据库初始化
// 1. memos service 启动逻辑
main --> cmd.Execute --> rootCmd.Execute --> db.NewDBDriver --> dbDriver.Migrate --> store.New --> server.NewServer --> s.Start
// 2. 配置文件解析逻辑
// profile 初始化,memos.go `init` 函数中完成,主要是通过 `viper` 全局变量绑定命令行参数
// 3. 数据库初始化
// 数据库初始化,以 sqlite 为例(memos 在 store 目录中实现了多种数据库支持)
db.NewDBDriver --> sqlite.NewDB(profile) --> dbDriver.Migrate -->
(1) sqlite db 文件不存在:--> applyLatestSchema (即执行 sqlite/migration/LATEST__SCHEMA.sql)
(2) sqlite db 文件存在:--> FindMigrationHistoryList --> applyMigrationForMinorVersion(即执行 sqlite/migration/${minor-version}/*.sql)
// 4. RPC server 启动逻辑
// Server 启动,至此数据库启动已经完成暂不做后面的源码分析
memos 库表设计
根据 memos 数据库初始化的流程分析,可以发现 memos 数据库表设计主要在 LATEST__SCHEMA.sql
文件中实现,具体可以参考 ${database type}/migration/prod/LATEST__SCHEMA.sql
文件:
-- drop all tables first
DROP TABLE IF EXISTS `migration_history`;
DROP TABLE IF EXISTS `system_setting`;
DROP TABLE IF EXISTS `user`;
DROP TABLE IF EXISTS `user_setting`;
DROP TABLE IF EXISTS `memo`;
DROP TABLE IF EXISTS `memo_organizer`;
DROP TABLE IF EXISTS `memo_relation`;
DROP TABLE IF EXISTS `resource`;
DROP TABLE IF EXISTS `tag`;
DROP TABLE IF EXISTS `activity`;
DROP TABLE IF EXISTS `storage`;
DROP TABLE IF EXISTS `idp`;
DROP TABLE IF EXISTS `inbox`;
DROP TABLE IF EXISTS `webhook`;
-- migration_history
CREATE TABLE `migration_history` (
`version` VARCHAR(256) NOT NULL PRIMARY KEY,
`created_ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- system_setting
CREATE TABLE `system_setting` (
`name` VARCHAR(256) NOT NULL PRIMARY KEY,
`value` LONGTEXT NOT NULL,
`description` TEXT NOT NULL
);
-- user
CREATE TABLE `user` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`created_ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`row_status` VARCHAR(256) NOT NULL DEFAULT 'NORMAL',
`username` VARCHAR(256) NOT NULL UNIQUE,
`role` VARCHAR(256) NOT NULL DEFAULT 'USER',
`email` VARCHAR(256) NOT NULL DEFAULT '',
`nickname` VARCHAR(256) NOT NULL DEFAULT '',
`password_hash` VARCHAR(256) NOT NULL,
`avatar_url` LONGTEXT NOT NULL
);
-- user_setting
CREATE TABLE `user_setting` (
`user_id` INT NOT NULL,
`key` VARCHAR(256) NOT NULL,
`value` LONGTEXT NOT NULL,
UNIQUE(`user_id`,`key`)
);
-- memo
CREATE TABLE `memo` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`creator_id` INT NOT NULL,
`created_ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`row_status` VARCHAR(256) NOT NULL DEFAULT 'NORMAL',
`content` TEXT NOT NULL,
`visibility` VARCHAR(256) NOT NULL DEFAULT 'PRIVATE'
);
-- memo_organizer
CREATE TABLE `memo_organizer` (
`memo_id` INT NOT NULL,
`user_id` INT NOT NULL,
`pinned` INT NOT NULL DEFAULT '0',
UNIQUE(`memo_id`,`user_id`)
);
-- memo_relation
CREATE TABLE `memo_relation` (
`memo_id` INT NOT NULL,
`related_memo_id` INT NOT NULL,
`type` VARCHAR(256) NOT NULL,
UNIQUE(`memo_id`,`related_memo_id`,`type`)
);
-- resource
CREATE TABLE `resource` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`creator_id` INT NOT NULL,
`created_ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`filename` TEXT NOT NULL,
`blob` MEDIUMBLOB,
`external_link` TEXT NOT NULL,
`type` VARCHAR(256) NOT NULL DEFAULT '',
`size` INT NOT NULL DEFAULT '0',
`internal_path` VARCHAR(256) NOT NULL DEFAULT '',
`memo_id` INT DEFAULT NULL
);
-- tag
CREATE TABLE `tag` (
`name` VARCHAR(256) NOT NULL,
`creator_id` INT NOT NULL,
UNIQUE(`name`,`creator_id`)
);
-- activity
CREATE TABLE `activity` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`creator_id` INT NOT NULL,
`created_ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`type` VARCHAR(256) NOT NULL DEFAULT '',
`level` VARCHAR(256) NOT NULL DEFAULT 'INFO',
`payload` TEXT NOT NULL
);
-- storage
CREATE TABLE `storage` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(256) NOT NULL,
`type` VARCHAR(256) NOT NULL,
`config` TEXT NOT NULL
);
-- idp
CREATE TABLE `idp` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` TEXT NOT NULL,
`type` TEXT NOT NULL,
`identifier_filter` VARCHAR(256) NOT NULL DEFAULT '',
`config` TEXT NOT NULL
);
-- inbox
CREATE TABLE `inbox` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`created_ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`sender_id` INT NOT NULL,
`receiver_id` INT NOT NULL,
`status` TEXT NOT NULL,
`message` TEXT NOT NULL
);
-- webhook
CREATE TABLE `webhook` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`created_ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`row_status` VARCHAR(256) NOT NULL DEFAULT 'NORMAL',
`creator_id` INT NOT NULL,
`name` TEXT NOT NULL,
`url` TEXT NOT NULL
);
可以看出 memos 的库表设计比较简单,唯一值得解释一下的是为跟 user 强相关的数据采用了 UNIQUE
进行了唯一索引的约束。