Steven Seida

1. D2RQ Blog Integration Lab Steven Seida

2. Data to RDF Integration Approaches* 1 of 2 *Summarized from Ch 9 of Semantiic Web Programming, 2009, by Hebeler et al..

3. Data to RDF Integration Approaches (2 of 2)

4. D2RQ Exercise • Use D2RQ to provide access to a blog database in Jena.

5. D2RQ Review • Database to RDF Query (D2RQ) • Converts database information into RDF triples • Generally wants to make classes and properties • 1. Create mapping file (auto-generates a starter) • 2. Use mapping file for conversion • -Can convert entire database • -Can leave database and provide • SPARQL endpoint

6. Configure MySQL with a Blog DB • Add “blog” database to your mySQL database • Connect as root (lakers), create database blog; exit; • Load an example blog database with a schema (and data) from file wordpressdump.sql at windows command prompt* • mysql -u root -plakers blog < wordpressdump.sql • This command must result in an empty/blank response. • Download and install d2r-server (d2r-server-0.7) to c:\extra\ • Select Download from http://www4.wiwiss.fu-berlin.de/bizer/d2rq/ • Create a mapping file. In the D2R server directory issue the command to generate a mapping file*: • Syntax is: generate-mapping [-u username] [-p password] [-d driverclass] [-o outfile.n3] [-b base uri] jdbcURL • generate-mapping -u root -p lakers jdbc:mysql://127.0.0.1/blog > blog.n3 • Specifying the mysql driver is optional • The result file is blog.n3 (inspect it) *A space or not a space after –p is very important and irritating.

7. D2RQ Concepts • Top Line of Mapping File: “map:” • Defaults to URL of output filename • -> Change this to anything (because will be a prefix), but you probably don’t want it to match Vocab. • “Vocab:” line -> This is your RDF ontology and instances prefix. Change to http://utdallas.semtech/2009/ • Two primary concepts in D2RQ are: • 1. ClassMap -> what classes to create • 2.PropertyBridge ->what relationships to create

8. MySQL tables in Blog • The blog schema is for WordPress (i.e. wp) • mysql> show tables; • +-----------------------+ • | Tables_in_blog | • +-----------------------+ • | wp_comments | • | wp_links | • | wp_options | • | wp_postmeta | • | wp_posts | • | wp_term_relationships | • | wp_term_taxonomy | • | wp_terms | • | wp_usermeta | • | wp_users | • +-----------------------+ • 10 rows in set (0.00 sec) Fields in wp_posts post_author post_date post_date_gmt post_content post_title post_excerpt post_status ... In looking at your mapping file, what is D2RQ saying should be a class? What does it say should be RDF properties?

9. Browsing with D2RQ • Open C:\extra\d2r-server-0.7\doc\index.html in a browser • Scroll down that page about half-way: We have finished step 3, “generate a mapping file,” now perform steps 4 and 5: • Start the server: d2r-server blog.n3 • Test the Server -> Open http://localhost:2020/ • in a web browser. • Look around at the data (in html). • -Who is the writer of the first two posts?

10. SPARQL Query in Jena • Make a copy of your Lab3_2 project • Make (and load) your model using: • Model d2rqModel = new ModelD2RQ(mappingFile); • -where mappingFile is the filename of the mapping file • -Also add d2rq-0.7.jar from the d2r-server*/lib area to your project • -Get rid of the model loading, timing, etc. • Then issue a SPARQL query and output (useful) results to XML for*: • All the property data (and values) about all users**. • All the property data about users that have issued postings. *Hovering your mouse over the rdf:type for instances in the localhost:2020 web page can show the actual type names. *Does the d2r-server you started on the preceding page need to be running for your SPARQL to work?

11. Other Questions • What steps are required to enable a database with D2RQ? • What Jena constructs are required to access a D2RQ store? • What do we do if we want joined tables to be the class instances (and properties)? • Does D2RQ provide us a persistence mechanism?

12. Backup

13. Optional: MySQL with Non-Root User • Inside mysql Add “blog” database with user “blogger” with password “noise” to your mySQL database • Connect as root, create database BLOG; • create user blogger identified by ‘noise’; create databse blog; • grant all privileges on blog.* to ‘blogger'@'localhost' identified by ‘noise' with grant option; • Load the blog database with a schema (and data) in file wordpressdump.sql (run at windows command line): • mysql -u blogger -pnoise blog < wordpressdump.sql • Install d2r-server to c:\extra\ (then run a shell in the d2r-server* directory) • Create a mapping file. In the D2R server directory issue the command to generate a mapping file: • generate-mapping [-u username] [-p password] [-d driverclass] [-o outfile.n3] [-b base uri] jdbcURL • generate-mapping -u blogger -p noise jdbc:mysql://127.0.0.1/blog > blog.n3 • The mysql driver is optional • The result file is blog.n3

14. How to dump a MySQL database • To Dump the blog database using credentials for user blogger (with password noise), run the following at a command line: • mysqldump -u blogger -pnoise blog >wordpressdump.sql • (Note: There is no space between –p and noise) • How to restore same database and credential, run the following: • mysql -u blogger -pnoise blog < wordpressdump.sql • (Note: that the blog database must already exist with blogger as a user and there is not space between –p and noise.)