CREATE TABLE accesscontrol_principals (id BIGINT AUTO_INCREMENT, principal_id BIGINT UNSIGNED NOT NULL, principal_type VARCHAR(255) NOT NULL, lft BIGINT UNSIGNED NOT NULL, rgt BIGINT UNSIGNED NOT NULL, level BIGINT UNSIGNED NOT NULL, INDEX index_lft_idx (lft), INDEX index_rgt_idx (rgt), INDEX index_lft_rgt_idx (lft, rgt), INDEX index_level_idx (level), INDEX index_lft_rgt_level_idx (lft, rgt, level), INDEX index_principal_idx (principal_id, principal_type), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE accesscontrol_privileges (name VARCHAR(32), description TEXT, PRIMARY KEY(name)) ENGINE = INNODB;
CREATE TABLE accesscontrol_privilege_entries (id BIGINT AUTO_INCREMENT, principal_id BIGINT UNSIGNED NOT NULL, principal_type VARCHAR(255) NOT NULL, resource VARCHAR(32) NOT NULL, privilege VARCHAR(32) NOT NULL, allow TINYINT(1) NOT NULL, INDEX index_principal_idx (principal_id, principal_type), INDEX index_principal_resource_idx (principal_id, principal_type, resource), UNIQUE INDEX index_principal_resource_privilege_idx (principal_id, principal_type, resource, privilege), INDEX resource_idx (resource), INDEX privilege_idx (privilege), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE accesscontrol_resources (name VARCHAR(32), description TEXT, PRIMARY KEY(name)) ENGINE = INNODB;
CREATE TABLE accesscontrol_resource_privileges (resource VARCHAR(32), privilege VARCHAR(32), PRIMARY KEY(resource, privilege)) ENGINE = INNODB;
CREATE TABLE accesscontrol_rights (name VARCHAR(32), description TEXT, INDEX index_name_idx (name), PRIMARY KEY(name)) ENGINE = INNODB;
CREATE TABLE accesscontrol_right_entries (id BIGINT AUTO_INCREMENT, principal_id BIGINT UNSIGNED NOT NULL, principal_type VARCHAR(255) NOT NULL, node_id BIGINT UNSIGNED NOT NULL, right_name VARCHAR(32) NOT NULL, allow TINYINT(1) NOT NULL, INDEX index_principal_idx (principal_id, principal_type), INDEX index_principal_node_idx (principal_id, principal_type, node_id), UNIQUE INDEX index_principal_node_right_idx (principal_id, principal_type, node_id, right_name), INDEX node_id_idx (node_id), INDEX right_name_idx (right_name), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE auth_methods (id VARCHAR(32), description TEXT, managing_class VARCHAR(128), created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE client_contexts (id BIGINT UNSIGNED AUTO_INCREMENT, name VARCHAR(64), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE client_context_settings (context_id BIGINT UNSIGNED, name VARCHAR(128), value TEXT, PRIMARY KEY(context_id, name)) ENGINE = INNODB;
CREATE TABLE client_context_trees (context_id BIGINT UNSIGNED, tree_id BIGINT UNSIGNED, position_hint VARCHAR(32), PRIMARY KEY(context_id, tree_id)) ENGINE = INNODB;
CREATE TABLE client_context_users (context_id BIGINT UNSIGNED, user_id BIGINT UNSIGNED, consumer_name VARCHAR(64), PRIMARY KEY(context_id, user_id, consumer_name)) ENGINE = INNODB;
CREATE TABLE comet_events (id BIGINT UNSIGNED AUTO_INCREMENT, sessiontoken VARCHAR(64), live_until DATETIME, INDEX index_live_until_idx (live_until), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE comet_event_contents (id BIGINT UNSIGNED, name VARCHAR(64), value TEXT, serialized_value TINYINT(1), PRIMARY KEY(id, name)) ENGINE = INNODB;
CREATE TABLE content_types (id VARCHAR(32), title VARCHAR(64) NOT NULL, description TEXT, managing_class VARCHAR(128) NOT NULL, PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE copy_nodes (id BIGINT UNSIGNED AUTO_INCREMENT, sourcenode_id BIGINT UNSIGNED NOT NULL, tree_id BIGINT UNSIGNED, PRIMARY KEY(id, tree_id)) ENGINE = INNODB;
CREATE TABLE cronjobs (minute VARCHAR(16), hour VARCHAR(16), day VARCHAR(16), month VARCHAR(16), weekday VARCHAR(16), command VARCHAR(128), enabled TINYINT(1) DEFAULT '1' NOT NULL, PRIMARY KEY(minute, hour, day, month, weekday, command)) ENGINE = INNODB;
CREATE TABLE groups (id BIGINT UNSIGNED AUTO_INCREMENT, name VARCHAR(32) NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE hashed_passwords (id BIGINT AUTO_INCREMENT, user_id BIGINT UNSIGNED, hash TEXT NOT NULL, INDEX user_idx (user_id), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE hook_bindings (id BIGINT UNSIGNED AUTO_INCREMENT, plugin_name VARCHAR(64), name VARCHAR(128) NOT NULL, class VARCHAR(128) NOT NULL, method VARCHAR(128), singleton TINYINT(1), INDEX plugin_name_idx (plugin_name), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE nodes (id BIGINT UNSIGNED AUTO_INCREMENT, title VARCHAR(64), state BIGINT UNSIGNED DEFAULT 1 NOT NULL, publish_timestamp DATETIME, revoke_timestamp DATETIME, content_type VARCHAR(32), content_id BIGINT UNSIGNED, tree_id BIGINT UNSIGNED NOT NULL, lft BIGINT UNSIGNED NOT NULL, rgt BIGINT UNSIGNED NOT NULL, level BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, INDEX index_lft_idx (lft), INDEX index_rgt_idx (rgt), INDEX index_level_idx (level), INDEX index_lft_rgt_tree_id_idx (lft, rgt, tree_id), INDEX index_lft_rgt_level_tree_id_idx (lft, rgt, level, tree_id), INDEX tree_id_idx (tree_id), INDEX content_type_idx (content_type), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE node_syncs (id BIGINT UNSIGNED AUTO_INCREMENT, node_id BIGINT UNSIGNED NOT NULL, tree_id BIGINT UNSIGNED NOT NULL, synctype VARCHAR(32) NOT NULL, name VARCHAR(64), enable_http_access TINYINT(1) DEFAULT '0' NOT NULL, UNIQUE INDEX node_id_name_idx (node_id, name), INDEX node_id_idx (node_id), INDEX synctype_idx (synctype), INDEX tree_id_idx (tree_id), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE node_sync_attributes (sync_id BIGINT UNSIGNED, name VARCHAR(64), value TEXT, PRIMARY KEY(sync_id, name)) ENGINE = INNODB;
CREATE TABLE node_sync_types (id VARCHAR(32), title VARCHAR(64) NOT NULL, description TEXT, kind VARCHAR(255), managing_class VARCHAR(128) NOT NULL, PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE plugins (name VARCHAR(64), description TEXT, release_version VARCHAR(32) NOT NULL, enabled TINYINT(1), PRIMARY KEY(name)) ENGINE = INNODB;
CREATE TABLE plugin_parameters (plugin_name VARCHAR(64), name VARCHAR(32), value TEXT, PRIMARY KEY(plugin_name, name)) ENGINE = INNODB;
CREATE TABLE user_sessions (token VARCHAR(128), created DATETIME NOT NULL, last_activity DATETIME, user_id BIGINT UNSIGNED NOT NULL, consumer_name VARCHAR(64) NOT NULL, impersonated_user_id BIGINT UNSIGNED, auth_method_name VARCHAR(32) NOT NULL, state VARCHAR(16) NOT NULL, INDEX user_id_idx (user_id), INDEX auth_method_name_idx (auth_method_name), PRIMARY KEY(token)) ENGINE = INNODB;
CREATE TABLE user_session_impersonatedvalues (token VARCHAR(128), name VARCHAR(128), value text, PRIMARY KEY(token, name)) ENGINE = INNODB;
CREATE TABLE user_session_values (token VARCHAR(128), name VARCHAR(128), value text, PRIMARY KEY(token, name)) ENGINE = INNODB;
CREATE TABLE tasks (name VARCHAR(64), class VARCHAR(128) NOT NULL, plugin VARCHAR(32), context VARCHAR(32) NOT NULL, INDEX name_idx (name), INDEX plugin_idx (plugin), PRIMARY KEY(name)) ENGINE = INNODB;
CREATE TABLE task_aliases (name VARCHAR(64), alias_for VARCHAR(64) NOT NULL, plugin VARCHAR(32), context VARCHAR(32) NOT NULL, INDEX alias_for_idx (alias_for), PRIMARY KEY(name)) ENGINE = INNODB;
CREATE TABLE taskqueue_entries (id BIGINT UNSIGNED AUTO_INCREMENT, taskname VARCHAR(128) NOT NULL, method VARCHAR(128) NOT NULL, format VARCHAR(128), body TEXT, priority BIGINT UNSIGNED DEFAULT 0 NOT NULL, PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE taskqueue_entry_arguments (entry_id BIGINT UNSIGNED, name VARCHAR(128), value TEXT, PRIMARY KEY(entry_id, name)) ENGINE = INNODB;
CREATE TABLE taskqueue_entry_tags (entry_id BIGINT UNSIGNED, tag VARCHAR(128), PRIMARY KEY(entry_id, tag)) ENGINE = INNODB;
CREATE TABLE trees (id BIGINT UNSIGNED AUTO_INCREMENT, title VARCHAR(32), type_id VARCHAR(32) NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, INDEX type_id_idx (type_id), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE tree_types (id VARCHAR(32), name VARCHAR(32), description TEXT, managing_class VARCHAR(128), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE users (id BIGINT UNSIGNED AUTO_INCREMENT, name VARCHAR(32) NOT NULL, is_locked TINYINT(1) DEFAULT '0' NOT NULL, login_failures BIGINT DEFAULT 0 NOT NULL, firstname VARCHAR(128), lastname VARCHAR(128), email TEXT, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, UNIQUE INDEX nameindex_idx (name), PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE user_auth_methods (id BIGINT AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, auth_method_id VARCHAR(32) NOT NULL, INDEX index_user_authmethod_idx (user_id, auth_method_id), INDEX user_id_idx (user_id), INDEX auth_method_id_idx (auth_method_id), PRIMARY KEY(id)) ENGINE = INNODB;
ALTER TABLE accesscontrol_privilege_entries ADD CONSTRAINT aran FOREIGN KEY (resource) REFERENCES accesscontrol_resources(name) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE accesscontrol_privilege_entries ADD CONSTRAINT apan FOREIGN KEY (privilege) REFERENCES accesscontrol_privileges(name) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE accesscontrol_resource_privileges ADD CONSTRAINT aran_1 FOREIGN KEY (resource) REFERENCES accesscontrol_resources(name) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE accesscontrol_resource_privileges ADD CONSTRAINT apan_1 FOREIGN KEY (privilege) REFERENCES accesscontrol_privileges(name) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE accesscontrol_right_entries ADD CONSTRAINT accesscontrol_right_entries_right_name_accesscontrol_rights_name FOREIGN KEY (right_name) REFERENCES accesscontrol_rights(name) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE accesscontrol_right_entries ADD CONSTRAINT accesscontrol_right_entries_node_id_nodes_id FOREIGN KEY (node_id) REFERENCES nodes(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE client_context_settings ADD CONSTRAINT client_context_settings_context_id_client_contexts_id FOREIGN KEY (context_id) REFERENCES client_contexts(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE client_context_trees ADD CONSTRAINT client_context_trees_tree_id_trees_id FOREIGN KEY (tree_id) REFERENCES trees(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE client_context_trees ADD CONSTRAINT client_context_trees_context_id_client_contexts_id FOREIGN KEY (context_id) REFERENCES client_contexts(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE client_context_users ADD CONSTRAINT client_context_users_user_id_users_id FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE client_context_users ADD CONSTRAINT client_context_users_context_id_client_contexts_id FOREIGN KEY (context_id) REFERENCES client_contexts(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE comet_event_contents ADD CONSTRAINT comet_event_contents_id_comet_events_id FOREIGN KEY (id) REFERENCES comet_events(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE copy_nodes ADD CONSTRAINT copy_nodes_tree_id_trees_id FOREIGN KEY (tree_id) REFERENCES trees(id);
ALTER TABLE hashed_passwords ADD CONSTRAINT hashed_passwords_user_id_users_id FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE hook_bindings ADD CONSTRAINT hook_bindings_plugin_name_plugins_name FOREIGN KEY (plugin_name) REFERENCES plugins(name) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE nodes ADD CONSTRAINT nodes_tree_id_trees_id FOREIGN KEY (tree_id) REFERENCES trees(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE nodes ADD CONSTRAINT nodes_content_type_content_types_id FOREIGN KEY (content_type) REFERENCES content_types(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE node_syncs ADD CONSTRAINT node_syncs_tree_id_trees_id FOREIGN KEY (tree_id) REFERENCES trees(id);
ALTER TABLE node_syncs ADD CONSTRAINT node_syncs_synctype_node_sync_types_id FOREIGN KEY (synctype) REFERENCES node_sync_types(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE node_syncs ADD CONSTRAINT node_syncs_node_id_nodes_id FOREIGN KEY (node_id) REFERENCES nodes(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE node_sync_attributes ADD CONSTRAINT node_sync_attributes_sync_id_node_syncs_id FOREIGN KEY (sync_id) REFERENCES node_syncs(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE plugin_parameters ADD CONSTRAINT plugin_parameters_plugin_name_plugins_name FOREIGN KEY (plugin_name) REFERENCES plugins(name) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE user_sessions ADD CONSTRAINT user_sessions_user_id_users_id FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE user_sessions ADD CONSTRAINT user_sessions_auth_method_name_auth_methods_id FOREIGN KEY (auth_method_name) REFERENCES auth_methods(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE user_session_impersonatedvalues ADD CONSTRAINT user_session_impersonatedvalues_token_user_sessions_token FOREIGN KEY (token) REFERENCES user_sessions(token) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE user_session_values ADD CONSTRAINT user_session_values_token_user_sessions_token FOREIGN KEY (token) REFERENCES user_sessions(token) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE tasks ADD CONSTRAINT tasks_plugin_plugins_name FOREIGN KEY (plugin) REFERENCES plugins(name) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE task_aliases ADD CONSTRAINT task_aliases_alias_for_tasks_name FOREIGN KEY (alias_for) REFERENCES tasks(name) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE taskqueue_entry_arguments ADD CONSTRAINT taskqueue_entry_arguments_entry_id_taskqueue_entries_id FOREIGN KEY (entry_id) REFERENCES taskqueue_entries(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE taskqueue_entry_tags ADD CONSTRAINT taskqueue_entry_tags_entry_id_taskqueue_entries_id FOREIGN KEY (entry_id) REFERENCES taskqueue_entries(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE trees ADD CONSTRAINT trees_type_id_tree_types_id FOREIGN KEY (type_id) REFERENCES tree_types(id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE user_auth_methods ADD CONSTRAINT user_auth_methods_user_id_users_id FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE user_auth_methods ADD CONSTRAINT user_auth_methods_auth_method_id_auth_methods_id FOREIGN KEY (auth_method_id) REFERENCES auth_methods(id) ON UPDATE CASCADE ON DELETE CASCADE;
