src/ports/postgres/modules/graph/test/apsp.sql_in

/* ----------------------------------------------------------------------- *//**
 *
 * 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.
 *
 *//* ----------------------------------------------------------------------- */

DROP TABLE IF EXISTS vertex,"EDGE",out,out_summary,out_path,
	vertex_alt,edge_alt,out_alt,out_alot_summary,
	edge_gr,out_gr,out_gr_summary,out_gr_path,
	edge_gr2, out_gr2, out_gr2_summary;


CREATE TABLE vertex(
                  id INTEGER
                );

CREATE TABLE "EDGE"(
                  src INTEGER,
                  "DEST" INTEGER,
                  weight DOUBLE PRECISION
                );

INSERT INTO vertex VALUES
(0),
(1),
(2),
(3),
(4),
(5),
(6),
(7)
;
INSERT INTO "EDGE" VALUES
(0, 1, 1),
(0, 2, 1),
(0, 4, 10),
(1, 2, 2),
(1, 3, 10),
(2, 3, 1),
(2, 5, 1),
(2, 6, 3),
(3, 0, 1),
(4, 0, -2),
(5, 6, 1),
(6, 7, 1)
;


SELECT graph_apsp('vertex',NULL,'"EDGE"','dest="DEST"','out');

SELECT * FROM out ORDER BY src,"DEST";

SELECT assert(weight = 4, 'Wrong output in graph (APSP)')
FROM out WHERE src = 0 AND "DEST" = 7;
SELECT assert(parent = 6, 'Wrong parent in graph (APSP)')
FROM out WHERE src = 0 AND "DEST" = 7;

SELECT assert(weight = 11, 'Wrong output in graph (APSP)')
FROM out WHERE src = 3 AND "DEST" = 4;
SELECT assert(parent = 0, 'Wrong parent in graph (APSP)')
FROM out WHERE src = 3 AND "DEST" = 4;
-- Path
SELECT graph_apsp_get_path('out',0,7,'out_path');

-- Grouping
CREATE TABLE edge_gr AS
(SELECT *, 0 AS grp FROM "EDGE"
UNION
SELECT *, 1 AS grp FROM "EDGE" WHERE src < 6 AND "DEST" < 6
UNION
SELECT *, 2 AS grp FROM "EDGE" WHERE src < 6 AND "DEST" < 6
);

INSERT INTO edge_gr VALUES
(7,NULL,NULL,1),
(4,0,-20,2);

SELECT graph_apsp('vertex',NULL,'edge_gr','dest="DEST"','out_gr','grp');

SELECT assert(weight = 2, 'Wrong output in graph (APSP)')
	FROM out_gr WHERE src = 0 AND "DEST" = 5 AND grp = 1;
SELECT assert(parent = 2, 'Wrong output in graph (APSP)')
	FROM out_gr WHERE src = 0 AND "DEST" = 5 AND grp = 1;

-- Test for common column names in vertex and edge tables
DROP TABLE IF EXISTS out, out_summary;
ALTER TABLE vertex RENAME COLUMN id TO src;

SELECT graph_apsp('vertex','src','edge_gr','dest="DEST"','out','grp');
SELECT * FROM out ORDER BY src,"DEST";

DROP TABLE IF EXISTS out, out_summary;
ALTER TABLE vertex RENAME COLUMN src TO "DEST";

SELECT graph_apsp('vertex','"DEST"','edge_gr','dest="DEST"','out','grp');
SELECT * FROM out ORDER BY src,"DEST";

ALTER TABLE vertex RENAME COLUMN "DEST" TO id;

-- Test for bigint columns
CREATE TABLE v2 AS SELECT id::bigint FROM vertex;
CREATE TABLE e2 AS SELECT src::bigint, "DEST"::bigint, weight FROM "EDGE";

DROP TABLE IF EXISTS pg_temp.out2, pg_temp.out2_summary, pg_temp.out2_path;
SELECT graph_apsp('v2',NULL,'e2','dest="DEST"','pg_temp.out2');
SELECT graph_apsp_get_path('pg_temp.out2',0,7,'pg_temp.out2_path');

-- Test for infinite paths
DROP TABLE IF EXISTS out, out_summary, out_path;
DELETE FROM "EDGE" WHERE "DEST" = 7;

SELECT graph_apsp('vertex',NULL,'"EDGE"','dest="DEST"','out');

SELECT assert(count(*) = 0, 'Null parent in the out table') FROM
(SELECT * FROM out WHERE parent IS NULL)q1;

SELECT graph_apsp_get_path('out',0,5,'out_path');