Document schema

As far as I know, this should be what is currently in our database.

diff --git a/schema/arches-tables.in b/schema/arches-tables.in
new file mode 100644 (file)
index 0000000..bc4c57c
--- /dev/null
+++ b/schema/arches-tables.in
@@ -0,0 +1,87 @@
+drop schema "ARCH" cascade;
+drop schema "ARCH_public" cascade;
+create schema "ARCH";
+create schema "ARCH_public";
+
+grant usage on schema "ARCH" to "ARCH", wb_all;
+grant usage on schema "ARCH_public" to "ARCH", public;
+grant usage on schema "ARCH" to wb_security;
+
+create table "ARCH".packages
+    package character varying NOT NULL,
+    distribution character varying NOT NULL,
+    version character varying,
+    state character varying,
+    section character varying,
+    priority character varying,
+    installed_version character varying,
+    previous_state character varying,
+    state_change timestamp without time zone,
+    notes character varying,
+    builder character varying,
+    failed text,
+    old_failed text,
+    binary_nmu_version integer,
+    binary_nmu_changelog character varying,
+    failed_category character varying,
+    permbuildpri integer,
+    buildpri integer,
+    depends character varying,
+    rel character varying,
+    bd_problem text,
+    primary key (package, distribution)
+);
+
+create index "idx_ARCH_state" on "ARCH".packages (state);
+
+create table "ARCH".users
+(
+        username varchar,
+        distribution varchar,
+        last_seen timestamp,
+        primary key (username, distribution)
+);
+
+create table "ARCH".transactions
+(
+        package varchar,
+        distribution varchar,
+        version varchar,
+        action varchar,
+        prevstate varchar,
+        state varchar,
+        real_user varchar,
+        set_user varchar,
+        time timestamp
+);
+
+CREATE TABLE "ARCH".pkg_history (
+    package character varying NOT NULL,
+    distribution character varying NOT NULL,
+    version character varying NOT NULL,
+    "timestamp" timestamp without time zone NOT NULL,
+    result character varying NOT NULL,
+    build_time integer,
+    disk_space bigint,
+    primary key (package, distribution, version, "timestamp")
+);
+
+grant all on "ARCH".packages to "ARCH";
+grant all on "ARCH".users to "ARCH";
+grant insert, select on "ARCH".transactions to "ARCH";
+grant all on table "ARCH".pkg_history TO "ARCH";
+
+grant select on "ARCH".packages to wb_security;
+grant select on "ARCH".transactions to wb_security;
+grant select on "ARCH".pkg_history TO wb_security;
+
+create view "ARCH_public".packages as select * from "ARCH".packages where distribution not like '%-security';
+create view "ARCH_public".users as select * from "ARCH".users where distribution not like '%-security';
+create view "ARCH_public".transactions as select * from "ARCH".transactions where distribution not like '%-security';
+create view "ARCH_public".pkg_history as select * from "ARCH".pkg_history where distribution not like '%-security';
+
+grant select on "ARCH_public".packages to public;
+grant select on "ARCH_public".users to public;
+grant select on "ARCH_public".transactions to public;
+#grant select on "ARCH_public".pkg_history to public;
+

diff --git a/schema/arches-tables.sh b/schema/arches-tables.sh
new file mode 100755 (executable)
index 0000000..846ce65
--- /dev/null
+++ b/schema/arches-tables.sh
@@ -0,0 +1,6 @@
+#!/bin/sh
+
+ARCHES="alpha amd64 arm armel hppa i386 ia64 kfreebsd-amd64 kfreebsd-i386 mips mipsel powerpc s390 sparc"
+
+rm arches-tables.sql
+for arch in $ARCHES; do sed -e "s/ARCH/$arch/g" < arches-tables.in >> arches-tables.sql ; done

diff --git a/schema/roles.sql b/schema/roles.sql
new file mode 100644 (file)
index 0000000..367b3b9
--- /dev/null
+++ b/schema/roles.sql
@@ -0,0 +1,91 @@
+create role alpha;
+create role amd64;
+create role arm;
+create role armel;
+create role hppa;
+create role i386;
+create role ia64;
+create role "kfreebsd-amd64";
+create role "kfreebsd-i386";
+create role mips;
+create role mipsel;
+create role powerpc;
+create role s390;
+create role sparc;
+create role "hurd-i386";
+
+create user buildd_alpha;
+create user buildd_amd64;
+create user buildd_arm;
+create user buildd_armel;
+create user buildd_hppa;
+create user buildd_i386;
+create user buildd_ia64;
+create user "buildd_kfreebsd-amd64";
+create user "buildd_kfreebsd-i386";
+create user buildd_mips;
+create user buildd_mipsel;
+create user buildd_powerpc;
+create user buildd_s390;
+create user buildd_sparc;
+create user "buildd_hurd-i386";
+
+grant alpha to buildd_alpha;
+grant amd64 to buildd_amd64;
+grant arm to buildd_arm;
+grant armel to buildd_armel;
+grant hppa to buildd_hppa;
+grant i386 to buildd_i386;
+grant ia64 to buildd_ia64;
+grant "kfreebsd-amd64" to "buildd_kfreebsd-amd64";
+grant "kfreebsd-i386" to "buildd_kfreebsd-i386";
+grant mips to buildd_mips;
+grant mipsel to buildd_mipsel;
+grant powerpc to buildd_powerpc;
+grant s390 to buildd_s390;
+grant sparc to buildd_sparc;
+grant "hurd-i386" to "buildd_hurd-i386";
+
+create role wb_all in role alpha, amd64, arm, armel, hppa, i386, ia64, "kfreebsd-amd64", "kfreebsd-i386", mips, mipsel, powerpc, s390, sparc;
+grant "hurd-i386" to wb_all;
+
+grant wb_all to wbadm;
+
+create user aba in role wbadm;
+create user adeodato in role wbadm;
+create user aloiret in role alpha;
+create user aurel32 in role arm, armel, "kfreebsd-amd64", "kfreebsd-i386";
+create user colint in role arm, armel;
+create user fs in role amd64;
+create user he in role sparc, wbadm;
+create user jurij in role wb_all;
+create user kibi in role "kfreebsd-amd64", "kfreebsd-i386";
+create user kroeckx in role amd64, i386, wb_all, wbadm;
+create user lamont in role hppa, ia64;
+create user luk in role i386, wbadm;
+create user p2 in role mips;
+create user pkern in role i386, powerpc, wbadm;
+create user riku in role arm, armel;
+create user vorlon in role alpha, wb_all;
+create user waldi in role s390;
+create user wookey in role arm, armel;
+create user wouter in role powerpc;
+create user zobel in role mipsel, sparc, wb_all;
+create user sthibault in role "hurd-i386";
+create user mbanck in role "hurd-i386";
+
+create role wb_security;
+
+create user sf in role wb_security;
+create user devin in role wb_security;
+create user fw in role wb_security;
+create user geissert in role wb_security;
+create user iuculano in role wb_security;
+create user jmm in role wb_security;
+create user joey in role wb_security;
+create user nion in role wb_security;
+create user noahm in role wb_security;
+create user thijs in role wb_security;
+create user white in role wb_security;
+grant wb_security to dannf;
+