summaryrefslogtreecommitdiff
path: root/opendc
diff options
context:
space:
mode:
Diffstat (limited to 'opendc')
-rw-r--r--opendc/api/v1/users/endpoint.py2
-rw-r--r--opendc/models/machine.py8
-rw-r--r--opendc/models/machine_state.py6
-rw-r--r--opendc/models/model.py11
-rw-r--r--opendc/models/rack_state.py6
-rw-r--r--opendc/models/room_state.py6
-rw-r--r--opendc/models/task_duration.py2
-rw-r--r--opendc/models/task_state.py2
-rw-r--r--opendc/models/user.py4
-rw-r--r--opendc/util/database.py22
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
generated by cgit v1.2.3 (git 2.25.1) at 2026-02-04 17:11:49 +0000