• Home
• net.sf.tsl2nano
• tsl2.nano.h5
• 2.5.1
• source code

×

Project download

Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $

You can buy this project and download/modify it how often you want.

no.tsl2.nano.h5.2.5.1.source-code.anyway.sql Maven / Gradle / Ivy

-- Anyway database - generic database for several use cases (Thomas Schneider / 2012)
-- ADMIN table"s" are in anyway-admin.sql
-- SQL-92 (Transformation: BLOB --> LONGVARBINARY)

-- H2 Version 1: quoting fields seems not to work!
-- H2 Version 2: the fields "value" and "end" must be quoted! But standard queries without quotes wont be correct after that change
-- quote all names:(^[ \t]+(CONSTRAINT|ON)?| TABLE| COLUMN| INDEX| KEY |REFERENCES \w+)([\s\(]*)([\w.]+)([ \t]*|\))   -> $1$3"$4"$5
-- the quoting does not work in eclipse, there is a replace failure on group 4

-- Hsqldb 2.7.0: if you quote everything, all queries must be quoted, too! But if you use uppercase, then not!

CREATE TABLE "ACCOUNT" (
                "ID" INTEGER NOT NULL,
                "NAME" VARCHAR(64) NOT NULL,
                "BIC" VARCHAR(12) NOT NULL,
                "IBAN" VARCHAR(22) NOT NULL,
                CONSTRAINT "ID" PRIMARY KEY ("ID")
);


CREATE TABLE "DIGITAL" (
                "ID" INTEGER NOT NULL,
                "LOCATION" INTEGER NOT NULL,
                "NAME" VARCHAR(64) NOT NULL,
                "VALUE" VARCHAR(64) NOT NULL,
                CONSTRAINT "IDX_DIGITAL" PRIMARY KEY ("ID")
);

CREATE TABLE "ORGANISATION" (
                "ID" INTEGER NOT NULL,
                "NAME" VARCHAR(64) NOT NULL,
                "DESCRIPTION" VARCHAR(256),
                "ICON" BLOB,
                CONSTRAINT "IDX_ORGANISATION" PRIMARY KEY ("ID")
);
COMMENT ON TABLE "ORGANISATION" IS 'ROOT DEFINITION - MAY BE USED FOR DIFFERENT CLIENTS';


CREATE UNIQUE INDEX "ORGANISATION_IDX"
 ON "ORGANISATION"
 ( "NAME" );

CREATE TABLE "CLASSIFICATION" (
                "ID" INTEGER NOT NULL,
                "VALUE" INTEGER NOT NULL,
                "NAME" VARCHAR(64) NOT NULL,
                "ICON" BLOB,
                CONSTRAINT "IDX_CLASSIFICATION" PRIMARY KEY ("ID")
);
COMMENT ON TABLE "CLASSIFICATION" IS 'ITEMS LEVEL OR STATUS';


CREATE INDEX "CLASSIFICATION_IDX"
 ON "CLASSIFICATION"
 ( "NAME" );

CREATE TABLE "CATEGORY" (
                "ID" INTEGER NOT NULL,
                "NAME" VARCHAR(64) NOT NULL,
                "ICON" BLOB,
                CONSTRAINT "IDX_CATEGORY" PRIMARY KEY ("ID")
);
COMMENT ON TABLE "CATEGORY" IS 'COMBINES A GROUP OF AREAS. MAY BE USELESS ON SOME USE CASES';


CREATE INDEX "CATEGORY_IDX"
 ON "CATEGORY"
 ( "NAME" );

CREATE TABLE "AREA" (
                "ID" INTEGER NOT NULL,
                "CATEGORY" INTEGER NOT NULL,
                "NAME" VARCHAR(64) NOT NULL,
                "ICON" BLOB,
                CONSTRAINT "IDX_AREA" PRIMARY KEY ("ID")
);
COMMENT ON TABLE "AREA" IS 'AN AREA COMBINES A GROUP OF ITEM TYPES';


CREATE INDEX "AREA_IDX"
 ON "AREA"
 ( "NAME" );

