ResetDatabaseScript.groovy revision a4bf32dcbc74d4ad01c20d0e9b89ceeeed86fd1a
/*
* The contents of this file are subject to the terms of the Common Development and
* Distribution License (the License). You may not use this file except in compliance with the
* License.
*
* You can obtain a copy of the License at legal/CDDLv1.0.txt. See the License for the
* specific language governing permission and limitations under the License.
*
* When distributing Covered Software, include this CDDL Header Notice in each file and include
* the License file at legal/CDDLv1.0.txt. If applicable, add the following below the CDDL
* Header, with the fields enclosed by brackets [] replaced by your own identifying
* information: "Portions copyright [year] [name of copyright owner]".
*
* Copyright 2010-2015 ForgeRock AS.
*/
// Parameters:
// The connector sends us the following:
// connection : SQL connection
// action: String correponding to the action ("RUNSCRIPTONCONNECTOR" here)
// log: a handler to the Log facility
// options: a handler to the OperationOptions Map
//
// Arguments can be passed to the script in the REST call, e.g.:
//
// curl -k --header "X-OpenIDM-Username: openidm-admin" \
// --header "X-OpenIDM-Password: openidm-admin" \
// --header "Content-Type: application/json" \
// --request POST "https://localhost:8443/openidm/system/hrdb?_action=script&scriptId=ResetDatabase" \
// -d "{\"arg1\":\"foo\",\"arg2\":\"bar\"}"
//
// These arguments can be accessed here by name, e.g.
//
// def firstArg = arg1 as String;
//
// Note that these can be complex types; Arguments are passed in as Object type.
// Create and use the db if it's not present and clear out old tables if they exist
try {
try {
} catch(Exception e){}
} catch(Exception e){}
// Create our tables
CREATE TABLE users(
id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
uid char(32) NOT NULL,
password char(128),
firstname varchar(32) NOT NULL DEFAULT '',
lastname varchar(32) NOT NULL DEFAULT '',
fullname varchar(32),
email varchar(128),
organization varchar(32),
timestamp timestamp DEFAULT now()
);
""")
CREATE TABLE car(
users_id int(11) NOT NULL,
year varchar(4) NOT NULL,
make varchar(32) NOT NULL,
model varchar(32) NOT NULL,
FOREIGN KEY (users_id) REFERENCES users(id) ON DELETE CASCADE
);
""")
CREATE TABLE groups(
id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
gid char(32) NOT NULL,
name varchar(32) NOT NULL DEFAULT '',
description varchar(32),
timestamp timestamp DEFAULT now()
);
""")
CREATE TABLE groups_users(
users_id int(11) NOT NULL,
groups_id int(11) NOT NULL,
FOREIGN KEY (users_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (groups_id) REFERENCES groups(id) ON DELETE CASCADE
);
""")
CREATE TABLE organizations(
id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(32) NOT NULL DEFAULT '',
description varchar(32),
timestamp timestamp DEFAULT now()
);
""")
// Now populate the tables
INSERT INTO users
( uid, password, firstname, lastname, fullname, email, organization, timestamp )
VALUES
("bob",sha1("password1"),"Bob", "Fleming","Bob Fleming","Bob.Fleming@example.com","HR",CURRENT_TIMESTAMP),
("rowley",sha1("password2"),"Rowley","Birkin","Rowley Birkin","Rowley.Birkin@example.com","SALES",CURRENT_TIMESTAMP),
("louis",sha1("password3"),"Louis", "Balfour","Louis Balfour","Louis.Balfor@example.com","SALES",CURRENT_TIMESTAMP),
("john",sha1("password4"),"John", "Smith","John Smith","John.Smith@example.com","SUPPORT",CURRENT_TIMESTAMP),
""")
INSERT INTO car (users_id,year,make,model) VALUES
""")
""")
""")
INSERT INTO groups_users (users_id, groups_id) SELECT id, 1 FROM users where organization='HR';
""")
INSERT INTO groups_users (users_id, groups_id) SELECT id, 2 FROM users where organization <> 'HR';
""")
""")
""")
""")
""")
// do a query to check it all worked ok
throw new InternalServerErrorException("Reset of HRDB was not successful");
}
return "Database reset successful."