Dumped on 2024-11-21

Index of database - udd

Schema history

Table: history.sources_count

history.sources_count Structure
F-Key Name Type Description
ts timestamp without time zone PRIMARY KEY
total_sid_main integer
total_sid_contrib integer
total_sid_nonfree integer
vcstype_arch integer
vcstype_bzr integer
vcstype_cvs integer
vcstype_darcs integer
vcstype_git integer
vcstype_hg integer
vcstype_mtn integer
vcstype_svn integer
format_3native integer
format_3quilt integer

Index - Schema history

Schema public

standard public schema

View: public.active_dds

public.active_dds Structure
F-Key Name Type Description
id integer
login text 
SELECT DISTINCT carnivore_login.id
,
    carnivore_login.login
   
FROM carnivore_login
,
    carnivore_keys
  
WHERE (
     (carnivore_keys.id = carnivore_login.id)
   AND (carnivore_keys.key_type = 'keyring'::text)
);

Index - Schema public

View: public.all_bugs

public.all_bugs Structure
F-Key Name Type Description
id integer
package text
source text
arrival timestamp without time zone
status text
severity bugs_severity
submitter text
submitter_name text
submitter_email text
owner text
owner_name text
owner_email text
done text
done_name text
done_email text
title text
last_modified timestamp without time zone
forwarded text
affects_oldstable boolean
affects_stable boolean
affects_testing boolean
affects_unstable boolean
affects_experimental boolean
affected_packages text
affected_sources text 
SELECT bugs.id
,
    bugs.package
,
    bugs.source
,
    bugs.arrival
,
    bugs.status
,
    bugs.severity
,
    bugs.submitter
,
    bugs.submitter_name
,
    bugs.submitter_email
,
    bugs.owner
,
    bugs.owner_name
,
    bugs.owner_email
,
    bugs.done
,
    bugs.done_name
,
    bugs.done_email
,
    bugs.title
,
    bugs.last_modified
,
    bugs.forwarded
,
    bugs.affects_oldstable
,
    bugs.affects_stable
,
    bugs.affects_testing
,
    bugs.affects_unstable
,
    bugs.affects_experimental
,
    bugs.affected_packages
,
    bugs.affected_sources
   
FROM bugs

UNION ALL
 
SELECT archived_bugs.id
,
    archived_bugs.package
,
    archived_bugs.source
,
    archived_bugs.arrival
,
    archived_bugs.status
,
    archived_bugs.severity
,
    archived_bugs.submitter
,
    archived_bugs.submitter_name
,
    archived_bugs.submitter_email
,
    archived_bugs.owner
,
    archived_bugs.owner_name
,
    archived_bugs.owner_email
,
    archived_bugs.done
,
    archived_bugs.done_name
,
    archived_bugs.done_email
,
    archived_bugs.title
,
    archived_bugs.last_modified
,
    archived_bugs.forwarded
,
    archived_bugs.affects_oldstable
,
    archived_bugs.affects_stable
,
    archived_bugs.affects_testing
,
    archived_bugs.affects_unstable
,
    archived_bugs.affects_experimental
,
    archived_bugs.affected_packages
,
    archived_bugs.affected_sources
   
FROM archived_bugs;

Index - Schema public

View: public.all_packages

public.all_packages Structure
F-Key Name Type Description
package text
version debversion
architecture text
maintainer text
maintainer_name text
maintainer_email text
description text
description_md5 text
source text
source_version debversion
essential text
depends text
recommends text
suggests text
enhances text
pre_depends text
breaks text
installed_size bigint
homepage text
size bigint
build_essential text
origin text
sha1 text
replaces text
section text
md5sum text
bugs text
priority text
tag text
task text
python_version text
ruby_versions text
provides text
conflicts text
sha256 text
original_maintainer text
distribution text
release text
component text
multi_arch text
package_type text 
SELECT packages.package
,
    packages.version
,
    packages.architecture
,
    packages.maintainer
,
    packages.maintainer_name
,
    packages.maintainer_email
,
    packages.description
,
    packages.description_md5
,
    packages.source
,
    packages.source_version
,
    packages.essential
,
    packages.depends
,
    packages.recommends
,
    packages.suggests
,
    packages.enhances
,
    packages.pre_depends
,
    packages.breaks
,
    packages.installed_size
,
    packages.homepage
,
    packages.size
,
    packages.build_essential
,
    packages.origin
,
    packages.sha1
,
    packages.replaces
,
    packages.section
,
    packages.md5sum
,
    packages.bugs
,
    packages.priority
,
    packages.tag
,
    packages.task
,
    packages.python_version
,
    packages.ruby_versions
,
    packages.provides
,
    packages.conflicts
,
    packages.sha256
,
    packages.original_maintainer
,
    packages.distribution
,
    packages.release
,
    packages.component
,
    packages.multi_arch
,
    packages.package_type
   
FROM packages

UNION ALL
 
SELECT ubuntu_packages.package
,
    ubuntu_packages.version
,
    ubuntu_packages.architecture
,
    ubuntu_packages.maintainer
,
    ubuntu_packages.maintainer_name
,
    ubuntu_packages.maintainer_email
,
    ubuntu_packages.description
,
    ubuntu_packages.description_md5
,
    ubuntu_packages.source
,
    ubuntu_packages.source_version
,
    ubuntu_packages.essential
,
    ubuntu_packages.depends
,
    ubuntu_packages.recommends
,
    ubuntu_packages.suggests
,
    ubuntu_packages.enhances
,
    ubuntu_packages.pre_depends
,
    ubuntu_packages.breaks
,
    ubuntu_packages.installed_size
,
    ubuntu_packages.homepage
,
    ubuntu_packages.size
,
    ubuntu_packages.build_essential
,
    ubuntu_packages.origin
,
    ubuntu_packages.sha1
,
    ubuntu_packages.replaces
,
    ubuntu_packages.section
,
    ubuntu_packages.md5sum
,
    ubuntu_packages.bugs
,
    ubuntu_packages.priority
,
    ubuntu_packages.tag
,
    ubuntu_packages.task
,
    ubuntu_packages.python_version
,
    ubuntu_packages.ruby_versions
,
    ubuntu_packages.provides
,
    ubuntu_packages.conflicts
,
    ubuntu_packages.sha256
,
    ubuntu_packages.original_maintainer
,
    ubuntu_packages.distribution
,
    ubuntu_packages.release
,
    ubuntu_packages.component
,
    ubuntu_packages.multi_arch
,
    ubuntu_packages.package_type
   
FROM ubuntu_packages

UNION ALL
 
SELECT derivatives_packages.package
,
    derivatives_packages.version
,
    derivatives_packages.architecture
,
    derivatives_packages.maintainer
,
    derivatives_packages.maintainer_name
,
    derivatives_packages.maintainer_email
,
    derivatives_packages.description
,
    derivatives_packages.description_md5
,
    derivatives_packages.source
,
    derivatives_packages.source_version
,
    derivatives_packages.essential
,
    derivatives_packages.depends
,
    derivatives_packages.recommends
,
    derivatives_packages.suggests
,
    derivatives_packages.enhances
,
    derivatives_packages.pre_depends
,
    derivatives_packages.breaks
,
    derivatives_packages.installed_size
,
    derivatives_packages.homepage
,
    derivatives_packages.size
,
    derivatives_packages.build_essential
,
    derivatives_packages.origin
,
    derivatives_packages.sha1
,
    derivatives_packages.replaces
,
    derivatives_packages.section
,
    derivatives_packages.md5sum
,
    derivatives_packages.bugs
,
    derivatives_packages.priority
,
    derivatives_packages.tag
,
    derivatives_packages.task
,
    derivatives_packages.python_version
,
    derivatives_packages.ruby_versions
,
    derivatives_packages.provides
,
    derivatives_packages.conflicts
,
    derivatives_packages.sha256
,
    derivatives_packages.original_maintainer
,
    derivatives_packages.distribution
,
    derivatives_packages.release
,
    derivatives_packages.component
,
    derivatives_packages.multi_arch
,
    derivatives_packages.package_type
   
FROM derivatives_packages;

Index - Schema public

View: public.all_packages_distrelcomparch

public.all_packages_distrelcomparch Structure
F-Key Name Type Description
distribution text
release text
component text
architecture text 
SELECT packages_distrelcomparch.distribution
,
    packages_distrelcomparch.release
,
    packages_distrelcomparch.component
,
    packages_distrelcomparch.architecture
   
FROM packages_distrelcomparch

UNION ALL
 
SELECT ubuntu_packages_distrelcomparch.distribution
,
    ubuntu_packages_distrelcomparch.release
,
    ubuntu_packages_distrelcomparch.component
,
    ubuntu_packages_distrelcomparch.architecture
   
FROM ubuntu_packages_distrelcomparch

UNION ALL
 
SELECT derivatives_packages_distrelcomparch.distribution
,
    derivatives_packages_distrelcomparch.release
,
    derivatives_packages_distrelcomparch.component
,
    derivatives_packages_distrelcomparch.architecture
   
FROM derivatives_packages_distrelcomparch;

Index - Schema public

View: public.all_sources

public.all_sources Structure
F-Key Name Type Description
source text
version debversion
maintainer text
maintainer_name text
maintainer_email text
format text
files text
uploaders text
bin text
architecture text
standards_version text
homepage text
build_depends text
build_depends_indep text
build_conflicts text
build_conflicts_indep text
priority text
section text
distribution text
release text
component text
vcs_type text
vcs_url text
vcs_browser text
python_version text
ruby_versions text
checksums_sha1 text
checksums_sha256 text
original_maintainer text
dm_upload_allowed boolean
testsuite text
autobuild text
extra_source_only boolean 
SELECT sources.source
,
    sources.version
,
    sources.maintainer
,
    sources.maintainer_name
,
    sources.maintainer_email
,
    sources.format
,
    sources.files
,
    sources.uploaders
,
    sources.bin
,
    sources.architecture
,
    sources.standards_version
,
    sources.homepage
,
    sources.build_depends
,
    sources.build_depends_indep
,
    sources.build_conflicts
,
    sources.build_conflicts_indep
,
    sources.priority
,
    sources.section
,
    sources.distribution
,
    sources.release
,
    sources.component
,
    sources.vcs_type
,
    sources.vcs_url
,
    sources.vcs_browser
,
    sources.python_version
,
    sources.ruby_versions
,
    sources.checksums_sha1
,
    sources.checksums_sha256
,
    sources.original_maintainer
,
    sources.dm_upload_allowed
,
    sources.testsuite
,
    sources.autobuild
,
    sources.extra_source_only
   
FROM sources

UNION ALL
 
SELECT ubuntu_sources.source
,
    ubuntu_sources.version
,
    ubuntu_sources.maintainer
,
    ubuntu_sources.maintainer_name
,
    ubuntu_sources.maintainer_email
,
    ubuntu_sources.format
,
    ubuntu_sources.files
,
    ubuntu_sources.uploaders
,
    ubuntu_sources.bin
,
    ubuntu_sources.architecture
,
    ubuntu_sources.standards_version
,
    ubuntu_sources.homepage
,
    ubuntu_sources.build_depends
,
    ubuntu_sources.build_depends_indep
,
    ubuntu_sources.build_conflicts
,
    ubuntu_sources.build_conflicts_indep
,
    ubuntu_sources.priority
,
    ubuntu_sources.section
,
    ubuntu_sources.distribution
,
    ubuntu_sources.release
,
    ubuntu_sources.component
,
    ubuntu_sources.vcs_type
,
    ubuntu_sources.vcs_url
,
    ubuntu_sources.vcs_browser
,
    ubuntu_sources.python_version
,
    ubuntu_sources.ruby_versions
,
    ubuntu_sources.checksums_sha1
,
    ubuntu_sources.checksums_sha256
,
    ubuntu_sources.original_maintainer
,
    ubuntu_sources.dm_upload_allowed
,
    ubuntu_sources.testsuite
,
    ubuntu_sources.autobuild
,
    ubuntu_sources.extra_source_only
   
FROM ubuntu_sources

UNION ALL
 
SELECT derivatives_sources.source
,
    derivatives_sources.version
,
    derivatives_sources.maintainer
,
    derivatives_sources.maintainer_name
,
    derivatives_sources.maintainer_email
,
    derivatives_sources.format
,
    derivatives_sources.files
,
    derivatives_sources.uploaders
,
    derivatives_sources.bin
,
    derivatives_sources.architecture
,
    derivatives_sources.standards_version
,
    derivatives_sources.homepage
,
    derivatives_sources.build_depends
,
    derivatives_sources.build_depends_indep
,
    derivatives_sources.build_conflicts
,
    derivatives_sources.build_conflicts_indep
,
    derivatives_sources.priority
,
    derivatives_sources.section
,
    derivatives_sources.distribution
,
    derivatives_sources.release
,
    derivatives_sources.component
,
    derivatives_sources.vcs_type
,
    derivatives_sources.vcs_url
,
    derivatives_sources.vcs_browser
,
    derivatives_sources.python_version
,
    derivatives_sources.ruby_versions
,
    derivatives_sources.checksums_sha1
,
    derivatives_sources.checksums_sha256
,
    derivatives_sources.original_maintainer
,
    derivatives_sources.dm_upload_allowed
,
    derivatives_sources.testsuite
,
    derivatives_sources.autobuild
,
    derivatives_sources.extra_source_only
   
FROM derivatives_sources;

Index - Schema public

Table: public.archived_bugs

public.archived_bugs Structure
F-Key Name Type Description
id integer PRIMARY KEY
package text
source text
arrival timestamp without time zone
status text
severity bugs_severity
submitter text
submitter_name text
submitter_email text
owner text
owner_name text
owner_email text
done text
done_name text
done_email text
title text
last_modified timestamp without time zone
forwarded text
affects_oldstable boolean
affects_stable boolean
affects_testing boolean
affects_unstable boolean
affects_experimental boolean
affected_packages text
affected_sources text

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.archived_bugs_blockedby

public.archived_bugs_blockedby Structure
F-Key Name Type Description
public.archived_bugs.id id integer PRIMARY KEY
blocker integer PRIMARY KEY

