scripthsqldb_create.sql

是一个专门设计用于触摸屏的POS(point of sales)应用软件

--    Tina POS is a point of sales application designed for touch screens.
--    Copyright (C) 2005 Adri醤 Romero Corchado.
--    http://sourceforge.net/projects/tinapos
--
--    This program is free software; you can redistribute it and/or modify
--    it under the terms of the GNU General Public License as published by
--    the Free Software Foundation; either version 2 of the License, or
--    (at your option) any later version.
--
--    This program is distributed in the hope that it will be useful,
--    but WITHOUT ANY WARRANTY; without even the implied warranty of
--    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
--    GNU General Public License for more details.
--
--    You should have received a copy of the GNU General Public License
--    along with this program; if not, write to the Free Software
--    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

-- Tina POS Database initial script for HSQLDB
-- v0.0.22

CREATE TABLE TINAPOS (
    VERSION VARCHAR NOT NULL,
    PRIMARY KEY (VERSION)
);
INSERT INTO TINAPOS VALUES('0.0.22');

CREATE TABLE ROLES (
    NAME VARCHAR NOT NULL,
    PERMISSIONS VARBINARY,
    PRIMARY KEY (NAME)
);
INSERT INTO ROLES VALUES('Administrator', NULL);
INSERT INTO ROLES VALUES('Manager', NULL);
INSERT INTO ROLES VALUES('Employee', NULL);
INSERT INTO ROLES VALUES('Guest', NULL);

CREATE TABLE PEOPLE (
    NAME VARCHAR NOT NULL,
    APPPASSWORD VARCHAR,
    ROLE VARCHAR NOT NULL,
    VISIBLE BOOLEAN NOT NULL,
    IMAGE VARBINARY,
    PRIMARY KEY (NAME),
    CONSTRAINT PEOPLE_FK_1 FOREIGN KEY (ROLE) REFERENCES ROLES(NAME)
);
INSERT INTO PEOPLE VALUES ('Administrator', NULL, 'Administrator', TRUE, NULL);
INSERT INTO PEOPLE VALUES ('Manager', NULL, 'Manager', TRUE, NULL);
INSERT INTO PEOPLE VALUES ('Employee', NULL, 'Employee', TRUE, NULL);
INSERT INTO PEOPLE VALUES ('Guest', NULL, 'Guest', TRUE, NULL);

CREATE TABLE EDITTICKETS (
    TICKETID INTEGER NOT NULL,
    HOST VARCHAR,
    CONTENT VARBINARY,
    PRIMARY KEY (TICKETID)
);
CREATE INDEX EDITTICKETS_INX_1 ON EDITTICKETS(HOST);

CREATE TABLE RESOURCES (
    NAME VARCHAR NOT NULL,
    RESTYPE INTEGER NOT NULL,
    CONTENT VARBINARY,
    PRIMARY KEY (NAME)
);
-- INSERT INTO RESOURCES VALUES('Printer.Start', 0, $FILE{/net/adrianromero/templates/printerstart.xml});
-- INSERT INTO RESOURCES VALUES('Printer.Ticket', 0, $FILE{/net/adrianromero/templates/printerticket.xml});
-- INSERT INTO RESOURCES VALUES('Printer.Ticket2', 0, $FILE{/net/adrianromero/templates/printerticket2.xml});
-- INSERT INTO RESOURCES VALUES('Printer.TicketPreview', 0, $FILE{/net/adrianromero/templates/printerticketpreview.xml});
-- INSERT INTO RESOURCES VALUES('Printer.TicketTotal', 0, $FILE{/net/adrianromero/templates/printertickettotal.xml});
-- INSERT INTO RESOURCES VALUES('Printer.OpenDrawer', 0, $FILE{/net/adrianromero/templates/printeropendrawer.xml});
-- INSERT INTO RESOURCES VALUES('Printer.Ticket.Logo', 1, $FILE{/net/adrianromero/templates/printerticketlogo.png});
-- INSERT INTO RESOURCES VALUES('Printer.TicketLine', 0, $FILE{/net/adrianromero/templates/printerticketline.xml});
-- INSERT INTO RESOURCES VALUES('Printer.CloseCash', 0, $FILE{/net/adrianromero/templates/printerclosecash.xml});
-- INSERT INTO RESOURCES VALUES('Window.Logo', 1, $FILE{/net/adrianromero/templates/windowlogo.png});
-- INSERT INTO RESOURCES VALUES('Window.Title', 0, $FILE{/net/adrianromero/templates/windowtitle.txt});
-- INSERT INTO RESOURCES VALUES('Ticket.Buttons', 0, $FILE{/net/adrianromero/templates/ticketbuttons.xml});
-- INSERT INTO RESOURCES VALUES('Ticket.Line', 0, $FILE{/net/adrianromero/templates/ticketline.xml});
-- INSERT INTO RESOURCES VALUES('Printer.Inventory', 0, $FILE{/net/adrianromero/templates/printerinventory.xml});

