DBIO-PostgreSQL-Age

 view release on metacpan or  search on metacpan

maint/docker/init-age-db.sh  view on Meta::CPAN

#
# Mounted into the container at /docker-entrypoint-initdb.d/10-age-db.sh
# by docker-compose.yml; the official postgres entrypoint picks it up
# automatically.

set -euo pipefail

psql -v ON_ERROR_STOP=1 -U postgres -d postgres <<'EOSQL'
  -- Dedicated test DB so the live suite can connect by name.
  SELECT 'CREATE DATABASE dbio_age_test'
    WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'dbio_age_test')\gexec
EOSQL

psql -v ON_ERROR_STOP=1 -U postgres -d dbio_age_test <<'EOSQL'
  -- AGE extension lives in shared_preload_libraries? No — it is a normal
  -- extension installed by the image's apt-get step. Just CREATE EXTENSION
  -- here and it is ready for the test suite to create graphs and run
  -- Cypher via ag_catalog.cypher().
  CREATE EXTENSION IF NOT EXISTS age;
EOSQL

misc/agent-context/.claude/skills/postgres-vector-graph/examples/sql/01_bootstrap.sql  view on Meta::CPAN

-- PostgreSQL 18 skill lab. Synthetic data; disposable database only.
-- Source-reviewed example, not integration-tested during package creation.
-- psql script: run with -X and ON_ERROR_STOP. Read README before executing.
\set ON_ERROR_STOP on

-- EFFECTS: creates extensions if absent; loads AGE on this backend.
-- Approval: database administrator; exact binaries must already be installed.
SELECT current_setting('server_version_num')::integer BETWEEN 180000 AND 189999 AS ok \gset
\if :ok
\else
  \echo 'This lab targets PG18. Revalidate and adapt before using another major.'
  DO $assert$ BEGIN RAISE EXCEPTION 'Lab assertion failed; see diagnostic above'; END; $assert$;
\endif
CREATE EXTENSION IF NOT EXISTS vector WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS age;
SELECT n.nspname = 'public' AS ok
FROM pg_extension e JOIN pg_namespace n ON n.oid=e.extnamespace
WHERE e.extname='vector' \gset
\if :ok
\else
  \echo 'Lab SQL expects vector in public; do not relocate an existing extension automatically.'
  DO $assert$ BEGIN RAISE EXCEPTION 'Lab assertion failed; see diagnostic above'; END; $assert$;
\endif
-- Self-hosted/direct or session-pooled lab. Managed preloading may replace LOAD;
-- do not grant superuser to an application to make this work.
LOAD 'age';
SET search_path = pg_catalog, ag_catalog, public;
SELECT extname, extversion FROM pg_extension WHERE extname IN ('age','vector');
-- AGE 1.8 JSONB-cast behavioral gate; it intentionally fails on unsupported builds.
SELECT ('{"gate":true}'::jsonb::ag_catalog.agtype)::jsonb = '{"gate":true}'::jsonb AS ok \gset
\if :ok
\else
  \echo 'Required AGE JSONB conversion behavior is missing.'
  DO $assert$ BEGIN RAISE EXCEPTION 'Lab assertion failed; see diagnostic above'; END; $assert$;
\endif

misc/agent-context/.claude/skills/postgres-vector-graph/examples/sql/07_atomic_rollback.sql  view on Meta::CPAN

-- Requires 02 and 04. Writes are rolled back. No persistent fixture change intended.
LOAD 'age';
SET search_path = pg_catalog, ag_catalog, public;
-- Ensure assertions cannot accidentally pass/fail due to a preexisting fixture key.
SELECT NOT EXISTS (
    SELECT 1 FROM skill_lab.document WHERE tenant_id=1 AND doc_id=999
) AND NOT EXISTS (
    SELECT 1 FROM ag_catalog.cypher('skill_graph', $$
      MATCH (d:document) WHERE d.tenant_id=1 AND d.doc_id=999 RETURN d.doc_id
    $$) AS g(doc_id ag_catalog.agtype)
) AS ok \gset
\if :ok
\else
  \echo 'Key 999 already exists; use a clean disposable lab.'
  DO $assert$ BEGIN RAISE EXCEPTION 'Lab assertion failed; see diagnostic above'; END; $assert$;
\endif
BEGIN;
INSERT INTO skill_lab.document (tenant_id,doc_id,title,body,model_id,embedding)
VALUES (1,999,'Rollback fixture','This row must not survive.','demo-v1','[1,0,0]');
SELECT * FROM ag_catalog.cypher('skill_graph', $$
  CREATE (d:document {tenant_id:1,doc_id:999}) RETURN d.doc_id
$$) AS g(doc_id ag_catalog.agtype);
ROLLBACK;
SELECT NOT EXISTS (
    SELECT 1 FROM skill_lab.document WHERE tenant_id=1 AND doc_id=999
) AND NOT EXISTS (
    SELECT 1 FROM ag_catalog.cypher('skill_graph', $$
      MATCH (d:document) WHERE d.tenant_id=1 AND d.doc_id=999 RETURN d.doc_id
    $$) AS g(doc_id ag_catalog.agtype)
) AS ok \gset
\if :ok
  \echo 'PASS: SQL row and AGE vertex are both absent after rollback.'
