From 47af08e4fe8a9f804c98688b5a1d5c9c06e7e8cb Mon Sep 17 00:00:00 2001 From: Kurt Roeckx Date: Sat, 10 Apr 2010 19:59:09 +0000 Subject: [PATCH] Document schema As far as I know, this should be what is currently in our database. --- schema/arches-tables.in | 87 +++++++++++++++++++++++++++++++++++++++ schema/arches-tables.sh | 6 +++ schema/roles.sql | 91 +++++++++++++++++++++++++++++++++++++++++ 3 files changed, 184 insertions(+) create mode 100644 schema/arches-tables.in create mode 100755 schema/arches-tables.sh create mode 100644 schema/roles.sql diff --git a/schema/arches-tables.in b/schema/arches-tables.in new file mode 100644 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 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 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; + -- 2.39.2