def record_dependency(jwt_payload: dict, schema_name: str, table_name: str, primary_restriction: dict) -> list: """ Return summary of dependencies associated with a restricted table :param jwt_payload: Dictionary containing databaseAddress, username and password strings :type jwt_payload: dict :param schema_name: Name of schema :type schema_name: str :param table_name: Table name under the given schema; must be in camel case :type table_name: str :param primary_restriction: Restriction to be applied to table :type primary_restriction: dict :return: Tables that are dependant on specific records. Includes accessibility and, if accessible, how many rows are affected. :rtype: list """ DJConnector.set_datajoint_config(jwt_payload) virtual_module = dj.VirtualModule(schema_name, schema_name) table = getattr(virtual_module, table_name) # Retrieve dependencies of related to retricted dependencies = [ dict(schema=descendant.database, table=descendant.table_name, accessible=True, count=len(descendant & primary_restriction)) for descendant in table().descendants(as_objects=True) ] return dependencies
def virtual_module(): dj.config['safemode'] = False connection = dj.conn(host=getenv('TEST_DB_SERVER'), user=getenv('TEST_DB_USER'), password=getenv('TEST_DB_PASS'), reset=True) schema = dj.Schema('filter') @schema class Student(dj.Lookup): definition = """ student_id: int --- student_name: varchar(50) student_ssn: varchar(20) student_enroll_date: datetime student_balance: float student_parking_lot=null : varchar(20) student_out_of_state: bool """ contents = [(i, faker.name(), faker.ssn(), faker.date_between_dates(date_start=date(2021, 1, 1), date_end=date(2021, 1, 31)), round(randint(1000, 3000), 2), choice([None, 'LotA', 'LotB', 'LotC']), bool(getrandbits(1))) for i in range(100)] yield dj.VirtualModule('filter', 'filter') schema.drop() connection.close() dj.config['safemode'] = True
def test_rename_non_dj_attribute(): schema = PREFIX + "_test1" connection = dj.conn(**CONN_INFO) connection.query( f"CREATE TABLE {schema}.test_table (oldID int PRIMARY KEY)" ).fetchall() mySchema = dj.VirtualModule(schema, schema) assert ("oldID" not in mySchema.TestTable.proj( new_name="oldID").heading.attributes.keys() ), "Failed to rename attribute correctly" connection.query(f"DROP TABLE {schema}.test_table")
def test_delete_independent_without_cascade(token, client, connection, schemas_simple): schema_name = f'{SCHEMA_PREFIX}group1_simple' table_name = 'TableB' restriction = dict(a_id=1, b_id=21) vm = dj.VirtualModule('group1_simple', schema_name) REST_response = client.post( '/delete_tuple?cascade=fAlSe', headers=dict(Authorization=f'Bearer {token}'), json=dict(schemaName=schema_name, tableName=table_name, restrictionTuple=restriction)) assert REST_response.status_code == 200 assert len(getattr(vm, table_name) & restriction) == 0
def test_delete_invalid(token, client, connection, schemas_simple): schema_name = f'{SCHEMA_PREFIX}group1_simple' table_name = 'TableB' restriction = dict() vm = dj.VirtualModule('group1_simple', schema_name) REST_response = client.post( '/delete_tuple?cascade=TRUE', headers=dict(Authorization=f'Bearer {token}'), json=dict(schemaName=schema_name, tableName=table_name, restrictionTuple=restriction)) assert REST_response.status_code == 500 assert b'Restriction is invalid' in REST_response.data assert len(getattr(vm, table_name)()) == 3
def test_delete_independent_without_cascade(token, client, connection, schemas_simple): schema_name = f"{SCHEMA_PREFIX}group1_simple" table_name = "TableB" restriction = dict(a_id=1, b_id=21) filters = [ dict(attributeName=k, operation="=", value=v) for k, v in restriction.items() ] encoded_filters = b64encode(dumps(filters).encode("utf-8")).decode("utf-8") q = dict(cascade="fAlSe", restriction=encoded_filters) vm = dj.VirtualModule("group1_simple", schema_name) REST_response = client.delete( f"/schema/{schema_name}/table/{table_name}/record?{urlencode(q)}", headers=dict(Authorization=f"Bearer {token}"), ) assert REST_response.status_code == 200 assert len(getattr(vm, table_name) & restriction) == 0
def refresh_schema(self): """refresh container of schemas """ schemata = {} for schema in dj.list_schemas(): if schema in self["skip_schemas"]: continue # TODO error messages schemata[schema] = dj.VirtualModule( schema, schema, connection=self['connection'], add_objects=custom_attributes_dict, create_tables=True) # make sure jobs table has been created schemata[schema].schema.jobs self['schemata'] = schemata
def _delete_records( jwt_payload: dict, schema_name: str, table_name: str, restriction: list = [], cascade: bool = False, ): """ Delete a specific record based on the restriction given. :param jwt_payload: Dictionary containing databaseAddress, username, and password strings :type jwt_payload: dict :param schema_name: Name of schema :type schema_name: str :param table_name: Table name under the given schema; must be in camel case :type table_name: str :param restriction: Sequence of filters as ``dict`` with ``attributeName``, ``operation``, ``value`` keys defined, defaults to ``[]`` :type restriction: list, optional :param cascade: Allow for cascading delete, defaults to ``False`` :type cascade: bool, optional """ _DJConnector._set_datajoint_config(jwt_payload) schema_virtual_module = dj.VirtualModule(schema_name, schema_name) # Get table object from name table = _DJConnector._get_table_object(schema_virtual_module, table_name) attributes = table.heading.attributes restrictions = [ _DJConnector._filter_to_restriction( f, attributes[f["attributeName"]].type) for f in restriction ] # Compute restriction query = table & dj.AndList(restrictions) # Check if there is only 1 tuple to delete otherwise raise error if len(query) == 0: raise InvalidRestriction("Nothing to delete") # All check pass thus proceed to delete query.delete(safemode=False) if cascade else query.delete_quick()
def _insert_tuple(jwt_payload: dict, schema_name: str, table_name: str, tuple_to_insert: dict): """ Insert record as tuple into table. :param jwt_payload: Dictionary containing databaseAddress, username, and password strings :type jwt_payload: dict :param schema_name: Name of schema :type schema_name: str :param table_name: Table name under the given schema; must be in camel case :type table_name: str :param tuple_to_insert: Record to be inserted :type tuple_to_insert: dict """ _DJConnector._set_datajoint_config(jwt_payload) schema_virtual_module = dj.VirtualModule(schema_name, schema_name) getattr(schema_virtual_module, table_name).insert(tuple_to_insert)
def get_table_definition(jwt_payload: dict, schema_name: str, table_name: str): """ Get the table definition :param jwt_payload: Dictionary containing databaseAddress, username and password strings :type jwt_payload: dict :param schema_name: Name of schema to list all tables from :type schema_name: str :param table_name: Table name under the given schema; must be in camel case :type table_name: str :return: definition of the table :rtype: str """ DJConnector.set_datajoint_config(jwt_payload) local_values = locals() local_values[schema_name] = dj.VirtualModule(schema_name, schema_name) return getattr(local_values[schema_name], table_name).describe()
def record_dependency(jwt_payload: dict, schema_name: str, table_name: str, primary_restriction: dict) -> list: """ Return summary of dependencies associated with a restricted table :param jwt_payload: Dictionary containing databaseAddress, username and password strings :type jwt_payload: dict :param schema_name: Name of schema :type schema_name: str :param table_name: Table name under the given schema; must be in camel case :type table_name: str :param primary_restriction: Restriction to be applied to table :type primary_restriction: dict :return: Tables that are dependant on specific records. Includes accessibility and, if accessible, how many rows are affected. :rtype: list """ DJConnector.set_datajoint_config(jwt_payload) virtual_module = dj.VirtualModule(schema_name, schema_name) table = getattr(virtual_module, table_name) # Retrieve dependencies of related to retricted dependencies = [ dict(schema=descendant.database, table=descendant.table_name, accessible=True, count=len(descendant & primary_restriction)) for descendant in table().descendants(as_objects=True) ] # Determine first issue regarding access # Start transaction, try to delete, catch first occurrence, rollback virtual_module.schema.connection.start_transaction() try: (table & primary_restriction).delete(safemode=False, transaction=False) except AccessError as errors: dependencies = dependencies + [ dict(TABLE_INFO_REGEX.match(errors.args[2]).groupdict(), accessible=False) ] finally: virtual_module.schema.connection.cancel_transaction() return dependencies
def refresh_schema(self): """refresh container of schemas """ schemata = {} # direct loading if possible # TODO (also in app init) if self['init_database']: from loris.schema import (experimenters, core) schemata['experimenters'] = experimenters schemata['core'] = core if self['include_fly']: from loris.schema import anatomy, subjects schemata['anatomy'] = anatomy # move out schemata['subjects'] = subjects for schema, module_path in self["import_schema_module"]: # TODO test spec = importlib.util.spec_from_file_location(schema, module_path) module = importlib.util.module_from_spec(spec) spec.loader.exec_module(module) schemata[schema] = module for schema in dj.list_schemas(): if schema in self["skip_schemas"]: continue if schema in schemata: continue # TODO error messages schemata[schema] = dj.VirtualModule( schema, schema, connection=self['connection'], add_objects=custom_attributes_dict, create_tables=True) # make sure jobs table has been created schemata[schema].schema.jobs self['schemata'] = schemata
def _record_dependency(jwt_payload: dict, schema_name: str, table_name: str, restriction: list = []) -> list: """ Return summary of dependencies associated with a restricted table. Will only show dependencies that user has access to. :param jwt_payload: Dictionary containing databaseAddress, username, and password strings :type jwt_payload: dict :param schema_name: Name of schema :type schema_name: str :param table_name: Table name under the given schema; must be in camel case :type table_name: str :param restriction: Sequence of filters as ``dict`` with ``attributeName``, ``operation``, ``value`` keys defined, defaults to ``[]`` :type restriction: list :return: Tables that are dependent on specific records. :rtype: list """ _DJConnector._set_datajoint_config(jwt_payload) virtual_module = dj.VirtualModule(schema_name, schema_name) table = getattr(virtual_module, table_name) attributes = table.heading.attributes # Retrieve dependencies of related to retricted dependencies = [ dict( schema=descendant.database, table=descendant.table_name, accessible=True, count=len((table if descendant.full_table_name == table.full_table_name else descendant * table) & dj.AndList([ _DJConnector._filter_to_restriction( f, attributes[f["attributeName"]].type) for f in restriction ])), ) for descendant in table().descendants(as_objects=True) ] return dependencies
def __init__(self, name, component_config, static_config, jwt_payload: dict): lcls = locals() self.name = name if static_config: self.static_variables = types.MappingProxyType(static_config) if not all(k in component_config for k in ("x", "y", "height", "width")): self.mode = "dynamic" else: self.mode = "fixed" self.x = component_config["x"] self.y = component_config["y"] self.height = component_config["height"] self.width = component_config["width"] self.type = component_config["type"] self.route = component_config["route"] exec(component_config["dj_query"], globals(), lcls) self.dj_query = lcls["dj_query"] if self.attributes_route_format: self.attribute_route = self.attributes_route_format.format( route=component_config["route"] ) if "restriction" in component_config: exec(component_config["restriction"], globals(), lcls) self.dj_restriction = lcls["restriction"] else: self.dj_restriction = lambda: dict() self.vm_list = [ dj.VirtualModule( s, s, connection=dj.conn( host=jwt_payload["databaseAddress"], user=jwt_payload["username"], password=jwt_payload["password"], reset=True, ), ) for s in inspect.getfullargspec(self.dj_query).args ]
def _update_tuple(jwt_payload: dict, schema_name: str, table_name: str, tuple_to_update: dict): """ Update record as tuple into table. :param jwt_payload: Dictionary containing databaseAddress, username, and password strings :type jwt_payload: dict :param schema_name: Name of schema :type schema_name: str :param table_name: Table name under the given schema; must be in camel case :type table_name: str :param tuple_to_update: Record to be updated :type tuple_to_update: dict """ conn = _DJConnector._set_datajoint_config(jwt_payload) schema_virtual_module = dj.VirtualModule(schema_name, schema_name) with conn.transaction: [ getattr(schema_virtual_module, table_name).update1(t) for t in tuple_to_update ]
def test_uppercase_schema(): # https://github.com/datajoint/datajoint-python/issues/564 dj.conn(**CONN_INFO_ROOT, reset=True) schema1 = dj.Schema('Schema_A') @schema1 class Subject(dj.Manual): definition = """ name: varchar(32) """ Schema_A = dj.VirtualModule('Schema_A', 'Schema_A') schema2 = dj.Schema('schema_b') @schema2 class Recording(dj.Manual): definition = """ -> Schema_A.Subject id: smallint """ schema2.drop() schema1.drop()
nx.barbell_graph(3, 1), nx.cycle_graph(5) ] c.insert((i, g) for i, g in enumerate(graphs)) returned_graphs = c.fetch('conn_graph', order_by='connid') for g1, g2 in zip(graphs, returned_graphs): assert_true(isinstance(g2, nx.Graph)) assert_equal(len(g1.edges), len(g2.edges)) assert_true(0 == len(nx.symmetric_difference(g1, g2).edges)) c.delete() dj.errors._switch_adapted_types(False) # test with virtual module virtual_module = dj.VirtualModule('virtual_module', adapted.schema_name, add_objects={'graph': graph}) def test_adapted_virtual(): dj.errors._switch_adapted_types(True) c = virtual_module.Connectivity() graphs = [ nx.lollipop_graph(4, 2), nx.star_graph(5), nx.barbell_graph(3, 1), nx.cycle_graph(5) ] c.insert((i, g) for i, g in enumerate(graphs)) c.insert1({'connid': 100}) # test work with NULLs returned_graphs = c.fetch('conn_graph', order_by='connid')
def test_virtual_module(): module = dj.VirtualModule('module', schema.schema.database, connection=dj.conn(**CONN_INFO)) assert_true(issubclass(module.Experiment, UserTable))
nx.barbell_graph(3, 1), nx.cycle_graph(5), ] c.insert((i, g) for i, g in enumerate(graphs)) returned_graphs = c.fetch("conn_graph", order_by="connid") for g1, g2 in zip(graphs, returned_graphs): assert_true(isinstance(g2, nx.Graph)) assert_equal(len(g1.edges), len(g2.edges)) assert_true(0 == len(nx.symmetric_difference(g1, g2).edges)) c.delete() dj.errors._switch_adapted_types(False) # test with virtual module virtual_module = dj.VirtualModule("virtual_module", adapted.schema_name, add_objects={"graph": graph}) def test_adapted_virtual(): dj.errors._switch_adapted_types(True) c = virtual_module.Connectivity() graphs = [ nx.lollipop_graph(4, 2), nx.star_graph(5), nx.barbell_graph(3, 1), nx.cycle_graph(5), ] c.insert((i, g) for i, g in enumerate(graphs)) c.insert1({"connid": 100}) # test work with NULLs returned_graphs = c.fetch("conn_graph", order_by="connid")
def connection(): dj.config['safemode'] = False connection = dj.conn(host=getenv('TEST_DB_SERVER'), user=getenv('TEST_DB_USER'), password=getenv('TEST_DB_PASS'), reset=True) connection.query(""" CREATE USER IF NOT EXISTS 'underprivileged'@'%%' IDENTIFIED BY 'datajoint'; """) connection.query( "GRANT ALL PRIVILEGES ON `deps`.* TO 'underprivileged'@'%%';") deps_secret = dj.VirtualModule('deps_secret', 'deps_secret', create_tables=True) deps = dj.VirtualModule('deps', 'deps', create_tables=True) @deps.schema class TableA(dj.Lookup): definition = """ a_id: int --- a_name: varchar(30) """ contents = [( 0, 'Raphael', ), ( 1, 'Bernie', )] @deps.schema class TableB(dj.Lookup): definition = """ -> TableA b_id: int --- b_number: float """ contents = [(0, 10, 22.12), ( 0, 11, -1.21, ), ( 1, 21, 7.77, )] deps = dj.VirtualModule('deps', 'deps', create_tables=True) @deps_secret.schema class DiffTableB(dj.Lookup): definition = """ -> deps.TableA bs_id: int --- bs_number: float """ contents = [(0, -10, -99.99), ( 0, -11, 287.11, )] @deps.schema class TableC(dj.Lookup): definition = """ -> TableB c_id: int --- c_int: int """ contents = [(0, 10, 100, -8), ( 0, 11, 200, -9, ), ( 0, 11, 300, -7, )] yield connection deps_secret.schema.drop() deps.schema.drop() connection.query("DROP USER 'underprivileged'@'%%';") connection.close() dj.config['safemode'] = True