Index - Schema public

Table: public.archived_bugs_blocks

public.archived_bugs_blocks Structure
F-Key Name Type Description
public.archived_bugs.id id integer PRIMARY KEY
blocked integer PRIMARY KEY

Index - Schema public

Table: public.archived_bugs_fixed_in

public.archived_bugs_fixed_in Structure
F-Key Name Type Description
public.archived_bugs.id id integer PRIMARY KEY
version text PRIMARY KEY

Index - Schema public

Table: public.archived_bugs_found_in

public.archived_bugs_found_in Structure
F-Key Name Type Description
public.archived_bugs.id id integer PRIMARY KEY
version text PRIMARY KEY

Index - Schema public

Table: public.archived_bugs_merged_with

public.archived_bugs_merged_with Structure
F-Key Name Type Description
public.archived_bugs.id id integer PRIMARY KEY
merged_with integer PRIMARY KEY

Index - Schema public

Table: public.archived_bugs_packages

public.archived_bugs_packages Structure
F-Key Name Type Description
public.archived_bugs.id id integer PRIMARY KEY
package text PRIMARY KEY
source text

Index - Schema public

Table: public.archived_bugs_stamps

public.archived_bugs_stamps Structure
F-Key Name Type Description
id integer PRIMARY KEY
update_requested bigint
db_updated bigint

Index - Schema public

Table: public.archived_bugs_tags

public.archived_bugs_tags Structure
F-Key Name Type Description
public.archived_bugs.id id integer PRIMARY KEY
tag text PRIMARY KEY

Index - Schema public

Table: public.archived_descriptions

public.archived_descriptions Structure
F-Key Name Type Description
package text PRIMARY KEY
distribution text PRIMARY KEY
release text PRIMARY KEY
component text PRIMARY KEY
language text PRIMARY KEY
description text PRIMARY KEY
long_description text NOT NULL
description_md5 text PRIMARY KEY

Index - Schema public

Table: public.archived_packages

public.archived_packages Structure
F-Key Name Type Description
public.archived_packages_summary.package#1 package text PRIMARY KEY
public.archived_packages_summary.version#1 version debversion PRIMARY KEY
architecture text PRIMARY KEY
maintainer text
maintainer_name text
maintainer_email text
description text
description_md5 text
source text
source_version debversion
essential text
depends text
recommends text
suggests text
enhances text
pre_depends text
breaks text
installed_size bigint
homepage text
size bigint
build_essential text
origin text
sha1 text
replaces text
section text
md5sum text
bugs text
priority text
tag text
task text
python_version text
ruby_versions text
provides text
conflicts text
sha256 text
original_maintainer text
public.archived_packages_summary.distribution#1 distribution text PRIMARY KEY
public.archived_packages_summary.release#1 release text PRIMARY KEY
public.archived_packages_summary.component#1 component text PRIMARY KEY
multi_arch text
package_type text
filename text

Index - Schema public

Table: public.archived_packages_distrelcomparch

public.archived_packages_distrelcomparch Structure
F-Key Name Type Description
distribution text
release text
component text
architecture text

Index - Schema public

Table: public.archived_packages_summary

public.archived_packages_summary Structure
F-Key Name Type Description
package text PRIMARY KEY
version debversion PRIMARY KEY
source text
source_version debversion
maintainer text
maintainer_name text
maintainer_email text
distribution text PRIMARY KEY
release text PRIMARY KEY
component text PRIMARY KEY

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.archived_sources

public.archived_sources Structure
F-Key Name Type Description
source text PRIMARY KEY
version debversion PRIMARY KEY
maintainer text
maintainer_name text
maintainer_email text
format text
files text
uploaders text
bin text
architecture text
standards_version text
homepage text
build_depends text
build_depends_indep text
build_conflicts text
build_conflicts_indep text
priority text
section text
distribution text PRIMARY KEY
release text PRIMARY KEY
component text PRIMARY KEY
vcs_type text
vcs_url text
vcs_browser text
python_version text
ruby_versions text
checksums_sha1 text
checksums_sha256 text
original_maintainer text
dm_upload_allowed boolean
testsuite text
autobuild text
extra_source_only boolean
build_depends_arch text
build_conflicts_arch text

Index - Schema public

Table: public.archived_uploaders

public.archived_uploaders Structure
F-Key Name Type Description
source text
version debversion
distribution text
release text
component text
uploader text
name text
email text

Index - Schema public

View: public.bapase

public.bapase Structure
F-Key Name Type Description
source text
version debversion
type text
bug integer
description text
orphaned_time timestamp without time zone
orphaned_age integer
in_testing date
testing_age integer
testing_version debversion
in_unstable date
unstable_age integer
unstable_version debversion
sync date
sync_age integer
sync_version debversion
first_seen date
first_seen_age integer
upload_date timestamp with time zone
upload_age integer
nmu boolean
nmus bigint
rc_bugs bigint
all_bugs bigint
insts integer
vote integer
maintainer text
last_modified timestamp without time zone
last_modified_age integer 
SELECT s.source
,
    s.version
,
    op.type
,
    op.bug
,
    op.description
,
    op.orphaned_time
,
    
(
     ('now'::text)::date - 
     (op.orphaned_time)::date
) AS orphaned_age
,
    tm.in_testing
,
    
(
     ('now'::text)::date - tm.in_testing
) AS testing_age
,
    tm.testing_version
,
    tm.in_unstable
,
    
(
     ('now'::text)::date - tm.in_unstable
) AS unstable_age
,
    tm.unstable_version
,
    tm.sync
,
    
(
     ('now'::text)::date - tm.sync
) AS sync_age
,
    tm.sync_version
,
    tm.first_seen
,
    
(
     ('now'::text)::date - tm.first_seen
) AS first_seen_age
,
    uh.date AS upload_date
,
    
(
     ('now'::text)::date - 
     (uh.date)::date
) AS upload_age
,
    uh.nmu
,
    COALESCE
(uhn.nmus
     , (0)::bigint
) AS nmus
,
    COALESCE
(b.rc_bugs
     , (0)::bigint
) AS rc_bugs
,
    COALESCE
(b.all_bugs
     , (0)::bigint
) AS all_bugs
,
    COALESCE
(ps.insts
     , 0
) AS insts
,
    COALESCE
(ps.vote
     , 0
) AS vote
,
    s.maintainer
,
    bugs.last_modified
,
    
(
     ('now'::text)::date - 
     (bugs.last_modified)::date
) AS last_modified_age
   
FROM (
     (
           (
                 (
                       (
                             (
                                   (sources_uniq s
     
                                 LEFT JOIN orphaned_packages op 
                                        ON (
                                               (s.source = op.source)
                                         )
                                   )
     
                           LEFT JOIN migrations tm 
                                  ON (
                                         (s.source = tm.source)
                                   )
                             )
     
                     LEFT JOIN upload_history uh 
                            ON (
                                   (
                                         (s.source = uh.source)
                                       AND (s.version = uh.version)
                                   )
                             )
                       )
     
               LEFT JOIN upload_history_nmus uhn 
                      ON (
                             (s.source = uhn.source)
                       )
                 )
     
         LEFT JOIN bugs_count b 
                ON (
                       (s.source = b.source)
                 )
           )
     
   LEFT JOIN popcon_src ps 
          ON (
                 (s.source = ps.source)
           )
     )
     
LEFT JOIN bugs 
    ON (
           (op.bug = bugs.id)
     )
)
  
WHERE (
     (s.distribution = 'debian'::text)
   AND (s.release = 'sid'::text)
);

Index - Schema public

Table: public.bibref

public.bibref Structure
F-Key Name Type Description
source text PRIMARY KEY
key text PRIMARY KEY
value text NOT NULL
package text PRIMARY KEY DEFAULT ''::text
rank integer PRIMARY KEY

Index - Schema public

Table: public.blends_dependencies

public.blends_dependencies Structure
F-Key Name Type Description
public.blends_metadata.blend blend text PRIMARY KEY
task text PRIMARY KEY
package text PRIMARY KEY
dependency character(1)
distribution text
component text
provides boolean

 

public.blends_dependencies Constraints
Name Constraint
blends_dependencies_component_check CHECK ((component = ANY (ARRAY['main'::text, 'main/debian-installer'::text, 'contrib'::text, 'non-free'::text, 'universe'::text, 'universe/debian-installer'::text, 'multiverse'::text, 'restricted'::text, 'local'::text])))
blends_dependencies_dependency_check CHECK ((dependency = ANY (ARRAY['d'::bpchar, 'i'::bpchar, 'r'::bpchar, 's'::bpchar, 'a'::bpchar])))
blends_dependencies_distribution_check CHECK ((distribution = ANY (ARRAY['debian'::text, 'new'::text, 'prospective'::text, 'ubuntu'::text, 'other'::text])))

Index - Schema public

Table: public.blends_dependencies_alternatives

public.blends_dependencies_alternatives Structure
F-Key Name Type Description
public.blends_metadata.blend blend text PRIMARY KEY
task text PRIMARY KEY
alternatives text PRIMARY KEY
dependency character(1)
distribution text
component text
contains_provides boolean

 

public.blends_dependencies_alternatives Constraints
Name Constraint
blends_dependencies_alternatives_component_check CHECK ((component = ANY (ARRAY['main'::text, 'main/debian-installer'::text, 'contrib'::text, 'non-free'::text, 'universe'::text, 'universe/debian-installer'::text, 'multiverse'::text, 'restricted'::text, 'local'::text])))
blends_dependencies_alternatives_dependency_check CHECK ((dependency = ANY (ARRAY['d'::bpchar, 'i'::bpchar, 'r'::bpchar, 's'::bpchar, 'a'::bpchar])))
blends_dependencies_alternatives_distribution_check CHECK ((distribution = ANY (ARRAY['debian'::text, 'new'::text, 'prospective'::text, 'ubuntu'::text, 'other'::text])))

Index - Schema public

Table: public.blends_dependencies_priorities

public.blends_dependencies_priorities Structure
F-Key Name Type Description
dependency character(1)
priority integer

 

public.blends_dependencies_priorities Constraints
Name Constraint
blends_dependencies_priorities_dependency_check CHECK ((dependency = ANY (ARRAY['d'::bpchar, 'i'::bpchar, 'r'::bpchar, 's'::bpchar, 'a'::bpchar])))

Index - Schema public

Table: public.blends_metadata

public.blends_metadata Structure
F-Key Name Type Description
blend text PRIMARY KEY
blendname text
projecturl text
tasksprefix text
homepage text
aliothurl text
projectlist text
logourl text
outputdir text
datadir text
vcsdir text
css text
advertising text
pkglist text
dehsmail text
distribution text

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.blends_prospectivepackages

public.blends_prospectivepackages Structure
F-Key Name Type Description
blend text
package text PRIMARY KEY
source text
maintainer text
maintainer_name text
maintainer_email text
changed_by text
changed_by_name text
changed_by_email text
uploaders text
description text
long_description text
description_md5 text
homepage text
component text
section text
priority text
vcs_type text
vcs_url text
vcs_browser text
wnpp integer
wnpp_type text
wnpp_desc text
license text
chlog_date text
chlog_version debversion

 

public.blends_prospectivepackages Constraints
Name Constraint
check_component CHECK ((component = ANY (ARRAY['main'::text, 'contrib'::text, 'non-free'::text])))

Index - Schema public

Table: public.blends_remarks

public.blends_remarks Structure
F-Key Name Type Description
blend text PRIMARY KEY
task text PRIMARY KEY
package text PRIMARY KEY
remark text

Index - Schema public

Table: public.blends_tasks

public.blends_tasks Structure
F-Key Name Type Description
public.blends_metadata.blend blend text PRIMARY KEY
task text PRIMARY KEY
title text
metapackage boolean
metapackage_name text
section text
enhances text
leaf text
test_always_lang text
description text
long_description text
hashkey text

Index - Schema public

Table: public.blends_unknown_packages

public.blends_unknown_packages Structure
F-Key Name Type Description
package text PRIMARY KEY
pkg_url text
wnpp integer
maintainer text
maintainer_name text
maintainer_email text
description text
long_description text
homepage text
license text
language text
vcs_type text
vcs_url text
vcs_browser text
blend text
task text

Index - Schema public

Table: public.bts_tags

public.bts_tags Structure
F-Key Name Type Description
tag text
tag_type text

Index - Schema public

Table: public.bugs

public.bugs Structure
F-Key Name Type Description
id integer PRIMARY KEY
package text
source text
arrival timestamp without time zone
status text
severity bugs_severity
submitter text
submitter_name text
submitter_email text
owner text
owner_name text
owner_email text
done text
done_name text
done_email text
title text
last_modified timestamp without time zone
forwarded text
affects_oldstable boolean
affects_stable boolean
affects_testing boolean
affects_unstable boolean
affects_experimental boolean
affected_packages text
affected_sources text

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.bugs_blockedby

public.bugs_blockedby Structure
F-Key Name Type Description
public.bugs.id id integer PRIMARY KEY
blocker integer PRIMARY KEY

Index - Schema public

Table: public.bugs_blocks

public.bugs_blocks Structure
F-Key Name Type Description
public.bugs.id id integer PRIMARY KEY
blocked integer PRIMARY KEY

Index - Schema public

View: public.bugs_count

public.bugs_count Structure
F-Key Name Type Description
source text
rc_bugs bigint
all_bugs bigint 
SELECT COALESCE
(b1.source
     , b2.source
) AS source
,
    COALESCE
(b1.rc_bugs
     , (0)::bigint
) AS rc_bugs
,
    COALESCE
(b2.all_bugs
     , (0)::bigint
) AS all_bugs
   
