1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
|
from sqlalchemy.sql import text
from sqlalchemy import create_engine
import os
def create_trigger_function(db):
# need to escape % with %%
auto_dealid = db.DDL("""
CREATE OR REPLACE FUNCTION auto_dealid()
RETURNS TRIGGER AS $$
DECLARE stub text;
sqlstr text;
BEGIN
sqlstr:= 'UPDATE '|| TG_TABLE_NAME ||' SET %%s WHERE id = %%L AND dealid is NULL';
IF (TG_TABLE_NAME = 'bonds') THEN
stub := 'SC_';
sqlstr := format(sqlstr, 'dealid = $1||upper(left(asset_class::text,3))||id,
identifier = COALESCE(identifier, cusip, isin)', NEW.id);
ELSIF (TG_TABLE_NAME = 'cds') THEN
stub := 'SCCDS';
sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id);
ELSIF (TG_TABLE_NAME = 'repo') THEN
stub := 'SC_REP';
sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id);
ELSIF (TG_TABLE_NAME = 'swaptions') THEN
stub := 'SWPTN';
sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id);
ELSIF (TG_TABLE_NAME = 'futures') THEN
stub := 'SCFUT';
sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id);
ELSIF (TG_TABLE_NAME = 'wires') THEN
stub := 'SCCSH';
sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id);
END IF;
EXECUTE sqlstr USING stub;
RETURN NEW;
END;
$$ language plpgsql""")
db.event.listen(db.metadata, 'before_create', auto_dealid)
def create_triggers(db):
trigger = db.DDL("""CREATE TRIGGER dealid
AFTER INSERT ON %(fullname)s
FOR EACH ROW
EXECUTE PROCEDURE auto_dealid()""")
for name, tb in db.metadata.tables.items():
if not name.endswith('counterparties'):
db.event.listen(tb, 'after_create', trigger)
if __name__ == "__main__":
""" This script will create the tables and triggers for the Dawn app.
If schema is not None, it will create it under a specific schema"""
#conn.execute("CREATE SCHEMA IF NOT EXISTS {}".format(schema))
#conn.execute("SET search_path TO %s", (schema,))
os.environ['CONF'] = 'config.ini'
from Dawn import db
create_trigger_function(db)
create_triggers(db)
db.create_all()
|
