sql/sqlserver/sqlserver.sql · mvphp/CBoard

代码拉取完成,页面将自动刷新

扫描微信二维码支付

取消

支付完成

richgiteeai

Watch

不关注关注所有动态仅关注版本发行动态关注但不提醒动态

2 Star 3 Fork 779

mvphp/CBoard

forked from tuiqiao/CBoard

代码 Issues 0 Pull Requests 0 Wiki 统计流水线

服务

加入 Gitee

与超过 1400万开发者一起发现、参与优秀开源项目,私有仓库也完全免费 :)

免费加入

已有帐号? 立即登录

文件

master

分支 (7)

标签 (6)

管理

master

branch-0.4.2

branch-0.4

branch-0.4.2-on-CM

branch-0.3

branch-0.2

branch-0.1

v0.4.1

v0.3.2

remove

v0.3.1

v0.2.1

v0.1

克隆/下载

HTTPS SSH SVN SVN+SSH 下载ZIP

提示

下载代码请复制以下命令到终端执行

为确保你提交的代码身份被 Gitee 正确识别,请执行以下命令完成配置

git config --global user.name userName 
git config --global user.email userEmail

初次使用 SSH 协议进行代码克隆、推送等操作时,需按下述提示完成 SSH 配置

1 生成 RSA 密钥

2 获取 RSA 公钥内容,并配置到 SSH公钥中

在 Gitee 上使用 SVN,请访问使用指南

使用 HTTPS 协议时,命令行会出现如下账号密码验证步骤。基于安全考虑,Gitee 建议配置并使用私人令牌替代登录密码进行克隆、推送等操作

Username for 'https://gitee.com': userName

Password for 'https://userName@gitee.com': # 私人令牌

分支 7

标签 6

CBoard

sql

sqlserver

sqlserver.sql

sqlserver.sql 4.90 KB

--
CREATE DATABASE CBoard;

CREATE TABLE dashboard_board (
 board_id bigint identity(1,1),
 user_id varchar(50) NOT NULL,
 category_id bigint DEFAULT NULL,
 board_name varchar(100) NOT NULL,
 layout_json text,
 PRIMARY KEY CLUSTERED (board_id)
);

CREATE TABLE dashboard_category (
 category_id bigint identity(1,1),
 category_name varchar(100) NOT NULL,
 user_id varchar(100) NOT NULL,
 PRIMARY KEY CLUSTERED (category_id)
);

CREATE TABLE dashboard_datasource (
 datasource_id bigint identity(1,1),
 user_id varchar(50) NOT NULL,
 source_name varchar(100) NOT NULL,
 source_type varchar(100) NOT NULL,
 config text,
 PRIMARY KEY CLUSTERED (datasource_id)
);

CREATE TABLE dashboard_widget (
 widget_id bigint identity(1,1),
 user_id varchar(100) NOT NULL,
 category_name varchar(100) DEFAULT NULL,
 widget_name varchar(100) DEFAULT NULL,
 data_json text,
 PRIMARY KEY CLUSTERED (widget_id)
);

CREATE TABLE dashboard_dataset (
 dataset_id bigint identity(1,1),
 user_id varchar(100) NOT NULL,
 category_name varchar(100) DEFAULT NULL,
 dataset_name varchar(100) DEFAULT NULL,
 data_json text,
 PRIMARY KEY CLUSTERED(dataset_id)
);

CREATE TABLE dashboard_user (
 user_id varchar(50) NOT NULL,
 login_name varchar(100) DEFAULT NULL,
 user_name varchar(100) DEFAULT NULL,
 user_password varchar(100) DEFAULT NULL,
 user_status varchar(100) DEFAULT NULL,
 PRIMARY KEY CLUSTERED(user_id)
);

INSERT INTO dashboard_user (user_id,login_name,user_name,user_password)
VALUES('1','admin','Administrator','ff9830c42660c1dd1942844f8069b74a');

CREATE TABLE dashboard_user_role (
 user_role_id bigint identity(1,1),
 user_id varchar(100) DEFAULT NULL,
 role_id varchar(100) DEFAULT NULL,
 PRIMARY KEY CLUSTERED(user_role_id)
);

CREATE TABLE dashboard_role (
 role_id varchar(100) NOT NULL,
 role_name varchar(100) DEFAULT NULL,
 user_id varchar(50) DEFAULT NULL,
 PRIMARY KEY CLUSTERED(role_id)
);

CREATE TABLE dashboard_role_res (
 role_res_id bigint identity(1,1),
 role_id varchar(100) DEFAULT NULL,
 res_type varchar(100) DEFAULT NULL,
 res_id bigint DEFAULT NULL,
 permission varchar(20) DEFAULT NULL,
 PRIMARY KEY CLUSTERED(role_res_id)
);

CREATE TABLE dashboard_job (
 job_id bigint identity(1,1),
 job_name varchar(200) DEFAULT NULL,
 cron_exp varchar(200) DEFAULT NULL,
 start_date datetime NULL DEFAULT NULL,
 end_date datetime NULL DEFAULT NULL,
 job_type varchar(200) DEFAULT NULL,
 job_config text,
 user_id varchar(100) DEFAULT NULL,
 last_exec_time datetime NULL DEFAULT NULL,
 job_status bigint,
 exec_log text,
 PRIMARY KEY CLUSTERED (job_id)
);

