test.sql

-- pg_ladybug test script
-- Tests the extension's basic functionality (pure SPI path) and,
-- if liblbug is available (via compile-time link), the Cypher path.
\set ON_ERROR_STOP on

CREATE EXTENSION IF NOT EXISTS pg_ladybug;

-- Set GUC for liblbug-dependent tests (port overridable via -v pgport=…)
\set pgport 5433
SET ladybug.pg_connstr = 'host=/var/run/postgresql port=' :pgport ' dbname=ladybug_test user=postgres';

-- List available functions
\df ladybug.*

-- ============================================================
-- Basic sql_query (pure SPI, no liblbug needed)
-- ============================================================
SELECT '=== sql_query test ===' AS info;

CREATE TEMP TABLE persons (
    id   SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age  INTEGER
);

INSERT INTO persons (name, age) VALUES
    ('Alice', 30),
    ('Bob',   25),
    ('Carol', 35),
    ('Dave',  28);

SELECT 'sql_query: count' AS info;
SELECT * FROM ladybug.sql_query('SELECT count(*)::int AS cnt FROM persons') AS t(cnt int);

SELECT 'sql_query: names' AS info;
SELECT * FROM ladybug.sql_query('SELECT name FROM persons ORDER BY name') AS t(name text);

-- ============================================================
-- register_node / _graph_meta (metadata only, no liblbug)
-- ============================================================
SELECT '=== register_node test ===' AS info;
SELECT ladybug.register_node(
    label      => 'Person',
    table_name => 'persons',
    id_column  => 'id'
);

SELECT 'graph_meta contents' AS info;
SELECT * FROM ladybug._graph_meta;

SELECT 'list_labels' AS info;
SELECT * FROM ladybug.list_labels();

-- ============================================================
-- explain / pushed_sql (needs liblbug at runtime)
-- ============================================================
SELECT '=== explain/pushed_sql tests (conditional on liblbug) ===' AS info;

-- These will fail if liblbug is not available or ladybug.pg_connstr
-- is not set. Wrap in a DO block with exception handling so the
-- test script succeeds either way.
DO $$
DECLARE
    plan_text text;
    sql_text  text;
BEGIN
    BEGIN
        plan_text := ladybug.explain('MATCH (n:Person) RETURN n.id');
        RAISE NOTICE 'EXPLAIN succeeded, plan length: %', length(plan_text);
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'EXPLAIN skipped (set ladybug.lib_path and ladybug.pg_connstr): %', SQLERRM;
    END;

    BEGIN
        sql_text := ladybug.pushed_sql('MATCH (n:Person) RETURN n.id');
        RAISE NOTICE 'pushed_sql succeeded: %', sql_text;
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'pushed_sql skipped (set ladybug.lib_path and ladybug.pg_connstr): %', SQLERRM;
    END;
END $$;

-- ============================================================
-- cypher (conditional on liblbug + pushed_sql)
-- ============================================================
SELECT '=== cypher test (conditional) ===' AS info;

DO $$
BEGIN
    BEGIN
        EXECUTE $q$
            SELECT * FROM ladybug.cypher('MATCH (n:Person) RETURN n.name AS name')
            AS t(name text)
        $q$;
        RAISE NOTICE 'cypher(MATCH Person) succeeded';
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'cypher skipped (set ladybug.lib_path and ladybug.pg_connstr): %', SQLERRM;
    END;
END $$;

-- ============================================================
-- Edge registration
-- ============================================================
SELECT '=== register_edge test ===' AS info;
CREATE TEMP TABLE knows (
    src_id  INTEGER NOT NULL,
    dst_id  INTEGER NOT NULL
);
INSERT INTO knows VALUES (1, 2), (2, 3);

SELECT ladybug.register_edge(
    label      => 'KNOWS',
    table_name => 'knows',
    from_col   => 'src_id',
    to_col     => 'dst_id'
);

SELECT 'edges in graph_meta:' AS info;
SELECT label, kind, table_name, props_json FROM ladybug._graph_meta
WHERE kind = 'edge';

