#4 Start NIF integration about neuroml-db HOT 1 CLOSED

Hi, Justas -

I have included below my queries in support of the NIF integration to return null values when the metadata does not exist and to return a collection of values as one field, e.g., authors.
I have not defined the views that are shown as comments at this point.
I still need to put this all together for NIF.
I would like you to look at the detailed plan (sent in an earlier email) and
verify these queries before our Wednesday meeting, if possible.

See you then,
Suzanne

use neuromldb;

-- create view model_ID_NAME_TYPE as
select Cell_ID as Model_ID, Cell_Name as Model_Name, "Cell" as Model_Type from cells union select Channel_ID as Model_ID, Channel_Name as Model_Name, "Channel" as Model_Type from channels union select Network_ID as Model_ID, Network_Name as Model_Name, "Network" as Model_Type from networks union select Synapse_ID as Model_ID, Synapse_Name as Model_Name, "Synapse" as Model_Type from synapses ;

-- create view model_neurolexes as
select m.Model_ID, nl.NeuroLex_URI, nl.NeuroLex_Term from models m left join (select mma.Model_ID, n.NeuroLex_URI, n.NeuroLex_Term
from model_metadata_associations mma join neurolexes n
on mma.Metadata_ID = n.NeuroLex_ID) nl on m.Model_ID = nl.Model_ID order by m.Model_ID;

-- create view model_publications as
select m.Model_ID, pub.Pubmed_Ref, pub.Full_Title as Pubmed_Title from models m left join (select mma.Model_ID, p.Pubmed_Ref, p.Full_Title
from model_metadata_associations mma join publications p
on mma.Metadata_ID = p.Publication_ID) pub
on m.Model_ID = pub.Model_ID order by m.Model_ID;

-- create view model_keywords as
select m.Model_ID, k.keywords as Keywords from models m left join (select mma.Model_ID, group_concat(ok.Other_Keyword_term) as keywords from model_metadata_associations mma join other_keywords ok
on mma.Metadata_ID = ok.Other_Keyword_ID group by mma.Model_ID) k on m.Model_ID = k.Model_ID order by m.Model_ID;

-- create view model_authors as
select m.Model_ID, aut.Authors
from models m left join
(select mma.Model_ID, group_concat(CONCAT(p.Person_First_Name, ' ', p.Person_Last_Name) SEPARATOR ', ') as Authors
from model_metadata_associations mma join author_lists al on mma.Metadata_ID = al.AuthorList_ID
join author_list_associations ala on al.AuthorList_ID = ala.AuthorList_ID
join people p on ala.Person_ID = p.Person_ID where ala.is_translator='0' or ala.is_translator='2'
group by mma.Model_ID) aut
on m.Model_ID = aut.Model_ID
order by m.Model_ID;

-- model with its "associated (parent or child) models
select m.Model_ID, group_concat(a.associated_Model_ID SEPARATOR ', ') as Associated_Models
from models m left join (select m.Model_ID, child as associated_Model_ID
from models m join partof_tables p
where m.Model_ID = p.parent
union
select m.Model_ID, parent as associated_Model_ID
from models m join partof_tables p
where m.Model_ID = p.child) a
on m.Model_ID = a.Model_ID
group by m.Model_ID
order by m.Model_ID ;

from neuroml-db.