scriptmysql_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 MYSQL
-- v0.0.22

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

CREATE TABLE ROLES (
    NAME VARCHAR(100) NOT NULL,
    PERMISSIONS MEDIUMBLOB,
    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(100) NOT NULL,
    APPPASSWORD VARCHAR(100),
    ROLE VARCHAR(100) NOT NULL,
    VISIBLE BIT NOT NULL,
    IMAGE MEDIUMBLOB,
    PRIMARY KEY (NAME),
    CONSTRAINT PEOPLE_FK_1 FOREIGN KEY (ROLE) REFERENCES ROLES(NAME)
);
INSERT INTO PEOPLE VALUES ('Administrator', NULL, 'Administrator', 1, NULL);
INSERT INTO PEOPLE VALUES ('Manager', NULL, 'Manager', 1, NULL);
INSERT INTO PEOPLE VALUES ('Employee', NULL, 'Employee', 1, NULL);
INSERT INTO PEOPLE VALUES ('Guest', NULL, 'Guest', 1, NULL);

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

CREATE TABLE RESOURCES (
    NAME VARCHAR(100) NOT NULL,
    RESTYPE INTEGER NOT NULL,
    CONTENT MEDIUMBLOB,
    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(100) NOT NULL,
    IMAGE MEDIUMBLOB,
    PRIMARY KEY(ID)
);
CREATE INDEX CATEGORIES_NAME_INX ON CATEGORIES(NAME);

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

CREATE TABLE PRODUCTS (
    REFERENCE VARCHAR(100) NOT NULL,
    CODE VARCHAR(100),
    CODETYPE VARCHAR(100),
    NAME VARCHAR(100) NOT NULL,
    PRICEBUY DOUBLE NOT NULL,
    PRICESELL DOUBLE NOT NULL,
    CATEGORY INTEGER NOT NULL,
    TAX INTEGER NOT NULL,
    STOCKCOST DOUBLE,
    STOCKVOLUME DOUBLE,
    IMAGE MEDIUMBLOB,
    ISCOM BIT NOT NULL,
    ISSCALE BIT 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(100) 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(100) NOT NULL,
    REFERENCE2 VARCHAR(100) 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(100) NOT NULL,
    ADDRESS VARCHAR(100),
    PRIMARY KEY (ID)
);
INSERT INTO LOCATIONS VALUES(0, 'General', NULL);

CREATE TABLE STOCKDIARY (
    ID INTEGER NOT NULL,
    DATENEW DATETIME NOT NULL,
    REASON INTEGER NOT NULL,
    LOCATION INTEGER NOT NULL,
    PRODUCT VARCHAR(100) 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 TABLE STOCKDIARYNUM (ID INTEGER NOT NULL);
INSERT INTO STOCKDIARYNUM VALUES(0);

CREATE TABLE STOCKCURRENT (
    LOCATION INTEGER NOT NULL,
    PRODUCT VARCHAR(100) 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(100) NOT NULL,
    DATESTART DATETIME NOT NULL,
    DATEEND DATETIME,
    PRIMARY KEY(MONEY)
);
CREATE INDEX CLOSEDCASH_INX_1 ON CLOSEDCASH(HOST, DATESTART);
CREATE TABLE CLOSEDCASHNUM (ID INTEGER NOT NULL);
INSERT INTO CLOSEDCASHNUM VALUES(0);

CREATE TABLE TICKETS (
    TICKETID INTEGER NOT NULL,
    DATENEW DATETIME NOT NULL,
    MONEY INTEGER NOT NULL,
    PERSON VARCHAR(100) 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 TABLE TICKETSNUM (ID INTEGER NOT NULL);
INSERT INTO TICKETSNUM VALUES(0);

CREATE TABLE PRODUCTSOUT (
    TICKETID INTEGER NOT NULL,
    TICKETLINE INTEGER NOT NULL,
    PRODUCT VARCHAR(100),
    NAME VARCHAR(100),
    ISCOM BIT,
    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(100) 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 TABLE PAYMENTSNUM (ID INTEGER NOT NULL);
INSERT INTO PAYMENTSNUM VALUES(0);

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

CREATE TABLE PLACES (
    NAME VARCHAR(100) 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 DATETIME NOT NULL,
    TITLE VARCHAR(100) NOT NULL,
    CHAIRS INTEGER NOT NULL,
    ISDONE BIT NOT NULL,
    DESCRIPTION VARCHAR(100),
    PRIMARY KEY (ID)
);
CREATE INDEX RESERVATIONS_INX_1 ON RESERVATIONS(DATENEW);
CREATE TABLE RESERVATIONSNUM (ID INTEGER NOT NULL);
INSERT INTO RESERVATIONSNUM VALUES(0);

CREATE TABLE THIRDPARTIES (
    ID VARCHAR(100) NOT NULL,
    CIF VARCHAR(100) NOT NULL,
    NAME VARCHAR(100) NOT NULL,
    ADDRESS VARCHAR(500),
    CONTACTCOMM VARCHAR(100),
    CONTACTFACT VARCHAR(100),
    PAYRULE VARCHAR(100),
    FAXNUMBER VARCHAR(100),
    PHONENUMBER VARCHAR(100),
    MOBILENUMBER VARCHAR(100),
    EMAIL VARCHAR(100),
    WEBPAGE VARCHAR(100),
    NOTES VARCHAR(500),
    PRIMARY KEY (ID)
);