regress/expected/age_load.out

/*
 * 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.
 */
\! rm -rf /tmp/age/age_load
\! mkdir -p /tmp/age
\! cp -r regress/age_load/data /tmp/age/age_load
LOAD 'age';
SET search_path TO ag_catalog;
-- Create a country using CREATE clause
SELECT create_graph('agload_test_graph');
NOTICE:  graph "agload_test_graph" has been created
 create_graph 
--------------
 
(1 row)

SELECT * FROM cypher('agload_test_graph', $$CREATE (n:Country {__id__:1}) RETURN n$$) as (n agtype);
                                        n                                         
----------------------------------------------------------------------------------
 {"id": 844424930131969, "label": "Country", "properties": {"__id__": 1}}::vertex
(1 row)

--
-- Load countries with id
--
SELECT load_labels_from_file('agload_test_graph', 'Country',
    'age_load/countries.csv', true);
 load_labels_from_file 
-----------------------
 
(1 row)

-- Sequence should be equal to max entry id i.e. 248
SELECT currval('agload_test_graph."Country_id_seq"')=248;
 ?column? 
----------
 t
(1 row)

-- Should error out on loading the same file again due to duplicate id
SELECT load_labels_from_file('agload_test_graph', 'Country',
    'age_load/countries.csv', true);
ERROR:  Cannot insert duplicate vertex id: 844424930131970
HINT:  Entry id 2 is already used
--
-- Load cities with id
--
-- Should create City label automatically and load cities
SELECT load_labels_from_file('agload_test_graph', 'City',
    'age_load/cities.csv', true);
NOTICE:  VLabel "City" has been created
 load_labels_from_file 
-----------------------
 
(1 row)

-- Sequence should be equal to max entry id i.e. 146941
SELECT currval('agload_test_graph."City_id_seq"')=146941;
 ?column? 
----------
 t
(1 row)

-- Should error out on loading the same file again due to duplicate id
SELECT load_labels_from_file('agload_test_graph', 'City',
    'age_load/cities.csv', true);
ERROR:  Cannot insert duplicate vertex id: 1125899906842777
HINT:  Entry id 153 is already used
--
-- Load edges -- Connects cities to countries
--
-- Should error out for using vertex label
SELECT load_edges_from_file('agload_test_graph', 'Country',
    'age_load/edges.csv');
ERROR:  label "Country" already exists as edge label
SELECT create_elabel('agload_test_graph','has_city');
NOTICE:  ELabel "has_city" has been created
 create_elabel 
---------------
 
(1 row)

SELECT load_edges_from_file('agload_test_graph', 'has_city',
     'age_load/edges.csv');
 load_edges_from_file 
----------------------
 
(1 row)

-- Sequence should be equal to number of edges loaded i.e. 72485
SELECT currval('agload_test_graph."has_city_id_seq"')=72485;
 ?column? 
----------
 t
(1 row)

-- Should error out for using edge label
SELECT load_labels_from_file('agload_test_graph', 'has_city',
     'age_load/cities.csv');
ERROR:  label "has_city" already exists as vertex label
SELECT table_catalog, table_schema, lower(table_name) as table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'agload_test_graph' ORDER BY table_name ASC;
   table_catalog    |   table_schema    |    table_name    | table_type 
--------------------+-------------------+------------------+------------
 contrib_regression | agload_test_graph | _ag_label_edge   | BASE TABLE
 contrib_regression | agload_test_graph | _ag_label_vertex | BASE TABLE
 contrib_regression | agload_test_graph | city             | BASE TABLE
 contrib_regression | agload_test_graph | country          | BASE TABLE
 contrib_regression | agload_test_graph | has_city         | BASE TABLE
(5 rows)

SELECT COUNT(*) FROM agload_test_graph."Country";
 count 
-------
    54
(1 row)

SELECT COUNT(*) FROM agload_test_graph."City";
 count 
-------
 72485
(1 row)

SELECT COUNT(*) FROM agload_test_graph."has_city";
 count 
-------
 72485
(1 row)

SELECT COUNT(*) FROM cypher('agload_test_graph', $$MATCH(n) RETURN n$$) as (n agtype);
 count 
