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.

Usage

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]
        jdbcURL

Connection parameters

jdbcURL

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: com.microsoft.sqlserver.jdbc.SQLServerDriver

-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:

• --schema SCOTT (maps only tables in the SCOTT schema)
• --tables PERSONS,ORGS (maps only the PERSONS and ORGS tables)
• --skip-table TEMP_CACHE (skips the TEMP_CACHE table)
• --skip-columns /.*/.CHECKSUM (skips the CHECKSUM column of each table, if present)
• --skip-tables /BACKUP.*/i (skips tables backup1, BACKUP_2 and so on)
• --skip-tables @exclude.txt (reads a list of excluded table names from a file)

Output parameters

--w3c

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.

-v

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.

--verbose

Print extra progress log information.

--debug

Print all debug log information.

Examples

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 jdbc:oracle:thin:@ora.intranet.deri.ie:1521:staffdb

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.