Thread: from PG_DUMP to CVS
Hi All, Does somebody know of a script that is capable of creating a CVS tree based on the structure of a given schema or database? I have a development DB that is structured with a lot of tables, functions, views, indexes... I've been asked to publish all the changes under a CVS tree, separating the objects in different sources and organizing it someway. This should include all the inserts that are used to prime tha database. Does somebody know of a script that does this job? regards, Riccardo
At 08:04 PM 26/08/2004, Riccardo G. Facchini wrote: >Does somebody know of a script that does this job? No, but a very useful idea. Sounds like another dump format to me -- so long as a well-defined structure that is likely to remain invariant over versions can be used. A client uses a trivial script to dump functions, but not tables, triggers or anything else. The simplest format might be to use the database names as a level 1 dir, schema names at level 2, and entry names at level 3, then create files for the 'definition', 'data' etc. It would be a little confusing when ACLs and constraints were dumped (eg. they would not appear under the tables). But it would be consistent, at least. Of course it would be impossible to restore from such a beast since we would be throwing away ordering -- I think. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 03 5330 3172 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp.mit.edu:11371 |/
--- Philip Warner <__> wrote: > At 08:04 PM 26/08/2004, Riccardo G. Facchini wrote: > >Does somebody know of a script that does this job? > > No, but a very useful idea. > > Sounds like another dump format to me -- so long as a well-defined > structure that is likely to remain invariant over versions can be > used. A > client uses a trivial script to dump functions, but not tables, > triggers or > anything else. > > The simplest format might be to use the database names as a level 1 > dir, > schema names at level 2, and entry names at level 3, then create > files for > the 'definition', 'data' etc. > > It would be a little confusing when ACLs and constraints were dumped > (eg. > they would not appear under the tables). But it would be consistent, > at least. > > Of course it would be impossible to restore from such a beast since > we > would be throwing away ordering -- I think. > [..] The plan is not to have something to be restored, but something that can be published on a CVS server, in order to see what has been modified overtime... I'd love to have this beast running daily. If you know of something even similar to what I'm looking for, let me know. regards, Riccardo
At 08:43 PM 26/08/2004, Riccardo G. Facchini wrote: >If you know of something even similar to what I'm looking for, let me >know. My thinking is to modify pg_dump to add a new output format, but I'd like to get some more feedback from others first, including yourself. Does what I specified before satisfy all your needs? ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 03 5330 3172 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp.mit.edu:11371 |/
On Thursday 26 August 2004 04:48 pm, Philip Warner wrote: > At 08:43 PM 26/08/2004, Riccardo G. Facchini wrote: > >If you know of something even similar to what I'm looking for, let me > >know. > > My thinking is to modify pg_dump to add a new output format, but I'd like > to get some more feedback from others first, including yourself. Does what > I specified before satisfy all your needs? there is a utility that does something like this - it is mentioned in a thread on this list relating to surrogate keys - posted by Karsten Hilbert - about a month back - sorry cant be more specific, i've stored the link somewhere, cant find it -- regards kg http://www.onlineindianhotels.net - fastest hotel search website in the world http://www.ootygolfclub.org
--- Philip Warner <__> wrote: > At 08:43 PM 26/08/2004, Riccardo G. Facchini wrote: > >If you know of something even similar to what I'm looking for, let > me > >know. > > My thinking is to modify pg_dump to add a new output format, but I'd > like > to get some more feedback from others first, including yourself. Does > what > I specified before satisfy all your needs? > > > ---------------------------------------------------------------- > Philip Warner | __---_____ > Albatross Consulting Pty. Ltd. |----/ - \ > (A.B.N. 75 008 659 498) | /(@) ______---_ > Tel: (+61) 0500 83 82 81 | _________ \ > Fax: (+61) 03 5330 3172 | ___________ | > Http://www.rhyme.com.au | / \| > | --________-- > PGP key available upon request, | / > and from pgp.mit.edu:11371 |/ > > yes, I think a specific format for pg_dump could be interesting. From my point of view, I would like to get a directory structure like this one: <database name>/schemas /<schema name> /<schema name>.sql /tables /<table name>.sql /views /<view name>.sql /functions /<function name>.sql /domains /<domain name>.sql /types /<type name>.sql /aggregates /<aggregate name>.sql /operators /<operator name>.sql /priming /<table name>.sql /acls /schemas /<schema name> /<schemaname>.sql /tables /<table name>.sql /views /<view name>.sql /functions /<function name>.sql /domains /<domain name>.sql /types /<type name>.sql /aggregates /<aggregate name>.sql /operators /<operator name>.sql/languages /<language name>.sql either following the pgadmin3 style or, better still, the ems postgresql manager output style (I believe is superior, even if not OS). My feeling is that a lot of the code for an output of this kind is already in the pgadmi3 code, so it could be a matter of codebashing. I wouldn't separate the functions from the triggers, but beware of the function overloading, but that's a small issue compared to the task. best regards, ===== Riccardo G. Facchini
--- Kenneth Gonsalves <__> wrote: > On Thursday 26 August 2004 04:48 pm, Philip Warner wrote: > > At 08:43 PM 26/08/2004, Riccardo G. Facchini wrote: > > >If you know of something even similar to what I'm looking for, let > me > > >know. > > > > My thinking is to modify pg_dump to add a new output format, but > I'd like > > to get some more feedback from others first, including yourself. > Does what > > I specified before satisfy all your needs? > > there is a utility that does something like this - it is mentioned in > a > thread on this list relating to surrogate keys - posted by Karsten > Hilbert - > about a month back - sorry cant be more specific, i've stored the > link > somewhere, cant find it > -- > regards > kg > > http://www.onlineindianhotels.net - fastest hotel search website in > the world > http://www.ootygolfclub.org > After searching throught the list, I assume you mean this link: http://www.rbt.ca/autodoc/index.html by Rod Taylor. Looks promising, but still what I need is a proper CVS output, as I need to review the changes made to the specific database structure. thanks for the link,
Hi! What I'm looking for is something that handles DB schema upgrades. We're building applications which require upgrades to the DB schema over time, so when we upgrade our apps we need to modify the DB. For this, we're using SQL scripts, which basically do incremental updates, e.g. from 1.2 to 1.3. So when we upgrade FooBar Version 2.3, which uses DB schema 1.3 to Version 3.9, which uses DB schema 1.9 we would run 6 scripts, and voila, we would have a 1.9 schema. OK, sounds simple. But it's sometimes a lot of work to build the update script, and easy to miss something. So I was just thinking whether there's a tool or script out there that builds an update script from two different schema dumps. Does anyone out there know of such a beast? Greetings,Jörg -- Leading SW developer - S.E.A GmbH Mail: joerg.hessdoerfer@sea-gmbh.com WWW: http://www.sea-gmbh.com
"Riccardo G. Facchini" <abief_ag_-postgresql@yahoo.com> writes: > After searching throught the list, I assume you mean this link: > http://www.rbt.ca/autodoc/index.html > by Rod Taylor. > > Looks promising, but still what I need is a proper CVS output, as I > need to review the changes made to the specific database structure. Well, CVS can still be useful even if the changes are all in one file. Look at "cvs annotate" for example. And CVS diff would still give you useful information. -- greg
Riccardo, > Looks promising, but still what I need is a proper CVS output, as I > need to review the changes made to the specific database structure. If it's Perl, I'd be interested in contributing. I've long needed something like this myself. -- Josh Berkus Aglio Database Solutions San Francisco
At 02:38 PM 27/08/2004, Josh Berkus wrote:
>If it's Perl, I'd be interested in contributing. I've long needed something
>like this myself.
My thinking at this stage is to try to get pg_dump/restore to produce the
output directly. Something like:
some-dbname/create.sql some-dbname/drop.sql some-dbname/econding.sql ...
some-dbname/some-schema/TABLE/sometable/create.sql some-dbname/some-schema/TABLE/sometable/drop.sql ...
some-dbname/some-schema/CONSTRAINT/someconstraint/create.sql
some-dbname/some-schema/CONSTRAINT/someconstraint/create.sql ... some-dbname/some-schema/ACL/some-table.sql
some-dbname/some-schema/ACL/some-function(int).sql ...
etc.
This would be easy. Question is, how useful would it be?
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \| | --________--
PGP key available upon request, | /
and from pgp.mit.edu:11371 |/
That depends on the use you plan to... as a backup, useless, as a documentary tool, very (at least, for what I need). Our way of developing things is to set up a development box, and set up the tables, functions, etc etc etc using a set of tools... the only problem is that these do not provide any kind of versioning control. At least, not one that the user will be able to understand or use. The problem that we face right now is that we provide not only versioning control, but web access to it! CVS is agreed on (both parties like it), but we need a quick way to convert the gazillions of tables, rules, views, functions, procedures, etc etc etc to an output format that can be moved to the CVS and, if necessity arouse, use the single files to perform maintenance on the user's end. This way, we are happy because we don't need to revise our way of development and the user is happy because he sees what we're doing, and he's able trace the changes. One other good things is that if this is done the right way, almost no human time is required to provide the info, and the tool can run daily. regards, Riccardo --- Philip Warner <__> wrote: > At 02:38 PM 27/08/2004, Josh Berkus wrote: > >If it's Perl, I'd be interested in contributing. I've long needed > something > >like this myself. > > My thinking at this stage is to try to get pg_dump/restore to produce > the > output directly. Something like: > > some-dbname/create.sql > some-dbname/drop.sql > some-dbname/econding.sql > ... > some-dbname/some-schema/TABLE/sometable/create.sql > some-dbname/some-schema/TABLE/sometable/drop.sql > ... > some-dbname/some-schema/CONSTRAINT/someconstraint/create.sql > some-dbname/some-schema/CONSTRAINT/someconstraint/create.sql > ... > some-dbname/some-schema/ACL/some-table.sql > some-dbname/some-schema/ACL/some-function(int).sql > ... > > etc. > > This would be easy. Question is, how useful would it be? > > > > ---------------------------------------------------------------- > Philip Warner | __---_____ > Albatross Consulting Pty. Ltd. |----/ - \ > (A.B.N. 75 008 659 498) | /(@) ______---_ > Tel: (+61) 0500 83 82 81 | _________ \ > Fax: (+61) 03 5330 3172 | ___________ | > Http://www.rhyme.com.au | / \| > | --________-- > PGP key available upon request, | / > and from pgp.mit.edu:11371 |/ > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html >
Philip,
> My thinking at this stage is to try to get pg_dump/restore to produce the
> output directly. Something like:
Hey, you do what you want, of course. However, it seems to me that hacking
AutoDoc would be a *lot* less effort than hacking pg_dump.
Interestingly, though, I was talking to someone on IRC (Neil? Gavin?) some 6
months ago or so about hacking a "PSQL-FS" that is, an interface to the
*live* database which would look like a filesystem. Were this done, it
would be child's play to rsync it with an archive. Whomever it was didn't
seem to think it too challenging a task.
> some-dbname/create.sql
> some-dbname/drop.sql
> some-dbname/econding.sql
> ...
> some-dbname/some-schema/TABLE/sometable/create.sql
> some-dbname/some-schema/TABLE/sometable/drop.sql
Hmmm. I see a slightly different structure below the schema level:
some-dbname/some-schema/TABLES/sometable/create.sql
some-dbname/some-schema/TABLES/sometable/indexes.sql
some-dbname/some-schema/TABLES/sometable/constraints.sql
some-dbname/some-schema/TABLES/sometable/triggers.sql
some-dbname/some-schema/TABLES/sometable/rules.sql
some-dbname/some-schema/VIEWS/someview/create.sql
some-dbname/some-schema/VIEWS/someview/rules.sql
some-dbname/some-schema/FUNCTIONS/somefunction/param{codes}.sql
some-dbname/some-schema/TYPES/sometype/create.sql
some-dbname/some-schema/OPERATORS/someoperator/create.sql
However, the above is somewhat unfriendly to CVS, as one can't drop
directories in CVS and that would be entailed in the dropping of any objects.
An alternative would be:
some-dbname/some-schema/TABLES/sometable-create.sql
some-dbname/some-schema/TABLES/sometable-indexes.sql
some-dbname/some-schema/TABLES/sometable-constraints.sql
some-dbname/some-schema/TABLES/sometable-triggers.sql
some-dbname/some-schema/TABLES/sometable-rules.sql
some-dbname/some-schema/VIEWS/someview-create.sql
some-dbname/some-schema/VIEWS/someview-rules.sql
some-dbname/some-schema/FUNCTIONS/somefunction-param{codes}.sql
some-dbname/some-schema/TYPES/sometype-create.sql
some-dbname/some-schema/OPERATORS/someoperator-create.sql
or even:
some-dbname/some-schema/TABLES/sometable.sql
some-dbname/some-schema/VIEWS/someview.sql
some-dbname/some-schema/FUNCTIONS/somefunction-param{codes}.sql
some-dbname/some-schema/TYPES/sometype.sql
some-dbname/some-schema/OPERATORS/OPsomeoperator.sql
In this last, all dependant objects of, for example, a table (rules, triggers,
indexes, etc. ) would be rolled up into one file. It's this last version
that I personally favor.
--
Josh Berkus
Aglio Database Solutions
San Francisco
At 02:02 AM 28/08/2004, Josh Berkus wrote:
>some-dbname/some-schema/TABLES/sometable.sql
>some-dbname/some-schema/VIEWS/someview.sql
>some-dbname/some-schema/FUNCTIONS/somefunction-param{codes}.sql
>some-dbname/some-schema/TYPES/sometype.sql
>some-dbname/some-schema/OPERATORS/OPsomeoperator.sql
>
>In this last, all dependant objects of, for example, a table (rules,
>triggers,
>indexes, etc. ) would be rolled up into one file. It's this last version
>that I personally favor.
Not sure I like it from the PoV of easily seeing what has changed.
Similarly:
>some-dbname/some-schema/TABLES/sometable/create.sql
>some-dbname/some-schema/TABLES/sometable/indexes.sql
>some-dbname/some-schema/TABLES/sometable/constraints.sql
combines all indexes into one file. It also has the disadvantage if being
impossible to construct from an existing dump file.
I'd like to be able to construct the structure from the information stored
in a dump file, without parsing SQL. OTOH, it might be nice to add some
more information to the dump file.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \| | --________--
PGP key available upon request, | /
and from pgp.mit.edu:11371 |/