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)
