root/trunk/install/dbschema.sql

Revision 2476, 84.0 kB (checked in by francesco, 1 week ago)

"Changed phpeace server settings"

Line 
1 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
2 /*!40103 SET TIME_ZONE='+00:00' */;
3 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
4 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
5 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
6 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
7 SET @saved_cs_client     = @@character_set_client;
8 SET character_set_client = utf8;
9 CREATE TABLE `accounts` (
10   `id_account` smallint(3) NOT NULL default '0',
11   `id_topic` smallint(4) NOT NULL default '0',
12   `id_user` smallint(4) NOT NULL default '0',
13   `id_type` tinyint(1) default '0',
14   `active` tinyint(1) default '1',
15   `name` varchar(150) default NULL,
16   `params` text,
17   `shared` tinyint(1) default '0',
18   PRIMARY KEY  (`id_account`)
19 );
20 SET character_set_client = @saved_cs_client;
21 SET @saved_cs_client     = @@character_set_client;
22 SET character_set_client = utf8;
23 CREATE TABLE `accounts_use` (
24   `id_use` smallint(3) NOT NULL default '0',
25   `type` tinyint(1) default '0',
26   `id_item` smallint(3) default '0',
27   `id_account` smallint(3) NOT NULL default '0',
28   PRIMARY KEY  (`id_use`)
29 );
30 SET character_set_client = @saved_cs_client;
31 SET @saved_cs_client     = @@character_set_client;
32 SET character_set_client = utf8;
33 CREATE TABLE `admin_text` (
34   `id_text` tinyint(2) NOT NULL default '0',
35   `title` varchar(200) NOT NULL default '',
36   `description` text NOT NULL,
37   `active` tinyint(1) NOT NULL default '0',
38   PRIMARY KEY  (`id_text`)
39 );
40 SET character_set_client = @saved_cs_client;
41 SET @saved_cs_client     = @@character_set_client;
42 SET character_set_client = utf8;
43 CREATE TABLE `ali_center` (
44   `id_center` smallint(4) NOT NULL default '0',
45   `name` varchar(60) default NULL,
46   `id_province` tinyint(4) default NULL,
47   PRIMARY KEY  (`id_center`)
48 );
49 SET character_set_client = @saved_cs_client;
50 SET @saved_cs_client     = @@character_set_client;
51 SET character_set_client = utf8;
52 CREATE TABLE `ali_company` (
53   `id_company` smallint(6) NOT NULL default '0',
54   `insert_date` date default NULL,
55   `name` varchar(100) default NULL,
56   `sector` varchar(255) default NULL,
57   `address` varchar(200) default NULL,
58   `town` varchar(30) default NULL,
59   `id_province` tinyint(4) default NULL,
60   `phone` varchar(50) default NULL,
61   `postcode` varchar(10) default NULL,
62   `notes` varchar(255) default NULL,
63   PRIMARY KEY  (`id_company`)
64 );
65 SET character_set_client = @saved_cs_client;
66 SET @saved_cs_client     = @@character_set_client;
67 SET character_set_client = utf8;
68 CREATE TABLE `ali_company_questions` (
69   `id_question` smallint(6) NOT NULL default '0',
70   `id_company` smallint(6) NOT NULL default '0',
71   `insert_date` date default NULL,
72   `name` varchar(100) default NULL,
73   `role` varchar(100) default NULL,
74   `employees` tinyint(1) default NULL,
75   `employees_f` tinyint(2) default NULL,
76   `employees_r` tinyint(2) default NULL,
77   `profiles` varchar(255) default NULL,
78   `gender` tinyint(1) default NULL,
79   `education` tinyint(1) default NULL,
80   `language` tinyint(1) default NULL,
81   `contract` tinyint(1) default NULL,
82   `user` varchar(100) default NULL,
83   `employees_rf` tinyint(2) default NULL,
84   `notes` varchar(255) default NULL,
85   PRIMARY KEY  (`id_question`)
86 );
87 SET character_set_client = @saved_cs_client;
88 SET @saved_cs_client     = @@character_set_client;
89 SET character_set_client = utf8;
90 CREATE TABLE `ali_person` (
91   `id_person` smallint(6) NOT NULL default '0',
92   `id_center` smallint(4) default NULL,
93   `id_user` smallint(4) default NULL,
94   `insert_date` date default NULL,
95   `name` varchar(50) default NULL,
96   `name2` varchar(50) default NULL,
97   `surname` varchar(50) default NULL,
98   `birth_place` varchar(100) default NULL,
99   `dob` date default NULL,
100   `gender` char(1) default NULL,
101   `country` varchar(50) default NULL,
102   `address_1` varchar(200) default NULL,
103   `zipcode_1` varchar(15) default NULL,
104   `town_1` varchar(30) default NULL,
105   `id_province_1` tinyint(4) default NULL,
106   `address_2` varchar(200) default NULL,
107   `zipcode_2` varchar(15) default NULL,
108   `town_2` varchar(30) default NULL,
109   `id_province_2` tinyint(4) default NULL,
110   `phone` varchar(50) default NULL,
111   `email` varchar(80) default NULL,
112   `taxcode` varchar(20) default NULL,
113   `passport` varchar(20) default NULL,
114   `passport_date` date default NULL,
115   `resident_permit_type` varchar(20) default NULL,
116   `resident_permit_expire_date` date default NULL,
117   `health_card` tinyint(1) default '0',
118   `driving_licence` tinyint(1) default '0',
119   `driving_licence_type` varchar(5) default NULL,
120   `family` tinyint(2) default '3',
121   `status` tinyint(1) default '1',
122   `marriage_day` date default NULL,
123   `gather_family` tinyint(1) default '0',
124   `sons` tinyint(2) default '0',
125   `relatives` varchar(255) default NULL,
126   `disability` varchar(200) default NULL,
127   `change_job` tinyint(1) default '1',
128   `shifts` varchar(10) default NULL,
129   `commute` tinyint(1) default NULL,
130   `transport` varchar(10) default NULL,
131   `languages` varchar(100) default NULL,
132   `italian` tinyint(1) default NULL,
133   `computer` tinyint(1) default NULL,
134   `edu_notes` varchar(255) default NULL,
135   `change_home` tinyint(1) default '0',
136   `change_home_reason` varchar(200) default NULL,
137   `home_area` varchar(200) default NULL,
138   `home_max_rent` smallint(6) default '0',
139   `home_type` varchar(10) default '0',
140   `home_notes` text,
141   `salary` tinyint(1) default '1',
142   `bank_account` tinyint(1) default '0',
143   `home_support` tinyint(1) default '1',
144   `religion` varchar(200) default NULL,
145   `emi_reason` varchar(10) default '0',
146   `town_reason` varchar(200) default NULL,
147   `varia_notes` text,
148   `contact` varchar(200) default NULL,
149   `employed` tinyint(1) default '0',
150   `skills` text,
151   `tskills` text,
152   `aims` text,
153   `job_notes` text,
154   `software` varchar(200) default NULL,
155   `how_much_support` smallint(5) default NULL,
156   PRIMARY KEY  (`id_person`)
157 );
158 SET character_set_client = @saved_cs_client;
159 SET @saved_cs_client     = @@character_set_client;
160 SET character_set_client = utf8;
161 CREATE TABLE `ali_person_edus` (
162   `id_edu` smallint(6) NOT NULL default '0',
163   `id_person` smallint(6) NOT NULL default '0',
164   `description` varchar(250) default NULL,
165   `country` varchar(20) default NULL,
166   `completed` tinyint(1) default '0',
167   `length` smallint(4) default '0',
168   PRIMARY KEY  (`id_edu`)
169 );
170 SET character_set_client = @saved_cs_client;
171 SET @saved_cs_client     = @@character_set_client;
172 SET character_set_client = utf8;
173 CREATE TABLE `ali_person_homes` (
174   `id_home` smallint(6) NOT NULL default '0',
175   `id_person` smallint(6) NOT NULL default '0',
176   `period` varchar(100) default NULL,
177   `place` varchar(200) default NULL,
178   `type` tinyint(1) default '1',
179   `contract` tinyint(1) default '1',
180   `contact` tinyint(1) default '1',
181   `left_reason` tinyint(1) default '1',
182   `home_notes` text,
183   PRIMARY KEY  (`id_home`)
184 );
185 SET character_set_client = @saved_cs_client;
186 SET @saved_cs_client     = @@character_set_client;
187 SET character_set_client = utf8;
188 CREATE TABLE `ali_person_inout` (
189   `id_inout` smallint(6) NOT NULL default '0',
190   `id_person` smallint(6) NOT NULL default '0',
191   `start_date` varchar(20) default NULL,
192   `reason` varchar(150) default NULL,
193   PRIMARY KEY  (`id_inout`)
194 );
195 SET character_set_client = @saved_cs_client;
196 SET @saved_cs_client     = @@character_set_client;
197 SET character_set_client = utf8;
198 CREATE TABLE `ali_person_jobs` (
199   `id_job` smallint(6) NOT NULL default '0',
200   `id_person` smallint(6) NOT NULL default '0',
201   `period` varchar(100) default NULL,
202   `description` varchar(200) default NULL,
203   `country` tinyint(1) default '1',
204   `contract` tinyint(1) default '1',
205   `employer` tinyint(1) default '1',
206   `contact` tinyint(1) default '1',
207   `left_reason` tinyint(1) default '1',
208   `parttime` tinyint(1) default '2',
209   `job_notes` text,
210   PRIMARY KEY  (`id_job`)
211 );
212 SET character_set_client = @saved_cs_client;
213 SET @saved_cs_client     = @@character_set_client;
214 SET character_set_client = utf8;
215 CREATE TABLE `ali_person_registrations` (
216   `id_register` smallint(6) NOT NULL default '0',
217   `id_person` smallint(6) NOT NULL default '0',
218   `start_date` varchar(20) default NULL,
219   `type` varchar(60) default NULL,
220   `length` varchar(4) default NULL,
221   PRIMARY KEY  (`id_register`)
222 );
223 SET character_set_client = @saved_cs_client;
224 SET @saved_cs_client     = @@character_set_client;
225 SET character_set_client = utf8;
226 CREATE TABLE `ali_person_relatives` (
227   `id_relative` smallint(3) NOT NULL default '0',
228   `id_person_rel` smallint(6) NOT NULL default '0',
229   `relation` varchar(12) default NULL,
230   `gender` char(1) default NULL,
231   `yearborn` smallint(4) default NULL,
232   `country` varchar(20) default NULL,
233   `study` varchar(30) default NULL,
234   `job` varchar(50) default NULL,
235   `id_person` smallint(6) default NULL,
236   PRIMARY KEY  (`id_relative`)
237 );
238 SET character_set_client = @saved_cs_client;
239 SET @saved_cs_client     = @@character_set_client;
240 SET character_set_client = utf8;
241 CREATE TABLE `ali_person_services` (
242   `id_service` smallint(6) NOT NULL default '0',
243   `id_person` smallint(6) NOT NULL default '0',
244   `insert_date` date default NULL,
245   `info` varchar(10) default NULL,
246   `request` varchar(10) default NULL,
247   `job_search` varchar(20) default NULL,
248   `home_search` varchar(20) default NULL,
249   `support` varchar(15) default NULL,
250   `support_description` text,
251   `result` varchar(10) default NULL,
252   `notes` text,
253   PRIMARY KEY  (`id_service`)
254 );
255 SET character_set_client = @saved_cs_client;
256 SET @saved_cs_client     = @@character_set_client;
257 SET character_set_client = utf8;
258 CREATE TABLE `ali_person_socs` (
259   `id_soc` smallint(6) NOT NULL default '0',
260   `id_person` smallint(6) NOT NULL default '0',
261   `period` varchar(100) default NULL,
262   `service` varchar(250) default NULL,
263   PRIMARY KEY  (`id_soc`)
264 );
265 SET character_set_client = @saved_cs_client;
266 SET @saved_cs_client     = @@character_set_client;
267 SET character_set_client = utf8;
268 CREATE TABLE `articles` (
269   `id_article` mediumint(6) unsigned NOT NULL default '0',
270   `id_user` smallint(4) default NULL,
271   `id_topic` smallint(4) default NULL,
272   `id_subtopic` smallint(5) default NULL,
273   `halftitle` varchar(255) default NULL,
274   `headline` varchar(255) default NULL,
275   `headline_visible` tinyint(1) default '1',
276   `source` text,
277   `author` varchar(255) default NULL,
278   `written` date default NULL,
279   `is_html` tinyint(1) default '0',
280   `approved` tinyint(1) default '0',
281   `id_approver` smallint(4) default NULL,
282   `published` datetime default NULL,
283   `show_date` tinyint(1) default '0',
284   `align` tinyint(2) default '0',
285   `id_visibility` tinyint(2) NOT NULL default '5',
286   `show_author` tinyint(1) default '1',
287   `original` date default NULL,
288   `id_language` tinyint(2) default '1',
289   `exported` datetime default NULL,
290   `available` tinyint(1) default '1',
291   `show_source` tinyint(1) default '0',
292   `show_latest` tinyint(1) default '1',
293   `author_notes` varchar(255) default NULL,
294   `id_licence` tinyint(2) default '0',
295   `allow_comments` tinyint(1) default '0',
296   `id_image` smallint(6) default '0',
297   `image_size` smallint(3) default '0',
298   `image_align` tinyint(1) default '1',
299   `image_width` smallint(5) default '0',
300   `image_height` smallint(5) default '0',
301   `jump_to_source` tinyint(1) NOT NULL default '0',
302   `id_template` smallint(3) NOT NULL default '0',
303   PRIMARY KEY  (`id_article`),
304   KEY `id_topic` (`id_topic`),
305   KEY `id_subtopic` (`id_subtopic`),
306   KEY `written` (`written`),
307   KEY `id_user` (`id_user`)
308 );
309 SET character_set_client = @saved_cs_client;
310 SET @saved_cs_client     = @@character_set_client;
311 SET character_set_client = utf8;
312 CREATE TABLE `articles_boxes` (
313   `id_box` smallint(6) NOT NULL default '0',
314   `id_article` mediumint(6) unsigned NOT NULL default '0',
315   `popup` tinyint(1) default '0',
316   `width` smallint(3) default '1',
317   `align` tinyint(1) default '0',
318   `z_width` smallint(4) default '400',
319   `z_height` smallint(4) default '400',
320   PRIMARY KEY  (`id_box`,`id_article`),
321   KEY `id_article` (`id_article`)
322 );
323 SET character_set_client = @saved_cs_client;
324 SET @saved_cs_client     = @@character_set_client;
325 SET character_set_client = utf8;
326 CREATE TABLE `articles_content` (
327   `id_article` mediumint(6) unsigned NOT NULL default '0',
328   `content` mediumtext,
329   `notes` text,
330   PRIMARY KEY  (`id_article`)
331 );
332 SET character_set_client = @saved_cs_client;
333 SET @saved_cs_client     = @@character_set_client;
334 SET character_set_client = utf8;
335 CREATE TABLE `articles_related` (
336   `id_article1` mediumint(6) unsigned NOT NULL default '0',
337   `id_article2` mediumint(6) unsigned NOT NULL default '0',
338   KEY `id_article1` (`id_article1`),
339   KEY `id_article2` (`id_article2`)
340 );
341 SET character_set_client = @saved_cs_client;
342 SET @saved_cs_client     = @@character_set_client;
343 SET character_set_client = utf8;
344 CREATE TABLE `articles_subhead` (
345   `id_article` mediumint(6) unsigned NOT NULL default '0',
346   `subhead` text,
347   PRIMARY KEY  (`id_article`)
348 );
349 SET character_set_client = @saved_cs_client;
350 SET @saved_cs_client     = @@character_set_client;
351 SET character_set_client = utf8;
352 CREATE TABLE `ass` (
353   `id_ass` smallint(5) NOT NULL default '0',
354   `nome` varchar(255) default NULL,
355   `nome2` varchar(255) default NULL,
356   `indirizzo` varchar(255) default NULL,
357   `citta` varchar(255) default NULL,
358   `id_prov` tinyint(3) default NULL,
359   `cap` varchar(10) default NULL,
360   `tel` varchar(50) default NULL,
361   `fax` varchar(50) default NULL,
362   `email` varchar(50) default NULL,
363   `sito` varchar(150) default NULL,
364   `ccp` varchar(255) default NULL,
365   `note` text NOT NULL,
366   `referente` varchar(200) default NULL,
367   `pubblicaz` varchar(255) default NULL,
368   `fonte` varchar(255) default NULL,
369   `id_tipo` tinyint(2) default NULL,
370   `approved` tinyint(1) default NULL,
371   `lastupd` timestamp NOT NULL default CURRENT_TIMESTAMP,
372   `id_p` mediumint(7) NOT NULL default '0',
373   `kparams` text,
374   `insert_date` datetime default NULL,
375   `admin_notes` varchar(1000) NOT NULL default '',
376   `sorting` float NOT NULL default '0',
377   PRIMARY KEY  (`id_ass`)
378 );
379 SET character_set_client = @saved_cs_client;
380 SET @saved_cs_client     = @@character_set_client;
381 SET character_set_client = utf8;
382 CREATE TABLE `ass_tipo` (
383   `id_ass_tipo` tinyint(2) NOT NULL default '0',
384   `ass_tipo` varchar(30) default NULL,
385   PRIMARY KEY  (`id_ass_tipo`)
386 );
387 SET character_set_client = @saved_cs_client;
388 SET @saved_cs_client     = @@character_set_client;
389 SET character_set_client = utf8;
390 CREATE TABLE `banners` (
391   `id_banner` smallint(4) NOT NULL default '0',
392   `alt_text` varchar(255) default NULL,
393   `link` varchar(255) default NULL,
394   `can_expire` tinyint(1) default '0',
395   `start_date` date default NULL,
396   `expire_date` date default NULL,
397   `id_group` smallint(3) default NULL,
398   `filename` varchar(255) default NULL,
399   `popup` tinyint(1) default '1',
400   PRIMARY KEY  (`id_banner`),
401   KEY `id_group` (`id_group`)
402 );
403 SET character_set_client = @saved_cs_client;
404 SET&nb