[select]
countryList = SELECT DISTINCT isocountry.full_country_name, isocountry.iso_country_id	FROM address, body, isocountry	WHERE address.address_id = body.address_fk AND address.iso_country_fk = isocountry.iso_country_id AND body.body_type_fk > 0		ORDER BY isocountry.full_country_name;
all = SELECT DISTINCT body.body_id, body.body_type_fk, bodytype.body_type, bodyname.bodyname, address.town,isocountry.iso_country_id, isocountry.full_country_name
details=SELECT bodyname.bodyname,isolanguage.language,address.institution_name,address.sub_unit_name,address.address_line_1,address.address_line_2,address.address_line_3,address.address_line_4,address.address_line_5,address.town,address.region,isocountry.full_country_name,body.access_restriction,body.usage_restriction, body.strength,body.purpose,body.includes_types,body.collection_code,body.number_of_subunits,body.notes,body.number_of_species,description.description,descriptionurl.description_url
partof=SELECT body_1.body_id, bodyname.bodyname
children=SELECT body_1.body_id, bodyname.bodyname
contact=SELECT DISTINCT body.body_id, person.family_name, personrole.person_role_id, person.person_id
keyword=SELECT bodyname.bodyname, keyword.keyword, keywordtype.keyword_type, keywordtype.controlled, body.body_id
contactdetails=SELECT personinbody.person_role_fk, body.body_id, person.family_name, person.given_name, person.additional_names, person.name_prefix, person.name_suffix, person.job_title, person.person_id, communication.telephone, communication.fax, communication.email, personrole.person_role
types=SELECT DISTINCT body_type_id, body_type 					
countries= SELECT DISTINCT isocountry.full_country_name,count(body_id) as total
people=SELECT DISTINCT family_name FROM person
network=SELECT DISTINCT body.body_id,bodyname.bodyname 
institution=SELECT DISTINCT body.body_id,bodyname.bodyname
collection=SELECT DISTINCT body.body_id,bodyname.bodyname
time=SELECT DISTINCT keyword,count(body_id)
scientificName=SELECT DISTINCT keyword,count(body_id)
vernacularName=SELECT DISTINCT keyword,count(body_id)
geographicName=SELECT DISTINCT keyword,count(body_id)


[from]
all= address, isocountry, bodytype, body, bodyname
allkeyword=address, isocountry, bodytype, body, bodyname,bodykeyword,keyword,keywordtype
details = body
partof= body AS body_1, body, ispartof
children= body AS body_1, body, ispartof
contact= body, personinbody, person, personrole
keyword= body
contactdetails= communication Right Outer JOIN (address INNER JOIN (body INNER JOIN (person INNER JOIN (personrole INNER JOIN personinbody ON personrole.person_role_id = personinbody.person_role_fk) ON person.person_id = personinbody.person_fk) ON body.body_id = personinbody.body_fk) ON address.address_id = body.address_fk) ON communication.communication_id = person.communication_fk
types=bodytype		
countries= address, body, isocountry 
network=body, bodyname 
collection=body, bodyname
institution=body, bodyname
time=body	
scientificName=body
vernacularName=body
geographicName=body
collectionKeyword=address, isocountry, bodytype, body, bodyname,bodykeyword,keyword

				
[leftJoin]
details=bodyname ON bodyname.body_fk = body.body_id LEFT JOIN address ON address.address_id = body.address_fk LEFT JOIN isolanguage ON isolanguage.iso_language_id=bodyname.language_fk LEFT JOIN description ON body_id=description.body_fk LEFT JOIN descriptionurl ON body_id=descriptionurl.body_fk LEFT JOIN isocountry ON isocountry.iso_country_id = address.iso_country_fk
keyword=bodykeyword ON body.body_id = bodykeyword.body_fk		LEFT JOIN bodyname ON body.bodyname_pref_fk = bodyname.bodyname_id LEFT JOIN keyword ON bodykeyword.keyword_fk = keyword.keyword_id		LEFT JOIN keywordtype ON keyword.keyword_type_fk = keywordtype.keyword_type_id
time=JOIN bodykeyword ON body_id=bodykeyword.body_fk JOIN keyword ON keyword_fk=keyword_id
scientificName=JOIN bodykeyword ON body_id=bodykeyword.body_fk JOIN keyword ON keyword_fk=keyword_id
vernacularName=JOIN bodykeyword ON body_id=bodykeyword.body_fk JOIN keyword ON keyword_fk=keyword_id	
geographicName=JOIN bodykeyword ON body_id=bodykeyword.body_fk JOIN keyword ON keyword_fk=keyword_id

