Advanced Features

-- Begin a distributed transaction
BEGIN TRANSACTION;

-- Operations span multiple graph partitions
INSERT (p:Person {id: 'user123', name: 'Alice'});
INSERT (c:Company {id: 'corp456', name: 'TechCo'});
INSERT (p)-[:WORKS_AT {since: DATE '2024-01-15'}]->(c);

-- Atomic commit ensures all-or-nothing semantics
COMMIT;

BEGIN TRANSACTION;

-- Create initial data
INSERT (u:User {id: 'u1', name: 'Bob'});

-- Create savepoint
SAVEPOINT sp1;

-- Risky operations
INSERT (u:User {id: 'u2', email: 'invalid'}); -- May fail validation

-- Rollback to savepoint if needed
ROLLBACK TO SAVEPOINT sp1;

-- Continue with valid operations
INSERT (u:User {id: 'u3', name: 'Charlie'});
COMMIT;

-- Set isolation level for current session
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Create policy restricting access to user's own data
-- Not expressible as a Geode policy predicate (only `=` against a string
-- literal compiles); precompute the decision into a property instead:
CALL geode.rls.add_attribute_policy('app', 'Person', 'access_tag', 'access_tag');

-- Create policy for role-based access
-- Not expressible as a Geode policy predicate (only `=` against a string
-- literal compiles); precompute the decision into a property instead:
CALL geode.rls.add_attribute_policy('app', 'Employee', 'access_tag', 'access_tag');

-- Relationship-level security
-- Not expressible as a Geode policy predicate (only `=` against a string
-- literal compiles); precompute the decision into a property instead:
CALL geode.rls.add_attribute_policy('app', 'Label', 'access_tag', 'access_tag');

-- Automatically filtered by RLS policies
MATCH (p:Person)
RETURN p.name, p.email;

-- Only returns nodes/relationships permitted by active policies
MATCH (e:Employee)-[:REPORTS_TO]->(m:Manager)
RETURN e.name, m.name;

-- Shortest path between two nodes
MATCH path = SHORTEST (a:Person {name: 'Alice'})-[:KNOWS*]->(b:Person {name: 'Bob'})
RETURN path;

-- All shortest paths
MATCH paths = ALL SHORTEST (a)-[:KNOWS*]->(b)
WHERE a.name = 'Alice' AND b.name = 'Bob'
RETURN paths;

-- K-shortest paths
MATCH paths = K SHORTEST 5 (a:Airport {code: 'SFO'})-[:FLIGHT*]->(b:Airport {code: 'JFK'})
RETURN paths, null /* reduce() is advertised by SHOW FUNCTIONS but has no evaluator dispatch; collect() the values and fold client-side (for path weights use algo.dijkstra.stream, which yields cost) */ AS total_cost;

-- Degree centrality (count of connections)
MATCH (p:Person)
OPTIONAL MATCH (p)-[r:KNOWS]-()
RETURN p.name, count(r) AS degree
ORDER BY degree DESC
LIMIT 10;

-- PageRank-style importance (recursive influence)
CALL algo.pagerank.stream()
YIELD nodeId, score
MATCH (p:Person) WHERE id(p) = nodeId
RETURN p.name, score
ORDER BY score DESC;

-- Label propagation for community detection
CALL algo.labelPropagation.stream()
YIELD nodeId, label
MATCH (p:Person) WHERE id(p) = nodeId
RETURN label AS community, collect(p.name) AS members;

-- Insert temporal data
INSERT (e:Event {
  name: 'Product Launch',
  scheduled: TIMESTAMP '2024-06-15 09:00:00-07:00',
  duration: DURATION 'PT2H30M'
});

-- Temporal queries
MATCH (e:Event)
WHERE e.scheduled >= CURRENT_TIMESTAMP
  AND e.scheduled <= CURRENT_TIMESTAMP + DURATION 'P7D'
RETURN e.name, e.scheduled;

-- Aggregate by time windows
MATCH (s:Sale)
WHERE s.timestamp >= DATE '2024-01-01'
RETURN
  $bucket_start /* no date_trunc(): bucket at write time or compare an explicit range */ AS month,
  count(*) AS sales_count,
  sum(s.amount) AS total_revenue
GROUP BY month
ORDER BY month;

-- Statistical analysis
MATCH (p:Product)-[:SOLD]->(s:Sale)
RETURN
  p.name,
  avg(s.price) AS mean_price,
  collect(s.price) /* stddev/variance parse but are never computed; reduce client-side */ AS price_stddev,
  collect(s.price) /* percentile_cont/_disc parse but are never computed; sort client-side */ AS median_price,
  collect(s.price) /* percentile_cont/_disc parse but are never computed; sort client-side */ AS p95_price;

-- Collect unique values into lists
MATCH (u:User)-[:PURCHASED]->(p:Product)
RETURN u.name, collect(DISTINCT p.category) AS categories;

