数据库迁移方案（征求意见稿）

这里总结数据库重构迁移的需要完成的任务，会根据论坛数据库重构方案这个issue来更新。以下内容按不同的表来分别总结。有意见的也可以去那个issue里面提出～

目前的数据库结构见这里

分类总结

表名	改动意见
attachments	目前一共只有30个有效附件，考虑整体删除
boardinfo
borrow	考虑删除，重新设计
calendar	删除
captcha_codes
codes	删除
downloads	删除，重新安排
lzl	将fid改为pid，识别所属楼层
mainpage	删除
messages	只保留私信，删除系统通知，转移到notifications表
null
posts	将pid设置为全局唯一的识别变量，将正文中的各种链接进行重新处理
sign
sms	删除
test	可以删除
threads	将tid设置为全局唯一的识别变量
userinfo	增加uid来识别每个用户，增加birthday（生日）、student_id（学号）、name（姓名）、tel（电话），删除score
group	新建 group 表
gruop__user	group 和 user 多对多关系
privilege	新建权限表
notification	新建通知表，承担原 messages 表的通知职责，原 messages 表现在只承担站内信职责

啊我建议我们这样表示我们的迁移方案：下面是原来的数据库表结构，然后谁都不许改任何一个字儿，每一个需要完成的任务用一块新的 sql 代码来表示，比如要新建列就 Alter，新建表就 Create，改值就 Update。每个代码块儿认为在之前所有代码块儿依次执行后得到的数据库上执行。每个代码块儿前两行是注释，第一行表示它是第几个代码块儿，第二行表示它干了什么。第三行写下作者的名字吧方便blame（嗯这个 wiki 也是被 git 版本控制的自带 blame）。

表示这里用 SQL 的原因完全是为了说话比说人话更清楚，主要是拿来看的，没有任何拿去执行的意思，如果觉得其它语言比 SQL 更方便看的话欢迎换成其它语言～

-- 1
-- 原来的数据库结构
CREATE TABLE `attachments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  `path` text NOT NULL,
  `size` int(11) NOT NULL,
  `uploader` text NOT NULL,
  `ref` int(11) NOT NULL DEFAULT '0',
  `count` int(11) NOT NULL,
  `price` int(11) NOT NULL,
  `auth` int(11) NOT NULL,
  `time` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `boardinfo` (
  `bid` tinyint(4) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `bbstitle` varchar(20) DEFAULT NULL,
  `hide` tinyint(4) NOT NULL DEFAULT '0',
  `m1` varchar(45) DEFAULT NULL,
  `m2` varchar(45) DEFAULT NULL,
  `m3` varchar(45) DEFAULT NULL,
  `m4` varchar(45) DEFAULT NULL,
  `need` tinyint(4) DEFAULT NULL,
  KEY `bid` (`bid`)
);

CREATE TABLE `borrow` (
  `number` int(11) NOT NULL AUTO_INCREMENT,
  `type` tinyint(4) NOT NULL,
  `id` varchar(30) NOT NULL,
  `sex` varchar(2) DEFAULT NULL,
  `phone` varchar(13) NOT NULL,
  `height` int(11) DEFAULT NULL,
  `bike` varchar(30) DEFAULT NULL,
  `condition` varchar(50) DEFAULT NULL,
  `length` varchar(30) DEFAULT NULL,
  `hint` varchar(300) DEFAULT NULL,
  `timestamp` bigint(20) NOT NULL,
  `state` tinyint(4) NOT NULL,
  PRIMARY KEY (`number`),
  KEY `type` (`type`,`id`,`state`)
);

CREATE TABLE `calendar` (
  `year` varchar(5) NOT NULL,
  `month` varchar(3) NOT NULL,
  `day` varchar(3) NOT NULL,
  `time` varchar(8) NOT NULL,
  `title` varchar(20) DEFAULT NULL,
  `content` varchar(40) DEFAULT NULL
);

CREATE TABLE `captcha_codes` (
  `id` varchar(40) NOT NULL,
  `namespace` varchar(32) NOT NULL,
  `code` varchar(32) NOT NULL,
  `code_display` varchar(32) NOT NULL,
  `created` int(11) NOT NULL,
  PRIMARY KEY (`id`,`namespace`),
  KEY `created` (`created`)
);

CREATE TABLE `codes` (
  `id` int(11) NOT NULL,
  `code` varchar(10) NOT NULL,
  `times` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code_2` (`code`),
  KEY `code` (`code`)
);

