KGuWrgPZ

1DROP DATABASE IF EXISTS servicehouse;
2CREATE DATABASE servicehouse;
3USE servicehouse;
4
5-- user start -----------------------------------------------------------------------
6
7CREATE TABLE users (
8  id BIGINT AUTO_INCREMENT,
9  username VARCHAR(100) NOT NULL,
10  PASSWORD VARCHAR(100) NOT NULL,
11  password_salt VARCHAR(100) NOT NULL,
12  created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
13  updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
14  CONSTRAINT pk_users PRIMARY KEY(id)
15) CHARSET=utf8 ENGINE=INNODB;
16CREATE UNIQUE INDEX idx_users_username ON users(username);
17
18CREATE TABLE user_roles(
19  id BIGINT AUTO_INCREMENT,
20  username VARCHAR(100),
21  role_name VARCHAR(100),
22  CONSTRAINT pk_user_roles PRIMARY KEY(id)
23) CHARSET=utf8 ENGINE=INNODB;
24CREATE UNIQUE INDEX idx_user_roles ON user_roles(username, role_name);
25
26CREATE TABLE role_permissions(
27  id BIGINT AUTO_INCREMENT,
28  role_name VARCHAR(100),
29  permission_name VARCHAR(100),
30  CONSTRAINT pk_role_permissions PRIMARY KEY(id)
31) CHARSET=utf8 ENGINE=INNODB;
32CREATE UNIQUE INDEX idx_role_permissions ON role_permissions(role_name, permission_name);
33
34-- user end -----------------------------------------------------------------------
35
36-- schema start -----------------------------------------------------------------------
37
38CREATE TABLE ontschema(
39  sid VARCHAR(128),
40  uid BIGINT,
41  gid BIGINT NOT NULL COMMENT 'gspace id',
42  STATUS INT,
43  sname VARCHAR(128),
44  created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
45  updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
46  CONSTRAINT pk_ontschema PRIMARY KEY(sid)
47) CHARSET=utf8 ENGINE=INNODB;
48CREATE UNIQUE INDEX idx_sname_ontschema ON ontschema(uid, sname);
49
50CREATE TABLE tempontschema(
51  sid VARCHAR(128),
52  related_sid VARCHAR(128),
53  created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
54  updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
55  CONSTRAINT pk_tempontschema PRIMARY KEY(sid)
56) CHARSET=utf8 ENGINE=INNODB;
57CREATE UNIQUE INDEX idx_rsid_tempontschema ON tempontschema(related_sid);
58
59CREATE TABLE matchtable(
60  id BIGINT AUTO_INCREMENT,
61  sid VARCHAR(128),
62  entity1Uri VARCHAR(128),
63  entity2Uri VARCHAR(128),
64  entityType VARCHAR(32),
65  confidence DOUBLE,
66  matchType VARCHAR(32),
67  created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
68  updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
69  CONSTRAINT pk_matchtable PRIMARY KEY(id)
70) CHARSET=utf8 ENGINE=INNODB;
71CREATE UNIQUE INDEX idx_matchtable ON matchtable(sid, entity1Uri);
72
73-- schema end -----------------------------------------------------------------------
74
75-- gspace start -----------------------------------------------------------------------
76
77CREATE TABLE gspace(
78  id BIGINT AUTO_INCREMENT,
79  NAME VARCHAR(128),
80  neo4jid VARCHAR(128),
81  jenaid VARCHAR(128),
82  modelid VARCHAR(128),
83  STATUS INT COMMENT '0 normal, 1 error' DEFAULT 0,
84  CONSTRAINT gspace PRIMARY KEY(id)
85) CHARSET=utf8 ENGINE=INNODB;
86CREATE UNIQUE INDEX idx_gspace ON gspace(NAME);
87
88CREATE TABLE user_gspace(
89  uid BIGINT,
90  gid BIGINT,
91  CONSTRAINT user_gspace PRIMARY KEY(uid, gid)
92) CHARSET=utf8 ENGINE=INNODB;
93
94CREATE TABLE gspaceneo(
95  id VARCHAR(128),
96  HOST VARCHAR(32),
97  httpport VARCHAR(16),
98  boltport VARCHAR(16),
99  CONSTRAINT gspaceneo PRIMARY KEY(id)
100) CHARSET=utf8 ENGINE=INNODB;
101CREATE UNIQUE INDEX idx_http_gspaceneo ON gspaceneo(HOST, httpport);
102CREATE UNIQUE INDEX idx_bolt_gspaceneo ON gspaceneo(HOST, boltport);
103
104
105CREATE TABLE gspacejena(
106  id VARCHAR(128),
107  path VARCHAR(256),
108  CONSTRAINT gspacejena PRIMARY KEY(id)
109) CHARSET=utf8 ENGINE=INNODB;
110CREATE UNIQUE INDEX idx_gspacejena ON gspacejena(path);
111
112CREATE TABLE gspacemodel(
113  id VARCHAR(128),
114  path VARCHAR(256),
115  CONSTRAINT gspacemodel PRIMARY KEY(id)
116) CHARSET=utf8 ENGINE=INNODB;
117CREATE UNIQUE INDEX idx_gspacemodel ON gspacemodel(path);
118
119CREATE TABLE gspacemodelparam(
120  gid BIGINT COMMENT 'gspace id',
121  modelname VARCHAR(16) COMMENT 'embedding model name',
122  available BOOL DEFAULT FALSE,
123  params LONGTEXT COMMENT 'embedding weight',
124  entity2id LONGTEXT,
125  relation2id LONGTEXT,
126  created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
127  updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
128  CONSTRAINT gspacemodel PRIMARY KEY(gid, modelname)
129) CHARSET=utf8 ENGINE=INNODB;
130
131-- gspace end -----------------------------------------------------------------------
132
133INSERT INTO users (username, PASSWORD, password_salt) VALUES ('admin', 'J/ms7qTJtqmysekuY8/v1TAS+VKqXdH5sB7ulXZOWho=', 'wxKYXuTPST5SG0jMQzVPsg==');
134INSERT INTO users (username, PASSWORD, password_salt) VALUES ('lxk', 'J/ms7qTJtqmysekuY8/v1TAS+VKqXdH5sB7ulXZOWho=', 'wxKYXuTPST5SG0jMQzVPsg==');
135INSERT INTO user_roles (username, role_name) VALUES ('admin', 'admin');
136INSERT INTO user_roles (username, role_name) VALUES ('lxk', 'ordinaryUser');
137INSERT INTO role_permissions (role_name, permission_name) VALUES ('admin', 'all');