launchpad-dev team mailing list archive

[Matthew Paul Thomas <mpt@xxxxxxxxxxxxx>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello

At the Ubuntu Developer Summit last month, we discussed how to implement
implement software ratings and reviews in the Ubuntu Software Center.
<https://dev.launchpad.net/SoftwareRatingsAndReviews> It is likely we
will use Launchpad to store ratings and reviews, because it has a
database that already knows about which binary packages are present in
each Ubuntu release. (Eventually we may move to a separate system.)

The first step was to design how the process should look and work for a
reviewer, which I've now done.
<https://wiki.ubuntu.com/SoftwareCenter#reviewing>

Now for the next step: working out the database schema to store ratings
and reviews in Launchpad. Curtis Hovey has asked me to have a go at
this, so here it is. I'm not an expert on database schemas, so probably
I have got a lot of this wrong. Please pull my ideas apart!

For the most part, a review will be of a particular binary package
release. However, some binary packages contain multiple applications,
and a user is interested only in reviewing one of them. (For example,
the python-wxtools package contains the PyShell Python shell and the
Editra programming editor.) Discovering these applications inside
packages will be Soyuz's job. <https://dev.launchpad.net/ArchiveIndex>

    application
    id              serial     primary key
    release         integer    references binarypackagerelease
    icon            integer    references libraryfilealias
    name            text
    summary         text
    category        text

A review, then, is of a "software item" -- either an application, or a
package that does not itself contain applications.

    softwareitem
    id              serial     primary key
    packagerelease  integer    references binarypackagerelease
    application     integer    references application
    keywords        text

(Is that the appropriate way to handle this? For any softwareitem,
exactly one of .packagerelease and .application should be null.)

Besides the review text and rating itself, we want to know who wrote the
review, when, in what language, and whether it has been approved by a
moderator. (We already know what version of the software they reviewed,
because the review is tied to a softwareitem which is tied to a
binarypackagerelease.)

    softwarereview
    id              serial     primary key
    datecreated     timestamp
    author          integer    references person
    softwareitem    integer    references softwareitem
    language        integer    references language
    rating          integer    (1 to 5)
    summary         text
    text            text
    dateapproved    timestamp
    approver        integer    references person

Reviews will be published by default, but a registered user may flag a
review as inappropriate so that it can be either approved or deleted.
(The .dateapproved and .approver should help prevent moderators from
having to re-moderate the same review.)

    softwarereviewflag
    id              serial     primary key
    datecreated     timestamp
    flagger         integer    references person
    reason          integer    references softwarereviewflagreason
    details         text

Moderators can then go through reviews in descending order of the number
of times they have been flagged.

Some constraints I know aren't expressed in these tables. I don't know
whether the database schema is the right place to do it.
*   Someone should be able to submit only one review of a softwareitem.
*   Someone should be able to flag someone else's review only once.
*   There should be limits on the length of a review's summary and text.

Thanks
- --
Matthew Paul Thomas
http://mpt.net.nz/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAksvo5cACgkQ6PUxNfU6ecqCHgCg08a2nGeJU38czsp76SOf5ojH
GG8AoKbBS1tlOszIMNkTDs7Z9or5m7a6
=9nAt
-----END PGP SIGNATURE-----