demo_data/db.sql
/**
***********************************************************************************************
* SQL script with database structure
*
* @copyright The Admidio Team
* @see https://www.admidio.org/
* @license https://www.gnu.org/licenses/gpl-2.0.html GNU General Public License v2.0 only
***********************************************************************************************
*/
/*==============================================================*/
/* Table Cleanup */
/*==============================================================*/
DROP TABLE IF EXISTS %PREFIX%_announcements CASCADE;
DROP TABLE IF EXISTS %PREFIX%_auto_login CASCADE;
DROP TABLE IF EXISTS %PREFIX%_category_report CASCADE;
DROP TABLE IF EXISTS %PREFIX%_components CASCADE;
DROP TABLE IF EXISTS %PREFIX%_events CASCADE;
DROP TABLE IF EXISTS %PREFIX%_dates CASCADE;
DROP TABLE IF EXISTS %PREFIX%_files CASCADE;
DROP TABLE IF EXISTS %PREFIX%_folders CASCADE;
DROP TABLE IF EXISTS %PREFIX%_guestbook_comments CASCADE;
DROP TABLE IF EXISTS %PREFIX%_guestbook CASCADE;
DROP TABLE IF EXISTS %PREFIX%_links CASCADE;
DROP TABLE IF EXISTS %PREFIX%_members CASCADE;
DROP TABLE IF EXISTS %PREFIX%_messages CASCADE;
DROP TABLE IF EXISTS %PREFIX%_messages_attachments CASCADE;
DROP TABLE IF EXISTS %PREFIX%_messages_content CASCADE;
DROP TABLE IF EXISTS %PREFIX%_messages_recipients CASCADE;
DROP TABLE IF EXISTS %PREFIX%_photos CASCADE;
DROP TABLE IF EXISTS %PREFIX%_preferences CASCADE;
DROP TABLE IF EXISTS %PREFIX%_registrations CASCADE;
DROP TABLE IF EXISTS %PREFIX%_role_dependencies CASCADE;
DROP TABLE IF EXISTS %PREFIX%_roles CASCADE;
DROP TABLE IF EXISTS %PREFIX%_roles_rights CASCADE;
DROP TABLE IF EXISTS %PREFIX%_roles_rights_data CASCADE;
DROP TABLE IF EXISTS %PREFIX%_list_columns CASCADE;
DROP TABLE IF EXISTS %PREFIX%_lists CASCADE;
DROP TABLE IF EXISTS %PREFIX%_rooms CASCADE;
DROP TABLE IF EXISTS %PREFIX%_sessions CASCADE;
DROP TABLE IF EXISTS %PREFIX%_texts CASCADE;
DROP TABLE IF EXISTS %PREFIX%_user_relations CASCADE;
DROP TABLE IF EXISTS %PREFIX%_user_relation_types CASCADE;
DROP TABLE IF EXISTS %PREFIX%_user_log CASCADE;
DROP TABLE IF EXISTS %PREFIX%_user_data CASCADE;
DROP TABLE IF EXISTS %PREFIX%_user_fields CASCADE;
DROP TABLE IF EXISTS %PREFIX%_categories CASCADE;
DROP TABLE IF EXISTS %PREFIX%_users CASCADE;
DROP TABLE IF EXISTS %PREFIX%_organizations CASCADE;
DROP TABLE IF EXISTS %PREFIX%_ids CASCADE;
DROP TABLE IF EXISTS %PREFIX%_menu CASCADE;
/*==============================================================*/
/* Table: adm_announcements */
/*==============================================================*/
CREATE TABLE %PREFIX%_announcements
(
ann_id integer unsigned NOT NULL AUTO_INCREMENT,
ann_cat_id integer unsigned NOT NULL,
ann_uuid varchar(36) NOT NULL,
ann_headline varchar(100) NOT NULL,
ann_description text,
ann_usr_id_create integer unsigned,
ann_timestamp_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
ann_usr_id_change integer unsigned,
ann_timestamp_change timestamp NULL DEFAULT NULL,
PRIMARY KEY (ann_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
CREATE UNIQUE INDEX %PREFIX%_idx_ann_uuid ON %PREFIX%_announcements (ann_uuid);
/*==============================================================*/
/* Table: adm_auto_login */
/*==============================================================*/
CREATE TABLE %PREFIX%_auto_login
(
atl_id integer unsigned NOT NULL AUTO_INCREMENT,
atl_auto_login_id varchar(255) NOT NULL,
atl_session_id varchar(255) NOT NULL,
atl_org_id integer unsigned NOT NULL,
atl_usr_id integer unsigned NOT NULL,
atl_last_login timestamp NULL DEFAULT NULL,
atl_number_invalid smallint NOT NULL DEFAULT 0,
PRIMARY KEY (atl_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
/*==============================================================*/
/* Table: adm_categories */
/*==============================================================*/
CREATE TABLE %PREFIX%_categories
(
cat_id integer unsigned NOT NULL AUTO_INCREMENT,
cat_org_id integer unsigned,
cat_uuid varchar(36) NOT NULL,
cat_type varchar(10) NOT NULL,
cat_name_intern varchar(110) NOT NULL,
cat_name varchar(100) NOT NULL,
cat_system boolean NOT NULL DEFAULT false,
cat_default boolean NOT NULL DEFAULT false,
cat_sequence smallint NOT NULL,
cat_usr_id_create integer unsigned,
cat_timestamp_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
cat_usr_id_change integer unsigned,
cat_timestamp_change timestamp NULL DEFAULT NULL,
PRIMARY KEY (cat_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
CREATE UNIQUE INDEX %PREFIX%_idx_cat_uuid ON %PREFIX%_categories (cat_uuid);
/*==============================================================*/
/* Table: adm_category_report */
/*==============================================================*/
CREATE TABLE %PREFIX%_category_report
(
crt_id integer unsigned NOT NULL AUTO_INCREMENT,
crt_org_id integer unsigned,
crt_name varchar(100) NOT NULL,
crt_col_fields text,
crt_selection_role varchar(100),
crt_selection_cat varchar(100),
crt_number_col boolean NOT NULL DEFAULT false,
PRIMARY KEY (crt_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
/*==============================================================*/
/* Table: adm_components */
/*==============================================================*/
CREATE TABLE %PREFIX%_components
(
com_id integer unsigned NOT NULL AUTO_INCREMENT,
com_type varchar(10) NOT NULL,
com_name varchar(255) NOT NULL,
com_name_intern varchar(255) NOT NULL,
com_version varchar(10) NOT NULL,
com_beta smallint NOT NULL DEFAULT 0,
com_update_step integer NOT NULL DEFAULT 0,
com_update_completed boolean NOT NULL DEFAULT true,
com_timestamp_installed timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (com_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
/*==============================================================*/
/* Table: adm_events */
/*==============================================================*/
CREATE TABLE %PREFIX%_events
(
dat_id integer unsigned NOT NULL AUTO_INCREMENT,
dat_cat_id integer unsigned NOT NULL,
dat_rol_id integer unsigned,
dat_room_id integer unsigned,
dat_uuid varchar(36) NOT NULL,
dat_begin timestamp NULL DEFAULT NULL,
dat_end timestamp NULL DEFAULT NULL,
dat_all_day boolean NOT NULL DEFAULT false,
dat_headline varchar(100) NOT NULL,
dat_description text,
dat_highlight boolean NOT NULL DEFAULT false,
dat_location varchar(100),
dat_country varchar(100),
dat_deadline timestamp NULL DEFAULT NULL,
dat_max_members integer NOT NULL DEFAULT 0,
dat_usr_id_create integer unsigned,
dat_timestamp_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
dat_usr_id_change integer unsigned,
dat_timestamp_change timestamp NULL DEFAULT NULL,
dat_allow_comments boolean NOT NULL DEFAULT false,
dat_additional_guests boolean NOT NULL DEFAULT false,
PRIMARY KEY (dat_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
CREATE UNIQUE INDEX %PREFIX%_idx_dat_uuid ON %PREFIX%_events (dat_uuid);
/*==============================================================*/
/* Table: adm_files */
/*==============================================================*/
CREATE TABLE %PREFIX%_files
(
fil_id integer unsigned NOT NULL AUTO_INCREMENT,
fil_fol_id integer unsigned NOT NULL,
fil_uuid varchar(36) NOT NULL,
fil_name varchar(255) NOT NULL,
fil_description text,
fil_locked boolean NOT NULL DEFAULT false,
fil_counter integer,
fil_usr_id integer unsigned,
fil_timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (fil_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
CREATE UNIQUE INDEX %PREFIX%_idx_fil_uuid ON %PREFIX%_files (fil_uuid);
/*==============================================================*/
/* Table: adm_folders */
/*==============================================================*/
CREATE TABLE %PREFIX%_folders
(
fol_id integer unsigned NOT NULL AUTO_INCREMENT,
fol_org_id integer unsigned NOT NULL,
fol_fol_id_parent integer unsigned,
fol_uuid varchar(36) NOT NULL,
fol_type varchar(10) NOT NULL,
fol_name varchar(255) NOT NULL,
fol_description text,
fol_path varchar(255) NOT NULL,
fol_locked boolean NOT NULL DEFAULT false,
fol_public boolean NOT NULL DEFAULT false,
fol_usr_id integer unsigned,
fol_timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (fol_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
CREATE UNIQUE INDEX %PREFIX%_idx_fol_uuid ON %PREFIX%_folders (fol_uuid);
/*==============================================================*/
/* Table: adm_guestbook */
/*==============================================================*/
CREATE TABLE %PREFIX%_guestbook
(
gbo_id integer unsigned NOT NULL AUTO_INCREMENT,
gbo_org_id integer unsigned NOT NULL,
gbo_uuid varchar(36) NOT NULL,
gbo_name varchar(60) NOT NULL,
gbo_text text NOT NULL,
gbo_email varchar(254),
gbo_homepage varchar(50),
gbo_ip_address varchar(39) NOT NULL,
gbo_locked boolean NOT NULL DEFAULT false,
gbo_usr_id_create integer unsigned,
gbo_timestamp_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
gbo_usr_id_change integer unsigned,
gbo_timestamp_change timestamp NULL DEFAULT NULL,
PRIMARY KEY (gbo_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
CREATE UNIQUE INDEX %PREFIX%_idx_gbo_uuid ON %PREFIX%_guestbook (gbo_uuid);
/*==============================================================*/
/* Table: adm_guestbook_comments */
/*==============================================================*/
CREATE TABLE %PREFIX%_guestbook_comments
(
gbc_id integer unsigned NOT NULL AUTO_INCREMENT,
gbc_gbo_id integer unsigned NOT NULL,
gbc_uuid varchar(36) NOT NULL,
gbc_name varchar(60) NOT NULL,
gbc_text text NOT NULL,
gbc_email varchar(254),
gbc_ip_address varchar(39) NOT NULL,
gbc_locked boolean NOT NULL DEFAULT false,
gbc_usr_id_create integer unsigned,
gbc_timestamp_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
gbc_usr_id_change integer unsigned,
gbc_timestamp_change timestamp NULL DEFAULT NULL,
PRIMARY KEY (gbc_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
CREATE UNIQUE INDEX %PREFIX%_idx_gbc_uuid ON %PREFIX%_guestbook_comments (gbc_uuid);
/*==============================================================*/
/* Table: adm_ids */
/*==============================================================*/
CREATE TABLE %PREFIX%_ids
(
ids_usr_id integer unsigned NOT NULL,
ids_reference_id integer unsigned NOT NULL
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
/*==============================================================*/
/* Table: adm_links */
/*==============================================================*/
CREATE TABLE %PREFIX%_links
(
lnk_id integer unsigned NOT NULL AUTO_INCREMENT,
lnk_cat_id integer unsigned NOT NULL,
lnk_uuid varchar(36) NOT NULL,
lnk_name varchar(255) NOT NULL,
lnk_description text,
lnk_url varchar(2000) NOT NULL,
lnk_counter integer NOT NULL DEFAULT 0,
lnk_usr_id_create integer unsigned,
lnk_timestamp_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
lnk_usr_id_change integer unsigned,
lnk_timestamp_change timestamp NULL DEFAULT NULL,
PRIMARY KEY (lnk_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
CREATE UNIQUE INDEX %PREFIX%_idx_lnk_uuid ON %PREFIX%_links (lnk_uuid);
/*==============================================================*/
/* Table: adm_lists */
/*==============================================================*/
CREATE TABLE %PREFIX%_lists
(
lst_id integer unsigned NOT NULL AUTO_INCREMENT,
lst_org_id integer unsigned NOT NULL,
lst_usr_id integer unsigned NOT NULL,
lst_uuid varchar(36) NOT NULL,
lst_name varchar(255),
lst_timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
lst_global boolean NOT NULL DEFAULT false,
PRIMARY KEY (lst_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
CREATE UNIQUE INDEX %PREFIX%_idx_lst_uuid ON %PREFIX%_lists (lst_uuid);
/*==============================================================*/
/* Table: adm_list_columns */
/*==============================================================*/
CREATE TABLE %PREFIX%_list_columns
(
lsc_id integer unsigned NOT NULL AUTO_INCREMENT,
lsc_lst_id integer unsigned NOT NULL,
lsc_number smallint NOT NULL,
lsc_usf_id integer unsigned,
lsc_special_field varchar(255),
lsc_sort varchar(5),
lsc_filter varchar(255),
PRIMARY KEY (lsc_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
/*==============================================================*/
/* Table: adm_members */
/*==============================================================*/
CREATE TABLE %PREFIX%_members
(
mem_id integer unsigned NOT NULL AUTO_INCREMENT,
mem_rol_id integer unsigned NOT NULL,
mem_usr_id integer unsigned NOT NULL,
mem_uuid varchar(36) NOT NULL,
mem_begin date NOT NULL,
mem_end date NOT NULL DEFAULT '9999-12-31',
mem_leader boolean NOT NULL DEFAULT false,
mem_usr_id_create integer unsigned,
mem_timestamp_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
mem_usr_id_change integer unsigned,
mem_timestamp_change timestamp NULL DEFAULT NULL,
mem_approved integer unsigned NULL DEFAULT NULL,
mem_comment varchar(4000),
mem_count_guests integer unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (mem_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
CREATE INDEX %PREFIX%_idx_mem_rol_usr_id ON %PREFIX%_members (mem_rol_id, mem_usr_id);
CREATE UNIQUE INDEX %PREFIX%_idx_mem_uuid ON %PREFIX%_members (mem_uuid);
/*==============================================================*/
/* Table: adm_menu */
/*==============================================================*/
CREATE TABLE %PREFIX%_menu
(
men_id integer unsigned NOT NULL AUTO_INCREMENT,
men_men_id_parent integer unsigned,
men_com_id integer unsigned,
men_uuid varchar(36) NOT NULL,
men_name_intern varchar(255),
men_name varchar(255),
men_description varchar(4000),
men_node boolean NOT NULL DEFAULT false,
men_order integer unsigned,
men_standard boolean NOT NULL DEFAULT false,
men_url varchar(2000),
men_icon varchar(100),
PRIMARY KEY (men_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
CREATE INDEX %PREFIX%_idx_men_men_id_parent ON %PREFIX%_menu (men_men_id_parent);
CREATE UNIQUE INDEX %PREFIX%_idx_men_uuid ON %PREFIX%_menu (men_uuid);
/*==============================================================*/
/* Table: adm_messages */
/*==============================================================*/
CREATE TABLE %PREFIX%_messages
(
msg_id integer unsigned NOT NULL AUTO_INCREMENT,
msg_uuid varchar(36) NOT NULL,
msg_type varchar(10) NOT NULL,
msg_subject varchar(256) NOT NULL,
msg_usr_id_sender integer unsigned NOT NULL,
msg_timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
msg_read smallint NOT NULL DEFAULT 0,
PRIMARY KEY (msg_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
CREATE UNIQUE INDEX %PREFIX%_idx_msg_uuid ON %PREFIX%_messages (msg_uuid);
/*==============================================================*/
/* Table: adm_messages_attachments */
/*==============================================================*/
CREATE TABLE %PREFIX%_messages_attachments
(
msa_id integer unsigned NOT NULL AUTO_INCREMENT,
msa_msg_id integer unsigned NOT NULL,
msa_file_name varchar(256) NOT NULL,
msa_original_file_name varchar(256) NOT NULL,
PRIMARY KEY (msa_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
/*==============================================================*/
/* Table: adm_messages_content */
/*==============================================================*/
CREATE TABLE %PREFIX%_messages_content
(
msc_id integer unsigned NOT NULL AUTO_INCREMENT,
msc_msg_id integer unsigned NOT NULL,
msc_usr_id integer unsigned,
msc_message text NOT NULL,
msc_timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (msc_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
/*==============================================================*/
/* Table: adm_messages_recipients */
/*==============================================================*/
CREATE TABLE %PREFIX%_messages_recipients
(
msr_id integer unsigned NOT NULL AUTO_INCREMENT,
msr_msg_id integer unsigned NOT NULL,
msr_rol_id integer unsigned,
msr_usr_id integer unsigned,
msr_role_mode smallint NOT NULL DEFAULT 0,
PRIMARY KEY (msr_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
/*==============================================================*/
/* Table: adm_organizations */
/*==============================================================*/
CREATE TABLE %PREFIX%_organizations
(
org_id integer unsigned NOT NULL AUTO_INCREMENT,
org_uuid varchar(36) NOT NULL,
org_shortname varchar(10) NOT NULL,
org_longname varchar(60) NOT NULL,
org_org_id_parent integer unsigned,
org_homepage varchar(60) NOT NULL,
PRIMARY KEY (org_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
CREATE UNIQUE INDEX %PREFIX%_idx_org_shortname ON %PREFIX%_organizations (org_shortname);
CREATE UNIQUE INDEX %PREFIX%_idx_org_uuid ON %PREFIX%_organizations (org_uuid);
/*==============================================================*/
/* Table: adm_photos */
/*==============================================================*/
CREATE TABLE %PREFIX%_photos
(
pho_id integer unsigned NOT NULL AUTO_INCREMENT,
pho_org_id integer unsigned NOT NULL,
pho_pho_id_parent integer unsigned,
pho_uuid varchar(36) NOT NULL,
pho_quantity integer unsigned NOT NULL DEFAULT 0,
pho_name varchar(50) NOT NULL,
pho_begin date NOT NULL,
pho_end date NOT NULL,
pho_description varchar(4000),
pho_photographers varchar(100),
pho_locked boolean NOT NULL DEFAULT false,
pho_usr_id_create integer unsigned,
pho_timestamp_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
pho_usr_id_change integer unsigned,
pho_timestamp_change timestamp NULL DEFAULT NULL,
PRIMARY KEY (pho_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
CREATE UNIQUE INDEX %PREFIX%_idx_pho_uuid ON %PREFIX%_photos (pho_uuid);
/*==============================================================*/
/* Table: adm_preferences */
/*==============================================================*/
CREATE TABLE %PREFIX%_preferences
(
prf_id integer unsigned NOT NULL AUTO_INCREMENT,
prf_org_id integer unsigned NOT NULL,
prf_name varchar(50) NOT NULL,
prf_value varchar(255),
PRIMARY KEY (prf_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
CREATE UNIQUE INDEX %PREFIX%_idx_prf_org_id_name ON %PREFIX%_preferences (prf_org_id, prf_name);
/*==============================================================*/
/* Table: adm_registrations */
/*==============================================================*/
CREATE TABLE %PREFIX%_registrations
(
reg_id integer unsigned NOT NULL AUTO_INCREMENT,
reg_org_id integer unsigned NOT NULL,
reg_usr_id integer unsigned NOT NULL,
reg_timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
reg_validation_id varchar(50),
PRIMARY KEY (reg_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
/*==============================================================*/
/* Table: adm_role_dependencies */
/*==============================================================*/
CREATE TABLE %PREFIX%_role_dependencies
(
rld_rol_id_parent integer unsigned NOT NULL,
rld_rol_id_child integer unsigned NOT NULL,
rld_comment text,
rld_usr_id integer unsigned,
rld_timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (rld_rol_id_parent, rld_rol_id_child)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
/*==============================================================*/
/* Table: adm_roles */
/*==============================================================*/
CREATE TABLE %PREFIX%_roles
(
rol_id integer unsigned NOT NULL AUTO_INCREMENT,
rol_cat_id integer unsigned NOT NULL,
rol_lst_id integer unsigned,
rol_uuid varchar(36) NOT NULL,
rol_name varchar(100) NOT NULL,
rol_description varchar(4000),
rol_assign_roles boolean NOT NULL DEFAULT false,
rol_approve_users boolean NOT NULL DEFAULT false,
rol_announcements boolean NOT NULL DEFAULT false,
rol_events boolean NOT NULL DEFAULT false,
rol_documents_files boolean NOT NULL DEFAULT false,
rol_edit_user boolean NOT NULL DEFAULT false,
rol_guestbook boolean NOT NULL DEFAULT false,
rol_guestbook_comments boolean NOT NULL DEFAULT false,
rol_mail_to_all boolean NOT NULL DEFAULT false,
rol_mail_this_role smallint NOT NULL DEFAULT 0,
rol_photo boolean NOT NULL DEFAULT false,
rol_profile boolean NOT NULL DEFAULT false,
rol_weblinks boolean NOT NULL DEFAULT false,
rol_all_lists_view boolean NOT NULL DEFAULT false,
rol_default_registration boolean NOT NULL DEFAULT false,
rol_leader_rights smallint NOT NULL DEFAULT 0,
rol_view_memberships smallint NOT NULL DEFAULT 0,
rol_view_members_profiles smallint NOT NULL DEFAULT 0,
rol_start_date date,
rol_start_time time,
rol_end_date date,
rol_end_time time,
rol_weekday smallint,
rol_location varchar(100),
rol_max_members integer,
rol_cost float,
rol_cost_period smallint,
rol_usr_id_create integer unsigned,
rol_timestamp_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
rol_usr_id_change integer unsigned,
rol_timestamp_change timestamp NULL DEFAULT NULL,
rol_valid boolean NOT NULL DEFAULT true,
rol_system boolean NOT NULL DEFAULT false,
rol_administrator boolean NOT NULL DEFAULT false,
PRIMARY KEY (rol_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
CREATE UNIQUE INDEX %PREFIX%_idx_rol_uuid ON %PREFIX%_roles (rol_uuid);
/*==============================================================*/
/* Table: adm_roles_rights */
/*==============================================================*/
CREATE TABLE %PREFIX%_roles_rights
(
ror_id integer unsigned NOT NULL AUTO_INCREMENT,
ror_name_intern varchar(50) NOT NULL,
ror_table varchar(50) NOT NULL,
ror_ror_id_parent integer unsigned,
PRIMARY KEY (ror_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
/*==============================================================*/
/* Table: adm_roles_rights_data */
/*==============================================================*/
CREATE TABLE %PREFIX%_roles_rights_data
(
rrd_id integer unsigned NOT NULL AUTO_INCREMENT,
rrd_ror_id integer unsigned NOT NULL,
rrd_rol_id integer unsigned NOT NULL,
rrd_object_id integer unsigned NOT NULL,
rrd_usr_id_create integer unsigned,
rrd_timestamp_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (rrd_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
CREATE UNIQUE INDEX %PREFIX%_idx_rrd_ror_rol_object_id ON %PREFIX%_roles_rights_data (rrd_ror_id, rrd_rol_id, rrd_object_id);
/*==============================================================*/
/* Table: adm_rooms */
/*==============================================================*/
CREATE TABLE %PREFIX%_rooms
(
room_id integer unsigned NOT NULL AUTO_INCREMENT,
room_uuid varchar(36) NOT NULL,
room_name varchar(50) NOT NULL,
room_description text,
room_capacity integer NOT NULL,
room_overhang integer,
room_usr_id_create integer unsigned,
room_timestamp_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
room_usr_id_change integer unsigned,
room_timestamp_change timestamp NULL DEFAULT NULL,
PRIMARY KEY (room_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
CREATE UNIQUE INDEX %PREFIX%_idx_room_uuid ON %PREFIX%_rooms (room_uuid);
/*==============================================================*/
/* Table: adm_sessions */
/*==============================================================*/
CREATE TABLE %PREFIX%_sessions
(
ses_id integer unsigned NOT NULL AUTO_INCREMENT,
ses_usr_id integer unsigned NULL DEFAULT NULL,
ses_org_id integer unsigned NOT NULL,
ses_session_id varchar(255) NOT NULL,
ses_begin timestamp NULL DEFAULT NULL,
ses_timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
ses_ip_address varchar(39) NOT NULL,
ses_binary blob,
ses_reload boolean NOT NULL DEFAULT false,
PRIMARY KEY (ses_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
CREATE INDEX %PREFIX%_idx_session_id ON %PREFIX%_sessions (ses_session_id);
/*==============================================================*/
/* Table: adm_texts */
/*==============================================================*/
CREATE TABLE %PREFIX%_texts
(
txt_id integer unsigned NOT NULL AUTO_INCREMENT,
txt_org_id integer unsigned NOT NULL,
txt_name varchar(100) NOT NULL,
txt_text text,
PRIMARY KEY (txt_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
/*==============================================================*/
/* Table: adm_user_fields */
/*==============================================================*/
CREATE TABLE %PREFIX%_user_fields
(
usf_id integer unsigned NOT NULL AUTO_INCREMENT,
usf_cat_id integer unsigned NOT NULL,
usf_uuid varchar(36) NOT NULL,
usf_type varchar(30) NOT NULL,
usf_name_intern varchar(110) NOT NULL,
usf_name varchar(100) NOT NULL,
usf_description text,
usf_description_inline boolean NOT NULL DEFAULT false,
usf_value_list text,
usf_default_value varchar(100),
usf_regex varchar(100),
usf_icon varchar(100),
usf_url varchar(2000),
usf_system boolean NOT NULL DEFAULT false,
usf_disabled boolean NOT NULL DEFAULT false,
usf_hidden boolean NOT NULL DEFAULT false,
usf_registration boolean NOT NULL DEFAULT false,
usf_required_input smallint NOT NULL DEFAULT 0,
usf_sequence smallint NOT NULL,
usf_usr_id_create integer unsigned,
usf_timestamp_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
usf_usr_id_change integer unsigned,
usf_timestamp_change timestamp NULL DEFAULT NULL,
PRIMARY KEY (usf_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
CREATE UNIQUE INDEX %PREFIX%_idx_usf_name_intern ON %PREFIX%_user_fields (usf_name_intern);
CREATE UNIQUE INDEX %PREFIX%_idx_usf_uuid ON %PREFIX%_user_fields (usf_uuid);
/*==============================================================*/
/* Table: adm_user_data */
/*==============================================================*/
CREATE TABLE %PREFIX%_user_data
(
usd_id integer unsigned NOT NULL AUTO_INCREMENT,
usd_usr_id integer unsigned NOT NULL,
usd_usf_id integer unsigned NOT NULL,
usd_value varchar(4000),
PRIMARY KEY (usd_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
CREATE UNIQUE INDEX %PREFIX%_idx_usd_usr_usf_id ON %PREFIX%_user_data (usd_usr_id, usd_usf_id);
/*==============================================================*/
/* Table: adm_user_log */
/*==============================================================*/
CREATE TABLE %PREFIX%_user_log
(
usl_id integer NOT NULL AUTO_INCREMENT,
usl_usr_id integer unsigned NOT NULL,
usl_usf_id integer unsigned NOT NULL,
usl_value_old varchar(4000) NULL,
usl_value_new varchar(4000) NULL,
usl_usr_id_create integer unsigned NULL,
usl_timestamp_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
usl_comment varchar(255) NULL,
PRIMARY KEY (usl_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
/*==============================================================*/
/* Table: adm_users */
/*==============================================================*/
CREATE TABLE %PREFIX%_users
(
usr_id integer unsigned NOT NULL AUTO_INCREMENT,
usr_uuid varchar(36) NOT NULL,
usr_login_name varchar(254),
usr_password varchar(255),
usr_photo blob,
usr_text text,
usr_pw_reset_id varchar(50),
usr_pw_reset_timestamp timestamp NULL DEFAULT NULL,
usr_last_login timestamp NULL DEFAULT NULL,
usr_actual_login timestamp NULL DEFAULT NULL,
usr_number_login integer NOT NULL DEFAULT 0,
usr_date_invalid timestamp NULL DEFAULT NULL,
usr_number_invalid smallint NOT NULL DEFAULT 0,
usr_usr_id_create integer unsigned,
usr_timestamp_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
usr_usr_id_change integer unsigned,
usr_timestamp_change timestamp NULL DEFAULT NULL,
usr_valid boolean NOT NULL DEFAULT false,
PRIMARY KEY (usr_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
CREATE UNIQUE INDEX %PREFIX%_idx_usr_login_name ON %PREFIX%_users (usr_login_name);
CREATE UNIQUE INDEX %PREFIX%_idx_usr_uuid ON %PREFIX%_users (usr_uuid);
/*==============================================================*/
/* Table: adm_user_relation_types */
/*==============================================================*/
CREATE TABLE %PREFIX%_user_relation_types
(
urt_id integer unsigned NOT NULL AUTO_INCREMENT,
urt_uuid varchar(36) NOT NULL,
urt_name varchar(100) NOT NULL,
urt_name_male varchar(100) NOT NULL,
urt_name_female varchar(100) NOT NULL,
urt_edit_user boolean NOT NULL DEFAULT false,
urt_id_inverse integer unsigned NULL DEFAULT NULL,
urt_usr_id_create integer unsigned NULL DEFAULT NULL,
urt_timestamp_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
urt_usr_id_change integer unsigned NULL DEFAULT NULL,
urt_timestamp_change timestamp NULL DEFAULT NULL,
PRIMARY KEY (urt_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
CREATE UNIQUE INDEX %PREFIX%_idx_ure_urt_name ON %PREFIX%_user_relation_types (urt_name);
CREATE UNIQUE INDEX %PREFIX%_idx_urt_uuid ON %PREFIX%_user_relation_types (urt_uuid);
/*==============================================================*/
/* Table: adm_user_relations */
/*==============================================================*/
CREATE TABLE %PREFIX%_user_relations
(
ure_id integer unsigned NOT NULL AUTO_INCREMENT,
ure_urt_id integer unsigned NOT NULL,
ure_usr_id1 integer unsigned NOT NULL,
ure_usr_id2 integer unsigned NOT NULL,
ure_usr_id_create integer unsigned NULL DEFAULT NULL,
ure_timestamp_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
ure_usr_id_change integer unsigned NULL DEFAULT NULL,
ure_timestamp_change timestamp NULL DEFAULT NULL,
PRIMARY KEY (ure_id)
)
ENGINE = InnoDB
DEFAULT character SET = utf8
COLLATE = utf8_unicode_ci;
CREATE UNIQUE INDEX %PREFIX%_idx_ure_urt_usr ON %PREFIX%_user_relations (ure_urt_id, ure_usr_id1, ure_usr_id2);
/*==============================================================*/
/* Foreign Key Constraints */
/*==============================================================*/
ALTER TABLE %PREFIX%_announcements
ADD CONSTRAINT %PREFIX%_fk_ann_cat FOREIGN KEY (ann_cat_id) REFERENCES %PREFIX%_categories (cat_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_ann_usr_create FOREIGN KEY (ann_usr_id_create) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_ann_usr_change FOREIGN KEY (ann_usr_id_change) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_auto_login
ADD CONSTRAINT %PREFIX%_fk_atl_usr FOREIGN KEY (atl_usr_id) REFERENCES %PREFIX%_users (usr_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_atl_org FOREIGN KEY (atl_org_id) REFERENCES %PREFIX%_organizations (org_id) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_categories
ADD CONSTRAINT %PREFIX%_fk_cat_org FOREIGN KEY (cat_org_id) REFERENCES %PREFIX%_organizations (org_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_cat_usr_create FOREIGN KEY (cat_usr_id_create) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_cat_usr_change FOREIGN KEY (cat_usr_id_change) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_category_report
ADD CONSTRAINT %PREFIX%_fk_crt_org FOREIGN KEY (crt_org_id) REFERENCES %PREFIX%_organizations (org_id) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_events
ADD CONSTRAINT %PREFIX%_fk_dat_cat FOREIGN KEY (dat_cat_id) REFERENCES %PREFIX%_categories (cat_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_dat_rol FOREIGN KEY (dat_rol_id) REFERENCES %PREFIX%_roles (rol_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_dat_room FOREIGN KEY (dat_room_id) REFERENCES %PREFIX%_rooms (room_id) ON DELETE SET NULL ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_dat_usr_create FOREIGN KEY (dat_usr_id_create) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_dat_usr_change FOREIGN KEY (dat_usr_id_change) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_files
ADD CONSTRAINT %PREFIX%_fk_fil_fol FOREIGN KEY (fil_fol_id) REFERENCES %PREFIX%_folders (fol_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_fil_usr FOREIGN KEY (fil_usr_id) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_folders
ADD CONSTRAINT %PREFIX%_fk_fol_org FOREIGN KEY (fol_org_id) REFERENCES %PREFIX%_organizations (org_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_fol_fol_parent FOREIGN KEY (fol_fol_id_parent) REFERENCES %PREFIX%_folders (fol_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_fol_usr FOREIGN KEY (fol_usr_id) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_guestbook
ADD CONSTRAINT %PREFIX%_fk_gbo_org FOREIGN KEY (gbo_org_id) REFERENCES %PREFIX%_organizations (org_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_gbo_usr_create FOREIGN KEY (gbo_usr_id_create) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_gbo_usr_change FOREIGN KEY (gbo_usr_id_change) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_guestbook_comments
ADD CONSTRAINT %PREFIX%_fk_gbc_gbo FOREIGN KEY (gbc_gbo_id) REFERENCES %PREFIX%_guestbook (gbo_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_gbc_usr_create FOREIGN KEY (gbc_usr_id_create) REFERENCES %PREFIX%_users (usr_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_gbc_usr_change FOREIGN KEY (gbc_usr_id_change) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_ids
ADD CONSTRAINT %PREFIX%_fk_ids_usr_id FOREIGN KEY (ids_usr_id) REFERENCES %PREFIX%_users (usr_id) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_links
ADD CONSTRAINT %PREFIX%_fk_lnk_cat FOREIGN KEY (lnk_cat_id) REFERENCES %PREFIX%_categories (cat_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_lnk_usr_create FOREIGN KEY (lnk_usr_id_create) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_lnk_usr_change FOREIGN KEY (lnk_usr_id_change) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_lists
ADD CONSTRAINT %PREFIX%_fk_lst_usr FOREIGN KEY (lst_usr_id) REFERENCES %PREFIX%_users (usr_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_lst_org FOREIGN KEY (lst_org_id) REFERENCES %PREFIX%_organizations (org_id) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_list_columns
ADD CONSTRAINT %PREFIX%_fk_lsc_lst FOREIGN KEY (lsc_lst_id) REFERENCES %PREFIX%_lists (lst_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_lsc_usf FOREIGN KEY (lsc_usf_id) REFERENCES %PREFIX%_user_fields (usf_id) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_members
ADD CONSTRAINT %PREFIX%_fk_mem_rol FOREIGN KEY (mem_rol_id) REFERENCES %PREFIX%_roles (rol_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_mem_usr FOREIGN KEY (mem_usr_id) REFERENCES %PREFIX%_users (usr_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_mem_usr_create FOREIGN KEY (mem_usr_id_create) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_mem_usr_change FOREIGN KEY (mem_usr_id_change) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_menu
ADD CONSTRAINT %PREFIX%_fk_men_men_parent FOREIGN KEY (men_men_id_parent) REFERENCES %PREFIX%_menu (men_id) ON DELETE SET NULL ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_men_com_id FOREIGN KEY (men_com_id) REFERENCES %PREFIX%_components (com_id) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_messages
ADD CONSTRAINT %PREFIX%_fk_msg_usr_sender FOREIGN KEY (msg_usr_id_sender) REFERENCES %PREFIX%_users (usr_id) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_messages_attachments
ADD CONSTRAINT %PREFIX%_fk_msa_msg_id FOREIGN KEY (msa_msg_id) REFERENCES %PREFIX%_messages (msg_id) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_messages_content
ADD CONSTRAINT %PREFIX%_fk_msc_msg_id FOREIGN KEY (msc_msg_id) REFERENCES %PREFIX%_messages (msg_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_msc_usr_id FOREIGN KEY (msc_usr_id) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_messages_recipients
ADD CONSTRAINT %PREFIX%_fk_msr_msg_id FOREIGN KEY (msr_msg_id) REFERENCES %PREFIX%_messages (msg_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_msr_rol_id FOREIGN KEY (msr_rol_id) REFERENCES %PREFIX%_roles (rol_id) ON DELETE SET NULL ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_msr_usr_id FOREIGN KEY (msr_usr_id) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_organizations
ADD CONSTRAINT %PREFIX%_fk_org_org_parent FOREIGN KEY (org_org_id_parent) REFERENCES %PREFIX%_organizations (org_id) ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_photos
ADD CONSTRAINT %PREFIX%_fk_pho_pho_parent FOREIGN KEY (pho_pho_id_parent) REFERENCES %PREFIX%_photos (pho_id) ON DELETE SET NULL ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_pho_org FOREIGN KEY (pho_org_id) REFERENCES %PREFIX%_organizations (org_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_pho_usr_create FOREIGN KEY (pho_usr_id_create) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_pho_usr_change FOREIGN KEY (pho_usr_id_change) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_preferences
ADD CONSTRAINT %PREFIX%_fk_prf_org FOREIGN KEY (prf_org_id) REFERENCES %PREFIX%_organizations (org_id) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_registrations
ADD CONSTRAINT %PREFIX%_fk_reg_org FOREIGN KEY (reg_org_id) REFERENCES %PREFIX%_organizations (org_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_reg_usr FOREIGN KEY (reg_usr_id) REFERENCES %PREFIX%_users (usr_id) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_role_dependencies
ADD CONSTRAINT %PREFIX%_fk_rld_rol_child FOREIGN KEY (rld_rol_id_child) REFERENCES %PREFIX%_roles (rol_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_rld_rol_parent FOREIGN KEY (rld_rol_id_parent) REFERENCES %PREFIX%_roles (rol_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_rld_usr FOREIGN KEY (rld_usr_id) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_roles
ADD CONSTRAINT %PREFIX%_fk_rol_cat FOREIGN KEY (rol_cat_id) REFERENCES %PREFIX%_categories (cat_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_rol_lst_id FOREIGN KEY (rol_lst_id) REFERENCES %PREFIX%_lists (lst_id) ON DELETE SET NULL ON UPDATE SET NULL,
ADD CONSTRAINT %PREFIX%_fk_rol_usr_create FOREIGN KEY (rol_usr_id_create) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_rol_usr_change FOREIGN KEY (rol_usr_id_change) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_roles_rights
ADD CONSTRAINT %PREFIX%_fk_ror_ror_parent FOREIGN KEY (ror_ror_id_parent) REFERENCES %PREFIX%_roles_rights (ror_id) ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_roles_rights_data
ADD CONSTRAINT %PREFIX%_fk_rrd_ror FOREIGN KEY (rrd_ror_id) REFERENCES %PREFIX%_roles_rights (ror_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_rrd_rol FOREIGN KEY (rrd_rol_id) REFERENCES %PREFIX%_roles (rol_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_rrd_usr_create FOREIGN KEY (rrd_usr_id_create) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_rooms
ADD CONSTRAINT %PREFIX%_fk_room_usr_create FOREIGN KEY (room_usr_id_create) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_room_usr_change FOREIGN KEY (room_usr_id_change) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_sessions
ADD CONSTRAINT %PREFIX%_fk_ses_org FOREIGN KEY (ses_org_id) REFERENCES %PREFIX%_organizations (org_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_ses_usr FOREIGN KEY (ses_usr_id) REFERENCES %PREFIX%_users (usr_id) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_texts
ADD CONSTRAINT %PREFIX%_fk_txt_org FOREIGN KEY (txt_org_id) REFERENCES %PREFIX%_organizations (org_id) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_user_fields
ADD CONSTRAINT %PREFIX%_fk_usf_cat FOREIGN KEY (usf_cat_id) REFERENCES %PREFIX%_categories (cat_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_usf_usr_create FOREIGN KEY (usf_usr_id_create) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_usf_usr_change FOREIGN KEY (usf_usr_id_change) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_user_data
ADD CONSTRAINT %PREFIX%_fk_usd_usf FOREIGN KEY (usd_usf_id) REFERENCES %PREFIX%_user_fields (usf_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_usd_usr FOREIGN KEY (usd_usr_id) REFERENCES %PREFIX%_users (usr_id) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_user_log
ADD CONSTRAINT %PREFIX%_fk_user_log_1 FOREIGN KEY (usl_usr_id) REFERENCES %PREFIX%_users (usr_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_user_log_2 FOREIGN KEY (usl_usr_id_create) REFERENCES %PREFIX%_users (usr_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_user_log_3 FOREIGN KEY (usl_usf_id) REFERENCES %PREFIX%_user_fields (usf_id) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_users
ADD CONSTRAINT %PREFIX%_fk_usr_usr_create FOREIGN KEY (usr_usr_id_create) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_usr_usr_change FOREIGN KEY (usr_usr_id_change) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_user_relation_types
ADD CONSTRAINT %PREFIX%_fk_urt_id_inverse FOREIGN KEY (urt_id_inverse) REFERENCES %PREFIX%_user_relation_types (urt_id) ON DELETE CASCADE ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_urt_usr_change FOREIGN KEY (urt_usr_id_change) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_urt_usr_create FOREIGN KEY (urt_usr_id_create) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE %PREFIX%_user_relations
ADD CONSTRAINT %PREFIX%_fk_ure_urt FOREIGN KEY (ure_urt_id) REFERENCES %PREFIX%_user_relation_types (urt_id) ON DELETE CASCADE ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_ure_usr1 FOREIGN KEY (ure_usr_id1) REFERENCES %PREFIX%_users (usr_id) ON DELETE CASCADE ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_ure_usr2 FOREIGN KEY (ure_usr_id2) REFERENCES %PREFIX%_users (usr_id) ON DELETE CASCADE ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_ure_usr_change FOREIGN KEY (ure_usr_id_change) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT,
ADD CONSTRAINT %PREFIX%_fk_ure_usr_create FOREIGN KEY (ure_usr_id_create) REFERENCES %PREFIX%_users (usr_id) ON DELETE SET NULL ON UPDATE RESTRICT;