path: root/misc/aslo-sync

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

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