FROM (
     (
      SELECT bugs.source
           ,
            count
           (*) AS rc_bugs
           
        FROM bugs
          
       WHERE (
                 (bugs.severity >= 'serious'::bugs_severity)
               AND (bugs.status = 'pending'::text)
               AND (NOT 
                       (bugs.id IN 
                             (
                              SELECT bugs_merged_with.id
                   
                                FROM bugs_merged_with
                  
                               WHERE (bugs_merged_with.id > bugs_merged_with.merged_with)
                             )
                       )
                 )
           )
          
    GROUP BY bugs.source
     ) b1
     FULL 
  JOIN (
      SELECT bugs.source
           ,
            count
           (*) AS all_bugs
           
        FROM bugs
          
       WHERE (
                 (bugs.status = 'pending'::text)
               AND (NOT 
                       (bugs.id IN 
                             (
                              SELECT bugs_merged_with.id
                   
                                FROM bugs_merged_with
                  
                               WHERE (bugs_merged_with.id > bugs_merged_with.merged_with)
                             )
                       )
                 )
           )
          
    GROUP BY bugs.source
     ) b2 
    ON (
           (b1.source = b2.source)
     )
);

Index - Schema public

Table: public.bugs_fixed_in

public.bugs_fixed_in Structure
F-Key Name Type Description
public.bugs.id id integer PRIMARY KEY
version text PRIMARY KEY

Index - Schema public

Table: public.bugs_found_in

public.bugs_found_in Structure
F-Key Name Type Description
public.bugs.id id integer PRIMARY KEY
version text PRIMARY KEY

Index - Schema public

Table: public.bugs_merged_with

public.bugs_merged_with Structure
F-Key Name Type Description
public.bugs.id id integer PRIMARY KEY
merged_with integer PRIMARY KEY

Index - Schema public

Table: public.bugs_packages

public.bugs_packages Structure
F-Key Name Type Description
public.bugs.id id integer PRIMARY KEY
package text PRIMARY KEY
source text

Index - Schema public

View: public.bugs_rt_affects_oldstable

public.bugs_rt_affects_oldstable Structure
F-Key Name Type Description
id integer
package text
source text 
SELECT bugs_rt_affects_dist.id
,
    bugs_rt_affects_dist.package
,
    bugs_rt_affects_dist.source
   
FROM bugs_rt_affects_dist
('oldstable'::text) bugs_rt_affects_dist
(id
     , package
     , source
);

Index - Schema public

View: public.bugs_rt_affects_stable

public.bugs_rt_affects_stable Structure
F-Key Name Type Description
id integer
package text
source text 
SELECT bugs_rt_affects_dist.id
,
    bugs_rt_affects_dist.package
,
    bugs_rt_affects_dist.source
   
FROM bugs_rt_affects_dist
('stable'::text) bugs_rt_affects_dist
(id
     , package
     , source
);

Index - Schema public

View: public.bugs_rt_affects_testing

public.bugs_rt_affects_testing Structure
F-Key Name Type Description
id integer
package text
source text 
SELECT bugs_rt_affects_dist.id
,
    bugs_rt_affects_dist.package
,
    bugs_rt_affects_dist.source
   
FROM bugs_rt_affects_dist
('testing'::text) bugs_rt_affects_dist
(id
     , package
     , source
);

Index - Schema public

View: public.bugs_rt_affects_testing_and_unstable

public.bugs_rt_affects_testing_and_unstable Structure
F-Key Name Type Description
id integer
package text
source text 
SELECT bugs_rt_affects_testing.id
,
    bugs_rt_affects_testing.package
,
    bugs_rt_affects_testing.source
   
FROM bugs_rt_affects_testing
  
WHERE (bugs_rt_affects_testing.id IN 
     (
      SELECT bugs_rt_affects_unstable.id
           
        FROM bugs_rt_affects_unstable
     )
);

Index - Schema public

View: public.bugs_rt_affects_unstable

public.bugs_rt_affects_unstable Structure
F-Key Name Type Description
id integer
package text
source text 
SELECT bugs_rt_affects_dist.id
,
    bugs_rt_affects_dist.package
,
    bugs_rt_affects_dist.source
   
FROM bugs_rt_affects_dist
('unstable'::text) bugs_rt_affects_dist
(id
     , package
     , source
);

Index - Schema public

Table: public.bugs_stamps

public.bugs_stamps Structure
F-Key Name Type Description
id integer PRIMARY KEY
update_requested bigint
db_updated bigint

Index - Schema public

Table: public.bugs_tags

public.bugs_tags Structure
F-Key Name Type Description
public.bugs.id id integer PRIMARY KEY
tag text PRIMARY KEY

Index - Schema public

Table: public.bugs_usertags

public.bugs_usertags Structure
F-Key Name Type Description
email text
tag text
id integer

Index - Schema public

Table: public.carnivore_emails

public.carnivore_emails Structure
F-Key Name Type Description
id integer PRIMARY KEY
email text PRIMARY KEY

Index - Schema public

Table: public.carnivore_keys

public.carnivore_keys Structure
F-Key Name Type Description
id integer
key text PRIMARY KEY
key_type text PRIMARY KEY

Index - Schema public

Table: public.carnivore_login

public.carnivore_login Structure
F-Key Name Type Description
id integer PRIMARY KEY
login text

Index - Schema public

Table: public.carnivore_names

public.carnivore_names Structure
F-Key Name Type Description
id integer PRIMARY KEY
name text PRIMARY KEY

Index - Schema public

Table: public.ci

public.ci Structure
F-Key Name Type Description
suite text PRIMARY KEY
arch text PRIMARY KEY
source text PRIMARY KEY
version debversion
date timestamp without time zone
run_id text
status text
previous_status text
duration integer
message text

Index - Schema public

Table: public.debian_maintainers

public.debian_maintainers Structure
F-Key Name Type Description
maintainer text
maintainer_name text
maintainer_email text
fingerprint text
package text
granted_by_fingerprint text

Index - Schema public

Table: public.debtags

public.debtags Structure
F-Key Name Type Description
package text NOT NULL
tag text NOT NULL

Index - Schema public

Table: public.deferred

public.deferred Structure
F-Key Name Type Description
source text PRIMARY KEY
version debversion PRIMARY KEY
distribution text
urgency text
date timestamp with time zone
delayed_until timestamp without time zone
delay_remaining interval
changed_by text
changed_by_name text
changed_by_email text
maintainer text
maintainer_name text
maintainer_email text
changes text

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.deferred_architecture

public.deferred_architecture Structure
F-Key Name Type Description
public.deferred.source#1 source text PRIMARY KEY
public.deferred.version#1 version debversion PRIMARY KEY
architecture text PRIMARY KEY

Index - Schema public

Table: public.deferred_binary

public.deferred_binary Structure
F-Key Name Type Description
public.deferred.source#1 source text PRIMARY KEY
public.deferred.version#1 version debversion PRIMARY KEY
package text PRIMARY KEY

Index - Schema public

Table: public.deferred_closes

public.deferred_closes Structure
F-Key Name Type Description
public.deferred.source#1 source text PRIMARY KEY
public.deferred.version#1 version debversion PRIMARY KEY
id integer PRIMARY KEY

Index - Schema public

Table: public.dehs

public.dehs Structure
F-Key Name Type Description
source text PRIMARY KEY
unstable_version debversion
unstable_upstream text
unstable_parsed_version text
unstable_status dehs_status
unstable_last_uptodate timestamp without time zone
experimental_version debversion
experimental_upstream text
experimental_parsed_version text
experimental_status dehs_status
experimental_last_uptodate timestamp without time zone

Index - Schema public

Table: public.derivatives_descriptions

public.derivatives_descriptions Structure
F-Key Name Type Description
package text PRIMARY KEY
distribution text PRIMARY KEY
release text PRIMARY KEY
component text PRIMARY KEY
language text PRIMARY KEY
description text PRIMARY KEY
long_description text NOT NULL
description_md5 text PRIMARY KEY

Index - Schema public

Table: public.derivatives_packages

public.derivatives_packages Structure
F-Key Name Type Description
public.derivatives_packages_summary.package#1 package text PRIMARY KEY
public.derivatives_packages_summary.version#1 version debversion PRIMARY KEY
architecture text PRIMARY KEY
maintainer text
maintainer_name text
maintainer_email text
description text
description_md5 text
source text
source_version debversion
essential text
depends text
recommends text
suggests text
enhances text
pre_depends text
breaks text
installed_size bigint
homepage text
size bigint
build_essential text
origin text
sha1 text
replaces text
section text
md5sum text
bugs text
priority text
tag text
task text
python_version text
ruby_versions text
provides text
conflicts text
sha256 text
original_maintainer text
public.derivatives_packages_summary.distribution#1 distribution text PRIMARY KEY
public.derivatives_packages_summary.release#1 release text PRIMARY KEY
public.derivatives_packages_summary.component#1 component text PRIMARY KEY
multi_arch text
package_type text
filename text

Index - Schema public

Table: public.derivatives_packages_distrelcomparch

public.derivatives_packages_distrelcomparch Structure
F-Key Name Type Description
distribution text
release text
component text
architecture text

Index - Schema public

Table: public.derivatives_packages_summary

public.derivatives_packages_summary Structure
F-Key Name Type Description
package text PRIMARY KEY
version debversion PRIMARY KEY
source text
source_version debversion
maintainer text
maintainer_name text
maintainer_email text
distribution text PRIMARY KEY
release text PRIMARY KEY
component text PRIMARY KEY

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.derivatives_sources

public.derivatives_sources Structure
F-Key Name Type Description
source text PRIMARY KEY
version debversion PRIMARY KEY
maintainer text
maintainer_name text
maintainer_email text
format text
files text
uploaders text
bin text
architecture text
standards_version text
homepage text
build_depends text
build_depends_indep text
build_conflicts text
build_conflicts_indep text
priority text
section text
distribution text PRIMARY KEY
release text PRIMARY KEY
component text PRIMARY KEY
vcs_type text
vcs_url text
vcs_browser text
python_version text
ruby_versions text
checksums_sha1 text
checksums_sha256 text
original_maintainer text
dm_upload_allowed boolean
testsuite text
autobuild text
extra_source_only boolean
build_depends_arch text
build_conflicts_arch text

Index - Schema public

Table: public.derivatives_uploaders

public.derivatives_uploaders Structure
F-Key Name Type Description
source text
version debversion
distribution text
release text
component text
uploader text
name text
email text

Index - Schema public

Table: public.description_imports

public.description_imports Structure
F-Key Name Type Description
distribution text PRIMARY KEY
release text PRIMARY KEY
component text PRIMARY KEY
language text PRIMARY KEY
translationfile text NOT NULL
translationfile_sha1 text NOT NULL
import_date timestamp without time zone DEFAULT now()

Index - Schema public

Table: public.descriptions

public.descriptions Structure
F-Key Name Type Description
package text PRIMARY KEY
distribution text PRIMARY KEY
release text PRIMARY KEY
component text PRIMARY KEY
language text PRIMARY KEY
description text PRIMARY KEY
long_description text NOT NULL
description_md5 text PRIMARY KEY

Index - Schema public

Table: public.duck

public.duck Structure
F-Key Name Type Description
source text PRIMARY KEY

Index - Schema public

Table: public.edam

public.edam Structure
F-Key Name Type Description
source text PRIMARY KEY
package text PRIMARY KEY
topics text[]
scopes jsonb

Index - Schema public

Table: public.ftp_autorejects

public.ftp_autorejects Structure
F-Key Name Type Description
tag text PRIMARY KEY
autoreject_type ftp_autoreject_type
autoreject_level ftp_autoreject_level

Index - Schema public

Table: public.hints

public.hints Structure
F-Key Name Type Description
source text
version debversion
architecture text
type text
argument text
file text
comment text

Index - Schema public

Table: public.key_packages

public.key_packages Structure
F-Key Name Type Description
source text PRIMARY KEY
reason text

Index - Schema public

Table: public.ldap

public.ldap Structure
F-Key Name Type Description
uid numeric PRIMARY KEY
login text
cn text
sn text
expire boolean
location text
country text
activity_from timestamp with time zone
activity_from_info text
activity_pgp timestamp with time zone
activity_pgp_info text
gecos text
birthdate date
gender numeric
fingerprint text

Index - Schema public

Table: public.lintian_logs

public.lintian_logs Structure
F-Key Name Type Description
ts timestamp without time zone NOT NULL
lintian_version debversion NOT NULL
source text NOT NULL
version debversion NOT NULL
package text
package_version debversion
architecture text
result text NOT NULL
duration integer NOT NULL
message text

Index - Schema public

Table: public.lintian_results

public.lintian_results Structure
F-Key Name Type Description
lintian_version debversion NOT NULL
source text NOT NULL
version debversion NOT NULL
package text
package_version debversion
architecture text
tag_type lintian_tagtype
tag text
information text

Index - Schema public

Table: public.lintian_tags_descriptions

public.lintian_tags_descriptions Structure
F-Key Name Type Description
lintian_version debversion NOT NULL
tag_type lintian_tagtype
tag text
description text

Index - Schema public

View: public.mentors_most_recent_package_versions

public.mentors_most_recent_package_versions Structure
F-Key Name Type Description
id numeric
changes text
closes text
component text
distribution text
package text
uploaded timestamp without time zone
uploader text
version text 
SELECT mu.id
,
    mu.changes
,
    mu.closes
,
    mu.component
,
    mu.distribution
,
    mu.package
,
    mu.uploaded
,
    mu.uploader
,
    mu.version
   
FROM (mentors_raw_uploads mu
     
LEFT JOIN mentors_raw_uploads mu2 
    ON (
           (
                 (mu.package = mu2.package)
               AND (mu.uploaded < mu2.uploaded)
               AND (mu.distribution = mu2.distribution)
           )
     )
)
  
WHERE (mu2.id IS NULL);

Index - Schema public

Table: public.mentors_raw_uploads

public.mentors_raw_uploads Structure
F-Key Name Type Description
id numeric PRIMARY KEY
changes text
closes text
component text
distribution text
package text
uploaded timestamp without time zone
uploader text
version text

Index - Schema public

Table: public.migration_excuses

public.migration_excuses Structure
F-Key Name Type Description
item_name text PRIMARY KEY
source text NOT NULL
migration_policy_verdict text
old_version debversion
new_version debversion
is_candidate boolean NOT NULL
excuses text[]
reason text[]
hints text[]
policy_info text
dependencies text
invalidated_by_other_package boolean
missing_builds text
old_binaries text

