def basic_graph(project_id, skeleton_ids): def newSynapseCounts(): return [0, 0, 0, 0, 0] if not skeleton_ids: raise ValueError("No skeleton IDs provided") cursor = connection.cursor() relations = get_relation_to_id_map(project_id, ('presynaptic_to', 'postsynaptic_to'), cursor) preID, postID = relations['presynaptic_to'], relations['postsynaptic_to'] cursor.execute(''' SELECT t1.skeleton_id, t2.skeleton_id, LEAST(t1.confidence, t2.confidence) FROM treenode_connector t1, treenode_connector t2 WHERE t1.skeleton_id IN (%s) AND t1.relation_id = %s AND t1.connector_id = t2.connector_id AND t2.relation_id = %s ''' % (','.join(map(str, skeleton_ids)), preID, postID)) edges = defaultdict(partial(defaultdict, newSynapseCounts)) for row in cursor.fetchall(): edges[row[0]][row[1]][row[2] - 1] += 1 return {'edges': tuple((pre, post, count) for pre, edge in edges.iteritems() for post, count in edge.iteritems())} ''' return {'edges': [{'source': pre, 'target': post, 'weight': count} for pre, edge in edges.iteritems() for post, count in edge.iteritems()]} ''' """ Can't get the variable to be set with all the skeleton IDs
def skeleton_connectors_by_partner(request, project_id): """ Return a dict of requested skeleton vs relation vs partner skeleton vs list of connectors. Connectors lacking a skeleton partner will of course not be included. """ skeleton_ids = set( int(v) for k, v in request.POST.iteritems() if k.startswith('skids[')) cursor = connection.cursor() relations = get_relation_to_id_map(project_id, ('presynaptic_to', 'postsynaptic_to'), cursor) pre = relations['presynaptic_to'] post = relations['postsynaptic_to'] cursor.execute(''' SELECT tc1.skeleton_id, tc1.relation_id, tc2.skeleton_id, tc1.connector_id FROM treenode_connector tc1, treenode_connector tc2 WHERE tc1.skeleton_id IN (%s) AND tc1.connector_id = tc2.connector_id AND tc1.skeleton_id != tc2.skeleton_id AND tc1.relation_id != tc2.relation_id AND (tc1.relation_id = %s OR tc1.relation_id = %s) AND (tc2.relation_id = %s OR tc2.relation_id = %s) ''' % (','.join(map(str, skeleton_ids)), pre, post, pre, post)) # Dict of skeleton vs relation vs skeleton vs list of connectors partners = defaultdict(partial(defaultdict, partial(defaultdict, list))) for row in cursor.fetchall(): partners[row[0]][relations[row[1]]][row[2]].append(row[3]) return HttpResponse(json.dumps(partners))
def _connector_associated_edgetimes(connector_ids, project_id): """ Return a dictionary of connector ID as keys and a dictionary as value containing two entries: 'presynaptic_to' with a skeleton ID of None, and 'postsynaptic_to' with a list of skeleton IDs (maybe empty) including the timestamp of the edge. """ cursor = connection.cursor() relations = get_relation_to_id_map(project_id, ('presynaptic_to', 'postsynaptic_to'), cursor) PRE = relations['presynaptic_to'] POST = relations['postsynaptic_to'] cursor.execute(''' SELECT connector_id, relation_id, skeleton_id, treenode_id, creation_time FROM treenode_connector WHERE connector_id IN (%s) AND (relation_id = %s OR relation_id = %s) ''' % (",".join(map(str, connector_ids), PRE, POST))) cs = {} for row in cursor.fetchall(): c = cs.get(row[0]) if not c: # Ensure each connector has the two entries at their minimum c = {'presynaptic_to': None, 'postsynaptic_to': []} cs[row[0]] = c if POST == row[1]: c['postsynaptic_to'].append( (row[2], row[3], row[4]) ) elif PRE == row[1]: c['presynaptic_to'] = (row[2], row[3], row[4]) return cs
def add_test_connector_links(self): c_1 = Connector.objects.get(pk=2463) c_1.id = None c_1.save() c_2 = Connector.objects.get(pk=2466) c_2.id = None c_2.save() cursor = connection.cursor() relations = get_relation_to_id_map(self.test_project_id, cursor=cursor) pre_id, post_id = relations['presynaptic_to'], relations['postsynaptic_to'] connector_links = [ # Treenode ID, connector ID, relation_id, user ID, creation date [ 7, pre_id, c_1.id, 1, "2017-06-01T07:54:16.301Z"], [15, post_id, c_1.id, 1, "2017-06-30T22:23:24.117Z"], [11, pre_id, c_2.id, 1, "2017-07-02T08:54:16.301Z"], [13, post_id, c_2.id, 1, "2017-08-02T08:55:16.301Z"], [ 7, post_id, c_1.id, 3, "2017-07-01T07:54:16.301Z"], [11, post_id, c_1.id, 3, "2017-07-01T07:55:13.844Z"], [13, pre_id, c_2.id, 3, "2017-07-01T22:55:16.301Z"], [15, post_id, c_2.id, 3, "2017-07-01T02:50:10.204Z"], ] link_data = list(chain.from_iterable(connector_links)) link_template = ','.join('(%s,%s,%s,%s,%s)' for _ in connector_links) cursor.execute(f""" INSERT INTO treenode_connector (project_id, treenode_id, skeleton_id, relation_id, connector_id, user_id, creation_time) SELECT %s, link.treenode_id, t.skeleton_id, link.relation_id, link.connector_id, link.user_id, link.creation_time::timestamptz FROM treenode t JOIN (VALUES {link_template}) link(treenode_id, relation_id, connector_id, user_id, creation_time) ON t.id = link.treenode_id """, [self.test_project_id] + link_data)
def connector_user_info(request, project_id): """ Return information on a treenode connector edge. This function is called often (every connector mouseover) and should therefore be as fast as possible. Analogous to user_info for treenodes and connectors. """ treenode_id = int(request.GET.get('treenode_id')) connector_id = int(request.GET.get('connector_id')) cursor = connection.cursor() relation_names = ('presynaptic_to', 'postsynaptic_to', 'abutting') relations = get_relation_to_id_map(project_id, relation_names, cursor) relation_id = relations[request.GET.get('relation_name')] cursor.execute(''' SELECT tc.id, tc.user_id, tc.creation_time, tc.edition_time FROM treenode_connector tc WHERE tc.treenode_id = %s AND tc.connector_id = %s AND tc.relation_id = %s ''', (treenode_id, connector_id, relation_id)) # We expect at least one result node. if not cursor.rowcount: return HttpResponse(json.dumps({ 'error': 'No treenode connector exists for treenode %s, connector %s, relation %s' % (treenode_id, connector_id, relation_id)})) # Build result. Because there is no uniqueness restriction on treenode # connector edges, even with the same relation, the response must handle # multiple rows. return HttpResponse(json.dumps([{ 'user': info[1], 'creation_time': str(info[2].isoformat()), 'edition_time': str(info[3].isoformat()), } for info in cursor.fetchall()]))
def skeleton_connectors_by_partner(request, project_id): """ Return a dict of requested skeleton vs relation vs partner skeleton vs list of connectors. Connectors lacking a skeleton partner will of course not be included. """ skeleton_ids = set(int(v) for k,v in request.POST.iteritems() if k.startswith('skids[')) cursor = connection.cursor() relations = get_relation_to_id_map(project_id, ('presynaptic_to', 'postsynaptic_to'), cursor) pre = relations['presynaptic_to'] post = relations['postsynaptic_to'] cursor.execute(''' SELECT tc1.skeleton_id, tc1.relation_id, tc2.skeleton_id, tc1.connector_id FROM treenode_connector tc1, treenode_connector tc2 WHERE tc1.skeleton_id IN (%s) AND tc1.connector_id = tc2.connector_id AND tc1.skeleton_id != tc2.skeleton_id AND tc1.relation_id != tc2.relation_id AND (tc1.relation_id = %s OR tc1.relation_id = %s) AND (tc2.relation_id = %s OR tc2.relation_id = %s) ''' % (','.join(map(str, skeleton_ids)), pre, post, pre, post)) # Dict of skeleton vs relation vs skeleton vs list of connectors partners = defaultdict(partial(defaultdict, partial(defaultdict, list))) for row in cursor.fetchall(): partners[row[0]][relations[row[1]]][row[2]].append(row[3]) return HttpResponse(json.dumps(partners))
def _connector_associated_edgetimes(connector_ids, project_id): """ Return a dictionary of connector ID as keys and a dictionary as value containing two entries: 'presynaptic_to' with a skeleton ID of None, and 'postsynaptic_to' with a list of skeleton IDs (maybe empty) including the timestamp of the edge. """ cursor = connection.cursor() relations = get_relation_to_id_map(project_id, ('presynaptic_to', 'postsynaptic_to'), cursor) PRE = relations['presynaptic_to'] POST = relations['postsynaptic_to'] cursor.execute(''' SELECT connector_id, relation_id, skeleton_id, treenode_id, creation_time FROM treenode_connector WHERE connector_id IN (%s) AND (relation_id = %s OR relation_id = %s) ''' % (",".join(map(str, connector_ids), PRE, POST))) cs = {} for row in cursor.fetchall(): c = cs.get(row[0]) if not c: # Ensure each connector has the two entries at their minimum c = {'presynaptic_to': None, 'postsynaptic_to': []} cs[row[0]] = c if POST == row[1]: c['postsynaptic_to'].append((row[2], row[3], row[4])) elif PRE == row[1]: c['presynaptic_to'] = (row[2], row[3], row[4]) return cs
def add_test_connector_links(self): c_1 = Connector.objects.get(pk=2463) c_1.id = None c_1.save() c_2 = Connector.objects.get(pk=2466) c_2.id = None c_2.save() cursor = connection.cursor() relations = get_relation_to_id_map(self.test_project_id, cursor=cursor) pre_id, post_id = relations['presynaptic_to'], relations['postsynaptic_to'] connector_links = [ # Treenode ID, connector ID, relation_id, user ID, creation date [ 7, pre_id, c_1.id, 1, "2017-06-01T07:54:16.301Z"], [15, post_id, c_1.id, 1, "2017-06-30T22:23:24.117Z"], [11, pre_id, c_2.id, 1, "2017-07-02T08:54:16.301Z"], [13, post_id, c_2.id, 1, "2017-08-02T08:55:16.301Z"], [ 7, post_id, c_1.id, 3, "2017-07-01T07:54:16.301Z"], [11, post_id, c_1.id, 3, "2017-07-01T07:55:13.844Z"], [13, pre_id, c_2.id, 3, "2017-07-01T22:55:16.301Z"], [15, post_id, c_2.id, 3, "2017-07-01T02:50:10.204Z"], ] link_data = list(chain.from_iterable(connector_links)) link_template = ','.join('(%s,%s,%s,%s,%s)' for _ in connector_links) cursor.execute(""" INSERT INTO treenode_connector (project_id, treenode_id, skeleton_id, relation_id, connector_id, user_id, creation_time) SELECT %s, link.treenode_id, t.skeleton_id, link.relation_id, link.connector_id, link.user_id, link.creation_time::timestamptz FROM treenode t JOIN (VALUES {}) link(treenode_id, relation_id, connector_id, user_id, creation_time) ON t.id = link.treenode_id """.format(link_template), [self.test_project_id] + link_data)
def test_delete_non_root_treenode(self): self.fake_authentication() treenode_id = 265 relation_map = get_relation_to_id_map(self.test_project_id) get_skeleton = lambda: TreenodeClassInstance.objects.filter( project=self.test_project_id, relation=relation_map['element_of'], treenode=treenode_id) self.assertEqual(1, get_skeleton().count()) children = Treenode.objects.filter(parent=treenode_id) self.assertTrue(children.count() > 0) tn_count = Treenode.objects.all().count() parent = get_object_or_404(Treenode, id=treenode_id).parent response = self.client.post( '/%d/treenode/delete' % self.test_project_id, { 'treenode_id': treenode_id, 'state': make_nocheck_state() }) self.assertEqual(response.status_code, 200) parsed_response = json.loads(response.content.decode('utf-8')) expected_result = 'Removed treenode successfully.' self.assertEqual(expected_result, parsed_response['success']) self.assertEqual(0, Treenode.objects.filter(id=treenode_id).count()) self.assertEqual(0, get_skeleton().count()) self.assertEqual(tn_count - 1, Treenode.objects.all().count()) for child in children: child_after_change = get_object_or_404(Treenode, id=child.id) self.assertEqual(parent, child_after_change.parent)
def test_delete_non_root_treenode(self): self.fake_authentication() treenode_id = 265 relation_map = get_relation_to_id_map(self.test_project_id) get_skeleton = lambda: TreenodeClassInstance.objects.filter( project=self.test_project_id, relation=relation_map['element_of'], treenode=treenode_id) self.assertEqual(1, get_skeleton().count()) children = Treenode.objects.filter(parent=treenode_id) self.assertTrue(children.count() > 0) tn_count = Treenode.objects.all().count() parent = get_object_or_404(Treenode, id=treenode_id).parent response = self.client.post( '/%d/treenode/delete' % self.test_project_id, {'treenode_id': treenode_id, 'state': make_nocheck_state()}) self.assertEqual(response.status_code, 200) parsed_response = json.loads(response.content.decode('utf-8')) expected_result = 'Removed treenode successfully.' self.assertEqual(expected_result, parsed_response['success']) self.assertEqual(0, Treenode.objects.filter(id=treenode_id).count()) self.assertEqual(0, get_skeleton().count()) self.assertEqual(tn_count - 1, Treenode.objects.all().count()) for child in children: child_after_change = get_object_or_404(Treenode, id=child.id) self.assertEqual(parent, child_after_change.parent)
def connectors_info(request, project_id): """ Given a list of connectors, a list of presynaptic skeletons and a list of postsynatic skeletons, return a list of rows, one per synaptic connection, in the same format as one_to_many_synapses. The list of connectors is optional. """ cids = tuple( str(int(v)) for k, v in request.POST.iteritems() if k.startswith('cids[')) skids_pre = tuple( str(int(v)) for k, v in request.POST.iteritems() if k.startswith('pre[')) skids_post = tuple( str(int(v)) for k, v in request.POST.iteritems() if k.startswith('post[')) cursor = connection.cursor() relations = get_relation_to_id_map(project_id, ('presynaptic_to', 'postsynaptic_to'), cursor) pre = relations['presynaptic_to'] post = relations['postsynaptic_to'] cursor.execute(''' SELECT DISTINCT tc1.connector_id, c.location_x, c.location_y, c.location_z, tc1.treenode_id, tc1.skeleton_id, tc1.confidence, tc1.user_id, t1.location_x, t1.location_y, t1.location_z, tc2.treenode_id, tc2.skeleton_id, tc2.confidence, tc2.user_id, t2.location_x, t2.location_y, t2.location_z FROM treenode_connector tc1, treenode_connector tc2, treenode t1, treenode t2, connector c WHERE %s tc1.connector_id = c.id AND tc1.connector_id = tc2.connector_id AND tc1.skeleton_id IN (%s) AND tc2.skeleton_id IN (%s) AND tc1.relation_id = %s AND tc2.relation_id = %s AND tc1.id != tc2.id AND tc1.treenode_id = t1.id AND tc2.treenode_id = t2.id ORDER BY tc2.skeleton_id ''' % ("c.id IN (%s) AND" % ",".join(cids) if cids else "", ",".join(skids_pre), ",".join(skids_post), pre, post)) rows = tuple((row[0], (row[1], row[2], row[3]), row[4], row[5], row[6], row[7], (row[8], row[9], row[10]), row[11], row[12], row[13], row[14], (row[15], row[16], row[17])) for row in cursor.fetchall()) return HttpResponse(json.dumps(rows))
def basic_graph(project_id, skeleton_ids, relations=None, source_link: str = "presynaptic_to", target_link: str = "postsynaptic_to", allowed_connector_ids=None) -> Dict[str, Tuple]: if not skeleton_ids: raise ValueError("No skeleton IDs provided") cursor = connection.cursor() if not relations: relations = get_relation_to_id_map(project_id, (source_link, target_link), cursor) source_rel_id, target_rel_id = relations[source_link], relations[ target_link] undirected_links = source_link in UNDIRECTED_LINK_TYPES and \ target_link in UNDIRECTED_LINK_TYPES # Find all links in the passed in set of skeletons. If a relation is # reciprocal, we need to avoid getting two result rows back for each # treenode-connector-treenode connection. To keep things simple, we will add # a "skeleton ID 1" < "skeleton ID 2" test for reciprocal links. cursor.execute( f""" SELECT t1.skeleton_id, t2.skeleton_id, LEAST(t1.confidence, t2.confidence) FROM treenode_connector t1, treenode_connector t2 WHERE t1.skeleton_id = ANY(%(skeleton_ids)s::bigint[]) AND t1.relation_id = %(source_rel)s AND t1.connector_id = t2.connector_id AND t2.skeleton_id = ANY(%(skeleton_ids)s::bigint[]) AND t2.relation_id = %(target_rel)s AND t1.id <> t2.id {'AND t1.skeleton_id < t2.skeleton_id' if undirected_links else ''} {'AND t1.connector_id = ANY(%(allowed_c_ids)s::bigint[])' if allowed_connector_ids else ''} """, { 'skeleton_ids': list(skeleton_ids), 'source_rel': source_rel_id, 'target_rel': target_rel_id, 'allowed_c_ids': allowed_connector_ids, }) edges: DefaultDict = defaultdict( partial(defaultdict, make_new_synapse_count_array)) for row in cursor.fetchall(): edges[row[0]][row[1]][row[2] - 1] += 1 return { 'edges': tuple((s, t, count) for s, edge in edges.items() for t, count in edge.items()) }
def test_create_treenode2(self): self.fake_authentication() relation_map = get_relation_to_id_map(self.test_project_id) class_map = get_class_to_id_map(self.test_project_id) count_treenodes = lambda: Treenode.objects.all().count() count_skeletons = lambda: ClassInstance.objects.filter( project=self.test_project_id, class_column=class_map['skeleton' ]).count() count_neurons = lambda: ClassInstance.objects.filter( project=self.test_project_id, class_column=class_map['neuron' ]).count() treenode_count = count_treenodes() skeleton_count = count_skeletons() neuron_count = count_neurons() response = self.client.post( '/%d/treenode/create' % self.test_project_id, { 'x': 5, 'y': 10, 'z': 15, 'confidence': 5, 'parent_id': -1, 'radius': 2 }) self.assertEqual(response.status_code, 200) parsed_response = json.loads(response.content.decode('utf-8')) self.assertTrue('treenode_id' in parsed_response) self.assertTrue('skeleton_id' in parsed_response) self.assertEqual(treenode_count + 1, count_treenodes()) self.assertEqual(skeleton_count + 1, count_skeletons()) self.assertEqual(neuron_count + 1, count_neurons()) treenode_skeleton_relation = TreenodeClassInstance.objects.filter( project=self.test_project_id, relation=relation_map['element_of'], treenode=parsed_response['treenode_id'], class_instance=parsed_response['skeleton_id']) neuron_skeleton_relation = ClassInstanceClassInstance.objects.filter( project=self.test_project_id, relation=relation_map['model_of'], class_instance_a=parsed_response['skeleton_id']) # FIXME: Log test doesn't work like this, because we don't have the # neuron ID available #neuron_log = Log.objects.filter( # project=self.test_project_id, # operation_type='create_neuron', # freetext='Create neuron %s and skeleton %s' % (parsed_response['neuron_id'], parsed_response['skeleton_id'])) root = ClassInstance.objects.filter(project=self.test_project_id, class_column=class_map['root'])[0] self.assertEqual(1, neuron_skeleton_relation.count())
def connectors_info(request, project_id): """ Given a list of connectors, a list of presynaptic skeletons and a list of postsynatic skeletons, return a list of rows, one per synaptic connection, in the same format as one_to_many_synapses. The list of connectors is optional. """ int_to_str = lambda x: str(int(x)) cids = get_request_list(request.POST, 'cids', map_fn=int_to_str) skids_pre = get_request_list(request.POST, 'pre', map_fn=int_to_str) skids_post = get_request_list(request.POST, 'post', map_fn=int_to_str) cursor = connection.cursor() relations = get_relation_to_id_map(project_id, ('presynaptic_to', 'postsynaptic_to'), cursor) pre = relations['presynaptic_to'] post = relations['postsynaptic_to'] cursor.execute(''' SELECT DISTINCT tc1.connector_id, c.location_x, c.location_y, c.location_z, tc1.treenode_id, tc1.skeleton_id, tc1.confidence, tc1.user_id, t1.location_x, t1.location_y, t1.location_z, tc2.treenode_id, tc2.skeleton_id, tc2.confidence, tc2.user_id, t2.location_x, t2.location_y, t2.location_z FROM treenode_connector tc1, treenode_connector tc2, treenode t1, treenode t2, connector c WHERE %s tc1.connector_id = c.id AND tc1.connector_id = tc2.connector_id AND tc1.skeleton_id IN (%s) AND tc2.skeleton_id IN (%s) AND tc1.relation_id = %s AND tc2.relation_id = %s AND tc1.id != tc2.id AND tc1.treenode_id = t1.id AND tc2.treenode_id = t2.id ORDER BY tc2.skeleton_id ''' % ("c.id IN (%s) AND" % ",".join(cids) if cids else "", ",".join(skids_pre), ",".join(skids_post), pre, post)) rows = tuple((row[0], (row[1], row[2], row[3]), row[4], row[5], row[6], row[7], (row[8], row[9], row[10]), row[11], row[12], row[13], row[14], (row[15], row[16], row[17])) for row in cursor.fetchall()) return HttpResponse(json.dumps(rows))
def tree_object_expand(request, project_id=None): class_instance_id = request.POST.get('class_instance_id', None) if class_instance_id is None: raise Exception('A skeleton id has not been provided!') else: class_instance_id = int(class_instance_id) # sanitize by casting to int relation_map = get_relation_to_id_map(project_id) # Treenode is element_of class_instance (skeleton), which is model_of (neuron) # which is part_of class_instance (?), recursively, until reaching class_instance # ('root'). response_on_error = '' try: # 1. Retrieve neuron id of the skeleton response_on_error = 'Cannot find neuron for the skeleton with id: %s' % class_instance_id neuron_id = ClassInstanceClassInstance.objects.filter( project=project_id, relation=relation_map['model_of'], class_instance_a=class_instance_id)[0].class_instance_b_id path = [class_instance_id, neuron_id] while True: # 2. Retrieve all the nodes of which the neuron is a part of. response_on_error = 'Cannot find parent instance for instance with id: %s' % path[-1] parent = ClassInstanceClassInstance.objects.filter( project=project_id, class_instance_a=path[-1], relation=relation_map['part_of']).values( 'class_instance_b', 'class_instance_b__class_column__class_name', 'class_instance_b__name')[0] path.append(parent['class_instance_b']) # The 'Isolated synaptic terminals' is a special group: # 1. Its contained elements are never listed by default. # 2. If a treenode is selected that belongs to it, the neuron of the skeleton of that node # is listed alone. # Here, interrupt the chain at the group level if 'Isolated synaptic terminals' == parent['class_instance_b__name']: break if 'root' == parent['class_instance_b__class_column__class_name']: break path.reverse() return HttpResponse(json.dumps(path)) except Exception as e: raise Exception(response_on_error + ':' + str(e))
def test_create_treenode2(self): self.fake_authentication() relation_map = get_relation_to_id_map(self.test_project_id) class_map = get_class_to_id_map(self.test_project_id) count_treenodes = lambda: Treenode.objects.all().count() count_skeletons = lambda: ClassInstance.objects.filter( project=self.test_project_id, class_column=class_map['skeleton']).count() count_neurons = lambda: ClassInstance.objects.filter( project=self.test_project_id, class_column=class_map['neuron']).count() treenode_count = count_treenodes() skeleton_count = count_skeletons() neuron_count = count_neurons() response = self.client.post('/%d/treenode/create' % self.test_project_id, { 'x': 5, 'y': 10, 'z': 15, 'confidence': 5, 'parent_id': -1, 'radius': 2}) self.assertEqual(response.status_code, 200) parsed_response = json.loads(response.content.decode('utf-8')) self.assertTrue('treenode_id' in parsed_response) self.assertTrue('skeleton_id' in parsed_response) self.assertEqual(treenode_count + 1, count_treenodes()) self.assertEqual(skeleton_count + 1, count_skeletons()) self.assertEqual(neuron_count + 1, count_neurons()) treenode_skeleton_relation = TreenodeClassInstance.objects.filter( project=self.test_project_id, relation=relation_map['element_of'], treenode=parsed_response['treenode_id'], class_instance=parsed_response['skeleton_id']) neuron_skeleton_relation = ClassInstanceClassInstance.objects.filter( project=self.test_project_id, relation=relation_map['model_of'], class_instance_a=parsed_response['skeleton_id']) # FIXME: Log test doesn't work like this, because we don't have the # neuron ID available #neuron_log = Log.objects.filter( # project=self.test_project_id, # operation_type='create_neuron', # freetext='Create neuron %s and skeleton %s' % (parsed_response['neuron_id'], parsed_response['skeleton_id'])) root = ClassInstance.objects.filter( project=self.test_project_id, class_column=class_map['root'])[0] self.assertEqual(1, neuron_skeleton_relation.count())
def _list_completed(project_id, completed_by=None, from_date=None, to_date=None): """ Get a list of connector links that can be optionally constrained to be completed by a certain user in a given time frame. The returned connector links are by default only constrained by both sides having different relations and the first link was created before the second one. """ cursor = connection.cursor() relations = get_relation_to_id_map(project_id, ('presynaptic_to', 'postsynaptic_to'), cursor) pre = relations['presynaptic_to'] post = relations['postsynaptic_to'] params = [project_id, pre, post, pre, post] query = ''' SELECT tc2.connector_id, c.location_x, c.location_y, c.location_z, tc2.treenode_id, tc2.skeleton_id, tc2.confidence, tc2.user_id, t2.location_x, t2.location_y, t2.location_z, tc1.treenode_id, tc1.skeleton_id, tc1.confidence, tc1.user_id, t1.location_x, t1.location_y, t1.location_z FROM treenode_connector tc1 JOIN treenode_connector tc2 ON tc1.connector_id = tc2.connector_id JOIN connector c ON tc1.connector_id = c.id JOIN treenode t1 ON t1.id = tc1.treenode_id JOIN treenode t2 ON t2.id = tc2.treenode_id WHERE t1.project_id=%s AND tc1.relation_id <> tc2.relation_id AND tc1.creation_time > tc2.creation_time AND (tc1.relation_id = %s OR tc1.relation_id = %s) AND (tc2.relation_id = %s OR tc2.relation_id = %s)''' if completed_by: params.append(completed_by) query += " AND tc1.user_id=%s" if from_date: params.append(from_date.isoformat()) query += " AND tc1.creation_time >= %s" if to_date: to_date = to_date + timedelta(days=1) params.append(to_date.isoformat()) query += " AND tc1.creation_time < %s" cursor.execute(query, params) return tuple((row[0], (row[1], row[2], row[3]), row[4], row[5], row[6], row[7], (row[8], row[9], row[10]), row[11], row[12], row[13], row[14], (row[15], row[16], row[17])) for row in cursor.fetchall())
def populate_connector_stats_summary(project_id, incremental=True, cursor=None): """Add connector summary information to the summary table. Create hourly aggregates in UTC time. These aggregates can still be moved in other timezones with good enough precision for our purpose. By default, this happens in an incremental manner, but can optionally be fone for all data from scratch (overriding existing statistics). """ if not cursor: cursor = connection.cursor() relations = get_relation_to_id_map(project_id, cursor=cursor) pre_id, post_id = relations.get('presynaptic_to'), relations.get( 'postsynaptic_to') if pre_id and post_id: cursor.execute( """ WITH last_precomputation AS ( SELECT CASE WHEN %(incremental)s = FALSE THEN '-infinity' ELSE COALESCE(date_trunc('hour', MAX(date)) - interval '1 hour', '-infinity') END AS max_date FROM catmaid_stats_summary WHERE project_id=%(project_id)s AND n_connector_links > 0 ), connector_info AS ( SELECT t1.user_id, date_trunc('hour', t1.creation_time) AS date, count(*) AS n_connector_links FROM last_precomputation, treenode_connector t1 JOIN treenode_connector t2 ON t1.connector_id = t2.connector_id WHERE t1.project_id=%(project_id)s AND t1.creation_time >= last_precomputation.max_date AND t1.creation_time < date_trunc('hour', CURRENT_TIMESTAMP) AND t1.relation_id <> t2.relation_id AND (t1.relation_id = %(pre_id)s OR t1.relation_id = %(post_id)s) AND (t2.relation_id = %(pre_id)s OR t2.relation_id = %(post_id)s) AND t1.creation_time > t2.creation_time GROUP BY t1.user_id, date ) INSERT INTO catmaid_stats_summary (project_id, user_id, date, n_connector_links) SELECT %(project_id)s, ci.user_id, ci.date, ci.n_connector_links FROM connector_info ci ON CONFLICT (project_id, user_id, date) DO UPDATE SET n_connector_links = EXCLUDED.n_connector_links; """, dict(project_id=project_id, pre_id=pre_id, post_id=post_id, incremental=incremental))
def test_create_treenode(self): self.fake_authentication() relation_map = get_relation_to_id_map(self.test_project_id) class_map = get_class_to_id_map(self.test_project_id) count_treenodes = lambda: Treenode.objects.all().count() count_skeletons = lambda: ClassInstance.objects.filter( project=self.test_project_id, class_column=class_map['skeleton' ]).count() count_neurons = lambda: ClassInstance.objects.filter( project=self.test_project_id, class_column=class_map['neuron' ]).count() treenode_count = count_treenodes() skeleton_count = count_skeletons() neuron_count = count_neurons() response = self.client.post( '/%d/treenode/create' % self.test_project_id, { 'x': 5, 'y': 10, 'z': 15, 'confidence': 5, 'parent_id': -1, 'radius': 2 }) self.assertEqual(response.status_code, 200) parsed_response = json.loads(response.content.decode('utf-8')) self.assertTrue('treenode_id' in parsed_response) self.assertTrue('skeleton_id' in parsed_response) self.assertEqual(treenode_count + 1, count_treenodes()) self.assertEqual(skeleton_count + 1, count_skeletons()) self.assertEqual(neuron_count + 1, count_neurons()) treenode_skeleton_relation = TreenodeClassInstance.objects.filter( project=self.test_project_id, relation=relation_map['element_of'], treenode=parsed_response['treenode_id'], class_instance=parsed_response['skeleton_id']) neuron_skeleton_relation = ClassInstanceClassInstance.objects.filter( project=self.test_project_id, relation=relation_map['model_of'], class_instance_a=parsed_response['skeleton_id']) neuron_log = Log.objects.filter(project=self.test_project_id, operation_type='create_neuron') # FIXME: discussed in # https://github.com/catmaid/CATMAID/issues/754 #self.assertEqual(1, treenode_skeleton_relation.count()) self.assertEqual(1, neuron_skeleton_relation.count())
def test_create_treenode(self): self.fake_authentication() relation_map = get_relation_to_id_map(self.test_project_id) class_map = get_class_to_id_map(self.test_project_id) count_treenodes = lambda: Treenode.objects.all().count() count_skeletons = lambda: ClassInstance.objects.filter( project=self.test_project_id, class_column=class_map['skeleton']).count() count_neurons = lambda: ClassInstance.objects.filter( project=self.test_project_id, class_column=class_map['neuron']).count() treenode_count = count_treenodes() skeleton_count = count_skeletons() neuron_count = count_neurons() response = self.client.post('/%d/treenode/create' % self.test_project_id, { 'x': 5, 'y': 10, 'z': 15, 'confidence': 5, 'parent_id': -1, 'radius': 2}) self.assertEqual(response.status_code, 200) parsed_response = json.loads(response.content.decode('utf-8')) self.assertTrue('treenode_id' in parsed_response) self.assertTrue('skeleton_id' in parsed_response) self.assertEqual(treenode_count + 1, count_treenodes()) self.assertEqual(skeleton_count + 1, count_skeletons()) self.assertEqual(neuron_count + 1, count_neurons()) treenode_skeleton_relation = TreenodeClassInstance.objects.filter( project=self.test_project_id, relation=relation_map['element_of'], treenode=parsed_response['treenode_id'], class_instance=parsed_response['skeleton_id']) neuron_skeleton_relation = ClassInstanceClassInstance.objects.filter( project=self.test_project_id, relation=relation_map['model_of'], class_instance_a=parsed_response['skeleton_id']) neuron_log = Log.objects.filter( project=self.test_project_id, operation_type='create_neuron') # FIXME: discussed in # https://github.com/catmaid/CATMAID/issues/754 #self.assertEqual(1, treenode_skeleton_relation.count()) self.assertEqual(1, neuron_skeleton_relation.count())
def connector_types(request, project_id): """Get a list of available connector types. Returns a list of all available connector link types in a project. Each list element consists of an object with the following fields: type, relation, relation_id. """ relation_map = get_relation_to_id_map(project_id) types = copy.deepcopy(LINK_TYPES) for t in types: t['relation_id'] = relation_map[t['relation']] return JsonResponse(types, safe=False)
def connector_types(request, project_id): """Get a list of available connector types. Returns a list of all available connector link types in a project. Each list element consists of an object with the following fields: type, relation, relation_id. """ relation_map = get_relation_to_id_map(project_id) types = copy.deepcopy(LINK_TYPES) for t in types: t['relation_id'] = relation_map[t['relation']] return JsonResponse(types, safe=False)
def basic_graph(project_id, skeleton_ids, relations=None, source_link="presynaptic_to", target_link="postsynaptic_to"): if not skeleton_ids: raise ValueError("No skeleton IDs provided") cursor = connection.cursor() if not relations: relations = get_relation_to_id_map(project_id, (source_link, target_link), cursor) source_rel_id, target_rel_id = relations[source_link], relations[ target_link] cursor.execute( ''' SELECT t1.skeleton_id, t2.skeleton_id, LEAST(t1.confidence, t2.confidence) FROM treenode_connector t1, treenode_connector t2 WHERE t1.skeleton_id IN (%(skids)s) AND t1.relation_id = %(source_rel)s AND t1.connector_id = t2.connector_id AND t2.skeleton_id IN (%(skids)s) AND t2.relation_id = %(target_rel)s AND t1.id <> t2.id ''' % { 'skids': ','.join(map(str, skeleton_ids)), 'source_rel': source_rel_id, 'target_rel': target_rel_id }) edges = defaultdict(partial(defaultdict, make_new_synapse_count_array)) for row in cursor.fetchall(): edges[row[0]][row[1]][row[2] - 1] += 1 return { 'edges': tuple((s, t, count) for s, edge in six.iteritems(edges) for t, count in six.iteritems(edge)) } ''' return {'edges': [{'source': pre, 'target': post, 'weight': count} for pre, edge in six.iteritems(edges) for post, count in six.iteritems(edge)]} ''' """ Can't get the variable to be set with all the skeleton IDs
def setup_classification(workspace_pid, user): """ Tests which of the needed classes and relations is missing from the dummy project''s semantic space and adds those. """ # Get classification and relation data class_map = get_class_to_id_map(workspace_pid) relation_map = get_relation_to_id_map(workspace_pid) # Add what is missing for c in needed_classes: if c not in class_map: add_class(workspace_pid, user, c, needed_classes[c]) for r in needed_relations: if r not in relation_map: add_relation(workspace_pid, user, r, needed_relations[r])
def _list_completed(project_id, completed_by=None, from_date=None, to_date=None): """ Get a list of connector links that can be optionally constrained to be completed by a certain user in a given time frame. The returned connector links are by default only constrained by both sides having different relations and the first link was created before the second one. """ cursor = connection.cursor() relations = get_relation_to_id_map(project_id, ('presynaptic_to', 'postsynaptic_to'), cursor) pre = relations['presynaptic_to'] post = relations['postsynaptic_to'] params = [project_id, pre, post, pre, post] query = ''' SELECT tc2.connector_id, c.location_x, c.location_y, c.location_z, tc2.treenode_id, tc2.skeleton_id, tc2.confidence, tc2.user_id, t2.location_x, t2.location_y, t2.location_z, tc1.treenode_id, tc1.skeleton_id, tc1.confidence, tc1.user_id, t1.location_x, t1.location_y, t1.location_z FROM treenode_connector tc1 JOIN treenode_connector tc2 ON tc1.connector_id = tc2.connector_id JOIN connector c ON tc1.connector_id = c.id JOIN treenode t1 ON t1.id = tc1.treenode_id JOIN treenode t2 ON t2.id = tc2.treenode_id WHERE t1.project_id=%s AND tc1.relation_id <> tc2.relation_id AND tc1.creation_time > tc2.creation_time AND (tc1.relation_id = %s OR tc1.relation_id = %s) AND (tc2.relation_id = %s OR tc2.relation_id = %s)''' if completed_by: params.append(completed_by) query += " AND tc1.user_id=%s" if from_date: params.append(from_date.isoformat()) query += " AND tc1.creation_time >= %s" if to_date: to_date = to_date + timedelta(days=1) params.append(to_date.isoformat()) query += " AND tc1.creation_time < %s" cursor.execute(query, params) return tuple((row[0], (row[1], row[2], row[3]), row[4], row[5], row[6], row[7], (row[8], row[9], row[10]), row[11], row[12], row[13], row[14], (row[15], row[16], row[17])) for row in cursor.fetchall())
def test_create_treenode_with_existing_neuron(self): self.fake_authentication() relation_map = get_relation_to_id_map(self.test_project_id) class_map = get_class_to_id_map(self.test_project_id) neuron_id = 2389 count_skeletons = lambda: ClassInstance.objects.filter( project=self.test_project_id, class_column=class_map['skeleton' ]).count() count_treenodes = lambda: Treenode.objects.all().count() treenode_count = count_treenodes() skeleton_count = count_skeletons() response = self.client.post( '/%d/treenode/create' % self.test_project_id, { 'x': 5, 'y': 10, 'z': 15, 'confidence': 5, 'parent_id': -1, 'useneuron': neuron_id, 'radius': 2 }) self.assertEqual(response.status_code, 200) parsed_response = json.loads(response.content.decode('utf-8')) self.assertTrue('treenode_id' in parsed_response) self.assertTrue('skeleton_id' in parsed_response) self.assertEqual(treenode_count + 1, count_treenodes()) self.assertEqual(skeleton_count + 1, count_skeletons()) treenode_skeleton_relation = TreenodeClassInstance.objects.filter( project=self.test_project_id, relation=relation_map['element_of'], treenode=parsed_response['treenode_id'], class_instance=parsed_response['skeleton_id']) neuron_skeleton_relation = ClassInstanceClassInstance.objects.filter( project=self.test_project_id, relation=relation_map['model_of'], class_instance_a=parsed_response['skeleton_id'], class_instance_b=neuron_id) # FIXME: treenode_skeleton_relation.count() should be 1, but we # currently don't store these relations. # See: https://github.com/catmaid/CATMAID/issues/754 self.assertEqual(0, treenode_skeleton_relation.count()) self.assertEqual(1, neuron_skeleton_relation.count())
def _many_to_many_synapses(skids1, skids2, relation_name, project_id): """ Return all rows that connect skeletons of one set with another set with a specific relation. """ if relation_name not in LINK_RELATION_NAMES: raise Exception("Cannot accept a relation named '%s'" % relation_name) cursor = connection.cursor() relations = get_relation_to_id_map(project_id, cursor=cursor) relation_id = relations[relation_name] undirected_link_ids = [relations[l] for l in UNDIRECTED_LINK_TYPES] cursor.execute(''' SELECT tc1.connector_id, c.location_x, c.location_y, c.location_z, tc1.treenode_id, tc1.skeleton_id, tc1.confidence, tc1.user_id, t1.location_x, t1.location_y, t1.location_z, tc2.treenode_id, tc2.skeleton_id, tc2.confidence, tc2.user_id, t2.location_x, t2.location_y, t2.location_z FROM treenode_connector tc1, treenode_connector tc2, treenode t1, treenode t2, connector c WHERE tc1.skeleton_id = ANY(%(skeleton_ids_1)s::int[]) AND tc1.connector_id = c.id AND tc2.skeleton_id = ANY(%(skeleton_ids_2)s::int[]) AND tc1.connector_id = tc2.connector_id AND tc1.relation_id = %(relation_id)s AND (tc1.relation_id != tc2.relation_id OR tc1.relation_id = ANY(%(undir_rel_ids)s::int[])) AND tc1.id != tc2.id AND tc1.treenode_id = t1.id AND tc2.treenode_id = t2.id ''', { 'skeleton_ids_1': skids1, 'skeleton_ids_2': skids2, 'relation_id': relation_id, 'undir_rel_ids': undirected_link_ids, }) return tuple((row[0], (row[1], row[2], row[3]), row[4], row[5], row[6], row[7], (row[8], row[9], row[10]), row[11], row[12], row[13], row[14], (row[15], row[16], row[17])) for row in cursor.fetchall())
def _many_to_many_synapses(skids1, skids2, relation_name, project_id) -> Tuple: """ Return all rows that connect skeletons of one set with another set with a specific relation. """ if relation_name not in LINK_RELATION_NAMES: raise Exception("Cannot accept a relation named '%s'" % relation_name) cursor = connection.cursor() relations = get_relation_to_id_map(project_id, cursor=cursor) relation_id = relations[relation_name] undirected_link_ids = [relations[link_type] for link_type in UNDIRECTED_LINK_TYPES] cursor.execute(''' SELECT tc1.connector_id, c.location_x, c.location_y, c.location_z, tc1.treenode_id, tc1.skeleton_id, tc1.confidence, tc1.user_id, t1.location_x, t1.location_y, t1.location_z, tc2.treenode_id, tc2.skeleton_id, tc2.confidence, tc2.user_id, t2.location_x, t2.location_y, t2.location_z FROM treenode_connector tc1, treenode_connector tc2, treenode t1, treenode t2, connector c WHERE tc1.skeleton_id = ANY(%(skeleton_ids_1)s::bigint[]) AND tc1.connector_id = c.id AND tc2.skeleton_id = ANY(%(skeleton_ids_2)s::bigint[]) AND tc1.connector_id = tc2.connector_id AND tc1.relation_id = %(relation_id)s AND (tc1.relation_id != tc2.relation_id OR tc1.relation_id = ANY(%(undir_rel_ids)s::bigint[])) AND tc1.id != tc2.id AND tc1.treenode_id = t1.id AND tc2.treenode_id = t2.id ''', { 'skeleton_ids_1': skids1, 'skeleton_ids_2': skids2, 'relation_id': relation_id, 'undir_rel_ids': undirected_link_ids, }) return tuple((row[0], (row[1], row[2], row[3]), row[4], row[5], row[6], row[7], (row[8], row[9], row[10]), row[11], row[12], row[13], row[14], (row[15], row[16], row[17])) for row in cursor.fetchall())
def __init__(self, job): self.job = job # The name of entities that are exported self.entity_name = "treenode" # Output path for this job will be initialized, when needed self.output_path = None # Cache for neuron and relation folder names self.skid_to_neuron_folder = {} self.relid_to_rel_folder = {} # Get relation map self.relation_map = get_relation_to_id_map(job.project_id) # Store meta data for each node self.metadata = {}
def test_create_treenode_with_existing_neuron(self): self.fake_authentication() relation_map = get_relation_to_id_map(self.test_project_id) class_map = get_class_to_id_map(self.test_project_id) neuron_id = 2389 count_skeletons = lambda: ClassInstance.objects.filter( project=self.test_project_id, class_column=class_map['skeleton']).count() count_treenodes = lambda: Treenode.objects.all().count() treenode_count = count_treenodes() skeleton_count = count_skeletons() response = self.client.post('/%d/treenode/create' % self.test_project_id, { 'x': 5, 'y': 10, 'z': 15, 'confidence': 5, 'parent_id': -1, 'useneuron': neuron_id, 'radius': 2}) self.assertEqual(response.status_code, 200) parsed_response = json.loads(response.content.decode('utf-8')) self.assertTrue('treenode_id' in parsed_response) self.assertTrue('skeleton_id' in parsed_response) self.assertEqual(treenode_count + 1, count_treenodes()) self.assertEqual(skeleton_count + 1, count_skeletons()) treenode_skeleton_relation = TreenodeClassInstance.objects.filter( project=self.test_project_id, relation=relation_map['element_of'], treenode=parsed_response['treenode_id'], class_instance=parsed_response['skeleton_id']) neuron_skeleton_relation = ClassInstanceClassInstance.objects.filter( project=self.test_project_id, relation=relation_map['model_of'], class_instance_a=parsed_response['skeleton_id'], class_instance_b=neuron_id) # FIXME: treenode_skeleton_relation.count() should be 1, but we # currently don't store these relations. # See: https://github.com/catmaid/CATMAID/issues/754 self.assertEqual(0, treenode_skeleton_relation.count()) self.assertEqual(1, neuron_skeleton_relation.count())
def __init__(self, job): self.job = job # The name of entities that are exported self.entity_name = "treenode" # Output path for this job will be initialized, when needed self.output_path = None # Cache for neuron and relation folder names self.skid_to_neuron_folder = {} self.relid_to_rel_folder = {} # Get relation map self.relation_map = get_relation_to_id_map(job.project_id) # Store meta data for each node self.metadata = {}
def populate_connector_stats_summary(project_id, incremental=True, cursor=None): """Add connector summary information to the summary table. Create hourly aggregates in UTC time. These aggregates can still be moved in other timezones with good enough precision for our purpose. By default, this happens in an incremental manner, but can optionally be fone for all data from scratch (overriding existing statistics). """ if not cursor: cursor = connection.cursor() relations = get_relation_to_id_map(project_id, cursor=cursor) pre_id, post_id = relations.get('presynaptic_to'), relations.get('postsynaptic_to') if pre_id and post_id: cursor.execute(""" WITH last_precomputation AS ( SELECT CASE WHEN %(incremental)s = FALSE THEN '-infinity' ELSE COALESCE(date_trunc('hour', MAX(date)) - interval '1 hour', '-infinity') END AS max_date FROM catmaid_stats_summary WHERE project_id=%(project_id)s AND n_connector_links > 0 ), connector_info AS ( SELECT t1.user_id, date_trunc('hour', t1.creation_time) AS date, count(*) AS n_connector_links FROM last_precomputation, treenode_connector t1 JOIN treenode_connector t2 ON t1.connector_id = t2.connector_id WHERE t1.project_id=%(project_id)s AND t1.creation_time >= last_precomputation.max_date AND t1.creation_time < date_trunc('hour', CURRENT_TIMESTAMP) AND t1.relation_id <> t2.relation_id AND (t1.relation_id = %(pre_id)s OR t1.relation_id = %(post_id)s) AND (t2.relation_id = %(pre_id)s OR t2.relation_id = %(post_id)s) AND t1.creation_time > t2.creation_time GROUP BY t1.user_id, date ) INSERT INTO catmaid_stats_summary (project_id, user_id, date, n_connector_links) SELECT %(project_id)s, ci.user_id, ci.date, ci.n_connector_links FROM connector_info ci ON CONFLICT (project_id, user_id, date) DO UPDATE SET n_connector_links = EXCLUDED.n_connector_links; """, dict(project_id=project_id, pre_id=pre_id, post_id=post_id, incremental=incremental))
def _many_to_many_synapses(skids1, skids2, relation_name, project_id): """ Return all rows that connect skeletons of one set with another set with a specific relation. """ if relation_name not in ('postsynaptic_to', 'presynaptic_to', 'gapjunction_with'): raise Exception("Cannot accept a relation named '%s'" % relation_name) cursor = connection.cursor() relations = get_relation_to_id_map(project_id, cursor=cursor) gapjunction_id = relations.get('gapjunction_with', -1) cursor.execute(''' SELECT tc1.connector_id, c.location_x, c.location_y, c.location_z, tc1.treenode_id, tc1.skeleton_id, tc1.confidence, tc1.user_id, t1.location_x, t1.location_y, t1.location_z, tc2.treenode_id, tc2.skeleton_id, tc2.confidence, tc2.user_id, t2.location_x, t2.location_y, t2.location_z FROM treenode_connector tc1, treenode_connector tc2, treenode t1, treenode t2, relation r1, connector c WHERE tc1.skeleton_id IN (%s) AND tc1.connector_id = c.id AND tc2.skeleton_id IN (%s) AND tc1.connector_id = tc2.connector_id AND tc1.relation_id = r1.id AND r1.relation_name = '%s' AND (tc1.relation_id != tc2.relation_id OR tc1.relation_id = %d) AND tc1.id != tc2.id AND tc1.treenode_id = t1.id AND tc2.treenode_id = t2.id ''' % (','.join(map(str, skids1)), ','.join(map(str, skids2)), relation_name, gapjunction_id)) return tuple((row[0], (row[1], row[2], row[3]), row[4], row[5], row[6], row[7], (row[8], row[9], row[10]), row[11], row[12], row[13], row[14], (row[15], row[16], row[17])) for row in cursor.fetchall())
def check_classification_setup(workspace_pid): """ Checks if all classes and relations needed by the classification system are available. Needed classes are 'classification_root' and 'classification_project' and the nedded relations are 'is_a' and 'classified_by'. """ # Get classification and relation data class_map = get_class_to_id_map(workspace_pid) relation_map = get_relation_to_id_map(workspace_pid) # Check if all is good all_good = True for c in needed_classes: all_good = (all_good and (c in class_map)) for r in needed_relations: all_good = (all_good and (r in relation_map)) return all_good
def synapseNodesFromSkeletonID(sid): sk = ClassInstance.objects.get(pk=sid) pid = sk.project_id relations = get_relation_to_id_map(pid, ("presynaptic_to", "postsynaptic_to")) qs_tc = TreenodeConnector.objects.filter( project=pid, skeleton=sid, relation__in=(relations["presynaptic_to"], relations["postsynaptic_to"]) ).select_related("connector") synapse_nodes = [] connector_ids = [] synapse_relations = [] for tc in qs_tc: synapse_nodes.append(tc.treenode_id) connector_ids.append(tc.connector_id) synapse_relations.append(tc.relation_id) return synapse_nodes, connector_ids, synapse_relations
def _connector_skeletons(connector_ids, project_id): """Return a dictionary of connector ID as keys and a dictionary as value containing two entries: 'presynaptic_to' with a skeleton ID or None, and 'postsynaptic_to' with a list of skeleton IDs (maybe empty). """ if not connector_ids: raise ValueError('No connector IDs provided') cursor = connection.cursor() relations = get_relation_to_id_map(project_id, ('presynaptic_to', 'postsynaptic_to'), cursor) PRE = relations['presynaptic_to'] POST = relations['postsynaptic_to'] cursor.execute(''' SELECT connector_id, relation_id, skeleton_id, treenode_id FROM treenode_connector WHERE connector_id IN (%s) AND (relation_id = %s OR relation_id = %s) ''' % (",".join(map(str, connector_ids)), PRE, POST)) cs = {} for row in cursor.fetchall(): c = cs.get(row[0]) if not c: # Ensure each connector has the two entries at their minimum c = { 'presynaptic_to': None, 'postsynaptic_to': [], 'presynaptic_to_node': None, 'postsynaptic_to_node': [] } cs[row[0]] = c if POST == row[1]: c['postsynaptic_to'].append(row[2]) c['postsynaptic_to_node'].append(row[3]) elif PRE == row[1]: c['presynaptic_to'] = row[2] c['presynaptic_to_node'] = row[3] return cs
def list_annotations(request, project_id=None): """ Creates a list of objects containing an annotation name and the user name and ID of the users having linked that particular annotation. """ if not request.POST: cursor = connection.cursor() classes = get_class_to_id_map(project_id, ("annotation",), cursor) relations = get_relation_to_id_map(project_id, ("annotated_with",), cursor) cursor.execute( """ SELECT DISTINCT ci.name, ci.id, u.id, u.username FROM class_instance ci LEFT OUTER JOIN class_instance_class_instance cici ON (ci.id = cici.class_instance_b) LEFT OUTER JOIN auth_user u ON (cici.user_id = u.id) WHERE (ci.class_id = %s AND cici.relation_id = %s AND ci.project_id = %s AND cici.project_id = %s); """, (classes["annotation"], relations["annotated_with"], project_id, project_id), ) annotation_tuples = cursor.fetchall() else: annotation_query = create_annotation_query(project_id, request.POST) annotation_tuples = annotation_query.distinct().values_list( "name", "id", "cici_via_b__user__id", "cici_via_b__user__username" ) # Create a set mapping annotation names to its users ids = {} annotation_dict = {} for annotation, aid, uid, username in annotation_tuples: ids[aid] = annotation ls = annotation_dict.get(aid) if ls is None: ls = [] annotation_dict[aid] = ls ls.append({"id": uid, "name": username}) # Flatten dictionary to list annotations = tuple({"name": ids[aid], "id": aid, "users": users} for aid, users in annotation_dict.iteritems()) return HttpResponse(json.dumps({"annotations": annotations}), content_type="text/json")
def basic_graph(project_id, skeleton_ids, relations=None, source_link="presynaptic_to", target_link="postsynaptic_to"): if not skeleton_ids: raise ValueError("No skeleton IDs provided") cursor = connection.cursor() if not relations: relations = get_relation_to_id_map(project_id, (source_link, target_link), cursor) source_rel_id, target_rel_id = relations[source_link], relations[target_link] cursor.execute(''' SELECT t1.skeleton_id, t2.skeleton_id, LEAST(t1.confidence, t2.confidence) FROM treenode_connector t1, treenode_connector t2 WHERE t1.skeleton_id IN (%(skids)s) AND t1.relation_id = %(source_rel)s AND t1.connector_id = t2.connector_id AND t2.skeleton_id IN (%(skids)s) AND t2.relation_id = %(target_rel)s AND t1.id <> t2.id ''' % {'skids': ','.join(map(str, skeleton_ids)), 'source_rel': source_rel_id, 'target_rel': target_rel_id}) edges = defaultdict(partial(defaultdict, make_new_synapse_count_array)) for row in cursor.fetchall(): edges[row[0]][row[1]][row[2] - 1] += 1 return { 'edges': tuple((s, t, count) for s, edge in edges.items() for t, count in edge.items()) } ''' return {'edges': [{'source': pre, 'target': post, 'weight': count} for pre, edge in edges.items() for post, count in edge.items()]} ''' """ Can't get the variable to be set with all the skeleton IDs
def _many_to_many_synapses(skids1, skids2, relation_name, project_id): """ Return all rows that connect skeletons of one set with another set with a specific relation. """ if relation_name not in ('postsynaptic_to', 'presynaptic_to', 'gapjunction_with'): raise Exception("Cannot accept a relation named '%s'" % relation_name) cursor = connection.cursor() relations = get_relation_to_id_map(project_id, cursor=cursor) gapjunction_id = relations.get('gapjunction_with', -1) cursor.execute(''' SELECT tc1.connector_id, c.location_x, c.location_y, c.location_z, tc1.treenode_id, tc1.skeleton_id, tc1.confidence, tc1.user_id, t1.location_x, t1.location_y, t1.location_z, tc2.treenode_id, tc2.skeleton_id, tc2.confidence, tc2.user_id, t2.location_x, t2.location_y, t2.location_z FROM treenode_connector tc1, treenode_connector tc2, treenode t1, treenode t2, relation r1, connector c WHERE tc1.skeleton_id IN (%s) AND tc1.connector_id = c.id AND tc2.skeleton_id IN (%s) AND tc1.connector_id = tc2.connector_id AND tc1.relation_id = r1.id AND r1.relation_name = '%s' AND (tc1.relation_id != tc2.relation_id OR tc1.relation_id = %d) AND tc1.id != tc2.id AND tc1.treenode_id = t1.id AND tc2.treenode_id = t2.id ''' % (','.join(map(str, skids1)), ','.join(map( str, skids2)), relation_name, gapjunction_id)) return tuple((row[0], (row[1], row[2], row[3]), row[4], row[5], row[6], row[7], (row[8], row[9], row[10]), row[11], row[12], row[13], row[14], (row[15], row[16], row[17])) for row in cursor.fetchall())
def basic_graph(project_id, skeleton_ids): if not skeleton_ids: raise ValueError("No skeleton IDs provided") cursor = connection.cursor() relations = get_relation_to_id_map(project_id, ("presynaptic_to", "postsynaptic_to"), cursor) preID, postID = relations["presynaptic_to"], relations["postsynaptic_to"] cursor.execute( """ SELECT tc.connector_id, tc.relation_id, tc.skeleton_id FROM treenode_connector tc WHERE tc.project_id = %s AND tc.skeleton_id IN (%s) AND (tc.relation_id = %s OR tc.relation_id = %s) """ % (int(project_id), ",".join(str(int(skid)) for skid in skeleton_ids), preID, postID) ) # stores entire query set in memory, linking pre and post connectors = defaultdict(partial(defaultdict, list)) for row in cursor.fetchall(): connectors[row[0]][row[1]].append(row[2]) # Safe to placeholder, half-complete connectors: # only adds the edge if both pre and post exist edges = defaultdict(partial(defaultdict, int)) for c in connectors.itervalues(): for pre in c[preID]: # should be one or none for post in c[postID]: edges[pre][post] += 1 return {"edges": tuple((pre, post, count) for pre, edge in edges.iteritems() for post, count in edge.iteritems())} """ return {'edges': [{'source': pre, 'target': post, 'weight': count} for pre, edge in edges.iteritems() for post, count in edge.iteritems()]} """ """ Can't get the variable to be set with all the skeleton IDs
def check_tracing_setup_detailed( project_id, opt_class_map=None, opt_relation_map=None, check_root_ci=True) -> Tuple[bool, List, List, List]: """ Checks if all classes and relations needed by the tracing system are available. It returns a four-tuple with a boolean indicating if all is setup, the missing class names, the missing relation names and the missing class instance names. Allows avoidng tests for root class instances and passing already available class and relation maps. """ # Get class and relation data. If available, use the provided one. class_map = opt_class_map or get_class_to_id_map(project_id) relation_map = opt_relation_map or get_relation_to_id_map(project_id) # Check if all classes and relations are available all_good = True missing_classes = [] missing_relations = [] missing_classinstances = [] for c in needed_classes: if c not in class_map: all_good = False missing_classes.append(c) for r in needed_relations: if r not in relation_map: all_good = False missing_relations.append(r) # Check if the root node is there if requested if check_root_ci: if 'root' in class_map: exists = ClassInstance.objects.filter( class_column=class_map['root'], project_id=project_id).exists() if not exists: all_good = False missing_classinstances.append('root') else: missing_classinstances.append('root') return all_good, missing_classes, missing_relations, missing_classinstances
def skeletons_neuroml(request, project_id=None): """ Export a list of skeletons each as a Cell in NeuroML. """ project_id = int(project_id) # sanitize skeleton_ids = tuple(int(v) for k,v in request.POST.iteritems() if k.startswith('skids[')) cursor = connection.cursor() relations = get_relation_to_id_map(project_id, ('presynaptic_to', 'postsynaptic_to'), cursor) preID = relations['presynaptic_to'] postID = relations['postsynaptic_to'] # TODO could certainly fetch all nodes and synapses in one single query and then split them up. cells = (_skeleton_neuroml_cell(skeleton_id, preID, postID) for skeleton_id in skeleton_ids) response = HttpResponse(content_type='text/txt') response['Content-Disposition'] = 'attachment; filename="data.neuroml"' neuroml_network(cells, response) return response
def skeletons_neuroml(request, project_id=None): """ Export a list of skeletons each as a Cell in NeuroML. """ project_id = int(project_id) # sanitize skeleton_ids = tuple(int(v) for k,v in request.POST.iteritems() if k.startswith('skids[')) cursor = connection.cursor() relations = get_relation_to_id_map(project_id, ('presynaptic_to', 'postsynaptic_to'), cursor) preID = relations['presynaptic_to'] postID = relations['postsynaptic_to'] # TODO could certainly fetch all nodes and synapses in one single query and then split them up. cells = (_skeleton_neuroml_cell(skeleton_id, preID, postID) for skeleton_id in skeleton_ids) response = HttpResponse(content_type='text/txt') response['Content-Disposition'] = 'attachment; filename="data.neuroml"' neuroml_network(cells, response) return response
def connector_user_info(request, project_id): """ Return information on a treenode connector edge. This function is called often (every connector mouseover) and should therefore be as fast as possible. Analogous to user_info for treenodes and connectors. """ treenode_id = int(request.GET.get('treenode_id')) connector_id = int(request.GET.get('connector_id')) cursor = connection.cursor() relation_names = ('presynaptic_to', 'postsynaptic_to', 'abutting', 'gapjunction_with') relations = get_relation_to_id_map(project_id, relation_names, cursor) relation_id = relations[request.GET.get('relation_name')] cursor.execute( ''' SELECT tc.id, tc.user_id, tc.creation_time, tc.edition_time FROM treenode_connector tc WHERE tc.treenode_id = %s AND tc.connector_id = %s AND tc.relation_id = %s ''', (treenode_id, connector_id, relation_id)) # We expect at least one result node. if not cursor.rowcount: return HttpResponse( json.dumps({ 'error': 'No treenode connector exists for treenode %s, connector %s, relation %s' % (treenode_id, connector_id, relation_id) })) # Build result. Because there is no uniqueness restriction on treenode # connector edges, even with the same relation, the response must handle # multiple rows. return HttpResponse( json.dumps([{ 'user': info[1], 'creation_time': str(info[2].isoformat()), 'edition_time': str(info[3].isoformat()), } for info in cursor.fetchall()]))
def basic_graph(project_id, skeleton_ids): def newSynapseCounts(): return [0, 0, 0, 0, 0] if not skeleton_ids: raise ValueError("No skeleton IDs provided") cursor = connection.cursor() relations = get_relation_to_id_map(project_id, ('presynaptic_to', 'postsynaptic_to'), cursor) preID, postID = relations['presynaptic_to'], relations['postsynaptic_to'] cursor.execute(''' SELECT t1.skeleton_id, t2.skeleton_id, LEAST(t1.confidence, t2.confidence) FROM treenode_connector t1, treenode_connector t2 WHERE t1.skeleton_id IN (%s) AND t1.relation_id = %s AND t1.connector_id = t2.connector_id AND t2.relation_id = %s ''' % (','.join(map(str, skeleton_ids)), preID, postID)) edges = defaultdict(partial(defaultdict, newSynapseCounts)) for row in cursor.fetchall(): edges[row[0]][row[1]][row[2] - 1] += 1 return { 'edges': tuple((pre, post, count) for pre, edge in edges.iteritems() for post, count in edge.iteritems()) } ''' return {'edges': [{'source': pre, 'target': post, 'weight': count} for pre, edge in edges.iteritems() for post, count in edge.iteritems()]} ''' """ Can't get the variable to be set with all the skeleton IDs
def connector_types(request, project_id): """Get a list of available connector types. Returns a list of all available connector link types in a project. Each list element consists of an object with the following fields: type, relation, relation_id. """ relation_map = get_relation_to_id_map(project_id) def set_id(t): relation_id = relation_map.get(t['relation']) # If the relation doesn't exist in the database, don't return it. Add it # to the log though: if relation_id is None: logger.info("Tracing relation {} not found in database".format(t['relation'])) return False else: t['relation_id'] = relation_id return True types = list(filter(set_id, copy.deepcopy(LINK_TYPES))) return JsonResponse(types, safe=False)
def synapseNodesFromSkeletonID(sid) -> Tuple[List, List, List]: sk = ClassInstance.objects.get(pk=sid) pid = sk.project_id relations = get_relation_to_id_map(pid, ('presynaptic_to', 'postsynaptic_to')) qs_tc = TreenodeConnector.objects.filter( project=pid, skeleton=sid, relation__in=( relations['presynaptic_to'], relations['postsynaptic_to'])).select_related('connector') synapse_nodes = [] connector_ids = [] synapse_relations = [] for tc in qs_tc: synapse_nodes.append(tc.treenode_id) connector_ids.append(tc.connector_id) synapse_relations.append(tc.relation_id) return synapse_nodes, connector_ids, synapse_relations
def check_tracing_setup_detailed(project_id, opt_class_map=None, opt_relation_map=None, check_root_ci=True): """ Checks if all classes and relations needed by the tracing system are available. It returns a four-tuple with a boolean indicating if all is setup, the missing class names, the missing relation names and the missing class instance names. Allows to avoid test for root class instances and to pass already available class and relation maps. """ # Get class and relation data. If available, use the provided one. class_map = opt_class_map or get_class_to_id_map(project_id) relation_map = opt_relation_map or get_relation_to_id_map(project_id) # Check if all classes and relations are available all_good = True missing_classes = [] missing_relations = [] missing_classinstances = [] for c in needed_classes: if not c in class_map: all_good = False missing_classes.append(c) for r in needed_relations: if not r in relation_map: all_good = False missing_relations.append(r) # Check if the root node is there if requested if check_root_ci: if 'root' in class_map: exists = ClassInstance.objects.filter( class_column=class_map['root'], project_id=project_id).exists() if not exists: all_good = False missing_classinstances.append('root') else: missing_classinstances.append('root') return all_good, missing_classes, missing_relations, missing_classinstances
def connector_types(request:HttpRequest, project_id) -> JsonResponse: """Get a list of available connector types. Returns a list of all available connector link types in a project. Each list element consists of an object with the following fields: type, relation, relation_id. """ relation_map = get_relation_to_id_map(project_id) def set_id(t) -> bool: relation_id = relation_map.get(t['relation']) # If the relation doesn't exist in the database, don't return it. Add it # to the log though: if relation_id is None: logger.info(f"Tracing relation {t['relation']} not found in database") return False else: t['relation_id'] = relation_id return True types = list(filter(set_id, copy.deepcopy(LINK_TYPES))) return JsonResponse(types, safe=False)
def _connector_skeletons(connector_ids, project_id): """Return a dictionary of connector ID as keys and a dictionary as value containing two entries: 'presynaptic_to' with a skeleton ID or None, and 'postsynaptic_to' with a list of skeleton IDs (maybe empty). """ if not connector_ids: raise ValueError('No connector IDs provided') cursor = connection.cursor() relations = get_relation_to_id_map(project_id, ('presynaptic_to', 'postsynaptic_to'), cursor) PRE = relations['presynaptic_to'] POST = relations['postsynaptic_to'] cursor.execute(''' SELECT connector_id, relation_id, skeleton_id, treenode_id FROM treenode_connector WHERE connector_id IN (%s) AND (relation_id = %s OR relation_id = %s) ''' % (",".join(map(str, connector_ids)), PRE, POST)) cs = {} for row in cursor.fetchall(): c = cs.get(row[0]) if not c: # Ensure each connector has the two entries at their minimum c = {'presynaptic_to': None, 'postsynaptic_to': [], 'presynaptic_to_node': None, 'postsynaptic_to_node': []} cs[row[0]] = c if POST == row[1]: c['postsynaptic_to'].append(row[2]) c['postsynaptic_to_node'].append(row[3]) elif PRE == row[1]: c['presynaptic_to'] = row[2] c['presynaptic_to_node'] = row[3] return cs
def list_ontology(request, project_id=None): root_class = request.GET.get('rootclass', None) parent_id = int(request.GET.get('parentid', 0)) expand_request = request.GET.get('expandtarget', None) parent_type = request.GET.get('parenttype', "relation") class_b_id = int(request.GET.get('classbid', 0)) if expand_request is None: expand_request = tuple() else: # Parse to int to sanitize expand_request = tuple(int(x) for x in expand_request.split(',')) relation_map = get_relation_to_id_map(project_id) class_map = get_class_to_id_map(project_id) response_on_error = '' try: if parent_type == "relation": # A class is wanted if 0 == parent_id: response_on_error = 'Could not select the id of any ontology root node' # If the no root class is explicitely requested, return all known # root classes. root_class_ids = [] if root_class is None: for rc in root_classes: if rc in class_map: root_class_ids.append( class_map[rc] ) if len(root_class_ids) == 0: warning = {'warning': 'Could not find any of the known root classes. ' \ 'Please add at least one of them to build an ontology.'} return JsonResponse(warning) else: if root_class not in class_map: raise Exception('Root class "{0}" not found'.format( root_class )) root_class_ids = [ class_map[root_class] ] root_node_q = Class.objects.filter(id__in=root_class_ids, project=project_id) # Make sure we actually got at least one root node if 0 == len(root_node_q): raise Exception("Couldn't select any root node") roots = [] for root_node in root_node_q: root_id = root_node.id root_name = root_node.class_name num_children = ClassClass.objects.filter( class_b=root_id, project=project_id).count() data = { 'id': root_id, 'text': '%s (%d)' % (root_name, root_id), 'type': 'root', 'cname': root_name } # Test if there are links present and mark the root # as leaf if there are none. if num_children > 0: data['state'] = { 'opened': False } # Add this root node to the output list roots.append(data) return JsonResponse(tuple(r for r in roots), safe=False) else: response_on_error = 'Could not retrieve child nodes.' # Select all classes that are linked with the passed relation cc_q = ClassClass.objects.filter(class_b=class_b_id, relation=parent_id, project=project_id) links = [] for cc in cc_q: # Get known restrictions restrictions = get_restrictions( cc ) restrictions_json = json.dumps( restrictions ) # Create name, mark restrictin availability with * node_name = "%s (%d)" % (cc.class_a.class_name, cc.class_a.id) if len(restrictions) > 0: node_name = node_name + "*" # Collect standard jSTree data data = { 'id': cc.class_a.id, 'text': node_name, 'type': 'class', 'restrictions': restrictions_json, 'cname': cc.class_a.class_name, 'ccid': cc.id } # Only add a 'state' field if this node has children # (i.e. relations where it is class_b). num_children = ClassClass.objects.filter( class_b=cc.class_a.id, project=project_id).count() if num_children > 0: data['state'] = { 'opened': False } # Add this class-class link to the list links.append(data) return JsonResponse(tuple(l for l in links), safe=False) elif parent_type in ("class", "root"): # A relation is wanted cc_q = ClassClass.objects.filter( project=project_id, class_b_id=parent_id) # Combine same relations into one relations = {} for cc in cc_q: if cc.relation not in relations: relations[ cc.relation ] = [] relations[ cc.relation ].append( cc ) return JsonResponse(tuple({ 'id': r.id, 'text': '%s (%d)' % (r.relation_name, r.id), 'type': 'relation', 'name': r.relation_name, 'classbname': relations[r][0].class_b.class_name, 'classbid': parent_id } for r in relations), safe=False) else: response_on_error = 'Unknown parent type' raise Exception(parent_type) except Exception as e: raise Exception(response_on_error + ': ' + str(e))
def get_available_relations(request, project_id=None): """ Returns a simple list of all relations available available for the given project.""" relation_map = get_relation_to_id_map(project_id) return JsonResponse(relation_map)
def stats_user_history(request, project_id=None): # Get the start date for the query, defaulting to 10 days ago. start_date = request.GET.get('start_date', None) if start_date: start_date = dateparser.parse(start_date) print(start_date) else: start_date = datetime.now() - timedelta(10) # Get the end date for the query, defaulting to now. end_date = request.GET.get('end_date', None) if end_date: # We need to set the end date to the last second of the day to get all # events. end_date = dateparser.parse(end_date) + timedelta(days=1) - timedelta(seconds=1) else: end_date = datetime.now() # Calculate number of days between (including) start and end daydelta = (end_date + timedelta(days=1) - start_date).days all_users = User.objects.filter().values_list('id', flat=True) days = [] daysformatted = [] for i in range(daydelta): tmp_date = start_date + timedelta(days=i) days.append(tmp_date.strftime("%Y%m%d")) daysformatted.append(tmp_date.strftime("%a %d, %h %Y")) stats_table = {} for userid in all_users: if userid == -1: continue userid = str(userid) stats_table[userid] = {} for i in range(daydelta): date = (start_date + timedelta(days=i)).strftime("%Y%m%d") stats_table[userid][date] = {} # Look up all tree nodes for the project in the given date range. Also add # a computed field which is just the day of the last edited date/time. treenode_stats = [] cursor = connection.cursor() cursor.execute(''' SELECT child.uid, child.day, round(sum(edge.length)) FROM ( SELECT child.user_id AS uid, date_trunc('day', child.creation_time) AS day, child.parent_id, child.location_x, child.location_y, child.location_z FROM treenode child WHERE child.project_id = %(project_id)s AND child.creation_time BETWEEN %(start_date)s AND %(end_date)s ) AS child INNER JOIN LATERAL ( SELECT sqrt(pow(child.location_x - parent.location_x, 2) + pow(child.location_y - parent.location_y, 2) + pow(child.location_z - parent.location_z, 2)) AS length FROM treenode parent WHERE parent.project_id = %(project_id)s AND parent.id = child.parent_id LIMIT 1 ) AS edge ON TRUE GROUP BY child.uid, child.day ''', dict(project_id=project_id, start_date=start_date, end_date=end_date)) treenode_stats = cursor.fetchall() relations = get_relation_to_id_map(project_id, cursor=cursor) preId, postId = relations['presynaptic_to'], relations['postsynaptic_to'] # Retrieve a list of how many completed connector relations a user has # created in a given time frame. A completed connector relation is either # one were a user created both the presynaptic and the postsynaptic side # (one of them in the given time frame) or if a user completes an existing # 'half connection'. To avoid duplicates, only links are counted, where the # second node is younger than the first one cursor.execute(''' SELECT t1.user_id, (date_trunc('day', t1.creation_time)) AS date, count(*) FROM treenode_connector t1 JOIN treenode_connector t2 ON t1.connector_id = t2.connector_id WHERE t1.project_id=%s AND t1.creation_time BETWEEN %s AND %s AND t1.relation_id <> t2.relation_id AND (t1.relation_id = %s OR t1.relation_id = %s) AND (t2.relation_id = %s OR t2.relation_id = %s) AND t1.creation_time > t2.creation_time GROUP BY t1.user_id, date ''', (project_id, start_date, end_date, preId, postId, preId, postId)) connector_stats = cursor.fetchall() tree_reviewed_nodes = Review.objects \ .filter( project_id=project_id, review_time__range=(start_date, end_date)) \ .extra(select={'date': "date_trunc('day', review_time)"}) \ .order_by('date') \ .values_list('reviewer_id', 'date') \ .annotate(count = Count('treenode')) for di in treenode_stats: user_id = str(di[0]) date = di[1].strftime('%Y%m%d') stats_table[user_id][date]['new_treenodes'] = di[2] for di in connector_stats: user_id = str(di[0]) date = di[1].strftime('%Y%m%d') stats_table[user_id][date]['new_connectors'] = di[2] for di in tree_reviewed_nodes: user_id = str(di[0]) date = di[1].strftime('%Y%m%d') stats_table[user_id][date]['new_reviewed_nodes'] = di[2] return HttpResponse(json.dumps({ 'stats_table': stats_table, 'days': days, 'daysformatted': daysformatted}), content_type='text/json')
def list_annotations(request, project_id=None): """List annotations matching filtering criteria that are currently in use. The result set is the intersection of annotations matching criteria (the criteria are conjunctive) unless stated otherwise. --- parameters: - name: annotations description: A list of (meta) annotations with which which resulting annotations should be annotated with. paramType: form type: array items: type: integer description: An annotation ID - name: annotates description: A list of entity IDs (like annotations and neurons) that should be annotated by the result set. paramType: form type: array items: type: integer description: An entity ID - name: parallel_annotations description: A list of annotation that have to be used alongside the result set. paramType: form type: array items: type: integer description: An annotation ID - name: user_id description: Result annotations have to be used by this user. paramType: form type: integer - name: neuron_id description: Result annotations will annotate this neuron. paramType: form type: integer - name: skeleton_id description: Result annotations will annotate the neuron modeled by this skeleton. paramType: form type: integer - name: ignored_annotations description: A list of annotation names that will be excluded from the result set. paramType: form type: array items: type: string models: annotation_user_list_element: id: annotation_user_list_element properties: id: type: integer name: id description: The user id required: true name: type: string name: name description: The user name required: true annotation_list_element: id: annotation_list_element description: Represents one annotation along with its users. properties: name: type: string description: The name of the annotation required: true id: type: integer description: The id of the annotation required: true users: type: array description: A list of users required: true items: $ref: annotation_user_list_element type: - type: array items: $ref: annotation_list_element required: true """ if not request.POST: cursor = connection.cursor() classes = get_class_to_id_map(project_id, ('annotation', ), cursor) relations = get_relation_to_id_map(project_id, ('annotated_with', ), cursor) cursor.execute( ''' SELECT DISTINCT ci.name, ci.id, u.id, u.username FROM class_instance ci LEFT OUTER JOIN class_instance_class_instance cici ON (ci.id = cici.class_instance_b) LEFT OUTER JOIN auth_user u ON (cici.user_id = u.id) WHERE (ci.class_id = %s AND (cici.relation_id = %s OR cici.id IS NULL)); ''', (classes['annotation'], relations['annotated_with'])) annotation_tuples = cursor.fetchall() else: annotation_query = create_annotation_query(project_id, request.POST) annotation_tuples = annotation_query.distinct().values_list( 'name', 'id', 'cici_via_b__user__id', 'cici_via_b__user__username') # Create a set mapping annotation names to its users ids = {} annotation_dict = {} for annotation, aid, uid, username in annotation_tuples: ids[aid] = annotation ls = annotation_dict.get(aid) if ls is None: ls = [] annotation_dict[aid] = ls if uid is not None: ls.append({'id': uid, 'name': username}) # Flatten dictionary to list annotations = tuple({ 'name': ids[aid], 'id': aid, 'users': users } for aid, users in annotation_dict.iteritems()) return JsonResponse({'annotations': annotations})
def node_nearest(request, project_id=None): params = {} param_float_defaults = { 'x': 0, 'y': 0, 'z': 0} param_int_defaults = { 'skeleton_id': -1, 'neuron_id': -1} for p in param_float_defaults.keys(): params[p] = float(request.POST.get(p, param_float_defaults[p])) for p in param_int_defaults.keys(): params[p] = int(request.POST.get(p, param_int_defaults[p])) relation_map = get_relation_to_id_map(project_id) if params['skeleton_id'] < 0 and params['neuron_id'] < 0: raise Exception('You must specify either a skeleton or a neuron') for rel in ['part_of', 'model_of']: if rel not in relation_map: raise Exception('Could not find required relation %s for project %s.' % (rel, project_id)) skeletons = [] if params['skeleton_id'] > 0: skeletons.append(params['skeleton_id']) response_on_error = '' try: if params['neuron_id'] > 0: # Add skeletons related to specified neuron # Assumes that a cici 'model_of' relationship always involves a # skeleton as ci_a and a neuron as ci_b. response_on_error = 'Finding the skeletons failed.' neuron_skeletons = ClassInstanceClassInstance.objects.filter( class_instance_b=params['neuron_id'], relation=relation_map['model_of']) for neur_skel_relation in neuron_skeletons: skeletons.append(neur_skel_relation.class_instance_a_id) # Get all treenodes connected to skeletons response_on_error = 'Finding the treenodes failed.' treenodes = Treenode.objects.filter(project=project_id, skeleton__in=skeletons) def getNearestTreenode(x, y, z, treenodes): minDistance = -1 nearestTreenode = None for tn in treenodes: xdiff = x - tn.location_x ydiff = y - tn.location_y zdiff = z - tn.location_z distanceSquared = xdiff ** 2 + ydiff ** 2 + zdiff ** 2 if distanceSquared < minDistance or minDistance < 0: nearestTreenode = tn minDistance = distanceSquared return nearestTreenode nearestTreenode = getNearestTreenode( params['x'], params['y'], params['z'], treenodes) if nearestTreenode is None: raise Exception('No treenodes were found for skeletons in %s' % skeletons) return HttpResponse(json.dumps({ 'treenode_id': nearestTreenode.id, 'x': int(nearestTreenode.location_x), 'y': int(nearestTreenode.location_y), 'z': int(nearestTreenode.location_z), 'skeleton_id': nearestTreenode.skeleton_id})) except Exception as e: raise Exception(response_on_error + ':' + str(e))
def _create_treenode(project_id, creator, editor, x, y, z, radius, confidence, neuron_id, parent_id, creation_time=None, neuron_name=None): relation_map = get_relation_to_id_map(project_id) class_map = get_class_to_id_map(project_id) def insert_new_treenode(parent_id=None, skeleton_id=None): """ If the parent_id is not None and the skeleton_id of the parent does not match with the skeleton.id, then the database will throw an error given that the skeleton_id, being defined as foreign key in the treenode table, will not meet the being-foreign requirement. """ new_treenode = Treenode() new_treenode.user = creator new_treenode.editor = editor new_treenode.project_id = project_id if creation_time: new_treenode.creation_time = creation_time new_treenode.location_x = float(x) new_treenode.location_y = float(y) new_treenode.location_z = float(z) new_treenode.radius = int(radius) new_treenode.skeleton_id = skeleton_id new_treenode.confidence = int(confidence) if parent_id: new_treenode.parent_id = parent_id new_treenode.save() return new_treenode def relate_neuron_to_skeleton(neuron, skeleton): return _create_relation(creator, project_id, relation_map['model_of'], skeleton, neuron) response_on_error = '' try: if -1 != int(parent_id): # A root node and parent node exist # Select the parent treenode for update to prevent race condition # updates to its skeleton ID while this node is being created. cursor = connection.cursor() cursor.execute( ''' SELECT t.skeleton_id, t.edition_time FROM treenode t WHERE t.id = %s FOR NO KEY UPDATE OF t ''', (parent_id, )) if cursor.rowcount != 1: raise ValueError('Parent treenode %s does not exist' % parent_id) parent_node = cursor.fetchone() parent_skeleton_id = parent_node[0] parent_edition_time = parent_node[1] # Raise an Exception if the user doesn't have permission to edit # the neuron the skeleton of the treenode is modeling. can_edit_skeleton_or_fail(editor, project_id, parent_skeleton_id, relation_map['model_of']) response_on_error = 'Could not insert new treenode!' new_treenode = insert_new_treenode(parent_id, parent_skeleton_id) return NewTreenode(new_treenode.id, new_treenode.edition_time, parent_skeleton_id, parent_edition_time) else: # No parent node: We must create a new root node, which needs a # skeleton and a neuron to belong to. response_on_error = 'Could not insert new treenode instance!' new_skeleton = ClassInstance() new_skeleton.user = creator new_skeleton.project_id = project_id new_skeleton.class_column_id = class_map['skeleton'] new_skeleton.name = 'skeleton' new_skeleton.save() new_skeleton.name = 'skeleton %d' % new_skeleton.id new_skeleton.save() if -1 != neuron_id: # Check that the neuron to use exists if 0 == ClassInstance.objects.filter(pk=neuron_id).count(): neuron_id = -1 if -1 != neuron_id: # Raise an Exception if the user doesn't have permission to # edit the existing neuron. can_edit_class_instance_or_fail(editor, neuron_id, 'neuron') # A neuron already exists, so we use it response_on_error = 'Could not relate the neuron model to ' \ 'the new skeleton!' relate_neuron_to_skeleton(neuron_id, new_skeleton.id) response_on_error = 'Could not insert new treenode!' new_treenode = insert_new_treenode(None, new_skeleton.id) return NewTreenode(new_treenode.id, new_treenode.edition_time, new_skeleton.id, None) else: # A neuron does not exist, therefore we put the new skeleton # into a new neuron. response_on_error = 'Failed to insert new instance of a neuron.' new_neuron = ClassInstance() new_neuron.user = creator new_neuron.project_id = project_id new_neuron.class_column_id = class_map['neuron'] if neuron_name: # Create a regular expression to find allowed patterns. The # first group is the whole {nX} part, while the second group # is X only. counting_pattern = re.compile(r"(\{n(\d+)\})") # Look for patterns, replace all {n} with {n1} to normalize. neuron_name = neuron_name.replace("{n}", "{n1}") if counting_pattern.search(neuron_name): # Find starting values for each substitution. counts = [ int(m.groups()[1]) for m in counting_pattern.finditer(neuron_name) ] # Find existing matching neurons in database. name_match = counting_pattern.sub( r"(\d+)", neuron_name) name_pattern = re.compile(name_match) matching_neurons = ClassInstance.objects.filter( project_id=project_id, class_column_id=class_map['neuron'], name__regex=name_match).order_by('name') # Increment substitution values based on existing neurons. for n in matching_neurons: for i, (count, g) in enumerate( zip(counts, name_pattern.search(n.name).groups())): if count == int(g): counts[i] = count + 1 # Substitute values. count_ind = 0 m = counting_pattern.search(neuron_name) while m: neuron_name = m.string[:m.start()] + str( counts[count_ind]) + m.string[m.end():] count_ind = count_ind + 1 m = counting_pattern.search(neuron_name) new_neuron.name = neuron_name else: new_neuron.name = 'neuron' new_neuron.save() new_neuron.name = 'neuron %d' % new_neuron.id new_neuron.save() response_on_error = 'Could not relate the neuron model to ' \ 'the new skeleton!' relate_neuron_to_skeleton(new_neuron.id, new_skeleton.id) response_on_error = 'Failed to insert instance of treenode.' new_treenode = insert_new_treenode(None, new_skeleton.id) response_on_error = 'Failed to write to logs.' new_location = (new_treenode.location_x, new_treenode.location_y, new_treenode.location_z) insert_into_log( project_id, creator.id, 'create_neuron', new_location, 'Create neuron %d and skeleton ' '%d' % (new_neuron.id, new_skeleton.id)) return NewTreenode(new_treenode.id, new_treenode.edition_time, new_skeleton.id, None) except Exception as e: import traceback raise Exception( "%s: %s %s" % (response_on_error, str(e), str(traceback.format_exc())))
def list_connector(request, project_id=None): stack_id = request.POST.get('stack_id', None) skeleton_id = request.POST.get('skeleton_id', None) def empty_result(): return HttpResponse( json.dumps({ 'iTotalRecords': 0, 'iTotalDisplayRecords': 0, 'aaData': [] })) if not skeleton_id: return empty_result() else: skeleton_id = int(skeleton_id) relation_type = int(request.POST.get('relation_type', 0)) # 0: Presyn, 1 Postsyn, 2 Gj display_start = int(request.POST.get('iDisplayStart', 0)) display_length = int(request.POST.get('iDisplayLength', 0)) sorting_column = int(request.POST.get('iSortCol_0', 0)) sort_descending = upper(request.POST.get('sSortDir_0', 'DESC')) != 'ASC' response_on_error = '' try: response_on_error = 'Could not fetch relations.' relation_map = get_relation_to_id_map(project_id) for rel in [ 'presynaptic_to', 'postsynaptic_to', 'gapjunction_with', 'element_of', 'labeled_as' ]: if rel not in relation_map: raise Exception('Failed to find the required relation %s' % rel) if relation_type == 1: relation_type_id = relation_map['presynaptic_to'] inverse_relation_type_id = relation_map['postsynaptic_to'] elif relation_type == 2: relation_type_id = relation_map['gapjunction_with'] inverse_relation_type_id = relation_map['gapjunction_with'] else: relation_type_id = relation_map['postsynaptic_to'] inverse_relation_type_id = relation_map['presynaptic_to'] response_on_error = 'Failed to select connectors.' cursor = connection.cursor() cursor.execute( ''' SELECT connector.id AS connector_id, tn_other.user_id AS connector_user_id, treenode_user.username AS connector_username, connector.location_x AS connector_x, connector.location_y AS connector_y, connector.location_z AS connector_z, tn_other.id AS other_treenode_id, tn_other.location_x AS other_treenode_x, tn_other.location_y AS other_treenode_y, tn_other.location_z AS other_treenode_z, tn_other.skeleton_id AS other_skeleton_id, tn_this.location_x AS this_treenode_x, tn_this.location_y AS this_treenode_y, tn_this.location_z AS this_treenode_z, tn_this.id AS this_treenode_id, tc_this.relation_id AS this_to_connector_relation_id, tc_other.relation_id AS connector_to_other_relation_id, tc_other.confidence AS confidence, to_char(connector.edition_time, 'DD-MM-YYYY HH24:MI') AS last_modified FROM treenode tn_other, treenode_connector tc_other, connector, "auth_user" treenode_user, treenode_connector tc_this, treenode tn_this WHERE treenode_user.id = tn_other.user_id AND tn_other.id = tc_other.treenode_id AND tc_other.connector_id = connector.id AND tc_other.relation_id = %s AND tc_this.connector_id = connector.id AND tn_this.id = tc_this.treenode_id AND tn_this.skeleton_id = %s AND tc_this.relation_id = %s ORDER BY connector_id, other_treenode_id, this_treenode_id ''', [inverse_relation_type_id, skeleton_id, relation_type_id]) connectors = cursor_fetch_dictionary(cursor) connected_skeletons = map(lambda con: con['other_skeleton_id'], connectors) connector_ids = map(lambda con: con['connector_id'], connectors) response_on_error = 'Failed to find counts of treenodes in skeletons.' skel_tn_count = Treenode.objects.filter(skeleton__in=connected_skeletons)\ .values('skeleton').annotate(treenode_count=Count('skeleton')) # .values to group by skeleton_id. See http://tinyurl.com/dj-values-annotate skeleton_to_treenode_count = {} for s in skel_tn_count: skeleton_to_treenode_count[s['skeleton']] = s['treenode_count'] # Rather than do a LEFT OUTER JOIN to also include the connectors # with no partners, just do another query to find the connectors # without the conditions: response_on_error = 'Failed to select all connectors.' cursor.execute( ''' SELECT connector.id AS connector_id, connector.user_id AS connector_user_id, connector_user.username AS connector_username, connector.location_x AS connector_x, connector.location_y AS connector_y, connector.location_z AS connector_z, tn_this.id AS this_treenode_id, tc_this.relation_id AS this_to_connector_relation_id, tc_this.confidence AS confidence, to_char(connector.edition_time, 'DD-MM-YYYY HH24:MI') AS last_modified FROM connector, "auth_user" connector_user, treenode_connector tc_this, treenode tn_this WHERE connector_user.id = connector.user_id AND tc_this.connector_id = connector.id AND tn_this.id = tc_this.treenode_id AND tn_this.skeleton_id = %s AND tc_this.relation_id = %s ORDER BY connector_id, this_treenode_id ''', [skeleton_id, relation_type_id]) for row in cursor_fetch_dictionary(cursor): connector_id = row['connector_id'] if connector_id not in connector_ids: connectors.append(row) connector_ids.append(connector_id) # For each of the connectors, find all of its labels: response_on_error = 'Failed to find the labels for connectors' if (connector_ids > 0): connector_labels = ConnectorClassInstance.objects.filter( project=project_id, connector__in=connector_ids, relation=relation_map['labeled_as']).values( 'connector', 'class_instance__name') labels_by_connector = { } # Key: Connector ID, Value: List of labels. for label in connector_labels: if label['connector'] not in labels_by_connector: labels_by_connector[label['connector']] = [ label['class_instance__name'] ] else: labels_by_connector[label['connector']].append( label['class_instance__name']) # Sort labels by name for labels in labels_by_connector.values(): labels.sort(key=upper) total_result_count = len(connectors) if 0 == total_result_count: return empty_result() # Paging if display_length == 0: connectors = connectors[display_start:] connector_ids = connector_ids[display_start:] else: connectors = connectors[display_start:display_start + display_length] connector_ids = connector_ids[display_start:display_start + display_length] response_on_error = 'Could not retrieve resolution and translation parameters for project.' if stack_id: resolution = get_object_or_404(Stack, id=int(stack_id)).resolution translation = get_object_or_404(ProjectStack, stack=int(stack_id), project=project_id).translation else: resolution = Double3D(1.0, 1.0, 1.0) translation = Double3D(0.0, 0.0, 0.0) # Format output aaData_output = [] for c in connectors: response_on_error = 'Failed to format output for connector with ID %s.' % c[ 'connector_id'] if 'other_skeleton_id' in c: connected_skeleton_treenode_count = skeleton_to_treenode_count[ c['other_skeleton_id']] else: c['other_skeleton_id'] = '' c['other_treenode_id'] = '' c['other_treenode_x'] = c['connector_x'] c['other_treenode_y'] = c['connector_y'] c['other_treenode_z'] = c['connector_z'] connected_skeleton_treenode_count = 0 if c['connector_id'] in labels_by_connector: labels = ', '.join( map(str, labels_by_connector[c['connector_id']])) else: labels = '' row = [] row.append(c['connector_id']) row.append(c['other_skeleton_id']) row.append(c['other_treenode_x']) #('%.2f' % ) row.append(c['other_treenode_y']) z = c['other_treenode_z'] row.append(z) # FIXME: This is the only place we need a stack nad this can be # done in the client as well. So we really want to keep this and # have a more complicated API? row.append(int((z - translation.z) / resolution.z)) row.append(c['confidence']) row.append(labels) row.append(connected_skeleton_treenode_count) row.append(c['connector_username']) row.append(c['other_treenode_id']) row.append(c['last_modified']) aaData_output.append(row) # Sort output def fetch_value_for_sorting(row): value = row[sorting_column] if isinstance(value, str) or isinstance(value, unicode): return upper(value) return value aaData_output.sort(key=fetch_value_for_sorting) # Fix excessive decimal precision in coordinates for row in aaData_output: row[2] = float('%.2f' % row[2]) row[3] = float('%.2f' % row[3]) row[4] = float('%.2f' % row[4]) if sort_descending: aaData_output.reverse() return HttpResponse( json.dumps({ 'iTotalRecords': total_result_count, 'iTotalDisplayRecords': total_result_count, 'aaData': aaData_output })) except Exception as e: raise Exception(response_on_error + ':' + str(e))
def connectors_info(request, project_id): """ Given a list of connectors, a list of presynaptic skeletons and a list of postsynatic skeletons, return a list of rows, one per synaptic connection, in the same format as one_to_many_synapses. The list of connectors (cids), pre-skeletons (pre) and post-skeletons (post) is optional. """ cids = get_request_list(request.POST, 'cids', map_fn=int) skids = get_request_list(request.POST, 'skids', map_fn=int) skids_pre = get_request_list(request.POST, 'pre', map_fn=int) skids_post = get_request_list(request.POST, 'post', map_fn=int) cursor = connection.cursor() if skids_pre or skids_post: if skids: raise ValueError("The skids parameter can't be used together with " "pre and/or post.") relations = get_relation_to_id_map( project_id, ('presynaptic_to', 'postsynaptic_to'), cursor) pre = relations['presynaptic_to'] post = relations['postsynaptic_to'] else: pre = post = None # Construct base query query_parts = [ ''' SELECT DISTINCT tc1.connector_id, c.location_x, c.location_y, c.location_z, tc1.treenode_id, tc1.skeleton_id, tc1.confidence, tc1.user_id, t1.location_x, t1.location_y, t1.location_z, tc2.treenode_id, tc2.skeleton_id, tc2.confidence, tc2.user_id, t2.location_x, t2.location_y, t2.location_z FROM connector c ''' ] query_params = [] # Add connector filter, if requested if cids: cid_template = ",".join(("(%s)", ) * len(cids)) query_parts.append(''' JOIN (VALUES {}) rc(id) ON c.id = rc.id '''.format(cid_template)) query_params.extend(cids) # Add pre-synaptic skeleton filter, if requested query_parts.append(''' JOIN treenode_connector tc1 ON tc1.connector_id = c.id JOIN treenode t1 ON tc1.treenode_id = t1.id ''') if skids_pre: pre_skid_template = ",".join(("(%s)", ) * len(skids_pre)) query_parts.append(''' JOIN (VALUES {}) sk_pre(id) ON tc1.skeleton_id = sk_pre.id '''.format(pre_skid_template)) query_params.extend(skids_pre) # Add post-synaptic skeleton filter, if requested query_parts.append(''' JOIN treenode_connector tc2 ON tc2.connector_id = c.id JOIN treenode t2 ON tc2.treenode_id = t2.id ''') if skids_post: post_skid_template = ",".join(("(%s)", ) * len(skids_post)) query_parts.append(''' JOIN (VALUES {}) sk_post(id) ON tc2.skeleton_id = sk_post.id '''.format(post_skid_template)) query_params.extend(skids_post) # Add generic skeleton filters if skids: skid_template = ",".join(("(%s)", ) * len(skids)) query_parts.append(''' JOIN (VALUES {}) sk(id) ON tc1.skeleton_id = sk.id OR tc2.skeleton_id = sk.id '''.format(skid_template)) query_params.extend(skids) # Prevent self-joins of connector partners query_parts.append(''' WHERE tc1.id != tc2.id ''') # Pre-synaptic skeleton filters also constrain the relation if skids_pre: query_parts.append(''' AND tc1.relation_id = %s ''') query_params.append(pre) # Post-synaptic skeleton filters also constrain the relation if skids_post: query_parts.append(''' AND tc2.relation_id = %s ''') query_params.append(post) if skids: query_parts.append(''' AND tc1.treenode_id < tc2.treenode_id ''') query_parts.append(''' ORDER BY tc2.skeleton_id ''') cursor.execute("\n".join(query_parts), query_params) rows = tuple((row[0], (row[1], row[2], row[3]), row[4], row[5], row[6], row[7], (row[8], row[9], row[10]), row[11], row[12], row[13], row[14], (row[15], row[16], row[17])) for row in cursor.fetchall()) return HttpResponse(json.dumps(rows))