jrgemignani commented on code in PR #2364: URL: https://github.com/apache/age/pull/2364#discussion_r3029296609
########## 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) Review Comment: AI response: agtype does not have a round(agtype, int) overload — this would fail with a function-not-found error. The values (e.g., 335.14612999999997) are deterministic IEEE 754 float8 representations, consistent across all platforms since PG12's extra_float_digits default. Not a flakiness risk. -- 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]
