def node_list_tuples(request, project_id=None):
''' Retrieve an JSON array with four entries:
[0] an array of arrays, each array representing a treenode
[1] an array of arrays, each array representing a connector and containing
arrays inside that specify the relations between the connector and treenodes.
In this function tuples are used as much as possible for immutable list,
and uses directly the tuples returned by the database cursor.
[2] the labels, if requested.
[3] a boolean which is true when the node limit has been reached.
The returned JSON data is therefore sensitive to indices in the array,
so care must be taken never to alter the order of the variables in the SQL
statements without modifying the accesses to said data both in this function
and in the client that consumes it.
'''
project_id = int(project_id) # sanitize
params = {}
# z: the section index in calibrated units.
# width: the width of the field of view in calibrated units.
# height: the height of the field of view in calibrated units.
# zres: the resolution in the Z axis, used to determine the thickness of a section.
# as: the ID of the active skeleton
# top: the Y coordinate of the bounding box (field of view) in calibrated units
# left: the X coordinate of the bounding box (field of view) in calibrated units
atnid = int(request.POST.get('atnid', -1))
for p in ('top', 'left', 'z', 'width', 'height', 'zres'):
params[p] = float(request.POST.get(p, 0))
params['limit'] = 5000 # Limit the number of retrieved treenodes within the section
params['project_id'] = project_id
try:
cursor = connection.cursor()
cursor.execute('''
SELECT relation_name, id FROM relation WHERE project_id=%s
''' % project_id)
relation_map = dict(cursor.fetchall())
response_on_error = 'Failed to query treenodes'
is_superuser = request.user.is_superuser
user_id = request.user.id
# Set of other user_id for which the request user has editing rights on.
# For a superuser, the domain is all users, and implicit.
domain = None if is_superuser else user_domain(cursor, user_id)
# Fetch treenodes which are in the bounding box,
# which in z it includes the full thickess of the prior section
# and of the next section (therefore the '<' and not '<=' for zhigh)
params['bottom'] = params['top'] + params['height']
params['right'] = params['left'] + params['width']
cursor.execute('''
SELECT
t1.id,
t1.parent_id,
t1.location_x,
t1.location_y,
t1.location_z,
t1.confidence,
t1.radius,
t1.skeleton_id,
t1.user_id,
t2.id,
t2.parent_id,
t2.location_x,
t2.location_y,
t2.location_z,
t2.confidence,
t2.radius,
t2.skeleton_id,
t2.user_id
FROM treenode t1
INNER JOIN treenode t2 ON
( (t1.id = t2.parent_id OR t1.parent_id = t2.id)
OR (t1.parent_id IS NULL AND t1.id = t2.id))
WHERE
t1.location_z = %(z)s
AND t1.location_x > %(left)s
AND t1.location_x < %(right)s
AND t1.location_y > %(top)s
AND t1.location_y < %(bottom)s
AND t1.project_id = %(project_id)s
LIMIT %(limit)s
''', params)
# Above, notice that the join is done for:
# 1. A parent-child or child-parent pair (where the first one is in section z)
# 2. A node with itself when the parent is null
# This is by far the fastest way to retrieve all parents and children nodes
# of the nodes in section z within the specified 2d bounds.
# A list of tuples, each tuple containing the selected columns for each treenode
# The id is the first element of each tuple
treenodes = []
# A set of unique treenode IDs
treenode_ids = set()
n_retrieved_nodes = 0 # at one per row, only those within the section
for row in cursor.fetchall():
n_retrieved_nodes += 1
t1id = row[0]
if t1id not in treenode_ids:
treenode_ids.add(t1id)
treenodes.append(row[0:8] + (is_superuser or row[8] == user_id or row[8] in domain,))
t2id = row[9]
if t2id not in treenode_ids:
treenode_ids.add(t2id)
treenodes.append(row[9:17] + (is_superuser or row[17] == user_id or row[17] in domain,))
# Find connectors related to treenodes in the field of view
# Connectors found attached to treenodes
crows = []
if treenode_ids:
response_on_error = 'Failed to query connector locations.'
cursor.execute('''
SELECT connector.id,
connector.location_x,
connector.location_y,
connector.location_z,
connector.confidence,
treenode_connector.relation_id,
treenode_connector.treenode_id,
treenode_connector.confidence,
connector.user_id,
connector.parent_id
FROM treenode_connector,
connector
WHERE treenode_connector.treenode_id IN (%s)
AND treenode_connector.connector_id = connector.id
''' % ','.join(map(str, treenode_ids)))
crows = list(cursor.fetchall())
# Obtain connectors within the field of view that were not captured above.
# Uses a LEFT OUTER JOIN to include disconnected connectors,
# that is, connectors that aren't referenced from treenode_connector.
cursor.execute('''
SELECT connector.id,
connector.location_x,
connector.location_y,
connector.location_z,
connector.confidence,
treenode_connector.relation_id,
treenode_connector.treenode_id,
treenode_connector.confidence,
connector.user_id,
connector.parent_id
FROM connector LEFT OUTER JOIN treenode_connector
ON connector.id = treenode_connector.connector_id
WHERE connector.project_id = %(project_id)s
AND connector.location_z = %(z)s
AND connector.location_x > %(left)s
AND connector.location_x < %(right)s
AND connector.location_y > %(top)s
AND connector.location_y < %(bottom)s
''', params)
crows.extend(cursor.fetchall())
# Obtain parent connectors not already captured because they are outside
# the field of view.
connector_ids = set(str(crow[0]) for crow in crows)
parent_ids = set(str(crow[9]) for crow in crows if crow[9] is not None)
if connector_ids or parent_ids:
cursor.execute('''
SELECT connector.id,
connector.location_x,
connector.location_y,
connector.location_z,
connector.confidence,
treenode_connector.relation_id,
treenode_connector.treenode_id,
treenode_connector.confidence,
connector.user_id,
connector.parent_id
FROM connector LEFT OUTER JOIN treenode_connector
ON connector.id = treenode_connector.connector_id
WHERE connector.id IN (%s)
OR connector.parent_id IN (%s)
''' % (','.join(parent_ids or ['-1']),
','.join(connector_ids or ['-1'])))
crows.extend(cursor.fetchall())
connectors = []
# A set of missing treenode IDs
missing_treenode_ids = set()
# Check if the active treenode is present; if not, load it
if -1 != atnid and atnid not in treenode_ids:
# If atnid is a connector, it doesn't matter, won't be found in treenode table
missing_treenode_ids.add(atnid)
# A set of unique connector IDs
connector_ids = set()
# The relations between connectors and treenodes, stored
# as connector ID keys vs a list of tuples, each with the treenode id,
# the type of relation (presynaptic_to or postsynaptic_to), and the confidence.
# The list of tuples is generated later from a dict,
# so that repeated tnid entries are overwritten.
pre = defaultdict(dict)
post = defaultdict(dict)
gj = defaultdict(dict)
# Process crows (rows with connectors) which could have repeated connectors
# given the join with treenode_connector
presynaptic_to = relation_map['presynaptic_to']
gapjunction_with = relation_map['gapjunction_with']
for row in crows:
# Collect treeenode IDs related to connectors but not yet in treenode_ids
# because they lay beyond adjacent sections
tnid = row[6] # The tnid column is index 7 (see SQL statement above)
cid = row[0] # connector ID
if tnid is not None:
if tnid not in treenode_ids:
missing_treenode_ids.add(tnid)
# Collect relations between connectors and treenodes
# row[5]: treenode_relation_id
# row[6]: treenode_id (tnid above)
# row[7]: tc_confidence
if row[5] == presynaptic_to:
pre[cid][tnid] = row[7]
elif row[5] == gapjunction_with:
gj[cid][tnid] = row[7]
else:
post[cid][tnid] = row[7]
# Collect unique connectors
if cid not in connector_ids:
connectors.append(row)
connector_ids.add(cid)
# Fix connectors to contain only the relevant entries, plus the relations
for i in xrange(len(connectors)):
c = connectors[i]
cid = c[0]
connectors[i] = (cid, c[1], c[2], c[3], c[4],
[kv for kv in pre[cid].iteritems()],
[kv for kv in post[cid].iteritems()],
[kv for kv in gj[cid].iteritems()],
is_superuser or c[8] == user_id or c[8] in domain,
c[9])
# Fetch missing treenodes. These are related to connectors
# but not in the bounding box of the field of view.
# This is so that we can draw arrows from any displayed connector
# to all of its connected treenodes, even if one is several slices
# below.
if missing_treenode_ids:
params['missing'] = tuple(missing_treenode_ids)
response_on_error = 'Failed to query treenodes from connectors'
cursor.execute('''
SELECT id,
parent_id,
location_x,
location_y,
location_z,
confidence,
radius,
skeleton_id,
user_id
FROM treenode
WHERE id IN %(missing)s''', params)
for row in cursor.fetchall():
treenodes.append(row)
treenode_ids.add(row[0:8] + (is_superuser or row[8] == user_id or row[8] in domain,))
labels = defaultdict(list)
if 'true' == request.POST.get('labels', None):
z0 = params['z']
# Collect treenodes visible in the current section
visible = ','.join(str(row[0]) for row in treenodes if row[4] == z0)
if visible:
cursor.execute('''
SELECT treenode.id, class_instance.name
FROM treenode, class_instance, treenode_class_instance
WHERE treenode_class_instance.relation_id = %s
AND treenode.id IN (%s)
AND treenode_class_instance.treenode_id = treenode.id
AND class_instance.id = treenode_class_instance.class_instance_id
''' % (relation_map['labeled_as'], visible))
for row in cursor.fetchall():
labels[row[0]].append(row[1])
# Collect connectors visible in the current section
visible = ','.join(str(row[0]) for row in connectors if row[3] == z0)
if visible:
cursor.execute('''
SELECT connector.id, class_instance.name
FROM connector, class_instance, connector_class_instance
WHERE connector_class_instance.relation_id = %s
AND connector.id IN (%s)
AND connector_class_instance.connector_id = connector.id
AND class_instance.id = connector_class_instance.class_instance_id
''' % (relation_map['labeled_as'], visible))
for row in cursor.fetchall():
labels[row[0]].append(row[1])
return HttpResponse(json.dumps((treenodes, connectors, labels, n_retrieved_nodes == params['limit']), separators=(',', ':'))) # default separators have spaces in them like (', ', ': '). Must provide two: for list and for dictionary. The point of this: less space, more compact json
except Exception as e:
raise Exception(response_on_error + ':' + str(e))
def node_list_tuples_query(user, params, project_id, atnid, includeLabels, tn_provider):
try:
cursor = connection.cursor()
cursor.execute('''
SELECT relation_name, id FROM relation WHERE project_id=%s
''' % project_id)
relation_map = dict(cursor.fetchall())
response_on_error = 'Failed to query treenodes'
is_superuser = user.is_superuser
user_id = user.id
# Set of other user_id for which the request user has editing rights on.
# For a superuser, the domain is all users, and implicit.
domain = None if is_superuser else user_domain(cursor, user_id)
# Above, notice that the join is done for:
# 1. A parent-child or child-parent pair (where the first one is in section z)
# 2. A node with itself when the parent is null
# This is by far the fastest way to retrieve all parents and children nodes
# of the nodes in section z within the specified 2d bounds.
# A list of tuples, each tuple containing the selected columns for each treenode
# The id is the first element of each tuple
treenodes = []
# A set of unique treenode IDs
treenode_ids = set()
n_retrieved_nodes = 0 # at one per row, only those within the section
for row in tn_provider(cursor, params):
n_retrieved_nodes += 1
t1id = row[0]
if t1id not in treenode_ids:
treenode_ids.add(t1id)
treenodes.append(row[0:8] + (is_superuser or row[8] == user_id or row[8] in domain,))
t2id = row[9]
if t2id not in treenode_ids:
treenode_ids.add(t2id)
treenodes.append(row[9:17] + (is_superuser or row[17] == user_id or row[17] in domain,))
# Find connectors related to treenodes in the field of view
# Connectors found attached to treenodes
crows = []
if treenode_ids:
treenode_list = ','.join('({0})'.format(t) for t in treenode_ids)
response_on_error = 'Failed to query connector locations.'
cursor.execute('''
SELECT c.id,
c.location_x,
c.location_y,
c.location_z,
c.confidence,
tc.relation_id,
tc.treenode_id,
tc.confidence,
c.user_id
FROM treenode_connector tc
INNER JOIN connector c ON (tc.connector_id = c.id)
INNER JOIN (VALUES %s) vals(v) ON tc.treenode_id = v
''' % treenode_list)
crows = list(cursor.fetchall())
# Obtain connectors within the field of view that were not captured above.
# Uses a LEFT OUTER JOIN to include disconnected connectors,
# that is, connectors that aren't referenced from treenode_connector.
cursor.execute('''
SELECT connector.id,
connector.location_x,
connector.location_y,
connector.location_z,
connector.confidence,
treenode_connector.relation_id,
treenode_connector.treenode_id,
treenode_connector.confidence,
connector.user_id
FROM connector LEFT OUTER JOIN treenode_connector
ON connector.id = treenode_connector.connector_id
WHERE connector.project_id = %(project_id)s
AND connector.location_z >= %(z1)s
AND connector.location_z < %(z2)s
AND connector.location_x >= %(left)s
AND connector.location_x < %(right)s
AND connector.location_y >= %(top)s
AND connector.location_y < %(bottom)s
''', params)
crows.extend(cursor.fetchall())
connectors = []
# A set of missing treenode IDs
missing_treenode_ids = set()
# Check if the active treenode is present; if not, load it
if -1 != atnid and atnid not in treenode_ids:
# If atnid is a connector, it doesn't matter, won't be found in treenode table
missing_treenode_ids.add(atnid)
# A set of unique connector IDs
connector_ids = set()
# The relations between connectors and treenodes, stored
# as connector ID keys vs a list of tuples, each with the treenode id,
# the type of relation (presynaptic_to or postsynaptic_to), and the confidence.
# The list of tuples is generated later from a dict,
# so that repeated tnid entries are overwritten.
pre = defaultdict(dict)
post = defaultdict(dict)
other = defaultdict(dict)
# Process crows (rows with connectors) which could have repeated connectors
# given the join with treenode_connector
presynaptic_to = relation_map['presynaptic_to']
postsynaptic_to = relation_map['postsynaptic_to']
for row in crows:
# Collect treeenode IDs related to connectors but not yet in treenode_ids
# because they lay beyond adjacent sections
tnid = row[6] # The tnid column is index 7 (see SQL statement above)
cid = row[0] # connector ID
if tnid is not None:
if tnid not in treenode_ids:
missing_treenode_ids.add(tnid)
# Collect relations between connectors and treenodes
# row[5]: treenode_relation_id
# row[6]: treenode_id (tnid above)
# row[7]: tc_confidence
if row[5] == presynaptic_to:
pre[cid][tnid] = row[7]
elif row[5] == postsynaptic_to:
post[cid][tnid] = row[7]
else:
other[cid][tnid] = row[7]
# Collect unique connectors
if cid not in connector_ids:
connectors.append(row)
connector_ids.add(cid)
# Fix connectors to contain only the relevant entries, plus the relations
for i in xrange(len(connectors)):
c = connectors[i]
cid = c[0]
connectors[i] = (cid, c[1], c[2], c[3], c[4],
[kv for kv in pre[cid].iteritems()],
[kv for kv in post[cid].iteritems()],
[kv for kv in other[cid].iteritems()],
is_superuser or c[8] == user_id or c[8] in domain)
# Fetch missing treenodes. These are related to connectors
# but not in the bounding box of the field of view.
# This is so that we can draw arrows from any displayed connector
# to all of its connected treenodes, even if one is several slices
# below.
if missing_treenode_ids:
missing_id_list = ','.join('({0})'.format(mnid) for mnid in missing_treenode_ids)
response_on_error = 'Failed to query treenodes from connectors'
cursor.execute('''
SELECT id,
parent_id,
location_x,
location_y,
location_z,
confidence,
radius,
skeleton_id,
user_id
FROM treenode, (VALUES %s) missingnodes(mnid)
WHERE id = mnid''' % missing_id_list)
for row in cursor.fetchall():
treenodes.append(row)
treenode_ids.add(row[0:8] + (is_superuser or row[8] == user_id or row[8] in domain,))
labels = defaultdict(list)
if includeLabels:
# Avoid dict lookups in loop
top, left, z1 = params['top'], params['left'], params['z1']
bottom, right, z2 = params['bottom'], params['right'], params['z2']
def is_visible(r):
return r[2] >= left and r[2] < right and \
r[3] >= top and r[3] < bottom and \
r[4] >= z1 and r[4] < z2
# Collect treenodes visible in the current section
visible = ','.join('({0})'.format(row[0]) for row in treenodes if is_visible(row))
if visible:
cursor.execute('''
SELECT tnid, class_instance.name
FROM class_instance, treenode_class_instance,
(VALUES %s) treenodes(tnid)
WHERE treenode_class_instance.relation_id = %s
AND treenode_class_instance.treenode_id = tnid
AND class_instance.id = treenode_class_instance.class_instance_id
''' % (visible, relation_map['labeled_as']))
for row in cursor.fetchall():
labels[row[0]].append(row[1])
# Collect connectors visible in the current section
visible = ','.join('({0})'.format(row[0]) for row in connectors if row[3] >= z1 and row[3] < z2)
if visible:
cursor.execute('''
SELECT cnid, class_instance.name
FROM class_instance, connector_class_instance,
(VALUES %s) connectors(cnid)
WHERE connector_class_instance.relation_id = %s
AND connector_class_instance.connector_id = cnid
AND class_instance.id = connector_class_instance.class_instance_id
''' % (visible, relation_map['labeled_as']))
for row in cursor.fetchall():
labels[row[0]].append(row[1])
return HttpResponse(json.dumps((treenodes, connectors, labels, n_retrieved_nodes == params['limit']), separators=(',', ':'))) # default separators have spaces in them like (', ', ': '). Must provide two: for list and for dictionary. The point of this: less space, more compact json
except Exception as e:
raise Exception(response_on_error + ':' + str(e))
def node_list_tuples(request, project_id=None):
''' Retrieve an JSON array with four entries:
[0] an array of arrays, each array representing a treenode
[1] an array of arrays, each array representing a connector and containing
arrays inside that specify the relations between the connector and treenodes.
In this function tuples are used as much as possible for immutable list,
and uses directly the tuples returned by the database cursor.
[2] the labels, if requested.
[3] a boolean which is true when the node limit has been reached.
The returned JSON data is therefore sensitive to indices in the array,
so care must be taken never to alter the order of the variables in the SQL
statements without modifying the accesses to said data both in this function
and in the client that consumes it.
'''
project_id = int(project_id) # sanitize
params = {}
# z: the section index in calibrated units.
# width: the width of the field of view in calibrated units.
# height: the height of the field of view in calibrated units.
# zres: the resolution in the Z axis, used to determine the thickness of a section.
# as: the ID of the active skeleton
# top: the Y coordinate of the bounding box (field of view) in calibrated units
# left: the X coordinate of the bounding box (field of view) in calibrated units
atnid = int(request.POST.get('atnid', -1))
for p in ('top', 'left', 'z', 'width', 'height', 'zres'):
params[p] = float(request.POST.get(p, 0))
params['limit'] = 5000 # Limit the number of retrieved treenodes within the section
params['project_id'] = project_id
try:
cursor = connection.cursor()
cursor.execute('''
SELECT relation_name, id FROM relation WHERE project_id=%s
''' % project_id)
relation_map = dict(cursor.fetchall())
response_on_error = 'Failed to query treenodes'
is_superuser = request.user.is_superuser
user_id = request.user.id
# Set of other user_id for which the request user has editing rights on.
# For a superuser, the domain is all users, and implicit.
domain = None if is_superuser else user_domain(cursor, user_id)
# Fetch treenodes which are in the bounding box,
# which in z it includes the full thickess of the prior section
# and of the next section (therefore the '<' and not '<=' for zhigh)
params['bottom'] = params['top'] + params['height']
params['right'] = params['left'] + params['width']
cursor.execute('''
SELECT
t1.id,
t1.parent_id,
t1.location_x,
t1.location_y,
t1.location_z,
t1.confidence,
t1.radius,
t1.skeleton_id,
t1.user_id,
t2.id,
t2.parent_id,
t2.location_x,
t2.location_y,
t2.location_z,
t2.confidence,
t2.radius,
t2.skeleton_id,
t2.user_id
FROM treenode t1
INNER JOIN treenode t2 ON
( (t1.id = t2.parent_id OR t1.parent_id = t2.id)
OR (t1.parent_id IS NULL AND t1.id = t2.id))
WHERE
t1.location_z = %(z)s
AND t1.location_x > %(left)s
AND t1.location_x < %(right)s
AND t1.location_y > %(top)s
AND t1.location_y < %(bottom)s
AND t1.project_id = %(project_id)s
LIMIT %(limit)s
''', params)
# Above, notice that the join is done for:
# 1. A parent-child or child-parent pair (where the first one is in section z)
# 2. A node with itself when the parent is null
# This is by far the fastest way to retrieve all parents and children nodes
# of the nodes in section z within the specified 2d bounds.
# A list of tuples, each tuple containing the selected columns for each treenode
# The id is the first element of each tuple
treenodes = []
# A set of unique treenode IDs
treenode_ids = set()
n_retrieved_nodes = 0 # at one per row, only those within the section
for row in cursor.fetchall():
n_retrieved_nodes += 1
t1id = row[0]
if t1id not in treenode_ids:
treenode_ids.add(t1id)
treenodes.append(row[0:8] + (is_superuser or row[8] == user_id or row[8] in domain,))
t2id = row[9]
if t2id not in treenode_ids:
treenode_ids.add(t2id)
treenodes.append(row[9:17] + (is_superuser or row[17] == user_id or row[17] in domain,))
# Find connectors related to treenodes in the field of view
# Connectors found attached to treenodes
crows = []
if treenode_ids:
response_on_error = 'Failed to query connector locations.'
cursor.execute('''
SELECT connector.id,
connector.location_x,
connector.location_y,
connector.location_z,
connector.confidence,
treenode_connector.relation_id,
treenode_connector.treenode_id,
treenode_connector.confidence,
connector.user_id
FROM treenode_connector,
connector
WHERE treenode_connector.treenode_id IN (%s)
AND treenode_connector.connector_id = connector.id
''' % ','.join(map(str, treenode_ids)))
crows = list(cursor.fetchall())
# Obtain connectors within the field of view that were not captured above.
# Uses a LEFT OUTER JOIN to include disconnected connectors,
# that is, connectors that aren't referenced from treenode_connector.
cursor.execute('''
SELECT connector.id,
connector.location_x,
connector.location_y,
connector.location_z,
connector.confidence,
treenode_connector.relation_id,
treenode_connector.treenode_id,
treenode_connector.confidence,
connector.user_id
FROM connector LEFT OUTER JOIN treenode_connector
ON connector.id = treenode_connector.connector_id
WHERE connector.project_id = %(project_id)s
AND connector.location_z = %(z)s
AND connector.location_x > %(left)s
AND connector.location_x < %(right)s
AND connector.location_y > %(top)s
AND connector.location_y < %(bottom)s
''', params)
crows.extend(cursor.fetchall())
connectors = []
# A set of missing treenode IDs
missing_treenode_ids = set()
# Check if the active treenode is present; if not, load it
if -1 != atnid and atnid not in treenode_ids:
# If atnid is a connector, it doesn't matter, won't be found in treenode table
missing_treenode_ids.add(atnid)
# A set of unique connector IDs
connector_ids = set()
# The relations between connectors and treenodes, stored
# as connector ID keys vs a list of tuples, each with the treenode id,
# the type of relation (presynaptic_to or postsynaptic_to), and the confidence.
# The list of tuples is generated later from a dict,
# so that repeated tnid entries are overwritten.
pre = defaultdict(dict)
post = defaultdict(dict)
# Process crows (rows with connectors) which could have repeated connectors
# given the join with treenode_connector
presynaptic_to = relation_map['presynaptic_to']
for row in crows:
# Collect treeenode IDs related to connectors but not yet in treenode_ids
# because they lay beyond adjacent sections
tnid = row[6] # The tnid column is index 7 (see SQL statement above)
cid = row[0] # connector ID
if tnid is not None:
if tnid not in treenode_ids:
missing_treenode_ids.add(tnid)
# Collect relations between connectors and treenodes
# row[5]: treenode_relation_id
# row[6]: treenode_id (tnid above)
# row[7]: tc_confidence
if row[5] == presynaptic_to:
pre[cid][tnid] = row[7]
else:
post[cid][tnid] = row[7]
# Collect unique connectors
if cid not in connector_ids:
connectors.append(row)
connector_ids.add(cid)
# Fix connectors to contain only the relevant entries, plus the relations
for i in xrange(len(connectors)):
c = connectors[i]
cid = c[0]
connectors[i] = (cid, c[1], c[2], c[3], c[4],
[kv for kv in pre[cid].iteritems()],
[kv for kv in post[cid].iteritems()],
is_superuser or c[8] == user_id or c[8] in domain)
# Fetch missing treenodes. These are related to connectors
# but not in the bounding box of the field of view.
# This is so that we can draw arrows from any displayed connector
# to all of its connected treenodes, even if one is several slices
# below.
if missing_treenode_ids:
params['missing'] = tuple(missing_treenode_ids)
response_on_error = 'Failed to query treenodes from connectors'
cursor.execute('''
SELECT id,
parent_id,
location_x,
location_y,
location_z,
confidence,
radius,
skeleton_id,
user_id
FROM treenode
WHERE id IN %(missing)s''', params)
for row in cursor.fetchall():
treenodes.append(row)
treenode_ids.add(row[0:8] + (is_superuser or row[8] == user_id or row[8] in domain,))
labels = defaultdict(list)
if 'true' == request.POST.get('labels', None):
z0 = params['z']
# Collect treenodes visible in the current section
visible = ','.join(str(row[0]) for row in treenodes if row[4] == z0)
if visible:
cursor.execute('''
SELECT treenode.id, class_instance.name
FROM treenode, class_instance, treenode_class_instance
WHERE treenode_class_instance.relation_id = %s
AND treenode.id IN (%s)
AND treenode_class_instance.treenode_id = treenode.id
AND class_instance.id = treenode_class_instance.class_instance_id
''' % (relation_map['labeled_as'], visible))
for row in cursor.fetchall():
labels[row[0]].append(row[1])
# Collect connectors visible in the current section
visible = ','.join(str(row[0]) for row in connectors if row[3] == z0)
if visible:
cursor.execute('''
SELECT connector.id, class_instance.name
FROM connector, class_instance, connector_class_instance
WHERE connector_class_instance.relation_id = %s
AND connector.id IN (%s)
AND connector_class_instance.connector_id = connector.id
AND class_instance.id = connector_class_instance.class_instance_id
''' % (relation_map['labeled_as'], visible))
for row in cursor.fetchall():
labels[row[0]].append(row[1])
return HttpResponse(json.dumps((treenodes, connectors, labels, n_retrieved_nodes == params['limit']), separators=(',', ':'))) # default separators have spaces in them like (', ', ': '). Must provide two: for list and for dictionary. The point of this: less space, more compact json
except Exception as e:
raise Exception(response_on_error + ':' + str(e))
def node_list_tuples_query(user, params, project_id, atnid, includeLabels,
tn_provider):
try:
cursor = connection.cursor()
cursor.execute('''
SELECT relation_name, id FROM relation WHERE project_id=%s
''' % project_id)
relation_map = dict(cursor.fetchall())
response_on_error = 'Failed to query treenodes'
is_superuser = user.is_superuser
user_id = user.id
# Set of other user_id for which the request user has editing rights on.
# For a superuser, the domain is all users, and implicit.
domain = None if is_superuser else user_domain(cursor, user_id)
# Above, notice that the join is done for:
# 1. A parent-child or child-parent pair (where the first one is in section z)
# 2. A node with itself when the parent is null
# This is by far the fastest way to retrieve all parents and children nodes
# of the nodes in section z within the specified 2d bounds.
# A list of tuples, each tuple containing the selected columns for each treenode
# The id is the first element of each tuple
treenodes = []
# A set of unique treenode IDs
treenode_ids = set()
n_retrieved_nodes = 0 # at one per row, only those within the section
for row in tn_provider(cursor, params):
n_retrieved_nodes += 1
t1id = row[0]
if t1id not in treenode_ids:
treenode_ids.add(t1id)
treenodes.append(row[0:8] + (
is_superuser or row[8] == user_id or row[8] in domain, ))
t2id = row[9]
if t2id not in treenode_ids:
treenode_ids.add(t2id)
treenodes.append(row[9:17] + (
is_superuser or row[17] == user_id or row[17] in domain, ))
# Find connectors related to treenodes in the field of view
# Connectors found attached to treenodes
crows = []
if treenode_ids:
treenode_list = ','.join('({0})'.format(t) for t in treenode_ids)
response_on_error = 'Failed to query connector locations.'
cursor.execute('''
SELECT c.id,
c.location_x,
c.location_y,
c.location_z,
c.confidence,
tc.relation_id,
tc.treenode_id,
tc.confidence,
c.user_id
FROM treenode_connector tc
INNER JOIN connector c ON (tc.connector_id = c.id)
INNER JOIN (VALUES %s) vals(v) ON tc.treenode_id = v
''' % treenode_list)
crows = list(cursor.fetchall())
# Obtain connectors within the field of view that were not captured above.
# Uses a LEFT OUTER JOIN to include disconnected connectors,
# that is, connectors that aren't referenced from treenode_connector.
cursor.execute(
'''
SELECT connector.id,
connector.location_x,
connector.location_y,
connector.location_z,
connector.confidence,
treenode_connector.relation_id,
treenode_connector.treenode_id,
treenode_connector.confidence,
connector.user_id
FROM connector LEFT OUTER JOIN treenode_connector
ON connector.id = treenode_connector.connector_id
WHERE connector.project_id = %(project_id)s
AND connector.location_z >= %(z1)s
AND connector.location_z < %(z2)s
AND connector.location_x >= %(left)s
AND connector.location_x < %(right)s
AND connector.location_y >= %(top)s
AND connector.location_y < %(bottom)s
''', params)
crows.extend(cursor.fetchall())
connectors = []
# A set of missing treenode IDs
missing_treenode_ids = set()
# Check if the active treenode is present; if not, load it
if -1 != atnid and atnid not in treenode_ids:
# If atnid is a connector, it doesn't matter, won't be found in treenode table
missing_treenode_ids.add(atnid)
# A set of unique connector IDs
connector_ids = set()
# The relations between connectors and treenodes, stored
# as connector ID keys vs a list of tuples, each with the treenode id,
# the type of relation (presynaptic_to or postsynaptic_to), and the confidence.
# The list of tuples is generated later from a dict,
# so that repeated tnid entries are overwritten.
pre = defaultdict(dict)
post = defaultdict(dict)
other = defaultdict(dict)
# Process crows (rows with connectors) which could have repeated connectors
# given the join with treenode_connector
presynaptic_to = relation_map['presynaptic_to']
postsynaptic_to = relation_map['postsynaptic_to']
for row in crows:
# Collect treeenode IDs related to connectors but not yet in treenode_ids
# because they lay beyond adjacent sections
tnid = row[
6] # The tnid column is index 7 (see SQL statement above)
cid = row[0] # connector ID
if tnid is not None:
if tnid not in treenode_ids:
missing_treenode_ids.add(tnid)
# Collect relations between connectors and treenodes
# row[5]: treenode_relation_id
# row[6]: treenode_id (tnid above)
# row[7]: tc_confidence
if row[5] == presynaptic_to:
pre[cid][tnid] = row[7]
elif row[5] == postsynaptic_to:
post[cid][tnid] = row[7]
else:
other[cid][tnid] = row[7]
# Collect unique connectors
if cid not in connector_ids:
connectors.append(row)
connector_ids.add(cid)
# Fix connectors to contain only the relevant entries, plus the relations
for i in xrange(len(connectors)):
c = connectors[i]
cid = c[0]
connectors[i] = (cid, c[1], c[2], c[3], c[4], [
kv for kv in pre[cid].iteritems()
], [kv for kv in post[cid].iteritems()
], [kv for kv in other[cid].iteritems()], is_superuser
or c[8] == user_id or c[8] in domain)
# Fetch missing treenodes. These are related to connectors
# but not in the bounding box of the field of view.
# This is so that we can draw arrows from any displayed connector
# to all of its connected treenodes, even if one is several slices
# below.
if missing_treenode_ids:
missing_id_list = ','.join('({0})'.format(mnid)
for mnid in missing_treenode_ids)
response_on_error = 'Failed to query treenodes from connectors'
cursor.execute('''
SELECT id,
parent_id,
location_x,
location_y,
location_z,
confidence,
radius,
skeleton_id,
user_id
FROM treenode, (VALUES %s) missingnodes(mnid)
WHERE id = mnid''' % missing_id_list)
for row in cursor.fetchall():
treenodes.append(row)
treenode_ids.add(row[0:8] + (
is_superuser or row[8] == user_id or row[8] in domain, ))
labels = defaultdict(list)
if includeLabels:
# Avoid dict lookups in loop
top, left, z1 = params['top'], params['left'], params['z1']
bottom, right, z2 = params['bottom'], params['right'], params['z2']
def is_visible(r):
return r[2] >= left and r[2] < right and \
r[3] >= top and r[3] < bottom and \
r[4] >= z1 and r[4] < z2
# Collect treenodes visible in the current section
visible = ','.join('({0})'.format(row[0]) for row in treenodes
if is_visible(row))
if visible:
cursor.execute('''
SELECT tnid, class_instance.name
FROM class_instance, treenode_class_instance,
(VALUES %s) treenodes(tnid)
WHERE treenode_class_instance.relation_id = %s
AND treenode_class_instance.treenode_id = tnid
AND class_instance.id = treenode_class_instance.class_instance_id
''' % (visible, relation_map['labeled_as']))
for row in cursor.fetchall():
labels[row[0]].append(row[1])
# Collect connectors visible in the current section
visible = ','.join('({0})'.format(row[0]) for row in connectors
if row[3] >= z1 and row[3] < z2)
if visible:
cursor.execute('''
SELECT cnid, class_instance.name
FROM class_instance, connector_class_instance,
(VALUES %s) connectors(cnid)
WHERE connector_class_instance.relation_id = %s
AND connector_class_instance.connector_id = cnid
AND class_instance.id = connector_class_instance.class_instance_id
''' % (visible, relation_map['labeled_as']))
for row in cursor.fetchall():
labels[row[0]].append(row[1])
return HttpResponse(
json.dumps((treenodes, connectors, labels, n_retrieved_nodes
== params['limit']),
separators=(',', ':'))
) # default separators have spaces in them like (', ', ': '). Must provide two: for list and for dictionary. The point of this: less space, more compact json
except Exception as e:
raise Exception(response_on_error + ':' + str(e))
def node_list_tuples_query(user, params, project_id, atnid, atntype, includeLabels, tn_provider):
try:
cursor = connection.cursor()
cursor.execute('''
SELECT relation_name, id FROM relation WHERE project_id=%s
''' % project_id)
relation_map = dict(cursor.fetchall())
id_to_relation = {v: k for k, v in relation_map.items()}
response_on_error = 'Failed to query treenodes'
is_superuser = user.is_superuser
user_id = user.id
# Set of other user_id for which the request user has editing rights on.
# For a superuser, the domain is all users, and implicit.
domain = None if is_superuser else user_domain(cursor, user_id)
# Above, notice that the join is done for:
# 1. A parent-child or child-parent pair (where the first one is in section z)
# 2. A node with itself when the parent is null
# This is by far the fastest way to retrieve all parents and children nodes
# of the nodes in section z within the specified 2d bounds.
# A list of tuples, each tuple containing the selected columns for each treenode
# The id is the first element of each tuple
treenodes = []
# A set of unique treenode IDs
treenode_ids = set()
n_retrieved_nodes = 0 # at one per row, only those within the section
for row in tn_provider(cursor, params):
n_retrieved_nodes += 1
t1id = row[0]
if t1id not in treenode_ids:
treenode_ids.add(t1id)
can_edit = is_superuser or row[9] == user_id or row[9] in domain
treenodes.append(row[0:9] + (can_edit,))
t2id = row[10]
if t2id not in treenode_ids:
treenode_ids.add(t2id)
can_edit = is_superuser or row[19] == user_id or row[19] in domain
treenodes.append(row[10:19] + (can_edit,))
# A set of missing treenode and connector IDs
missing_treenode_ids = set()
missing_connector_ids = set()
# Check if the active treenode or connector is present; if not, load it
if atnid and -1 != atnid:
if atntype == 'treenode' and atnid not in treenode_ids:
missing_treenode_ids.add(atnid)
elif atntype == 'connector':
missing_connector_ids.add(atnid)
# Find connectors related to treenodes in the field of view
# Connectors found attached to treenodes
crows = []
if treenode_ids:
treenode_list = ','.join('({0})'.format(t) for t in treenode_ids)
response_on_error = 'Failed to query connector locations.'
cursor.execute('''
SELECT c.id,
c.location_x,
c.location_y,
c.location_z,
c.confidence,
tc.relation_id,
tc.treenode_id,
tc.confidence,
tc.edition_time,
tc.id,
c.user_id,
c.edition_time
FROM treenode_connector tc
INNER JOIN connector c ON (tc.connector_id = c.id)
INNER JOIN (VALUES %s) vals(v) ON tc.treenode_id = v
''' % treenode_list)
crows = list(cursor.fetchall())
# Obtain connectors within the field of view that were not captured above.
# Uses a LEFT OUTER JOIN to include disconnected connectors,
# that is, connectors that aren't referenced from treenode_connector.
connector_query = '''
SELECT connector.id,
connector.location_x,
connector.location_y,
connector.location_z,
connector.confidence,
treenode_connector.relation_id,
treenode_connector.treenode_id,
treenode_connector.confidence,
treenode_connector.edition_time,
treenode_connector.id,
connector.user_id,
connector.edition_time
FROM connector LEFT OUTER JOIN treenode_connector
ON connector.id = treenode_connector.connector_id
WHERE connector.project_id = %(project_id)s
AND connector.location_z >= %(z1)s
AND connector.location_z < %(z2)s
AND connector.location_x >= %(left)s
AND connector.location_x < %(right)s
AND connector.location_y >= %(top)s
AND connector.location_y < %(bottom)s
'''
# Add additional connectors to the pool before links are collected
if missing_connector_ids:
sanetized_connector_ids = [int(cid) for cid in missing_connector_ids]
connector_query += '''
UNION
SELECT connector.id,
connector.location_x,
connector.location_y,
connector.location_z,
connector.confidence,
treenode_connector.relation_id,
treenode_connector.treenode_id,
treenode_connector.confidence,
treenode_connector.edition_time,
treenode_connector.id,
connector.user_id,
connector.edition_time
FROM connector LEFT OUTER JOIN treenode_connector
ON connector.id = treenode_connector.connector_id
WHERE connector.project_id = %(project_id)s
AND connector.id IN ({})
'''.format(','.join(str(cid) for cid in sanetized_connector_ids))
cursor.execute(connector_query, params)
crows.extend(cursor.fetchall())
connectors = []
# A set of unique connector IDs
connector_ids = set()
# Collect links to connectors for each treenode. Each entry maps a
# relation ID to a an object containing the relation name, and an object
# mapping connector IDs to confidences.
links = defaultdict(list)
used_relations = set()
seen_links = set()
for row in crows:
# Collect treeenode IDs related to connectors but not yet in treenode_ids
# because they lay beyond adjacent sections
tnid = row[6] # The tnid column is index 7 (see SQL statement above)
cid = row[0] # connector ID
tcid = row[9] # treenode connector ID
if tnid is not None:
if tnid not in treenode_ids:
missing_treenode_ids.add(tnid)
if tcid in seen_links:
continue
seen_links.add(tcid)
# Collect relations between connectors and treenodes
# row[5]: treenode_relation_id
# row[6]: treenode_id (tnid above)
# row[7]: tc_confidence
# row[8]: tc_edition_time
# row[9]: tc_id
links[cid].append((tnid, row[5], row[7], row[8], tcid))
used_relations.add(row[5])
# Collect unique connectors
if cid not in connector_ids:
connectors.append(row)
connector_ids.add(cid)
# Fix connectors to contain only the relevant entries, plus the relations
for i in xrange(len(connectors)):
c = connectors[i]
cid = c[0]
connectors[i] = (cid, c[1], c[2], c[3], c[4], links[cid], c[11],
is_superuser or c[10] == user_id or c[10] in domain)
# Fetch missing treenodes. These are related to connectors
# but not in the bounding box of the field of view.
# This is so that we can draw arrows from any displayed connector
# to all of its connected treenodes, even if one is several slices
# below.
if missing_treenode_ids:
missing_id_list = ','.join('({0})'.format(mnid) for mnid in missing_treenode_ids)
response_on_error = 'Failed to query treenodes from connectors'
cursor.execute('''
SELECT id,
parent_id,
location_x,
location_y,
location_z,
confidence,
radius,
skeleton_id,
edition_time,
user_id
FROM treenode, (VALUES %s) missingnodes(mnid)
WHERE id = mnid''' % missing_id_list)
for row in cursor.fetchall():
treenodes.append(row)
treenode_ids.add(row[0:9] + (is_superuser or row[9] == user_id
or row[9] in domain,))
labels = defaultdict(list)
if includeLabels:
# Avoid dict lookups in loop
top, left, z1 = params['top'], params['left'], params['z1']
bottom, right, z2 = params['bottom'], params['right'], params['z2']
def is_visible(r):
return r[2] >= left and r[2] < right and \
r[3] >= top and r[3] < bottom and \
r[4] >= z1 and r[4] < z2
# Collect treenodes visible in the current section
visible = ','.join('({0})'.format(row[0]) for row in treenodes if is_visible(row))
if visible:
cursor.execute('''
SELECT tnid, class_instance.name
FROM class_instance, treenode_class_instance,
(VALUES %s) treenodes(tnid)
WHERE treenode_class_instance.relation_id = %s
AND treenode_class_instance.treenode_id = tnid
AND class_instance.id = treenode_class_instance.class_instance_id
''' % (visible, relation_map['labeled_as']))
for row in cursor.fetchall():
labels[row[0]].append(row[1])
# Collect connectors visible in the current section
visible = ','.join('({0})'.format(row[0]) for row in connectors if row[3] >= z1 and row[3] < z2)
if visible:
cursor.execute('''
SELECT cnid, class_instance.name
FROM class_instance, connector_class_instance,
(VALUES %s) connectors(cnid)
WHERE connector_class_instance.relation_id = %s
AND connector_class_instance.connector_id = cnid
AND class_instance.id = connector_class_instance.class_instance_id
''' % (visible, relation_map['labeled_as']))
for row in cursor.fetchall():
labels[row[0]].append(row[1])
used_rel_map = {r:id_to_relation[r] for r in used_relations}
return HttpResponse(json.dumps((
treenodes, connectors, labels,
n_retrieved_nodes == params['limit'],
used_rel_map),
cls=DjangoJSONEncoder,
separators=(',', ':')), # default separators have spaces in them like (', ', ': '). Must provide two: for list and for dictionary. The point of this: less space, more compact json
content_type='application/json')
except Exception as e:
raise Exception(response_on_error + ':' + str(e))