CREATE TABLE "TYPE" (
                "ID" INTEGER NOT NULL,
                "AREA" INTEGER NOT NULL,
                "NAME" VARCHAR(64) NOT NULL,
                "ICON" BLOB,
                CONSTRAINT "IDX_TYPE" PRIMARY KEY ("ID")
);
COMMENT ON TABLE "TYPE" IS 'ITEM TYPE';


CREATE INDEX "TYPE_IDX"
 ON "TYPE"
 ( "NAME" );

CREATE TABLE "ITEM" (
                "ID" INTEGER NOT NULL,
                "ORGA" INTEGER NOT NULL,
                "CLASS" INTEGER NOT NULL,
                "TYPE" INTEGER NOT NULL,
                "NAME" VARCHAR(64) NOT NULL,
                "START" DATE NOT NULL,
                "END" DATE,
                "VALUE" DECIMAL(8,2) NOT NULL,
                "DESCRIPTION" VARCHAR(256),
                "ICON" BLOB,
                CONSTRAINT "IDX_ITEM" PRIMARY KEY ("ID")
);
COMMENT ON TABLE "ITEM" IS 'SHOPPING ITEM, MENU ITEM IN A RESTAURANT, TIME-PERIOD IN A TIMESHEET, ETC.';
COMMENT ON COLUMN "ITEM"."END" IS 'AVAILABILITY';
COMMENT ON COLUMN "ITEM"."ICON" IS 'ITEM IMAGE FOR BETTER USER EXPERIENCE';


CREATE INDEX "ITEM_IDX"
 ON "ITEM"
 ( "NAME" );

CREATE TABLE "CHARGEITEM" (
                "ID" INTEGER NOT NULL,
                "CHARGE" DECIMAL(8,2) NOT NULL,
                "ITEM" INTEGER NOT NULL,
                CONSTRAINT "IDX_CHARGEITEM" PRIMARY KEY ("ID")
);
COMMENT ON TABLE "CHARGEITEM" IS 'MAPPING BETWEEN PARTY AND CHARGE - RESOLVING A MANY-TO-MANY RELATION. A PARTY WILL CHARGE ITEMS.';


CREATE TABLE "COORDINATE" (
                "ID" INTEGER NOT NULL,
                "X" NUMERIC NOT NULL,
                "Y" NUMERIC NOT NULL,
                "Z" NUMERIC DEFAULT 0,
                CONSTRAINT "IDX_COORDINATE" PRIMARY KEY ("ID")
);


CREATE TABLE "ADDRESS" (
                "ID" INTEGER NOT NULL,
                "STREET" VARCHAR(64) NOT NULL,
                "CODE" VARCHAR(64) NOT NULL,
                "CITY" VARCHAR(64) NOT NULL,
                "COUNTRY" VARCHAR(64) NOT NULL,
                CONSTRAINT "IDX_ADDRESS" PRIMARY KEY ("ID")
);


CREATE TABLE "PARTY" (
                "ID" INTEGER NOT NULL,
                "ORGA" INTEGER NOT NULL,
                "MISSION" INTEGER NOT NULL,
                "NAME" VARCHAR(64) NOT NULL,
                "DESCRIPTION" VARCHAR(256),
                "SHORTNAME" VARCHAR(8) NOT NULL,
                "ICON" BLOB,
                CONSTRAINT "IDX_PARTY" PRIMARY KEY ("ID")
);
COMMENT ON TABLE "PARTY" IS 'MAY BE A PERSON, CLIENT, RESERVATION ITEM LIKE A RESTAURANT TABLE "ETC."';

CREATE TABLE "MISSION" (
                "ID" INTEGER NOT NULL,
                "NAME" VARCHAR(64) NOT NULL,
                "DESCRIPTION" VARCHAR(256),
                "ICON" BLOB,
                CONSTRAINT "IDX_MISSION" PRIMARY KEY ("ID")
);
COMMENT ON TABLE "MISSION" IS 'DEFINES THE MISSION/OPERATION OF A PARTY';

