ResetDatabaseScript.groovy revision 64ca5d02a4179512c5d68956e5088aa9c6ccc287
/*
*
* Copyright (c) 2010 ForgeRock Inc. All Rights Reserved
*
* 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
* See the License for the specific language governing
* permission and limitations under the License.
*
* When distributing Covered Code, include this CDDL
* Header Notice in each file and include the License file
* at OpenIDM/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 Copyrighted 2010 [name of copyright owner]"
*
* $Id$
*/
// 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
// scriptArguments: a Map<String,Object> containing the arguments that are passed by the initial caller
// 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
return "Database reset successful."