CREATE TABLE dashboard_board_param (
 board_param_id bigint identity(1,1),
 user_id varchar(50) NOT NULL,
 board_id bigint NOT NULL,
 config text,
 PRIMARY KEY CLUSTERED (board_param_id)
);

ALTER TABLE dbo.dashboard_dataset ADD create_time DATETIME2 DEFAULT GETDATE();
ALTER TABLE dbo.dashboard_dataset ADD update_time DATETIME2 DEFAULT GETDATE();

ALTER TABLE dbo.dashboard_datasource ADD create_time DATETIME2 DEFAULT GETDATE();
ALTER TABLE dbo.dashboard_datasource ADD update_time DATETIME2 DEFAULT GETDATE();

ALTER TABLE dbo.dashboard_widget ADD create_time DATETIME2 DEFAULT GETDATE();
ALTER TABLE dbo.dashboard_widget ADD update_time DATETIME2 DEFAULT GETDATE();

ALTER TABLE dbo.dashboard_board ADD create_time DATETIME2 DEFAULT GETDATE();
ALTER TABLE dbo.dashboard_board change COLUMN update_time DATETIME2 DEFAULT GETDATE();

-- Real folder
DROP TABLE dashboard_folder;
CREATE TABLE dashboard_folder (
 folder_id int PRIMARY KEY IDENTITY(10000,1),
 folder_name VARCHAR(50),
 parent_id int DEFAULT -1,
 is_private int DEFAULT 0,
 user_id VARCHAR(50) DEFAULT '1',
 create_time DATETIME2 DEFAULT GETDATE(),
 update_time DATETIME2 DEFAULT GETDATE()
);

-- 根目录
INSERT INTO dashboard_folder (folder_name,parent_id) VALUES ('Root', -1);
INSERT into dashboard_folder (folder_name, parent_id, is_private) VALUES ('.private', 10000, 1);
INSERT into dashboard_folder (folder_name, parent_id) SELECT category_name, 10000 FROM dashboard_category;

ALTER TABLE dashboard_dataset ADD folder_id INT DEFAULT 10000;
ALTER TABLE dashboard_widget ADD folder_id INT DEFAULT 10000;
ALTER TABLE dashboard_board ADD folder_id bigint DEFAULT NULL;

-- 脚本更新board的目录
UPDATE dashboard_board set folder_id = f.folder_id
 FROM dashboard_board a
 JOIN dashboard_category c on a.category_id = c.category_id
 JOIN dashboard_folder f on c.category_name = f.folder_name and f.parent_id=10000
;

-- Update Private Folder id
UPDATE dashboard_board
 SET folder_id = (SELECT folder_id FROM dashboard_folder WHERE folder_name = '.private' AND parent_id=10000)
 WHERE folder_id IS NULL ;

-- add version
DROP TABLE Meta_Version;
CREATE TABLE Meta_Version (
 id int PRIMARY KEY IDENTITY(1,1),
 name VARCHAR(50),
 status int DEFAULT 0,
 create_time DATETIME2 DEFAULT getdate(),
 update_time DATETIME2 DEFAULT getdate()
);

INSERT INTO Meta_Version (name) values('Folder');

一键复制编辑原始数据按行查看历史

yzhang921 提交于 2017年12月18日 17:53 +08:00 . update sql

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163

--
CREATE DATABASE CBoard;

CREATE TABLE dashboard_board (
 board_id bigint identity(1,1),
 user_id varchar(50) NOT NULL,
 category_id bigint DEFAULT NULL,
 board_name varchar(100) NOT NULL,
 layout_json text,
 PRIMARY KEY CLUSTERED (board_id)
);

CREATE TABLE dashboard_category (
 category_id bigint identity(1,1),
 category_name varchar(100) NOT NULL,
 user_id varchar(100) NOT NULL,
 PRIMARY KEY CLUSTERED (category_id)
);

CREATE TABLE dashboard_datasource (
 datasource_id bigint identity(1,1),
 user_id varchar(50) NOT NULL,
 source_name varchar(100) NOT NULL,
 source_type varchar(100) NOT NULL,
 config text,
 PRIMARY KEY CLUSTERED (datasource_id)
);

CREATE TABLE dashboard_widget (
 widget_id bigint identity(1,1),
 user_id varchar(100) NOT NULL,
 category_name varchar(100) DEFAULT NULL,
 widget_name varchar(100) DEFAULT NULL,
 data_json text,
 PRIMARY KEY CLUSTERED (widget_id)
);

CREATE TABLE dashboard_dataset (
 dataset_id bigint identity(1,1),
 user_id varchar(100) NOT NULL,
 category_name varchar(100) DEFAULT NULL,
 dataset_name varchar(100) DEFAULT NULL,
 data_json text,
 PRIMARY KEY CLUSTERED(dataset_id)
);

