/install/sql/yabs-1.0-derby.sql
SQL | 1668 lines | 1383 code | 285 blank | 0 comment | 0 complexity | 558ee7e4247e1702e9a0e2207e049509 MD5 | raw file
Possible License(s): LGPL-3.0, Apache-2.0, GPL-3.0, GPL-2.0, AGPL-3.0, JSON, BSD-3-Clause
1CREATE TABLE ACCOUNTS (
2 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
3 INTACCOUNTCLASS SMALLINT DEFAULT 0,
4 CNAME VARCHAR(250) NOT NULL,
5 DESCRIPTION VARCHAR(250) NOT NULL,
6 TAXVALUE DOUBLE NOT NULL DEFAULT 0,
7 DATEADDED DATE NOT NULL,
8 INTADDEDBY BIGINT(20) DEFAULT 0,
9 INTPARENTACCOUNT BIGINT(20) DEFAULT 0,
10 GROUPSIDS BIGINT(20) DEFAULT 1,
11 INVISIBLE SMALLINT DEFAULT 0,
12 INTACCOUNTTYPE SMALLINT NOT NULL,
13 INTPROFITFID SMALLINT NOT NULL,
14 INTTAXFID SMALLINT NOT NULL,
15 INTTAXUID SMALLINT NOT NULL,
16 FRAME VARCHAR(25) NOT NULL,
17 HIERARCHYPATH VARCHAR(250) DEFAULT NULL
18);
19
20CREATE TABLE ADDRESSES (
21 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
22 GROUPSIDS BIGINT(20) DEFAULT 1,
23 CONTACTSIDS BIGINT(20),
24 TITLE VARCHAR(250) DEFAULT NULL,
25 TAXNUMBER VARCHAR(250),
26 PRENAME VARCHAR(250) DEFAULT NULL,
27 CNAME VARCHAR(250) DEFAULT NULL,
28 STREET VARCHAR(250) DEFAULT NULL,
29 ZIP VARCHAR(50) DEFAULT NULL,
30 CITY VARCHAR(300) DEFAULT NULL,
31 COMPANY VARCHAR(250) DEFAULT NULL,
32 DEPARTMENT VARCHAR(250) DEFAULT NULL,
33 COUNTRY VARCHAR(50) DEFAULT NULL,
34 ISMALE SMALLINT DEFAULT 0,
35 INTADDEDBY BIGINT(20) DEFAULT 0,
36 DATEADDED DATE DEFAULT NULL,
37 INTTYPE SMALLINT DEFAULT 0,
38 RESERVE1 VARCHAR(500) DEFAULT NULL,
39 RESERVE2 VARCHAR(500) DEFAULT NULL
40);
41
42CREATE TABLE COMPS (
43 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
44 GROUPSIDS BIGINT(20) DEFAULT 1,
45 CNAME VARCHAR(250) DEFAULT NULL,
46 STATE VARCHAR(250) DEFAULT NULL,
47 PHONEPREFIX VARCHAR(250) DEFAULT NULL,
48 BUSINESS VARCHAR(250) DEFAULT NULL,
49 TAXADVISOR VARCHAR(250) DEFAULT NULL,
50 CITY VARCHAR(250) DEFAULT NULL,
51 TAXADVJOB VARCHAR(250) DEFAULT NULL,
52 STREET VARCHAR(250) DEFAULT NULL,
53 STB VARCHAR(250) DEFAULT NULL,
54 EMAIL VARCHAR(250) DEFAULT NULL,
55 NAME VARCHAR(250) DEFAULT NULL,
56 ZIPCODE VARCHAR(250) DEFAULT NULL,
57 PHONE VARCHAR(250) DEFAULT NULL,
58 FIRSTNAME VARCHAR(250) DEFAULT NULL,
59 TAXAUTHORITY VARCHAR(250) DEFAULT NULL,
60 TAXNUMBER VARCHAR(250) DEFAULT NULL,
61 TAXADVMANDANT VARCHAR(250) DEFAULT NULL,
62 DATEADDED DATE NOT NULL,
63 ISMALE SMALLINT DEFAULT 0,
64 ISENABLED SMALLINT DEFAULT 1,
65 INTADDEDBY BIGINT(20) DEFAULT 0,
66 INVISIBLE SMALLINT DEFAULT 0,
67 RESERVE1 VARCHAR(500) DEFAULT NULL,
68 RESERVE2 VARCHAR(500) DEFAULT NULL
69);
70
71CREATE TABLE CONTACTS (
72 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
73 CNUMBER VARCHAR(250) DEFAULT NULL,
74 TAXNUMBER VARCHAR(250),
75 TITLE VARCHAR(250) DEFAULT NULL,
76 GROUPSIDS BIGINT(20) DEFAULT 1,
77 COUNTRY VARCHAR(50) DEFAULT NULL,
78 PRENAME VARCHAR(250) DEFAULT NULL,
79 CNAME VARCHAR(250) DEFAULT NULL,
80 STREET VARCHAR(250) DEFAULT NULL,
81 ZIP VARCHAR(50) DEFAULT NULL,
82 CITY VARCHAR(300) DEFAULT NULL,
83 MAINPHONE VARCHAR(250) DEFAULT NULL,
84 FAX VARCHAR(250) DEFAULT NULL,
85 MOBILEPHONE VARCHAR(250) DEFAULT NULL,
86 WORKPHONE VARCHAR(250) DEFAULT NULL,
87 MAILADDRESS VARCHAR(350) DEFAULT NULL,
88 COMPANY VARCHAR(250) DEFAULT NULL,
89 DEPARTMENT VARCHAR(250) DEFAULT NULL,
90 WEBSITE VARCHAR(350) DEFAULT NULL,
91 NOTES VARCHAR(10000),
92 DATEADDED DATE NOT NULL,
93 ISACTIVE SMALLINT DEFAULT 0,
94 ISCUSTOMER SMALLINT DEFAULT 0,
95 ISMANUFACTURER SMALLINT DEFAULT 0,
96 ISSUPPLIER SMALLINT DEFAULT 0,
97 ISCOMPANY SMALLINT DEFAULT 0,
98 ISMALE SMALLINT DEFAULT 0,
99 ISENABLED SMALLINT DEFAULT 1,
100 INTADDEDBY BIGINT(20) DEFAULT 0,
101 INVISIBLE SMALLINT DEFAULT 0,
102 RESERVE1 VARCHAR(500) DEFAULT NULL,
103 RESERVE2 VARCHAR(500) DEFAULT NULL
104);
105
106CREATE TABLE COUNTRIES (
107 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
108 CNAME VARCHAR(250) NOT NULL,
109 ISO SMALLINT NOT NULL,
110 GROUPSIDS BIGINT(20) DEFAULT 1,
111 RESERVE1 VARCHAR(500) DEFAULT NULL,
112 RESERVE2 VARCHAR(500) DEFAULT NULL
113);
114
115CREATE TABLE EXPENSES (
116 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
117 GROUPSIDS BIGINT(20) DEFAULT 1,
118 CNUMBER VARCHAR(250) DEFAULT NULL,
119 CNAME VARCHAR(250) DEFAULT NULL,
120 ACCOUNTSIDS BIGINT(20),
121 NETVALUE DOUBLE DEFAULT 0,
122 TAXPERCENTVALUE DOUBLE DEFAULT 0,
123 BRUTVALUE DOUBLE DEFAULT 0,
124 DESCRIPTION VARCHAR(5000) DEFAULT NULL,
125 DATEADDED DATE NOT NULL,
126 INTADDEDBY BIGINT(20) DEFAULT 0,
127 INVISIBLE SMALLINT DEFAULT 0,
128 RESERVE1 VARCHAR(500) DEFAULT NULL,
129 RESERVE2 VARCHAR(500) DEFAULT NULL
130);
131
132CREATE TABLE FAVOURITES (
133 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
134 CNAME VARCHAR(250) NOT NULL,
135 USERSIDS BIGINT(20),
136 GROUPSIDS BIGINT(20) DEFAULT 1,
137 ITEMSIDS BIGINT(20) NOT NULL,
138 DATEADDED DATE NOT NULL,
139 INTADDEDBY BIGINT(20) DEFAULT 0,
140 INVISIBLE SMALLINT DEFAULT 0,
141 RESERVE1 VARCHAR(500) DEFAULT NULL,
142 RESERVE2 VARCHAR(500) DEFAULT NULL
143);
144
145CREATE TABLE FILES (
146 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
147 CNAME VARCHAR(25) NOT NULL,
148 GROUPSIDS BIGINT(20) DEFAULT 1,
149 DATEADDED DATE NOT NULL,
150 DATA BLOB(5242880) NOT NULL,
151 FILESIZE BIGINT(20) NOT NULL,
152 INTADDEDBY BIGINT(20) DEFAULT 0,
153 INVISIBLE SMALLINT DEFAULT 0
154);
155
156CREATE TABLE FILESTOCONTACTS (
157 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
158 CNAME VARCHAR(250) NOT NULL,
159 DESCRIPTION VARCHAR(550) DEFAULT NULL,
160 CONTACTSIDS BIGINT(20) NOT NULL,
161 FILENAME VARCHAR(25) NOT NULL,
162 INTSIZE BIGINT(20) DEFAULT 0,
163 MIMETYPE VARCHAR(25) DEFAULT NULL,
164 INTADDEDBY BIGINT(20) DEFAULT 0,
165 DATEADDED DATE NOT NULL,
166 GROUPSIDS BIGINT(20) DEFAULT 1
167);
168
169CREATE TABLE FILESTOITEMS (
170 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
171 CNAME VARCHAR(250) NOT NULL,
172 DESCRIPTION VARCHAR(550) DEFAULT NULL,
173 ITEMSIDS BIGINT(20) NOT NULL,
174 FILENAME VARCHAR(25) NOT NULL,
175 INTSIZE BIGINT(20) DEFAULT 0,
176 MIMETYPE VARCHAR(25) DEFAULT NULL,
177 INTADDEDBY BIGINT(20) DEFAULT 0,
178 DATEADDED DATE NOT NULL,
179 GROUPSIDS BIGINT(20) DEFAULT 1
180);
181
182CREATE TABLE FILESTOPRODUCTS (
183 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
184 CNAME VARCHAR(250) NOT NULL,
185 DESCRIPTION VARCHAR(550) DEFAULT NULL,
186 PRODUCTSIDS BIGINT(20) NOT NULL,
187 FILENAME VARCHAR(25) NOT NULL,
188 INTSIZE BIGINT(20) DEFAULT 0,
189 MIMETYPE VARCHAR(25) DEFAULT NULL,
190 INTADDEDBY BIGINT(20) DEFAULT 0,
191 DATEADDED DATE NOT NULL,
192 GROUPSIDS BIGINT(20) DEFAULT 1
193);
194
195CREATE TABLE FORMATSTOUSERS (
196 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
197 CNAME VARCHAR(250) NOT NULL,
198 USERSIDS BIGINT(20),
199 INTTYPE SMALLINT DEFAULT 0
200);
201
202CREATE TABLE GLOBALSETTINGS (
203 IDS BIGINT(20) DEFAULT 1,
204 CNAME VARCHAR(250) NOT NULL,
205 GROUPSIDS BIGINT(20) DEFAULT 1,
206 VALUE VARCHAR(250) NOT NULL
207 );
208
209CREATE TABLE GROUPS (
210 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
211 CNAME VARCHAR(250) NOT NULL,
212 DESCRIPTION VARCHAR(750) DEFAULT NULL,
213 DEFAULTS VARCHAR(250) DEFAULT NULL,
214 GROUPSIDS BIGINT(20) DEFAULT 0,
215 DATEADDED DATE NOT NULL,
216 RESERVE1 VARCHAR(500) DEFAULT NULL,
217 INTADDEDBY BIGINT(20) DEFAULT 0,
218 HIERARCHYPATH VARCHAR(500) DEFAULT NULL,
219 RESERVE2 VARCHAR(500) DEFAULT NULL
220);
221
222CREATE TABLE HISTORY (
223 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
224 CNAME VARCHAR(250) NOT NULL,
225 USERNAME VARCHAR(250) NOT NULL,
226 DBIDENTITY VARCHAR(25) NOT NULL,
227 INTITEM SMALLINT NOT NULL,
228 GROUPSIDS BIGINT(20) DEFAULT 0,
229 DATEADDED DATE NOT NULL,
230 INTADDEDBY BIGINT(20) DEFAULT 0,
231 RESERVE1 VARCHAR(500) DEFAULT NULL,
232 RESERVE2 VARCHAR(500) DEFAULT NULL
233);
234
235CREATE TABLE ITEMS (
236 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
237 CNAME VARCHAR(250) NOT NULL,
238 CNUMBER VARCHAR(250) NOT NULL,
239 DESCRIPTION VARCHAR(2500) DEFAULT NULL,
240 GROUPSIDS BIGINT(20) DEFAULT 1,
241 ACCOUNTSIDS BIGINT(20) DEFAULT 1,
242 CONTACTSIDS BIGINT(20),
243 NETVALUE DOUBLE DEFAULT 0,
244 TAXVALUE DOUBLE DEFAULT 0,
245 DISCOUNTVALUE DOUBLE DEFAULT 0,
246 SHIPPINGVALUE DOUBLE DEFAULT 0,
247 DATETODO DATE DEFAULT NULL,
248 DATEEND DATE DEFAULT NULL,
249 INTREMINDERS INTEGER DEFAULT 0,
250 INTTYPE SMALLINT DEFAULT 0,
251 DATEADDED DATE NOT NULL,
252 INTADDEDBY BIGINT(20) DEFAULT 0,
253 INVISIBLE SMALLINT DEFAULT 0,
254 INTSTATUS SMALLINT DEFAULT 0,
255 HIERARCHYPATH VARCHAR(500) DEFAULT NULL,
256 RESERVE1 VARCHAR(500) DEFAULT NULL,
257 RESERVE2 VARCHAR(500) DEFAULT NULL
258);
259
260CREATE TABLE ITEMSTOACCOUNTS (
261 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
262 ITEMSIDS BIGINT(20) NOT NULL,
263 ACCOUNTSIDS BIGINT(20),
264 RESERVE1 VARCHAR(500) DEFAULT NULL,
265 RESERVE2 VARCHAR(500) DEFAULT NULL
266);
267
268CREATE TABLE LANGUAGES (
269 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
270 CNAME VARCHAR(250) NOT NULL,
271 GROUPSIDS BIGINT(20) DEFAULT 1,
272 LONGNAME VARCHAR(250) NOT NULL,
273 FILENAME VARCHAR(25) NOT NULL,
274 DATEADDED DATE NOT NULL,
275 INTADDEDBY BIGINT(20) DEFAULT 0,
276 INVISIBLE SMALLINT DEFAULT 0,
277 RESERVE1 VARCHAR(500) DEFAULT NULL,
278 RESERVE2 VARCHAR(500) DEFAULT NULL
279);
280
281CREATE TABLE MAILS (
282 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
283 CNAME VARCHAR(1000) NOT NULL,
284 DATEADDED DATE NOT NULL,
285 INTADDEDBY BIGINT(20) DEFAULT 0,
286 GROUPSIDS BIGINT(20) DEFAULT 1,
287 INVISIBLE SMALLINT DEFAULT 0,
288 USERSIDS BIGINT(20),
289 DESCRIPTION VARCHAR(5000) NOT NULL
290);
291
292CREATE TABLE PLUGINS (
293 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
294 CNAME VARCHAR(250) NOT NULL,
295 DESCRIPTION VARCHAR(550) DEFAULT NULL,
296 FILENAME VARCHAR(25) NOT NULL,
297 INTADDEDBY BIGINT(20) DEFAULT 0,
298 DATEADDED DATE NOT NULL,
299 GROUPSIDS BIGINT(20) DEFAULT 1
300);
301
302CREATE TABLE PLUGINSTOUSERS (
303 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
304 CNAME VARCHAR(250) NOT NULL,
305 USERSIDS BIGINT(20) NOT NULL,
306 PLUGINSIDS BIGINT(20) NOT NULL,
307 INTADDEDBY BIGINT(20) DEFAULT 0,
308 DATEADDED DATE NOT NULL,
309 GROUPSIDS BIGINT(20) DEFAULT 1
310);
311
312CREATE TABLE PRODUCTGROUPS (
313 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
314 CNAME VARCHAR(250) NOT NULL,
315 DESCRIPTION VARCHAR(750) DEFAULT NULL,
316 DEFAULTS VARCHAR(250) DEFAULT NULL,
317 GROUPSIDS BIGINT(20) DEFAULT 0,
318 PRODUCTGROUPSIDS BIGINT(20) DEFAULT 0,
319 DATEADDED DATE NOT NULL,
320 HIERARCHYPATH VARCHAR(500) DEFAULT NULL,
321 RESERVE1 VARCHAR(500) DEFAULT NULL,
322 INTADDEDBY BIGINT(20) DEFAULT 0,
323 RESERVE2 VARCHAR(500) DEFAULT NULL
324);
325
326CREATE TABLE PRODUCTLISTITEMS (
327 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
328 CNAME VARCHAR(2500) DEFAULT NULL,
329 LINKURL VARCHAR(250) DEFAULT NULL,
330 GROUPSIDS BIGINT(20) DEFAULT 1,
331 PRODUCTLISTSIDS BIGINT(20),
332 ORIGINALPRODUCTSIDS BIGINT(20),
333 COUNTVALUE DOUBLE NOT NULL DEFAULT 0,
334 QUANTITYVALUE DOUBLE NOT NULL DEFAULT 0,
335 MEASURE VARCHAR(250) NOT NULL,
336 DESCRIPTION VARCHAR(1000) DEFAULT NULL,
337 INTERNALVALUE DOUBLE DEFAULT 0,
338 TOTALNETVALUE DOUBLE DEFAULT 0,
339 TOTALBRUTVALUE DOUBLE DEFAULT 0,
340 EXTERNALVALUE DOUBLE DEFAULT 0,
341 TAXPERCENTVALUE DOUBLE NOT NULL DEFAULT 0,
342 DATEADDED DATE NOT NULL,
343 INTADDEDBY BIGINT(20) DEFAULT 0,
344 INVISIBLE SMALLINT DEFAULT 0,
345 RESERVE1 VARCHAR(500) DEFAULT NULL,
346 RESERVE2 VARCHAR(500) DEFAULT NULL
347);
348
349CREATE TABLE PRODUCTLISTS (
350 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
351 CNAME VARCHAR(2500) DEFAULT NULL,
352 GROUPSIDS BIGINT(20) DEFAULT 1,
353 DESCRIPTION VARCHAR(1000) DEFAULT NULL,
354 DATEADDED DATE NOT NULL,
355 INTADDEDBY BIGINT(20) DEFAULT 0,
356 INVISIBLE SMALLINT DEFAULT 0,
357 RESERVE1 VARCHAR(500) DEFAULT NULL,
358 RESERVE2 VARCHAR(500) DEFAULT NULL
359);
360
361CREATE TABLE PRODUCTS (
362 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
363 CNAME VARCHAR(500) NOT NULL,
364 CNUMBER VARCHAR(250) NOT NULL,
365 DESCRIPTION VARCHAR(5000),
366 EXTERNALNETVALUE DOUBLE DEFAULT 0,
367 INTERNALNETVALUE DOUBLE DEFAULT 0,
368 MEASURE VARCHAR(250) NOT NULL,
369 TAXIDS BIGINT(20),
370 MANUFACTURERSIDS BIGINT(20) DEFAULT 0,
371 SUPPLIERSIDS BIGINT(20) DEFAULT 0,
372 GROUPSIDS BIGINT(20) DEFAULT 1,
373 PRODUCTGROUPSIDS BIGINT(20) DEFAULT 1,
374 URL VARCHAR(250) DEFAULT NULL,
375 EAN VARCHAR(25) DEFAULT NULL,
376 REFERENCE VARCHAR(50) DEFAULT NULL,
377 DATEADDED DATE NOT NULL,
378 INTADDEDBY BIGINT(20) DEFAULT 0,
379 INVISIBLE SMALLINT DEFAULT 0,
380 INTTYPE SMALLINT NOT NULL,
381 DEFAULTIMAGE VARCHAR(30) DEFAULT NULL,
382 RESERVE1 VARCHAR(500) DEFAULT NULL,
383 RESERVE2 VARCHAR(500) DEFAULT NULL
384);
385
386CREATE TABLE REMINDERS (
387 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
388 CNAME VARCHAR(250) NOT NULL,
389 DESCRIPTION VARCHAR(550) DEFAULT NULL,
390 STAGESIDS BIGINT(20) NOT NULL,
391 ITEMSIDS BIGINT(20) NOT NULL,
392 EXTRAVALUE DOUBLE DEFAULT 0,
393 INTADDEDBY BIGINT(20) DEFAULT 0,
394 DATEADDED DATE NOT NULL,
395 GROUPSIDS BIGINT(20) DEFAULT 1
396);
397
398CREATE TABLE REVENUES (
399 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
400 GROUPSIDS BIGINT(20) DEFAULT 1,
401 CNUMBER VARCHAR(250) DEFAULT NULL,
402 CNAME VARCHAR(250) DEFAULT NULL,
403 ACCOUNTSIDS BIGINT(20),
404 NETVALUE DOUBLE DEFAULT 0,
405 TAXPERCENTVALUE DOUBLE DEFAULT 0,
406 BRUTVALUE DOUBLE DEFAULT 0,
407 DESCRIPTION VARCHAR(5000) DEFAULT NULL,
408 DATEADDED DATE NOT NULL,
409 INTADDEDBY BIGINT(20) DEFAULT 0,
410 INVISIBLE SMALLINT DEFAULT 0,
411 RESERVE1 VARCHAR(500) DEFAULT NULL,
412 RESERVE2 VARCHAR(500) DEFAULT NULL
413);
414
415CREATE TABLE SCHEDULE (
416 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
417 CNAME VARCHAR(250) NOT NULL,
418 GROUPSIDS BIGINT(20) DEFAULT 1,
419 USERSIDS BIGINT(20),
420 ITEMSIDS BIGINT(20),
421 STOPDATE DATE NOT NULL,
422 STARTDATE DATE NOT NULL,
423 NEXTDATE DATE NOT NULL,
424 ISDONE SMALLINT DEFAULT 0,
425 INTERVALMONTH SMALLINT NOT NULL,
426 DATEADDED DATE NOT NULL,
427 INTADDEDBY BIGINT(20) DEFAULT 0,
428 INVISIBLE SMALLINT DEFAULT 0,
429 RESERVE1 VARCHAR(500) DEFAULT NULL,
430 RESERVE2 VARCHAR(500) DEFAULT NULL
431);
432
433CREATE TABLE SEARCHINDEX (
434 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
435 DBIDENTITY VARCHAR(25) NOT NULL,
436 GROUPSIDS BIGINT(20),
437 ROWID BIGINT(20) NOT NULL,
438 TEXT VARCHAR(5000) DEFAULT NULL
439 );
440
441CREATE TABLE STAGES (
442 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
443 CNAME VARCHAR(250) NOT NULL,
444 DESCRIPTION VARCHAR(550) DEFAULT NULL,
445 EXTRAVALUE DOUBLE DEFAULT 0,
446 INTADDEDBY BIGINT(20) DEFAULT 0,
447 DATEADDED DATE NOT NULL,
448 GROUPSIDS BIGINT(20) DEFAULT 1
449);
450
451CREATE TABLE SUBITEMS (
452 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
453 CNAME VARCHAR(5000) DEFAULT NULL,
454 ITEMSIDS BIGINT(20),
455 GROUPSIDS BIGINT(20) DEFAULT 1,
456 ORIGINALPRODUCTSIDS BIGINT(20) DEFAULT NULL,
457 COUNTVALUE DOUBLE NOT NULL DEFAULT 0,
458 QUANTITYVALUE DOUBLE NOT NULL DEFAULT 0,
459 MEASURE VARCHAR(250) NOT NULL,
460 LINKURL VARCHAR(1000) DEFAULT NULL,
461 DESCRIPTION VARCHAR(1000) DEFAULT NULL,
462 INTERNALVALUE DOUBLE DEFAULT 0,
463 TOTALNETVALUE DOUBLE DEFAULT 0,
464 TOTALBRUTVALUE DOUBLE DEFAULT 0,
465 EXTERNALVALUE DOUBLE DEFAULT 0,
466 TAXPERCENTVALUE DOUBLE NOT NULL DEFAULT 0,
467 DATEDELIVERY DATE DEFAULT NULL,
468 DATEADDED DATE NOT NULL,
469 INTADDEDBY BIGINT(20) DEFAULT 0,
470 INVISIBLE SMALLINT DEFAULT 0,
471 RESERVE1 VARCHAR(500) DEFAULT NULL,
472 RESERVE2 VARCHAR(500) DEFAULT NULL
473);
474
475CREATE TABLE TABLELOCK (
476 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
477 CNAME VARCHAR(250),
478 ROWID BIGINT(20) NOT NULL,
479 USERSIDS BIGINT(20),
480 RESERVE1 VARCHAR(500) DEFAULT NULL,
481 RESERVE2 VARCHAR(500) DEFAULT NULL
482);
483
484CREATE TABLE TAX (
485 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
486 CNAME VARCHAR(250),
487 TAXVALUE DOUBLE DEFAULT 0,
488 IDENTIFIER VARCHAR(250) DEFAULT NULL,
489 GROUPSIDS BIGINT(20) DEFAULT 1,
490 COUNTRY VARCHAR(50) DEFAULT NULL,
491 DATEADDED DATE NOT NULL,
492 INTADDEDBY BIGINT(20) DEFAULT 0,
493 INVISIBLE SMALLINT DEFAULT 0,
494 RESERVE1 VARCHAR(500) DEFAULT NULL,
495 RESERVE2 VARCHAR(500) DEFAULT NULL
496);
497
498CREATE TABLE TEMPLATES (
499 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
500 CNAME VARCHAR(250) NOT NULL,
501 FORMAT VARCHAR(55) DEFAULT NULL,
502 DESCRIPTION VARCHAR(550) DEFAULT NULL,
503 INTSIZE BIGINT(20) DEFAULT 0,
504 MIMETYPE VARCHAR(25) DEFAULT NULL,
505 FILENAME VARCHAR(25) NOT NULL,
506 INTADDEDBY BIGINT(20) DEFAULT 0,
507 DATEADDED DATE NOT NULL,
508 GROUPSIDS BIGINT(20) DEFAULT 1
509);
510
511CREATE TABLE TEMPLATESTOUSERS (
512 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
513 CNAME VARCHAR(250) NOT NULL,
514 USERSIDS BIGINT(20),
515 TEMPLATESIDS BIGINT(20) NOT NULL,
516 GROUPSIDS BIGINT(20) DEFAULT 1
517);
518
519CREATE TABLE TRASHBIN (
520 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
521 CNAME VARCHAR(500),
522 ROWID BIGINT(20) NOT NULL,
523 DESCRIPTION VARCHAR(2500),
524 DELETEME SMALLINT DEFAULT 1,
525 RESERVE1 VARCHAR(500) DEFAULT NULL,
526 RESERVE2 VARCHAR(500) DEFAULT NULL
527);
528
529CREATE TABLE USERPROPERTIES (
530 IDS BIGINT(20) DEFAULT 1,
531 CNAME VARCHAR(250) NOT NULL,
532 VALUE VARCHAR(250) NOT NULL,
533 USERSIDS BIGINT(20) NOT NULL,
534 DATEADDED DATE NOT NULL,
535 INTADDEDBY BIGINT(20) DEFAULT 0,
536 GROUPSIDS BIGINT(20) DEFAULT 1,
537 INVISIBLE SMALLINT DEFAULT 0
538 );
539
540CREATE TABLE USERS (
541 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
542 CNAME VARCHAR(250) NOT NULL,
543 GROUPSIDS BIGINT(20) DEFAULT 1,
544 INTDEFAULTACCOUNT BIGINT(20) DEFAULT 1,
545 COMPSIDS BIGINT(20) DEFAULT 0,
546 INTDEFAULTSTATUS BIGINT(20) DEFAULT 1,
547 FULLNAME VARCHAR(250) NOT NULL,
548 PASSWORD VARCHAR(250) NOT NULL,
549 LAF VARCHAR(50) DEFAULT NULL,
550 LOCALE VARCHAR(50) DEFAULT NULL,
551 DEFCOUNTRY VARCHAR(50) DEFAULT NULL,
552 MAIL VARCHAR(50) DEFAULT NULL,
553 LANGUAGE VARCHAR(50) DEFAULT NULL,
554 INTHIGHESTRIGHT SMALLINT DEFAULT 3,
555 ISENABLED SMALLINT DEFAULT 1,
556 ISRGROUPED SMALLINT DEFAULT 0,
557 ISLOGGEDIN SMALLINT DEFAULT 0,
558 DATELASTLOG DATE DEFAULT NULL,
559 DATEADDED DATE NOT NULL,
560 INTADDEDBY BIGINT(20) DEFAULT 0,
561 INVISIBLE SMALLINT DEFAULT 0,
562 RESERVE1 VARCHAR(500) DEFAULT NULL,
563 RESERVE2 VARCHAR(500) DEFAULT NULL
564);
565
566CREATE TABLE WEBSHOPS (
567 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
568 CNAME VARCHAR(250) NOT NULL,
569 DESCRIPTION VARCHAR(750) DEFAULT NULL,
570 GROUPSIDS BIGINT(20) DEFAULT 0,
571 DATEADDED DATE NOT NULL,
572 ISREQUESTCOMPRESSION SMALLINT DEFAULT 0,
573 ISAUTHENTICATED SMALLINT DEFAULT 0,
574 USERNAME VARCHAR(50) DEFAULT NULL,
575 PASSWORD VARCHAR(50) DEFAULT NULL,
576 RESERVE1 VARCHAR(500) DEFAULT NULL,
577 INTADDEDBY BIGINT(20) DEFAULT 0,
578 INTERVAL BIGINT(20) DEFAULT 0,
579 URL VARCHAR(500) DEFAULT NULL,
580 RESERVE2 VARCHAR(500) DEFAULT NULL
581);
582
583CREATE TABLE WSCONTACTSMAPPING (
584 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
585 WEBSHOPSIDS BIGINT(20),
586 CNAME VARCHAR(250) NOT NULL,
587 GROUPSIDS BIGINT(20),
588 CONTACTSIDS BIGINT(20),
589 WSCONTACT VARCHAR(250) NOT NULL,
590 DATEADDED DATE DEFAULT NULL,
591 INTADDEDBY BIGINT(20) DEFAULT 0,
592 INVISIBLE SMALLINT DEFAULT 0,
593 RESERVE1 VARCHAR(500) DEFAULT NULL,
594 RESERVE2 VARCHAR(500) DEFAULT NULL
595);
596
597CREATE TABLE WSITEMSMAPPING (
598 IDS BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY auto_increment,
599 WEBSHOPSIDS BIGINT(20),
600 CNAME VARCHAR(250) NOT NULL,
601 GROUPSIDS BIGINT(20),
602 ITEMSIDS BIGINT(20),
603 WSITEM VARCHAR(250) NOT NULL,
604 DATEADDED DATE DEFAULT NULL,
605 INTADDEDBY BIGINT(20) DEFAULT 0,
606 INVISIBLE SMALLINT DEFAULT 0,
607 RESERVE1 VARCHAR(500) DEFAULT NULL,
608 RESERVE2 VARCHAR(500) DEFAULT NULL
609);
610
611CREATE UNIQUE INDEX SQL091006084310810
612 ON GROUPS(CNAME);
613
614CREATE UNIQUE INDEX SQL091006084310820
615 ON GROUPS(IDS);
616
617CREATE UNIQUE INDEX SQL091006084311391
618 ON PRODUCTGROUPS(CNAME, GROUPSIDS);
619
620CREATE UNIQUE INDEX SQL091006084311390
621 ON PRODUCTGROUPS(IDS);
622
623CREATE UNIQUE INDEX SQL091006084311800
624 ON HISTORY(IDS);
625
626CREATE UNIQUE INDEX SQL091006084312110
627 ON COUNTRIES(ISO);
628
629CREATE UNIQUE INDEX SQL091006084312111
630 ON COUNTRIES(IDS);
631
632CREATE UNIQUE INDEX SQL091006084312620
633 ON GLOBALSETTINGS(CNAME, GROUPSIDS);
634
635CREATE UNIQUE INDEX SQL091006084312900
636 ON TAX(IDS);
637
638CREATE INDEX SQL091006084312890
639 ON TAX(GROUPSIDS);
640
641CREATE UNIQUE INDEX SQL091006084313452
642 ON CONTACTS(CNUMBER, GROUPSIDS);
643
644CREATE UNIQUE INDEX SQL091006084313451
645 ON CONTACTS(IDS);
646
647CREATE INDEX SQL091006084313450
648 ON CONTACTS(GROUPSIDS);
649
650CREATE UNIQUE INDEX SQL091006084313971
651 ON USERS(CNAME, GROUPSIDS);
652
653CREATE UNIQUE INDEX SQL091006084313970
654 ON USERS(IDS);
655
656CREATE INDEX SQL091006084313960
657 ON USERS(GROUPSIDS);
658
659CREATE UNIQUE INDEX SQL091006084314570
660 ON FILES(CNAME);
661
662CREATE UNIQUE INDEX SQL091006084314572
663 ON FILES(IDS);
664
665CREATE INDEX SQL091006084314571
666 ON FILES(GROUPSIDS);
667
668CREATE UNIQUE INDEX SQL091006084315174
669 ON LANGUAGES(CNAME, GROUPSIDS);
670
671CREATE UNIQUE INDEX SQL091006084315171
672 ON LANGUAGES(LONGNAME);
673
674CREATE UNIQUE INDEX SQL091006084315173
675 ON LANGUAGES(IDS);
676
677CREATE INDEX SQL091006084315170
678 ON LANGUAGES(GROUPSIDS);
679
680CREATE INDEX SQL091006084315172
681 ON LANGUAGES(FILENAME);
682
683CREATE UNIQUE INDEX SQL091006084315882
684 ON FAVOURITES(IDS);
685
686CREATE INDEX SQL091006084315880
687 ON FAVOURITES(USERSIDS);
688
689CREATE INDEX SQL091006084315881
690 ON FAVOURITES(GROUPSIDS);
691
692CREATE UNIQUE INDEX SQL091006084316451
693 ON ACCOUNTS(IDS);
694
695CREATE INDEX SQL091006084316450
696 ON ACCOUNTS(GROUPSIDS);
697
698CREATE UNIQUE INDEX SQL091006084316862
699 ON ITEMS(CNUMBER, INTTYPE);
700
701CREATE UNIQUE INDEX SQL091006084316861
702 ON ITEMS(IDS);
703
704CREATE INDEX SQL091006084316850
705 ON ITEMS(GROUPSIDS);
706
707CREATE INDEX SQL091006084316851
708 ON ITEMS(ACCOUNTSIDS);
709
710CREATE INDEX SQL091006084316860
711 ON ITEMS(CONTACTSIDS);
712
713CREATE UNIQUE INDEX SQL091006084317902
714 ON SUBITEMS(IDS);
715
716CREATE INDEX SQL091006084317900
717 ON SUBITEMS(ITEMSIDS);
718
719CREATE INDEX SQL091006084317901
720 ON SUBITEMS(GROUPSIDS);
721
722CREATE UNIQUE INDEX SQL091006084318483
723 ON SCHEDULE(IDS);
724
725CREATE INDEX SQL091006084318480
726 ON SCHEDULE(GROUPSIDS);
727
728CREATE INDEX SQL091006084318481
729 ON SCHEDULE(USERSIDS);
730
731CREATE INDEX SQL091006084318482
732 ON SCHEDULE(ITEMSIDS);
733
734CREATE UNIQUE INDEX SQL091006084319621
735 ON PRODUCTS(CNUMBER, GROUPSIDS);
736
737CREATE UNIQUE INDEX SQL091006084319620
738 ON PRODUCTS(IDS);
739
740CREATE INDEX SQL091006084319610
741 ON PRODUCTS(TAXIDS);
742
743CREATE INDEX SQL091006084319611
744 ON PRODUCTS(GROUPSIDS);
745
746CREATE INDEX SQL091006084319612
747 ON PRODUCTS(PRODUCTGROUPSIDS);
748
749CREATE UNIQUE INDEX SQL091006084320591
750 ON PRODUCTLISTS(IDS);
751
752CREATE INDEX SQL091006084320590
753 ON PRODUCTLISTS(GROUPSIDS);
754
755CREATE UNIQUE INDEX SQL091006084321141
756 ON PRODUCTLISTITEMS(IDS);
757
758CREATE INDEX SQL091006084321130
759 ON PRODUCTLISTITEMS(GROUPSIDS);
760
761CREATE INDEX SQL091006084321131
762 ON PRODUCTLISTITEMS(PRODUCTLISTSIDS);
763
764CREATE INDEX SQL091006084321140
765 ON PRODUCTLISTITEMS(ORIGINALPRODUCTSIDS);
766
767CREATE INDEX SQL091006084322190
768 ON USERPROPERTIES(GROUPSIDS);
769
770CREATE UNIQUE INDEX SQL091006084322552
771 ON MAILS(IDS);
772
773CREATE INDEX SQL091006084322550
774 ON MAILS(GROUPSIDS);
775
776CREATE INDEX SQL091006084322551
777 ON MAILS(USERSIDS);
778
779CREATE UNIQUE INDEX SQL091006084323381
780 ON COMPS(IDS);
781
782CREATE INDEX SQL091006084323380
783 ON COMPS(GROUPSIDS);
784
785CREATE UNIQUE INDEX SQL091006084324160
786 ON EXPENSES(IDS);
787
788CREATE INDEX SQL091006084324140
789 ON EXPENSES(GROUPSIDS);
790
791CREATE INDEX SQL091006084324141
792 ON EXPENSES(ACCOUNTSIDS);
793
794CREATE UNIQUE INDEX SQL091006084324810
795 ON REVENUES(IDS);
796
797CREATE INDEX SQL091006084324790
798 ON REVENUES(GROUPSIDS);
799
800CREATE INDEX SQL091006084324800
801 ON REVENUES(ACCOUNTSIDS);
802
803CREATE UNIQUE INDEX SQL091006084325551
804 ON WEBSHOPS(CNAME, GROUPSIDS);
805
806CREATE UNIQUE INDEX SQL091006084325550
807 ON WEBSHOPS(IDS);
808
809CREATE UNIQUE INDEX SQL091006084326054
810 ON WSCONTACTSMAPPING(WSCONTACT, GROUPSIDS, WEBSHOPSIDS);
811
812CREATE UNIQUE INDEX SQL091006084326053
813 ON WSCONTACTSMAPPING(IDS);
814
815CREATE INDEX SQL091006084326050
816 ON WSCONTACTSMAPPING(WEBSHOPSIDS);
817
818CREATE INDEX SQL091006084326051
819 ON WSCONTACTSMAPPING(GROUPSIDS);
820
821CREATE INDEX SQL091006084326052
822 ON WSCONTACTSMAPPING(CONTACTSIDS);
823
824CREATE UNIQUE INDEX SQL091006084326881
825 ON WSITEMSMAPPING(WSITEM, GROUPSIDS, WEBSHOPSIDS);
826
827CREATE UNIQUE INDEX SQL091006084326880
828 ON WSITEMSMAPPING(IDS);
829
830CREATE INDEX SQL091006084326870
831 ON WSITEMSMAPPING(WEBSHOPSIDS);
832
833CREATE INDEX SQL091006084326871
834 ON WSITEMSMAPPING(GROUPSIDS);
835
836CREATE INDEX SQL091006084326872
837 ON WSITEMSMAPPING(ITEMSIDS);
838
839CREATE UNIQUE INDEX SQL091006084327910
840 ON TABLELOCK(IDS);
841
842CREATE UNIQUE INDEX SQL091006084327911
843 ON TABLELOCK(CNAME, ROWID);
844
845CREATE INDEX SQL091006084327890
846 ON TABLELOCK(USERSIDS);
847
848CREATE UNIQUE INDEX SQL091006084328480
849 ON ITEMSTOACCOUNTS(IDS);
850
851CREATE INDEX SQL091006084328460
852 ON ITEMSTOACCOUNTS(ITEMSIDS);
853
854CREATE INDEX SQL091006084328461
855 ON ITEMSTOACCOUNTS(ACCOUNTSIDS);
856
857CREATE UNIQUE INDEX SQL091006084329120
858 ON TRASHBIN(IDS);
859
860CREATE UNIQUE INDEX SQL091006084329671
861 ON FILESTOCONTACTS(IDS);
862
863CREATE INDEX SQL091006084329650
864 ON FILESTOCONTACTS(CONTACTSIDS);
865
866CREATE INDEX SQL091006084329651
867 ON FILESTOCONTACTS(FILENAME);
868
869CREATE INDEX SQL091006084329670
870 ON FILESTOCONTACTS(GROUPSIDS);
871
872CREATE UNIQUE INDEX SQL091006084330492
873 ON TEMPLATES(IDS);
874
875CREATE INDEX SQL091006084330490
876 ON TEMPLATES(FILENAME);
877
878CREATE INDEX SQL091006084330491
879 ON TEMPLATES(GROUPSIDS);
880
881CREATE UNIQUE INDEX SQL091006084331263
882 ON FILESTOITEMS(IDS);
883
884CREATE INDEX SQL091006084331260
885 ON FILESTOITEMS(ITEMSIDS);
886
887CREATE INDEX SQL091006084331261
888 ON FILESTOITEMS(FILENAME);
889
890CREATE INDEX SQL091006084331262
891 ON FILESTOITEMS(GROUPSIDS);
892
893CREATE UNIQUE INDEX SQL091006084332163
894 ON FILESTOPRODUCTS(IDS);
895
896CREATE INDEX SQL091006084332160
897 ON FILESTOPRODUCTS(PRODUCTSIDS);
898
899CREATE INDEX SQL091006084332161
900 ON FILESTOPRODUCTS(FILENAME);
901
902CREATE INDEX SQL091006084332162
903 ON FILESTOPRODUCTS(GROUPSIDS);
904
905CREATE UNIQUE INDEX SQL091006084333560
906 ON PLUGINS(IDS);
907
908CREATE INDEX SQL091006084333550
909 ON PLUGINS(FILENAME);
910
911CREATE INDEX SQL091006084333551
912 ON PLUGINS(GROUPSIDS);
913
914CREATE UNIQUE INDEX SQL091006084334241
915 ON PLUGINSTOUSERS(IDS);
916
917CREATE INDEX SQL091006084334230
918 ON PLUGINSTOUSERS(PLUGINSIDS);
919
920CREATE INDEX SQL091006084334240
921 ON PLUGINSTOUSERS(GROUPSIDS);
922
923CREATE UNIQUE INDEX SQL091006084334891
924 ON FORMATSTOUSERS(IDS);
925
926CREATE INDEX SQL091006084334890
927 ON FORMATSTOUSERS(USERSIDS);
928
929CREATE UNIQUE INDEX SQL091006084335461
930 ON TEMPLATESTOUSERS(IDS);
931
932CREATE INDEX SQL091006084335450
933 ON TEMPLATESTOUSERS(USERSIDS);
934
935CREATE INDEX SQL091006084335451
936 ON TEMPLATESTOUSERS(TEMPLATESIDS);
937
938CREATE INDEX SQL091006084335460
939 ON TEMPLATESTOUSERS(GROUPSIDS);
940
941CREATE UNIQUE INDEX SQL091006084336332
942 ON ADDRESSES(IDS);
943
944CREATE INDEX SQL091006084336330
945 ON ADDRESSES(GROUPSIDS);
946
947CREATE INDEX SQL091006084336331
948 ON ADDRESSES(CONTACTSIDS);
949
950CREATE UNIQUE INDEX SQL091006084336882
951 ON REMINDERS(IDS);
952
953CREATE INDEX SQL091006084336880
954 ON REMINDERS(ITEMSIDS);
955
956CREATE INDEX SQL091006084336881
957 ON REMINDERS(GROUPSIDS);
958
959CREATE UNIQUE INDEX SQL091006084337581
960 ON STAGES(IDS);
961
962CREATE INDEX SQL091006084337580
963 ON STAGES(GROUPSIDS);
964
965 ALTER TABLE CONTACTS
966 ADD CONSTRAINT CONST3
967 UNIQUE (CNUMBER, GROUPSIDS) ;
968
969ALTER TABLE COUNTRIES
970 ADD CONSTRAINT SQL091006084312110
971 UNIQUE (ISO) ;
972
973ALTER TABLE FILES
974 ADD CONSTRAINT SQL091006084314570
975 UNIQUE (CNAME) ;
976
977ALTER TABLE GLOBALSETTINGS
978 ADD CONSTRAINT CONST2
979 UNIQUE (CNAME, GROUPSIDS) ;
980
981ALTER TABLE GROUPS
982 ADD CONSTRAINT SQL091006084310810
983 UNIQUE (CNAME) ;
984
985ALTER TABLE ITEMS
986 ADD CONSTRAINT CONST7
987 UNIQUE (CNUMBER, INTTYPE) ;
988
989ALTER TABLE LANGUAGES
990 ADD CONSTRAINT SQL091006084315171
991 UNIQUE (LONGNAME) ;
992
993ALTER TABLE LANGUAGES
994 ADD CONSTRAINT CONST6
995 UNIQUE (CNAME, GROUPSIDS) ;
996
997ALTER TABLE PRODUCTGROUPS
998 ADD CONSTRAINT CONST1
999 UNIQUE (CNAME, GROUPSIDS) ;
1000
1001ALTER TABLE PRODUCTS
1002 ADD CONSTRAINT CONST8
1003 UNIQUE (CNUMBER, GROUPSIDS) ;
1004
1005ALTER TABLE TABLELOCK
1006 ADD CONSTRAINT ONE_LOCK
1007 UNIQUE (CNAME, ROWID) ;
1008
1009ALTER TABLE USERS
1010 ADD CONSTRAINT CONST4
1011 UNIQUE (CNAME, GROUPSIDS) ;
1012
1013ALTER TABLE WEBSHOPS
1014 ADD CONSTRAINT CONST9
1015 UNIQUE (CNAME, GROUPSIDS) ;
1016
1017ALTER TABLE WSCONTACTSMAPPING
1018 ADD CONSTRAINT CONST10
1019 UNIQUE (WSCONTACT, GROUPSIDS, WEBSHOPSIDS) ;
1020
1021ALTER TABLE WSITEMSMAPPING
1022 ADD CONSTRAINT CONST11
1023 UNIQUE (WSITEM, GROUPSIDS, WEBSHOPSIDS) ;
1024
1025ALTER TABLE ACCOUNTS
1026 ADD CONSTRAINT SQL091006084316450
1027 FOREIGN KEY(GROUPSIDS)
1028 REFERENCES GROUPS(IDS);
1029
1030ALTER TABLE ADDRESSES
1031 ADD CONSTRAINT SQL091006084336331
1032 FOREIGN KEY(CONTACTSIDS)
1033 REFERENCES CONTACTS(IDS);
1034
1035ALTER TABLE ADDRESSES
1036 ADD CONSTRAINT SQL091006084336330
1037 FOREIGN KEY(GROUPSIDS)
1038 REFERENCES GROUPS(IDS);
1039
1040ALTER TABLE COMPS
1041 ADD CONSTRAINT SQL091006084323380
1042 FOREIGN KEY(GROUPSIDS)
1043 REFERENCES GROUPS(IDS);
1044
1045ALTER TABLE CONTACTS
1046 ADD CONSTRAINT SQL091006084313450
1047 FOREIGN KEY(GROUPSIDS)
1048 REFERENCES GROUPS(IDS);
1049
1050ALTER TABLE EXPENSES
1051 ADD CONSTRAINT SQL091006084324141
1052 FOREIGN KEY(ACCOUNTSIDS)
1053 REFERENCES ACCOUNTS(IDS);
1054
1055ALTER TABLE EXPENSES
1056 ADD CONSTRAINT SQL091006084324140
1057 FOREIGN KEY(GROUPSIDS)
1058 REFERENCES GROUPS(IDS);
1059
1060ALTER TABLE FAVOURITES
1061 ADD CONSTRAINT SQL091006084315881
1062 FOREIGN KEY(GROUPSIDS)
1063 REFERENCES GROUPS(IDS);
1064
1065ALTER TABLE FAVOURITES
1066 ADD CONSTRAINT SQL091006084315880
1067 FOREIGN KEY(USERSIDS)
1068 REFERENCES USERS(IDS)
1069 ON DELETE CASCADE ;
1070
1071ALTER TABLE FILES
1072 ADD CONSTRAINT SQL091006084314571
1073 FOREIGN KEY(GROUPSIDS)
1074 REFERENCES GROUPS(IDS);
1075
1076ALTER TABLE FILESTOCONTACTS
1077 ADD CONSTRAINT SQL091006084329670
1078 FOREIGN KEY(GROUPSIDS)
1079 REFERENCES GROUPS(IDS);
1080
1081ALTER TABLE FILESTOCONTACTS
1082 ADD CONSTRAINT SQL091006084329651
1083 FOREIGN KEY(FILENAME)
1084 REFERENCES FILES(CNAME)
1085 ON DELETE CASCADE ;
1086
1087ALTER TABLE FILESTOCONTACTS
1088 ADD CONSTRAINT SQL091006084329650
1089 FOREIGN KEY(CONTACTSIDS)
1090 REFERENCES CONTACTS(IDS)
1091 ON DELETE CASCADE ;
1092
1093ALTER TABLE FILESTOITEMS
1094 ADD CONSTRAINT SQL091006084331262
1095 FOREIGN KEY(GROUPSIDS)
1096 REFERENCES GROUPS(IDS);
1097
1098ALTER TABLE FILESTOITEMS
1099 ADD CONSTRAINT SQL091006084331261
1100 FOREIGN KEY(FILENAME)
1101 REFERENCES FILES(CNAME)
1102 ON DELETE CASCADE ;
1103
1104ALTER TABLE FILESTOITEMS
1105 ADD CONSTRAINT SQL091006084331260
1106 FOREIGN KEY(ITEMSIDS)
1107 REFERENCES ITEMS(IDS)
1108 ON DELETE CASCADE ;
1109
1110ALTER TABLE FILESTOPRODUCTS
1111 ADD CONSTRAINT SQL091006084332162
1112 FOREIGN KEY(GROUPSIDS)
1113 REFERENCES GROUPS(IDS);
1114
1115ALTER TABLE FILESTOPRODUCTS
1116 ADD CONSTRAINT SQL091006084332161
1117 FOREIGN KEY(FILENAME)
1118 REFERENCES FILES(CNAME)
1119 ON DELETE CASCADE ;
1120
1121ALTER TABLE FILESTOPRODUCTS
1122 ADD CONSTRAINT SQL091006084332160
1123 FOREIGN KEY(PRODUCTSIDS)
1124 REFERENCES PRODUCTS(IDS)
1125 ON DELETE CASCADE ;
1126
1127ALTER TABLE FORMATSTOUSERS
1128 ADD CONSTRAINT SQL091006084334890
1129 FOREIGN KEY(USERSIDS)
1130 REFERENCES USERS(IDS)
1131 ON DELETE CASCADE ;
1132
1133ALTER TABLE ITEMS
1134 ADD CONSTRAINT SQL091006084316860
1135 FOREIGN KEY(CONTACTSIDS)
1136 REFERENCES CONTACTS(IDS)
1137 ON DELETE CASCADE ;
1138
1139ALTER TABLE ITEMS
1140 ADD CONSTRAINT SQL091006084316851
1141 FOREIGN KEY(ACCOUNTSIDS)
1142 REFERENCES ACCOUNTS(IDS);
1143
1144ALTER TABLE ITEMS
1145 ADD CONSTRAINT SQL091006084316850
1146 FOREIGN KEY(GROUPSIDS)
1147 REFERENCES GROUPS(IDS);
1148
1149ALTER TABLE ITEMSTOACCOUNTS
1150 ADD CONSTRAINT SQL091006084328461
1151 FOREIGN KEY(ACCOUNTSIDS)
1152 REFERENCES ACCOUNTS(IDS)
1153 ON DELETE CASCADE ;
1154
1155ALTER TABLE ITEMSTOACCOUNTS
1156 ADD CONSTRAINT SQL091006084328460
1157 FOREIGN KEY(ITEMSIDS)
1158 REFERENCES ITEMS(IDS)
1159 ON DELETE CASCADE ;
1160
1161ALTER TABLE LANGUAGES
1162 ADD CONSTRAINT SQL091006084315172
1163 FOREIGN KEY(FILENAME)
1164 REFERENCES FILES(CNAME)
1165 ON DELETE CASCADE ;
1166
1167ALTER TABLE LANGUAGES
1168 ADD CONSTRAINT SQL091006084315170
1169 FOREIGN KEY(GROUPSIDS)
1170 REFERENCES GROUPS(IDS);
1171
1172ALTER TABLE MAILS
1173 ADD CONSTRAINT SQL091006084322551
1174 FOREIGN KEY(USERSIDS)
1175 REFERENCES USERS(IDS)
1176 ON DELETE CASCADE ;
1177
1178ALTER TABLE MAILS
1179 ADD CONSTRAINT SQL091006084322550
1180 FOREIGN KEY(GROUPSIDS)
1181 REFERENCES GROUPS(IDS);
1182
1183ALTER TABLE PLUGINS
1184 ADD CONSTRAINT SQL091006084333551
1185 FOREIGN KEY(GROUPSIDS)
1186 REFERENCES GROUPS(IDS);
1187
1188ALTER TABLE PLUGINS
1189 ADD CONSTRAINT SQL091006084333550
1190 FOREIGN KEY(FILENAME)
1191 REFERENCES FILES(CNAME)
1192 ON DELETE CASCADE ;
1193
1194ALTER TABLE PLUGINSTOUSERS
1195 ADD CONSTRAINT SQL091006084334240
1196 FOREIGN KEY(GROUPSIDS)
1197 REFERENCES GROUPS(IDS);
1198
1199ALTER TABLE PLUGINSTOUSERS
1200 ADD CONSTRAINT SQL091006084334230
1201 FOREIGN KEY(PLUGINSIDS)
1202 REFERENCES PLUGINS(IDS)
1203 ON DELETE CASCADE ;
1204
1205ALTER TABLE PRODUCTLISTITEMS
1206 ADD CONSTRAINT SQL091006084321140
1207 FOREIGN KEY(ORIGINALPRODUCTSIDS)
1208 REFERENCES PRODUCTS(IDS)
1209 ON DELETE CASCADE ;
1210
1211ALTER TABLE PRODUCTLISTITEMS
1212 ADD CONSTRAINT SQL091006084321131
1213 FOREIGN KEY(PRODUCTLISTSIDS)
1214 REFERENCES PRODUCTLISTS(IDS)
1215 ON DELETE CASCADE ;
1216
1217ALTER TABLE PRODUCTLISTITEMS
1218 ADD CONSTRAINT SQL091006084321130
1219 FOREIGN KEY(GROUPSIDS)
1220 REFERENCES GROUPS(IDS);
1221
1222ALTER TABLE PRODUCTLISTS
1223 ADD CONSTRAINT SQL091006084320590
1224 FOREIGN KEY(GROUPSIDS)
1225 REFERENCES GROUPS(IDS);
1226
1227ALTER TABLE PRODUCTS
1228 ADD CONSTRAINT SQL091006084319612
1229 FOREIGN KEY(PRODUCTGROUPSIDS)
1230 REFERENCES PRODUCTGROUPS(IDS);
1231
1232ALTER TABLE PRODUCTS
1233 ADD CONSTRAINT SQL091006084319611
1234 FOREIGN KEY(GROUPSIDS)
1235 REFERENCES GROUPS(IDS);
1236
1237ALTER TABLE PRODUCTS
1238 ADD CONSTRAINT SQL091006084319610
1239 FOREIGN KEY(TAXIDS)
1240 REFERENCES TAX(IDS);
1241
1242ALTER TABLE REMINDERS
1243 ADD CONSTRAINT SQL091006084336881
1244 FOREIGN KEY(GROUPSIDS)
1245 REFERENCES GROUPS(IDS);
1246
1247ALTER TABLE REMINDERS
1248 ADD CONSTRAINT SQL091006084336880
1249 FOREIGN KEY(ITEMSIDS)
1250 REFERENCES ITEMS(IDS)
1251 ON DELETE CASCADE ;
1252
1253ALTER TABLE REVENUES
1254 ADD CONSTRAINT SQL091006084324800
1255 FOREIGN KEY(ACCOUNTSIDS)
1256 REFERENCES ACCOUNTS(IDS);
1257
1258ALTER TABLE REVENUES
1259 ADD CONSTRAINT SQL091006084324790
1260 FOREIGN KEY(GROUPSIDS)
1261 REFERENCES GROUPS(IDS);
1262
1263ALTER TABLE SCHEDULE
1264 ADD CONSTRAINT SQL091006084318482
1265 FOREIGN KEY(ITEMSIDS)
1266 REFERENCES ITEMS(IDS)
1267 ON DELETE CASCADE ;
1268
1269ALTER TABLE SCHEDULE
1270 ADD CONSTRAINT SQL091006084318481
1271 FOREIGN KEY(USERSIDS)
1272 REFERENCES USERS(IDS)
1273 ON DELETE CASCADE ;
1274
1275ALTER TABLE SCHEDULE
1276 ADD CONSTRAINT SQL091006084318480
1277 FOREIGN KEY(GROUPSIDS)
1278 REFERENCES GROUPS(IDS);
1279
1280ALTER TABLE STAGES
1281 ADD CONSTRAINT SQL091006084337580
1282 FOREIGN KEY(GROUPSIDS)
1283 REFERENCES GROUPS(IDS);
1284
1285ALTER TABLE SUBITEMS
1286 ADD CONSTRAINT SQL091006084317901
1287 FOREIGN KEY(GROUPSIDS)
1288 REFERENCES GROUPS(IDS);
1289
1290ALTER TABLE SUBITEMS
1291 ADD CONSTRAINT SQL091006084317900
1292 FOREIGN KEY(ITEMSIDS)
1293 REFERENCES ITEMS(IDS)
1294 ON DELETE CASCADE ;
1295
1296ALTER TABLE TABLELOCK
1297 ADD CONSTRAINT SQL091006084327890
1298 FOREIGN KEY(USERSIDS)
1299 REFERENCES USERS(IDS)
1300 ON DELETE CASCADE ;
1301
1302ALTER TABLE TAX
1303 ADD CONSTRAINT SQL091006084312890
1304 FOREIGN KEY(GROUPSIDS)
1305 REFERENCES GROUPS(IDS);
1306
1307ALTER TABLE TEMPLATES
1308 ADD CONSTRAINT SQL091006084330491
1309 FOREIGN KEY(GROUPSIDS)
1310 REFERENCES GROUPS(IDS);
1311
1312ALTER TABLE TEMPLATES
1313 ADD CONSTRAINT SQL091006084330490
1314 FOREIGN KEY(FILENAME)
1315 REFERENCES FILES(CNAME)
1316 ON DELETE CASCADE ;
1317
1318ALTER TABLE TEMPLATESTOUSERS
1319 ADD CONSTRAINT SQL091006084335460
1320 FOREIGN KEY(GROUPSIDS)
1321 REFERENCES GROUPS(IDS);
1322
1323ALTER TABLE TEMPLATESTOUSERS
1324 ADD CONSTRAINT SQL091006084335451
1325 FOREIGN KEY(TEMPLATESIDS)
1326 REFERENCES TEMPLATES(IDS)
1327 ON DELETE CASCADE ;
1328
1329ALTER TABLE TEMPLATESTOUSERS
1330 ADD CONSTRAINT SQL091006084335450
1331 FOREIGN KEY(USERSIDS)
1332 REFERENCES USERS(IDS)
1333 ON DELETE CASCADE ;
1334
1335ALTER TABLE USERPROPERTIES
1336 ADD CONSTRAINT SQL091006084322190
1337 FOREIGN KEY(GROUPSIDS)
1338 REFERENCES GROUPS(IDS);
1339
1340ALTER TABLE USERS
1341 ADD CONSTRAINT SQL091006084313960
1342 FOREIGN KEY(GROUPSIDS)
1343 REFERENCES GROUPS(IDS);
1344
1345ALTER TABLE WSCONTACTSMAPPING
1346 ADD CONSTRAINT SQL091006084326052
1347 FOREIGN KEY(CONTACTSIDS)
1348 REFERENCES CONTACTS(IDS)
1349 ON DELETE CASCADE ;
1350
1351ALTER TABLE WSCONTACTSMAPPING
1352 ADD CONSTRAINT SQL091006084326051
1353 FOREIGN KEY(GROUPSIDS)
1354 REFERENCES GROUPS(IDS)
1355 ON DELETE CASCADE ;
1356
1357ALTER TABLE WSCONTACTSMAPPING
1358 ADD CONSTRAINT SQL091006084326050
1359 FOREIGN KEY(WEBSHOPSIDS)
1360 REFERENCES WEBSHOPS(IDS)
1361 ON DELETE CASCADE ;
1362
1363ALTER TABLE WSITEMSMAPPING
1364 ADD CONSTRAINT SQL091006084326872
1365 FOREIGN KEY(ITEMSIDS)
1366 REFERENCES CONTACTS(IDS)
1367 ON DELETE CASCADE ;
1368
1369ALTER TABLE WSITEMSMAPPING
1370 ADD CONSTRAINT SQL091006084326871
1371 FOREIGN KEY(GROUPSIDS)
1372 REFERENCES GROUPS(IDS)
1373 ON DELETE CASCADE ;
1374
1375ALTER TABLE WSITEMSMAPPING
1376 ADD CONSTRAINT SQL091006084326870
1377 FOREIGN KEY(WEBSHOPSIDS)
1378 REFERENCES WEBSHOPS(IDS)
1379 ON DELETE CASCADE ;
1380
1381CREATE TRIGGER CONTACTS_INDEXER1
1382 AFTER INSERT ON CONTACTS
1383 REFERENCING NEW AS NEWDATA
1384 FOR EACH ROW MODE DB2SQL
1385 INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids, 'contacts',newdata.ids,newdata.cnumber||' '||newdata.taxnumber||' '||newdata.title||' '||newdata.country||' '|| newdata.prename||' '|| newdata.cname||' '|| newdata.street||' '||newdata.zip||' '|| newdata.city ||' '||newdata.mainphone||' '||newdata.fax||' '||newdata.mobilephone||' '||newdata.workphone||' '||newdata.mailaddress||' '||newdata.company||' '|| newdata.department||' '||newdata.website||' '||newdata.notes);
1386
1387CREATE TRIGGER CONTACTS_INDEXER2
1388 AFTER UPDATE ON CONTACTS
1389 REFERENCING NEW AS NEWDATA
1390 FOR EACH ROW MODE DB2SQL
1391 DELETE FROM searchindex WHERE dbidentity = 'contacts' AND rowid = newdata.ids;
1392
1393CREATE TRIGGER CONTACTS_INDEXER3
1394 AFTER UPDATE ON CONTACTS
1395 REFERENCING NEW AS NEWDATA
1396 FOR EACH ROW MODE DB2SQL
1397 INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'contacts',newdata.ids,newdata.cnumber||' '||newdata.taxnumber||' '||newdata.title||' '||newdata.country||' '|| newdata.prename||' '|| newdata.cname||' '|| newdata.street||' '||newdata.zip||' '|| newdata.city ||' '||newdata.mainphone||' '||newdata.fax||' '||newdata.mobilephone||' '||newdata.workphone||' '||newdata.mailaddress||' '||newdata.company||' '|| newdata.department||' '||newdata.website||' '||newdata.notes);
1398
1399CREATE TRIGGER CONTACTS_INDEXER4
1400 AFTER DELETE ON CONTACTS
1401 REFERENCING OLD AS NEWDATA
1402 FOR EACH ROW MODE DB2SQL
1403 DELETE FROM searchindex WHERE dbidentity = 'contacts' AND rowid = newdata.ids;
1404
1405CREATE TRIGGER CONTACTS_TRASH1
1406 AFTER UPDATE ON CONTACTS
1407 REFERENCING NEW AS NEWDATA
1408 FOR EACH ROW MODE DB2SQL
1409 INSERT INTO trashbin (deleteme, cname, rowid, description) VALUES (newdata.invisible,'contacts',newdata.ids,newdata.cnumber||' ('|| newdata.cname||')');
1410
1411CREATE TRIGGER CONTACTS_TRASH2
1412 AFTER DELETE ON CONTACTS
1413 REFERENCING OLD AS NEWDATA
1414 FOR EACH ROW MODE DB2SQL
1415 DELETE FROM trashbin WHERE cname = 'contacts' AND rowid = newdata.ids;
1416
1417CREATE TRIGGER EXPENSES_INDEXER1
1418 AFTER INSERT ON EXPENSES
1419 REFERENCING NEW AS NEWDATA
1420 FOR EACH ROW MODE DB2SQL
1421 INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'expenses',newdata.ids,newdata.cname||' '||newdata.dateadded);
1422
1423CREATE TRIGGER EXPENSES_INDEXER2
1424 AFTER UPDATE ON EXPENSES
1425 REFERENCING NEW AS NEWDATA
1426 FOR EACH ROW MODE DB2SQL
1427 DELETE FROM searchindex WHERE dbidentity = 'expenses' AND rowid = newdata.ids;
1428
1429CREATE TRIGGER EXPENSES_INDEXER3
1430 AFTER UPDATE ON EXPENSES
1431 REFERENCING NEW AS NEWDATA
1432 FOR EACH ROW MODE DB2SQL
1433 INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'expenses',newdata.ids,newdata.cname||' '||newdata.dateadded);
1434
1435CREATE TRIGGER EXPENSES_INDEXER4
1436 AFTER DELETE ON EXPENSES
1437 REFERENCING OLD AS NEWDATA
1438 FOR EACH ROW MODE DB2SQL
1439 DELETE FROM searchindex WHERE dbidentity = 'expenses' AND rowid = newdata.ids;
1440
1441CREATE TRIGGER EXPENSES_TRASH1
1442 AFTER UPDATE ON EXPENSES
1443 REFERENCING NEW AS NEWDATA
1444 FOR EACH ROW MODE DB2SQL
1445 INSERT INTO trashbin (deleteme, cname, rowid, description) VALUES (newdata.invisible,'expenses',newdata.ids,newdata.cname);
1446
1447CREATE TRIGGER EXPENSES_TRASH2
1448 AFTER DELETE ON EXPENSES
1449 REFERENCING OLD AS NEWDATA
1450 FOR EACH ROW MODE DB2SQL
1451 DELETE FROM trashbin WHERE cname = 'expenses' AND rowid = newdata.ids;
1452
1453CREATE TRIGGER FILESTOCONTACTS_INDEXER1
1454 AFTER INSERT ON FILESTOCONTACTS
1455 REFERENCING NEW AS NEWDATA
1456 FOR EACH ROW MODE DB2SQL
1457 INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'filestocontacts',newdata.ids,newdata.cname||' '||newdata.description||' '||newdata.filename);
1458
1459CREATE TRIGGER FILESTOCONTACTS_INDEXER2
1460 AFTER UPDATE ON FILESTOCONTACTS
1461 REFERENCING NEW AS NEWDATA
1462 FOR EACH ROW MODE DB2SQL
1463 DELETE FROM searchindex WHERE dbidentity = 'filestocontacts' AND rowid = newdata.ids;
1464
1465CREATE TRIGGER FILESTOCONTACTS_INDEXER3
1466 AFTER UPDATE ON FILESTOCONTACTS
1467 REFERENCING NEW AS NEWDATA
1468 FOR EACH ROW MODE DB2SQL
1469 INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'filestocontacts',newdata.ids,newdata.cname||' '||newdata.description||' '||newdata.filename);
1470
1471CREATE TRIGGER FILESTOCONTACTS_INDEXER4
1472 AFTER DELETE ON FILESTOCONTACTS
1473 REFERENCING OLD AS NEWDATA
1474 FOR EACH ROW MODE DB2SQL
1475 DELETE FROM searchindex WHERE dbidentity = 'filestocontacts' AND rowid = newdata.ids;
1476
1477CREATE TRIGGER GROUPS_INDEXER1
1478 AFTER INSERT ON GROUPS
1479 REFERENCING NEW AS NEWDATA
1480 FOR EACH ROW MODE DB2SQL
1481 INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'groups',newdata.ids,newdata.cname||' '||newdata.description||' '||newdata.dateadded);
1482
1483CREATE TRIGGER GROUPS_INDEXER2
1484 AFTER UPDATE ON GROUPS
1485 REFERENCING NEW AS NEWDATA
1486 FOR EACH ROW MODE DB2SQL
1487 DELETE FROM searchindex WHERE dbidentity = 'groups' AND rowid = newdata.ids;
1488
1489CREATE TRIGGER GROUPS_INDEXER3
1490 AFTER UPDATE ON GROUPS
1491 REFERENCING NEW AS NEWDATA
1492 FOR EACH ROW MODE DB2SQL
1493 INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'groups',newdata.ids,newdata.cname||' '||newdata.description||' '||newdata.dateadded);
1494
1495CREATE TRIGGER GROUPS_INDEXER4
1496 AFTER DELETE ON GROUPS
1497 REFERENCING OLD AS NEWDATA
1498 FOR EACH ROW MODE DB2SQL
1499 DELETE FROM searchindex WHERE dbidentity = 'groups' AND rowid = newdata.ids;
1500
1501CREATE TRIGGER ITEMS_INDEXER1
1502 AFTER INSERT ON ITEMS
1503 REFERENCING NEW AS NEWDATA
1504 FOR EACH ROW MODE DB2SQL
1505 INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'items',newdata.ids,newdata.cname||' '||newdata.dateadded);
1506
1507CREATE TRIGGER ITEMS_INDEXER2
1508 AFTER UPDATE ON ITEMS
1509 REFERENCING NEW AS NEWDATA
1510 FOR EACH ROW MODE DB2SQL
1511 DELETE FROM searchindex WHERE dbidentity = 'items' AND rowid = newdata.ids;
1512
1513CREATE TRIGGER ITEMS_INDEXER3
1514 AFTER UPDATE ON ITEMS
1515 REFERENCING NEW AS NEWDATA
1516 FOR EACH ROW MODE DB2SQL
1517 INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'items',newdata.ids,newdata.cname||' '||newdata.dateadded);
1518
1519CREATE TRIGGER ITEMS_INDEXER4
1520 AFTER DELETE ON ITEMS
1521 REFERENCING OLD AS NEWDATA
1522 FOR EACH ROW MODE DB2SQL
1523 DELETE FROM searchindex WHERE dbidentity = 'items' AND rowid = newdata.ids;
1524
1525CREATE TRIGGER ITEMS_TRASH1
1526 AFTER UPDATE ON ITEMS
1527 REFERENCING NEW AS NEWDATA
1528 FOR EACH ROW MODE DB2SQL
1529 INSERT INTO trashbin (deleteme, cname, rowid, description) VALUES (newdata.invisible,'items',newdata.ids,newdata.cname);
1530
1531CREATE TRIGGER ITEMS_TRASH2
1532 AFTER DELETE ON ITEMS
1533 REFERENCING OLD AS NEWDATA
1534 FOR EACH ROW MODE DB2SQL
1535 DELETE FROM trashbin WHERE cname = 'items' AND rowid = newdata.ids;
1536
1537CREATE TRIGGER PRODUCTS_INDEXER1
1538 AFTER INSERT ON PRODUCTS
1539 REFERENCING NEW AS NEWDATA
1540 FOR EACH ROW MODE DB2SQL
1541 INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'products',newdata.ids,newdata.cname||' '||newdata.cnumber||' '||newdata.description||' '||newdata.dateadded);
1542
1543CREATE TRIGGER PRODUCTS_INDEXER2
1544 AFTER UPDATE ON PRODUCTS
1545 REFERENCING NEW AS NEWDATA
1546 FOR EACH ROW MODE DB2SQL
1547 DELETE FROM searchindex WHERE dbidentity = 'products' AND rowid = newdata.ids;
1548
1549CREATE TRIGGER PRODUCTS_INDEXER3
1550 AFTER UPDATE ON PRODUCTS
1551 REFERENCING NEW AS NEWDATA
1552 FOR EACH ROW MODE DB2SQL
1553 INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'products',newdata.ids,newdata.cname||' '||newdata.cnumber||' '||newdata.description||' '||newdata.dateadded);
1554
1555CREATE TRIGGER PRODUCTS_INDEXER4
1556 AFTER DELETE ON PRODUCTS
1557 REFERENCING OLD AS NEWDATA
1558 FOR EACH ROW MODE DB2SQL
1559 DELETE FROM searchindex WHERE dbidentity = 'products' AND rowid = newdata.ids;
1560
1561CREATE TRIGGER PRODUCTS_TRASH1
1562 AFTER UPDATE ON PRODUCTS
1563 REFERENCING NEW AS NEWDATA
1564 FOR EACH ROW MODE DB2SQL
1565 INSERT INTO trashbin (deleteme, cname, rowid, description) VALUES (newdata.invisible, 'products',newdata.ids,newdata.cnumber||' ('|| newdata.cname||')');
1566
1567CREATE TRIGGER PRODUCTS_TRASH2
1568 AFTER DELETE ON PRODUCTS
1569 REFERENCING OLD AS NEWDATA
1570 FOR EACH ROW MODE DB2SQL
1571 DELETE FROM trashbin WHERE cname = 'products' AND rowid = newdata.ids;
1572
1573CREATE TRIGGER REVENUES_INDEXER1
1574 AFTER INSERT ON REVENUES
1575 REFERENCING NEW AS NEWDATA
1576 FOR EACH ROW MODE DB2SQL
1577 INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'revenues',newdata.ids,newdata.cname||' '||newdata.dateadded);
1578
1579CREATE TRIGGER REVENUES_INDEXER2
1580 AFTER UPDATE ON REVENUES
1581 REFERENCING NEW AS NEWDATA
1582 FOR EACH ROW MODE DB2SQL
1583 DELETE FROM searchindex WHERE dbidentity = 'revenues' AND rowid = newdata.ids;
1584
1585CREATE TRIGGER REVENUES_INDEXER3
1586 AFTER UPDATE ON REVENUES
1587 REFERENCING NEW AS NEWDATA
1588 FOR EACH ROW MODE DB2SQL
1589 INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'revenues',newdata.ids,newdata.cname||' '||newdata.dateadded);
1590
1591CREATE TRIGGER REVENUES_INDEXER4
1592 AFTER DELETE ON REVENUES
1593 REFERENCING OLD AS NEWDATA
1594 FOR EACH ROW MODE DB2SQL
1595 DELETE FROM searchindex WHERE dbidentity = 'revenues' AND rowid = newdata.ids;
1596
1597CREATE TRIGGER REVENUES_TRASH1
1598 AFTER UPDATE ON REVENUES
1599 REFERENCING NEW AS NEWDATA
1600 FOR EACH ROW MODE DB2SQL
1601 INSERT INTO trashbin (deleteme, cname, rowid, description) VALUES (newdata.invisible,'revenues',newdata.ids,newdata.cname);
1602
1603CREATE TRIGGER REVENUES_TRASH2
1604 AFTER DELETE ON REVENUES
1605 REFERENCING OLD AS NEWDATA
1606 FOR EACH ROW MODE DB2SQL
1607 DELETE FROM trashbin WHERE cname = 'revenues' AND rowid = newdata.ids;
1608
1609CREATE TRIGGER SUBITEMS_INDEXER1
1610 AFTER INSERT ON SUBITEMS
1611 REFERENCING NEW AS NEWDATA
1612 FOR EACH ROW MODE DB2SQL
1613 INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'subitems',newdata.ids,newdata.cname||' '||newdata.description||' '||newdata.dateadded);
1614
1615CREATE TRIGGER SUBITEMS_INDEXER2
1616 AFTER UPDATE ON SUBITEMS
1617 REFERENCING NEW AS NEWDATA
1618 FOR EACH ROW MODE DB2SQL
1619 DELETE FROM searchindex WHERE dbidentity = 'subitems' AND rowid = newdata.ids;
1620
1621CREATE TRIGGER SUBITEMS_INDEXER3
1622 AFTER UPDATE ON SUBITEMS
1623 REFERENCING NEW AS NEWDATA
1624 FOR EACH ROW MODE DB2SQL
1625 INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'subitems',newdata.ids,newdata.cname||' '||newdata.description||' '||newdata.dateadded);
1626
1627CREATE TRIGGER SUBITEMS_INDEXER4
1628 AFTER DELETE ON SUBITEMS
1629 REFERENCING OLD AS NEWDATA
1630 FOR EACH ROW MODE DB2SQL
1631 DELETE FROM searchindex WHERE dbidentity = 'subitems' AND rowid = newdata.ids;
1632
1633CREATE TRIGGER THRASH_HANDLER1
1634 AFTER INSERT ON TRASHBIN
1635 FOR EACH STATEMENT MODE DB2SQL
1636 DELETE FROM trashbin WHERE deleteme = 0;
1637
1638CREATE TRIGGER THRASH_HANDLER2
1639 AFTER INSERT ON TRASHBIN
1640 FOR EACH STATEMENT MODE DB2SQL
1641 DELETE FROM trashbin WHERE ids IN (SELECT ids FROM trashbin WHERE EXISTS( SELECT ids FROM trashbin AS tmptable WHERE trashbin.cname = tmptable.cname AND trashbin.rowid = tmptable.rowid HAVING trashbin.ids < MAX(tmptable.ids) ) );
1642
1643
1644INSERT INTO GROUPS(CNAME, DESCRIPTION, DEFAULTS, GROUPSIDS, DATEADDED, RESERVE1, INTADDEDBY, HIERARCHYPATH, RESERVE2)
1645 VALUES( 'All Groups', 'This group is visible to everyone.', NULL, 0, '2009-04-03', NULL, 0, NULL, NULL);
1646INSERT INTO ACCOUNTS(INTACCOUNTCLASS, CNAME, DESCRIPTION, TAXVALUE, DATEADDED, INTADDEDBY, INTPARENTACCOUNT, GROUPSIDS, INVISIBLE, INTACCOUNTTYPE, INTPROFITFID, INTTAXFID, INTTAXUID, FRAME, HIERARCHYPATH)
1647 VALUES( 0, 'All Accounts', 'This account is the parent account of all account frames.', 0.0, '2009-04-03', 0, 0, 1, 0, 0, 0, 0, 0, 'builtin', NULL);
1648INSERT INTO ACCOUNTS(INTACCOUNTCLASS, CNAME, DESCRIPTION, TAXVALUE, DATEADDED, INTADDEDBY, INTPARENTACCOUNT, GROUPSIDS, INVISIBLE, INTACCOUNTTYPE, INTPROFITFID, INTTAXFID, INTTAXUID, FRAME, HIERARCHYPATH)
1649 VALUES( 0, 'Expenses', '', 0.0, '2009-04-03', 0, 1, 1, 0, 2, 0, 0, 0, 'builtin', NULL);
1650INSERT INTO ACCOUNTS(INTACCOUNTCLASS, CNAME, DESCRIPTION, TAXVALUE, DATEADDED, INTADDEDBY, INTPARENTACCOUNT, GROUPSIDS, INVISIBLE, INTACCOUNTTYPE, INTPROFITFID, INTTAXFID, INTTAXUID, FRAME, HIERARCHYPATH)
1651 VALUES( 0, 'Income', '', 0.0, '2009-04-03', 0, 1, 1, 0, 3, 0, 0, 0, 'builtin', NULL);
1652INSERT INTO COUNTRIES(CNAME, ISO, GROUPSIDS, RESERVE1, RESERVE2)
1653 VALUES( 'Deutschland', 276, 1, NULL, NULL);
1654INSERT INTO GLOBALSETTINGS(CNAME, GROUPSIDS, VALUE)
1655 VALUES( 'yabs_dbversion', 1, '1.0');
1656INSERT INTO PRODUCTGROUPS(CNAME, DESCRIPTION, DEFAULTS, GROUPSIDS, PRODUCTGROUPSIDS, DATEADDED, HIERARCHYPATH, RESERVE1, INTADDEDBY, RESERVE2)
1657 VALUES( 'All Products', 'This product group is visible to everyone.', NULL, 0, 0, '2009-04-03', NULL, NULL, 0, NULL);
1658INSERT INTO SEARCHINDEX(DBIDENTITY, GROUPSIDS, ROWID, TEXT)
1659 VALUES( 'groups', 0, 1, 'All Groups This group is visible to everyone. 2009-04-03');
1660INSERT INTO TAX(CNAME, TAXVALUE, IDENTIFIER, GROUPSIDS, COUNTRY, DATEADDED, INTADDEDBY, INVISIBLE, RESERVE1, RESERVE2)
1661 VALUES( 'Default 0%', 0.0, 'Default 0%', 1, NULL, '2009-04-03', 0, 0, NULL, NULL);
1662INSERT INTO TAX(CNAME, TAXVALUE, IDENTIFIER, GROUPSIDS, COUNTRY, DATEADDED, INTADDEDBY, INVISIBLE, RESERVE1, RESERVE2)
1663 VALUES( 'Default 19%', 19.0, 'Default 19%', 1, NULL, '2009-04-03', 0, 0, NULL, NULL);
1664INSERT INTO TAX( CNAME, TAXVALUE, IDENTIFIER, GROUPSIDS, COUNTRY, DATEADDED, INTADDEDBY, INVISIBLE, RESERVE1, RESERVE2)
1665 VALUES( 'Default 7%', 7.0, 'Default 7%', 1, NULL, '2009-04-03', 0, 0, NULL, NULL);
1666INSERT INTO USERS(CNAME, GROUPSIDS, INTDEFAULTACCOUNT, COMPSIDS, INTDEFAULTSTATUS, FULLNAME, PASSWORD, LAF, LOCALE, DEFCOUNTRY, MAIL, LANGUAGE, INTHIGHESTRIGHT, ISENABLED, ISRGROUPED, ISLOGGEDIN, DATELASTLOG, DATEADDED, INTADDEDBY, INVISIBLE, RESERVE1, RESERVE2)
1667 VALUES( 'admin', 1, 1, 0, 1, 'Administrator', '5f4dcc3b5aa765d61d8327deb882cf99', 'com.sun.java.swing.plaf.windows.WindowsLookAndFeel', 'en_GB', '', '', 'buildin_en', 0, 1, 0, 0, '2009-10-06', '2009-04-03', 0, 0, NULL, NULL);
1668