PageRenderTime 228ms CodeModel.GetById 121ms app.highlight 43ms RepoModel.GetById 57ms app.codeStats 1ms

/src/mpv5/db/common/DatabaseInstallation.java

http://mp-rechnungs-und-kundenverwaltung.googlecode.com/
Java | 307 lines | 275 code | 3 blank | 29 comment | 0 complexity | 181373f76d2409e1ca70f8e88cd41b1d MD5 | raw file

Large files files are truncated, but you can click here to view the full file

  1  /*
  2 *  This file is part of YaBS.
  3 *
  4 *  YaBS is free software: you can redistribute it and/or modify
  5 *  it under the terms of the GNU General Public License as published by
  6 *  the Free Software Foundation, either version 3 of the License, or
  7 *  (at your option) any later version.
  8 *
  9 *  YaBS is distributed in the hope that it will be useful,
 10 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 11 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 12 *  GNU General Public License for more details.
 13 *
 14 *  You should have received a copy of the GNU General Public License
 15 *  along with YaBS.  If not, see <http://www.gnu.org/licenses/>.
 16 */
 17package mpv5.db.common;
 18
 19import javax.swing.UIManager;
 20import mpv5.db.objects.Account;
 21import mpv5.globals.Constants;
 22import mpv5.globals.Messages;
 23
 24/**
 25 * 
 26 */
 27public class DatabaseInstallation {
 28
 29    /**
 30     * This contains the database structure for mpv5
 31     *
 32     * As SQL.Views are currently not updateable from DERBY, i use two nearly identical tables here, to store user informations.
 33     * First one holds a users default data, where the second table holds additional address info.
 34     *
 35     */
 36    public final static String[] DERBY_STRUCTURE = new String[]{
 37        "CREATE TABLE groups (IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), cname VARCHAR(250) UNIQUE NOT NULL,description VARCHAR(750) DEFAULT NULL,defaults VARCHAR(250) DEFAULT NULL,groupsids BIGINT DEFAULT 0,dateadded DATE NOT NULL,reserve1 VARCHAR(500) DEFAULT NULL,intaddedby BIGINT DEFAULT 0, hierarchypath VARCHAR(500) DEFAULT NULL, reserve2 VARCHAR(500) DEFAULT NULL, PRIMARY KEY  (ids))",
 38        "CREATE TABLE productgroups (IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), CONSTRAINT const1 UNIQUE (cname, groupsids), cname VARCHAR(250) NOT NULL,description VARCHAR(750) DEFAULT NULL,defaults VARCHAR(250) DEFAULT NULL,groupsids BIGINT DEFAULT 0, productgroupsids BIGINT DEFAULT 0,dateadded DATE NOT NULL, hierarchypath VARCHAR(500) DEFAULT NULL, reserve1 VARCHAR(500) DEFAULT NULL,intaddedby BIGINT DEFAULT 0, reserve2 VARCHAR(500) DEFAULT NULL, PRIMARY KEY  (ids))",
 39        "CREATE TABLE history (IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),cname VARCHAR(250)  NOT NULL, username VARCHAR(250) NOT NULL,dbidentity VARCHAR(25)  NOT NULL, INTitem SMALLINT NOT NULL,groupsids BIGINT DEFAULT 0, dateadded DATE NOT NULL,intaddedby BIGINT DEFAULT 0,reserve1 VARCHAR(500) DEFAULT NULL, reserve2 VARCHAR(500) DEFAULT NULL,PRIMARY KEY  (ids))",
 40        "CREATE TABLE countries (IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), cname VARCHAR(250) NOT NULL, iso SMALLINT  UNIQUE NOT NULL,groupsids BIGINT DEFAULT 1,reserve1 VARCHAR(500) DEFAULT NULL, reserve2 VARCHAR(500) DEFAULT NULL,PRIMARY KEY  (ids))",
 41        "CREATE TABLE searchindex (IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),dbidentity VARCHAR(25) NOT NULL, groupsids BIGINT, rowID BIGINT NOT NULL,text VARCHAR(5000) DEFAULT NULL)",
 42        "CREATE TABLE globalsettings (IDS BIGINT DEFAULT 1,  CONSTRAINT const2 UNIQUE (cname, groupsids), cname VARCHAR(250) NOT NULL, groupsids BIGINT DEFAULT 1, value VARCHAR(250) NOT NULL)",
 43        //Main tables, must have ids, cname, groupsids, dateadded, intaddedby, invisible
 44        "CREATE TABLE tax (IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), cname VARCHAR(250), taxvalue DOUBLE DEFAULT 0,identifier VARCHAR(250) DEFAULT NULL, groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1,country VARCHAR(50) DEFAULT NULL, dateadded DATE NOT NULL,intaddedby BIGINT DEFAULT 0, invisible SMALLINT DEFAULT 0, reserve1 VARCHAR(500) DEFAULT NULL,reserve2 VARCHAR(500) DEFAULT NULL,PRIMARY KEY  (ids))",
 45        "CREATE TABLE contacts (IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), CONSTRAINT const3 UNIQUE (cnumber, groupsids), cnumber VARCHAR(250) DEFAULT NULL, taxnumber VARCHAR(250), title VARCHAR(250) DEFAULT NULL,groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1,country VARCHAR(50) DEFAULT NULL, prename VARCHAR(250) DEFAULT NULL, cname VARCHAR(250) DEFAULT NULL, street VARCHAR(250) DEFAULT NULL,zip VARCHAR(50) DEFAULT NULL,city VARCHAR(300) DEFAULT NULL, mainphone VARCHAR(250) DEFAULT NULL,fax VARCHAR(250) DEFAULT NULL,mobilephone VARCHAR(250) DEFAULT NULL,workphone VARCHAR(250) DEFAULT NULL,mailaddress VARCHAR(350) DEFAULT NULL,company VARCHAR(250) DEFAULT NULL, department VARCHAR(250) DEFAULT NULL,website VARCHAR(350) DEFAULT NULL,notes VARCHAR(10000),dateadded DATE NOT NULL,isactive SMALLINT DEFAULT 0,iscustomer SMALLINT DEFAULT 0,ismanufacturer SMALLINT DEFAULT 0,issupplier SMALLINT DEFAULT 0,iscompany SMALLINT DEFAULT 0,ismale SMALLINT DEFAULT 0,isenabled SMALLINT DEFAULT 1,intaddedby BIGINT DEFAULT 0, invisible SMALLINT DEFAULT 0, reserve1 VARCHAR(500) DEFAULT NULL,reserve2 VARCHAR(500) DEFAULT NULL,PRIMARY KEY  (ids))",
 46        "CREATE TABLE users (IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), CONSTRAINT const4 UNIQUE (cname, groupsids), cname VARCHAR(250) NOT NULL, groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1, intdefaultaccount BIGINT DEFAULT 1, compsids BIGINT DEFAULT 0, intdefaultstatus BIGINT DEFAULT 1,fullname VARCHAR(250) NOT NULL, password VARCHAR(250) NOT NULL,laf VARCHAR(150) DEFAULT NULL, locale VARCHAR(50) DEFAULT NULL, defcountry VARCHAR(50) DEFAULT '276', mail VARCHAR(50) DEFAULT NULL, language VARCHAR(150) DEFAULT NULL, inthighestright SMALLINT DEFAULT 3,isenabled SMALLINT DEFAULT 1,isrgrouped SMALLINT DEFAULT 0,isloggedin SMALLINT DEFAULT 0,datelastlog DATE DEFAULT NULL, dateadded DATE NOT NULL,intaddedby BIGINT DEFAULT 0,invisible SMALLINT DEFAULT 0,reserve1 VARCHAR(500) DEFAULT NULL,reserve2 VARCHAR(500) DEFAULT NULL,PRIMARY KEY  (ids))",
 47        "CREATE TABLE files (IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), cname VARCHAR(25) UNIQUE NOT NULL, groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1,dateadded DATE NOT NULL,data BLOB(50M) NOT NULL, filesize BIGINT NOT NULL,intaddedby BIGINT DEFAULT 0,invisible SMALLINT DEFAULT 0,PRIMARY KEY  (ids))",
 48        "CREATE TABLE languages(IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), CONSTRAINT const6 UNIQUE (cname, groupsids), cname VARCHAR(250) NOT NULL, groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1, longname VARCHAR(250) UNIQUE NOT NULL, filename VARCHAR(25) NOT NULL REFERENCES files(cname) ON DELETE CASCADE,dateadded DATE NOT NULL,intaddedby BIGINT DEFAULT 0,invisible SMALLINT DEFAULT 0,reserve1 VARCHAR(500) DEFAULT NULL,reserve2 VARCHAR(500) DEFAULT NULL,PRIMARY KEY  (ids))",
 49        "CREATE TABLE favourites (IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),cname VARCHAR(250) NOT NULL, usersids BIGINT REFERENCES users (ids)  ON DELETE CASCADE,groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1,itemsids BIGINT NOT NULL,dateadded DATE NOT NULL,intaddedby BIGINT DEFAULT 0,invisible SMALLINT DEFAULT 0,reserve1 VARCHAR(500) DEFAULT NULL, reserve2 VARCHAR(500) DEFAULT NULL,PRIMARY KEY  (ids))",
 50        "CREATE TABLE accounts(IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), intaccountclass SMALLINT DEFAULT 0, cname VARCHAR(250) NOT NULL, description VARCHAR(250) NOT NULL, taxvalue DOUBLE NOT NULL DEFAULT 0, dateadded DATE NOT NULL,intaddedby BIGINT DEFAULT 0, intparentaccount BIGINT DEFAULT 0, groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1, invisible SMALLINT DEFAULT 0, intaccounttype SMALLINT NOT NULL, intprofitfid SMALLINT NOT NULL, inttaxfid SMALLINT NOT NULL, inttaxuid SMALLINT NOT NULL, frame VARCHAR(25) NOT NULL, hierarchypath VARCHAR(250) DEFAULT NULL, PRIMARY KEY  (ids))",
 51        "CREATE TABLE items (IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), CONSTRAINT const7 UNIQUE (cnumber, inttype), cname VARCHAR(250) NOT NULL, cnumber VARCHAR(250) NOT NULL, description VARCHAR(2500) DEFAULT NULL, groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1, accountsids BIGINT REFERENCES accounts(ids) DEFAULT 1,contactsids BIGINT REFERENCES contacts(ids)  ON DELETE CASCADE, netvalue DOUBLE DEFAULT 0,taxvalue DOUBLE DEFAULT 0, discountvalue DOUBLE DEFAULT 0, shippingvalue DOUBLE DEFAULT 0, datetodo DATE DEFAULT NULL, dateend DATE DEFAULT NULL, intreminders INTEGER DEFAULT 0, inttype SMALLINT DEFAULT 0, dateadded DATE NOT NULL,intaddedby BIGINT DEFAULT 0,invisible SMALLINT DEFAULT 0,intstatus SMALLINT DEFAULT 0, hierarchypath VARCHAR(500) DEFAULT NULL, reserve1 VARCHAR(500) DEFAULT NULL, reserve2 VARCHAR(500) DEFAULT NULL, PRIMARY KEY  (ids))",
 52        "CREATE TABLE subitems (IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), cname VARCHAR(5000) DEFAULT NULL,itemsids BIGINT REFERENCES items(ids)  ON DELETE CASCADE, groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1,originalproductsids BIGINT DEFAULT NULL, countvalue DOUBLE DEFAULT 0 NOT NULL, quantityvalue DOUBLE DEFAULT 0 NOT NULL, measure VARCHAR(250) NOT NULL, linkurl VARCHAR(1000) DEFAULT NULL,description VARCHAR(1000) DEFAULT NULL, internalvalue DOUBLE DEFAULT 0, totalnetvalue DOUBLE DEFAULT 0, totalbrutvalue DOUBLE DEFAULT 0, externalvalue DOUBLE DEFAULT 0, taxpercentvalue DOUBLE DEFAULT 0 NOT NULL,datedelivery DATE DEFAULT NULL, dateadded DATE NOT NULL,intaddedby BIGINT DEFAULT 0,invisible SMALLINT DEFAULT 0,reserve1 VARCHAR(500) DEFAULT NULL,reserve2 VARCHAR(500) DEFAULT NULL,PRIMARY KEY  (ids))",
 53        "CREATE TABLE schedule (IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),cname VARCHAR(250) NOT NULL, groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1,usersids BIGINT REFERENCES users (ids)  ON DELETE CASCADE,itemsids BIGINT REFERENCES items (ids)  ON DELETE CASCADE,stopdate DATE NOT NULL,startdate DATE NOT NULL,nextdate DATE NOT NULL, isdone SMALLINT DEFAULT 0, intervalmonth SMALLINT NOT NULL, dateadded DATE NOT NULL,intaddedby BIGINT DEFAULT 0,invisible SMALLINT DEFAULT 0,reserve1 VARCHAR(500) DEFAULT NULL, reserve2 VARCHAR(500) DEFAULT NULL,PRIMARY KEY  (ids))",
 54        "CREATE TABLE products (IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), CONSTRAINT const8 UNIQUE (cnumber, groupsids), cname VARCHAR(500) NOT NULL, cnumber VARCHAR(250) NOT NULL, description VARCHAR(5000), externalnetvalue DOUBLE DEFAULT 0, internalnetvalue DOUBLE DEFAULT 0, measure VARCHAR(250) NOT NULL, taxids BIGINT REFERENCES tax(ids), manufacturersids BIGINT DEFAULT 0, suppliersids BIGINT DEFAULT 0, groupsids  BIGINT  REFERENCES groups(ids) DEFAULT 1, productgroupsids  BIGINT  REFERENCES productgroups(ids) DEFAULT 1, url VARCHAR(250) DEFAULT NULL,ean VARCHAR(25) DEFAULT NULL, reference VARCHAR(50) DEFAULT NULL,dateadded DATE NOT NULL,intaddedby BIGINT DEFAULT 0,invisible SMALLINT DEFAULT 0, inttype SMALLINT NOT NULL, defaultimage VARCHAR(30) DEFAULT NULL, reserve1 VARCHAR(500) DEFAULT NULL,reserve2 VARCHAR(500) DEFAULT NULL,PRIMARY KEY  (ids))",
 55        "CREATE TABLE productlists (IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), cname VARCHAR(2500) DEFAULT NULL, groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1, description VARCHAR(1000) DEFAULT NULL, dateadded DATE NOT NULL,intaddedby BIGINT DEFAULT 0,invisible SMALLINT DEFAULT 0,reserve1 VARCHAR(500) DEFAULT NULL,reserve2 VARCHAR(500) DEFAULT NULL,PRIMARY KEY  (ids))",
 56        "CREATE TABLE productlistitems (IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), cname VARCHAR(2500) DEFAULT NULL, linkurl VARCHAR(250) DEFAULT NULL, groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1, productlistsids BIGINT REFERENCES productlists(ids) ON DELETE CASCADE,originalproductsids BIGINT REFERENCES products(ids) ON DELETE CASCADE, countvalue DOUBLE DEFAULT 0 NOT NULL, quantityvalue DOUBLE DEFAULT 0 NOT NULL, measure VARCHAR(250) NOT NULL,description VARCHAR(1000) DEFAULT NULL, internalvalue DOUBLE DEFAULT 0, totalnetvalue DOUBLE DEFAULT 0, totalbrutvalue DOUBLE DEFAULT 0, externalvalue DOUBLE DEFAULT 0, taxpercentvalue DOUBLE DEFAULT 0 NOT NULL, dateadded DATE NOT NULL,intaddedby BIGINT DEFAULT 0,invisible SMALLINT DEFAULT 0,reserve1 VARCHAR(500) DEFAULT NULL,reserve2 VARCHAR(500) DEFAULT NULL,PRIMARY KEY  (ids))",
 57        "CREATE TABLE userproperties(IDS BIGINT DEFAULT 1,cname VARCHAR(250) NOT NULL, value VARCHAR(250) NOT NULL, usersids BIGINT NOT NULL, dateadded DATE NOT NULL,intaddedby BIGINT DEFAULT 0, groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1,invisible SMALLINT DEFAULT 0)",
 58        "CREATE TABLE mails(IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),cname VARCHAR(1000) NOT NULL, dateadded DATE NOT NULL,intaddedby BIGINT DEFAULT 0, groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1,invisible SMALLINT DEFAULT 0, usersids BIGINT REFERENCES users(ids) ON DELETE CASCADE, description VARCHAR(5000) NOT NULL, PRIMARY KEY  (ids))",
 59        "CREATE TABLE comps (IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1, cname VARCHAR(250) DEFAULT NULL,state VARCHAR(250) DEFAULT NULL,phoneprefix VARCHAR(250) DEFAULT NULL,business VARCHAR(250) DEFAULT NULL,taxadvisor VARCHAR(250) DEFAULT NULL,city  VARCHAR(250) DEFAULT NULL,taxadvjob VARCHAR(250) DEFAULT NULL,street VARCHAR(250) DEFAULT NULL,stb  VARCHAR(250) DEFAULT NULL,email  VARCHAR(250) DEFAULT NULL,name  VARCHAR(250) DEFAULT NULL,zipcode VARCHAR(250) DEFAULT NULL,phone VARCHAR(250) DEFAULT NULL,firstname  VARCHAR(250) DEFAULT NULL,taxauthority VARCHAR(250) DEFAULT NULL,taxnumber VARCHAR(250) DEFAULT NULL,taxadvmandant VARCHAR(250) DEFAULT NULL,dateadded DATE NOT NULL,ismale SMALLINT DEFAULT 0,isenabled SMALLINT DEFAULT 1,intaddedby BIGINT DEFAULT 0, invisible SMALLINT DEFAULT 0, reserve1 VARCHAR(500) DEFAULT NULL,reserve2 VARCHAR(500) DEFAULT NULL,PRIMARY KEY  (ids))",
 60        "CREATE TABLE expenses (IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1, cnumber VARCHAR(250) DEFAULT NULL, cname VARCHAR(250) DEFAULT NULL, accountsids BIGINT REFERENCES accounts(ids), netvalue DOUBLE DEFAULT 0, taxpercentvalue DOUBLE DEFAULT 0, brutvalue DOUBLE DEFAULT 0, description VARCHAR(5000) DEFAULT NULL,dateadded DATE NOT NULL,intaddedby BIGINT DEFAULT 0, invisible SMALLINT DEFAULT 0, reserve1 VARCHAR(500) DEFAULT NULL,reserve2 VARCHAR(500) DEFAULT NULL,PRIMARY KEY  (ids))",
 61        "CREATE TABLE revenues (IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1, cnumber VARCHAR(250) DEFAULT NULL, cname VARCHAR(250) DEFAULT NULL, accountsids BIGINT REFERENCES accounts(ids), netvalue DOUBLE DEFAULT 0, taxpercentvalue DOUBLE DEFAULT 0, brutvalue DOUBLE DEFAULT 0, description VARCHAR(5000) DEFAULT NULL,dateadded DATE NOT NULL,intaddedby BIGINT DEFAULT 0, invisible SMALLINT DEFAULT 0, reserve1 VARCHAR(500) DEFAULT NULL,reserve2 VARCHAR(500) DEFAULT NULL,PRIMARY KEY  (ids))",
 62        "CREATE TABLE webshops (IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), CONSTRAINT const9 UNIQUE (cname, groupsids), cname VARCHAR(250) NOT NULL,description VARCHAR(750) DEFAULT NULL,groupsids BIGINT DEFAULT 0,dateadded DATE NOT NULL, isrequestcompression SMALLINT DEFAULT 0, isauthenticated SMALLINT DEFAULT 0, username VARCHAR(50) DEFAULT NULL, passw VARCHAR(50) DEFAULT NULL,reserve1 VARCHAR(500) DEFAULT NULL,intaddedby BIGINT DEFAULT 0, interv BIGINT DEFAULT 0, url VARCHAR(500) DEFAULT NULL, reserve2 VARCHAR(500) DEFAULT NULL, PRIMARY KEY  (ids))",
 63        "CREATE TABLE wscontactsmapping (IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), webshopsids BIGINT REFERENCES webshops(ids) ON DELETE CASCADE, cname VARCHAR(250) NOT NULL, groupsids BIGINT REFERENCES groups(ids) ON DELETE CASCADE,contactsids BIGINT REFERENCES contacts(ids) ON DELETE CASCADE, wscontact VARCHAR(250) NOT NULL, dateadded DATE DEFAULT NULL, intaddedby BIGINT DEFAULT 0, invisible SMALLINT DEFAULT 0, reserve1 VARCHAR(500) DEFAULT NULL, CONSTRAINT const10 UNIQUE (wscontact, groupsids, webshopsids), reserve2 VARCHAR(500) DEFAULT NULL, PRIMARY KEY  (ids))",
 64        "CREATE TABLE wsitemsmapping (IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), webshopsids BIGINT REFERENCES webshops(ids) ON DELETE CASCADE, cname VARCHAR(250) NOT NULL, groupsids BIGINT REFERENCES groups(ids) ON DELETE CASCADE,itemsids BIGINT REFERENCES contacts(ids) ON DELETE CASCADE, wsitem VARCHAR(250) NOT NULL, dateadded DATE DEFAULT NULL, intaddedby BIGINT DEFAULT 0, invisible SMALLINT DEFAULT 0, CONSTRAINT const11 UNIQUE (wsitem, groupsids, webshopsids), reserve1 VARCHAR(500) DEFAULT NULL,reserve2 VARCHAR(500) DEFAULT NULL,PRIMARY KEY  (ids))",
 65        //sub tables #2
 66        "CREATE TABLE tablelock (IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),cname VARCHAR(250), rowID BIGINT NOT NULL, usersids BIGINT REFERENCES users(ids) ON DELETE CASCADE,reserve1 VARCHAR(500) DEFAULT NULL,reserve2 VARCHAR(500) DEFAULT NULL,PRIMARY KEY  (ids), CONSTRAINT one_lock UNIQUE(cname, rowid))",
 67        "CREATE TABLE itemstoaccounts (IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), itemsids BIGINT NOT NULL REFERENCES items(ids) ON DELETE CASCADE, accountsids BIGINT REFERENCES accounts(ids) ON DELETE CASCADE,reserve1 VARCHAR(500) DEFAULT NULL,reserve2 VARCHAR(500) DEFAULT NULL,PRIMARY KEY  (ids))",
 68        "CREATE TABLE trashbin (IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),cname VARCHAR(500), rowID BIGINT NOT NULL, description VARCHAR(2500), deleteme SMALLINT DEFAULT 1, reserve1 VARCHAR(500) DEFAULT NULL,reserve2 VARCHAR(500) DEFAULT NULL,PRIMARY KEY  (ids))",
 69        "CREATE TABLE filestocontacts(IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),cname VARCHAR(250) NOT NULL, description VARCHAR(550) DEFAULT NULL, contactsids BIGINT NOT NULL REFERENCES contacts(ids) ON DELETE CASCADE, filename VARCHAR(25) NOT NULL REFERENCES files(cname) ON DELETE CASCADE, intsize BIGINT DEFAULT 0, mimetype VARCHAR(25) DEFAULT NULL, intaddedby BIGINT DEFAULT 0,dateadded DATE NOT NULL,groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1,PRIMARY KEY  (ids))",
 70        "CREATE TABLE templates(IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),cname VARCHAR(250) NOT NULL, format VARCHAR(55) DEFAULT NULL, description VARCHAR(550) DEFAULT NULL, intsize BIGINT DEFAULT 0, mimetype VARCHAR(25) DEFAULT NULL, filename VARCHAR(25) NOT NULL REFERENCES files(cname) ON DELETE CASCADE, intaddedby BIGINT DEFAULT 0,dateadded DATE NOT NULL,groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1,PRIMARY KEY  (ids))",
 71        "CREATE TABLE filestoitems(IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),cname VARCHAR(250) NOT NULL, description VARCHAR(550) DEFAULT NULL, itemsids BIGINT NOT NULL REFERENCES items(ids) ON DELETE CASCADE,filename VARCHAR(25) NOT NULL REFERENCES files(cname) ON DELETE CASCADE,intsize BIGINT DEFAULT 0, mimetype VARCHAR(25) DEFAULT NULL, intaddedby BIGINT DEFAULT 0,dateadded DATE NOT NULL,groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1,PRIMARY KEY  (ids))",
 72        "CREATE TABLE filestoproducts(IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),cname VARCHAR(250) NOT NULL, description VARCHAR(550) DEFAULT NULL, productsids BIGINT NOT NULL REFERENCES products(ids) ON DELETE CASCADE,filename VARCHAR(25) NOT NULL REFERENCES files(cname) ON DELETE CASCADE,intsize BIGINT DEFAULT 0, mimetype VARCHAR(25) DEFAULT NULL, intaddedby BIGINT DEFAULT 0,dateadded DATE NOT NULL,groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1,PRIMARY KEY  (ids))",
 73        "CREATE TABLE plugins(IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),cname VARCHAR(250) NOT NULL, description VARCHAR(550) DEFAULT NULL,filename VARCHAR(25) NOT NULL REFERENCES files(cname) ON DELETE CASCADE,intaddedby BIGINT DEFAULT 0,dateadded DATE NOT NULL,groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1,PRIMARY KEY  (ids))",
 74        "CREATE TABLE pluginstousers(IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),cname VARCHAR(250) NOT NULL, usersids BIGINT NOT NULL, pluginsids BIGINT NOT NULL REFERENCES plugins(ids) ON DELETE CASCADE,intaddedby BIGINT DEFAULT 0,dateadded DATE NOT NULL,groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1,PRIMARY KEY  (ids))",
 75        "CREATE TABLE formatstousers(IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),cname VARCHAR(250) NOT NULL, usersids BIGINT REFERENCES users(ids) ON DELETE CASCADE, inttype SMALLINT  DEFAULT 0,PRIMARY KEY (ids))",
 76        "CREATE TABLE templatestousers(IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),cname VARCHAR(250) NOT NULL, usersids BIGINT REFERENCES users(ids) ON DELETE CASCADE, templatesids BIGINT NOT NULL REFERENCES templates(ids) ON DELETE CASCADE, groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1, PRIMARY KEY (ids))",
 77        "CREATE TABLE addresses(IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1,contactsids BIGINT REFERENCES contacts(ids),title VARCHAR(250) DEFAULT NULL, taxnumber VARCHAR(250),prename VARCHAR(250) DEFAULT NULL, cname VARCHAR(250) DEFAULT NULL, street VARCHAR(250) DEFAULT NULL,zip VARCHAR(50) DEFAULT NULL,city VARCHAR(300) DEFAULT NULL, company VARCHAR(250) DEFAULT NULL, department VARCHAR(250) DEFAULT NULL,country VARCHAR(50) DEFAULT NULL, ismale SMALLINT DEFAULT 0,intaddedby BIGINT DEFAULT 0,dateadded DATE DEFAULT NULL,inttype SMALLINT DEFAULT 0, reserve1 VARCHAR(500) DEFAULT NULL,reserve2 VARCHAR(500) DEFAULT NULL,PRIMARY KEY  (ids))",
 78        "CREATE TABLE reminders(IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),cname VARCHAR(250) NOT NULL, description VARCHAR(550) DEFAULT NULL, stagesids BIGINT NOT NULL,itemsids BIGINT NOT NULL REFERENCES items(ids) ON DELETE CASCADE, extravalue DOUBLE DEFAULT 0, intaddedby BIGINT DEFAULT 0,dateadded DATE NOT NULL,groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1,PRIMARY KEY  (ids))",
 79        "CREATE TABLE stages(IDS BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),cname VARCHAR(250) NOT NULL, description VARCHAR(550) DEFAULT NULL, extravalue DOUBLE DEFAULT 0, intaddedby BIGINT DEFAULT 0,dateadded DATE NOT NULL,groupsids BIGINT  REFERENCES groups(ids) DEFAULT 1,PRIMARY KEY  (ids))",
 80        //Trigger
 81        "CREATE TRIGGER contacts_indexer1 AFTER INSERT ON contacts REFERENCING NEW AS newdata FOR EACH ROW 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)",
 82        "CREATE TRIGGER contacts_indexer2 AFTER UPDATE ON contacts REFERENCING NEW AS newdata FOR EACH ROW DELETE FROM searchindex WHERE dbidentity = 'contacts' AND  rowid = newdata.ids",
 83        "CREATE TRIGGER contacts_indexer3 AFTER UPDATE ON contacts REFERENCING NEW AS newdata FOR EACH ROW 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)",
 84        "CREATE TRIGGER contacts_indexer4 AFTER DELETE ON contacts REFERENCING OLD AS newdata FOR EACH ROW DELETE FROM searchindex WHERE dbidentity = 'contacts' AND  rowid = newdata.ids",
 85        "CREATE TRIGGER contacts_trash1   AFTER UPDATE ON contacts REFERENCING NEW AS newdata FOR EACH ROW INSERT INTO trashbin (deleteme, cname, rowid, description) VALUES (newdata.invisible,'contacts',newdata.ids,newdata.cnumber||' ('|| newdata.cname||')')",
 86        "CREATE TRIGGER contacts_trash2 AFTER DELETE ON contacts REFERENCING OLD AS newdata FOR EACH ROW DELETE FROM trashbin WHERE cname = 'contacts' AND  rowid = newdata.ids",
 87        "CREATE TRIGGER filestocontacts_indexer1 AFTER INSERT ON filestocontacts REFERENCING NEW AS newdata FOR EACH ROW INSERT INTO searchindex (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'filestocontacts',newdata.ids,newdata.cname||' '||newdata.description||' '||newdata.filename)",
 88        "CREATE TRIGGER filestocontacts_indexer2 AFTER UPDATE ON filestocontacts REFERENCING NEW AS newdata FOR EACH ROW DELETE FROM searchindex WHERE dbidentity = 'filestocontacts' AND  rowid = newdata.ids",
 89        "CREATE TRIGGER filestocontacts_indexer3 AFTER UPDATE ON filestocontacts REFERENCING NEW AS newdata FOR EACH ROW INSERT INTO searchindex  (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'filestocontacts',newdata.ids,newdata.cname||' '||newdata.description||' '||newdata.filename)",
 90        "CREATE TRIGGER filestocontacts_indexer4 AFTER DELETE ON filestocontacts REFERENCING OLD AS newdata FOR EACH ROW DELETE FROM searchindex WHERE dbidentity = 'filestocontacts' AND  rowid = newdata.ids",
 91        "CREATE TRIGGER groups_indexer1 AFTER INSERT ON groups REFERENCING NEW AS newdata FOR EACH ROW INSERT INTO searchindex  (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'groups',newdata.ids,newdata.cname||' '||newdata.description||' '||newdata.dateadded)",
 92        "CREATE TRIGGER groups_indexer2 AFTER UPDATE ON groups REFERENCING NEW AS newdata FOR EACH ROW DELETE FROM searchindex WHERE dbidentity = 'groups' AND  rowid = newdata.ids",
 93        "CREATE TRIGGER groups_indexer3 AFTER UPDATE ON groups REFERENCING NEW AS newdata FOR EACH ROW INSERT INTO searchindex  (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'groups',newdata.ids,newdata.cname||' '||newdata.description||' '||newdata.dateadded)",
 94        "CREATE TRIGGER groups_indexer4 AFTER DELETE ON groups REFERENCING OLD AS newdata FOR EACH ROW DELETE FROM searchindex WHERE dbidentity = 'groups' AND  rowid = newdata.ids",
 95        "CREATE TRIGGER items_indexer1 AFTER INSERT ON items REFERENCING NEW AS newdata FOR EACH ROW INSERT INTO searchindex  (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'items',newdata.ids,newdata.cname||' '||newdata.dateadded)",
 96        "CREATE TRIGGER items_indexer2 AFTER UPDATE ON items REFERENCING NEW AS newdata FOR EACH ROW DELETE FROM searchindex WHERE dbidentity = 'items' AND  rowid = newdata.ids",
 97        "CREATE TRIGGER items_indexer3 AFTER UPDATE ON items REFERENCING NEW AS newdata FOR EACH ROW INSERT INTO searchindex  (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'items',newdata.ids,newdata.cname||' '||newdata.dateadded)",
 98        "CREATE TRIGGER items_indexer4 AFTER DELETE ON items REFERENCING OLD AS newdata FOR EACH ROW DELETE FROM searchindex WHERE dbidentity = 'items' AND  rowid = newdata.ids",
 99        "CREATE TRIGGER items_trash2 AFTER DELETE ON items REFERENCING OLD AS newdata FOR EACH ROW DELETE FROM trashbin WHERE cname = 'items' AND  rowid = newdata.ids",
100        "CREATE TRIGGER items_trash1 AFTER UPDATE ON items REFERENCING NEW AS newdata FOR EACH ROW INSERT INTO trashbin (deleteme, cname, rowid, description) VALUES (newdata.invisible,'items',newdata.ids,newdata.cname)",
101        "CREATE TRIGGER expenses_indexer1 AFTER INSERT ON expenses REFERENCING NEW AS newdata FOR EACH ROW INSERT INTO searchindex  (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'expenses',newdata.ids,newdata.cname||' '||newdata.dateadded)",
102        "CREATE TRIGGER expenses_indexer2 AFTER UPDATE ON expenses REFERENCING NEW AS newdata FOR EACH ROW DELETE FROM searchindex WHERE dbidentity = 'expenses' AND  rowid = newdata.ids",
103        "CREATE TRIGGER expenses_indexer3 AFTER UPDATE ON expenses REFERENCING NEW AS newdata FOR EACH ROW INSERT INTO searchindex  (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'expenses',newdata.ids,newdata.cname||' '||newdata.dateadded)",
104        "CREATE TRIGGER expenses_indexer4 AFTER DELETE ON expenses REFERENCING OLD AS newdata FOR EACH ROW DELETE FROM searchindex WHERE dbidentity = 'expenses' AND  rowid = newdata.ids",
105        "CREATE TRIGGER expenses_trash2 AFTER DELETE ON expenses REFERENCING OLD AS newdata FOR EACH ROW DELETE FROM trashbin WHERE cname = 'expenses' AND  rowid = newdata.ids",
106        "CREATE TRIGGER expenses_trash1 AFTER UPDATE ON expenses REFERENCING NEW AS newdata FOR EACH ROW INSERT INTO trashbin (deleteme, cname, rowid, description) VALUES (newdata.invisible,'expenses',newdata.ids,newdata.cname)",
107        "CREATE TRIGGER revenues_indexer1 AFTER INSERT ON revenues REFERENCING NEW AS newdata FOR EACH ROW INSERT INTO searchindex  (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'revenues',newdata.ids,newdata.cname||' '||newdata.dateadded)",
108        "CREATE TRIGGER revenues_indexer2 AFTER UPDATE ON revenues REFERENCING NEW AS newdata FOR EACH ROW DELETE FROM searchindex WHERE dbidentity = 'revenues' AND  rowid = newdata.ids",
109        "CREATE TRIGGER revenues_indexer3 AFTER UPDATE ON revenues REFERENCING NEW AS newdata FOR EACH ROW INSERT INTO searchindex  (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'revenues',newdata.ids,newdata.cname||' '||newdata.dateadded)",
110        "CREATE TRIGGER revenues_indexer4 AFTER DELETE ON revenues REFERENCING OLD AS newdata FOR EACH ROW DELETE FROM searchindex WHERE dbidentity = 'revenues' AND  rowid = newdata.ids",
111        "CREATE TRIGGER revenues_trash2 AFTER DELETE ON revenues REFERENCING OLD AS newdata FOR EACH ROW DELETE FROM trashbin WHERE cname = 'revenues' AND  rowid = newdata.ids",
112        "CREATE TRIGGER revenues_trash1 AFTER UPDATE ON revenues REFERENCING NEW AS newdata FOR EACH ROW INSERT INTO trashbin (deleteme, cname, rowid, description) VALUES (newdata.invisible,'revenues',newdata.ids,newdata.cname)",
113        "CREATE TRIGGER subitems_indexer1 AFTER INSERT ON subitems REFERENCING NEW AS newdata FOR EACH ROW INSERT INTO searchindex  (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'subitems',newdata.ids,newdata.cname||' '||newdata.description||' '||newdata.dateadded)",
114        "CREATE TRIGGER subitems_indexer2 AFTER UPDATE ON subitems REFERENCING NEW AS newdata FOR EACH ROW DELETE FROM searchindex WHERE dbidentity = 'subitems' AND  rowid = newdata.ids",
115        "CREATE TRIGGER subitems_indexer3 AFTER UPDATE ON subitems REFERENCING NEW AS newdata FOR EACH ROW INSERT INTO searchindex  (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'subitems',newdata.ids,newdata.cname||' '||newdata.description||' '||newdata.dateadded)",
116        "CREATE TRIGGER subitems_indexer4 AFTER DELETE ON subitems REFERENCING OLD AS newdata FOR EACH ROW DELETE FROM searchindex WHERE dbidentity = 'subitems' AND  rowid = newdata.ids",
117        "CREATE TRIGGER products_indexer1 AFTER INSERT ON products REFERENCING NEW AS newdata FOR EACH ROW INSERT INTO searchindex  (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'products',newdata.ids,newdata.cname||' '||newdata.cnumber||' '||newdata.description||' '||newdata.dateadded)",
118        "CREATE TRIGGER products_indexer2 AFTER UPDATE ON products REFERENCING NEW AS newdata FOR EACH ROW DELETE FROM searchindex WHERE dbidentity = 'products' AND  rowid = newdata.ids",
119        "CREATE TRIGGER products_indexer3 AFTER UPDATE ON products REFERENCING NEW AS newdata FOR EACH ROW INSERT INTO searchindex  (groupsids, dbidentity, rowid, text) VALUES (newdata.groupsids,'products',newdata.ids,newdata.cname||' '||newdata.cnumber||' '||newdata.description||' '||newdata.dateadded)",
120        "CREATE TRIGGER products_indexer4 AFTER DELETE ON products REFERENCING OLD AS newdata FOR EACH ROW DELETE FROM searchindex WHERE dbidentity = 'products' AND  rowid = newdata.ids",
121        "CREATE TRIGGER products_trash1 AFTER UPDATE ON products REFERENCING NEW AS newdata FOR EACH ROW INSERT INTO trashbin (deleteme, cname, rowid, description) VALUES (newdata.invisible, 'products',newdata.ids,newdata.cnumber||' ('|| newdata.cname||')')",
122        "CREATE TRIGGER products_trash2 AFTER DELETE ON products REFERENCING OLD AS newdata FOR EACH ROW DELETE FROM trashbin WHERE cname = 'products' AND  rowid = newdata.ids",
123        "CREATE TRIGGER thrash_handler1 AFTER INSERT ON trashbin FOR EACH STATEMENT DELETE FROM trashbin WHERE deleteme = 0",
124        "CREATE TRIGGER thrash_handler2 AFTER INSERT ON trashbin FOR EACH STATEMENT 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) ) )",};
125    public static String[] MYSQL_STRUCTURE = new String[]{
126        "CREATE TABLE accounts ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, intaccountclass SMALLINT DEFAULT 0, cname VARCHAR(250) NOT NULL, description VARCHAR(250) NOT NULL, taxvalue DOUBLE NOT NULL DEFAULT 0, dateadded DATE NOT NULL, intaddedby BIGINT(20) DEFAULT 0, intparentaccount BIGINT(20) DEFAULT 0, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1, invisible SMALLINT DEFAULT 0, intaccounttype SMALLINT NOT NULL, intprofitfid SMALLINT NOT NULL, inttaxfid SMALLINT NOT NULL, inttaxuid SMALLINT NOT NULL, frame VARCHAR(25) NOT NULL, hierarchypath VARCHAR(250) DEFAULT NULL ) ENGINE=myisam DEFAULT CHARSET=utf8",
127        "CREATE TABLE addresses ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1, contactsids BIGINT(20) UNSIGNED NOT NULL, title VARCHAR(250) DEFAULT NULL, taxnumber VARCHAR(250) DEFAULT NULL, prename VARCHAR(250) DEFAULT NULL, cname VARCHAR(250) DEFAULT NULL, street VARCHAR(250) DEFAULT NULL, zip VARCHAR(50) DEFAULT NULL, city VARCHAR(300) DEFAULT NULL, company VARCHAR(250) DEFAULT NULL, department VARCHAR(250) DEFAULT NULL, country VARCHAR(50) DEFAULT NULL, ismale SMALLINT DEFAULT 0, intaddedby BIGINT(20) DEFAULT 0, dateadded DATE DEFAULT NULL, inttype SMALLINT DEFAULT 0, reserve1 VARCHAR(500) DEFAULT NULL, reserve2 VARCHAR(500) DEFAULT NULL ) ENGINE=myisam DEFAULT CHARSET=utf8",
128        "CREATE TABLE comps ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1, cname VARCHAR(250) DEFAULT NULL, state VARCHAR(250) DEFAULT NULL, phoneprefix VARCHAR(250) DEFAULT NULL, business VARCHAR(250) DEFAULT NULL, taxadvisor VARCHAR(250) DEFAULT NULL, city VARCHAR(250) DEFAULT NULL, taxadvjob VARCHAR(250) DEFAULT NULL, street VARCHAR(250) DEFAULT NULL, stb VARCHAR(250) DEFAULT NULL, email VARCHAR(250) DEFAULT NULL, name VARCHAR(250) DEFAULT NULL, zipcode VARCHAR(250) DEFAULT NULL, phone VARCHAR(250) DEFAULT NULL, firstname VARCHAR(250) DEFAULT NULL, taxauthority VARCHAR(250) DEFAULT NULL, taxnumber VARCHAR(250) DEFAULT NULL, taxadvmandant VARCHAR(250) DEFAULT NULL, dateadded DATE NOT NULL, ismale SMALLINT DEFAULT 0, isenabled SMALLINT DEFAULT 1, intaddedby BIGINT(20) DEFAULT 0, invisible SMALLINT DEFAULT 0, reserve1 VARCHAR(500) DEFAULT NULL, reserve2 VARCHAR(500) DEFAULT NULL ) ENGINE=myisam DEFAULT CHARSET=utf8",
129        "CREATE TABLE contacts ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, cnumber VARCHAR(250) DEFAULT NULL, taxnumber VARCHAR(250), title VARCHAR(250) DEFAULT NULL, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1, country VARCHAR(50) DEFAULT NULL, prename VARCHAR(250) DEFAULT NULL, cname VARCHAR(250) DEFAULT NULL, street VARCHAR(250) DEFAULT NULL, zip VARCHAR(50) DEFAULT NULL, city VARCHAR(300) DEFAULT NULL, mainphone VARCHAR(250) DEFAULT NULL, fax VARCHAR(250) DEFAULT NULL, mobilephone VARCHAR(250) DEFAULT NULL, workphone VARCHAR(250) DEFAULT NULL, mailaddress VARCHAR(350) DEFAULT NULL, company VARCHAR(250) DEFAULT NULL, department VARCHAR(250) DEFAULT NULL, website VARCHAR(350) DEFAULT NULL, notes VARCHAR(10000), dateadded DATE NOT NULL, isactive SMALLINT DEFAULT 0, iscustomer SMALLINT DEFAULT 0, ismanufacturer SMALLINT DEFAULT 0, issupplier SMALLINT DEFAULT 0, iscompany SMALLINT DEFAULT 0, ismale SMALLINT DEFAULT 0, isenabled SMALLINT DEFAULT 1, intaddedby BIGINT(20) DEFAULT 0, invisible SMALLINT DEFAULT 0, reserve1 VARCHAR(500) DEFAULT NULL, reserve2 VARCHAR(500) DEFAULT NULL ) ENGINE=myisam DEFAULT CHARSET=utf8",
130        "CREATE TABLE countries ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(250) NOT NULL, iso SMALLINT NOT NULL, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1, reserve1 VARCHAR(500) DEFAULT NULL, reserve2 VARCHAR(500) DEFAULT NULL ) ENGINE=myisam DEFAULT CHARSET=utf8",
131        "CREATE TABLE expenses ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1, cnumber VARCHAR(250) DEFAULT NULL, cname VARCHAR(250) DEFAULT NULL, accountsids BIGINT(20) UNSIGNED NOT NULL, netvalue DOUBLE DEFAULT 0, taxpercentvalue DOUBLE DEFAULT 0, brutvalue DOUBLE DEFAULT 0, description VARCHAR(5000) DEFAULT NULL, dateadded DATE NOT NULL, intaddedby BIGINT(20) DEFAULT 0, invisible SMALLINT DEFAULT 0, reserve1 VARCHAR(500) DEFAULT NULL, reserve2 VARCHAR(500) DEFAULT NULL ) ENGINE=myisam DEFAULT CHARSET=utf8",
132        "CREATE TABLE favourites ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(250) NOT NULL, usersids BIGINT(20) UNSIGNED NOT NULL, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1, itemsids BIGINT(20) UNSIGNED NOT NULL NOT NULL, dateadded DATE NOT NULL, intaddedby BIGINT(20) DEFAULT 0, invisible SMALLINT DEFAULT 0, reserve1 VARCHAR(500) DEFAULT NULL, reserve2 VARCHAR(500) DEFAULT NULL ) ENGINE=myisam DEFAULT CHARSET=utf8",
133        "CREATE TABLE files ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(25) NOT NULL, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1, dateadded DATE NOT NULL, DATA BLOB(5242880) NOT NULL, filesize BIGINT(20) NOT NULL, intaddedby BIGINT(20) DEFAULT 0, invisible SMALLINT DEFAULT 0 ) ENGINE=myisam DEFAULT CHARSET=utf8",
134        "CREATE TABLE filestocontacts ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(250) NOT NULL, description VARCHAR(550) DEFAULT NULL, contactsids BIGINT(20) UNSIGNED NOT NULL NOT NULL, filename VARCHAR(25) NOT NULL, intsize BIGINT(20) DEFAULT 0, mimetype VARCHAR(25) DEFAULT NULL, intaddedby BIGINT(20) DEFAULT 0, dateadded DATE NOT NULL, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1 ) ENGINE=myisam DEFAULT CHARSET=utf8",
135        "CREATE TABLE filestoitems ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(250) NOT NULL, description VARCHAR(550) DEFAULT NULL, itemsids BIGINT(20) UNSIGNED NOT NULL NOT NULL, filename VARCHAR(25) NOT NULL, intsize BIGINT(20) DEFAULT 0, mimetype VARCHAR(25) DEFAULT NULL, intaddedby BIGINT(20) DEFAULT 0, dateadded DATE NOT NULL, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1 ) ENGINE=myisam DEFAULT CHARSET=utf8",
136        "CREATE TABLE filestoproducts ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(250) NOT NULL, description VARCHAR(550) DEFAULT NULL, productsids BIGINT(20) UNSIGNED NOT NULL NOT NULL, filename VARCHAR(25) NOT NULL, intsize BIGINT(20) DEFAULT 0, mimetype VARCHAR(25) DEFAULT NULL, intaddedby BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, dateadded DATE NOT NULL, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1 ) ENGINE=myisam DEFAULT CHARSET=utf8",
137        "CREATE TABLE formatstousers ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(250) NOT NULL, usersids BIGINT(20) UNSIGNED NOT NULL, inttype SMALLINT DEFAULT 0 ) ENGINE=myisam DEFAULT CHARSET=utf8",
138        "CREATE TABLE globalsettings ( ids BIGINT(20) DEFAULT 1, cname VARCHAR(250) NOT NULL, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1, VALUE VARCHAR(250) NOT NULL ) ENGINE=myisam DEFAULT CHARSET=utf8",
139        "CREATE TABLE groups ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(250) NOT NULL, description VARCHAR(750) DEFAULT NULL, defaults VARCHAR(250) DEFAULT NULL, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, dateadded DATE NOT NULL, reserve1 VARCHAR(500) DEFAULT NULL, intaddedby BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, hierarchypath VARCHAR(500) DEFAULT NULL, reserve2 VARCHAR(500) DEFAULT NULL ) ENGINE=myisam DEFAULT CHARSET=utf8",
140        "CREATE TABLE history ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(250) NOT NULL, username VARCHAR(250) NOT NULL, dbidentity VARCHAR(25) NOT NULL, intitem SMALLINT NOT NULL, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, dateadded DATE NOT NULL, intaddedby BIGINT(20) DEFAULT 0, reserve1 VARCHAR(500) DEFAULT NULL, reserve2 VARCHAR(500) DEFAULT NULL ) ENGINE=myisam DEFAULT CHARSET=utf8",
141        "CREATE TABLE items ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(250) NOT NULL, cnumber VARCHAR(250) NOT NULL, description VARCHAR(2500) DEFAULT NULL, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1, accountsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1, contactsids BIGINT(20) UNSIGNED NOT NULL, netvalue DOUBLE DEFAULT 0, taxvalue DOUBLE DEFAULT 0, discountvalue DOUBLE DEFAULT 0, shippingvalue DOUBLE DEFAULT 0, datetodo DATE DEFAULT NULL, dateend DATE DEFAULT NULL, intreminders INTEGER DEFAULT 0, inttype SMALLINT DEFAULT 0, dateadded DATE NOT NULL, intaddedby BIGINT(20) DEFAULT 0, invisible SMALLINT DEFAULT 0, intstatus SMALLINT DEFAULT 0, hierarchypath VARCHAR(500) DEFAULT NULL, reserve1 VARCHAR(500) DEFAULT NULL, reserve2 VARCHAR(500) DEFAULT NULL ) ENGINE=myisam DEFAULT CHARSET=utf8",
142        "CREATE TABLE itemstoaccounts ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, itemsids BIGINT(20) UNSIGNED NOT NULL NOT NULL, accountsids BIGINT(20) UNSIGNED NOT NULL, reserve1 VARCHAR(500) DEFAULT NULL, reserve2 VARCHAR(500) DEFAULT NULL ) ENGINE=myisam DEFAULT CHARSET=utf8",
143        "CREATE TABLE languages ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(250) NOT NULL, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1, longname VARCHAR(250) NOT NULL, filename VARCHAR(25) NOT NULL, dateadded DATE NOT NULL, intaddedby BIGINT(20) DEFAULT 0, invisible SMALLINT DEFAULT 0, reserve1 VARCHAR(500) DEFAULT NULL, reserve2 VARCHAR(500) DEFAULT NULL ) ENGINE=myisam DEFAULT CHARSET=utf8",
144        "CREATE TABLE mails ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(1000) NOT NULL, dateadded DATE NOT NULL, intaddedby BIGINT(20) DEFAULT 0, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1, invisible SMALLINT DEFAULT 0, usersids BIGINT(20) UNSIGNED NOT NULL, description VARCHAR(5000) NOT NULL ) ENGINE=myisam DEFAULT CHARSET=utf8",
145        "CREATE TABLE plugins ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(250) NOT NULL, description VARCHAR(550) DEFAULT NULL, filename VARCHAR(25) NOT NULL, intaddedby BIGINT(20) DEFAULT 0, dateadded DATE NOT NULL, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1 ) ENGINE=myisam DEFAULT CHARSET=utf8",
146        "CREATE TABLE pluginstousers ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(250) NOT NULL, usersids BIGINT(20) UNSIGNED NOT NULL NOT NULL, pluginsids BIGINT(20) UNSIGNED NOT NULL NOT NULL, intaddedby BIGINT(20) DEFAULT 0, dateadded DATE NOT NULL, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1 ) ENGINE=myisam DEFAULT CHARSET=utf8",
147        "CREATE TABLE productgroups ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(250) NOT NULL, description VARCHAR(750) DEFAULT NULL, defaults VARCHAR(250) DEFAULT NULL, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, productgroupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, dateadded DATE NOT NULL, hierarchypath VARCHAR(500) DEFAULT NULL, reserve1 VARCHAR(500) DEFAULT NULL, intaddedby BIGINT(20) DEFAULT 0, reserve2 VARCHAR(500) DEFAULT NULL ) ENGINE=myisam DEFAULT CHARSET=utf8",
148        "CREATE TABLE productlistitems ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(2500) DEFAULT NULL, linkurl VARCHAR(250) DEFAULT NULL, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1, productlistsids BIGINT(20) UNSIGNED NOT NULL, originalproductsids BIGINT(20) UNSIGNED NOT NULL, countvalue DOUBLE NOT NULL DEFAULT 0, quantityvalue DOUBLE NOT NULL DEFAULT 0, measure VARCHAR(250) NOT NULL, description VARCHAR(1000) DEFAULT NULL, internalvalue DOUBLE DEFAULT 0, totalnetvalue DOUBLE DEFAULT 0, totalbrutvalue DOUBLE DEFAULT 0, externalvalue DOUBLE DEFAULT 0, taxpercentvalue DOUBLE NOT NULL DEFAULT 0, dateadded DATE NOT NULL, intaddedby BIGINT(20) DEFAULT 0, invisible SMALLINT DEFAULT 0, reserve1 VARCHAR(500) DEFAULT NULL, reserve2 VARCHAR(500) DEFAULT NULL ) ENGINE=myisam DEFAULT CHARSET=utf8",
149        "CREATE TABLE productlists ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(2500) DEFAULT NULL, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1, description VARCHAR(1000) DEFAULT NULL, dateadded DATE NOT NULL, intaddedby BIGINT(20) DEFAULT 0, invisible SMALLINT DEFAULT 0, reserve1 VARCHAR(500) DEFAULT NULL, reserve2 VARCHAR(500) DEFAULT NULL ) ENGINE=myisam DEFAULT CHARSET=utf8",
150        "CREATE TABLE products ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(500) NOT NULL, cnumber VARCHAR(250) NOT NULL, description VARCHAR(5000), externalnetvalue DOUBLE DEFAULT 0, internalnetvalue DOUBLE DEFAULT 0, measure VARCHAR(250) NOT NULL, taxids BIGINT(20) UNSIGNED NOT NULL, manufacturersids BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, suppliersids BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1, productgroupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1, url VARCHAR(250) DEFAULT NULL, ean VARCHAR(25) DEFAULT NULL, reference VARCHAR(50) DEFAULT NULL, dateadded DATE NOT NULL, intaddedby BIGINT(20) DEFAULT 0, invisible SMALLINT DEFAULT 0, inttype SMALLINT NOT NULL, defaultimage VARCHAR(30) DEFAULT NULL, reserve1 VARCHAR(500) DEFAULT NULL, reserve2 VARCHAR(500) DEFAULT NULL ) ENGINE=myisam DEFAULT CHARSET=utf8",
151        "CREATE TABLE reminders ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(250) NOT NULL, description VARCHAR(550) DEFAULT NULL, stagesids BIGINT(20) UNSIGNED NOT NULL, itemsids BIGINT(20) UNSIGNED NOT NULL, extravalue DOUBLE DEFAULT 0, intaddedby BIGINT(20) DEFAULT 0, dateadded DATE NOT NULL, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1 ) ENGINE=myisam DEFAULT CHARSET=utf8",
152        "CREATE TABLE revenues ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1, cnumber VARCHAR(250) DEFAULT NULL, cname VARCHAR(250) DEFAULT NULL, accountsids BIGINT(20) UNSIGNED NOT NULL, netvalue DOUBLE DEFAULT 0, taxpercentvalue DOUBLE DEFAULT 0, brutvalue DOUBLE DEFAULT 0, description VARCHAR(5000) DEFAULT NULL, dateadded DATE NOT NULL, intaddedby BIGINT(20) DEFAULT 0, invisible SMALLINT DEFAULT 0, reserve1 VARCHAR(500) DEFAULT NULL, reserve2 VARCHAR(500) DEFAULT NULL ) ENGINE=myisam DEFAULT CHARSET=utf8",
153        "CREATE TABLE schedule ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(250) NOT NULL, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1, usersids BIGINT(20) UNSIGNED NOT NULL, itemsids BIGINT(20) UNSIGNED NOT NULL, stopdate DATE NOT NULL, startdate DATE NOT NULL, nextdate DATE NOT NULL, isdone SMALLINT DEFAULT 0, intervalmonth SMALLINT NOT NULL, dateadded DATE NOT NULL, intaddedby BIGINT(20) DEFAULT 0, invisible SMALLINT DEFAULT 0, reserve1 VARCHAR(500) DEFAULT NULL, reserve2 VARCHAR(500) DEFAULT NULL ) ENGINE=myisam DEFAULT CHARSET=utf8",
154        "CREATE TABLE searchindex ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, dbidentity VARCHAR(25) NOT NULL, groupsids BIGINT(20) UNSIGNED NOT NULL, rowid BIGINT(20) NOT NULL, TEXT VARCHAR(5000) DEFAULT NULL ) ENGINE=myisam DEFAULT CHARSET=utf8",
155        "CREATE TABLE stages ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(250) NOT NULL, description VARCHAR(550) DEFAULT NULL, extravalue DOUBLE DEFAULT 0, intaddedby BIGINT(20) DEFAULT 0, dateadded DATE NOT NULL, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1 ) ENGINE=myisam DEFAULT CHARSET=utf8",
156        "CREATE TABLE subitems ( ids BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(5000) DEFAULT NULL, itemsids BIGINT(20) UNSIGNED NOT NULL, groupsids BIGINT(20) UNSIGNED NOT NULL DEFAULT 1, originalproductsids BIGINT(20) UNSIGNED DEFAULT NULL, countvalue DOUBLE NOT NULL DEFAULT 0, quantityvalue DOUBLE NOT NULL DEFAULT 0, measure VARCHAR(250) NOT NULL, linkurl VARCHAR(1000) DEFAULT NULL, description VARCHAR(1000) DEFAULT NULL, internalvalue DOUBLE DEFAULT 0, totalnetvalue DOUBL

Large files files are truncated, but you can click here to view the full file