Обсуждение: Preserving the source code of views

Hello! I'm evaluating PostgreSQL as a replacement for SQL Server in our in-house systems. I've been really impressed with it so far, and I'm eager to try it with our data sets.

I've run across one thing that would make a transfer difficult. Postgres doesn't preserve the source code for views, as far as I can tell. It parses them and then prints them its own way. We have a lot of complicated views, where both the formatting and the comments are significant. In fact, we produce some of our system documentation directly from the comments.

Is there currently a way to preserve the original source code of a view as entered in the CREATE VIEW statement?

--Brian

1. You have access to the source of PostgreSQL, so you could modify the source code to preserve the source code of the views.
   
   
2. This might be able to be done using the extension mechanism, but I have not looked into that myself.

On Sat, 19 Oct 2013 22:38:28 -0500 Brian Crowell <brian@fluggo.com> wrote:

> Hello! I'm evaluating PostgreSQL as a replacement for SQL Server in our
> in-house systems. I've been really impressed with it so far, and I'm eager
> to try it with our data sets.
>
> I've run across one thing that would make a transfer difficult. Postgres
> doesn't preserve the source code for views, as far as I can tell. It parses
> them and then prints them its own way. We have a lot of complicated views,
> where both the formatting and the comments are significant. In fact, we
> produce some of our system documentation directly from the comments.
>
> Is there currently a way to preserve the original source code of a view as
> entered in the CREATE VIEW statement?

You could adjust your workflow to use something like dbsteward:
http://dbsteward.org/

--
Bill Moran <wmoran@potentialtech.com>

Brian Crowell <brian@fluggo.com> writes:
> I've run across one thing that would make a transfer difficult. Postgres
> doesn't preserve the source code for views, as far as I can tell. It parses
> them and then prints them its own way. We have a lot of complicated views,
> where both the formatting and the comments are significant. In fact, we
> produce some of our system documentation directly from the comments.

> Is there currently a way to preserve the original source code of a view as
> entered in the CREATE VIEW statement?

No, and it's very unlikely that there ever will be, because it's
completely against the system structure at a number of levels.  However,
there's more than one way to skin this cat.  Many people keep their DDL as
text in some external CMS, and just load it into the database again after
any change.  If you write the view as "CREATE OR REPLACE VIEW ..." in your
DDL, this is easy, at least for cases where you're not changing the set of
columns provided by the view.

            regards, tom lane

On Sun, Oct 20, 2013 at 4:24 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> No, and it's very unlikely that there ever will be, because it's
> completely against the system structure at a number of levels.  However,
> there's more than one way to skin this cat.  Many people keep their DDL as
> text in some external CMS, and just load it into the database again after
> any change.  If you write the view as "CREATE OR REPLACE VIEW ..." in your
> DDL, this is easy, at least for cases where you're not changing the set of
> columns provided by the view.

All right, so I'm getting the picture. Postgres developers are keeping
their code in larger scripts and committing them to databases as
batches, rather than editing them in place. We had a pretty slick
setup going with developers writing code against a live dev database,
with system documentation pulled from the SQL object comments, and the
ability to script the whole database to/from git when needed for
source control purposes.

In some raw form, the workflow here is more like: write a module in a
script file that re-commits all its objects to the database, and then
execute that whole batch when testing or deploying. Commit that script
to git. If I want to keep my documentation scheme, I'll need to pull
the comments out of that file. A harder sell to my developers, but not
impossible.

I noticed some objects (like views) are very picky about dependents.
Do you drop all the module's objects at the beginning of the script,
just in case there's a change in the number or types of columns? That
seems tricky, especially considering there will be modules that depend
on yours.

You also mentioned an external CMS. Any suggestions?

--Brian

On Sun, Oct 20, 2013 at 7:01 AM, Bill Moran <wmoran@potentialtech.com> wrote:
> You could adjust your workflow to use something like dbsteward:
> http://dbsteward.org/

Nifty, but without an editor, I don't think I could convince our
developers to author the databases in XML.

--Brian

On Sat, Oct 19, 2013 at 11:37 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> For my work is very significant @a point - I wrote and I am writing usually
> database centric stored procedures centric applications and @a works
> perfect. For me a SQL code is code as any other - I use a my favourite
> editor, I use a GIT for versioning, I can simple distributed application to
> my customers.

What do you do to manage dependencies among the objects? How do you
have your scripts set up to handle deploying, say, an extra column to
an existing view?

Our databases tended to be SP-heavy before I came along. I'm big on
views because that allows my client code to do very specific queries
without having to write new SPs all the time.

--Brian

Brian,

* Brian Crowell (brian@fluggo.com) wrote:
> On Sun, Oct 20, 2013 at 4:24 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > No, and it's very unlikely that there ever will be, because it's
> > completely against the system structure at a number of levels.  However,
> > there's more than one way to skin this cat.  Many people keep their DDL as
> > text in some external CMS, and just load it into the database again after
> > any change.  If you write the view as "CREATE OR REPLACE VIEW ..." in your
> > DDL, this is easy, at least for cases where you're not changing the set of
> > columns provided by the view.
>
> All right, so I'm getting the picture. Postgres developers are keeping
> their code in larger scripts and committing them to databases as
> batches, rather than editing them in place. We had a pretty slick
> setup going with developers writing code against a live dev database,
> with system documentation pulled from the SQL object comments, and the
> ability to script the whole database to/from git when needed for
> source control purposes.

Sounds pretty neat.

> In some raw form, the workflow here is more like: write a module in a
> script file that re-commits all its objects to the database, and then
> execute that whole batch when testing or deploying. Commit that script
> to git. If I want to keep my documentation scheme, I'll need to pull
> the comments out of that file. A harder sell to my developers, but not
> impossible.
>
> I noticed some objects (like views) are very picky about dependents.
> Do you drop all the module's objects at the beginning of the script,
> just in case there's a change in the number or types of columns? That
> seems tricky, especially considering there will be modules that depend
> on yours.
>
> You also mentioned an external CMS. Any suggestions?

I'm a big fan of git, but if you really want to keep things in-database
and track dependencies, etc, it occurs to me that you might be able to
use an actual table in the database to store the raw form of your view
definitions and then have an SP or something which can link up that
table to the actual views in the database and then you can look at
dependencies through the PG system catalogs...

Haven't really thought this through completely, but wanted to mention
the idea as it might help you.

    Thanks,

        Stephen

2013/10/22 Stephen Frost <sfrost@snowman.net>:

>> You also mentioned an external CMS. Any suggestions?
>
> I'm a big fan of git, but if you really want to keep things in-database
> and track dependencies, etc, it occurs to me that you might be able to
> use an actual table in the database to store the raw form of your view
> definitions and then have an SP or something which can link up that
> table to the actual views in the database and then you can look at
> dependencies through the PG system catalogs...
>
> Haven't really thought this through completely, but wanted to mention
> the idea as it might help you.

At my current workplace, view definitions (which are very old-school with
revision history etcetera recorded in comments) are stored in PL/PgSQL functions
which are updated and executed whenever views are modified. I'm looking at
adding a wrapper function to automatically detect and recreate dependencies,
not sure how well that would work. We keep track of schema changes via
pg_extractor[1] which integrates quite nicely with GIT.

[1] https://github.com/omniti-labs/pg_extractor

Regards

Ian Barwick