当前位置: 开发笔记 > 后端 > 正文

davincisqlserver

作者：星寴 | 来源：互联网 | 2023-10-12 11:18

--------------------------------Tablestructureforcron_job------------------------------DRO

-- ---------------------------- -- Table structure for cron_job -- ---------------------------- DROP TABLE IF EXISTS cron_job; CREATE TABLE cron_job ( id bigint NOT NULL identity(1,1), name nvarchar(45) NOT NULL, project_id bigint NOT NULL, job_type nvarchar(45) NOT NULL, job_status nvarchar(10) NOT NULL DEFAULT '', cron_expression nvarchar(45) NOT NULL, start_date datetime NOT NULL, end_date datetime NOT NULL, config text NOT NULL, description nvarchar(255) DEFAULT NULL, exec_log text , create_by bigint NOT NULL, create_time smalldatetime NOT NULL DEFAULT getdate(), update_by bigint DEFAULT NULL, update_time smalldatetime NULL DEFAULT NULL, parent_id bigint DEFAULT NULL, full_parent_id nvarchar(255) DEFAULT NULL, is_folder tinyint DEFAULT NULL, [index] int DEFAULT NULL, PRIMARY KEY (id) ) ; alter table cron_job add constraint name_UNIQUE unique(name); -- ---------------------------- -- Table structure for dashboard -- ---------------------------- DROP TABLE IF EXISTS dashboard; CREATE TABLE dashboard ( id bigint NOT NULL identity(1,1), name nvarchar(255) NOT NULL, dashboard_portal_id bigint NOT NULL, type smallint NOT NULL, [index] int NOT NULL, parent_id bigint NOT NULL DEFAULT '0', config text, full_parent_Id nvarchar(100) DEFAULT NULL, create_by bigint DEFAULT NULL, create_time datetime DEFAULT NULL, update_by bigint DEFAULT NULL, update_time datetime DEFAULT NULL, PRIMARY KEY (id) ) ; create index idx_dashboard_id on dashboard(dashboard_portal_id); create index idx_parent_id on dashboard(parent_id); -- ---------------------------- -- Table structure for dashboard_portal -- ---------------------------- DROP TABLE IF EXISTS dashboard_portal; CREATE TABLE dashboard_portal ( id bigint NOT NULL identity(1,1), name nvarchar(255) NOT NULL, description nvarchar(255) DEFAULT NULL, project_id bigint NOT NULL, avatar nvarchar(255) DEFAULT NULL, publish tinyint NOT NULL DEFAULT '0', create_by bigint DEFAULT NULL, create_time datetime DEFAULT NULL, update_by bigint DEFAULT NULL, update_time datetime DEFAULT NULL, PRIMARY KEY (id) ) ; create index idx_project_id on dashboard_portal(project_id); -- ---------------------------- -- Table structure for display -- ---------------------------- DROP TABLE IF EXISTS display; CREATE TABLE display ( id bigint NOT NULL identity(1,1), name nvarchar(255) NOT NULL, description nvarchar(255) DEFAULT NULL, project_id bigint NOT NULL, avatar nvarchar(255) DEFAULT NULL, publish tinyint NOT NULL, config text NULL, create_by bigint DEFAULT NULL, create_time datetime DEFAULT NULL, update_by bigint DEFAULT NULL, update_time datetime DEFAULT NULL, PRIMARY KEY (id) ) ; create index idx_project_id on display(project_id); -- ---------------------------- -- Table structure for display_slide -- ---------------------------- DROP TABLE IF EXISTS display_slide; CREATE TABLE display_slide ( id bigint NOT NULL identity(1,1), display_id bigint NOT NULL, [index] int NOT NULL, config text NOT NULL, create_by bigint DEFAULT NULL, create_time datetime DEFAULT NULL, update_by bigint DEFAULT NULL, update_time datetime DEFAULT NULL, PRIMARY KEY (id) ) ; create index idx_display_id on display_slide(display_id); -- ---------------------------- -- Table structure for download_record -- ---------------------------- DROP TABLE IF EXISTS download_record; CREATE TABLE download_record ( id bigint NOT NULL identity(1,1), name nvarchar(255) NOT NULL, user_id bigint NOT NULL, path nvarchar(255) DEFAULT NULL, status smallint NOT NULL, create_time datetime NOT NULL, last_download_time datetime DEFAULT NULL, PRIMARY KEY (id) ) ; create index idx_user on download_record(user_id); -- ---------------------------- -- Table structure for favorite -- ---------------------------- DROP TABLE IF EXISTS favorite; CREATE TABLE favorite ( id bigint NOT NULL identity(1,1), user_id bigint NOT NULL, project_id bigint NOT NULL, create_time datetime NOT NULL DEFAULT getdate(), PRIMARY KEY (id) ) ; create UNIQUE index idx_user_project on favorite(user_id,project_id); -- ---------------------------- -- Table structure for mem_dashboard_widget -- ---------------------------- DROP TABLE IF EXISTS mem_dashboard_widget; CREATE TABLE mem_dashboard_widget ( id bigint NOT NULL identity(1,1), alias nvarchar(30) NULL, dashboard_id bigint NOT NULL, widget_Id bigint DEFAULT NULL, x int NOT NULL, y int NOT NULL, width int NOT NULL, height int NOT NULL, polling tinyint NOT NULL DEFAULT '0', frequency int DEFAULT NULL, config text, create_by bigint DEFAULT NULL, create_time datetime DEFAULT NULL, update_by bigint DEFAULT NULL, update_time datetime DEFAULT NULL, PRIMARY KEY (id) ) ; create index idx_protal_id on mem_dashboard_widget(dashboard_id); create index idx_widget_id on mem_dashboard_widget(widget_Id); -- ---------------------------- -- Table structure for mem_display_slide_widget -- ---------------------------- DROP TABLE IF EXISTS mem_display_slide_widget; CREATE TABLE mem_display_slide_widget ( id bigint NOT NULL identity(1,1), display_slide_id bigint NOT NULL, widget_id bigint DEFAULT NULL, name nvarchar(255) NOT NULL, params text NOT NULL, type smallint NOT NULL, sub_type smallint DEFAULT NULL, [index] int NOT NULL DEFAULT '0', create_by bigint DEFAULT NULL, create_time datetime DEFAULT NULL, update_by bigint DEFAULT NULL, update_time datetime DEFAULT NULL, PRIMARY KEY (id) ) ; create index idx_slide_id on mem_display_slide_widget(display_slide_id); create index idx_widget_id on mem_display_slide_widget(widget_id); -- ---------------------------- -- Table structure for organization -- ---------------------------- DROP TABLE IF EXISTS organization; CREATE TABLE organization ( id bigint NOT NULL identity(1,1), name nvarchar(255) NOT NULL, description nvarchar(255) DEFAULT NULL, avatar nvarchar(255) DEFAULT NULL, user_id bigint NOT NULL, project_num int DEFAULT '0', member_num int DEFAULT '0', role_num int DEFAULT '0', allow_create_project tinyint DEFAULT '1', member_permission smallint NOT NULL DEFAULT '0', create_time smalldatetime NOT NULL DEFAULT getdate(), create_by bigint NOT NULL DEFAULT '0', update_time smalldatetime NULL, update_by bigint DEFAULT NULL, PRIMARY KEY (id) ) ; -- ---------------------------- -- Table structure for platform -- ---------------------------- DROP TABLE IF EXISTS platform; CREATE TABLE platform ( id bigint NOT NULL, name nvarchar(255) NOT NULL, platform nvarchar(255) NOT NULL, code nvarchar(32) NOT NULL, checkCode nvarchar(255) DEFAULT NULL, checkSystemToken nvarchar(255) DEFAULT NULL, checkUrl nvarchar(255) DEFAULT NULL, alternateField1 nvarchar(255) DEFAULT NULL, alternateField2 nvarchar(255) DEFAULT NULL, alternateField3 nvarchar(255) DEFAULT NULL, alternateField4 nvarchar(255) DEFAULT NULL, alternateField5 nvarchar(255) DEFAULT NULL, PRIMARY KEY (id) ) ; -- ---------------------------- -- Table structure for project -- ---------------------------- DROP TABLE IF EXISTS project; CREATE TABLE project ( id bigint NOT NULL identity(1,1), name nvarchar(255) NOT NULL, description nvarchar(255) DEFAULT NULL, pic nvarchar(255) DEFAULT NULL, org_id bigint NOT NULL, user_id bigint NOT NULL, visibility tinyint DEFAULT '1', star_num int DEFAULT '0', is_transfer tinyint NOT NULL DEFAULT '0', initial_org_id bigint NOT NULL, create_by bigint DEFAULT NULL, create_time datetime DEFAULT NULL, update_by bigint DEFAULT NULL, update_time datetime DEFAULT NULL, PRIMARY KEY (id) ) ; -- ---------------------------- -- Table structure for rel_project_admin -- ---------------------------- DROP TABLE IF EXISTS rel_project_admin; CREATE TABLE rel_project_admin ( id bigint NOT NULL identity(1,1), project_id bigint NOT NULL, user_id bigint NOT NULL, create_by bigint DEFAULT NULL, create_time datetime DEFAULT NULL, update_by bigint DEFAULT NULL, update_time datetime DEFAULT NULL, PRIMARY KEY (id) ) ; create UNIQUE index idx_project_user on rel_project_admin(project_id,user_id); -- ---------------------------- -- Table structure for rel_role_dashboard -- ---------------------------- DROP TABLE IF EXISTS rel_role_dashboard; CREATE TABLE rel_role_dashboard ( role_id bigint NOT NULL, dashboard_id bigint NOT NULL, visible tinyint NOT NULL DEFAULT '0', create_by bigint DEFAULT NULL, create_time datetime DEFAULT NULL, update_by bigint DEFAULT NULL, update_time datetime DEFAULT NULL, PRIMARY KEY (role_id, dashboard_id) ) ; -- ---------------------------- -- Table structure for rel_role_display -- ---------------------------- DROP TABLE IF EXISTS rel_role_display; CREATE TABLE rel_role_display ( role_id bigint NOT NULL, display_id bigint NOT NULL, visible tinyint NOT NULL DEFAULT '0', create_by bigint DEFAULT NULL, create_time datetime DEFAULT NULL, update_by bigint DEFAULT NULL, update_time datetime DEFAULT NULL, PRIMARY KEY (role_id, display_id) ) ; -- ---------------------------- -- Table structure for rel_role_portal -- ---------------------------- DROP TABLE IF EXISTS rel_role_portal; CREATE TABLE rel_role_portal ( role_id bigint NOT NULL, portal_id bigint NOT NULL, visible tinyint NOT NULL DEFAULT '0', create_by bigint DEFAULT NULL, create_time datetime DEFAULT NULL, update_by bigint DEFAULT NULL, update_time datetime DEFAULT NULL, PRIMARY KEY (role_id, portal_id) ) ; -- ---------------------------- -- Table structure for rel_role_project -- ---------------------------- DROP TABLE IF EXISTS rel_role_project; CREATE TABLE rel_role_project ( id bigint NOT NULL identity(1,1), project_id bigint NOT NULL, role_id bigint NOT NULL, source_permission smallint NOT NULL DEFAULT '1', view_permission smallint NOT NULL DEFAULT '1', widget_permission smallint NOT NULL DEFAULT '1', viz_permission smallint NOT NULL DEFAULT '1', schedule_permission smallint NOT NULL DEFAULT '1', share_permission tinyint NOT NULL DEFAULT '0', download_permission tinyint NOT NULL DEFAULT '0', create_by bigint DEFAULT NULL, create_time datetime DEFAULT NULL, update_by bigint DEFAULT NULL, update_time datetime DEFAULT NULL, PRIMARY KEY (id) ) ; create UNIQUE index idx_role_project on rel_role_project(project_id,role_id); -- ---------------------------- -- Table structure for rel_role_slide -- ---------------------------- DROP TABLE IF EXISTS rel_role_slide; CREATE TABLE rel_role_slide ( role_id bigint NOT NULL, slide_id bigint NOT NULL, visible tinyint NOT NULL DEFAULT '0', create_by bigint DEFAULT NULL, create_time datetime DEFAULT NULL, update_by bigint DEFAULT NULL, update_time datetime DEFAULT NULL, PRIMARY KEY (role_id, slide_id) ) ; -- ---------------------------- -- Table structure for rel_role_user -- ---------------------------- DROP TABLE IF EXISTS rel_role_user; CREATE TABLE rel_role_user ( id bigint NOT NULL identity(1,1), user_id bigint NOT NULL, role_id bigint NOT NULL, create_by bigint DEFAULT NULL, create_time datetime DEFAULT NULL, update_by bigint DEFAULT NULL, update_time datetime DEFAULT NULL, PRIMARY KEY (id) ) ; create UNIQUE index idx_role_user on rel_role_user(user_id,role_id); -- ---------------------------- -- Table structure for rel_role_view -- ---------------------------- DROP TABLE IF EXISTS rel_role_view; CREATE TABLE rel_role_view ( view_id bigint NOT NULL, role_id bigint NOT NULL, row_auth text, column_auth text, create_by bigint DEFAULT NULL, create_time datetime DEFAULT NULL, update_by bigint DEFAULT NULL, update_time datetime DEFAULT NULL, PRIMARY KEY (view_id, role_id) ) ; -- ---------------------------- -- Table structure for rel_user_organization -- ---------------------------- DROP TABLE IF EXISTS rel_user_organization; CREATE TABLE rel_user_organization ( id bigint NOT NULL identity(1,1), org_id bigint NOT NULL, user_id bigint NOT NULL, role smallint NOT NULL DEFAULT '0', create_by bigint DEFAULT NULL, create_time datetime DEFAULT NULL, update_by bigint DEFAULT NULL, update_time datetime DEFAULT NULL, PRIMARY KEY (id) ) ; create UNIQUE index idx_org_user on rel_user_organization(org_id,user_id); -- ---------------------------- -- Table structure for role -- ---------------------------- DROP TABLE IF EXISTS role; CREATE TABLE role ( id bigint NOT NULL identity(1,1), org_id bigint NOT NULL, name nvarchar(100) NOT NULL, description nvarchar(255) DEFAULT NULL, create_by bigint DEFAULT NULL, create_time datetime DEFAULT NULL, update_by bigint DEFAULT NULL, update_time datetime DEFAULT NULL, avatar nvarchar(255) DEFAULT NULL, PRIMARY KEY (id) ) ; create index idx_orgid on role(org_id); -- ---------------------------- -- Table structure for source -- ---------------------------- DROP TABLE IF EXISTS source; CREATE TABLE source ( id bigint NOT NULL identity(1,1), name nvarchar(255) NOT NULL, description nvarchar(255) DEFAULT NULL, config text NOT NULL, type nvarchar(10) NOT NULL, project_id bigint NOT NULL, create_by bigint DEFAULT NULL, create_time datetime DEFAULT NULL, update_by bigint DEFAULT NULL, update_time datetime DEFAULT NULL, parent_id bigint DEFAULT NULL, full_parent_id nvarchar(255) DEFAULT NULL, is_folder tinyint DEFAULT NULL, [index] int DEFAULT NULL, PRIMARY KEY (id) ) ; create index idx_project_id on source(project_id); -- ---------------------------- -- Table structure for star -- ---------------------------- DROP TABLE IF EXISTS star; CREATE TABLE star ( id bigint NOT NULL identity(1,1), target nvarchar(20) NOT NULL, target_id bigint NOT NULL, user_id bigint NOT NULL, star_time datetime NOT NULL default getdate(), PRIMARY KEY (id) ) ; create index idx_target_id on star(target_id); create index idx_user_id on star(user_id); -- ---------------------------- -- Table structure for user -- ---------------------------- DROP TABLE IF EXISTS [user]; CREATE TABLE [user] ( id bigint NOT NULL identity(1,1), email nvarchar(255) NOT NULL, username nvarchar(255) NOT NULL, password nvarchar(255) NOT NULL, admin tinyint NOT NULL, active tinyint DEFAULT NULL, name nvarchar(255) DEFAULT NULL, description nvarchar(255) DEFAULT NULL, department nvarchar(255) DEFAULT NULL, avatar nvarchar(255) DEFAULT NULL, create_time smalldatetime NOT NULL DEFAULT getdate(), create_by bigint NOT NULL DEFAULT '0', update_time smalldatetime NULL, update_by bigint DEFAULT NULL, PRIMARY KEY (id) ) ; -- ---------------------------- -- Table structure for view -- ---------------------------- DROP TABLE IF EXISTS [view]; CREATE TABLE [view] ( id bigint NOT NULL identity(1,1), name nvarchar(255) NOT NULL, description nvarchar(255) DEFAULT NULL, project_id bigint NOT NULL, source_id bigint NOT NULL, sql text, model text, variable text, config text, create_by bigint DEFAULT NULL, create_time datetime DEFAULT NULL, update_by bigint DEFAULT NULL, update_time datetime DEFAULT NULL, parent_id bigint DEFAULT NULL, full_parent_id nvarchar(255) DEFAULT NULL, is_folder tinyint DEFAULT NULL, [index] int DEFAULT NULL, PRIMARY KEY (id) ) ; create index idx_project_id on [view](project_id); -- ---------------------------- -- Table structure for widget -- ---------------------------- DROP TABLE IF EXISTS widget; CREATE TABLE widget ( id bigint NOT NULL identity(1,1), name nvarchar(255) NOT NULL, description nvarchar(255) DEFAULT NULL, view_id bigint NOT NULL, project_id bigint NOT NULL, type bigint NOT NULL, publish tinyint NOT NULL, config text NOT NULL, create_by bigint DEFAULT NULL, create_time datetime DEFAULT NULL, update_by bigint DEFAULT NULL, update_time datetime DEFAULT NULL, parent_id bigint DEFAULT NULL, full_parent_id nvarchar(255) DEFAULT NULL, is_folder tinyint DEFAULT NULL, [index] int DEFAULT NULL, PRIMARY KEY (id) ) ; create index idx_project_id on widget(project_id); create index idx_view_id on widget(view_id); DROP TABLE IF EXISTS rel_role_display_slide_widget; CREATE TABLE rel_role_display_slide_widget ( role_id bigint NOT NULL, mem_display_slide_widget_id bigint NOT NULL, visible tinyint NOT NULL DEFAULT '0', create_by bigint DEFAULT NULL, create_time datetime DEFAULT NULL, update_by bigint DEFAULT NULL, update_time datetime DEFAULT NULL, PRIMARY KEY (role_id, mem_display_slide_widget_id) ) ; DROP TABLE IF EXISTS rel_role_dashboard_widget; CREATE TABLE rel_role_dashboard_widget ( role_id bigint NOT NULL, mem_dashboard_widget_id bigint NOT NULL, visible tinyint NOT NULL DEFAULT '0', create_by bigint DEFAULT NULL, create_time datetime DEFAULT NULL, update_by bigint DEFAULT NULL, update_time datetime DEFAULT NULL, PRIMARY KEY (role_id, mem_dashboard_widget_id) ) ; DROP TABLE IF EXISTS davinci_statistic_visitor_operation; CREATE TABLE davinci_statistic_visitor_operation ( id bigint NOT NULL identity(1,1), user_id bigint DEFAULT NULL, email nvarchar(255) DEFAULT NULL, action nvarchar(255) DEFAULT NULL , org_id bigint DEFAULT NULL, project_id bigint DEFAULT NULL, project_name nvarchar(255) DEFAULT NULL, viz_type nvarchar(255) DEFAULT NULL , viz_id bigint DEFAULT NULL, viz_name nvarchar(255) DEFAULT NULL, sub_viz_id bigint DEFAULT NULL, sub_viz_name nvarchar(255) DEFAULT NULL, widget_id bigint DEFAULT NULL, widget_name nvarchar(255) DEFAULT NULL, variables nvarchar(500) DEFAULT NULL, filters nvarchar(500) DEFAULT NULL, groups nvarchar(500) DEFAULT NULL, create_time smalldatetime NULL DEFAULT NULL, PRIMARY KEY (id) ) ; DROP TABLE IF EXISTS davinci_statistic_terminal; CREATE TABLE davinci_statistic_terminal ( id bigint NOT NULL identity(1,1), user_id bigint DEFAULT NULL, email nvarchar(255) DEFAULT NULL, browser_name nvarchar(255) DEFAULT NULL, browser_version nvarchar(255) DEFAULT NULL, engine_name nvarchar(255) DEFAULT NULL, engine_version nvarchar(255) DEFAULT NULL, os_name nvarchar(255) DEFAULT NULL, os_version nvarchar(255) DEFAULT NULL, device_model nvarchar(255) DEFAULT NULL, device_type nvarchar(255) DEFAULT NULL, device_vendor nvarchar(255) DEFAULT NULL, cpu_architecture nvarchar(255) DEFAULT NULL, create_time smalldatetime NULL DEFAULT NULL, PRIMARY KEY (id) ) ; DROP TABLE IF EXISTS davinci_statistic_duration; CREATE TABLE davinci_statistic_duration ( id bigint NOT NULL identity(1,1), user_id bigint DEFAULT NULL, email nvarchar(255) DEFAULT NULL, org_id bigint DEFAULT NULL , project_id bigint DEFAULT NULL , project_name nvarchar(255) DEFAULT NULL , viz_type nvarchar(10) DEFAULT NULL , viz_id bigint DEFAULT NULL , viz_name nvarchar(255) DEFAULT NULL , sub_viz_id bigint DEFAULT NULL , sub_viz_name nvarchar(255) DEFAULT NULL , start_time smalldatetime NULL DEFAULT NULL, end_time smalldatetime NULL DEFAULT NULL, PRIMARY KEY (id) ) ; DROP TABLE IF EXISTS share_download_record; CREATE TABLE share_download_record ( id bigint NOT NULL identity(1,1), uuid nvarchar(50) DEFAULT NULL, name nvarchar(255) NOT NULL, path nvarchar(255) DEFAULT NULL, status smallint NOT NULL, create_time datetime NOT NULL, last_download_time datetime DEFAULT NULL, PRIMARY KEY (id) ) ; INSERT INTO [user] ( email, username, password, admin, active, name, description, department, avatar, create_time, create_by, update_by, update_time) VALUES ( 'guest@davinci.cn', 'guest', '$2a$10$RJKb4jhMgRYnGPlVRV036erxQ3oGZ8NnxZrlrrBJJha9376cAuTRO', 1, 1, NULL, NULL, NULL, NULL, '2020-01-01 00:00:00', 0, NULL, NULL); INSERT INTO organization ( name, description, avatar, user_id, project_num, member_num, role_num, allow_create_project, member_permission, create_time, create_by, update_time, update_by) VALUES ( 'guest Organization', NULL, NULL, 1, 0, 1, 0, 1, 1, '2020-01-01 00:00:00', 1, NULL, NULL); INSERT INTO rel_user_organization ( org_id, user_id, role, create_by, create_time, update_by, update_time) VALUES ( 1, 1, 1, 1, '2020-01-01 00:00:00', NULL, NULL); INSERT INTO [user] ( email, username, password, admin, active, name, description, department, avatar, create_time, create_by, update_by, update_time) VALUES ( 'admin@davinci.cn', 'admin', '$2a$10$RJKb4jhMgRYnGPlVRV036erxQ3oGZ8NnxZrlrrBJJha9376cAuTRO', 1, 1, NULL, NULL, NULL, NULL, '2020-01-01 00:00:00', 0, NULL, NULL); INSERT INTO organization ( name, description, avatar, user_id, project_num, member_num, role_num, allow_create_project, member_permission, create_time, create_by, update_time, update_by) VALUES ( 'admin Organization', NULL, NULL, 2, 0, 1, 0, 1, 1, '2020-01-01 00:00:00', 1, NULL, NULL); INSERT INTO rel_user_organization ( org_id, user_id, role, create_by, create_time, update_by, update_time) VALUES ( 1, 2, 1, 1, '2020-01-01 00:00:00', NULL, NULL);