-- $Header$
\connect - ldm
CREATE SEQUENCE "auction_id_seq" start 67 increment 1 maxvalue 2147483647 minvalue 1  cache 1 ;
SELECT nextval ('"auction_id_seq"');
CREATE SEQUENCE "person_id_seq" start 4 increment 1 maxvalue 2147483647 minvalue 1  cache 1 ;
SELECT nextval ('"person_id_seq"');
CREATE TABLE "person" (
	"id" int4 DEFAULT nextval('person_id_seq'::text) NOT NULL,
	"login" text NOT NULL,
	"password" text NOT NULL,
	"mail" text NOT NULL,
	"firstname" text NOT NULL,
	"lastname" text NOT NULL,
	"gender" character NOT NULL,
	"birthday" timestamp,
	"organisation" text,
	"taxid" text,
	"street" text,
	"zip" text,
	"city" text,
	"country" text,
	"homephone" text,
	"workphone" text,
	"locale" text,
	"currency" character(3) DEFAULT 'EUR' NOT NULL,
	"option" int8,
	"created" timestamp DEFAULT now() NOT NULL,
	"updated" timestamp DEFAULT now() NOT NULL,
	"notify_new_auction" bool DEFAULT 't',
	"notify_question_asked" bool DEFAULT 't',
	"notify_question_answered" bool,
	"notify_auction_renewed" bool,
	"notify_auction_closed" bool DEFAULT 't',
	"notify_auction_posted" bool DEFAULT 't',
	"notify_rating_posted" bool,
	"notify_bid_posted" bool,
	"notify_outbid" bool,
	"description" text,
	"photo" text,
	"hit" int4,
	CONSTRAINT "user_currency" CHECK ((((((((((currency = 'EUR'::bpchar) OR (currency = 'USD'::bpchar)) OR (currency = 'DEM'::bpchar)) OR (currency = 'GBP'::bpchar)) OR (currency = 'FRF'::bpchar)) OR (currency = 'JPY'::bpchar)) OR (currency = 'CAD'::bpchar)) OR (currency = 'AUD'::bpchar)) OR (currency = 'CHF'::bpchar))),
	CONSTRAINT "person_mail" CHECK ((mail ~* '[-.a-z_0-9]+@[-.a-z_0-9]+\\.[a-z]+'::text)),
	PRIMARY KEY ("id")
);
REVOKE ALL on "person" from PUBLIC;
GRANT ALL on "person" to "ldm";
GRANT ALL on "person" to "www-data";
COMMENT ON TABLE "person" IS 'main list of auction system members';
CREATE TABLE "auction" (
	"id" int4 DEFAULT nextval('auction_id_seq'::text) NOT NULL,
	"person_id" int4,
	"startdate" timestamp DEFAULT now() NOT NULL,
	"stopdate" timestamp NOT NULL,
	"description" text NOT NULL,
	"startprice" float8 NOT NULL,
	"reserveprice" float8,
	"category" text NOT NULL,
	"imageurl" text,
	"title" text NOT NULL,
	"quantity" int4 DEFAULT 1,
	"created" timestamp DEFAULT now() NOT NULL,
	"modified" timestamp DEFAULT now() NOT NULL,
	"option" int8,
	"auto_renew" bool,
	"renew_count" int2 DEFAULT 1,
	"private" bool,
	"dutch" bool,
	"accept_visa" bool,
	"accept_amex" bool,
	"accept_cheque" bool,
	"accept_bank_transfer" bool,
	"accept_bank_cheque" bool,
	"ship_international" bool,
	"buyer_pays_shipping" bool,
	"currency" character(3) DEFAULT 'EUR' NOT NULL,
	"hit" int4,
	CONSTRAINT "auction_category" CHECK ((category ~ '^/[-/A-Za-z0-9_]+'::text)),
	CONSTRAINT "auction_quantity" CHECK ((quantity > 0)),
	CONSTRAINT "auction_imageurl" CHECK ((imageurl ~ '^http://'::text)),
	CONSTRAINT "auction_stopdate" CHECK ((stopdate > startdate)),
	CONSTRAINT "auction_reserveprice" CHECK (((reserveprice ISNULL) OR (reserveprice > startprice))),
	CONSTRAINT "auction_currency" CHECK ((((((((((currency = 'EUR'::bpchar) OR (currency = 'USD'::bpchar)) OR (currency = 'DEM'::bpchar)) OR (currency = 'GBP'::bpchar)) OR (currency = 'FRF'::bpchar)) OR (currency = 'JPY'::bpchar)) OR (currency = 'CAD'::bpchar)) OR (currency = 'AUD'::bpchar)) OR (currency = 'CHF'::bpchar))),
	PRIMARY KEY ("id")
);
REVOKE ALL on "auction" from PUBLIC;
GRANT ALL on "auction" to "ldm";
GRANT ALL on "auction" to "www-data";
CREATE TABLE "test" (
	"t" timestamp
);
CREATE TABLE "bid" (
	"person_id" int4 NOT NULL,
	"auction_id" int4 NOT NULL,
	"price" float8 NOT NULL,
	"quantity" int4 DEFAULT 1 NOT NULL,
	"created" timestamp DEFAULT now() NOT NULL,
	"modified" timestamp DEFAULT now() NOT NULL,
	"comment" text,
	PRIMARY KEY ("person_id", "auction_id", "price")
);
REVOKE ALL on "bid" from PUBLIC;
GRANT ALL on "bid" to "ldm";
GRANT ALL on "bid" to "www-data";
CREATE TABLE "autobid" (
	
)
inherits ("bid");
REVOKE ALL on "autobid" from PUBLIC;
GRANT ALL on "autobid" to "ldm";
GRANT ALL on "autobid" to "www-data";
CREATE TABLE "auction_auto" (
	"auto_mileage" int4 NOT NULL,
	"auto_year" int4 NOT NULL,
	"auto_color" text NOT NULL,
	CONSTRAINT "auction_auto_auto_year" CHECK ((float8(auto_year) <= date_part('year'::text, now())))
)
inherits ("auction");
REVOKE ALL on "auction_auto" from PUBLIC;
GRANT ALL on "auction_auto" to "ldm";
GRANT ALL on "auction_auto" to "www-data";
CREATE TABLE "auction_dvd" (
	"dvd_zone" int4 NOT NULL,
	CONSTRAINT "auction_dvd_zone" CHECK ((dvd_zone <= 5))
)
inherits ("auction");
REVOKE ALL on "auction_dvd" from PUBLIC;
GRANT ALL on "auction_dvd" to "ldm";
GRANT ALL on "auction_dvd" to "www-data";
CREATE TABLE "auction_real_estate" (
	"real_estate_surface" int4 NOT NULL,
	"real_estate_room" int4 NOT NULL
)
inherits ("auction");
REVOKE ALL on "auction_real_estate" from PUBLIC;
GRANT ALL on "auction_real_estate" to "ldm";
GRANT ALL on "auction_real_estate" to "www-data";
CREATE TABLE "image" (
	"auction_id" int4 NOT NULL,
	"path" text,
	"description" text,
	"type" text,
	"width" int4,
	"height" int4,
	"obj" oid,
	"created" timestamp DEFAULT now() NOT NULL
);
REVOKE ALL on "image" from PUBLIC;
GRANT ALL on "image" to "ldm";
GRANT ALL on "image" to "www-data";
CREATE TABLE "forum" (
	"comment" text,
	"answer" text,
	"grade" int4 NOT NULL,
	"auction_id" int4 NOT NULL,
	"created" timestamp DEFAULT now() NOT NULL,
	"modified" timestamp DEFAULT now() NOT NULL,
	PRIMARY KEY ("auction_id")
);
REVOKE ALL on "forum" from PUBLIC;
GRANT ALL on "forum" to "ldm";
GRANT ALL on "forum" to "www-data";
CREATE TABLE "faq" (
	"auction_id" int4 NOT NULL,
	"person_id" int4 NOT NULL,
	"question" text NOT NULL,
	"answer" text,
	"created" timestamp DEFAULT now() NOT NULL,
	"modified" timestamp DEFAULT now() NOT NULL
);
REVOKE ALL on "faq" from PUBLIC;
GRANT ALL on "faq" to "ldm";
GRANT ALL on "faq" to "www-data";
CREATE TABLE "a" (
	"owner" text,
	"bider" text,
	"id" int4,
	"startprice" float8,
	"price" float8
);
CREATE TABLE "c" (
	"seller" text,
	"id" int4,
	"startprice" float8,
	"remaining" text,
	"max_bid" float8,
	"bids" int4,
	"incr" float8,
	"buyer" text
);
CREATE TABLE "b" (
	"login" text,
	"id" int4,
	"price" float8,
	"max" float8
);
CREATE TABLE "watch" (
	"person_id" int4 NOT NULL,
	"auction_id" int4 NOT NULL,
	"created" timestamp DEFAULT now() NOT NULL,
	PRIMARY KEY ("person_id", "auction_id")
);
REVOKE ALL on "watch" from PUBLIC;
GRANT ALL on "watch" to "ldm";
GRANT ALL on "watch" to "www-data";
CREATE TABLE "currency" (
	"USD" float4 DEFAULT 1 NOT NULL,
	"FRF" float4,
	"AUD" float4,
	"CAD" float4,
	"EUR" float4,
	"GBP" float4,
	"DEM" float4,
	"JPY" float4,
	"CHF" float4
);
REVOKE ALL on "currency" from PUBLIC;
GRANT ALL on "currency" to "ldm";
GRANT ALL on "currency" to "www-data";
\connect - postgres
CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS '/usr/lib/postgresql/lib/plpgsql.so' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL';
\connect - ldm
CREATE FUNCTION "greater" (float8,float8 ) RETURNS float8 AS 'select case when $1 > $2 then $1 else $2 end' LANGUAGE 'SQL';
CREATE FUNCTION "smaller" (float8,float8 ) RETURNS float8 AS 'select case when $1 < $2 then $1 else $2 end' LANGUAGE 'SQL';
CREATE FUNCTION "incr" (float8,float8 ) RETURNS float8 AS '
SELECT CASE WHEN $1 < dpow(10,int8(log($1))+1)/2 
	THEN (dpow(10,int8(log($1)))) * $2 
	ELSE (dpow(10,int8(log($1))+1)/2) * $2 
