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