From 634202d433957146385d7a0ae84cc5fa66665423 Mon Sep 17 00:00:00 2001 From: Ray Lee Date: Fri, 9 Aug 2019 20:02:48 -0700 Subject: [PATCH] DRYD-704: Make create_db without recreate_db=true create the cspace db iff it does not exist. Clean up SQL init scripts. --- build.xml | 44 +++++++---- .../main/resources/db/postgresql/account.sql | 45 ++++++++--- .../resources/db/postgresql/account_index.sql | 12 +-- .../db/postgresql/authentication.sql | 19 ++++- .../src/main/resources/db/postgresql/acl.sql | 72 ++++++++--------- .../resources/db/postgresql/authorization.sql | 79 +++++++++++++------ .../db/postgresql/authorization_index.sql | 17 ++-- 7 files changed, 175 insertions(+), 113 deletions(-) diff --git a/build.xml b/build.xml index 2627f6d4d..373cf7730 100644 --- a/build.xml +++ b/build.xml @@ -195,7 +195,7 @@ Top-level target to create (or recreate) CollectionSpace related databases. Checks the 'recreate_db' property or environment variable. --> - @@ -207,7 +207,7 @@ If the environment variable 'recreate_db' is set to true then the Ant property 'create_db.recreate' will get set to true; otherwise, it will get set to the current value of the Ant property 'recreate_db' -which will be null if not set on the command line. --> - + @@ -217,7 +217,7 @@ - + @@ -225,24 +225,28 @@ - - - - - - - - + + + + + - + + + + + + - + @@ -251,23 +255,29 @@ - - + + Recreating nuxeo databases with credentials ${db.csadmin.user}:${db.csadmin.user.password} + + + - - Creating the cspace database with credentials ${db.cspace.user}:${db.cspace.user.password} + + Recreating the cspace database with credentials ${db.csadmin.user}:${db.csadmin.user.password} + + + diff --git a/services/account/pstore/src/main/resources/db/postgresql/account.sql b/services/account/pstore/src/main/resources/db/postgresql/account.sql index 7055839bf..e64dd9fb9 100644 --- a/services/account/pstore/src/main/resources/db/postgresql/account.sql +++ b/services/account/pstore/src/main/resources/db/postgresql/account.sql @@ -1,14 +1,33 @@ --- alter table accounts_tenants drop constraint FKFDA649B05A9CEEB5; -DROP TABLE IF EXISTS accounts_common CASCADE; -DROP TABLE IF EXISTS accounts_tenants CASCADE; -DROP TABLE IF EXISTS tenants CASCADE; -DROP SEQUENCE IF EXISTS hibernate_sequence; -create table accounts_common (csid varchar(128) not null, created_at timestamp not null, email varchar(255) not null, mobile varchar(255), person_ref_name varchar(255), phone varchar(255), screen_name varchar(128) not null, - status varchar(15) not null, updated_at timestamp, userid varchar(128) not null, - metadata_protection varchar(255), roles_protection varchar(255), - primary key (csid), unique (userid)); +CREATE TABLE IF NOT EXISTS accounts_common ( + csid VARCHAR(128) NOT NULL PRIMARY KEY, + created_at TIMESTAMP NOT NULL, + email VARCHAR(255) NOT NULL, + mobile VARCHAR(255), + person_ref_name VARCHAR(255), + phone VARCHAR(255), + screen_name VARCHAR(128) NOT NULL, + status VARCHAR(15) NOT NULL, + updated_at TIMESTAMP, + userid VARCHAR(128) NOT NULL UNIQUE, + metadata_protection VARCHAR(255), + roles_protection VARCHAR(255) +); - create table accounts_tenants (HJID int8 not null, tenant_id varchar(128) not null, TENANTS_ACCOUNTS_COMMON_CSID varchar(128), primary key (HJID)); -create table tenants (id varchar(128) not null, created_at timestamp not null, name varchar(255) not null, config_md5hash varchar(255), authorities_initialized boolean not null, disabled boolean not null, updated_at timestamp, primary key (id)); -alter table accounts_tenants add constraint FKFDA649B05A9CEEB5 foreign key (TENANTS_ACCOUNTS_COMMON_CSID) references accounts_common; -create sequence hibernate_sequence; +CREATE TABLE IF NOT EXISTS accounts_tenants ( + hjid INT8 NOT NULL PRIMARY KEY, + tenant_id VARCHAR(128) NOT NULL, + tenants_accounts_common_csid VARCHAR(128), + FOREIGN KEY (tenants_accounts_common_csid) REFERENCES accounts_common +); + +CREATE TABLE IF NOT EXISTS tenants ( + id VARCHAR(128) NOT NULL PRIMARY KEY, + created_at TIMESTAMP NOT NULL, + name VARCHAR(255) NOT NULL, + config_md5hash VARCHAR(255), + authorities_initialized BOOLEAN NOT NULL, + disabled BOOLEAN NOT NULL, + updated_at TIMESTAMP +); + +CREATE SEQUENCE IF NOT EXISTS hibernate_sequence; diff --git a/services/account/pstore/src/main/resources/db/postgresql/account_index.sql b/services/account/pstore/src/main/resources/db/postgresql/account_index.sql index 0eace4edc..903ad7086 100644 --- a/services/account/pstore/src/main/resources/db/postgresql/account_index.sql +++ b/services/account/pstore/src/main/resources/db/postgresql/account_index.sql @@ -4,9 +4,9 @@ -- You may not use this file except in compliance with this License. -- -- use cspace; -CREATE INDEX index_userid ON accounts_common (userid); -CREATE INDEX index_screen_name ON accounts_common (screen_name); -CREATE INDEX index_email ON accounts_common (email); -CREATE INDEX index_person_ref_name ON accounts_common (person_ref_name); -CREATE INDEX index_update_at ON accounts_common (updated_at); -CREATE INDEX index_status ON accounts_common (status); +CREATE INDEX IF NOT EXISTS index_userid ON accounts_common (userid); +CREATE INDEX IF NOT EXISTS index_screen_name ON accounts_common (screen_name); +CREATE INDEX IF NOT EXISTS index_email ON accounts_common (email); +CREATE INDEX IF NOT EXISTS index_person_ref_name ON accounts_common (person_ref_name); +CREATE INDEX IF NOT EXISTS index_update_at ON accounts_common (updated_at); +CREATE INDEX IF NOT EXISTS index_status ON accounts_common (status); diff --git a/services/authentication/pstore/src/main/resources/db/postgresql/authentication.sql b/services/authentication/pstore/src/main/resources/db/postgresql/authentication.sql index d424d2ae6..4760478b5 100644 --- a/services/authentication/pstore/src/main/resources/db/postgresql/authentication.sql +++ b/services/authentication/pstore/src/main/resources/db/postgresql/authentication.sql @@ -1,5 +1,16 @@ -DROP TABLE IF EXISTS users; -create table users (username varchar(128) not null, created_at timestamp not null, passwd varchar(128) not null, updated_at timestamp, primary key (username)); +CREATE TABLE IF NOT EXISTS users ( + username VARCHAR(128) NOT NULL PRIMARY KEY, + created_at TIMESTAMP NOT NULL, + passwd VARCHAR(128) NOT NULL, + updated_at TIMESTAMP +); -DROP TABLE IF EXISTS tokens; -create table tokens (id varchar(128) not null, account_csid varchar(128) not null, tenant_id varchar(128) not null, expire_seconds integer not null, enabled boolean not null, created_at timestamp not null, updated_at timestamp, primary key (id)); +CREATE TABLE IF NOT EXISTS tokens ( + id VARCHAR(128) NOT NULL PRIMARY KEY, + account_csid VARCHAR(128) NOT NULL, + tenant_id VARCHAR(128) NOT NULL, + expire_seconds INTEGER NOT NULL, + enabled BOOLEAN NOT NULL, + created_at TIMESTAMP NOT NULL, + updated_at TIMESTAMP +); diff --git a/services/authorization/pstore/src/main/resources/db/postgresql/acl.sql b/services/authorization/pstore/src/main/resources/db/postgresql/acl.sql index 41f9276eb..a3f9f7f66 100644 --- a/services/authorization/pstore/src/main/resources/db/postgresql/acl.sql +++ b/services/authorization/pstore/src/main/resources/db/postgresql/acl.sql @@ -4,63 +4,53 @@ -- You may not use this file except in compliance with this License. -- --- use cspace; -DROP TABLE IF EXISTS acl_entry; -DROP TABLE IF EXISTS acl_object_identity; -DROP TABLE IF EXISTS acl_sid; -DROP TABLE IF EXISTS acl_class; - -- -- Table structure for table acl_class -- -CREATE TABLE acl_class( - id bigserial not null primary key, - class varchar(100) not null, - constraint unique_uk_2 unique(class) +CREATE TABLE IF NOT EXISTS acl_class ( + id BIGSERIAL NOT NULL PRIMARY KEY, + class VARCHAR(100) NOT NULL UNIQUE ); - -- -- Table structure for table acl_sid -- -CREATE TABLE acl_sid( - id bigserial not null primary key, - principal boolean not null, - sid varchar(100) not null, - constraint unique_uk_1 unique(sid,principal) +CREATE TABLE IF NOT EXISTS acl_sid ( + id BIGSERIAL NOT NULL PRIMARY KEY, + principal BOOLEAN NOT NULL, + sid VARCHAR(100) NOT NULL, + UNIQUE (sid, principal) ); -- -- Table structure for table acl_object_identity -- -CREATE TABLE acl_object_identity( - id bigserial primary key, - object_id_class bigint not null, - object_id_identity bigint not null, - parent_object bigint, - owner_sid bigint, - entries_inheriting boolean not null, - constraint unique_uk_3 unique(object_id_class,object_id_identity), - constraint acl_obj_id_ibfk_1 foreign key(parent_object) references acl_object_identity(id), - constraint acl_obj_id_ibfk_2 foreign key(object_id_class) references acl_class(id), - constraint acl_obj_id_ibfk_3 foreign key(owner_sid) references acl_sid(id) +CREATE TABLE IF NOT EXISTS acl_object_identity ( + id BIGSERIAL PRIMARY KEY, + object_id_class BIGINT NOT NULL, + object_id_identity BIGINT NOT NULL, + parent_object BIGINT, + owner_sid BIGINT, + entries_inheriting BOOLEAN NOT NULL, + UNIQUE (object_id_class, object_id_identity), + FOREIGN KEY (parent_object) REFERENCES acl_object_identity (id), + FOREIGN KEY (object_id_class) REFERENCES acl_class (id), + FOREIGN KEY (owner_sid) REFERENCES acl_sid (id) ); -- -- Table structure for table acl_entry -- -CREATE TABLE acl_entry( - id bigserial primary key, - acl_object_identity bigint not null, - ace_order int not null, - sid bigint not null, - mask integer not null, - granting boolean not null, - audit_success boolean not null, - audit_failure boolean not null, - constraint unique_uk_4 unique(acl_object_identity,ace_order), - constraint acl_entry_ibfk_1 foreign key(acl_object_identity) - references acl_object_identity(id), - constraint acl_entry_ibfk_2 foreign key(sid) references acl_sid(id) +CREATE TABLE IF NOT EXISTS acl_entry ( + id BIGSERIAL PRIMARY KEY, + acl_object_identity BIGINT NOT NULL, + ace_order INT NOT NULL, + sid BIGINT NOT NULL, + mask INTEGER NOT NULL, + granting BOOLEAN NOT NULL, + audit_success BOOLEAN NOT NULL, + audit_failure BOOLEAN NOT NULL, + UNIQUE(acl_object_identity,ace_order), + FOREIGN KEY (acl_object_identity) REFERENCES acl_object_identity (id), + FOREIGN KEY (sid) REFERENCES acl_sid (id) ); - diff --git a/services/authorization/pstore/src/main/resources/db/postgresql/authorization.sql b/services/authorization/pstore/src/main/resources/db/postgresql/authorization.sql index eb9f87fc1..1c8cc3958 100644 --- a/services/authorization/pstore/src/main/resources/db/postgresql/authorization.sql +++ b/services/authorization/pstore/src/main/resources/db/postgresql/authorization.sql @@ -1,28 +1,61 @@ --- alter table permissions_actions drop constraint FK85F82042E2DC84FD; -DROP TABLE IF EXISTS accounts_roles CASCADE; -DROP TABLE IF EXISTS permissions CASCADE; -DROP TABLE IF EXISTS permissions_actions CASCADE; -DROP TABLE IF EXISTS permissions_roles CASCADE; -DROP TABLE IF EXISTS roles CASCADE; -DROP SEQUENCE IF EXISTS hibernate_sequence; +CREATE TABLE IF NOT EXISTS accounts_roles ( + hjid INT8 NOT NULL PRIMARY KEY, + account_id VARCHAR(128) NOT NULL, + created_at TIMESTAMP NOT NULL, + role_id VARCHAR(128) NOT NULL, + role_name VARCHAR(255) NOT NULL, + screen_name VARCHAR(255), + user_id VARCHAR(128) NOT NULL, + UNIQUE (account_id, role_id) +); -create table accounts_roles (HJID int8 not null, account_id varchar(128) not null, created_at timestamp not null, role_id varchar(128) not null, - role_name varchar(255) not null, screen_name varchar(255), user_id varchar(128) not null, primary key (HJID), unique (account_id, role_id)); +CREATE TABLE IF NOT EXISTS permissions ( + csid VARCHAR(128) NOT NULL PRIMARY KEY, + action_group VARCHAR(128), + attribute_name VARCHAR(128), + created_at TIMESTAMP NOT NULL, + description VARCHAR(255), + effect VARCHAR(32) NOT NULL, + metadata_protection VARCHAR(255), + actions_protection VARCHAR(255), + resource_name VARCHAR(128) NOT NULL, + tenant_id VARCHAR(128) NOT NULL, + updated_at TIMESTAMP +); -create table permissions (csid varchar(128) not null, action_group varchar(128), attribute_name varchar(128), created_at timestamp not null, description varchar(255), effect varchar(32) not null, - metadata_protection varchar(255), actions_protection varchar(255), - resource_name varchar(128) not null, tenant_id varchar(128) not null, - updated_at timestamp, primary key (csid)); +CREATE TABLE IF NOT EXISTS permissions_actions ( + hjid INT8 NOT NULL PRIMARY KEY, + name VARCHAR(128) NOT NULL, + objectidentity VARCHAR(128) NOT NULL, + objectidentityresource VARCHAR(128) NOT NULL, + action__permission_csid VARCHAR(128), + FOREIGN KEY (action__permission_csid) REFERENCES permissions +); -create table permissions_actions (HJID int8 not null, name varchar(128) not null, objectIdentity varchar(128) not null, objectIdentityResource varchar(128) not null, - ACTION__PERMISSION_CSID varchar(128), primary key (HJID)); +CREATE TABLE IF NOT EXISTS permissions_roles ( + hjid INT8 NOT NULL PRIMARY KEY, + actiongroup VARCHAR(255), + created_at TIMESTAMP NOT NULL, + permission_id VARCHAR(128) NOT NULL, + permission_resource VARCHAR(255), + role_id VARCHAR(128) NOT NULL, + role_name VARCHAR(255), + UNIQUE (permission_id, role_id) +); - create table permissions_roles (HJID int8 not null, actionGroup varchar(255), created_at timestamp not null, permission_id varchar(128) not null, permission_resource varchar(255), role_id varchar(128) not null, role_name varchar(255), primary key (HJID), unique (permission_id, role_id)); +CREATE TABLE IF NOT EXISTS roles ( + csid VARCHAR(128) NOT NULL PRIMARY KEY, + created_at TIMESTAMP NOT NULL, + description VARCHAR(255), + displayname VARCHAR(200) NOT NULL, + rolegroup VARCHAR(255), + rolename VARCHAR(200) NOT NULL, + tenant_id VARCHAR(128) NOT NULL, + metadata_protection VARCHAR(255), + perms_protection VARCHAR(255), + updated_at TIMESTAMP, + UNIQUE (rolename, tenant_id), + UNIQUE (displayname, tenant_id) +); -create table roles (csid varchar(128) not null, created_at timestamp not null, description varchar(255), displayname varchar(200) not null, rolegroup varchar(255), - rolename varchar(200) not null, tenant_id varchar(128) not null, - metadata_protection varchar(255), perms_protection varchar(255), - updated_at timestamp, primary key (csid), unique (rolename, tenant_id), unique (displayname, tenant_id)); - -alter table permissions_actions add constraint FK85F82042E2DC84FD foreign key (ACTION__PERMISSION_CSID) references permissions; -create sequence hibernate_sequence; +CREATE SEQUENCE IF NOT EXISTS hibernate_sequence; diff --git a/services/authorization/pstore/src/main/resources/db/postgresql/authorization_index.sql b/services/authorization/pstore/src/main/resources/db/postgresql/authorization_index.sql index 63b46ef90..b5940e534 100644 --- a/services/authorization/pstore/src/main/resources/db/postgresql/authorization_index.sql +++ b/services/authorization/pstore/src/main/resources/db/postgresql/authorization_index.sql @@ -4,14 +4,13 @@ -- You may not use this file except in compliance with this License. -- -- use cspace; -CREATE INDEX index_rolename ON roles (rolename); -CREATE INDEX index_rolegroup ON roles (rolegroup); -CREATE INDEX index_tenant_id ON roles (tenant_id); +CREATE INDEX IF NOT EXISTS index_rolename ON roles (rolename); +CREATE INDEX IF NOT EXISTS index_rolegroup ON roles (rolegroup); +CREATE INDEX IF NOT EXISTS index_tenant_id ON roles (tenant_id); -CREATE INDEX index_user_id ON accounts_roles (user_id); -CREATE INDEX index_account_id ON accounts_roles (account_id); -CREATE INDEX index_acct_role_id ON accounts_roles (role_id); - -CREATE INDEX index_permission_id ON permissions_roles (permission_id); -CREATE INDEX index_perm_role_id ON permissions_roles (role_id); +CREATE INDEX IF NOT EXISTS index_user_id ON accounts_roles (user_id); +CREATE INDEX IF NOT EXISTS index_account_id ON accounts_roles (account_id); +CREATE INDEX IF NOT EXISTS index_acct_role_id ON accounts_roles (role_id); +CREATE INDEX IF NOT EXISTS index_permission_id ON permissions_roles (permission_id); +CREATE INDEX IF NOT EXISTS index_perm_role_id ON permissions_roles (role_id); -- 2.47.3