Dumping apt-ftparchive

Packages files

  4 tads2-version 
 31 tads3-version 
 39 multi-arch
 47 npp-file
 62 subarchitecture
 66 npp-description
 75 gstreamer-uri-sinks
100 npp-applications
100 npp-mimetype  
100 npp-name
118 python-runtime
125 gstreamer-decoders
125 gstreamer-encoders
125 gstreamer-uri-sources
153 maintainer-homepage
163 bugs
199 origin
255 url
304 gstreamer-elements
304 gstreamer-version
338 package-type  
689 build-essential
1217 essential
1874 original-maintainer
2056 breaks
2496 installer-menu-item
2888 kernel-version
13277 pre-depends   
17844 enhances
31221 task
38976 python-version
155616 provides
183415 recommends
195001 replaces
234507 conflicts
268831 suggests
585281 homepage
705478 source
958255 tag
1071068 depends
1191262 priority
1191302 architecture  
1191302 description   
1191302 filename
1191302 installed-size
1191302 maintainer
1191302 md5sum
1191302 package
1191302 section
1191302 sha1
1191302 sha256
1191302 size
1191302 version

Fields that come from override:
- priority (references priority)
- section (references section)
- the fields from control should be dropped
Fields that might need to come from override:
- build-essential
- tag
Fields that are already in binaries:
- package
- version
- maintainer (references maintainer)
- source (references source)
- architecture (references architecture)
Fields that are already in files:
- filename
- size
- md5sum
- sha1
- sha256
Fields that should be added in binaries:
- depends, pre-depends, conflicts, breaks, replaces, recommends, suggests, enhances, provides
- installed-size
- essential
- homepage
- python-version (really?)
- task
- description

Everything else should go to a general binary_fields table.

Sources files

  2 build-recommends
  2 maintainer-homepage
  2 x-vcs-git
  2 x-vcs-hg
  3 vcs-mtn
  5 x-collab-maint
  6 x-vcs-browser
  9 comment
  9 origin
 13 x-vcs-bzr
 16 x-vcs-darcs
 21 vcs-arch
 23 vcs-browse
 24 url
 26 python-standards-version
 41 build-conflicts-indep
 51 original-maintainer
113 vcs-hg
118 x-vcs-svn
123 vcs-cvs
144 vcs-darcs
171 autobuild
461 vcs-bzr
1103 build-conflicts
2686 dm-upload-allowed
2921 python-version
5056 vcs-git
12075 vcs-svn
15884 build-depends-indep
16011 vcs-browser
22783 homepage
23459 uploaders
28276 checksums-sha1
28276 checksums-sha256
59178 build-depends
62502 priority
62592 standards-version
63043 section
63055 architecture
63055 binary
63055 directory
63055 files
63055 format
63055 maintainer
63055 package
63055 version

Fields that come from override:
- section
Fields that are already in source:
- package
- version
- maintainer
- dm-upload-allowed
Fields that are already in files (via dsc_files):
- files
- checksums-sha1
- checksums-sha256
- directory
Fields that are already in binaries:
- binary (but we probably want to use the field from the source package for now, should we save the original value in the db?)
Fields that are already in src_uploaders:
- uploaders (references maintainer)
Fields that should be added in source:
- format
- priority (Really? Shouldn't we drop it?)
- standards-version
- build-depends, build-depends-indep, build-conflicts, build-conflicts-indep
- architecture
- homepage
- python-version (Really?)

Everything else should go to a general source_fields table.

SQL

Clean Up Contents

tables: pending_bin_contents, (u)deb_contents
warning about indices in dbconn.py
classes: PendingBinContents, DebContents, UdebContents
files: binary.py

Mapping Tables

CREATE TABLE source_fields (
    source   INT NOT NULL REFERENCES source(id),
    field    TEXT NOT NULL,
    value    TEXT NOT NULL,

    UNIQUE (source, field)
);
CREATE INDEX source_fields_field_idx ON source_fields (field);

CREATE TABLE binary_fields (
    binary   INT NOT NULL REFERENCES binary(id),
    field    TEXT NOT NULL,
    value    TEXT NOT NULL,

    UNIQUE (binary, field)
);
CREATE INDEX binary_fields_field_idx ON binary_fields (field);

Example Queries

Extract information already available

unstable/amd64/Packages

SELECT b.package, b.version, src.source, src.version AS source_version, c.name AS component, p.priority, s.section, m.name AS maintainer, 'pool/' || f.filename AS filename, f.size, f.md5sum, f.sha1sum, f.sha256sum
FROM bin_associations ba, binaries b, override o, priority p, component c, section s, maintainer m, files f, source src
WHERE ba.suite = 5 AND ba.bin = b.id AND (b.architecture = 16 OR b.architecture = 2) AND b.type = 'deb' AND b.package = o.package AND o.type = 7 AND o.suite = 5 AND o.priority = p.id AND o.component = c.id AND o.section = s.id AND b.maintainer = m.id AND b.file = f.id AND b.source = src.id;

unstable/Source

SELECT s.source, s.version, c.name AS component, sec.section,
  (SELECT priority FROM priority WHERE level =
          (SELECT min(pp.level) FROM binaries pb, override po, priority pp WHERE pb.source = s.id AND po.package = pb.package AND po.type = 7 AND po.suite = 5 AND pp.id = po.priority)) AS priority,
  m.name AS maintainer, s.dm_upload_allowed,
  ARRAY(SELECT mu.name FROM maintainer mu, src_uploaders su
        WHERE su.source = s.id AND su.maintainer = mu.id AND su.maintainer != m.id)
    AS uploaders,
  ARRAY(SELECT f.filename || ' ' || f.size || ' ' || f.md5sum || ' ' || f.sha1sum || ' ' || f.sha256sum
       FROM files f, dsc_files df
       WHERE df.source = s.id AND df.file = f.id)
   AS files
FROM src_associations sa, source s, maintainer m, component c, section sec, override o
WHERE sa.suite = 5 AND sa.source = s.id AND s.maintainer = m.id AND o.package = s.source AND o.type = 9 AND o.component = c.id AND o.section = sec.id;

New Queries

unstable(suite.id=5)/main(component.id=1)/Source(override_type.id=9), uses new src_priority and source_fields tables

SELECT s.source, s.version, 'main' AS component, sec.section, sp.priority, m.name AS maintainer, s.dm_upload_allowed,
  ARRAY(SELECT mu.name FROM maintainer mu, src_uploaders su
        WHERE su.source = s.id AND su.maintainer = mu.id AND su.maintainer != m.id)
  AS uploaders,
  ARRAY(SELECT f.filename || ' ' || f.size || ' ' || f.md5sum || ' ' || f.sha1sum || ' ' || f.sha256sum
        FROM files f, dsc_files df
        WHERE df.source = s.id AND df.file = f.id)
  AS files,
  ARRAY(SELECT fld.field || ': ' || fld.value FROM source_fields fld WHERE fld.source = s.id)
  AS fields 
FROM src_associations sa, source s, maintainer m, section sec, override o, src_priority sp
WHERE sa.suite = 5 AND sa.source = s.id AND sa.suite = sp.suite AND sa.source = sp.source AND s.maintainer = m.id AND o.package = s.source AND o.type = 9 AND o.component = 1 AND o.section = sec.id;