[Qa-debsources] patch-tracker DB schema

[Stefano Zacchiroli]

On Fri, Mar 11, 2016 at 07:11:53PM +0100, Orestis Ioannou wrote:
> I've never used th jsonb format but in the docs it looks ok in the docs.
> You can test for a presence of a key or a set of keys which could be
> useful in this case.
> 
> I guess the next step is propose a json structure right?

It is not strictly needed, but it'd be welcome, yes. In a separate
project I've started using JSON Schema to systematically describe all
JSONB columns that appear in a Postgres DB. It'd be nice to do the
same. But feel free to describe an informal spec of what the JSON values
will look like, and we can extract from it a Schema later on.

> Yes a separate table storing individual patches metadata sounds good to
> me. Something like:
> 
> CREATE TABLE patches (
>   id SERIAL NOT NULL,
>   source_package_id BIGINT NOT NULL,
>   name VARCHAR NOT NULL,
>   CONSTRAINT source_packages_package_id_fkey
>     FOREIGN KEY (source_package_id) REFERENCES source_package(id)
>     ON DELETE CASCADE,
>   PRIMARY KEY (id)
> );
> 
> With an index on source_package_id
> and maybe an index on name as well so we can search a patch fast?

Looks good to me.

I think you will also want an UNIQUE constraint on (source_package_id,
name), to avoid multiple patches with the same name on the same source
package.

Finally, don't we need an extra "order" column here? Otherwise how do we
know the order in which patches are applied? (*Some* source package
formats forces an alphabetical ordering---which maintainers then build
upon using 00, 01, 02, etc---but not *all* of them do.)

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/20160312/c645eab0/attachment.sig>