Thread: Storing Configuration settings for a database?

I want to store config file keys and values in my PG database
in order to 1) be able to INSERT/UPDATE/DELETE configuration settings
without a flat file and 2) give access to the config settings with
my web application.

Right now, my java application uses a config file similar to an .INI
file with the following format:

    [section1]
        key1 = value1
        key2 = "Value Two"
        multi word key3 = 12345
    [section2]
       ...

My question is ... can this be done in PG, should it be done, and what's
the best way to store the data?  I can flatten the tree heirarchy if
necessary to yeild something like:

    section1.key1 = value1
    section1.key2 = "Value Two"
    section1.multi word key3 = 12345
    ...

Since PG stores different datatypes it'd be ideal to have integers be
ints and strings as varchar etc, but to do this means I'd have to define
a separate column for each config option and I'd end up with only one
row of data.

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?

Dante

----------
D. Dante Lorenso
dante@lorenso.com

On Monday 26 January 2004 11:47, D. Dante Lorenso wrote:
> I want to store config file keys and values in my PG database
> in order to 1) be able to INSERT/UPDATE/DELETE configuration settings
> without a flat file and 2) give access to the config settings with
> my web application.
>
> Right now, my java application uses a config file similar to an .INI
> file with the following format:
>
>     [section1]
>         key1 = value1
>         key2 = "Value Two"
>         multi word key3 = 12345
>     [section2]

> Who has built something similar to what I'm attempting, and what do you
> recommend?

I tend to have something like:

CREATE TABLE config_settings AS (
    section  varchar(64),
    item     varchar(64),
    type     varchar(16),
    value    text,
    PRIMARY KEY (section,item)
);

In general, two levels (section,item) seem enough, but it's trivial to add
another. I cast the value from text => my desired type in the app (or raise
an exception if there is a problem).

Oh - I frequently add a "notes" or "description" column to for instructions on
what value should go in.

--
  Richard Huxton
  Archonet Ltd

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

> I want to store config file keys and values in my PG database
> in order to 1) be able to INSERT/UPDATE/DELETE configuration settings
> without a flat file and 2) give access to the config settings with
> my web application.
In GnuMed (www.gnumed.org) we do it like this:

http://savannah.gnu.org/cgi-bin/viewcvs/*checkout*/gnumed/gnumed/gnumed/server/sql/gmconfiguration.sql?rev=1.28

(look at the cfg_* tables)

Regards,

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346