default:
  context:
    node_name: '@node'
    node: 'cleanWater'
    visitor: 'ant'
  src: ""
  sql: "[%Q{SELECT nodes.* FROM nodes WHERE #{secure_scope('nodes')} AND nodes.parent_id = ? ORDER BY nodes.zip ASC}, @node.id]"
  res: "Etat des travaux, The lake we love, it's a lake, water, crocodiles, super ouverture"

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

pages:
  src: "pages"
  sql: "[%Q{SELECT nodes.* FROM nodes WHERE #{secure_scope('nodes')} AND nodes.kpath LIKE 'NP%' AND nodes.parent_id = ? ORDER BY nodes.zip ASC}, @node.id]"
  res: "Etat des travaux, crocodiles"

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

nodes_in_site:
  sql: "%Q{SELECT nodes.* FROM nodes WHERE #{secure_scope('nodes')} ORDER BY nodes.zip ASC}"

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

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

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

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

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

# --- fixed up to here

pages_or_letters:
  context:
    node: zena
  src: pages or letters
  sql: "[%Q{SELECT nodes.* FROM nodes WHERE #{secure_scope('nodes')} AND ((nodes.kpath LIKE 'NP%' AND nodes.parent_id = ?) OR (nodes.kpath LIKE 'NNL%' AND nodes.parent_id = ?)) GROUP BY id ORDER BY nodes.zip ASC}, @node.id, @node.id]"
  res: "people, projects list, Collections, Kill the bugs, Nature, zena enhancements, Skins (layout themes)"

project:
  sql: "[%Q{SELECT nodes.* FROM nodes WHERE #{secure_scope('nodes')} AND nodes.id = ? ORDER BY nodes.zip ASC}, @node.get_project_id]"
  res: 'Clean Water project'

many_alternatives:
  src: tagged in site or images in site or tags in site
  sql: "%Q{SELECT nodes.*,links.id AS `link_id`,links.status AS `l_status`,links.comment AS `l_comment`,links.date AS `l_date` FROM links,nodes WHERE #{secure_scope('nodes')} AND ((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)) GROUP BY nodes.id ORDER BY nodes.zip ASC}"
#  res: "art, bird, Clean Water project, flower, it's a lake, menu, news, opening, tree"

alternatives_same_join:
  src: tagged in site or icons
  sql: "[%Q{SELECT nodes.*,links.id AS `link_id`,links.status AS `l_status`,links.comment AS `l_comment`,links.date AS `l_date` FROM links,nodes WHERE #{secure_scope('nodes')} AND ((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 = ?)) GROUP BY nodes.id ORDER BY nodes.zip ASC}, @node.id]"
  res: "Clean Water project, it's a lake, super ouverture"

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


notes_in_project:
  res: "super ouverture"

vclass_in_project:
  src: "posts in project"
  res: "super ouverture"

vlcass_or_pages:
  src: "(posts in project) or pages"
  res: "Etat des travaux, crocodiles, super ouverture"

find_relation:
  context:
    node: 'ant'
    class: Contact
  src: "favorites"
  res: "Nature"

root:
  res: "Zena the wild CMS"

self:
  sql: "[%Q{SELECT nodes.* FROM nodes WHERE #{secure_scope('nodes')} AND nodes.id = ? ORDER BY nodes.zip ASC}, @node.id]"

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

notes_or_news:
  context:
    class: Project
  sql: "[%Q{SELECT nodes.*,links.id AS `link_id`,links.status AS `l_status`,links.comment AS `l_comment`,links.date AS `l_date` FROM links,nodes WHERE #{secure_scope('nodes')} AND ((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 = ?)) GROUP BY nodes.id ORDER BY nodes.zip ASC}, @node.id, @node.id]"

news_or_notes:
  context:
    class: Project
  sql: "[%Q{SELECT nodes.*,links.id AS `link_id`,links.status AS `l_status`,links.comment AS `l_comment`,links.date AS `l_date` FROM links,nodes WHERE #{secure_scope('nodes')} AND ((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)) GROUP BY nodes.id ORDER BY nodes.zip ASC}, @node.id, @node.id]"

order_l_status:
  context:
    node:  art
    class: Tag
  src: "tagged order by l_status ASC, zip ASC"
  sql: "[%Q{SELECT nodes.*,links.id AS `link_id`,links.status AS `l_status`,links.comment AS `l_comment`,links.date AS `l_date` FROM links,nodes WHERE #{secure_scope('nodes')} AND nodes.id = links.source_id AND links.relation_id = _ID(node_has_tags) AND links.target_id = ? ORDER BY links.status ASC, nodes.zip ASC}, @node.id]"
  res: "super ouverture, Clean Water project"

order_random:
  context:
    node: wiki
  src: "nodes order by random limit 1"
  sqlite3: "[%Q{SELECT nodes.* FROM nodes WHERE #{secure_scope('nodes')} AND nodes.parent_id = ? ORDER BY random() LIMIT 1}, @node.id]"
  mysql: "[%Q{SELECT nodes.* FROM nodes WHERE #{secure_scope('nodes')} AND nodes.parent_id = ? ORDER BY RAND() LIMIT 1}, @node.id]"
  res: "/bird|flower/"

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: "[%Q{SELECT nodes.* FROM nodes WHERE #{secure_scope('nodes')} ORDER BY nodes.zip LIMIT 2 OFFSET ?}, ((params[:p].to_i > 0 ? params[:p].to_i : 1)-1)*2]"
  res: "Zena the wild CMS, people"

paginate_two:
  context:
    params:
      p: 2
  src: "nodes in site order by zip limit 2 paginate p"
  sql: "[%Q{SELECT nodes.* FROM nodes WHERE #{secure_scope('nodes')} ORDER BY nodes.zip LIMIT 2 OFFSET ?}, ((params[:p].to_i > 0 ? params[:p].to_i : 1)-1)*2]"
  res: "Solenopsis Invicta, Panthera Tigris Sumatran"

select_index_field:
  src: "nodes select title as ti"
  sql: "/SELECT nodes.*,ml1.value AS `ti`/"

coalesce:
  src: "nodes in site order by parent_id.coalesce(0) asc limit 1"
  sql: "%Q{SELECT nodes.* FROM nodes WHERE #{secure_scope('nodes')} ORDER BY COALESCE(nodes.parent_id,0) ASC LIMIT 1}"
  res: "Zena the wild CMS"