Commit | Line | Data |
---|---|---|
c7139c07 SJ |
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 '', | |
b814a8b8 | 8 | email VARCHAR(255) NOT NULL DEFAULT '', |
c7139c07 SJ |
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 | ||
f9cddbef SJ |
34 | INSERT IGNORE INTO profile_job_enum (name, url) |
35 | SELECT entreprise, web | |
36 | FROM entreprises; | |
c7139c07 | 37 | |
f9cddbef SJ |
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 | |
c7139c07 SJ |
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: |