BootstrapBean.java

/*
 * Copyright Codecrew Ry
 * 
 * All rights reserved.
 * 
 * This license applies to any software containing a notice placed by the 
 * copyright holder. Such software is herein referred to as the Software. 
 * This license covers modification, distribution and use of the Software. 
 * 
 * Any distribution and use in source and binary forms, with or without 
 * modification is not permitted without explicit written permission from the 
 * copyright owner. 
 * 
 * A non-exclusive royalty-free right is granted to the copyright owner of the 
 * Software to use, modify and distribute all modifications to the Software in 
 * future versions of the Software. 
 * 
 */
package fi.codecrew.moya.beans;

import java.util.ArrayList;
import java.util.List;

import javax.annotation.PostConstruct;
import javax.ejb.EJB;
import javax.ejb.Singleton;
import javax.ejb.Startup;

import fi.codecrew.moya.facade.DBModelFacade;
import fi.codecrew.moya.facade.UserFacade;
import fi.codecrew.moya.model.DBModel;
import fi.codecrew.moya.sql.DDLBuilder;

@Singleton
@Startup
public class BootstrapBean implements BootstrapBeanLocal {

	@EJB
	UserFacade userFacade;

	@EJB
	private DBModelFacade dbModelFacade;

	private static final List<String[]> dbUpdates = new ArrayList<String[]>();
	static {
		// {"Query1","Query2",...}
		dbUpdates.add(new String[] { "" }); // first version, no changes
		dbUpdates.add(new String[] { "ALTER TABLE tournaments ADD COLUMN game integer NOT NULL REFERENCES tournament_games(id)" });
		dbUpdates.add(new String[] { "ALTER TABLE tournaments ADD COLUMN rules integer NOT NULL REFERENCES tournament_rules(id)" });
		dbUpdates.add(new String[] { "ALTER TABLE tournaments ADD COLUMN max_participants integer NOT NULL DEFAULT 0" });
		dbUpdates.add(new String[] { "ALTER TABLE tournament_participants ADD COLUMN tournament integer NOT NULL REFERENCES tournaments(id)" });
		dbUpdates.add(new String[] { "DELETE FROM application_permissions WHERE application = 'MAP' and permission = 'RELEASE_PLACE'" });
		dbUpdates.add(new String[] { "ALTER TABLE site_page_content ADD COLUMN locale varchar(10)" });
		dbUpdates.add(new String[] { "ALTER TABLE products ALTER COLUMN vat TYPE NUMERIC(4,3)" });
		dbUpdates.add(new String[] { "ALTER TABLE actionlog_messages DROP COLUMN crew" });
		dbUpdates.add(new String[] { "delete from application_permissions where application ilike '%terminal%'" });
		dbUpdates.add(new String[] {
				"ALTER TABLE org_roles ADD ldap_role boolean not null default false",
				"ALTER TABLE org_roles ADD ldap_weight integer NOT NULL default 100"
		});
		// barcodefuckup
		dbUpdates.add(new String[] {
				"DROP TABLE card_barcode",
				"ALTER TABLE printed_cards DROP COLUMN barcode;",
				"ALTER TABLE printed_cards DROP COLUMN rfid_uid;",
				"ALTER TABLE reader_events ADD COLUMN event_users_id integer REFERENCES event_users(id) DEFAULT null;",
				"ALTER TABLE reader_events ADD COLUMN places_id integer REFERENCES places(id) DEFAULT null;",
				"ALTER TABLE reader_events ADD COLUMN products_id integer REFERENCES products(id) DEFAULT null;",
				"ALTER TABLE reader_events ADD COLUMN type text NOT NULL DEFAULT 'UNKNOWN';",
				"ALTER TABLE reader_events DROP COLUMN gamepoint;",
				"ALTER TABLE reader_events ALTER COLUMN type DROP DEFAULT;",
		});

		dbUpdates.add(new String[] {
				"delete from menu_navigation where item_id in (select id from menuitem where url in ( '/actionlog/messagelist'))",
				"delete from menuitem where url in ('/actionlog/messagelist')",
		});
		dbUpdates.add(new String[] {
				"alter table compos add hidden boolean default false not null"
		});

		dbUpdates.add(alterTables("ADD COLUMN meta json",
				"account_events",
				"actionlog_message_responses",
				"actionlog_message_tags",
				"actionlog_messages",
				"api_application_instances",
				"api_applications",
				"application_permissions",
				"approvals",
				"bill_lines",
				"bills",
				"card_code",
				"card_templates",
				"compo_entries",
				"compo_entry_files",
				"compo_entry_participations",
				"compo_votes",
				"compos",
				"discount_instances",
				"discounts",
				"event_domains",
				"event_log",
				"event_log_types",
				"event_organiser",
				"event_private_properties",
				"event_properties",
				"event_users",
				"events",
				"feedback",
				"food_wave_templates",
				"food_waves",
				"game_ids",
				"group_memberships",
				"groups",
				"inventory_events",
				"licensetargets",
				"licensecodes",
				"locations",
				"maps",
				"match_results",
				"matches",
				"menu_navigation",
				"menuitem",
				"news",
				"news_groups",
				"org_roles",
				"places",
				"poll",
				"poll_answer",
				"poll_question",
				"possible_answer",
				"printed_cards",
				"product_limitations",
				"products",
				"reader_events",
				"readers",
				"roles",
				"sales_entity",
				"salespoint",
				"site_page_content",
				"site_pages",
				"tournament_games",
				"tournament_participants",
				"tournament_rules",
				"tournament_team_members",
				"tournaments",
				"user_approvals",
				"user_images",
				"user_notes",
				"users"));

		dbUpdates.add(new String[] { "CREATE TABLE network_associations (id  SERIAL NOT NULL, create_time TIMESTAMPTZ NOT NULL, ip TEXT, mac TEXT, meta TEXT, modify_time TIMESTAMPTZ NOT NULL, status TEXT NOT NULL, event INTEGER, event_user INTEGER, place INTEGER, PRIMARY KEY (id))" });

		dbUpdates.add(new String[] {
				"CREATE TABLE card_text_data (id  SERIAL NOT NULL, font_name TEXT NOT NULL, font_style INTEGER NOT NULL, size INTEGER NOT NULL, text TEXT, text_alignment TEXT NOT NULL, card_text_data_type TEXT NOT NULL, x INTEGER NOT NULL, y INTEGER NOT NULL, z_index INTEGER NOT NULL, PRIMARY KEY (id));"
		});
		dbUpdates.add(new String[] {
				"CREATE TABLE card_object_data (id  SERIAL NOT NULL, card_object_data_type TEXT NOT NULL, size INTEGER NOT NULL, x INTEGER NOT NULL, y INTEGER NOT NULL, z_index INTEGER NOT NULL, PRIMARY KEY (id));"
		});

		dbUpdates.add(new String[] {
				"ALTER TABLE card_text_data ADD COLUMN card_templates_id integer REFERENCES card_templates(id) DEFAULT null;",
				"ALTER TABLE card_object_data ADD COLUMN card_templates_id integer REFERENCES card_templates(id) DEFAULT null;"
		});

		dbUpdates.add(alterTables("ADD COLUMN meta json",
				"card_text_data",
				"card_object_data"
				));
		dbUpdates.add(new String[] {
				"ALTER TABLE card_text_data ADD COLUMN font_color_r integer DEFAULT 0;",
				"ALTER TABLE card_text_data ADD COLUMN font_color_g integer DEFAULT 0;",
				"ALTER TABLE card_text_data ADD COLUMN font_color_b integer DEFAULT 0;"
		});
		dbUpdates.add(new String[] {
				"ALTER TABLE card_text_data ALTER COLUMN size TYPE numeric(5,2);",
				"ALTER TABLE card_object_data ALTER COLUMN size TYPE numeric(5,2);"
		});
		dbUpdates.add(new String[] {
				"ALTER TABLE card_text_data DROP COLUMN font_style;",
				"ALTER TABLE card_text_data ADD COLUMN font_style TEXT NOT NULL DEFAULT 'PLAIN';"
		});

		dbUpdates.add(new String[] {
				"ALTER TABLE event_log RENAME log_id TO id;"
		});

		dbUpdates.add(new String[] {
				"ALTER TABLE network_associations ALTER COLUMN meta TYPE json USING (meta::json);"
		});

		dbUpdates.add(new String[] { "CREATE TABLE lecture_groups (id  SERIAL NOT NULL, event_id integer NOT NULL,  description TEXT, name TEXT,  select_count INTEGER, meta json, PRIMARY KEY (id))",
				"CREATE TABLE lectures (id  SERIAL NOT NULL, description TEXT, hours numeric(10,2), max_participants_count INTEGER, name TEXT, start_time TIMESTAMPTZ, lecture_group_id INTEGER, meta json, PRIMARY KEY (id))",
				"CREATE TABLE lecture_roles (role_id INTEGER NOT NULL, lecture_id INTEGER NOT NULL, PRIMARY KEY (role_id, lecture_id))",
				"CREATE TABLE lecture_participants (eventuser_id INTEGER NOT NULL, lecture_id INTEGER NOT NULL, PRIMARY KEY (eventuser_id, lecture_id))",
				"ALTER TABLE lectures ADD CONSTRAINT FK_lectures_lecture_group_id FOREIGN KEY (lecture_group_id) REFERENCES lecture_groups (id)",
				"ALTER TABLE lecture_roles ADD CONSTRAINT FK_lecture_roles_lecture_id FOREIGN KEY (lecture_id) REFERENCES lectures (id)",
				"ALTER TABLE lecture_roles ADD CONSTRAINT FK_lecture_roles_role_id FOREIGN KEY (role_id) REFERENCES roles (id)",
				"ALTER TABLE lecture_participants ADD CONSTRAINT FK_lecture_participants_eventuser_id FOREIGN KEY (eventuser_id) REFERENCES event_users (id)",
				"ALTER TABLE lecture_participants ADD CONSTRAINT FK_lecture_participants_lecture_id FOREIGN KEY (lecture_id) REFERENCES lectures (id)" });

		dbUpdates.add(new String[] { "ALTER TABLE roles ADD COLUMN user_selectable_role boolean DEFAULT false;" });

		dbUpdates.add(new String[] {
				"ALTER TABLE group_memberships ADD COLUMN place_product INTEGER",
				"ALTER TABLE group_memberships ADD CONSTRAINT FK_group_memberships_place_product FOREIGN KEY (place_product) REFERENCES products (id)"
		});

		dbUpdates.add(new String[] {
				"ALTER TABLE events ADD COLUMN ticket_sales_begin timestamp without time zone DEFAULT null;",
		});

		dbUpdates.add(new String[] {
				"ALTER TABLE events ADD COLUMN theme varchar(255) DEFAULT null;",
		});

		// Deprekoidaan ko. enum, jolloin se pitää myös poistaa tietokannasta
		// tai saadaan poikkeuksia nyrkillä kurkusta .
		dbUpdates.add(new String[] {
				"DELETE FROM  product_productflags where productflags = 'PREPAID_INSTANT_CREATE'"
		});

		dbUpdates.add(new String[] {
				"ALTER TABLE food_wave_templates ADD COLUMN wait_payments_minutes integer DEFAULT null;",
		});

		dbUpdates.add(new String[] {
				"ALTER TABLE maps ADD COLUMN width integer",
				"ALTER TABLE maps ADD COLUMN height integer",
		});

		dbUpdates.add(new String[] {
				"CREATE TABLE place_slots (id SERIAL NOT NULL, CREATED TIMESTAMPTZ, DESCRIPTION TEXT, meta json, USED TIMESTAMPTZ, BILL_id INTEGER NOT NULL, PRODUCT_id INTEGER NOT NULL, PLACE_id INTEGER UNIQUE, PRIMARY KEY (id))",
				"ALTER TABLE place_slots ADD CONSTRAINT FK_place_slots_BILL_id FOREIGN KEY (BILL_id) REFERENCES bills (id)",
				"ALTER TABLE place_slots ADD CONSTRAINT FK_place_slots_PLACE_id FOREIGN KEY (PLACE_id) REFERENCES places (id)",
				"ALTER TABLE place_slots ADD CONSTRAINT FK_place_slots_PRODUCT_id FOREIGN KEY (PRODUCT_id) REFERENCES products (id)"
		});

		dbUpdates.add(new String[] {
				"ALTER TABLE maps ADD COLUMN mime_type TEXT default 'image/png'",
		});

		dbUpdates.add(new String[] {
				"ALTER TABLE group_memberships ADD COLUMN entered_event_admin_id INTEGER",
				"ALTER TABLE group_memberships ADD CONSTRAINT FK_group_memberships_entered_event_admin_id FOREIGN KEY (entered_event_admin_id) REFERENCES event_users (id)",
		});

		dbUpdates.add(new String[] {
				"ALTER TABLE group_memberships DROP COLUMN place_product",
		});

		dbUpdates.add(new String[] {
				"DELETE FROM product_limitations_roles where productlimitation_id in (SELECT id FROM product_limitations WHERE TYPE = 'PLACE')",
				"DELETE FROM products_product_limitations where productlimits_id in (SELECT id FROM product_limitations WHERE TYPE = 'PLACE')",
				"DELETE FROM product_limitations WHERE TYPE = 'PLACE'",

		});

		dbUpdates.add(new String[] {
				new DDLBuilder().createTable("vips")
						.serialPK("id")
						.text("description", false)
						.timestampTZ("created", false, "default", "now()")
						.reference("event_id", false, "events")
						.reference("vip_event_user_id", true, "event_users")
						.reference("creator_event_user_id", false, "event_users")
						.reference("host_event_user_id", false, "event_users")
						.field("meta", "json", true)
						.toString()
		});

		dbUpdates.add(new String[] {
				"CREATE TABLE vip_product_deliveries (id SERIAL NOT NULL, DELIVERYTIME TIMESTAMPTZ NOT NULL, meta json, NOTES TEXT, quantity DECIMAL(24,4) NOT NULL, DELIVERER_id INTEGER NOT NULL, VIPPRODUCT_id INTEGER NOT NULL, PRIMARY KEY (id))",
				"CREATE TABLE vip_products (id SERIAL NOT NULL, meta json, NAME TEXT, NOTES TEXT, quantity DECIMAL(24,4) NOT NULL, PRODUCT_id INTEGER, VIP_id INTEGER NOT NULL, PRIMARY KEY (id))",
				"ALTER TABLE vip_product_deliveries ADD CONSTRAINT FK_vip_product_deliveries_DELIVERER_id FOREIGN KEY (DELIVERER_id) REFERENCES event_users (id)",
				"ALTER TABLE vip_product_deliveries ADD CONSTRAINT FK_vip_product_deliveries_VIPPRODUCT_id FOREIGN KEY (VIPPRODUCT_id) REFERENCES vip_products (id)",
				"ALTER TABLE vip_products ADD CONSTRAINT FK_vip_products_PRODUCT_id FOREIGN KEY (PRODUCT_id) REFERENCES products (id)",
				"ALTER TABLE vip_products ADD CONSTRAINT FK_vip_products_VIP_id FOREIGN KEY (VIP_id) REFERENCES vips (id)",
				"ALTER TABLE vips ADD shortdescr TEXT"
		});

		dbUpdates.add(new String[] {
				"ALTER TABLE products ADD shop_required_role INTEGER",
				"ALTER TABLE products ADD CONSTRAINT FK_products_shop_required_role FOREIGN KEY (shop_required_role) REFERENCES roles (id)",
		});
	}

