number 9

public service announcement

I need a good database to run locally. When I Google for ‘postgreSQL OSX’ I see a lot of old posts that start with ‘download and build readline’. No wonder people still use MySQL.

If you’re on a Mac use EnterpriseDBs installer. It’s up to date and you get a reasonably sane default config

(everything is under /Library/PostgreSQL/8.3 if you want to tweak it).

Also bundles docs and pgAdmin (which seems to have become a half-decent tool  while I wasn’t looking) in /Applications/PostgreSQL (I found a JDBC driver under /Library/Extensions/Java too, although that could have come from anywhere).

There are also installers there for Linux (but you might as well use your package manager ) and Windows (no, really).

For production, I’d use something on a ‘real’ UNIX obviously - the Solaris 10 / SXCE bundled PostgreSQL 8.2 / 8.3 works great.

One downside to embedding your database server is that Glassfish needs more memory.

I’d been messing around with a few webapps and yesterday got Roller4 running. After a few clicks around the app, I started to see ‘PermGen space: java.lang.OutOfMemoryError’ errors. 

More power, Doctor

The fix is pretty obvious : have the JVM allocate more PermGen space.

It’s simple to do this through the admin UI ( ‘Application Server’ -> ‘JVM Settings’ -> ‘JVM Options’).

Or you can do it on the command line:

 hypnotoad:~ $ asadmin create-jvm-options \-XX\\:MaxPermSize=128m
created 1 option(s)
Command create-jvm-options executed successfully.
hypnotoad:~ $ 

You need to restart the server for it to actually take effect.

Since writing this, I’ve realised JavaDB is pretty crap. Do yourself a favour and try the decent PostgreSQL installer I found.

Glassfish v3 ships with JavaDB (aka Apache Derby aka Cloudscape).
I’ll be using this for trying out Rails and JRuby, but it’s also handy
for things like authentication via JDBC Realms.

A JavaDB database is essentially a directory that only one process can access at a time (a bit like sqlite). This can be Glassfish itself (an embedded database) or a standalone database process (that serves SQL clients over TCP/IP).

Both have pros and cons. I’ll take you through creating both.

option 1: standalone database server

You sure you want JavaDB? There are better options (sorry, can’t help it).

The main benefit to running a network server process is that it’s the only way for multiple clients to access the database simultaneously

(it’s also the only option that makes sense if you were clustering Glassfish).

If you need to create a schema before you deploy a webapp (with NetBeans or ‘rake migrate’)
you’ll have to stop Glassfish first unless you go down this route.

hypnotoad:databases $ asadmin start-database --dbhost 127.0.0.1
Database started in Network Server mode on host 127.0.0.1 and port 1527.
Could not connect to Derby Network Server on host 127.0.0.1 port 1527.
Starting database in the background.
Log redirected to /Users/dick/Applications/glassfishv3-tp2/glassfish/databases/derby.log.
Command start-database executed successfully.

( –dbhost defaults to ‘0.0.0.0’ but this causes problems if you change IP . Stick to 127.0.0.1).

Next, create the connection pool (and associated database - see later).
It’s simplest to do this on the command line (partly due to bug 4889 ):

hypnotoad:databases $ asadmin create-jdbc-connection-pool \
--datasourceclassname=org.apache.derby.jdbc.ClientConnectionPoolDataSource\
--isconnectvalidatereq=true --validationmethod=meta-data \
--property user=GFv3:password=GFv3:databaseName=railsdb:\
connectionAttributes=\;create\\=true \
railspool
Command create-jdbc-connection-pool executed successfully.

• The ’;create=true’ option tells JavaDB to create the ‘railsdb’ database on demand
• host:port defaults to localhost:1527
• username and password can be anything, but are required

We now ‘ping’ the pool. This checks our network connection is good, and has the side-effect
of creating the ‘railsdb’ database:

hypnotoad:databases $ asadmin ping-connection-pool railspool
Command ping-connection-pool executed successfully.
hypnotoad:databases $ ls
derby.log railsdb

option 2. embed Derby in Glassfish

This is my preferred option for several reasons:

