/*

 Records And Services Optimizer - barter trade optimization
 Copyright (C) 2007 Brian Edward Croner
 
 This program is free software; you can redistribute it and/or
 modify it under the terms of the GNU General Public License
 as published by the Free Software Foundation; either version 3
 of the License, or (at your option) any later version.
 
 This program is distributed in the hope that it will be useful,
 but WITHOUT ANY WARRANTY; without even the implied warranty of
 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 GNU General Public License for more details.
 
 You should have received a copy of the GNU General Public License
 along with this program; if not, write to the Free Software
 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301,  USA.
 
*/

DROP TABLE IF EXISTS trader CASCADE ;
DROP INDEX IF EXISTS trader_name_ix ;
DROP TABLE IF EXISTS contract CASCADE ;
DROP INDEX IF EXISTS contract_title_ix ;
DROP TABLE IF EXISTS voucher CASCADE ;
DROP INDEX IF EXISTS voucher_trader_ix ;
DROP INDEX IF EXISTS voucher_title_ix ;
DROP TABLE IF EXISTS trade_offer CASCADE ;
DROP INDEX IF EXISTS trade_offer_offered_ix ;
DROP INDEX IF EXISTS trade_offer_wanted_ix ;

SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';


--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE PROCEDURAL LANGUAGE plpgsql;


SET search_path = public, pg_catalog;

