Re: [ShopSuite-dev] new shop.psql

[Davi Leal]

Tomasz Wegrzanowski wrote:

> On Tue, Aug 21, 2001 at 03:31:40PM +0100, Davi Leal wrote:
> > CREATE VIEW ProdsToSell
> > CREATE VIEW ProdsToBuy
> >
> > [...]
> > FROM products
> > WHERE  NOT inactivation [...]
> >
> > moved to
> >
> > [...]
> > FROM products
> > WHERE  active [...]
> >
> > Right?
>
> There's a question whether we need active if we have inactivation
> that may be NULL. I prefer second version, as active field gives no new data.
> Following is always true:
>     active ::= (inactivation != NULL)

The "products.active ::= (transform.inactivation != NULL)" rule is wrong. I
think you mix the 'products' and 'transform' tables. On the one hand, the
'active' field of the table products means if a product has been removed or not,
that is to say, if it is active or not. On the other, the 'inactivation' field
of the table transform shows if a 'transformid' is history, that is to say, its
'inactivation' field IS NOT NULL; or if it is the current transform, that is to
say, its inactivation IS NULL. See below, and see the attached xfig figure.

CREATE TABLE products (
 shortdsc char(11) 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 transform (
 prodid_out int NOT NULL,
 notes  text,
 inactivation timestamp DEFAULT NULL,
 transformid serial
);



P.S.: You can make up the 'constraints' following the xfig figure.

#FIG 3.2
Landscape
Center
Metric
A4      
100.00
Single
-2
1200 2
0 32 #e2e2ee
0 33 #94949a
0 34 #dbdbdb
0 35 #a1a1b7
0 36 #9c0000
0 37 #ededed
0 38 #e0e0e0
0 39 #86acff
0 40 #7070ff
0 41 #c6b797
0 42 #eff8ff
0 43 #dccba6
0 44 #404040
0 45 #808080
0 46 #c0c0c0
0 47 #8e8f8e
0 48 #aaaaaa
0 49 #555555
0 50 #bebebe
0 51 #515151
0 52 #e7e3e7
0 53 #000049
0 54 #797979
0 55 #303430
0 56 #414141
0 57 #c7b696
0 58 #8e8e8e
0 59 #414541
0 60 #8e8e8e
0 61 #909090
0 62 #e6e4ce
0 63 #9b9b9b
0 64 #ce9100
0 65 #f762aa
0 66 #5f5f5f
0 67 #000079
0 68 #ff8200
0 69 #007d00
0 70 #0000be
0 71 #444444
0 72 #cccccc
0 73 #fff2d3
0 74 #ffe4ae
0 75 #ffd279
0 76 #cfcfcf
0 77 #999999
0 78 #b2b2b2
0 79 #868286
0 80 #c7c3c7
0 81 #868686
0 82 #c7c7c7
0 83 #e7e7e7
0 84 #f7f7f7
0 85 #9e9e9e
0 86 #717571
0 87 #effbff
0 88 #666666
6 450 4050 1800 6525
# Magenta square
6 1260 4635 1395 4778
2 2 0 1 0 28 49 0 20 0.000 0 0 -1 0 0 5
         1395 4635 1260 4635 1260 4778 1395 4778 1395 4635
-6
# Blue square
6 1125 6200 1260 6345
2 2 0 1 0 1 49 0 20 0.000 0 0 -1 0 0 5
         1260 6200 1125 6200 1125 6344 1260 6344 1260 6200
-6
2 2 0 1 0 7 50 0 -1 0.000 0 0 -1 0 0 5
         450 4050 1800 4050 1800 6525 450 6525 450 4050
4 0 0 50 0 18 12 0.0000 4 135 795 540 4320 Products\001
4 0 0 50 0 16 12 0.0000 4 135 705 540 4770 shortdsc\001
4 0 0 50 0 16 12 0.0000 4 180 660 540 4995 longdsc\001
4 0 0 50 0 16 12 0.0000 4 135 390 540 5220 units\001
4 0 0 50 0 16 12 0.0000 4 180 705 540 5445 sellprice\001
4 0 0 50 0 16 12 0.0000 4 150 315 540 5670 qpu\001
4 0 0 50 0 16 12 0.0000 4 135 435 540 5895 stock\001
4 0 0 50 0 16 12 0.0000 4 135 510 540 6120 active\001
4 0 0 50 0 16 12 0.0000 4 180 525 540 6345 prodid\001
-6
6 2700 4590 4545 5400
2 2 0 1 0 7 50 0 -1 0.000 0 0 -1 0 0 5
         2700 4590 4545 4590 4545 5400 2700 5400 2700 4590
4 0 0 50 0 16 12 0.0000 4 135 300 2835 5220 unit\001
4 0 0 50 0 18 12 0.0000 4 135 1680 2790 4860 MeasurementUnits\001
-6
6 3735 5670 8505 7515
6 3735 5670 5085 7470
# Blue square
6 4770 6480 4905 6625
2 2 0 1 0 1 49 0 20 0.000 0 0 -1 0 0 5
         4905 6480 4770 6480 4770 6624 4905 6624 4905 6480
-6
# Magenta square
6 4725 6255 4860 6398
2 2 0 1 0 75 49 0 20 0.000 0 0 -1 0 0 5
         4860 6255 4725 6255 4725 6398 4860 6398 4860 6255
-6
2 2 0 1 0 7 50 0 -1 0.000 0 0 -1 0 0 5
         3735 5670 5085 5670 5085 7470 3735 7470 3735 5670
4 0 0 50 0 18 12 0.0000 4 135 930 3825 5940 Transform\001
4 0 0 50 0 16 12 0.0000 4 180 885 3825 6390 prodid_out\001
4 0 0 50 0 16 12 0.0000 4 135 900 3825 6615 transformid\001
4 0 0 50 0 16 12 0.0000 4 135 450 3825 6840 notes\001
4 0 0 50 0 16 12 0.0000 4 135 960 3825 7065 inactivation\001
-6
6 6435 5670 8505 7515
# Magenta square
6 7515 6472 7650 6615
2 2 0 1 0 75 49 0 20 0.000 0 0 -1 0 0 5
         7650 6472 7515 6472 7515 6615 7650 6615 7650 6472
-6
2 2 0 1 0 7 50 0 -1 0.000 0 0 -1 0 0 5
         6435 5670 8505 5670 8505 7515 6435 7515 6435 5670
4 0 0 50 0 16 12 0.0000 4 135 900 6525 6615 transformid\001
4 0 0 50 0 18 12 0.0000 4 180 1905 6525 5940 Transform_ingredient\001
4 0 0 50 0 16 12 0.0000 4 180 780 6525 6840 prodid_in\001
4 0 0 50 0 16 12 0.0000 4 135 600 6525 7065 amount\001
-6
2 1 0 1 0 7 50 0 -1 0.000 0 0 -1 1 0 2
        3 1 1.00 60.00 120.00
         4770 6525 6480 6525
4 0 0 50 0 16 12 0.0000 4 135 300 5535 6480 1..n\001
-6
6 8685 1620 10485 3240
6 8685 1620 10485 3240
2 2 0 1 0 7 50 0 -1 0.000 0 0 -1 0 0 5
         8685 1620 10485 1620 10485 3240 8685 3240 8685 1620
4 0 0 50 0 18 12 0.0000 4 180 1170 8820 1890 TradeHistory\001
4 0 0 50 0 16 12 0.0000 4 180 360 8820 2295 opid\001
4 0 0 50 0 16 12 0.0000 4 180 525 8820 2520 prodid\001
4 0 0 50 0 16 12 0.0000 4 135 600 8820 2745 amount\001
4 0 0 50 0 16 12 0.0000 4 180 420 8820 2970 price\001
-6
-6
6 5985 900 7785 2520
6 5985 900 7785 2520
2 2 0 1 0 7 50 0 -1 0.000 0 0 -1 0 0 5
         5985 900 7785 900 7785 2520 5985 2520 5985 900
4 0 0 50 0 18 12 0.0000 4 180 975 6120 1170 Operations\001
4 0 0 50 0 16 12 0.0000 4 135 45 6120 1575 t\001
4 0 0 50 0 16 12 0.0000 4 135 450 6120 1800 notes\001
4 0 0 50 0 16 12 0.0000 4 135 510 6120 2025 userid\001
4 0 0 50 0 16 12 0.0000 4 180 360 6120 2250 opid\001
-6
# Blue square
6 6525 2115 6660 2260
2 2 0 1 0 1 49 0 20 0.000 0 0 -1 0 0 5
         6660 2115 6525 2115 6525 2259 6660 2259 6660 2115
-6
-6
6 450 1350 2250 2700
6 450 1350 2250 2700
# Blue square
6 1080 2340 1215 2485
2 2 0 1 0 1 49 0 20 0.000 0 0 -1 0 0 5
         1215 2340 1080 2340 1080 2484 1215 2484 1215 2340
-6
2 2 0 1 0 7 50 0 -1 0.000 0 0 -1 0 0 5
         450 1350 2250 1350 2250 2700 450 2700 450 1350
4 0 0 50 0 18 12 0.0000 4 180 1290 585 1620 DeviationType\001
4 0 0 50 0 16 12 0.0000 4 135 705 585 2025 shortdsc\001
4 0 0 50 0 16 12 0.0000 4 180 660 585 2250 longdsc\001
4 0 0 50 0 16 12 0.0000 4 135 465 585 2475 devid\001
-6
-6
6 3150 1575 5130 3195
2 2 0 1 0 7 50 0 -1 0.000 0 0 -1 0 0 5
         3150 1575 5130 1575 5130 3195 3150 3195 3150 1575
4 0 0 50 0 16 12 0.0000 4 180 360 3285 2250 opid\001
4 0 0 50 0 18 12 0.0000 4 180 1710 3285 1845 InventoryDeviation\001
4 0 0 50 0 16 12 0.0000 4 135 240 3285 2925 diff\001
4 0 0 50 0 16 12 0.0000 4 135 465 3285 2475 devid\001
4 0 0 50 0 16 12 0.0000 4 180 525 3285 2700 prodid\001
-6
6 3735 7920 5985 9315
# Magenta square
6 4770 8505 4905 8648
2 2 0 1 0 75 49 0 20 0.000 0 0 -1 0 0 5
         4905 8505 4770 8505 4770 8648 4905 8648 4905 8505
-6
2 2 0 1 0 7 50 0 -1 0.000 0 0 -1 0 0 5
         3735 7920 5985 7920 5985 9315 3735 9315 3735 7920
4 0 0 50 0 18 12 0.0000 4 180 2100 3825 8190 HowToMake_Ingredient\001
4 0 0 50 0 16 12 0.0000 4 180 780 3825 8865 prodid_in\001
4 0 0 50 0 16 12 0.0000 4 135 600 3825 9090 amount\001
4 0 0 50 0 16 12 0.0000 4 180 885 3825 8640 prodid_out\001
-6
6 9810 7695 11205 8820
# Magenta square
6 9810 7740 9945 7883
2 2 0 1 0 28 49 0 20 0.000 0 0 -1 0 0 5
         9945 7740 9810 7740 9810 7883 9945 7883 9945 7740
-6
# Magenta square
6 9810 8190 9945 8333
2 2 0 1 0 75 49 0 20 0.000 0 0 -1 0 0 5
         9945 8190 9810 8190 9810 8333 9945 8333 9945 8190
-6
# Blue square
6 9810 8640 9945 8785
2 2 0 1 0 1 49 0 20 0.000 0 0 -1 0 0 5
         9945 8640 9810 8640 9810 8784 9945 8784 9945 8640
-6
4 0 0 50 0 16 12 0.0000 4 180 1095 10080 7830 Unique index\001
4 0 0 50 0 16 12 0.0000 4 135 450 10080 8325 Index\001
4 0 0 50 0 16 12 0.0000 4 135 480 10080 8775 Serial\001
-6
2 1 0 1 0 7 50 0 -1 0.000 0 0 -1 1 0 2
        3 1 1.00 60.00 120.00
         1125 6300 3780 6300
2 1 0 1 0 7 50 0 -1 0.000 0 0 -1 1 0 2
        3 1 1.00 60.00 120.00
         945 5175 2790 5175
2 1 0 1 0 7 50 0 -1 0.000 0 0 -1 1 0 2
        3 1 1.00 60.00 120.00
         1260 6210 3240 2700
2 1 0 1 0 7 50 0 -1 0.000 0 0 -1 1 0 2
        3 1 1.00 60.00 120.00
         6705 2205 8775 2205
2 1 0 1 0 7 50 0 -1 0.000 0 0 -1 0 1 2
        3 1 1.00 60.00 120.00
         3690 2205 6075 2205
2 1 0 1 0 7 50 0 -1 0.000 0 0 -1 1 0 2
        3 1 1.00 60.00 120.00
         1215 2430 3240 2430
2 1 0 1 0 7 50 0 -1 0.000 0 0 -1 1 0 2
        3 1 1.00 60.00 120.00
         1260 6300 8775 2475
2 1 0 1 0 7 50 0 -1 0.000 0 0 -1 1 0 2
        3 1 1.00 60.00 120.00
         1260 6345 3780 8550
2 1 0 1 0 7 50 0 -1 0.000 0 0 -1 1 0 2
        3 1 1.00 60.00 120.00
         1260 6345 3780 8775
4 0 0 50 0 16 12 0.0000 4 135 300 2115 5130 n..1\001
4 0 0 50 0 16 12 0.0000 4 135 300 2385 6255 1..n\001
4 0 0 50 0 16 12 0.0000 4 135 300 5400 2160 n..1\001
4 0 0 50 0 16 12 0.0000 4 135 300 8055 2160 1..n\001
4 0 0 50 0 16 12 0.0000 4 135 300 2520 2385 1..n\001
4 0 0 50 0 16 12 0.0000 4 135 300 2295 3780 1..n\001
4 0 0 50 0 16 12 0.0000 4 135 300 5895 3780 1..n\001
4 0 0 50 0 18 16 0.0000 4 240 2505 4500 315 ShopSuite Data Base\001
4 0 0 50 0 16 12 0.0000 4 135 300 2520 7425 1..n\001
4 0 0 50 0 16 12 0.0000 4 135 300 2115 7605 1..n\001
4 0 0 50 0 3 16 0.0000 4 210 5025 3195 5220 Tomasz, Does 'unit' need a "Unique 
index" flag?\001

CREATE TABLE products (
        shortdsc        char(11) 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 UNIQUE INDEX products_shortdsc_index ON products (shortdsc);

CREATE TABLE measurementunits (
        unit            char(20) 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_ingredient_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_ingredient_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);