Re: [ShopSuite-dev] shop and drop

[Davi Leal]

Tomasz Wegrzanowski wrote:

> Here are two scripts: shop.psql and drop.psql
>
> drop.psql drops all shopDB tables from db, so newer version can be
> added (that's mostly for debug)

We have scripts/clean.psql (DELETE). Must the drop.psql (DROP) be added to the
scripts/ directory?. I do not think so. At least not now. What do you think?.



> New shop.psql allows only UNQIUE measurementunits,
> and it inplicitely creates a key for them.
>
> It also shortens two indexes names that would be shorted by psql
> anyway, as they are two long.

Agreed. And ... What about using 'UNIQUE' too into the products.shortdsc field?.
See the diff of attached shop.psql.

CREATE TABLE products (
        shortdsc        char(11) UNIQUE NOT NULL,
        longdsc         text NOT NULL,
        sellprice       numeric(11,2),
        stock           rational NOT NULL,
        unit            char(20) NOT NULL,
        active          bool NOT NULL DEFAULT 'true',
        prodid          serial
);

CREATE TABLE measurementunits (
        unit            char(20) UNIQUE NOT NULL
);
CREATE TABLE transform (
        prodid_out      int NOT NULL,
        notes           text,
        inactivation    timestamp DEFAULT NULL,
        transformid     serial
);
CREATE INDEX transform_prodid_out_index ON transform (prodid_out);

CREATE TABLE transform_ingredient (
        transformid     int NOT NULL,
        prodid_in       int NOT NULL,
        amount          rational
);
CREATE INDEX transform_ing_transformid_index ON transform_ingredient 
(transformid);

CREATE TABLE howtomake_ingredient (
        prodid_out      int NOT NULL,
        prodid_in       int NOT NULL,
        amount          rational
);
CREATE INDEX howtomake_ing_prodid_out_index ON howtomake_ingredient 
(prodid_out);

CREATE TABLE deviationtype (
        shortdsc        char(16) NOT NULL,
        longdsc         text NOT NULL,
        devid           serial
);

CREATE TABLE operations (
        t               timestamp NOT NULL,
        notes           text NOT NULL,
        userid          char(20) NOT NULL,
        opid            serial
);

CREATE TABLE tradehistory (
        opid            int NOT NULL,
        prodid          int NOT NULL,
        amount          rational NOT NULL,
        price           numeric(11,2)
);

CREATE TABLE inventorydeviation (
        opid            int NOT NULL,
        devid           int NOT NULL,
        prodid          int NOT NULL,
        diff            rational
);

CREATE VIEW ProdsToSell
AS SELECT
        shortdsc,
        longdsc,
        sellprice,
        unit,
        prodid
FROM products
WHERE active AND sellprice IS NOT NULL;

CREATE VIEW ProdsToBuy
AS SELECT
        shortdsc,
        longdsc,
        stock,
        unit,
        prodid
FROM products
WHERE active AND prodid NOT IN (SELECT prodid_out FROM transform WHERE active);