source: lliurex-jocomunico/trunk/fuentes/lliurex-jocomunico.install/var/lib/ddbb/2016-07-15-definicio-bbdd-new-jocomunico.txt @ 5728

Last change on this file since 5728 was 5728, checked in by joamuran, 2 years ago

Added zero-installed and updated jocomunico

File size: 28.6 KB
Line 
1CREATE DATABASE jocomunicoapp
2    DEFAULT CHARACTER SET utf8mb4_unicode_ci;
3
4#*****************
5# BLOC USERS
6#*****************
7
8CREATE TABLE Languages (
9        ID_Language INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
10        languageabbr VARCHAR(20),
11        languageName VARCHAR(100),
12        canExpand ENUM('0', '1') DEFAULT '1',
13        type ENUM('svo', 'sov') DEFAULT 'svo',
14        nounAdjOrder ENUM('0', '1') DEFAULT '0',
15        nounComplementOrder ENUM('0', '1') DEFAULT '1'
16) ENGINE=INNODB;
17
18CREATE TABLE Voices (
19        ID_Voice INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
20        IDLang_L INT,
21        voiceType ENUM('online', 'offline') DEFAULT 'online',
22        voiceName VARCHAR(100),
23        vocalwareIdLang INT NULL,
24        vocalwareLangAbbr VARCHAR(10) NULL,
25        vocalwareVoiceId INT NULL,
26        vocalwareDescr VARCHAR(20) NULL,
27        mascfem ENUM('masc', 'fem') DEFAULT 'masc',
28        INDEX IDLang (IDLang_L),
29        FOREIGN KEY (IDLang_L) REFERENCES Languages (ID_Language) ON DELETE SET NULL
30) ENGINE=INNODB;
31
32
33CREATE TABLE SuperUser (
34        ID_SU INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
35        SUname VARCHAR(50),
36        pswd VARCHAR(32),
37        realname VARCHAR(200),
38        surnames VARCHAR(300),
39        email VARCHAR(300),
40        cfgDefUser INT,
41        cfgIsFem ENUM('0', '1') DEFAULT '0',
42        cfgUsageMouseOneCTwoC ENUM('0', '1', '2') DEFAULT '0',
43        cfgTimeClick INT DEFAULT '1000',
44        cfgExpansionOnOff ENUM('0', '1') DEFAULT '1',
45        cfgAutoEraseSentenceBar ENUM('0', '1') DEFAULT '0',
46        cfgPredOnOff ENUM('0', '1') DEFAULT '1',
47        cfgPredBarVertHor ENUM('0', '1') DEFAULT '0',
48        cfgPredBarNumPred INT DEFAULT '5',
49        cfgScanningOnOff ENUM('0', '1') DEFAULT '0',
50        cfgScanningCustomRowCol ENUM('0', '1', '2') DEFAULT '1',
51        cfgScanningAutoOnOff ENUM('0', '1') DEFAULT '0',
52        cfgCancelScanOnOff ENUM('0', '1') DEFAULT '1',
53        cfgTimeScanning INT DEFAULT '1000',
54        cfgScanStartClick ENUM('0', '1') DEFAULT '0',
55        cfgScanOrderPred INT DEFAULT '1',
56        cfgScanOrderMenu INT DEFAULT '2',
57        cfgScanOrderPanel INT DEFAULT '3',
58        cfgScanColor VARCHAR(100) DEFAULT 'FF0000',
59        cfgMenuReadActive ENUM('0', '1') DEFAULT '1',
60        cfgMenuHomeActive ENUM('0', '1') DEFAULT '1',
61        cfgMenuDeleteLastActive ENUM('0', '1') DEFAULT '1',
62        cfgMenuDeleteAllActive ENUM('0', '1') DEFAULT '1',
63        cfgSentenceBarUpDown ENUM('0', '1') DEFAULT '0',
64        cfgBgColorPanel INT DEFAULT '3',
65        cfgBgColorPred INT DEFAULT '6',
66        cfgTextInCell ENUM('0', '1') DEFAULT '1',
67        cfgUserExpansionFeedback ENUM('0', '1') DEFAULT '0',
68        cfgHistOnOff ENUM('0', '1') DEFAULT '1',
69        cfgBlackOnWhiteVSWhiteOnBlack ENUM('0', '1') DEFAULT '0',
70        cfgTimeLapseSelectOnOff ENUM('0', '1') DEFAULT '0',
71        cfgTimeLapseSelect INT DEFAULT '1500',
72        cfgTimeNoRepeatedClickOnOff ENUM('0', '1') DEFAULT '0',
73        cfgTimeNoRepeatedClick INT DEFAULT '300',
74        UserValidated ENUM('0', '1', '2') DEFAULT '0',
75        insertDate DATE NULL
76) ENGINE=INNODB;
77
78
79CREATE TABLE Organization (
80        ID_Org INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
81        orgName VARCHAR(300)
82) ENGINE=INNODB;
83
84
85CREATE TABLE User (
86        ID_User INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
87        ID_USU INT,
88        ID_ULanguage INT,
89        ID_UOrg INT,
90        cfgExpansionVoiceOnline VARCHAR(300),
91        cfgExpansionVoiceOnlineType ENUM('online', 'offline') DEFAULT 'online',
92        cfgExpansionVoiceOffline VARCHAR(300),
93        cfgInterfaceVoiceOnOff ENUM('0', '1') DEFAULT '0',
94        cfgInterfaceVoiceMascFem ENUM('masc', 'fem') DEFAULT 'fem',
95        cfgInterfaceVoiceOnline VARCHAR(300),
96        cfgInterfaceVoiceOffline VARCHAR(300),
97        cfgVoiceOfflineRate INT DEFAULT '0',   
98        cfgExpansionLanguage INT DEFAULT '1',
99        errorTemp INT NULL,
100        INDEX USuperUser (ID_USU),
101        FOREIGN KEY (ID_USU) REFERENCES SuperUser (ID_SU) ON DELETE SET NULL,
102        INDEX ULanguage (ID_ULanguage),
103        FOREIGN KEY (ID_ULanguage) REFERENCES Languages (ID_Language) ON DELETE SET NULL,
104        INDEX UOrg (ID_UOrg),
105        FOREIGN KEY (ID_UOrg) REFERENCES Organization (ID_Org) ON DELETE SET NULL,
106        INDEX ExpansionLanguage (cfgExpansionLanguage),
107        FOREIGN KEY (cfgExpansionLanguage) REFERENCES Languages (ID_Language) ON DELETE SET NULL
108) ENGINE=INNODB;
109
110
111#*****************
112# BLOC BOARDS
113#*****************
114
115# (per defecte a tots els usus se'ls hi ha de crear un groupboard per boards orfes, com esborranys)
116CREATE TABLE GroupBoards (
117        ID_GB INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
118        ID_GBUser INT,
119        GBname VARCHAR(100),
120        primaryGroupBoard ENUM('0', '1') DEFAULT '0',
121        defWidth INT DEFAULT '8',
122        defHeight INT DEFAULT '5',
123        imgGB VARCHAR(300),
124        INDEX GBUser (ID_GBUser),
125        FOREIGN KEY (ID_GBUser) REFERENCES User (ID_User) ON DELETE CASCADE
126) ENGINE=INNODB;
127
128
129# (opció de moure i de copiar boards a altres groupboards)
130CREATE TABLE Boards (
131        ID_Board INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
132        ID_GBBoard INT,
133        primaryBoard ENUM('0', '1') DEFAULT '0',
134        Bname VARCHAR(200),
135        width INT,
136        height INT,
137        autoReturn ENUM('0', '1') DEFAULT '0',
138        autoReadSentence ENUM('0', '1') DEFAULT '0',
139        INDEX GBBoard (ID_GBBoard),
140        FOREIGN KEY (ID_GBBoard) REFERENCES GroupBoards (ID_GB) ON DELETE CASCADE
141) ENGINE=INNODB;       
142
143
144# (supportsExpansion: si han introduït tota la info necessària perquè aquest pictograma funcioni amb el sistema d'expansió / afegir-ho al codi)
145# (ID_PUser: per defecte serà de l'usuari 0 que tindrà el vocab per defecte)
146CREATE TABLE Pictograms (
147        pictoid INT NOT NULL AUTO_INCREMENT,
148        ID_PUser INT DEFAULT '1',
149        pictoType VARCHAR(100),
150        supportsExpansion ENUM('0', '1') DEFAULT '1',
151        imgPicto VARCHAR(300),
152        PRIMARY KEY (pictoid, ID_PUser),
153        INDEX PUser (ID_PUser),
154        FOREIGN KEY (ID_PUser) REFERENCES User (ID_User) ON DELETE CASCADE
155) ENGINE=INNODB;
156
157
158# (functShortName el nom de la funció js o del modificador del sistema d'expansió (tipus frase, i, femení, temps verbal, etc.)
159# (functNameCa... el nom que apareixerà al llistat per triar les funcions)
160CREATE TABLE Function (
161        ID_Function INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
162        functType VARCHAR(50),
163        functValue VARCHAR(50),
164        functNameCA VARCHAR(100),
165        functNameES VARCHAR(100),
166        functDescrCA TEXT NULL,
167        functDescrES TEXT NULL,
168        imgFunct VARCHAR(300) NULL
169) ENGINE=INNODB;
170
171
172#*****************
173# BLOC SENTENCES
174#*****************
175
176CREATE TABLE S_Temp (
177        ID_STemp INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
178        ID_STUser INT,
179        sentenceType VARCHAR(20) DEFAULT 'defecte',
180        isNegative ENUM('0', '1') DEFAULT '0',
181        sentenceTense VARCHAR(20) DEFAULT 'defecte',
182        sentenceDate DATE,
183        sentenceFinished ENUM('0', '1') DEFAULT '0',
184        intendedSentence VARCHAR(500) NULL,
185        inputWords VARCHAR(500) NULL,
186        inputIds VARCHAR(500) NULL,
187        parseScore INT NULL,
188        parseString TEXT NULL,
189        generatorScore INT NULL,
190        generatorString VARCHAR(500) NULL,
191        comments TEXT NULL,
192        INDEX STUser (ID_STUser),
193        FOREIGN KEY (ID_STUser) REFERENCES User (ID_User) ON DELETE CASCADE
194) ENGINE=INNODB;
195
196
197# (ja que un pictograma pot estar repetit a la mateixa frase) --> s'haurà de vigilar quan es canviï al codi els noms de les vars --> aquest serà diferent per la taula R_S_TempPictograms i la taula R_S_HistoricPictograms
198
199CREATE TABLE R_S_TempPictograms (
200        ID_RSTPSentencePicto INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
201        ID_RSTPSentence INT,
202        pictoid INT,
203        isplural ENUM('0', '1') DEFAULT '0',
204        isfem ENUM('0', '1') DEFAULT '0',
205        coordinated ENUM('0', '1') DEFAULT '0',
206        ID_RSTPUser INT,
207        imgtemp VARCHAR(300) NULL,
208        INDEX RSTPSentence (ID_RSTPSentence),
209        FOREIGN KEY (ID_RSTPSentence) REFERENCES S_Temp (ID_STemp) ON DELETE CASCADE,
210        INDEX PictoID (pictoid),
211        FOREIGN KEY (pictoid) REFERENCES Pictograms (pictoid) ON DELETE CASCADE,
212        INDEX RSTPUser (ID_RSTPUser),
213        FOREIGN KEY (ID_RSTPUser) REFERENCES User (ID_User) ON DELETE CASCADE
214) ENGINE=INNODB;
215
216
217CREATE TABLE S_Historic (
218        ID_SHistoric INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
219        ID_SHUser INT,
220        sentenceType VARCHAR(20) DEFAULT 'defecte',
221        isNegative ENUM('0', '1') DEFAULT '0',
222        sentenceTense VARCHAR(20) DEFAULT 'defecte',
223        sentenceDate DATE,
224        sentenceFinished ENUM('0', '1') DEFAULT '0',
225        intendedSentence VARCHAR(500) NULL,
226        inputWords VARCHAR(500) NULL,
227        inputIds VARCHAR(500) NULL,
228        parseScore INT NULL,
229        parseString TEXT NULL,
230        generatorScore INT NULL,
231        generatorString VARCHAR(500) NULL,
232        comments TEXT NULL,
233        userScore INT NULL,
234        INDEX SHUser (ID_SHUser),
235        FOREIGN KEY (ID_SHUser) REFERENCES User (ID_User) ON DELETE CASCADE
236) ENGINE=INNODB;
237
238
239CREATE TABLE R_S_HistoricPictograms (
240        ID_RSHPSentencePicto INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
241        ID_RSHPSentence INT,
242        pictoid INT,
243        isplural ENUM('0', '1') DEFAULT '0',
244        isfem ENUM('0', '1') DEFAULT '0',
245        coordinated ENUM('0', '1') DEFAULT '0',
246        ID_RSHPUser INT,
247        imgtemp VARCHAR(300) NULL,
248        INDEX RSHPSentence (ID_RSHPSentence),
249        FOREIGN KEY (ID_RSHPSentence) REFERENCES S_Historic (ID_SHistoric) ON DELETE CASCADE,
250        INDEX PictoID (pictoid),
251        FOREIGN KEY (pictoid) REFERENCES Pictograms (pictoid) ON DELETE CASCADE,
252        INDEX RSHPUser (ID_RSHPUser),
253        FOREIGN KEY (ID_RSHPUser) REFERENCES User (ID_User) ON DELETE CASCADE
254) ENGINE=INNODB;
255
256
257CREATE TABLE S_Folder (
258        ID_Folder INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
259        ID_SFUser INT,
260        folderName VARCHAR(200),
261        folderDescr TEXT,
262        imgSFolder VARCHAR(300),
263        folderColor VARCHAR(100) DEFAULT 'FFFFFF',
264        folderOrder INT NULL,
265        INDEX SFUser (ID_SFUser),
266        FOREIGN KEY (ID_SFUser) REFERENCES User (ID_User) ON DELETE SET NULL
267) ENGINE=INNODB;       
268
269# (per les PreRec: fem que sempre tingui els mp3 guardats al servidor o que faci servir la cache generar amb els mp3 temporals?)
270CREATE TABLE S_Sentence (
271        ID_SSentence INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
272        ID_SSUser INT,
273        ID_SFolder INT,
274        posInFolder INT NULL,
275        sentenceType VARCHAR(20) DEFAULT 'defecte',
276        isNegative ENUM('0', '1') DEFAULT '0',
277        sentenceTense VARCHAR(20) DEFAULT 'defecte',
278        sentenceDate DATE,
279        sentenceFinished ENUM('0', '1') DEFAULT '0',
280        intendedSentence VARCHAR(500) NULL,
281        inputWords VARCHAR(500) NULL,
282        inputIds VARCHAR(500) NULL,
283        parseScore INT NULL,
284        parseString TEXT NULL,
285        generatorScore INT NULL,
286        generatorString VARCHAR(500) NULL,
287        comments TEXT NULL,
288        userScore INT NULL,
289        isPreRec ENUM('0', '1') DEFAULT '0',
290        sPreRecText TEXT,
291        sPreRecDate DATE,
292        sPreRecImg1 VARCHAR(300),
293        sPreRecImg2 VARCHAR(300),
294        sPreRecImg3 VARCHAR(300),
295        sPreRecPath VARCHAR(300),
296        INDEX SSUser (ID_SSUser),
297        FOREIGN KEY (ID_SSUser) REFERENCES User (ID_User) ON DELETE SET NULL,
298        INDEX SFolder (ID_SFolder),
299        FOREIGN KEY (ID_SFolder) REFERENCES S_Folder (ID_Folder) ON DELETE SET NULL
300) ENGINE=INNODB;
301
302
303CREATE TABLE R_S_SentencePictograms (
304        ID_RSSPSentencePicto INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
305        ID_RSSPSentence INT,
306        pictoid INT,
307        isplural ENUM('0', '1') DEFAULT '0',
308        isfem ENUM('0', '1') DEFAULT '0',
309        coordinated ENUM('0', '1') DEFAULT '0',
310        ID_RSSPUser INT,
311        imgtemp VARCHAR(300) NULL,
312        INDEX RSSPSentence (ID_RSSPUser),
313        FOREIGN KEY (ID_RSSPUser) REFERENCES S_Sentence (ID_SSentence) ON DELETE CASCADE,
314        INDEX PictoID (pictoid),
315        FOREIGN KEY (pictoid) REFERENCES Pictograms (pictoid) ON DELETE SET NULL,
316        INDEX RSSPUser (ID_RSSPUser),
317        FOREIGN KEY (ID_RSSPUser) REFERENCES User (ID_User) ON DELETE SET NULL
318) ENGINE=INNODB;
319       
320
321#*****************
322# BLOC CELLS
323#*****************
324
325
326# (boardLink tindrà l'ID del Board al que apunti, en cas de ser un enllaç)
327CREATE TABLE Cell (
328        ID_Cell INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
329        isFixedInGroupBoards ENUM('0', '1') DEFAULT '0',
330        imgCell VARCHAR(300),
331        ID_CPicto INT NULL,
332        ID_CSentence INT NULL,
333        sentenceFolder INT NULL,
334        boardLink INT NULL,
335        color VARCHAR(100) DEFAULT 'FFFFFF',
336        ID_CFunction INT NULL,
337        textInCell VARCHAR(100),
338        textInCellTextOnOff  ENUM('0', '1') DEFAULT '1',
339        cellType ENUM('picto', 'link', 'funct', 'sentence', 'sfolder', 'other') DEFAULT 'other',
340        activeCell ENUM('0', '1') DEFAULT '1',
341        INDEX CPicto (ID_CPicto),
342        FOREIGN KEY (ID_CPicto) REFERENCES Pictograms (pictoid) ON DELETE SET NULL,
343        INDEX CSentence (ID_CSentence),
344        FOREIGN KEY (ID_CSentence) REFERENCES S_Sentence (ID_SSentence) ON DELETE SET NULL,
345        INDEX CFunction (ID_CFunction),
346        FOREIGN KEY (ID_CFunction) REFERENCES Function (ID_Function) ON DELETE SET NULL
347) ENGINE=INNODB;
348
349CREATE TABLE R_BoardCell (
350        ID_RBoard INT,
351        ID_RCell INT,
352        posInBoard INT,
353        isMenu ENUM('0', '1') DEFAULT '0',
354        posInMenu INT,
355        customScanBlock1 INT DEFAULT '1',
356        customScanBlockText1 VARCHAR(200) NULL,
357        customScanBlock2 INT NULL,
358        customScanBlockText2 VARCHAR(200) NULL,
359        PRIMARY KEY (ID_RBoard, ID_RCell),
360        INDEX RBoard (ID_RBoard),
361        FOREIGN KEY (ID_RBoard) REFERENCES Boards (ID_Board) ON DELETE CASCADE,
362        INDEX RCell (ID_RCell),
363        FOREIGN KEY (ID_RCell) REFERENCES Cell (ID_Cell) ON DELETE CASCADE
364) ENGINE=INNODB;
365
366
367
368#******************************
369# BLOC FITXERS I CONTINGUT WEB
370#******************************
371
372# (per les imatges pujades pels usuaris)
373CREATE TABLE  Images (
374        ID_Image INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
375        ID_ISU INT,
376        imgPath VARCHAR(300),
377        imgName VARCHAR(300),
378        INDEX ISuperUser (ID_ISU),
379        FOREIGN KEY (ID_ISU) REFERENCES SuperUser (ID_SU) ON DELETE SET NULL
380) ENGINE=INNODB;
381
382# (per la cache de frases fetes)
383CREATE TABLE MP3 (
384        ID_Mp3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
385        mp3TextSentence TEXT,
386        mp3TSMd5Encoded VARCHAR(32),
387        mp3Path VARCHAR(300)
388) ENGINE=INNODB;
389
390CREATE TABLE Content (
391        idString INT NOT NULL,
392        tagString VARCHAR(100) NOT NULL,
393        ID_CLanguage INT NOT NULL,
394        content TEXT,
395        section VARCHAR(20),
396        PRIMARY KEY (idString, tagString, ID_CLanguage),
397        INDEX CLanguage (ID_CLanguage),
398        FOREIGN KEY (ID_CLanguage) REFERENCES Languages (ID_Language) ON DELETE CASCADE
399) ENGINE=INNODB;
400
401
402#*****************
403# BLOC PREDICCIÓ
404#*****************
405
406# Opció per ressetejar les taules de stats del sistema de predicció
407CREATE TABLE P_StatsUserPicto (
408        ID_PSUPUser INT,
409        pictoid INT,
410        countx1 INT,
411        0h INT DEFAULT '0',
412        1h INT DEFAULT '0',
413        2h INT DEFAULT '0',
414        3h INT DEFAULT '0',
415        4h INT DEFAULT '0',
416        5h INT DEFAULT '0',
417        6h INT DEFAULT '0',
418        7h INT DEFAULT '0',
419        8h INT DEFAULT '0',
420        9h INT DEFAULT '0',
421        10h INT DEFAULT '0',
422        11h INT DEFAULT '0',
423        12h INT DEFAULT '0',
424        13h INT DEFAULT '0',
425        14h INT DEFAULT '0',
426        15h INT DEFAULT '0',
427        16h INT DEFAULT '0',
428        17h INT DEFAULT '0',
429        18h INT DEFAULT '0',
430        19h INT DEFAULT '0',
431        20h INT DEFAULT '0',
432        21h INT DEFAULT '0',
433        22h INT DEFAULT '0',
434        23h INT DEFAULT '0',
435        Mon INT DEFAULT '0',
436        Tue INT DEFAULT '0',
437        Wed INT DEFAULT '0',
438        Thu INT DEFAULT '0',
439        Fri INT DEFAULT '0',
440        Sat INT DEFAULT '0',
441        Sun INT DEFAULT '0',
442        lastdate DATE,
443        imgtemp VARCHAR(300) NULL,
444        PRIMARY KEY (ID_PSUPUser, pictoid),
445        INDEX PSUPUser (ID_PSUPUser),
446        FOREIGN KEY (ID_PSUPUser) REFERENCES User (ID_User) ON DELETE CASCADE,
447        INDEX PictoId (pictoid),
448        FOREIGN KEY (pictoid) REFERENCES Pictograms (pictoid) ON DELETE CASCADE
449) ENGINE=INNODB;
450
451CREATE TABLE P_StatsUserPictox2 (
452        ID_PSUP2User INT,
453        picto1id INT,
454        picto2id INT,
455        countx2 INT,
456        0h INT DEFAULT '0',
457        1h INT DEFAULT '0',
458        2h INT DEFAULT '0',
459        3h INT DEFAULT '0',
460        4h INT DEFAULT '0',
461        5h INT DEFAULT '0',
462        6h INT DEFAULT '0',
463        7h INT DEFAULT '0',
464        8h INT DEFAULT '0',
465        9h INT DEFAULT '0',
466        10h INT DEFAULT '0',
467        11h INT DEFAULT '0',
468        12h INT DEFAULT '0',
469        13h INT DEFAULT '0',
470        14h INT DEFAULT '0',
471        15h INT DEFAULT '0',
472        16h INT DEFAULT '0',
473        17h INT DEFAULT '0',
474        18h INT DEFAULT '0',
475        19h INT DEFAULT '0',
476        20h INT DEFAULT '0',
477        21h INT DEFAULT '0',
478        22h INT DEFAULT '0',
479        23h INT DEFAULT '0',
480        Mon INT DEFAULT '0',
481        Tue INT DEFAULT '0',
482        Wed INT DEFAULT '0',
483        Thu INT DEFAULT '0',
484        Fri INT DEFAULT '0',
485        Sat INT DEFAULT '0',
486        Sun INT DEFAULT '0',
487        lastdate DATE,
488        PRIMARY KEY (ID_PSUP2User, picto1id, picto2id),
489        INDEX PSUP2User (ID_PSUP2User),
490        FOREIGN KEY (ID_PSUP2User) REFERENCES User (ID_User) ON DELETE CASCADE,
491        INDEX PictoId1 (picto1id),
492        FOREIGN KEY (picto1id) REFERENCES Pictograms (pictoid) ON DELETE CASCADE,
493        INDEX PictoId2 (picto2id),
494        FOREIGN KEY (picto2id) REFERENCES Pictograms (pictoid) ON DELETE CASCADE
495) ENGINE=INNODB;
496
497CREATE TABLE P_StatsUserPictox3 (
498        ID_PSUP3User INT,
499        picto1id INT,
500        picto2id INT,
501        picto3id INT,
502        countx3 INT,
503        0h INT DEFAULT '0',
504        1h INT DEFAULT '0',
505        2h INT DEFAULT '0',
506        3h INT DEFAULT '0',
507        4h INT DEFAULT '0',
508        5h INT DEFAULT '0',
509        6h INT DEFAULT '0',
510        7h INT DEFAULT '0',
511        8h INT DEFAULT '0',
512        9h INT DEFAULT '0',
513        10h INT DEFAULT '0',
514        11h INT DEFAULT '0',
515        12h INT DEFAULT '0',
516        13h INT DEFAULT '0',
517        14h INT DEFAULT '0',
518        15h INT DEFAULT '0',
519        16h INT DEFAULT '0',
520        17h INT DEFAULT '0',
521        18h INT DEFAULT '0',
522        19h INT DEFAULT '0',
523        20h INT DEFAULT '0',
524        21h INT DEFAULT '0',
525        22h INT DEFAULT '0',
526        23h INT DEFAULT '0',
527        Mon INT DEFAULT '0',
528        Tue INT DEFAULT '0',
529        Wed INT DEFAULT '0',
530        Thu INT DEFAULT '0',
531        Fri INT DEFAULT '0',
532        Sat INT DEFAULT '0',
533        Sun INT DEFAULT '0',
534        lastdate DATE,
535        PRIMARY KEY (ID_PSUP3User, picto1id, picto2id, picto3id),
536        INDEX PSUP3User (ID_PSUP3User),
537        FOREIGN KEY (ID_PSUP3User) REFERENCES User (ID_User) ON DELETE CASCADE,
538        INDEX PictoId1 (picto1id),
539        FOREIGN KEY (picto1id) REFERENCES Pictograms (pictoid) ON DELETE CASCADE,
540        INDEX PictoId2 (picto2id),
541        FOREIGN KEY (picto2id) REFERENCES Pictograms (pictoid) ON DELETE CASCADE,
542        INDEX PictoId3 (picto3id),
543        FOREIGN KEY (picto3id) REFERENCES Pictograms (pictoid) ON DELETE CASCADE
544) ENGINE=INNODB;
545
546
547#*****************
548# BLOC EXPANSIÓ
549#*****************
550
551CREATE TABLE PictogramsLanguage (
552        pictoid INT,   
553        languageid INT,
554        insertdate DATE,
555        pictotext VARCHAR(200),
556        pictofreq FLOAT,
557        PRIMARY KEY (pictoid, languageid),
558        INDEX PictoId (pictoid),
559        FOREIGN KEY (pictoid) REFERENCES Pictograms (pictoid) ON DELETE CASCADE,
560        INDEX LanguageId (languageid),
561        FOREIGN KEY (languageid) REFERENCES Languages (ID_Language) ON DELETE CASCADE
562) ENGINE=INNODB;
563
564CREATE TABLE VerbCA (
565        verbid INT NOT NULL PRIMARY KEY,
566        verbtext VARCHAR(50),
567        actiu ENUM('0', '1'),
568        INDEX verbText (verbtext),
569        INDEX VerbId (verbid),
570        FOREIGN KEY (verbid) REFERENCES Pictograms (pictoid) ON DELETE CASCADE
571) ENGINE=INNODB;
572
573CREATE TABLE VerbConjugationCA (
574        verbid INT,
575        tense VARCHAR(50),
576        pers INT,
577        singpl ENUM('sing', 'pl'),
578        verbconj VARCHAR(50),
579        PRIMARY KEY (verbid, tense, pers, singpl),
580        INDEX VerbId (verbid),
581        FOREIGN KEY (verbid) REFERENCES VerbCA (verbid) ON DELETE CASCADE
582) ENGINE=INNODB;
583
584# Taula canviada de nom, de VerbPatternCA a PatternCA
585CREATE TABLE PatternCA (
586        patternid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
587        verbid INT,
588        pronominal ENUM('0', '1'),
589        pseudoimpersonal ENUM('0', '1'),
590        copulatiu ENUM('0', '1'),
591        tipusfrase ENUM('enunciativa', 'peticio', 'ordre', 'pregunta', 'resposta'),
592        defaulttense ENUM('present', 'perfet', 'perifrastic', 'futur', 'imperatiu', 'infinitiu', 'verbless'),
593        verbpeticio VARCHAR(50),
594        subj VARCHAR(20),
595        subjdef VARCHAR(30),
596        theme ENUM('1', '0', 'opt'),
597        themetipus VARCHAR(20) NULL,
598        themedef VARCHAR(30) NULL,
599        themeprep VARCHAR(15) NULL,
600        themeart ENUM('0', '1', 'sense') NULL,
601        receiver ENUM('1', '0', 'opt'),
602        receiverdef VARCHAR(30) NULL,
603        receiverprep VARCHAR(15) NULL,
604        benef ENUM('1', '0', 'opt'),
605        beneftipus VARCHAR(20) NULL,
606        benefdef VARCHAR(30) NULL,
607        benefprep VARCHAR(15) NULL,
608        acomp ENUM('1', '0', 'opt'),
609        acompdef VARCHAR(30) NULL,
610        acompprep VARCHAR(15) NULL,
611        tool ENUM('1', '0', 'opt'),
612        tooldef VARCHAR(30) NULL,
613        toolprep VARCHAR(15) NULL,
614        manera ENUM('1', '0', 'opt'),
615        maneradef VARCHAR(30) NULL,
616        maneratipus VARCHAR(20) NULL,
617        locto ENUM('1', '0', 'opt'),
618        loctotipus VARCHAR(20) NULL,
619        loctodef VARCHAR(30) NULL,
620        loctoprep VARCHAR(15) NULL,
621        locfrom ENUM('1', '0', 'opt'),
622        locfromtipus VARCHAR(20) NULL, 
623        locfromdef VARCHAR(30) NULL,
624        locfromprep VARCHAR(15) NULL,
625        locat ENUM('1', '0', 'opt'),
626        locatdef VARCHAR(30) NULL,
627        locatprep VARCHAR(15) NULL,
628        time ENUM('1', '0', 'opt'),
629        expressio VARCHAR(50) NULL,
630        subverb ENUM('0', '1'),
631        exemple TEXT,
632        INDEX VerbId (verbid),
633        FOREIGN KEY (verbid) REFERENCES VerbCA (verbid) ON DELETE SET NULL
634) ENGINE=INNODB;
635
636CREATE TABLE VerbPatternCA (
637        verbid INT,
638        patternid INT,
639        INDEX VerbId (verbid),
640        FOREIGN KEY (verbid) REFERENCES VerbCA (verbid) ON DELETE CASCADE,
641        INDEX PatternId (patternid),
642        FOREIGN KEY (patternid) REFERENCES PatternCA (patternid) ON DELETE CASCADE,
643        PRIMARY KEY (verbid, patternid)
644) ENGINE=INNODB;
645
646CREATE TABLE PatternOrdreCA (
647        patternid INT PRIMARY KEY,
648        subject INT DEFAULT '1000',
649        theme INT DEFAULT '1000',
650        receiver INT DEFAULT '1000',
651        benef INT DEFAULT '1000',
652        acomp INT DEFAULT '1000',
653        tool INT DEFAULT '1000',
654        manera INT DEFAULT '1000',
655        locto INT DEFAULT '1000',
656        locfrom INT DEFAULT '1000',
657        locat INT DEFAULT '1000',
658        time INT DEFAULT '1000',
659        INDEX PatternId (patternid),
660        FOREIGN KEY (patternid) REFERENCES VerbPatternCA (patternid) ON DELETE CASCADE
661) ENGINE=INNODB;
662
663CREATE TABLE NameCA (
664        nameid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
665        nomtext VARCHAR(50),
666        mf ENUM('masc', 'fem'),
667        singpl ENUM('sing', 'pl'),
668        contabincontab ENUM('contable', 'incontable'),
669        determinat ENUM('0', '1', 'sense'),
670        ispropernoun ENUM('0', '1') DEFAULT '0',
671        defaultverb INT DEFAULT '0',
672        plural VARCHAR(50) NULL,
673        femeni VARCHAR(50) NULL,
674        fempl VARCHAR(50) NULL,
675        INDEX NameId (nameid),
676        FOREIGN KEY (nameid) REFERENCES Pictograms (pictoid) ON DELETE CASCADE,
677        INDEX DefaultVerb (defaultverb),
678        FOREIGN KEY (defaultverb) REFERENCES VerbCA (verbid) ON DELETE CASCADE
679) ENGINE=INNODB;
680
681CREATE TABLE NameClassCA (
682        nameid INT,
683        class VARCHAR(20) DEFAULT 'noun',
684        INDEX NameId (nameid),
685        FOREIGN KEY (nameid) REFERENCES NameCA (nameid) ON DELETE CASCADE,
686        PRIMARY KEY (nameid, class)
687) ENGINE=INNODB;
688
689CREATE TABLE AdjectiveCA (
690        adjid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
691        masc VARCHAR(50),
692        fem VARCHAR(50),
693        mascpl VARCHAR(50),
694        fempl VARCHAR(50),
695        defaultverb INT DEFAULT '86',
696        subjdef VARCHAR(30) DEFAULT '1',
697        INDEX AjdId (adjid),
698        FOREIGN KEY (adjid) REFERENCES Pictograms (pictoid) ON DELETE CASCADE,
699        INDEX DefaultVerb (defaultverb),
700        FOREIGN KEY (defaultverb) REFERENCES VerbCA (verbid) ON DELETE CASCADE
701) ENGINE=INNODB;
702
703CREATE TABLE AdjClassCA (
704        adjid INT,
705        class VARCHAR(20) DEFAULT 'noun',
706        INDEX AdjId (adjid),
707        FOREIGN KEY (adjid) REFERENCES AdjectiveCA (adjid) ON DELETE CASCADE,
708        PRIMARY KEY (adjid, class)
709) ENGINE=INNODB;
710
711CREATE TABLE AdverbCA (
712        advid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
713        advtext VARCHAR(50),
714        INDEX AjvId (advid),
715        FOREIGN KEY (advid) REFERENCES Pictograms (pictoid) ON DELETE CASCADE
716) ENGINE=INNODB;
717
718CREATE TABLE AdvTypeCA (
719        advid INT,
720        type VARCHAR(20) DEFAULT 'manera',
721        INDEX AdvId (advid),
722        FOREIGN KEY (advid) REFERENCES AdverbCA (advid) ON DELETE CASCADE,
723        PRIMARY KEY (advid, type)
724) ENGINE=INNODB;
725
726CREATE TABLE ModifierCA (
727        modid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
728        masc VARCHAR(50),
729        fem VARCHAR(50) NULL,
730        mascpl VARCHAR(50) NULL,
731        fempl VARCHAR(50) NULL,
732        negatiu ENUM('0', '1'),
733        type VARCHAR(20) NULL,
734        scope VARCHAR(20) DEFAULT 'phrase',
735        INDEX ModId (modid),
736        FOREIGN KEY (modid) REFERENCES Pictograms (pictoid) ON DELETE CASCADE
737) ENGINE=INNODB;
738
739CREATE TABLE ExpressionsCA (
740        exprid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
741        exprtext VARCHAR(100),
742        negatiu ENUM('0', '1'),
743        front ENUM('0', '1'),
744        INDEX ExprId (exprid),
745        FOREIGN KEY (exprid) REFERENCES Pictograms (pictoid) ON DELETE CASCADE
746) ENGINE=INNODB;
747
748CREATE TABLE ExprTypeCA (
749        exprid INT,
750        type VARCHAR(20) DEFAULT 'complet',
751        INDEX ExprId (exprid),
752        FOREIGN KEY (exprid) REFERENCES ExpressionsCA (exprid) ON DELETE CASCADE,
753        PRIMARY KEY (exprid, type)
754) ENGINE=INNODB;
755
756CREATE TABLE QuestionPartCA (
757        questid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
758        parttext VARCHAR(100),
759        complement1 VARCHAR(20) DEFAULT 'theme',
760        complement2 VARCHAR(20) NULL,
761        INDEX QuestId (questid),
762        FOREIGN KEY (questid) REFERENCES Pictograms (pictoid) ON DELETE CASCADE
763) ENGINE=INNODB;
764
765CREATE TABLE VerbES (
766        verbid INT NOT NULL PRIMARY KEY,
767        verbtext VARCHAR(50),
768        actiu ENUM('0', '1'),
769        INDEX verbText (verbtext),
770        INDEX VerbId (verbid),
771        FOREIGN KEY (verbid) REFERENCES Pictograms (pictoid) ON DELETE CASCADE
772) ENGINE=INNODB;
773
774CREATE TABLE VerbConjugationES (
775        verbid INT,
776        tense VARCHAR(50),
777        pers INT,
778        singpl ENUM('sing', 'pl'),
779        verbconj VARCHAR(50),
780        PRIMARY KEY (verbid, tense, pers, singpl),
781        INDEX VerbId (verbid),
782        FOREIGN KEY (verbid) REFERENCES VerbES (verbid) ON DELETE CASCADE
783) ENGINE=INNODB;
784
785
786# Taula canviada de nom, de VerbPatternES a PatternES
787CREATE TABLE PatternES (
788        patternid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
789        verbid INT,
790        pronominal ENUM('0', '1'),
791        pseudoimpersonal ENUM('0', '1'),
792        copulatiu ENUM('0', '1'),
793        tipusfrase ENUM('enunciativa', 'peticio', 'ordre', 'pregunta', 'resposta'),
794        defaulttense ENUM('present', 'perfet', 'perifrastic', 'futur', 'imperatiu', 'infinitiu', 'verbless'),
795        verbpeticio VARCHAR(50),
796        subj VARCHAR(20),
797        subjdef VARCHAR(30),
798        theme ENUM('1', '0', 'opt'),
799        themetipus VARCHAR(20) NULL,
800        themedef VARCHAR(30) NULL,
801        themeprep VARCHAR(15) NULL,
802        themeart ENUM('0', '1', 'sense') NULL,
803        receiver ENUM('1', '0', 'opt'),
804        receiverdef VARCHAR(30) NULL,
805        receiverprep VARCHAR(15) NULL,
806        benef ENUM('1', '0', 'opt'),
807        beneftipus VARCHAR(20) NULL,
808        benefdef VARCHAR(30) NULL,
809        benefprep VARCHAR(15) NULL,
810        acomp ENUM('1', '0', 'opt'),
811        acompdef VARCHAR(30) NULL,
812        acompprep VARCHAR(15) NULL,
813        tool ENUM('1', '0', 'opt'),
814        tooldef VARCHAR(30) NULL,
815        toolprep VARCHAR(15) NULL,
816        manera ENUM('1', '0', 'opt'),
817        maneradef VARCHAR(30) NULL,
818        maneratipus VARCHAR(20) NULL,
819        locto ENUM('1', '0', 'opt'),
820        loctotipus VARCHAR(20) NULL,
821        loctodef VARCHAR(30) NULL,
822        loctoprep VARCHAR(15) NULL,
823        locfrom ENUM('1', '0', 'opt'),
824        locfromtipus VARCHAR(20) NULL, 
825        locfromdef VARCHAR(30) NULL,
826        locfromprep VARCHAR(15) NULL,
827        locat ENUM('1', '0', 'opt'),
828        locatdef VARCHAR(30) NULL,
829        locatprep VARCHAR(15) NULL,
830        time ENUM('1', '0', 'opt'),
831        expressio VARCHAR(50) NULL,
832        subverb ENUM('0', '1'),
833        exemple TEXT,
834        INDEX VerbId (verbid),
835        FOREIGN KEY (verbid) REFERENCES VerbES (verbid) ON DELETE SET NULL
836) ENGINE=INNODB;
837
838CREATE TABLE VerbPatternES (
839        verbid INT,
840        patternid INT,
841        INDEX VerbId (verbid),
842        FOREIGN KEY (verbid) REFERENCES VerbES (verbid) ON DELETE CASCADE,
843        INDEX PatternId (patternid),
844        FOREIGN KEY (patternid) REFERENCES PatternES (patternid) ON DELETE CASCADE,
845        PRIMARY KEY (verbid, patternid)
846) ENGINE=INNODB;
847
848CREATE TABLE PatternOrdreES (
849        patternid INT PRIMARY KEY,
850        subject INT DEFAULT '1000',
851        theme INT DEFAULT '1000',
852        receiver INT DEFAULT '1000',
853        benef INT DEFAULT '1000',
854        acomp INT DEFAULT '1000',
855        tool INT DEFAULT '1000',
856        manera INT DEFAULT '1000',
857        locto INT DEFAULT '1000',
858        locfrom INT DEFAULT '1000',
859        locat INT DEFAULT '1000',
860        time INT DEFAULT '1000',
861        INDEX PatternId (patternid),
862        FOREIGN KEY (patternid) REFERENCES VerbPatternES (patternid) ON DELETE CASCADE
863) ENGINE=INNODB;
864
865CREATE TABLE NameES (
866        nameid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
867        nomtext VARCHAR(50),
868        mf ENUM('masc', 'fem'),
869        singpl ENUM('sing', 'pl'),
870        contabincontab ENUM('contable', 'incontable'),
871        determinat ENUM('0', '1', 'sense'),
872        ispropernoun ENUM('0', '1') DEFAULT '0',
873        defaultverb INT DEFAULT '0',
874        plural VARCHAR(50) NULL,
875        femeni VARCHAR(50) NULL,
876        fempl VARCHAR(50) NULL,
877        INDEX NameId (nameid),
878        FOREIGN KEY (nameid) REFERENCES Pictograms (pictoid) ON DELETE CASCADE,
879        INDEX DefaultVerb (defaultverb),
880        FOREIGN KEY (defaultverb) REFERENCES VerbES (verbid) ON DELETE CASCADE
881) ENGINE=INNODB;
882
883CREATE TABLE NameClassES (
884        nameid INT,
885        class VARCHAR(20) DEFAULT 'noun',
886        INDEX NameId (nameid),
887        FOREIGN KEY (nameid) REFERENCES NameES (nameid) ON DELETE CASCADE,
888        PRIMARY KEY (nameid, class)
889) ENGINE=INNODB;
890
891CREATE TABLE AdjectiveES (
892        adjid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
893        masc VARCHAR(50),
894        fem VARCHAR(50),
895        mascpl VARCHAR(50),
896        fempl VARCHAR(50),
897        defaultverb INT DEFAULT '86',
898        subjdef VARCHAR(30),
899        INDEX AjdId (adjid),
900        FOREIGN KEY (adjid) REFERENCES Pictograms (pictoid) ON DELETE CASCADE,
901        INDEX DefaultVerb (defaultverb),
902        FOREIGN KEY (defaultverb) REFERENCES VerbES (verbid) ON DELETE CASCADE
903) ENGINE=INNODB;
904
905CREATE TABLE AdjClassES (
906        adjid INT,
907        class VARCHAR(20) DEFAULT 'noun',
908        INDEX AdjId (adjid),
909        FOREIGN KEY (adjid) REFERENCES AdjectiveES (adjid) ON DELETE CASCADE,
910        PRIMARY KEY (adjid, class)
911) ENGINE=INNODB;
912
913CREATE TABLE AdverbES (
914        advid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
915        advtext VARCHAR(50),
916        INDEX AjvId (advid),
917        FOREIGN KEY (advid) REFERENCES Pictograms (pictoid) ON DELETE CASCADE
918) ENGINE=INNODB;
919
920CREATE TABLE AdvTypeES (
921        advid INT,
922        type VARCHAR(20) DEFAULT 'manera',
923        INDEX AdvId (advid),
924        FOREIGN KEY (advid) REFERENCES AdverbES (advid) ON DELETE CASCADE,
925        PRIMARY KEY (advid, type)
926) ENGINE=INNODB;
927
928CREATE TABLE ModifierES (
929        modid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
930        masc VARCHAR(50),
931        fem VARCHAR(50) NULL,
932        mascpl VARCHAR(50) NULL,
933        fempl VARCHAR(50) NULL,
934        negatiu ENUM('0', '1'),
935        type VARCHAR(20) NULL,
936        scope VARCHAR(20) DEFAULT 'phrase',
937        INDEX ModId (modid),
938        FOREIGN KEY (modid) REFERENCES Pictograms (pictoid) ON DELETE CASCADE
939) ENGINE=INNODB;
940
941CREATE TABLE ExpressionsES (
942        exprid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
943        exprtext VARCHAR(100),
944        negatiu ENUM('0', '1'),
945        front ENUM('0', '1'),
946        INDEX ExprId (exprid),
947        FOREIGN KEY (exprid) REFERENCES Pictograms (pictoid) ON DELETE CASCADE
948) ENGINE=INNODB;
949
950CREATE TABLE ExprTypeES (
951        exprid INT,
952        type VARCHAR(20) DEFAULT 'complet',
953        INDEX ExprId (exprid),
954        FOREIGN KEY (exprid) REFERENCES ExpressionsES (exprid) ON DELETE CASCADE,
955        PRIMARY KEY (exprid, type)
956) ENGINE=INNODB;
957
958CREATE TABLE QuestionPartES (
959        questid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
960        parttext VARCHAR(100),
961        complement1 VARCHAR(20) DEFAULT 'theme',
962        complement2 VARCHAR(20) NULL,
963        INDEX QuestId (questid),
964        FOREIGN KEY (questid) REFERENCES Pictograms (pictoid) ON DELETE CASCADE
965) ENGINE=INNODB;
966
Note: See TracBrowser for help on using the repository browser.