[join]
personInBody=JOIN personinbody ON person_fk = person_id
personInBody_body=JOIN personinbody ON body_fk=body_id JOIN person ON person_fk = person_id
personRole= JOIN personrole ON person_role_fk=person_role_id 
body= JOIN body ON body_fk=body_id
bodyType=JOIN bodytype ON body_type_fk = body_type_id
address=JOIN address ON body.address_fk=address_id
address_country=JOIN address ON body.address_fk=address_id JOIN isocountry ON iso_country_fk=iso_country_id 
isoCountry=JOIN isocountry ON iso_country_fk=iso_country_id
person=JOIN person ON person_fk = person_id
bodyKeyword=JOIN bodykeyword ON body_id=bodykeyword.body_fk JOIN keyword ON keyword_fk=keyword_id
bodyName=JOIN bodyname ON bodyname.body_fk=body_id


[where]
all= address_id = address_fk AND iso_country_fk = iso_country_id AND body_type_fk = body_type_id AND bodyname.body_fk = body.body_id AND address.town is not null AND address.iso_country_fk is not null
allkeyword=address_id = address_fk AND iso_country_fk = iso_country_id AND body_type_fk = body_type_id AND bodyname.body_fk = body.body_id AND address.town is not null AND address.iso_country_fk is not null AND  body.body_id = bodykeyword.body_fk AND bodykeyword.keyword_fk = keyword.keyword_id  AND keyword.keyword_type_fk = keywordtype.keyword_type_id
details= body.body_id
partof= bodyname.bodyname_id = body_1.bodyname_pref_fk		AND body.body_id = ispartof.child_body_fk		AND body_1.body_id = ispartof.parent_body_fk	AND body.body_id 
children= bodyname.bodyname_id = body_1.bodyname_pref_fk    AND body.body_id = ispartof.parent_body_fk		AND body_1.body_id = ispartof.child_body_fk		AND body.body_id 
contact= body.body_id = personinbody.body_fk	AND personinbody.person_fk = person.person_id	AND personinbody.person_role_fk = personrole.person_role_id  AND body.body_id
contactONLY=AND personrole.person_role_id IN (7,8,9) 
keyword=body.body_id 	
contactdetails= person.person_id %s %s
countries=address.address_id = body.address_fk AND address.iso_country_fk = isocountry.iso_country_id AND body.body_type_fk > 0
regions=region IS NOT NULL 
towns=town is not null 
people=(substr(family_name,1,1) between '%s' and '%s')
network= bodyname.body_fk = body.body_id AND body.body_type_fk=1
collection=bodyname.body_fk = body.body_id AND body.body_type_fk=3 
institution=bodyname.body_fk = body.body_id AND body.body_type_fk=2 
collectionABC=(substr(bodyname,1,1) between '%s' and '%s')
institutionABC=(substr(bodyname,1,1) between '%s' and '%s')
time=keyword_type_fk IN (6,12,13)
collectionKeyword=address_id = address_fk AND iso_country_fk = iso_country_id AND body_type_fk = body_type_id AND bodyname.body_fk = body.body_id AND address.town is not null AND address.iso_country_fk is not null AND bodykeyword.body_fk = body.body_id AND keyword.keyword_type_fk = keywordtype.keyword_type_id AND bodykeyword.keyword_fk = keyword.keyword_id
scientificName=keyword_type_fk =4 AND (substr(keyword,1,1) = '%s')
vernacularName=keyword_type_fk =7 AND (substr(keyword,1,1) = '%s')
geographicName=keyword_type_fk =5 AND (substr(keyword,1,1) = '%s')

[and]
keyword = ( body.freetext_search ILIKE '%s' or keyword.keyword ILIKE  '%s' or bodyname.bodyname ILIKE  '%s')
countryEq= body.body_type_fk > 0 AND isocountry.iso_country_id = 
countryAll=body.body_type_fk > 0 AND isocountry.iso_country_id > 0
bodyIDlist=body.body_id
collectionTimeKeyword=keyword_type_fk IN (6,12,13) AND keyword
collectionScientificNameKeyword=keyword_type_fk =4 AND keyword
collectionVernacularNameKeyword=keyword_type_fk =7 AND keyword
collectionGeographicNameKeyword=keyword_type_fk =5 AND keyword
isCollection=body.body_type_fk=3


[groupBy]
countries=GROUP BY isocountry.full_country_name
regions=GROUP BY body_id
towns=GROUP BY body_id
network=
collection=
institution=
time=GROUP BY keyword
scientificName=GROUP BY keyword
vernacularName=GROUP BY keyword
geographicName=GROUP BY keyword

[orderBy]
all=bodyname.bodyname
country= isocountry.full_country_name, address.town, bodyname.bodyname
town = address.town, isocountry.full_country_name, bodyname.bodyname
node=bodyname.bodyname 
countries=ORDER BY isocountry.full_country_name
regions=ORDER BY region ASC
town=ORDER BY town ASC
network=ORDER BY bodyname.bodyname
collection=ORDER BY bodyname.bodyname
institution=ORDER BY bodyname.bodyname
scientificName=
time=
vernacularName=
geographicName=


[postgres]
not = not        
and = and
or = or
isnull = is null
isnotnull = is not null
equals = =
notequals = <>
like = LIKE
lessthan = <
lessthanorequals = <=
greaterthan = >
greaterthanorequals = >=
in = IN

[escape]    
'= '
* = %