jrgemignani commented on code in PR #2364: URL: https://github.com/apache/age/pull/2364#discussion_r3029348664
########## regress/sql/age_upgrade.sql: ########## @@ -0,0 +1,295 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +-- +-- Extension upgrade regression test +-- +-- This test validates the upgrade template (age--<VER>--y.y.y.sql) by: +-- 1. Installing AGE at the current version (built from the initial +-- version-bump commit's SQL — the "day-one release" state) +-- 2. Creating three graphs with multiple labels, edges, GIN indexes, +-- and numeric properties that serve as integrity checksums +-- 3. Upgrading to a synthetic "next" version via the stamped template +-- 4. Verifying all data, structure, and checksums survived the upgrade +-- +-- The Makefile builds: +-- age--<CURR>.sql from the initial version-bump commit (git history) +-- age--<NEXT>.sql from current HEAD's sql/sql_files +-- age--<CURR>--<NEXT>.sql stamped from the upgrade template +-- +-- All version discovery is dynamic — no hardcoded versions anywhere. +-- This test is version-agnostic and works on any branch for any version. +-- + +LOAD 'age'; +SET search_path TO ag_catalog; + +-- Step 1: Clean up any state left by prior tests, then drop AGE entirely. +-- The --load-extension=age flag installed AGE at the current (default) version. +-- We need to remove it so we can cleanly re-create for this test. +SELECT drop_graph(name, true) FROM ag_graph ORDER BY name; +DROP EXTENSION age; + +-- Step 2: Verify we have multiple installable versions. +SELECT count(*) > 1 AS has_upgrade_path +FROM pg_available_extension_versions WHERE name = 'age'; + +-- Step 3: Install AGE at the default version (the initial release SQL). +CREATE EXTENSION age; +SELECT extversion IS NOT NULL AS version_installed FROM pg_extension WHERE extname = 'age'; + +-- Step 4: Create three test graphs with diverse labels, edges, and data. +LOAD 'age'; +SET search_path TO ag_catalog, "$user", public; + +-- +-- Graph 1: "company" — organization hierarchy with numeric checksums. +-- Labels: Employee, Department, Project +-- Edges: WORKS_IN, MANAGES, ASSIGNED_TO +-- Each vertex has a "val" property (float) for checksum validation. +-- +SELECT create_graph('company'); + +SELECT * FROM cypher('company', $$ + CREATE (e1:Employee {name: 'Alice', role: 'VP', val: 3.14159}) + CREATE (e2:Employee {name: 'Bob', role: 'Manager', val: 2.71828}) + CREATE (e3:Employee {name: 'Charlie', role: 'Engineer', val: 1.41421}) + CREATE (e4:Employee {name: 'Diana', role: 'Engineer', val: 1.73205}) + CREATE (d1:Department {name: 'Engineering', budget: 500000, val: 42.0}) + CREATE (d2:Department {name: 'Research', budget: 300000, val: 17.5}) + CREATE (p1:Project {name: 'Atlas', priority: 1, val: 99.99}) + CREATE (p2:Project {name: 'Beacon', priority: 2, val: 88.88}) + CREATE (p3:Project {name: 'Cipher', priority: 3, val: 77.77}) + CREATE (e1)-[:WORKS_IN {since: 2019}]->(d1) + CREATE (e2)-[:WORKS_IN {since: 2020}]->(d1) + CREATE (e3)-[:WORKS_IN {since: 2021}]->(d1) + CREATE (e4)-[:WORKS_IN {since: 2022}]->(d2) + CREATE (e1)-[:MANAGES {level: 1}]->(e2) + CREATE (e2)-[:MANAGES {level: 2}]->(e3) + CREATE (e3)-[:ASSIGNED_TO {hours: 40}]->(p1) + CREATE (e3)-[:ASSIGNED_TO {hours: 20}]->(p2) + CREATE (e4)-[:ASSIGNED_TO {hours: 30}]->(p2) + CREATE (e4)-[:ASSIGNED_TO {hours: 10}]->(p3) + RETURN 'company graph created' +$$) AS (result agtype); + +-- GIN index on Employee properties in company graph +CREATE INDEX company_employee_gin ON company."Employee" USING GIN (properties); + +-- +-- Graph 2: "network" — social network with weighted edges. +-- Labels: User, Post +-- Edges: FOLLOWS, AUTHORED, LIKES +-- +SELECT create_graph('network'); + +SELECT * FROM cypher('network', $$ + CREATE (u1:User {handle: '@alpha', score: 1000.01}) + CREATE (u2:User {handle: '@beta', score: 2000.02}) + CREATE (u3:User {handle: '@gamma', score: 3000.03}) + CREATE (u4:User {handle: '@delta', score: 4000.04}) + CREATE (u5:User {handle: '@epsilon', score: 5000.05}) + CREATE (p1:Post {title: 'Hello World', views: 150}) + CREATE (p2:Post {title: 'Graph Databases 101', views: 890}) + CREATE (p3:Post {title: 'AGE is awesome', views: 2200}) + CREATE (u1)-[:FOLLOWS {weight: 0.9}]->(u2) + CREATE (u2)-[:FOLLOWS {weight: 0.8}]->(u3) + CREATE (u3)-[:FOLLOWS {weight: 0.7}]->(u4) + CREATE (u4)-[:FOLLOWS {weight: 0.6}]->(u5) + CREATE (u5)-[:FOLLOWS {weight: 0.5}]->(u1) + CREATE (u1)-[:AUTHORED]->(p1) + CREATE (u2)-[:AUTHORED]->(p2) + CREATE (u3)-[:AUTHORED]->(p3) + CREATE (u4)-[:LIKES]->(p1) + CREATE (u5)-[:LIKES]->(p2) + CREATE (u1)-[:LIKES]->(p3) + CREATE (u2)-[:LIKES]->(p3) + RETURN 'network graph created' +$$) AS (result agtype); + +-- GIN indexes on network graph +CREATE INDEX network_user_gin ON network."User" USING GIN (properties); +CREATE INDEX network_post_gin ON network."Post" USING GIN (properties); + +-- +-- Graph 3: "routes" — geographic routing with precise coordinates. +-- Labels: City, Airport +-- Edges: ROAD, FLIGHT +-- Coordinates use precise decimals that are easy to checksum. +-- +SELECT create_graph('routes'); + +SELECT * FROM cypher('routes', $$ + CREATE (c1:City {name: 'Portland', lat: 45.5152, lon: -122.6784, pop: 652503}) + CREATE (c2:City {name: 'Seattle', lat: 47.6062, lon: -122.3321, pop: 749256}) + CREATE (c3:City {name: 'Vancouver', lat: 49.2827, lon: -123.1207, pop: 631486}) + CREATE (a1:Airport {code: 'PDX', elev: 30.5}) + CREATE (a2:Airport {code: 'SEA', elev: 131.7}) + CREATE (a3:Airport {code: 'YVR', elev: 4.3}) + CREATE (c1)-[:ROAD {distance_km: 279.5, toll: 0.0}]->(c2) + CREATE (c2)-[:ROAD {distance_km: 225.3, toll: 5.0}]->(c3) + CREATE (c1)-[:ROAD {distance_km: 502.1, toll: 5.0}]->(c3) + CREATE (a1)-[:FLIGHT {distance_km: 229.0, duration_min: 55}]->(a2) + CREATE (a2)-[:FLIGHT {distance_km: 198.0, duration_min: 50}]->(a3) + CREATE (a1)-[:FLIGHT {distance_km: 426.0, duration_min: 75}]->(a3) + RETURN 'routes graph created' +$$) AS (result agtype); + +-- GIN index on routes graph +CREATE INDEX routes_city_gin ON routes."City" USING GIN (properties); + +-- Step 5: Record pre-upgrade integrity checksums. +-- These sums use the "val" / "score" / coordinate properties as fingerprints. + +-- company: sum of all val properties (should be a precise known value) +SELECT * FROM cypher('company', $$ + MATCH (n) WHERE n.val IS NOT NULL RETURN sum(n.val) +$$) AS (company_val_sum_before agtype); + +-- network: sum of all score properties +SELECT * FROM cypher('network', $$ + MATCH (n:User) RETURN sum(n.score) +$$) AS (network_score_sum_before agtype); + +-- routes: sum of all latitude values +SELECT * FROM cypher('routes', $$ + MATCH (c:City) RETURN sum(c.lat) +$$) AS (routes_lat_sum_before agtype); + +-- Total vertex and edge counts across all three graphs +SELECT sum(cnt)::int AS total_vertices_before FROM ( + SELECT count(*) AS cnt FROM cypher('company', $$ MATCH (n) RETURN n $$) AS (n agtype) + UNION ALL + SELECT count(*) FROM cypher('network', $$ MATCH (n) RETURN n $$) AS (n agtype) + UNION ALL + SELECT count(*) FROM cypher('routes', $$ MATCH (n) RETURN n $$) AS (n agtype) +) sub; + +SELECT sum(cnt)::int AS total_edges_before FROM ( + SELECT count(*) AS cnt FROM cypher('company', $$ MATCH ()-[e]->() RETURN e $$) AS (e agtype) + UNION ALL + SELECT count(*) FROM cypher('network', $$ MATCH ()-[e]->() RETURN e $$) AS (e agtype) + UNION ALL + SELECT count(*) FROM cypher('routes', $$ MATCH ()-[e]->() RETURN e $$) AS (e agtype) +) sub; + +-- Count of distinct labels (ag_label entries) across all graphs +SELECT count(*)::int AS total_labels_before +FROM ag_label al JOIN ag_graph ag ON al.graph = ag.graphid +WHERE ag.name <> '_ag_catalog'; + +-- Step 6: Upgrade AGE to the synthetic next version via the stamped template. +DO $$ +DECLARE next_ver text; +BEGIN + SELECT version INTO next_ver + FROM pg_available_extension_versions + WHERE name = 'age' AND version <> ( + SELECT default_version FROM pg_available_extensions WHERE name = 'age' + ) + ORDER BY string_to_array(version, '.')::int[] DESC + LIMIT 1; + + IF next_ver IS NULL THEN + RAISE EXCEPTION 'No next version available for upgrade test'; + END IF; + + EXECUTE format('ALTER EXTENSION age UPDATE TO %L', next_ver); +END; +$$; + +-- Step 7: Confirm version changed. +SELECT installed_version <> default_version AS upgraded_past_default +FROM pg_available_extensions WHERE name = 'age'; + +-- Step 8: Verify all data survived — reload and recheck. +LOAD 'age'; +SET search_path TO ag_catalog, "$user", public; + +-- Repeat integrity checksums — must match pre-upgrade values exactly. +SELECT * FROM cypher('company', $$ + MATCH (n) WHERE n.val IS NOT NULL RETURN sum(n.val) +$$) AS (company_val_sum_after agtype); + +SELECT * FROM cypher('network', $$ + MATCH (n:User) RETURN sum(n.score) +$$) AS (network_score_sum_after agtype); + +SELECT * FROM cypher('routes', $$ + MATCH (c:City) RETURN sum(c.lat) +$$) AS (routes_lat_sum_after agtype); + +SELECT sum(cnt)::int AS total_vertices_after FROM ( + SELECT count(*) AS cnt FROM cypher('company', $$ MATCH (n) RETURN n $$) AS (n agtype) + UNION ALL + SELECT count(*) FROM cypher('network', $$ MATCH (n) RETURN n $$) AS (n agtype) + UNION ALL + SELECT count(*) FROM cypher('routes', $$ MATCH (n) RETURN n $$) AS (n agtype) +) sub; + +SELECT sum(cnt)::int AS total_edges_after FROM ( + SELECT count(*) AS cnt FROM cypher('company', $$ MATCH ()-[e]->() RETURN e $$) AS (e agtype) + UNION ALL + SELECT count(*) FROM cypher('network', $$ MATCH ()-[e]->() RETURN e $$) AS (e agtype) + UNION ALL + SELECT count(*) FROM cypher('routes', $$ MATCH ()-[e]->() RETURN e $$) AS (e agtype) +) sub; + +SELECT count(*)::int AS total_labels_after +FROM ag_label al JOIN ag_graph ag ON al.graph = ag.graphid +WHERE ag.name <> '_ag_catalog'; + +-- Step 9: Verify specific structural queries across all three graphs. + +-- company: management chain +SELECT * FROM cypher('company', $$ + MATCH (boss:Employee)-[:MANAGES*]->(report:Employee) + RETURN boss.name, report.name + ORDER BY boss.name, report.name +$$) AS (boss agtype, report agtype); + +-- network: circular follow chain (proves full cycle survived) +SELECT * FROM cypher('network', $$ + MATCH (a:User)-[:FOLLOWS]->(b:User) + RETURN a.handle, b.handle + ORDER BY a.handle +$$) AS (follower agtype, followed agtype); + +-- routes: all flights with distances (proves edge properties intact) +SELECT * FROM cypher('routes', $$ + MATCH (a:Airport)-[f:FLIGHT]->(b:Airport) + RETURN a.code, b.code, f.distance_km + ORDER BY a.code, b.code +$$) AS (origin agtype, dest agtype, dist agtype); + +-- Step 10: Verify GIN indexes still exist after upgrade. +SELECT indexname FROM pg_indexes +WHERE tablename IN ('Employee', 'User', 'Post', 'City') Review Comment: Updated -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
