~launchpad-pqm/launchpad/devel

# Copyright 2009 Canonical Ltd.  This software is licensed under the

# GNU Affero General Public License version 3 (see the file LICENSE).

# pylint: disable-msg=E0611,W0212

"""Hardware database related table classes."""

__all__ = [

    'HWDevice',

    'HWDeviceClass',

    'HWDeviceSet',

    'HWDeviceDriverLink',

    'HWDeviceDriverLinkSet',

    'HWDeviceNameVariant',

    'HWDeviceNameVariantSet',

    'HWDriver',

    'HWDriverName',

    'HWDriverPackageName',

    'HWDriverSet',

    'HWSubmission',

    'HWSubmissionBug',

    'HWSubmissionBugSet',

    'HWSubmissionSet',

    'HWSubmissionDevice',

    'HWSubmissionDeviceSet',

    'HWSystemFingerprint',

    'HWSystemFingerprintSet',

    'HWVendorID',

    'HWVendorIDSet',

    'HWVendorName',

    'HWVendorNameSet',

    'make_submission_device_statistics_clause',

    '_userCanAccessSubmissionStormClause',

    ]

import re

from sqlobject import (

    BoolCol,

    ForeignKey,

    IntCol,

    StringCol,

    )

from storm.expr import (

    Alias,

    And,

    Count,

    Not,

    Or,

    Select,

    )

from storm.store import Store

from zope.component import getUtility

from zope.interface import implements

from canonical.database.constants import (

    DEFAULT,

    UTC_NOW,

    )

from canonical.database.datetimecol import UtcDateTimeCol

from canonical.database.enumcol import EnumCol

from canonical.database.sqlbase import (

    SQLBase,

    sqlvalues,

    )

from canonical.launchpad.interfaces.librarian import ILibraryFileAliasSet

from canonical.launchpad.webapp.interfaces import (

    DEFAULT_FLAVOR,

    IStoreSelector,

    MAIN_STORE,

    )

from lp.app.interfaces.launchpad import ILaunchpadCelebrities

from lp.app.validators.name import valid_name

from lp.bugs.model.bug import (

    Bug,

    BugAffectsPerson,

    BugTag,

    )

from lp.bugs.model.bugsubscription import BugSubscription

from lp.hardwaredb.interfaces.hwdb import (

    HWBus,

    HWSubmissionFormat,

    HWSubmissionKeyNotUnique,

    HWSubmissionProcessingStatus,

    IHWDevice,

    IHWDeviceClass,

    IHWDeviceClassSet,

    IHWDeviceDriverLink,

    IHWDeviceDriverLinkSet,

    IHWDeviceNameVariant,

    IHWDeviceNameVariantSet,

    IHWDeviceSet,

    IHWDriver,

    IHWDriverName,

    IHWDriverPackageName,

    IHWDriverSet,

    IHWSubmission,

    IHWSubmissionBug,

    IHWSubmissionBugSet,

    IHWSubmissionDevice,

    IHWSubmissionDeviceSet,

    IHWSubmissionSet,

    IHWSystemFingerprint,

    IHWSystemFingerprintSet,

    IHWVendorID,

    IHWVendorIDSet,

    IHWVendorName,

    IHWVendorNameSet,

    IllegalQuery,

    ParameterError,

    )

from lp.registry.interfaces.distribution import IDistribution

from lp.registry.interfaces.distroseries import IDistroSeries

from lp.registry.interfaces.person import (

    IPersonSet,

    validate_public_person,

    )

from lp.registry.interfaces.product import License

from lp.registry.model.distribution import Distribution

from lp.registry.model.distroseries import DistroSeries

from lp.registry.model.person import Person

from lp.registry.model.teammembership import TeamParticipation

from lp.soyuz.interfaces.distroarchseries import IDistroArchSeries

from lp.soyuz.model.distroarchseries import DistroArchSeries

# The vendor name assigned to new, unknown vendor IDs. See

# HWDeviceSet.create().

UNKNOWN = 'Unknown'

