This document is part of the D2RQ documentation.

generate-mapping: Auto-generating D2RQ mapping files

The generate-mapping tool creates a D2RQ mapping file by analyzing the schema of an existing database. This mapping file, called the default mapping, maps each table to a new RDFS class that is based on the table's name, and maps each column to a property based on the column's name. This mapping file can be used as-is or can be customized.


generate-mapping [-u user] [-p password] [-d driver]
        [-l script.sql] [--[skip-](schemas|tables|columns) list]
        [--w3c] [-v] [-b baseURI] [-o outfile.ttl]
        [--verbose] [--debug]

Connection parameters


JDBC connection URL for the database. Refer to your JDBC driver documentation for the format for your database engine. Examples:

MySQL: jdbc:mysql://servername/databasename
PostgreSQL: jdbc:postgresql://servername/databasename
Oracle: jdbc:oracle:thin:@servername:1521:databasename
HSQLDB: jdbc:hsqldb:mem:databasename (in-memory database)
Microsoft SQL Server: jdbc:sqlserver://servername;databaseName=databasename (due to the semicolon, the URL must be put in quotes when passed as a command-line argument in Linux/Unix shells)

If -l is present, then the JDBC URL can be omitted to load a SQL script into an in-memory HSQLDB database. See example.

-u user
The login name of the database user.
-p password
The password of the database user.
-d driver

The fully qualified Java class name of the database driver. For MySQL, PostgreSQL, and HSQLDB, this argument can be omitted as drivers are already included with D2RQ. For other databases, a driver has to be downloaded from the vendor or a third party. The jar file containing the JDBC driver class has to be in D2RQ's /lib/db-drivers/ directory. To find the driver class name, consult the driver documentation. Examples:

Oracle: oracle.jdbc.OracleDriver
Microsoft SQL Server:

-l script.sql
Load a SQL script before running the tool. Useful for initializing the connection and testing. The d2rq:startupSQLScript property of the database in the generated mapping will be initialized with the same value.
--schemas, --tables, --columns, --skip-schemas, --skip-tables, --skip-columns

Only map the specified schemas, tables or columns. The value of each argument is a comma-separated list of names. Schema names are of the form schema, table names of the form table or schema.table, and column names are table.column or schema.table.column.

Each dot-separated segment can be specified as a regular expression encloded between slashes.

If the value starts with “@”, then it is interpreted as a file name, and the list of names is loaded from the file. The file contains one name per line or comma-separated names. Examples follow:

Output parameters

Generate a mapping file that is compatible with W3C's Direct Mapping of Relational Data to RDF. This is an experimental feature and work in progress.
Generate an RDF Schema description of the vocabulary instead of a mapping file.
-o outfile.ttl
The generated mapping (or vocabulary if -v is used) will be stored in this file in Turtle syntax. If this parameter is omitted, the result will be written to standard out.
-b baseURI
The base URI is used to construct a vocabulary namespace that will automatically be served as Linked Data by D2R Server, following the convention http://baseURI/vocab/resource/. This should be the same base URI that is used when invoking the server. Defaults to http://localhost:2020/. For more information on vocabulary serving, see the D2RQ language specification.
Print extra progress log information.
Print all debug log information.


Local MySQL database

generate-mapping -u root jdbc:mysql:///iswc

Remote Oracle database

generate-mapping -u riccyg -p password -d oracle.jdbc.OracleDriver
    -o staffdb-mapping.ttl

This will save the mapping file into staffdb-mapping.ttl.

Initializing a temporary in-memory database form a SQL dump

generate-mapping -l db_dump.sql -o mapping.ttl

This will create a new temporary in-memory HSQLDB database, load the SQL dump db_dump.sql into the database, and create a mapping file mapping.ttl for it. The mapping file will reference the dump as a d2rq:startupSQLScript. This is a useful setup for quickly testing and debugging D2RQ.

Read a list of excluded columns from a file

generate-mapping -u root --skip-columns @bad-columns.txt jdbc:mysql:///iswc

The file bad-columns.txt would contain column names in schema.table.column or table.column notation, one per line.