#!/usr/bin/env python # Copyright (C) 2012-2013 Aleksey Lim # # This program is free software: you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation, either version 3 of the License, or # (at your option) any later version. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program. If not, see . import os import sys import time import getpass import hashlib import traceback from cStringIO import StringIO from os.path import join import MySQLdb as mdb from sugar_network import db, client, model, toolkit from sugar_network.node import data_root from sugar_network.node.slave import SlaveRoutes from sugar_network.node.routes import load_bundle from sugar_network.toolkit import util, licenses, application, Option DOWNLOAD_URL = 'http://download.sugarlabs.org/activities' ASLO_AUTHOR = {'d26cef70447160f31a7497cc0320f23a4e383cc3': { 'order': 0, 'role': 1, 'name': 'Activity Library', }} ACTIVITIES_PATH = '/upload/activities' SUGAR_GUID = 'sugar' SN_GUID = 'sugar-network' PACKAGES_GUID = 'packages' CATEGIORIES_TO_TAGS = { 'Search & Discovery': 'discovery', 'Documents': 'productivity', 'Chat, mail and talk': 'communication', 'Programming': 'programming', 'Maps & Geography': 'geography', 'Media players': 'media', 'Teacher tools': 'teacher', 'Games': 'games', 'Media creation': 'media', 'Maths & Science': 'science', 'News': 'news', 'Utilities': 'tools', 'Web': 'web', 'Communications and Language': 'literacy', } MISNAMED_LICENSES = { ('artistic', '2.0'): 'Artistic 2.0', ('cc-by-sa',): 'CC-BY-SA', ('creative', 'share', 'alike'): 'CC-BY-SA', ('apache',): 'ASL 2.0', } IGNORE_ADDONS = frozenset([ 'net.gcompris.', # Exclude per-activity GC addons 'org.laptop.GmailActivity', # Licensing question 'com.batovi.SuperVampireNinjaZero', # Licensing question 'org.sugarlabs.SugarNetworkActivity', ]) IGNORE_VERSIONS = frozenset([ 30410, # Bad version number 30906, # No spec 29269, # No file 29311, # No file 29464, # No file 30074, # No file 30234, # No file 31809, # rsvg fails to load icon 29559, # Bad license 29806, # Bad license 29815, # Bad license 31808, # Bad license 29982, # Bad license 30104, # Bad license 30436, # Bad license 30752, # Bad license 30414, # Bad license 30703, # Bad license 31164, # Bad bundle_id 31512, # Bad license ]) IGNORE_PREVIEWS = frozenset([ 475, # Malformed PNG 476, # Malformed PNG ]) LICENSES_MAP = { 'org.laptop.x2o': ['GPLv2+'], 'org.wesnoth.Wesnoth': ['GPLv2'], 'org.laptop.Micropolis': ['GPLv3'], 'org.gvr.olpc.GvRng': ['GPLv2'], 'org.laptop.bridge': ['GPLv3'], 'org.laptop.pippy.Lines': ['GPLv2+'], 'org.laptop.pippy.Snow': ['GPLv2+'], 'org.laptop.pippy.Bounce': ['GPLv2+'], 'org.laptop.xolympics': ['GPLv3'], 'org.laptop.FirefoxActivity': ['MPLv2.0', 'GPLv2', 'LGPLv2'], 'com.mediamason.geoquiz': ['GPLv3+'], 'uy.edu.fing.geirea.leerpendrive': ['GPLv3+'], 'org.winehq.Wine': ['LGPLv2.1'], 'org.x.tuxsuper': ['GPLv2'], 'com.ywwg.Sonata': ['GPLv3'], 'org.laptop.StarChart': ['GPLv2+'], 'rw.olpc.Learn': ['GPLv2', 'CC-BY-SA'], 'org.kiwix.Kiwix': ['GPLv3'], 'org.laptop.community.TypingTurtle': ['GPLv3'], 'org.sugarlabs.IRC': ['GPLv2+'], 'org.laptop.community.Finance': ['GPLv3+'], 'org.sugarlabs.InfoSlicer': ['GPLv2+'], 'org.laptop.sugar.DistributeActivity': ['GPLv2+'], 'org.laptop.community.Colors': ['GPLv3+'], 'org.laptop.Develop': ['GPLv2+'], 'org.worldwideworkshop.JokeMachineActivity': ['GPLv2+'], 'org.worldwideworkshop.olpc.storybuilder': ['GPLv2+'], 'org.blender.blender': ['GPLv2+'], 'org.laptop.physics': ['GPLv3'], 'au.net.acid.Jam2Jam1': ['GPLv2+'], } class Application(application.Application): _my_connection = None _volume = None _client = None @property def volume(self): if self._volume is None: self._volume = db.Volume(data_root.value, model.RESOURCES) self._volume.populate() return self._volume def epilog(self): if self._volume is not None: self._volume.close() @application.command( 'consecutively launch pull and push commands') def sync(self): self.pull() self.push() @application.command( 'pull activities.sugarlabs.org content to local db') def pull(self): if not self.volume['context'].exists(SN_GUID): self.volume['context'].create({ 'guid': SN_GUID, 'implement': SN_GUID, 'type': 'project', 'title': 'Sugar Network', 'summary': 'Sugar Network', 'description': 'Sugar Network', 'ctime': time.time(), 'mtime': time.time(), 'author': ASLO_AUTHOR, }) if not self.volume['context'].exists(SUGAR_GUID): self.volume['context'].create({ 'guid': SUGAR_GUID, 'implement': SUGAR_GUID, 'type': 'package', 'title': 'sugar', 'summary': 'Constructionist learning platform', 'description': 'Sugar provides simple yet powerful means of engaging ' 'young children in the world of learning that is ' 'opened up by computers and the Internet. With Sugar, ' 'even the youngest learner will quickly become ' 'proficient in using the computer as a tool to engage ' 'in authentic problem-solving. Sugar promotes ' 'sharing, collaborative learning, and reflection, ' 'developing skills that help them in all aspects ' 'of life.', 'ctime': time.time(), 'mtime': time.time(), 'author': ASLO_AUTHOR, }) if not self.volume['context'].exists(PACKAGES_GUID): self.volume['context'].create({ 'guid': PACKAGES_GUID, 'implement': PACKAGES_GUID, 'type': 'project', 'title': 'Packages', 'summary': 'Collection of GNU/Linux packages metadata', 'description': 'Collection of GNU/Linux packages metadata', 'ctime': time.time(), 'mtime': time.time(), 'author': ASLO_AUTHOR, 'icon': { 'url': '/static/images/package.png', }, }) if self.args: for addon_id in self.args: self.sync_activities(addon_id) else: self.sync_activities() @application.command( 'submit pulled activities.sugarlabs.org content to ' 'Sugar Network server') def push(self): node = SlaveRoutes(join(data_root.value, 'key'), self.volume) node.online_sync(no_pull=True) def sync_activities(self, addon_id=None): directory = self.volume['context'] items, __ = directory.find(type='activity', guid=addon_id, not_layer='deleted') existing_activities = set([i.guid for i in items]) sql = """ SELECT id, guid FROM addons WHERE status > 0 AND status < 5 """ if addon_id: sql += ' AND id = %s' % addon_id for addon_id, bundle_id in self.sqlexec(sql): if [i for i in IGNORE_ADDONS if i in bundle_id]: continue try: authors = self.sync_context(addon_id, bundle_id) self.sync_versions(addon_id, bundle_id) self.sync_reviews(addon_id, bundle_id) self.sync_previews(addon_id, bundle_id, authors) self.sync_comments(addon_id, bundle_id) except Exception: print '-- Failed to sync %s addon' % addon_id traceback.print_exception(*sys.exc_info()) if bundle_id in existing_activities: existing_activities.remove(bundle_id) for guid in existing_activities: print '-- Hide %r deleted activity' % guid directory.update(guid, {'layer': ['deleted']}) def sync_previews(self, addon_id, bundle_id, authors): directory = self.volume['artifact'] items, __ = directory.find(context=bundle_id, type='preview', not_layer='deleted') existing = set([i.guid for i in items]) sql = """ SELECT id, created, modified, caption, filedata FROM previews WHERE addon_id = %s """ % addon_id for guid, created, modified, caption, data in self.sqlexec(sql): if guid in IGNORE_PREVIEWS: continue guid = str(guid) if directory.exists(guid): existing.remove(guid) continue try: preview = scale_png(data, 200, 200) except Exception: print '-- Failed to load %s preview for %s' % (guid, bundle_id) continue directory.create({ 'guid': guid, 'ctime': int(time.mktime(created.timetuple())), 'mtime': int(time.mktime(modified.timetuple())), 'context': bundle_id, 'type': 'preview', 'title': self.get_i18n_field(caption), 'description': self.get_i18n_field(caption), 'author': authors, 'preview': { 'blob': StringIO(preview), 'mime_type': 'image/png', 'digest': hashlib.sha1(preview).hexdigest(), }, 'data': { 'blob': StringIO(data), 'mime_type': 'image/png', 'digest': hashlib.sha1(data).hexdigest(), }, }) for guid in existing: print '-- Hide %s %s deleted preview' % (bundle_id, guid) directory.update(guid, {'layer': ['deleted']}) def sync_comments(self, addon_id, bundle_id): directory = self.volume['comment'] items, __ = directory.find(context=bundle_id, not_layer='deleted') existing = set([i.guid for i in items]) sql = """ SELECT reviews.id, reviews.created, reviews.modified, reviews.body, users.email, users.nickname, CONCAT_WS(' ', users.firstname, users.lastname), reviews.reply_to FROM reviews INNER JOIN versions ON versions.id = reviews.version_id INNER JOIN users ON users.id=reviews.user_id WHERE reply_to IS NOT NULL AND versions.addon_id = %s """ % addon_id for guid, created, modified, content, email, nickname, fullname, \ reply_to in self.sqlexec(sql): guid = str(guid) if directory.exists(guid): existing.remove(guid) continue if not nickname: nickname = email.split('@')[0] fullname = fullname.strip() if not fullname: fullname = nickname directory.create({ 'guid': guid, 'ctime': int(time.mktime(created.timetuple())), 'mtime': int(time.mktime(modified.timetuple())), 'context': bundle_id, 'review': str(reply_to), 'message': self.get_i18n_field(content), 'author': {nickname: { 'order': 0, 'role': 3, 'name': fullname, }}, }) for guid in existing: print '-- Hide %s %s deleted comment' % (bundle_id, guid) directory.update(guid, {'layer': ['deleted']}) def sync_reviews(self, addon_id, bundle_id): directory = self.volume['review'] items, __ = directory.find(context=bundle_id, not_layer='deleted') existing = set([i.guid for i in items]) sql = """ SELECT reviews.id, reviews.created, reviews.modified, reviews.title, reviews.body, reviews.rating, users.email, users.nickname, CONCAT_WS(' ', users.firstname, users.lastname) FROM reviews INNER JOIN versions ON versions.id = reviews.version_id INNER JOIN users ON users.id=reviews.user_id WHERE reply_to IS NULL AND versions.addon_id = %s """ % addon_id for guid, created, modified, title, content, rating, email, nickname, \ fullname in self.sqlexec(sql): guid = str(guid) if directory.exists(guid): existing.remove(guid) continue if not nickname: nickname = email.split('@')[0] fullname = fullname.strip() if not fullname: fullname = nickname directory.create({ 'guid': guid, 'ctime': int(time.mktime(created.timetuple())), 'mtime': int(time.mktime(modified.timetuple())), 'context': bundle_id, 'title': self.get_i18n_field(title), 'content': self.get_i18n_field(content), 'rating': rating, 'author': {nickname: { 'order': 0, 'role': 3, 'name': fullname, }}, }) for guid in existing: print '-- Hide %s %s deleted review' % (bundle_id, guid) directory.update(guid, {'layer': ['deleted']}) def sync_versions(self, addon_id, bundle_id): directory = self.volume['release'] items, __ = directory.find(context=bundle_id, not_layer='deleted') existing = set([i.guid for i in items]) sql = """ SELECT versions.id, versions.version, licenses.name, (select max(localized_string) from translations where id=licenses.text), versions.created, versions.releasenotes, files.filename, (select version from appversions where id=applications_versions.min), (select version from appversions where id=applications_versions.max), users.email, users.nickname, CONCAT_WS(' ', users.firstname, users.lastname), addons.status FROM addons INNER JOIN versions ON versions.addon_id=addons.id LEFT JOIN licenses ON licenses.id=versions.license_id INNER JOIN files ON files.version_id=versions.id INNER JOIN applications_versions ON applications_versions.version_id=versions.id INNER JOIN users ON users.id=versions.uploader WHERE addons.status > 0 AND addons.status < 5 AND addons.id = %s ORDER BY versions.id ASC """ % addon_id for version_id, version, license_id, alicense, release_date, \ releasenotes, filename, sugar_min, sugar_max, \ email, nickname, fullname, status in self.sqlexec(sql): if version_id in IGNORE_VERSIONS: continue version_id = str(version_id) if filename.endswith('.xol'): print '-- Ignore %r[%s] library bundle' % \ (filename, version_id) continue try: util.parse_version(version) except Exception, error: print '-- Cannot parse %r version for %r[%s]: %s' % \ (version, filename, version_id, error) continue if license_id is None: pass elif license_id == 0: alicense = ['MPLv1.1'] elif license_id == 1: alicense = ['GPLv2'] elif license_id == 2: alicense = ['GPLv3'] elif license_id == 3: alicense = ['LGPLv2'] elif license_id == 4: alicense = ['LGPLv3'] elif license_id == 5: alicense = ['MIT'] elif license_id == 6: alicense = ['BSD'] else: parsed_license = self.parse_license(alicense) if parsed_license: alicense = [parsed_license] elif bundle_id in LICENSES_MAP: alicense = LICENSES_MAP[bundle_id] else: print '-- Skip %r[%s] bad %r license' % \ (filename, version_id, alicense) continue if not alicense and bundle_id in LICENSES_MAP: alicense = LICENSES_MAP[bundle_id] layers = ['origin'] if status == 4: layers.append('public') if not nickname: nickname = email.split('@')[0] fullname = fullname.strip() if not fullname: fullname = nickname if directory.exists(version_id): if set(directory.get(version_id).layer) != set(layers) or \ version_id not in existing: directory.update(version_id, {'layer': layers}) if version_id in existing: existing.remove(version_id) continue bundle_path = join(ACTIVITIES_PATH, str(addon_id), filename) try: with load_bundle( self.volume, Request(self.volume, { 'requires': 'sugar>=%s<=%s' % (sugar_min, sugar_max), 'license': alicense, }), bundle_path) as impl: impl['guid'] = version_id if 'notes' not in impl: impl['notes'] = self.get_i18n_field(releasenotes) impl['stability'] = 'stable' impl['ctime'] = int(time.mktime(release_date.timetuple())) impl['mtime'] = time.time() impl['author'] = {nickname: { 'order': 0, 'role': 3, 'name': fullname, }} impl['layer'] = layers impl['data']['url'] = \ '/'.join([DOWNLOAD_URL, str(addon_id), filename]) impl['data']['blob_size'] = os.stat(bundle_path).st_size except Exception, error: print '-- Failed to sync %r[%s]' % (filename, version_id) traceback.print_exception(*sys.exc_info()) else: print '-- Sync %r' % filename for guid in existing: print '-- Hide %s %s deleted version' % (bundle_id, guid) directory.update(guid, {'layer': ['deleted']}) def sync_context(self, addon_id, bundle_id): directory = self.volume['context'] created, modified, title, summary, description, homepage, \ featured = self.sqlexec(""" SELECT created, modified, name, summary, description, (select max(localized_string) from translations where id=homepage), exists (select * from addons_categories where addons_categories.addon_id=addons.id and feature>0) FROM addons WHERE addons.id=%s """ % addon_id)[0] created = int(time.mktime(created.timetuple())) modified = int(time.mktime(modified.timetuple())) layers = ['featured'] if featured else [] if directory.exists(bundle_id) and \ directory.get(bundle_id)['mtime'] >= modified and \ directory.get(bundle_id)['layer'] == layers: return tags = set() for row in self.sqlexec(""" SELECT (select localized_string from translations where id=categories.name AND locale='en-US') FROM addons_categories INNER JOIN categories ON categories.id=addons_categories.category_id WHERE addons_categories.addon_id=%s """ % addon_id): tags.add(CATEGIORIES_TO_TAGS[row[0]]) for row in self.sqlexec(""" SELECT tags.tag_text FROM users_tags_addons INNER JOIN tags ON tags.id=users_tags_addons.tag_id INNER JOIN addons_users ON addons_users.addon_id=users_tags_addons.addon_id WHERE users_tags_addons.addon_id=%s """ % addon_id): tags.add(row[0]) authors = {} for order, (role, email, nickname, fullname) in enumerate(self.sqlexec( """ SELECT addons_users.role, users.email, users.nickname, CONCAT_WS(' ', users.firstname, users.lastname) FROM addons_users INNER JOIN users on users.id=addons_users.user_id WHERE addons_users.addon_id=%s ORDER BY position """ % addon_id)): if not nickname: nickname = email.split('@')[0] fullname = fullname.strip() if not fullname: fullname = nickname authors[nickname] = { 'order': order, 'role': 3 if role == 5 else 1, 'name': fullname, } directory.update(bundle_id, { 'guid': bundle_id, 'type': 'activity', 'title': self.get_i18n_field(title), 'summary': self.get_i18n_field(summary), 'description': self.get_i18n_field(description), 'homepage': homepage or '', 'tags': list(tags), 'author': authors, 'ctime': created, 'mtime': modified, 'layer': layers, }) print '-- Sync %r activity' % bundle_id return authors def parse_license(self, alicense): for good in licenses.GOOD_LICENSES: if not alicense or good in ['ec']: continue if good in alicense: alicense = good break else: for words, good in MISNAMED_LICENSES.items(): for i in words: if i not in alicense.lower(): break else: alicense = good break else: return None return alicense def get_i18n_field(self, an_id): result = {} if an_id: for locale, value in self.sqlexec(""" SELECT locale, localized_string FROM translations WHERE id = %s""" % an_id): if value: result[locale.lower()] = value return result def sqlexec(self, text): if self._my_connection is None: password = mysql_password.value if not password: password = getpass.getpass() self._my_connection = mdb.connect(mysql_server.value, mysql_user.value, password, mysql_database.value) cursor = self._my_connection.cursor() cursor.execute(text) return cursor.fetchall() class Request(dict): def __init__(self, volume, props): dict.__init__(self, props) self._volume = volume def call(self, method, path, content): if method == 'POST': resource, = path return self._volume[resource].create(content) elif method == 'PUT': resource, guid = path self._volume[resource].update(guid, content) def scale_png(data, w, h): with toolkit.NamedTemporaryFile() as src: src.write(data) src.flush() with toolkit.NamedTemporaryFile() as dst: toolkit.assert_call(['convert', '-thumbnail', '%sx%s' % (w, h), '-background', 'transparent', '-gravity', 'center', '-extent', '%sx%s' % (w, h), src.name, dst.name, ]) with file(dst.name, 'rb') as f: return f.read() mysql_server = Option( 'MySQL server', default='localhost', name='mysql_server') mysql_database = Option( 'MySQL database', default='activities', name='mysql_database') mysql_user = Option( 'MySQL user', default='root', name='mysql_user') mysql_password = Option( 'MySQL password', name='mysql_password') Option.seek('main', [application.debug, toolkit.cachedir]) Option.seek('aslo', [mysql_server, mysql_user, mysql_password, mysql_database]) Option.seek('node', [data_root]) Option.seek('client', [client.api_url]) db.index_write_queue.value = 1024 * 10 db.index_flush_threshold.value = 0 db.index_flush_timeout.value = 0 application = Application( name='sugar-network-aslo', description= 'Synchronize Sugar Network content with ' 'http://activities.sugarlabs.org', config_files=['/etc/sweets.conf', '~/.config/sweets/config']) application.start()