HP 3000 Manuals

Up and Running with ALLBASE/SQL

Sample Mapping of a TurboIMAGE Database to an ALLBASE/SQL DBEnvironment 

Consider the following TurboIMAGE schema:

     BEGIN DATABASE TIPART;

     PASSWORDS:
        12 BUYER;
        14 CLERK;
        18 DO-ALL;

     ITEMS:
        BINNUMBER      , K     (12,14/18);
        COUNTCYCLE     , 3I    (12/18);
        ITEMCOUNT      , I2    (/14,18);
        LASTCOUNTDATE  , X8    (12/18);
        PARTNAME       , X32   (14/12,18);
        PARTNUMBER     , X16   (14/12,18);
        SALESPRICE     , P12   (/12,18);
        WAREHOUSE      , X32   (/12,18);

     SETS:
        NAME: PARTS, MANUAL;
        ENTRY:
           PARTNUMBER(1),
           PARTNAME,
           SALESPRICE;
        CAPACITY: 301;

        NAME: INVENTORY, DETAIL;
        ENTRY:
           PARTNUMBER(PARTS),
           BINNUMBER,
           ITEMCOUNT,
           WAREHOUSE,
           LASTCOUNTDATE,
           COUNTCYCLE;
        CAPACITY: 200;
     END.

You might implement this design using a set of SQL commands such as the
following:

     START DBE 'TIPARTS' NEW;

     CREATE GROUP BUYER;
     CREATE GROUP CLERK;
     CREATE GROUP DO_ALL;

     COMMIT WORK;
     CREATE DBEFILESET DATAFS;
     CREATE DBEFILE DATAF1 WITH
        PAGES=200, NAME='DATAF1', TYPE=MIXED;
     ADD DBEFILE DATAF1 TO DBEFILESET DATAFS;
     CREATE PUBLIC TABLE INVENTORY
       (PARTNUMBER    CHAR(16)    NOT NULL
           REFERENCES PARTS (PARTNUMBER),
        BINNUMBER     INTEGER     NOT NULL,
        ITEMCOUNT     INTEGER     NOT NULL,
        WAREHOUSE     CHAR(32)    NOT NULL,
        LASTCOUNTDATE CHAR(8)     NOT NULL,
        COUNTCYCLE_1  SMALLINT    NOT NULL,
        COUNTCYCLE_2  SMALLINT    NOT NULL,
        COUNTCYCLE_3  SMALLINT    NOT NULL)
        IN DATAFS;
     COMMIT WORK;

     CREATE DBEFILESET HASHFS;
     CREATE DBEFILE HASHF1 WITH
        PAGES=350, NAME= 'HASHF1';
     ADD DBEFILE HASHF1 TO DBEFILESET HASHFS;
     CREATE PUBLIC TABLE PARTS
       (PARTNUMBER    CHAR(16)      NOT NULL,
        PARTNAME      CHAR(32)      NOT NULL,
        SALESPRICE    DECIMAL(11,0) NOT NULL)
       HASH ON (PARTNUMBER) PAGES=301
       IN HASHFS;
     COMMIT WORK;

     REVOKE ALL ON INVENTORY FROM PUBLIC;
     REVOKE ALL ON PARTS FROM PUBLIC;

     GRANT SELECT ON INVENTORY, PARTS TO
           BUYER, CLERK, DO_ALL;
     GRANT INSERT, UPDATE, DELETE ON
           INVENTORY, PARTS TO DO_ALL;
     GRANT UPDATE ON INVENTORY
           (PARTNUMBER, ITEMCOUNT) TO CLERK;
     GRANT UPDATE ON PARTS (PARTNUMBER, PARTNAME,
           SALESPRICE, WAREHOUSE) TO BUYER;
     COMMIT WORK;

This mapping is intended as illustrative only, not an exact migration of
the database.  To create an exact mapping, you would create views to
define specific subsets of the tables, grant authorities on the views to
appropriate users, then revoke access to the base tables.

MPE/iX 5.0 Documentation