def alter_structure():
documents_orders_table.drop(db_engine(), checkfirst=True)
documents_order_parts_table.drop(db_engine(), checkfirst=True)
TemplateDocument.__table__.drop(db_engine(), checkfirst=True)
Document.__table__.drop(db_engine(), checkfirst=True)
Document.__table__.create()
TemplateDocument.__table__.create()
documents_orders_table.create()
documents_order_parts_table.create()
session().connection().execute("GRANT ALL ON horse.documents TO horse_clt")
session().connection().execute(
"GRANT ALL ON horse.template_documents TO horse_clt")
session().connection().execute(
"GRANT ALL ON horse.documents_orders TO horse_clt")
session().connection().execute(
"GRANT ALL ON horse.documents_order_parts TO horse_clt")
# FIXME No idea why we have that sequence
session().connection().execute(
"GRANT ALL ON SEQUENCE horse.documents_document_id_seq TO horse_clt")
session().commit()
# Bug fix
session().connection().execute(
"GRANT ALL ON horse.filter_queries TO horse_clt ;")
def create_all_tables():
mainlog.info("Creating all the tables and sequences")
Base.metadata.create_all(db_engine(), checkfirst=True)
session().commit()
mainlog.info("Creating all the functions in the database")
create_functions(session())
session().commit()
def _exec_out_of_transaction(self, sql):
# Some Postgres administrative commands cannot run inside
# a transaction. Unfortunately, SQLA tries very hard
# to put us in a transaction at any time.
# This is a small hack to leave the transaction.
connection = db_engine().connect()
connection.execute("commit") # Get out of SQLA's transaction
self._log(sql)
connection.execute(sql)
connection.close()
def drop_entity(entity):
if isinstance(entity, DeclarativeMeta):
drop_entity(entity.__table__)
elif isinstance(entity, Table):
entity.drop(db_engine(), checkfirst=True)
session().commit()
elif isinstance(entity, DeclEnumMeta):
entity.db_type().drop(bind=db_engine(), checkfirst=True)
try:
session().connection().execute("DROP TYPE IF EXISTS {}".format(
entity.db_name()))
session().commit()
except Exception as ex:
mainlog.exception(ex)
mainlog.error("Could not : DROP TYPE horse.ck_quality_event_type")
session().rollback()
else:
raise Exception("Unrecognized entity type : {}".format(type(entity)))
session().commit()
def alter_structure():
# session().connection().execute("DROP TABLE horse.day_event")
# session().commit()
DayEvent.__table__.drop(db_engine(), checkfirst=True)
session().commit()
print(" ---------------------------- table droppe")
DayEventType.db_type().drop(bind=db_engine(), checkfirst=True)
session().commit()
# For some reason, SQLA doesn't drop the enum type :-(
# Although the doc seems to state it does...
# Will create the enum and sequence, in the right schema
DayEvent.__table__.create()
session().commit()
session().connection().execute("GRANT ALL ON horse.day_event TO horse_clt")
session().connection().execute(
"GRANT ALL ON SEQUENCE horse.day_event_id_generator TO horse_clt")
session().commit()
def extend_enumeration(enumeration: DeclEnum, symbol: EnumSymbol):
# The following statement really wants to run outside of a transaction.
# SO I have to use the raw_connection stuff to escape SQLA's autoamted
# transaction management.
# See enumeration type information
# select enumtypid, typname, enumlabel from pg_enum join pg_type on pg_type.oid = pg_enum.enumtypid order by enumtypid, enumlabel;
c = db_engine().raw_connection()
cursor = c.cursor()
cursor.execute("COMMIT") # Leave any pending transaction
try:
sql = "ALTER TYPE {} ADD VALUE '{}'".format(
enumeration.db_type().impl.name, symbol.value)
mainlog.debug(" /// " + sql)
cursor.execute(sql)
except Exception as ex:
print(ex)
cursor.close()
c.close()
def alter_structure():
try:
session().connection().execute("drop table horse.supplier_orders")
session().commit()
except Exception as ex:
session().rollback()
try:
session().connection().execute("drop table horse.achat_parts")
session().commit()
except Exception as ex:
session().rollback()
try:
session().connection().execute("drop table horse.achats")
session().commit()
except Exception as ex:
session().rollback()
try:
session().connection().execute("drop table horse.suppliers")
session().commit()
except Exception as ex:
session().rollback()
pass
SupplyOrderPart.__table__.drop(db_engine(), checkfirst=True)
SupplyOrder.__table__.drop(db_engine(), checkfirst=True)
Supplier.__table__.drop(db_engine(), checkfirst=True)
Supplier.__table__.create()
SupplyOrder.__table__.create()
SupplyOrderPart.__table__.create()
session().connection().execute(
"GRANT ALL ON horse.suppliers TO horse_clt ;")
session().connection().execute(
"GRANT ALL ON horse.supply_orders TO horse_clt ;")
session().connection().execute(
"GRANT ALL ON horse.supply_order_parts TO horse_clt ;")
session().connection().execute(
"GRANT ALL ON SEQUENCE horse.suppliers_supplier_id_seq TO horse_clt ;"
)
session().connection().execute(
"GRANT ALL ON SEQUENCE horse.supply_orders_supply_order_id_seq TO horse_clt;"
)
session().connection().execute(
"GRANT ALL ON SEQUENCE horse.supply_order_parts_supply_order_part_id_seq TO horse_clt;"
)
session().connection().execute(
"alter table horse.filter_queries drop constraint fq_by_name;")
session().connection().execute(
"ALTER TABLE horse.filter_queries ADD COLUMN \"family\" character varying;"
)
session().connection().execute(
"UPDATE horse.filter_queries SET family='order_parts_overview';")
session().connection().execute(
"ALTER TABLE horse.filter_queries ALTER COLUMN \"family\" SET NOT NULL;"
)
session().connection().execute(
"alter table horse.filter_queries add constraint fq_by_name unique (family,name);"
)
session().connection().execute(
"CREATE TYPE horse.ck_special_activity_type AS ENUM ('holidays','partial_activity','unemployment','sick_leave','other');"
)
session().connection().execute(
"ALTER TABLE horse.special_activities ADD COLUMN activity_type horse.ck_special_activity_type NOT NULL DEFAULT 'other';"
)
session().commit()
def alter_structure():
ftn = full_table_name(DocumentCategory)
doc_tn = full_table_name(Document)
try:
session().connection().execute(
"ALTER TABLE {} DROP CONSTRAINT fk_category".format(doc_tn))
session().commit()
except Exception as ex:
mainlog.exception(ex)
session().rollback()
try:
session().connection().execute(
"ALTER TABLE {} DROP COLUMN document_category_id".format(doc_tn))
session().commit()
except Exception as ex:
mainlog.exception(ex)
session().rollback()
documents_quality_events_table.drop(db_engine(), checkfirst=True)
session().commit()
drop_entity(DocumentCategory)
drop_entity(QualityEvent)
drop_entity(UserClass)
drop_entity(QualityEventType)
# Creations...
create_entity(DocumentCategory)
session().connection().execute(
"ALTER TABLE {} ADD document_category_id INTEGER".format(doc_tn))
session().connection().execute(
"ALTER TABLE {} ADD CONSTRAINT fk_category FOREIGN KEY(document_category_id) REFERENCES {} (document_category_id)"
.format(doc_tn, ftn))
# Quality
create_entity(QualityEvent)
# Autoincrement implies a sequence. SQLA chooses the name of that sequence.
session().connection().execute(
"GRANT USAGE, SELECT, UPDATE ON SEQUENCE horse.quality_events_quality_event_id_seq TO horse_clt"
)
create_entity(documents_quality_events_table)
from koi.db_mapping import OrderPartStateType
extend_enumeration(OrderPartStateType, OrderPartStateType.non_conform)
create_entity(UserClass)
# Autoincrement implies a sequence. SQLA chooses the name of that sequence.
session().connection().execute(
"GRANT USAGE, SELECT, UPDATE ON SEQUENCE horse.user_classes_user_class_id_seq TO horse_clt"
)
# Fix a database issue
session().connection().execute(
"GRANT SELECT,INSERT,UPDATE,DELETE ON {} TO horse_clt".format(doc_tn))
session().commit()
session().commit()
def set_up_database(url_admin, url_client):
""" Create the very basic Koi database. That is :
the client user, the admin user, the schema, grant privileges.
:param url_admin:
:param url_client:
:return:
"""
# The administrative user must be "horse_adm"
# He must have the right to create databases and roles
# Just to be sure we're outside any connection
disconnect_db()
db_url, params = parse_db_url(url_client)
login, password, dbname, host, port = extract_db_params_from_url(db_url)
db_url, params = parse_db_url(url_admin)
login_adm, password_adm, dbname, host, port = extract_db_params_from_url(
db_url)
mainlog.info("Admin user is {}, regular user is {}".format(
login_adm, login))
db_url, params = template1_connection_parameters(url_admin)
init_db_session(db_url, params=params)
mainlog.info("creating database")
conn = db_engine().connect()
conn.execute("commit")
conn.execute("drop database if exists {}".format(dbname))
if login_adm != login:
conn.execute("drop role if exists {}".format(login))
conn.execute("CREATE ROLE {} LOGIN PASSWORD '{}'".format(
login, password))
conn.execute(
"ALTER ROLE {} SET statement_timeout = 30000".format(login))
conn.execute("commit") # Leave transaction
conn.execute("CREATE DATABASE {}".format(dbname))
conn.execute("ALTER DATABASE {} SET search_path TO {},public".format(
dbname, DATABASE_SCHEMA))
conn.close()
disconnect_db()
init_db_session(url_admin)
session().commit() # Leave SQLA's transaction
# Schema will be created for current database (i.e. horse or horse_test)
mainlog.info("Creating schema {}".format(DATABASE_SCHEMA))
session().connection().execute("create schema {}".format(DATABASE_SCHEMA))
if login_adm != login:
mainlog.info("Granting privileges to {}".format(login))
session().connection().execute("grant usage on schema {} to {}".format(
DATABASE_SCHEMA, login))
# Alter the default privileges so that every tables and sequences
# created right after will be usable by horse_clt
# Also, if one adds tables, etc. afterwards, they'll benefit from
# the privileges as well
session().connection().execute("""ALTER DEFAULT PRIVILEGES
FOR ROLE {}
IN SCHEMA {}
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO {}""".format(
login_adm, DATABASE_SCHEMA, login))
session().connection().execute("""ALTER DEFAULT PRIVILEGES
FOR ROLE {}
IN SCHEMA {}
GRANT SELECT, UPDATE ON SEQUENCES TO {}""".format(
login_adm, DATABASE_SCHEMA, login))
session().commit()
disconnect_db()
mainlog.info("Database setup complete")
def drop_all_tables(current_session):
mainlog.info("Dropping all the functions in the database")
drop_functions(current_session)
# WARNING Pay attention with follwowing code, it destroys the session !
# But the session may be in use in other components (dao for example)
# Close the connection to PG
# This avoids DROP's to lock
# http://www.sqlalchemy.org/trac/wiki/FAQ#MyprogramishangingwhenIsaytable.dropmetadata.drop_all
# current_session.connection().close()
# current_session.close()
# current_session.bind.dispose()
# current_session = session_factory()
mainlog.info("Dropping all the tables in the database")
#db_engine().execute( DropTable(ProductionFile))
Comment.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
CommentLocation.__table__.drop(db_engine(), checkfirst=True)
#comments_locations.drop(db_engine(), checkfirst=True)
current_session.commit()
# StockItem.__table__.drop(db_engine(), checkfirst=True)
# current_session.commit()
DayEvent.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
TemplateDocument.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
documents_order_parts_table.drop(db_engine(), checkfirst=True)
current_session.commit()
documents_orders_table.drop(db_engine(), checkfirst=True)
current_session.commit()
documents_quality_events_table.drop(db_engine(), checkfirst=True)
current_session.commit()
QualityEvent.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
Document.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
DocumentCategory.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
AuditTrail.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
FilterQuery.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
MonthTimeSynthesis.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
SpecialActivity.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
TaskActionReport.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
TimeTrack.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
TaskForPresence.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
TaskOnOperation.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
TaskOnOrder.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
TaskOnNonBillable.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
Task.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
Operation.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
ProductionFile.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
DeliverySlipPart.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
OrderPart.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
Order.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
DeliverySlip.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
OfferPart.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
Offer.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
Customer.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
OperationDefinitionPeriod.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
Machine.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
OperationDefinition.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
DayTimeSynthesis.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
MonthTimeSynthesis.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
Employee.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
SupplyOrderPart.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
SupplyOrder.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
Supplier.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
Resource.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
UserClass.__table__.drop(db_engine(), checkfirst=True)
current_session.commit()
gapless_seq_table.drop(db_engine(), checkfirst=True)
#current_session.connection().execute("DROP TYPE IF EXISTS ck_task_action_report_type")
current_session.commit()
def alter_structure():
try:
session().connection().execute(
"ALTER TABLE horse.operations DROP CONSTRAINT fk_employee")
session().connection().execute(
"ALTER TABLE horse.operations DROP COLUMN employee_id")
except Exception as ex:
session().rollback()
session().connection().execute(
"ALTER TABLE horse.operations ADD employee_id INTEGER")
session().connection().execute(
"ALTER TABLE horse.operations ADD CONSTRAINT fk_employee FOREIGN KEY(employee_id) REFERENCES horse.employees (employee_id)"
)
# Commit necessary, else the next drop table hangs ad infinitum
session().commit()
try:
session().connection().execute(
"ALTER TABLE horse.task_action_reports DROP COLUMN machine_id")
session().commit()
except Exception as ex:
session().rollback()
try:
session().connection().execute(
"ALTER TABLE horse.timetracks DROP COLUMN machine_id")
session().commit()
except Exception as ex:
session().rollback()
try:
session().connection().execute(
"ALTER TABLE horse.tasks_operations DROP COLUMN machine_id")
session().commit()
except Exception as ex:
session().rollback()
try:
session().connection().execute(
"ALTER TABLE horse.tasks_operations DROP CONSTRAINT tasks_operations_operation_id_key"
)
session().commit()
except Exception as ex:
session().rollback()
Machine.__table__.drop(db_engine(), checkfirst=True)
session().commit()
try:
session().connection().execute(
"DROP SEQUENCE horse.machine_id_generator")
except Exception as ex:
print(ex)
print("###############################################")
session().rollback()
Resource.__table__.drop(db_engine(), checkfirst=True)
session().commit()
try:
session().connection().execute(
"DROP SEQUENCE horse.resource_id_generator")
except Exception as ex:
print("---------------------------------------------------")
print(ex)
mainlog.exception(ex)
session().rollback()
session().commit()
# resource_id_generator.create()
Resource.__table__.create()
session().connection().execute("GRANT ALL ON horse.resources TO horse_clt")
session().connection().execute(
"GRANT ALL ON SEQUENCE horse.resource_id_generator TO horse_clt")
session().commit()
Machine.__table__.create()
session().connection().execute("GRANT ALL ON horse.machines TO horse_clt")
# session().connection().execute("GRANT ALL ON SEQUENCE horse.machine_id_generator TO horse_clt")
session().commit()
session().connection().execute(
"ALTER TABLE horse.task_action_reports ADD machine_id INTEGER")
session().connection().execute(
"ALTER TABLE horse.task_action_reports ADD CONSTRAINT fk_machine FOREIGN KEY(machine_id) REFERENCES horse.machines (resource_id)"
)
session().commit()
session().connection().execute(
"ALTER TABLE horse.timetracks ADD machine_id INTEGER")
session().connection().execute(
"ALTER TABLE horse.timetracks ADD CONSTRAINT fk_machine FOREIGN KEY(machine_id) REFERENCES horse.machines (resource_id)"
)
session().commit()
session().connection().execute(
"ALTER TABLE horse.tasks_operations ADD machine_id INTEGER")
session().connection().execute(
"ALTER TABLE horse.tasks_operations ADD CONSTRAINT fk_machine FOREIGN KEY(machine_id) REFERENCES horse.machines (resource_id)"
)
session().connection().execute(
"ALTER TABLE horse.tasks_operations ADD CONSTRAINT unique_task_on_machine_and_operation UNIQUE(operation_id,machine_id)"
)
session().commit()