	public BootstrapBean() {
	}

	/**
	 * Runs a "ALTER TABLE
	 * <table>
	 * <statement>" for each of tables.
	 * 
	 * @param alterStatement
	 *            e.g. "ADD meta json"
	 * @param tables
	 *            table name strings
	 * @return1
	 */
	private static final String[] alterTables(String alterStatement, String... tables) {
		String[] strings = new String[tables.length];
		for (int i = 0; i < tables.length; i++) {
			strings[i] = "ALTER TABLE  \"" + tables[i] + "\" " + alterStatement;
		}
		return strings;
	}

	@PostConstruct
	public void bootstrap() {
		this.doDBUpdates();
	}

	private void doDBUpdates() {
		DBModel dBm = dbModelFacade.findLastRevision();
		Integer upIdx = dbUpdates.size() - 1;
		if (dBm != null) {
			Integer revId = dBm.getRevision();

			// Check if we have unapplied updates
			if ((dbUpdates.size() - 1) > revId) {
				// Oh yes we do

				this.executeUpdates(revId, upIdx);
			} else if (upIdx < revId) {
				throw new RuntimeException("Sanity check failed! DB is newer than the codebase!");
			}
		} else {
			// DB is up to date by default! We need to mark the current version
			// down though.
			dBm = new DBModel();
			dBm.setRevision(upIdx);
			dbModelFacade.create(dBm);
		}
	}

	private void executeUpdates(Integer dbRev, Integer targetRev) {
		while (dbRev < targetRev) {
			dbRev++;

			System.out.format("attempt to upgrade db from version %d -> %d", (dbRev - 1), dbRev);

			String[] queries = dbUpdates.get(dbRev);
			for (String query : queries) {
				if (!query.isEmpty()) {
					dbModelFacade.updateModel(query);
				}
			}
			System.out.println("update ok!");

			DBModel dBm = new DBModel();
			dBm.setRevision(dbRev);
			dbModelFacade.create(dBm);
		}
	}
	// We will never run this again with empty database
	//	public void saneDefaults() {
	//		User adminUser = userFacade.findByLogin("admin");
	//		if (adminUser == null) {
	//			adminUser = new User();
	//			adminUser.setLogin("admin");
	//			// adminUser.setSuperadmin(true);
	//			adminUser.resetPassword("admin");
	//			userFacade.create(adminUser);
	//		}
	//	}

}