CREATE TABLE CATEGORIES (
    ID INTEGER NOT NULL,
    NAME VARCHAR NOT NULL,
    IMAGE VARBINARY,
    PRIMARY KEY(ID)
);
CREATE INDEX CATEGORIES_NAME_INX ON CATEGORIES(NAME);

CREATE TABLE TAXES (
    ID INTEGER NOT NULL,
    NAME VARCHAR NOT NULL,
    RATE DOUBLE NOT NULL,
    PRIMARY KEY(ID)
);
INSERT INTO TAXES VALUES (0, '*', 0);

CREATE TABLE PRODUCTS (
    REFERENCE VARCHAR NOT NULL,
    CODE VARCHAR,
    CODETYPE VARCHAR,
    NAME VARCHAR NOT NULL,
    PRICEBUY DOUBLE NOT NULL,
    PRICESELL DOUBLE NOT NULL,
    CATEGORY INTEGER NOT NULL,
    TAX INTEGER NOT NULL,
    STOCKCOST DOUBLE,
    STOCKVOLUME DOUBLE,
    IMAGE VARBINARY,
    ISCOM BOOLEAN NOT NULL,
    ISSCALE BOOLEAN NOT NULL,
    PRIMARY KEY (REFERENCE),
    CONSTRAINT PRODUCTS_FK_1 FOREIGN KEY (CATEGORY) REFERENCES CATEGORIES(ID),
    CONSTRAINT PRODUCTS_FK_2 FOREIGN KEY (TAX) REFERENCES TAXES(ID)
);
CREATE INDEX PRODUCTS_INX_1 ON PRODUCTS(CODE);
CREATE INDEX PRODUCTS_NAME_INX ON PRODUCTS(NAME);

CREATE TABLE PRODUCTS_CAT (
    REFERENCE VARCHAR NOT NULL,
    CATORDER INTEGER,
    PRIMARY KEY (REFERENCE),
    CONSTRAINT PRODUCTS_CAT_FK_1 FOREIGN KEY (REFERENCE) REFERENCES PRODUCTS(REFERENCE)
);
CREATE INDEX PRODUCTS_CAT_INX_1 ON PRODUCTS_CAT(CATORDER);

CREATE TABLE PRODUCTS_COM (
    REFERENCE VARCHAR NOT NULL,
    REFERENCE2 VARCHAR NOT NULL,
    PRIMARY KEY (REFERENCE, REFERENCE2),
    CONSTRAINT PRODUCTS_COM_FK_1 FOREIGN KEY (REFERENCE) REFERENCES PRODUCTS(REFERENCE),
    CONSTRAINT PRODUCTS_COM_FK_2 FOREIGN KEY (REFERENCE2) REFERENCES PRODUCTS(REFERENCE)
);

CREATE TABLE LOCATIONS (
    ID INTEGER NOT NULL,
    NAME VARCHAR NOT NULL,
    ADDRESS VARCHAR,
    PRIMARY KEY (ID)
);
INSERT INTO LOCATIONS VALUES(0, 'General', NULL);

CREATE TABLE STOCKDIARY (
    ID INTEGER NOT NULL,
    DATENEW TIMESTAMP NOT NULL,
    REASON INTEGER NOT NULL,
    LOCATION INTEGER NOT NULL,
    PRODUCT VARCHAR NOT NULL,
    UNITS DOUBLE NOT NULL,
    PRICE DOUBLE NOT NULL,
    PRIMARY KEY (ID),
    CONSTRAINT STOCKDIARY_FK_1 FOREIGN KEY (PRODUCT) REFERENCES PRODUCTS(REFERENCE),
    CONSTRAINT STOCKDIARY_FK_2 FOREIGN KEY (LOCATION) REFERENCES LOCATIONS(ID)
);
CREATE INDEX STOCKDIARY_INX_1 ON STOCKDIARY(DATENEW);
CREATE SEQUENCE STOCKDIARYNUM;

CREATE TABLE STOCKCURRENT (
    LOCATION INTEGER NOT NULL,
    PRODUCT VARCHAR NOT NULL,
    STOCKSECURITY DOUBLE,
    STOCKMAXIMUM DOUBLE,
    UNITS DOUBLE NOT NULL,
    PRIMARY KEY (LOCATION, PRODUCT),
    CONSTRAINT STOCKCURRENT_FK_1 FOREIGN KEY (PRODUCT) REFERENCES PRODUCTS(REFERENCE),
    CONSTRAINT STOCKCURRENT_FK_2 FOREIGN KEY (LOCATION) REFERENCES LOCATIONS(ID)
);

