diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/Dawn/utils.py | 15 | ||||
| -rw-r--r-- | python/dawn_utils.py | 60 |
2 files changed, 60 insertions, 15 deletions
diff --git a/python/Dawn/utils.py b/python/Dawn/utils.py index 37350949..99451080 100644 --- a/python/Dawn/utils.py +++ b/python/Dawn/utils.py @@ -1,5 +1,4 @@ import pandas as pd -from sqlalchemy.sql import text import os import re from pickle import dumps @@ -54,20 +53,6 @@ def load_trades(engine): blotter.to_sql('bonds', engine, if_exists='append', index=False) return blotter -def add_triggers(engine): - engine.execute(text(""" - CREATE OR REPLACE FUNCTION mydealid() - RETURNS TRIGGER AS $$ - BEGIN - UPDATE bonds SET dealid = 'SC_' ||upper(left(asset_class::text,3))||id, - identifier = COALESCE(identifier, cusip, isin) WHERE bonds.id = NEW.id; - RETURN NEW; - END; - $$ language plpgsql""")) - engine.execute(text("""CREATE TRIGGER dealid - AFTER INSERT ON bonds - FOR EACH ROW - EXECUTE PROCEDURE mydealid()""")) def bump_rev(filename): pattern = "([^r]*)(\srev(\d)|).pdf" diff --git a/python/dawn_utils.py b/python/dawn_utils.py new file mode 100644 index 00000000..b2c7ae49 --- /dev/null +++ b/python/dawn_utils.py @@ -0,0 +1,60 @@ +from sqlalchemy.sql import text +from sqlalchemy import create_engine, MetaData +import os + +def create_trigger_function(conn): + auto_dealid = text(""" + 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); + END IF; + EXECUTE sqlstr USING stub; + RETURN NEW; + END; + $$ language plpgsql""") + conn.execute(auto_dealid) + +def create_triggers(schema = None): + trigger = db.DDL("""CREATE TRIGGER dealid + AFTER INSERT ON %(fullname)s + FOR EACH ROW + EXECUTE PROCEDURE %(schema)s.auto_dealid()""") + for name, tb in db.metadata.tables.items(): + if schema is not None: + tb.schema = schema + 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""" + engine = create_engine('postgresql://dawn_user@debian/dawndb') + conn = engine.connect() + schema = 'qa' + conn.execute("CREATE SCHEMA IF NOT EXISTS {}".format(schema)) + conn.execute("SET search_path TO %s", (schema,)) + create_trigger_function(conn) + conn.close() + + os.environ['CONF'] = 'config.ini' + from Dawn import db, app + db.init_app(app) + with app.app_context(): + create_triggers('qa') + db.create_all() |