-- String aggregation: collect() returns a LIST (there is no string_agg);
-- join it in the client if you need one string.
MATCH (t:Team)-[:HAS_MEMBER]->(m:Member)
RETURN t.name, collect(m.name) AS members;

-- Profile query execution
PROFILE
MATCH (p:Person)-[:KNOWS*2..3]->(friend)
WHERE p.city = 'San Francisco'
RETURN friend.name, count(*) AS connections
ORDER BY connections DESC
LIMIT 20;

-- Create composite index for complex queries
CREATE INDEX person_city_age ON Person(city, age);

-- Full-text search index
CREATE INDEX product_search ON Product(name, description) USING fulltext;

-- Use full-text search
MATCH (p:Product)
CALL geode.fts.search('product_search', 'wireless headphones')
YIELD node, score
MATCH (p:Product) WHERE id(p) = node
RETURN p.name, p.price;

-- Force index usage
MATCH (p:Person)
USING INDEX person_city_age
WHERE p.city = 'Austin' AND p.age > 25
RETURN p.name;

-- Disable index for full scan
MATCH (p:Person)
WITHOUT INDEX
RETURN count(*);

-- Parallel aggregation over large graph
MATCH (u:User)-[:PURCHASED]->(p:Product)
WITH p, count(u) AS buyers
WHERE buyers > 100
RETURN p.category, sum(buyers) AS total_buyers
GROUP BY p.category;

-- Bulk insert with UNWIND
UNWIND $users AS user_data
INSERT (u:User {
  id: user_data.id,
  name: user_data.name,
  email: user_data.email
});

-- Batch update
MATCH (p:Product)
WHERE p.price IS NOT NULL
SET p.price = p.price * 1.05  -- 5% price increase
RETURN count(*) AS updated;

-- Find paths with constraints on intermediate nodes
MATCH path = (start:Person {name: 'Alice'})
  -[:KNOWS*2..5 (r, n | n.active = true)]->(end:Person)
WHERE end.city = 'Seattle'
RETURN path, length(path) AS hops;

-- Complex pattern with multiple conditions
MATCH (author:Person)-[:WROTE]->(paper:Paper),
      (paper)-[:CITES]->(cited:Paper),
      (cited)<-[:WROTE]-(coauthor:Person)
WHERE author <> coauthor
  AND paper.year >= 2020
  AND cited.year < paper.year
RETURN author.name, coauthor.name, count(cited) AS citations
ORDER BY citations DESC;

-- Ingest the external rows as nodes, then join with a pattern
MERGE (o:Order {order_id: $order_id})
SET o.total = $total;

MATCH (p:Person {id: $user_id}), (o:Order {order_id: $order_id})
MERGE (p)-[:PLACED]->(o);

MATCH (p:Person)-[:PLACED]->(o:Order)
RETURN p.name, o.order_id, o.total;

-- Materialized views are not reachable from GQL: the grammar has no
-- MATERIALIZED keyword. Precompute the aggregate onto nodes instead:
MATCH (u:User)-[:PURCHASED]->(p:Product)
WITH p, count(*) AS purchases
SET p.purchase_count = purchases;

-- There is no materialized view to refresh or drop. Re-run the
-- precompute batch above to update the stored aggregate.

CREATE INDEX user_email ON User(email);
CREATE INDEX product_category_price ON Product(category, price);

-- Good: parameterized
MATCH (p:Person {id: $user_id})
RETURN p;

-- Avoid: literal values prevent plan caching
MATCH (p:Person {id: 'user123'})
RETURN p;

MATCH (p:Person)
RETURN p.name
ORDER BY p.created DESC
LIMIT 100
OFFSET $page_offset;

PROFILE
MATCH (p:Person)-[:KNOWS*3..5]->(friend)
RETURN friend.name;

-- Centralized security in database
-- Not expressible as a Geode policy predicate (only `=` against a string
-- literal compiles); precompute the decision into a property instead:
CALL geode.rls.add_attribute_policy('app', 'Label', 'access_tag', 'access_tag');

# In geode.conf
enable_rls = true
enable_parallel_query = true
max_query_parallelism = 8

-- Create a small sample graph (there is no sample-data loader procedure)
CREATE (alice:Person {name: 'Alice'}),
       (bob:Person {name: 'Bob'}),
       (alice)-[:KNOWS]->(bob);

-- Try shortest path
MATCH path = SHORTEST (a:Person)-[:KNOWS*]-(b:Person)
WHERE a.name = 'Alice' AND b.name = 'Bob'
RETURN path;

-- Test RLS
-- Not expressible as a Geode policy predicate (only `=` against a string
-- literal compiles); precompute the decision into a property instead:
CALL geode.rls.add_attribute_policy('app', 'Person', 'access_tag', 'access_tag');

PROFILE
MATCH (p:Product)<-[:PURCHASED]-(u:User)
WHERE p.category = 'Electronics'
RETURN p.name, count(u) AS buyers
ORDER BY buyers DESC;