default:
  context:
    node_name: '@node'
    node: 'cleanWater'
    visitor: 'ant'
  src: "nodes where log_at = event_at in site"
  sql: "\"SELECT nodes.* FROM nodes WHERE nodes.log_at = nodes.event_at AND (#{@node.secure_scope('nodes')}) ORDER BY nodes.position ASC, nodes.name ASC\""

id_becomes_zip:
  src: "nodes where id ne 1"
  sql: "/nodes.zip <> 1/"

version_filter_title_like:
  src: "nodes where v_title like 'P%' in site order by zip"
  sql: "\"SELECT nodes.* FROM nodes,versions WHERE versions.title LIKE 'P%' AND nodes.id = versions.node_id AND (#{@node.secure_scope('nodes')}) GROUP BY nodes.id ORDER BY nodes.zip ASC\""
  # we avoid sorting by name here because of collation issues when using different
  # databases, and that's not what we are testing here
  res: "people, projects, opening, Project-changes-xml, Page-changes, Project"

dynattr_filter:
  src: "nodes where d_assigned = 'gaspard' in site"
  sql: "\"SELECT nodes.* FROM nodes,versions LEFT JOIN dyn_attributes ON versions.id = dyn_attributes.owner_id AND dyn_attributes.key = 'assigned' WHERE dyn_attributes.value = 'gaspard' AND nodes.id = versions.node_id AND (#{@node.secure_scope('nodes')}) GROUP BY nodes.id ORDER BY nodes.position ASC, nodes.name ASC\""
  res: "cleanWater, people, status"

# Results can be wrong if a node has many 'versions', some of which have d_assigned set and others not. See #190.
dynattr_filter_is_null:
  src: "projects where d_assigned is null in site"
  sql: "\"SELECT nodes.* FROM nodes,versions LEFT JOIN dyn_attributes ON versions.id = dyn_attributes.owner_id AND dyn_attributes.key = 'assigned' WHERE nodes.kpath LIKE 'NPP%' AND dyn_attributes.value IS NULL AND nodes.id = versions.node_id AND (#{@node.secure_scope('nodes')}) GROUP BY nodes.id ORDER BY nodes.position ASC, nodes.name ASC\""
  res: "wiki, zena"

dynattr_filter_in_relation:
  src: "favorites where d_priority = 'important'"
  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,versions LEFT JOIN dyn_attributes ON versions.id = dyn_attributes.owner_id AND dyn_attributes.key = 'priority',links WHERE nodes.id = links.target_id AND links.relation_id = _ID(contact_has_favorites) AND links.source_id = ? AND dyn_attributes.value = 'important' AND nodes.id = versions.node_id AND (#{@node.secure_scope('nodes')}) GROUP BY nodes.id ORDER BY nodes.position ASC, nodes.name ASC\", @node.id]"
  res: ""

version_filter_comment:
  context:
    visitor: 'lion'
  src: pages where v_comment = "no comment yet" in project
  sql: "[\"SELECT nodes.* FROM nodes,versions WHERE nodes.kpath LIKE 'NP%' AND nodes.project_id = ? AND versions.comment = 'no comment yet' AND nodes.id = versions.node_id AND (#{@node.secure_scope('nodes')}) GROUP BY nodes.id ORDER BY nodes.position ASC, nodes.name ASC\", @node.get_project_id]"
  res: "bananas, crocodiles"

year_function:
  context:
    visitor: 'lion'
  src: "nodes where v_updated_at:year = 2007 in project"
  sql: "[\"SELECT nodes.* FROM nodes,versions WHERE nodes.project_id = ? AND year(versions.updated_at) = 2007 AND nodes.id = versions.node_id AND (#{@node.secure_scope('nodes')}) GROUP BY nodes.id ORDER BY nodes.position ASC, nodes.name ASC\", @node.get_project_id]"
  sqlite3: "[\"SELECT nodes.* FROM nodes,versions WHERE nodes.project_id = ? AND strftime('%Y', versions.updated_at)*1 = 2007 AND nodes.id = versions.node_id AND (#{@node.secure_scope('nodes')}) GROUP BY nodes.id ORDER BY nodes.position ASC, nodes.name ASC\", @node.get_project_id]"
  res: "bananas"

two_fields:
  src: "notes where event_at = log_at in site"
  sql: "\"SELECT nodes.* FROM nodes WHERE nodes.kpath LIKE 'NN%' AND nodes.event_at = nodes.log_at AND (#{@node.secure_scope('nodes')}) ORDER BY nodes.position ASC, nodes.name ASC\""

filter_l_status:
  context:
    node: art
  src: "tagged where l_status > 5"
  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 links.status > 5 AND (#{@node.secure_scope('nodes')}) ORDER BY nodes.position ASC, nodes.name ASC\", @node.id]"
  res: "cleanWater"

param_filter:
  context:
    node: art
  src:
    - "tagged where name like \"%[param:tag]%\" and \"[param:tag]\" ne \"\" in site"
    - "tagged where l_status > 5"
  res: "cleanWater"

param_filter_no_string:
  src: "notes where created_at gt REF_DATE - param:f in site"
  sql: "/nodes.created_at\ > now\(\) - \? /"

filter_literal:
  src: "nodes where name like 'w%' in site"
  sql: "/name LIKE 'w%'/"
  res: "water, wiki, wikiSkin"

param_filter_tag:
  context:
    node: art
    params:
      tag: 'op'
  src:
    - "tagged where name like \"%[param:tag]%\" and \"[param:tag]\" ne \"\" in site"
    - "tagged where l_status > 5"
  res: "cleanWater, opening"

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

nodes_group_by_year:
  src: "nodes in site group by log_at:year order by log_at:year asc, name asc"
  sql: "\"SELECT nodes.* FROM nodes WHERE (#{@node.secure_scope('nodes')}) GROUP BY year(nodes.log_at) ORDER BY year(nodes.log_at) ASC, nodes.name ASC\""
  sqlite3: "\"SELECT nodes.* FROM nodes WHERE (#{@node.secure_scope('nodes')}) GROUP BY strftime('%Y', nodes.log_at)*1 ORDER BY strftime('%Y', nodes.log_at)*1 ASC, nodes.name ASC\""

negative_value:
  src: "notes where custom_a ne -10 in site"
  sql: "\"SELECT nodes.* FROM nodes WHERE nodes.kpath LIKE 'NN%' AND nodes.custom_a <> -10 AND (#{@node.secure_scope('nodes')}) ORDER BY nodes.position ASC, nodes.name ASC\""

filter_through_links:
  src: "nodes where (set_tag_id eq 33 and hot_id eq 22) in site"
  sql: "/jnode\.zip = 33 AND jn1\.zip = 22/"
  res: "cleanWater"

filter_with_fullpath:
  src: "nodes where fullpath like '[node:fullpath]/%' in site order by fullpath"
  sql: "[\"SELECT nodes.* FROM nodes WHERE nodes.fullpath LIKE ? AND (#{@node.secure_scope('nodes')}) ORDER BY nodes.fullpath ASC\", @node.safe_read(\"fullpath\").to_s+ \"/%\"]"
  res: 'crocodiles, lake, lakeAddress, opening, status, track, water'