def validate_set_configuration(configuration):
for table in configuration.tables:
assert len(
table.primary_keys
) > 0, 'table %s.%s must have associated primary key column(s)' % (
quote(table.schema),
quote(table.name),
)
def handle_insert(state, operation):
statement = 'INSERT INTO {schema}.{table} ({columns}) VALUES ({placeholders})'.format(
schema=quote(operation.schema),
table=quote(operation.table),
columns=', '.join(map(quote, map(operator.attrgetter('name'), operation.new.columns))),
placeholders=', '.join(['%s' for _ in operation.new.columns]),
)
parameters = map(operator.itemgetter(1), map(column_converter.to_python, operation.new.columns))
return statement, parameters
def drop_trigger(cluster, cursor, name, schema, table):
"""
Drops a log trigger on the provided table for the specified replication set.
"""
logger.info('Dropping log trigger on %s.%s...', schema, table)
cursor.execute('DROP TRIGGER {name} ON {schema}.{table}'.format(
name=quote(cluster.get_trigger_name(name)),
schema=quote(schema),
table=quote(table),
))
def drop_trigger(cluster, cursor, name, schema, table):
"""
Drops a log trigger on the provided table for the specified replication set.
"""
logger.info('Dropping log trigger on %s.%s...', schema, table)
cursor.execute('DROP TRIGGER {name} ON {schema}.{table}'.format(
name=quote(cluster.get_trigger_name(name)),
schema=quote(schema),
table=quote(table),
))
def handle_delete(state, operation):
constraints = get_identity_constraints(operation)
statement = 'DELETE FROM {schema}.{table} WHERE {constraints}'.format(
schema=quote(operation.schema),
table=quote(operation.table),
constraints=', '.join(['%s = %%s' % quote(c[0]) for c in constraints]),
)
parameters = [c[1] for c in constraints]
return statement, parameters
def handle_update(state, operation):
constraints = get_identity_constraints(operation)
statement = 'UPDATE {schema}.{table} SET ({columns}) = ({placeholders}) WHERE {constraints}'.format(
schema=quote(operation.schema),
table=quote(operation.table),
columns=', '.join(map(quote, map(operator.attrgetter('name'), operation.new.columns))),
placeholders=', '.join(['%s' for _ in operation.new.columns]),
constraints=', '.join(['%s = %%s' % quote(c[0]) for c in constraints]),
)
parameters = map(operator.itemgetter(1), map(column_converter.to_python, operation.new.columns)) + [c[1] for c in constraints]
return statement, parameters
def load(self, table, records):
# TODO: This should lock and truncate table (check to make sure there
# are no rows first?), as well as deal with dropping indexes, foreign
# keys, etc for a more efficient load.
with self.connection.cursor() as cursor:
for record in records:
# TODO: This would be better to move out of the loop, if
# possible. The column list would need to be exported by the
# loader itself. (This is probably necessary for loading from
# files, anyway.)
statement = 'INSERT INTO {schema}.{table} ({columns}) VALUES ({placeholders})'.format(
schema=quote(table.schema),
table=quote(table.name),
columns=', '.join(map(quote, map(operator.attrgetter('name'), record.columns))),
placeholders=', '.join(['%s' for _ in record.columns]),
)
cursor.execute(statement, map(operator.itemgetter(1), map(column_converter.to_python, record.columns)))
def create_trigger(table):
logger.info('Installing (or replacing) log trigger on %s.%s...', table.schema, table.name)
primary_keys = unique(list(table.primary_keys))
all_columns = unique(primary_keys + list(table.columns))
if table.columns:
column_list = 'OF %s' % ', '.join(map(quote, all_columns))
else:
column_list = ''
statement = """
CREATE TRIGGER {name}
AFTER INSERT OR UPDATE {columns} OR DELETE
ON {schema}.{table}
FOR EACH ROW EXECUTE PROCEDURE {cluster_schema}.log(%s, %s, %s, %s)
""".format(
name=quote(trigger),
columns=column_list,
schema=quote(table.schema),
table=quote(table.name),
cluster_schema=quote(cluster.schema),
)
cursor.execute("DROP TRIGGER IF EXISTS {name} ON {schema}.{table}".format(
name=quote(trigger),
schema=quote(table.schema),
table=quote(table.name),
))
cursor.execute(statement, (
cluster.get_queue_name(name),
pickle.dumps(primary_keys),
pickle.dumps(all_columns if table.columns else None),
get_version(configuration)),
)
def loader(table):
with connection_lock, connection.cursor('records', cursor_factory=NamedTupleCursor) as cursor:
if table.columns:
columns = ', '.join(map(quote, table.columns))
else:
columns = '*'
statement = 'SELECT {columns} FROM {schema}.{name}'.format(
columns=columns,
schema=quote(table.schema),
name=quote(table.name),
)
cursor.execute(statement)
for row in cursor:
converted = row_converter.to_protobuf(row._asdict())
# XXX: This is necessary because of a bug in protocol buffer oneof.
yield type(converted).FromString(converted.SerializeToString())
def get_configuration_value(cluster, cursor, key, default=None):
statement = 'SELECT value FROM {schema}.configuration WHERE key = %s'.format(
schema=quote(cluster.schema))
cursor.execute(statement, (key, ))
results = cursor.fetchall()
assert len(results) <= 1
if results:
return results[0][0]
else:
return default
def create_log_trigger_function(cluster, cursor, node_id):
"""
Installs the log trigger function on the database for the provided cursor,
returning the function name that can be used as part of a ``CREATE
TRIGGER`` statement.
"""
body = resource_string('sql/log_trigger.py.tmpl')
statement = INSTALL_LOG_TRIGGER_STATEMENT_TEMPLATE.format(
schema=quote(cluster.schema),
body=body,
version=__version__,
)
cursor.execute(statement)
def create_log_trigger_function(cluster, cursor, node_id):
"""
Installs the log trigger function on the database for the provided cursor,
returning the function name that can be used as part of a ``CREATE
TRIGGER`` statement.
"""
body = resource_string('sql/log_trigger.py.tmpl')
statement = INSTALL_LOG_TRIGGER_STATEMENT_TEMPLATE.format(
schema=quote(cluster.schema),
body=body,
version=__version__,
)
cursor.execute(statement)
def setup_database(cluster, cursor):
"""
Configures a database (the provided cursor) for use with pgshovel.
This function can also be used to repair a broken installation, or update
an existing installation's log trigger function.
"""
# Install PGQ if it doesn't already exist.
logger.info('Creating PgQ extension (if it does not already exist)...')
cursor.execute('CREATE EXTENSION IF NOT EXISTS pgq')
# Install pypythonu if it doesn't already exist.
logger.info('Creating (or updating) plpythonu language...')
cursor.execute('CREATE OR REPLACE LANGUAGE plpythonu')
# Create the schema if it doesn't already exist.
logger.info('Creating schema (if it does not already exist)...')
cursor.execute('CREATE SCHEMA IF NOT EXISTS {schema}'.format(
schema=quote(cluster.schema),
))
# Create the configuration table if it doesn't already exist.
logger.info('Creating configuration table (if it does not already exist)...')
create_configuration_table(cluster, cursor)
# Create the replication state table if it doesn't already exist.
logger.info('Creating replication state table (if it does not already exist)...')
create_replication_state_table(cluster, cursor)
version = get_configuration_value(cluster, cursor, 'version')
if version is None:
set_configuration_value(cluster, cursor, 'version', __version__)
elif version is not None and str(version) != __version__:
update_configuration_value(cluster, cursor, 'version', __version__)
# Ensure that this database has already had an identifier associated with it.
logger.info('Checking for node ID...')
node_id = get_or_set_node_identifier(cluster, cursor)
logger.info('Installing (or updating) log trigger function...')
create_log_trigger_function(cluster, cursor, node_id)
return node_id
def setup_database(cluster, cursor):
"""
Configures a database (the provided cursor) for use with pgshovel.
This function can also be used to repair a broken installation, or update
an existing installation's log trigger function.
"""
# Install PGQ if it doesn't already exist.
logger.info('Creating PgQ extension (if it does not already exist)...')
cursor.execute('CREATE EXTENSION IF NOT EXISTS pgq')
# Install pypythonu if it doesn't already exist.
logger.info('Creating (or updating) plpythonu language...')
cursor.execute('CREATE OR REPLACE LANGUAGE plpythonu')
# Create the schema if it doesn't already exist.
logger.info('Creating schema (if it does not already exist)...')
cursor.execute('CREATE SCHEMA IF NOT EXISTS {schema}'.format(schema=quote(
cluster.schema), ))
# Create the configuration table if it doesn't already exist.
logger.info(
'Creating configuration table (if it does not already exist)...')
create_configuration_table(cluster, cursor)
version = get_configuration_value(cluster, cursor, 'version')
if version is None:
set_configuration_value(cluster, cursor, 'version', __version__)
elif version is not None and str(version) != __version__:
update_configuration_value(cluster, cursor, 'version', __version__)
# Ensure that this database has already had an identifier associated with it.
logger.info('Checking for node ID...')
node_id = get_or_set_node_identifier(cluster, cursor)
logger.info('Installing (or updating) log trigger function...')
create_log_trigger_function(cluster, cursor, node_id)
return node_id
def create_trigger(table):
logger.info('Installing (or replacing) log trigger on %s.%s...',
table.schema, table.name)
primary_keys = unique(list(table.primary_keys))
all_columns = unique(primary_keys + list(table.columns))
if table.columns:
column_list = 'OF %s' % ', '.join(map(quote, all_columns))
else:
column_list = ''
statement = """
CREATE TRIGGER {name}
AFTER INSERT OR UPDATE {columns} OR DELETE
ON {schema}.{table}
FOR EACH ROW EXECUTE PROCEDURE {cluster_schema}.log(%s, %s, %s, %s)
""".format(
name=quote(trigger),
columns=column_list,
schema=quote(table.schema),
table=quote(table.name),
cluster_schema=quote(cluster.schema),
)
cursor.execute(
"DROP TRIGGER IF EXISTS {name} ON {schema}.{table}".format(
name=quote(trigger),
schema=quote(table.schema),
table=quote(table.name),
))
cursor.execute(
statement,
(cluster.get_queue_name(name), pickle.dumps(primary_keys),
pickle.dumps(all_columns if table.columns else None),
get_version(configuration)),
)
def set_configuration_value(cluster, cursor, key, value):
statement = 'INSERT INTO {schema}.configuration (key, value) VALUES (%s, %s)'.format(schema=quote(cluster.schema))
cursor.execute(statement, (key, value))
def create_configuration_table(cluster, cursor):
statement = INSTALL_CONFIGURATION_TABLE_STATEMENT_TEMPLATE.format(
schema=quote(cluster.schema), )
cursor.execute(statement)
def validate_set_configuration(configuration):
for table in configuration.tables:
assert len(table.primary_keys) > 0, 'table %s.%s must have associated primary key column(s)' % (quote(table.schema), quote(table.name),)
def update_configuration_value(cluster, cursor, key, value):
statement = 'UPDATE {schema}.configuration SET value = %s WHERE key = %s'.format(
schema=quote(cluster.schema))
cursor.execute(statement, (value, key))
def set_configuration_value(cluster, cursor, key, value):
statement = 'INSERT INTO {schema}.configuration (key, value) VALUES (%s, %s)'.format(
schema=quote(cluster.schema))
cursor.execute(statement, (key, value))
def update_configuration_value(cluster, cursor, key, value):
statement = 'UPDATE {schema}.configuration SET value = %s WHERE key = %s'.format(schema=quote(cluster.schema))
cursor.execute(statement, (value, key))
def create_configuration_table(cluster, cursor):
statement = INSTALL_CONFIGURATION_TABLE_STATEMENT_TEMPLATE.format(
schema=quote(cluster.schema),
)
cursor.execute(statement)
def get_configuration_value(cluster, cursor, key, default=None):
statement = 'SELECT value FROM {schema}.configuration WHERE key = %s'.format(schema=quote(cluster.schema))
cursor.execute(statement, (key,))
results = cursor.fetchall()
assert len(results) <= 1
if results:
return results[0][0]
else:
return default
def create_replication_state_table(cluster, cursor):
statement = INSTALL_REPLICATION_STATE_TABLE_STATEMENT_TEMPLATE.format(
schema=quote(cluster.schema),
)
cursor.execute(statement)
