[plt-scheme] HOWTO: SchemeQL + MySQL on OS X

I wanted to play with SchemeQL on my Mac, and decided to use MySQL 
since it seems to have a lot of documentation and support. This took me 
a long time to figure out, so I thought I'd share my results -- feel 
free to correct anything you think is wrong, or email me with questions.

For information on MySQL, go to <http://www.mysql.com>

For information on SchemeQL, go to <http://schematics.sourceforge.net/> 
and download the paper "Two Little Languages: SchemeUnit and SchemeQL", 
or use the Helpdesk manual for the schemeql collection after you've 
installed it.

Hope this is useful.

Cheers,
Dave


======================================================================
HOWTO: SchemeQL + MySQL on Mac OS X
======================================================================

First of all, you should have the latest version of DrScheme installed 
(version 205 as of this email). It doesn't have to be built from 
sources; the latest binary release is fine. Also make sure that the 
bin/ directory containing mzscheme and mzc is in your PATH environment 
variable.

1. Install Server Logistics Complete MySQL package

This *greatly* reduces the difficulty of installing a version of MySQL 
that will work with OS X's ODBC support. (Apparently the standard, 
out-of-the-box installation from MySQL doesn't work well with ODBC.) 
What's even better is that it's a Mac-style installation package 
(.pkg), and it comes with fabulous installation instructions.

a. Download the package from <http://www.serverlogistics.com/mysql.php>
b. Run the MySQL.pkg installation package
c. Follow the installation instructions (Install Guide.pdf) to 
initialize the database and create a root password
d. Run the Drivers/MyODBC.pkg installation package
e. Follow the installation instructions to set up the ODBC driver.

2. Install CocoaMySQL

A nice GUI for administering MySQL databases.

a. Download the program from <http://cocoamysql.sourceforge.net/>
b. Copy the program file to wherever you want it (I love Macs)
c. Run the program and create whatever databases you want.

3. Set up DSN's for your databases

Run /Applications/Utilities/ODBC Administrator and create User DSN's 
(or, if you run as root, you can create System DSN's). Use the driver 
that you already set up in step (1) and add the key "database" with the 
name of the database as the value.

4. Test your DSN

a. From the Terminal, run /usr/bin/odbctest. At the prompt, type 
"dsn=<dsn-name>;user=root;password=<root-password>" with appropriate 
values for <dsn-name> and <root-password>.
b. If it succeeded, you should get the "SQL>" prompt, and you can type 
in MySQL commands like "show tables;"

5. Install SrPersist:

a. Download the exp-tagged CVS sources of PLT Scheme:
     % cvs -d :pserver:anonymous at anoncvs.plt-scheme.org:/cvs login
       (hit <enter> at password prompt)
     % cvs -d :pserver:anonymous at anoncvs.plt-scheme.org:/cvs checkout -r 
exp plt
b. Edit plt/src/srpersist/Makefile
     - set ODBCVER to 0x0351
     - set DRIVER_MGR_LIB to iodbc
     - set LIBDIR to /usr/lib
     - set STDCPP_LIBDIR to /usr/lib
     - add -DNO_SQLUCODE to CPP_FLAGS
     - uncomment the "-L$(STDCPP_LIBDIR) -ldstdc++" line from the 
srpmain.so target
     - set MZC to mzc
     - set ARCH to use mzscheme instead of ../../bin/mzscheme
c. Build the srpersist native library
     % cd plt/src/srpersist
     % make
     % make install
d. Download the latest SrPersist Scheme sources from 
<http://download.plt-scheme.org/srpersist/> (choose "Platforms other 
than Windows") -- version 204 as of this email.
e. Install the SrPersist package and add the native library
     % setup-plt srpersist-204.plt
     % mkdir -p ~/Library/PLT\ 
Scheme/205/collects/srpersist/private/compiled
     % cp -R plt/src/srpersist/private/compiled/native ~/Library/PLT\ 
Scheme/205/collects/srpersist/private/compiled/

6. Install SchemeQL:

a. Download the latest CVS sources:
     % cvs -d:pserver:anonymous at cvs.sourceforge.net:/cvsroot/schematics 
login
       (hit <enter> at password prompt)
     % cvs -z3 
-d:pserver:anonymous at cvs.sourceforge.net:/cvsroot/schematics co schemeql
b. Build and install the PLT package:
     % cd schemeql
     % ./pack.ss
     % setup-plt schemeql.plt

In this final step you should see a copyright message for SisterPersist 
by Paul Steckler. If you get an error message, the most common mistake 
I made was copying the SrPersist native library to the wrong directory. 
There should be a copy of the .so file in these two directories:

~/Library/PLT\ Scheme/205/collects/srpersist/private/compiled/native/
~/Library/PLT\ 
Scheme/205/collects/srpersist/private/compiled/native/ppc-macosx/

7. Try it out:

a. Make sure your MySQL server is up and running
b. Run DrScheme
c. Put this in the definitions window

(require (lib "schemeql.ss" "schemeql"))
(define (test-mysql database user password)
   (let ([conn (connect-to-database database user password)])
     (disconnect-from-database conn)))

d. Type (test-mysql <db> <user> <password>) with appropriate values for 
each parameter into the interactions window

If it works, you should see #<sql-hdbc> in the interactions window.

======================================================================