[Qa-debsources] patch-tracker DB schema

[Orestis Ioannou]

Hop,

On 03/06/2016 04:08 PM, Stefano Zacchiroli wrote:
>> Ok i didn't know about all the other checksums. Thanks for pointing
>> this to me. AFAIU we only need to store the checksum of the
>> orig.tar.gz so that upstream can verify if the maintainer changed
>> it. Do you think we need to store other checksums?
> 
> If it's just for that use case, I agree that the orig checksum would be
> enough. However, there is still a multiplicity problem. AFAIR in
> dpkg-source (3.0) you can have *multiple* orig tarballs, so we need a
> way to store all those checksums, and therefore to associate the tarball
> name to each checksum (otherwise we will not know which-is-which).
> 
> So it looks like we need either an extra table here, or maybe a jsonb
> field containing an associative list mapping file names to checksums.  I
> like the latter solution better, because it will allow to store easily
> other checksums in the future if we need too.  But I've never checked
> out sqlalchemy deals with postgres jsonb fields...
> 

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?

[snip]
> 
> How about a separate patches table, that cross-reference the table
> you've proposed here with individual patches?
> 
> That way we can store arbitrary metadata associated to each patch if we
> need to, and nothing needs to be done at runtime by the webapp other
> than querying the DB.  If we just want to count the number of patches,
> it will be a trivial SELECT COUNT(), after having filtered the patches
> table by source package id (that we can easily index).

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?

Cheers,

Orestis