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