pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedthinking about versioning my database schema
5+ messages / 4 participants
[nested] [flat]
* thinking about versioning my database schema
@ 2002-03-04 07:10 Janning Vygen <vygen@gmx.de>
0 siblings, 2 replies; 5+ messages in thread
From: Janning Vygen @ 2002-03-04 07:10 UTC (permalink / raw)
To: pgsql-sql
Hi,
I had always problems when sharing my database development with
others via cvs. Every developer has its own database to test and to
run the program. But if he gets the new files via cvs which updates
the db schema, it should be an automatic process which brings the
database up to date.
I want to ask you if it is a reasonable way doing it like this:
1. In src/sql/updates developers can put files like
update1_explain.sql
2. every database has a table like
create table _version (time timestamp, filename text);
3. after cvs update every developer should run a little script which
tests if there are files in src/sql/updates which are not in the
table _version. If so, it runs psql -f src/sql/updates/filename and
doing an insert of a new line in table _version. If there are more
files to update, they will be run in the numbered order.
So i am saving the schema version inside the databse. Is it a good
idea? or is it nonsens and there is a much better way how to do it?
I dont want to recreate the database every time because i want to
keep my testdata present.
regards
janning
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] thinking about versioning my database schema
@ 2002-03-04 07:51 Dan Langille <dan@langille.org>
parent: Janning Vygen <vygen@gmx.de>
1 sibling, 0 replies; 5+ messages in thread
From: Dan Langille @ 2002-03-04 07:51 UTC (permalink / raw)
To: Janning Vygen <vygen@gmx.de>; +Cc: pgsql-admin@postgresql.org
On 4 Mar 2002 at 8:10, Janning Vygen wrote:
> So i am saving the schema version inside the databse. Is it a good
> idea? or is it nonsens and there is a much better way how to do it?
I have a system table. One of the fields is the database version. If I
change the schema, I modify the version. The applications knows what
version it can run with. If it finds an older version, it reports this ot
the user and suggests they upgrade their database.
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: thinking about versioning my database schema
@ 2002-03-04 13:09 Roberto Mello <rmello@cc.usu.edu>
parent: Janning Vygen <vygen@gmx.de>
1 sibling, 1 reply; 5+ messages in thread
From: Roberto Mello @ 2002-03-04 13:09 UTC (permalink / raw)
To: Janning Vygen <vygen@gmx.de>; +Cc: pgsql-sql
On Mon, Mar 04, 2002 at 08:10:24AM +0100, Janning Vygen wrote:
>
> So i am saving the schema version inside the databse. Is it a good
> idea? or is it nonsens and there is a much better way how to do it?
Yeah, but then you'd have to change your queries to be able to query the
new tables.
> I dont want to recreate the database every time because i want to
> keep my testdata present.
How about a script that dumps your data, dumps your db, reloads the table
definitions, then reloads your data? You could have a table with rows for
every table that would keep track of the current version you have, that
could be updated when this script ran.
-Roberto
--
+----| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |------+
Roberto Mello - Computer Science, USU - http://www.brasileiro.net/
http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer
Make way! Make way! A PROGRAMMER HAS ARRIVED!!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: thinking about versioning my database schema
@ 2002-03-04 13:49 Janning Vygen <vygen@gmx.de>
parent: Roberto Mello <rmello@cc.usu.edu>
0 siblings, 0 replies; 5+ messages in thread
From: Janning Vygen @ 2002-03-04 13:49 UTC (permalink / raw)
To: Roberto Mello <rmello@cc.usu.edu>; +Cc: pgsql-sql
Am Montag, 4. März 2002 14:09 schrieb Roberto Mello:
> On Mon, Mar 04, 2002 at 08:10:24AM +0100, Janning Vygen wrote:
> > So i am saving the schema version inside the databse. Is it a
> > good idea? or is it nonsens and there is a much better way how to
> > do it?
>
> Yeah, but then you'd have to change your queries to be able to
> query the new tables.
>
> > I dont want to recreate the database every time because i want to
> > keep my testdata present.
>
> How about a script that dumps your data, dumps your db, reloads the
> table definitions, then reloads your data? You could have a table
> with rows for every table that would keep track of the current
> version you have, that could be updated when this script ran.
i cant reload my dumped data if the table definition changed. maybe i
have renamed a column or something like this. how should my dumped
data know this? So i have to write an update script. But then i have
to write the new schema AND have to write update scripts and still
hve trouble with other developers who should easily get a
corresponding schema for alle the queries they just fetched via cvs
janning
> -Roberto
--
Planwerk 6 /websolutions
Herzogstraße 86
40215 Düsseldorf
fon 0211-6015919
fax 0211-6015917
http://www.planwerk6.de
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: thinking about versioning my database schema
@ 2002-03-05 08:13 Matteo Beccati <m.beccati@crpsoftware.it>
0 siblings, 0 replies; 5+ messages in thread
From: Matteo Beccati @ 2002-03-05 08:13 UTC (permalink / raw)
To: pgsql-sql
Hi,
> How about a script that dumps your data, dumps your db,
> reloads the table
> definitions, then reloads your data? You could have a table
> with rows for
> every table that would keep track of the current version you
> have, that
> could be updated when this script ran.
This is exactly what I did for the update script of my own project
phpPgAds...
You could take a look to the code if you're interested
http://phppgads.sourceforge.net
Regards
--
Matteo Beccati
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-03-05 08:13 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-04 07:10 thinking about versioning my database schema Janning Vygen <vygen@gmx.de>
2002-03-04 07:51 ` Dan Langille <dan@langille.org>
2002-03-04 13:09 ` Roberto Mello <rmello@cc.usu.edu>
2002-03-04 13:49 ` Janning Vygen <vygen@gmx.de>
2002-03-05 08:13 Re: thinking about versioning my database schema Matteo Beccati <m.beccati@crpsoftware.it>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox