Обсуждение: chosing a database name
Hi all, 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. The rationale is that when going from 0.1 to 0.2 I can simply create the database "gnumed0.2" and populate it with tables all the while gnumed0.1 is still up and running serving users. Downtime would only be needed to actually migrate the data (which, of course, is the bigger time sink - unless we use Slony which may or may not possible due to schema changes). The other advantage is that I can instruct my doctors to "startup the previous client version if something breaks at 3:30am and call tech support in the morning". I *am* a doctor. I have personally been in that very situation with commercial electronic medical record applications. Which is part of why I am participating in GNUmed in the first place. Now, my question is whether I am failing to see the *dis*advantages that may be looming from such an approach. Anyone has any helpful thoughts on this ? Karsten Hilbert, MD GNUmed i18n coordinator -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
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 > > Thanks, > Karsten > -- > GPG key ID E4071346 @ wwwkeys.pgp.net > E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
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