Re: Storing Configuration settings for a database?

On Jan 26, 2004, at 6:47 AM, D. Dante Lorenso wrote:
<snip>

> If I use two columns instead like KEY / VALUE, then all values must be
> of
> the same datatype (probably VARCHAR) and setting or reading values will
> require casts and parsing of data constantly by applications using the
> configs.
>
> Who has built something similar to what I'm attempting, and what do you
> recommend?

heh, I'd say use XML (and Castor to autogenerate objects for your
configuration schema), but since you're talking about postgres...

Using the JDBC drivers (dunno about the other interfaces), you can
actually call ResultSet.getInt(), .getLong(), .getFloat(). getXXX() on
a column that is defined as a character field (varchar, text).

CREATE TABLE settings (
    section varchar(8),
    key varchar(255),
    value text
);
INSERT INTO settings values ('sec1', 'mykey', '42');

Then just do this:

Statement stmt = _conn.createStatement();
ResultSet rs = stmt.executeQuery("select value from settings where
section='sec1' and key='mykey'");
rs.next();
int value = rs.getInt("value");
stmt.close();

In reality, I don't know if this is a good idea or not as I suppose
you're relying on an implementation detail of the JDBC drivers, but it
works.

One of the nice side benefits of this is that if you call .getInt() for
a value of, say, "beer", you'll get a nice
java.lang.NumberFormatException, so it'll be rather easy to trap and
workaround misconfigurations.

eric