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