-- ============================================================
-- In-place query tests (requires liblbug + ATTACH)
-- These test the bridge flow: Cypher -> plan -> SQL -> SPI
-- ============================================================
-- Drop temp tables from prior tests so they don't interfere
DROP TABLE IF EXISTS persons, knows;
SELECT '=== in-place query tests (conditional on liblbug) ===' AS info;

/*
 * These tests use the naming convention:
 *   node_*     -> Cypher node labels
 *   rel_*      -> Cypher relationship labels (FK-backed, scan-driven)
 *   csr_rel_*  -> reserved for CSR-materialized rel tables (not used here)
 *
 * They work with the existing bridge by:
 * 1. ATTACHing Postgres via the Ladybug postgres extension
 * 2. The extension auto-detects rel_* tables and registers
 *    them as relationship tables (RelGroupCatalogEntry)
 * 3. The ForeignJoinPushDownOptimizer pushes down the entire
 *    pattern as a single SQL JOIN query
 * 4. The bridge extracts that SQL and executes it via SPI
 *
 * When the modified extension isn't available, rel_* tables
 * are still registered as node tables, and the bridge's
 * extract_pushed_sql falls back to constructing SELECT *
 * queries from the SCAN_NODE_TABLE sections.
 */

-- SQL-pushdown patterns below mirror upstream 0.21.2 suite:
-- extensions/duckdb/test/test_files/sql_pushdown.test
--
-- The following tests use pre-existing tables in ladybug_test:
--   node_person (id, name, age): Alice/35, Bob/25, Carol/45, Dave/28
--   node_city   (id, name): NYC, SF
--   rel_knows   (id, src_id, dst_id, since): Alice->Bob/2020,
--     Bob->Carol/2021, Alice->Carol/2019, Carol->Dave/2022
--   rel_likes   (id, src_id, dst_id, score): Alice->Bob/5,
--     Bob->Carol/8, Carol->Dave/3
--   rel_livesin (id, src_id, dst_id): Alice->NYC, Bob->SF, Carol->NYC

DO $$
DECLARE
    result_count int;
