def create_select_statement(source_table):
join_parts = []
if subquery_filter:
join_part = (
"JOIN ({0}) AS filter "
"ON filter.id = {1}.entity_id").format(
subquery_filter, source_table.render())
join_parts.append(join_part)
if relation_table_name:
relation_table = Table("relation", relation_table_name)
return_id_field = "r.target_id AS entity_id"
join_part = (
"LEFT JOIN {0} r "
"ON r.source_id = {1}.entity_id").format(
relation_table.render(), source_table.render())
join_parts.append(join_part)
else:
return_id_field = "entity_id"
return (
"SELECT {0}, %(end)s, {1} {2} "
"FROM {3} {4} "
"WHERE timestamp > %(start)s AND timestamp <= %(end)s").format(
return_id_field,
select_samples_column,
",".join(map(enquote_column_name, trend_names)),
source_table.render(),
" ".join(join_parts))
def delete_by_sub_query(conn, table_name, timestamp, entityselection):
"""
Delete rows from table for a specific timestamp and entity_ids in
entityselection.
"""
table = Table(SCHEMA, table_name)
delete_query = (
"DELETE FROM {} d USING entity_filter f "
"WHERE d.timestamp = %s AND f.entity_id = d.entity_id"
).format(table.render())
args = (timestamp,)
with closing(conn.cursor()) as cursor:
entityselection.create_temp_table(cursor, "entity_filter")
logging.debug(cursor.mogrify(delete_query, args))
try:
cursor.execute(delete_query, args)
except psycopg2.DatabaseError as exc:
if exc.pgcode == psycopg2.errorcodes.UNDEFINED_TABLE:
raise NoSuchTable()
else:
raise exc
def create_temp_table_from(cursor, table):
"""
Create a temporary table that is like `table` and return the temporary
table name.
"""
tmp_table = Table("tmp_{0}".format(table.name))
query = (
"CREATE TEMPORARY TABLE {0} (LIKE {1}) "
"ON COMMIT DROP").format(tmp_table.render(), table.render())
cursor.execute(query)
return tmp_table
def create_trend_table(conn, schema, table, column_names, data_types):
"""
:param conn: psycopg2 database connection
:param schema: name of the database schema to create the table in
:param table: name of table to be created, or Table instance
"""
columns_part = "".join(
['"{0}" {1}, '.format(name, data_type) for (name, data_type) in zip(column_names, data_types)]
)
if isinstance(table, str):
table = Table(schema, table)
query = (
"CREATE TABLE {0} ( "
"entity_id integer NOT NULL, "
'"timestamp" timestamp with time zone NOT NULL, '
'"modified" timestamp with time zone NOT NULL, '
"{1}"
'CONSTRAINT "{2}_pkey" PRIMARY KEY (entity_id, "timestamp"))'
).format(table.render(), columns_part, table.name)
alter_query = "ALTER TABLE {0} ALTER COLUMN modified " "SET DEFAULT CURRENT_TIMESTAMP".format(table.render())
index_query_modified = 'CREATE INDEX "idx_{0}_modified" ON {1} ' "USING btree (modified)".format(
table.name, table.render()
)
index_query_timestamp = 'CREATE INDEX "idx_{0}_timestamp" ON {1} ' "USING btree (timestamp)".format(
table.name, table.render()
)
owner_query = "ALTER TABLE {} OWNER TO minerva_writer".format(table.render())
with closing(conn.cursor()) as cursor:
try:
cursor.execute(query)
cursor.execute(alter_query)
cursor.execute(index_query_modified)
cursor.execute(index_query_timestamp)
cursor.execute(owner_query)
except psycopg2.IntegrityError as exc:
# apparently the table has been created already, so ignore
pass
except psycopg2.ProgrammingError as exc:
if exc.pgcode == psycopg2.errorcodes.DUPLICATE_TABLE:
# apparently the table has been created already, so ignore
pass
else:
raise NonRecoverableError("ProgrammingError({0}): {1}".format(exc.pgcode, exc.pgerror))
else:
grant(conn, "TABLE", "SELECT", table.render(), "minerva")
grant(conn, "TABLE", "TRIGGER", table.render(), "minerva_writer")
conn.commit()
class NotificationStore(object):
def __init__(self, datasource, attributes):
self.id = None
self.version = 1
self.datasource = datasource
self.attributes = attributes
table_name = datasource.name
self.table = Table("notification", table_name)
@staticmethod
def load(cursor, datasource):
"""Load NotificationStore from database and return it."""
query = (
"SELECT id "
"FROM notification.notificationstore "
"WHERE datasource_id = %s")
args = datasource.id,
cursor.execute(query, args)
if cursor.rowcount == 1:
notificationstore_id, = cursor.fetchone()
notificationstore = NotificationStore(datasource, [])
notificationstore.id = notificationstore_id
query = (
"SELECT id, name, data_type, description "
"FROM notification.attribute "
"WHERE notificationstore_id = %s"
)
args = (notificationstore_id, )
cursor.execute(query, args)
for attribute_id, name, data_type, description in cursor.fetchall():
attribute = Attribute(name, data_type, description)
attribute.id = attribute_id
notificationstore.attributes.append(attribute)
return notificationstore
def create(self, cursor):
"""Create notification store in database in return itself."""
if self.id:
raise NotImplementedError()
else:
query = (
"INSERT INTO notification.notificationstore "
"(datasource_id, version) "
"VALUES (%s, %s) RETURNING id")
args = self.datasource.id, self.version
cursor.execute(query, args)
self.id = first(cursor.fetchone())
for attribute in self.attributes:
query = (
"INSERT INTO notification.attribute "
"(notificationstore_id, name, data_type, description) "
"VALUES (%s, %s, %s, %s) "
"RETURNING id")
args = (self.id, attribute.name, attribute.data_type,
attribute.description)
cursor.execute(query, args)
return self
def store_record(self, record):
"""Return function that can store the data from a
:class:`~minerva.storage.notification.types.Record`."""
@translate_postgresql_exceptions
def f(cursor):
column_names = ['entity_id', 'timestamp'] + record.attribute_names
columns_part = ','.join(map(smart_quote, column_names))
entity_placeholder, entity_value = record.entity_ref.to_argument()
placeholders = (
[entity_placeholder, "%s"] +
(["%s"] * len(record.attribute_names))
)
query = (
"INSERT INTO {} ({}) "
"VALUES ({})"
).format(self.table.render(), columns_part, ",".join(placeholders))
args = (
[entity_value, record.timestamp]
+ map(prepare_value, record.values)
)
cursor.execute(query, args)
return f
def create_temp_table_from(conn, schema, table):
"""
Create a temporary table that inherits from `table` and return the temporary
table name.
"""
if isinstance(table, str):
table = Table(schema, table)
tmp_table_name = "tmp_{0}".format(table.name)
query = ('CREATE TEMPORARY TABLE "{0}" (LIKE {1}) ' "ON COMMIT DROP").format(tmp_table_name, table.render())
with closing(conn.cursor()) as cursor:
cursor.execute(query)
return tmp_table_name
