| 1 | DROP TABLE IF EXISTS profile_job_enum; |
| 2 | |
| 3 | CREATE TABLE IF NOT EXISTS profile_job_enum ( |
| 4 | id INT(6) UNSIGNED NOT NULL AUTO_INCREMENT, |
| 5 | name VARCHAR(255) NOT NULL DEFAULT '', |
| 6 | acronym VARCHAR(255) NOT NULL DEFAULT '', |
| 7 | url VARCHAR(255) NOT NULL DEFAULT '', |
| 8 | email VARCHAR(255) NOT NULL DEFAULT '', |
| 9 | holdingid INT(6) UNSIGNED DEFAULT NULL, |
| 10 | NAF_code CHAR(5) NOT NULL DEFAULT '', |
| 11 | AX_code BIGINT(10) UNSIGNED NOT NULL, |
| 12 | PRIMARY KEY (id), |
| 13 | UNIQUE KEY (name) |
| 14 | ) CHARSET=utf8; |
| 15 | |
| 16 | DROP TABLE IF EXISTS profile_job; |
| 17 | |
| 18 | CREATE TABLE IF NOT EXISTS profile_job ( |
| 19 | id TINYINT(1) UNSIGNED NOT NULL, |
| 20 | uid INT(11) NOT NULL DEFAULT 0, |
| 21 | jobid INT(6) UNSIGNED NOT NULL DEFAULT 0, |
| 22 | functionid SMALLINT(3) UNSIGNED NOT NULL DEFAULT 0, |
| 23 | sectorid TINYINT(2) UNSIGNED NOT NULL DEFAULT 0, |
| 24 | subsectorid TINYINT(3) UNSIGNED NOT NULL DEFAULT 0, |
| 25 | subsubsectorid SMALLINT(3) UNSIGNED NOT NULL DEFAULT 0, |
| 26 | description VARCHAR(255) NOT NULL DEFAULT '', |
| 27 | url VARCHAR(255) NOT NULL DEFAULT '', |
| 28 | email VARCHAR(255) NOT NULL DEFAULT '', |
| 29 | pub ENUM('private', 'ax', 'public') DEFAULT 'private', |
| 30 | email_pub ENUM('private', 'ax', 'public') DEFAULT 'private', |
| 31 | PRIMARY KEY (uid, id) |
| 32 | ) CHARSET=utf8; |
| 33 | |
| 34 | INSERT IGNORE INTO profile_job_enum (name, url) |
| 35 | SELECT entreprise, web |
| 36 | FROM entreprises; |
| 37 | |
| 38 | INSERT INTO profile_job (id, uid, jobid, description, email, pub, email_pub, functionid) |
| 39 | SELECT e.entrid, e.uid, j.id, e.poste, e.email, e.pub, e.email_pub, e.fonction |
| 40 | FROM entreprises AS e |
| 41 | INNER JOIN profile_job_enum AS j ON (e.entreprise = j.name); |
| 42 | |
| 43 | |
| 44 | -- vim:set syntax=mysql: |