CREATE TABLE "PROPERTY" (
                "ID" INTEGER NOT NULL,
                "ITEM" INTEGER,
                "PARTY" INTEGER,
                "ORGA" INTEGER,
                "AKEY" VARCHAR(64) NOT NULL,
                "AVALUE" VARCHAR(64) NOT NULL,
                CONSTRAINT "IDX_PROPERTY" PRIMARY KEY ("ID")
);
COMMENT ON TABLE "PROPERTY" IS 'EXTENDED OPTIONAL PROPERTIES FOR MAIN ENTRIES LIKE AN ORGANISATION, PARTY OR ITEM.

ON A PARTY, IT MAY BE A BANK CONNECTION OR A PARTY CATEGORY.';


CREATE INDEX "PROPERTY_IDX"
 ON "PROPERTY"
 ( "AKEY" );

CREATE TABLE "LOCATION" (
                "ID" INTEGER NOT NULL,
                "NAME" VARCHAR(64) NOT NULL,
                "ADDRESS" INTEGER,
                "COORDINATE" INTEGER,
                "PARTY" INTEGER NOT NULL,
                "DIGITAL" INTEGER NOT NULL,
                CONSTRAINT "IDX_LOCATION" PRIMARY KEY ("ID")
);
COMMENT ON TABLE "LOCATION" IS 'LOCATION OF A PARTY. CAN HAVE AN ADDRESS OR A COORDINATE';


CREATE TABLE "CHARGE" (
                "ID" INTEGER NOT NULL,
                "PARTY" INTEGER NOT NULL,
                "LOCATION" INTEGER,
                "CHARGEITEM" INTEGER NOT NULL,
                "FROMDATE" DATE NOT NULL,
                "FROMTIME" TIME NOT NULL,
                "TODATE" DATE NOT NULL,
                "TOTIME" TIME NOT NULL,
                "PAUSE" TIME,
                "VALUE" DECIMAL(8,2) NOT NULL,
                "STATUS" INTEGER,
                "COMMENT" VARCHAR(512),
                CONSTRAINT "IDX_CHARGE" PRIMARY KEY ("ID")
);
COMMENT ON TABLE "CHARGE" IS 'AGREEMENT, BOOKING OR RESERVATION';


CREATE INDEX "CHARGE_IDX"
 ON "CHARGE"
 ( "FROMDATE" );

CREATE TABLE "CHARGESTATUS" (
                "ID" INTEGER NOT NULL,
                "NAME" VARCHAR(64) NOT NULL,
                "DESCRIPTION" VARCHAR(256),
                "ICON" BLOB,
                CONSTRAINT "IDX_CHARGESTATUS" PRIMARY KEY ("ID")
);

CREATE INDEX "CHARGESTATUS_IDX"
 ON "CHARGESTATUS"
 ( "NAME" );

CREATE TABLE "DISCHARGE" (
                "ID" INTEGER NOT NULL,
                "CHARGE" INTEGER NOT NULL,
                "ACCOUNT" INTEGER NOT NULL,
                "DATE" TIMESTAMP NOT NULL,
                "VALUE" DECIMAL(8,2) NOT NULL,
                "COMMENT" VARCHAR(512),
                "DOCUMENT" BLOB,
                CONSTRAINT "IDX_DISCHARGE" PRIMARY KEY ("ID")
);


CREATE INDEX "DISCHARGE_IDX"
 ON "DISCHARGE"
 ( "DATE" );