CREATE TABLE CLOSEDCASH (
    MONEY INTEGER NOT NULL,
    HOST VARCHAR NOT NULL,
    DATESTART TIMESTAMP NOT NULL,
    DATEEND TIMESTAMP,
    PRIMARY KEY (MONEY)
);
CREATE INDEX CLOSEDCASH_INX_1 ON CLOSEDCASH(HOST, DATESTART);
CREATE SEQUENCE CLOSEDCASHNUM;

CREATE TABLE TICKETS (
    TICKETID INTEGER NOT NULL,
    DATENEW TIMESTAMP NOT NULL,
    MONEY INTEGER NOT NULL,
    PERSON VARCHAR NOT NULL,
    STATUS INTEGER DEFAULT 0 NOT NULL,
    PRIMARY KEY (TICKETID),
    CONSTRAINT TICKETS_FK_1 FOREIGN KEY (MONEY) REFERENCES CLOSEDCASH(MONEY),
    CONSTRAINT TICKETS_FK_2 FOREIGN KEY (PERSON) REFERENCES PEOPLE(NAME)
);
CREATE INDEX TICKETS_INX_1 ON TICKETS(DATENEW);
CREATE SEQUENCE TICKETSNUM;

CREATE TABLE PRODUCTSOUT (
    TICKETID INTEGER NOT NULL,
    TICKETLINE INTEGER NOT NULL,
    PRODUCT VARCHAR,
    NAME VARCHAR,
    ISCOM BOOLEAN,
    UNITS DOUBLE NOT NULL,
    PRICE DOUBLE NOT NULL,
    TAXID INTEGER,
    TAXRATE DOUBLE,
    PRIMARY KEY (TICKETID, TICKETLINE),
    CONSTRAINT PRODUCTSOUT_FK_1 FOREIGN KEY (TICKETID) REFERENCES TICKETS(TICKETID),
    CONSTRAINT PRODUCTSOUT_FK_2 FOREIGN KEY (PRODUCT) REFERENCES PRODUCTS(REFERENCE),
    CONSTRAINT PRODUCTSOUT_FK_3 FOREIGN KEY (TAXID) REFERENCES TAXES(ID)
);

CREATE TABLE PAYMENTS (
    ID INTEGER NOT NULL,
    TICKETID INTEGER NOT NULL,
    PAYMENT VARCHAR NOT NULL,
    TOTAL DOUBLE NOT NULL,
    PRIMARY KEY (ID),
    CONSTRAINT PAYMENTS_FK_2 FOREIGN KEY (TICKETID) REFERENCES TICKETS(TICKETID)
);
CREATE INDEX PAYMENTS_INX_1 ON PAYMENTS(PAYMENT);
CREATE SEQUENCE PAYMENTSNUM;

CREATE TABLE FLOORS (
    ID INTEGER NOT NULL,
    NAME VARCHAR NOT NULL,
    IMAGE VARBINARY,
    PRIMARY KEY (ID)
);
INSERT INTO FLOORS VALUES (0, 'Restaurant floor', $FILE{/net/adrianromero/templates/restaurantsample.jpg});

CREATE TABLE PLACES (
    NAME VARCHAR NOT NULL,
    X INTEGER NOT NULL,
    Y INTEGER NOT NULL,
    FLOOR INTEGER NOT NULL,
    PRIMARY KEY (NAME),
    CONSTRAINT PLACES_FK_1 FOREIGN KEY (FLOOR) REFERENCES FLOORS(ID)
);
INSERT INTO PLACES VALUES ('1', 133, 151, 0);
INSERT INTO PLACES VALUES ('2', 532, 151, 0);
INSERT INTO PLACES VALUES ('3', 133, 264, 0);
INSERT INTO PLACES VALUES ('4', 266, 264, 0);
INSERT INTO PLACES VALUES ('5', 399, 264, 0);
INSERT INTO PLACES VALUES ('6', 532, 264, 0);
INSERT INTO PLACES VALUES ('7', 133, 377, 0);
INSERT INTO PLACES VALUES ('8', 266, 377, 0);
INSERT INTO PLACES VALUES ('9', 399, 377, 0);
INSERT INTO PLACES VALUES ('10', 532, 377, 0);

CREATE TABLE RESERVATIONS (
    ID INTEGER NOT NULL,
    DATENEW TIMESTAMP NOT NULL,
    TITLE VARCHAR NOT NULL,
    CHAIRS INTEGER NOT NULL,
    ISDONE BOOLEAN NOT NULL,
    DESCRIPTION VARCHAR,
    PRIMARY KEY (ID)
);
CREATE INDEX RESERVATIONS_INX_1 ON RESERVATIONS(DATENEW);
CREATE SEQUENCE RESERVATIONSNUM;