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