Proposed SQL schema rev2

Todd Nemanich <[email protected]> Fri, 01 Feb 2002 19:13:26 -0500

Newsgroups	gmane.network.up2date.current.devel
Message-ID	<[email protected]>

Hey fellas,
	Attached is a file containing the statements needed 
to create a simplified version of the schema I 
proposed. Packagers, package_layers, checksums, 
and devices have been cut as per Hunter's request. 
A relationship has been added from packages to 
channels, but I still need to put a constraint on 
it to make sure that the package is of an 
acceptable arch. On the same note, arch is now a 
attribute of packages.
	The trigger functions for the dependencies aren't 
done yet, but I'll pass them on when I finish 
them. The trigger on the subchannel table enforces 
the hierarchy of the channels. It also needs some 
work (make sure that the subchannel arch is 
compatible with the parent), but it is in decent 
working order. I'll try to get Toby's CurrentDB.py 
updated to work with this schema.

current_schema_rev2.sql (text/plain, 4 KB)

CREATE SEQUENCE packages_package_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647
        START 1 CACHE 25 CYCLE;

CREATE TABLE packages (
	package_id int4 DEFAULT nextval('packages_package_id_seq') NOT NULL,
	name text NOT NULL,
	version text NOT NULL,
	release text NOT NULL,
	epoch int4 DEFAULT 0 NOT NULL,
	arch varchar(6) DEFAULT 'i386' NOT NULL,
	os text DEFAULT 'Linux' NOT NULL,
	summary text DEFAULT '' NOT NULL,
	packager_email text NULL,
	PRIMARY KEY (package_id),
	CONSTRAINT package_info_key UNIQUE (name,version,release,epoch,arch)
);

CREATE TABLE package_files (
	pkg_id int4 NOT NULL,
	package_file text NOT NULL,
	source_package text NULL,
	headers_file text NOT NULL,
	PRIMARY KEY (pkg_id),
	CONSTRAINT pkg_files_pkgs_fkey FOREIGN KEY (pkg_id) REFERENCES packages(package_id) 
		MATCH FULL ON DELETE CASCADE ON UPDATE RESTRICT DEFERRABLE
);

CREATE TABLE channels (
	name text NOT NULL,
	arch_fam varchar(6) NOT NULL,
	PRIMARY KEY (name)
);

CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
    '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C';

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
    HANDLER plpgsql_call_handler
    LANCOMPILER 'PL/pgSQL';

CREATE FUNCTION force_tree() RETURNS OPAQUE AS '
DECLARE
	chan_chk record;
BEGIN
	SELECT COUNT(*) INTO chan_chk FROM subchannel WHERE (channel=NEW.subchannel) OR (subchannel=NEW.channel);
	IF (chan_chk.count > 0) THEN
		RAISE EXCEPTION ''Cannot create multi-level tree or cycles in this table'';
	END IF;

	RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TABLE subchannel (
	channel text NOT NULL,
	subchannel text NOT NULL,
	PRIMARY KEY (channel,subchannel),
	CONSTRAINT subchannel_channel_fkey FOREIGN KEY (channel) REFERENCES channels(name)
		MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT subchannel_subchannel_fkey FOREIGN KEY (subchannel) REFERENCES channels(name)
		MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TRIGGER force_subchannel_hier BEFORE INSERT OR UPDATE ON subchannel
FOR EACH ROW EXECUTE PROCEDURE force_tree();

CREATE TABLE channel_pkgs (
	channel text NOT NULL,
	pkg_id int4 NOT NULL,
	PRIMARY KEY (channel,pkg_id),
	CONSTRAINT chan_pkgs_channel_fkey FOREIGN KEY (channel) REFERENCES channels(name)
		MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT chan_pkgs_pkg_id_fkey FOREIGN KEY (pkg_id) REFERENCES packages(package_id)
		MATCH FULL ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE dependencies (
	pkg_id int4 NOT NULL,
	dep_name text NOT NULL,
	dep_version text NULL,
	dep_flags int4 NULL,
	PRIMARY KEY (pkg_id,dep_name),
	CONSTRAINT deps_packages_fkey FOREIGN KEY (pkg_id) REFERENCES packages(package_id) 
		MATCH FULL ON DELETE CASCADE ON UPDATE RESTRICT
);

CREATE TABLE provides (
	pkg_id int4 NOT NULL,
	prov_name text NOT NULL,
	prov_version text NULL,
	prov_flags int4 NULL,
	PRIMARY KEY (pkg_id,prov_name),
	CONSTRAINT provs_packages_fkey FOREIGN KEY (pkg_id) REFERENCES packages(package_id) 
		MATCH FULL ON DELETE CASCADE ON UPDATE RESTRICT
);

CREATE TABLE obsoletes (
	pkg_id int4 NOT NULL,
	obs_name text NOT NULL,
	obs_version text NULL,
	PRIMARY KEY (pkg_id,obs_name),
	CONSTRAINT obs_packages_fkey FOREIGN KEY (pkg_id) REFERENCES packages(package_id) 
		MATCH FULL ON DELETE CASCADE ON UPDATE RESTRICT
);

CREATE TABLE conflicts (
	pkg_id int4 NOT NULL,
	con_name text NOT NULL,
	con_version text NULL,
	PRIMARY KEY (pkg_id,con_name),
	CONSTRAINT con_packages_fkey FOREIGN KEY (pkg_id) REFERENCES packages(package_id) 
		MATCH FULL ON DELETE CASCADE ON UPDATE RESTRICT
);

CREATE TABLE dep_resolve (
	dep_pkg int4 NOT NULL,
	dep_name text NOT NULL,
	prov_pkg int4 NOT NULL,
	prov_name text NOT NULL,
	pkg_name text NOT NULL,
	PRIMARY KEY (dep_pkg,dep_name,prov_pkg,prov_name),
	CONSTRAINT res_dependencies_fkey FOREIGN KEY (dep_pkg,dep_name) 
		REFERENCES dependencies(pkg_id,dep_name) MATCH FULL ON DELETE CASCADE 
		ON UPDATE RESTRICT,
	CONSTRAINT res_provides_fkey FOREIGN KEY (prov_pkg,prov_name)
		REFERENCES provides(pkg_id,prov_name) MATCH FULL ON DELETE CASCADE
		ON UPDATE RESTRICT
);