3 -- Run this script to create the Jibu database tables in your database.
5 -- *****************************************************
6 -- Create the tables and indices
8 -- ------------------------------------------------------
9 CREATE TABLE userbase (
10 id serial NOT NULL PRIMARY KEY,
11 username varchar(128) NOT NULL,
12 password varchar(64) NOT NULL,
13 fullname varchar(128) NOT NULL,
14 emailaddress varchar(128),
15 enabled boolean default true NOT NULL
17 ALTER TABLE userbase ADD CONSTRAINT userbase_username_uq UNIQUE ( username );
19 -- ------------------------------------------------------
21 id serial NOT NULL PRIMARY KEY,
22 name varchar(128) NOT NULL,
25 description varchar(512) NOT NULL
27 ALTER TABLE roles ADD CONSTRAINT roles_name_uq UNIQUE ( name );
29 -- ------------------------------------------------------
30 CREATE TABLE user_role_map (
31 user_id integer NOT NULL,
32 role_id integer NOT NULL
34 ALTER TABLE user_role_map ADD CONSTRAINT urmap_user_id_role_id_uq UNIQUE ( user_id,role_id );
35 ALTER TABLE user_role_map ADD CONSTRAINT urmap_role_id_fk FOREIGN KEY (role_id) REFERENCES roles (id);
36 ALTER TABLE user_role_map ADD CONSTRAINT urmap_user_id_fk FOREIGN KEY (user_id) REFERENCES userbase (id);
38 -- ------------------------------------------------------
39 CREATE TABLE authorities (
40 id serial NOT NULL PRIMARY KEY,
41 name varchar(128) NOT NULL,
42 value varchar(128) NOT NULL
44 ALTER TABLE authorities ADD CONSTRAINT authorities_value_uq UNIQUE ( value );
46 -- ------------------------------------------------------
47 CREATE TABLE role_authority_map (
48 role_id integer NOT NULL,
49 authority_id integer NOT NULL
51 ALTER TABLE role_authority_map ADD CONSTRAINT ramap_role_id_authority_id_uq UNIQUE ( role_id,authority_id );
52 ALTER TABLE role_authority_map ADD CONSTRAINT ramap_role_id_fk FOREIGN KEY (role_id) REFERENCES roles (id);
53 ALTER TABLE role_authority_map ADD CONSTRAINT ramap_authority_id_fk FOREIGN KEY (authority_id) REFERENCES authorities (id);
55 -- ------------------------------------------------------
56 CREATE TABLE settings(
57 id serial NOT NULL PRIMARY KEY,
58 name varchar(32) NOT NULL,
59 value varchar(32) NOT NULL,
60 sortindex integer default 0 NOT NULL,
61 enabled boolean default true NOT NULL
63 ALTER TABLE settings ADD CONSTRAINT setting_name_sortindex_uq UNIQUE ( name,sortindex );
64 ALTER TABLE settings ADD CONSTRAINT setting_name_value_uq UNIQUE ( name,value );
66 -- ------------------------------------------------------
67 CREATE TABLE user_setting_map(
68 user_id integer NOT NULL,
69 setting_id integer NOT NULL
71 ALTER TABLE user_setting_map ADD CONSTRAINT usmap_user_id_setting_id_uq UNIQUE ( user_id,setting_id );
72 ALTER TABLE user_setting_map ADD CONSTRAINT usmap_user_id_fk FOREIGN KEY (user_id) REFERENCES userbase (id);
73 ALTER TABLE user_setting_map ADD CONSTRAINT usmap_setting_id_fk FOREIGN KEY (setting_id) REFERENCES settings (id);
75 -- ------------------------------------------------------
77 id serial NOT NULL PRIMARY KEY,
78 value varchar(64) NOT NULL,
79 type varchar(16) NOT NULL,
80 expiration timestamp without time zone NOT NULL,
81 user_id integer NOT NULL
83 ALTER TABLE tokens ADD CONSTRAINT tokens_value_uq UNIQUE ( value );
84 ALTER TABLE tokens ADD CONSTRAINT tokens_user_id_fk FOREIGN KEY (user_id) REFERENCES userbase (id);