def test_preference_already_satisfied(self):
database.execute_statement(self.cli.conn, f"INSERT INTO bookings (ROOM, NAME_LASTNAME, BOOKING_DATE, CLIENT_ID) VALUES ('l-sm-n', 'test_name', '24-11-2020', '12345678J')")
with mock.patch('sys.stdout', new=io.StringIO()) as fake_stdout:
self.cli.onecmd('preference 1 n')
actual_result = fake_stdout.getvalue()
expected_result = 'Preference already satisfied\n'
self.assertEqual(actual_result, expected_result)
def set_frame_task_status(args, cfg, db, cursor, status, frames):
if not is_frame_status_valid(status):
return
now = dt.datetime.now(tz=dt.timezone.utc)
sql = '''
INSERT IGNORE INTO frame_task_history
(render_project_id, frame_index, change_date, machine_id, status)
VALUES
(
(SELECT id FROM render_project WHERE project_name = %s),
%s,
%s,
(SELECT id FROM render_machine WHERE machine_name = %s),
(SELECT id FROM frame_task_status WHERE status_name = %s)
);
'''
name = args.project_name
for i in frames:
database.execute_statement(
db,
cursor,
sql, (name, i, now, cfg['general']['machine_name'], status),
commit=True)
if len(frames) > 0:
logging.debug(
'Render status for project "{}" for the frame(s) {} has been changed to {}'
.format(name, ', '.join([str(f) for f in frames]), status))
def test_book_with_only_balcony_rooms_available(self):
database.execute_statement(self.cli.conn, f"INSERT INTO bookings (ROOM, NAME_LASTNAME, BOOKING_DATE, CLIENT_ID) VALUES ('l-sm-n', 'test_name', '30-11-2020', '12345678J')")
with mock.patch('sys.stdout', new=io.StringIO()) as fake_stdout:
self.cli.onecmd('book 30-11-2020 l sm test_name 12345678J')
actual_result = fake_stdout.getvalue()
expected_result = 'Booking confirmed for room type l of size sm with balcony. Booking id: 2\n'
self.assertEqual(actual_result, expected_result)
def test_availability_with_active_bookings(self):
database.execute_statement(self.cli.conn, f"INSERT INTO bookings (ROOM, NAME_LASTNAME, BOOKING_DATE, CLIENT_ID) VALUES ('l-sm-n', 'test_name', '30-11-2020', '12345678J')")
with mock.patch('sys.stdout', new=io.StringIO()) as fake_stdout:
with freeze_time("2020-11-30"):
self.cli.onecmd('availability november')
actual_result = fake_stdout.getvalue().split('\n')[1]
expected_result = "{'l-sm-n': 0, 'l-sm-y': 1, 'l-db-n': 2, 'l-db-y': 4, 'g-sm-n': 1, 'g-sm-y': 1, 'g-db-n': 3, 'g-db-y': 3, 's-sm-n': 2, 's-sm-y': 2, 's-db-n': 2, 's-db-y': 2, 'p-sm-n': 2, 'p-sm-y': 2, 'p-db-n': 2, 'p-db-y': 2}"
self.assertEqual(actual_result, expected_result)
def test_book_with_no_rooms_available(self):
database.execute_statement(self.cli.conn, f"INSERT INTO bookings (ROOM, NAME_LASTNAME, BOOKING_DATE, CLIENT_ID) VALUES ('l-sm-n', 'test_name', '30-11-2020', '12345678J')")
database.execute_statement(self.cli.conn, f"INSERT INTO bookings (ROOM, NAME_LASTNAME, BOOKING_DATE, CLIENT_ID) VALUES ('l-sm-y', 'test_name', '30-11-2020', '12345678J')")
with mock.patch('sys.stdout', new=io.StringIO()) as fake_stdout:
self.cli.onecmd('book 30-11-2020 l sm test_name 12345678J')
actual_result = fake_stdout.getvalue()
expected_result = 'Error. There is no room available\n'
self.assertEqual(actual_result, expected_result)
def test_cancel_booking_manager(self):
database.execute_statement(self.cli.conn, f"INSERT INTO bookings (ROOM, NAME_LASTNAME, BOOKING_DATE, CLIENT_ID) VALUES ('l-sm-y', 'test_name', '23-11-2020', '12345678J')")
with mock.patch('sys.stdout', new=io.StringIO()) as fake_stdout:
self.cli.onecmd('cancel 1')
actual_result = fake_stdout.getvalue()
expected_result ='Booking cancelled\n'
self.assertEqual(actual_result, expected_result)
c = self.cli.conn.cursor()
c.execute("SELECT * FROM bookings WHERE ID = '1'")
booking = c.fetchone()
self.assertEqual(None, booking)
def test_available_preference(self):
database.execute_statement(self.cli.conn, f"INSERT INTO bookings (ROOM, NAME_LASTNAME, BOOKING_DATE, CLIENT_ID) VALUES ('l-sm-n', 'test_name', '23-11-2020', '12345678J')")
with mock.patch('sys.stdout', new=io.StringIO()) as fake_stdout:
self.cli.onecmd('preference 1 y')
c = self.cli.conn.cursor()
c.execute("SELECT ROOM FROM bookings WHERE ID = '1'")
booking = c.fetchone()
self.assertEqual('y', booking[0].split('-')[2])
actual_result = fake_stdout.getvalue()
expected_result = 'Preference saved\n'
self.assertEqual(actual_result, expected_result)
def check_project_status(args, db, cursor):
sql = '''
SELECT render_project.id, project_name, filename, number_of_frames,
status_name, change_date FROM render_project
JOIN render_project_history
ON render_project.id = render_project_id
JOIN render_project_status
ON status = render_project_status.id
WHERE project_name = %s
ORDER BY change_date DESC
LIMIT 1;
'''
result = database.execute_statement(db,
cursor,
sql, (args.project_name, ),
with_result=True)[0]
return {
'project_id': result[0],
'project_name': result[1],
'filename': result[2],
'number_of_frames': result[3],
'status': result[4],
'change_date': result[5]
}
def get_project_frame_list(args, cfg, db, cursor):
sql = '''
SELECT frame_task_history.frame_index, status_name, render_machine.machine_name, change_date FROM frame_task_history
JOIN render_project
ON render_project.id = render_project_id
LEFT JOIN render_machine
ON render_machine.id = machine_id
JOIN frame_task_status fts
ON frame_task_history.status = fts.id
JOIN (
SELECT render_project_id, frame_index, MAX(change_date) as max_date FROM frame_task_history
JOIN render_project
ON render_project.id = render_project_id
WHERE project_name = %s
GROUP BY render_project_id, frame_index
) sub_table ON sub_table.frame_index = frame_task_history.frame_index AND sub_table.max_date = change_date
WHERE project_name = %s ORDER BY frame_task_history.frame_index;
'''
name = args.project_name
result = database.execute_statement(db,
cursor,
sql, (name, name),
with_result=True)
x = PrettyTable()
x.field_names = ['frame', 'status', 'render machine', 'change date']
for row in result:
x.add_row(row)
return x
def register_render_machine(cfg, db, cursor, name=None):
if name is None:
name = cfg['general']['machine_name']
logging.debug('No machine name given, took name from config.')
sql = '''
INSERT INTO render_machine
(machine_name)
VALUES
(%s);
'''
database.execute_statement(db, cursor, sql, (name, ), commit=True)
logging.info(
'Render machine "{}" was successfully registered with the server.'.
format(name))
def check_if_project_name_taken(args, db, cursor):
return len(
database.execute_statement(
db,
cursor,
'SELECT * FROM render_project WHERE project_name = %s;',
(args.project_name, ),
with_result=True)) > 0
def test_existing_list(self):
database.execute_statement(self.cli.conn, f"INSERT INTO bookings (ROOM, NAME_LASTNAME, BOOKING_DATE, CLIENT_ID) VALUES ('l-sm-y', 'test_name', '23-11-2020', '12345678J')")
with mock.patch('sys.stdout', new=io.StringIO()) as fake_stdout:
self.cli.onecmd('list pass')
actual_result = fake_stdout.getvalue()
expected_result ="""----------------
Booking ID : 1
Name : test
Last name : name
Client ID : 12345678J
Room type : Luxury
Room size : Single bed
Room characteristics : With balcony
Date : 23-11-2020
----------------
"""
self.assertEqual(actual_result, expected_result)
def set_project_status(args, cfg, db, cursor, status):
if status not in _VALID_PROJECT_STATUS:
logging.error('Invalid project status ("{}")!'.format(status))
return
sql = '''
INSERT INTO render_project_history
(render_project_id, change_date, status)
VALUES
((SELECT id FROM render_project WHERE project_name = %s), %s,
(SELECT id FROM render_project_status WHERE status_name = %s));
'''
name = args.project_name
now = dt.datetime.now(tz=dt.timezone.utc)
database.execute_statement(db,
cursor,
sql, (name, now, status),
commit=True)
# TODO from what status
logging.debug('Project status of "{}" changed to "{}".'.format(
name, status))
def is_machine_registered(cfg, db, cursor, name=None):
if name is None:
name = cfg['general']['machine_name']
logging.debug('No machine name given, took name from config.')
sql = '''
SELECT COUNT(*) FROM render_machine WHERE machine_name = %s;
'''
result = database.execute_statement(db,
cursor,
sql, (name, ),
with_result=True)[0][0]
return result != 0
def set_all_frame_task_status(args, cfg, db, cursor, status):
if not is_frame_status_valid(status):
return
sql = '''
SELECT frame_index FROM frame_task_history
JOIN render_project rp
ON frame_task_history.render_project_id = rp.id
WHERE project_name = %s;
'''
frames = [
row[0] for row in database.execute_statement(
db, cursor, sql, (args.project_name, ), with_result=True)
]
set_frame_task_status(args, cfg, db, cursor, status, frames)
def set_all_frame_task_status_by_machine(args, cfg, db, cursor, machine,
status):
if not is_frame_status_valid(status):
return
sql = '''
SELECT frame_index FROM frame_task_history
JOIN frame_task_status ON status = frame_task_status.id
JOIN render_machine ON render_machine.id = machine_id
WHERE render_project_id = (SELECT id FROM render_project WHERE project_name = %s)
AND machine_name = %s;
'''
frames = [
row[0] for row in database.execute_statement(
db, cursor, sql, (args.project_name, machine), with_result=True)
]
set_frame_task_status(args, cfg, db, cursor, status, frames)
def request_frames_to_render(args, cfg, db, cursor):
status = check_project_status(args, db, cursor)
if status['status'] != 'RUNNING':
set_project_status(args, cfg, db, cursor, 'RUNNING')
sql = '''
SELECT * FROM frame_task_history
JOIN render_project rp
ON frame_task_history.render_project_id = rp.id
JOIN frame_task_status
ON frame_task_history.status = frame_task_status.id
JOIN (
SELECT render_project_id, frame_index, MAX(change_date) as max_date FROM frame_task_history
JOIN render_project
ON render_project.id = render_project_id
WHERE project_name = %s
GROUP BY render_project_id, frame_index
) sub_table ON sub_table.frame_index = frame_task_history.frame_index AND sub_table.max_date = change_date
WHERE project_name = %s AND frame_task_status.status_name = 'CREATED'
ORDER BY frame_task_history.frame_index
LIMIT %s;
'''
name = args.project_name
result = database.execute_statement(
db,
cursor,
sql, (name, name, cfg['render']['frames_per_task']),
with_result=True)
frames = [row[1] for row in result]
if len(frames) > 0:
set_frame_task_status(args, cfg, db, cursor, 'RESERVED', frames)
logging.info('Got task to render frame(s) {} of project "{}".'.format(
name, ', '.join([str(f) for f in frames])))
else:
logging.info('No open tasks remaining.'
) # TODO check other tasks and offer finish
return frames
def start_project(args, cfg, db, cursor):
project_name = args.project_name
sql = '''
INSERT INTO render_project
(project_name, filename, number_of_frames)
VALUES
(%s, %s, %s);
'''
database.execute_statement(db,
cursor,
sql,
(project_name, args.filename, args.num_frames),
commit=True)
project_id = cursor.lastrowid
logging.debug('Project "{}" created with id = {}.'.format(
project_name, project_id))
sql = '''
INSERT INTO render_project_history(render_project_id, change_date, status)
VALUES (%s, %s,
(SELECT id FROM render_project_status WHERE status_name = "CREATED"));
'''
now = dt.datetime.now(tz=dt.timezone.utc)
database.execute_statement(db, cursor, sql, (project_id, now), commit=True)
sql = '''
INSERT INTO frame_task_history
(render_project_id, frame_index, change_date, machine_id, status)
VALUES
(%s, %s, %s,
(SELECT id FROM render_machine WHERE machine_name = "SERVER"),
(SELECT id FROM frame_task_status WHERE status_name = "CREATED"));
'''
for i in range(args.num_frames):
database.execute_statement(db,
cursor,
sql, (project_id, i, now),
commit=True)
logging.info('Project "{}" successfully started. '.format(project_name) +
'To see info about the project run: python main.py check '
'--project_name {}'.format(project_name))
def get_number_of_frames_with_status(args, cfg, db, cursor, status):
name = args.project_name
sql = '''
SELECT COUNT(*) FROM frame_task_history
JOIN frame_task_status
ON frame_task_history.status = frame_task_status.id
JOIN render_project rp
ON frame_task_history.render_project_id = rp.id
JOIN (
SELECT render_project_id, frame_index, MAX(change_date) as max_date FROM frame_task_history
JOIN render_project
ON render_project.id = render_project_id
WHERE project_name = %s
GROUP BY render_project_id, frame_index
) sub_table ON sub_table.frame_index = frame_task_history.frame_index AND sub_table.max_date = change_date
WHERE project_name = %s AND frame_task_status.status_name = %s;
'''
result = database.execute_statement(db,
cursor,
sql, (name, name, status),
with_result=True)[0][0]
return result
import database
connection = database.create_connection("localhost", "root", "rootpsw1",
"dhbw_base")
result = database.execute_statement(connection, "SELECT * FROM students")
for x in result:
print(x)
def setUp(self, outs):
self.cli = app.BookItShell()
self.cli.connect('test_database.db')
database.create_tables(self.cli.conn)
database.execute_statement(self.cli.conn, f"INSERT INTO bookings (ROOM, NAME_LASTNAME, BOOKING_DATE, CLIENT_ID) VALUES ('l-sm-y', 'test_name', '23-11-2020', '12345678J')")
def tearDown(self):
database.execute_statement(self.cli.conn, 'DROP TABLE bookings')
database.execute_statement(self.cli.conn, 'DROP TABLE room_types')
