Merge pull request #9 from atlarge-research/mariadb

Migration to MariaDB

8 files changed, 23 insertions, 22 deletions

diff --git a/opendc/models/machine.py b/opendc/models/machine.py
index 52cf221b..a2b7f57d 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