--- a/python/storage.py	Tue Nov 03 13:48:56 2015 -0500
+++ b/python/storage.py	Mon May 09 15:33:11 2016 -0400
@@ -89,38 +89,6 @@
     except sqlite3.OperationalError as error:
         printDBError(error)
 
-# TODO: add test if database connection is open
-# IO to sqlite
-def writeTrajectoriesToSqlite(objects, outputFilename, trajectoryType, objectNumbers = -1):
-    """
-    This function writers trajectories to a specified sqlite file
-    @param[in] objects -> a list of trajectories
-    @param[in] trajectoryType -
-    @param[out] outputFilename -> the .sqlite file containting the written objects
-    @param[in] objectNumber : number of objects loaded
-    """
-    connection = sqlite3.connect(outputFilename)
-    cursor = connection.cursor()
-
-    schema = "CREATE TABLE IF NOT EXISTS \"positions\"(trajectory_id INTEGER,frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))"
-    cursor.execute(schema)
-
-    trajectory_id = 0
-    frame_number = 0
-    if trajectoryType == 'feature':
-        if type(objectNumbers) == int and objectNumbers == -1:
-            for trajectory in objects:
-                trajectory_id += 1
-                frame_number = 0
-                for position in trajectory.getPositions():
-                    frame_number += 1
-                    query = "insert into positions (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)"
-                    cursor.execute(query,(trajectory_id,frame_number,position.x,position.y))
-                    
-    connection.commit()
-    connection.close()
-    
-
 def loadPrototypeMatchIndexesFromSqlite(filename):
     """
     This function loads the prototypes table in the database of name <filename>.
@@ -185,7 +153,7 @@
                 queryStatement += ' WHERE object_id '+objectCriteria
             queryStatement += ' ORDER BY object_id, frame_number'
         else:
-            print('no trajectory type was chosen')
+            print('Unknown trajectory type {}'.format(trajectoryType))
         if queryStatement is not None:
             cursor.execute(queryStatement)
             logging.debug(queryStatement)
@@ -227,7 +195,10 @@
     return userTypes
 
 def loadTrajectoriesFromSqlite(filename, trajectoryType, objectNumbers = None, withFeatures = False):
-    '''Loads the first objectNumbers objects or the indices in objectNumbers from the database'''
+    '''Loads the trajectories (in the general sense, 
+    either features, objects (feature groups) or bounding box series) 
+    The number loaded is either the first objectNumbers objects,
+    or the indices in objectNumbers from the database'''
     connection = sqlite3.connect(filename)
 
     objects = loadTrajectoriesFromTable(connection, 'positions', trajectoryType, objectNumbers)
@@ -284,6 +255,83 @@
     connection.close()
     return objects
 
+def addCurvilinearTrajectoriesFromSqlite(filename, objects):
+    '''Adds curvilinear positions (s_coordinate, y_coordinate, lane)
+    from a database to an existing MovingObject dict (indexed by each objects's num)'''
+    connection = sqlite3.connect(filename)
+    cursor = connection.cursor()
+
+    try:
+        cursor.execute('SELECT * from curvilinear_positions order by trajectory_id, frame_number')
+    except sqlite3.OperationalError as error:
+        printDBError(error)
+        return []
+    
+    missingObjectNumbers = []
+    objNum = None
+    for row in cursor:
+        if objNum != row[0]:
+            objNum = row[0]
+            if objNum in objects:
+                objects[objNum].curvilinearPositions = moving.CurvilinearTrajectory()
+            else:
+                missingObjectNumbers.append(objNum)
+        if objNum in objects:
+            objects[objNum].curvilinearPositions.addPositionSYL(row[2],row[3],row[4])
+    if len(missingObjectNumbers) > 0:
+        print('List of missing objects to attach corresponding curvilinear trajectories: {}'.format(missingObjectNumbers))
+
+def saveTrajectoriesToSqlite(outputFilename, objects, trajectoryType, withFeatures = False):
+    '''Writes features, ie the trajectories positions (and velocities if exist)
+    with their instants to a specified sqlite file
+    Either feature positions (and velocities if they exist)
+    or curvilinear positions will be saved at a time
+
+    TODO: Not implemented for trajectoryType MovingObject with features
+    For objects, with features will control whether the features
+    corresponding to the object are also saved'''
+
+    connection = sqlite3.connect(outputFilename)
+    try:
+        cursor = connection.cursor()
+
+        if trajectoryType == 'feature':
+            cursor.execute("CREATE TABLE IF NOT EXISTS positions (trajectory_id INTEGER, frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))")
+            cursor.execute("CREATE TABLE IF NOT EXISTS velocities (trajectory_id INTEGER, frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))")
+
+            positionQuery = "insert into positions (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)"
+            velocityQuery = "insert into velocities (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)"
+            for obj in objects:
+                num = obj.getNum()
+                frame_number = obj.getFirstInstant()
+                for position in obj.getPositions():
+                    cursor.execute(positionQuery, (num, frame_number, position.x, position.y))
+                    frame_number += 1
+                # velocities
+                velocities = obj.getVelocities()
+                if velocities is not None:
+                    frame_number = obj.getFirstInstant()
+                    for i in xrange(velocities.length()-1):
+                        v = velocities[i]
+                        cursor.execute(velocityQuery, (num, frame_number, v.x, v.y))
+                        frame_number += 1
+        elif trajectoryType == 'curvilinear':
+            cursor.execute("CREATE TABLE IF NOT EXISTS curvilinear_positions (trajectory_id INTEGER, frame_number INTEGER, s_coordinate REAL, y_coordinate REAL, lane TEXT, PRIMARY KEY(trajectory_id, frame_number))")
+            curvilinearQuery = "insert into curvilinear_positions (trajectory_id, frame_number, s_coordinate, y_coordinate, lane) values (?,?,?,?,?)"
+            for obj in objects:
+                num = obj.getNum()
+                frame_number = obj.getFirstInstant()
+                for position in obj.getCurvilinearPositions():
+                    cursor.execute(curvilinearQuery, (num, frame_number, position[0], position[1], position[2]))
+                    frame_number += 1
+        #elif trajectoryType == 'object':
+        else:
+            print('Unknown trajectory type {}'.format(trajectoryType))
+        connection.commit()
+    except sqlite3.OperationalError as error:
+        printDBError(error)
+    connection.close()
+
 def savePrototypesToSqlite(filename, prototypes, trajectoryType = 'feature'):
     'Work in progress, do not use'
     connection = sqlite3.connect(filename)
@@ -300,26 +348,30 @@
 def loadPrototypesFromSqlite(filename):
     pass
 
-def loadGroundTruthFromSqlite(filename, gtType = 'bb', gtNumbers = None):
-    'Loads bounding box annotations (ground truth) from an SQLite '
-    connection = sqlite3.connect(filename)
-    gt = []
+def loadBBMovingObjectsFromSqlite(filename, objectType = 'bb', objectNumbers = None):
+    '''Loads bounding box moving object from an SQLite
+    (format of SQLite output by the ground truth annotation tool
+    or Urban Tracker
 
-    if gtType == 'bb':
-        topCorners = loadTrajectoriesFromTable(connection, 'bounding_boxes', 'bbtop', gtNumbers)
-        bottomCorners = loadTrajectoriesFromTable(connection, 'bounding_boxes', 'bbbottom', gtNumbers)
-        userTypes = loadUserTypesFromTable(connection.cursor(), 'object', gtNumbers) # string format is same as object
+    Load descriptions?'''
+    connection = sqlite3.connect(filename)
+    objects = []
+
+    if objectType == 'bb':
+        topCorners = loadTrajectoriesFromTable(connection, 'bounding_boxes', 'bbtop', objectNumbers)
+        bottomCorners = loadTrajectoriesFromTable(connection, 'bounding_boxes', 'bbbottom', objectNumbers)
+        userTypes = loadUserTypesFromTable(connection.cursor(), 'object', objectNumbers) # string format is same as object
         
         for t, b in zip(topCorners, bottomCorners):
             num = t.getNum()
             if t.getNum() == b.getNum():
-                annotation = moving.BBAnnotation(num, t.getTimeInterval(), t, b, userTypes[num])
-                gt.append(annotation)
+                annotation = moving.BBMovingObject(num, t.getTimeInterval(), t, b, userTypes[num])
+                objects.append(annotation)
     else:
-        print ('Unknown type of annotation {}'.format(gtType))
+        print ('Unknown type of bounding box {}'.format(objectType))
 
     connection.close()
-    return gt
+    return objects
 
 def deleteFromSqlite(filename, dataType):
     'Deletes (drops) some tables in the filename depending on type of data'
@@ -442,7 +494,7 @@
     boundingBoxes = {} # list of bounding boxes for each instant
     try:
         cursor.execute('SELECT name FROM sqlite_master WHERE type=\'table\' AND name=\'bounding_boxes\'')
-        result = [row for row in cursor]
+        result = cursor.fetchall()
         if len(result) > 0:
             cursor.execute('SELECT * FROM bounding_boxes')
             for row in cursor:
@@ -457,33 +509,12 @@
 # saving and loading for scene interpretation (Mohamed Gomaa Mohamed's PhD)
 #########################
 
-def writeFeaturesToSqlite(objects, outputFilename, trajectoryType, objectNumbers = -1):
-    '''write features trajectories maintain trajectory ID,velocities dataset  '''
-    connection = sqlite3.connect(outputFilename)
-    cursor = connection.cursor()
-
-    cursor.execute("CREATE TABLE IF NOT EXISTS \"positions\"(trajectory_id INTEGER,frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))")
-    cursor.execute("CREATE TABLE IF NOT EXISTS \"velocities\"(trajectory_id INTEGER,frame_number INTEGER, x_coordinate REAL, y_coordinate REAL, PRIMARY KEY(trajectory_id, frame_number))")
-    
-    if trajectoryType == 'feature':
-        if type(objectNumbers) == int and objectNumbers == -1:
-            for trajectory in objects:
-                trajectory_id = trajectory.num
-                frame_number = trajectory.timeInterval.first
-                for position,velocity in zip(trajectory.getPositions(),trajectory.getVelocities()):
-                    cursor.execute("insert into positions (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)",(trajectory_id,frame_number,position.x,position.y))
-                    cursor.execute("insert into velocities (trajectory_id, frame_number, x_coordinate, y_coordinate) values (?,?,?,?)",(trajectory_id,frame_number,velocity.x,velocity.y))
-                    frame_number += 1
-                    
-    connection.commit()
-    connection.close()
-    
 def writePrototypesToSqlite(prototypes,nMatching, outputFilename):
     """ prototype dataset is a dictionary with  keys== routes, values== prototypes Ids """
     connection = sqlite3.connect(outputFilename)
     cursor = connection.cursor()
 
-    cursor.execute("CREATE TABLE IF NOT EXISTS \"prototypes\"(prototype_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, nMatching INTEGER, PRIMARY KEY(prototype_id))")
+    cursor.execute("CREATE TABLE IF NOT EXISTS prototypes (prototype_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, nMatching INTEGER, PRIMARY KEY(prototype_id))")
     
     for route in prototypes.keys():
         if prototypes[route]!=[]:
@@ -507,7 +538,7 @@
     try:
         cursor.execute('SELECT * from prototypes order by prototype_id, routeIDstart,routeIDend, nMatching')
     except sqlite3.OperationalError as error:
-        utils.printDBError(error)
+        printDBError(error)
         return []
 
     for row in cursor:
@@ -526,7 +557,7 @@
     connection = sqlite3.connect(outputFilename)
     cursor = connection.cursor()
 
-    cursor.execute("CREATE TABLE IF NOT EXISTS \"labels\"(object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, prototype_id INTEGER, PRIMARY KEY(object_id))")
+    cursor.execute("CREATE TABLE IF NOT EXISTS labels (object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, prototype_id INTEGER, PRIMARY KEY(object_id))")
     
     for route in labels.keys():
         if labels[route]!=[]:
@@ -546,7 +577,7 @@
     try:
         cursor.execute('SELECT * from labels order by object_id, routeIDstart,routeIDend, prototype_id')
     except sqlite3.OperationalError as error:
-        utils.printDBError(error)
+        printDBError(error)
         return []
 
     for row in cursor:
@@ -566,7 +597,7 @@
     connection = sqlite3.connect(outFilename)
     cursor = connection.cursor()
 
-    cursor.execute("CREATE TABLE IF NOT EXISTS \"speedprototypes\"(spdprototype_id INTEGER,prototype_id INTEGER,routeID_start INTEGER, routeID_end INTEGER, nMatching INTEGER, PRIMARY KEY(spdprototype_id))")
+    cursor.execute("CREATE TABLE IF NOT EXISTS speedprototypes (spdprototype_id INTEGER,prototype_id INTEGER,routeID_start INTEGER, routeID_end INTEGER, nMatching INTEGER, PRIMARY KEY(spdprototype_id))")
     
     for route in prototypes.keys():
         if prototypes[route]!={}:
@@ -590,7 +621,7 @@
     try:
         cursor.execute('SELECT * from speedprototypes order by spdprototype_id,prototype_id, routeID_start, routeID_end, nMatching')
     except sqlite3.OperationalError as error:
-        utils.printDBError(error)
+        printDBError(error)
         return []
 
     for row in cursor:
@@ -611,7 +642,7 @@
     connection = sqlite3.connect(outputFilename)
     cursor = connection.cursor()
 
-    cursor.execute("CREATE TABLE IF NOT EXISTS \"routes\"(object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, PRIMARY KEY(object_id))")
+    cursor.execute("CREATE TABLE IF NOT EXISTS routes (object_id INTEGER,routeIDstart INTEGER,routeIDend INTEGER, PRIMARY KEY(object_id))")
     
     for route in Routes.keys():
         if Routes[route]!=[]:
@@ -630,7 +661,7 @@
     try:
         cursor.execute('SELECT * from routes order by object_id, routeIDstart,routeIDend')
     except sqlite3.OperationalError as error:
-        utils.printDBError(error)
+        printDBError(error)
         return []
 
     for row in cursor:
@@ -761,6 +792,16 @@
     except sqlite3.OperationalError as error:
         printDBError(error)
 
+def getNObjectsInLinkFromVissimFile(filename, linkIds):
+    '''Returns the number of objects that traveled through the link ids'''
+    connection = sqlite3.connect(filename)
+    cursor = connection.cursor()
+    queryStatement = 'SELECT link_id, COUNT(DISTINCT trajectory_id) FROM curvilinear_positions where link_id IN ('+','.join([str(id) for id in linkIds])+') GROUP BY link_id'
+    try:
+        cursor.execute(queryStatement)
+        return {row[0]:row[1] for row in cursor}
+    except sqlite3.OperationalError as error:
+        printDBError(error)
 
 def loadTrajectoriesFromVissimFile(filename, simulationStepsPerTimeUnit, objectNumbers = None, warmUpLastInstant = None, usePandas = False, nDecimals = 2, lowMemory = True):
     '''Reads data from VISSIM .fzp trajectory file