# Syntax:
# * lines starting with "#" are remarks.
# * lines starting with "select" are queries, followed by expected results and an empty line
# * lines starting with "explain" are followed by expected query plan and an empty line
# * all other lines are are committed into the microkernel (line by line)
# * new tests are typically be added on top, after the syntax docs
# * use ascii character only

+ "test": { "jcr:resource": {}, "resource": { "x" : {}}}

explain select * from [nt:base] as b where ischildnode(b, '/test')
nt:base AS b /* traverse "/test/*" */

explain select * from [nt:base] as b where isdescendantnode(b, '/test')
nt:base AS b /* traverse "/test//*" */

select * from [nt:base] as b where ischildnode(b, '/test')
/test/jcr:resource
/test/resource

select * from [nt:base] as b where issamenode(b, '/test')
/test

select * from [nt:base] where name() = 'resource'
/test/resource

select * from [nt:base] as b where localname(b) = 'resource'
/test/jcr:resource
/test/resource

select * from [nt:base] as x where isdescendantnode(x, '/')
/
/test
/test/jcr:resource
/test/resource
/test/resource/x

- "test"

+ "parents": { "p0": {"id": "0"}, "p1": {"id": "1"}, "p2": {"id": "2"}}
+ "children": { "c1": {"p": "1"}, "c2": {"p": "1"}, "c3": {"p": "2"}, "c4": {"p": "3"}}

select * from [nt:base] as p where p.[jcr:path] = '/parents'
/parents

select * from [nt:base] as p inner join [nt:base] as p2 on ischildnode(p2, p) where p.[jcr:path] = '/'
/, /parents
/, /children

select * from [nt:base] as p inner join [nt:base] as p2 on isdescendantnode(p2, p) where p.[jcr:path] = '/parents'
/parents, /parents/p0
/parents, /parents/p1
/parents, /parents/p2

select * from [nt:base] as p inner join [nt:base] as p2 on issamenode(p2, p) where p.[jcr:path] = '/parents'
/parents, /parents

select id from [nt:base] where id is not null
0
1
2

select id from [nt:base] where id is not null order by id desc
2
1
0

select * from [nt:base] as c right outer join [nt:base] as p on p.id = c.p where p.id is not null
null, /parents/p0
/children/c1, /parents/p1
/children/c2, /parents/p1
/children/c3, /parents/p2

select * from [nt:base] as p left outer join [nt:base] as c on p.id = c.p where p.id is not null
/parents/p0, null
/parents/p1, /children/c1
/parents/p1, /children/c2
/parents/p2, /children/c3

select * from [nt:base] as p left outer join [nt:base] as c on p.id = c.p where p.id is not null and c.p is null
/parents/p0, null

select * from [nt:base] as p left outer join [nt:base] as c on p.id = c.p where p.id is not null and c.p is not null
/parents/p1, /children/c1
/parents/p1, /children/c2
/parents/p2, /children/c3

select * from [nt:base] as p inner join [nt:base] as c on p.id = c.p
/parents/p1, /children/c1
/parents/p1, /children/c2
/parents/p2, /children/c3

explain select * from [nt:base] as p inner join [nt:base] as c on p.id = c.p
nt:base AS p /* traverse "//*" */ INNER JOIN nt:base AS c /* traverse "//*" */

- "parents"
- "children"

+ "test": { "hello": { "x": "1" }, "world": { "x": "2" } }
+ "test2": { "id":"1", "x": "2" }

select * from [nt:base]
/
/test
/test/hello
/test/world
/test2

select * from [nt:base] where id = '1'
/test2

select * from [nt:base] where id = '1' and x = '2'
/test2

select * from [nt:base] where id = '1' or x = '2'
/test/world
/test2

select * from [nt:base] where not (id = '1' or x = '2')
/
/test
/test/hello

select * from [nt:base] where x is null
/
/test

- "test"
- "test2"

+ "test": { "name": "hello" }
+ "test2": { "name": "World!" }
+ "test3": { "name": "Hallo" }
+ "test4": { "name": "10%" }
+ "test5": { "name": "10 percent" }

select name from [nt:base] order by upper(name)
10 percent
10%
Hallo
hello
World!
null

select * from [nt:base] where length(name) = 5
/test
/test3

select * from [nt:base] where upper(name) = 'HELLO'
/test

select * from [nt:base] where lower(name) = 'world!'
/test2

select * from [nt:base] where name like 'W%'
/test2

select * from [nt:base] where name like '%o_%'
/test2

select * from [nt:base] where name like '__llo'
/test
/test3

select * from [nt:base] where upper(name) like 'H_LLO'
/test
/test3

select * from [nt:base] where upper(name) like 'H\_LLO'

select * from [nt:base] where upper(name) like '10%'
/test4
/test5

select * from [nt:base] where upper(name) like '10\%'
/test4