Index - Schema public

Table: public.migrations

public.migrations Structure
F-Key Name Type Description
source text PRIMARY KEY
in_testing date
testing_version debversion
in_unstable date
unstable_version debversion
sync date
sync_version debversion
first_seen date

Index - Schema public

Table: public.new_packages

public.new_packages Structure
F-Key Name Type Description
package text PRIMARY KEY
version text PRIMARY KEY
architecture text PRIMARY KEY
maintainer text
description text
source text
depends text
recommends text
suggests text
enhances text
pre_depends text
breaks text
replaces text
provides text
conflicts text
installed_size integer
homepage text
long_description text
section text
component text
distribution text
license text

Index - Schema public

View: public.new_packages_madison

public.new_packages_madison Structure
F-Key Name Type Description
package text
version text
source text
release text
architecture text
component text
distribution text 
SELECT new_packages.package
,
    new_packages.version
,
    new_packages.source
,
    new_packages.distribution AS release
,
    new_packages.architecture
,
    new_packages.component
,
    'debian'::text AS distribution
   
FROM new_packages;

Index - Schema public

Table: public.new_sources

public.new_sources Structure
F-Key Name Type Description
source text PRIMARY KEY
version text PRIMARY KEY
maintainer text
maintainer_name text
maintainer_email text
format text
files text
uploaders text
binaries text
changed_by text
architecture text
homepage text
vcs_type text
vcs_url text
vcs_browser text
section text
component text
distribution text PRIMARY KEY
closes integer
license text
last_modified timestamp without time zone
queue text

Index - Schema public

View: public.new_sources_madison

public.new_sources_madison Structure
F-Key Name Type Description
source text
version text
component text
architecture text
release text
distribution text 
SELECT new_sources.source
,
    new_sources.version
,
    new_sources.component
,
    new_sources.architecture
,
    new_sources.distribution AS release
,
    'debian'::text AS distribution
   
FROM new_sources;

Index - Schema public

Table: public.orphaned_packages

public.orphaned_packages Structure
F-Key Name Type Description
source text PRIMARY KEY
type text
bug integer
description text
orphaned_time timestamp without time zone

Index - Schema public

Table: public.package_removal

public.package_removal Structure
F-Key Name Type Description
public.package_removal_batch.id batch_id integer PRIMARY KEY
name text PRIMARY KEY
version debversion PRIMARY KEY
arch_array text[]

Index - Schema public

Table: public.package_removal_batch

public.package_removal_batch Structure
F-Key Name Type Description
id integer PRIMARY KEY
time timestamp without time zone
ftpmaster text
distribution text
requestor text
reasons text

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.packages

public.packages Structure
F-Key Name Type Description
package text PRIMARY KEY
version debversion PRIMARY KEY
architecture text PRIMARY KEY
maintainer text
maintainer_name text
maintainer_email text
description text
description_md5 text
source text
source_version debversion
essential text
depends text
recommends text
suggests text
enhances text
pre_depends text
breaks text
installed_size bigint
homepage text
size bigint
build_essential text
origin text
sha1 text
replaces text
section text
md5sum text
bugs text
priority text
tag text
task text
python_version text
ruby_versions text
provides text
conflicts text
sha256 text
original_maintainer text
distribution text PRIMARY KEY
release text PRIMARY KEY
component text PRIMARY KEY
multi_arch text
package_type text
filename text

Index - Schema public

Table: public.packages_distrelcomparch

public.packages_distrelcomparch Structure
F-Key Name Type Description
distribution text
release text
component text
architecture text

Index - Schema public

Table: public.packages_summary

public.packages_summary Structure
F-Key Name Type Description
package text PRIMARY KEY
version debversion PRIMARY KEY
source text
source_version debversion
maintainer text
maintainer_name text
maintainer_email text
distribution text PRIMARY KEY
release text PRIMARY KEY
component text PRIMARY KEY

Index - Schema public

Table: public.patches

public.patches Structure
F-Key Name Type Description
hash text
file text
patch text
description text
forwarded text
last_update timestamp without time zone
author text
origin text
forwarded_short text
headers text
upstream_bug text
debian_bug text
forwarded_url text
invalid_reason text

Index - Schema public

Table: public.patches_status

public.patches_status Structure
F-Key Name Type Description
hash text
file text
status text

Index - Schema public

Table: public.piuparts_status

public.piuparts_status Structure
F-Key Name Type Description
section text
source text
version text
status text

Index - Schema public

Table: public.popcon

public.popcon Structure
F-Key Name Type Description
package text PRIMARY KEY
insts integer
vote integer
olde integer
recent integer
nofiles integer

Index - Schema public

Table: public.popcon_src

public.popcon_src Structure
F-Key Name Type Description
source text PRIMARY KEY
insts integer
vote integer
olde integer
recent integer
nofiles integer

Index - Schema public

Table: public.popcon_src_average

public.popcon_src_average Structure
F-Key Name Type Description
source text PRIMARY KEY
insts integer
vote integer
olde integer
recent integer
nofiles integer

Index - Schema public

Table: public.ports_descriptions

public.ports_descriptions Structure
F-Key Name Type Description
package text PRIMARY KEY
distribution text PRIMARY KEY
release text PRIMARY KEY
component text PRIMARY KEY
language text PRIMARY KEY
description text PRIMARY KEY
long_description text NOT NULL
description_md5 text PRIMARY KEY

Index - Schema public

Table: public.ports_packages

public.ports_packages Structure
F-Key Name Type Description
package text PRIMARY KEY
version debversion PRIMARY KEY
architecture text PRIMARY KEY
maintainer text
maintainer_name text
maintainer_email text
description text
description_md5 text
source text
source_version debversion
essential text
depends text
recommends text
suggests text
enhances text
pre_depends text
breaks text
installed_size bigint
homepage text
size bigint
build_essential text
origin text
sha1 text
replaces text
section text
md5sum text
bugs text
priority text
tag text
task text
python_version text
ruby_versions text
provides text
conflicts text
sha256 text
original_maintainer text
distribution text PRIMARY KEY
release text PRIMARY KEY
component text PRIMARY KEY
multi_arch text
package_type text
filename text

Index - Schema public

Table: public.ports_packages_distrelcomparch

public.ports_packages_distrelcomparch Structure
F-Key Name Type Description
distribution text
release text
component text
architecture text

Index - Schema public

Table: public.ports_packages_summary

public.ports_packages_summary Structure
F-Key Name Type Description
package text PRIMARY KEY
version debversion PRIMARY KEY
source text
source_version debversion
maintainer text
maintainer_name text
maintainer_email text
distribution text PRIMARY KEY
release text PRIMARY KEY
component text PRIMARY KEY

Index - Schema public

Table: public.ports_sources

public.ports_sources Structure
F-Key Name Type Description
source text PRIMARY KEY
version debversion PRIMARY KEY
maintainer text
maintainer_name text
maintainer_email text
format text
files text
uploaders text
bin text
architecture text
standards_version text
homepage text
build_depends text
build_depends_indep text
build_conflicts text
build_conflicts_indep text
priority text
section text
distribution text PRIMARY KEY
release text PRIMARY KEY
component text PRIMARY KEY
vcs_type text
vcs_url text
vcs_browser text
python_version text
ruby_versions text
checksums_sha1 text
checksums_sha256 text
original_maintainer text
dm_upload_allowed boolean
testsuite text
autobuild text
extra_source_only boolean
build_depends_arch text
build_conflicts_arch text

Index - Schema public

Table: public.ports_uploaders

public.ports_uploaders Structure
F-Key Name Type Description
source text
version debversion
distribution text
release text
component text
uploader text
name text
email text

Index - Schema public

Table: public.potential_bug_closures

public.potential_bug_closures Structure
F-Key Name Type Description
id integer
source text
distribution text
origin text

Index - Schema public

Table: public.pseudo_packages

public.pseudo_packages Structure
F-Key Name Type Description
package text PRIMARY KEY
maintainer text
maintainer_name text
maintainer_email text
description text

Index - Schema public

Table: public.registry

public.registry Structure
F-Key Name Type Description
source text PRIMARY KEY
name text PRIMARY KEY
entry text PRIMARY KEY

Index - Schema public

Table: public.releases

public.releases Structure
F-Key Name Type Description
release text PRIMARY KEY
releasedate date
role text
releaseversion text
distribution text
sort integer

Index - Schema public

View: public.relevant_hints

public.relevant_hints Structure
F-Key Name Type Description
source text
version debversion
architecture text
type text
argument text
file text
comment text 
SELECT hints.source
,
    hints.version
,
    hints.architecture
,
    hints.type
,
    hints.argument
,
    hints.file
,
    hints.comment
   
FROM hints
  
WHERE (
     (hints.version IS NULL)
    OR (hints.type = 'approve'::text)
    OR (
           (hints.type = ANY 
                 (ARRAY['unblock'::text
                       ,'age-days'::text
                       ,'hint'::text
                       ,'easy'::text]
                 )
           )
         AND (
                 (hints.source
                       , (hints.version)::text
                 ) IN 
                 (
                  SELECT sources.source
                       ,
            sources.version
           
                    FROM sources
          
                   WHERE (sources.release = 'sid'::text)
                 )
           )
     )
    OR (
           (hints.type = 'remove'::text)
         AND (
                 (hints.source
                       , (hints.version)::text
                 ) IN 
                 (
                  SELECT sources.source
                       ,
            sources.version
           
                    FROM sources
          
                   WHERE (sources.release = 'squeeze'::text)
                 )
           )
     )
);

Index - Schema public

Table: public.reproducible

public.reproducible Structure
F-Key Name Type Description
source text PRIMARY KEY
version debversion
release text PRIMARY KEY
architecture text PRIMARY KEY
status text

Index - Schema public

Table: public.screenshots

public.screenshots Structure
F-Key Name Type Description
package text NOT NULL
version text
homepage text
maintainer_name text
maintainer_email text
description text
section text
screenshot_url text NOT NULL
large_image_url text NOT NULL
small_image_url text PRIMARY KEY

Index - Schema public

Table: public.security_issues

public.security_issues Structure
F-Key Name Type Description
source text PRIMARY KEY
issue text PRIMARY KEY
description text
scope security_issues_scope
bug integer

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.security_issues_releases

public.security_issues_releases Structure
F-Key Name Type Description
public.security_issues.source#1 source text PRIMARY KEY
public.security_issues.issue#1 issue text PRIMARY KEY
release text PRIMARY KEY
fixed_version text
status security_issues_releases_status
urgency text
nodsa text
nodsa_reason text
next_point_update boolean

Index - Schema public

Table: public.sources

public.sources Structure
F-Key Name Type Description
source text PRIMARY KEY
version debversion PRIMARY KEY
maintainer text
maintainer_name text
maintainer_email text
format text
files text
uploaders text
bin text
architecture text
standards_version text
homepage text
build_depends text
build_depends_indep text
build_conflicts text
build_conflicts_indep text
priority text
section text
distribution text PRIMARY KEY
release text PRIMARY KEY
component text PRIMARY KEY
vcs_type text
vcs_url text
vcs_browser text
python_version text
ruby_versions text
checksums_sha1 text
checksums_sha256 text
original_maintainer text
dm_upload_allowed boolean
testsuite text
autobuild text
extra_source_only boolean
build_depends_arch text
build_conflicts_arch text

Index - Schema public

Table: public.sources_files

public.sources_files Structure
F-Key Name Type Description
source text
version debversion
distribution text
release text
component text
format text
hash text
size bigint
file text

Index - Schema public

View: public.sources_patches

public.sources_patches Structure
F-Key Name Type Description
source text
version debversion
distribution text
release text
component text
format text
hash text
size bigint
file text 
SELECT sources_files.source
,
    sources_files.version
,
    sources_files.distribution
,
    sources_files.release
,
    sources_files.component
,
    sources_files.format
,
    sources_files.hash
,
    sources_files.size
,
    sources_files.file
   
FROM sources_files
  
WHERE (
     (sources_files.format = '3.0 (quilt)'::text
)
AND (sources_files.file ~ 'debian\.tar\.([a-zA-Z0-9]+)$'::text
)     
AND    (sources_files.release IN 
(
SELECT releases.release
           
  FROM releases
          
 WHERE (releases.sort >= 
           (
            SELECT releases_1.sort
                   
              FROM releases releases_1
                  
             WHERE (releases_1.role = 'oldstable'::text)
           )
     )
)
)     
)           ;

Index - Schema public

View: public.sources_popcon

public.sources_popcon Structure
F-Key Name Type Description
source text
insts integer
vote integer
olde integer
recent integer
nofiles integer 
SELECT popcon_src.source
,
    popcon_src.insts
,
    popcon_src.vote
,
    popcon_src.olde
,
    popcon_src.recent
,
    popcon_src.nofiles
   
FROM popcon_src;

Index - Schema public

View: public.sources_redundant

public.sources_redundant Structure
F-Key Name Type Description
source text
version debversion
maintainer text
maintainer_name text
maintainer_email text
format text
files text
uploaders text
bin text
architecture text
standards_version text
homepage text
build_depends text
build_depends_indep text
build_conflicts text
build_conflicts_indep text
priority text
section text
distribution text
release text
component text
vcs_type text
vcs_url text
vcs_browser text
python_version text
ruby_versions text
checksums_sha1 text
checksums_sha256 text
original_maintainer text
dm_upload_allowed boolean
testsuite text
autobuild text
extra_source_only boolean 
SELECT s1.source
,
    s1.version
,
    s1.maintainer
,
    s1.maintainer_name
,
    s1.maintainer_email
,
    s1.format
,
    s1.files
,
    s1.uploaders
,
    s1.bin
,
    s1.architecture
,
    s1.standards_version
,
    s1.homepage
,
    s1.build_depends
,
    s1.build_depends_indep
,
    s1.build_conflicts
,
    s1.build_conflicts_indep
,
    s1.priority
,
    s1.section
,
    s1.distribution
,
    s1.release
,
    s1.component
,
    s1.vcs_type
