Direct mapping example
university.employee
id | name | email
----+------------+---------------------
0 | Mary Smith | mary@smith.no
1 | Carl Green | carl_green@mail.net
university.student
id | name | supervisor
----+-------------+------------
0 | Peter Swan |
1 | Helen Brown | 0
2 | Nathan Case | 1
Resulting triples:
@prefix uni: <http://example.org/university/> .
@prefix xsd: <http://www.w3.org/2001/XMLSchema#> .
<http://example.org/university/student/id=2>
a uni:student ;
uni:student_id "2"^^xsd:int ;
uni:student_name "Nathan Case" ;
uni:student_ref_supervisor <http://example.org/university/employee/id=1> ;
uni:student_supervisor "1"^^xsd:int .
<http://example.org/university/employee/id=0>
a uni:employee ;
uni:employee_email "mary@smith.no" ;
uni:employee_id "0"^^xsd:int ;
uni:employee_name "Mary Smith" .
<http://example.org/university/student/id=0>
a uni:student ;
uni:student_id "0"^^xsd:int ;
uni:student_name "Peter Swan" .
<http://example.org/university/employee/id=1>
a uni:employee ;
uni:employee_email "carl_green@mail.net" ;
uni:employee_id "1"^^xsd:int ;
uni:employee_name "Carl Green" .
<http://example.org/university/student/id=1>
a uni:student ;
uni:student_id "1"^^xsd:int ;
uni:student_name "Helen Brown" ;
uni:student_ref_supervisor <http://example.org/university/employee/id=0> ;
uni:student_supervisor "0"^^xsd:int .
Direct Mappings with Lore/Triplelore: Example
IMPORT 'http://leifhka.org/lore/library/direct_mappings.lore';
-- Schema, relation/view name, base-IRI, prefix, forward-rules
mappings.direct_mapping('university', 'employee', 'http://example.org/', 'uni', true);
mappings.direct_mapping('university', 'student', 'http://example.org/', 'uni', true);
If university.employee and
university.student are Lore-relations or views:
IMPORT 'http://leifhka.org/lore/library/direct_mappings.lore';
mappings.direct_mapping('university', 'employee', 'http://example.org/', 'uni', true);
mappings.direct_mapping('university', 'student', 'http://example.org/', 'uni', true);
-- Schema, relation/view name, column
-- (use multiple lines for multi-column PK)
mappings.primary_key('university', 'employee', 'id');
mappings.primary_key('university', 'student', 'id');
-- Constraint name (make something up) but use same value for multi-column FK,
-- schema, relation/view name, fk-column, ref'ed schema and relation/view/table name, ref'ed column
mappings.foreign_key(1, 'university', 'student', 'supervisor', 'university', 'employee', 'id');
Foreign Data Wrappers: PostgreSQL Example
Customer-DB:
CREATE TABLE customer (
email text PRIMARY KEY,
name text,
city text,
country text
);
INSERT INTO customer VALUES
('erina22@somemail,com', 'Erin Amone', 'Oslo', 'Norway'),
('aliden@fmail,com', 'Ali Dent', 'Moss', 'Norway'),
('pennyla@mail,com', 'Penny Larsen', 'Aarhus', 'Denmark');
-- ...
Survey CSV:
sid, done_at, customer_email, happiness_score
1, 2023-01-02, supa_user@mail.com, 4
2, 2023-01-05, erina22@somemail.com, 2
3, 2023-02-03, aliden@fmail.org, 1
4, 2022-12-17, peterin@mail.net, 6
...
Foreign data wrappers:
-- Create the foreign data wrapper extension for other PostgreSQL-DBs:
-- Documentation: https://www.postgresql.org/docs/14/postgres-fdw.html
-- (this is already done for you in your IFI-databases)
CREATE EXTENSION postgres_fwd;
-- Then create a reference to the other database.
-- Lets assume "customer_db" is on host "123.4.5.6"
-- and port 5432:
CREATE SERVER customer_db_server
FOREGIGN DATA WRAPPER postgres_fdw
OPTIONS (host '123.4.5.6', port '5432', dbname 'customer_db');
-- Now need to create a user-mapping to gain access
-- to the other_db:
-- Lets assume we can use user with username 'cuser' and password 'pwd123':
CREATE USER MAPPING FOR master_db
SERVER customer_db_server
OPTIONS (user 'cuser', password 'pwd123');
-- Create schema to map foreign source into
CREATE SCHEMA customer; -- Could also be named something different
-- Map the foreign source's public-schema into the created schema
IMPORT FOREIGN SCHEMA public
FROM SERVER customer_db_server
INTO customer;
-- Can now run queries over customer-db as if it were
-- a normal schame in this database.
-- Now want to include a CSV-file, use the file FDW:
-- Documentation: https://www.postgresql.org/docs/14/file-fdw.html
CREATE EXTENSION file_fwd;
-- Create a server using the file_fwd
CREATE SERVER survey FOREIGN DATA WRAPPER file_fdw;
-- Create a table from the CSV-file surveys/user_survey.csv
CREATE FOREIGN TABLE survey (
sid int,
done_at date,
customer_email text,
happiness_score int
)
SERVER survey
OPTIONS ( filename 'surveys/user_survey.csv', format 'csv');
-- Finally, maybe we want to know more about the city population of unhappy customers
-- Include data from a SPARQL-query over DBPedia via the sparql_fdw
-- Documentation: https://github.com/sjstoelting/sparql_fdw
-- Installation is done via multicorn, see above link for more info
CREATE EXTENSION multicorn;
CREATE SERVER dbpedia FOREIGN DATA WRAPPER multicorn
OPTIONS ( wrapper 'sparqlfdw.SparqlForeignDataWrapper', endpoint 'http://dbpedia.org/sparql' );
CREATE FOREIGN TABLE city_data ( city text, population int )
SERVER dbpedia OPTIONS ( sparql '
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX dbo: <http://dbpedia.org/ontology/>
PREFIX dbp: <http://dbpedia.org/property/>
SELECT ?label ?population
WHERE {
?city a dbo:City ;
rdfs:label ?label ;
dbo:populationTotal ?population .
}');
-- Can now query for population of all unhappy customers:
SELECT ci.population
FROM customer.customer AS cu
JOIN survey AS s ON (cu.email = s.customer_email)
JOIN city_data AS ci USING (city)
WHERE s.happiness_score <= 3;
