DROP DATABASE ISMI;
CREATE DATABASE ISMI;
GRANT ALL ON ISMI.* TO ISMI;
USE ISMI;

CREATE TABLE TDATTRIBUTE (FID BIGINT NOT NULL, FINDEX INTEGER, FDEPLOYTYPE VARCHAR(255), FVALUE TEXT, FNAME VARCHAR(255), FCONTENTTYPE VARCHAR(255), FTDITEM_FID BIGINT, FTDRELATION_FID BIGINT, PRIMARY KEY (FID));
CREATE TABLE TDITEM (FID BIGINT NOT NULL, FOBJECTCLASS VARCHAR(255), FNAME VARCHAR(255), FOWNVALUE TEXT, FCONTENTTYPE VARCHAR(255), PRIMARY KEY (FID));
CREATE TABLE TDRELATION (FID BIGINT NOT NULL, FLABEL VARCHAR(255), FTARGET BIGINT, FSOURCE BIGINT, PRIMARY KEY (FID));
CREATE TABLE TMDEFINITION (IBOXFID INTEGER NOT NULL, FINDEX INTEGER, FOBJECTCLASS VARCHAR(255), FMAINATTRIBUTE VARCHAR(255), PRIMARY KEY (IBOXFID));

CREATE TABLE TMLABEL (FID BIGINT NOT NULL, FINDEX INTEGER, FDEPLOYTYPE VARCHAR(255), FOBJECTCLASS VARCHAR(255), FRELATIONID BIGINT, FNAME VARCHAR(255), FCONTENTTYPE VARCHAR(255), FLABELTOOBJECT TINYINT, PRIMARY KEY (FID));
CREATE TABLE TMLABELLANGUAGE (FID BIGINT NOT NULL, FTMLABEL_FID BIGINT, FDISPLAYLABEL VARCHAR(255), FLANGUAGE VARCHAR(255), FHELPTEXT TEXT, PRIMARY KEY (FID));

CREATE TABLE TMRELATION (FID BIGINT NOT NULL, FFILTER TINYINT, FLABELTOFILTER VARCHAR(255), FLABEL VARCHAR(255), FTARGET VARCHAR(255), FMAINLABELSOURCE VARCHAR(255), FVALUELABEL TEXT, FDEPLOYMETHOD VARCHAR(255), FMAINLABELTARGET VARCHAR(255), FSOURCE VARCHAR(255), PRIMARY KEY (FID));
CREATE TABLE TMSUGGESTVALUE (FID BIGINT NOT NULL, FTMLABEL_FID BIGINT, FVALUE TEXT, PRIMARY KEY (FID));
CREATE TABLE TSEQUENCE (FNAME VARCHAR(255), FKEY BIGINT(20));
-- CREATE TABLE TSEQUENCETABLE (NAME VARCHAR(255), ID BIGINT(20));
CREATE TABLE TVARIABLE (IBOXFID INTEGER NOT NULL, FVALUE TEXT, FNAME VARCHAR(255), PRIMARY KEY (IBOXFID));

CREATE TABLE TROLE (FROLENAME VARCHAR(255) NOT NULL, FDESCRIPTION VARCHAR(255), PRIMARY KEY (FROLENAME));
CREATE TABLE TUSER (FUSERNAME VARCHAR(255) NOT NULL, FPASSWORD VARCHAR(255), FOTHER VARCHAR(255), FDEFINITIONSEDITOR TINYINT, FANSWER VARCHAR(255), FLASTNAME VARCHAR(255), FENTITIESEDITOR TINYINT, FADMINISTRATOR TINYINT, FDESCRIPTION VARCHAR(255), FEMAIL VARCHAR(255), FNAVIGATOR TINYINT, FNAME VARCHAR(255), FQUESTION VARCHAR(255), PRIMARY KEY (FUSERNAME));
CREATE TABLE TUSER_ROLE (FUSERNAME VARCHAR(255), FROLENAME VARCHAR(255));


INSERT INTO TSEQUENCE (FNAME, FKEY) VALUES ('default', 100);
INSERT INTO TSEQUENCE (FNAME, FKEY) VALUES ('item', 100);
INSERT INTO TSEQUENCE (FNAME, FKEY) VALUES ('attribute', 100);
INSERT INTO TSEQUENCE (FNAME, FKEY) VALUES ('relation', 100);
INSERT INTO TSEQUENCE (FNAME, FKEY) VALUES ('label', 100);
INSERT INTO TSEQUENCE (FNAME, FKEY) VALUES ('labellanguage', 100);
INSERT INTO TSEQUENCE (FNAME, FKEY) VALUES ('modelrelation', 100);
INSERT INTO TSEQUENCE (FNAME, FKEY) VALUES ('suggestvalue', 100);

-- INSERT INTO TSEQUENCETABLE (NAME, ID) VALUES ('attribute', 100);
-- INSERT INTO TSEQUENCETABLE (NAME, ID) VALUES ('suggestvalue', 100);

ALTER TABLE TDATTRIBUTE CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE TDITEM CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE TDRELATION CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE TMDEFINITION CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE TMLABEL CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE TMRELATION CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE TMSUGGESTVALUE CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE TROLE CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE TSEQUENCE CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE TUSER CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE TVARIABLE CHARACTER SET utf8 COLLATE utf8_general_ci;