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/.
|
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.
|
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.
|
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 &
|
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>
|
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.
|
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.
|
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 ...
|
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%
|
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.
|
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 9i 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 9i instance, you'll just need the JDBC drivers; otherwise, download the entire database for installation on your platform.
Oracle 9i 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.
|
For more information on Oracle 9i, 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).
