default:
  context:
    node_name: '@node'
    node: 'cleanWater'
    visitor: 'ant'
  src: ""
  sql: "[\"SELECT nodes.* FROM nodes WHERE nodes.parent_id = ? AND (#{@node.secure_scope('nodes')}) ORDER BY nodes.position ASC, nodes.name ASC\", @node.id]"
  res: "crocodiles, lake, lakeAddress, opening, status, track, water"

nodes:
  src: "nodes"
  sql: "[\"SELECT nodes.* FROM nodes WHERE nodes.parent_id = ? AND (#{@node.secure_scope('nodes')}) ORDER BY nodes.position ASC, nodes.name ASC\", @node.id]"

pages:
  src: "pages"
  sql: "[\"SELECT nodes.* FROM nodes WHERE nodes.kpath LIKE 'NP%' AND nodes.parent_id = ? AND (#{@node.secure_scope('nodes')}) ORDER BY nodes.position ASC, nodes.name ASC\", @node.id]"
  res: "crocodiles, status, track"

letters:
  src: "letters"
  sql: "[\"SELECT nodes.* FROM nodes WHERE nodes.kpath LIKE 'NNL%' AND nodes.parent_id = ? AND (#{@node.secure_scope('nodes')}) ORDER BY nodes.position ASC, nodes.name ASC\", @node.id]"

nodes_in_site:
  sql: "\"SELECT nodes.* FROM nodes WHERE (#{@node.secure_scope('nodes')}) ORDER BY nodes.position ASC, nodes.name ASC\""

pages_in_project:
  sql: "[\"SELECT nodes.* FROM nodes WHERE nodes.kpath LIKE 'NP%' AND nodes.project_id = ? AND (#{@node.secure_scope('nodes')}) ORDER BY nodes.position ASC, nodes.name ASC\", @node.get_project_id]"

nodes_in_section:
  sql: "[\"SELECT nodes.* FROM nodes WHERE nodes.section_id = ? AND (#{@node.secure_scope('nodes')}) ORDER BY nodes.position ASC, nodes.name ASC\", @node.get_section_id]"

nodes_in_self:
  sql: "[\"SELECT nodes.* FROM nodes WHERE nodes.parent_id = ? AND (#{@node.secure_scope('nodes')}) ORDER BY nodes.position ASC, nodes.name ASC\", @node.id]"

nodes_in_parent:
  sql: "[\"SELECT nodes.* FROM nodes WHERE nodes.parent_id = ? AND (#{@node.secure_scope('nodes')}) ORDER BY nodes.position ASC, nodes.name ASC\", @node.parent_id]"

children:
  sql: "[\"SELECT nodes.* FROM nodes WHERE nodes.parent_id = ? AND (#{@node.secure_scope('nodes')}) ORDER BY nodes.position ASC, nodes.name ASC\", @node.id]"

pages_or_letters:
  context:
    node: zena
  src:
    - 'pages'
    - 'letters'
  sql: "[\"SELECT nodes.* FROM nodes WHERE ((nodes.kpath LIKE 'NP%' AND nodes.parent_id = ?) OR (nodes.kpath LIKE 'NNL%' AND nodes.parent_id = ?)) AND (#{@node.secure_scope('nodes')}) ORDER BY nodes.position ASC, nodes.name ASC\", @node.id, @node.id]"
  res: "collections, letter, nature, people, projects, skins"

project:
  sql: "[\"SELECT nodes.* FROM nodes WHERE nodes.id = ? AND (#{@node.secure_scope('nodes')}) ORDER BY nodes.position ASC, nodes.name ASC\", @node.get_project_id]"
  res: 'cleanWater'

many_alternatives:
  src:
    - 'tagged in site'
    - 'images in site'
    - 'tags in site'
  sql: "\"SELECT nodes.*,links.id AS link_id,links.status AS l_status,links.comment AS l_comment,links.date AS l_date FROM nodes,links WHERE ((nodes.id = links.source_id AND links.relation_id = _ID(node_has_tags)) OR (nodes.kpath LIKE 'NDI%' AND links.id = 0) OR (nodes.kpath LIKE 'NPT%' AND links.id = 0)) AND (#{@node.secure_scope('nodes')}) GROUP BY nodes.id ORDER BY nodes.position ASC, nodes.name ASC\""
  res: 'art, bird, cleanWater, flower, lake, menu, news, opening, tree'

alternatives_same_join:
  src:
    - 'tagged in site'
    - 'icons'
  sql: "[\"SELECT nodes.*,links.id AS link_id,links.status AS l_status,links.comment AS l_comment,links.date AS l_date FROM nodes,links WHERE ((nodes.id = links.source_id AND links.relation_id = _ID(node_has_tags)) OR (nodes.id = links.target_id AND links.relation_id = _ID(node_has_an_icon) AND links.source_id = ?)) AND (#{@node.secure_scope('nodes')}) GROUP BY nodes.id ORDER BY nodes.position ASC, nodes.name ASC\", @node.id]"
  res: "cleanWater, lake, opening"

