<commit_before>

create or replace view helper_view as

select 
	vt.uid as child, 
	iat_vt.uid as parent
from vocabulary_term vt 
-- left join term_category_classification tcc on tcc.vocabulary_term_id = vt.id 
left join internal_associated_terms  iat on iat.subject_vocabulary_term_id = vt.id 
left join vocabulary_term iat_vt on iat.object_vocabulary_term_id = iat_vt.id  and iat.association_type_name = 'isInstanceOf' 
;


-- Strategy is to return all child, parent relationships via the recursion .
-- then filter for where there's no parent and the start leaf is matched.

-- note that it may return multiple roots,

-- see example here, http://practiceovertheory.com/blog/2013/07/12/recursive-query-is-recursive/
-- it's complicated because we want to maintain the starting position

-- ok in fact we want another view ...

WITH RECURSIVE t( start, child, parent ) AS (
	-- intitial state - find the parent and record the startial position
	select h.child, h.child, h.parent 
		from helper_view h 
	union all
	select t.child, h.child, h.parent  
		from helper_view h 
		join t on (h.child = t.parent )
)

	select  trim( child) as child 
	from t where start = 'http://vocab.aodn.org.au/def/platform/271' -- 'http://vocab.nerc.ac.uk/collection/L06/current/32' -- 'http://vocab.aodn.org.au/def/platform/271' -- and parent is not null  
	and parent is null
;

<commit_msg>turned it into a view<commit_after>

create or replace view helper_view as

select 
	vt.uid as child, 
	iat_vt.uid as parent
from vocabulary_term vt 
-- left join term_category_classification tcc on tcc.vocabulary_term_id = vt.id 
left join internal_associated_terms  iat on iat.subject_vocabulary_term_id = vt.id 
left join vocabulary_term iat_vt on iat.object_vocabulary_term_id = iat_vt.id  and iat.association_type_name = 'isInstanceOf' 
;


-- Strategy is to return all child, parent relationships via the recursion .
-- then filter for where there's no parent and the start leaf is matched.

-- note that it may return multiple roots,

-- see example here, http://practiceovertheory.com/blog/2013/07/12/recursive-query-is-recursive/
-- it's complicated because we want to maintain the starting position

-- ok in fact we want this to be another view
create or replace view myview2
as 
WITH RECURSIVE t( start, child, parent ) AS (
	-- intitial state - find initial child,parent tuple and record with start leaf
	select h.child, h.child, h.parent 
		from helper_view h 
	union all
	-- recurse - trace new child, parent tuples and record against leaf
	select t.child, h.child, h.parent  
		from helper_view h 
		join t on (h.child = t.parent )
)

	select  start, child from t
	-- from t where start = 'http://vocab.aodn.org.au/def/platform/271' -- 'http://vocab.nerc.ac.uk/collection/L06/current/32' -- 'http://vocab.aodn.org.au/def/platform/271' -- and parent is not null  
	where parent is null
;





{'fraction_non_alphanumeric': 0.08781673030197848, 'fraction_numerical': 0.012842762929538355, 'mean_word_length': 3.5314465408805034, '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': '7104070', 'n_tokens_mistral': 1017, 'n_tokens_neox': 938, 'n_words': 328}