END
' LANGUAGE 'SQL';
CREATE FUNCTION "max_bid" (int4 ) RETURNS float8 AS '
SELECT max(bid.price) FROM bid WHERE bid.auction_id = $1
' LANGUAGE 'SQL';
CREATE FUNCTION "num_bid" (int4 ) RETURNS int4 AS '
SELECT count(bid.price) FROM bid WHERE bid.auction_id = $1
' LANGUAGE 'SQL';
CREATE FUNCTION "max_price" (int4) RETURNS float8 AS '
SELECT CASE WHEN num_bid($1) = 0 
	THEN (SELECT startprice FROM auction* WHERE id = $1) 
	ELSE max_bid($1) 
END
' LANGUAGE 'SQL';
CREATE FUNCTION "next_incr" (int4 ) RETURNS float8 AS '
SELECT incr(max_price($1),0.05)
' LANGUAGE 'SQL';
CREATE FUNCTION "next_incr" (int4,float8 ) RETURNS float8 AS '
SELECT incr(max_price($1),$2)
' LANGUAGE 'SQL';
CREATE FUNCTION "seller" (int4 ) RETURNS int4 AS '
SELECT person_id FROM auction* a WHERE a.id = $1
' LANGUAGE 'SQL';
CREATE FUNCTION "buyer" (int4 ) RETURNS int4 AS '
SELECT person_id FROM bid WHERE auction_id = $1 AND price = max_bid($1)
' LANGUAGE 'SQL';
CREATE FUNCTION "adjust" (float8 ) RETURNS float8 AS '
SELECT $1 - ($1 % incr($1,0.05))::float8
' LANGUAGE 'SQL';
CREATE FUNCTION "next_price" (int4 ) RETURNS float8 AS '
SELECT incr(max_price($1),0.05) + adjust(max_price($1))
' LANGUAGE 'SQL';
CREATE FUNCTION "auction_status" (int4 ) RETURNS text AS '
SELECT CASE WHEN (SELECT stopdate FROM auction* WHERE id = $1) < now() 
	THEN 
		CASE WHEN num_bid($1) = 0
			THEN ''EXPIRED''::text
			ELSE ''CLOSED''::text
		END 
	ELSE 
		''CURRENT''::text
	END 
