path: root/misc/aslo-sync

#!/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 <http://www.gnu.org/licenses/>.

import os
import time
import shutil
import getpass
import gettext
import tempfile
from os.path import join

import MySQLdb as mdb

from sugar_network import db, client
from sugar_network.node import data_root
from sugar_network.client.bundle import Bundle
from sugar_network.resources.volume import Volume
from sugar_network.node.slave import SlaveCommands
from sugar_network.node.routes import load_bundle
from sugar_network.toolkit import util, licenses, application, spec, Option


DOWNLOAD_URL = 'http://download.sugarlabs.org/activities'
ASLO_GUID = 'd26cef70447160f31a7497cc0320f23a4e383cc3'
ACTIVITIES_PATH = '/upload/activities'
SUGAR_GUID = 'sugar'
SN_GUID = 'sugar-network'
PACKAGES_GUID = 'packages'

CATEGIORIES_TO_TAGS = {
        'Search & Discovery': 'discovery',
        'Documents': 'documents',
        'Chat, mail and talk': 'conversation',
        'Programming': 'programming',
        'Maps & Geography': 'geography',
        'Media players': 'media',
        'Teacher tools': 'teacher',
        'Games': 'games',
        'Media creation': 'media',
        'Maths & Science': 'science',
        'News': 'news',
        }

MISNAMED_LICENSES = {
        ('artistic', '2.0'): 'Artistic 2.0',
        ('cc-by-sa',): 'CC-BY-SA',
        ('creative', 'share', 'alike'): 'CC-BY-SA',
        }

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
        ])

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 AbsDict(dict):
    pass


