icwTBYPk

1BEGIN;
2
3IF EXISTS (SELECT 1 )
4
5CREATE DATABASE stocky
6WITH   OWNER = postgres
7       ENCODING = 'UTF8'
8       TABLESPACE = pg_default
9       LC_COLLATE = 'en_US.utf8'
10       LC_CTYPE = 'en_US.utf8'
11       CONNECTION LIMIT = -1;
12
13
14IF EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = 'st') THEN
15   DROP SCHEMA st;
16END IF;
17
18CREATE SCHEMA st;
19
20CREATE TYPE U_ROLE AS ENUM ('R_USER', 'R_ADMIN');
21CREATE TYPE U_ENABLED AS ENUM ('E_ENABLED', 'E_DISABLED');                                                                                                                                        
22                                                                                                                                                                                                  
23CREATE TABLE st.user (                                                                                                                                                                            
24       user_id          INTEGER PRIMARY KEY NOT NULL,                                                                                                                                             
25       username         VARCHAR(512) NOT NULL,                                                                                                                                                    
26       first_name       VARCHAR(256) NOT NULL,                                                                                                                                                    
27       last_name        VARCHAR(256) NOT NULL,                                                                                                                                                    
28       email            VARCHAR(1024) NOT NULL,                                                                                                                                                   
29       user_role        U_ROLE NOT NULL,                                                                                                                                                          
30       balance          MONEY NOT NULL,                                                                                                                                                           
31       enabled          U_ENABLED NOT NULL                                                                                                                                                        
32);                                                                                                                                                                                                
33                                                                                                                                                                                                  
34CREATE TABLE st.company (                                                                                                                                                                         
35      company_id        INTEGER PRIMARY KEY NOT NULL,
36      name              VARCHAR(512) NOT NULL,
37      symbol            VARCHAR(6) NOT NULL,
38      amount_on_market  INTEGER NOT NULL
39);
40
41CREATE TABLE st.credit_card (
42      credit_card_id    INTEGER PRIMARY KEY NOT NULL,
43      cc_number         UUID NOT NULL,
44      last_four         VARCHAR(4) NOT NULL 
45);
46
47CREATE TABLE st.user_credit_card (
48      user_id           INTEGER REFERENCES st.user (user_id),
49      credit_card_id    INTEGER REFERENCES st.credit_card (credit_card_id),
50      PRIMARY KEY (user_id, credit_card_id)
51);
52
53CREATE TABLE st.trade (
54);
55
56CREATE TABLE st.stock_price (
57);