,
    s1.vcs_url
,
    s1.vcs_browser
,
    s1.python_version
,
    s1.ruby_versions
,
    s1.checksums_sha1
,
    s1.checksums_sha256
,
    s1.original_maintainer
,
    s1.dm_upload_allowed
,
    s1.testsuite
,
    s1.autobuild
,
    s1.extra_source_only
   
FROM sources s1
  
WHERE (EXISTS 
     (
      SELECT s2.source
           ,
            s2.version
           ,
            s2.maintainer
           ,
            s2.maintainer_name
           ,
            s2.maintainer_email
           ,
            s2.format
           ,
            s2.files
           ,
            s2.uploaders
           ,
            s2.bin
           ,
            s2.architecture
           ,
            s2.standards_version
           ,
            s2.homepage
           ,
            s2.build_depends
           ,
            s2.build_depends_indep
           ,
            s2.build_conflicts
           ,
            s2.build_conflicts_indep
           ,
            s2.priority
           ,
            s2.section
           ,
            s2.distribution
           ,
            s2.release
           ,
            s2.component
           ,
            s2.vcs_type
           ,
            s2.vcs_url
           ,
            s2.vcs_browser
           ,
            s2.python_version
           ,
            s2.ruby_versions
           ,
            s2.checksums_sha1
           ,
            s2.checksums_sha256
           ,
            s2.original_maintainer
           ,
            s2.dm_upload_allowed
           ,
            s2.testsuite
           ,
            s2.autobuild
           ,
            s2.extra_source_only
           
        FROM sources s2
          
       WHERE (
                 (s1.source = s2.source)
               AND (s1.distribution = s2.distribution)
               AND (s1.release = s2.release)
               AND (s1.component = s2.component)
               AND (s2.version > s1.version)
           )
     )
);

Index - Schema public

View: public.sources_uniq

public.sources_uniq Structure
F-Key Name Type Description
source text
version debversion
maintainer text
maintainer_name text
maintainer_email text
format text
files text
uploaders text
bin text
architecture text
standards_version text
homepage text
build_depends text
build_depends_indep text
build_conflicts text
build_conflicts_indep text
priority text
section text
distribution text
release text
component text
vcs_type text
vcs_url text
vcs_browser text
python_version text
ruby_versions text
checksums_sha1 text
checksums_sha256 text
original_maintainer text
dm_upload_allowed boolean
testsuite text
autobuild text
extra_source_only boolean 
SELECT s1.source
,
    s1.version
,
    s1.maintainer
,
    s1.maintainer_name
,
    s1.maintainer_email
,
    s1.format
,
    s1.files
,
    s1.uploaders
,
    s1.bin
,
    s1.architecture
,
    s1.standards_version
,
    s1.homepage
,
    s1.build_depends
,
    s1.build_depends_indep
,
    s1.build_conflicts
,
    s1.build_conflicts_indep
,
    s1.priority
,
    s1.section
,
    s1.distribution
,
    s1.release
,
    s1.component
,
    s1.vcs_type
,
    s1.vcs_url
,
    s1.vcs_browser
,
    s1.python_version
,
    s1.ruby_versions
,
    s1.checksums_sha1
,
    s1.checksums_sha256
,
    s1.original_maintainer
,
    s1.dm_upload_allowed
,
    s1.testsuite
,
    s1.autobuild
,
    s1.extra_source_only
   
FROM sources s1
  
WHERE (NOT 
     (EXISTS 
           (
            SELECT s2.source
                 ,
            s2.version
                 ,
            s2.maintainer
                 ,
            s2.maintainer_name
                 ,
            s2.maintainer_email
                 ,
            s2.format
                 ,
            s2.files
                 ,
            s2.uploaders
                 ,
            s2.bin
                 ,
            s2.architecture
                 ,
            s2.standards_version
                 ,
            s2.homepage
                 ,
            s2.build_depends
                 ,
            s2.build_depends_indep
                 ,
            s2.build_conflicts
                 ,
            s2.build_conflicts_indep
                 ,
            s2.priority
                 ,
            s2.section
                 ,
            s2.distribution
                 ,
            s2.release
                 ,
            s2.component
                 ,
            s2.vcs_type
                 ,
            s2.vcs_url
                 ,
            s2.vcs_browser
                 ,
            s2.python_version
                 ,
            s2.ruby_versions
                 ,
            s2.checksums_sha1
                 ,
            s2.checksums_sha256
                 ,
            s2.original_maintainer
                 ,
            s2.dm_upload_allowed
                 ,
            s2.testsuite
                 ,
            s2.autobuild
                 ,
            s2.extra_source_only
           
              FROM sources s2
          
             WHERE (
                       (s1.source = s2.source)
                     AND (s1.distribution = s2.distribution)
                     AND (s1.release = s2.release)
                     AND (s1.component = s2.component)
                     AND (s2.version > s1.version)
                 )
           )
     )
);

Index - Schema public

View: public.sponsorship_requests

public.sponsorship_requests Structure
F-Key Name Type Description
id integer
source text
version text
title text 
SELECT bugs.id
,
    "substring"
(bugs.title
     ,'^RFS: ([^/]*)/'::text
)      AS source
,     
    "substring"
(     bugs.title
,'/([^ ]*)( |$)'::text
)            AS version
,           
    bugs.title
   
FROM         bugs
  
WHERE        (
(     bugs.package = 'sponsorship-requests'::text)
AND    (bugs.status = 'pending'::text)
)           ;

Index - Schema public

Table: public.testing_autoremovals

public.testing_autoremovals Structure
F-Key Name Type Description
source text
version text
bugs text
first_seen bigint
last_checked bigint
removal_time bigint
rdeps text
buggy_deps text
bugs_deps text
rdeps_popcon bigint

Index - Schema public

Table: public.timestamps

public.timestamps Structure
F-Key Name Type Description
id serial PRIMARY KEY
source text
command text
start_time timestamp without time zone
end_time timestamp without time zone

Index - Schema public

Table: public.ubuntu_bugs

public.ubuntu_bugs Structure
F-Key Name Type Description
bug integer PRIMARY KEY
title text
reporter_login text
reporter_name text
duplicate_of integer
date_reported text
date_updated text
security boolean
patches boolean

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.ubuntu_bugs_duplicates

public.ubuntu_bugs_duplicates Structure
F-Key Name Type Description
public.ubuntu_bugs.bug bug integer PRIMARY KEY
duplicate integer PRIMARY KEY

Index - Schema public

Table: public.ubuntu_bugs_subscribers

public.ubuntu_bugs_subscribers Structure
F-Key Name Type Description
public.ubuntu_bugs.bug bug integer
subscriber_login text
subscriber_name text

Index - Schema public

Table: public.ubuntu_bugs_tags

public.ubuntu_bugs_tags Structure
F-Key Name Type Description
public.ubuntu_bugs.bug bug integer PRIMARY KEY
tag text PRIMARY KEY

Index - Schema public

Table: public.ubuntu_bugs_tasks

public.ubuntu_bugs_tasks Structure
F-Key Name Type Description
public.ubuntu_bugs.bug bug integer PRIMARY KEY
package text PRIMARY KEY
distro text PRIMARY KEY
status text
importance text
component text
milestone text
date_created text
date_assigned text
date_closed text
date_incomplete text
date_confirmed text
date_inprogress text
date_fix_committed text
date_fix_released text
date_left_new text
date_triaged text
date_left_closed text
watch text
reporter_login text
reporter_name text
assignee_login text
assignee_name text

Index - Schema public

Table: public.ubuntu_description_imports

public.ubuntu_description_imports Structure
F-Key Name Type Description
distribution text PRIMARY KEY
release text PRIMARY KEY
component text PRIMARY KEY
language text PRIMARY KEY
translationfile text NOT NULL
translationfile_sha1 text NOT NULL
import_date timestamp without time zone DEFAULT now()

Index - Schema public

Table: public.ubuntu_descriptions

public.ubuntu_descriptions Structure
F-Key Name Type Description
package text PRIMARY KEY
distribution text PRIMARY KEY
release text PRIMARY KEY
component text PRIMARY KEY
language text PRIMARY KEY
description text PRIMARY KEY
long_description text NOT NULL
description_md5 text PRIMARY KEY

Index - Schema public

Table: public.ubuntu_packages

public.ubuntu_packages Structure
F-Key Name Type Description
public.ubuntu_packages_summary.package#1 package text PRIMARY KEY
public.ubuntu_packages_summary.version#1 version debversion PRIMARY KEY
architecture text PRIMARY KEY
maintainer text
maintainer_name text
maintainer_email text
description text
description_md5 text
source text
source_version debversion
essential text
depends text
recommends text
suggests text
enhances text
pre_depends text
breaks text
installed_size bigint
homepage text
size bigint
build_essential text
origin text
sha1 text
replaces text
section text
md5sum text
bugs text
priority text
tag text
task text
python_version text
ruby_versions text
provides text
conflicts text
sha256 text
original_maintainer text
public.ubuntu_packages_summary.distribution#1 distribution text PRIMARY KEY
public.ubuntu_packages_summary.release#1 release text PRIMARY KEY
public.ubuntu_packages_summary.component#1 component text PRIMARY KEY
multi_arch text
package_type text
filename text

Index - Schema public

Table: public.ubuntu_packages_distrelcomparch

public.ubuntu_packages_distrelcomparch Structure
F-Key Name Type Description
distribution text
release text
component text
architecture text

Index - Schema public

Table: public.ubuntu_packages_summary

public.ubuntu_packages_summary Structure
F-Key Name Type Description
package text PRIMARY KEY
version debversion PRIMARY KEY
source text
source_version debversion
maintainer text
maintainer_name text
maintainer_email text
distribution text PRIMARY KEY
release text PRIMARY KEY
component text PRIMARY KEY

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.ubuntu_popcon

public.ubuntu_popcon Structure
F-Key Name Type Description
package text PRIMARY KEY
insts integer
vote integer
olde integer
recent integer
nofiles integer

Index - Schema public

Table: public.ubuntu_popcon_src

public.ubuntu_popcon_src Structure
F-Key Name Type Description
source text PRIMARY KEY
insts integer
vote integer
olde integer
recent integer
nofiles integer

Index - Schema public

Table: public.ubuntu_popcon_src_average

public.ubuntu_popcon_src_average Structure
F-Key Name Type Description
source text PRIMARY KEY
insts integer
vote integer
olde integer
recent integer
nofiles integer

Index - Schema public

Table: public.ubuntu_sources

public.ubuntu_sources Structure
F-Key Name Type Description
source text PRIMARY KEY
version debversion PRIMARY KEY
maintainer text
maintainer_name text
maintainer_email text
format text
files text
uploaders text
bin text
architecture text
standards_version text
homepage text
build_depends text
build_depends_indep text
build_conflicts text
build_conflicts_indep text
priority text
section text
distribution text PRIMARY KEY
release text PRIMARY KEY
component text PRIMARY KEY
vcs_type text
vcs_url text
vcs_browser text
python_version text
ruby_versions text
checksums_sha1 text
checksums_sha256 text
original_maintainer text
dm_upload_allowed boolean
testsuite text
autobuild text
extra_source_only boolean
build_depends_arch text
build_conflicts_arch text

Index - Schema public

View: public.ubuntu_sources_popcon

public.ubuntu_sources_popcon Structure
F-Key Name Type Description
source text
insts integer
vote integer
olde integer
recent integer
nofiles integer 
SELECT ubuntu_popcon_src.source
,
    ubuntu_popcon_src.insts
,
    ubuntu_popcon_src.vote
,
    ubuntu_popcon_src.olde
,
    ubuntu_popcon_src.recent
,
    ubuntu_popcon_src.nofiles
   
FROM ubuntu_popcon_src;

Index - Schema public

Table: public.ubuntu_upload_history

public.ubuntu_upload_history Structure
F-Key Name Type Description
source text PRIMARY KEY
version debversion PRIMARY KEY
date timestamp with time zone
changed_by text
changed_by_name text
changed_by_email text
maintainer text
maintainer_name text
maintainer_email text
nmu boolean
signed_by text
signed_by_name text
signed_by_email text
key_id text
distribution text
component text
file text
fingerprint text
original_maintainer text
original_maintainer_name text
original_maintainer_email text

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.ubuntu_upload_history_closes

public.ubuntu_upload_history_closes Structure
F-Key Name Type Description
public.ubuntu_upload_history.source#1 source text PRIMARY KEY
public.ubuntu_upload_history.version#1 version debversion PRIMARY KEY
bug integer PRIMARY KEY
file text

Index - Schema public

Table: public.ubuntu_upload_history_launchpad_closes

public.ubuntu_upload_history_launchpad_closes Structure
F-Key Name Type Description
public.ubuntu_upload_history.source#1 source text PRIMARY KEY
public.ubuntu_upload_history.version#1 version debversion PRIMARY KEY
bug integer PRIMARY KEY
file text

Index - Schema public

Table: public.ubuntu_uploaders

public.ubuntu_uploaders Structure
F-Key Name Type Description
source text
version debversion
distribution text
release text
component text
uploader text
name text
email text

Index - Schema public

Table: public.udd_logs

public.udd_logs Structure
F-Key Name Type Description
importer text PRIMARY KEY
time timestamp with time zone PRIMARY KEY
duration integer
status integer
log text

Index - Schema public

Table: public.unofficial_descriptions

public.unofficial_descriptions Structure
F-Key Name Type Description
package text PRIMARY KEY
distribution text PRIMARY KEY
release text PRIMARY KEY
component text PRIMARY KEY
language text PRIMARY KEY
description text PRIMARY KEY
long_description text NOT NULL
description_md5 text PRIMARY KEY

Index - Schema public

Table: public.unofficial_packages