CREATE TABLE `downloads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(300) NOT NULL,
  `url` varchar(300) NOT NULL,
  `times` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
);

CREATE TABLE `lzl` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fid` int(11) NOT NULL,
  `author` text NOT NULL,
  `text` text NOT NULL,
  `time` int(11) NOT NULL,
  `visible` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
);

CREATE TABLE `mainpage` (
  `number` int(11) NOT NULL AUTO_INCREMENT,
  `id` tinyint(4) NOT NULL,
  `field1` varchar(300) DEFAULT NULL,
  `field2` varchar(300) DEFAULT NULL,
  `field3` varchar(300) DEFAULT NULL,
  `field4` varchar(300) DEFAULT NULL,
  `field5` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`number`)
);

CREATE TABLE `messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sender` text NOT NULL,
  `receiver` text NOT NULL,
  `text` text NOT NULL,
  `time` int(11) NOT NULL,
  `hasread` int(11) NOT NULL,
  `rbid` int(11) NOT NULL,
  `rtid` int(11) NOT NULL,
  `rpid` int(11) NOT NULL,
  `ruser` text NOT NULL,
  `rmsg` text NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `null` (
  `number` int(11) NOT NULL AUTO_INCREMENT,
  `bid` tinyint(4) NOT NULL,
  `tid` int(11) NOT NULL,
  `pid` int(11) NOT NULL,
  `title` text,
  `text` longtext,
  `author` varchar(50) NOT NULL,
  `deleter` varchar(50) NOT NULL,
  `replytime` bigint(20) NOT NULL,
  `updatetime` bigint(20) NOT NULL,
  `deletetime` bigint(20) NOT NULL,
  `replyip` varchar(20) NOT NULL,
  `deleteip` varchar(20) NOT NULL,
  PRIMARY KEY (`number`,`bid`,`tid`,`pid`)
);

CREATE TABLE `posts` (
  `bid` tinyint(4) NOT NULL,
  `tid` int(11) NOT NULL,
  `pid` int(11) NOT NULL,
  `fid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` tinytext,
  `author` varchar(30) DEFAULT NULL,
  `text` longtext,
  `ishtml` tinytext NOT NULL,
  `attachs` text NOT NULL,
  `replytime` bigint(20) DEFAULT NULL,
  `updatetime` bigint(20) DEFAULT NULL,
  `sig` tinyint(4) DEFAULT NULL,
  `type` tinytext,
  `ip` varchar(20) DEFAULT NULL,
  `lzl` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`fid`),
  KEY `bid` (`bid`),
  KEY `tid` (`tid`),
  KEY `fid` (`fid`)
);

CREATE TABLE `sign` (
  `year` int(11) NOT NULL,
  `month` tinyint(4) NOT NULL,
  `day` tinyint(4) NOT NULL,
  `hour` tinyint(4) NOT NULL,
  `minute` tinyint(4) NOT NULL,
  `second` tinyint(4) NOT NULL,
  `week` tinyint(4) NOT NULL,
  `username` varchar(30) NOT NULL,
  KEY `username` (`username`)
);

CREATE TABLE `sms` (
  `number` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(30) NOT NULL,
  `phone` varchar(13) NOT NULL,
  `text` varchar(300) NOT NULL,
  `ip` varchar(20) NOT NULL,
  `timestamp` bigint(20) NOT NULL,
  PRIMARY KEY (`number`)
);

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `username` varchar(30) NOT NULL,
  `message` varchar(100) NOT NULL,
  `field` varchar(100) NOT NULL,
  `text` varchar(100) NOT NULL
);

CREATE TABLE `threads` (
  `bid` tinyint(4) NOT NULL,
  `tid` int(11) NOT NULL,
  `title` tinytext,
  `author` varchar(30) DEFAULT NULL,
  `replyer` varchar(30) DEFAULT NULL,
  `click` int(11) DEFAULT NULL,
  `reply` int(11) DEFAULT NULL,
  `guesture` tinyint(4) DEFAULT NULL,
  `extr` tinyint(4) DEFAULT NULL,
  `top` tinyint(4) DEFAULT NULL,
  `locked` tinyint(4) DEFAULT NULL,
  `timestamp` bigint(20) DEFAULT NULL,
  `postdate` varchar(30) DEFAULT NULL,
  KEY `bid` (`bid`)
);

