SELECT sparql.add_context('testctx', 'test context'); SELECT sparql.add_prefix('testctx', 'rdf', 'http://www.w3.org/2000/01/rdf-schema#'); CREATE SERVER virtuoso FOREIGN DATA WRAPPER rdf_fdw OPTIONS ( endpoint 'http://virtuoso:8890/sparql-auth', prefix_context 'testctx', batch_size '5'); CREATE USER MAPPING FOR postgres SERVER virtuoso OPTIONS (user 'dba', password '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 virtuoso OPTIONS ( log_sparql 'false', sparql 'SELECT * { GRAPH {?s ?p ?o} }', sparql_update_pattern 'GRAPH { ?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); SELECT subject, predicate, object FROM ft WHERE subject = '' ORDER BY predicate, object::text COLLATE "C"; /* 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 = ''; 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 = ''; /* 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; /* insert with RETURNING */ INSERT INTO ft (subject, predicate, object) VALUES ('', '', '"Universidade de Münster"@pt') RETURNING *; /* * Here we transfer data from a local static graph into a destination named * graph in virtuoso. 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 virtuoso 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 virtuoso OPTIONS ( log_sparql 'false', sparql $$ SELECT * { GRAPH { ?s ?p ?o . FILTER(?s=) FILTER(?p=) } } $$ ); CREATE FOREIGN TABLE rdbms_virtuoso ( s rdfnode OPTIONS (variable '?s'), p rdfnode OPTIONS (variable '?p'), o rdfnode OPTIONS (variable '?o') ) SERVER virtuoso OPTIONS ( log_sparql 'true', sparql $$ SELECT * { GRAPH { ?s ?p ?o . FILTER(?s=) FILTER(?p=) } } $$, sparql_update_pattern $$ GRAPH { ?s ?p ?o . } $$); INSERT INTO rdbms_virtuoso (s, p, o) SELECT s, p, o FROM rdbms_wikidata; SELECT * FROM rdbms_virtuoso ORDER BY o::text COLLATE "C"; COPY ( SELECT s, p , o FROM rdbms_virtuoso ORDER BY o::text COLLATE "C" ) TO STDOUT DELIMITER E' '; /*** Exception tests ***/ /* COPY .. FROM must fail - not supported */ COPY ft (subject, predicate, object) FROM STDIN WITH (DELIMITER ' '); "42"^^ "37"^^ \. /* 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'); 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'); /* ALTER COLUMN must fail - variable is required */ ALTER FOREIGN TABLE ft ALTER COLUMN predicate OPTIONS (DROP variable); INSERT INTO ft (subject, predicate, object) VALUES ('', '', 'http://dbpedia.org/resource/University'); \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'); /* 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'); /* invalid update_url - no URL */ ALTER FOREIGN TABLE ft OPTIONS (SET sparql_update_pattern '?s ?p o .'); ALTER SERVER virtuoso OPTIONS (ADD update_url 'foo'); INSERT INTO ft (subject, predicate, object) VALUES ('', '', 'http://dbpedia.org/resource/University'); /* invalid triple pattern - no sparql_update_pattern OPTION */ ALTER SERVER virtuoso OPTIONS (DROP update_url); ALTER FOREIGN TABLE ft OPTIONS (DROP sparql_update_pattern); INSERT INTO ft (subject, predicate, object) VALUES ('', '', 'http://dbpedia.org/resource/University'); /* 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'); /* invalid value - NULL value */ ALTER FOREIGN TABLE ft OPTIONS (SET sparql_update_pattern 'GRAPH { ?s ?p ?o . }'); INSERT INTO ft (subject, predicate, object) VALUES ('', '', NULL); /* invalid value - blank node */ INSERT INTO ft (subject, predicate, object) VALUES ('', '', sparql.bnode()); /* invalid credentials test */ ALTER USER MAPPING FOR postgres SERVER virtuoso OPTIONS (SET user 'dba', SET password 'foo'); -- wrong password INSERT INTO ft (subject, predicate, object) VALUES ('', '', '"foo"@de'); ALTER USER MAPPING FOR postgres SERVER virtuoso OPTIONS (SET user 'dba', SET password 'secret'); -- restore correct password /* read-only server: blocks INSERT regardless of triple pattern */ ALTER SERVER virtuoso OPTIONS (ADD readonly 'true'); INSERT INTO ft (subject, predicate, object) VALUES ('', '', 'http://dbpedia.org/resource/University'); /* 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'; ALTER FOREIGN TABLE ft OPTIONS (DROP readonly); /* read-only foreign table: server is writable, table explicitly read-only */ ALTER SERVER virtuoso OPTIONS (SET readonly 'false'); ALTER FOREIGN TABLE ft OPTIONS (ADD readonly 'true'); INSERT INTO ft (subject, predicate, object) VALUES ('', '', 'http://dbpedia.org/resource/University'); /* read-write server and foreign table, but no triple pattern */ ALTER SERVER virtuoso 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'); /* 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'); ALTER FOREIGN TABLE ft OPTIONS (SET sparql_update_pattern ''); -- empty pattern INSERT INTO ft (subject, predicate, object) VALUES ('', '', 'http://dbpedia.org/resource/University'); /* invalid data type */ ALTER FOREIGN TABLE ft OPTIONS (SET sparql_update_pattern 'GRAPH { ?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'); ALTER FOREIGN TABLE ft ALTER COLUMN predicate TYPE rdfnode; /* cleanup */ DELETE FROM ft; DELETE FROM rdbms_virtuoso; -- clear DELETE FROM rdbms_static; -- clear SELECT sparql.drop_context('testctx', true); DROP SERVER virtuoso CASCADE;