public.unofficial_packages Structure
F-Key Name Type Description
public.unofficial_packages_summary.package#1 package text PRIMARY KEY
public.unofficial_packages_summary.version#1 version debversion PRIMARY KEY
architecture text PRIMARY KEY
maintainer text
maintainer_name text
maintainer_email text
description text
description_md5 text
source text
source_version debversion
essential text
depends text
recommends text
suggests text
enhances text
pre_depends text
breaks text
installed_size bigint
homepage text
size bigint
build_essential text
origin text
sha1 text
replaces text
section text
md5sum text
bugs text
priority text
tag text
task text
python_version text
ruby_versions text
provides text
conflicts text
sha256 text
original_maintainer text
public.unofficial_packages_summary.distribution#1 distribution text PRIMARY KEY
public.unofficial_packages_summary.release#1 release text PRIMARY KEY
public.unofficial_packages_summary.component#1 component text PRIMARY KEY
multi_arch text
package_type text
filename text

Index - Schema public

Table: public.unofficial_packages_distrelcomparch

public.unofficial_packages_distrelcomparch Structure
F-Key Name Type Description
distribution text
release text
component text
architecture text

Index - Schema public

Table: public.unofficial_packages_summary

public.unofficial_packages_summary Structure
F-Key Name Type Description
package text PRIMARY KEY
version debversion PRIMARY KEY
source text
source_version debversion
maintainer text
maintainer_name text
maintainer_email text
distribution text PRIMARY KEY
release text PRIMARY KEY
component text PRIMARY KEY

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.unofficial_sources

public.unofficial_sources Structure
F-Key Name Type Description
source text PRIMARY KEY
version debversion PRIMARY KEY
maintainer text
maintainer_name text
maintainer_email text
format text
files text
uploaders text
bin text
architecture text
standards_version text
homepage text
build_depends text
build_depends_indep text
build_conflicts text
build_conflicts_indep text
priority text
section text
distribution text PRIMARY KEY
release text PRIMARY KEY
component text PRIMARY KEY
vcs_type text
vcs_url text
vcs_browser text
python_version text
ruby_versions text
checksums_sha1 text
checksums_sha256 text
original_maintainer text
dm_upload_allowed boolean
testsuite text
autobuild text
extra_source_only boolean
build_depends_arch text
build_conflicts_arch text

Index - Schema public

Table: public.unofficial_uploaders

public.unofficial_uploaders Structure
F-Key Name Type Description
source text
version debversion
distribution text
release text
component text
uploader text
name text
email text

Index - Schema public

Table: public.upload_history

public.upload_history Structure
F-Key Name Type Description
source text PRIMARY KEY
version debversion PRIMARY KEY
date timestamp with time zone
changed_by text
changed_by_name text
changed_by_email text
maintainer text
maintainer_name text
maintainer_email text
nmu boolean
signed_by text
signed_by_name text
signed_by_email text
key_id text
distribution text
file text
fingerprint text

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: public.upload_history_architecture

public.upload_history_architecture Structure
F-Key Name Type Description
public.upload_history.source#1 source text PRIMARY KEY
public.upload_history.version#1 version debversion PRIMARY KEY
architecture text PRIMARY KEY
file text

Index - Schema public

Table: public.upload_history_closes

public.upload_history_closes Structure
F-Key Name Type Description
public.upload_history.source#1 source text PRIMARY KEY
public.upload_history.version#1 version debversion PRIMARY KEY
bug integer PRIMARY KEY
file text

Index - Schema public

View: public.upload_history_nmus

public.upload_history_nmus Structure
F-Key Name Type Description
source text
nmus bigint 
SELECT uh1.source
,
    count
(*) AS nmus
   
FROM upload_history uh1
,
    
(
SELECT upload_history.source
     ,
            max
     (upload_history.date) AS date
           
  FROM upload_history
          
 WHERE (upload_history.nmu = false)
          
GROUP BY upload_history.source
) uh2
  
WHERE (
     (uh1.nmu = true)
   AND (uh1.source = uh2.source)
   AND (uh1.date > uh2.date)
)
  
GROUP BY uh1.source;

Index - Schema public

Table: public.uploaders

public.uploaders Structure
F-Key Name Type Description
source text
version debversion
distribution text
release text
component text
uploader text
name text
email text

Index - Schema public

Table: public.upstream

public.upstream Structure
F-Key Name Type Description
source text PRIMARY KEY
version debversion PRIMARY KEY
distribution text PRIMARY KEY
release text PRIMARY KEY
component text PRIMARY KEY
watch_file text
signing_key_pgp bytea
signing_key_asc text
debian_uversion text
debian_mangled_uversion text
upstream_version text
upstream_url text
errors text
warnings text
status text
last_check timestamp without time zone

Index - Schema public

Table: public.upstream_metadata

public.upstream_metadata Structure
F-Key Name Type Description
source text PRIMARY KEY
key text PRIMARY KEY
value text NOT NULL

Index - Schema public

Table: public.vcs

public.vcs Structure
F-Key Name Type Description
source text PRIMARY KEY
team text
version debversion
distribution text

Index - Schema public

Table: public.vcswatch

public.vcswatch Structure
F-Key Name Type Description
source text PRIMARY KEY
version debversion
vcs text
url text
branch text
browser text
last_scan timestamp with time zone
next_scan timestamp with time zone DEFAULT now()
status text DEFAULT 'TODO'::text
debian_dir boolean DEFAULT true
changelog_version debversion
changelog_distribution text
changelog text
error text
commit_id text
ci_url text
ci_status text
issues integer
merge_requests integer
blocked integer

Index - Schema public

Table: public.wannabuild

public.wannabuild Structure
F-Key Name Type Description
source text PRIMARY KEY
distribution text PRIMARY KEY
architecture text PRIMARY KEY
version debversion
state text
installed_version debversion
previous_state text
state_change timestamp without time zone
binary_nmu_version numeric
notes text
vancouvered boolean

Index - Schema public

View: public.wnpp

public.wnpp Structure
F-Key Name Type Description
id integer
type text
source text
title text 
SELECT bugs.id
,
    "substring"
(bugs.title
     ,'^([A-Z]{1,3}): .*'::text
)      AS type
,     
    "substring"
(     bugs.title
,'^[A-Z]{1,3}: ([^ ]+)(?: -- .*)'::text
)            AS source
,           
    bugs.title
   
FROM         bugs
  
WHERE        (
(     bugs.package = 'wnpp'::text)
AND    (bugs.status <> 'done'::text)
)           ;

Index - Schema public

Function: public.array_accum(anyelement)

Returns: anyarray

Language: INTERNAL

aggregate_dummy

Function: public.array_sort(anyarray)

Returns: anyarray

Language: SQL

SELECT ARRAY(
    SELECT $1[s.i] AS "foo"
    FROM
        generate_series(array_lower($1,1), array_upper($1,1)) AS s(i)
    ORDER BY foo
);

Function: public.bibtex()

Returns: SET OF text

Language: SQL

  SELECT DISTINCT
         CASE WHEN bibjournal.value IS NULL AND bibin.value IS NOT NULL AND bibpublisher.value IS NOT NULL THEN '@Book{' || bibkey.value
              ELSE CASE WHEN bibauthor.value IS NULL OR bibjournal.value IS NULL THEN '@Misc{'|| bibkey.value ||
                   CASE WHEN bibauthor.value IS NULL THEN E',\n  Key     = "' || bibkey.value || '"' ELSE '' END -- without author we need a sorting key
              ELSE '@Article{' || bibkey.value END END  ||
            CASE WHEN bibauthor.value  IS NOT NULL THEN E',\n  Author  = {' || bibauthor.value  || '}' ELSE '' END ||
            CASE WHEN bibtitle.value   IS NOT NULL THEN E',\n  Title   = "{' || 
                  replace(replace(replace(replace(bibtitle.value,
                        '_', E'\\_'),            --
                        '%', E'\\%'),            --
                        '&', E'\\&'),            --
                        E'\xe2\x80\x89', E'\\,') -- TeX syntax for '_' and UTF-8 "thin space"
                                               -- see http://www.utf8-chartable.de/unicode-utf8-table.pl?start=8192&number=128&utf8=string-literal
                   || '}"'
                 ELSE '' END ||
            CASE WHEN bibbooktitle.value IS NOT NULL THEN E',\n  Booktitle = "{' || bibbooktitle.value || '}"' ELSE '' END ||
            CASE WHEN bibyear.value    IS NOT NULL THEN E',\n  Year    = {' || bibyear.value    || '}' ELSE '' END ||
            CASE WHEN bibmonth.value   IS NOT NULL THEN E',\n  Month   = {' || bibmonth.value   || '}' ELSE '' END ||
            CASE WHEN bibjournal.value IS NOT NULL THEN E',\n  Journal = {' || replace(bibjournal.value, '&', E'\\&') || '}' ELSE '' END ||
            CASE WHEN bibaddress.value IS NOT NULL THEN E',\n  Address = {' || bibaddress.value || '}' ELSE '' END ||
            CASE WHEN bibpublisher.value IS NOT NULL THEN E',\n  Publisher = {' || bibpublisher.value || '}' ELSE '' END ||
            CASE WHEN bibvolume.value  IS NOT NULL THEN E',\n  Volume  = {' || bibvolume.value  || '}' ELSE '' END ||
            CASE WHEN bibnumber.value  IS NOT NULL THEN E',\n  Number  = {' || bibnumber.value  || '}' ELSE '' END ||
            CASE WHEN bibpages.value   IS NOT NULL THEN E',\n  Pages   = {' || regexp_replace(bibpages.value, E'(\\d)-([\\d])', E'\\1--\\2')   || '}' ELSE '' END ||
            CASE WHEN biburl.value     IS NOT NULL THEN E',\n  URL     = {' ||
                  replace(replace(replace(replace(replace(biburl.value,
                        '_', E'\\_'),           --
                        '%', E'\\%'),           --
                        '&', E'\\&'),           --
                        '#', E'\\#'),           --
                        '~', E'\\~{}')          --
                   || '}'
                 ELSE '' END ||
            CASE WHEN bibdoi.value     IS NOT NULL THEN E',\n  DOI     = {' ||
                  replace(replace(bibdoi.value,
                        '_', E'\\_'),           --
                        '&', E'\\&')            --
                   || '}'
                 ELSE '' END ||
            CASE WHEN bibpmid.value    IS NOT NULL THEN E',\n  PMID    = {' || bibpmid.value    || '}' ELSE '' END ||
            CASE WHEN bibeprint.value  IS NOT NULL THEN E',\n  EPrint  = {' ||
                  replace(replace(replace(replace(bibeprint.value,
                        '_', E'\\_'),           --
                        '%', E'\\%'),           --
                        '&', E'\\&'),           --
                        '~', E'\\~{}')          --
                   || '}'
                 ELSE '' END ||
            CASE WHEN bibin.value      IS NOT NULL THEN E',\n  In      = {' || bibin.value      || '}' ELSE '' END ||
            CASE WHEN bibissn.value    IS NOT NULL THEN E',\n  ISSN    = {' || bibissn.value    || '}' ELSE '' END ||
            E',\n}\n'
            AS bibentry
--         p.source         AS source,
--         p.rank           AS rank,
    FROM (SELECT DISTINCT source, package, rank FROM bibref) p
    LEFT OUTER JOIN bibref bibkey     ON p.source = bibkey.source     AND bibkey.rank     = p.rank AND bibkey.package     = p.package AND bibkey.key     = 'bibtex'
    LEFT OUTER JOIN bibref bibyear    ON p.source = bibyear.source    AND bibyear.rank    = p.rank AND bibyear.package    = p.package AND bibyear.key    = 'year'  
    LEFT OUTER JOIN bibref bibmonth   ON p.source = bibmonth.source   AND bibmonth.rank   = p.rank AND bibmonth.package   = p.package AND bibmonth.key   = 'month'  
    LEFT OUTER JOIN bibref bibtitle   ON p.source = bibtitle.source   AND bibtitle.rank   = p.rank AND bibtitle.package   = p.package AND bibtitle.key   = 'title'  
    LEFT OUTER JOIN bibref bibbooktitle ON p.source = bibbooktitle.source AND bibbooktitle.rank = p.rank AND bibbooktitle.package = p.package AND bibbooktitle.key = 'booktitle'  
    LEFT OUTER JOIN bibref bibauthor  ON p.source = bibauthor.source  AND bibauthor.rank  = p.rank AND bibauthor.package  = p.package AND bibauthor.key  = 'author'
    LEFT OUTER JOIN bibref bibjournal ON p.source = bibjournal.source AND bibjournal.rank = p.rank AND bibjournal.package = p.package AND bibjournal.key = 'journal'
    LEFT OUTER JOIN bibref bibaddress ON p.source = bibaddress.source AND bibaddress.rank = p.rank AND bibaddress.package = p.package AND bibaddress.key = 'address'
    LEFT OUTER JOIN bibref bibpublisher ON p.source = bibpublisher.source AND bibpublisher.rank = p.rank AND bibpublisher.package = p.package AND bibpublisher.key = 'publisher'
    LEFT OUTER JOIN bibref bibvolume  ON p.source = bibvolume.source  AND bibvolume.rank  = p.rank AND bibvolume.package  = p.package AND bibvolume.key  = 'volume'
    LEFT OUTER JOIN bibref bibdoi     ON p.source = bibdoi.source     AND bibdoi.rank     = p.rank AND bibdoi.package     = p.package AND bibdoi.key     = 'doi'
    LEFT OUTER JOIN bibref bibpmid    ON p.source = bibpmid.source    AND bibpmid.rank    = p.rank AND bibpmid.package    = p.package AND bibpmid.key    = 'pmid'
    LEFT OUTER JOIN bibref biburl     ON p.source = biburl.source     AND biburl.rank     = p.rank AND biburl.package     = p.package AND biburl.key     = 'url'
    LEFT OUTER JOIN bibref bibnumber  ON p.source = bibnumber.source  AND bibnumber.rank  = p.rank AND bibnumber.package  = p.package AND bibnumber.key  = 'number'
    LEFT OUTER JOIN bibref bibpages   ON p.source = bibpages.source   AND bibpages.rank   = p.rank AND bibpages.package   = p.package AND bibpages.key   = 'pages'
    LEFT OUTER JOIN bibref bibeprint  ON p.source = bibeprint.source  AND bibeprint.rank  = p.rank AND bibeprint.package  = p.package AND bibeprint.key  = 'eprint'
    LEFT OUTER JOIN bibref bibin      ON p.source = bibin.source      AND bibin.rank      = p.rank AND bibin.package      = p.package AND bibin.key      = 'in'
    LEFT OUTER JOIN bibref bibissn    ON p.source = bibissn.source    AND bibissn.rank    = p.rank AND bibissn.package    = p.package AND bibissn.key    = 'issn'
    ORDER BY bibentry -- p.source
