Copy of schema with new functional FTS indexes

Switch search to use functional FTS indexes

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>