[Qa-debsources] patch-tracker DB schema

[Stefano Zacchiroli]

Sorry for the delay, again :)

On Thu, Feb 04, 2016 at 06:42:27PM +0100, Orestis Ioannou wrote:
> So here (attached :P ) is a proposed SQL migration that covers the
> creation of a new table (source package) that will contain the checksum
> and the format of the package.

Some comments inlined below:

> CREATE TYPE patch_format as ENUM(
>     '3.0 (native)',
>     '3.0 (quilt)',
> );

Looks good.

Just a nit: I think this enum should support all the formats listed in
the DPKG-SOURCE(1) manpage. It will make the data type more complete,
and it still will not force us to support anything more than the above 2
entries in Debsources.

> CREATE TABLE source_package (
>   id SERIAL NOT NULL,
>   package_id BIGINT NOT NULL,
>   format patches_format NOT NULL,
>   sha256 VARCHAR(64) NOT NULL,

So, what checksum is that?

In an actual .dsc, there are the checksums of all the (other) components
of the source package, usually an .orig.tar.gz and a .diff.gz. In
addition to that, .changes files also have the checksum of the .dsc.

Calling this field just sha256 looks like too ambiguous to me. And it
opens the question of whether we also want to store the other checksums.

>   CONSTRAINT source_packages_package_id_fkey
>     FOREIGN KEY (package_id) REFERENCES package(id)
>     ON DELETE CASCADE,
>   PRIMARY KEY (id)

This looks good.

> CREATE INDEX ix_source_package_id ON package (package_id);

We certainly also want an index on package_id here, because it will
optimize the ON DELETE CASCADE.

Do we want other indexes? To answer that we'll need to know what will be
the typical queries we're going to do on this query. Can you elaborate
on that?

Thanks for your work! It's much appreciated and I'll try to be quicker
in keeping up with this thread from now on :)

Cheers.
-- 
Stefano Zacchiroli  . . . . . . .  zack at upsilon.cc . . . . o . . . o . o
Maître de conférences . . . . . http://upsilon.cc/zack . . . o . . . o o
Former Debian Project Leader . . . . . @zacchiro . . . . o o o . . . o .
« the first rule of tautology club is the first rule of tautology club »
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 819 bytes
Desc: not available
URL: <http://lists.alioth.debian.org/pipermail/qa-debsources/attachments/20160228/271c65fc/attachment.sig>