CREATE TABLE host ( host_no serial, name varchar(255) not null, ip varchar(32) not null, info varchar(255) default '', online int default 0, primary key (host_no) ) ; CREATE UNIQUE INDEX host_idx ON host USING BTREE (name, ip) ; CREATE TABLE hostgroup ( hostgroup_no serial, name varchar(255) not null, info varchar(255) default '', primary key (hostgroup_no) ) ; CREATE UNIQUE INDEX hostgroup_idx ON hostgroup USING BTREE (name) ; CREATE TABLE hostgroup_member ( host_no bigint REFERENCES host (host_no) ON DELETE CASCADE, hostgroup_no bigint REFERENCES hostgroup (hostgroup_no) ON DELETE CASCADE, primary key(host_no, hostgroup_no) ) ; INSERT INTO hostgroup (name) VALUES ('all') ; INSERT INTO host (name, ip) VALUES ('plume', '127.0.0.1') ; INSERT INTO host (name, ip) VALUES ('plume2', '127.0.0.1') ; INSERT INTO host (name, ip) VALUES ('master', '127.0.0.1') ; INSERT INTO hostgroup_member (host_no, hostgroup_no) VALUES(1,1) ; INSERT INTO hostgroup_member (host_no, hostgroup_no) VALUES(2,1) ; INSERT INTO hostgroup_member (host_no, hostgroup_no) VALUES(3,1) ; -- il faut ajouter un trigger quand on ajoute un host -- -- SELECT count(*) -- FROM hostgroup_member -- INNER JOIN host on hostgroup_member.host_no = host.host_no -- INNER JOIN hostgroup on hostgroup.hostgroup_no = hostgroup_member.hostgroup_no -- WHERE host.name = 'plume' and hostgroup.name = 'grp_prod'; -- -- -- CREATE TABLE job ( job_no serial, id varchar(255) not null, md5 varchar(127) not null, host text not null, date timestamp, comment text, xml text not null, primary key(id, md5) ) ; CREATE UNIQUE INDEX job_no_idx ON job USING BTREE (job_no) ; CREATE UNIQUE INDEX job_md5_idx ON job USING BTREE (md5) ; CREATE TABLE task ( job_no bigint REFERENCES job (job_no) ON DELETE CASCADE, id varchar(255) not null, host varchar(255) not null, xml text not null, primary key (job_no, id) ) ; -- que veut on en sortir... CREATE TABLE task_hist ( task_sid varchar(255), job_no bigint REFERENCES job (job_no) ON DELETE CASCADE, date timestamp, host varchar(255), xml text, PRIMARY KEY (task_sid) ) ; CREATE TABLE task_notify ( task_sid varchar(255) REFERENCES task_hist (task_sid) ON DELETE CASCADE, host varchar(255), date timestamp, primary key(task_sid, host) ) ; -- que veut on en sortir... -- on supprime pas l historique CREATE TABLE job_hist ( job_sid varchar(255), job_no bigint REFERENCES job (job_no) ON DELETE CASCADE, start_date timestamp, end_date timestamp, host varchar(255), xml text , status int, PRIMARY KEY (job_sid) ) ; CREATE INDEX job_hist_start_date_idx ON job_hist USING BTREE (start_date) ; CREATE INDEX job_hist_end_date_idx ON job_hist USING BTREE (end_date) ; -- INSERT INTO task_hist ( task_sid, job_no, date, host) VALUES ('toto', 40, now(), 'plume2') ;