--
-- Name: dat2doc(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dat2doc(parm integer) RETURNS character varying
    AS $$

DECLARE

	dat_row dat%ROWTYPE ;
	maxv integer ;


BEGIN


	maxv := 256 ;

	SELECT * INTO dat_row FROM dat WHERE i = parm ;
	IF dat_row.m IS NULL AND dat_row.l IS NULL THEN
		return '' ;
	ELSIF dat_row.l IS NULL AND dat_row.m < maxv THEN
		return chr ( dat_row.m ) ;
	ELSIF dat_row.l IS NULL AND dat_row.m >= maxv THEN
		return dat2doc(dat_row.m) ;
	ELSIF dat_row.m IS NULL AND dat_row.l < maxv THEN
		return chr ( dat_row.l ) ;
	ELSIF dat_row.m IS NULL AND dat_row.l >= maxv THEN
		return dat2doc(dat_row.l) ;
	ELSIF dat_row.m < maxv AND dat_row.l < maxv THEN
		return chr ( dat_row.m ) || chr ( dat_row.l ) ;
	ELSE
		return dat2doc(dat_row.m) || dat2doc(dat_row.l) ;
	END IF ;

END;
$$
    LANGUAGE plpgsql;


ALTER FUNCTION public.dat2doc(parm integer) OWNER TO postgres;

--
-- Name: datins(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION datins(max_high integer) RETURNS integer
    AS $$

DECLARE

	l_parm INTEGER ;
	m_parm INTEGER ;
	high   INTEGER ;

BEGIN

	INSERT INTO dat ( m , i ) VALUES ( 0 , 0 ) ;

	high := 1 ;

	while high < max_high LOOP

		INSERT INTO dat ( m , i) SELECT high, MAX(i) + 1 FROM dat ;

		l_parm := 0 ;

		RAISE NOTICE 'l_parm: % ; high: %' , l_parm , high ;

	high := high + 1;

	END LOOP ;

	INSERT INTO dat ( i ) SELECT MAX(i)+1 from dat ;

	return 1;

END;
$$
    LANGUAGE plpgsql;


ALTER FUNCTION public.datins(max_high integer) OWNER TO postgres;

--
-- Name: arrbin2dat(integer[], integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION arrbin2dat(parm integer[], low integer, high integer) RETURNS integer
    AS $$

DECLARE

	len INTEGER ;
	m INTEGER ;
	l INTEGER ;

BEGINc

	len := high - low + 1 ;

	IF len > 2 THEN
		m := arrbin2dat(parm,low,low+CAST(round(len/2) AS INTEGER)-1) ;
		l := arrbin2dat(parm,low+CAST(round(len/2) AS INTEGER),high) ;
	ELSE

		m := parm [ low  ] ;
		l := parm [ high ] ;

	END IF;

	RETURN ml2i_dat(m , l ) ;

END;
$$
    LANGUAGE plpgsql;


ALTER FUNCTION public.arrbin2dat(parm integer[], low integer, high integer) OWNER TO postgres;

--
-- Name: cdat2doc(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION cdat2doc(d integer) RETURNS integer[]
    AS $$

DECLARE

	cdat_row RECORD ;

	cur_arr INTEGER [] ;

BEGIN

	FOR cdat_row IN SELECT cdat.dat AS dat FROM cdat WHERE cdat.i IN ( SELECT cdat.i AS cdati FROM cdat ) ORDER BY cdat.i LOOP
		cur_arr := cur_arr || b2inta (cdat_row.dat) ;
	END LOOP;

	return cur_arr ;

END;
$$
    LANGUAGE plpgsql;


ALTER FUNCTION public.cdat2doc(d integer) OWNER TO postgres;

--
-- Name: doc2dat(character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION doc2dat(parm character varying) RETURNS text
    AS $$

DECLARE

	dat_row dat%ROWTYPE ;

	mlen INTEGER ;

BEGIN

	mlen := CAST(ROUND(power(2.0 , CEIL(LOG(2.0,LENGTH(parm))) )) AS INTEGER) ;

	return docbin2dat(rpad(parm,mlen,chr(0)),mlen) ;

END;
$$
    LANGUAGE plpgsql;


ALTER FUNCTION public.doc2dat(parm character varying) OWNER TO postgres;

--
-- Name: doc2datix(character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION doc2datix(parm character varying) RETURNS integer
    AS $$

DECLARE

	mlen INTEGER ;

BEGIN

	mlen := CAST(ROUND(power(2.0 , CEIL(LOG(2.0,LENGTH(parm))) )) AS INTEGER) ;

	return docbin2datix(rpad(parm,mlen,chr(0)),mlen) ;

END;
$$
    LANGUAGE plpgsql;


ALTER FUNCTION public.doc2datix(parm character varying) OWNER TO postgres;

--
-- Name: docbin2dat(text, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION docbin2dat(parm text, len integer) RETURNS integer
    AS $$

DECLARE

	m INTEGER ;
	l INTEGER ;

BEGIN

	IF len > 2 THEN
		m := docbin2dat(substr(parm,0,CAST(round(len/2) AS INTEGER))) ;
		l := docbin2dat(substr(parm,CAST(round(len/2) AS INTEGER),
			CAST(round(len/2) AS INTEGER))) ;
	ELSE

		m := ascii(substr(parm || repeat(chr(0),2) ,0,1));
		l := ascii(substr(parm || repeat(chr(0),2) ,1,1));

	END IF;

	RETURN ml2i_dat(m , l ) ;

END;
$$
    LANGUAGE plpgsql;


ALTER FUNCTION public.docbin2dat(parm text, len integer) OWNER TO postgres;

--
-- Name: docbin2datix(text, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION docbin2datix(parm text, len integer) RETURNS integer
    AS $$

DECLARE

	m INTEGER ;
	l INTEGER ;

BEGIN

	IF len > 2 THEN
		m := docbin2datix(substr(parm,0,CAST(round(len/2) AS INTEGER))) ;
		l := docbin2datix(substr(parm,CAST(round(len/2) AS INTEGER),
			CAST(round(len/2) AS INTEGER))) ;
	ELSE

		m := ascii(substr(parm || repeat(chr(0),2) ,0,1));
		l := ascii(substr(parm || repeat(chr(0),2) ,1,1));

	END IF;

	RETURN ml2i_datix(m , l ) ;

END;
$$
    LANGUAGE plpgsql;


ALTER FUNCTION public.docbin2datix(parm text, len integer) OWNER TO postgres;

--
-- Name: ml2i_dat(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ml2i_dat(m_parm integer, l_parm integer) RETURNS integer
    AS $$

DECLARE

	dat_row dat%ROWTYPE ;

BEGIN

	SELECT * INTO dat_row FROM dat WHERE dat.m = m_parm AND dat.l = l_parm ;
	return dat_row.i ;

END;
$$
    LANGUAGE plpgsql;


ALTER FUNCTION public.ml2i_dat(m_parm integer, l_parm integer) OWNER TO postgres;

--
-- Name: ml2i_datix(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ml2i_datix(m_parm integer, l_parm integer) RETURNS integer
    AS $$

DECLARE

	dat_row dat%ROWTYPE ;

BEGIN

	SELECT * INTO dat_row FROM dat WHERE dat.m = m_parm AND dat.l = l_parm ;

	IF NOT FOUND THEN
		INSERT INTO dat(m, l, i) SELECT m_parm , l_parm , MAX(i)+1 from dat ;
		SELECT * INTO dat_row FROM dat WHERE dat.m = m_parm AND dat.l = l_parm ;
	END IF;

	return dat_row.i ;

END;
$$
    LANGUAGE plpgsql;


ALTER FUNCTION public.ml2i_datix(m_parm integer, l_parm integer) OWNER TO postgres;

--
-- Name: perfect_square(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION perfect_square(parm integer) RETURNS boolean
    AS $$

DECLARE

	v INTEGER ;

	ret_val BOOLEAN ;

BEGIN

	ret_val := FALSE ;

	v := 1 ;

	WHILE v < parm LOOP

		v := v * 2 ;

	END LOOP ;

	IF v = parm THEN
		ret_val := TRUE ;
	END IF ;

	return ret_val ;

END;
$$
    LANGUAGE plpgsql;


ALTER FUNCTION public.perfect_square(parm integer) OWNER TO postgres;

--
-- Name: plpgsql_call_handler(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
    AS '$libdir/plpgsql', 'plpgsql_call_handler'
    LANGUAGE c;


ALTER FUNCTION public.plpgsql_call_handler() OWNER TO postgres;

--
-- Name: plpgsql_validator(oid); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION plpgsql_validator(oid) RETURNS void
    AS '$libdir/plpgsql', 'plpgsql_validator'
    LANGUAGE c;


ALTER FUNCTION public.plpgsql_validator(oid) OWNER TO postgres;

--
-- Name: pnp_datidx(character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION pnp_datidx(parm character varying) RETURNS integer
    AS $$

DECLARE

	growth INTEGER[] ;

	i INTEGER ;
	j INTEGER ;
	k INTEGER ;
	v INTEGER ;
	w INTEGER ;
	x INTEGER ;

	m_parm INTEGER ;
	l_parm INTEGER ;

	mlog INTEGER ;
	tlog INTEGER ;
	mlen INTEGER ;

	tmp_arr INTEGER[] ;
	cur_arr INTEGER[] ;
	blank_a INTEGER[] ;

BEGIN

	mlen := CAST(ROUND(power(2.0 , CEIL(LOG(2.0,LENGTH(parm))) )) AS INTEGER) ;
	mlog := CAST(ROUND(FLOOR(LOG(2.0,mlen))) AS INTEGER) ;

	i := 1 ;

	WHILE i <= length(parm) LOOP
		cur_arr := array_append(cur_arr , ascii(substr(parm,i,1))) ;
		i := i + 1 ;
	END LOOP ;

	FOR i IN array_upper(cur_arr,1)+1..mlen LOOP
		cur_arr := array_append (cur_arr , 0 ) ;
	END LOOP ;

	FOR i IN 1..mlog LOOP

		tmp_arr := array_cat ( cur_arr , blank_a ) ;
		cur_arr := array_cat ( blank_a , blank_a ) ;

		tlog := CAST (FLOOR(array_upper(tmp_arr,1) / 2) AS INTEGER) ;

		FOR j IN 1..tlog LOOP
			k := ( j - 1 ) * 2 + 1 ;
			v := tmp_arr [ k + 0 ] ;
			w := tmp_arr [ k + 1 ] ;
			x := ml2i_datix ( v , w ) ;
			cur_arr := array_append ( cur_arr , x ) ;
		END LOOP ;
	END LOOP ;

	RETURN cur_arr [ 1 ] ;
END;
$$
    LANGUAGE plpgsql;


ALTER FUNCTION public.pnp_datidx(parm character varying) OWNER TO postgres;

--
-- Name: datcache(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION datcache(parm integer) RETURNS integer
    AS $$

DECLARE

	ident INTEGER ;

	cdat_row RECORD ;  -- cache dat row
	dat_row  RECORD ;  -- dat row reader

	blank_a INTEGER [] ;
	cur_arr INTEGER [] ;

	cj INTEGER  ;   -- intra-frame datum counter
	ck INTEGER  ;   -- post-intra-frame fill datum counter
	len INTEGER ;   -- length determined of frame

	mlen INTEGER ;  -- maximum length
	mlog INTEGER ;  -- maximum length log 2

	clen INTEGER ;  -- current length in focus

	itr INTEGER ; -- iteration through codec arrays
	pit INTEGER ; -- iteration within codec arrays

	j INTEGER ;  -- generic iterator
	k INTEGER ;  -- generic iterator

	lval INTEGER ; -- l-value from dat
	mval INTEGER ; -- m-value from dat
	ival INTEGER ; -- i-value yield from dat_ml2i_ix

	pivot INTEGER ; -- iteration focus within codec arrays

BEGIN

	-- get the null-null identity

	SELECT * INTO dat_row FROM dat WHERE m IS NULL AND l IS NULL ;
	ident := dat_row.i ;

	-- create the encode table

	CREATE TEMPORARY TABLE dat_cur (
		iteration INTEGER ,
		pivot_val INTEGER ,
		dat       INTEGER ,
		CONSTRAINT dat_cur_pk PRIMARY KEY (iteration,pivot_val)
	) ;


	-- read into the temporary table from cdat (dat cache)

	cj := 0 ;

	FOR cdat_row IN SELECT cdat.dat AS dat FROM cdat WHERE cdat.i IN ( SELECT cdat.i AS cdati FROM cdat ) ORDER BY cdat.i LOOP
		cur_arr := b2inta (cdat_row.dat) ;
		for j in 1..array_upper (cur_arr,1) LOOP
			INSERT INTO dat_cur (iteration, pivot_val, dat) VALUES (0, cj, cur_arr [j] ) ;
			cj := cj + 1 ;
		END LOOP ;
	END LOOP;

	len := cj ;

	-- fill in the rest with null-null ident

	mlen := CAST(ROUND(power(2.0 , CEIL(LOG(2.0,len)) )) AS INTEGER) ;
	mlog := CAST(ROUND(FLOOR(LOG(2.0,mlen))) AS INTEGER) ;

	ck := cj ;
	for k in len+1..mlen LOOP
		INSERT INTO dat_cur (iteration, pivot_val, dat) VALUES (0, ck, ident ) ;
		ck := ck + 1 ;
	END LOOP ;

	clen := mlen ;

	for itr in 1..mlog LOOP
		clen := clen / 2 ;
		for pit in 1..clen LOOP
			j := ( pit - 1 ) * 2 ;
			k := j + 1 ;
			SELECT INTO lval dat FROM dat_cur WHERE iteration = itr - 1 AND pivot_val = j ;
			SELECT INTO mval dat FROM dat_cur WHERE iteration = itr - 1 AND pivot_val = k ;
			ival := ml2i_datix ( mval , lval ) ;
			INSERT INTO dat_cur (iteration, pivot_val, dat) VALUES ( itr , pit - 1 , ival ) ;
		END LOOP ;
	END LOOP ;

	SELECT INTO ival dat FROM dat_cur WHERE iteration = mlog ;

	drop table dat_cur ;

	return ival ;

END ;
$$
    LANGUAGE plpgsql;


ALTER FUNCTION public.datcache(parm integer) OWNER TO postgres;

--
-- Name: b2inta(bytea); Type: FUNCTION; Schema: public; Owner: postgres
--


CREATE FUNCTION b2inta(dat bytea) RETURNS integer[]
    AS $$

DECLARE

	cur_arr INTEGER [] ;
	i       INTEGER    ;

BEGIN

	i := 0 ;

	WHILE i < octet_length(dat) LOOP
		cur_arr := array_append(cur_arr , get_byte(dat,i)) ;
		i := i + 1 ;
	END LOOP ;


	return cur_arr ;

END;
$$
    LANGUAGE plpgsql;


ALTER FUNCTION public.b2inta(dat bytea) OWNER TO postgres;

--
-- Name: t2inta(character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION t2inta(parm character varying) RETURNS integer[]
    AS $$

DECLARE

	cur_arr INTEGER [] ;
	i       INTEGER    ;

	v       VARCHAR ;
	x       INTEGER ;
	y       INTEGER ;
	z       INTEGER ;

BEGIN

	i := 1 ;

	WHILE i <= length(parm) LOOP
		v := to_hex(ascii(substr(parm,i,1))) ;
		x := ascii(substr(v,1,1));
		y := ascii(substr(v,2,1));
		z := ascii(substr(parm,i,1)) ;
		cur_arr := cur_arr || z ;
		--cur_arr := cur_arr || x || y ;
		i := i + 1 ;
	END LOOP ;

	return cur_arr ;

END;
$$
    LANGUAGE plpgsql;


ALTER FUNCTION public.t2inta(parm character varying) OWNER TO postgres;


--
-- Name: vexpandc(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION vexpandc(parm integer) RETURNS integer[]
    AS $$

DECLARE

	j   INTEGER ;
	k   INTEGER ;

	len INTEGER ;

	m_parm INTEGER ;
	l_parm INTEGER ;

	tmp_arr INTEGER[] ;
	cur_arr INTEGER[] ;
	new_arr INTEGER[] ;
	blank_a INTEGER[] ;

	dat_row RECORD ;

BEGIN

	tmp_arr := parm || blank_a ;
	new_arr := parm || blank_a ;
	cur_arr := blank_a || blank_a ;

	len := array_upper ( tmp_arr , 1 ) ;

	LOOP

		FOR dat_row IN SELECT * FROM dat WHERE m = ANY (tmp_arr) OR l = ANY (tmp_arr) LOOP
			cur_arr := cur_arr || dat_row.i ;
		END LOOP ;

		IF NOT FOUND THEN
			exit ;
		END IF ;

		new_arr := new_arr || cur_arr ;
		tmp_arr := cur_arr || blank_a ;
		cur_arr := blank_a || blank_a ;

	END LOOP ;

	return new_arr ;

END;
$$
    LANGUAGE plpgsql;


ALTER FUNCTION public.vexpandc(parm integer) OWNER TO postgres;


SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: dat; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE dat (
    i integer NOT NULL,
    m integer ,
    l integer
);


ALTER TABLE public.dat OWNER TO postgres;

--
-- Name: cdat; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE cdat (
    i integer NOT NULL,
    d integer NOT NULL,
    dat bytea
);


ALTER TABLE public.cdat OWNER TO postgres;

--
-- Name: d_i_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE d_i_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.d_i_seq OWNER TO postgres;

--
-- Name: d_i_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('d_i_seq', 1, false);


--
-- Name: d; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE d (
    i integer DEFAULT nextval('d_i_seq'::regclass) NOT NULL,
    url bytea,
    host integer,
    dir bytea,
    fname bytea,
    field bytea,
    dt_cache timestamp without time zone
);


ALTER TABLE public.d OWNER TO postgres;

--
-- Name: h_i_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE h_i_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.h_i_seq OWNER TO postgres;

--
-- Name: h_i_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('h_i_seq', 1, false);


--
-- Name: h; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE h (
    i integer DEFAULT nextval('h_i_seq'::regclass) NOT NULL,
    h bytea,
    p bytea
);


ALTER TABLE public.h OWNER TO postgres;

--
-- Name: tdat; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE tdat (
    d integer NOT NULL,
    dat integer NOT NULL,
    f integer NOT NULL,
    l integer
);


ALTER TABLE public.tdat OWNER TO postgres;

--
-- Data for Name: dat; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY dat (i, m, l) FROM stdin;
\.


--
-- Data for Name: cdat; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY cdat (i, d, dat) FROM stdin;
\.


--
-- Data for Name: d; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY d (i, url, host, dir, fname, field, dt_cache) FROM stdin;
\.


--
-- Data for Name: h; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY h (i, h, p) FROM stdin;
\.


--
-- Data for Name: tdat; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY tdat (d, dat, f, l) FROM stdin;
\.


--
-- Name: dat_pk; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY dat
    ADD CONSTRAINT dat_pk PRIMARY KEY (i);


--
-- Name: dat_uq; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY dat
    ADD CONSTRAINT dat_uq UNIQUE (m, l);


--
-- Name: cdat_pk; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY cdat
    ADD CONSTRAINT cdat_pk PRIMARY KEY (i, d);


--
-- Name: d_pk; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY d
    ADD CONSTRAINT d_pk PRIMARY KEY (i);


--
-- Name: d_uq; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY d
    ADD CONSTRAINT d_uq UNIQUE (url, field);


--
-- Name: h_pk; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY h
    ADD CONSTRAINT h_pk PRIMARY KEY (i);


--
-- Name: h_uq; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY h
    ADD CONSTRAINT h_uq UNIQUE (h);


--
-- Name: tdat_pk; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY tdat
    ADD CONSTRAINT tdat_pk PRIMARY KEY (d, dat, f);

--
-- Name: ix_dat_l; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX ix_dat_l ON dat USING btree (l);


--
-- Name: ix_dat_m; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX ix_dat_m ON dat USING btree (m);

--
-- Name: ix_cdat_d; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX ix_cdat_d ON cdat USING btree (d);


--
-- Name: ix_cdat_i; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX ix_cdat_i ON cdat USING btree (i);


--
-- Name: ix_d_url; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX ix_d_url ON d USING btree (url);


--
-- Name: ix_h_h; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX ix_h_h ON h USING btree (h);


--
-- Name: ix_tdat_f; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX ix_tdat_f ON tdat USING btree (f);


--
-- Name: ix_tdat_dat; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX ix_tdat_dat ON tdat USING btree (dat);


--
-- Name: ix_tdat_d; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX ix_tdat_d ON tdat USING btree (d);

--
-- Name: d_host_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY d
    ADD CONSTRAINT d_host_fkey FOREIGN KEY (host) REFERENCES h(i);


--
-- Name: tdat_dat_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY tdat
    ADD CONSTRAINT tdat_dat_fkey FOREIGN KEY (dat) REFERENCES dat(i);

SELECT datins (256) ;


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;

CREATE TABLE trader (

	id    INTEGER NOT NULL ,
	name  BYTEA   NOT NULL ,
	pass  BYTEA   NOT NULL ,
	email BYTEA   NOT NULL ,
	url   BYTEA   NOT NULL ,

	CONSTRAINT trader_pk   PRIMARY KEY ( id    ) ,
	CONSTRAINT trader_n_uq UNIQUE      ( name  ) ,
	CONSTRAINT trader_e_uq UNIQUE      ( email )

) ;

CREATE TABLE contract (

	id    INTEGER NOT NULL ,
	title BYTEA   NOT NULL ,
	terms INTEGER REFERENCES dat ( i ) ,

	CONSTRAINT contract_pk PRIMARY KEY ( id    ) ,
	CONSTRAINT contract_uq UNIQUE      ( title , terms )

) ;

CREATE TABLE voucher (

	id       INTEGER NOT NULL ,
	trader   INTEGER NOT NULL REFERENCES trader ( id ) ,
	title    BYTEA   NOT NULL ,
	terms    INTEGER REFERENCES dat ( i ) ,

	CONSTRAINT voucher_pk PRIMARY KEY ( id , trader )

) ;

CREATE TABLE trade_offer (

	id       INTEGER NOT NULL ,
	offered  INTEGER NOT NULL ,
	wanted   INTEGER NOT NULL ,
	contract INTEGER REFERENCES contract ( id ) ,

	CONSTRAINT trader_voucher_pk PRIMARY KEY ( id ) ,
	CONSTRAINT trader_voucher_uq UNIQUE      ( offered , wanted , contract )

) ;

CREATE INDEX trader_name_ix ON trader USING btree ( name ) ;
CREATE INDEX contract_title_ix ON contract USING btree ( title ) ;
CREATE INDEX trade_offer_offered_ix ON trade_offer USING BTREE ( offered ) ;
CREATE INDEX trade_offer_wanted_ix ON trade_offer USING BTREE ( wanted ) ;
CREATE INDEX voucher_trader_ix ON voucher USING btree ( trader ) ;
CREATE INDEX voucher_title_ix  ON voucher USING btree ( title  ) ;

INSERT INTO h(h) VALUES(E'localhost');
INSERT INTO d(url,host) VALUES ('http://www.recordsandservices.com/',1) ;

INSERT INTO trader ( id , name , pass , email , url ) VALUES
	( 0 , E'recordsandservices' , E'communism' , E'webmaster@recordsandservices.com' , 'www.recordsandservices.com' ) ;
INSERT INTO contract ( id , title, terms ) VALUES ( 0 , E'null contract' , 0 ) ;
INSERT INTO voucher ( id , trader , title , terms ) VALUES ( 0 , 0 , E'null title' , 0 ) ;
INSERT INTO trade_offer ( id , offered , wanted , contract ) VALUES ( 0 , 0 , 0 , 0 ) ;