setup/init_pool.sql

   1 DROP DATABASE projectb;
   2 CREATE DATABASE projectb WITH ENCODING = 'SQL_ASCII';
   3
   4 \c projectb
   5
   6 CREATE TABLE archive (
   7        id SERIAL PRIMARY KEY,
   8        name TEXT UNIQUE NOT NULL,
   9        origin_server TEXT,
  10        description TEXT
  11 );
  12
  13 CREATE TABLE component (
  14        id SERIAL PRIMARY KEY,
  15        name TEXT UNIQUE NOT NULL,
  16        description TEXT,
  17        meets_dfsg BOOLEAN
  18 );
  19
  20 CREATE TABLE architecture (
  21        id SERIAL PRIMARY KEY,
  22        arch_string TEXT UNIQUE NOT NULL,
  23        description TEXT
  24 );
  25
  26 CREATE TABLE maintainer (
  27        id SERIAL PRIMARY KEY,
  28        name TEXT UNIQUE NOT NULL
  29 );
  30
  31 CREATE TABLE src_uploaders (
  32        id SERIAL PRIMARY KEY,
  33        source INT4 NOT NULL REFERENCES source,
  34        maintainer INT4 NOT NULL REFERENCES maintainer
  35 );
  36
  37 CREATE TABLE uid (
  38        id SERIAL PRIMARY KEY,
  39        uid TEXT UNIQUE NOT NULL,
  40        name TEXT
  41 );
  42
  43 CREATE TABLE keyrings (
  44        id SERIAL PRIMARY KEY,
  45        name TEXT
  46 );
  47
  48
  49 CREATE TABLE fingerprint (
  50        id SERIAL PRIMARY KEY,
  51        fingerprint TEXT UNIQUE NOT NULL,
  52        uid INT4 REFERENCES uid,
  53        keyring INT4 REFERENCES keyrings
  54 );
  55
  56 CREATE TABLE location (
  57        id SERIAL PRIMARY KEY,
  58        path TEXT NOT NULL,
  59        component INT4 REFERENCES component,
  60        archive INT4 REFERENCES archive,
  61        type TEXT NOT NULL
  62 );
  63
  64 -- No references below here to allow sane population; added post-population
  65
  66 CREATE TABLE files (
  67        id SERIAL PRIMARY KEY,
  68        filename TEXT NOT NULL,
  69        size INT8 NOT NULL,
  70        md5sum TEXT NOT NULL,
  71        location INT4 NOT NULL, -- REFERENCES location
  72        last_used TIMESTAMP,
  73        sha1sum TEXT NOT NULL,
  74        sha256sum TEXT NOT NULL,
  75        unique (filename, location)
  76 );
  77
  78 CREATE TABLE source (
  79         id SERIAL PRIMARY KEY,
  80         source TEXT NOT NULL,
  81         version TEXT NOT NULL,
  82         maintainer INT4 NOT NULL, -- REFERENCES maintainer
  83         changedby INT4 NOT NULL, -- REFERENCES maintainer
  84         file INT4 UNIQUE NOT NULL, -- REFERENCES files
  85         install_date TIMESTAMP NOT NULL,
  86         sig_fpr INT4 NOT NULL, -- REFERENCES fingerprint
  87         dm-upload-allowed BOOLEAN NOT NULL,
  88         unique (source, version)
  89 );
  90
  91 CREATE TABLE src_uploaders (
  92        id SERIAL PRIMARY KEY,
  93        source INT4 NOT NULL REFERENCES source,
  94        maintainer INT4 NOT NULL REFERENCES maintainer
  95 );
  96
  97 CREATE TABLE dsc_files (
  98        id SERIAL PRIMARY KEY,
  99        source INT4 NOT NULL, -- REFERENCES source,
 100        file INT4 NOT NULL, -- RERENCES files
 101        unique (source, file)
 102 );
 103
 104 CREATE TABLE binaries (
 105        id SERIAL PRIMARY KEY,
 106        package TEXT NOT NULL,
 107        version TEXT NOT NULL,
 108        maintainer INT4 NOT NULL, -- REFERENCES maintainer
 109        source INT4, -- REFERENCES source,
 110        architecture INT4 NOT NULL, -- REFERENCES architecture
 111        file INT4 UNIQUE NOT NULL, -- REFERENCES files,
 112        type TEXT NOT NULL,
 113 -- joeyh@ doesn't want .udebs and .debs with the same name, which is why the unique () doesn't mention type
 114        sig_fpr INT4 NOT NULL, -- REFERENCES fingerprint
 115        unique (package, version, architecture)
 116 );
 117
 118 CREATE TABLE suite (
 119        id SERIAL PRIMARY KEY,
 120        suite_name TEXT NOT NULL,
 121        version TEXT,
 122        origin TEXT,
 123        label TEXT,
 124        policy_engine TEXT,
 125        description TEXT
 126 );
 127
 128 CREATE TABLE queue (
 129        id SERIAL PRIMARY KEY,
 130        queue_name TEXT NOT NULL
 131 );
 132
 133 CREATE TABLE suite_architectures (
 134        suite INT4 NOT NULL, -- REFERENCES suite
 135        architecture INT4 NOT NULL, -- REFERENCES architecture
 136        unique (suite, architecture)
 137 );
 138
 139 CREATE TABLE bin_associations (
 140        id SERIAL PRIMARY KEY,
 141        suite INT4 NOT NULL, -- REFERENCES suite
 142        bin INT4 NOT NULL, -- REFERENCES binaries
 143        unique (suite, bin)
 144 );
 145
 146 CREATE TABLE src_associations (
 147        id SERIAL PRIMARY KEY,
 148        suite INT4 NOT NULL, -- REFERENCES suite
 149        source INT4 NOT NULL, -- REFERENCES source
 150        unique (suite, source)
 151 );
 152
 153 CREATE TABLE section (
 154        id SERIAL PRIMARY KEY,
 155        section TEXT UNIQUE NOT NULL
 156 );
 157
 158 CREATE TABLE priority (
 159        id SERIAL PRIMARY KEY,
 160        priority TEXT UNIQUE NOT NULL,
 161        level INT4 UNIQUE NOT NULL
 162 );
 163
 164 CREATE TABLE override_type (
 165        id SERIAL PRIMARY KEY,
 166        type TEXT UNIQUE NOT NULL
 167 );
 168
 169 CREATE TABLE override (
 170        package TEXT NOT NULL,
 171        suite INT4 NOT NULL, -- references suite
 172        component INT4 NOT NULL, -- references component
 173        priority INT4, -- references priority
 174        section INT4 NOT NULL, -- references section
 175        type INT4 NOT NULL, -- references override_type
 176        maintainer TEXT,
 177        unique (suite, component, package, type)
 178 );
 179
 180 CREATE TABLE queue_build (
 181        suite INT4 NOT NULL, -- references suite
 182        queue INT4 NOT NULL, -- references queue
 183        filename TEXT NOT NULL,
 184        in_queue BOOLEAN NOT NULL,
 185        last_used TIMESTAMP
 186 );
 187
 188 -- Critical indexes
 189
 190 CREATE INDEX bin_associations_bin ON bin_associations (bin);
 191 CREATE INDEX src_associations_source ON src_associations (source);
 192 CREATE INDEX source_maintainer ON source (maintainer);
 193 CREATE INDEX binaries_maintainer ON binaries (maintainer);
 194 CREATE INDEX binaries_fingerprint on binaries (sig_fpr);
 195 CREATE INDEX source_fingerprint on source (sig_fpr);
 196 CREATE INDEX dsc_files_file ON dsc_files (file);
 197
 198 -- Own function
 199 CREATE FUNCTION space_concat(text, text) RETURNS text
 200     AS $_$select case
 201 WHEN $2 is null or $2 = '' THEN $1
 202 WHEN $1 is null or $1 = '' THEN $2
 203 ELSE $1 || ' ' || $2
 204 END$_$
 205     LANGUAGE sql;
 206
 207 CREATE AGGREGATE space_separated_list (
 208     BASETYPE = text,
 209     SFUNC = space_concat,
 210     STYPE = text,
 211     INITCOND = ''
 212 );