-------
 72539
(1 row)

SELECT COUNT(*) FROM cypher('agload_test_graph', $$MATCH (a)-[e]->(b) RETURN e$$) as (n agtype);
 count 
-------
 72485
(1 row)

--
-- Load countries and cities without id
--
-- Should load countries in Country label without error since it should use sequence now
SELECT load_labels_from_file('agload_test_graph', 'Country',
    'age_load/countries.csv', false);
 load_labels_from_file 
-----------------------
 
(1 row)

SELECT create_vlabel('agload_test_graph','Country2');
NOTICE:  VLabel "Country2" has been created
 create_vlabel 
---------------
 
(1 row)

SELECT load_labels_from_file('agload_test_graph', 'Country2',
                             'age_load/countries.csv', false);
 load_labels_from_file 
-----------------------
 
(1 row)

SELECT create_vlabel('agload_test_graph','City2');
NOTICE:  VLabel "City2" has been created
 create_vlabel 
---------------
 
(1 row)

SELECT load_labels_from_file('agload_test_graph', 'City2',
                             'age_load/cities.csv', false);
 load_labels_from_file 
-----------------------
 
(1 row)

SELECT COUNT(*) FROM agload_test_graph."Country2";
 count 
-------
    53
(1 row)

SELECT COUNT(*) FROM agload_test_graph."City2";
 count 
-------
 72485
(1 row)

SELECT id FROM agload_test_graph."Country" LIMIT 10;
       id        
-----------------
 844424930131969
 844424930131970
 844424930131971
 844424930131974
 844424930131983
 844424930131989
 844424930131990
 844424930131996
 844424930132002
 844424930132023
(10 rows)

SELECT id FROM agload_test_graph."Country2" LIMIT 10;
        id        
------------------
 1688849860263937
 1688849860263938
 1688849860263939
 1688849860263940
 1688849860263941
 1688849860263942
 1688849860263943
 1688849860263944
 1688849860263945
 1688849860263946
(10 rows)

-- Should return 2 rows for Country with same properties, but different ids
SELECT * FROM cypher('agload_test_graph', $$MATCH(n:Country {iso2 : 'BE'})
    RETURN id(n), n.name, n.iso2 $$) as ("id(n)" agtype, "n.name" agtype, "n.iso2" agtype);
      id(n)      |  n.name   | n.iso2 
-----------------+-----------+--------
 844424930131990 | "Belgium" | "BE"
 844424930132223 | "Belgium" | "BE"
(2 rows)

-- Should return 1 row
SELECT * FROM cypher('agload_test_graph', $$MATCH(n:Country2 {iso2 : 'BE'})
    RETURN id(n), n.name, n.iso2 $$) as ("id(n)" agtype, "n.name" agtype, "n.iso2" agtype);
      id(n)       |  n.name   | n.iso2 
------------------+-----------+--------
 1688849860263942 | "Belgium" | "BE"
(1 row)

-- Should return 2 rows for Country with same properties, but different ids
SELECT * FROM cypher('agload_test_graph', $$MATCH(n:Country {iso2 : 'AT'})
    RETURN id(n), n.name, n.iso2 $$) as ("id(n)" agtype, "n.name" agtype, "n.iso2" agtype);
      id(n)      |  n.name   | n.iso2 
-----------------+-----------+--------
 844424930131983 | "Austria" | "AT"
 844424930132221 | "Austria" | "AT"
(2 rows)

-- Should return 1 row
SELECT * FROM cypher('agload_test_graph', $$MATCH(n:Country2 {iso2 : 'AT'})
    RETURN id(n), n.name, n.iso2 $$) as ("id(n)" agtype, "n.name" agtype, "n.iso2" agtype);
      id(n)       |  n.name   | n.iso2 
------------------+-----------+--------
 1688849860263940 | "Austria" | "AT"
(1 row)

-- Should return 2 rows for Country with same properties, but different ids
SELECT * FROM cypher('agload_test_graph', $$
    MATCH (u:Country {region : "Europe"})
    WHERE u.name =~ 'Cro.*'
    RETURN id(u), u.name, u.region
$$) AS ("id(u)" agtype, result_1 agtype, result_2 agtype);
      id(u)      | result_1  | result_2 