;

Function: public.bibtex_example_data()

Returns: SET OF record

Language: SQL

SELECT package, source, bibkey, description FROM (
  SELECT -- DISTINCT
         p.package        AS package,
         p.source         AS source,
         b.package        AS bpackage,
         b.value          AS bibkey,
         replace(replace(replace(replace(p.description, E'\xc2\xa0', E'\\ '), E'#', E'\\#'), E'&', E'\\&'), E'_', E'\\_') AS description -- replace non-breaking spaces to TeX syntax and escape '#'
    FROM ( -- Make sure we have only one (package,source,description) record fitting the latest release with highest version
       SELECT package, source, description FROM
         (SELECT *, rank() OVER (PARTITION BY package ORDER BY rsort DESC, version DESC) FROM
           (SELECT DISTINCT package, source, description, sort as rsort, version FROM packages p
              JOIN releases r ON p.release = r. release
           ) tmp
         ) tmp WHERE rank = 1
    ) p
    JOIN (SELECT DISTINCT source, package, value FROM bibref WHERE key = 'bibtex') b ON b.source = p.source
 ) tmp
 WHERE package = bpackage OR bpackage = ''
 ORDER BY package, bibkey
;

Function: public.blends_metapackage_translations(text[])

Returns: SET OF record

Language: SQL

  SELECT
         p.package,
         p.description,     en.long_description_en,
         cs.description_cs, cs.long_description_cs,
         da.description_da, da.long_description_da,
         de.description_de, de.long_description_de,
         es.description_es, es.long_description_es,
         fi.description_fi, fi.long_description_fi,
         fr.description_fr, fr.long_description_fr,
         hu.description_hu, hu.long_description_hu,
         it.description_it, it.long_description_it,
         ja.description_ja, ja.long_description_ja,
         ko.description_ko, ko.long_description_ko,
         nl.description_nl, nl.long_description_nl,
         pl.description_pl, pl.long_description_pl,
         pt_BR.description_pt_BR, pt_BR.long_description_pt_BR,
         ru.description_ru, ru.long_description_ru,
         sk.description_sk, sk.long_description_sk,
         sr.description_sr, sr.long_description_sr,
         sv.description_sv, sv.long_description_sv,
         uk.description_uk, uk.long_description_uk,
         vi.description_vi, vi.long_description_vi,
         zh_CN.description_zh_CN, zh_CN.long_description_zh_CN,
         zh_TW.description_zh_TW, zh_TW.long_description_zh_TW
    FROM packages p
    LEFT OUTER JOIN (SELECT * FROM ddtp_unique('en', $1) AS (package text, description_en text, long_description_en text)) en ON en.package = p.package
    LEFT OUTER JOIN (SELECT * FROM ddtp_unique('cs', $1) AS (package text, description_cs text, long_description_cs text)) cs ON cs.package = p.package
    LEFT OUTER JOIN (SELECT * FROM ddtp_unique('da', $1) AS (package text, description_da text, long_description_da text)) da ON da.package = p.package
    LEFT OUTER JOIN (SELECT * FROM ddtp_unique('de', $1) AS (package text, description_de text, long_description_de text)) de ON de.package = p.package
    LEFT OUTER JOIN (SELECT * FROM ddtp_unique('es', $1) AS (package text, description_es text, long_description_es text)) es ON es.package = p.package
    LEFT OUTER JOIN (SELECT * FROM ddtp_unique('fi', $1) AS (package text, description_fi text, long_description_fi text)) fi ON fi.package = p.package
    LEFT OUTER JOIN (SELECT * FROM ddtp_unique('fr', $1) AS (package text, description_fr text, long_description_fr text)) fr ON fr.package = p.package
    LEFT OUTER JOIN (SELECT * FROM ddtp_unique('hu', $1) AS (package text, description_hu text, long_description_hu text)) hu ON hu.package = p.package
    LEFT OUTER JOIN (SELECT * FROM ddtp_unique('it', $1) AS (package text, description_it text, long_description_it text)) it ON it.package = p.package
    LEFT OUTER JOIN (SELECT * FROM ddtp_unique('ja', $1) AS (package text, description_ja text, long_description_ja text)) ja ON ja.package = p.package
    LEFT OUTER JOIN (SELECT * FROM ddtp_unique('ko', $1) AS (package text, description_ko text, long_description_ko text)) ko ON ko.package = p.package
    LEFT OUTER JOIN (SELECT * FROM ddtp_unique('nl', $1) AS (package text, description_nl text, long_description_nl text)) nl ON nl.package = p.package
    LEFT OUTER JOIN (SELECT * FROM ddtp_unique('pl', $1) AS (package text, description_pl text, long_description_pl text)) pl ON pl.package = p.package
    LEFT OUTER JOIN (SELECT * FROM ddtp_unique('pt_BR', $1) AS (package text, description_pt_BR text, long_description_pt_BR text)) pt_BR ON pt_BR.package = p.package
    LEFT OUTER JOIN (SELECT * FROM ddtp_unique('ru', $1) AS (package text, description_ru text, long_description_ru text)) ru ON ru.package = p.package
    LEFT OUTER JOIN (SELECT * FROM ddtp_unique('sk', $1) AS (package text, description_sk text, long_description_sk text)) sk ON sk.package = p.package
    LEFT OUTER JOIN (SELECT * FROM ddtp_unique('sr', $1) AS (package text, description_sr text, long_description_sr text)) sr ON sr.package = p.package
    LEFT OUTER JOIN (SELECT * FROM ddtp_unique('sv', $1) AS (package text, description_sv text, long_description_sv text)) sv ON sv.package = p.package
    LEFT OUTER JOIN (SELECT * FROM ddtp_unique('uk', $1) AS (package text, description_uk text, long_description_uk text)) uk ON uk.package = p.package
    LEFT OUTER JOIN (SELECT * FROM ddtp_unique('vi', $1) AS (package text, description_vi text, long_description_vi text)) vi ON vi.package = p.package
    LEFT OUTER JOIN (SELECT * FROM ddtp_unique('zh_CN', $1) AS (package text, description_zh_CN text, long_description_zh_CN text)) zh_CN ON zh_CN.package = p.package
    LEFT OUTER JOIN (SELECT * FROM ddtp_unique('zh_TW', $1) AS (package text, description_zh_TW text, long_description_zh_TW text)) zh_TW ON zh_TW.package = p.package
    WHERE p.package = ANY ($1)

Function: public.blends_query_packages(text[], text[], text)

Returns: SET OF record

