def clean_core(args): major_version = '.'.join(get_distribution('CGRdb').version.split('.')[:-1]) schema = args.name db_config = Database() LazyEntityMeta.attach(db_config, database='CGRdb_config') db_config.bind('postgres', **args.connection) db_config.generate_mapping() with db_session: config = db_config.Config.get(name=schema, version=major_version) if not config: raise KeyError('schema not exists or version incompatible') config = config.config for p in config['packages']: try: p = get_distribution(p) import_module(p.project_name) except (DistributionNotFound, VersionConflict): raise ImportError( f'packages not installed or has invalid versions: {p}') db = Database() LazyEntityMeta.attach(db, schema, 'CGRdb') db.bind('postgres', **args.connection) db.generate_mapping() with db_session: db.execute(f'TRUNCATE TABLE "{schema}"."MoleculeSearchCache", ' f'"{schema}"."ReactionSearchCache" RESTART IDENTITY')
def init_core(args): db = Database() LazyEntityMeta.attach(db, database='CGRdb_config') db.bind('postgres', **args.connection) db.generate_mapping(create_tables=True) with db_session: db.execute('CREATE EXTENSION IF NOT EXISTS intarray') db.execute('CREATE EXTENSION IF NOT EXISTS plpython3u')
def db(self): db = Database() load_tables(db, 'cgrdb', None) db.bind('postgres', user='******', password='******', host=None, database=None) with db_session: db.execute('drop SCHEMA cgrdb CASCADE') db.execute('CREATE SCHEMA cgrdb') db.generate_mapping(create_tables=True) return db
def index_core(args): major_version = '.'.join(get_distribution('CGRdb').version.split('.')[:-1]) schema = args.name db_config = Database() LazyEntityMeta.attach(db_config, database='CGRdb_config') db_config.bind('postgres', user=args.user, password=args.password, host=args.host, database=args.base, port=args.port) db_config.generate_mapping() with db_session: config = db_config.Config.get(name=schema, version=major_version) if not config: raise KeyError('schema not exists or version incompatible') config = config.config for p in config['packages']: try: p = get_distribution(p) import_module(p.project_name) except (DistributionNotFound, VersionConflict): raise ImportError( f'packages not installed or has invalid versions: {p}') db = Database() LazyEntityMeta.attach(db, schema, 'CGRdb') db.bind('postgres', user=args.user, password=args.password, host=args.host, database=args.base, port=args.port) db.generate_mapping() with db_session: db.execute( f'CREATE INDEX idx_moleculestructure__smlar ON "{schema}"."MoleculeStructure" USING ' 'GIST (fingerprint _int4_sml_ops)') db.execute( f'CREATE INDEX idx_moleculestructure__subst ON "{schema}"."MoleculeStructure" USING ' 'GIN (fingerprint gin__int_ops)') db.execute( f'CREATE INDEX idx_reactionindex__smlar ON "{schema}"."ReactionIndex" USING ' 'GIST (fingerprint _int4_sml_ops)') db.execute( f'CREATE INDEX idx_reactionindex__subst ON "{schema}"."ReactionIndex" USING ' 'GIN (fingerprint gin__int_ops)')
def test_depuis_version(resources, tmpfilename, caplogger, version): base = resources / "db_version" / f"{version}.sqlite" shutil.copy(base, tmpfilename) assert make_migrations(tmpfilename), caplogger.read() assert (Schema(tmpfilename).framgments == Schema( resources / "db_version" / f"{schema_version}.sqlite").framgments) # test tdes données ok ddb = Database(provider="sqlite", filename=str(tmpfilename)) with db_session_disconnect_db(ddb): assert ddb.execute("select id from Page").fetchall()
def test_1_3_0_vers_1_4_0(new_res, resources, caplogger): # setup base = new_res(resources / "db_version" / f"1.3.0.sqlite") mk = MakeMigrations(base, "1.4.0", migrations_history) ddb = Database(provider="sqlite", filename=str(base)) # test tdes données ok with db_session_disconnect_db(ddb): nom, prenom = ddb.get( 'select nom,prenom from Utilisateur where nom=="lenom"') assert mk(CheckMigrations(until="1.4.0"), lambda x: True), caplogger.read() # test: pas de perte de donnée pour Annee with db_session_disconnect_db(ddb): assert ddb.execute("select * from annee").fetchall() == [ (2018, "cm2018"), (2019, "cm2019"), ] # test transfert nom, prenom, user_set de Utilisateur vers Configuration with db_session_disconnect_db(ddb): assert ddb.execute("select * from Configuration").fetchall() == [ ( "vue", "str_value", "intéresser", None, None, None, None, None, None, "{}", ), ("nom", "str_value", nom, None, None, None, None, None, None, 0), ("prenom", "str_value", prenom, None, None, None, None, None, None, 0), ("user_set", "bool_value", "", None, 1, None, None, None, None, 0), ]
def test_1_5_0_vers_1_6_0(new_res, resources, caplogger): # setup base = new_res(resources / "db_version" / f"1.5.0.sqlite") mk = MakeMigrations(base, "1.6.0", migrations_history) ddb = Database(provider="sqlite", filename=str(base)) # test tdes données ok with db_session_disconnect_db(ddb): pass #pour winddows assert mk(CheckMigrations(until="1.6.0"), lambda x: True), caplogger.read() # # test tdes données ok #ddb = Database(provider="sqlite", filename=str(base)) with db_session_disconnect_db(ddb): res = ddb.execute("select content, modified from Traduction") assert res.fetchone() == ( "coucou", "2021-01-01 00:00:00.000000", )
def load_schema(schema, *args, **kwargs): """ Load schema from db with compatible version :param schema: schema name for loading """ major_version = '.'.join(get_distribution('CGRdb').version.split('.')[:-1]) db_config = Database() LazyEntityMeta.attach(db_config, database='CGRdb_config') db_config.bind('postgres', *args, **kwargs) db_config.generate_mapping() with db_session: config = db_config.Config.get(name=schema, version=major_version) if not config: raise KeyError('schema not exists') config = config.config for p in config['packages']: try: p = get_distribution(p) import_module(p.project_name) except (DistributionNotFound, VersionConflict): raise ImportError( f'packages not installed or has invalid versions: {p}') db = Database() LazyEntityMeta.attach(db, schema, 'CGRdb') db.bind('postgres', *args, **kwargs) db.generate_mapping() init = f'SELECT "{schema}".cgrdb_init_session(\'{dumps(config)}\')' db.cgrdb_init_session = db_session()( lambda: db.execute(init) and True or False) db.cgrdb_init_session() return db
def update_core(args): major_version = '.'.join(get_distribution('CGRdb').version.split('.')[:-1]) schema = args.name db_config = Database() LazyEntityMeta.attach(db_config, database='CGRdb_config') db_config.bind('postgres', **args.connection) db_config.generate_mapping() with db_session: config = db_config.Config.get(name=schema, version=major_version) if not config: raise KeyError('schema not exists or version incompatible') config = config.config for p in config['packages']: try: p = get_distribution(p) import_module(p.project_name) except (DistributionNotFound, VersionConflict): raise ImportError( f'packages not installed or has invalid versions: {p}') db = Database() LazyEntityMeta.attach(db, schema, 'CGRdb') db.bind('postgres', **args.connection) db.generate_mapping() with db_session: db.execute(init_session.replace('{schema}', schema)) db.execute(insert_molecule.replace('{schema}', schema)) db.execute(after_insert_molecule.replace('{schema}', schema)) db.execute(delete_molecule.replace('{schema}', schema)) db.execute(insert_reaction.replace('{schema}', schema)) db.execute(merge_molecules.replace('{schema}', schema)) db.execute(search_structure_molecule.replace('{schema}', schema)) db.execute(search_structure_reaction.replace('{schema}', schema)) db.execute(search_similar_molecules.replace('{schema}', schema)) db.execute(search_substructure_molecule.replace('{schema}', schema)) db.execute(search_similar_reactions.replace('{schema}', schema)) db.execute(search_substructure_reaction.replace('{schema}', schema)) db.execute(search_reactions_by_molecule.replace('{schema}', schema)) db.execute(search_mappingless_reaction.replace('{schema}', schema))
def create_core(args): major_version = '.'.join(get_distribution('CGRdb').version.split('.')[:-1]) schema = args.name config = args.config and load(args.config) or {} if 'packages' not in config: config['packages'] = [] for p in config['packages']: try: p = get_distribution(p) import_module(p.project_name) except (DistributionNotFound, VersionConflict): raise ImportError( f'packages not installed or has invalid versions: {p}') db_config = Database() LazyEntityMeta.attach(db_config, database='CGRdb_config') db_config.bind('postgres', user=args.user, password=args.password, host=args.host, database=args.base, port=args.port) db_config.generate_mapping() with db_session: if db_config.Config.exists(name=schema): raise KeyError('schema already exists') db = Database() LazyEntityMeta.attach(db, schema, 'CGRdb') db.bind('postgres', user=args.user, password=args.password, host=args.host, database=args.base, port=args.port) db.generate_mapping(create_tables=True) with db_session: db.execute(f'ALTER TABLE "{schema}"."Reaction" DROP COLUMN structure') db.execute( f'ALTER TABLE "{schema}"."Reaction" RENAME TO "ReactionRecord"') db.execute( f'CREATE VIEW "{schema}"."Reaction" AS SELECT id, NULL::bytea as structure ' f'FROM "{schema}"."ReactionRecord"') db.execute(init_session.replace('{schema}', schema)) db.execute(merge_molecules.replace('{schema}', schema)) db.execute(insert_molecule.replace('{schema}', schema)) db.execute(insert_molecule_trigger.replace('{schema}', schema)) db.execute(after_insert_molecule.replace('{schema}', schema)) db.execute(after_insert_molecule_trigger.replace('{schema}', schema)) db.execute(delete_molecule.replace('{schema}', schema)) db.execute(delete_molecule_trigger.replace('{schema}', schema)) db.execute(insert_reaction.replace('{schema}', schema)) db.execute(insert_reaction_trigger.replace('{schema}', schema)) db.execute(search_similar_molecules.replace('{schema}', schema)) db.execute(search_substructure_molecule.replace('{schema}', schema)) db.execute(search_similar_reactions.replace('{schema}', schema)) db.execute(search_substructure_reaction.replace('{schema}', schema)) db.execute( search_substructure_fingerprint_molecule.replace( '{schema}', schema)) db.execute( search_similar_fingerprint_molecule.replace('{schema}', schema)) db.execute(search_reactions_by_molecule.replace('{schema}', schema)) db.execute(search_mappingless_reaction.replace('{schema}', schema)) if args.indexed: with db_session: db.execute( f'CREATE INDEX idx_moleculestructure__smlar ON "{schema}"."MoleculeStructure" USING ' 'GIST (fingerprint _int4_sml_ops)') db.execute( f'CREATE INDEX idx_moleculestructure__subst ON "{schema}"."MoleculeStructure" USING ' 'GIN (fingerprint gin__int_ops)') db.execute( f'CREATE INDEX idx_reactionindex__smlar ON "{schema}"."ReactionIndex" USING ' 'GIST (fingerprint _int4_sml_ops)') db.execute( f'CREATE INDEX idx_reactionindex__subst ON "{schema}"."ReactionIndex" USING ' 'GIN (fingerprint gin__int_ops)') with db_session: db_config.Config(name=schema, config=config, version=major_version)
def create_core(args): schema = args.name config = args.config and load(args.config) or {} db_config = Database() LazyEntityMeta.attach(db_config, database='CGRdb_config') db_config.bind('postgres', user=args.user, password=args.password, host=args.host, database=args.base, port=args.port) db_config.generate_mapping() db = Database() LazyEntityMeta.attach(db, schema, 'CGRdb') db.bind('postgres', user=args.user, password=args.password, host=args.host, database=args.base, port=args.port) db.generate_mapping(create_tables=True) with db_session: db.execute('CREATE EXTENSION IF NOT EXISTS smlar') db.execute('CREATE EXTENSION IF NOT EXISTS intarray') db.execute('CREATE EXTENSION IF NOT EXISTS pg_cron') with db_session: db.execute(f'CREATE INDEX idx_smlar_molecule_structure ON "{schema}"."MoleculeStructure" USING ' 'GIST (bit_array _int4_sml_ops)') db.execute(f'CREATE INDEX idx_smlar_reaction_index ON "{schema}"."ReactionIndex" USING ' 'GIST (bit_array _int4_sml_ops)') db.execute(f'CREATE INDEX idx_subst_molecule_structure ON "{schema}"."MoleculeStructure" USING ' 'GIN (bit_array gin__int_ops)') db.execute(f'CREATE INDEX idx_subst_reaction_index ON "{schema}"."ReactionIndex" USING ' 'GIN (bit_array gin__int_ops)') db.execute(f"SELECT cron.schedule('0 3 * * *', $$$$\n" f'DELETE FROM "{schema}"."MoleculeSearchCache"' " WHERE date < CURRENT_TIMESTAMP - INTERVAL '1 day' $$$$)") db.execute(f"SELECT cron.schedule('0 3 * * *', $$$$\n" f'DELETE FROM "{schema}"."ReactionSearchCache"' " WHERE date < CURRENT_TIMESTAMP - INTERVAL '1 day' $$$$)") with db_session: db_config.Config(name=schema, config=config, version=major_version)
def index_core(args): from ..index import SimilarityIndex, SubstructureIndex major_version = '.'.join(get_distribution('CGRdb').version.split('.')[:-1]) schema = args.name db_config = Database() LazyEntityMeta.attach(db_config, database='CGRdb_config') db_config.bind('postgres', **args.connection) db_config.generate_mapping() with db_session: config = db_config.Config.get(name=schema, version=major_version) if not config: raise KeyError('schema not exists or version incompatible') config = config.config for p in config['packages']: try: p = get_distribution(p) import_module(p.project_name) except (DistributionNotFound, VersionConflict): raise ImportError( f'packages not installed or has invalid versions: {p}') db = Database() LazyEntityMeta.attach(db, schema, 'CGRdb') db.bind('postgres', **args.connection) db.generate_mapping() if 'check_threshold' in args.params: sort_by_tanimoto = args.params['check_threshold'] is not None else: sort_by_tanimoto = True with db_session: substructure_molecule = SubstructureIndex( db.execute( f'SELECT id, fingerprint FROM "{schema}"."MoleculeStructure"'), False) with db_session: similarity_molecule = SimilarityIndex( db.execute( f'SELECT id, fingerprint FROM "{schema}"."MoleculeStructure"'), **args.params) if sort_by_tanimoto: # pairing fingerprints for memory saving substructure_molecule._fingerprints = similarity_molecule._fingerprints with db_session: substructure_reaction = SubstructureIndex( db.execute( f'SELECT id, fingerprint FROM "{schema}"."ReactionIndex"'), False) with db_session: similarity_reaction = SimilarityIndex( db.execute( f'SELECT id, fingerprint FROM "{schema}"."ReactionIndex"'), **args.params) if sort_by_tanimoto: # pairing fingerprints for memory saving substructure_reaction._fingerprints = similarity_reaction._fingerprints dump((substructure_molecule, substructure_reaction, similarity_molecule, similarity_reaction), args.data)
def create_core(args): major_version = '.'.join(get_distribution('CGRdb').version.split('.')[:-1]) schema = args.name config = args.config and load(args.config) or {} if 'packages' not in config: config['packages'] = [] for p in config['packages']: try: p = get_distribution(p) import_module(p.project_name) except (DistributionNotFound, VersionConflict): raise ImportError( f'packages not installed or has invalid versions: {p}') db_config = Database() LazyEntityMeta.attach(db_config, database='CGRdb_config') db_config.bind('postgres', **args.connection) db_config.generate_mapping() with db_session: if db_config.Config.exists(name=schema): raise KeyError('schema already exists') with db_session: db_config.execute(f'DROP SCHEMA IF EXISTS {schema} CASCADE') db_config.execute(f'CREATE SCHEMA {schema}') db = Database() LazyEntityMeta.attach(db, schema, 'CGRdb') db.bind('postgres', **args.connection) db.generate_mapping(create_tables=True) with db_session: db.execute(f'ALTER TABLE "{schema}"."Reaction" DROP COLUMN structure') db.execute( f'ALTER TABLE "{schema}"."Reaction" RENAME TO "ReactionRecord"') db.execute( f'CREATE VIEW "{schema}"."Reaction" AS SELECT id, NULL::bytea as structure ' f'FROM "{schema}"."ReactionRecord"') with db_session: db.execute(init_session.replace('{schema}', schema)) db.execute(insert_molecule.replace('{schema}', schema)) db.execute(after_insert_molecule.replace('{schema}', schema)) db.execute(delete_molecule.replace('{schema}', schema)) db.execute(insert_reaction.replace('{schema}', schema)) db.execute(merge_molecules.replace('{schema}', schema)) db.execute(insert_molecule_trigger.replace('{schema}', schema)) db.execute(after_insert_molecule_trigger.replace('{schema}', schema)) db.execute(delete_molecule_trigger.replace('{schema}', schema)) db.execute(insert_reaction_trigger.replace('{schema}', schema)) db.execute(search_structure_molecule.replace('{schema}', schema)) db.execute(search_structure_reaction.replace('{schema}', schema)) db.execute(search_similar_molecules.replace('{schema}', schema)) db.execute(search_substructure_molecule.replace('{schema}', schema)) db.execute(search_similar_reactions.replace('{schema}', schema)) db.execute(search_substructure_reaction.replace('{schema}', schema)) db.execute(search_reactions_by_molecule.replace('{schema}', schema)) db.execute(search_mappingless_reaction.replace('{schema}', schema)) with db_session: db_config.Config(name=schema, config=config, version=major_version)