Storing Configuration settings for a database?

[D. Dante Lorenso]

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
da***@lorenso.com

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Eric Ridge]

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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Karsten Hilbert]

> 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/view...n.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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org