\pset null '(null)' SELECT sparql.add_context('testctx', 'test context'); add_context ------------- (1 row) SELECT sparql.add_prefix('testctx', 'rdf', 'http://www.w3.org/2000/01/rdf-schema#'); add_prefix ------------ (1 row) CREATE SERVER qlever FOREIGN DATA WRAPPER rdf_fdw OPTIONS ( endpoint 'http://qlever:7001/update', -- this tests if the regular endpoint is used for updates prefix_context 'testctx', batch_size '5'); CREATE USER MAPPING FOR postgres SERVER qlever OPTIONS (token 'secret'); CREATE FOREIGN TABLE ft ( subject rdfnode OPTIONS (variable '?s'), predicate rdfnode OPTIONS (variable '?p'), foo rdfnode OPTIONS (variable '?foo'), -- will be ignored object rdfnode OPTIONS (variable '?o') ) SERVER qlever OPTIONS ( log_sparql 'false', sparql 'SELECT * {?s ?p ?o}', sparql_update_pattern '?s ?p ?o . ?s rdf:comment "added via rdf_fdw 🐘"^^.' ); INSERT INTO ft (subject, predicate, object) VALUES ('', '', 'http://dbpedia.org/resource/University'); INSERT INTO ft (subject, predicate, object) VALUES ('', '', 'http://dbpedia.org/resource/University'), ('', '', ''), ('', '', '""'), ('', '', '""@es'), ('', '', '"University of Münster"@en'), ('', '', '"Westfälische \"Wilhelms-Universität\" Münster"@de'), ('', '', '"Westfälische \nWilhelms-Universität\n Münster"@de'), ('', '', '"Westfälische .. Wilhelms-Universität . Münster"@de'), ('', '', '🐘'::rdfnode); /* Set the endpoint to the right URL and add update_url */ ALTER SERVER qlever OPTIONS (ADD update_url 'http://qlever:7001/update', SET endpoint 'http://qlever:7001/sparql'); SELECT subject, predicate, object FROM ft WHERE subject = '' ORDER BY predicate, object::text COLLATE "C"; subject | predicate | object -------------------------------+------------------------------------------------------+----------------------------------------------------- | | "🐘" | | "http://dbpedia.org/resource/University" | | "added via rdf_fdw 🐘" | | "" | | ""@es | | "University of Münster"@en | | "Westfälische + | | Wilhelms-Universität + | | Münster"@de | | "Westfälische .. Wilhelms-Universität . Münster"@de | | "Westfälische \"Wilhelms-Universität\" Münster"@de (9 rows) /* Test DEFAULT values handling */ ALTER FOREIGN TABLE ft ALTER COLUMN object SET DEFAULT '"default literal"'::rdfnode; INSERT INTO ft (subject, predicate) VALUES ('', ''); SELECT subject, predicate, object FROM ft WHERE predicate = ''; subject | predicate | object -------------------------------+-------------------------------------------------+------------------- | | "default literal" (1 row) ALTER FOREIGN TABLE ft ALTER COLUMN object DROP DEFAULT; /* insert large literal */ ALTER FOREIGN TABLE ft OPTIONS (SET log_sparql 'false'); -- disable logging to not flood the ouput file INSERT INTO ft (subject, predicate, object) VALUES ('', '', repeat('b', 1000000)::rdfnode); SELECT subject, predicate, sparql.strlen(object) FROM ft WHERE predicate = ''; subject | predicate | strlen -------------------------------+-------------------------------------------------+--------- | | 1000000 | | 15 (2 rows) /* bulk insert */ INSERT INTO ft (subject, predicate, object) SELECT sparql.iri('https://www.uni-muenster.de'), sparql.iri('http://www.w3.org/1999/02/22-rdf-syntax-ns#value'), i::rdfnode FROM generate_series(1,100) AS j (i); SELECT count(*) FROM ft; count ------- 111 (1 row) /* insert with RETURNING */ INSERT INTO ft (subject, predicate, object) VALUES ('', '', '"Universidade de Münster"@pt') RETURNING *; subject | predicate | foo | object -------------------------------+----------------------------------------------+--------+------------------------------ | | (null) | "Universidade de Münster"@pt (1 row) /* * Here we transfer data from a local static graph into a destination named * graph in qlever. This exercises the cross-FDW INSERT-SELECT code path * without depending on any external network service. * * The source graph is pre-loaded with * a fixed snapshot of Wikidata altLabel data for Q192490 (PostgreSQL). */ /* Load static source data into a local named graph */ CREATE FOREIGN TABLE rdbms_static ( s rdfnode OPTIONS (variable '?s'), p rdfnode OPTIONS (variable '?p'), o rdfnode OPTIONS (variable '?o') ) SERVER qlever OPTIONS ( log_sparql 'false', sparql 'SELECT * { GRAPH {?s ?p ?o} }', sparql_update_pattern 'GRAPH { ?s ?p ?o . }' ); INSERT INTO rdbms_static (s, p, o) VALUES ('', '', '"PG"@zh-CN'), ('', '', '"POSTGRE SQL"@vi'), ('', '', '"PgSQL"@pl'), ('', '', '"Postgre SQL"@sr'), ('', '', '"Postgre"@pl'), ('', '', '"Postgre"@sr'), ('', '', '"PostgreSQL project"@de'), ('', '', '"PostgreSQL, слободни софтвер"@sr'), ('', '', '"Postgres"@mul'), ('', '', '"Postgresql"@sr'), ('', '', '"pgsql"@pt-BR'), ('', '', '"postgres"@nl'), ('', '', '"پست گر اس کیوال"@fa'), ('', '', '"پستگر اسکیوال"@fa'), ('', '', '"পোস্টজিআরই"@bn'), ('', '', '"போசுகிரசு"@ta'), ('', '', '"โพสต์เกรส"@th'), ('', '', '"ポスグレ"@ja'), ('', '', '"ポストグレスキューエル"@ja'); /* Read from the static local graph — same code path as reading from a remote endpoint */ CREATE FOREIGN TABLE rdbms_wikidata ( s rdfnode OPTIONS (variable '?s'), p rdfnode OPTIONS (variable '?p'), o rdfnode OPTIONS (variable '?o') ) SERVER qlever OPTIONS ( log_sparql 'false', sparql $$ SELECT * { GRAPH { ?s ?p ?o . FILTER(?s=) FILTER(?p=) } } $$ ); CREATE FOREIGN TABLE rdbms_qlever ( s rdfnode OPTIONS (variable '?s'), p rdfnode OPTIONS (variable '?p'), o rdfnode OPTIONS (variable '?o') ) SERVER qlever OPTIONS ( log_sparql 'true', sparql $$ SELECT * { GRAPH { ?s ?p ?o . FILTER(?s=) FILTER(?p=) } } $$, sparql_update_pattern $$ GRAPH { ?s ?p ?o . } $$); INSERT INTO rdbms_qlever (s, p, o) SELECT s, p, o FROM rdbms_wikidata; INFO: SPARQL query sent to 'qlever': PREFIX rdf: INSERT DATA { GRAPH { "PG"@zh-CN . } }; INSERT DATA { GRAPH { "pgsql"@pt-BR . } }; INSERT DATA { GRAPH { "PgSQL"@pl . } }; INSERT DATA { GRAPH { "Postgre"@pl . } }; INSERT DATA { GRAPH { "Postgre"@sr . } }; INFO: SPARQL query sent to 'qlever': PREFIX rdf: INSERT DATA { GRAPH { "Postgre SQL"@sr . } }; INSERT DATA { GRAPH { "POSTGRE SQL"@vi . } }; INSERT DATA { GRAPH { "postgres"@nl . } }; INSERT DATA { GRAPH { "Postgres"@mul . } }; INSERT DATA { GRAPH { "Postgresql"@sr . } }; INFO: SPARQL query sent to 'qlever': PREFIX rdf: INSERT DATA { GRAPH { "PostgreSQL project"@de . } }; INSERT DATA { GRAPH { "PostgreSQL, слободни софтвер"@sr . } }; INSERT DATA { GRAPH { "پست گر اس کیوال"@fa . } }; INSERT DATA { GRAPH { "پستگر اسکیوال"@fa . } }; INSERT DATA { GRAPH { "পোস্টজিআরই"@bn . } }; INFO: SPARQL query sent to 'qlever': PREFIX rdf: INSERT DATA { GRAPH { "போசுகிரசு"@ta . } }; INSERT DATA { GRAPH { "โพสต์เกรส"@th . } }; INSERT DATA { GRAPH { "ポスグレ"@ja . } }; INSERT DATA { GRAPH { "ポストグレスキューエル"@ja . } }; SELECT * FROM rdbms_qlever ORDER BY o::text COLLATE "C"; INFO: SPARQL query sent to 'qlever': PREFIX rdf: SELECT ?s ?p ?o { GRAPH { ?s ?p ?o . FILTER(?s=) FILTER(?p=) } } INFO: SPARQL returned 19 records. s | p | o ------------------------------------------+------------------------------------------------+----------------------------------- | | "PG"@zh-CN | | "POSTGRE SQL"@vi | | "PgSQL"@pl | | "Postgre SQL"@sr | | "Postgre"@pl | | "Postgre"@sr | | "PostgreSQL project"@de | | "PostgreSQL, слободни софтвер"@sr | | "Postgres"@mul | | "Postgresql"@sr | | "pgsql"@pt-BR | | "postgres"@nl | | "پست گر اس کیوال"@fa | | "پستگر اسکیوال"@fa | | "পোস্টজিআরই"@bn | | "போசுகிரசு"@ta | | "โพสต์เกรส"@th | | "ポスグレ"@ja | | "ポストグレスキューエル"@ja (19 rows) COPY ( SELECT s, p , o FROM rdbms_qlever ORDER BY o::text COLLATE "C" ) TO STDOUT DELIMITER E' '; INFO: SPARQL query sent to 'qlever': PREFIX rdf: SELECT ?s ?p ?o { GRAPH { ?s ?p ?o . FILTER(?s=) FILTER(?p=) } } INFO: SPARQL returned 19 records. "PG"@zh-CN "POSTGRE\ SQL"@vi "PgSQL"@pl "Postgre\ SQL"@sr "Postgre"@pl "Postgre"@sr "PostgreSQL\ project"@de "PostgreSQL,\ слободни\ софтвер"@sr "Postgres"@mul "Postgresql"@sr "pgsql"@pt-BR "postgres"@nl "پست\ گر\ اس\ کیوال"@fa "پستگر\ اسکیوال"@fa "পোস্টজিআরই"@bn "போசுகிரசு"@ta "โพสต์เกรส"@th "ポスグレ"@ja "ポストグレスキューエル"@ja /*** Exception tests ***/ /* COPY .. FROM must fail - not supported */ COPY ft (subject, predicate, object) FROM STDIN WITH (DELIMITER ' '); ERROR: COPY FROM is not supported by rdf_fdw /* INSERT must fail - all columns must be of type rdfnode */ ALTER FOREIGN TABLE ft ALTER COLUMN predicate TYPE text; INSERT INTO ft (subject, predicate, object) VALUES ('', '', 'http://dbpedia.org/resource/University'); WARNING: the rdf_fdw FOREIGN TABLE "ft" has columns using native PostgreSQL types which are deprecated: predicate HINT: Use the "rdfnode" type instead. ERROR: invalid data type for INSERT on column "predicate" DETAIL: Only columns of type rdfnode can be used in INSERT operations. ALTER FOREIGN TABLE ft ALTER COLUMN predicate TYPE rdfnode; /* INSERT must fail - no column with the variable ?p */ ALTER FOREIGN TABLE ft ALTER COLUMN predicate OPTIONS (SET variable '?bar'); INSERT INTO ft (subject, predicate, object) VALUES ('', '', 'http://dbpedia.org/resource/University'); ERROR: SPARQL variable '?p' in 'sparql_update_pattern' is not mapped to any table column /* ALTER COLUMN must fail - variable is required */ ALTER FOREIGN TABLE ft ALTER COLUMN predicate OPTIONS (DROP variable); ERROR: required option 'variable' is missing INSERT INTO ft (subject, predicate, object) VALUES ('', '', 'http://dbpedia.org/resource/University'); ERROR: SPARQL variable '?p' in 'sparql_update_pattern' is not mapped to any table column \set VERBOSITY terse /* invalid sparql_update_pattern - no triple pattern */ ALTER FOREIGN TABLE ft ALTER COLUMN predicate OPTIONS (SET variable '?p'); ALTER FOREIGN TABLE ft OPTIONS (SET sparql_update_pattern '?s ?o .'); INSERT INTO ft (subject, predicate, object) VALUES ('', '', 'http://dbpedia.org/resource/University'); ERROR: 'sparql_update_pattern' contains no valid triple patterns /* invalid sparql_update_pattern - no variable */ ALTER FOREIGN TABLE ft OPTIONS (SET sparql_update_pattern 'foo .'); INSERT INTO ft (subject, predicate, object) VALUES ('', '', 'http://dbpedia.org/resource/University'); ERROR: 'sparql_update_pattern' contains no valid triple patterns /* invalid update_url - no URL */ ALTER FOREIGN TABLE ft OPTIONS (SET sparql_update_pattern '?s ?p o .'); ALTER SERVER qlever OPTIONS (SET update_url 'foo'); INSERT INTO ft (subject, predicate, object) VALUES ('', '', 'http://dbpedia.org/resource/University'); ERROR: invalid update_url: 'foo' /* invalid triple pattern - no sparql_update_pattern OPTION */ ALTER SERVER qlever OPTIONS (SET update_url 'http://qlever:7001/update', SET endpoint 'http://qlever:7001/sparql'); ALTER FOREIGN TABLE ft OPTIONS (DROP sparql_update_pattern); INSERT INTO ft (subject, predicate, object) VALUES ('', '', 'http://dbpedia.org/resource/University'); ERROR: foreign table "ft" does not allow inserts /* invalid triple pattern - empty sparql_update_pattern OPTION */ ALTER FOREIGN TABLE ft OPTIONS (ADD sparql_update_pattern ' '); INSERT INTO ft (subject, predicate, object) VALUES ('', '', 'http://dbpedia.org/resource/University'); ERROR: 'sparql_update_pattern' contains no valid triple patterns /* invalid value - NULL value */ ALTER FOREIGN TABLE ft OPTIONS (SET sparql_update_pattern '?s ?p ?o .'); INSERT INTO ft (subject, predicate, object) VALUES ('', '', NULL); ERROR: NULL value in column "object" violates RDF constraint /* invalid value - blank node */ INSERT INTO ft (subject, predicate, object) VALUES ('', '', sparql.bnode()); ERROR: blank nodes are not allowed in INSERT operations /* invalid credentials test */ ALTER USER MAPPING FOR postgres SERVER qlever OPTIONS (SET token 'bad_token'); -- wrong token INSERT INTO ft (subject, predicate, object) VALUES ('', '', '"foo"@de'); ERROR: bad request on server "qlever" (HTTP 400) ALTER USER MAPPING FOR postgres SERVER qlever OPTIONS (SET token 'secret'); -- restore correct token /* read-only server: blocks INSERT regardless of triple pattern */ ALTER SERVER qlever OPTIONS (ADD readonly 'true'); INSERT INTO ft (subject, predicate, object) VALUES ('', '', 'http://dbpedia.org/resource/University'); ERROR: foreign table "ft" does not allow inserts /* table overrides server: server is readonly but table explicitly sets readonly=false */ ALTER FOREIGN TABLE ft OPTIONS (ADD readonly 'false'); INSERT INTO ft (subject, predicate, object) VALUES ('', '', '"foo"@en'); -- succeeds: table override allows writes SELECT * FROM ft WHERE object = '"foo"@en'; subject | predicate | foo | object -------------------------------+---------------------------------------------------+--------+---------- | | (null) | "foo"@en (1 row) ALTER FOREIGN TABLE ft OPTIONS (DROP readonly); /* read-only foreign table: server is writable, table explicitly read-only */ ALTER SERVER qlever OPTIONS (SET readonly 'false'); ALTER FOREIGN TABLE ft OPTIONS (ADD readonly 'true'); INSERT INTO ft (subject, predicate, object) VALUES ('', '', 'http://dbpedia.org/resource/University'); ERROR: foreign table "ft" does not allow inserts /* read-write server and foreign table, but no triple pattern */ ALTER SERVER qlever OPTIONS (DROP readonly); ALTER FOREIGN TABLE ft OPTIONS (DROP readonly); ALTER FOREIGN TABLE ft OPTIONS (DROP sparql_update_pattern); INSERT INTO ft (subject, predicate, object) VALUES ('', '', 'http://dbpedia.org/resource/University'); ERROR: foreign table "ft" does not allow inserts /* invalid triple patterns */ ALTER FOREIGN TABLE ft OPTIONS (ADD sparql_update_pattern '?s ?p .'); -- missing object variable INSERT INTO ft (subject, predicate, object) VALUES ('', '', 'http://dbpedia.org/resource/University'); ERROR: 'sparql_update_pattern' contains no valid triple patterns ALTER FOREIGN TABLE ft OPTIONS (SET sparql_update_pattern ''); -- empty pattern ERROR: empty value in option 'sparql_update_pattern' INSERT INTO ft (subject, predicate, object) VALUES ('', '', 'http://dbpedia.org/resource/University'); ERROR: 'sparql_update_pattern' contains no valid triple patterns /* invalid data type */ ALTER FOREIGN TABLE ft OPTIONS (SET sparql_update_pattern '?s ?p ?o .'); -- restore correct pattern ALTER FOREIGN TABLE ft ALTER COLUMN predicate TYPE text; -- should fail, predicate column is now text, not rdfnode INSERT INTO ft (subject, predicate, object) VALUES ('', '', 'http://dbpedia.org/resource/University'); WARNING: the rdf_fdw FOREIGN TABLE "ft" has columns using native PostgreSQL types which are deprecated: predicate ERROR: invalid data type for INSERT on column "predicate" ALTER FOREIGN TABLE ft ALTER COLUMN predicate TYPE rdfnode; /* cleanup */ DELETE FROM ft; DELETE FROM rdbms_qlever; -- clear INFO: SPARQL query sent to 'qlever': PREFIX rdf: SELECT ?s ?p ?o { GRAPH { ?s ?p ?o . FILTER(?s=) FILTER(?p=) } } INFO: SPARQL returned 19 records. INFO: SPARQL query sent to 'qlever': PREFIX rdf: DELETE DATA { GRAPH { "PG"@zh-CN . } }; DELETE DATA { GRAPH { "pgsql"@pt-BR . } }; DELETE DATA { GRAPH { "PgSQL"@pl . } }; DELETE DATA { GRAPH { "Postgre"@pl . } }; DELETE DATA { GRAPH { "Postgre"@sr . } }; INFO: SPARQL query sent to 'qlever': PREFIX rdf: DELETE DATA { GRAPH { "Postgre SQL"@sr . } }; DELETE DATA { GRAPH { "POSTGRE SQL"@vi . } }; DELETE DATA { GRAPH { "postgres"@nl . } }; DELETE DATA { GRAPH { "Postgres"@mul . } }; DELETE DATA { GRAPH { "Postgresql"@sr . } }; INFO: SPARQL query sent to 'qlever': PREFIX rdf: DELETE DATA { GRAPH { "PostgreSQL project"@de . } }; DELETE DATA { GRAPH { "PostgreSQL, слободни софтвер"@sr . } }; DELETE DATA { GRAPH { "پست گر اس کیوال"@fa . } }; DELETE DATA { GRAPH { "پستگر اسکیوال"@fa . } }; DELETE DATA { GRAPH { "পোস্টজিআরই"@bn . } }; INFO: SPARQL query sent to 'qlever': PREFIX rdf: DELETE DATA { GRAPH { "போசுகிரசு"@ta . } }; DELETE DATA { GRAPH { "โพสต์เกรส"@th . } }; DELETE DATA { GRAPH { "ポスグレ"@ja . } }; DELETE DATA { GRAPH { "ポストグレスキューエル"@ja . } }; DELETE FROM rdbms_static; -- clear SELECT sparql.drop_context('testctx', true); drop_context -------------- (1 row) DROP SERVER qlever CASCADE; NOTICE: drop cascades to 5 other objects