Preserving the source code of views

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Preserving the source code of views

Brian Crowell
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
Reply | Threaded
Open this post in threaded view
|

Re: Preserving the source code of views

Gavin Flower-2
On 20/10/13 16:38, Brian Crowell 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?

--Brian
The 'Real Experts' may well have more practically elegant solutions, but a couple possibilities I can think of are as follows:

  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.

So yes, you can do what you want, but not necessarily as easily as you would like.


Cheers,
Gavin
Reply | Threaded
Open this post in threaded view
|

Re: Preserving the source code of views

Pavel Stehule
In reply to this post by Brian Crowell
Hello


2013/10/20 Brian Crowell <[hidden email]>
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?


I don't known about any way, how to do it (without hacking postgresql source code). PostgreSQL saves a views in preprocessed form from performance reasons.

There are a few recommendation how to solve this issue - I never had a problem with it, because I use a different workflow.

a) never modify a database object structure in database with admin tools. Use a SQL scripts ever.
* a admin tools has not good has not good editors
* there are no possibility to join related code together
* there are no good versioning
* a portability of handly written SQL scripts is significantly better than SQL scripts generated by admin tools

I ever write a SQL scripts saved in files - then I can to push on one place (one file) related different objects - triggers, views, tables, procedures - with comments on file start, and with comments before any object.

b) if you don't like @a, use a COMMENTs
 
postgres=# create view simply as select 10;
CREATE VIEW
postgres=# comment on view simply is 'very simple view';
COMMENT

postgres=# \dv+
                      List of relations
 Schema │  Name  │ Type │ Owner │  Size   │   Description   
────────┼────────┼──────┼───────┼─────────┼──────────────────
 public │ simply │ view │ pavel │ 0 bytes │ very simple view
(1 row)

a 9.2 and newer PostgreSQL formats a materialized view - so it lost your formatting, but result will be in good format too.

postgres=# create view simply as select 10 from pg_class where true;
CREATE VIEW
Time: 97.584 ms
postgres=# \d+ simply
                  View "public.simply"
  Column  │  Type   │ Modifiers │ Storage │ Description
──────────┼─────────┼───────────┼─────────┼─────────────
 ?column? │ integer │           │ plain   │
View definition:
 SELECT 10
   FROM pg_class
  WHERE true;

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.

Regards

Pavel


--Brian

PT
Reply | Threaded
Open this post in threaded view
|

Re: Preserving the source code of views

PT
In reply to this post by Brian Crowell
On Sat, 19 Oct 2013 22:38:28 -0500 Brian Crowell <[hidden email]> 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 <[hidden email]>


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: Preserving the source code of views

Tom Lane-2
In reply to this post by Brian Crowell
Brian Crowell <[hidden email]> 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


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: Preserving the source code of views

Brian Crowell
On Sun, Oct 20, 2013 at 4:24 PM, Tom Lane <[hidden email]> 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


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: Preserving the source code of views

Brian Crowell
In reply to this post by PT
On Sun, Oct 20, 2013 at 7:01 AM, Bill Moran <[hidden email]> 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


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: Preserving the source code of views

Brian Crowell
In reply to this post by Pavel Stehule
On Sat, Oct 19, 2013 at 11:37 PM, Pavel Stehule <[hidden email]> 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


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: Preserving the source code of views

Pavel Stehule



2013/10/21 Brian Crowell <[hidden email]>
On Sat, Oct 19, 2013 at 11:37 PM, Pavel Stehule <[hidden email]> 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?

I write a delta scripts - that ensure moving database structure to expected state. Now, you can use a infrastructure in PostgreSQL related to CREATE EXTENSION that is able to manage these delta scripts. But I wrote own (simple) three years ago. When you manage these scripts (together with regress tests) continuously, then it works - and delta scripts are a good self documentation again - there is usually different delta than GIT repository. I'll do more often commits in GIT than I delivery features to customer.
 

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.

There is little bit different approach in PG than MSSQL. Some times is recommended to write procedures for all in MSSQL. PG is similar to Oracle. Procedures (functions) are written only for processes that changes data (or for triggers). For queries use a views mainly. Only when views cannot be used from some reasons (more complex security, some auditing) use a table functions.

Regards

Pavel
 

--Brian

Reply | Threaded
Open this post in threaded view
|

Re: Preserving the source code of views

Stephen Frost
In reply to this post by Brian Crowell
Brian,

* Brian Crowell ([hidden email]) wrote:

> On Sun, Oct 20, 2013 at 4:24 PM, Tom Lane <[hidden email]> 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

signature.asc (205 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Preserving the source code of views

Ian Lawrence Barwick
2013/10/22 Stephen Frost <[hidden email]>:

>> 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


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general