summaryrefslogtreecommitdiffstats
path: root/tools/laas-fog/source/database.py
diff options
context:
space:
mode:
Diffstat (limited to 'tools/laas-fog/source/database.py')
-rw-r--r--tools/laas-fog/source/database.py296
1 files changed, 296 insertions, 0 deletions
diff --git a/tools/laas-fog/source/database.py b/tools/laas-fog/source/database.py
new file mode 100644
index 00000000..ca7e5c89
--- /dev/null
+++ b/tools/laas-fog/source/database.py
@@ -0,0 +1,296 @@
+"""
+#############################################################################
+#Copyright 2017 Parker Berberian and others #
+# #
+#Licensed under the Apache License, Version 2.0 (the "License"); #
+#you may not use this file except in compliance with the License. #
+#You may obtain a copy of the License at #
+# #
+# http://www.apache.org/licenses/LICENSE-2.0 #
+# #
+#Unless required by applicable law or agreed to in writing, software #
+#distributed under the License is distributed on an "AS IS" BASIS, #
+#WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. #
+#See the License for the specific language governing permissions and #
+#limitations under the License. #
+#############################################################################
+"""
+import sqlite3
+import sys
+import time
+
+
+class HostDataBase:
+ """
+ This class talks with a simple sqlite database and can select a free host
+ when one is needed.
+ The layout of the database is:
+ TABLE host:
+ name <hostname> status <status_code> book_start
+ <Unix timestamp> book_end <Unix timestamp>
+ status_codes:
+ 0 - idle
+ 1 - deploying
+ 2 - deployed, in use
+ 3 - expired, ready to be reset
+ """
+
+ def __init__(self, path):
+ """
+ init function. Will create the file at the end of path
+ if it doesnt already exist
+ """
+ self.database = sqlite3.connect(path)
+ self.cursor = self.database.cursor()
+
+ def resetHosts(self, hosts):
+ """
+ Recreates the host table in the database.
+ WILL ERASE ALL DATA. USE WITH CAUTION.
+ """
+ try:
+ self.cursor.execute("DROP TABLE hosts")
+ self.createTable()
+ except:
+ pass
+
+ for host in hosts:
+ self.addHost(host)
+
+ def createTable(self):
+ """
+ This method creates the table hosts with
+ a name and status field
+ """
+ self.cursor.execute("CREATE TABLE hosts (name text, status integer)")
+ self.database.commit()
+
+ def addHost(self, name):
+ """
+ Adds a host with name to the available hosts.
+ When first added, the host is assumed to be idle.
+ """
+ host = (name, )
+ self.cursor.execute("INSERT INTO hosts VALUES (?, 0) ", host)
+ self.database.commit()
+
+ def getHost(self, requested=None):
+ """
+ Returns the name of an available host.
+ If a host is specifically requested,
+ that host is returned.
+ If the requested host is not available,
+ this method will throw an error.
+ If no host is specificaly requested,
+ the next available host is returned.
+ """
+ self.cursor.execute("SELECT name FROM hosts WHERE status = 0")
+ hostList = self.cursor.fetchall()
+ if len(hostList) < 1:
+ # throw and exception
+ sys.exit(1)
+ host = None
+ if requested is not None:
+ if (requested, ) in hostList and self.hostIsIdle(requested):
+ host = requested # If requested, exists, and idle, return it
+ else:
+ sys.exit(1)
+ else:
+ host = hostList[0][0]
+ self.makeHostBusy(host)
+ return host
+
+ def makeHostBusy(self, name):
+ """
+ makes the status of host 'name' equal 1,
+ making it 'busy'
+ """
+ host = (name, )
+ self.cursor.execute("UPDATE hosts SET status = 1 WHERE name=?", host)
+ self.database.commit()
+
+ def makeHostDeployed(self, name):
+ """
+ makes the status of host 'name' equal 2,
+ making it 'deployed' and/or in use
+ """
+ host = (name, )
+ self.cursor.execute("UPDATE hosts SET status = 2 WHERE name=?", host)
+ self.database.commit()
+
+ def makeHostExpired(self, name):
+ """
+ makes the status of host 'name' equal 3,
+ meaning its booking has ended and needs to be cleaned.
+ """
+ host = (name, )
+ self.cursor.execute("UPDATE hosts SET status = 3 WHERE name=?", host)
+ self.database.commit()
+
+ def getExpiredHosts(self):
+ """
+ returns a list of all hosts with an expired booking that
+ need to be cleaned.
+ """
+ self.cursor.execute("SELECT name FROM hosts where status = 3")
+ host_tuples = self.cursor.fetchall()
+ hosts = []
+ for host in host_tuples:
+ hosts.append(host[0])
+ return hosts # returns list of strings, not tuples
+
+ def hostIsBusy(self, name):
+ """
+ returns True if the host is not idle
+ """
+ host = (name, )
+ self.cursor.execute("SELECT status FROM hosts WHERE name=?", host)
+ stat = self.cursor.fetchone()[0]
+ if stat < 1:
+ return False
+ return True
+
+ def hostIsIdle(self, name):
+ """
+ returns True if the host is idle.
+ """
+ return not self.hostIsBusy(name)
+
+ def getAllHosts(self):
+ """
+ returns the whole host database.
+ """
+ self.cursor.execute("SELECT * FROM hosts")
+ return self.cursor.fetchall()
+
+ def close(self):
+ """
+ commits and closes connection to the database file.
+ """
+ self.database.commit()
+ self.database.close()
+
+
+class BookingDataBase:
+ """
+ Database to hold all active bookings for our servers.
+ Database contains table bookings - can be same or different
+ db file as the host database
+ bookings contains a field for every json key from the pharos dashboard,
+ plus a "status" integer which is either
+ 0 - waiting to start
+ 1 - started
+ 2 - booking over
+
+ As written, the pharos listener will immediately store all bookings that
+ are both for your dev pods and not
+ yet over, regardless of when the booking starts. Once the booking ends
+ and the dev pod is cleaned, the booking is deleted to save space and cpu.
+ """
+
+ def __init__(self, path):
+ """
+ creates a BookingDataBase object with the database located
+ at path. if path does not yet exist, it will be created.
+ """
+ self.database = sqlite3.connect(path)
+ self.cursor = self.database.cursor()
+
+ def createTable(self):
+ """
+ Creates table in the database to store booking information
+ """
+ try:
+ self.cursor.execute("DROP TABLE bookings")
+ except:
+ pass
+ self.cursor.execute("""CREATE TABLE bookings
+ (id integer, resource_id integer, start double, end double,
+ installer_name text, scenario_name text,
+ purpose text, status integer, vpn text)""")
+ self.database.commit()
+
+ def checkAddBooking(self, booking):
+ """
+ This method accepts a JSON booking definition from the dashboard
+ api and adds it to the database if it does not already exist.
+ """
+ # first, check if booking is already expired
+ if time.time() > booking['end']:
+ return
+ # check if booking is in database already
+ b_id = (booking['id'], )
+ self.cursor.execute("SELECT * FROM bookings WHERE id=?", b_id)
+ if len(self.cursor.fetchall()) > 0: # booking already in the db
+ return
+ tup = (
+ booking['id'],
+ booking['resource_id'],
+ booking['start'],
+ booking['end'],
+ booking['installer_name'],
+ booking['scenario_name'],
+ booking['purpose'],
+ 0,
+ ''
+ )
+ self.cursor.execute(
+ "INSERT INTO bookings VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", tup)
+ self.database.commit()
+
+ def removeBooking(self, idNum):
+ """
+ deletes booking with given id from the database.
+ """
+ booking_id = (idNum, )
+ self.cursor.execute("DELETE FROM bookings WHERE id=?", booking_id)
+
+ def getBookings(self):
+ """
+ returns a list of all bookings.
+ """
+ self.cursor.execute("SELECT * FROM bookings")
+ return self.cursor.fetchall()
+
+ def setStatus(self, booking_id, status):
+ """
+ sets the status of the booking with booking id booking_id.
+ as noted above, the status codes are:
+ 0 - not yet started
+ 1 - started, but not yet over
+ 2 - over, expired
+ """
+ data = (status, booking_id)
+ self.cursor.execute("UPDATE bookings SET status=? WHERE id=?", data)
+ self.database.commit()
+
+ def setVPN(self, resource, uid):
+ data = (uid, resource, 1)
+ self.cursor.execute(
+ "UPDATE bookings SET vpn=? WHERE resource_id=? AND status=?",
+ data
+ )
+ self.database.commit()
+
+ def getVPN(self):
+ """
+ returns a list of all vpn users associated with current
+ bookings.
+ """
+ self.cursor.execute("SELECT vpn FROM bookings WHERE status=1")
+ users_messy = self.cursor.fetchall()
+ users = []
+ for user in users_messy:
+ user = user[0] # get string rather than tuple
+ user = user.strip()
+ if len(user) < 1:
+ continue
+ users.append(user) # a list of non-empty strings
+ return users
+
+ def close(self):
+ """
+ commits changes and closes connection to db file.
+ """
+ self.database.commit()
+ self.database.close()