class Application(application.Application):

    _my_connection = None
    _volume = None
    _client = None

    @property
    def volume(self):
        if self._volume is None:
            self._volume = Volume(data_root.value)
            self._volume.populate()
        return self._volume

    @property
    def client(self):
        if self._client is None:
            self._client = client.Client()
        return self._client

    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': self.authors(),
                    })

        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': self.authors(),
                    })

        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': self.authors(),
                    })
            self.volume['context'].set_blob(PACKAGES_GUID, '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 = SlaveCommands(join(data_root.value, 'node.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', layer='public',
                limit=db.MAX_LIMIT)
        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:
                self.sync_context(addon_id, bundle_id)
                self.sync_versions(addon_id, bundle_id)
                self.sync_reviews(addon_id, bundle_id)
            except Exception, error:
                print '-- Cannot sync %r addon: %s' % (addon_id, error)
            if bundle_id in existing_activities:
                existing_activities.remove(bundle_id)

        for guid in existing_activities:
            print '-- Hide %r addon deleted on ASLO' % guid
            directory.update(guid, {'layer': ['deleted']})

    def sync_reviews(self, addon_id, bundle_id):
        directory = self.volume['review']
        items, __ = directory.find(context=bundle_id, layer='public',
                limit=db.MAX_LIMIT)
        existing_reviews = set([i.guid for i in items])

        sql = """
            SELECT
                reviews.id,
                reviews.created,
                reviews.title,
                reviews.body,
                reviews.rating,
                users.nickname,
                IF(users.firstname!="",
                    CONCAT_WS(' ', users.firstname, users.lastname),
                    users.nickname)
            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, title, content, rating, nickname, author in \
                self.sqlexec(sql):
            guid = str(guid)
            if directory.exists(guid):
                existing_reviews.remove(guid)
                continue
            directory.create({
                    'guid': guid,
                    'ctime': int(time.mktime(created.timetuple())),
                    'mtime': int(time.mktime(created.timetuple())),
                    'context': bundle_id,
                    'title': self.get_i18n_field(title),
                    'content': self.get_i18n_field(content),
                    'rating': rating,
                    'author': self.authors(nickname, author),
                    })

        for guid in existing_reviews:
            print '-- Hide %r review deleted on ASLO' % guid
            directory.update(guid, {'layer': ['deleted']})

    def sync_versions(self, addon_id, bundle_id):
        directory = self.volume['implementation']
        items, __ = directory.find(context=bundle_id, layer='public',
                limit=db.MAX_LIMIT)
        existing_versions = 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)
            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
            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 \
                in self.sqlexec(sql):
            if version_id in IGNORE_VERSIONS:
                continue

            if filename.endswith('.xol'):
                print '-- Ignore library bundles for %r' % bundle_id
                continue

            try:
                parsed_version = util.parse_version(version)
            except Exception, error:
                print '-- Cannot parse %r version for %r: %s' % \
                        (version, bundle_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 bad %r license from %s in %s' % \
                            (alicense, filename, addon_id)
                    continue

            items, __ = directory.find(context=bundle_id,
                    version=version, limit=1)
            if items:
                for i in items:
                    existing_versions.remove(i.guid)
                continue

            try:
                self.sync_implementaiton(bundle_id, addon_id, filename,
                        sugar_min, sugar_max, status, alicense,
                        self.get_i18n_field(releasenotes),
                        int(time.mktime(release_date.timetuple())),
                        )
            except Exception, error:
                print '-- Failed to sync %s for %s: %s' % \
                        (version, bundle_id, error)
                continue

        for guid in existing_versions:
            print '-- Hide %s %r version deleted on ASLO' % (bundle_id, guid)
            directory.update(guid, {'layer': ['deleted']})

    def sync_context(self, addon_id, bundle_id):
        if not self.volume['context'].exists(bundle_id):
            self.volume['context'].create({
                    'guid': bundle_id,
                    'type': 'activity',
                    'implement': bundle_id,
                    'title': {},
                    'summary': {},
                    'description': {},
                    'author': self.authors(),
                    'layer': ['public'],
                    'ctime': 0,
                    'mtime': 0,
                    })

        created, modified, title, summary, description, homepage, nickname, \
                author = self.sqlexec("""
            SELECT
                addons.created,
                addons.modified,
                addons.name,
                addons.summary,
                addons.description,
                (select max(localized_string) from translations where
                    id=addons.homepage),
                users.nickname,
                IF(users.firstname != '',
                    CONCAT_WS(' ', users.firstname, users.lastname),
                    users.nickname)
            FROM
                addons
                INNER JOIN addons_users on addons_users.addon_id=addons.id
                INNER JOIN users on users.id=addons_users.user_id
            WHERE addons.id=%s
            """ % addon_id)[0]
        created = int(time.mktime(created.timetuple()))
        modified = int(time.mktime(modified.timetuple()))

        if self.volume['context'].get(bundle_id)['mtime'] >= modified:
            return
        print '-- Update %r activity' % bundle_id

        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])

        self.volume['context'].update(bundle_id, {
                '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': self.authors(nickname, author),
                'ctime': created,
                'mtime': modified,
                })

    def sync_implementaiton(self, context, addon_id, filename,
            sugar_min, sugar_max, status, license, notes, date):
        bundle_path = join(ACTIVITIES_PATH, str(addon_id), filename)
        with load_bundle(self.volume, bundle_path, {
                'requires': 'sugar>=%s; sugar<=%s' % (sugar_min, sugar_max),
                }) as impl:
            if impl['license'] == spec.EMPTY_LICENSE:
                if not license and context in LICENSES_MAP:
                    license = LICENSES_MAP[context]
                if not license:
                    raise Exception('No licenses for %r' % filename)
                impl['license'] = license
            if 'notes' not in impl:
                impl['notes'] = notes
            impl['stability'] = 'stable' if status == 4 else 'developer'
            impl['ctime'] = date
            impl['mtime'] = time.time()
            impl['author'] = self.authors()
            impl['data']['url'] = \
                    '/'.join([DOWNLOAD_URL, str(addon_id), filename])
            impl['data']['blob_size'] = os.stat(bundle_path).st_size

        print '-- Add %r version to %r activity' % (impl['version'], context)

    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()

    def authors(self, nickname=None, original=None):
        result = {
                ASLO_GUID: {
                    'role': 1, 'order': 0, 'name': 'Activity Library',
                    },
                }
        if nickname:
            result[nickname] = {
                    'role': 2, 'order': 1, 'name': original,
                    }
        return result


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])
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()