same_name_as_class:
  context:
    node: 'opening'
  src: "set_tags"
  sql: "[\"SELECT nodes.*,links.id AS link_id,links.status AS l_status,links.comment AS l_comment,links.date AS l_date FROM nodes,links WHERE nodes.id = links.target_id AND links.relation_id = _ID(node_has_tags) AND links.source_id = ? AND (#{@node.secure_scope('nodes')}) ORDER BY nodes.position ASC, nodes.name ASC\", @node.id]"
  res: "art, news"


notes_in_project:
  res: "opening"

vclass_in_project:
  src: "posts in project"
  res: "opening"

vlcass_or_pages:
  src:
    - "posts in project"
    - "pages"
  res: "crocodiles, opening, status, track"

find_relation:
  context:
    node: 'ant'
  src: "favorites"
  res: "nature"

root:
  res: "zena"

site:
  err: "unknown relation 'site'"

pages_from_site_or_images:
  src:
    - 'project from site'
    - 'images'
  err: "unknown relation 'site'"

children_with_order_clause:
  src: "children order by name ASC"
  sql: "[\"SELECT nodes.* FROM nodes WHERE nodes.parent_id = ? AND (#{@node.secure_scope('nodes')}) ORDER BY nodes.name ASC\", @node.id]"

notes_or_news:
  src:
    - notes
    - news
  sql: "[\"SELECT nodes.*,links.id AS link_id,links.status AS l_status,links.comment AS l_comment,links.date AS l_date FROM nodes,links WHERE ((nodes.kpath LIKE 'NN%' AND nodes.parent_id = ? AND links.id = 0) OR (nodes.id = links.source_id AND links.relation_id = _ID(note_has_calendars) AND links.target_id = ?)) AND (#{@node.secure_scope('nodes')}) GROUP BY nodes.id ORDER BY nodes.position ASC, nodes.name ASC\", @node.id, @node.id]"

news_or_notes:
  src:
    - news
    - notes
  sql: "[\"SELECT nodes.*,links.id AS link_id,links.status AS l_status,links.comment AS l_comment,links.date AS l_date FROM nodes,links WHERE ((nodes.id = links.source_id AND links.relation_id = _ID(note_has_calendars) AND links.target_id = ?) OR (nodes.kpath LIKE 'NN%' AND nodes.parent_id = ? AND links.id = 0)) AND (#{@node.secure_scope('nodes')}) GROUP BY nodes.id ORDER BY nodes.position ASC, nodes.name ASC\", @node.id, @node.id]"

order_dynattr:
  src: "nodes order by d_assigned ASC, name ASC"
  sql: "[\"SELECT nodes.* FROM nodes,versions LEFT JOIN dyn_attributes ON versions.id = dyn_attributes.owner_id AND dyn_attributes.key = 'assigned' WHERE nodes.id = versions.node_id AND nodes.parent_id = ? AND (#{@node.secure_scope('nodes')}) GROUP BY nodes.id ORDER BY dyn_attributes.value ASC, nodes.name ASC\", @node.id]"
  res: "crocodiles, lake, lakeAddress, opening, track, water, status"

order_l_status:
  context:
    node: art
  src: "tagged order by l_status ASC, name ASC"
  sql: "[\"SELECT nodes.*,links.id AS link_id,links.status AS l_status,links.comment AS l_comment,links.date AS l_date FROM nodes,links WHERE nodes.id = links.source_id AND links.relation_id = _ID(node_has_tags) AND links.target_id = ? AND (#{@node.secure_scope('nodes')}) ORDER BY links.status ASC, nodes.name ASC\", @node.id]"
  res: "opening, cleanWater"

paginate_one:
  src: "nodes in site order by zip limit 2 paginate p"
  # we sort by zip to avoid collation problems when some DB sort Capital letters first
  # and others are case-insensitive.
  sql: "[\"SELECT nodes.* FROM nodes WHERE (#{@node.secure_scope('nodes')}) ORDER BY nodes.zip ASC LIMIT 2 OFFSET ?\", ((params[:p].to_i > 0 ? params[:p].to_i : 1)-1)*2]"
  res: "zena, people"

paginate_two:
  context:
    params:
      p: 2
  src: "nodes in site order by zip limit 2 paginate p"
  sql: "[\"SELECT nodes.* FROM nodes WHERE (#{@node.secure_scope('nodes')}) ORDER BY nodes.zip ASC LIMIT 2 OFFSET ?\", ((params[:p].to_i > 0 ? params[:p].to_i : 1)-1)*2]"
  res: "ant, tiger"