simple_reproducer_setup_sql.pgsql

set search_path = public ;

-- project_stream
drop table if exists zps ;
create table zps (
	id bigint primary key,
	sid bigint not null,
	pid bigint not null,
	col1 bigint,
	col2 varchar(255) not null,
	unique(sid, col1),
	unique(pid, sid)
	) ;
insert into zps(id, sid, pid, col1, col2)
	select id, stream_id, project_id, primary_project_indicator, 'L' || id from project_stream ;

-- stream
drop table if exists zs ;
create table zs(
	id bigint primary key,
	col18 bigint not null default 42
	);
insert into zs select id from stream ;

-- stream_element
drop table if exists zse ;
create table zse(id bigint, sid bigint);
insert into zse(id, sid) select id, stream_id from stream_element;
alter table zse add primary key (id) ;
create index on zse(sid) ;

-- stream_defect
drop table if exists zsd ;
create table zsd(id bigint, sei bigint, dti bigint);
insert into zsd(id, sei, dti) select id, stream_element_id, defect_triage_id from stream_defect ;
alter table zsd add primary key (id) ;
create index on zsd(dti) ;
create index on zsd(sei) ;

-- stream_defect_occurrence
drop table if exists zsdo ;
create table zsdo(sdi bigint, sfi bigint);
insert into zsdo(sdi, sfi) select stream_defect_id, stream_file_id from stream_defect_occurrence ;
create index on zsdo(sdi) ;
create index on zsdo(sfi) ;

-- stream_file
drop table if exists zsf ;
create table zsf(
	id bigint primary key,
	cid bigint not null,
	fpi bigint not null,
	unique (id, fpi, cid)
	);
insert into zsf(id, cid, fpi) select id, component_id, file_path_id from stream_file ;
create index on zsf(cid) ;

-- defect_triage
drop table if exists zdt ;
create table zdt(id bigint);
insert into zdt select id from defect_triage ;
alter table zdt add primary key (id) ;


\q

/usr/pgsql-15/bin/pg_dump -h localhost -p $PG15_PORT -Fc -t 'public.z*' connect > simple_reproducer.dump

createdb -h localhost -p $PG17_PORT simple_reproducer
pg_restore -h localhost -p $PG17_PORT -Fc -d simple_reproducer simple_reproducer.dump