From 12a717c77b1479601eac4652b84a5eb24668b531 Mon Sep 17 00:00:00 2001 From: Chuck Scott Date: Mon, 19 Jan 2015 12:56:18 -0500 Subject: [PATCH] Removing stupid temporary files. --- .../databaseScripts/create_database_V0.1.sql~ | 836 ------------------ misc/databaseScripts/drop_database_V0.1.sql~ | 1 - 2 files changed, 837 deletions(-) delete mode 100644 misc/databaseScripts/create_database_V0.1.sql~ delete mode 100644 misc/databaseScripts/drop_database_V0.1.sql~ diff --git a/misc/databaseScripts/create_database_V0.1.sql~ b/misc/databaseScripts/create_database_V0.1.sql~ deleted file mode 100644 index 5a365c8c..00000000 --- a/misc/databaseScripts/create_database_V0.1.sql~ +++ /dev/null @@ -1,836 +0,0 @@ --- Gaslight Media Members Database --- File Created: 12/09/14 15:27:15 --- Database Version: 0.1 --- Database Creation Script - --- ----------------------------------------------------- --- Table 'members'.'cities' --- ----------------------------------------------------- -CREATE TABLE '{prefix}cities' ( - 'id' INT NOT NULL AUTO_INCREMENT, - 'name' TINYTEXT NULL, - PRIMARY KEY ('id') -); - - --- ----------------------------------------------------- --- Table 'members'.'states' --- ----------------------------------------------------- -CREATE TABLE '{prefix}states' ( - 'id' INT NOT NULL AUTO_INCREMENT, - 'name' TINYTEXT NULL, - 'code' TINYTEXT NULL, - PRIMARY KEY ('id') -); - - --- ----------------------------------------------------- --- Table 'members'.'countries' --- ----------------------------------------------------- -CREATE TABLE '{prefix}countries' ( - 'id' INT NOT NULL AUTO_INCREMENT, - 'name' TINYTEXT NULL, - 'code' TINYTEXT NULL, - PRIMARY KEY ('id') -); - - --- ----------------------------------------------------- --- Table 'members'.'regions' --- ----------------------------------------------------- -CREATE TABLE '{prefix}regions' ( - 'id' INT NOT NULL AUTO_INCREMENT, - 'active' TINYINT(1) NULL, - 'name' TINYTEXT NULL, - 'descr' TEXT NULL, - 'short_descr' TINYTEXT NULL, - PRIMARY KEY ('id') -); - - --- ----------------------------------------------------- --- Table 'members'.'members' --- ----------------------------------------------------- -CREATE TABLE '{prefix}members' ( - 'id' INT NOT NULL AUTO_INCREMENT, - 'active' TINYINT(1) NULL, - 'archived' TINYINT(1) NULL, - 'type' ENUM('something','somethingelse') NULL, - 'name' TINYTEXT NULL, - 'descr' TEXT NULL, - 'short_descr' TINYTEXT NULL, - 'addr1' TINYTEXT NULL, - 'addr2' TINYTEXT NULL, - 'city' INT NULL, - 'state' INT NULL, - 'country' INT NULL, - 'zip' TINYTEXT NULL, - 'lat' FLOAT NULL, - 'lon' FLOAT NULL, - 'region' INT NULL, - 'phone' TINYTEXT NULL, - 'toll_free' TINYTEXT NULL, - 'url' TINYTEXT NULL, - 'logo' TINYTEXT NULL, - 'cc_type' INT NULL, - 'join_date' DATE NULL, - 'notex' TEXT NULL, - 'create_time' TIMESTAMP NULL, - 'modify_time' TIMESTAMP NULL, - PRIMARY KEY ('id'), - INDEX 'fk_city_idx' ('city' ASC), - INDEX 'fk_state_idx' ('state' ASC), - INDEX 'fk_country_idx' ('country' ASC), - INDEX 'fk_region_idx' ('region' ASC), - UNIQUE INDEX 'id_UNIQUE' ('id' ASC), - CONSTRAINT 'fk_city' - FOREIGN KEY ('city') - REFERENCES 'members'.'cities' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_state' - FOREIGN KEY ('state') - REFERENCES 'members'.'states' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_country' - FOREIGN KEY ('country') - REFERENCES 'members'.'countries' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_region' - FOREIGN KEY ('region') - REFERENCES 'members'.'regions' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION -); - - --- ----------------------------------------------------- --- Table 'members'.'locations' --- ----------------------------------------------------- -CREATE TABLE '{prefix}locations' ( - 'id' INT NOT NULL AUTO_INCREMENT, - 'active' TINYINT(1) NULL, - 'archived' TINYINT(1) NULL, - 'type' ENUM('hotel','restaurant') NULL, - 'member' INT NULL, - 'name' TINYTEXT NULL, - 'descr' TEXT NULL, - 'short_descr' TINYTEXT NULL, - 'addr1' TINYTEXT NULL, - 'addr2' TINYTEXT NULL, - 'city' INT NULL, - 'state' INT NULL, - 'country' INT NULL, - 'zip' TINYTEXT NULL, - 'lat' FLOAT NULL, - 'lon' FLOAT NULL, - 'region' INT NULL, - 'phone' TINYTEXT NULL, - 'toll_free' TINYTEXT NULL, - 'url' TINYTEXT NULL, - 'logo' TINYTEXT NULL, - 'notes' TEXT NULL, - 'create_time' TIMESTAMP NULL, - 'modify_time' TIMESTAMP NULL, - PRIMARY KEY ('id'), - INDEX 'fk_member_idx' ('member' ASC), - INDEX 'fk_city_idx' ('city' ASC), - INDEX 'fk_state_idx' ('state' ASC), - INDEX 'fk_country_idx' ('country' ASC), - INDEX 'fk_region_idx' ('region' ASC), - CONSTRAINT 'fk_member' - FOREIGN KEY ('member') - REFERENCES 'members'.'members' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_city' - FOREIGN KEY ('city') - REFERENCES 'members'.'cities' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_state' - FOREIGN KEY ('state') - REFERENCES 'members'.'states' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_country' - FOREIGN KEY ('country') - REFERENCES 'members'.'countries' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_region' - FOREIGN KEY ('region') - REFERENCES 'members'.'regions' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION -); - - --- ----------------------------------------------------- --- Table 'members'.'facilties' --- ----------------------------------------------------- -CREATE TABLE '{prefix}facilties' ( - -); - - --- ----------------------------------------------------- --- Table 'members'.'facilities' --- ----------------------------------------------------- -CREATE TABLE '{prefix}facilities' ( - 'id' INT NOT NULL AUTO_INCREMENT, - 'active' TINYINT(1) NULL, - 'archived' TINYINT(1) NULL, - 'type' ENUM('hotel_motel','bandb','restaurant','golf') NULL COMMENT 'Type of facility', - 'location' INT NULL, - 'name' TINYTEXT NULL, - 'descr' TEXT NULL, - 'short_descr' TINYTEXT NULL, - 'addr1' TINYTEXT NULL, - 'addr2' TINYTEXT NULL, - 'city' INT NULL, - 'state' INT NULL, - 'country' INT NULL, - 'zip' TINYTEXT NULL, - 'lat' FLOAT NULL, - 'lon' FLOAT NULL, - 'phone' TINYTEXT NULL, - 'toll_free' TINYTEXT NULL, - 'url' TINYTEXT NULL, - 'logo' TINYTEXT NULL, - 'notes' TEXT NULL, - 'create_time' TIMESTAMP NULL, - 'modify_time' TIMESTAMP NULL, - PRIMARY KEY ('id'), - INDEX 'fk_location_idx' ('location' ASC), - INDEX 'fk_city_idx' ('city' ASC), - INDEX 'fk_state_idx' ('state' ASC), - INDEX 'fk_country_idx' ('country' ASC), - CONSTRAINT 'fk_location' - FOREIGN KEY ('location') - REFERENCES 'members'.'locations' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_city' - FOREIGN KEY ('city') - REFERENCES 'members'.'cities' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_state' - FOREIGN KEY ('state') - REFERENCES 'members'.'states' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_country' - FOREIGN KEY ('country') - REFERENCES 'members'.'countries' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION -); - - --- ----------------------------------------------------- --- Table 'members'.'activties' --- ----------------------------------------------------- -CREATE TABLE '{prefix}activties' ( - 'id' INT NOT NULL AUTO_INCREMENT, - 'active' TINYINT(1) NULL, - 'archived' TINYINT(1) NULL, - 'type' TINYTEXT NULL, - 'facility' INT NULL, - 'name' TINYTEXT NULL, - 'descr' TEXT NULL, - 'short_descr' TINYTEXT NULL, - 'phone' TINYTEXT NULL, - 'url' TINYTEXT NULL, - 'notes' TEXT NULL, - 'create_time' TIMESTAMP NULL, - 'modify_time' TIMESTAMP NULL, - 'ref_type' ENUM('member','location','facility') NULL, - 'ref_dest' INT NULL, - PRIMARY KEY ('id'), - INDEX 'fk_member_idx' ('ref_dest' ASC), - CONSTRAINT 'fk_member' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'members' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_location' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'locations' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_facility' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'facilities' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION -); - - --- ----------------------------------------------------- --- Table 'members'.'contacts' --- ----------------------------------------------------- -CREATE TABLE '{prefix}contacts' ( - 'id' INT NOT NULL AUTO_INCREMENT, - 'active' TINYINT(1) NULL, - 'archive' TINYINT(1) NULL, - 'ref_type' ENUM('member','location','facility','activity') NULL, - 'ref_dest' INT NULL, - 'fname' TINYTEXT NULL, - 'lname' TINYTEXT NULL, - 'type' ENUM('personal','role') NULL, - 'org' TINYTEXT NULL, - 'addr1' TINYTEXT NULL, - 'addr2' TINYTEXT NULL, - 'city' INT NULL, - 'state' INT NULL, - 'country' INT NULL, - 'zip' TINYTEXT NULL, - 'lat' FLOAT NULL, - 'lon' FLOAT NULL, - 'url' TINYTEXT NULL, - 'office_phone' TINYTEXT NULL, - 'home_phone' TINYTEXT NULL, - 'mobile_phone' TINYTEXT NULL, - 'alt_phone' TINYTEXT NULL, - 'fax' TINYTEXT NULL, - 'email' TINYTEXT NULL, - 'alt_email' TINYTEXT NULL, - 'password' TINYTEXT NULL, - 'permissions' TINYTEXT NULL, - 'notes' TEXT NULL, - 'create_time' TIMESTAMP NULL, - 'modify_time' TIMESTAMP NULL, - PRIMARY KEY ('id'), - INDEX 'fk_member_idx' ('ref_dest' ASC), - INDEX 'fk_city_idx' ('city' ASC), - INDEX 'fk_state_idx' ('state' ASC), - INDEX 'fk_country_idx' ('country' ASC), - CONSTRAINT 'fk_member' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'members' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_location' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'locations' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_facility' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'facilities' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_activity' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'activties' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_city' - FOREIGN KEY ('city') - REFERENCES 'members'.'cities' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_state' - FOREIGN KEY ('state') - REFERENCES 'members'.'states' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_country' - FOREIGN KEY ('country') - REFERENCES 'members'.'countries' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION -); - - --- ----------------------------------------------------- --- Table 'members'.'amenities' --- ----------------------------------------------------- -CREATE TABLE '{prefix}amenities' ( - 'id' INT NOT NULL AUTO_INCREMENT, - 'active' TINYINT(1) NULL, - 'name' TINYTEXT NULL, - 'descr' TEXT NULL, - 'short_descr' TINYTEXT NULL, - 'facility_type' ENUM('hotel_motel','bandb','restaurant','golf') NULL, - PRIMARY KEY ('id'), - UNIQUE INDEX 'id_UNIQUE' ('id' ASC) -); - - --- ----------------------------------------------------- --- Table 'members'.'social_media' --- ----------------------------------------------------- -CREATE TABLE '{prefix}social_media' ( - 'id' INT NOT NULL AUTO_INCREMENT, - 'name' TINYTEXT NULL, - 'descr' TEXT NULL, - 'short_descr' TINYTEXT NULL, - PRIMARY KEY ('id') -); - - --- ----------------------------------------------------- --- Table 'members'.'social_media_member' --- ----------------------------------------------------- -CREATE TABLE '{prefix}social_media_member' ( - 'id' INT NOT NULL AUTO_INCREMENT, - 'ref_type' ENUM('member','location','facility','contact') NULL COMMENT 'Which table this entry is linked with.\n', - 'ref_dest' INT NULL COMMENT 'The entry ID in the table set by ref_type\n', - 'social_media' INT NULL, - 'url' TINYTEXT NULL, - PRIMARY KEY ('id'), - INDEX 'fk_member_idx' ('ref_dest' ASC), - INDEX 'fk_service_idx' ('social_media' ASC), - UNIQUE INDEX 'id_UNIQUE' ('id' ASC), - CONSTRAINT 'fk_member' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'members' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_location' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'locations' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_service' - FOREIGN KEY ('social_media') - REFERENCES 'members'.'social_media' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_facility' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'facilities' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_contact' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'contacts' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION -); - - --- ----------------------------------------------------- --- Table 'members'.'category' --- ----------------------------------------------------- -CREATE TABLE '{prefix}category' ( - 'id' INT NOT NULL AUTO_INCREMENT, - 'name' (255) NOT NULL, - PRIMARY KEY ('category_id')); - - --- ----------------------------------------------------- --- Table 'members'.'amenity_member' --- ----------------------------------------------------- -CREATE TABLE '{prefix}amenity_member' ( - 'id' INT NOT NULL AUTO_INCREMENT, - 'amenity' INT NULL, - 'ref_type' ENUM('member','location','facility') NULL, - 'ref_dest' INT NULL, - PRIMARY KEY ('id'), - UNIQUE INDEX 'id_UNIQUE' ('id' ASC), - INDEX 'fk_location_idx' ('ref_dest' ASC), - INDEX 'fk_amenity_idx' ('amenity' ASC), - CONSTRAINT 'fk_member' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'members' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_location' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'locations' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_facility' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'facilities' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_amenity' - FOREIGN KEY ('amenity') - REFERENCES 'members'.'amenities' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION -); - - --- ----------------------------------------------------- --- Table 'members'.'accommodations' --- ----------------------------------------------------- -CREATE TABLE '{prefix}accommodations' ( - 'id' INT NOT NULL AUTO_INCREMENT, - 'active' TINYINT(1) NULL, - 'archived' TINYINT(1) NULL, - 'name' TINYTEXT NULL, - 'descr' TEXT NULL, - 'short_descr' TINYTEXT NULL, - 'quant' INT NULL, - 'reservation_url' TINYTEXT NULL, - 'reservation_id' TINYTEXT NULL, - 'year_round' TINYINT(1) NULL, - 'ref_type' ENUM('member','location','facility') NULL, - 'ref_dest' INT NULL, - PRIMARY KEY ('id'), - UNIQUE INDEX 'id_UNIQUE' ('id' ASC), - INDEX 'fk_member_idx' ('ref_dest' ASC), - CONSTRAINT 'fk_member' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'members' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_location' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'locations' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_facility' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'facilities' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION -); - - --- ----------------------------------------------------- --- Table 'members'.'accounts' --- ----------------------------------------------------- -CREATE TABLE '{prefix}accounts' ( - 'id' INT NOT NULL AUTO_INCREMENT, - 'member' INT NULL, - 'payment_type' ENUM('credit_card','check') NULL, - 'invoice_delivery' ENUM('email','usmail','fax') NULL, - PRIMARY KEY ('id'), - UNIQUE INDEX 'id_UNIQUE' ('id' ASC), - INDEX 'fk_member_idx' ('member' ASC), - CONSTRAINT 'fk_member' - FOREIGN KEY ('member') - REFERENCES 'members'.'members' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION -); - - --- ----------------------------------------------------- --- Table 'members'.'categories' --- ----------------------------------------------------- -CREATE TABLE '{prefix}categories' ( - 'id' INT NOT NULL AUTO_INCREMENT, - 'name' TINYTEXT NULL, - 'descr' TEXT NULL, - 'short_descr' TINYTEXT NULL, - 'parent' INT NULL, - PRIMARY KEY ('id'), - UNIQUE INDEX 'id_UNIQUE' ('id' ASC), - INDEX 'fk_parent_idx' ('parent' ASC), - CONSTRAINT 'fk_parent' - FOREIGN KEY ('parent') - REFERENCES 'members'.'categories' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION -); - - --- ----------------------------------------------------- --- Table 'members'.'category_member' --- ----------------------------------------------------- -CREATE TABLE '{prefix}category_member' ( - 'id' INT NOT NULL AUTO_INCREMENT, - 'cateogory' INT NULL, - 'primary' TINYINT(1) NULL, - 'ref_type' ENUM('member','location','facility') NULL, - 'ref_dest' INT NULL, - PRIMARY KEY ('id'), - UNIQUE INDEX 'id_UNIQUE' ('id' ASC), - INDEX 'fk_category_idx' ('cateogory' ASC), - INDEX 'fk_member_idx' ('ref_dest' ASC), - CONSTRAINT 'fk_category' - FOREIGN KEY ('cateogory') - REFERENCES 'members'.'categories' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_member' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'members' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_location' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'locations' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_facility' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'facilities' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION -); - - --- ----------------------------------------------------- --- Table 'members'.'golf' --- ----------------------------------------------------- -CREATE TABLE '{prefix}golf' ( - 'id' INT NOT NULL AUTO_INCREMENT, - 'active' TINYINT(1) NULL, - 'archived' TINYINT(1) NULL, - 'name' TINYTEXT NULL, - 'descr' TEXT NULL, - 'short_descr' TINYTEXT NULL, - 'rating' TINYTEXT NULL, - 'par' TINYTEXT NULL, - 'yardage' TINYTEXT NULL, - 'slope' TINYTEXT NULL, - 'walking' TINYINT(1) NULL, - 'holes' ENUM('9','18') NULL, - 'reservation_url' TINYTEXT NULL, - 'ref_type' ENUM('member','location','facility') NULL, - 'ref_dest' INT NULL, - PRIMARY KEY ('id'), - INDEX 'fk_member_idx' ('ref_dest' ASC), - CONSTRAINT 'fk_member' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'members' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_location' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'locations' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_facility' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'facilities' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION -); - - --- ----------------------------------------------------- --- Table 'members'.'restaurants' --- ----------------------------------------------------- -CREATE TABLE '{prefix}restaurants' ( - 'id' INT NOT NULL AUTO_INCREMENT, - 'active' TINYINT(1) NULL, - 'archived' TINYINT(1) NULL, - 'name' TINYTEXT NULL, - 'descr' TEXT NULL, - 'short_descr' TINYTEXT NULL, - 'menu' TEXT NULL, - 'special_menu' TEXT NULL, - 'url' TINYTEXT NULL, - 'reservation_url' TINYTEXT NULL, - 'phone' TINYTEXT NULL, - 'hours_descr' TINYTEXT NULL, - 'alcohol' TINYINT(1) NULL, - 'non_smoking' TINYINT(1) NULL, - 'notes' TEXT NULL, - 'ref_type' ENUM('member','location','facility') NULL, - 'ref_dest' INT NULL, - PRIMARY KEY ('id'), - UNIQUE INDEX 'id_UNIQUE' ('id' ASC), - INDEX 'fk_member_idx' ('ref_dest' ASC), - CONSTRAINT 'fk_member' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'members' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_location' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'locations' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_facility' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'facilities' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION -); - - --- ----------------------------------------------------- --- Table 'members'.'files' --- ----------------------------------------------------- -CREATE TABLE '{prefix}files' ( - 'id' INT NOT NULL AUTO_INCREMENT, - 'name' TINYTEXT NULL, - 'file_name' TINYTEXT NULL, - 'descr' TEXT NULL, - 'short_descr' TINYTEXT NULL, - 'size' INT NULL, - 'pending' TINYINT(1) NULL, - 'create_date' DATE NULL, - 'ref_type' ENUM('member','location','facility') NULL, - 'ref_dest' INT NULL, - PRIMARY KEY ('id'), - UNIQUE INDEX 'id_UNIQUE' ('id' ASC), - INDEX 'fk_member_idx' ('ref_dest' ASC), - CONSTRAINT 'fk_member' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'members' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_location' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'locations' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_facility' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'facilities' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_activity' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'activties' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_accommodation' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'accommodations' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_golf' - FOREIGN KEY () - REFERENCES 'members'.'golf' () - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_restaurant' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'restaurants' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_contact' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'contacts' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION -); - - --- ----------------------------------------------------- --- Table 'members'.'images' --- ----------------------------------------------------- -CREATE TABLE '{prefix}images' ( - 'id' INT NOT NULL AUTO_INCREMENT, - 'name' TINYTEXT NULL, - 'file_name' TINYTEXT NULL, - 'descr' TEXT NULL, - 'caption' TINYTEXT NULL, - 'pending' TINYINT(1) NULL, - 'position' INT NULL, - 'ref_type' ENUM('member','location','facility','activity','accommodation','golf','restaurant','contact') NULL, - 'ref_dest' INT NULL, - PRIMARY KEY ('id'), - INDEX 'fk_member_idx' ('ref_dest' ASC), - CONSTRAINT 'fk_member' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'members' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_location' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'locations' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_facility' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'facilities' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_activity' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'activties' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_contact' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'contacts' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_accommodation' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'accommodations' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_golf' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'golf' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION, - CONSTRAINT 'fk_restaraunt' - FOREIGN KEY ('ref_dest') - REFERENCES 'members'.'restaurants' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION) -ENGINE = InnoDB -COMMENT = ' '; - - --- ----------------------------------------------------- --- Table 'members'.'meals' --- ----------------------------------------------------- -CREATE TABLE '{prefix}meals' ( - 'id' INT NOT NULL AUTO_INCREMENT, - 'active' TINYINT(1) NULL, - 'archived' TINYINT(1) NULL, - 'name' TINYTEXT NULL, - 'descr' TEXT NULL, - 'short_descr' TINYTEXT NULL, - 'menu' TEXT NULL, - 'special_menu' TEXT NULL, - 'daily_time' TINYINT(1) NULL, - 'daily_start_time' TIME NULL, - 'daily_end_time' TIME NULL, - 'daily_res_req' TINYINT(1) NULL, - 'sunday' TINYINT(1) NULL, - 'sun_menu' TEXT NULL, - 'sun_start_time' TIME NULL, - 'sun_end_time' TIME NULL, - 'sun_res_req' TINYINT(1) NULL, - 'monday' TINYINT(1) NULL, - 'mon_menu' TEXT NULL, - 'mon_start_time' TIME NULL, - 'mon_end_time' TIME NULL, - 'mon_res_req' TINYINT(1) NULL, - 'tuesday' TINYINT(1) NULL, - 'tue_menu' TEXT NULL, - 'tue_start_time' TIME NULL, - 'tue_end_time' TIME NULL, - 'tue_res_req' TINYINT(1) NULL, - 'wednesday' TINYINT(1) NULL, - 'wed_menu' TEXT NULL, - 'wed_start_time' TIME NULL, - 'wed_end_time' TIME NULL, - 'wed_res_req' TINYINT(1) NULL, - 'thursday' TINYINT(1) NULL, - 'thu_menu' TEXT NULL, - 'thu_start_time' TIME NULL, - 'thu_end_time' TIME NULL, - 'thu_res_req' TINYINT(1) NULL, - 'friday' TINYINT(1) NULL, - 'fri_menu' TEXT NULL, - 'fri_start_time' TIME NULL, - 'fri_end_time' TIME NULL, - 'fri_res_req' TINYINT(1) NULL, - 'saturday' TINYINT(1) NULL, - 'sat_menu' TEXT NULL, - 'sat_start_time' TIME NULL, - 'sat_end_time' TIME NULL, - 'sat_res_req' TINYINT(1) NULL, - 'restaurant' INT NULL, - PRIMARY KEY ('id'), - UNIQUE INDEX 'id_UNIQUE' ('id' ASC), - INDEX 'fk_restaurant_idx' ('restaurant' ASC), - CONSTRAINT 'fk_restaurant' - FOREIGN KEY ('restaurant') - REFERENCES 'members'.'restaurants' ('id') - ON DELETE NO ACTION - ON UPDATE NO ACTION -); - - diff --git a/misc/databaseScripts/drop_database_V0.1.sql~ b/misc/databaseScripts/drop_database_V0.1.sql~ deleted file mode 100644 index 94c40bba..00000000 --- a/misc/databaseScripts/drop_database_V0.1.sql~ +++ /dev/null @@ -1 +0,0 @@ -drop table {prefix}test_table; -- 2.17.1