-----------------+-----------+----------
 844424930132023 | "Croatia" | "Europe"
 844424930132226 | "Croatia" | "Europe"
(2 rows)

-- There shouldn't be any duplicates
SELECT * FROM cypher('agload_test_graph', $$return graph_stats('agload_test_graph')$$) as (a agtype);
                                            a                                             
------------------------------------------------------------------------------------------
 {"graph": "agload_test_graph", "num_loaded_edges": 72485, "num_loaded_vertices": 145130}
(1 row)

SELECT drop_graph('agload_test_graph', true);
NOTICE:  drop cascades to 7 other objects
DETAIL:  drop cascades to table agload_test_graph._ag_label_vertex
drop cascades to table agload_test_graph._ag_label_edge
drop cascades to table agload_test_graph."Country"
drop cascades to table agload_test_graph."City"
drop cascades to table agload_test_graph.has_city
drop cascades to table agload_test_graph."Country2"
drop cascades to table agload_test_graph."City2"
NOTICE:  graph "agload_test_graph" has been dropped
 drop_graph 
------------
 
(1 row)

--
-- Test property type conversion
--
-- vertex: load as agtype
-- Should create graph and label automatically
SELECT load_labels_from_file('agload_conversion', 'Person1', 'age_load/conversion_vertices.csv', true, true);
NOTICE:  graph "agload_conversion" has been created
NOTICE:  VLabel "Person1" has been created
 load_labels_from_file 
-----------------------
 
(1 row)

SELECT * FROM cypher('agload_conversion', $$ MATCH (n:Person1) RETURN properties(n) $$) as (a agtype);
                                         a                                          
------------------------------------------------------------------------------------
 {"id": 1, "bool": true, "__id__": 1, "string": "John Smith", "numeric": 1}
 {"id": 2, "bool": false, "__id__": 2, "string": "John", "numeric": -2}
 {"id": 3, "bool": true, "__id__": 3, "string": "John Smith", "numeric": 1.4}
 {"id": 4, "bool": false, "__id__": 4, "string": "John", "numeric": -10000000000.0}
 {"id": 5, "bool": false, "__id__": 5, "string": null, "numeric": 0}
 {"id": 6, "bool": false, "__id__": 6, "string": "nUll", "numeric": 3.14}
(6 rows)

-- vertex: load as string
SELECT create_vlabel('agload_conversion','Person2');
NOTICE:  VLabel "Person2" has been created
 create_vlabel 
---------------
 
(1 row)

SELECT load_labels_from_file('agload_conversion', 'Person2', 'age_load/conversion_vertices.csv', true, false);
 load_labels_from_file 
-----------------------
 
(1 row)

SELECT * FROM cypher('agload_conversion', $$ MATCH (n:Person2) RETURN properties(n) $$) as (a agtype);
                                          a                                          
-------------------------------------------------------------------------------------
 {"id": "1", "bool": "true", "__id__": 1, "string": "John Smith", "numeric": "1"}
 {"id": "2", "bool": "false", "__id__": 2, "string": "John", "numeric": "-2"}
 {"id": "3", "bool": "true", "__id__": 3, "string": "John Smith", "numeric": "1.4"}
 {"id": "4", "bool": "false", "__id__": 4, "string": "\"John\"", "numeric": "-1e10"}
 {"id": "5", "bool": "false", "__id__": 5, "string": "null", "numeric": "0"}
 {"id": "6", "bool": "false", "__id__": 6, "string": "nUll", "numeric": "3.14"}
(6 rows)

-- edge: load as agtype
SELECT create_elabel('agload_conversion','Edges1');
NOTICE:  ELabel "Edges1" has been created
 create_elabel 
---------------
 
(1 row)

SELECT load_edges_from_file('agload_conversion', 'Edges1', 'age_load/conversion_edges.csv', true);
 load_edges_from_file 
----------------------
 
(1 row)

SELECT * FROM cypher('agload_conversion', $$ MATCH ()-[e:Edges1]->() RETURN properties(e) $$) as (a agtype);
                              a                               