CREATE TABLE `userinfo` (
  `username` varchar(30) NOT NULL,
  `password` varchar(45) NOT NULL,
  `token` varchar(60) DEFAULT NULL,
  `tokentime` bigint(20) unsigned DEFAULT NULL,
  `sex` varchar(2) NOT NULL,
  `icon` tinytext,
  `intro` varchar(330) DEFAULT NULL,
  `sig1` varchar(330) DEFAULT NULL,
  `sig2` varchar(330) DEFAULT NULL,
  `sig3` varchar(330) DEFAULT NULL,
  `hobby` varchar(50) DEFAULT NULL,
  `qq` varchar(12) DEFAULT NULL,
  `mail` varchar(50) DEFAULT NULL,
  `place` varchar(50) DEFAULT NULL,
  `regdate` varchar(12) DEFAULT NULL,
  `lastdate` varchar(12) DEFAULT NULL,
  `lastip` varchar(16) DEFAULT NULL,
  `star` smallint(3) unsigned DEFAULT NULL,
  `score` int(11) NOT NULL DEFAULT '0',
  `post` smallint(5) unsigned DEFAULT NULL,
  `reply` smallint(5) unsigned DEFAULT NULL,
  `water` smallint(5) unsigned DEFAULT NULL,
  `sign` smallint(5) unsigned DEFAULT NULL,
  `rights` tinyint(4) unsigned DEFAULT NULL,
  `newmsg` tinyint(3) unsigned DEFAULT NULL,
  `extr` tinyint(3) unsigned DEFAULT NULL,
  `lastpost` tinytext,
  `nowboard` smallint(6) DEFAULT NULL,
  `onlinetype` varchar(10) DEFAULT NULL,
  `logininfo` varchar(500) DEFAULT NULL,
  `code` varchar(10) DEFAULT NULL,
  `other2` tinytext,
  `other3` tinytext,
  `other4` tinytext,
  `other5` tinytext,
  `other6` tinytext,
  KEY `username` (`username`),
  KEY `token` (`token`)
);

-- 2
-- 增加 uid 字段，用 uid 字段标记用户
ALTER TABLE userinfo ADD uid INT AUTO_INCREMENT PRIMARY KEY;

ALTER TABLE lzl ADD author_uid INT;
UPDATE lzl INNER JOIN userinfo ON lzl.author = userinfo.username SET lzl.author_uid = userinfo.uid;
ALTER TABLE lzl ADD FOREIGN KEY (author_uid) REFERENCES userinfo;

ALTER TABLE posts ADD author_uid INT;
UPDATE posts INNER JOIN userinfo ON posts.author = userinfo.username SET posts.author_uid = userinfo.uid;
ALTER TABLE posts ADD FOREIGN KEY (author_uid) REFERENCES userinfo;

ALTER TABLE threads ADD author_uid INT;
UPDATE threads INNER JOIN userinfo ON threads.author = userinfo.username SET threads.author_uid = userinfo.uid;
ALTER TABLE threads ADD FOREIGN KEY (author_uid) REFERENCES userinfo;

ALTER TABLE threads ADD replier_uid INT;
UPDATE threads INNER JOIN userinfo ON threads.replyer = userinfo.username SET threads.replier_uid = userinfo.uid;
ALTER TABLE threads ADD FOREIGN KEY (replier_uid) REFERENCES userinfo;

ALTEL TABLE sign ADD uid INT;
UPDATE sign INNER JOIN userinfo ON threads.author = userinfo.username SET sign.uid = userinfo.uid;
ALTER TABLE sign ADD FOREIGN KEY (uid) REFERENCES userinfo;

-- 3
-- 删除首页相关的没有用的表
-- Jeldor
DROP TABLE mainpage;
DROP TABLE downloads;
DROP TABLE codes;
DROP TABLE calendar;

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

数据库迁移方案（征求意见稿）

分类总结

Clone this wiki locally