--
-- Sampledb SQL database for AceQL usage tested with:
--  PostgreSQL 8.4.1+,

CREATE TABLE user_login
(               
  username              varchar(255)    not null,     
  hash_password         varchar(40)     not null,
        PRIMARY KEY (username)
);


CREATE TABLE banned_usernames
(               
  username              varchar(255)    not null,     
        PRIMARY KEY (username)
);


CREATE TABLE customer
(
    customer_id     integer     not null,
    customer_title  char(4)         null,
    fname           varchar(32)     null,
    lname           varchar(32) not null,
    addressline     varchar(64) not null,
    town            varchar(32) not null,
    zipcode         char(10)    not null,
    phone           varchar(32)     null,
        PRIMARY KEY(customer_id)
);

CREATE TABLE product_image
(
    product_id  integer     not null,
    name        varchar(64) not null,
    image       oid         null, 
        PRIMARY KEY(product_id)
);

--
-- orderlog table 
-- 

CREATE TABLE orderlog
(
    customer_id     integer     not null,
    item_id         integer     not null,
    description     varchar(64) not null,
    item_cost	    numeric     null,
    date_placed     date        not null,
    date_shipped    timestamp 	null, 
    jpeg_image      oid 	    null, 
    is_delivered    integer     null,
    quantity        integer     not null,    
         PRIMARY KEY(customer_id, item_id)
);

--
-- Example of a table with an auto-increment primary index
--

CREATE TABLE customer_auto
(
    customer_id     serial      not null,
    customer_title  char(4)     null,
    fname           varchar(32) null,
    lname           varchar(32) not null,
    addressline     varchar(64) null,
    town            varchar(32) null,
    zipcode         char(10)    not null,
    phone           varchar(32) null,
        PRIMARY KEY(customer_id)
);

--
-- documentation table
-- A CLOB example.

CREATE TABLE documentation
(
    item_id         integer     not null,
    item_doc        text        NOT NULL,         
         PRIMARY KEY(item_id)
);

--
-- Init the user_login table with a (login, hashed password)
--

insert into user_login values ('username', '5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8');
insert into user_login values ('MyUsername', 'eabbec6f31804eb968e2faeaaef150546a595fc3');