--------------------------------------------------------------
 {"bool": true, "string": "John Smith", "numeric": 1}
 {"bool": false, "string": "John", "numeric": -2}
 {"bool": true, "string": "John Smith", "numeric": 1.4}
 {"bool": false, "string": "John", "numeric": -10000000000.0}
 {"bool": false, "string": null, "numeric": 0}
 {"bool": false, "string": "nUll", "numeric": 3.14}
(6 rows)

-- edge: load as string
SELECT create_elabel('agload_conversion','Edges2');
NOTICE:  ELabel "Edges2" has been created
 create_elabel 
---------------
 
(1 row)

SELECT load_edges_from_file('agload_conversion', 'Edges2', 'age_load/conversion_edges.csv', false);
 load_edges_from_file 
----------------------
 
(1 row)

SELECT * FROM cypher('agload_conversion', $$ MATCH ()-[e:Edges2]->() RETURN properties(e) $$) as (a agtype);
                              a                              
-------------------------------------------------------------
 {"bool": "true", "string": "John Smith", "numeric": "1"}
 {"bool": "false", "string": "John", "numeric": "-2"}
 {"bool": "true", "string": "John Smith", "numeric": "1.4"}
 {"bool": "false", "string": "\"John\"", "numeric": "-1e10"}
 {"bool": "false", "string": "null", "numeric": "0"}
 {"bool": "false", "string": "nUll", "numeric": "3.14"}
(6 rows)

--
-- Check sandbox
--
-- check null file name
SELECT load_labels_from_file('agload_conversion', 'Person1', NULL, true, true);
ERROR:  file path must not be NULL
SELECT load_edges_from_file('agload_conversion', 'Edges1', NULL, true);
ERROR:  file path must not be NULL
-- check no file name
SELECT load_labels_from_file('agload_conversion', 'Person1', '', true, true);
ERROR:  file name cannot be zero length
SELECT load_edges_from_file('agload_conversion', 'Edges1', '', true);
ERROR:  file name cannot be zero length
-- check for file/path does not exist
SELECT load_labels_from_file('agload_conversion', 'Person1', 'age_load_xxx/conversion_vertices.csv', true, true);
ERROR:  File or path does not exist [/tmp/age/age_load_xxx/conversion_vertices.csv]
SELECT load_edges_from_file('agload_conversion', 'Edges1', 'age_load_xxx/conversion_edges.csv', true);
ERROR:  File or path does not exist [/tmp/age/age_load_xxx/conversion_edges.csv]
SELECT load_labels_from_file('agload_conversion', 'Person1', 'age_load/conversion_vertices.txt', true, true);
ERROR:  File or path does not exist [/tmp/age/age_load/conversion_vertices.txt]
SELECT load_edges_from_file('agload_conversion', 'Edges1', 'age_load/conversion_edges.txt', true);
ERROR:  File or path does not exist [/tmp/age/age_load/conversion_edges.txt]
-- check wrong extension
\! touch /tmp/age/age_load/conversion_vertices.txt
\! touch /tmp/age/age_load/conversion_edges.txt
SELECT load_labels_from_file('agload_conversion', 'Person1', 'age_load/conversion_vertices.txt', true, true);
ERROR:  You can only load files with extension [.csv].
SELECT load_edges_from_file('agload_conversion', 'Edges1', 'age_load/conversion_edges.txt', true);
ERROR:  You can only load files with extension [.csv].
-- check outside sandbox directory
SELECT load_labels_from_file('agload_conversion', 'Person1', '../../etc/passwd', true, true);
ERROR:  You can only load files located in [/tmp/age/].
SELECT load_edges_from_file('agload_conversion', 'Edges1', '../../etc/passwd', true);
ERROR:  You can only load files located in [/tmp/age/].
--
-- Cleanup
--
SELECT drop_graph('agload_conversion', true);
NOTICE:  drop cascades to 6 other objects
DETAIL:  drop cascades to table agload_conversion._ag_label_vertex
drop cascades to table agload_conversion._ag_label_edge
drop cascades to table agload_conversion."Person1"
drop cascades to table agload_conversion."Person2"
drop cascades to table agload_conversion."Edges1"
drop cascades to table agload_conversion."Edges2"
NOTICE:  graph "agload_conversion" has been dropped
 drop_graph 
