diff options
Diffstat (limited to 'laas-fog/source/database.py')
| -rw-r--r-- | laas-fog/source/database.py | 296 |
1 files changed, 296 insertions, 0 deletions
diff --git a/laas-fog/source/database.py b/laas-fog/source/database.py new file mode 100644 index 0000000..ca7e5c8 --- /dev/null +++ b/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() |