--Module: poll
-- hopp-poll-create.sql Copyright (C) 2001 Sandino Araico Sánchez
/* --LICENSE IN US ENGLISH----------------------------------------------------- 
 * "THE BEER-WARE LICENSE" (Revision 42-sandino-3): 
 * Sandino Araico Sánchez <sandino@sandino.net> wrote this file. 
 * As long as you retain this notice you can do whatever you want with this 
 * software. If we meet some day, and you think this stuff is worth it, you can 
 * buy me a beer in return. 
 * Sandino Araico Sánchez 
 * --END LICENSE---------------------------------------------------------------
 * --LICENCIA EN ESPAÑOL MEXICANO----------------------------------------------
 * "LA LICENCIA BEER-WARE" (traducción al español de la revisión 42-sandino-3):
 * Sandino Araico Sánchez <sandino@sandino.net> escribió éste archivo.
 * Siempre y cuando usted retenga éste aviso (en español y en inglés) usted
 * puede hacer cualquier cosa con éste software. Si algún día nos encontramos y 
 * usted piensa que éste software vale la pena, usted puede invitarme una 
 * cerveza en recompensa.
 * Sandino Araico Sánchez
 * --FIN DE LICENCIA-----------------------------------------------------------
 */
 
-- Changelog: 
-- 2001-09-18 Sandino Araico Sánchez - poll tables creation sql script

create sequence s_poll; 
create table h_poll (
	pll_key	int2	constraint pk_pll_key primary key,
	pll_name	varchar(250) constraint nn_pll_name not null,
	pll_type	char	constraint nn_pll_type not null,
	pll_active	char constraint nn_pll_active not null
);
create index i_pll_active on h_poll(pll_active);
grant all on s_poll to nobody;
grant all on h_poll to nobody;

create sequence s_poll_det;
create table h_poll_det(
	pll_det_key	int2 constraint pk_pll_det_key primary key,
	pll_det_poll	int2 constraint nn_pll_det_poll not null,
	pll_det_option	varchar(500) constraint nn_pll_det_option not null,
	pll_det_order	int2
);
alter table h_poll_det add constraint fk_pll_det_poll foreign key (pll_det_poll) references h_poll(pll_key); 
create index i_pll_det_poll on h_poll_det(pll_det_poll);
create index i_pll_det_order on h_poll_det(pll_det_order);
grant all on s_poll_det to nobody;
grant all on h_poll_det to nobody;

create sequence s_poll_cookie;
create table h_poll_cookie(
	pll_coo_key	int4 constraint pk_pll_coo_key primary key,
	pll_coo_poll	int2 constraint nn_pll_coo_poll not null,
	pll_coo_cookie	char(32) constraint nn_pll_coo_cookie not null,
	pll_coo_ip	char(15) constraint nn_pll_coo_ip not null,
	pll_coo_date	timestamp constraint nn_pll_coo_date not null
);
alter table h_poll_cookie add constraint fk_pll_coo_poll foreign key(pll_coo_poll) references h_poll(pll_key);
create index i_pll_coo_poll on h_poll_cookie(pll_coo_poll);
create index i_pll_coo_ip on h_poll_cookie(pll_coo_ip);
create index i_pll_coo_cookie on h_poll_cookie(pll_coo_cookie);
create index i_pll_coo_date on h_poll_cookie(pll_coo_date);
grant all on s_poll_cookie to nobody;
grant all on h_poll_cookie to nobody;

create sequence s_poll_vot;
create table h_poll_vot(
	pll_vot_key	int4 constraint pk_pll_vot_key primary key,
	pll_vot_det	int2 constraint nn_pll_vot_det not null,
	pll_vot_cookie	char(32) constraint nn_pll_vot_cookie not null,
	pll_vot_date	timestamp constraint nn_pll_vot_date not null,
	pll_vot_value	int2
);	
alter table h_poll_vot add constraint fk_pll_vot_det foreign key(pll_vot_det) references h_poll_det(pll_det_key);
--alter table h_poll_vot add constraint fk_pll_vot_cookie foreign key(pll_vot_cookie) references h_poll_cookie(pll_coo_cookie);
create index i_pll_vot_det on h_poll_vot(pll_vot_det);
create index i_pll_vot_cookie on h_poll_vot(pll_vot_cookie);
create index i_pll_vot_date on h_poll_vot(pll_vot_date);
grant all on s_poll_vot to nobody;
grant all on h_poll_vot to nobody;

create sequence s_poll_comment;
create table h_poll_comment(
	pll_com_key	int4 constraint pk_pll_com_key primary key,
	pll_com_poll	int2 constraint nn_pll_com_poll not null,	
	pll_com_cookie	char(32) constraint nn_pll_com_cookie not null,
	pll_com_txt	text constraint nn_pll_com_txt not null
);
alter table h_poll_comment add constraint fk_pll_com_poll foreign key(pll_com_poll) references h_poll(pll_key);
--alter table h_poll_comment add constraint fk_pll_com_cookie foreign key(pll_com_cookie) references h_poll_cookie(pll_coo_cookie);
create index i_pll_com_poll on h_poll_comment(pll_com_poll);
create index i_pll_com_cookie on h_poll_comment(pll_com_cookie);
grant all on s_poll_comment to nobody;
grant all on h_poll_comment to nobody;