PbootCMS各个版本升级sql脚本(SQLite版本)
Pbootcms 2.x升级Pbootcms 3.0脚本 sqlite-3.0.0-update.sql
-- ----------------------------
-- Sqlite数据库升级脚本
-- 适用于PbootCMS 2.1.1版本升级至3.0.0
-- ----------------------------
--
-- 创建会员菜单
--
DELETE FROM `ay_menu` WHERE mcode='M1001';
DELETE FROM `ay_menu_action` WHERE mcode='M1001';
INSERT INTO `ay_menu` (`mcode`,`pcode`,`name`,`url`,`sorting`,`status`,`shortcut`,`ico`,`create_user`,`update_user`,`create_time`,`update_time`) SELECT
'M1001','0','会员中心','/admin/M1001/index','600','1','0','fa-user-o','admin','admin','2019-10-04 08:25:41','2019-10-04 08:26:23';
DELETE FROM `ay_menu` WHERE mcode='M1002';
DELETE FROM `ay_menu_action` WHERE mcode='M1002';
INSERT INTO `ay_menu` (`mcode`,`pcode`,`name`,`url`,`sorting`,`status`,`shortcut`,`ico`,`create_user`,`update_user`,`create_time`,`update_time`) SELECT
'M1002','M1001','会员等级','/admin/MemberGroup/index','601','1','0','fa-signal','admin','admin','2019-10-04 08:25:41','2019-10-04 08:26:23';
INSERT INTO `ay_menu_action` (`mcode`,`action`) VALUES('M1002','add');
INSERT INTO `ay_menu_action` (`mcode`,`action`) VALUES('M1002','del');
INSERT INTO `ay_menu_action` (`mcode`,`action`) VALUES('M1002','mod');
DELETE FROM `ay_menu` WHERE mcode='M1003';
DELETE FROM `ay_menu_action` WHERE mcode='M1003';
INSERT INTO `ay_menu` (`mcode`,`pcode`,`name`,`url`,`sorting`,`status`,`shortcut`,`ico`,`create_user`,`update_user`,`create_time`,`update_time`) SELECT
'M1003','M1001','会员字段','/admin/MemberField/index','602','1','0','fa-wpforms','admin','admin','2019-10-04 08:25:41','2019-10-04 08:26:23';
INSERT INTO `ay_menu_action` (`mcode`,`action`) VALUES('M1003','add');
INSERT INTO `ay_menu_action` (`mcode`,`action`) VALUES('M1003','del');
INSERT INTO `ay_menu_action` (`mcode`,`action`) VALUES('M1003','mod');
DELETE FROM `ay_menu` WHERE mcode='M1004';
DELETE FROM `ay_menu_action` WHERE mcode='M1004';
INSERT INTO `ay_menu` (`mcode`,`pcode`,`name`,`url`,`sorting`,`status`,`shortcut`,`ico`,`create_user`,`update_user`,`create_time`,`update_time`) SELECT
'M1004','M1001','会员管理','/admin/Member/index','603','1','0','fa-users','admin','admin','2019-10-04 08:25:41','2019-10-04 08:26:23';
INSERT INTO `ay_menu_action` (`mcode`,`action`) VALUES('M1004','add');
INSERT INTO `ay_menu_action` (`mcode`,`action`) VALUES('M1004','del');
INSERT INTO `ay_menu_action` (`mcode`,`action`) VALUES('M1004','mod');
DELETE FROM `ay_menu` WHERE mcode='M1005';
DELETE FROM `ay_menu_action` WHERE mcode='M1005';
INSERT INTO `ay_menu` (`mcode`,`pcode`,`name`,`url`,`sorting`,`status`,`shortcut`,`ico`,`create_user`,`update_user`,`create_time`,`update_time`) SELECT
'M1005','M1001','文章评论','/admin/MemberComment/index','604','1','0','fa-commenting-o','admin','admin','2019-10-04 08:25:41','2019-10-04 08:26:23';
INSERT INTO `ay_menu_action` (`mcode`,`action`) VALUES('M1005','del');
--
-- 创建会员等级表
--
DROP TABLE IF EXISTS "ay_member_group";
CREATE TABLE "ay_member_group" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"gcode" TEXT(20) NOT NULL,
"gname" TEXT(100) NOT NULL,
"description" TEXT(200) NOT NULL,
"status" TEXT(1) NOT NULL,
"lscore" INTEGER(10) NOT NULL DEFAULT 0,
"uscore" INTEGER(10) NOT NULL DEFAULT 0,
"create_user" TEXT(30) NOT NULL,
"update_user" TEXT(30) NOT NULL,
"create_time" TEXT NOT NULL,
"update_time" TEXT NOT NULL
);
CREATE UNIQUE INDEX "ay_member_group_gcode" ON "ay_member_group" ("gcode" ASC);
INSERT INTO "ay_member_group" VALUES (1, 1, '初级会员', '初级会员具备基本的权限', 1, 0, 999, 'admin', 'admin', '2020-06-25 00:00:00', '2020-06-25 00:00:00');
INSERT INTO "ay_member_group" VALUES (2, 2, '中级会员', '中级会员具备部分特殊权限', 1, 1000, 9999, 'admin', 'admin', '2020-06-25 00:00:00', '2020-06-25 00:00:00');
INSERT INTO "ay_member_group" VALUES (3, 3, '高级会员', '高级会员具备全部特殊权限', 1, 10000, 9999999999, 'admin', 'admin', '2020-06-25 00:00:00', '2020-06-25 00:00:00');
--
-- 创建会员表
--
DROP TABLE IF EXISTS "ay_member";
CREATE TABLE "ay_member" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"ucode" TEXT(20) NOT NULL,
"username" TEXT(100) NOT NULL,
"nickname" TEXT(100) NOT NULL,
"password" TEXT(32) NOT NULL,
"headpic" TEXT(200) NOT NULL,
"status" TEXT(1) NOT NULL,
"gid" TEXT(20) NOT NULL,
"wxid" TEXT(50) NOT NULL,
"qqid" TEXT(50) NOT NULL,
"wbid" TEXT(50) NOT NULL,
"score" INTEGER(10) NOT NULL DEFAULT 0,
"register_time" TEXT NOT NULL,
"login_count" INTEGER(10) NOT NULL DEFAULT 0,
"last_login_ip" TEXT(11) NOT NULL,
"last_login_time" TEXT(11) NOT NULL
);
CREATE INDEX "ay_member_gid" ON "ay_member" ("gid" ASC);
CREATE INDEX "ay_member_wxid" ON "ay_member" ("wxid" ASC);
CREATE INDEX "ay_member_qqid" ON "ay_member" ("qqid" ASC);
CREATE INDEX "ay_member_wbid" ON "ay_member" ("wbid" ASC);
CREATE UNIQUE INDEX "ay_member_ucode" ON "ay_member" ("ucode" ASC);
CREATE UNIQUE INDEX "ay_member_username" ON "ay_member" ("username" ASC);
--
-- 创建会员字段表
--
DROP TABLE IF EXISTS "ay_member_field";
CREATE TABLE "ay_member_field" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"name" TEXT(30) NOT NULL,
"length" INTEGER(10) NOT NULL,
"required" TEXT(1) NOT NULL,
"description" TEXT(30) NOT NULL,
"sorting" INTEGER(10) NOT NULL,
"status" TEXT(1) NOT NULL,
"create_user" TEXT(30) NOT NULL,
"update_user" TEXT(30) NOT NULL,
"create_time" TEXT NOT NULL,
"update_time" TEXT NOT NULL
);
INSERT INTO "ay_member_field" VALUES (1, 'sex', 2, 0, '性别', 255, 1, 'admin', 'admin', '2020-06-25 00:00:00', '2020-06-25 00:00:00');
INSERT INTO "ay_member_field" VALUES (2, 'birthday', 20, 0, '生日', 255, 1, 'admin', 'admin', '2020-06-25 00:00:00', '2020-06-25 00:00:00');
INSERT INTO "ay_member_field" VALUES (3, 'qq', 15, 0, 'QQ', 255, 1, 'admin', 'admin', '2020-06-25 00:00:00', '2020-06-25 00:00:00');
--
-- 创建会员表字段
--
ALTER TABLE ay_member ADD COLUMN sex TEXT(2) NOT NULL DEFAULT '';
ALTER TABLE ay_member ADD COLUMN birthday TEXT(20) NOT NULL DEFAULT '';
ALTER TABLE ay_member ADD COLUMN telephone TEXT(20) NOT NULL DEFAULT '';
ALTER TABLE ay_member ADD COLUMN email TEXT(50) NOT NULL DEFAULT '';
ALTER TABLE ay_member ADD COLUMN qq TEXT(15) NOT NULL DEFAULT '';
--
-- 创建内容及栏目查看字段
--
ALTER TABLE ay_content_sort ADD COLUMN gtype TEXT(1) NOT NULL DEFAULT '4';
ALTER TABLE ay_content_sort ADD COLUMN gid TEXT(20) NOT NULL DEFAULT '';
ALTER TABLE ay_content_sort ADD COLUMN gnote TEXT(100) NOT NULL DEFAULT '';
ALTER TABLE ay_content ADD COLUMN gtype TEXT(1) NOT NULL DEFAULT '4';
ALTER TABLE ay_content ADD COLUMN gid TEXT(20) NOT NULL DEFAULT '';
ALTER TABLE ay_content ADD COLUMN gnote TEXT(100) NOT NULL DEFAULT '';
--
-- 创建会员文章评论表
--
DROP TABLE IF EXISTS "ay_member_comment";
CREATE TABLE "ay_member_comment" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"pid" INTEGER(10) NOT NULL DEFAULT 0,
"contentid" INTEGER(10) NOT NULL,
"comment" TEXT(1000) NOT NULL,
"uid" INTEGER(10) NOT NULL,
"puid" INTEGER(10) NOT NULL,
"likes" INTEGER(10) NOT NULL DEFAULT 0,
"oppose" INTEGER(10) NOT NULL DEFAULT 0,
"status" TEXT(1) NOT NULL,
"user_ip" TEXT(11) NOT NULL,
"user_os" TEXT(30) NOT NULL,
"user_bs" TEXT(30) NOT NULL,
"create_time" TEXT NOT NULL,
"update_user" TEXT(30) NOT NULL,
"update_time" TEXT NOT NULL
);
CREATE INDEX "ay_member_comment_pid" ON "ay_member_comment" ("pid" ASC);
CREATE INDEX "ay_member_comment_contentid" ON "ay_member_comment" ("contentid" ASC);
CREATE INDEX "ay_member_comment_uid" ON "ay_member_comment" ("uid" ASC);
CREATE INDEX "ay_member_comment_puid" ON "ay_member_comment" ("puid" ASC);
PbootCMS 3.0.0版本升级至3.0.0.20200706脚本 sqlite-3.0.0.20200706-update.sql
-- ----------------------------
-- Sqlite数据库升级脚本
-- 适用于PbootCMS 3.0.0版本升级至3.0.0.20200706
-- ----------------------------
ALTER TABLE ay_member ADD COLUMN useremail TEXT(50) NOT NULL DEFAULT '';
ALTER TABLE ay_member ADD COLUMN usermobile TEXT(11) NOT NULL DEFAULT '';
ALTER TABLE ay_member ADD COLUMN activation TEXT(1) NOT NULL DEFAULT 1;
ALTER TABLE ay_message ADD COLUMN uid INTEGER(10) NOT NULL DEFAULT 0;
CREATE INDEX "ay_member_useremail" ON "ay_member" ("useremail" ASC);
CREATE INDEX "ay_member_usermobile" ON "ay_member" ("usermobile" ASC);
PbootCMS 3.0.0版本升级至3.0.6 sqlite-3.0.6-update.sql
-- ----------------------------
-- Sqlite数据库升级脚本
-- 适用于PbootCMS 3.0.0版本升级至3.0.6
-- ----------------------------
--
-- 新增多图标题字段
--
ALTER TABLE ay_content ADD COLUMN picstitle TEXT(1000) NOT NULL DEFAULT '';
--
-- 栏目新增三个描述备用字段
--
ALTER TABLE ay_content_sort ADD COLUMN def1 TEXT(1000) NOT NULL DEFAULT '';
ALTER TABLE ay_content_sort ADD COLUMN def2 TEXT(1000) NOT NULL DEFAULT '';
ALTER TABLE ay_content_sort ADD COLUMN def3 TEXT(1000) NOT NULL DEFAULT '';