' LANGUAGE 'SQL';
CREATE FUNCTION "bid_status" (int4,int4 ) RETURNS text AS '
SELECT CASE WHEN (SELECT stopdate FROM auction* WHERE id = $1) < now() 
	THEN 
		CASE WHEN buyer($1) = $2 
			THEN ''won'' 
			ELSE ''lost to ''||buyer($1) 
		END 
	ELSE 
		CASE WHEN buyer($1) = $2 
			THEN ''current high bid'' 
			ELSE ''overbid by ''||buyer($1) 
		END 
	END 
' LANGUAGE 'SQL';
CREATE FUNCTION "bid_control" ( ) RETURNS opaque AS '/usr/local/auction/database/bid_control.so' LANGUAGE 'C';
CREATE FUNCTION "add_user_password" ( ) RETURNS opaque AS '/usr/local/auction/database/bid_control.so' LANGUAGE 'C';
CREATE FUNCTION "get_currency" (int4 ) RETURNS bpchar AS 'SELECT currency FROM auction* WHERE auction.id = $1
' LANGUAGE 'SQL';
\connect - ldm
COPY "person" FROM stdin;
1	mito	miW01nH8Wq.QY	ldm@apartia.com	oilkj	kljlkjl	m	1900-01-01 00:00:00	\N	\N	dfsdfs	sdfsdf	dsfsdfdsf	FR	\N	\N	de_DE	EUR	\N	2000-08-05 17:08:06+02	2000-08-05 17:08:06+02	t	t	\N	\N	t	t	\N	\N	\N	\N	\N	\N
2	vindex	vieRfHg3nEs2A	ldm@apartia.com	jjkjkljkl	lkjlkj	m	1900-01-01 00:00:00	\N	\N	lkjlkjlk\	l	jlkjkl	lkjl	gr	\N	\N	\N	EUR	\N	2000-08-10 15:06:28+02	2000-08-10 15:06:28+02	t	t	\N	\N	t	t	\N	\N	\N	\N	\N	\N
3	papy	paYlk0.OzDdQU	ldm@apartia.com	sdlfksdldsf	slkdfsd	m	1900-01-01 00:00:00	\N	\N	sdfds\	kjj\		sdfsdf	sdf	ww	\N	\N	\N	EUR	\N	2000-08-10 15:16:49+02	2000-08-10 15:16:49+02	t	t	\N	\N	t	t	\N	\N	\N	\N	\N	\N
4	cunctator	cu.YaLV54Ye3E	ldm@apartia.com	lkjlk	lkjlk	m	1900-01-01 00:00:00	\N	\N	dfdfs	oipoi	vdffdfs	ff	\N	\N	\N	EUR	\N	2000-08-10 15:18:21+02	2000-08-10 15:18:21+02	t	t	\N	\N	t	t	\N	\N	\N	\N	\N	\N
\.
COPY "auction" FROM stdin;
\.
COPY "test" FROM stdin;
2000-06-12 00:00:00+02
2000-06-12 02:00:00+02
2000-06-12 02:00:00+02
2000-06-12 00:00:00+02
2000-06-12 00:00:00+02
2000-06-30 00:00:00+02
\.
COPY "bid" FROM stdin;
\.
COPY "autobid" FROM stdin;
\.
COPY "auction_auto" FROM stdin;
67	1	2000-08-06 17:46:12+02	2000-09-06 15:00:00+02	St-1100 cm3, best motorcycle ever...	32322	\N	/Item/Automotive/Motorcycle	\N	Honda Pan-European	1	2000-08-06 17:46:12+02	2000-08-06 17:46:12+02	\N	t	1	\N	\N	\N	\N	t	t	\N	t	t	USD	\N	1111	1999	klkjlsfd
\.
COPY "auction_dvd" FROM stdin;
\.
COPY "auction_real_estate" FROM stdin;
65	1	2000-08-05 19:23:41+02	2000-09-05 17:00:00+02	dfsdf   sd   fsd   f   sd   f   sdf      	3342	\N	/Item/Real_Estate/House	\N	sdfsdf	1	2000-08-05 19:23:41+02	2000-08-05 19:23:41+02	\N	\N	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	EUR	\N	432	34
\.
COPY "image" FROM stdin;
51	/usr/local/auction/www-user/vindex/noel.gif	\N	\N	\N	\N	\N	2000-06-28 17:51:12+02
51	/usr/local/auction/www-user/vindex/aparima.gif	\N	\N	\N	\N	\N	2000-06-28 17:51:12+02
51	/usr/local/auction/www-user/vindex/gfx_by_gimp.gif	\N	\N	\N	\N	\N	2000-06-28 17:51:12+02
54	/usr/local/auction/www-user/vindex/horloge_breguet.jpg	\N	\N	540	360	\N	2000-06-28 18:04:57+02
54	/usr/local/auction/www-user/vindex/gfx_by_gimp.gif	\N	\N	-1	-1	\N	2000-06-28 18:04:57+02
52	/usr/local/auction/www-user/vindex/dscf0005.jpg	\N	\N	-1	-1	\N	2000-06-28 17:59:32+02
55	/usr/local/auction/www-user/mito/noframes.gif	\N	\N	-1	-1	\N	2000-06-29 11:04:01+02
55	/usr/local/auction/www-user/mito/horloge_breguet.jpg	\N	\N	540	360	\N	2000-06-29 11:04:01+02
56	/usr/local/auction/www-user/cunctator/meuble_chinois.jpg	\N	\N	540	360	\N	2000-06-29 11:31:10+02
56	/usr/local/auction/www-user/cunctator/noframes.gif	\N	\N	-1	-1	\N	2000-06-29 11:31:10+02
57	/usr/local/auction/www-user/cunctator/meuble_chinois.jpg	\N	\N	540	360	\N	2000-06-29 11:35:15+02
57	/usr/local/auction/www-user/cunctator/noframes.gif	\N	\N	100	100	\N	2000-06-29 11:35:15+02
58	/usr/local/auction/www-user/cunctator/bjc3000.jpg	\N	\N	165	142	\N	2000-06-29 12:25:21+02
60	/usr/local/auction/www-user/mito/gilbert_daniellou_pilote.jpg	\N	\N	1280	1024	\N	2000-07-03 19:47:45+02
61	/usr/local/auction/www-user/ddd/st1100.jpg	\N	\N	705	536	\N	2000-07-04 16:39:56+02
62	/usr/local/auction/www-user/mito/st1100.jpg	\N	\N	705	536	\N	2000-07-10 12:12:11+02
67	/usr/local/auction/www-user/mito/st1100.jpg	\N	\N	705	536	\N	2000-08-06 17:46:12+02
\.
COPY "forum" FROM stdin;
\.
COPY "faq" FROM stdin;
\.
COPY "a" FROM stdin;
\.
COPY "c" FROM stdin;
\.
COPY "b" FROM stdin;
\.
COPY "watch" FROM stdin;
\.
COPY "currency" FROM stdin;
\.
CREATE UNIQUE INDEX "person_login_key" on "person" using btree ( "login" "text_ops" );
CREATE UNIQUE INDEX "person_id_key" on "person" using btree ( "id" "int4_ops" );
CREATE TRIGGER "ubefore" BEFORE INSERT ON "person"  FOR EACH ROW EXECUTE PROCEDURE "add_user_password" ();
CREATE TRIGGER "uafter" AFTER INSERT ON "person"  FOR EACH ROW EXECUTE PROCEDURE "add_user_password" ();
CREATE TRIGGER "tafter" AFTER INSERT ON "bid"  FOR EACH ROW EXECUTE PROCEDURE "bid_control" ();
CREATE TRIGGER "tbefore" BEFORE INSERT ON "bid"  FOR EACH ROW EXECUTE PROCEDURE "bid_control" ();