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]