groovity.portal.lib.schema.grvt Maven / Gradle / Ivy
The newest version!
/*******************************************************************************
* © 2018 Disney | ABC Television Group
*
* Licensed under the Apache License, Version 2.0 (the "Apache License")
* with the following modification; you may not use this file except in
* compliance with the Apache License and the following modification to it:
* Section 6. Trademarks. is deleted and replaced with:
*
* 6. Trademarks. This License does not grant permission to use the trade
* names, trademarks, service marks, or product names of the Licensor
* and its affiliates, except as required to comply with Section 4(c) of
* the License and to reproduce the content of the NOTICE file.
*
* You may obtain a copy of the Apache License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the Apache License with the above modification is
* distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
* KIND, either express or implied. See the Apache License for the specific
* language governing permissions and limitations under the Apache License.
*******************************************************************************/
/**
* Create portal database tables on startup; MySQL compatible datasource
* is required to be registered as 'portalDB' either via JNDI or using
*
* load('/groovity/sql').bind('portalDB',...
*
* @author Alex Vigdor
*/
public static init(){
def sqlLib = load('/groovity/sql')
sqlLib.init('portalDB',[
'''
CREATE TABLE IF NOT EXISTS crew(
id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
name VARCHAR(255) NOT NULL,
description VARCHAR(255),
created DATETIME DEFAULT CURRENT_TIMESTAMP,
modified DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE(name)
) ENGINE=InnoDB
''',
'''
CREATE TABLE IF NOT EXISTS person(
id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
userName VARCHAR(255) NOT NULL,
fullName VARCHAR(255),
nickName VARCHAR(255),
digest VARCHAR(255),
created DATETIME DEFAULT CURRENT_TIMESTAMP,
modified DATETIME DEFAULT CURRENT_TIMESTAMP,
suspended BOOL DEFAULT 0,
lastLogin DATETIME,
PRIMARY KEY (id),
UNIQUE(userName)
) ENGINE=InnoDB
''',
'''
CREATE TABLE IF NOT EXISTS membership(
id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
crewId INT UNSIGNED NOT NULL,
personId INT UNSIGNED NOT NULL,
created DATETIME DEFAULT CURRENT_TIMESTAMP,
modified DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE (crewId,personId),
INDEX (crewId),
INDEX (personId),
FOREIGN KEY (crewId) REFERENCES crew(id),
FOREIGN KEY (personId) REFERENCES person(id)
) ENGINE=InnoDB
''',
'''
CREATE TABLE IF NOT EXISTS leadership(
id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
crewId INT UNSIGNED NOT NULL,
personId INT UNSIGNED NOT NULL,
created DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE (crewId,personId),
INDEX (crewId),
INDEX (personId),
FOREIGN KEY (crewId) REFERENCES crew(id),
FOREIGN KEY (personId) REFERENCES person(id)
) ENGINE=InnoDB
''',
'''
CREATE TABLE IF NOT EXISTS role(
id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
path VARCHAR(255) NOT NULL,
role VARCHAR(255) NOT NULL,
crewId INT UNSIGNED NOT NULL,
createdBy INT UNSIGNED NOT NULL,
created DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE (crewId,path,role),
FOREIGN KEY (crewId) REFERENCES crew(id),
FOREIGN KEY (createdBy) REFERENCES person(id)
) ENGINE=InnoDB
''',
'''
CREATE TABLE IF NOT EXISTS config(
id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
path VARCHAR(255) NOT NULL,
property VARCHAR(255) NOT NULL,
value VARCHAR(255) NOT NULL,
createdBy INT UNSIGNED NOT NULL,
created DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
FOREIGN KEY (createdBy) REFERENCES person(id)
) ENGINE=InnoDB
''',
'''
CREATE TABLE IF NOT EXISTS notice(
id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
message VARCHAR(2048) NOT NULL,
link VARCHAR(2048) NOT NULL,
sender VARCHAR(512),
priority TINYINT UNSIGNED DEFAULT 127,
sent DATETIME DEFAULT CURRENT_TIMESTAMP,
expires DATETIME,
PRIMARY KEY (id),
INDEX (sender),
INDEX (sent),
INDEX (expires)
) ENGINE=InnoDB
''',
'''
CREATE TABLE IF NOT EXISTS delivery(
id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
personId INT UNSIGNED NOT NULL,
noticeId INT UNSIGNED NOT NULL,
delivered DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE (personId,noticeId),
INDEX (noticeId),
INDEX (delivered),
FOREIGN KEY (personId) REFERENCES person(id),
FOREIGN KEY (noticeId) REFERENCES notice(id)
) ENGINE=InnoDB
''',
'''
CREATE TABLE IF NOT EXISTS inbox(
id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
personId INT UNSIGNED NOT NULL,
lastAccess DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE (personId),
FOREIGN KEY (personId) REFERENCES person(id)
) ENGINE=InnoDB
''',
'''
CREATE OR REPLACE VIEW crewView AS (
SELECT
c.id as crewId, c.name as crewName, m.id, m.created, p.id as personId, p.userName, p.fullName, p.nickName
FROM
crew c
LEFT JOIN
membership m
ON m.crewId = c.id
LEFT JOIN
person p
ON p.id = m.personId
)
''',
'''
CREATE OR REPLACE VIEW crewLeadersView AS (
SELECT
c.id as crewId, c.name as crewName, m.id, m.created, p.id as personId, p.userName, p.fullName, p.nickName
FROM
crew c
LEFT JOIN
leadership m
ON m.crewId = c.id
LEFT JOIN
person p
ON p.id = m.personId
)
''',
'''
CREATE OR REPLACE VIEW currentConfigView AS (
SELECT c.*, p.id as personId, p.userName, p.fullName, p.nickName FROM config c
LEFT OUTER JOIN config m
ON c.path=m.path AND c.property=m.property AND c.created