Merge branch 'master' into flat-api

10 files changed, 38 insertions, 31 deletions

diff --git a/opendc/api/v1/users/endpoint.py b/opendc/api/v1/users/endpoint.py
index 7e381ed5..1c971b56 100644
--- a/opendc/api/v1/users/endpoint.py
+++ b/opendc/api/v1/users/endpoint.py
@@ -52,7 +52,7 @@ def POST(request):
         return Response(400, e.message)
 
     # Instantiate a User
-
+    
     request.params_body['user']['googleId'] = request.google_id
     user = User.from_JSON(request.params_body['user'])
 
diff --git a/opendc/models/machine.py b/opendc/models/machine.py
index 54838ffd..90945ef1 100644
--- a/opendc/models/machine.py
+++ b/opendc/models/machine.py
@@ -44,14 +44,14 @@ class Machine(Model):
 
             # First, delete current machine-device links
 
-            statement = 'DELETE FROM machine_{} WHERE machine_id = ?'.format(device_table)
+            statement = 'DELETE FROM machine_{} WHERE machine_id = %s'.format(device_table)
             database.execute(statement, (before_insert.id,))
 
             # Then, add current ones
             
             for device_id in getattr(before_insert, before_insert.device_table_to_attribute[device_table]):
 
-                statement = 'INSERT INTO machine_{} (machine_id, {}) VALUES (?, ?)'.format(
+                statement = 'INSERT INTO machine_{} (machine_id, {}) VALUES (%s, %s)'.format(
                     device_table,
                     before_insert.device_table_to_attribute[device_table][:-1]
                 )
@@ -68,7 +68,7 @@ class Machine(Model):
             return cls(id = -1)
         
         try:
-            statement = 'SELECT id FROM machines WHERE rack_id = ? AND position = ?'
+            statement = 'SELECT id FROM machines WHERE rack_id = %s AND position = %s'
             machine_id = database.fetchone(statement, (rack.id, position))[0]
         except:
             return cls(id = -1)
@@ -106,7 +106,7 @@ class Machine(Model):
 
         for device_table in self.device_table_to_attribute.keys():
             
-            statement = 'SELECT * FROM machine_{} WHERE machine_id = ?'.format(device_table)
+            statement = 'SELECT * FROM machine_{} WHERE machine_id = %s'.format(device_table)
             results = database.fetchall(statement, (self.id,))
         
             device_ids = []
diff --git a/opendc/models/machine_state.py b/opendc/models/machine_state.py
index 44bc6746..693b57d2 100644
--- a/opendc/models/machine_state.py
+++ b/opendc/models/machine_state.py
@@ -38,7 +38,7 @@ class MachineState(Model):
 
         machine_states = []
         
-        statement = 'SELECT * FROM machine_states WHERE experiment_id = ?'
+        statement = 'SELECT * FROM machine_states WHERE experiment_id = %s'
         results = database.fetchall(statement, (experiment_id,))
  
         for row in results:    
@@ -52,7 +52,7 @@ class MachineState(Model):
 
         machine_states = []
  
-        statement = 'SELECT * FROM machine_states WHERE experiment_id = ? AND machine_states.tick = ?'
+        statement = 'SELECT * FROM machine_states WHERE experiment_id = %s AND machine_states.tick = %s'
         results = database.fetchall(statement, (experiment_id, tick))
  
         for row in results:    
@@ -65,7 +65,7 @@ class MachineState(Model):
 
         super(MachineState, self).read()
 
-        statement = 'SELECT tick FROM task_states WHERE id = ?'
+        statement = 'SELECT tick FROM task_states WHERE id = %s'
         result = database.fetchone(statement, (self.task_state_id,))
 
         self.tick = result[0]
diff --git a/opendc/models/model.py b/opendc/models/model.py
index e8a14e76..2507a287 100644
--- a/opendc/models/model.py
+++ b/opendc/models/model.py
@@ -109,20 +109,20 @@ class Model(object):
     def _generate_insert_placeholders_string(cls):
         """Generate a SQLite insertion placeholders string for this Model."""
 
-        return ', '.join(['?'] * len(cls.COLUMNS))
+        return ', '.join(['%s'] * len(cls.COLUMNS))
 
     @classmethod
     def _generate_primary_key_string(cls):
         """Generate the SQLite primary key string for this Model."""
         
-        return ' AND '.join(['{} = ?'.format(x) for x in cls.COLUMNS_PRIMARY_KEY])
+        return ' AND '.join(['{} = %s'.format(x) for x in cls.COLUMNS_PRIMARY_KEY])
 
     @classmethod
     def _generate_update_columns_string(cls):
         """Generate a SQLite updatable columns string for this Model."""
 
         return ', '.join(
-            ['{} = ?'.format(x) for x in cls.COLUMNS if not x in cls.COLUMNS_PRIMARY_KEY]
+            ['{} = %s'.format(x) for x in cls.COLUMNS if not x in cls.COLUMNS_PRIMARY_KEY]
         )
 
     # SQL TUPLE GENERATION METHODS
@@ -207,7 +207,7 @@ class Model(object):
         """Return all instances of the Model in the database where column_name = value."""
 
         if column_name is not None and value is not None:
-            statement = 'SELECT * FROM {} WHERE {} = ?'.format(cls.TABLE_NAME, column_name)
+            statement = 'SELECT * FROM {} WHERE {} = %s'.format(cls.TABLE_NAME, column_name)
             database_models = database.fetchall(statement, (value,))
         
         else:
@@ -264,7 +264,7 @@ class Model(object):
         else:
             query = query.format(
                 self.TABLE_NAME,
-                '{} = ?'.format(column)
+                '{} = %s'.format(column)
             )
             values = (getattr(self, column),)
 
@@ -292,6 +292,7 @@ class Model(object):
         try:
             last_row_id = database.execute(statement, values)
         except Exception as e:
+            print e
             raise exceptions.ForeignKeyError(e.message)
 
         if 'id' in self.COLUMNS_PRIMARY_KEY:
diff --git a/opendc/models/rack_state.py b/opendc/models/rack_state.py
index 77cafd95..e43dc940 100644
--- a/opendc/models/rack_state.py
+++ b/opendc/models/rack_state.py
@@ -32,7 +32,7 @@ class RackState(Model):
             FROM racks
                 JOIN machines ON racks.id = machines.rack_id
                 JOIN machine_states ON machines.id = machine_states.machine_id
-            WHERE machine_states.experiment_id = ?
+            WHERE machine_states.experiment_id = %s
             GROUP BY machine_states.tick, racks.id
         '''
         results = database.fetchall(statement, (experiment_id,))
@@ -53,8 +53,8 @@ class RackState(Model):
             FROM racks
                 JOIN machines ON racks.id = machines.rack_id
                 JOIN machine_states ON machines.id = machine_states.machine_id
-            WHERE machine_states.experiment_id = ?
-                AND machine_states.tick = ?
+            WHERE machine_states.experiment_id = %s
+                AND machine_states.tick = %s
             GROUP BY machine_states.tick, racks.id
         '''
         results = database.fetchall(statement, (experiment_id, tick))
diff --git a/opendc/models/room_state.py b/opendc/models/room_state.py
index 7dd151e6..169aaa55 100644
--- a/opendc/models/room_state.py
+++ b/opendc/models/room_state.py
@@ -36,7 +36,7 @@ class RoomState(Model):
                 JOIN machines ON racks.id = machines.rack_id
                 JOIN machine_states ON machines.id = machine_states.machine_id
             WHERE objects.type = "RACK"
-                AND machine_states.experiment_id = ?
+                AND machine_states.experiment_id = %s
             GROUP BY machine_states.tick, rooms.id
         '''
         results = database.fetchall(statement, (experiment_id,))
@@ -61,8 +61,8 @@ class RoomState(Model):
                 JOIN machines ON racks.id = machines.rack_id
                 JOIN machine_states ON machines.id = machine_states.machine_id
             WHERE objects.type = "RACK"
-                AND machine_states.experiment_id = ?
-                AND machine_states.tick = ?
+                AND machine_states.experiment_id = %s
+                AND machine_states.tick = %s
             GROUP BY rooms.id
         '''
         results = database.fetchall(statement, (experiment_id, tick))
diff --git a/opendc/models/task_duration.py b/opendc/models/task_duration.py
index 31334040..0d3432e3 100644
--- a/opendc/models/task_duration.py
+++ b/opendc/models/task_duration.py
@@ -27,7 +27,7 @@ class TaskDuration(Model):
 
         statement = '''
             SELECT task_id, MAX(tick) - MIN(tick) as duration FROM task_states
-            WHERE experiment_id = ?
+            WHERE experiment_id = %s
             GROUP BY task_id
         '''
 
diff --git a/opendc/models/task_state.py b/opendc/models/task_state.py
index e9b5b63f..7d216aa0 100644
--- a/opendc/models/task_state.py
+++ b/opendc/models/task_state.py
@@ -25,7 +25,7 @@ class TaskState(Model):
 
         task_states = []
 
-        statement = 'SELECT * FROM task_states WHERE experiment_id = ? AND tick = ?'
+        statement = 'SELECT * FROM task_states WHERE experiment_id = %s AND tick = %s'
         results = database.fetchall(statement, (experiment_id, tick))
 
         for row in results:
diff --git a/opendc/models/user.py b/opendc/models/user.py
index 9c716470..885170d0 100644
--- a/opendc/models/user.py
+++ b/opendc/models/user.py
@@ -22,7 +22,7 @@ class User(Model):
     def from_google_id(cls, google_id):
         """Initialize a User by fetching them by their google id."""
 
-        user = cls._from_database('SELECT * FROM users WHERE google_id = ?', (google_id,))
+        user = cls._from_database('SELECT * FROM users WHERE google_id = %s', (google_id,))
 
         if user is not None:
             return user
@@ -33,7 +33,7 @@ class User(Model):
     def from_email(cls, email):
         """Initialize a User by fetching them by their email."""
 
-        user = cls._from_database('SELECT * FROM users WHERE email = ?', (email,))
+        user = cls._from_database('SELECT * FROM users WHERE email = %s', (email,))
 
         if user is not None:
             return user
diff --git a/opendc/util/database.py b/opendc/util/database.py
index 2ef8b982..337f8fc7 100644
--- a/opendc/util/database.py
+++ b/opendc/util/database.py
@@ -3,40 +3,46 @@ import json
 import sqlite3
 import sys
 
+from mysql.connector.pooling import MySQLConnectionPool
+
 # Get keys from config file
 with open(sys.argv[1]) as file:
     KEYS = json.load(file)
 
 DATETIME_STRING_FORMAT = '%Y-%m-%dT%H:%M:%S'
+CONNECTION_POOL = None
+
+def init_connection_pool(user, password, database, host, port):
+    global CONNECTION_POOL
+    CONNECTION_POOL = MySQLConnectionPool(pool_name = "opendcpool", pool_size = 5, \
+                        user=user, password=password, database=database, host=host, port=port)
 
 def execute(statement, t):
     """Open a database connection and execute the statement."""
 
     # Connect to the database
-    connection = sqlite3.connect(KEYS['DATABASE_LOCATION'])
+    connection = CONNECTION_POOL.get_connection()
     cursor = connection.cursor()
-
-    # Turn on foreign key checks
-    cursor.execute('pragma foreign_keys=ON')
     
     # Execute the statement
     cursor.execute(statement, t)
 
     # Get the id
-    database_id = cursor.execute('SELECT last_insert_rowid()').fetchone()[0]
+    cursor.execute('SELECT last_insert_id();')
+    row_id = cursor.fetchone()[0]
 
     # Disconnect from the database
     connection.commit()
     connection.close()
 
     # Return the id
-    return database_id
+    return row_id
 
 def fetchone(statement, t=None):
     """Open a database connection and return the first row matched by the SELECT statement."""
 
     # Connect to the database
-    connection = sqlite3.connect(KEYS['DATABASE_LOCATION'])
+    connection = CONNECTION_POOL.get_connection()
     cursor = connection.cursor()
 
     # Execute the SELECT statement
@@ -56,7 +62,7 @@ def fetchall(statement, t=None):
     """Open a database connection and return all rows matched by the SELECT statement."""
 
     # Connect to the database
-    connection = sqlite3.connect(KEYS['DATABASE_LOCATION'])
+    connection = CONNECTION_POOL.get_connection()
     cursor = connection.cursor()
 
     # Execute the SELECT statement