def create_data_table(conn, table_name, column_names, data_types):
"""
:param conn: psycopg2 database connection
:param schema: name of the database schema to create the table in
:param table_name: name of table to be created
"""
columns_part = "".join(
["\"{0}\" {1}, ".format(name, type) for
(name, type) in zip(column_names, data_types)])
full_table_name = create_full_table_name(schema, table_name)
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(
full_table_name, columns_part, table_name))
alter_query = (
"ALTER TABLE {0} ALTER COLUMN modified "
"SET DEFAULT CURRENT_TIMESTAMP".format(full_table_name))
index_query_modified = (
'CREATE INDEX "idx_{0}_modified" ON {1} '
'USING btree (modified)'.format(table_name, full_table_name))
index_query_timestamp = (
'CREATE INDEX "idx_{0}_timestamp" ON {1} '
'USING btree (timestamp)'.format(table_name, full_table_name))
trigger_query = (
"CREATE TRIGGER update_modified_modtime "
"BEFORE UPDATE "
"ON {0} FOR EACH ROW EXECUTE PROCEDURE "
"directory.update_modified_column()".format(full_table_name))
owner_query = "ALTER TABLE {} OWNER TO minerva_writer".format(full_table_name)
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(trigger_query)
cursor.execute(owner_query)
except psycopg2.IntegrityError as exc:
raise RecoverableError(str(exc), NoOpFix)
except psycopg2.ProgrammingError as exc:
if exc.pgcode == psycopg2.errorcodes.DUPLICATE_TABLE:
raise RecoverableError(str(exc), NoOpFix)
else:
raise NonRecoverableError(\
"ProgrammingError({0}): {1}".format(exc.pgcode, exc.pgerror))
else:
grant(conn, "TABLE", "SELECT", full_table_name, "minerva")
conn.commit()
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()
