12.2 Mac OS X Databases

The rest of this chapter will walk through the installation of popular databases on the Mac OS X platform. Each database has pros and cons, and your decision to use a specific database will probably depend on its performance, functionality, and price. You should browse the O'Reilly library for more extensive coverage of these databases before making your final decision.

12.2.1 MySQL

MySQL is a popular, lightweight, open source relational database. Many developers support it, which makes it an ideal database on which to start the RDBMS learning process. Over the years , MySQL has evolved, adding an increasingly broad range of sophisticated features. Still, its most popular use is as a storage mechanism for dynamic web applications ” specifically those built using open source languages such as Perl and PHP. It is also an extraordinarily solid database for small, JSP-based web applications.

12.2.1.1 Installation

Installing MySQL on Mac OS X is fairly straightforward. First download a distribution of MySQL. A prebuilt Mac OS X binary is available at http://www.mysql.com/downloads/.

Currently, an ideal download is MySQL 3.23, which is the one you'll use here. You can get it at http://www.mysql.com/downloads/mysql-3.23.html.

Scrolling through the list, you'll notice versions available for Windows, Solaris, FreeBSD, and Mac OS X. The availability of a Windows version sometimes makes MySQL a better choice than PostgreSQL (discussed later in this chapter).

The specific version used here is MySQL 3.23.55 for Mac OS X 10.2. You might notice that the last revision number for MySQL is updated frequently. Be sure to stay on top of the release notes to look for bug fixes for problems you may have.

When you click on the download link (I grabbed the "Standard" version rather than the "Max" version) and select a mirror site, you'll end up with a .tar.gz file. Double-click on it, and assuming your StuffIt Expander is installed, the file will be expanded into a new folder called mysql-3.23.55-apple-darwin6.1-powerpc . Move this folder into a location you can remember. For the rest of this discussion, assume that the files are installed in /Developer/mysql-3.23 .

The folder/directory name is shortened to make it easier to type, but the version number remains to ensure that you can easily remember which version you are using. Later, you may wish to test or upgrade to another version, and you'll want this version information to keep things straight.

Next , go to the Apple menu, select "System Preferences . . . ", and click on "Users." Add a new user, name the user mysql (for both the full name and the short name), and select an icon for that user.

