chosing a database name

On Wed, Jul 13, 2005 at 05:56:03PM +0200, Karsten Hilbert wrote:

we are developing GNUmed, a medical practice management
application running on PostgreSQL (you want your medical
data to be hosted by something reliable, don't you ;-) We
are putting out our first release sometime in the next two
weeks.

The idea is to name the production database "gnumed0.1" for
version 0.1 (gnumed0.2 etc for upcoming releases). I do
realize the "." may force me to quote the database name in,
say, a CREATE DATABASE call.

I doubt you'll have any problems with the tools, but the quoting may
prove painful. Why not replace the dot with an underscore? gnumed0_1

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Nadie esta tan esclavizado como el que se cree libre no siendolo" (Goethe)

Alvaro Herrera wrote:

On Wed, Jul 13, 2005 at 05:56:03PM +0200, Karsten Hilbert wrote:

we are developing GNUmed, a medical practice management
application running on PostgreSQL (you want your medical
data to be hosted by something reliable, don't you ;-) We
are putting out our first release sometime in the next two
weeks.

The idea is to name the production database "gnumed0.1" for
version 0.1 (gnumed0.2 etc for upcoming releases). I do
realize the "." may force me to quote the database name in,
say, a CREATE DATABASE call.

I doubt you'll have any problems with the tools, but the quoting may
prove painful. Why not replace the dot with an underscore? gnumed0_1

Or why bother including either? Just use sequential integers, maybe
left-padded with zeros to make the name the same length for the first
thousand or so releases?

On Wed, Jul 13, 2005 at 12:53:15PM -0400, Alvaro Herrera wrote:

we are developing GNUmed, a medical practice management
application running on PostgreSQL (you want your medical
data to be hosted by something reliable, don't you ;-) We
are putting out our first release sometime in the next two
weeks.

The idea is to name the production database "gnumed0.1" for
version 0.1 (gnumed0.2 etc for upcoming releases). I do
realize the "." may force me to quote the database name in,
say, a CREATE DATABASE call.

I doubt you'll have any problems with the tools, but the quoting may
prove painful. Why not replace the dot with an underscore? gnumed0_1

Good suggestion. I will try to find a name that a) makes the
version tag unambigous and b) does not require quoting.

My main concern, however, was whether the *approach* is
sound, eg using a separate database name per release or IOW
version. One way would be to use the database name "gnumed"
regardless of release, another way would be to use
"gnumedX_Y" for release X.Y. I wonder whether the latter
approach has any drawbacks people might think of regarding
release management etc.

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

On Wed, Jul 13, 2005 at 01:18:09PM -0400, Berend Tober wrote:

Or why bother including either? Just use sequential integers, maybe
left-padded with zeros to make the name the same length for the first
thousand or so releases?

A good tip, too, thanks. Would solve the ambiguity dilemma, too.

I think we'll go with

"gnumed_0_1"

for release 0.1 and see what gives. I'm still happy to hear
people speak up and say why I am crazy to do so in the first
place. I guess it's a bit related to my using Python:

"Explicit is better than implicit ..."

(which doesn't mean overly verbose)

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

On Jul 13, 2005, at 1:18 PM, Berend Tober wrote:

Or why bother including either? Just use sequential integers, maybe
left-padded with zeros to make the name the same length for the
first thousand or so releases?

I concur with this advice. Just use a sequence number which happens
to correspond with your software release numbers... or not. They can
be separate, especially once you get more stable and have more
software updaes than schema updates.

Or you could do something like Pg and require changes only for major
version number releases. :-)

Vivek Khera, Ph.D.
+1-301-869-4449 x806

we are developing GNUmed, a medical practice management
application running on PostgreSQL (you want your medical
data to be hosted by something reliable, don't you ;-) We
are putting out our first release sometime in the next two
weeks.

The idea is to name the production database "gnumed0.1" for
version 0.1 (gnumed0.2 etc for upcoming releases). I do
realize the "." may force me to quote the database name in,
say, a CREATE DATABASE call.

I doubt you'll have any problems with the tools, but the quoting may
prove painful. Why not replace the dot with an underscore? gnumed0_1

Good suggestion. I will try to find a name that a) makes the
version tag unambigous and b) does not require quoting.

My main concern, however, was whether the *approach* is
sound, eg using a separate database name per release or IOW
version. One way would be to use the database name "gnumed"
regardless of release, another way would be to use
"gnumedX_Y" for release X.Y. I wonder whether the latter
approach has any drawbacks people might think of regarding
release management etc.

The only thing I can think is that if the changes from v1 to v2 don't
touch the schema, then you've got a lot of extra update-work to do that
really isn't necessary. Doesn't hurt anything though and it gives you a
nice clean way of reverting back a version if necessary.

On Wed, Jul 13, 2005 at 01:21:01PM -0700, Philip Hallstrom wrote:

My main concern, however, was whether the *approach* is
sound, eg using a separate database name per release or IOW
version. One way would be to use the database name "gnumed"
regardless of release, another way would be to use
"gnumedX_Y" for release X.Y. I wonder whether the latter
approach has any drawbacks people might think of regarding
release management etc.

The only thing I can think is that if the changes from v1 to v2 don't
touch the schema, then you've got a lot of extra update-work to do that
really isn't necessary.

Got me ! :-)

Doesn't hurt anything though and it gives you a
nice clean way of reverting back a version if necessary.

That's what I hope to achieve. Doesn't free me from backup
before upgrade but still.

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

pgsql-general-owner@postgresql.org wrote on 07/13/2005 02:59:02 PM:

On Wed, Jul 13, 2005 at 12:53:15PM -0400, Alvaro Herrera wrote:

we are developing GNUmed, a medical practice management
application running on PostgreSQL (you want your medical
data to be hosted by something reliable, don't you ;-) We
are putting out our first release sometime in the next two
weeks.

The idea is to name the production database "gnumed0.1" for
version 0.1 (gnumed0.2 etc for upcoming releases). I do
realize the "." may force me to quote the database name in,
say, a CREATE DATABASE call.

I doubt you'll have any problems with the tools, but the quoting may
prove painful. Why not replace the dot with an underscore? gnumed0_1

Good suggestion. I will try to find a name that a) makes the
version tag unambigous and b) does not require quoting.

My main concern, however, was whether the *approach* is
sound, eg using a separate database name per release or IOW
version. One way would be to use the database name "gnumed"
regardless of release, another way would be to use
"gnumedX_Y" for release X.Y. I wonder whether the latter
approach has any drawbacks people might think of regarding
release management etc.

I think a better approach is to handle configuration management with a
table in each schema. Update the schema, update the table. This works
well with automating database upgrades as well, where upgrades are written
as scripts, and applied in a given order to upgrade a database from release
A to C, or A to X, depending on when it was archived. A script naming
convention (e.g. numerical) can determine order, and each script can
register in (write a line to) the configuration management table. This
allows for error analysis, among other things.

Rick

On Wed, Jul 13, 2005 at 03:53:26PM -0500, Richard_D_Levine@raytheon.com wrote:

I think a better approach is to handle configuration management with a
table in each schema. Update the schema, update the table.

We already do that anyways. Our schema scripts have their
CVS version tag embedded in an INSERT statement which
updates the schema revision tracking table with file name
and revision.

From our first release (== CVS release tag) onwards we will
employ the change-script-only technique described in Elein's
latest Tidbits.

I have now added a database revision table which enforces to
have only one single row which holds the "database schema
version". That row holds the md5 hash of the (ordered)
concatenation of the file name/revision rows in the table
described above. Thereby, when the schema changes, the hash
changes, too. We might then employ a lookup table/function
where the client can match its version against the database
version thereby determining whether it can work with that
database. Of course, any user with sufficient access rights
can manually screw up this construct ...

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

Karsten Hilbert wrote:

My main concern, however, was whether the *approach* is
sound, eg using a separate database name per release or IOW
version. One way would be to use the database name "gnumed"
regardless of release, another way would be to use
"gnumedX_Y" for release X.Y. I wonder whether the latter
approach has any drawbacks people might think of regarding
release management etc.

Others have given you some of the advice I would have given. One more
suggestion - does your database fit in just one "schema" in the gnumed
database? If so, then an easy option would be to have all versions of
your software use the same database, but each one has its own unique
schema within that database. This gives you the same ease of reversion
to a previous version, and gives you the added advantage that it's much
easier to write the scripts that update from one version to another,
since they can just operate within the same database, eg selecting data
from one schema and inserting it into another. Your users would then
drop old schemas on whatever basis they feel comfortable with.

What we do in practice is upgrade the database in situ when we upgrade
the software. We use a schema version number to automatically determine
what scripts to run - similar, I think to what Rick Levine was
describing. However, we have no easy way to revert to an old version if
required - so your plan will be better than ours in that regard.

Tim

--
-----------------------------------------------
Tim Allen tim@proximity.com.au
Proximity Pty Ltd http://www.proximity.com.au/

On Wed, Jul 13, 2005 at 04:18:34PM -0400, Vivek Khera wrote:

I concur with this advice. Just use a sequence number which happens
to correspond with your software release numbers... or not. They can
be separate, especially once you get more stable and have more
software updaes than schema updates.

Truly, words of wisdom. I have added that concept to our
revision handling.

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

On Thu, Jul 14, 2005 at 11:18:30AM +1000, Tim Allen wrote:

Others have given you some of the advice I would have given. One more
suggestion - does your database fit in just one "schema" in the gnumed
database?

It would, for the time being, "size-wise". However, we have
conceptually separated the data into parts, eg clinical
data, demographic data, reference data, BLOB data.

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