From 950bf4f677e923c34f7d1523215f06876e338717 Mon Sep 17 00:00:00 2001 From: Florent Bruneau Date: Sun, 21 Feb 2010 10:23:36 +0100 Subject: [PATCH] Update import scripts: create table using InnoDB engine. Signed-off-by: Florent Bruneau --- upgrade/account/00_account.sql | 6 +++--- upgrade/account/01_profiles.sql | 2 +- upgrade/account/02_carnet.sql | 2 +- upgrade/account/03_carva.sql | 2 +- upgrade/account/04_emails.sql | 2 +- upgrade/account/copy_tables.php | 3 ++- upgrade/newdirectory-0.0.1/00_names.sql | 8 +++----- upgrade/newdirectory-0.0.1/02_networking.sql | 4 ++-- upgrade/newdirectory-0.0.1/03_emails.sql | 2 +- upgrade/newdirectory-0.0.1/04_telephone.sql | 2 +- upgrade/newdirectory-0.0.1/06_education.sql | 10 +++++----- upgrade/newdirectory-0.0.1/{07_corps => 07_corps.sql} | 6 +++--- upgrade/newdirectory-0.0.1/08_nationalities.sql | 2 +- upgrade/newdirectory-0.0.1/11_jobs.sql | 4 ++-- upgrade/newdirectory-0.0.1/12_secteurs.sql | 8 ++++---- upgrade/newdirectory-0.0.1/13_mentoring.sql | 6 +++--- upgrade/newdirectory-0.0.1/15_addresses.sql | 10 +++++----- 17 files changed, 39 insertions(+), 40 deletions(-) rename upgrade/newdirectory-0.0.1/{07_corps => 07_corps.sql} (94%) diff --git a/upgrade/account/00_account.sql b/upgrade/account/00_account.sql index e8523d3..f580252 100644 --- a/upgrade/account/00_account.sql +++ b/upgrade/account/00_account.sql @@ -32,14 +32,14 @@ CREATE TABLE accounts ( key full_name (full_name), key state (state), key type (type) -); +) ENGINE=InnoDB, CHARSET=utf8; CREATE TABLE account_types ( type varchar(16) not null, perms set('mail', 'groups', 'forums', 'list', 'search', 'portal') default '', primary key type (type) -); +) ENGINE=InnoDB, CHARSET=utf8; CREATE TABLE account_profiles ( uid int(6) not null, @@ -49,6 +49,6 @@ CREATE TABLE account_profiles ( primary key id (uid, pid), key uid (uid), key pid (pid) -); +) ENGINE=InnoDB, CHARSET=utf8; # vim:set syntax=mysql: diff --git a/upgrade/account/01_profiles.sql b/upgrade/account/01_profiles.sql index 02fa34b..96c0f85 100644 --- a/upgrade/account/01_profiles.sql +++ b/upgrade/account/01_profiles.sql @@ -37,6 +37,6 @@ create table profiles ( key nationality1 (nationality1), key nationality2 (nationality2), key nationality3 (nationality3) -); +) ENGINE=InnoDB, CHARSET=utf8; # vim:set syntax=mysql: diff --git a/upgrade/account/02_carnet.sql b/upgrade/account/02_carnet.sql index 8e635fa..ec903bf 100644 --- a/upgrade/account/02_carnet.sql +++ b/upgrade/account/02_carnet.sql @@ -6,6 +6,6 @@ create table watch ( primary key uid (uid), key flags (flags) -); +) ENGINE=InnoDB, CHARSET=utf8; # vim:set syntax=mysql: diff --git a/upgrade/account/03_carva.sql b/upgrade/account/03_carva.sql index b583fc0..b3a40cd 100644 --- a/upgrade/account/03_carva.sql +++ b/upgrade/account/03_carva.sql @@ -3,6 +3,6 @@ create table carvas ( url varchar(255) not null, primary key uid (uid) -); +) ENGINE=InnoDB, CHARSET=utf8; # vim:set syntax=mysql: diff --git a/upgrade/account/04_emails.sql b/upgrade/account/04_emails.sql index c8df1a1..4036f17 100644 --- a/upgrade/account/04_emails.sql +++ b/upgrade/account/04_emails.sql @@ -3,6 +3,6 @@ create table email_options ( storage set('imap', 'googleapps') not null default '', primary key uid (uid) -); +) ENGINE=InnoDB, CHARSET=utf8; # vim:set syntax=mysql: diff --git a/upgrade/account/copy_tables.php b/upgrade/account/copy_tables.php index 1fcc473..76aecb3 100755 --- a/upgrade/account/copy_tables.php +++ b/upgrade/account/copy_tables.php @@ -25,7 +25,8 @@ require('./connect.db.inc.php'); function copyTable($source, $target) { XDB::execute('CREATE TABLE ' . $target . ' - LIKE ' . $source); + LIKE ' . $source . ' + ENGINE = InnoDB'); XDB::execute('INSERT INTO ' . $target . ' SELECT * FROM ' . $source); diff --git a/upgrade/newdirectory-0.0.1/00_names.sql b/upgrade/newdirectory-0.0.1/00_names.sql index 39d1b1d..0fcc2b6 100644 --- a/upgrade/newdirectory-0.0.1/00_names.sql +++ b/upgrade/newdirectory-0.0.1/00_names.sql @@ -10,7 +10,7 @@ CREATE TABLE profile_display ( sort_name VARCHAR(255) NOT NULL, promo VARCHAR(255) DEFAULT '' NOT NULL, PRIMARY KEY(pid) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; INSERT INTO profile_display (pid, yourself, public_name, private_name, directory_name, short_name, sort_name) SELECT u.user_id, u.prenom, @@ -35,7 +35,7 @@ CREATE TABLE IF NOT EXISTS profile_name_enum ( score TINYINT(2) UNSIGNED NOT NULL DEFAULT 10, PRIMARY KEY (id), UNIQUE (name) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; INSERT INTO profile_name_enum (name, flags, explanations, type, score) VALUES ('Nom patronymique', 'has_particle,always_displayed,public', @@ -69,7 +69,7 @@ CREATE TABLE IF NOT EXISTS profile_name ( typeid TINYINT(2) UNSIGNED NOT NULL COMMENT 'type of name', PRIMARY KEY (pid, id), INDEX pid (pid) -) CHARSET=utf8 COMMENT = 'Names of alumni (search table)'; +) ENGINE=InnoDB, CHARSET=utf8, COMMENT = 'Names of alumni (search table)'; INSERT INTO profile_name (pid, name, typeid) SELECT u.user_id, u.nom, e.id @@ -107,8 +107,6 @@ INSERT INTO profile_name (pid, name, typeid) INNER JOIN profile_name_enum AS e ON (e.name = 'Surnom') WHERE profile_nick != ''; -DROP TABLE IF EXISTS recherche_soundex; - DELETE FROM search_autocomplete WHERE name = 'name' OR name = 'firstname' OR name = 'nickname'; -- vim:set syntax=mysql: diff --git a/upgrade/newdirectory-0.0.1/02_networking.sql b/upgrade/newdirectory-0.0.1/02_networking.sql index fa01af8..0ef0ddc 100644 --- a/upgrade/newdirectory-0.0.1/02_networking.sql +++ b/upgrade/newdirectory-0.0.1/02_networking.sql @@ -5,7 +5,7 @@ CREATE TABLE IF NOT EXISTS `profile_networking_enum` ( `filter` enum('email','web','number','none') NOT NULL DEFAULT 'none' COMMENT 'filter type for addresses', `link` varchar(255) NOT NULL COMMENT 'string used to forge an URL linking to the the profile page', PRIMARY KEY (`network_type`) -) CHARSET=utf8 COMMENT='types of networking addresses'; +) ENGINE=InnoDB, CHARSET=utf8, COMMENT='types of networking addresses'; CREATE TABLE IF NOT EXISTS `profile_networking` ( `uid` int NOT NULL COMMENT 'user id', @@ -15,7 +15,7 @@ CREATE TABLE IF NOT EXISTS `profile_networking` ( `pub` enum('private','public') NOT NULL DEFAULT 'private', PRIMARY KEY (`uid`, `nwid`), INDEX uid (uid) -) CHARSET=utf8 COMMENT='networking addresses'; +) ENGINE=InnoDB, CHARSET=utf8, COMMENT='networking addresses'; -- Insert a first address type for old URLs INSERT INTO `profile_networking_enum` (`network_type`, `name`, `icon`, `filter`) diff --git a/upgrade/newdirectory-0.0.1/03_emails.sql b/upgrade/newdirectory-0.0.1/03_emails.sql index 6ad3c7f..dc7eea3 100644 --- a/upgrade/newdirectory-0.0.1/03_emails.sql +++ b/upgrade/newdirectory-0.0.1/03_emails.sql @@ -2,6 +2,6 @@ CREATE TABLE IF NOT EXISTS profile_directory ( uid INT NOT NULL, email_directory VARCHAR(255) DEFAULT NULL, PRIMARY KEY (uid) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; ALTER TABLE register_marketing MODIFY COLUMN type ENUM('user', 'staff', 'ax'); diff --git a/upgrade/newdirectory-0.0.1/04_telephone.sql b/upgrade/newdirectory-0.0.1/04_telephone.sql index bff48ee..dc09189 100644 --- a/upgrade/newdirectory-0.0.1/04_telephone.sql +++ b/upgrade/newdirectory-0.0.1/04_telephone.sql @@ -11,7 +11,7 @@ CREATE TABLE IF NOT EXISTS `profile_phones` ( PRIMARY KEY(`uid`, `link_type`, `link_id`, `tel_id`), INDEX (`search_tel`), INDEX uid (uid) -); +) ENGINE=InnoDB, CHARSET=utf8; -- Adds a temporary column to convert phone prefixes from varchar to int diff --git a/upgrade/newdirectory-0.0.1/06_education.sql b/upgrade/newdirectory-0.0.1/06_education.sql index 60d75e5..bd0c9ab 100644 --- a/upgrade/newdirectory-0.0.1/06_education.sql +++ b/upgrade/newdirectory-0.0.1/06_education.sql @@ -3,7 +3,7 @@ CREATE TABLE IF NOT EXISTS profile_education_field_enum ( field VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id), UNIQUE KEY(field) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; CREATE TABLE IF NOT EXISTS profile_education_degree_enum ( id INT(2) NOT NULL AUTO_INCREMENT, @@ -12,13 +12,13 @@ CREATE TABLE IF NOT EXISTS profile_education_degree_enum ( level TINYINT (1) UNSIGNED DEFAULT 0 NOT NULL, PRIMARY KEY(id), UNIQUE KEY(degree) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; CREATE TABLE IF NOT EXISTS profile_education_degree ( eduid INT(4) NOT NULL DEFAULT 0, degreeid INT(2) NOT NULL DEFAULT 0, PRIMARY KEY(eduid, degreeid) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; CREATE TABLE IF NOT EXISTS profile_education_enum ( id INT(4) NOT NULL AUTO_INCREMENT, @@ -28,7 +28,7 @@ CREATE TABLE IF NOT EXISTS profile_education_enum ( country CHAR(2) NOT NULL DEFAULT 'FR', PRIMARY KEY(id), UNIQUE KEY(name) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; CREATE TABLE IF NOT EXISTS profile_education ( id TINYINT(2) UNSIGNED NOT NULL DEFAULT 0, @@ -42,7 +42,7 @@ CREATE TABLE IF NOT EXISTS profile_education ( flags SET('primary') DEFAULT '' NOT NULL, PRIMARY KEY(id, uid), INDEX uid (uid) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; INSERT INTO profile_education_field_enum (field) VALUES ('Aéronautique'), ('Agronomie'), ('Assurance'), ('Biologie'), diff --git a/upgrade/newdirectory-0.0.1/07_corps b/upgrade/newdirectory-0.0.1/07_corps.sql similarity index 94% rename from upgrade/newdirectory-0.0.1/07_corps rename to upgrade/newdirectory-0.0.1/07_corps.sql index f0b52d9..0487410 100644 --- a/upgrade/newdirectory-0.0.1/07_corps +++ b/upgrade/newdirectory-0.0.1/07_corps.sql @@ -5,7 +5,7 @@ CREATE TABLE IF NOT EXISTS profile_corps ( rankid INT(4) UNSIGNED NOT NULL DEFAULT 0, corps_pub ENUM('private', 'ax', 'public') NOT NULL DEFAULT 'private', PRIMARY KEY(uid) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; CREATE TABLE IF NOT EXISTS profile_corps_enum ( @@ -16,7 +16,7 @@ CREATE TABLE IF NOT EXISTS profile_corps_enum ( PRIMARY KEY(id), UNIQUE KEY(name), UNIQUE KEY(abbreviation) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; CREATE TABLE IF NOT EXISTS profile_corps_rank_enum ( @@ -26,7 +26,7 @@ CREATE TABLE IF NOT EXISTS profile_corps_rank_enum ( PRIMARY KEY(id), UNIQUE KEY(name), UNIQUE KEY(abbreviation) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; UPDATE profile_corps AS c INNER JOIN profile_corps_enum AS e ON (c.original_corpsid = e.id) diff --git a/upgrade/newdirectory-0.0.1/08_nationalities.sql b/upgrade/newdirectory-0.0.1/08_nationalities.sql index 247c102..937579d 100644 --- a/upgrade/newdirectory-0.0.1/08_nationalities.sql +++ b/upgrade/newdirectory-0.0.1/08_nationalities.sql @@ -5,7 +5,7 @@ CREATE TEMPORARY TABLE IF NOT EXISTS tmp_update_geoloc_pays ( code CHAR(4) DEFAULT NULL, PRIMARY KEY(a2), UNIQUE KEY(code) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; INSERT IGNORE INTO tmp_update_geoloc_pays (a2, code) VALUES ('AF', 'AFG'), diff --git a/upgrade/newdirectory-0.0.1/11_jobs.sql b/upgrade/newdirectory-0.0.1/11_jobs.sql index 7c175a4..58896bc 100644 --- a/upgrade/newdirectory-0.0.1/11_jobs.sql +++ b/upgrade/newdirectory-0.0.1/11_jobs.sql @@ -11,7 +11,7 @@ CREATE TABLE IF NOT EXISTS profile_job_enum ( AX_code BIGINT(10) UNSIGNED NOT NULL, PRIMARY KEY (id), UNIQUE KEY (name) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; DROP TABLE IF EXISTS profile_job; @@ -29,7 +29,7 @@ CREATE TABLE IF NOT EXISTS profile_job ( email_pub ENUM('private', 'ax', 'public') DEFAULT 'private', PRIMARY KEY (uid, id), INDEX uid (uid) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; INSERT IGNORE INTO profile_job_enum (name, url) SELECT entreprise, web diff --git a/upgrade/newdirectory-0.0.1/12_secteurs.sql b/upgrade/newdirectory-0.0.1/12_secteurs.sql index 799870c..e8065b1 100644 --- a/upgrade/newdirectory-0.0.1/12_secteurs.sql +++ b/upgrade/newdirectory-0.0.1/12_secteurs.sql @@ -5,7 +5,7 @@ CREATE TABLE IF NOT EXISTS profile_job_sector_enum ( name VARCHAR(256) NOT NULL DEFAULT '', PRIMARY KEY(id), UNIQUE KEY(name) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; DROP TABLE IF EXISTS profile_job_subsector_enum; @@ -15,7 +15,7 @@ CREATE TABLE IF NOT EXISTS profile_job_subsector_enum ( flags SET('optgroup') DEFAULT '' NOT NULL, name VARCHAR(256) NOT NULL DEFAULT '', PRIMARY KEY(id) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; DROP TABLE IF EXISTS profile_job_subsubsector_enum; @@ -26,7 +26,7 @@ CREATE TABLE IF NOT EXISTS profile_job_subsubsector_enum ( name VARCHAR(256) NOT NULL DEFAULT '', flags SET('display') NOT NULL DEFAULT 'display', PRIMARY KEY(id) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; DROP TABLE IF EXISTS profile_job_alternates; @@ -36,7 +36,7 @@ CREATE TABLE IF NOT EXISTS profile_job_alternates ( name VARCHAR(256) NOT NULL DEFAULT '', PRIMARY KEY(id, subsubsectorid), UNIQUE KEY(name) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; ALTER TABLE profile_phones CHANGE COLUMN link_type link_type ENUM('address','pro','user', 'hq') NOT NULL DEFAULT 'user'; diff --git a/upgrade/newdirectory-0.0.1/13_mentoring.sql b/upgrade/newdirectory-0.0.1/13_mentoring.sql index 9191616..9e392fd 100644 --- a/upgrade/newdirectory-0.0.1/13_mentoring.sql +++ b/upgrade/newdirectory-0.0.1/13_mentoring.sql @@ -7,7 +7,7 @@ CREATE TABLE IF NOT EXISTS profile_mentor ( expertise TEXT NOT NULL, PRIMARY KEY (uid), FULLTEXT INDEX (expertise) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; INSERT INTO profile_mentor (uid, expertise) SELECT uid, expertise @@ -18,7 +18,7 @@ CREATE TABLE IF NOT EXISTS profile_mentor_country ( country CHAR(2) NOT NULL DEFAULT "FR", PRIMARY KEY (uid, country), INDEX uid (uid) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; INSERT INTO profile_mentor_country (uid, country) SELECT uid, pid @@ -30,6 +30,6 @@ CREATE TABLE IF NOT EXISTS profile_mentor_sector ( subsectorid TINYINT(3) UNSIGNED NOT NULL, PRIMARY KEY (uid, sectorid, subsectorid), INDEX uid (uid) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; -- vim:set syntax=mysql: diff --git a/upgrade/newdirectory-0.0.1/15_addresses.sql b/upgrade/newdirectory-0.0.1/15_addresses.sql index 276bea9..8a95d0a 100644 --- a/upgrade/newdirectory-0.0.1/15_addresses.sql +++ b/upgrade/newdirectory-0.0.1/15_addresses.sql @@ -32,7 +32,7 @@ CREATE TABLE IF NOT EXISTS profile_addresses ( INDEX administrativeAreaId (administrativeAreaId), INDEX subAdministrativeAreaId (subAdministrativeAreaId), INDEX countryId (countryId) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; INSERT INTO profile_addresses (pid, id, postalCode, updateTime, pub, comment, latitude, longitude, countryId, type, flags) @@ -63,7 +63,7 @@ CREATE TABLE IF NOT EXISTS geoloc_countries ( UNIQUE KEY(iso_3166_1_num), INDEX(iso_3166_1_a2), INDEX(phonePrefix) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; UPDATE geoloc_pays SET n3 = 450 @@ -89,7 +89,7 @@ CREATE TABLE IF NOT EXISTS geoloc_administrativeareas ( INDEX(id), INDEX(name), INDEX(country) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; CREATE TABLE IF NOT EXISTS geoloc_subadministrativeareas ( id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, @@ -100,7 +100,7 @@ CREATE TABLE IF NOT EXISTS geoloc_subadministrativeareas ( INDEX(id), INDEX(name), INDEX(country) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; CREATE TABLE IF NOT EXISTS geoloc_localities ( id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, @@ -111,6 +111,6 @@ CREATE TABLE IF NOT EXISTS geoloc_localities ( INDEX(id), INDEX(name), INDEX(country) -) CHARSET=utf8; +) ENGINE=InnoDB, CHARSET=utf8; -- vim:set syntax=mysql: -- 2.1.4