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()