/*
 * Generated by XCodeGen 1.0
 * Sun Jul 07 16:05:44 EDT 2002
 */

---------------------------------------------------
-- Drop all the tables
---------------------------------------------------

DROP TABLE ORDER_ITEM;

DROP TABLE ORDER;

DROP TABLE USER;

DROP TABLE PERSON;

---------------------------------------------------
-- Create all the tables
---------------------------------------------------
    
CREATE TABLE PERSON
(
                                        
    id INTEGER NOT NULL
                                        
    ,firstname VARCHAR(50)
                                        
    ,lastname VARCHAR(50)
                                            
    ,email VARCHAR(255) NOT NULL
            
    ,PRIMARY KEY (id)
);

                                                        
    
CREATE TABLE USER
(
                            
    _pk INTEGER NOT NULL
                                        
    ,password VARCHAR(16) NOT NULL
            
    ,PRIMARY KEY (_pk)
);

                            
ALTER TABLE USER ADD CONSTRAINT USER_PERSON__pk
    FOREIGN KEY (_pk) REFERENCES PERSON(id)
    ON DELETE CASCADE;
    
    
CREATE TABLE ORDER
(
                                        
    id INTEGER NOT NULL
                                        
    ,created TIMESTAMP
                                            
    ,personId INTEGER NOT NULL
            
    ,PRIMARY KEY (id)
);

                                                
ALTER TABLE ORDER ADD CONSTRAINT ORDER_PERSON_personId
    FOREIGN KEY(personId) REFERENCES PERSON(id)
                        
    ON DELETE SET NULL;
                            
    
CREATE TABLE ORDER_ITEM
(
                                        
    id INTEGER NOT NULL
                                        
    ,description VARCHAR(255)
                                        
    ,unitPrice DOUBLE
                                        
    ,quantity INTEGER
                                        
    ,orderId INTEGER
            
    ,PRIMARY KEY (id)
);

                                                                        
ALTER TABLE ORDER_ITEM ADD CONSTRAINT ORDER_ITEM_ORDER_orderId
    FOREIGN KEY(orderId) REFERENCES ORDER(id)
                        
    ON DELETE CASCADE;
                            
