def _load_collection (self):
"""Load the collection from the database."""
assert(type(self['id']) == int)
if self._db.get('id'):
sql = "SELECT * FROM collections WHERE id=%d;" % self['id']
elif self._db.get('name'):
sql = "SELECT * FROM collections "\
"WHERE name='%s' ORDER BY id DESC "\
"LIMIT 1;" % self['name']
else:
raise Error.Invalid
q = Query(sql)
if not q['id']:
self['id'] = None
return False
for key in q.get_headers():
self._db[key] = q[key][0]
sql = "SELECT id FROM assets " +\
"WHERE collections_id=%d;" % self['id']
q = Query(sql)
if len(q):
self._assets = [q[x]['id'] for x in q]
def _lookup_file (self):
sql = "SELECT * FROM files "+\
"WHERE id=%d;" % self['id']
q = Query(sql)
if len(q):
for x in q.get_headers():
self._file[x] = q[x][0]
def get_files_by_flag (flag):
q = "SELECT * "\
"FROM files "\
"WHERE queue_flag = %s;" % flag
query = Query(q)
if len(query) == 0:
return []
files = []
for x in query:
f = {}
for key in query.get_headers():
f[key]= query[x][key]
files.append(f)
return files
def get_user (username = None):
if username == None:
username = CTK.cookie['user']
if not username:
return {}
q = "SELECT * FROM users WHERE username='******';" % username
query = Query(q)
user = {}
try:
for key in query.get_headers():
user[key] = query[0][key]
except:
pass
return user
def get_files_by_name (filenames):
assert type(filenames) == list
names = ','.join(["'%s'"% f for f in filenames])
q = "SELECT * "\
"FROM files "\
"WHERE filename IN (%s);" % names
query = Query(q)
if len(query) == 0:
return []
files = []
for x in query:
f = {}
for key in query.get_headers():
if query[x][key]:
f[key]= query[x][key]
files.append(f)
return files
def deauth(self):
user = CTK.cookie['user']
vali = CTK.cookie['validation']
if not user or not vali:
return False
q = ("DELETE FROM sessions WHERE " +
" user = '******' AND validation = '%(vali)s';") % (locals())
Query(q)
return True
def get_user_roles(user=None):
if not user:
user = CTK.cookie['user']
q = ("SELECT roles_id AS roles FROM users "
"JOIN profiles ON users.profile_id = profiles.id "
"JOIN profiles_has_roles "
"ON profiles.id = profiles_has_roles.profiles_id "
"WHERE users.username = '******';" % user)
query = Query(q)
if len(query):
return query['roles']
return []
def get_license(license_id):
q = "SELECT id, name, description "\
"FROM licenses WHERE id=%(license_id)s;" % locals()
query = Query(q)
if len(query) != 1:
return None
ret = {
'id': license_id,
'name': query['name'][0],
'description': query['description'][0]
}
return ret
def __check_parts(parts_list):
if not parts_list:
return
numeric_list = Validations.is_numeric_list(parts_list)
numeric_list = list(set(numeric_list))
q = "SELECT COUNT(*) FROM assets WHERE id IN (%s);" % (
','.join(numeric_list))
re = Query(q)
if re['COUNT(*)'][0] != len(numeric_list):
raise ValueError, "Hay partes invalidas."
return parts_list
def __get_new_targets_table(format_id):
q_formats = "SELECT id, format FROM formats WHERE id != %(format_id)s;" % locals(
)
q_current = "SELECT id,format FROM transcode_targets "\
"JOIN formats ON target_id = id WHERE source_id = '%(format_id)s';" % locals()
q_name = "SELECT * FROM formats WHERE id = %(format_id)s;" % locals()
formats_query = Query(q_formats)
current_query = Query(q_current)
format_name = Query(q_name)['format'][0]
formats, current_targets = {}, []
for x in formats_query:
formats[formats_query[x]['format']] = formats_query[x]['id']
if len(current_query) > 0:
current_targets = [current_query[x]['format'] for x in current_query]
targets = [(formats[x], x) for x in formats.keys()
if x not in current_targets]
if format_name in Format.AV:
targets = [(value, label) for value, label in targets
if label in Format.AV]
else:
targets = [(value, label) for value, label in targets
if label in Format.IMG]
if len(targets) == 0:
return None
options = [('', '--')] + targets
table = CTK.PropsAuto('%s/edit/apply' % LOCATION)
table.AddConstant('formatid', format_id)
table.Add('Objetivo', CTK.Combobox({'name': 'target'}, options),
'Destino de la transcodificación')
return table
class ComboboxSQL(CTK.Combobox):
def __init__(self, props={}, sql=None, custom=None):
# SQL
self.SQL_read = sql
self.SQL_result = Query(sql)
# Build options list
options = []
if custom:
assert type(custom) == tuple
options.append(custom)
options += [tuple(x) for x in self.SQL_result.get_values()]
# Init widget
CTK.Combobox.__init__(self, props, options)
def get_types ():
q = "SELECT id, type "\
"FROM asset_types;" % locals()
query = Query(q)
if not len(query):
return None
ret = []
for x in query:
d={'id': query[x]['id'],
'name': query[x]['type']}
ret.append(d)
return ret
def _load_asset (self):
assert(type(self['id']) == int)
sql = "SELECT assets.* FROM assets "\
"LEFT JOIN files ON assets.id=files.id "\
"WHERE assets.id=%d" % self['id']
q = Query(sql)
if not q['id']:
self['id'] = None
return False
for key in q.get_headers():
self._db[key] = q[key][0]
self._lookup_parent()
self._lookup_children()
self._lookup_versions()
self._lookup_formats()
self._lookup_parts()
self._lookup_replacements()
self._lookup_collection()
self._lookup_file()
self._compile_tags()
def _lookup_versions (self):
# Versions
#
self._versions = [{'version': self['version'],
'id': self['id']}]
sql = "SELECT derivative_id, version "+\
"FROM view_asset_versions " +\
"WHERE source_id=%d;" % self['id']
q = Query(sql)
if len(q):
self._versions += [{'version': q[x]['version'],
'id': q[x]['derivative_id']}
for x in q]
def get_profiles ():
q = ('SELECT id, name, GROUP_CONCAT(roles_id) AS roles '
'FROM profiles '
'JOIN profiles_has_roles ON profiles_id=id '
'GROUP by profiles.id;')
query = Query(q)
ret = []
for x in query:
roles = query[x]['roles'].split(',')
roles = [int(r) for r in roles]
d={'id': query[x]['id'],
'name': query[x]['name'],
'roles': roles}
ret.append(d)
return ret
def _check_asset_usage(self):
"""Return number of relations of an asset"""
asset_id = self._asset['id']
q = "SELECT SUM(total) AS total FROM ("\
"SELECT COUNT(*) AS total "\
"FROM children WHERE parent_id='%(asset_id)d' UNION " \
"SELECT COUNT(*) AS total FROM asset_versions " \
"WHERE source_id ='%(asset_id)d' UNION " \
"SELECT COUNT(*) AS total FROM parts " \
"WHERE source_id='%(asset_id)d' UNION " \
"SELECT COUNT(*) AS total FROM replacements " \
"WHERE replacee_id='%(asset_id)d') AS relations;" % locals()
re = Query(q)
return re['total'][0]
def is_logged_in(self):
user = CTK.cookie['user']
vali = CTK.cookie['validation']
now = time.strftime("%Y-%m-%d %H:%M:%S", time.gmtime())
if not user or not vali:
return False
q = ("SELECT COUNT(*) FROM sessions WHERE user = '******' AND " +
"validation = '%(vali)s' AND expiration > '%(now)s';") % (
locals())
if Query(q)['COUNT(*)'][0] != 1:
return False
return True
def __get_new_format_form():
q = 'SELECT format FROM formats;'
query = Query(q)
current = []
if len(query) > 0:
current = [query[x]['format'] for x in query]
options = [('', '--')]
options += [(x, x) for x in Format.FORMATS if x not in current]
table = CTK.PropsTable()
table.Add('Formato', CTK.Combobox({'name': 'format'}, options),
'Nombre del formato')
form = CTK.Submitter("%s/new/apply" % LOCATION)
form += table
return form
def get_asset_collections (asset_ids):
"""Get dictionary with pairs asset_id:collection_id"""
d = {}
ids = ','.join(map(str,asset_ids))
if len(ids)==0:
return d
sql = ('SELECT id,collections_id AS col_id '
'FROM assets '
'WHERE id IN (%s);' % ids)
q = Query(sql)
for x in q:
key, value = q['id'][x], q['col_id'][x]
d[key] = value
return d
def default(message=None):
# Authentication
fail = Auth.assert_is_role(Role.ROLE_ADMIN)
if fail: return fail
# List of types
q = ("SELECT id,type FROM asset_types;")
types = Query(q)
table = CTK.Table()
title = [CTK.RawHTML(x) for x in ['Tipo de activo']]
table[(1, 1)] = title
table.set_header(row=True, num=1)
page = Page.Default()
n = 2
for asset_type in types:
type_id = types[asset_type]['id']
name = types[asset_type]['type']
# Delete asset_type link
dialog = CTK.Dialog({
'title': "¿Quieres eliminar %s?" % (name),
'autoOpen': False
})
dialog += CTK.RawHTML(NOTE_DELETE)
dialog.AddButton('Cancelar', "close")
dialog.AddButton('Borrar', "%s/del/%s" % (LOCATION, type_id))
linkdel = LINK_JS_ON_CLICK % (dialog.JS_to_show(), "Borrar")
linkname = LINK_HREF % ("%s/edit/%s" % (LOCATION, type_id), name)
table[(n, 1)] = [CTK.RawHTML(linkname), CTK.RawHTML(linkdel)]
page += dialog
n += 1
# Render
page += CTK.RawHTML("<h1>%s: Tipos</h1>" % ADMIN_LINK)
page += table
page += CTK.RawHTML(LINK_HREF %
('%s/new' % LOCATION, 'Añadir tipo'))
if message:
page += Message(message)
return page.Render()
def get_formats():
q = "SELECT * "\
"FROM formats;"
query = Query(q)
if len(query) == 0:
return None
formats = []
for x in query:
f = {
'id': query[x]['id'],
'name': query[x]['format'],
'lossy_flag': query[x]['lossy_flag']
}
formats.append(f)
return formats
def default(message=None):
# Authentication
fail = Auth.assert_is_role(Role.ROLE_ADMIN)
if fail: return fail
# List of formats
q = ("SELECT * FROM formats;")
formats = Query(q)
table = CTK.Table()
title = [CTK.RawHTML(x) for x in ['Nombre', 'Pérdida']]
table[(1, 1)] = title
table.set_header(row=True, num=1)
page = Page.Default()
n = 2
for format in formats:
format_id = str(formats[format]['id'])
formatname = formats[format]['format']
lossy = ['No', 'Sí'][formats[format]['lossy_flag']]
# Delete format link
dialog = __get_del_dialog("del/%s" % (format_id), NOTE_DELETE)
linkdel = LINK_JS_ON_CLICK % (dialog.JS_to_show(), "Borrar")
linkname = LINK_HREF % ("%s/edit/%s" %
(LOCATION, format_id), formatname)
table[(n, 1)] = [
CTK.RawHTML(linkname),
CTK.RawHTML(lossy),
CTK.RawHTML(linkdel)
]
page += dialog
n += 1
# Render
page += CTK.RawHTML("<h1>%s: Administración de Formatos</h1>" %
(ADMIN_LINK))
page += table
page += CTK.RawHTML("<h2>Añadir formato</h2>")
page += __get_new_format_form()
if message:
page += Message(message)
return page.Render()
def new_format_apply():
# Authentication
fail = Auth.assert_is_role(Role.ROLE_ADMIN)
if fail: return fail
# Update the database
fields = []
values = []
for key in ['format']:
if key in CTK.post:
fields.append(key)
values.append("'%s'" % (CTK.post[key]))
q = "INSERT INTO formats (%s) VALUES (%s);" % (", ".join(fields),
", ".join(values))
query = Query(q)
try:
format_id = query.result[0]['INSERT_ID']
except TypeError, KeyError:
return {'ret': "error"}
def user_has_role(role, user=None, check_auth=True):
if check_auth: # This is disabled on unit tests
# Is he logged?
auth = Auth.Auth()
if not auth.is_logged_in():
return False
if not user:
user = CTK.cookie['user']
q = (
"SELECT COUNT(*) " +
"FROM users JOIN profiles ON users.profile_id = profiles.id " +
" JOIN profiles_has_roles ON profiles.id = profiles_has_roles.profiles_id "
+
"WHERE users.username = '******' AND profiles_has_roles.roles_id = %(role)d;"
) % (locals())
if Query(q)['COUNT(*)'][0] != 1:
return False
return True
def new_profile_apply():
# Authentication
fail = Auth.assert_is_role(Role.ROLE_ADMIN)
if fail: return fail
name = CTK.post['name']
desc = CTK.post['description']
q = ("INSERT INTO profiles (name, description) VALUES ('%s', '%s');") % (
name, desc)
query_check_success(q)
q = "SELECT id FROM profiles WHERE name = '%s';" % (name)
re = Query(q)
new_id = re['id'][0]
if not new_id:
return {'ret': "error"}
q = ("INSERT INTO profiles_has_roles VALUES (%s, %d);") % (
new_id, Role.ROLE_CONSUMER)
query_check_success(q)
return {'ret': "ok", 'redirect': "/admin/profile/%s" % (new_id)}
def del_profile():
# Authentication
fail = Auth.assert_is_role(Role.ROLE_ADMIN)
if fail: return fail
# Target profile
profile_id = CTK.request.url.split('/')[4]
# Check whether it can be deleted
q = "SELECT COUNT(*) FROM users WHERE profile_id = %(profile_id)s;" % (
locals())
re = Query(q)
usage = re['COUNT(*)'][0]
if usage != 0:
return default("El perfil está en uso por %d usuarios" % (usage))
# Delete it
q = "DELETE FROM profiles_has_roles WHERE profiles_id = %(profile_id)s;" % (
locals())
q += "DELETE FROM profiles WHERE id = %(profile_id)s;" % (locals())
ok = query_check_success(q)
return CTK.HTTP_Redir('/admin/profile/')
def get_user_bookmarks(user_id):
sql = "SELECT assets_id FROM bookmarks " \
"WHERE users_id='%s';" % (str(user_id))
q = Query(sql)
return q['assets_id']
def get_usage_system ():
q = "SELECT IFNULL(SUM(size),0) AS size, COUNT(size) AS files FROM files;"
query = Query(q)
return {'files': int(query['files'][0]),
'size': int(query['size'][0])}
def default(message=None):
# Authentication
fail = Auth.assert_is_role(Role.ROLE_ADMIN)
if fail: return fail
# List of profiles
q = "SELECT * FROM profiles;"
profiles = Query(q)
table = CTK.Table()
title = [CTK.RawHTML(x) for x in ['Profile', 'Descripcion', 'Role']]
table[(1, 1)] = title
table.set_header(row=True, num=1)
page = Page.Default()
n = 2
for profile in profiles:
# Fetch data
profile_id = profiles[profile]['id']
profile_name = profiles[profile]['name']
profile_desc = profiles[profile]['description']
# Role
q = "SELECT * FROM profiles_has_roles WHERE profiles_id='%s';" % (
profile_id)
roles = Query(q)
profile_roles_str = ', '.join(
[Role.role_to_name(roles[x]['roles_id']) for x in roles])
# Delete profile
dialog = CTK.Dialog({
'title': "Eliminando profile %s?" % (profile_name),
'autoOpen': False
})
dialog += CTK.RawHTML(NOTE_DELETE)
dialog.AddButton('Cancelar', "close")
dialog.AddButton('Borrar', "/admin/profile/del/%s" % (profile_id))
del_img = '<img src="/CTK/images/del.png" alt="Borrar"'
linkdel = LINK_JS_ON_CLICK % (dialog.JS_to_show(), del_img)
linkname = LINK_HREF % ("/admin/profile/%s" % profile_id, profile_name)
table[(n, 1)] = [
CTK.RawHTML(linkname),
CTK.RawHTML(profile_desc),
CTK.RawHTML(profile_roles_str),
CTK.RawHTML(linkdel)
]
page += dialog
n += 1
# Render
page += CTK.RawHTML("<h1>%s: Administración de Profiles</h1>" %
(ADMIN_PREFIX))
page += CTK.RawHTML(LINK_HREF %
('/admin/profile/new', 'Añadir profile'))
page += table
if message:
page += Message(message)
return page.Render()
def edit_profile():
# Authentication
fail = Auth.assert_is_role(Role.ROLE_ADMIN)
if fail: return fail
# Table
profile_id = CTK.request.url.split('/')[3]
q = ("SELECT *, GROUP_CONCAT(roles_id) " +
" FROM profiles, profiles_has_roles " +
" WHERE profiles.id = %(profile_id)s AND " +
" profiles_has_roles.profiles_id = %(profile_id)s " +
" GROUP by profiles.id;") % (locals())
p = Query(q)
profile_roles = [int(x) for x in p['GROUP_CONCAT(roles_id)'][0].split(',')]
table = CTK.PropsAuto('/admin/profile/apply')
table.AddConstant('profile_id', profile_id)
table.Add('Nombre', CTK.TextField({
'name': "name",
'value': p['name'][0]
}), "Nombre del profile")
table.Add(
'Description',
CTK.TextField({
'name': "description",
'value': p['description'][0]
}), "Descripcion del profile")
table.Add(
'Role: Administrador',
CTK.Checkbox({
'name': "role_" + str(Role.ROLE_ADMIN),
'checked': '01'[Role.ROLE_ADMIN in profile_roles]
}), "Tiene permisos de administrador")
table.Add(
'Role: Ingestador',
CTK.Checkbox({
'name': "role_" + str(Role.ROLE_UPLOADER),
'checked': '01'[Role.ROLE_UPLOADER in profile_roles]
}), "Puede dar de alta nuevos activos en el sistema")
table.Add(
'Role: Editor',
CTK.Checkbox({
'name': "role_" + str(Role.ROLE_EDITOR),
'checked': '01'[Role.ROLE_EDITOR in profile_roles]
}), "Puede editar los activos que ya existen en el sistema")
table.Add(
'Role: Publicador',
CTK.Checkbox({
'name': "role_" + str(Role.ROLE_PUBLISHER),
'checked': '01'[Role.ROLE_PUBLISHER in profile_roles]
}), "Puede publicar activos")
table.Add(
'Role: Consumidor',
CTK.Checkbox({
'name': "role_" + str(Role.ROLE_CONSUMER),
'checked': '01'[Role.ROLE_CONSUMER in profile_roles]
}), "El usuario puede consumir, ver, y descargar activos")
page = Page.Default()
page += CTK.RawHTML("<h1>%s: Edición profile</h1>" %
(PROFILES_PREFIX))
page += table
return page.Render()
def get_collections():
q = "SELECT collections.id, name, creator_id, username "\
"FROM collections JOIN users ON creator_id=users.id;"
collections = Query(q)
return collections
def add(self):
"""Feed asset to platform"""
sql_columns = []
sql_values = []
asset = self._asset
db = asset._db
if not db.has_key('published_flag'):
db['published_flag'] = 0
keys = DB_KEYS[:]
keys.remove('id')
for key in keys:
if db.get(key):
sql_columns.append(key)
sql_values.append("'%s'" % db[key])
q = "INSERT INTO assets (%s) VALUES (%s);" \
% (','.join(sql_columns), ','.join(sql_values))
query = Query(q)
try:
asset['id'] = query.result[0]['INSERT_ID']
except KeyError:
return False
q = ''
if asset._parent_id:
q += "INSERT INTO children VALUES (%d, %d);" % \
(asset._parent_id, asset['id'])
q += "INSERT INTO asset_versions VALUES (%d, %d);" % \
(asset._parent_id, asset['id'])
replaces = asset._replacements.get('replaces')
if replaces:
for x in replaces:
q += "INSERT INTO replacements VALUES (%d, %d);" % \
(asset['id'], x)
# Populate attachment info
if asset._file:
asset._file['id'] = asset['id']
keys, values = [], []
for key, value in asset._file.items():
if value:
keys.append(key)
values.append("'%s'" % str(value))
q += ("INSERT INTO files (%s) VALUES (%s);" %
(','.join(keys), ','.join(values)))
try:
for x in asset._parts['has_parts_of']:
q += "INSERT INTO parts VALUES (%d, %d);" % \
(int(x), asset['id'])
except (KeyError, TypeError):
pass
# Populate 'asset_formats' table
#
insert = []
for format in asset._formats:
if len(format) < 3 and format.get('source'):
# Insert incomplete references and remove them from
# memory in case the object is reused. It should be
# complete once it is reloaded
insert.append(format)
for format in insert:
q += ("INSERT INTO asset_formats (source_id, target_id) "
"VALUES (%d,%d);" % (format['source'], asset['id']))
asset._formats.remove(format)
# Perform
if q:
q = "START TRANSACTION; %s COMMIT;" % q
if not transaction_check_success(q):
# Delete previous commit
q = "DELETE FROM assets WHERE assets.id == '%s'" % asset['id']
query = Query(q)
return False
acl = ACL(self.params)
acl.set_default_asset_acl(self._asset)
return True
def unset_flag(flag):
q = "UPDATE files SET queue_flag=0 WHERE queue_flag=%s;" % (flag)
query = Query(q)
def transcode():
# Authentication
fail = Auth.assert_is_role(Role.ROLE_PUBLISHER)
if fail: return fail
asset_id = CTK.request.url.split('/')[-1]
asset = Asset(asset_id)
if not asset._file.get('filename'):
return error_page(NOTE_NO_FILE)
format_id = asset._file.get('formats_id')
if not format_id:
info = get_info(asset._file.get('filename'))
format_id = info.get('formats_id', None)
if not format_id:
return error_page(NOTE_NO_FORMAT)
sql = "SELECT * FROM transcode_targets "\
"JOIN formats ON target_id=formats.id "\
"WHERE source_id = %(format_id)s;" %(locals())
q = Query(sql)
if not len(q):
return error_page(NOTE_NO_TRANSCODING)
table = CTK.Table()
header = ['Formato', 'Convertir', 'Calidad de conversión']
table[(1, 1)] = [CTK.RawHTML(x) for x in header]
table.set_header(row=True, num=1)
n = 2
for x in q:
if q[x]['lossy_flag']: text = LOSSY
else: text = LOSSLESS
opts = {
'name': 'target_id_%s' % str(q[x]['target_id']),
'class': 'required',
'checked': 0
}
fields = [
CTK.RawHTML(q[x]['format'].upper()),
CTK.Checkbox(opts),
CTK.RawHTML(text)
]
table[(n, 1)] = fields
n += 1
form = CTK.Submitter("%s/apply" % LOCATION)
form += table
form += CTK.HiddenField({'name': 'asset_id', 'value': str(asset_id)})
form += CTK.SubmitterButton('Enviar')
page = Page.Default()
page += CTK.RawHTML('<h1>%s</h1>' % MENU_LINK)
page += form
return page.Render()
def get_users():
q = "SELECT id, username FROM users;"
query = Query(q)
return [(query['id'][x], query['username'][x]) for x in query]