Language: SQL

  SELECT DISTINCT
         p.package, p.distribution, p.release, p.component, p.version,
         p.maintainer,
         p.source, p.section, p.task, p.homepage,
         src.maintainer_name, src.maintainer_email,
         src.vcs_type, src.vcs_url, src.vcs_browser,
	 src.changed_by,
         enh.enhanced,
         rva.releases, versions, rva.architectures,
	 upstream_version AS unstable_upstream, debian_mangled_uversion AS unstable_parsed_version, status AS unstable_status,
         pop.vote, pop.recent, pop.insts,
         tags.debtags,
         screenshot_versions, large_image_urls, small_image_urls,
         bibyear.value    AS "year",
         bibtitle.value   AS "title",
         bibauthor.value  AS "authors",
         bibdoi.value     AS "doi",
         bibpmid.value    AS "pubmed",
         biburl.value     AS "url",
         bibjournal.value AS "journal",
         bibvolume.value  AS "volume",
         bibnumber.value  AS "number",
         bibpages.value   AS "pages",
         bibeprint.value  AS "eprint",
         en.description AS description_en, en.long_description AS long_description_en,
         cs.description AS description_cs, cs.long_description AS long_description_cs,
         da.description AS description_da, da.long_description AS long_description_da,
         de.description AS description_de, de.long_description AS long_description_de,
         es.description AS description_es, es.long_description AS long_description_es,
         fi.description AS description_fi, fi.long_description AS long_description_fi,
         fr.description AS description_fr, fr.long_description AS long_description_fr,
         hu.description AS description_hu, hu.long_description AS long_description_hu,
         it.description AS description_it, it.long_description AS long_description_it,
         ja.description AS description_ja, ja.long_description AS long_description_ja,
         ko.description AS description_ko, ko.long_description AS long_description_ko,
         nl.description AS description_nl, nl.long_description AS long_description_nl,
         pl.description AS description_pl, pl.long_description AS long_description_pl,
         pt_BR.description AS description_pt_BR, pt_BR.long_description AS long_description_pt_BR,
         ru.description AS description_ru, ru.long_description AS long_description_ru,
         sk.description AS description_sk, sk.long_description AS long_description_sk,
         sr.description AS description_sr, sr.long_description AS long_description_sr,
         sv.description AS description_sv, sv.long_description AS long_description_sv,
         uk.description AS description_uk, uk.long_description AS long_description_uk,
         vi.description AS description_vi, vi.long_description AS long_description_vi,
         zh_CN.description AS description_zh_CN, zh_CN.long_description AS long_description_zh_CN,
         zh_TW.description AS description_zh_TW, zh_TW.long_description AS long_description_zh_TW,
         re.remark,
         ubugs.upstream_bugs, urep.upstream_repository, edam.topics,
         biotools.entry AS biotools, omictools.entry AS omictools, bioconda.entry AS bioconda, scicrunch.entry AS scicrunch, rrid.entry AS rrid,
         bugs.bugs
    FROM (
      SELECT DISTINCT 
             package, distribution, release, component, strip_binary_upload(version) AS version,
             maintainer, source, section, task, homepage, description, description_md5
        FROM packages
       WHERE package = ANY ($1)
    ) p
    --                                                                                                                                                                   ---+  Ensure we get no old stuff from non-free
    --                                                                                                                                                                      v  packages with different architectures
    LEFT OUTER JOIN descriptions en ON en.language = 'en' AND en.package = p.package AND en.release = p.release  AND en.description_md5 = p.description_md5
    LEFT OUTER JOIN descriptions cs ON cs.language = 'cs' AND cs.package = p.package AND cs.release = p.release  AND cs.description_md5 = p.description_md5
    LEFT OUTER JOIN descriptions da ON da.language = 'da' AND da.package = p.package AND da.release = p.release  AND da.description_md5 = p.description_md5
    LEFT OUTER JOIN descriptions de ON de.language = 'de' AND de.package = p.package AND de.release = p.release  AND de.description_md5 = p.description_md5
    LEFT OUTER JOIN descriptions es ON es.language = 'es' AND es.package = p.package AND es.release = p.release  AND es.description_md5 = p.description_md5
    LEFT OUTER JOIN descriptions fi ON fi.language = 'fi' AND fi.package = p.package AND fi.release = p.release  AND fi.description_md5 = p.description_md5
    LEFT OUTER JOIN descriptions fr ON fr.language = 'fr' AND fr.package = p.package AND fr.release = p.release  AND fr.description_md5 = p.description_md5
    LEFT OUTER JOIN descriptions hu ON hu.language = 'hu' AND hu.package = p.package AND hu.release = p.release  AND hu.description_md5 = p.description_md5
    LEFT OUTER JOIN descriptions it ON it.language = 'it' AND it.package = p.package AND it.release = p.release  AND it.description_md5 = p.description_md5
    LEFT OUTER JOIN descriptions ja ON ja.language = 'ja' AND ja.package = p.package AND ja.release = p.release  AND ja.description_md5 = p.description_md5
    LEFT OUTER JOIN descriptions ko ON ko.language = 'ko' AND ko.package = p.package AND ko.release = p.release  AND ko.description_md5 = p.description_md5
    LEFT OUTER JOIN descriptions nl ON nl.language = 'nl' AND nl.package = p.package AND nl.release = p.release  AND nl.description_md5 = p.description_md5
    LEFT OUTER JOIN descriptions pl ON pl.language = 'pl' AND pl.package = p.package AND pl.release = p.release  AND pl.description_md5 = p.description_md5
    LEFT OUTER JOIN descriptions pt_BR ON pt_BR.language = 'pt_BR' AND pt_BR.package = p.package AND pt_BR.release = p.release AND pt_BR.description_md5 = p.description_md5
    LEFT OUTER JOIN descriptions ru ON ru.language = 'ru' AND ru.package = p.package AND ru.release = p.release  AND ru.description_md5 = p.description_md5
    LEFT OUTER JOIN descriptions sk ON sk.language = 'sk' AND sk.package = p.package AND sk.release = p.release  AND sk.description_md5 = p.description_md5
    LEFT OUTER JOIN descriptions sr ON sr.language = 'sr' AND sr.package = p.package AND sr.release = p.release  AND sr.description_md5 = p.description_md5
    LEFT OUTER JOIN descriptions sv ON sv.language = 'sv' AND sv.package = p.package AND sv.release = p.release  AND sv.description_md5 = p.description_md5
    LEFT OUTER JOIN descriptions uk ON uk.language = 'uk' AND uk.package = p.package AND uk.release = p.release  AND uk.description_md5 = p.description_md5
    LEFT OUTER JOIN descriptions vi ON vi.language = 'vi' AND vi.package = p.package AND vi.release = p.release  AND vi.description_md5 = p.description_md5
    LEFT OUTER JOIN descriptions zh_CN ON zh_CN.language = 'zh_CN' AND zh_CN.package = p.package AND zh_CN.release = p.release AND zh_CN.description_md5 = p.description_md5
    LEFT OUTER JOIN descriptions zh_TW ON zh_TW.language = 'zh_TW' AND zh_TW.package = p.package AND zh_TW.release = p.release AND zh_TW.description_md5 = p.description_md5
    -- extract one single package with highest version and release
    JOIN (
      -- select packages which have versions outside experimental
      SELECT px.package, strip_binary_upload(px.version) AS version, (SELECT release FROM releases WHERE sort = MAX(rx.sort)) AS release
        FROM (
           -- select highest version which is not in experimental - except if a package resides in experimental only
           SELECT pex.package, CASE WHEN pnoex.version IS NOT NULL THEN pnoex.version ELSE pex.version END AS version FROM
              (SELECT package, MAX(version) AS version FROM packages
                  WHERE package = ANY ($1)
                  GROUP BY package
              ) pex
              LEFT OUTER JOIN
              (SELECT package, MAX(version) AS version FROM packages
                  WHERE package = ANY ($1)
                    AND release != 'experimental'
                  GROUP BY package
              ) pnoex ON pex.package = pnoex.package
        ) px
        JOIN (
           -- select the release in which this version is available
           SELECT DISTINCT package, version, release FROM packages
            WHERE package = ANY ($1)
        ) py ON px.package = py.package AND px.version = py.version
        JOIN releases rx ON py.release = rx.release
        GROUP BY px.package, px.version
       ) pvar ON pvar.package = p.package AND pvar.version = p.version AND pvar.release = p.release
    -- obtain source_version of given package which is needed in cases where this is different form binary package version
    JOIN (
       SELECT DISTINCT package, source, strip_binary_upload(version) AS version,
                       strip_binary_upload(source_version) AS source_version, release,
                       maintainer_email
         FROM packages_summary WHERE package = ANY ($1)
    ) ps ON ps.package = p.package AND ps.release = p.release
    -- extract source and join with upload_history to find out latest uploader if different from Maintainer
    JOIN (
	SELECT DISTINCT s.source, strip_binary_upload(s.version) AS version,
               s.maintainer, s.release, s.maintainer_name, s.maintainer_email, s.vcs_type, s.vcs_url, s.vcs_browser,
               CASE WHEN uh.changed_by != s.maintainer THEN uh.changed_by ELSE NULL END AS changed_by
          FROM sources s
          LEFT OUTER JOIN upload_history uh ON s.source = uh.source AND s.version = uh.version
    ) src ON src.source = p.source AND src.source = ps.source
           AND src.release = p.release
           AND ( ( ps.version = p.version AND ps.version != ps.source_version ) OR
                 ( ps.version = p.version AND src.version = p.version) )
    -- join with sets of avialable versions in different releases
    JOIN (
      SELECT package, array_agg(release) AS releases,
             array_agg(CASE WHEN component = 'main' THEN version ELSE version || ' (' || component || ')' END) AS versions,
             array_agg(archs) AS architectures
          FROM (
     	    SELECT package, ptmp.release as release, strip_binary_upload(version) AS version, archs, component FROM
              ( SELECT package, release, version, array_to_string(array_sort(array_accum(architecture)),',') AS archs, component
                  FROM (
                    SELECT package,
                           release || CASE WHEN char_length(substring(distribution from '-.*')) > 0
                                        THEN substring(distribution from '-.*')
                                        ELSE '' END AS release,
                            -- make *-volatile a "pseudo-release"
                            strip_binary_upload(regexp_replace(version, '^[0-9]:', '')) AS version,
                            architecture,
                            component
                      FROM packages
	             WHERE package = ANY ($1)
                   ) AS prvac
		   GROUP BY package, version, release, component
              ) ptmp
	      JOIN releases ON releases.release = ptmp.release
              ORDER BY version, releases.sort
	    ) tmp GROUP BY package
         ) rva
         ON p.package = rva.package
    LEFT OUTER JOIN (
      SELECT DISTINCT
        source, upstream_version, debian_mangled_uversion, status
        FROM upstream
        WHERE release = 'sid' AND (status = 'Newer version available' OR status = 'newer package available') -- see https://lists.debian.org/debian-qa/2016/03/msg00018.html
    ) d ON p.source = d.source 
    LEFT OUTER JOIN popcon pop ON p.package = pop.package
    LEFT OUTER JOIN (
       SELECT package, array_agg(tag) AS debtags
         FROM debtags 
        WHERE tag NOT LIKE 'implemented-in::%'
	  AND tag NOT LIKE 'protocol::%'
          AND tag NOT LIKE '%::TODO'
          AND tag NOT LIKE '%not-yet-tagged%'
          GROUP BY package
    ) tags ON tags.package = p.package
    LEFT OUTER JOIN (
       SELECT package, 
              array_agg(version)  AS screenshot_versions,
              array_agg(large_image_url) AS large_image_urls,
              array_agg(small_image_url) AS small_image_urls 
         FROM screenshots 
         GROUP BY package
    ) sshots ON sshots.package = p.package
    -- check whether a package is enhanced by some other package
    LEFT OUTER JOIN (
      SELECT DISTINCT regexp_replace(package_version, E'\\s*\\(.*\\)', '') AS package, array_agg(enhanced_by) AS enhanced FROM (
        SELECT DISTINCT package AS enhanced_by, regexp_split_to_table(enhances, E',\\s*') AS package_version FROM packages
         WHERE enhances LIKE ANY( $2 )
      ) AS tmpenh GROUP BY package
    ) enh ON enh.package = p.package
    -- FIXME: To get reasonable querying of publications for specific packages and also multiple citations the table structure
    --        of the bibref table most probably needs to be changed to one entry per citation
    --        for the moment the specification of package is ignored because otherwise those citations would spoil the
    --        whole query
    --        example: if `bib*.package = ''` would be left out acedb-other would get more than 500 results !!!
    LEFT OUTER JOIN bibref bibyear    ON p.source = bibyear.source    AND bibyear.rank = 0    AND bibyear.key    = 'year'    AND bibyear.package = ''
    LEFT OUTER JOIN bibref bibtitle   ON p.source = bibtitle.source   AND bibtitle.rank = 0   AND bibtitle.key   = 'title'   AND bibtitle.package = ''
    LEFT OUTER JOIN bibref bibauthor  ON p.source = bibauthor.source  AND bibauthor.rank = 0  AND bibauthor.key  = 'author'  AND bibauthor.package = ''
    LEFT OUTER JOIN bibref bibdoi     ON p.source = bibdoi.source     AND bibdoi.rank = 0     AND bibdoi.key     = 'doi'     AND bibdoi.package = ''
    LEFT OUTER JOIN bibref bibpmid    ON p.source = bibpmid.source    AND bibpmid.rank = 0    AND bibpmid.key    = 'pmid'    AND bibpmid.package = ''
    LEFT OUTER JOIN bibref biburl     ON p.source = biburl.source     AND biburl.rank = 0     AND biburl.key     = 'url'     AND biburl.package = ''
    LEFT OUTER JOIN bibref bibjournal ON p.source = bibjournal.source AND bibjournal.rank = 0 AND bibjournal.key = 'journal' AND bibjournal.package = ''
    LEFT OUTER JOIN bibref bibvolume  ON p.source = bibvolume.source  AND bibvolume.rank = 0  AND bibvolume.key  = 'volume'  AND bibvolume.package = ''
    LEFT OUTER JOIN bibref bibnumber  ON p.source = bibnumber.source  AND bibnumber.rank = 0  AND bibnumber.key  = 'number'  AND bibnumber.package = ''
    LEFT OUTER JOIN bibref bibpages   ON p.source = bibpages.source   AND bibpages.rank = 0   AND bibpages.key   = 'pages'   AND bibpages.package = ''
    LEFT OUTER JOIN bibref bibeprint  ON p.source = bibeprint.source  AND bibeprint.rank = 0  AND bibeprint.key  = 'eprint'  AND bibeprint.package = ''
    LEFT OUTER JOIN blends_remarks re ON p.package = re.package AND re.task = $3
    LEFT OUTER JOIN (SELECT source, value AS upstream_bugs       FROM upstream_metadata WHERE key = 'Bug-Database') ubugs ON p.source = ubugs.source
    LEFT OUTER JOIN (SELECT source, value AS upstream_repository FROM upstream_metadata WHERE key = 'Repository')   urep  ON p.source = urep.source
    LEFT OUTER JOIN (SELECT source, package, topics              FROM edam) edam ON p.source = edam.source AND p.package = edam.package
    LEFT OUTER JOIN (SELECT source, entry FROM registry WHERE name  = 'bio.tools') biotools  ON p.source = biotools.source
    LEFT OUTER JOIN (SELECT source, entry FROM registry WHERE name  = 'OMICtools') omictools ON p.source = omictools.source
    LEFT OUTER JOIN (SELECT source, entry FROM registry WHERE name  = 'conda:bioconda') bioconda ON p.source = bioconda.source
    LEFT OUTER JOIN (SELECT source, entry FROM registry WHERE name  = 'SciCrunch') scicrunch ON p.source = scicrunch.source
    LEFT OUTER JOIN (SELECT source, entry FROM registry WHERE name  = 'RRID')      rrid      ON p.source = rrid.source
    LEFT OUTER JOIN (SELECT source, array_to_string(array_sort(array_accum(id)),',') AS bugs FROM bugs WHERE status != 'done' GROUP BY source) bugs ON p.source = bugs.source
    ORDER BY p.package

Function: public.bugs_rt_affects_dist( text)

Returns: SET OF record

Language: PLPGSQL

DECLARE
releasecodename text := release_name($1);
BEGIN
RETURN QUERY

EXECUTE '
SELECT b.id id, b.package package, b.source source FROM bugs b
WHERE b.affects_' || $1 || '
AND 
(
		b.id NOT IN (SELECT t.id FROM bugs_tags t WHERE t.tag IN
			(
				SELECT tag FROM bts_tags WHERE tag_type=''release''
			)
		)
	OR	b.id IN (SELECT t.id FROM bugs_tags t WHERE t.tag = ''' || releasecodename || ''')
)
AND
	b.id NOT IN (select t.id FROM bugs_tags t WHERE t.tag = ''' || releasecodename || '-ignore'')
AND
(
		b.id in (select bugs_packages.id from sources, bugs_packages where sources.source = bugs_packages.source and release = ''' || releasecodename || ''' )
	OR	b.id in (select bugs_packages.id from packages_summary, bugs_packages where packages_summary.package = bugs_packages.package and release = ''' || releasecodename || ''')
);'
;
END

Function: public.ddtp_unique(text, text[])

Returns: SET OF record

Language: SQL

  SELECT DISTINCT d.package, d.description, d.long_description FROM descriptions d
    JOIN (
      SELECT dr.package, (SELECT release FROM releases WHERE sort = MAX(r.sort)) AS release FROM descriptions dr
        JOIN releases r ON dr.release = r.release
        WHERE language = $1 AND dr.package = ANY ($2)
        GROUP BY dr.package
    -- sometimes there are different translations of the same package version in different releases
    -- because translators moved on working inbetween releases but we need to select only one of these
    -- (the last one)
    ) duvr ON duvr.package = d.package AND duvr.release = d.release
    WHERE language = $1 AND d.package = ANY ($2)

Function: public.debversion(bpchar)

Returns: debversion

Language: INTERNAL

rtrim1

Function: public.debversion_cmp(version1 public.debversion, version2 public.debversion)

Returns: integer

Language: C

Compare Debian versions

debversion_cmp

Function: public.debversion_eq(version1 public.debversion, version2 public.debversion)

Returns: boolean

Language: C

debversion equal

debversion_eq

Function: public.debversion_ge(version1 public.debversion, version2 public.debversion)

Returns: boolean

Language: C

debversion greater-than-or-equal

debversion_ge

Function: public.debversion_gt(version1 public.debversion, version2 public.debversion)

Returns: boolean

Language: C

debversion greater-than

debversion_gt

Function: public.debversion_hash(public.debversion)

Returns: integer

Language: C

debversion_hash

Function: public.debversion_larger(version1 public.debversion, version2 public.debversion)

Returns: debversion

Language: C

debversion_larger

Function: public.debversion_le(version1 public.debversion, version2 public.debversion)

Returns: boolean

Language: C

debversion less-than-or-equal

debversion_le

Function: public.debversion_lt(version1 public.debversion, version2 public.debversion)

Returns: boolean

Language: C

debversion less-than

debversion_lt

Function: public.debversion_ne(version1 public.debversion, version2 public.debversion)

Returns: boolean

Language: C

debversion not equal

debversion_ne

Function: public.debversion_smaller(version1 public.debversion, version2 public.debversion)

Returns: debversion

Language: C

debversion_smaller

Function: public.debversionin(cstring)

Returns: debversion

Language: INTERNAL

textin

Function: public.debversionout(public.debversion)

Returns: cstring

Language: INTERNAL

textout

Function: public.debversionrecv(internal)

Returns: debversion

Language: INTERNAL

textrecv

Function: public.debversionsend(public.debversion)

Returns: bytea

Language: INTERNAL

textsend

Function: public.max(public.debversion)

Returns: debversion

Language: INTERNAL

aggregate_dummy

Function: public.min(public.debversion)

Returns: debversion

Language: INTERNAL

aggregate_dummy

Function: public.release_name(text)

Returns: text

Language: SQL

SELECT release FROM releases WHERE role=$1;

Function: public.strip_binary_upload(text)

Returns: debversion

Language: SQL

       SELECT CAST(regexp_replace(regexp_replace($1, E'\\+b[0-9]+$', ''), E'^[0-9]+:', '') AS debversion) ;

Function: public.versions_archs_component(text)

Returns: SET OF record

Language: SQL

       SELECT p.release, version, archs, component FROM
          ( SELECT release || CASE WHEN char_length(substring(distribution from '-.*')) > 0
                                        THEN substring(distribution from '-.*')
                                        ELSE '' END AS release,
                            -- make *-volatile a "pseudo-release"
                        regexp_replace(version, '^[0-9]:', '') AS version,
                        array_to_string(array_sort(array_accum(architecture)),',') AS archs,
                        component
                    FROM packages
	           WHERE package = $1
		   GROUP BY version, release, distribution, component
          ) p
	  JOIN releases ON releases.release = p.release
	  ORDER BY releases.sort, version;

Generated by PostgreSQL Autodoc

W3C HTML 4.01 Strict