------------
 
(1 row)

--
-- Test security and permissions
--
SELECT create_graph('agload_security');
NOTICE:  graph "agload_security" has been created
 create_graph 
--------------
 
(1 row)

SELECT create_vlabel('agload_security', 'Person1');
NOTICE:  VLabel "Person1" has been created
 create_vlabel 
---------------
 
(1 row)

SELECT create_vlabel('agload_security', 'Person2');
NOTICE:  VLabel "Person2" has been created
 create_vlabel 
---------------
 
(1 row)

SELECT create_elabel('agload_security', 'SecEdge');
NOTICE:  ELabel "SecEdge" has been created
 create_elabel 
---------------
 
(1 row)

--
-- Test 1: File read permission (pg_read_server_files role)
--
-- Create a user without pg_read_server_files role
CREATE USER load_test_user;
GRANT USAGE ON SCHEMA ag_catalog TO load_test_user;
-- This should fail because load_test_user doesn't have pg_read_server_files
SET ROLE load_test_user;
SELECT load_labels_from_file('agload_security', 'Person1', 'age_load/conversion_vertices.csv', true);
ERROR:  permission denied to LOAD from a file
DETAIL:  Only roles with privileges of the "pg_read_server_files" role may LOAD from a file.
SELECT load_edges_from_file('agload_security', 'SecEdge', 'age_load/conversion_edges.csv');
ERROR:  permission denied to LOAD from a file
DETAIL:  Only roles with privileges of the "pg_read_server_files" role may LOAD from a file.
RESET ROLE;
-- Grant pg_read_server_files and try again - should fail on table permission now
GRANT pg_read_server_files TO load_test_user;
--
-- Test 2: Table INSERT permission (ACL_INSERT)
--
-- User has file read permission but no INSERT on the label table
SET ROLE load_test_user;
SELECT load_labels_from_file('agload_security', 'Person1', 'age_load/conversion_vertices.csv', true);
ERROR:  permission denied for table Person1
SELECT load_edges_from_file('agload_security', 'SecEdge', 'age_load/conversion_edges.csv');
ERROR:  permission denied for table SecEdge
RESET ROLE;
-- Grant INSERT permission and try again - should succeed
GRANT USAGE ON SCHEMA agload_security TO load_test_user;
GRANT INSERT ON agload_security."Person1" TO load_test_user;
GRANT INSERT ON agload_security."SecEdge" TO load_test_user;
GRANT UPDATE ON SEQUENCE agload_security."Person1_id_seq" TO load_test_user;
GRANT UPDATE ON SEQUENCE agload_security."SecEdge_id_seq" TO load_test_user;
GRANT SELECT ON ag_catalog.ag_label TO load_test_user;
GRANT SELECT ON ag_catalog.ag_graph TO load_test_user;
SET ROLE load_test_user;
SELECT load_labels_from_file('agload_security', 'Person1', 'age_load/conversion_vertices.csv', true);
 load_labels_from_file 
-----------------------
 
(1 row)

SELECT load_edges_from_file('agload_security', 'SecEdge', 'age_load/conversion_edges.csv');
 load_edges_from_file 
----------------------
 
(1 row)

RESET ROLE;
-- Verify data was loaded
SELECT COUNT(*) FROM agload_security."Person1";
 count 
-------
     6
(1 row)

SELECT COUNT(*) FROM agload_security."SecEdge";
 count 
-------
     6
(1 row)