BEGIN
    -- Test simple node MATCH
    BEGIN
        EXECUTE $q$
            SELECT count(*)::int FROM ladybug.cypher(
                'MATCH (n:node_person) RETURN n.name, n.age'
            ) AS t(name text, age int)
        $q$ INTO result_count;
        RAISE NOTICE 'node_person MATCH returned % rows', result_count;
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'node_person MATCH skipped: %', SQLERRM;
    END;

    -- Test node MATCH with WHERE
    BEGIN
        EXECUTE $q$
            SELECT count(*)::int FROM ladybug.cypher(
                'MATCH (n:node_person) WHERE n.age > 28 RETURN n.name, n.age'
            ) AS t(name text, age int)
        $q$ INTO result_count;
        RAISE NOTICE 'node_person WHERE MATCH returned % rows', result_count;
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'node_person WHERE MATCH skipped: %', SQLERRM;
    END;

    -- Test node MATCH with ORDER BY
    BEGIN
        EXECUTE $q$
            SELECT count(*)::int FROM ladybug.cypher(
                'MATCH (n:node_person) RETURN n.name, n.age ORDER BY n.age'
            ) AS t(name text, age int)
        $q$ INTO result_count;
        RAISE NOTICE 'node_person ORDER BY MATCH returned % rows', result_count;
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'node_person ORDER BY MATCH skipped: %', SQLERRM;
    END;

    -- Test relationship pattern (requires rel_* auto-detection)
    -- This only works when the Ladybug extension has been modified
    -- to detect rel_* tables as relationship tables.
    BEGIN
        EXECUTE $q$
            SELECT count(*)::int FROM ladybug.cypher(
                'MATCH (a:node_person)-[r:rel_knows]->(b:node_person) RETURN a.name, b.name, r.since'
            ) AS t(a_name text, b_name text, since int)
        $q$ INTO result_count;
        RAISE NOTICE 'rel_knows pattern MATCH returned % rows', result_count;
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'rel_knows pattern MATCH skipped (requires rel_* extension support): %', SQLERRM;
    END;

    -- Two-hop with node filter (upstream TwoHopNodeFilterPushdown, expect 2)
    BEGIN
        EXECUTE $q$
            SELECT count(*)::int FROM ladybug.cypher(
                'MATCH (a:node_person)-[r1:rel_knows]->(b:node_person)-[r2:rel_likes]->(c:node_person) WHERE a.age > 30 RETURN a.name, b.name, c.name'
            ) AS t(a text, b text, c text)
        $q$ INTO result_count;
        RAISE NOTICE 'two-hop node-filter MATCH returned % rows', result_count;
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'two-hop node-filter MATCH skipped: %', SQLERRM;
    END;

    -- Two-hop with edge filters (upstream TwoHopEdgeFilterPushdown, expect 1)
    BEGIN
        EXECUTE $q$
            SELECT count(*)::int FROM ladybug.cypher(
                'MATCH (a:node_person)-[r1:rel_knows]->(b:node_person)-[r2:rel_likes]->(c:node_person) WHERE r1.since >= 2020 AND r2.score > 4 RETURN a.name, c.name'
            ) AS t(a text, c text)
        $q$ INTO result_count;
        RAISE NOTICE 'two-hop edge-filter MATCH returned % rows', result_count;
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'two-hop edge-filter MATCH skipped: %', SQLERRM;
    END;

    -- Two-hop mixed node labels person->city (upstream, expect 3)
    BEGIN
        EXECUTE $q$
            SELECT count(*)::int FROM ladybug.cypher(
                'MATCH (a:node_person)-[r1:rel_knows]->(b:node_person)-[r2:rel_livesin]->(c:node_city) RETURN a.name, c.name'
            ) AS t(a text, c text)
        $q$ INTO result_count;
        RAISE NOTICE 'two-hop mixed-label MATCH returned % rows', result_count;
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'two-hop mixed-label MATCH skipped: %', SQLERRM;
    END;

    -- Aggregation with ORDER BY (upstream AggregationOrderByPushdown,
    -- expect 3 groups: Alice|2, Bob|1, Carol|1)
    BEGIN
        EXECUTE $q$
            SELECT count(*)::int FROM ladybug.cypher(
                'MATCH (a:node_person)-[r:rel_knows]->(b:node_person) RETURN a.name, count(*) AS cnt ORDER BY cnt DESC, a.name'
            ) AS t(a text, cnt bigint)
        $q$ INTO result_count;
        RAISE NOTICE 'aggregation ORDER BY MATCH returned % rows', result_count;
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'aggregation ORDER BY MATCH skipped: %', SQLERRM;
    END;

    -- Recursive variable-length path (upstream RecursiveCTEPushdown, expect 8)
    BEGIN
        EXECUTE $q$
            SELECT count(*)::int FROM ladybug.cypher(
                'MATCH (a:node_person)-[e:rel_knows*1..3]->(b:node_person) RETURN b.name'
            ) AS t(b text)
        $q$ INTO result_count;
        RAISE NOTICE 'recursive path MATCH returned % rows', result_count;
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'recursive path MATCH skipped: %', SQLERRM;
    END;

    -- Recursive path with edge filter (upstream, expect 1: Bob)
    BEGIN
        EXECUTE $q$
            SELECT count(*)::int FROM ladybug.cypher(
                'MATCH (a:node_person)-[e:rel_knows*1..2 {since: 2020}]->(b:node_person) RETURN b.name'
            ) AS t(b text)
        $q$ INTO result_count;
        RAISE NOTICE 'recursive edge-filter MATCH returned % rows', result_count;
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'recursive edge-filter MATCH skipped: %', SQLERRM;
    END;
END $$;

-- ============================================================
-- Cleanup
-- ============================================================
SELECT '=== cleanup ===' AS info;
SELECT ladybug.reset_graph();
SELECT 'graph cleared:', count(*) FROM ladybug._graph_meta;

SELECT '=== ALL TESTS PASSED ===' AS status;