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')