ALTER TABLE "DISCHARGE" ADD CONSTRAINT ACCOUNT_DISCHARGE_FK
FOREIGN KEY ("ACCOUNT")
REFERENCES ACCOUNT ("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
-- NOT DEFERRABLE
;

ALTER TABLE "LOCATION" ADD CONSTRAINT DIGITAL_LOCATION_FK
FOREIGN KEY ("DIGITAL")
REFERENCES DIGITAL ("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
-- NOT DEFERRABLE
;

ALTER TABLE "PARTY" ADD CONSTRAINT ORGANISATION_PARTY_FK
FOREIGN KEY ("ORGA")
REFERENCES ORGANISATION ("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
-- NOT DEFERRABLE
;

ALTER TABLE "PROPERTY" ADD CONSTRAINT ORGANISATION_PROPERTY_FK
FOREIGN KEY ("ORGA")
REFERENCES ORGANISATION ("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
-- NOT DEFERRABLE
;

ALTER TABLE "ITEM" ADD CONSTRAINT ORGANISATION_ITEM_FK
FOREIGN KEY ("ORGA")
REFERENCES ORGANISATION ("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
-- NOT DEFERRABLE
;

ALTER TABLE "ITEM" ADD CONSTRAINT CLASSIFICATION_ITEM_FK
FOREIGN KEY ("CLASS")
REFERENCES CLASSIFICATION ("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
-- NOT DEFERRABLE
;

ALTER TABLE "AREA" ADD CONSTRAINT CATEGORY_AREA_FK
FOREIGN KEY ("CATEGORY")
REFERENCES CATEGORY ("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
-- NOT DEFERRABLE
;

ALTER TABLE "TYPE" ADD CONSTRAINT AREA_TYPE_FK
FOREIGN KEY ("AREA")
REFERENCES AREA ("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
-- NOT DEFERRABLE
;

ALTER TABLE "ITEM" ADD CONSTRAINT TYPE_ITEM_FK
FOREIGN KEY ("TYPE")
REFERENCES TYPE ("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
-- NOT DEFERRABLE
;

ALTER TABLE "PROPERTY" ADD CONSTRAINT ITEM_PROPERTY_FK
FOREIGN KEY ("ITEM")
REFERENCES ITEM ("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
-- NOT DEFERRABLE
;

ALTER TABLE "CHARGEITEM" ADD CONSTRAINT ITEM_CHARGEITEM_FK
FOREIGN KEY ("ITEM")
REFERENCES ITEM ("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
-- NOT DEFERRABLE
;

ALTER TABLE "CHARGE" ADD CONSTRAINT CHARGEITEM_CHARGE_FK
FOREIGN KEY ("CHARGEITEM")
REFERENCES CHARGEITEM ("ID")
ON DELETE CASCADE
ON UPDATE NO ACTION
-- NOT DEFERRABLE
;

ALTER TABLE "CHARGE" ADD CONSTRAINT CHARGESTATUS_CHARGE_FK
FOREIGN KEY ("STATUS")
REFERENCES CHARGESTATUS ("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
-- NOT DEFERRABLE
;

ALTER TABLE "LOCATION" ADD CONSTRAINT COORDINATES_LOCATION_FK
FOREIGN KEY ("COORDINATE")
REFERENCES COORDINATE ("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
-- NOT DEFERRABLE
;

ALTER TABLE "LOCATION" ADD CONSTRAINT ADDRESS_LOCATION_FK
FOREIGN KEY ("ADDRESS")
REFERENCES ADDRESS ("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
-- NOT DEFERRABLE
;

ALTER TABLE "CHARGE" ADD CONSTRAINT PARTY_CHARGE_FK
FOREIGN KEY ("PARTY")
REFERENCES PARTY ("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
-- NOT DEFERRABLE
;

ALTER TABLE "PARTY" ADD CONSTRAINT PARTY_MISSION_FK
FOREIGN KEY ("MISSION")
REFERENCES MISSION ("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
-- NOT DEFERRABLE
;

ALTER TABLE "CHARGE" ADD CONSTRAINT CHARGE_LOCATION_FK
FOREIGN KEY ("LOCATION")
REFERENCES LOCATION ("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
-- NOT DEFERRABLE
;

ALTER TABLE "LOCATION" ADD CONSTRAINT PARTY_LOCATION_FK
FOREIGN KEY ("PARTY")
REFERENCES PARTY ("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
-- NOT DEFERRABLE
;

ALTER TABLE "PROPERTY" ADD CONSTRAINT PARTY_PROPERTY_FK
FOREIGN KEY ("PARTY")
REFERENCES PARTY ("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
-- NOT DEFERRABLE
;

ALTER TABLE "DISCHARGE" ADD CONSTRAINT CHARGE_DISCHARGE_FK
FOREIGN KEY ("CHARGE")
REFERENCES CHARGE ("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
-- NOT DEFERRABLE
;