summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorParker Berberian <pberberian@iol.unh.edu>2017-08-21 09:31:16 -0400
committerParker Berberian <pberberian@iol.unh.edu>2017-08-31 13:28:58 -0400
commit0a783da920b78ddac25bde796e0f9e165c4a7597 (patch)
treeca864fe7ff661a7265de3bfc5f6cdc3c7417d25e
parent136e34eeb584922f6a3fab4ed9e8f5b35d8b0921 (diff)
Adds SQLite DataBase
JIRA: N/A Adds a database handler in source/database.py to store all hosts and to store any bookings coming from the dashboard. source/resetDataBase will clean the db and try to repopulate it with information from the FOG server. Change-Id: I14537452d8566db17787f116018f45bb1ddd75ba Signed-off-by: Parker Berberian <pberberian@iol.unh.edu>
-rw-r--r--tools/laas-fog/source/database.py296
-rwxr-xr-xtools/laas-fog/source/resetDataBase.py110
2 files changed, 406 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()
diff --git a/tools/laas-fog/source/resetDataBase.py b/tools/laas-fog/source/resetDataBase.py
new file mode 100755
index 00000000..ff141e58
--- /dev/null
+++ b/tools/laas-fog/source/resetDataBase.py
@@ -0,0 +1,110 @@
+#!/usr/bin/python
+"""
+#############################################################################
+#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 sys
+import os
+import yaml
+from api.fog import FOG_Handler
+from database import HostDataBase
+from database import BookingDataBase
+
+"""
+This file just resets the host database with
+all the hosts in fog, with all of them
+showing as available
+
+This file is just provided to make populating the host db easier.
+If you wanted to do this yourself, you could do the following in
+a python command prompt:
+ from database import HostDataBase
+ db = HostDataBase("/path/to/file")
+ db.addHost("host-name")
+ db.addHost("host-name")
+ db.addHost("host-name")
+
+"""
+config = None
+if "--config" in sys.argv:
+ i = sys.argv.index("--config")
+ if len(sys.argv) > i+1 and os.path.isfile(sys.argv[i+1]):
+ try:
+ config = yaml.safe_load(open(sys.argv[i+1]))
+ except Exception:
+ print "failed to read config file. exiting"
+ sys.exit(1)
+ else:
+ print "config file not found. exiting"
+ sys.exit(1)
+else:
+ print "no config file given. Specify file with '--config <FILE_PATH>'"
+ sys.exit(1)
+
+host = False
+if "--host" in sys.argv or "--both" in sys.argv:
+ host = True
+
+booking = False
+if "--booking" in sys.argv or "--both" in sys.argv:
+ booking = True
+
+
+if host:
+
+ fog = FOG_Handler(
+ config['fog']['server']
+ )
+ if os.path.isfile(config['fog']['api_key']):
+ fog.getFogKeyFromFile(config['fog']['api_key'])
+ else:
+ fog.setFogKey(config['fog']['api_key'])
+
+ if os.path.isfile(config['fog']['user_key']):
+ fog.getUserKeyFromFile(config['fog']['user_key'])
+ else:
+ fog.setUserKey(config['fog']['user_key'])
+ hosts = fog.getHostsinGroup("vm")
+ host_names = []
+ for host in hosts:
+ host_names.append(host['name'])
+
+ # creates the directory of the db, if it doesnt yet exist
+ dbDir = os.path.dirname(config['database'])
+ if not os.path.isdir(dbDir):
+ os.makedirs(dbDir)
+
+ db = HostDataBase(config['database'])
+
+ # check if the table already exists or not
+ try:
+ db.cursor.execute("SELECT * FROM hosts")
+ except Exception as err:
+ if "no such table" in str(err):
+ db.createTable()
+
+ db.resetHosts(host_names)
+
+if booking:
+ db = BookingDataBase(config['database'])
+ db.createTable()
+ db.close()
+
+else:
+ print "you must specify the '--host', '--booking', or '--both' option"
+ print "depending on which database you wish to reset"
+ sys.exit(0)