:-eval(consult("utils.prova")).

:- eval(uniprot_use_001()).

location(database,uniprot,"jdbc:mysql://localhost/uniprot","guest","guestdb").

uniprot_use_001() :-
	dbopen(uniprot,DB),
	println(["===========================uniprot use 001==========================="]),
	issue_query(DB).

issue_query(DB) :-
	println(["---------------------------------------------------------------------"]),
	println(["The first 10 counts of terms with the same name and counts>4:"]),
   % The equivalent SELECT statement is:
   % select name,count(*) from term group by name having count(*)>4 limit 10
	sql_select(DB,term,[name,N],[count(*),C],[having,'count(*)>4'],[options,'limit 10']),
	println([N,C],"->").
issue_query(DB) :-
	println(["---------------------------------------------------------------------"]),
	println(["The first 10 counts of terms with the same name and counts>4:"]),
	sql_select(DB,'term group by name',[name,N],['count(*)',C],[having,'count(*)>4'],[options,'limit 10']),
	println([N,C],"->").
issue_query(DB) :-
	println(["---------------------------------------------------------------------"]),
	println(["The first 10 types of records with name ZYX:"]),
	sql_select(DB,term,[name,'ZYX'],[type,T],[options,'limit 10']),
	println([T]," ").
issue_query(DB) :-
	println(["---------------------------------------------------------------------"]),
	println(["The first 10 synonym-id pairs:"]),
	sql_select(DB,synonym,[id1,ID1],[id2,ID2],[options,'limit 10']),
	println([ID1,ID2]," ").
issue_query(DB) :-
	println(["---------------------------------------------------------------------"]),
	println(["The first 10 synonym-ids of synonyms for a term ZYX_MOUSE:"]),
	sql_select(DB,'term,synonym',['term.name','ZYX_MOUSE'],['synonym.id2',ID2],[where,'term.id=synonym.id1'],[options,'limit 10']),
	println([ID2]," ").
issue_query(DB) :-
	println(["---------------------------------------------------------------------"]),
	println(["The first 10 synonyms of ZYX_MOUSE:"]),
	sql_select(DB,'term as t1,synonym as s,term as t2',['t1.name','ZYX_MOUSE'],['t2.name',N],['t2.type',T],[where,'t1.id=s.id1 and s.id2=t2.id'],[options,'limit 10']),
	println([N,T],"->").
issue_query(DB) :-
	println(["---------------------------------------------------------------------"]),
	println(["The first 10 terms containing 'ykl001c':"]),
	sql_select(DB,term,[id,ID],[name,N],[type,T],[where,"match(name) against('+"ykl001c"' in boolean mode)"],[options,'limit 10']),
	println([ID,N,T],"->").
issue_query(DB) :-
	println(["---------------------------------------------------------------------"]),
	println(["The first 10 terms containing 'yll041c':"]),
	sql_select(DB,'term as t1,synonym as s,term as t2',['t1.name',N1],['t2.name',N2],['t2.type',T],[where,"match(t1.name) against('+"yll041c"' in boolean mode) and t1.id=s.id1 and s.id2=t2.id"],[options,'limit 10']),
	println([N1,N2,T],"->").