-- cleanup
DELETE FROM agload_security."Person1";
DELETE FROM agload_security."SecEdge";
--
-- Test 3: Row-Level Security (RLS)
--
-- Enable RLS on the label tables
ALTER TABLE agload_security."Person1" ENABLE ROW LEVEL SECURITY;
ALTER TABLE agload_security."SecEdge" ENABLE ROW LEVEL SECURITY;
-- Switch to load_test_user
SET ROLE load_test_user;
-- Loading should fail when RLS is enabled
SELECT load_labels_from_file('agload_security', 'Person1', 'age_load/conversion_vertices.csv', true);
ERROR:  LOAD from file is not supported with row-level security
HINT:  Use Cypher CREATE clause instead.
SELECT load_edges_from_file('agload_security', 'SecEdge', 'age_load/conversion_edges.csv');
ERROR:  LOAD from file is not supported with row-level security
HINT:  Use Cypher CREATE clause instead.
RESET ROLE;
-- Disable RLS and try again - should succeed
ALTER TABLE agload_security."Person1" DISABLE ROW LEVEL SECURITY;
ALTER TABLE agload_security."SecEdge" DISABLE ROW LEVEL SECURITY;
SELECT load_labels_from_file('agload_security', 'Person1', 'age_load/conversion_vertices.csv', true);
 load_labels_from_file 
-----------------------
 
(1 row)

SELECT load_edges_from_file('agload_security', 'SecEdge', 'age_load/conversion_edges.csv');
 load_edges_from_file 
----------------------
 
(1 row)

-- Verify data was loaded
SELECT COUNT(*) FROM agload_security."Person1";
 count 
-------
     6
(1 row)

SELECT COUNT(*) FROM agload_security."SecEdge";
 count 
-------
     6
(1 row)

-- cleanup
DELETE FROM agload_security."Person1";
DELETE FROM agload_security."SecEdge";
--
-- Test 4: Constraint checking (CHECK constraint)
--
-- Add constraint on vertex properties - fail if bool property is false
ALTER TABLE agload_security."Person1" ADD CONSTRAINT check_bool_true
    CHECK ((properties->>'"bool"')::boolean = true);
-- This should fail - constraint violation
SELECT load_labels_from_file('agload_security', 'Person1', 'age_load/conversion_vertices.csv', true);
ERROR:  new row for relation "Person1" violates check constraint "check_bool_true"
DETAIL:  Failing row contains (844424930131970, {"id": "2", "bool": "false", "__id__": 2, "string": "John", "num...).
-- Add constraint on edge properties - fail if bool property is false
ALTER TABLE agload_security."SecEdge" ADD CONSTRAINT check_bool_true
    CHECK ((properties->>'"bool"')::boolean = true);
-- This should fail - some edges have bool = false
SELECT load_edges_from_file('agload_security', 'SecEdge', 'age_load/conversion_edges.csv');
ERROR:  new row for relation "SecEdge" violates check constraint "check_bool_true"
DETAIL:  Failing row contains (1407374883553294, 844424930131969, 1125899906842625, {"bool": "false", "string": "John", "numeric": "-2"}).
-- cleanup
ALTER TABLE agload_security."Person1" DROP CONSTRAINT check_bool_true;
ALTER TABLE agload_security."SecEdge" DROP CONSTRAINT check_bool_true;
--
-- Cleanup
--
REVOKE ALL ON agload_security."Person1" FROM load_test_user;
REVOKE ALL ON agload_security."SecEdge" FROM load_test_user;
REVOKE ALL ON SEQUENCE agload_security."Person1_id_seq" FROM load_test_user;
REVOKE ALL ON SEQUENCE agload_security."SecEdge_id_seq" FROM load_test_user;
REVOKE ALL ON ag_catalog.ag_label FROM load_test_user;
REVOKE ALL ON ag_catalog.ag_graph FROM load_test_user;
REVOKE ALL ON SCHEMA agload_security FROM load_test_user;
REVOKE ALL ON SCHEMA ag_catalog FROM load_test_user;
REVOKE pg_read_server_files FROM load_test_user;
DROP USER load_test_user;
SELECT drop_graph('agload_security', true);
NOTICE:  drop cascades to 5 other objects
DETAIL:  drop cascades to table agload_security._ag_label_vertex
drop cascades to table agload_security._ag_label_edge
drop cascades to table agload_security."Person1"
drop cascades to table agload_security."Person2"
drop cascades to table agload_security."SecEdge"
NOTICE:  graph "agload_security" has been dropped
 drop_graph 
------------
 
(1 row)

--
-- End
--