def manual_link(ident, editor=None): """Manually set a video_link if the state is 'UNEDITED' or 'DONE' and the upload_location is 'manual'.""" link = flask.request.json['link'] conn = app.db_manager.get_conn() results = database.query( conn, """ SELECT id, state, upload_location FROM events WHERE id = %s""", ident) old_row = results.fetchone() if old_row is None: return 'Row {} not found'.format(ident), 404 if old_row.state != 'UNEDITED' and not ( old_row.state == 'DONE' and old_row.upload_location == 'manual'): return 'Invalid state {} for manual video link'.format( old_row.state), 403 now = datetime.datetime.utcnow() results = database.query( conn, """ UPDATE events SET state='DONE', upload_location = 'manual', video_link = %s, editor = %s, edit_time = %s, upload_time = %s WHERE id = %s AND (state = 'UNEDITED' OR (state = 'DONE' AND upload_location = 'manual'))""", link, editor, now, now, ident) logging.info("Row {} video_link set to {}".format(ident, link)) return ''
def sync_playlists(self, rows): """Parse rows with a valid playlist id and at least one tag, overwriting the entire playlists table""" playlists = [] for row in rows: if len(row) != 3: continue tags, _, playlist_id = row tags = self.column_parsers['tags'](tags) if not tags: continue playlist_id = playlist_id.strip() if len(playlist_id) != 34 or not playlist_id.startswith('PL'): continue playlists.append((tags, playlist_id)) # We want to wipe and replace all the current entries in the table. # The easiest way to do this is a DELETE then an INSERT, all within a transaction. # The "with" block will perform everything under it within a transaction, rolling back # on error or committing on exit. logging.info("Updating playlists table with {} playlists".format( len(playlists))) with self.conn: query(self.conn, "DELETE FROM playlists") execute_values( self.conn.cursor(), "INSERT INTO playlists(tags, playlist_id) VALUES %s", playlists)
def create_coverage_page(self, quality): nodes = {} try: connection = self.db_manager.get_conn() host = [ s.split('=')[-1] for s in connection.dsn.split() if 'host' in s ][0] self.logger.info('Fetching list of nodes from {}'.format(host)) results = database.query( connection, """ SELECT name, url FROM nodes WHERE backfill_from""") for row in results: nodes[row.name] = row.url except: self.logger.exception('Getting nodes failed.', exc_info=True) return self.logger.info('Nodes fetched: {}'.format(list(nodes.keys()))) html = """<!DOCTYPE html> <html> <head> <meta charset="utf-8"> <meta http-equiv="refresh" content="30"/> <title>{0} {1} Segment Coverage Maps</title> <style> html {{ background-color: #222;}} h1 {{ color: #eee; text-align: center; font-family: sans-serif;}} h3 {{ color: #eee; text-align: center; font-family: sans-serif;}} img {{ display: block; margin-left: auto; margin-right: auto;}} </style> </head> <body> <h1>{0} {1}</h1>""".format(self.channel, quality) for node in sorted(nodes.keys()): html += """ <h3>{}</h3> <img src="{}/segments/coverage-maps/{}_{}_coverage.png" alt="{}"> """.format(node, nodes[node], self.channel, quality, node) html += """ </body> </html>""" path_prefix = os.path.join(self.base_dir, 'coverage-maps', '{}_{}'.format(self.channel, quality)) temp_path = '{}_{}.html'.format(path_prefix, uuid.uuid4()) final_path = '{}_coverage.html'.format(path_prefix) common.ensure_directory(temp_path) with open(temp_path, 'w') as f: common.writeall(f.write, html) os.rename(temp_path, final_path) self.logger.info('Coverage page for {} created'.format(quality))
def set_row(**kwargs): """Set columns on the row being cut. Raises JobConsistencyError on failure. Example: set_row(state='UNEDITED', error=e) """ # construct an UPDATE query like "SET key1=%(key1)s, key2=%(key2)s, ..." built_query = sql.SQL(""" UPDATE events SET {} WHERE id = %(id)s AND uploader = %(name)s """).format( sql.SQL(", ").join( sql.SQL("{} = {}").format( sql.Identifier(key), sql.Placeholder(key), ) for key in kwargs)) result = query(self.conn, built_query, id=job.id, name=self.name, **kwargs) if result.rowcount != 1: # If we hadn't yet set finalizing, then this means an operator cancelled the job # while we were cutting it. This isn't a problem. if not finalize_begun[0]: raise JobCancelled() raise JobConsistencyError( "No job with id {} and uploader {} when setting: {}". format( job.id, self.name, ", ".join("{} = {!r}".format(k, v) for k, v in kwargs.items())))
def get_events(self): """Return the entire events table as a map {id: event namedtuple}""" result = query(self.conn, "SELECT * FROM events") by_id = {} for row in result.fetchall(): by_id[row.id] = row return by_id
def set_row(**kwargs): """Set columns on the row being cut. Raises JobConsistencyError on failure. Example: set_row(state='UNEDITED', error=e) """ # construct an UPDATE query like "SET key1=%(key1)s, key2=%(key2)s, ..." built_query = sql.SQL(""" UPDATE events SET {} WHERE id = %(id)s AND uploader = %(name)s """).format( sql.SQL(", ").join( sql.SQL("{} = {}").format( sql.Identifier(key), sql.Placeholder(key), ) for key in kwargs)) result = query(self.conn, built_query, id=job.id, name=self.name, **kwargs) if result.rowcount != 1: raise JobConsistencyError( "No job with id {} and uploader {} when setting: {}". format( job.id, self.name, ", ".join("{} = {!r}".format(k, v) for k, v in kwargs.items())))
def auth_wrapper(*args, **kwargs): if app.no_authentication: return f(*args, editor='NOT_AUTH', **kwargs) try: userToken = flask.request.json['token'] except (KeyError, TypeError): return 'User token required', 401 # check whether token is valid try: idinfo = google.oauth2.id_token.verify_oauth2_token( userToken, google.auth.transport.requests.Request(), None) if idinfo['iss'] not in [ 'accounts.google.com', 'https://accounts.google.com' ]: raise ValueError('Wrong issuer.') except ValueError: return 'Invalid token. Access denied.', 403 # check whether user is in the database email = idinfo['email'].lower() conn = app.db_manager.get_conn() results = database.query( conn, """ SELECT email FROM editors WHERE lower(email) = %s""", email) row = results.fetchone() if row is None: return 'Unknown user. Access denied.', 403 return f(*args, editor=email, **kwargs)
def mark_done(self, ids): result = query( self.conn, """ UPDATE events SET state = 'DONE', upload_time = %s WHERE id = ANY (%s::uuid[]) AND state = 'TRANSCODING' """, datetime.datetime.utcnow(), ids.keys()) return result.rowcount
def get_ids_to_check(self): result = query( self.conn, """ SELECT id, video_id FROM events WHERE state = 'TRANSCODING' """) return {id: video_id for id, video_id in result.fetchall()}
def get_nodes(self): """List address of other wubloaders. This returns a list of the other wubloaders as URL strings. Node URLs are taken from three places. First, the --static-nodes command line argument can be used to provide a list of URLs that are always backfilled from. Node names are infered from the hostnames of the URLs. Second, nodes are read from the file named in the --node-file command line argument. In this file, nodes are listed one per line as name-URL pairs or as just node URLs. Lines starting with '#' are ignored. If only the URL is provided, the node name is taken from the hostname. Third, node names and URLs can be requested from the database given by --node-database. If multiple nodes URLs with the same name are found, only the last is retained and any nodes with names matching the localhost name (given by the --localhost argument) are ignored to try to prevent this node from backfilling from itself.""" nodes = { urlparse.urlparse(node).hostname: node for node in self.static_nodes } if self.node_file is not None: self.logger.info('Fetching list of nodes from {}'.format( self.node_file)) with open(self.node_file) as f: for line in f.readlines(): substrs = line.split() if not len(line) or substrs[0][0] == '#': continue elif len(substrs) == 1: nodes[urlparse.urlparse( substrs[0]).hostname] = substrs[0] else: nodes[substrs[0]] = substrs[1] if self.db_manager is not None: if self.connection is None: self.connection = self.db_manager.get_conn() host = [ s.split('=')[-1] for s in self.connection.dsn.split() if 'host' in s ][0] self.logger.info('Fetching list of nodes from {}'.format(host)) results = database.query( self.connection, """ SELECT name, url FROM nodes WHERE backfill_from""") for row in results: nodes[row.name] = row.url nodes.pop(self.localhost, None) self.logger.info('Nodes fetched: {}'.format(nodes.keys())) return nodes.values()
def reset_row(ident, editor=None): """Clear state and video_link columns and reset state to 'UNEDITED'.""" conn = app.db_manager.get_conn() results = database.query( conn, """ UPDATE events SET state='UNEDITED', error = NULL, video_id = NULL, video_link = NULL, uploader = NULL, editor = NULL, edit_time = NULL, upload_time = NULL WHERE id = %s""", ident) if results.rowcount != 1: return 'Row id = {} not found'.format(ident), 404 logging.info("Row {} reset to 'UNEDITED'".format(ident)) return ''
def manual_link(ident, editor=None): """Manually set a video_link if the state is 'UNEDITED' or 'DONE' and the upload_location is 'manual' or 'youtube-manual'.""" link = flask.request.json['link'] upload_location = flask.request.json.get('upload_location', 'manual') if upload_location == 'youtube-manual': YOUTUBE_URL_RE = r'^https?://(?:youtu\.be/|youtube.com/watch\?v=)([a-zA-Z0-9_-]{11})$' match = re.match(YOUTUBE_URL_RE, link) if not match: return 'Link does not appear to be a youtube.com or youtu.be video link. Try removing any extra query params (after the video id).', 400 video_id, = match.groups() elif upload_location == 'manual': video_id = None else: return 'Upload location must be "manual" or "youtube-manual"', 400 conn = app.db_manager.get_conn() results = database.query( conn, """ SELECT id, state FROM events WHERE id = %s""", ident) old_row = results.fetchone() if old_row is None: return 'Row {} not found'.format(ident), 404 if old_row.state != 'UNEDITED': return 'Invalid state {} for manual video link'.format( old_row.state), 403 now = datetime.datetime.utcnow() results = database.query( conn, """ UPDATE events SET state='DONE', upload_location = %s, video_link = %s, video_id = %s, editor = %s, edit_time = %s, upload_time = %s WHERE id = %s AND state = 'UNEDITED' """, upload_location, link, video_id, editor, now, now, ident) logging.info("Row {} video_link set to {}".format(ident, link)) return ''
def get_videos(self): # Most of the time by getting then re-putting the conn, we'll just use the same # one every time. But if there's an error we won't re-put it so we'll get a new one # the next time. conn = self.dbmanager.get_conn() videos = query( conn, """ SELECT video_id, tags, COALESCE((video_ranges[1]).start, event_start) AS start_time FROM events WHERE state = 'DONE' AND upload_location = ANY (%s) """, self.upload_locations) self.dbmanager.put_conn(conn) return {video.video_id: video for video in videos}
def get_playlist_tags(self): conn = self.dbmanager.get_conn() playlist_tags = { row.playlist_id: [tag.lower() for tag in row.tags] for row in query(conn, "SELECT playlist_id, tags FROM playlists") } self.dbmanager.put_conn(conn) duplicates = set(playlist_tags) & set(self.static_playlist_tags) if duplicates: raise ValueError( "Some playlists are listed in both static and dynamic playlist sources: {}" .format(", ".join(duplicates))) playlist_tags.update(self.static_playlist_tags) return playlist_tags
def rollback_all_owned(self): """Roll back any in-progress jobs that claim to be owned by us, to recover from an unclean shutdown.""" result = query(self.conn, """ UPDATE events SET state = 'EDITED', uploader = NULL WHERE state = 'CLAIMED' AND uploader = %(name)s """, name=self.name) if result.rowcount > 0: self.logger.warning( "Rolled back {} CLAIMED rows for {} - unclean shutdown?". format( result.rowcount, self.name, )) # Also mark any rows in FINALIZED owned by us as errored, these require manual intervention result = query( self.conn, """ UPDATE events SET error = %(error)s WHERE state = 'FINALIZING' AND uploader = %(name)s AND error IS NULL """, name=self.name, error= ("Uploader died during FINALIZING, please determine if video was actually " "uploaded or not and either move to TRANSCODING/DONE and populate video_id or rollback " "to EDITED and clear uploader.")) if result.rowcount > 0: self.logger.error( "Found {} FINALIZING rows for {}, marked as errored".format( result.rowcount, self.name, ))
def get_events(self): """Return the entire events table as a map {id: event namedtuple}""" result = query(self.conn, "SELECT * FROM events") by_id = {} counts = defaultdict(lambda: 0) for row in result.fetchall(): by_id[row.id] = row counts[row.sheet_name, row.category, str(row.poster_moment), row.state, str(bool(row.error))] += 1 # Reach into metric internals and forget about all previous values, # or else any values we don't update will remain as a stale count. event_counts._metrics.clear() for labels, count in counts.items(): event_counts.labels(*labels).set(count) return by_id
def get_row(ident): """Gets the row from the database with id == ident.""" conn = app.db_manager.get_conn() results = database.query( conn, """ SELECT * FROM events WHERE id = %s """, ident) row = results.fetchone() if row is None: return 'Row id = {} not found'.format(ident), 404 assert row.id == ident response = row._asdict() response['id'] = str(response['id']) if response["video_channel"] is None: response["video_channel"] = app.default_channel response["title_prefix"] = app.title_header response["title_max_length"] = MAX_TITLE_LENGTH - len(app.title_header) response["bustime_start"] = app.bustime_start response["upload_locations"] = app.upload_locations # remove any added headers or footers so round-tripping is a no-op if (app.title_header and response["video_title"] is not None and response["video_title"].startswith(app.title_header)): response["video_title"] = response["video_title"][len(app.title_header ):] if (app.description_footer and response["video_description"] is not None and response["video_description"].endswith( app.description_footer)): response["video_description"] = response[ "video_description"][:-len(app.description_footer)] logging.info('Row {} fetched'.format(ident)) def convert(value): if isinstance(value, datetime.datetime): return value.isoformat() if isinstance(value, datetime.timedelta): return value.total_seconds() raise TypeError( f"Can't convert object of type {value.__class__.__name__} to JSON: {value}" ) return json.dumps(response, default=convert)
def list_candidates(self): """Return a list of all available candidates that we might be able to cut.""" # We only accept candidates if they haven't excluded us by whitelist, # and we are capable of uploading to their desired upload location. built_query = sql.SQL(""" SELECT id, {} FROM events WHERE state = 'EDITED' AND (uploader_whitelist IS NULL OR %(name)s = ANY (uploader_whitelist)) AND upload_location = ANY (%(upload_locations)s) """).format( sql.SQL(", ").join(sql.Identifier(key) for key in CUT_JOB_PARAMS)) result = query(self.conn, built_query, name=self.name, upload_locations=self.upload_locations.keys()) return result.fetchall()
def get_row(ident): """Gets the row from the database with id == ident.""" conn = app.db_manager.get_conn() results = database.query(conn, """ SELECT * FROM events WHERE id = %s""", ident) row = results.fetchone() if row is None: return 'Row id = {} not found'.format(ident), 404 assert row.id == ident response = row._asdict() response['id'] = str(response['id']) response = { key: ( value.isoformat() if isinstance(value, datetime.datetime) else value ) for key, value in response.items() } if response["video_channel"] is None: response["video_channel"] = app.default_channel response["title_prefix"] = app.title_header response["title_max_length"] = MAX_TITLE_LENGTH - len(app.title_header) response["bustime_start"] = app.bustime_start response["upload_locations"] = app.upload_locations # remove any added headers or footers so round-tripping is a no-op if ( app.title_header and response["video_title"] is not None and response["video_title"].startswith(app.title_header) ): response["video_title"] = response["video_title"][len(app.title_header):] if ( app.description_footer and response["video_description"] is not None and response["video_description"].endswith(app.description_footer) ): response["video_description"] = response["video_description"][:-len(app.description_footer)] logging.info('Row {} fetched'.format(ident)) return json.dumps(response)
def get_all_rows(): """Gets all rows from the events table from the database""" conn = app.db_manager.get_conn() results = database.query( conn, """ SELECT * FROM events ORDER BY event_start""") rows = [] for row in results: row = row._asdict() row['id'] = str(row['id']) row = { key: (value.isoformat() if isinstance(value, datetime.datetime) else value) for key, value in row.items() } rows.append(row) logging.info('All rows fetched') return json.dumps(rows)
def reset_row(ident, editor=None): """Clear state and video_link columns and reset state to 'UNEDITED'. If force is 'true', it will do so regardless of current state. Otherwise, it will only do so if we know no video has been uploaded (state is UNEDITED, EDITED or CLAIMED) """ force = (flask.request.args.get('force', '').lower() == "true") conn = app.db_manager.get_conn() query = """ UPDATE events SET state='UNEDITED', error = NULL, video_id = NULL, video_link = NULL, uploader = NULL, editor = NULL, edit_time = NULL, upload_time = NULL WHERE id = %s {} """.format( "" if force else "AND state IN ('UNEDITED', 'EDITED', 'CLAIMED')", ) results = database.query(conn, query, ident) if results.rowcount != 1: return 'Row id = {} not found or not in cancellable state'.format( ident), 404 logging.info("Row {} reset to 'UNEDITED'".format(ident)) return ''
def set_error(error): """Common code for the two paths below, for setting an error on the row for humans to see""" try: # Since this error message is just for humans, we don't go to too large # a length to prevent it being put on the row if the row has changed. # We just check its state is still EDITING. # Any successful claim will clear its error. result = query( self.conn, """ UPDATE events SET error = %s WHERE id = %s AND state = 'EDITED' AND error IS NULL """, candidate.id, error) except Exception: self.logger.exception( "Failed to set error for candidate {}, ignoring". format(format_job(candidate))) self.refresh_conn() else: if result.rowcount > 0: assert result.rowcount == 1 self.logger.info( "Set error for candidate {}".format( format_job(candidate)))
def claim_job(self, job): """Update event in DB to say we're working on it. If someone beat us to it, or it's changed, raise CandidateGone.""" # We need to verify all relevant cut params are unchanged, in case they # were updated between verifying the candidate and now. built_query = sql.SQL(""" UPDATE events SET state = 'CLAIMED', uploader = %(name)s, error = NULL WHERE id = %(id)s AND state = 'EDITED' AND {} """).format( # A built AND over all CUT_JOB_PARAMS to check key = %(key)s. # Note the use of IS NOT DISTINCT FROM because key = NULL is false if key is NULL. sql.SQL(' AND ').join( sql.SQL("{} IS NOT DISTINCT FROM {}").format( sql.Identifier(key), sql.Placeholder(key)) for key in CUT_JOB_PARAMS)) try: result = query(self.conn, built_query, name=self.name, **job._asdict()) except Exception: # Rather than retry on failure here, just assume someone else claimed it in the meantime self.logger.exception( "Error while claiming job {}, aborting claim".format( format_job(job))) self.refresh_conn() self.wait(self.ERROR_RETRY_INTERVAL) raise CandidateGone if result.rowcount == 0: self.logger.info("Failed to claim job {}".format(format_job(job))) raise CandidateGone self.logger.info("Claimed job {}".format(format_job(job))) assert result.rowcount == 1
def sync_row(self, worksheet, row_index, row, event): """Take a row dict and an Event from the database (or None if id not found) and take whatever action is required to sync them, ie. writing to the database or sheet.""" if event is None: # No event currently in DB, if any field is non-empty, then create it. # Otherwise ignore it. if not any(row[col] for col in self.input_columns): return # Only generate row when needed (unless it's already there) # Originally we would allocate rows on first sync, but this led to rate limiting issues. if row['id'] is None: if self.allocate_ids: row['id'] = uuid.uuid4() logging.info("Allocating id for row {!r}:{} = {}".format( worksheet, row_index, row['id'])) self.sheets.write_value( self.sheet_id, worksheet, row_index, self.column_map['id'], str(row['id']), ) else: logging.warning( "Row {!r}:{} has no valid id, skipping".format( worksheet, row_index)) return logging.info("Inserting new event {}".format(row['id'])) # Insertion conflict just means that another sheet sync beat us to the insert. # We can ignore it. insert_cols = ['id', 'sheet_name'] + self.input_columns built_query = sql.SQL(""" INSERT INTO events ({}) VALUES ({}) ON CONFLICT DO NOTHING """).format( sql.SQL(", ").join(sql.Identifier(col) for col in insert_cols), sql.SQL(", ").join( sql.Placeholder(col) for col in insert_cols), ) query(self.conn, built_query, sheet_name=worksheet, **row) rows_found.labels(worksheet).inc() rows_changed.labels('insert', worksheet).inc() self.mark_modified(worksheet) return rows_found.labels(worksheet).inc() # If no database error, but we have parse errors, indicate they should be displayed. if event.error is None and row['_parse_errors']: event = event._replace(error=", ".join(row['_parse_errors'])) # Update database with any changed inputs changed = [ col for col in self.input_columns if row[col] != getattr(event, col) ] if changed: logging.info("Updating event {} with new value(s) for {}".format( row['id'], ', '.join(changed))) built_query = sql.SQL(""" UPDATE events SET {} WHERE id = %(id)s """).format( sql.SQL(", ").join( sql.SQL("{} = {}").format(sql.Identifier(col), sql.Placeholder(col)) for col in changed)) query(self.conn, built_query, **row) rows_changed.labels('input', worksheet).inc() self.mark_modified(worksheet) # Update sheet with any changed outputs format_output = lambda v: '' if v is None else v # cast nulls to empty string changed = [ col for col in self.output_columns if row[col] != format_output(getattr(event, col)) ] if changed: logging.info( "Updating sheet row {} with new value(s) for {}".format( row['id'], ', '.join(changed))) for col in changed: self.sheets.write_value( self.sheet_id, worksheet, row_index, self.column_map[col], format_output(getattr(event, col)), ) rows_changed.labels('output', worksheet).inc() self.mark_modified(worksheet) # Set edit link if marked for editing and start/end set. # This prevents accidents / clicking the wrong row and provides # feedback that sheet sync is still working. # Also clear it if it shouldn't be set. edit_link = self.edit_url.format( row['id']) if row['marked_for_edit'] == '[+] Marked' else '' if row['edit_link'] != edit_link: logging.info("Updating sheet row {} with edit link {}".format( row['id'], edit_link)) self.sheets.write_value( self.sheet_id, worksheet, row_index, self.column_map['edit_link'], edit_link, ) self.mark_modified(worksheet)
def update_row(ident, editor=None): new_row = flask.request.json """Updates row of database with id = ident with the edit columns in new_row.""" state_columns = ['state', 'uploader', 'error', 'video_link'] #these have to be set before a video can be set as 'EDITED' non_null_columns = [ 'upload_location', 'video_start', 'video_end', 'video_channel', 'video_quality', 'video_title', 'video_description' ] edit_columns = non_null_columns + ['allow_holes', 'uploader_whitelist'] #check vital edit columns are in new_row wanted = set(non_null_columns + ['state']) missing = wanted - set(new_row) if missing: return 'Fields missing in JSON: {}'.format(', '.join(missing)), 400 #get rid of irrelevant columns extras = set(new_row) - set(edit_columns + state_columns) for extra in extras: del new_row[extra] # Include headers and footers if 'video_title' in new_row: new_row['video_title'] = app.title_header + new_row['video_title'] if 'video_description' in new_row: new_row['video_description'] += app.description_footer #validate title length if len(new_row['video_title']) > MAX_TITLE_LENGTH: return 'Title must be {} characters or less, including prefix'.format( MAX_TITLE_LENGTH), 400 #validate start time is less than end time if new_row['video_start'] > new_row['video_end']: return 'Video Start must be less than Video End.', 400 conn = app.db_manager.get_conn() #check a row with id = ident is in the database results = database.query( conn, """ SELECT id, state FROM events WHERE id = %s""", ident) old_row = results.fetchone() if old_row is None: return 'Row {} not found'.format(ident), 404 assert old_row.id == ident if old_row.state not in ['UNEDITED', 'EDITED', 'CLAIMED']: return 'Video already published', 403 # handle state columns if new_row['state'] == 'EDITED': missing = [] for column in non_null_columns: if not new_row[column]: missing.append(column) if missing: return 'Fields {} must be non-null for video to be cut'.format( ', '.join(missing)), 400 if len(new_row.get('video_title', '')) <= len(app.title_header): return 'Video title must not be blank', 400 if len(new_row.get('video_description', '')) <= len( app.description_footer): return 'Video description must not be blank. If you have nothing else to say, just repeat the title.', 400 elif new_row['state'] != 'UNEDITED': return 'Invalid state {}'.format(new_row['state']), 400 new_row['uploader'] = None new_row['error'] = None new_row['editor'] = editor new_row['edit_time'] = datetime.datetime.utcnow() # actually update database build_query = sql.SQL(""" UPDATE events SET {} WHERE id = %(id)s AND state IN ('UNEDITED', 'EDITED', 'CLAIMED')""").format( sql.SQL(", ").join( sql.SQL("{} = {}").format( sql.Identifier(column), sql.Placeholder(column), ) for column in new_row.keys())) result = database.query(conn, build_query, id=ident, **new_row) if result.rowcount != 1: return 'Video likely already published', 403 logging.info('Row {} updated to state {}'.format(ident, new_row['state'])) return ''
def update_row(ident, editor=None): """Updates row of database with id = ident with the edit columns in new_row.""" new_row = flask.request.json override_changes = new_row.get('override_changes', False) state_columns = ['state', 'uploader', 'error', 'video_link'] # These have to be set before a video can be set as 'EDITED' non_null_columns = [ 'upload_location', 'video_ranges', 'video_transitions', 'video_channel', 'video_quality', 'video_title', 'video_description', 'video_tags', ] edit_columns = non_null_columns + ['allow_holes', 'uploader_whitelist'] sheet_columns = [ 'sheet_name', 'event_start', 'event_end', 'category', 'description', 'notes', 'tags', ] # Check vital edit columns are in new_row wanted = set(non_null_columns + ['state'] + sheet_columns) missing = wanted - set(new_row) if missing: return 'Fields missing in JSON: {}'.format(', '.join(missing)), 400 # Get rid of irrelevant columns extras = set(new_row) - set(edit_columns + state_columns + sheet_columns) for extra in extras: del new_row[extra] # Include headers and footers if 'video_title' in new_row: new_row['video_title'] = app.title_header + new_row['video_title'] if 'video_description' in new_row: new_row['video_description'] += app.description_footer # Validate youtube requirements on title and description if len(new_row['video_title']) > MAX_TITLE_LENGTH: return 'Title must be {} characters or less, including prefix'.format( MAX_TITLE_LENGTH), 400 if len(new_row['video_description']) > MAX_DESCRIPTION_LENGTH: return 'Description must be {} characters or less, including footer'.format( MAX_DESCRIPTION_LENGTH), 400 for char in ['<', '>']: if char in new_row['video_title']: return 'Title may not contain a {} character'.format(char), 400 if char in new_row['video_description']: return 'Description may not contain a {} character'.format( char), 400 # Validate and convert video ranges and transitions. num_ranges = len(new_row['video_ranges']) if num_ranges == 0: return 'Ranges must contain at least one range', 400 if len(new_row['video_transitions']) != num_ranges - 1: return 'There must be exactly {} transitions for {} ranges'.format( num_ranges - 1, num_ranges, ) for start, end in new_row['video_ranges']: if start > end: return 'Range start must be less than end', 400 # We need these to be tuples not lists for psycopg2 to do the right thing, # but since they come in as JSON they are currently lists. new_row['video_ranges'] = [ tuple(range) for range in new_row['video_ranges'] ] new_row['video_transitions'] = [ None if transition is None else tuple(transition) for transition in new_row['video_transitions'] ] conn = app.db_manager.get_conn() # Check a row with id = ident is in the database built_query = sql.SQL(""" SELECT id, state, {} FROM events WHERE id = %s """).format(sql.SQL(', ').join(sql.Identifier(key) for key in sheet_columns)) results = database.query(conn, built_query, ident) old_row = results.fetchone()._asdict() if old_row is None: return 'Row {} not found'.format(ident), 404 assert old_row['id'] == ident if old_row['state'] not in ['UNEDITED', 'EDITED', 'CLAIMED']: return 'Video already published', 403 # check whether row has been changed in the sheet since editing has begun changes = '' for column in sheet_columns: if isinstance(old_row[column], datetime.datetime): old_row[column] = old_row[column].isoformat() def normalize(value): if isinstance(value, list): return sorted(map(normalize, value)) if value is None: return None return value.lower().strip() if normalize(new_row[column]) != normalize(old_row[column]): changes += '{}: {} => {}\n'.format(column, new_row[column], old_row[column]) if changes and not override_changes: return 'Sheet columns have changed since editing has begun. Please review changes\n' + changes, 409 # handle state columns if new_row['state'] == 'EDITED': missing = [] for column in non_null_columns: if new_row[column] is None: missing.append(column) if missing: return 'Fields {} must be non-null for video to be cut'.format( ', '.join(missing)), 400 if len(new_row.get('video_title', '')) <= len(app.title_header): return 'Video title must not be blank', 400 if len(new_row.get('video_description', '')) <= len( app.description_footer): return 'Video description must not be blank. If you have nothing else to say, just repeat the title.', 400 elif new_row['state'] != 'UNEDITED': return 'Invalid state {}'.format(new_row['state']), 400 new_row['uploader'] = None new_row['error'] = None new_row['editor'] = editor new_row['edit_time'] = datetime.datetime.utcnow() # actually update database build_query = sql.SQL(""" UPDATE events SET {} WHERE id = %(id)s AND state IN ('UNEDITED', 'EDITED', 'CLAIMED')""").format( sql.SQL(", ").join( sql.SQL("{} = {}").format( sql.Identifier(column), database.get_column_placeholder(column), ) for column in new_row.keys() if column not in sheet_columns)) result = database.query(conn, build_query, id=ident, **new_row) if result.rowcount != 1: return 'Video likely already published', 403 logging.info('Row {} updated to state {}'.format(ident, new_row['state'])) return ''