\else
  \echo 'FAIL: cross-model rollback did not match expectations.'
  DO $assert$ BEGIN RAISE EXCEPTION 'Lab assertion failed; see diagnostic above'; END; $assert$;
\endif

misc/agent-context/.claude/skills/postgres-vector-graph/examples/sql/09_rls_assertions.sql  view on Meta::CPAN

-- Requires OPTIONAL 08; run as authorized administrator able to SET ROLE.
-- Read-only checks as non-owner, non-bypass skill_lab_reader. LOAD happens first.
LOAD 'age';
SET search_path = pg_catalog, ag_catalog, public;
BEGIN READ ONLY;
SET LOCAL statement_timeout='10s';
SET LOCAL ROLE skill_lab_reader;
SET LOCAL app.tenant_id='1';
-- Positive and negative checks together avoid accepting a policy that hides all rows.
SELECT count(*)=6 AND count(*) FILTER (WHERE tenant_id<>1)=0 AS ok
FROM skill_lab.document \gset
\if :ok
\else
  \echo 'FAIL: relational tenant-one policy.'
  DO $assert$ BEGIN RAISE EXCEPTION 'Lab assertion failed; see diagnostic above'; END; $assert$;
\endif
SELECT count(*)=6 AND count(*) FILTER (WHERE tenant_id::bigint<>1)=0 AS ok
FROM ag_catalog.cypher('skill_graph', $$
    MATCH (d:document) RETURN d.tenant_id
$$) AS g(tenant_id ag_catalog.agtype) \gset
\if :ok
\else
  \echo 'FAIL: labeled Cypher tenant-one policy.'
  DO $assert$ BEGIN RAISE EXCEPTION 'Lab assertion failed; see diagnostic above'; END; $assert$;
\endif
SELECT count(*)=6 AND count(*) FILTER (WHERE tenant_id::bigint<>1)=0 AS ok
FROM ag_catalog.cypher('skill_graph', $$
    MATCH (d) RETURN d.tenant_id
$$) AS g(tenant_id ag_catalog.agtype) \gset
\if :ok
\else
  \echo 'FAIL: unlabeled Cypher policy; inspect base/label behavior.'
  DO $assert$ BEGIN RAISE EXCEPTION 'Lab assertion failed; see diagnostic above'; END; $assert$;
\endif
SELECT count(*)=4 AND count(*) FILTER (WHERE tenant_id::bigint<>1)=0 AS ok
FROM ag_catalog.cypher('skill_graph', $$
    MATCH ()-[r:related]->() RETURN r.tenant_id
$$) AS g(tenant_id ag_catalog.agtype) \gset
\if :ok
\else
  \echo 'FAIL: edge Cypher policy.'
  DO $assert$ BEGIN RAISE EXCEPTION 'Lab assertion failed; see diagnostic above'; END; $assert$;
\endif
SELECT count(*)=6 AND count(*) FILTER (
    WHERE (properties::jsonb ->> 'tenant_id')::bigint<>1
)=0 AS ok FROM skill_graph.document \gset
\if :ok
\else
  \echo 'FAIL: direct label SQL policy.'
  DO $assert$ BEGIN RAISE EXCEPTION 'Lab assertion failed; see diagnostic above'; END; $assert$;
\endif
-- The same document ID exists in both tenants. Context change must not retain T1.
SET LOCAL app.tenant_id='2';
SELECT count(*)=2 AND count(*) FILTER (WHERE tenant_id<>2)=0 AS ok
FROM skill_lab.document \gset
\if :ok
\else
  \echo 'FAIL: switched relational tenant context.'
  DO $assert$ BEGIN RAISE EXCEPTION 'Lab assertion failed; see diagnostic above'; END; $assert$;
\endif
SELECT count(*)=2 AND count(*) FILTER (WHERE tenant_id::bigint<>2)=0 AS ok
FROM ag_catalog.cypher('skill_graph', $$
    MATCH (d:document) RETURN d.tenant_id
$$) AS g(tenant_id ag_catalog.agtype) \gset
\if :ok
\else
  \echo 'FAIL: switched graph tenant context.'
  DO $assert$ BEGIN RAISE EXCEPTION 'Lab assertion failed; see diagnostic above'; END; $assert$;
\endif
SET LOCAL app.tenant_id='';
SELECT count(*)=0 AS ok FROM skill_lab.document \gset
\if :ok
\else
  \echo 'FAIL: empty tenant context must return no SQL rows.'
  DO $assert$ BEGIN RAISE EXCEPTION 'Lab assertion failed; see diagnostic above'; END; $assert$;
\endif
SELECT count(*)=0 AS ok FROM ag_catalog.cypher('skill_graph', $$
    MATCH (d) RETURN d.doc_id
$$) AS g(doc_id ag_catalog.agtype) \gset
\if :ok
\else
  \echo 'FAIL: empty tenant context must return no graph rows.'
  DO $assert$ BEGIN RAISE EXCEPTION 'Lab assertion failed; see diagnostic above'; END; $assert$;
\endif
ROLLBACK;
\echo 'PASS: selected RLS assertions; other query/write/pool paths remain untested.'