1. it saves having to run 2 JVMs
2. in development, I don’t mind stopping Glassfish
3. for production, I want webapps to create their own schema anyway
4. connection validation and authentication is no longer an issue
5. sorry to bang on about it, but if you want a standalone database there are much better options

One side effect is that Glassfish is going to use more memory (especially if you have several connection pools configured). You might want to tweak your JVM .

There’s no need to ‘start-database’ in this case – just go ahead and make the pool:

hypnotoad:glassfishv3-tp2 $ asadmin create-jdbc-connection-pool \
--datasourceclassname org.apache.derby.jdbc.EmbeddedDataSource \
--property databaseName=\$\{com.sun.aas.instanceRoot\}/databases/railsdb:\
connectionAttributes=\;create\\=true \
railspool
Command create-jdbc-connection-pool executed successfully.

• the different DataSource class is what makes it embedded
• provide a full path in the databaseName attribute to avoid current working directory hell
• since Glassfish is the database server, we can skip username,password and connection validation options

If we ping the pool, we can see Glassfish creates derby.log and the database dir

hypnotoad:glassfishv3-tp2 $ asadmin ping-connection-pool railspool
Command ping-connection-pool executed successfully.
hypnotoad:glassfishv3-tp2 $ tail derby.log
2008-05-15 11:12:48.770 GMT:
Booting Derby version The Apache Software Foundation – Apache Derby – 10.2.2.1 – (538595): instance c013800d-0119-ec48-424c-000001a39158
on database directory /Users/dick/Applications/glassfishv3-tp2/glassfish/domains/domain1/databases/railsdb
Database Class Loader started – derby.database.classpath=’‘
hypnotoad:glassfishv3-tp2 $

Butler Lampsons mamma didn’t raise no fools

Whichever option you choose, the command to give the pool a JNDI name is the same:

hypnotoad:glassfishv3-tp2 $ asadmin create-jdbc-resource --connectionpoolid=railspool jdbc/railspool
Command create-jdbc-resource executed successfully.

And we’re done. Now simply write a webapp to use the damn thing.

I’m happy. It feels a lot lot faster, both rendering and in the admin screens.

Quick summary :

• articles and comments  came over ok (articles via the RSS importer, comments by hand)
•  the theme existed for both typo and wordpress
• google analytics needed pasting into your <head>
• have told wordpress to use the same permalink format as typo did
• set up some 301s for the old typo RSS and atom feeds

Let me know if anything seems wrong. I’ve been tailing access logs and amazingly I do have some readers (!), so don’t be shy.

 Things I know are broken but probably won’t fix:

• old Typo URLs for per-article RSS ( I couldn’t see why anyone would want that)
• tags (the RSS importer set them up as categories instead, which I’m fine with)

I’m not delighted about having to run on MySQL (I‘ll be mysqldumping twice a day),but the schema is much saner than typos had become by 4.x. I’ve already fixed up somethings on the mysql command line faster than I could have found them in the web frontend.

Ubuntu put their postgresql client bits where gems can’t see them.

Google turned up the answer in the past, but today I had to figure it out myself.
So for the next time, here’s how to get the client bits needed for a gem build.

planb $ sudo apt-get install  postgresql-client-8.2 libpq-dev
planb $ sudo gem install postgres -- \
--with-pgsql-include-dir=/usr/include/postgresql \
--with-pgsql-lib-dir=/usr/lib/postgresql

Rails (well, rake) calls out to dropdb and createdb to flush the test database. If you want to do TDD, make sure the rails DB user has ‘createdb’ rights and owns the 3 databases:

postgres@elephantom $ createuser -PREdS railsguy
postgres@elephantom $ createdb -O railsguy live
postgres@elephantom $ createdb -O railsguy dev
postgres@elephantom $ createdb -O railsguy test

dropdb/createdb need access to the ‘postgres’ database to operate. Until you know this,
errors like

FATAL: no pg_hba.conf entry for host “1.2.3.4”, user “railsguy”, database “postgres”, SSL off

will have you staring at database.yml until your eyes cross.

you might as well have a pg_hba.conf line like

#TYPE  DB   USER       CIDR         METHOD
host   all   railsguy    1.2.3.4/32   md5

and have done.