diff options
| -rw-r--r-- | gentoobrowse/datasources/schema.sql | 2175 | ||||
| -rw-r--r-- | gentoobrowse/views/search.xml | 10 |
2 files changed, 2179 insertions, 6 deletions
diff --git a/gentoobrowse/datasources/schema.sql b/gentoobrowse/datasources/schema.sql new file mode 100644 index 0000000..47761fc --- /dev/null +++ b/gentoobrowse/datasources/schema.sql @@ -0,0 +1,2175 @@ +-- +-- PostgreSQL database dump +-- + +SET statement_timeout = 0; +SET lock_timeout = 0; +SET client_encoding = 'UTF8'; +SET standard_conforming_strings = on; +SET check_function_bodies = false; +SET client_min_messages = warning; + +-- +-- Name: gentoobrowse; Type: SCHEMA; Schema: -; Owner: gentoo +-- + +CREATE SCHEMA gentoobrowse; + + +ALTER SCHEMA gentoobrowse OWNER TO gentoo; + +SET search_path = gentoobrowse, pg_catalog; + +-- +-- Name: ebuildversion; Type: TYPE; Schema: gentoobrowse; Owner: gentoo +-- + +CREATE TYPE ebuildversion AS ( + ver bigint[], + versuffix character(1), + suffix bigint, + suffixnum bigint, + revision bigint +); + + +ALTER TYPE gentoobrowse.ebuildversion OWNER TO gentoo; + +SET default_tablespace = ''; + +SET default_with_oids = false; + +-- +-- Name: bugs; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE TABLE bugs ( + bugid integer NOT NULL, + severity text NOT NULL, + status text NOT NULL, + summary text NOT NULL, + firstseen timestamp without time zone DEFAULT now() NOT NULL +); + + +ALTER TABLE gentoobrowse.bugs OWNER TO gentoo; + +-- +-- Name: TABLE bugs; Type: COMMENT; Schema: gentoobrowse; Owner: gentoo +-- + +COMMENT ON TABLE bugs IS 'Import of Gentoo Bugs bug list from snapshots'; + + +-- +-- Name: bugfts(bugs); Type: FUNCTION; Schema: gentoobrowse; Owner: gentoo +-- + +CREATE FUNCTION bugfts(b bugs) RETURNS tsvector + LANGUAGE plpgsql IMMUTABLE + AS $$ +BEGIN + RETURN TO_TSVECTOR('english', regexp_replace(b.summary, '-[0-9][^ ]*', '', 'g')); +END +$$; + + +ALTER FUNCTION gentoobrowse.bugfts(b bugs) OWNER TO gentoo; + +-- +-- Name: ebuildfilter(text); Type: FUNCTION; Schema: gentoobrowse; Owner: gentoo +-- + +CREATE FUNCTION ebuildfilter(atomspec text) RETURNS SETOF integer + LANGUAGE plpgsql + AS $_$ +declare + op text; + cat text; + pkg text; + ver gentoobrowse.ebuildversion; +begin +if (atomspec like '=%*') then + atomspec = '~' || substr(atomspec, 2, length(atomspec) - 2); +end if; +op = (regexp_matches(atomspec, '^([^a-z]*)'))[1]; +cat = (regexp_matches(atomspec, '^[^a-z]*([^/]+)/'))[1]; +pkg = (regexp_matches(atomspec, '/(.+?)((-[0-9])|$)'))[1]; +ver = ebuildversion_constructor((regexp_matches(atomspec, '-([0-9]+.*)$'))[1]); +return query select ebuildid +from ebuilds e, packages p, categories c +where e.packageid = p.packageid +and p.categoryid = c.categoryid +and p.name = pkg +and c.name = cat +and case + when op = '=' then e.versioninst = ver + when op = '>=' then e.versioninst >= ver + when op = '<=' then e.versioninst <= ver + when op = '<' then e.versioninst < ver + when op = '>' then e.versioninst > ver + when op = '~' then e.versioninst ~ ver + when op = '' then true + else false +end +; +end; +$_$; + + +ALTER FUNCTION gentoobrowse.ebuildfilter(atomspec text) OWNER TO gentoo; + +-- +-- Name: ebuildversion_constructor(text); Type: FUNCTION; Schema: gentoobrowse; Owner: gentoo +-- + +CREATE FUNCTION ebuildversion_constructor(val text) RETURNS ebuildversion + LANGUAGE plpgsql IMMUTABLE + AS $_$ +declare + v ebuildversion; + s text; +begin + v.ver = string_to_array((regexp_matches(val, '^([0-9.]+)'))[1], '.')::bigint[]; + v.versuffix = (regexp_matches(val, '[0-9]([a-z])'))[1]; + v.versuffix = coalesce(v.versuffix, '@'); + s = (regexp_matches(val, '_([a-z]+)'))[1]; + case + when s = 'alpha' then v.suffix = -4; + when s = 'beta' then v.suffix = -3; + when s = 'pre' then v.suffix = -2; + when s = 'rc' then v.suffix = -1; + when s = 'p' then v.suffix = 1; + else v.suffix = 0; + end case; + v.suffixnum = (regexp_matches(val, '_[a-z]+([0-9]+)'))[1]; + v.suffixnum = coalesce(v.suffixnum, 0); + v.revision = (regexp_matches(val, '-r([0-9]+)$'))[1]; + v.revision = coalesce(v.revision, 0); + return v; +end; +$_$; + + +ALTER FUNCTION gentoobrowse.ebuildversion_constructor(val text) OWNER TO gentoo; + +-- +-- Name: ebuildversionrange(ebuildversion, ebuildversion); Type: FUNCTION; Schema: gentoobrowse; Owner: gentoo +-- + +CREATE FUNCTION ebuildversionrange(a ebuildversion, b ebuildversion) RETURNS boolean + LANGUAGE plpgsql + AS $$begin +-- If spec if longer, can't match +if (array_length(a.ver, 1) < array_length(b.ver, 1)) then + return false; +end if; +-- Otherwise, they have to start the same +if (position(array_to_string(b.ver, '.') in array_to_string(a.ver, '.')) != 1) then + return false; +end if; +if (a.ver = b.ver) then + if (a.versuffix != b.versuffix and b.versuffix != '@') then + return false; + end if; + if (a.suffix != b.suffix and b.suffix != 0) then + return false; + end if; + if (a.suffixnum != b.suffixnum and b.suffixnum != 0) then + return false; + end if; + if (a.revision != b.revision and b.revision != 0) then + return false; + end if; + return true; +else + return ((b.versuffix = '@' + and b.suffix = 0 + and b.suffixnum = 0 + and b.revision = 0) or b is null); +end if; +end +$$; + + +ALTER FUNCTION gentoobrowse.ebuildversionrange(a ebuildversion, b ebuildversion) OWNER TO gentoo; + +-- +-- Name: ebuildversiontotext(ebuildversion); Type: FUNCTION; Schema: gentoobrowse; Owner: gentoo +-- + +CREATE FUNCTION ebuildversiontotext(v ebuildversion) RETURNS text + LANGUAGE plpgsql + AS $_$ +declare +str text; +begin +str = array_to_string($1.ver, '.'); +if ($1.versuffix != '@') then + str = str || $1.versuffix; +end if; +if ($1.suffix != 0) then +str = str || ( + case + when $1.suffix = -4 then '_alpha' + when $1.suffix = -3 then '_beta' + when $1.suffix = -2 then '_pre' + when $1.suffix = -1 then '_rc' + when $1.suffix = 1 then '_p' + end ); + str = str || $1.suffixnum; +end if; +if ($1.revision != 0) then + str = str || '-r' || $1.revision; +end if; +return str; +end; +$_$; + + +ALTER FUNCTION gentoobrowse.ebuildversiontotext(v ebuildversion) OWNER TO gentoo; + +-- +-- Name: namedpackages(text); Type: FUNCTION; Schema: gentoobrowse; Owner: gentoo +-- + +CREATE FUNCTION namedpackages(str text) RETURNS text[] + LANGUAGE plpgsql IMMUTABLE + AS $$ +declare + pkgs text[]; + p record; +begin + for p in select regexp_matches(str, '([[:alnum:]]+-[[:alnum:]]+/(?:-?[_[:alpha:]+][[:alnum:]+]+)+)', 'g') as p + loop + pkgs = array_append(pkgs, p.p[1]); + end loop; + return pkgs; +end +$$; + + +ALTER FUNCTION gentoobrowse.namedpackages(str text) OWNER TO gentoo; + +-- +-- Name: packages; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE TABLE packages ( + packageid integer NOT NULL, + categoryid integer NOT NULL, + name text NOT NULL, + firstseen timestamp without time zone DEFAULT now() NOT NULL, + description text NOT NULL, + summary text, + maintainer text, + herd text, + metadatadate timestamp without time zone +); + + +ALTER TABLE gentoobrowse.packages OWNER TO gentoo; + +-- +-- Name: TABLE packages; Type: COMMENT; Schema: gentoobrowse; Owner: gentoo +-- + +COMMENT ON TABLE packages IS 'Packages as summarised from their ebuilds'; + + +-- +-- Name: packagefts(packages); Type: FUNCTION; Schema: gentoobrowse; Owner: gentoo +-- + +CREATE FUNCTION packagefts(p packages) RETURNS tsvector + LANGUAGE plpgsql IMMUTABLE + AS $$ +BEGIN + RETURN (SETWEIGHT(TO_TSVECTOR('english', p.name), 'A') || + SETWEIGHT(TO_TSVECTOR('english', p.description), 'B') || + SETWEIGHT(TO_TSVECTOR('english', COALESCE(p.description, '')), 'C')); +END +$$; + + +ALTER FUNCTION gentoobrowse.packagefts(p packages) OWNER TO gentoo; + +-- +-- Name: ~; Type: OPERATOR; Schema: gentoobrowse; Owner: gentoo +-- + +CREATE OPERATOR ~ ( + PROCEDURE = ebuildversionrange, + LEFTARG = ebuildversion, + RIGHTARG = ebuildversion +); + + +ALTER OPERATOR gentoobrowse.~ (ebuildversion, ebuildversion) OWNER TO gentoo; + +-- +-- Name: categories; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE TABLE categories ( + categoryid integer NOT NULL, + name text, + summary text, + metadatadate timestamp without time zone +); + + +ALTER TABLE gentoobrowse.categories OWNER TO gentoo; + +-- +-- Name: TABLE categories; Type: COMMENT; Schema: gentoobrowse; Owner: gentoo +-- + +COMMENT ON TABLE categories IS 'Gentoo defined package categories'; + + +-- +-- Name: ebuild_archs; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE TABLE ebuild_archs ( + ebuildid integer NOT NULL, + arch text NOT NULL +); + + +ALTER TABLE gentoobrowse.ebuild_archs OWNER TO gentoo; + +-- +-- Name: TABLE ebuild_archs; Type: COMMENT; Schema: gentoobrowse; Owner: gentoo +-- + +COMMENT ON TABLE ebuild_archs IS 'Architectures an ebuild is suitable for'; + + +-- +-- Name: ebuild_deps; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE TABLE ebuild_deps ( + ebuildid integer NOT NULL, + packageid integer NOT NULL, + versionspec text NOT NULL, + flags text NOT NULL, + op character varying(4) NOT NULL, + slot text NOT NULL +); + + +ALTER TABLE gentoobrowse.ebuild_deps OWNER TO gentoo; + +-- +-- Name: TABLE ebuild_deps; Type: COMMENT; Schema: gentoobrowse; Owner: gentoo +-- + +COMMENT ON TABLE ebuild_deps IS 'The dependencies of ebuilds (includes blocks, etc)'; + + +-- +-- Name: ebuild_masks; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE TABLE ebuild_masks ( + line integer NOT NULL, + setno integer NOT NULL, + ebuildid integer NOT NULL +); + + +ALTER TABLE gentoobrowse.ebuild_masks OWNER TO gentoo; + +-- +-- Name: ebuild_rdeps; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE TABLE ebuild_rdeps ( + ebuildid integer NOT NULL, + packageid integer NOT NULL, + versionspec text NOT NULL, + flags text NOT NULL, + op character varying(4) NOT NULL, + slot text NOT NULL +); + + +ALTER TABLE gentoobrowse.ebuild_rdeps OWNER TO gentoo; + +-- +-- Name: ebuild_uses; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE TABLE ebuild_uses ( + ebuildid integer NOT NULL, + use text NOT NULL +); + + +ALTER TABLE gentoobrowse.ebuild_uses OWNER TO gentoo; + +-- +-- Name: TABLE ebuild_uses; Type: COMMENT; Schema: gentoobrowse; Owner: gentoo +-- + +COMMENT ON TABLE ebuild_uses IS 'Use flags affecting ebuilds'; + + +-- +-- Name: ebuilds; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE TABLE ebuilds ( + ebuildid integer NOT NULL, + packageid integer NOT NULL, + version text NOT NULL, + versioninst ebuildversion NOT NULL, + slot text NOT NULL, + license text, + firstseen timestamp without time zone NOT NULL, + moddate timestamp without time zone NOT NULL +); + + +ALTER TABLE gentoobrowse.ebuilds OWNER TO gentoo; + +-- +-- Name: TABLE ebuilds; Type: COMMENT; Schema: gentoobrowse; Owner: gentoo +-- + +COMMENT ON TABLE ebuilds IS 'Ebuilds :-)'; + + +-- +-- Name: filecache; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE TABLE filecache ( + line integer NOT NULL, + content text NOT NULL, + fileid integer NOT NULL +); + + +ALTER TABLE gentoobrowse.filecache OWNER TO gentoo; + +-- +-- Name: files; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE TABLE files ( + filename text NOT NULL, + fileid integer NOT NULL, + moddate timestamp without time zone NOT NULL, + firstseen timestamp without time zone DEFAULT now() NOT NULL, + cachedat timestamp without time zone, + filetypeid integer NOT NULL +); + + +ALTER TABLE gentoobrowse.files OWNER TO gentoo; + +-- +-- Name: file_masks; Type: VIEW; Schema: gentoobrowse; Owner: gentoo +-- + +CREATE VIEW file_masks AS + SELECT fc.line, + fc.content + FROM files f, + filecache fc + WHERE (((f.filename = 'package.mask'::text) AND (f.fileid = fc.fileid)) AND (fc.line > 32)); + + +ALTER TABLE gentoobrowse.file_masks OWNER TO gentoo; + +-- +-- Name: file_masks_withset; Type: VIEW; Schema: gentoobrowse; Owner: gentoo +-- + +CREATE VIEW file_masks_withset AS + SELECT o.line, + o.content, + CASE + WHEN (o.content !~~ '#%'::text) THEN ( SELECT max(i.line) AS max + FROM file_masks i + WHERE ((i.line <= o.line) AND (substr(i.content, 1, 1) = '#'::text))) + ELSE ( SELECT (min(i.line) - 1) + FROM file_masks i + WHERE ((i.line >= o.line) AND (substr(i.content, 1, 1) <> '#'::text))) + END AS setno + FROM file_masks o; + + +ALTER TABLE gentoobrowse.file_masks_withset OWNER TO gentoo; + +-- +-- Name: filetypes; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE TABLE filetypes ( + filetypeid integer NOT NULL, + description text NOT NULL, + searchroot text, + searchspec text, + content boolean DEFAULT true NOT NULL +); + + +ALTER TABLE gentoobrowse.filetypes OWNER TO gentoo; + +-- +-- Name: TABLE filetypes; Type: COMMENT; Schema: gentoobrowse; Owner: gentoo +-- + +COMMENT ON TABLE filetypes IS 'What kind of file is this?... metadata, changelog, etc'; + + +-- +-- Name: license; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE TABLE license ( + name text NOT NULL, + legalbumph text +); + + +ALTER TABLE gentoobrowse.license OWNER TO gentoo; + +-- +-- Name: masksets; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE TABLE masksets ( + setno integer NOT NULL, + person text NOT NULL, + email text NOT NULL, + dateadded date NOT NULL, + message text NOT NULL +); + + +ALTER TABLE gentoobrowse.masksets OWNER TO gentoo; + +-- +-- Name: package_bugs; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE TABLE package_bugs ( + packageid integer NOT NULL, + bugid integer NOT NULL, + firstseen timestamp without time zone DEFAULT now() NOT NULL +); + + +ALTER TABLE gentoobrowse.package_bugs OWNER TO gentoo; + +-- +-- Name: TABLE package_bugs; Type: COMMENT; Schema: gentoobrowse; Owner: gentoo +-- + +COMMENT ON TABLE package_bugs IS 'Association of bugs to products by bug description'; + + +-- +-- Name: package_changelogs; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE TABLE package_changelogs ( + packageid integer NOT NULL, + date date NOT NULL, + n smallint NOT NULL, + person text, + email text, + comment text +); + + +ALTER TABLE gentoobrowse.package_changelogs OWNER TO gentoo; + +-- +-- Name: package_urls; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE TABLE package_urls ( + packageid integer NOT NULL, + url text NOT NULL +); + + +ALTER TABLE gentoobrowse.package_urls OWNER TO gentoo; + +-- +-- Name: TABLE package_urls; Type: COMMENT; Schema: gentoobrowse; Owner: gentoo +-- + +COMMENT ON TABLE package_urls IS 'URLs associated with a given package'; + + +-- +-- Name: seq_categoryid; Type: SEQUENCE; Schema: gentoobrowse; Owner: gentoo +-- + +CREATE SEQUENCE seq_categoryid + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE gentoobrowse.seq_categoryid OWNER TO gentoo; + +-- +-- Name: seq_categoryid; Type: SEQUENCE OWNED BY; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER SEQUENCE seq_categoryid OWNED BY categories.categoryid; + + +-- +-- Name: seq_fileid; Type: SEQUENCE; Schema: gentoobrowse; Owner: gentoo +-- + +CREATE SEQUENCE seq_fileid + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE gentoobrowse.seq_fileid OWNER TO gentoo; + +-- +-- Name: seq_fileid; Type: SEQUENCE OWNED BY; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER SEQUENCE seq_fileid OWNED BY files.fileid; + + +-- +-- Name: seq_packageid; Type: SEQUENCE; Schema: gentoobrowse; Owner: gentoo +-- + +CREATE SEQUENCE seq_packageid + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE gentoobrowse.seq_packageid OWNER TO gentoo; + +-- +-- Name: seq_packageid; Type: SEQUENCE OWNED BY; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER SEQUENCE seq_packageid OWNED BY packages.packageid; + + +-- +-- Name: use_global; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE TABLE use_global ( + use text NOT NULL, + description text +); + + +ALTER TABLE gentoobrowse.use_global OWNER TO gentoo; + +-- +-- Name: use_group; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE TABLE use_group ( + usegroupid integer NOT NULL, + use text NOT NULL, + description text NOT NULL +); + + +ALTER TABLE gentoobrowse.use_group OWNER TO gentoo; + +-- +-- Name: use_groups; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE TABLE use_groups ( + usegroupid integer NOT NULL, + name text NOT NULL +); + + +ALTER TABLE gentoobrowse.use_groups OWNER TO gentoo; + +-- +-- Name: use_groups_usegroupid_seq; Type: SEQUENCE; Schema: gentoobrowse; Owner: gentoo +-- + +CREATE SEQUENCE use_groups_usegroupid_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE gentoobrowse.use_groups_usegroupid_seq OWNER TO gentoo; + +-- +-- Name: use_groups_usegroupid_seq; Type: SEQUENCE OWNED BY; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER SEQUENCE use_groups_usegroupid_seq OWNED BY use_groups.usegroupid; + + +-- +-- Name: use_local; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE TABLE use_local ( + packageid integer NOT NULL, + use text NOT NULL, + description text +); + + +ALTER TABLE gentoobrowse.use_local OWNER TO gentoo; + +-- +-- Name: user_ebuild_emails; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE TABLE user_ebuild_emails ( + userid integer NOT NULL, + ebuildid integer NOT NULL, + sentat timestamp without time zone NOT NULL +); + + +ALTER TABLE gentoobrowse.user_ebuild_emails OWNER TO gentoo; + +-- +-- Name: user_packages; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE TABLE user_packages ( + userid integer NOT NULL, + packageid integer NOT NULL, + trackedsince timestamp without time zone DEFAULT now() NOT NULL +); + + +ALTER TABLE gentoobrowse.user_packages OWNER TO gentoo; + +-- +-- Name: TABLE user_packages; Type: COMMENT; Schema: gentoobrowse; Owner: gentoo +-- + +COMMENT ON TABLE user_packages IS 'Lists packages that registered users are interested in'; + + +-- +-- Name: users; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE TABLE users ( + userid integer NOT NULL, + username text NOT NULL, + userrealname text NOT NULL, + userpassword text NOT NULL, + useremail text NOT NULL, + verifyguid uuid +); + + +ALTER TABLE gentoobrowse.users OWNER TO gentoo; + +-- +-- Name: TABLE users; Type: COMMENT; Schema: gentoobrowse; Owner: gentoo +-- + +COMMENT ON TABLE users IS 'Registered users'; + + +-- +-- Name: users_userid_seq; Type: SEQUENCE; Schema: gentoobrowse; Owner: gentoo +-- + +CREATE SEQUENCE users_userid_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE gentoobrowse.users_userid_seq OWNER TO gentoo; + +-- +-- Name: users_userid_seq; Type: SEQUENCE OWNED BY; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER SEQUENCE users_userid_seq OWNED BY users.userid; + + +-- +-- Name: categoryid; Type: DEFAULT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY categories ALTER COLUMN categoryid SET DEFAULT nextval('seq_categoryid'::regclass); + + +-- +-- Name: fileid; Type: DEFAULT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY files ALTER COLUMN fileid SET DEFAULT nextval('seq_fileid'::regclass); + + +-- +-- Name: packageid; Type: DEFAULT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY packages ALTER COLUMN packageid SET DEFAULT nextval('seq_packageid'::regclass); + + +-- +-- Name: usegroupid; Type: DEFAULT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY use_groups ALTER COLUMN usegroupid SET DEFAULT nextval('use_groups_usegroupid_seq'::regclass); + + +-- +-- Name: userid; Type: DEFAULT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY users ALTER COLUMN userid SET DEFAULT nextval('users_userid_seq'::regclass); + + +-- +-- Name: license_pkey; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY license + ADD CONSTRAINT license_pkey PRIMARY KEY (name); + + +-- +-- Name: pk_bugs; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY bugs + ADD CONSTRAINT pk_bugs PRIMARY KEY (bugid); + + +-- +-- Name: pk_ebuild; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY ebuilds + ADD CONSTRAINT pk_ebuild PRIMARY KEY (ebuildid); + + +-- +-- Name: pk_ebuildarch; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY ebuild_archs + ADD CONSTRAINT pk_ebuildarch PRIMARY KEY (ebuildid, arch); + + +-- +-- Name: pk_ebuilddeps; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY ebuild_deps + ADD CONSTRAINT pk_ebuilddeps PRIMARY KEY (ebuildid, packageid, versionspec, flags, slot, op); + + +-- +-- Name: pk_ebuildrdeps; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY ebuild_rdeps + ADD CONSTRAINT pk_ebuildrdeps PRIMARY KEY (ebuildid, packageid, versionspec, flags, slot, op); + + +-- +-- Name: pk_ebuilduses; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY ebuild_uses + ADD CONSTRAINT pk_ebuilduses PRIMARY KEY (ebuildid, use); + + +-- +-- Name: pk_filecache; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY filecache + ADD CONSTRAINT pk_filecache PRIMARY KEY (fileid, line); + + +-- +-- Name: pk_fileclass; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY filetypes + ADD CONSTRAINT pk_fileclass PRIMARY KEY (filetypeid); + + +-- +-- Name: pk_files; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY files + ADD CONSTRAINT pk_files PRIMARY KEY (fileid); + + +-- +-- Name: pk_package_changelogs; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY package_changelogs + ADD CONSTRAINT pk_package_changelogs PRIMARY KEY (packageid, date, n); + + +-- +-- Name: pk_packagebugs; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY package_bugs + ADD CONSTRAINT pk_packagebugs PRIMARY KEY (packageid, bugid); + + +-- +-- Name: pk_packagemasks; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY ebuild_masks + ADD CONSTRAINT pk_packagemasks PRIMARY KEY (ebuildid, setno, line); + + +-- +-- Name: pk_packagemasksets; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY masksets + ADD CONSTRAINT pk_packagemasksets PRIMARY KEY (setno); + + +-- +-- Name: pk_packages; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY packages + ADD CONSTRAINT pk_packages PRIMARY KEY (packageid); + + +-- +-- Name: pk_pkggroup; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY categories + ADD CONSTRAINT pk_pkggroup PRIMARY KEY (categoryid); + + +-- +-- Name: pk_pkgurls; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY package_urls + ADD CONSTRAINT pk_pkgurls PRIMARY KEY (packageid, url); + + +-- +-- Name: pk_usegroup; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY use_groups + ADD CONSTRAINT pk_usegroup PRIMARY KEY (usegroupid); + + +-- +-- Name: pk_userebuildemails; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY user_ebuild_emails + ADD CONSTRAINT pk_userebuildemails PRIMARY KEY (userid, ebuildid); + + +-- +-- Name: pk_userpackages; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY user_packages + ADD CONSTRAINT pk_userpackages PRIMARY KEY (userid, packageid); + + +-- +-- Name: pk_users; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY users + ADD CONSTRAINT pk_users PRIMARY KEY (userid); + + +-- +-- Name: uni_ebuild_pkgver; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY ebuilds + ADD CONSTRAINT uni_ebuild_pkgver UNIQUE (packageid, version); + + +-- +-- Name: uni_files_filename; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY files + ADD CONSTRAINT uni_files_filename UNIQUE (filename); + + +-- +-- Name: uni_pkg_name; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY packages + ADD CONSTRAINT uni_pkg_name UNIQUE (categoryid, name); + + +-- +-- Name: uni_pkggroup_group; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY categories + ADD CONSTRAINT uni_pkggroup_group UNIQUE (name); + + +-- +-- Name: uni_useremail; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY users + ADD CONSTRAINT uni_useremail UNIQUE (useremail); + + +-- +-- Name: uni_username; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY users + ADD CONSTRAINT uni_username UNIQUE (username); + + +-- +-- Name: use_global_pkey; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY use_global + ADD CONSTRAINT use_global_pkey PRIMARY KEY (use); + + +-- +-- Name: use_group_pkey; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY use_group + ADD CONSTRAINT use_group_pkey PRIMARY KEY (usegroupid, use); + + +-- +-- Name: use_local_pkey; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY use_local + ADD CONSTRAINT use_local_pkey PRIMARY KEY (packageid, use); + + +-- +-- Name: idx_bug_fts; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE INDEX idx_bug_fts ON bugs USING gin (bugfts(bugs.*)); + + +-- +-- Name: idx_bugs_firstseen; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE INDEX idx_bugs_firstseen ON bugs USING btree (firstseen DESC); + + +-- +-- Name: idx_bugs_summarypackages; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE INDEX idx_bugs_summarypackages ON bugs USING gin (namedpackages(summary)); + + +-- +-- Name: idx_ebuilddeps_package; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE INDEX idx_ebuilddeps_package ON ebuild_deps USING btree (packageid); + + +-- +-- Name: idx_ebuildrdeps_package; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE INDEX idx_ebuildrdeps_package ON ebuild_rdeps USING btree (packageid); + + +-- +-- Name: idx_ebuilds_firstseen; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE INDEX idx_ebuilds_firstseen ON ebuilds USING btree (firstseen); + + +-- +-- Name: idx_ebuilduses_use; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE INDEX idx_ebuilduses_use ON ebuild_uses USING btree (use); + + +-- +-- Name: idx_filecache_line; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE INDEX idx_filecache_line ON filecache USING btree (line); + +ALTER TABLE filecache CLUSTER ON idx_filecache_line; + + +-- +-- Name: idx_package_name; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE INDEX idx_package_name ON packages USING btree (name); + + +-- +-- Name: idx_packagebugs_bug; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE INDEX idx_packagebugs_bug ON package_bugs USING btree (bugid); + + +-- +-- Name: idx_packages_fts; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE INDEX idx_packages_fts ON packages USING gin (packagefts(packages.*)); + + +-- +-- Name: idx_packages_herd; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE INDEX idx_packages_herd ON packages USING btree (herd); + + +-- +-- Name: idx_uselocal_use; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE INDEX idx_uselocal_use ON use_local USING btree (use); + + +-- +-- Name: idx_userpackages_package; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: +-- + +CREATE INDEX idx_userpackages_package ON user_packages USING btree (packageid); + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- + + + +-- +-- Name: fk_ebuild_pkg; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY ebuilds + ADD CONSTRAINT fk_ebuild_pkg FOREIGN KEY (packageid) REFERENCES packages(packageid) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: fk_ebuildarch_file; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY ebuild_archs + ADD CONSTRAINT fk_ebuildarch_file FOREIGN KEY (ebuildid) REFERENCES ebuilds(ebuildid) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: fk_ebuilddeps_ebuild; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY ebuild_deps + ADD CONSTRAINT fk_ebuilddeps_ebuild FOREIGN KEY (ebuildid) REFERENCES ebuilds(ebuildid) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: fk_ebuilddeps_pkg; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY ebuild_deps + ADD CONSTRAINT fk_ebuilddeps_pkg FOREIGN KEY (packageid) REFERENCES packages(packageid) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: fk_ebuildrdeps_ebuild; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY ebuild_rdeps + ADD CONSTRAINT fk_ebuildrdeps_ebuild FOREIGN KEY (ebuildid) REFERENCES ebuilds(ebuildid) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: fk_ebuildrdeps_pkg; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY ebuild_rdeps + ADD CONSTRAINT fk_ebuildrdeps_pkg FOREIGN KEY (packageid) REFERENCES packages(packageid) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: fk_ebuilduses_file; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY ebuild_uses + ADD CONSTRAINT fk_ebuilduses_file FOREIGN KEY (ebuildid) REFERENCES ebuilds(ebuildid) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: fk_filecache_fileid; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY filecache + ADD CONSTRAINT fk_filecache_fileid FOREIGN KEY (fileid) REFERENCES files(fileid) ON DELETE CASCADE; + + +-- +-- Name: fk_files_type; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY files + ADD CONSTRAINT fk_files_type FOREIGN KEY (filetypeid) REFERENCES filetypes(filetypeid) ON UPDATE CASCADE; + + +-- +-- Name: fk_packagebugs_bug; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY package_bugs + ADD CONSTRAINT fk_packagebugs_bug FOREIGN KEY (bugid) REFERENCES bugs(bugid) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: fk_packagebugs_package; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY package_bugs + ADD CONSTRAINT fk_packagebugs_package FOREIGN KEY (packageid) REFERENCES packages(packageid) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: fk_packagechangelogs_package; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY package_changelogs + ADD CONSTRAINT fk_packagechangelogs_package FOREIGN KEY (packageid) REFERENCES packages(packageid) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: fk_packageurls_package; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY package_urls + ADD CONSTRAINT fk_packageurls_package FOREIGN KEY (packageid) REFERENCES packages(packageid) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: fk_pkg_grp; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY packages + ADD CONSTRAINT fk_pkg_grp FOREIGN KEY (categoryid) REFERENCES categories(categoryid) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: fk_pkgurls_pkg; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY package_urls + ADD CONSTRAINT fk_pkgurls_pkg FOREIGN KEY (packageid) REFERENCES packages(packageid) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: fk_usegroup_groupid; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY use_group + ADD CONSTRAINT fk_usegroup_groupid FOREIGN KEY (usegroupid) REFERENCES use_groups(usegroupid) ON DELETE CASCADE; + + +-- +-- Name: fk_uselocal_package; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY use_local + ADD CONSTRAINT fk_uselocal_package FOREIGN KEY (packageid) REFERENCES packages(packageid) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: fk_userebuildemails_ebuild; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY user_ebuild_emails + ADD CONSTRAINT fk_userebuildemails_ebuild FOREIGN KEY (ebuildid) REFERENCES ebuilds(ebuildid) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: fk_userebuildemails_user; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY user_ebuild_emails + ADD CONSTRAINT fk_userebuildemails_user FOREIGN KEY (userid) REFERENCES users(userid) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: fk_userpackage_package; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY user_packages + ADD CONSTRAINT fk_userpackage_package FOREIGN KEY (packageid) REFERENCES packages(packageid) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: fk_userpackage_user; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY user_packages + ADD CONSTRAINT fk_userpackage_user FOREIGN KEY (userid) REFERENCES users(userid) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: fk_userpackages_package; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo +-- + +ALTER TABLE ONLY user_packages + ADD CONSTRAINT fk_userpackages_package FOREIGN KEY (packageid) REFERENCES packages(packageid) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: gentoobrowse; Type: ACL; Schema: -; Owner: gentoo +-- + +REVOKE ALL ON SCHEMA gentoobrowse FROM PUBLIC; +REVOKE ALL ON SCHEMA gentoobrowse FROM gentoo; +GRANT USAGE ON SCHEMA gentoobrowse TO apache; + + +-- +-- Name: bugs; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON TABLE bugs FROM PUBLIC; +REVOKE ALL ON TABLE bugs FROM gentoo; +GRANT ALL ON TABLE bugs TO gentoo; +GRANT SELECT ON TABLE bugs TO apache; + + +-- +-- Name: packages; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON TABLE packages FROM PUBLIC; +REVOKE ALL ON TABLE packages FROM gentoo; +GRANT ALL ON TABLE packages TO gentoo; +GRANT SELECT ON TABLE packages TO apache; + + +-- +-- Name: categories; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON TABLE categories FROM PUBLIC; +REVOKE ALL ON TABLE categories FROM gentoo; +GRANT ALL ON TABLE categories TO gentoo; +GRANT SELECT ON TABLE categories TO apache; + + +-- +-- Name: ebuild_archs; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON TABLE ebuild_archs FROM PUBLIC; +REVOKE ALL ON TABLE ebuild_archs FROM gentoo; +GRANT ALL ON TABLE ebuild_archs TO gentoo; +GRANT SELECT ON TABLE ebuild_archs TO apache; + + +-- +-- Name: ebuild_deps; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON TABLE ebuild_deps FROM PUBLIC; +REVOKE ALL ON TABLE ebuild_deps FROM gentoo; +GRANT ALL ON TABLE ebuild_deps TO gentoo; +GRANT SELECT ON TABLE ebuild_deps TO apache; + + +-- +-- Name: ebuild_masks; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON TABLE ebuild_masks FROM PUBLIC; +REVOKE ALL ON TABLE ebuild_masks FROM gentoo; +GRANT ALL ON TABLE ebuild_masks TO gentoo; +GRANT SELECT ON TABLE ebuild_masks TO apache; + + +-- +-- Name: ebuild_rdeps; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON TABLE ebuild_rdeps FROM PUBLIC; +REVOKE ALL ON TABLE ebuild_rdeps FROM gentoo; +GRANT ALL ON TABLE ebuild_rdeps TO gentoo; +GRANT SELECT ON TABLE ebuild_rdeps TO apache; + + +-- +-- Name: ebuild_uses; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON TABLE ebuild_uses FROM PUBLIC; +REVOKE ALL ON TABLE ebuild_uses FROM gentoo; +GRANT ALL ON TABLE ebuild_uses TO gentoo; +GRANT SELECT ON TABLE ebuild_uses TO apache; + + +-- +-- Name: ebuilds; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON TABLE ebuilds FROM PUBLIC; +REVOKE ALL ON TABLE ebuilds FROM gentoo; +GRANT ALL ON TABLE ebuilds TO gentoo; +GRANT SELECT ON TABLE ebuilds TO apache; + + +-- +-- Name: filecache; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON TABLE filecache FROM PUBLIC; +REVOKE ALL ON TABLE filecache FROM gentoo; +GRANT ALL ON TABLE filecache TO gentoo; +GRANT SELECT ON TABLE filecache TO apache; + + +-- +-- Name: files; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON TABLE files FROM PUBLIC; +REVOKE ALL ON TABLE files FROM gentoo; +GRANT ALL ON TABLE files TO gentoo; +GRANT SELECT ON TABLE files TO apache; + + +-- +-- Name: file_masks; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON TABLE file_masks FROM PUBLIC; +REVOKE ALL ON TABLE file_masks FROM gentoo; +GRANT ALL ON TABLE file_masks TO gentoo; +GRANT SELECT ON TABLE file_masks TO apache; + + +-- +-- Name: file_masks_withset; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON TABLE file_masks_withset FROM PUBLIC; +REVOKE ALL ON TABLE file_masks_withset FROM gentoo; +GRANT ALL ON TABLE file_masks_withset TO gentoo; +GRANT SELECT ON TABLE file_masks_withset TO apache; + + +-- +-- Name: filetypes; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON TABLE filetypes FROM PUBLIC; +REVOKE ALL ON TABLE filetypes FROM gentoo; +GRANT ALL ON TABLE filetypes TO gentoo; +GRANT SELECT ON TABLE filetypes TO apache; + + +-- +-- Name: masksets; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON TABLE masksets FROM PUBLIC; +REVOKE ALL ON TABLE masksets FROM gentoo; +GRANT ALL ON TABLE masksets TO gentoo; +GRANT SELECT ON TABLE masksets TO apache; + + +-- +-- Name: package_bugs; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON TABLE package_bugs FROM PUBLIC; +REVOKE ALL ON TABLE package_bugs FROM gentoo; +GRANT ALL ON TABLE package_bugs TO gentoo; +GRANT SELECT ON TABLE package_bugs TO apache; + + +-- +-- Name: package_changelogs; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON TABLE package_changelogs FROM PUBLIC; +REVOKE ALL ON TABLE package_changelogs FROM gentoo; +GRANT ALL ON TABLE package_changelogs TO gentoo; +GRANT SELECT ON TABLE package_changelogs TO apache; + + +-- +-- Name: package_urls; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON TABLE package_urls FROM PUBLIC; +REVOKE ALL ON TABLE package_urls FROM gentoo; +GRANT ALL ON TABLE package_urls TO gentoo; +GRANT SELECT ON TABLE package_urls TO apache; + + +-- +-- Name: use_global; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON TABLE use_global FROM PUBLIC; +REVOKE ALL ON TABLE use_global FROM gentoo; +GRANT ALL ON TABLE use_global TO gentoo; +GRANT SELECT ON TABLE use_global TO apache; + + +-- +-- Name: use_group; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON TABLE use_group FROM PUBLIC; +REVOKE ALL ON TABLE use_group FROM gentoo; +GRANT ALL ON TABLE use_group TO gentoo; +GRANT SELECT ON TABLE use_group TO apache; + + +-- +-- Name: use_groups; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON TABLE use_groups FROM PUBLIC; +REVOKE ALL ON TABLE use_groups FROM gentoo; +GRANT ALL ON TABLE use_groups TO gentoo; +GRANT SELECT ON TABLE use_groups TO apache; + + +-- +-- Name: use_local; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON TABLE use_local FROM PUBLIC; +REVOKE ALL ON TABLE use_local FROM gentoo; +GRANT ALL ON TABLE use_local TO gentoo; +GRANT SELECT ON TABLE use_local TO apache; + + +-- +-- Name: user_ebuild_emails; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON TABLE user_ebuild_emails FROM PUBLIC; +REVOKE ALL ON TABLE user_ebuild_emails FROM gentoo; +GRANT ALL ON TABLE user_ebuild_emails TO gentoo; +GRANT SELECT ON TABLE user_ebuild_emails TO apache; + + +-- +-- Name: user_packages; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON TABLE user_packages FROM PUBLIC; +REVOKE ALL ON TABLE user_packages FROM gentoo; +GRANT ALL ON TABLE user_packages TO gentoo; +GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE user_packages TO apache; + + +-- +-- Name: users; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON TABLE users FROM PUBLIC; +REVOKE ALL ON TABLE users FROM gentoo; +GRANT ALL ON TABLE users TO gentoo; +GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE users TO apache; + + +-- +-- Name: users_userid_seq; Type: ACL; Schema: gentoobrowse; Owner: gentoo +-- + +REVOKE ALL ON SEQUENCE users_userid_seq FROM PUBLIC; +REVOKE ALL ON SEQUENCE users_userid_seq FROM gentoo; +GRANT ALL ON SEQUENCE users_userid_seq TO gentoo; +GRANT ALL ON SEQUENCE users_userid_seq TO apache; + + +-- +-- PostgreSQL database dump complete +-- + diff --git a/gentoobrowse/views/search.xml b/gentoobrowse/views/search.xml index 347642a..063098d 100644 --- a/gentoobrowse/views/search.xml +++ b/gentoobrowse/views/search.xml @@ -5,14 +5,12 @@ <sql> select c.name as catname, p.name as pkgname, description <filter name="freeText">, - ts_rank((setweight(to_tsvector('english', p.name), 'A') || setweight(to_tsvector('english', p.description), 'B')), - plainto_tsquery('english', <param name="criteria" />)) as rank + ts_rank(packagefts(p), plainto_tsquery('english', <param name="criteria" />)) as rank </filter> from packages p, categories c where p.categoryid = c.categoryid <filter name="freeText"> - and (setweight(to_tsvector('english', p.name), 'A') || setweight(to_tsvector('english', p.description), 'B')) - @@ plainto_tsquery('english', <param name="criteria" />) + and packagefts(p) @@ plainto_tsquery('english', <param name="criteria" />) order by rank desc, p.name </filter> </sql> @@ -21,11 +19,11 @@ <sql> SELECT b.bugid, b.severity, b.status, b.summary, b.firstseen <filter name="freeText">, - ts_rank(to_tsvector('english', regexp_replace(summary, '-[0-9][^ ]*', '', 'g')), plainto_tsquery('english', <param name="criteria" />)) as rank + ts_rank(bugfts(b), plainto_tsquery('english', <param name="criteria" />)) as rank </filter> FROM bugs b <filter name="freeText"> - WHERE to_tsvector('english', summary) @@ plainto_tsquery('english', <param name="criteria" />) + WHERE bugfts(b) @@ plainto_tsquery('english', <param name="criteria" />) ORDER BY rank DESC, bugid DESC </filter> </sql> |