class HWSubmission(SQLBase):

    """See `IHWSubmission`."""

    implements(IHWSubmission)

    _table = 'HWSubmission'

    date_created = UtcDateTimeCol(notNull=True, default=UTC_NOW)

    date_submitted = UtcDateTimeCol(notNull=True, default=UTC_NOW)

    format = EnumCol(enum=HWSubmissionFormat, notNull=True)

    status = EnumCol(enum=HWSubmissionProcessingStatus, notNull=True)

    private = BoolCol(notNull=True)

    contactable = BoolCol(notNull=True)

    submission_key = StringCol(notNull=True)

    owner = ForeignKey(dbName='owner', foreignKey='Person',

                       storm_validator=validate_public_person)

    distroarchseries = ForeignKey(dbName='distroarchseries',

                                  foreignKey='DistroArchSeries')

    raw_submission = ForeignKey(dbName='raw_submission',

                                foreignKey='LibraryFileAlias',

                                notNull=False, default=DEFAULT)

    system_fingerprint = ForeignKey(dbName='system_fingerprint',

                                    foreignKey='HWSystemFingerprint',

                                    notNull=True)

    raw_emailaddress = StringCol()

    @property

    def devices(self):

        return HWSubmissionDeviceSet().getDevices(submission=self)

class HWSubmissionSet:

    """See `IHWSubmissionSet`."""

    implements(IHWSubmissionSet)

    def createSubmission(self, date_created, format, private, contactable,

                         submission_key, emailaddress, distroarchseries,

                         raw_submission, filename, filesize,

                         system_fingerprint):

        """See `IHWSubmissionSet`."""

        assert valid_name(submission_key), "Invalid key %s" % submission_key

        submission_exists = HWSubmission.selectOneBy(

            submission_key=submission_key)

        if submission_exists is not None:

            raise HWSubmissionKeyNotUnique(

                'A submission with this ID already exists')

        personset = getUtility(IPersonSet)

        if emailaddress is not None:

            owner = personset.getByEmail(emailaddress)

        else:

            owner = None

        fingerprint = HWSystemFingerprint.selectOneBy(

            fingerprint=system_fingerprint)

        if fingerprint is None:

            fingerprint = HWSystemFingerprint(fingerprint=system_fingerprint)

        libraryfileset = getUtility(ILibraryFileAliasSet)

        libraryfile = libraryfileset.create(

            name=filename,

            size=filesize,

            file=raw_submission,

            # XXX: kiko 2007-09-20: The hwdb client sends us bzipped XML, but

            # arguably other clients could send us other formats. The right

            # way to do this is either to enforce the format in the browser

            # code, allow the client to specify the format, or use a

            # magic module to sniff what it is we got.

            contentType='application/x-bzip2',

            expires=None)

        return HWSubmission(

            date_created=date_created,

            format=format,

            status=HWSubmissionProcessingStatus.SUBMITTED,

            private=private,

            contactable=contactable,

            submission_key=submission_key,

            owner=owner,

            distroarchseries=distroarchseries,

            raw_submission=libraryfile,

            system_fingerprint=fingerprint,

            raw_emailaddress=emailaddress)

    def _userHasAccessClause(self, user):

        """Limit results of HWSubmission queries to rows the user can access.

        """

        admins = getUtility(ILaunchpadCelebrities).admin

        if user is None:

            return " AND NOT HWSubmission.private"

        elif not user.inTeam(admins):

            return """

                AND (NOT HWSubmission.private

                     OR EXISTS

                         (SELECT 1

                             FROM HWSubmission as HWAccess, TeamParticipation

                             WHERE HWAccess.id=HWSubmission.id

                                 AND HWAccess.private

                                 AND HWAccess.owner=TeamParticipation.team

                                 AND TeamParticipation.person=%i

                                 ))

                """ % user.id

        else:

            return ""

    def getBySubmissionKey(self, submission_key, user=None):

        """See `IHWSubmissionSet`."""

        store = getUtility(IStoreSelector).get(MAIN_STORE, DEFAULT_FLAVOR)

        return store.find(

            HWSubmission,

            And(HWSubmission.submission_key == submission_key,

                _userCanAccessSubmissionStormClause(user))).one()

    def getByFingerprintName(self, name, user=None):

        """See `IHWSubmissionSet`."""

        fp = HWSystemFingerprintSet().getByName(name)

        query = """

            system_fingerprint=%s

            AND HWSystemFingerprint.id = HWSubmission.system_fingerprint

            """ % sqlvalues(fp)

        query = query + self._userHasAccessClause(user)

        return HWSubmission.select(

            query,

            clauseTables=['HWSystemFingerprint'],

            prejoinClauseTables=['HWSystemFingerprint'],

            orderBy=['-date_submitted',

                     'HWSystemFingerprint.fingerprint',

                     'submission_key'])

    def getByOwner(self, owner, user=None):

        """See `IHWSubmissionSet`."""

        query = """

            owner=%i

            AND HWSystemFingerprint.id = HWSubmission.system_fingerprint

            """ % owner.id

        query = query + self._userHasAccessClause(user)

        return HWSubmission.select(

            query,

            clauseTables=['HWSystemFingerprint'],

            prejoinClauseTables=['HWSystemFingerprint'],

            orderBy=['-date_submitted',

                     'HWSystemFingerprint.fingerprint',

                     'submission_key'])

    def submissionIdExists(self, submission_key):

        """See `IHWSubmissionSet`."""

        rows = HWSubmission.selectBy(submission_key=submission_key)

        return rows.count() > 0

    def getByStatus(self, status, user=None):

        """See `IHWSubmissionSet`."""

        store = getUtility(IStoreSelector).get(MAIN_STORE, DEFAULT_FLAVOR)

        result_set = store.find(HWSubmission,

                                HWSubmission.status == status,

                                _userCanAccessSubmissionStormClause(user))

        # Provide a stable order. Sorting by id, to get the oldest

        # submissions first. When date_submitted has an index, we could

        # sort by that first.

        result_set.order_by(HWSubmission.id)

        return result_set

    def search(self, user=None, device=None, driver=None, distribution=None,

               distroseries=None, architecture=None, owner=None,

               created_before=None, created_after=None,

               submitted_before=None, submitted_after=None):

        """See `IHWSubmissionSet`."""

        store = getUtility(IStoreSelector).get(MAIN_STORE, DEFAULT_FLAVOR)

        args = []

        if device is not None:

            args.append(HWDeviceDriverLink.device == HWDevice.id)

            args.append(HWDevice.id == device.id)

        if driver is not None:

            args.append(HWDeviceDriverLink.driver == HWDriver.id)

            args.append(HWDriver.id == driver.id)

        # HWDevice and HWDriver are linked to submissions via

        # HWDeviceDriverLink and HWSubmissionDevice.

        if args:

            args.append(HWSubmissionDevice.device_driver_link ==

                        HWDeviceDriverLink.id)

            args.append(HWSubmissionDevice.submission == HWSubmission.id)

        if (distribution is not None or distroseries is not None

            or architecture is not None):

            # We need to select a specific distribution, distroseries,

            # and/or processor architecture.

            if distribution and distroseries:

                raise IllegalQuery(

                    'Only one of `distribution` or '

                    '`distroseries` can be present.')

            args.append(HWSubmission.distroarchseries == DistroArchSeries.id)

            if architecture is not None:

                args.append(DistroArchSeries.architecturetag == architecture)

            if distribution is not None:

                args.append(DistroArchSeries.distroseries == DistroSeries.id)

                args.append(DistroSeries.distribution == Distribution.id)

                args.append(Distribution.id == distribution.id)

            if distroseries is not None:

                args.append(DistroArchSeries.distroseries == distroseries.id)

        if owner is not None:

            args.append(HWSubmission.owner == owner.id)

        if created_before is not None:

            args.append(HWSubmission.date_created <= created_before)

        if created_after is not None:

            args.append(HWSubmission.date_created > created_after)

        if submitted_before is not None:

            args.append(HWSubmission.date_submitted <= submitted_before)

        if submitted_after is not None:

            args.append(HWSubmission.date_submitted > submitted_after)

        result_set = store.find(

            HWSubmission,

            _userCanAccessSubmissionStormClause(user),

            *args)

        # Many devices are associated with more than one driver, even

        # for one submission, hence we may have more than one

        # HWSubmissionDevice record and more than one HWDeviceDriverLink

        # for one device and one submission matching the WHERE clause

        # defined above. This leads to duplicate results without a

        # DISTINCT clause.

        result_set.config(distinct=True)

        result_set.order_by(HWSubmission.id)

        return result_set

    def _submissionsSubmitterSelects(

        self, target_column, bus, vendor_id, product_id, driver_name,

        package_name, distro_target):

        """Return Select objects for statistical queries.

        :return: A tuple

            (select_device_related_records, select_all_records)

            where select_device_related_records is a Select instance

            returning target_column matching all other method

            parameters, and where select_all_records is a Select

            instance returning target_column and matching distro_target,

        :param target_column: The records returned by the Select instance.

        :param bus: The `HWBus` of the device.

        :param vendor_id: The vendor ID of the device.

        :param product_id: The product ID of the device.

        :param driver_name: The name of the driver used for the device

            (optional).

        :param package_name: The name of the package the driver is a part of.

            (optional).

        :param distro_target: Limit the result to submissions made for the

            given distribution, distroseries or distroarchseries.

            (optional).

        """

        tables, clauses = make_distro_target_clause(distro_target)

        if HWSubmission not in tables:

            tables.append(HWSubmission)

        clauses.append(

            HWSubmission.status == HWSubmissionProcessingStatus.PROCESSED)

        all_submissions = Select(

            columns=[target_column], tables=tables, where=And(*clauses),

            distinct=True)

        device_tables, device_clauses = (

            make_submission_device_statistics_clause(

                bus, vendor_id, product_id, driver_name, package_name, False))

        submission_ids = Select(

            columns=[HWSubmissionDevice.submissionID],

            tables=device_tables, where=And(*device_clauses))

        clauses.append(HWSubmission.id.is_in(submission_ids))

        submissions_with_device = Select(

            columns=[target_column], tables=tables, where=And(*clauses),

            distinct=True)

        return (submissions_with_device, all_submissions)

    def numSubmissionsWithDevice(

        self, bus=None, vendor_id=None, product_id=None, driver_name=None,

        package_name=None, distro_target=None):

        """See `IHWSubmissionSet`."""

        store = getUtility(IStoreSelector).get(MAIN_STORE, DEFAULT_FLAVOR)

        submissions_with_device_select, all_submissions_select = (

            self._submissionsSubmitterSelects(

                Count(), bus, vendor_id, product_id, driver_name,

                package_name, distro_target))

        submissions_with_device = store.execute(

            submissions_with_device_select)

        all_submissions = store.execute(all_submissions_select)

        return (submissions_with_device.get_one()[0],

                all_submissions.get_one()[0])

    def numOwnersOfDevice(

        self, bus=None, vendor_id=None, product_id=None, driver_name=None,

        package_name=None, distro_target=None):

        """See `IHWSubmissionSet`."""

        store = getUtility(IStoreSelector).get(MAIN_STORE, DEFAULT_FLAVOR)

        submitters_with_device_select, all_submitters_select = (

            self._submissionsSubmitterSelects(

                HWSubmission.raw_emailaddress, bus, vendor_id, product_id,

                driver_name, package_name, distro_target))

        submitters_with_device = store.execute(

            Select(

                columns=[Count()],

                tables=[Alias(submitters_with_device_select, 'addresses')]))

        all_submitters = store.execute(

            Select(

                columns=[Count()],

                tables=[Alias(all_submitters_select, 'addresses')]))

        return (submitters_with_device.get_one()[0],

                all_submitters.get_one()[0])

    def deviceDriverOwnersAffectedByBugs(

        self, bus=None, vendor_id=None, product_id=None, driver_name=None,

        package_name=None, bug_ids=None, bug_tags=None, affected_by_bug=False,

        subscribed_to_bug=False, user=None):

        """See `IHWSubmissionSet`."""

        store = getUtility(IStoreSelector).get(MAIN_STORE, DEFAULT_FLAVOR)

        tables, clauses = make_submission_device_statistics_clause(

                bus, vendor_id, product_id, driver_name, package_name, False)

        tables.append(HWSubmission)

        clauses.append(HWSubmissionDevice.submission == HWSubmission.id)

        clauses.append(_userCanAccessSubmissionStormClause(user))

        if ((bug_ids is None or len(bug_ids) == 0) and

            (bug_tags is None or len(bug_tags) == 0)):

            raise ParameterError('bug_ids or bug_tags must be supplied.')

        tables.append(Bug)

        if bug_ids is not None and bug_ids is not []:

            clauses.append(Bug.id.is_in(bug_ids))

        if bug_tags is not None and bug_tags is not []:

            clauses.extend([

                Bug.id == BugTag.bugID, BugTag.tag.is_in(bug_tags)])

            tables.append(BugTag)

        # If we OR-combine the search for bug owners, subscribers

        # and affected people on SQL level, the query runs very slow.

        # So let's run the queries separately and join the results

        # on Python level.

        # This would be quicker still if we did it as a single query

        # using UNION.

        owner_query = Select(

            columns=[HWSubmission.ownerID], tables=tables,

            where=And(*(clauses + [Bug.ownerID == HWSubmission.ownerID])))

        user_ids = set(store.execute(owner_query))

        if subscribed_to_bug:

            subscriber_clauses = [

                BugSubscription.person_id == HWSubmission.ownerID,

                BugSubscription.bug == Bug.id,

                ]

            subscriber_query = Select(

                columns=[HWSubmission.ownerID],

                tables=tables + [BugSubscription],

                where=And(*(clauses + subscriber_clauses)))

            user_ids.update(store.execute(subscriber_query))

        if affected_by_bug:

            affected_clauses = [

                BugAffectsPerson.personID == HWSubmission.ownerID,

                BugAffectsPerson.bug == Bug.id,

                BugAffectsPerson.affected,

                ]

            affected_query = Select(

                columns=[HWSubmission.ownerID],

                tables=tables + [BugAffectsPerson],

                where=And(*(clauses + affected_clauses)))

            user_ids.update(store.execute(affected_query))

        # A "WHERE x IN (y, z...)" query needs at least one element

        # on the right side of IN.

        if len(user_ids) == 0:

            result = store.find(Person, False)

        else:

            user_ids = [row[0] for row in user_ids]

            result = store.find(Person, Person.id.is_in(user_ids))

        result.order_by(Person.displayname)

        return result

    def hwInfoByBugRelatedUsers(

        self, bug_ids=None, bug_tags=None, affected_by_bug=False,

        subscribed_to_bug=False, user=None):

        """See `IHWSubmissionSet`."""

        store = getUtility(IStoreSelector).get(MAIN_STORE, DEFAULT_FLAVOR)

        if ((bug_ids is None or len(bug_ids) == 0) and

            (bug_tags is None or len(bug_tags) == 0)):

            raise ParameterError('bug_ids or bug_tags must be supplied.')

        tables = [

            Person, HWSubmission, HWSubmissionDevice, HWDeviceDriverLink,

            HWDevice, HWVendorID, Bug, BugTag,

            ]

        clauses = [

            Person.id == HWSubmission.ownerID,

            HWSubmissionDevice.submission == HWSubmission.id,

            HWSubmissionDevice.device_driver_link == HWDeviceDriverLink.id,

            HWDeviceDriverLink.device == HWDevice.id,

            HWDevice.bus_vendor == HWVendorID.id]

        if bug_ids is not None and bug_ids is not []:

            clauses.append(Bug.id.is_in(bug_ids))

        if bug_tags is not None and bug_tags is not []:

            clauses.extend(

                [Bug.id == BugTag.bugID, BugTag.tag.is_in(bug_tags)])

        clauses.append(_userCanAccessSubmissionStormClause(user))

        person_clauses = [Bug.ownerID == HWSubmission.ownerID]

        if subscribed_to_bug:

            person_clauses.append(

                And(BugSubscription.person_id == HWSubmission.ownerID,

                    BugSubscription.bug == Bug.id))

            tables.append(BugSubscription)

        if affected_by_bug:

            person_clauses.append(

                And(BugAffectsPerson.personID == HWSubmission.ownerID,

                    BugAffectsPerson.bug == Bug.id,

                    BugAffectsPerson.affected))

            tables.append(BugAffectsPerson)

        clauses.append(Or(person_clauses))

        query = Select(

            columns=[

                Person.name, HWVendorID.bus,

                HWVendorID.vendor_id_for_bus, HWDevice.bus_product_id],

            tables=tables, where=And(*clauses), distinct=True,

            order_by=[HWVendorID.bus, HWVendorID.vendor_id_for_bus,

                      HWDevice.bus_product_id, Person.name])

        return [

            (person_name, HWBus.items[bus_id], vendor_id, product_id)

             for person_name, bus_id, vendor_id, product_id

             in store.execute(query)]

