diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 53 |
1 files changed, 52 insertions, 1 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index eee85f6e..30782a24 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -61,6 +61,8 @@ CREATE TYPE beta_type AS ENUM('IG', 'CRT', 'EU', 'SUBPRIME', 'XO'); CREATE TYPE account_type AS ENUM('Cash', 'Brokerage', 'Fcm', 'Future'); +CREATE TYPE equity_option_type AS ENUM ('CALL', 'PUT'); + CREATE TABLE accounts( code varchar(5) PRIMARY KEY, name text, @@ -2495,4 +2497,53 @@ AS SELECT trades.settle_date, accounts.name FROM accounts) cp_code) cps USING (cp_code) GROUP BY trades.settle_date, trades.fund, cps.name, trades.cp_code, trades.asset_class, trades.currency - ORDER BY trades.settle_date DESC, trades.fund, cps.name, trades.asset_class, trades.currency;
\ No newline at end of file + ORDER BY trades.settle_date DESC, trades.fund, cps.name, trades.asset_class, trades.currency; + + + + CREATE TABLE public.equities ( + id serial4 NOT NULL, + dealid varchar(28) NULL GENERATED ALWAYS AS ((('EQ'::text || id::text))) STORED, + lastupdate timestamp NULL DEFAULT now(), + "action" action NOT NULL, + folder future_strat NOT NULL, + trade_date date NOT NULL, + settle_date date NOT NULL, + buysell bool NOT NULL, + bbg_ticker varchar(32) NOT NULL, + quantity float8 NOT NULL, + price float8 NOT NULL, + commission float8 NULL, + security_desc varchar(32) NOT NULL, + "currency" currency NOT NULL, + exchange varchar(3) NOT NULL, + "fund" fund NOT NULL DEFAULT 'SERCGMAST'::fund, + account_code text NOT NULL DEFAULT 'IB'::text, + CONSTRAINT equities_dealid_key UNIQUE (dealid), + CONSTRAINT equities_pkey PRIMARY KEY (id), + CONSTRAINT equities_account_code_fkey FOREIGN KEY (account_code) REFERENCES public.accounts(code) +); + +CREATE TABLE public.equity_options ( + id serial4 NOT NULL, + dealid varchar(28) NULL GENERATED ALWAYS AS ((('EQOPT'::text || id::text))) STORED, + lastupdate timestamp NULL DEFAULT now(), + "action" action NOT NULL, + folder future_strat NOT NULL, + trade_date date NOT NULL, + settle_date date NOT NULL, + buysell bool NOT NULL, + bbg_ticker varchar(32) NOT NULL, + quantity float8 NOT NULL, + option_type equity_option_type NOT NULL, + price float8 NOT NULL, + commission float8 NULL, + security_desc varchar(32) NOT NULL, + "currency" currency NOT NULL, + exchange varchar(3) NOT NULL, + "fund" fund NOT NULL DEFAULT 'SERCGMAST'::fund, + account_code text NOT NULL DEFAULT 'IB'::text, + CONSTRAINT equity_options_dealid_key UNIQUE (dealid), + CONSTRAINT equity_options_pkey PRIMARY KEY (id), + CONSTRAINT equity_options_account_code_fkey FOREIGN KEY (account_code) REFERENCES public.accounts(code) +); |