<commit_before>
create or replace view publisher.dataset_column_diff as
select d.id dataset_id, 'ADD' diff, dc.name, dc.data_type from publisher.dataset d
join publisher.dataset_column dc on dc.dataset_id = d.id
where not exists (
	select * from publisher.dataset_import_job_column_union u
	where u.name = dc.name and u.data_type = dc.data_type and u.dataset_id = d.id
)
and exists (
	select * from publisher.last_import_job lij
	where lij.dataset_id = d.id
)
union all
select d.id dataset_id, 'REMOVE' diff, inc.name, inc.data_type from publisher.dataset d
join publisher.last_import_job lij on lij.dataset_id = d.id
join publisher.import_job ij on ij.job_id = lij.job_id
join publisher.import_job_column inc on inc.import_job_id = ij.id
where not exists (
	select * from publisher.dataset_import_job_column_union u
	where u.name = inc.name and u.data_type = inc.data_type and u.dataset_id = d.id
)
and exists (
	select * from publisher.last_import_job lij
	where lij.dataset_id = d.id
);

create or replace view publisher.source_dataset_column_diff as
select d.id dataset_id, 'ADD' diff, sdvc.name, sdvc.data_type from publisher.dataset d
join publisher.last_source_dataset_version lsdv on lsdv.dataset_id = d.id
join publisher.source_dataset_version_column sdvc on sdvc.source_dataset_version_id = lsdv.source_dataset_version_id
where not exists (
	select * from publisher.source_dataset_import_job_column_union u
	where u.name = sdvc.name and u.data_type = sdvc.data_type and u.dataset_id = d.id
)
and exists (
	select * from publisher.last_import_job lij
	where lij.dataset_id = d.id
)
union all
select d.id dataset_id, 'REMOVE' diff, sdvc.name, sdvc.data_type from publisher.dataset d
join publisher.last_import_job lij on lij.dataset_id = d.id
join publisher.import_job ij on ij.job_id = lij.job_id
join publisher.source_dataset_version_column sdvc on sdvc.source_dataset_version_id = ij.source_dataset_version_id
where not exists (
	select * from publisher.source_dataset_import_job_column_union u
	where u.name = sdvc.name and u.data_type = sdvc.data_type and u.dataset_id = d.id
)
and exists (
	select * from publisher.last_import_job lij
	where lij.dataset_id = d.id
);

insert into publisher.version(id) values(29);<commit_msg>*column_diff views rewriten<commit_after>
create or replace view publisher.dataset_column_diff as
select ij.dataset_id, 'REMOVE' diff, ijc.name, ijc.data_type from publisher.import_job_column ijc
join publisher.import_job ij on ij.id = ijc.import_job_id
where not exists ( -- imported column but no longer part of configuration 
	select * from publisher.dataset d
	join publisher.dataset_column dc on dc.dataset_id = d.id
	where d.id = ij.dataset_id and dc.name = ijc.name and dc.data_type = ijc.data_type
) and ij.id = ( -- last import job for dataset
	select max(id) 
	from publisher.import_job ij_max
	where ij_max.dataset_id = ij.dataset_id
	and exists (
		select * from publisher.job_state js
		where js.job_id = ij_max.job_id 
		and js.state in ('SUCCEEDED', 'ABORTED', 'FAILED')
	)
)
union all
select dc.dataset_id, 'ADD' diff, dc.name, dc.data_type from publisher.dataset_column dc
join publisher.dataset d on d.id = dc.dataset_id
where not exists ( -- part of configuration but not imported
	select * from publisher.import_job ij
	join publisher.import_job_column ijc on ijc.import_job_id = ij.id
	where ij.dataset_id = d.id and ijc.name = dc.name and ijc.data_type = dc.data_type
	and ij.id = ( -- last import job for dataset
		select max(id) 
		from publisher.import_job ij_max
		where ij_max.dataset_id = ij.dataset_id
		and exists (
			select * from publisher.job_state js
			where js.job_id = ij_max.job_id 
			and js.state in ('SUCCEEDED', 'ABORTED', 'FAILED')
		)
	)
) and exists ( -- is dataset imported
	select * from publisher.import_job ij
	where ij.dataset_id = d.id
	and exists (
		select * from publisher.job_state js
		where js.job_id = ij.job_id 
		and js.state in ('SUCCEEDED', 'ABORTED', 'FAILED')
	)
);

create or replace view publisher.source_dataset_column_diff as
select ij.dataset_id, 'REMOVE' diff, imported_sdvc.name, imported_sdvc.data_type from publisher.import_job ij
join publisher.source_dataset_version_column imported_sdvc on imported_sdvc.source_dataset_version_id = ij.source_dataset_version_id
join publisher.source_dataset_version sdv on sdv.id = imported_sdvc.source_dataset_version_id
where not exists ( -- in imported version but not in latest version
	select * from publisher.source_dataset_version_column latest_sdvc
	where latest_sdvc.name = imported_sdvc.name and latest_sdvc.data_type = imported_sdvc.data_type
	and latest_sdvc.source_dataset_version_id = ( -- latest version of the same source dataset
		select max(id) 
		from publisher.source_dataset_version max_sdv
		where max_sdv.source_dataset_id = sdv.source_dataset_id)
) and ij.id = ( -- last import job for dataset
	select max(id) from publisher.import_job ij_max
	where ij_max.dataset_id = ij.dataset_id
	and exists (
		select * from publisher.job_state js
		where js.job_id = ij_max.job_id 
		and js.state in ('SUCCEEDED', 'ABORTED', 'FAILED')
	)
)
union all
select ij.dataset_id, 'ADD' diff, latest_sdvc.name, latest_sdvc.data_type from publisher.import_job ij
join publisher.dataset d on d.id = ij.dataset_id
join publisher.source_dataset sd on sd.id = d.source_dataset_id
join publisher.source_dataset_version sdv on sdv.source_dataset_id = sd.id
join publisher.source_dataset_version_column latest_sdvc on latest_sdvc.source_dataset_version_id = sdv.id
where not exists ( -- in latest version but not in imported version
	select * from publisher.source_dataset_version_column imported_sdvc
	where imported_sdvc.name = latest_sdvc.name and imported_sdvc.data_type = latest_sdvc.data_type
	and imported_sdvc.source_dataset_version_id = ij.source_dataset_version_id
) and latest_sdvc.source_dataset_version_id = ( -- latest version of the source dataset
	select max(id) 
	from publisher.source_dataset_version max_sdv
	where max_sdv.source_dataset_id = sdv.source_dataset_id
) and ij.id = ( -- last import job for dataset
	select max(id) from publisher.import_job ij_max
	where ij_max.dataset_id = ij.dataset_id
	and exists (
		select * from publisher.job_state js
		where js.job_id = ij_max.job_id 
		and js.state in ('SUCCEEDED', 'ABORTED', 'FAILED')
	)
);

drop view publisher.dataset_import_job_column_union;
drop view publisher.source_dataset_import_job_column_union;

insert into publisher.version(id) values(29);
{'fraction_non_alphanumeric': 0.07522397281433427, 'fraction_numerical': 0.0006178560395427865, 'mean_word_length': 4.907846715328467, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4266820', 'n_tokens_mistral': 2247, 'n_tokens_neox': 2204, 'n_words': 610}