def insert_admin_feature(): from general.domains.admin.models.feature_sets import FeatureSets from general.domains.admin.models.feature_sets_users import FeatureSetsUsers from general.domains.auth.models.users import Users with session_scope(raise_integrity_error=False) as session: new_feature_set = FeatureSets() new_feature_set.name = 'admin' session.add(new_feature_set) with session_scope(raise_integrity_error=False) as session: admin_feature_set = ( session.query(FeatureSets) .filter(FeatureSets.name == 'admin') .one() ) user_role = os.environ['PGUSER'] try: user = ( session.query(Users) .filter(Users.role == user_role) .one() ) except NoResultFound: user = Users() user.role = user_role user.active = True session.add(user) session.commit() new_feature_sets_users = FeatureSetsUsers() new_feature_sets_users.user_id = user.id new_feature_sets_users.feature_set_id = admin_feature_set.id session.add(new_feature_sets_users)
def insert_transactions(): for TransactionType in [ BankingTransactions, BitfinexTransactions, BitstampTransactions, BittrexTransactions, BtceTransactions, CoinapultTransactions, CryptsyTransactions, GeminiTransactions, GenesisTransactions, HavelockTransactions, KrakenTransactions, MtgoxTransactions, PoloniexTransactions ]: file_names = list( glob.iglob(data_directory + TransactionType.file_pattern, recursive=True)) for file_name in file_names: logging.info(file_name) user_name = file_name.split('/')[-2] with session_scope() as session: user = ( session.query(Users) .filter(Users.role == user_name) .one() ) user_id = user.id records = TransactionType.process_file(file_name=file_name, user_id=user_id) logging.info(f'Found {len(records)} records') tmp_file_path = data_directory + '/tmp.csv' with open(tmp_file_path, 'w', newline='') as csvfile: field_names = set().union(*(d.keys() for d in records)) writer = csv.DictWriter(csvfile, fieldnames=field_names) writer.writeheader() for record in records: writer.writerow(record) flags = {'format': 'csv', 'header': True} with session_scope(database='bitcoinadvisory') as session: with open(tmp_file_path, 'rb') as fp: try: postgres_copy.copy_from(fp, TransactionType, session.connection(), field_names, **flags) except psycopg2.IntegrityError as exc: logging.warn(exc) os.remove(tmp_file_path)
def create_submenu_items_view(): with session_scope() as session: session.execute(""" DROP VIEW IF EXISTS admin.submenu_items CASCADE; """) session.execute(""" CREATE OR REPLACE VIEW admin.submenu_items AS SELECT dts.user, dts.id, dts.custom_name AS "label", dts.icon, ARRAY['/', dts.schema_name, dts.table_name] AS "routerLink", dts.order_index, NULL as "items", dts.submenu_id FROM admin.default_datatable_settings dts WHERE dts.submenu_id IS NOT NULL UNION SELECT dfs.user, dfs.id, dfs.custom_name AS "label", dfs.icon, ARRAY['/', dfs.schema_name, 'rpc', dfs.form_name] AS "routerLink", dfs.order_index, NULL as "items", dfs.submenu_id FROM admin.default_form_settings dfs WHERE dfs.submenu_id IS NOT NULL ORDER BY order_index ASC NULLS LAST, "label" ASC NULLS LAST; """)
def create_default_form_settings_view(): with session_scope() as session: session.execute(""" DROP VIEW IF EXISTS admin.default_form_settings CASCADE; """) session.execute(""" CREATE OR REPLACE VIEW admin.default_form_settings AS SELECT coalesce(fs.id, auth.gen_random_uuid()) as id, u.role as "user", u.id as user_id, f.form_name, f.schema_name, coalesce(fs.custom_button_copy, 'Submit') AS custom_button_copy, coalesce(fs.custom_name, initcap(replace(f.form_name, '_', ' '))) as custom_name, fs.submenu_id, coalesce(fs.icon, 'fa-pencil-square-o') AS icon, coalesce(fs.is_visible, TRUE) AS is_visible, coalesce(fs.order_index, 99) AS order_index, row_to_json(ds)::JSONB AS dialog_settings FROM auth.users u LEFT OUTER JOIN admin.forms f ON TRUE LEFT OUTER JOIN admin.form_settings fs ON f.form_name = fs.form_name AND f.schema_name = fs.schema_name AND u.id = fs.user_id LEFT OUTER JOIN admin.dialog_settings ds ON fs.dialog_settings_id = ds.id AND fs.user_id = ds.user_id ORDER BY u.role, f.schema_name, f.form_name; """)
def create_jwt_algorithm_sign_function(): with session_scope() as session: session.execute(""" DROP FUNCTION IF EXISTS auth.jwt_algorithm_sign(signables TEXT, secret TEXT, algorithm TEXT); """) session.execute(""" CREATE OR REPLACE FUNCTION auth.jwt_algorithm_sign(signables TEXT, secret TEXT, algorithm TEXT) RETURNS TEXT LANGUAGE SQL AS $$ WITH alg AS ( SELECT CASE WHEN algorithm = 'HS256' THEN 'sha256' WHEN algorithm = 'HS384' THEN 'sha384' WHEN algorithm = 'HS512' THEN 'sha512' ELSE '' END AS id) -- hmac throws error SELECT auth.jwt_url_encode(auth.hmac(signables, secret, alg.id)) FROM alg; $$; """)
def create_token_api_trigger(): with session_scope() as session: session.execute(""" DROP FUNCTION IF EXISTS auth_api.token() CASCADE; """) session.execute(""" CREATE OR REPLACE FUNCTION auth_api.token() RETURNS auth.JWT_TOKEN LANGUAGE plpgsql AS $$ DECLARE result auth.JWT_TOKEN; BEGIN SELECT auth.jwt_sign(row_to_json(r), current_setting('app.jwt_ws_secret')) AS token FROM ( SELECT 'anon' AS role, 'rw' AS mode, extract(EPOCH FROM now()) :: INTEGER + current_setting('app.jwt_hours')::INTEGER * 60 * 60 AS exp ) r INTO result; RETURN result; END; $$; """)
def create_output_entries_api_view(): with session_scope() as session: session.execute(""" DROP VIEW IF EXISTS wallet_api.output_entries CASCADE; """) session.execute(""" CREATE OR REPLACE VIEW wallet_api.output_entries AS SELECT oe.id, t.time_received, t.txid, oe.vector AS vout, oe.category, oe.amount*1000000 AS bits, oe.destination, a.name, a.purpose FROM wallet.output_entries oe LEFT JOIN wallet.transactions t ON oe.etransaction_id = t.id LEFT JOIN wallet.addresses a ON (a.p2pkh_address = oe.destination OR a.sw_bech32_address = oe.destination OR a.sw_p2sh_address = oe.destination) WHERE oe.role = current_user ORDER BY t.time_received DESC; """)
def create_wallet_gemini_reconciliation_view(): with session_scope() as session: session.execute(""" DROP VIEW IF EXISTS exchanges.wallet_gemini_reconciliation CASCADE; """) session.execute(""" CREATE OR REPLACE VIEW exchanges.wallet_gemini_reconciliation AS SELECT t.date, t.transaction_type, t.symbol, t.specification, t.tx_hash, t.deposit_tx_output, t.withdrawal_tx_output, t.withdrawal_destination, oe.id as output_entry_id, oe.time_received as wallet_time_received, oe.name as wallet_name, abs(t.btc_amount*1000000) as gemini_bits, oe.bits as wallet_bits, abs(t.btc_amount*1000000) - coalesce(oe.bits, 0) AS amount_rec FROM exchanges.gemini_transactions t LEFT OUTER JOIN wallet_api.output_entries oe ON oe.txid = t.tx_hash AND ( oe.vout = t.withdrawal_tx_output OR oe.vout = t.deposit_tx_output ) JOIN auth.users u ON u.id = t.user_id WHERE t.tx_hash IS NOT NULL ORDER BY t.date DESC """)
def create_jwt_sign_function(): with session_scope() as session: session.execute(""" DROP FUNCTION IF EXISTS auth.jwt_sign(payload JSON, secret TEXT) CASCADE; """) session.execute(""" CREATE OR REPLACE FUNCTION auth.jwt_sign(payload JSON, secret TEXT) RETURNS TEXT LANGUAGE SQL AS $$ WITH header AS ( SELECT auth.jwt_url_encode( convert_to('{"alg":"HS256","typ":"JWT"}', 'utf8')) AS data ), payload AS ( SELECT auth.jwt_url_encode(convert_to(payload :: TEXT, 'utf8')) AS data ), signables AS ( SELECT header.data || '.' || payload.data AS data FROM header, payload ) SELECT signables.data || '.' || auth.jwt_algorithm_sign(signables.data, secret, 'HS256') FROM signables; $$; """)
def create_addresses_view(): with session_scope() as session: session.execute(""" DROP VIEW IF EXISTS wallet.addresses CASCADE; """) session.execute(""" CREATE OR REPLACE VIEW wallet.addresses AS SELECT ea.id, ea.p2pkh_address, ea.sw_p2sh_address, ea.sw_bech32_address, ea.purpose, ea.name, ea.is_used, ea.time, u.role FROM wallet."eAddresses" ea JOIN wallet."eWallets" ew ON ew.wallet_id = ea.wallet_id JOIN wallet.wallets_users wu ON wu.wallet_id = ew.wallet_id JOIN auth.users u ON u.id = wu.user_id """)
def create_output_entries_view(): with session_scope() as session: session.execute(""" DROP VIEW IF EXISTS wallet.output_entries CASCADE; """) session.execute(""" CREATE OR REPLACE VIEW wallet.output_entries AS SELECT eoe.id, to_timestamp(output_eb.time) AS output_time, eoe.is_output_mine, eoe.n_value::DECIMAL/100000000 AS n_value, to_timestamp(input_eb.time) AS input_time, output_et.hash AS output_hash, eoe.output_vector, input_et.hash AS input_hash, eoe.input_vector, eoe.destination FROM wallet."eOutputEntries" eoe JOIN wallet."eTransactions" output_et ON output_et.id = eoe.output_etransaction_id LEFT OUTER JOIN wallet."eTransactions" input_et ON input_et.id = eoe.input_etransaction_id JOIN wallet."eBlocks" output_eb ON output_et.block_id = output_eb.id LEFT OUTER JOIN wallet."eBlocks" input_eb ON input_et.block_id = input_eb.id ORDER BY output_time ASC; """)
def create_table_notifications(source_schema_name, source_table_name, target_schema_name, target_table_name): with session_scope() as session: session.execute(''' CREATE OR REPLACE FUNCTION {source_schema_name}_{source_table_name}_notify() RETURNS TRIGGER AS $$ DECLARE id INT; payload TEXT; json_record JSON; notification_message_settings RECORD; BEGIN IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN id = NEW.id; json_record = row_to_json(NEW); ELSE id = OLD.id; json_record = row_to_json(OLD); END IF; SELECT * INTO notification_message_settings FROM admin.notification_message_settings nms WHERE nms.namespace = '{target_schema_name}.{target_table_name}' AND nms.message_type = TG_OP; payload = json_build_object('id', id, 'schema_name', '{target_schema_name}', 'table_name', '{target_table_name}', 'type', TG_OP, 'is_visible', notification_message_settings.is_visible, 'severity', notification_message_settings.severity, 'summary', notification_message_settings.summary, 'detail', notification_message_settings.detail)::TEXT; PERFORM pg_notify('updates', payload); RETURN NEW; END; $$ LANGUAGE plpgsql; '''.format(source_schema_name=source_schema_name, source_table_name=source_table_name, target_schema_name=target_schema_name, target_table_name=target_table_name)) session.execute(''' DROP TRIGGER IF EXISTS {source_schema_name}_{source_table_name}_notify_update ON {source_schema_name}."{source_table_name}"; CREATE TRIGGER {source_schema_name}_{source_table_name}_notify_update AFTER UPDATE ON {source_schema_name}."{source_table_name}" FOR EACH ROW EXECUTE PROCEDURE {source_schema_name}_{source_table_name}_notify(); DROP TRIGGER IF EXISTS {source_schema_name}_{source_table_name}_notify_insert ON {source_schema_name}."{source_table_name}"; CREATE TRIGGER {source_schema_name}_{source_table_name}_notify_insert AFTER INSERT ON {source_schema_name}."{source_table_name}" FOR EACH ROW EXECUTE PROCEDURE {source_schema_name}_{source_table_name}_notify(); DROP TRIGGER IF EXISTS {source_schema_name}_{source_table_name}_notify_delete ON {source_schema_name}."{source_table_name}"; CREATE TRIGGER {source_schema_name}_{source_table_name}_notify_delete AFTER DELETE ON {source_schema_name}."{source_table_name}" FOR EACH ROW EXECUTE PROCEDURE {source_schema_name}_{source_table_name}_notify(); '''.format(source_schema_name=source_schema_name, source_table_name=source_table_name))
def insert_form_settings(): form_settings_path = os.path.join(script_path, 'form_settings') form_settings_file_names = os.listdir(form_settings_path) for file_name in form_settings_file_names: file_path = os.path.join(form_settings_path, file_name) with open(file_path) as data_file: form_settings_list = json.load(data_file) for form_settings in form_settings_list: with session_scope() as session: for user in session.query(Users).all(): try: form_settings_record = ( session.query(FormSettings).filter( and_( FormSettings.user_id == user.id, FormSettings.form_name == form_settings['form_name'], FormSettings.schema_name == form_settings['schema_name'])).one()) except NoResultFound: form_settings_record = FormSettings() form_settings_record.user_id = user.id form_settings_record.form_name = form_settings[ 'form_name'] form_settings_record.schema_name = form_settings[ 'schema_name'] session.add(form_settings_record) session.commit() for key, value in form_settings.items(): setattr(form_settings_record, key, value)
def insert_table_column_settings(): schema_name = 'wallet' tcs_path = os.path.join(script_path, 'table_column_settings') file_names = list(glob.iglob(tcs_path + '/*.json', recursive=True)) print(pformat(file_names)) for file_name in file_names: with open(file_name) as data_file: table_column_settings = json.load(data_file) with session_scope() as session: for user in session.query(Users).all(): for table_column_setting in table_column_settings: column_name = table_column_setting['column_name'] table_name = table_column_setting['table_name'] try: record = (session.query(TableColumnSettings).filter( and_( TableColumnSettings.schema_name == schema_name, TableColumnSettings.table_name == table_name, TableColumnSettings.column_name == column_name, TableColumnSettings.user_id == user.id)).one()) except NoResultFound: record = TableColumnSettings() record.user_id = user.id record.schema_name = schema_name record.table_name = table_name record.column_name = column_name session.add(record) session.commit() for key, value in table_column_setting.items(): if not hasattr(record, key): print( f'Warning: {key} from {file_name} does not exist in TCS' ) setattr(record, key, value)
def create_check_if_role_exists_function(): with session_scope() as session: session.execute(""" DROP FUNCTION IF EXISTS auth.check_if_role_exists() CASCADE; """) session.execute(""" CREATE OR REPLACE FUNCTION auth.check_if_role_exists() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN IF NOT exists(SELECT 1 FROM pg_roles WHERE pg_roles.rolname = NEW.role) THEN RAISE foreign_key_violation USING MESSAGE = 'Unknown database role: ' || NEW.role; RETURN NULL; END IF; RETURN NEW; END $$; """)
def insert_dialog_settings(): dialog_settings_path = os.path.join(script_path, 'dialog_settings') dialog_settings_file_names = os.listdir(dialog_settings_path) for file_name in dialog_settings_file_names: file_path = os.path.join(dialog_settings_path, file_name) with open(file_path) as data_file: dialog_settings_list = json.load(data_file) for dialog_settings in dialog_settings_list: with session_scope() as session: for user in session.query(Users).all(): form_settings_record = (session.query(FormSettings).filter( and_( FormSettings.user_id == user.id, FormSettings.form_name == dialog_settings['form_name'], FormSettings.schema_name == dialog_settings['schema_name'])).one()) if not form_settings_record.dialog_settings_id: dialog_settings_record = DialogSettings() dialog_settings_record.user_id = user.id dialog_settings_record.name = dialog_settings['name'] session.add(dialog_settings_record) session.commit() form_settings_record.dialog_settings_id = dialog_settings_record.id else: dialog_settings_record = ( session.query(DialogSettings).filter( DialogSettings.id == form_settings_record.dialog_settings_id).one()) for key, value in dialog_settings.items(): setattr(dialog_settings_record, key, value)
def create_transactions_view(): with session_scope() as session: session.execute(""" DROP VIEW IF EXISTS wallet.transactions CASCADE; """) session.execute(""" CREATE OR REPLACE VIEW wallet.transactions AS SELECT et.id, et.block_index, et.is_trusted, et.size, to_timestamp(et.time) AS time, to_timestamp(et.time_received) AS time_received, et.txid, u.role, ew.wallet_id FROM wallet."eTransactions" et JOIN wallet."eWallets" ew ON ew.wallet_id = et.wallet_id JOIN wallet.wallets_users wu ON wu.wallet_id = ew.wallet_id JOIN auth.users u ON u.id = wu.user_id; """)
def insert_user_feature(): """ The user feature set is the opposite of the admin feature set. Hide the admin tables. """ schema_name = 'admin' api_view_names = [ 'datatable_columns', 'datatables', 'form_fields', 'forms', 'home' ] with session_scope() as session: users = (session.query(Users).outerjoin( FeatureSetsUsers, FeatureSetsUsers.user_id == Users.id).outerjoin( FeatureSets, FeatureSetsUsers.feature_set_id == FeatureSets.id).filter( FeatureSets.name.is_(None)).all()) user_ids = [user.id for user in users] for user in users: for api_view_name in api_view_names: try: menubar_view_setting = ( session.query(TableSettings).filter( TableSettings.user_id == user.id).filter( TableSettings.table_name == api_view_name). filter(TableSettings.schema_name == schema_name).one()) except NoResultFound: menubar_view_setting_data = { 'schema_name': schema_name, 'table_name': api_view_name, 'user_id': user.id } menubar_view_setting = TableSettings( **menubar_view_setting_data) session.add(menubar_view_setting) session.commit() menubar_view_setting.is_visible = False
def create_types(): with session_scope(raise_programming_error=False) as session: session.execute(""" CREATE TYPE auth.jwt_token AS ( token TEXT ); """)
def insert_context_menu_items(): path = os.path.join(script_path, 'context_menu_items') file_names = os.listdir(path) for file_name in file_names: file_path = os.path.join(path, file_name) with open(file_path) as data_file: record_data_list = json.load(data_file) for record_data in record_data_list: with session_scope() as session: for user in session.query(Users).all(): try: record = (session.query(ContextMenuItems).filter( and_( ContextMenuItems.schema_name == record_data['schema_name'], ContextMenuItems.table_name == record_data['table_name'], ContextMenuItems.label == record_data['label'], ContextMenuItems.user_id == user.id)).one()) for key, value in record_data.items(): setattr(record, key, value) except NoResultFound: record = ContextMenuItems(**record_data) record.user_id = user.id session.add(record) session.commit()
def has_been_tweeted(subject_type, subject_id) -> bool: with session_scope() as session: try: (session.query(Tweets).filter( and_(Tweets.subject_type == subject_type, Tweets.subject_id == subject_id)).one()) return True except NoResultFound: return False
def create_triggers_on_users(): with session_scope() as session: session.execute(""" DROP TRIGGER IF EXISTS encrypt_password ON auth.users; CREATE TRIGGER encrypt_password BEFORE INSERT OR UPDATE ON auth.users FOR EACH ROW EXECUTE PROCEDURE auth.encrypt_password(); """)
def create_constraint_triggers_on_users(): with session_scope() as session: session.execute(""" DROP TRIGGER IF EXISTS ensure_user_role_exists ON auth.users; CREATE CONSTRAINT TRIGGER ensure_user_role_exists AFTER INSERT OR UPDATE ON auth.users FOR EACH ROW EXECUTE PROCEDURE auth.check_if_role_exists(); """)
def get_blocks(start_height: int, last_height: int) -> pd.DataFrame: with session_scope() as session: query = (session.query(EBlocks).filter( and_(EBlocks.height >= start_height, EBlocks.height <= last_height))) df = pd.read_sql(query.statement, session.bind) df = df.sort_values('height') for column in ['time', 'median_time']: df[column] = df[column].apply( lambda x: pd.to_datetime(x, unit='s')) return df
def get_latest_block(limit: int = 1) -> pd.DataFrame: log.debug('Getting latest block from db') with session_scope() as session: query = (session.query(EBlocks).order_by( EBlocks.height.desc()).limit(limit)) df = pd.read_sql(query.statement, session.bind) df = df.sort_values('height') for column in ['time', 'median_time']: df[column] = df[column].apply( lambda x: pd.to_datetime(x, unit='s')) return df
def create_logout_api_trigger(): with session_scope() as session: session.execute(""" CREATE OR REPLACE FUNCTION auth_api.logout() RETURNS VOID LANGUAGE plpgsql AS $$ BEGIN END; $$; """)
def create_schemas_materialized_view(): with session_scope() as session: session.execute(""" DROP MATERIALIZED VIEW IF EXISTS admin.schemas CASCADE; """) session.execute(""" CREATE MATERIALIZED VIEW admin.schemas AS SELECT replace(schema_name, '_api', '') AS schema_name FROM information_schema.schemata WHERE schema_name LIKE '%_api'; """)
def create_forms_materialized_view(): with session_scope() as session: session.execute(""" DROP MATERIALIZED VIEW IF EXISTS admin.forms CASCADE; """) session.execute(""" CREATE MATERIALIZED VIEW admin.forms AS SELECT replace(specific_schema, '_api', '') AS schema_name, routine_name as form_name FROM information_schema.routines WHERE specific_schema LIKE '%_api'; """)
def insert_anon(): from general.domains.auth.models import Users with session_scope() as session: try: session.execute(""" CREATE ROLE anon noinherit; """) except ProgrammingError: pass with session_scope() as session: try: user = ( session.query(Users) .filter(Users.role == 'anon') .one() ) except NoResultFound: user = Users() user.role = 'anon' user.active = True session.add(user)
def create_datatables_view(): with session_scope() as session: session.execute(""" DROP VIEW IF EXISTS admin_api.datatables CASCADE; """) session.execute(""" CREATE OR REPLACE VIEW admin_api.datatables AS SELECT (row_number() OVER())::INT id, * FROM ( SELECT dts.can_archive, dts.can_delete, dts.custom_name, dts.order_index, dts.row_limit, dts.row_offset, dts.schema_name, dts.sort_column, dts.sort_order, dts.table_name, dts.user_id, dts.context_menu_items, map.mapper_settings FROM admin.default_datatable_settings dts LEFT OUTER JOIN ( SELECT mq.table_settings_id, row_to_json(mq)::JSONB AS "mapper_settings" FROM ( SELECT ms.table_settings_id, row_to_json(fcdc) AS filter_column, row_to_json(mcdc) AS mapping_column, row_to_json(smcdc) AS saved_keyword_column, row_to_json(skdc) AS saved_mapping_column FROM ADMIN.mapper_settings MS LEFT JOIN ADMIN.default_datatable_column_settings fcdc ON fcdc.id = MS.filter_column_settings_id LEFT JOIN ADMIN.default_datatable_column_settings mcdc ON mcdc.id = MS.mapping_column_settings_id LEFT JOIN ADMIN.default_datatable_column_settings smcdc ON smcdc.id = MS.saved_mapping_column_settings_id LEFT JOIN ADMIN.default_datatable_column_settings skdc ON skdc.id = MS.saved_keyword_column_settings_id ) mq ) map ON dts.id = map.table_settings_id WHERE dts.user = current_user ) sub; """)