1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
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()
|