class HWSystemFingerprint(SQLBase):

    """Identifiers of a computer system."""

    implements(IHWSystemFingerprint)

    _table = 'HWSystemFingerprint'

    fingerprint = StringCol(notNull=True)

class HWSystemFingerprintSet:

    """A set of identifiers of a computer system."""

    implements(IHWSystemFingerprintSet)

    def getByName(self, fingerprint):

        """See `IHWSystemFingerprintSet`."""

        return HWSystemFingerprint.selectOneBy(fingerprint=fingerprint)

    def createFingerprint(self, fingerprint):

        """See `IHWSystemFingerprintSet`."""

        return HWSystemFingerprint(fingerprint=fingerprint)

class HWVendorName(SQLBase):

    """See `IHWVendorName`."""

    implements(IHWVendorName)

    _table = 'HWVendorName'

    name = StringCol(notNull=True)

class HWVendorNameSet:

    """See `IHWVendorNameSet`."""

    implements(IHWVendorNameSet)

    def create(self, name):

        """See `IHWVendorNameSet`."""

        return HWVendorName(name=name)

    def getByName(self, name):

        """See `IHWVendorNameSet`."""

        return HWVendorName.selectOne(

            'ulower(name)=ulower(%s)' % sqlvalues(name))

four_hex_digits = re.compile('^0x[0-9a-f]{4}$')

