Running Sedna XML Database on OS X with a MySQL ODBC Connection

Overview

Let's begin with a quick introduction in case you found this page by coincidence and you'd like to learn more.  Sedna is an open source XML Database that is licensed under Apache License 2.0.  An XML Database can store, query, and update XML data natively using XQuery FLWOR expressions. This is particularly useful if you're working with semi-structured data. Aside from being able to manage a native XML Database, Sedna can also connect via ODBC to other databases and expose their data in a simple XML format.  This can be especially useful if you want to convert a relational database, such as MySQL, to an XML Database, such as Sedna.  That should cover the basics.  The final part is that this guide uses OS X 10.5 x86 (Leopard), which may or may not matter to you.  Finally, you'll probably need to install the OS X development tools since we'll need to compile the Sedna source code to enable SQL support (something about GPL code contamination or other).  Now, let's go!

Build Sedna from Source

1. Download the most recent source file from the Sedna download page. I used sedna-3.1.73-src-darwin-i386.tar.gz.
2. Once downloaded, unzip it into a folder.
3. Open the Terminal application to get command line prompt and change directories to go into the unzipped Sedna folder.
4. I recommend you look at the INSTALL file to set the custom build options you like.  If not, I used the once listed below.  Just execute each statement from the command the line in the terminal window:
   export ACTIVE_CONFIGURATION=Release
export ENABLE_TRIGGERS=1
export EL_DEBUG=0
export SQL_CONNECTION=1
export SEDNA_INSTALL=~/Applications
5. Next run the build command: make
6. Sit back and relax.  The built takes about 10 minutes.
7. Now install the binaries by running: make install
8. This will install the Sedna binaries under ~/Applications/sedna/bin

Sedna is not ready to rumble, but we still need to install the ODBC drivers to connect to our MySQL database.

Install ODBC Drivers for MySQL

Next stop is to install the ODBC drivers required by Sedna to connect to MySQL database.  Note that you don't need the databae to be actually on the same machine.  You only need the ODBC drivers on the same machine as Sedna.

1. Download the most recent ODBC drivers for OS X x86 from the MySQL Connector/ODBC page.  I used mysql-connector-odbc-5.1.5-osx10.4-x86-32bit.
2. Open the Disk Image file (.dmg) and install the drivers.
3. Open Finder and go to Applications/Utilities and open the "ODBC Administrator" application.
4. Select the "Drivers" tab.
5. Take note of the ODBC driver name for MySQL.  Mine was "MySQL ODBC 5.1 Driver" (see picture).
   

Since OS X already ships with ODBC support, this was the only step we needed to perform, or almost.  Just one minor step is required so that Sedna can load the OS X ODBC libary.

Create symlink for libodbc.so

Sedna expects a libodbc.so file in /usr/lib.  However, this file is called libiodbc.dylib on OS X.  Not a problem though.  All we need to do is create a symbolic link (symlink) so that libodbc.so resolves to libiodbc.dylib

1. Change to the /usr/lib directory
2. Now type in this command to create the symlink: sudo ln -s libiodbc.dylib libodbc.so

Testing Sedna->MySQL querying

Now we're ready to take Sedna for a test drive.  If you haven't used Sedna before, we'll first need to start it up and create a database.  No problem here.  The steps are very simple.  If you'd like a bit more information on what's going on, the steps below are almost identical to those from Sedna's Quick Start guide.

1. Switch to the ~/Applications/sedna directory (or wherever you actually installed Sedna to).
2. Start the GOVERNOR process: bin/se_gov
3. Create a "test" database: bin/se_cdb test
4. Start the "test" database: bin/se_sm test
5. Copy-paste the XQuery FLWOR expression from below into a file called test.xquery.  Make sure to replace the ODBC driver name shown in RED with the one that you noted down from the "ODBC Adminstrator" tab.  The connection string is fairly simple once past the ODBC driver information.  After the :// use the IP address or hostname where your MySQL database is connected.  Then comes the username and password.  For this example, I connected to my developer Deki 8.08 database to fetch all records from the "services" table.
   

   declare namespace sql="http://modis.ispras.ru/Sedna/SQL"; 
   
   let $connection := sql:connect("odbc:MySQL ODBC 5.1 Driver://192.168.1.72/wikidb", "wikiuser", "password") 
   for $x in sql:execute($connection, "SELECT * FROM services")
   return 
   	<service id="{data($x/@service_id)}">
   		<sid>{data($x/@service_sid)}</sid>
   		<uri>{data($x/@service_uri)}</uri>
   		<description>{data($x/@service_description)}</description>
   		<local>{data($x/@service_local)}</local>
   		<enabled>{data($x/@service_enabled)}</enabled>
   		<status>{data($x/@service_last_status)}</status>
   		<modified>{data($x/@service_last_edit)}</modified>
   	</service>

6. Now run the XQuery file: bin/se_term -file examples/mysql/test.xquery test
7. You should see the results shown on your Terminal screen.

That's it!  Your Sedna installation is now able to fetch records via ODBC from your MySQL database.

Documentation

The Sedna website has lots of documentation, but I recommend you look into the doc folder of your Sedna installation.  It contains PDFs that are much easier to browser than their online counterparts.

Please leave comments or edit this page directly if I left something out that was important.  Enjoy!