Don't forget to assign a password to the mysql user (and remember the password). You can also use the NetInfo Manager to create new users that won't be visible in login screens, without a home directory, etc. For more information on NetInfo, consult Mac OS X for Unix Geeks by Brian Jepson and Ernest E. Rothman (O'Reilly).

Once you've installed MySQL, you'll need to perform some additional steps to get it running on your system. Open up the terminal, and navigate to the bin directory of the folder where you placed your MySQL installation.

Issue the commands as shown here to complete the software portion of the installation:

 [localhost:~] wiverson% cd /Developer/mysql-3.23 [localhost:/Developer/mysql-3.23] wiverson% ./scripts/mysql_install_db 

Now, set the permissions for the various directories:

 [localhost:/Developer/mysql-3.23] wiverson% sudo chown -R mysql ./data [localhost:/Developer/mysql-3.23] wiverson% sudo chown -R mysql * [localhost:/Developer/mysql-3.23] wiverson% sudo chown -R root ./bin/* 

This code assumes you created the mysql user, so be sure it is created before attempting these steps.

12.2.1.2 Starting MySQL

Now launch the server:

 [localhost:/Developer/mysql-3.23] wiverson% sudo ./bin/safe_mysqld & 

If you have already performed a sudo in the last five minutes or so, you'll be able to execute this command with the & to put it in the background. If a longer time period has elapsed since sudo -ing in, though, you may be prompted for an administrative password. If you're having trouble, open a new terminal and execute the command without the & , or execute a sudo ls to force a password check.

Next, set passwords for MySQL's default administrator account. Follow the steps shown here (replace the text "groovy" with your choice of password, but retain the quotation marks):

 [localhost:/Developer/mysql-3.23.47] wiverson% sudo ./bin/mysqladmin -u root  -p password '  groovy  ' [localhost:/Developer/mysql-3.23.47] wiverson% sudo ./bin/mysqladmin -u root  -h localhost -p password '  groovy  ' 

Now test the connection to the database:

 [localhost:/Developer/mysql-3.23.47] wiverson% ./bin/mysql --user root -- password Enter password:  Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 6 to server version: 3.23.54 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> 

After being prompted for the administrator password, you should see a MySQL status message go by that displays the database connection ID and server version. At the mysql> prompt, enter the show databases; command:

 mysql> show databases; +----------+  Database  +----------+  macjava    mysql      test      +----------+ 3 rows in set (0.00 sec) mysql> 

You should see a formatted display listing the mysql and test databases. Type exit at the prompt, and you'll return to the command shell.

12.2.1.3 Stopping MySQL

Shut down the database by entering the following command:

 [localhost:/Developer/mysql-3.23.47] wiverson% ./bin/mysqladmin --user=root  --password shutdown 

As you can see, most of the work around MySQL involves the mysqladmin and mysql commands. You should play around and become familiar with them, as they will be your mainstay in database work.

12.2.1.4 Creating a database

Now that you've gotten a handle on the basics, restart the database and get back into the MySQL monitor shell by executing the commands shown here:

 [localhost:/Developer/mysql-3.23.47] wiverson% sudo ./bin/safe_mysqld & [localhost:/Developer/mysql-3.23.47] wiverson% ./bin/mysql --user root -passwords 

You'll be prompted again for a password. At the MySQL prompt, create a new database and user. Replace the text "special" with your own password, retaining the quotation marks:

 mysql> create database macjava Query OK, 1 row affected (0.00 sec) mysql> grant all on macjava.* to javadev@localhost identified by "  special  "; Query OK, 0 rows affected (0.00 sec) mysql> exit 

12.2.1.5 Working with a database

Before issuing SQL commands to work with a database, log back in to the database:

 [localhost:/Developer/mysql-3.23.47] wiverson% ./bin/mysql --user javadev -- password macjava 

After entering your password again, you'll see the mysql> prompt. To start working with the macjava database, type the following command:

 mysql> use macjava Database changed 

You're now all set to begin adding tables, columns , and data to the database. To test this code, enter some SQL as shown:

 mysql> select now(  ); +---------------------+  now(  )                +---------------------+  2003-01-05 00:02:34  +---------------------+ 1 row in set (0.14 sec) mysql> 

To learn how to create tables and add sample data, see the next chapter.

12.2.1.6 MySQL JDBC configuration

You can download the MySQL JDBC driver (now known as Connector/J) from http://www.mysql.com/downloads/api-jdbc-stable.html (follow the links to a local mirror). Make sure the resultant JAR file ( mysql-connector-java-2.0.14-bin.jar in my case) is on your classpath.

If using Version 2.0.14 or later, you'll use com.mysql.jdbc.Driver as your driver class (prior releases used org.gjt.mm.mysql.Driver ). The JDBC connection URL is in the form jdbc:mysql://127.0.0.1/databasename . Replace 127.0.0.1 with the hostname of your MySQL server, and you know what to do with databasename .

12.2.2 PostgreSQL

PostgreSQL is another popular open source database. Like MySQL, PostgreSQL is free, easy to run, and great for development work. While it doesn't offer a native installation for the Windows platforms, it is a little heavier-duty than MySQL, so it often finds a place in open source production environments.

12.2.2.1 Installation

The easiest way to install PostgreSQL is to download a prebuilt package from http://www.osxgnu.org/software/Database/postgresql/. PostgreSQL prebuilt binaries for Mac OS X currently have some serious problems, though.

As of February 2003, the latest posted version (7.1.3) was built with support for a command history in the interactive "psql" interpreter using the up and down keys, and it includes the JDBC drivers (in /usr/local/pgsql/share/java ). Unfortunately, this package is not currently compatible with Mac OS X 10.2.

Therefore, the other way to install PostgreSQL on Mac OS X is to download and install it from the source available at http://www.postgresql.org/. Select a mirror location close to you, and then download the source for the database project. In this instance, you'll use the postgres-7.2.3.tar.gz release.

Create a new user in the "Users" System Preferences pane with the name "PostgreSQL User", the short name "postgres", and whatever password you want. Log out of Mac OS X, log back in as this user, and uncompress the postgres-7.2.3.tar.gz file in your ~/Documents directory.

As noted in the MySQL section, using NetInfo to create a user without a home directory is a good security practice.

Then open the Terminal and execute the commands shown here from the new PostgreSQL installation directory:

 [localhost:~/Documents/postgresql-7.2.3] wiverson% cd src/include/port/ darwin [localhost:include/port/darwin] wiverson% mv sem.h sem.orig.h [localhost:include/port/darwin] wiverson% echo '#include <sys/sem.h>' >  sem.h [localhost:include/port/darwin] wiverson% more sem.h #include <sys/sem.h> [localhost:include/port/darwin] wiverson% cd ../../../backend/port 

This code fixes some issues in the sem.h file included with the distribution; these fixes are critical, so don't omit this step!

Next, comment out a few lines in the file Makefile.in , located in your installation's src/backend/port directory. If you have followed these instructions, the complete path to this file would be ~/Documents/postgresql-7.2.3/src/backend/port/Makefile.in . Use the # character to comment out the lines shown here:

  ...   #  ifeq ($(PORTNAME), darwin)  #  OBJS += darwin/SUBSYS.o  #  endif ... 

The easiest way to do this from the command line is to use the pico text editor. You can search for this text and quickly find it using pico and the Control-W "Where is" shortcut.

Finally, after making these fixes, you can compile and install the database:

 [localhost:src/backend/port] wiverson% cd ~/Documents/postgresql-7.2.3 [localhost:~/Documents/postgresql-7.2.3] wiverson% ./configure --mandir=/ usr/local/share/man --with-openssl=/usr/lib --enable-recode creating cache ./config.cache checking host system type... powerpc-apple-darwin6.3 checking which template to use... darwin checking whether to build with locale support... no checking whether to build with recode support... yes checking whether to build with multibyte character support... no checking whether NLS is wanted... no  ...omitted   for   brevity...  linking ./src/include/port/darwin.h to src/include/pg_config_os.h linking ./src/makefiles/Makefile.darwin to src/Makefile.port linking ./src/backend/port/tas/dummy.s to src/backend/port/tas.s [localhost:~/Documents/postgresql-7.2.3] wiverson% make make -C doc all gzip -d -c man.tar.gz  /usr/bin/tar xf - for file in man1/*.1; do \   mv $file $file.bak && \   sed -e 's/\fR(l)/\fR(7)/' $file.bak >$file && \   rm $file.bak  exit; \ done /bin/sh ../config/mkinstalldirs man7 mkdir man7 for file in manl/*.l; do \   sed -e '/^\.TH/s/"l"/"7"/'   \               -e 's/\fR(l)/\fR(7)/' \       $file >man7/`basename $file  sed 's/.l$/.7/'`  exit; \ done make -C src all  ...omitted   for   brevity...  tsort: pl_comp.o ranlib libplpgsql.a gcc -traditional-cpp -g -O2 -Wall -Wmissing-prototypes -Wmissing- declarations -Wno-error  -flat_namespace -bundle -undefined suppress pl_ gram.o pl_scan.o pl_handler.o pl_comp.o pl_exec.o pl_funcs.o    -o  libplpgsql.so.1.0 rm -f libplpgsql.so.1 ln -s libplpgsql.so.1.0 libplpgsql.so.1 rm -f libplpgsql.so ln -s libplpgsql.so.1.0 libplpgsql.so All of PostgreSQL successfully made. Ready to install. [localhost:~/Documents/postgresql-7.2.3] wiverson% sudo make install make -C doc install gzip -d -c postgres.tar.gz  ( cd /usr/local/pgsql/doc/html && /usr/bin/tar  xf - ) for file in man1/*.1 man7/*.7 ; do \   /bin/sh ../config/install-sh -c -m 644 $file /usr/local/share/man/$file   exit; \ done make -C src install  ...omitted   for   brevity...  Thank you for choosing PostgreSQL, the most advanced open source database  engine. [localhost:~/Documents/postgresql-7.2.3] wiverson% 

Some of these commands can take time to execute (several minutes or more), and no user feedback will be provided.

You've now installed PostgreSQL on your system. When you're done, the resulting PostgreSQL installation is stored at /usr/local/pgsql , with the relevant PostgreSQL commands available at /usr/local/pgsql/bin .

12.2.2.2 Initializing PostgreSQL

Next, configure a test data set. Execute the following commands to initialize a database:

 [localhost:local/pgsql/bin] wiverson% su - postgres Password: [localhost:~] postgres% mkdir ~/pgsql [localhost:~] postgres% mkdir ~/pgsql/data [localhost:~] postgres% cd /usr/local/pgsql/bin/ [localhost:local/pgsql/bin] postgres% ./initdb -D ~/pgsql/data The files belonging to this database system will be owned by user "postgres". This user must also own the server process. Fixing permissions on existing directory /Users/postgres/pgsql/data... ok creating directory /Users/postgres/pgsql/data/base... ok creating directory /Users/postgres/pgsql/data/global... ok creating directory /Users/postgres/pgsql/data/pg_xlog... ok creating directory /Users/postgres/pgsql/data/pg_clog... ok creating template1 database in /Users/postgres/pgsql/data/base/1... ok creating configuration files... ok initializing pg_shadow... ok enabling unlimited row size for system tables... ok creating system views... ok loading pg_description... ok vacuuming database template1... ok copying template1 to template0... ok Success. You can now start the database server using:     ./postmaster -D /Users/postgres/pgsql/data or     ./pg_ctl -D /Users/postgres/pgsql/data -l logfile start [localhost:local/pgsql/bin] postgres% ./postmaster -D /Users/postgres/pgsql/data DEBUG:  database system was shut down at 2003-01-04 23:38:21 PST DEBUG:  checkpoint record is at 0/1096F4 DEBUG:  redo record is at 0/1096F4; undo record is at 0/0; shutdown TRUE DEBUG:  next transaction id: 89; next oid: 16556 DEBUG:  database system is ready 

You're now running the PostgreSQL server, and any status information will be echoed to the console. Press Control-C in the terminal; this will cause PostgreSQL to shut down. If you haven't already, log out of the postgres user account and log back in with your regular account.

12.2.2.3 Starting PostgreSQL

Open a new Terminal window and execute the commands shown below to start PostgreSQL:

 [localhost:/usr/local/pgsql] postgres% cd /usr/local/pgsql/ [localhost:/usr/local/pgsql] postgres% ./bin/postmaster -i -D ~/pgsql/data/  >& ~/pgsql/log & [1] 10524 [localhost:/usr/local/pgsql] postgres% 

The su - postgres command lets you masquerade as the postgres user (you'll need to enter the postgres user's password as well), so you don't have to constantly log out and log in as different users. When you execute the postmaster command, the server's output will be sent to the ~/pgsql/log file.

PostgreSQL reports much of its information by using the STDERR output stream (not just STDOUT ), and the >&~/pgsql/log sequence tells the shell to redirect output to a file instead of to these output streams. The final & tells the shell that this process should be run in the background.

You can monitor the output of this file by executing the command tail - 501f ~/pgsql/log :

 [localhost:~] postgres% tail -501f ~/pgsql/log DEBUG:  database system was shut down at 2003-01-04 23:42:58 PST DEBUG:  checkpoint record is at 0/109734 DEBUG:  redo record is at 0/109734; undo record is at 0/0; shutdown TRUE DEBUG:  next transaction id: 89; next oid: 16556 DEBUG:  database system is ready 

12.2.2.4 Stopping PostgreSQL

You can shut the server down by executing the command ps to find the process ID (PID) of the postmaster process, which you can then terminate by issuing a kill PID command, where PID is the postmaster process ID:

 [localhost:/usr/local/pgsql] postgres% ps  grep postmaster 10524 std  S      0:00.05 ./bin/postmaster -i -D /Users/postgres/pgsql/data/ 10531 std  R+     0:00.00 grep postmaster [localhost:/usr/local/pgsql] postgres% kill 10524 [localhost:/usr/local/pgsql] postgres% 

12.2.2.5 Creating a database

Now you can work with PostgreSQL data as a user. Make sure the database is running as described above. As the postgres user, execute the /usr/local/pgsql/bin/createuser command. Use your main account's short name from the "System Preferences Accounts" dialog for the username, and allow database creation for new users.

Next, open a new Terminal window and execute the createdb command to create your own database. You'll want to supply your own database name, of course:

 [localhost:/usr/local/pgsql] postgres% ./bin/createdb macjava CREATE DATABASE [localhost:/usr/local/pgsql] postgres% 

12.2.2.6 Working with a database

Now you're ready to work with the psql program, an interactive SQL tool:

 [localhost:/usr/local/pgsql] postgres% ./bin/psql macjava Welcome to psql, the PostgreSQL interactive terminal. Type:  \copyright for distribution terms        \h for help with SQL commands        \? for help on internal slash commands        \g or terminate with semicolon to execute query        \q to quit macjava=# 

Use the name of the database you just created. You can use this program to enter SQL commands that execute directly against the database:

 macjava=# select now(  );               now               -------------------------------  2003-01-04 23:59:14.946273-08 (1 row) macjava=# 

When you're done working in the psql shell, enter \q and press return.

12.2.2.7 PostgreSQL JDBC configuration

To start working with PostgreSQL via JDBC, you will need the JDBC drivers available at http://jdbc.postgresql.org/download.html and the Postgres 7.2 JDBC 2 release ( pgjdbc2.jar ). To work with PostgreSQL, make sure that this file is on your classpath.

The driver name is org.postgresql.Driver , and the JDBC connection URL (which connects to the database you just created) is in the form jdbc:postgresql://127.0.0.1/databasename .

12.2.3 Oracle 9i

One important validation of Mac OS X has been its release of commercial database products for the platform. The database world still largely revolves around the folks at Oracle, so there is perhaps no more important database product for Mac OS X than a release of Oracle. Happily, it's now possible to download a developer version of Oracle 9 i specifically tailored for use with Mac OS X 10.2 from http://otn.oracle.com/software/products/oracle9i/content.html. Click on the "Take a Survey" link to register, and you can then download the software. If you're connecting to an existing Oracle 9 i instance, you'll just need the JDBC drivers; otherwise , download the entire database for installation on your platform.

Oracle 9 i is a very complex product, so if you're just starting to work with SQL databases, it is not the easiest place to begin. Beginning with MySQL or PostgreSQL is much easier.

To get an idea of how complex Oracle is, note that the PostgreSQL 7.1.2 release is a little over 5 MB, whereas Oracle 9 i weighs 345 MB. While a release's size isn't always indicative of its productivity, it usually says something about the complexity of the software involved.

For more information on Oracle 9 i , and for guidelines on adopting it for your application development, read the overview at O'Reilly's MacDevCenter.com: http://www.macdevcenter.com/pub/a/mac/2002/11/12/oracle_part1.html. You should also check out Oracle in a Nutshell , by Rick Greenwald and David Kreines, and Java Programming with Oracle JDBC , by Donald Bales (both from O'Reilly).