DATABASE stores_demo

MAIN
  WHENEVER ERROR CONTINUE
    DROP TABLE customer
  WHENEVER ERROR STOP

  CREATE TABLE customer (
    customer_num         SERIAL(100),
    fname                CHAR(15),
    lname                CHAR(15),
    company              CHAR(20),
    address1             CHAR(20),
    address2             CHAR(20),
    city                 CHAR(15),
    state                CHAR(2),
    zipcode              CHAR(5),
    phone                CHAR(18)
  )

  LOAD FROM "unl/stores/customer.unl" INSERT INTO customer

  WHENEVER ERROR CONTINUE
    DROP TABLE items
  WHENEVER ERROR STOP

  CREATE TABLE items (
    item_num             SMALLINT,
    order_num            INTEGER,
    stock_num            SMALLINT,
    manu_code            CHAR(3),
    quantity             SMALLINT,
    total_price          MONEY(6)
  )

  LOAD FROM "unl/stores/items.unl" INSERT INTO items

  WHENEVER ERROR CONTINUE
    DROP TABLE manufact
  WHENEVER ERROR STOP

  CREATE TABLE manufact (
    manu_code            CHAR(3),
    manu_name            CHAR(15)
  )

  LOAD FROM "unl/stores/manufact.unl" INSERT INTO manufact

  WHENEVER ERROR CONTINUE
    DROP TABLE orders
  WHENEVER ERROR STOP

  CREATE TABLE orders (
    order_num            SERIAL(1000),
    order_date           DATE,
    customer_num         INTEGER,
    ship_instruct        CHAR(40),
    backlog              CHAR(1),
    po_num               CHAR(10),
    ship_date            DATE,
    ship_weight          DECIMAL(8,2),
    ship_charge          MONEY(6),
    paid_date            DATE
  )

  LOAD FROM "unl/stores/orders.unl" INSERT INTO orders

  WHENEVER ERROR CONTINUE
    DROP TABLE state
  WHENEVER ERROR STOP

  CREATE TABLE state (
    code                 CHAR(2),
    sname                CHAR(15)
  )

  LOAD FROM "unl/stores/state.unl" INSERT INTO state

  WHENEVER ERROR CONTINUE
    DROP TABLE stock
  WHENEVER ERROR STOP

  CREATE TABLE stock (
    stock_num            SMALLINT,
    manu_code            CHAR(3),
    description          CHAR(15),
    unit_price           MONEY(6),
    unit                 CHAR(4),
    unit_descr           CHAR(15)
  )

  LOAD FROM "unl/stores/stock.unl" INSERT INTO stock

  WHENEVER ERROR CONTINUE
    DROP TABLE syscolatt
  WHENEVER ERROR STOP

  CREATE TABLE syscolatt (
    tabname              CHAR(18),
    colname              CHAR(18),
    seqno                SERIAL,
    color                SMALLINT,
    inverse              CHAR(1),
    underline            CHAR(1),
    blink                CHAR(1),
    left                 CHAR(1),
    def_format           CHAR(64),
    condition            CHAR(64)
  )

  LOAD FROM "unl/stores/syscolatt.unl" INSERT INTO syscolatt

  WHENEVER ERROR CONTINUE
    DROP TABLE syscolval
  WHENEVER ERROR STOP

  CREATE TABLE syscolval (
    tabname              CHAR(18),
    colname              CHAR(18),
    attrname             CHAR(10),
    attrval              CHAR(64)
  )

  LOAD FROM "unl/stores/syscolval.unl" INSERT INTO syscolval

  WHENEVER ERROR CONTINUE
    DROP TABLE syscoltest
  WHENEVER ERROR STOP

  CREATE TABLE syscoltest (
    def_test CHAR(10),
    inc_test CHAR,
    com_test CHAR(10),
    ups_test CHAR(10),
    dns_test CHAR(10),
    mix_test CHAR(10)
  )

  WHENEVER ERROR CONTINUE
    DROP TABLE xxxart
  WHENEVER ERROR STOP

  CREATE TABLE xxxart
  (
    cod_emc       CHAR(3) NOT NULL,
    cod_div       CHAR(3),
    num_pro       INTEGER NOT NULL,
    cod_pro       CHAR(9) NOT NULL,
    cod_art       CHAR(14),
    des_art       CHAR(40) NOT NULL,
    cla_ref       CHAR(18),
    pre_bru       DECIMAL(16,7),
    can_pre       DECIMAL(8,2),
    por_dt1       DECIMAL(4,2),
    por_dt2       decimal(4,2),
    por_dt3       DECIMAL(4,2),
    inc_com       DECIMAL(7,5),
    pre_net       DECIMAL(16,7),
    mar_cas       DECIMAL(5,2),
    pre_soc       DECIMAL(16,7),
    emb_ven       DECIMAL(8,2),
    emb_com       DECIMAL(8,2),
    can_soc       DECIMAL(8,2),
    can_alm       DECIMAL(8,2),
    ser_lin       INTEGER NOT NULL,
    lin_cdv       INTEGER,
    bnum_pag      INTEGER,
    bnum_lin      SMALLINT,
    estado        CHAR(1),
    pre_pvp       DECIMAL(16,7),
    num_ord       INTEGER,
    fac_can_ven   DECIMAL(8,2),
    dto_pub       DECIMAL(4,2),
    imp_pub       DECIMAL(16,7),
    dto_ges       DECIMAL(4,2),
    inc_ctc       DECIMAL(4,2),
    envio_directo CHAR(1)
  )

  LOAD FROM "unl/stores/xxxart.unl" INSERT INTO xxxart 

  DISPLAY "All tables recreated and populated"
  
END MAIN