BootstrapBean.java

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.model.User;

@Singleton
@Startup
public class BootstrapBean implements BootstrapBeanLocal {

	@EJB
	UserFacade userFacade;

	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;
	}

	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);"	
		});
	}

	@EJB
	private DBModelFacade dbModelFacade;

	@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);
		}
	}

	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);
		}
	}

}