base.jee.api.sql.Setup Maven / Gradle / Ivy

Go to download
/**
 * Creative commons Attribution-NonCommercial license.
 *
 * http://creativecommons.org/licenses/by-nc/2.5/au/deed.en_GB
 *
 * NO WARRANTY IS GIVEN OR IMPLIED, USE AT YOUR OWN RISK.
 */
package base.jee.api.sql;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Map;

import javax.sql.DataSource;

import base.Command;
import base.jee.Constants;
import base.security.PermissionException;

import static base.jee.api.sql.util.SqlFilter.sqlFilter;

public class Setup extends Command {

	private DataSource ds;

	public Setup(DataSource ds) {
		this.ds = ds;

		if(ds == null) {
			throw new IllegalArgumentException("Invalid parameter: ds");
		}
	}

	@Override
	protected void execute() throws IOException {
		Connection c = null;
		try {
			c = ds.getConnection();
			c.setAutoCommit(false);

			int type = 0;
			DatabaseMetaData m = c.getMetaData();
			if(m.getDatabaseProductName().equalsIgnoreCase("mysql")) {
				type = 1;
			}

			PreparedStatement s;

			s = c.prepareStatement(sqlFilter("create table if not exists email (uuid char(36) primary key, to_address varchar(250), email text, retries integer, attempt_at bigint, in_progress boolean)", type));
			s.execute();
			s.close();
			s = c.prepareStatement(sqlFilter("create table if not exists person (uuid char(36) primary key, first_name varchar(128), last_name varchar(128), email varchar(250), username varchar(100), password text, last_auth bigint, last_auth_ip text, created bigint, updated bigint, expiry bigint)", type));
			s.execute();
			s.close();
			s = c.prepareStatement(sqlFilter("create table if not exists batch_load_info (uuid char(36), requestor char(36), executed bigint, primary key(uuid))", type));
			s.execute();
			s.close();
			s = c.prepareStatement(sqlFilter("create table if not exists batch_load_result (uuid char(36), sort_order int, value text, primary key(uuid, sort_order))", type));
			s.execute();
			s.close();
			s = c.prepareStatement(sqlFilter("create table if not exists base_group (uuid char(36) primary key, name varchar(128), type int)", type));
			s.execute();
			s.close();
			s = c.prepareStatement(sqlFilter("create table if not exists group_member (group_uuid char(36), person_uuid char(36), joined bigint, primary key (group_uuid, person_uuid))", type));
			s.execute();
			s.close();
			s = c.prepareStatement(sqlFilter("create table if not exists token (token char(64) primary key, person_uuid char(36), expiry integer, roles text, created integer)", type));
			s.execute();
			s.close();
			s = c.prepareStatement(sqlFilter("create table if not exists request_token (token varchar(64) primary key, person_uuid char(36), expiry integer, type varchar(64), ip varchar(64), data text)", type));
			s.execute();
			s.close();
			s = c.prepareStatement(sqlFilter("create table if not exists audit_event (uuid char(36) primary key, person_uuid char(36), level varchar(16), message text, event_time bigint, ip text)", type));
			s.execute();
			s.close();
			s = c.prepareStatement(sqlFilter("create table if not exists audit_type (audit_event_uuid char(36), resource_uid varchar(64), resource_type varchar(64))", type));
			s.execute();
			s.close();
			s = c.prepareStatement(sqlFilter("create table if not exists person_setting (uuid char(36), name varchar(64), value text, primary key (uuid, name))", type));
			s.execute();
			s.close();
			s = c.prepareStatement(sqlFilter("create table if not exists role (assignee_uuid char(36), role varchar(64), resource varchar(64), uid varchar(64))", type));
			s.execute();
			s.close();
			s = c.prepareStatement(sqlFilter("create table if not exists setting (name varchar(64) primary key, value text)", type));
			s.execute();
			s.close();
			s = c.prepareStatement(sqlFilter("create table if not exists update_notification (uuid char(36) primary key not null, updator char(36), updated bigint, type varchar(64), action varchar(64), uid varchar(250), delivered boolean)", type));
			s.execute();
			s.close();
			s = c.prepareStatement(sqlFilter("create table if not exists template (name varchar(64) primary key)", type));
			s.execute();
			s.close();
			s = c.prepareStatement(sqlFilter("create table if not exists template_resource (template varchar(64), resource varchar(64), content blob, isbinary boolean)", type));
			s.execute();
			s.close();
			s = c.prepareStatement(sqlFilter("create table if not exists throttle (key_value varchar(64) primary key, attempts int, updated bigint)", type));
			s.execute();
			s.close();
			s = c.prepareStatement(sqlFilter("create table if not exists ip_location (start_ip bigint, end_ip bigint, location bigint, primary key (start_ip))", type));
			s.execute();
			s.close();
			s = c.prepareStatement(sqlFilter("create table if not exists location (id bigint, country text, city text, latitude double, longitude double, primary key(id))", type));
			s.execute();
			s.close();

			try {
				s = c.prepareStatement("CREATE INDEX template_resource_lookup on template_resource (template, resource)");
				s.execute();
			} catch(SQLException e) {
			} finally {
				s.close();
			}

			try {
				s = c.prepareStatement("CREATE INDEX ip_location_start on ip_location (start_ip desc)");
				s.execute();
			} catch(SQLException e) {
			} finally {
				s.close();
			}

			try {
				s = c.prepareStatement("CREATE INDEX batch_load_result_idx on batch_load_result (uuid)");
				s.execute();
			} catch(SQLException e) {
			} finally {
				s.close();
			}

			try {
				s = c.prepareStatement("CREATE INDEX token_idx on token (token)");
				s.execute();
			} catch(SQLException e) {
			} finally {
				s.close();
			}

			try {
				s = c.prepareStatement("create index token_person on token(person_uuid,token);");
				s.execute();
			} catch(SQLException e) {
			} finally {
				s.close();
			}

			try {
				s = c.prepareStatement("CREATE INDEX request_token_idx on request_token (token)");
				s.execute();
			} catch(SQLException e) {
			} finally {
				s.close();
			}

			try {
				s = c.prepareStatement("CREATE INDEX audit_event_person_time on audit_event (person_uuid, event_time desc)");
				s.execute();
			} catch(SQLException e) {
			} finally {
				s.close();
			}

			try {
				s = c.prepareStatement("CREATE INDEX audit_event_ip_time on audit_event (ip, event_time desc)");
				s.execute();
			} catch(SQLException e) {
			} finally {
				s.close();
			}

			try {
				s = c.prepareStatement("CREATE INDEX audit_event_time on audit_event(event_time desc)");
				s.execute();
			} catch(SQLException e) {
			} finally {
				s.close();
			}

			try {
				s = c.prepareStatement("insert into setting (name, value) values('throttle.auth.lockout', '300')");
				s.execute();
			} catch(SQLException e) {
			} finally {
				s.close();
			}

			try {
				s = c.prepareStatement("insert into setting (name, value) values('throttle.auth.window', '60')");
				s.execute();
			} catch(SQLException e) {
			} finally {
				s.close();
			}

			try {
				s = c.prepareStatement("insert into setting (name, value) values('throttle.auth.attempts', '3')");
				s.execute();
			} catch(SQLException e) {
			} finally {
				s.close();
			}

			try {
				s = c.prepareStatement("insert into setting (name, value) values('throttle.ip.lockout', '60')");
				s.execute();
			} catch(SQLException e) {
			} finally {
				s.close();
			}

			try {
				s = c.prepareStatement("insert into setting (name, value) values('throttle.ip.window', '20')");
				s.execute();
			} catch(SQLException e) {
			} finally {
				s.close();
			}

			try {
				s = c.prepareStatement("insert into setting (name, value) values('throttle.ip.attempts', '10')");
				s.execute();
			} catch(SQLException e) {
			} finally {
				s.close();
			}

			try {
				s = c.prepareStatement("insert into base_group (uuid, name, type) values('" + Constants.ALL_USERS_GROUP.toString() + "','All users', 0)");
				s.execute();
			} catch(SQLException e) {
			} finally {
				s.close();
			}

			try {
				s = c.prepareStatement("insert into base_group (uuid, name, type) values('" + Constants.AUTHENTICATED_USERS_GROUP.toString() + "','Authenticated users', 0)");
				s.execute();
			} catch(SQLException e) {
			} finally {
				s.close();
			}

			c.commit();
		} catch(SQLException e) {
			throw new IOException(e);
		} finally {
			if(c != null) {
				try { c.rollback(); } catch (SQLException e) { }
				try { c.close(); } catch (SQLException e) { }
			}
		}
	}

	@Override
	public String getJsonParameters() {
		return "{" +
				"}";
	}

	@Override
	public Command newWithParameters(Map parameters) throws IOException, PermissionException {
		throw new IllegalArgumentException("Setup may not be instantiated with parameter map");
	}

}