-- init script for pydar2 db drop table pydar2_commands cascade; drop table pydar2_actions cascade; drop table pydar2_actions_files cascade; drop table pydar2_status cascade; drop table pydar2_users cascade; drop table pydar2_rights cascade; drop table pydar2_users_to_rights cascade; drop table pydar2_buildmachines cascade; drop table pydar2_buildmachine_registrations cascade; drop table pydar2_buildmachine_distroarchtags cascade; drop table pydar2_buildmachines_to_rights cascade; drop table pydar2_specrepo cascade; drop table pydar2_specfile cascade; drop table pydar2_specfile_version cascade; drop table pydar2_distroarch cascade; drop table pydar2_specfile_tags cascade; drop table pydar2_targets cascade; drop sequence pydar2_specfile_idseq; drop sequence pydar2_distro_idseq; drop sequence pydar2_specrepo_idseq; drop sequence pydar2_distroarch_idseq; drop sequence pydar2_targets_idseq; drop sequence pydar2_specfile_version_idseq; drop sequence pydar2_target_files_idseq; create sequence pydar2_specfile_idseq; create sequence pydar2_distro_idseq; create sequence pydar2_specrepo_idseq; create sequence pydar2_distroarch_idseq; create sequence pydar2_targets_idseq; create sequence pydar2_specfile_version_idseq; create sequence pydar2_target_files_idseq; create table pydar2_buildmachines ( id VARCHAR(64) NOT NULL UNIQUE, name VARCHAR(255) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL ); create table pydar2_targets ( id integer unique not null default nextval('pydar2_targets_idseq'), name varchar(512) unique ); create table pydar2_distroarch ( id integer unique not null default nextval('pydar2_distroarch_idseq'), name varchar(512) unique not null, distroarchtag varchar(200) unique not null, defines varchar(512) not null, disabled boolean not null default '0' ); create table pydar2_status ( id SMALLINT NOT NULL UNIQUE, name VARCHAR(255) NOT NULL ); create table pydar2_users ( id VARCHAR(64) NOT NULL UNIQUE, name VARCHAR(255) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL ); create table pydar2_specrepo ( id integer unique not null default nextval('pydar2_specrepo_idseq'), name varchar(512) unique not null, rootdirectory varchar(512) not null, type varchar(32) not null, addtimestamp timestamp not null default current_timestamp, deleted bool not null default false ); create table pydar2_specfile ( id integer unique not null default nextval('pydar2_specfile_idseq'), specrepoid integer not null, filename varchar(512) not null, subdir varchar(512) not null, checksum varchar(512) not null, addtimestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); alter table pydar2_specfile add constraint fk_specfile_srid foreign key (specrepoid) references pydar2_specrepo(id); create index pydar2_specfile__id on pydar2_specfile(id); create index pydar2_specfile__name on pydar2_specfile(filename); create index pydar2_specfile__both on pydar2_specfile(id,filename); create table pydar2_target_files ( name varchar(512) not null, targetid integer not null, id integer unique not null default nextval('pydar2_target_files_idseq'), filename varchar(512) not null, checksum varchar(512) not null, version integer not null, distroarchtag varchar(64) not null, addtimestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); alter table pydar2_target_files add constraint fk_targetfiles_targetid foreign key (targetid) references pydar2_targets(id); -- damn alter table pydar2_target_files add constraint fk_targetfiles_version foreign key (version) references pydar2_specfile_version(version); alter table pydar2_target_files add constraint fk_targetfiles_datag foreign key (distroarchtag) references pydar2_distroarch(distroarchtag); create table pydar2_commands ( id BIGSERIAL NOT NULL UNIQUE, addtimestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, commandname VARCHAR(255) NOT NULL, userid VARCHAR(255) NOT NULL, specrepoid INTEGER NOT NULL, specfileid INTEGER NOT NULL, toemail VARCHAR(255) NOT NULL, distroarchtag VARCHAR(255) NOT NULL, priority INTEGER NOT NULL default 0, targetid INTEGER NOT NULL, handled BOOLEAN NOT NULL DEFAULT FALSE, inprogress BOOLEAN NOT NULL DEFAULT FALSE, version INTEGER NOT NULL ); alter table pydar2_commands add constraint fk_commands_userid foreign key (userid) references pydar2_users(id); alter table pydar2_commands add constraint fk_commands_specrepoid foreign key (specrepoid) references pydar2_specrepo(id); alter table pydar2_commands add constraint fk_commands_specfileid foreign key (specfileid) references pydar2_specfile(id); alter table pydar2_commands add constraint fk_commands_distroarchtag foreign key (distroarchtag) references pydar2_distroarch(distroarchtag); alter table pydar2_commands add constraint fk_commands_targetid foreign key (targetid) references pydar2_targets(id); alter table pydar2_commands add constraint fk_commands_versionid foreign key (version) references pydar2_specfile_version(version); create index pydar2_comm_name on pydar2_commands(commandname); create index pydar2_comm_handled on pydar2_commands(handled); create index pydar2_comm_inprogress on pydar2_commands(inprogress); create index pydar2_comm_datag on pydar2_commands(distroarchtag); create index pydar2_comm_two on pydar2_commands(handled,inprogress); create index pydar2_comm_three on pydar2_commands(commandname,handled,inprogress); create table pydar2_actions ( commandid BIGINT NOT NULL, statusid SMALLINT NOT NULL, buildmachineid VARCHAR(64) NOT NULL, addtimestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); alter table pydar2_actions add constraint fk_actions_commandid foreign key (commandid) references pydar2_commands(id); alter table pydar2_actions add constraint fk_actions_statusid foreign key (statusid) references pydar2_status(id); alter table pydar2_actions add constraint fk_actions_bmid foreign key (buildmachineid) references pydar2_buildmachines(id); create table pydar2_actions_files ( id BIGSERIAL NOT NULL UNIQUE, addtimestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, commandid BIGINT NOT NULL, filename VARCHAR(255) NOT NULL ); alter table pydar2_actions add constraint fk_actionsfiles_commandid foreign key (commandid) references pydar2_commands(id); create table pydar2_rights ( id INTEGER NOT NULL UNIQUE, abbrev VARCHAR(255) NOT NULL UNIQUE, description VARCHAR(255) NOT NULL UNIQUE ); create table pydar2_users_to_rights ( userid VARCHAR(64) NOT NULL, rightid INTEGER NOT NULL ); alter table pydar2_users_to_rights add constraint fk_utor_userid foreign key (userid) references pydar2_users(id); alter table pydar2_users_to_rights add constraint fk_utor_rightid foreign key (rightid) references pydar2_rights(id); create table pydar2_buildmachines_to_rights ( buildmachineid VARCHAR(64) NOT NULL, rightid INTEGER NOT NULL ); alter table pydar2_buildmachines_to_rights add constraint fk_bmtor_buildmachineid foreign key (buildmachineid) references pydar2_buildmachines(id); alter table pydar2_buildmachines_to_rights add constraint fk_bmtor_rightid foreign key (rightid) references pydar2_rights(id); create table pydar2_buildmachine_registrations ( id VARCHAR(64) NOT NULL, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); alter table pydar2_buildmachine_registrations add constraint fk_bmreg_id foreign key (id) references pydar2_buildmachines(id); create table pydar2_buildmachine_distroarchtags ( buildmachineid VARCHAR(255) NOT NULL, distroarchtag VARCHAR(255) NOT NULL ); alter table pydar2_buildmachine_distroarchtags add constraint fk_bmdat_bmid foreign key (buildmachineid) references pydar2_buildmachines(id); alter table pydar2_buildmachine_distroarchtags add constraint fk_bmdat_dat foreign key (distroarchtag) references pydar2_distroarch(distroarchtag); create table pydar2_specfile_version ( id INTEGER NOT NULL UNIQUE default nextval('pydar2_specfile_version_idseq'), specfileid integer not null, version integer not null, addtimestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); alter table pydar2_specfile_version add column authoritytag varchar(256); alter table pydar2_specfile_version add column descriptiontag text; alter table pydar2_specfile_version add column summarytag varchar(1024); alter table pydar2_specfile_version add column excludeostag varchar(512); alter table pydar2_specfile_version add column versiontag varchar(256); alter table pydar2_specfile_version add column exclusiveostag varchar(512); alter table pydar2_specfile_version add column grouptag varchar(512); alter table pydar2_specfile_version add column archtag varchar(512); alter table pydar2_specfile_version add column exclusivearchtag varchar(512); alter table pydar2_specfile_version add column nametag varchar(256); alter table pydar2_specfile_version add column releasetag varchar(128); alter table pydar2_specfile_version add column licensetag varchar(256); alter table pydar2_specfile_version add column epochtag varchar(128); alter table pydar2_specfile_version add column urltag varchar(512); alter table pydar2_specfile_version add column excludearchtag varchar(256); alter table pydar2_specfile_version add column changelognametag varchar(512); alter table pydar2_specfile_version add column upstreamtag varchar(512); alter table pydar2_specfile_version add constraint fk_specfileversion_sfid foreign key (specfileid) references pydar2_specfile(id); create index pydar2_specfile_v__sfid on pydar2_specfile_version(specfileid); create index pydar2_specfile_v__scr on pydar2_specfile_version(version); create index pydar2_specfile_v__both on pydar2_specfile_version(specfileid,version); create table pydar2_specfile_tags ( versionid integer not null, distroid integer not null, name varchar(512) not null, value varchar(4000) not null ); alter table pydar2_specfile_tags add constraint fk_sftags_version foreign key (versionid) references pydar2_specfile_version(id); alter table pydar2_specfile_tags add constraint fk_sftags_distroid foreign key (distroid) references pydar2_distroarch(id); create index pydar2_specfile_t__scr on pydar2_specfile_tags(versionid); create index pydar2_specfile_t__did on pydar2_specfile_tags(distroid); create index pydar2_specfile_t__both on pydar2_specfile_tags(versionid,distroid); create index pydar2_specfile_t_three2 on pydar2_specfile_tags(versionid,distroid,name); create index pydar2_specfile_t_three3 on pydar2_specfile_tags(versionid,distroid,lower(name)); create index pydar2_specfile_t_four on pydar2_specfile_tags(versionid,distroid,name,value); create index pydar2_specfile_t_name on pydar2_specfile_tags(name); create index pydar2_specfile_t_value on pydar2_specfile_tags(value); create index pydar2_specfile_t_uname on pydar2_specfile_tags(upper(name)); create index pydar2_specfile_t_u2 on pydar2_specfile_tags(upper(name),versionid); --create VIEW pydar2_specfile_lastversion AS select max(v.version) as latestversion, v.specfileid, s.filename --from pydar2_specfile_version v, pydar2_specfile s where s.id=v.specfileid group by v.specfileid, s.filename; --create view pydar2_specfile_lastversion_and_authority as select distinct --lv.latestversion, lv.specfileid, lv.filename, t.value as authority from --pydar2_specfile_lastversion lv, pydar2_specfile_tags t where --lv.specfileid=t.specfileid and lv.latestversion=t.version and --lower(t.name) like '# authority'; -- old -- insert into pydar2_users (id,name) values ('0','test user'); -- insert into pydar2_buildmachines (id,name) values ('0','test buildmachine'); insert into pydar2_status (id,name) values (0,'command accepted'); insert into pydar2_status (id,name) values (1,'build succeeded'); insert into pydar2_status (id,name) values (2,'build failed'); insert into pydar2_distroarch (distroarchtag, name,defines) values ('fc3-i386','Fedora Core 3 i386',' --define "fc3 1"'); insert into pydar2_distroarch (distroarchtag, name,defines) values ('fc2-i386','Fedora Core 2 i386',' --define "fc2 1"'); insert into pydar2_distroarch (distroarchtag, name,defines) values ('fc1-i386','Fedora Core 1 i386',' --define "fc1 1"'); insert into pydar2_distroarch (distroarchtag, name,defines) values ('rh9-i386','Red Hat 9 i386',' --define "rh9 1"'); insert into pydar2_distroarch (distroarchtag, name,defines) values ('rh8-i386','Red Hat 8 i386',' --define "rh8 1"'); insert into pydar2_distroarch (distroarchtag, name,defines) values ('rh7-i386','Red Hat 7 i386',' --define "rh7 1"'); insert into pydar2_distroarch (distroarchtag, name,defines) values ('rh6-i386','Red Hat 6 i386',' --define "rh6 1"'); insert into pydar2_distroarch (distroarchtag, name,defines) values ('el4-i386','Red Hat Enterprise Linux 4',' --define "el4 1"'); insert into pydar2_distroarch (distroarchtag, name,defines) values ('el3-i386','Red Hat Enterprise Linux 3',' --define "el3 1"'); insert into pydar2_distroarch (distroarchtag, name,defines) values ('el2-i386','Red Hat Enterprise Linux 2',' --define "el2 1"'); insert into pydar2_distroarch (distroarchtag, name,defines) values ('au1.92-sparc', 'Aurora Linux 1.92 sparc', ' --define "fc2 1"'); insert into pydar2_distroarch (distroarchtag, name,defines) values ('au1.91-sparc', 'Aurora Linux 1.91 sparc', ' --define "fc2 1"'); insert into pydar2_distroarch (distroarchtag, name,defines) values ('fc4-i386', 'Fedora Core 4 i386',' --define "fc4 1"'); insert into pydar2_distroarch (distroarchtag, name,defines) values ('fc4-x86_64', 'Fedora Core 4 x86_64',' --define "dist fc4" --define "fc4 1"'); insert into pydar2_distroarch (distroarchtag, name,defines) values ('oss10.0beta4-i586', 'OpenSUSE 10.0 beta 4 i586', ' '); insert into pydar2_distroarch (distroarchtag, name,defines) values ('fc5-i386', 'Fedora Core 5 i386',' --define "dist fc5" --define "fc5 1"'); insert into pydar2_distroarch (distroarchtag, name,defines) values ('oss10.0-i586', 'OpenSUSE 10.0 i586',' --define "dist oss10.0" --define "oss10.0 1"'); insert into pydar2_distroarch (distroarchtag, name,defines) values ('fc5-x86_64', 'Fedora Core 5 x86_64',' --define "dist fc5" --define "fc5 1"'); insert into pydar2_rights (id, abbrev, description) values (0, 'REGISTER_SLAVE', 'register as a slave'); insert into pydar2_rights (id, abbrev, description) values (1, 'SEND_RESULT_FILE_NAME', 'send information about a built rpm (needed for a slave)'); insert into pydar2_rights (id, abbrev, description) values (2, 'SEND_BUILD_RESULT', 'send information about the build: succeeded or not?'); insert into pydar2_rights (id, abbrev, description) values (3, 'ADD_DISTRO_ARCH', 'register distro/arch (needed for a slave)'); insert into pydar2_rights (id, abbrev, description) values (4, 'GET_COMMAND', 'get a command (needed for a slave)'); insert into pydar2_rights (id, abbrev, description) values (5, 'BUILD_SPEC_BY_PATH', 'queue the build of a spec file (needed fro a client)'); insert into pydar2_rights (id, abbrev, description) values (6, 'UPDATE_SPEC_REPOSITORIES_FILE_LIST', 'ask the master to update a specrepository'); insert into pydar2_rights (id, abbrev, description) values (7, 'GET_SPEC_REPOSITORIES_UPDATE_COMMANDS', 'get the update commands for the specrepositories'); insert into pydar2_rights (id, abbrev, description) values (8, 'UPDATE_SITE_SCRIPT', 'call the script which builds a site'); insert into pydar2_rights (id, abbrev, description) values (9, 'UPDATE_ALL_SPEC_REPOSITORIES_FILE_LISTS', 'ask the master to update all spec repositories'); insert into pydar2_rights (id, abbrev, description) values (10, 'GET_NUMBER_OF_NOTYETSTARTED_COMMANDS', 'get the number of commands which are not yet reserved or finished'); insert into pydar2_rights (id, abbrev, description) values (11, 'AUTO_QUEUE', 'auto queue commands'); insert into pydar2_rights (id, abbrev, description) values (12, 'MOVE_COMMAND_RESULTS', 'move the results of a command'); insert into pydar2_rights (id, abbrev, description) values (13, 'UPDATE_TARGET_FILE_LIST', 'update the list of rpms of a target'); insert into pydar2_rights (id, abbrev, description) values (14, 'TEST_BUILD_SPEC_BY_PATH', 'test build a spec file');