six_hex_digits = re.compile('^0x[0-9a-f]{6}$')

# The regular expressions for the SCSI vendor and product IDs are not as

# "picky" as the specification requires. Considering the fact that for

# example Microtek sold at least one scanner model that returns '        '

# as the vendor ID, it seems reasonable to allows also somewhat broken

# looking IDs.

scsi_vendor = re.compile('^.{8}$')

scsi_product = re.compile('^.{16}$')

validVendorID = {

    HWBus.PCI: four_hex_digits,

    HWBus.PCCARD: four_hex_digits,

    HWBus.USB: four_hex_digits,

    HWBus.IEEE1394: six_hex_digits,

    HWBus.SCSI: scsi_vendor,

    }

validProductID = {

    HWBus.PCI: four_hex_digits,

    HWBus.PCCARD: four_hex_digits,

    HWBus.USB: four_hex_digits,

    HWBus.SCSI: scsi_product,

    }

def isValidVendorID(bus, id):

    """Check that the string id is a valid vendor ID for this bus.

    :return: True, if id is valid, otherwise False

    :param bus: A `HWBus` indicating the bus type of "id"

    :param id: A string with the ID

    Some busses have constraints for IDs, while some can use arbitrary

    values, for example the "fake" busses HWBus.SYSTEM and HWBus.SERIAL.