def uninstall_trigger(connection: connection, table: str, schema: str = "public", triggerid: str = "") -> None: """Uninstall a psycopg2-pgevents trigger from a table. Parameters ---------- connection: psycopg2.extensions.connection Active connection to a PostGreSQL database. table: str Table for which the trigger should be uninstalled. schema: str Schema to which the table belongs. triggerid: str If there's more than 1 trigger in this database, we need to uniquely identify the trigger with an extra id Returns ------- None """ log("Uninstalling {}.{} trigger...".format(schema, table), logger_name=_LOGGER_NAME) if triggerid: if not valid_trigger_id(triggerid): raise ValueError("invalid triggerid = {}".format(triggerid)) triggerid = "_{triggerid}".format(triggerid=triggerid) statement = UNINSTALL_TRIGGER_STATEMENT.format(schema=schema, table=table, triggerid=triggerid) execute(connection, statement)
def install_trigger_function(connection: connection, overwrite: bool = False) -> None: """Install the psycopg2-pgevents trigger function against the database. Parameters ---------- connection: psycopg2.extensions.connection Active connection to a PostGreSQL database. overwrite: bool Whether or not to overwrite existing installation of psycopg2-pgevents trigger function, if existing installation is found. Returns ------- None """ prior_install = False if not overwrite: prior_install = trigger_function_installed(connection) if not prior_install: log("Installing trigger function...", logger_name=_LOGGER_NAME) execute(connection, INSTALL_TRIGGER_FUNCTION_STATEMENT) else: log("Trigger function already installed; skipping...", logger_name=_LOGGER_NAME)
def test_force_remove_trigger_function_with_dependent_triggers( self, connection): trigger_function_still_installed = True trigger_installed = False trigger.uninstall_trigger_function(connection, force=True) try: execute( connection, "SELECT pg_get_functiondef('public.psycopg2_pgevents_create_event'::regproc);" ) except ProgrammingError: trigger_function_still_installed = False statement = """ SELECT * FROM information_schema.triggers WHERE event_object_schema = 'settings' AND event_object_table = 'public'; """ result = execute(connection, statement) if not result: trigger_installed = False assert not trigger_function_still_installed assert not trigger_installed
def uninstall_trigger_function(connection: connection, force: bool = False) -> None: """Uninstall the psycopg2-pgevents trigger function from the database. Parameters ---------- connection: psycopg2.extensions.connection Active connection to a PostGreSQL database. force: bool If True, force the un-registration even if dependent triggers are still installed. If False, if there are any dependent triggers for the trigger function, the un-registration will fail. Returns ------- None """ modifier = "" if force: modifier = "CASCADE" log("Uninstalling trigger function (cascade={})...".format(force), logger_name=_LOGGER_NAME) statement = UNINSTALL_TRIGGER_FUNCTION_STATEMENT.format(modifier=modifier) execute(connection, statement)
def install_trigger(connection: connection, table: str, schema: str='public', overwrite: bool=False) -> None: """Install a psycopg2-pgevents trigger against a table. Parameters ---------- connection: psycopg2.extensions.connection Active connection to a PostGreSQL database. table: str Table for which the trigger should be installed. schema: str Schema to which the table belongs. overwrite: bool Whether or not to overwrite existing installation of trigger for the given table, if existing installation is found. Returns ------- None """ prior_install = False if not overwrite: prior_install = trigger_installed(connection, table, schema) if not prior_install: log('Installing {}.{} trigger...'.format(schema, table), logger_name=_LOGGER_NAME) statement = INSTALL_TRIGGER_STATEMENT.format( schema=schema, table=table ) execute(connection, statement) else: log('{}.{} trigger already installed; skipping...'.format(schema, table), logger_name=_LOGGER_NAME)
def install_trigger_function(connection: connection, rowid: str = "id", rowidtype: str = "int", overwrite: bool = False, triggerid: str = "") -> None: """Install the psycopg2-pgevents trigger function against the database. Parameters ---------- connection: psycopg2.extensions.connection Active connection to a PostGreSQL database. overwrite: bool Whether or not to overwrite existing installation of psycopg2-pgevents trigger function, if existing installation is found. rowid: string The id to return for the row, e.g. id, ordercode, etc default is "id" rowidtype: string The type to return for the rowid, e.g. int, text etc default is "int" triggerid: str If there's more than 1 trigger in this database, we need to uniquely identify the trigger with an extra id Returns ------- None """ prior_install = False # doublecheck rowid/rowidtypes in case None or "" are inputs if not rowid: rowid = "id" if not rowidtype: rowidtype = "int" if not overwrite: prior_install = trigger_function_installed(connection, triggerid) if triggerid: if not valid_trigger_id(triggerid): raise ValueError("invalid triggerid = {}".format(triggerid)) triggerid = "_{triggerid}".format(triggerid=triggerid) if not valid_rowid_type(rowidtype): raise ValueError("invalid rowidtype = {}".format(rowidtype)) if not prior_install: log("Installing trigger function...", logger_name=_LOGGER_NAME) execute( connection, INSTALL_TRIGGER_FUNCTION_STATEMENT.format(rowid=rowid, rowidtype=rowidtype, triggerid=triggerid)) else: log("Trigger function already installed; skipping...", logger_name=_LOGGER_NAME)
def test_execute_programming_error(self, connection): programming_exception_raised = False try: # Raise exception by referencing a non-existent table execute(connection, 'SELECT * FROM information_schema.triggerss;') except ProgrammingError: programming_exception_raised = True assert (programming_exception_raised == True)
def test_poll_public_schema_table_event(self, connection, client): execute(client, "INSERT INTO public.settings(key, value) VALUES('foo', 1);") evts = [evt for evt in event.poll(connection)] assert len(evts) == 1 evt = evts.pop(0) assert evt.type == "INSERT" assert evt.schema_name == "public" assert evt.table_name == "settings"
def test_poll_custom_schema_table_event(self, connection, client): execute(client, "INSERT INTO pointofsale.orders(description) VALUES('bar');") evts = [evt for evt in event.poll(connection)] assert len(evts) == 1 evt = evts.pop(0) assert evt.type == "INSERT" assert evt.schema_name == "pointofsale" assert evt.table_name == "orders"
def test_remove_trigger_function(self, connection): trigger_function_installed = True trigger.uninstall_trigger_function(connection) try: execute( connection, "SELECT pg_get_functiondef('public.psycopg2_pgevents_create_event'::regproc);" ) except ProgrammingError: trigger_function_installed = False assert not trigger_function_installed
def test_poll_custom_schema_table_event(self, connection, client): execute(client, "INSERT INTO pointofsale.orders(description) VALUES('bar');") events = [event for event in poll(connection)] assert (len(events) == 1) event = events.pop(0) assert (event.type == 'INSERT') assert (event.schema_name == 'pointofsale') assert (event.table_name == 'orders')
def test_poll_public_schema_table_event(self, connection, client): execute(client, "INSERT INTO public.settings(key, value) VALUES('foo', 1);") events = [event for event in poll(connection)] assert (len(events) == 1) event = events.pop(0) assert (event.type == 'INSERT') assert (event.schema_name == 'public') assert (event.table_name == 'settings')
def trigger_function_installed(connection: connection, triggerid: str = ""): """Test whether or not the psycopg2-pgevents trigger function is installed. Parameters ---------- connection: psycopg2.extensions.connection Active connection to a PostGreSQL database. triggerid: str If there's more than 1 trigger in this database, we need to uniquely identify the trigger with an extra id Returns ------- bool True if the trigger function is installed, otherwise False. """ installed = False log("Checking if trigger function installed...", logger_name=_LOGGER_NAME) if triggerid: if not valid_trigger_id(triggerid): raise ValueError("invalid triggerid = {}".format(triggerid)) triggerid = "_{triggerid}".format(triggerid=triggerid) try: execute( connection, "SELECT pg_get_functiondef('public.psycopg2_pgevents_create_event{triggerid}'::regproc);" .format(triggerid=triggerid), ) installed = True except ProgrammingError as e: if e.args: error_stdout = e.args[0].splitlines() error = error_stdout.pop(0) if error.endswith("does not exist"): # Trigger function not installed pass else: # Some other exception; re-raise raise e else: # Some other exception; re-raise raise e log("...{}installed".format("" if installed else "NOT "), logger_name=_LOGGER_NAME) return installed
def unregister_event_channel(connection: connection) -> None: """Un-register psycopg2-pgevents event channel from the database. Parameters ---------- connection: psycopg2.extensions.connection Active connection to a PostGreSQL database. Returns ------- None """ log('Unregistering psycopg2-pgevents channel...', logger_name=_LOGGER_NAME) execute(connection, 'UNLISTEN "psycopg2_pgevents_channel";')
def trigger_installed(connection: connection, table: str, schema: str = "public"): """Test whether or not a psycopg2-pgevents trigger is installed for a table. Parameters ---------- connection: psycopg2.extensions.connection Active connection to a PostGreSQL database. table: str Table whose trigger-existence will be checked. schema: str Schema to which the table belongs. Returns ------- bool True if the trigger is installed, otherwise False. """ installed = False log("Checking if {}.{} trigger installed...".format(schema, table), logger_name=_LOGGER_NAME) statement = SELECT_TRIGGER_STATEMENT.format(table=table, schema=schema) result = execute(connection, statement) if result: installed = True log("...{}installed".format("" if installed else "NOT "), logger_name=_LOGGER_NAME) return installed
def test_add_trigger_function(self, connection): trigger_function_installed = False trigger.install_trigger_function(connection) try: execute( connection, "SELECT pg_get_functiondef('public.psycopg2_pgevents_create_event'::regproc);" ) trigger_function_installed = True except: # noqa: E722 # Ignore error, its only use in this test is cause following # assertion to fail pass assert trigger_function_installed
def test_poll_multiple_table_evts(self, connection, client): execute(client, "INSERT INTO public.settings(key, value) VALUES('foo', 1);") execute(client, "INSERT INTO pointofsale.orders(description) VALUES('bar');") evts = [evt for evt in event.poll(connection)] assert len(evts) == 2 evt = evts.pop(0) assert evt.type == "INSERT" assert evt.schema_name == "public" assert evt.table_name == "settings" evt = evts.pop(0) assert evt.type == "INSERT" assert evt.schema_name == "pointofsale" assert evt.table_name == "orders"
def test_poll_multiple_table_events(self, connection, client): execute(client, "INSERT INTO public.settings(key, value) VALUES('foo', 1);") execute(client, "INSERT INTO pointofsale.orders(description) VALUES('bar');") events = [event for event in poll(connection)] assert (len(events) == 2) event = events.pop(0) assert (event.type == 'INSERT') assert (event.schema_name == 'public') assert (event.table_name == 'settings') event = events.pop(0) assert (event.type == 'INSERT') assert (event.schema_name == 'pointofsale') assert (event.table_name == 'orders')
def test_unregister_event_channel(self, connection): channel_registered = True event.unregister_event_channel(connection) results = execute(connection, "SELECT pg_listening_channels();") if not results: channel_registered = False assert not channel_registered
def test_register_event_channel(self, connection): channel_registered = False event.register_event_channel(connection) results = execute(connection, "SELECT pg_listening_channels();") if results and "psycopg2_pgevents_channel" in results[0]: channel_registered = True assert channel_registered
def test_register_event_channel(self, connection): channel_registered = False register_event_channel(connection) results = execute(connection, 'SELECT pg_listening_channels();') if results and 'psycopg2_pgevents_channel' in results[0]: channel_registered = True assert (channel_registered == True)
def trigger_function_installed(connection: connection): """Test whether or not the psycopg2-pgevents trigger function is installed. Parameters ---------- connection: psycopg2.extensions.connection Active connection to a PostGreSQL database. Returns ------- bool True if the trigger function is installed, otherwise False. """ installed = False log("Checking if trigger function installed...", logger_name=_LOGGER_NAME) try: execute( connection, "SELECT pg_get_functiondef('public.psycopg2_pgevents_create_event'::regproc);" ) installed = True except ProgrammingError as e: if e.args: error_stdout = e.args[0].splitlines() error = error_stdout.pop(0) if error.endswith("does not exist"): # Trigger function not installed pass else: # Some other exception; re-raise raise e else: # Some other exception; re-raise raise e log("...{}installed".format("" if installed else "NOT "), logger_name=_LOGGER_NAME) return installed
def test_poll_event_event_types(self, connection, client): execute(client, "INSERT INTO public.settings(key, value) VALUES('foo', 1);") evts = [evt for evt in event.poll(connection)] assert len(evts) == 1 evt = evts.pop(0) assert evt.type == "INSERT" assert evt.schema_name == "public" assert evt.table_name == "settings" execute(client, "UPDATE public.settings SET value = 2 WHERE id = {row_id};".format(row_id=evt.row_id)) execute(client, "DELETE FROM public.settings WHERE id = {row_id};".format(row_id=evt.row_id)) evts = [evt for evt in event.poll(connection)] assert len(evts) == 2 evt = evts.pop(0) assert evt.type == "UPDATE" assert evt.schema_name == "public" assert evt.table_name == "settings" evt = evts.pop(0) assert evt.type == "DELETE" assert evt.schema_name == "public" assert evt.table_name == "settings"
def test_remove_trigger_function_with_dependent_triggers(self, connection): trigger_function_removal_failed = False trigger_function_still_installed = False try: trigger.uninstall_trigger_function(connection) except InternalError: trigger_function_removal_failed = True try: execute( connection, "SELECT pg_get_functiondef('public.psycopg2_pgevents_create_event'::regproc);" ) trigger_function_still_installed = True except: # noqa: E722 # Ignore error, its only use in this test is cause following # assertion to fail pass assert trigger_function_removal_failed assert trigger_function_still_installed
def uninstall_trigger(connection: connection, table: str, schema: str = "public") -> None: """Uninstall a psycopg2-pgevents trigger from a table. Parameters ---------- connection: psycopg2.extensions.connection Active connection to a PostGreSQL database. table: str Table for which the trigger should be uninstalled. schema: str Schema to which the table belongs. Returns ------- None """ log("Uninstalling {}.{} trigger...".format(schema, table), logger_name=_LOGGER_NAME) statement = UNINSTALL_TRIGGER_STATEMENT.format(schema=schema, table=table) execute(connection, statement)
def uninstall_trigger_function(connection: connection, force: bool = False, triggerid: str = "") -> None: """Uninstall the psycopg2-pgevents trigger function from the database. Parameters ---------- connection: psycopg2.extensions.connection Active connection to a PostGreSQL database. force: bool If True, force the un-registration even if dependent triggers are still installed. If False, if there are any dependent triggers for the trigger function, the un-registration will fail. triggerid: str If there's more than 1 trigger in this database, we need to uniquely identify the trigger with an extra id Returns ------- None """ modifier = "" if force: modifier = "CASCADE" if triggerid: if not valid_trigger_id(triggerid): raise ValueError("invalid triggerid = {}".format(triggerid)) triggerid = "_{triggerid}".format(triggerid=triggerid) log("Uninstalling trigger function (cascade={})...".format(force), logger_name=_LOGGER_NAME) statement = UNINSTALL_TRIGGER_FUNCTION_STATEMENT.format( modifier=modifier, triggerid=triggerid) execute(connection, statement)
def trigger_installed(connection: connection, table: str, schema: str = "public", triggerid: str = ""): """Test whether or not a psycopg2-pgevents trigger is installed for a table. Parameters ---------- connection: psycopg2.extensions.connection Active connection to a PostGreSQL database. table: str Table whose trigger-existence will be checked. schema: str Schema to which the table belongs. triggerid: str If there's more than 1 trigger in this database, we need to uniquely identify the trigger with an extra id Returns ------- bool True if the trigger is installed, otherwise False. """ installed = False log("Checking if {}.{} trigger installed...".format(schema, table), logger_name=_LOGGER_NAME) if triggerid: if not valid_trigger_id(triggerid): raise ValueError("invalid triggerid = {}".format(triggerid)) triggerid = "_{triggerid}".format(triggerid=triggerid) statement = SELECT_TRIGGER_STATEMENT.format(table=table, schema=schema, triggerid=triggerid) result = execute(connection, statement) if result: installed = True log("...{}installed".format("" if installed else "NOT "), logger_name=_LOGGER_NAME) return installed
def test_remove_public_schema_trigger(self, connection): trigger_installed = True trigger.uninstall_trigger(connection, "settings") statement = """ SELECT * FROM information_schema.triggers WHERE event_object_schema = 'settings' AND event_object_table = 'public'; """ result = execute(connection, statement) if not result: trigger_installed = False assert not trigger_installed
def test_remove_custom_schema_trigger(self, connection): trigger_installed = True trigger.uninstall_trigger(connection, "orders", schema="pointofsale") statement = """ SELECT * FROM information_schema.triggers WHERE event_object_schema = 'orders' AND event_object_table = 'pointofsale'; """ result = execute(connection, statement) if not result: trigger_installed = False assert not trigger_installed
def test_add_public_schema_trigger(self, connection): trigger_installed = False trigger.install_trigger(connection, "settings") try: statement = """ SELECT * FROM information_schema.triggers WHERE event_object_schema = 'public' AND event_object_table = 'settings'; """ result = execute(connection, statement) if result: trigger_installed = True except: # noqa: E722 # Ignore error, its only use in this test is cause following # assertion to fail pass assert trigger_installed