#!/usr/bin/env python # Copyright (C) 2012-2014 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 os.path import join import MySQLdb as mdb from sugar_network import db, toolkit from sugar_network.node import data_root, master_api from sugar_network.node.auth import Principal from sugar_network.node.master import RESOURCES from sugar_network.node.slave import SlaveRoutes from sugar_network.node.model import load_bundle from sugar_network.toolkit.spec import parse_version from sugar_network.toolkit.router import Request, Router from sugar_network.toolkit.coroutine import this from sugar_network.toolkit import licenses, application, Option DOWNLOAD_URL = 'http://download.sugarlabs.org/activities' ASLO_AUTHOR = {'d26cef70447160f31a7497cc0320f23a4e383cc3': { 'role': 1, 'name': 'Activity Library', }} ACTIVITIES_PATH = '/upload/activities' SUGAR_GUID = 'sugar' SN_GUID = 'sugar-network' PACKAGES_GUID = 'packages' SUGAR_API_COMPATIBILITY = { '0.94': [ parse_version('0.86'), parse_version('0.88'), parse_version('0.90'), parse_version('0.92'), ], } 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 30749, # Changed bundle_id 31238, # Changed bundle_id 31418, # Changed bundle_id 31369, # Malformed version 31557, # Malformed version 31454, # Malformed version ]) 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 _client = None _router = None def prolog(self): this.volume = db.Volume(data_root.value, RESOURCES) this.volume.populate() this.broadcast = lambda event: None this.localcast = lambda event: None this.request = Request({'HTTP_HOST': master_api.value}) auth = _Auth() routes = SlaveRoutes(master_api.value, this.volume, auth=auth) self._router = Router(routes) this.principal = auth.logon() def epilog(self): this.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 this.volume['context'][SN_GUID].exists: this.volume['context'].create({ 'guid': SN_GUID, 'type': ['group'], 'title': {'en': 'Sugar Network'}, 'summary': {'en': 'Sugar Network'}, 'description': {'en': 'Sugar Network'}, 'ctime': int(time.time()), 'mtime': int(time.time()), 'author': ASLO_AUTHOR, }) if not this.volume['context'][SUGAR_GUID].exists: this.volume['context'].create({ 'guid': SUGAR_GUID, 'type': ['package'], 'title': {'en': 'sugar'}, 'summary': {'en': 'Constructionist learning platform'}, 'description': {'en': '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': int(time.time()), 'mtime': int(time.time()), 'author': ASLO_AUTHOR, }) if not this.volume['context'][PACKAGES_GUID].exists: this.volume['context'].create({ 'guid': PACKAGES_GUID, 'type': ['group'], 'title': {'en': 'Packages'}, 'summary': { 'en': 'Collection of GNU/Linux packages metadata', }, 'description': { 'en': 'Collection of GNU/Linux packages metadata', }, 'ctime': int(time.time()), 'mtime': int(time.time()), 'author': ASLO_AUTHOR, 'icon': 'assets/package.png', 'logo': 'assets/package-logo.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): this.call(method='POST', cmd='online_sync', no_pull=True) def sync_activities(self, addon_id=None): directory = this.volume['context'] items, __ = directory.find(type='activity', guid=addon_id, not_state='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) 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, {'state': 'deleted'}) def sync_previews(self, addon_id, bundle_id, authors): existing = this.volume['context'][bundle_id]['previews'] updates = {} sql = """ SELECT id, modified, filedata FROM previews WHERE addon_id = %s """ % addon_id for guid, modified, data in self.sqlexec(sql): if guid in IGNORE_PREVIEWS: continue guid = str(guid) if guid in existing: del existing[guid] continue try: preview = scale_png(data, 200, 200) except Exception: print '-- Failed to load %s preview for %s' % (guid, bundle_id) continue preview = this.volume.blobs.post(preview, 'image/png') updates[guid] = { 'author': authors, 'value': preview.digest, 'ctime': int(time.mktime(modified.timetuple())), } for guid in existing: print '-- Hide %s %s deleted preview' % (bundle_id, guid) updates[guid] = {} this.volume['context'].update(bundle_id, {'previews': updates}) def sync_reviews(self, addon_id, bundle_id): directory = this.volume['post'] items, __ = directory.find(context=bundle_id, type='review', not_state='deleted') existing_topics = 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 topic, created, modified, title, content, vote, email, nickname, \ fullname in self.sqlexec(sql): topic = str(topic) if topic in existing_topics: existing_topics.remove(topic) else: if not nickname: nickname = email.split('@')[0] fullname = fullname.strip() if not fullname: fullname = nickname directory.create({ 'guid': topic, 'ctime': int(time.mktime(created.timetuple())), 'mtime': int(time.mktime(modified.timetuple())), 'context': bundle_id, 'type': 'review', 'title': self.get_i18n_field(title), 'message': self.get_i18n_field(content), 'vote': vote, 'author': {nickname: { 'role': 3, 'name': fullname, }}, }) existing_comments = directory[topic]['comments'] updates = {} sql = """ SELECT reviews.id, reviews.modified, reviews.body, 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 = %s ORDER BY reviews.created """ % topic for guid, modified, content, email, nickname, fullname, \ in self.sqlexec(sql): guid = str(guid) if guid in existing_comments: del existing_comments[guid] continue if not nickname: nickname = email.split('@')[0] fullname = fullname.strip() if not fullname: fullname = nickname updates[guid] = { 'author': {nickname: { 'role': 3, 'name': fullname, }}, 'value': self.get_i18n_field(content), 'ctime': int(time.mktime(modified.timetuple())), } for guid in existing_comments: print '-- Hide %s %s deleted comment' % (bundle_id, guid) updates[guid] = {} directory.update(topic, {'comments': updates}) for guid in existing_topics: print '-- Hide %s %s deleted review' % (bundle_id, guid) directory.update(guid, {'state': 'deleted'}) def sync_versions(self, addon_id, bundle_id): existing = this.volume['context'][bundle_id]['releases'] updates = {} most_recent = True 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) 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 DESC """ % addon_id for version_id, version, license_id, alicense, release_date, \ releasenotes, filename, sugar_min, sugar_max, \ email, nickname, fullname in self.sqlexec(sql): if version_id in IGNORE_VERSIONS: continue version_id = str(version_id) if version_id in existing: del existing[version_id] continue if filename.endswith('.xol'): print '-- Ignore %r[%s] library bundle' % \ (filename, version_id) continue try: 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] if not nickname: nickname = email.split('@')[0] fullname = fullname.strip() if not fullname: fullname = nickname for max_version, sub_versions in SUGAR_API_COMPATIBILITY.items(): if parse_version(sugar_min) in sub_versions: if parse_version(sugar_max) < parse_version(max_version): sugar_max = max_version elif parse_version(sugar_max) in sub_versions: sugar_max = max_version bundle_path = join(ACTIVITIES_PATH, str(addon_id), filename) digest = hashlib.sha1() with file(bundle_path, 'rb') as f: while True: chunk = f.read(toolkit.BUFFER_SIZE) if not chunk: break digest.update(chunk) blob = this.volume.blobs.post({ 'digest': digest.hexdigest(), 'location': '/'.join([DOWNLOAD_URL, str(addon_id), filename]), 'content-length': str(os.stat(bundle_path).st_size), }) blob.path = bundle_path try: __, release = load_bundle(blob, license=alicense, extra_deps='sugar>=%s<=%s' % (sugar_min, sugar_max), release_notes=self.get_i18n_field(releasenotes), update_context=most_recent) updates[version_id] = { 'author': { nickname: { 'role': 3, 'name': fullname, }, }, 'value': release, 'ctime': int(time.mktime(release_date.timetuple())), } most_recent = False 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) updates[guid] = {} this.volume['context'].update(bundle_id, {'releases': updates}) def sync_context(self, addon_id, bundle_id): directory = this.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())) pins = ['featured'] if featured else [] if directory[bundle_id].exists and \ directory.get(bundle_id)['mtime'] >= modified and \ directory.get(bundle_id)['pins'] == pins: 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 role, email, nickname, fullname in 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] = { '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, 'pins': pins, }) 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 _Auth(object): def logon(self, request=None): return Principal(ASLO_AUTHOR.keys()[0], 0xF) 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, master_api]) 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()