CREATE TABLE dashboard_user (
 user_id varchar(50) NOT NULL,
 login_name varchar(100) DEFAULT NULL,
 user_name varchar(100) DEFAULT NULL,
 user_password varchar(100) DEFAULT NULL,
 user_status varchar(100) DEFAULT NULL,
 PRIMARY KEY CLUSTERED(user_id)
);

INSERT INTO dashboard_user (user_id,login_name,user_name,user_password)
VALUES('1','admin','Administrator','ff9830c42660c1dd1942844f8069b74a');

CREATE TABLE dashboard_user_role (
 user_role_id bigint identity(1,1),
 user_id varchar(100) DEFAULT NULL,
 role_id varchar(100) DEFAULT NULL,
 PRIMARY KEY CLUSTERED(user_role_id)
);

CREATE TABLE dashboard_role (
 role_id varchar(100) NOT NULL,
 role_name varchar(100) DEFAULT NULL,
 user_id varchar(50) DEFAULT NULL,
 PRIMARY KEY CLUSTERED(role_id)
);

CREATE TABLE dashboard_role_res (
 role_res_id bigint identity(1,1),
 role_id varchar(100) DEFAULT NULL,
 res_type varchar(100) DEFAULT NULL,
 res_id bigint DEFAULT NULL,
 permission varchar(20) DEFAULT NULL,
 PRIMARY KEY CLUSTERED(role_res_id)
);

CREATE TABLE dashboard_job (
 job_id bigint identity(1,1),
 job_name varchar(200) DEFAULT NULL,
 cron_exp varchar(200) DEFAULT NULL,
 start_date datetime NULL DEFAULT NULL,
 end_date datetime NULL DEFAULT NULL,
 job_type varchar(200) DEFAULT NULL,
 job_config text,
 user_id varchar(100) DEFAULT NULL,
 last_exec_time datetime NULL DEFAULT NULL,
 job_status bigint,
 exec_log text,
 PRIMARY KEY CLUSTERED (job_id)
);

CREATE TABLE dashboard_board_param (
 board_param_id bigint identity(1,1),
 user_id varchar(50) NOT NULL,
 board_id bigint NOT NULL,
 config text,
 PRIMARY KEY CLUSTERED (board_param_id)
);

ALTER TABLE dbo.dashboard_dataset ADD create_time DATETIME2 DEFAULT GETDATE();
ALTER TABLE dbo.dashboard_dataset ADD update_time DATETIME2 DEFAULT GETDATE();

ALTER TABLE dbo.dashboard_datasource ADD create_time DATETIME2 DEFAULT GETDATE();
ALTER TABLE dbo.dashboard_datasource ADD update_time DATETIME2 DEFAULT GETDATE();

ALTER TABLE dbo.dashboard_widget ADD create_time DATETIME2 DEFAULT GETDATE();
ALTER TABLE dbo.dashboard_widget ADD update_time DATETIME2 DEFAULT GETDATE();

ALTER TABLE dbo.dashboard_board ADD create_time DATETIME2 DEFAULT GETDATE();
ALTER TABLE dbo.dashboard_board change COLUMN update_time DATETIME2 DEFAULT GETDATE();

-- Real folder
DROP TABLE dashboard_folder;
CREATE TABLE dashboard_folder (
 folder_id int PRIMARY KEY IDENTITY(10000,1),
 folder_name VARCHAR(50),
 parent_id int DEFAULT -1,
 is_private int DEFAULT 0,
 user_id VARCHAR(50) DEFAULT '1',
 create_time DATETIME2 DEFAULT GETDATE(),
 update_time DATETIME2 DEFAULT GETDATE()
);

-- 根目录
INSERT INTO dashboard_folder (folder_name,parent_id) VALUES ('Root', -1);
INSERT into dashboard_folder (folder_name, parent_id, is_private) VALUES ('.private', 10000, 1);
INSERT into dashboard_folder (folder_name, parent_id) SELECT category_name, 10000 FROM dashboard_category;

ALTER TABLE dashboard_dataset ADD folder_id INT DEFAULT 10000;
ALTER TABLE dashboard_widget ADD folder_id INT DEFAULT 10000;
ALTER TABLE dashboard_board ADD folder_id bigint DEFAULT NULL;

-- 脚本更新board的目录
UPDATE dashboard_board set folder_id = f.folder_id
 FROM dashboard_board a
 JOIN dashboard_category c on a.category_id = c.category_id
 JOIN dashboard_folder f on c.category_name = f.folder_name and f.parent_id=10000
;

-- Update Private Folder id
UPDATE dashboard_board
 SET folder_id = (SELECT folder_id FROM dashboard_folder WHERE folder_name = '.private' AND parent_id=10000)
 WHERE folder_id IS NULL ;

-- add version
DROP TABLE Meta_Version;
CREATE TABLE Meta_Version (
 id int PRIMARY KEY IDENTITY(1,1),
 name VARCHAR(50),
 status int DEFAULT 0,
 create_time DATETIME2 DEFAULT getdate(),
 update_time DATETIME2 DEFAULT getdate()
);

INSERT INTO Meta_Version (name) values('Folder');