Re: How to keep format of views source code as entered? - Mailing list pgsql-general

Markhof, Ingolf <ingolf.markhof@de.verizon.com> writes:

> Tom, all,
>
> when I change a tables column name in Oracle SQL , SQLdeveloper (a SQL client) marks views that refer to the table
usingthe old column name as erroneous. So, I can easily identify these cases. And of course I, as a user, I am acting
inmy context, i.e. my schema. So it is perfectly clear what table I am referring to.
 
>
> Please note: I am not developing any PL/SQL code. I don't have big development projects. I have more the role of an
dataanalyst. I just create rather complex SQL queries which, from time to time, may need to be adopted to some new
requirements.Or peers want to (re-) use (part of) my SQL queries. There is not really much versioning required.
 
>
> What I understood so far is: I can use e.g. DBeaver to interact with PostgreSQL, to develop my SQL code. But I
finallyneed to copy the SQL code into e.g. Github. Which is a manual process. I'd mark the SQL code in the DBeaver
editorwindow and copy&paste it into some file in e.g. GitHub. Using Github, I'd get version control and other enhanced
collaborationfeatures which I don't really need. At the price that code transfer from SQL (DBeaver) to the code
repositoryand vice versa is complete manually?! This doesn't really look like an enhancement.
 
>
> Most likely, there are more professional ways to do that. I'd be glad to get advice.
>
> What I would like to have is something that would automatically update the SQL code in the software repository when I
runa CREATE OR REPLACE VIEW.
 
>

Even with Oracle, I found it works much better to keep all your DDL/DML
in files stored within the OS file system. This approach also works fine
with tools like DBeaver, pgAdmin, etc as all of these files also support
working with files. The main benefits I find with this approach are -

1. Adding version control is easy. Doesn't matter if it is git,
hg, bzr, svn or rcs - any version control system works fine. It doesn't
have to be a cloud service like github, though some sort of centralised
repository can be useful for managing things like backups and sharing
code across a team (I've used gitlab hosted locally t great success).
Most editors also have built-in support for common version control
systems, so the additional overhead associated with using a version
control system is very little.

2. Having all your code in version control makes tracking changes
trivial. This is often really useful in tracking down problems/bugs
caused by a change and other diagnostics. More than once, I have found
I've gone down a bad path of changes and want to restore a previous
version. Assuming you use your version control system appropriately,
this becomes trivial. If your code is only in the db, once you make
changes, the old code is gone and cannot easily be restored.

3. Having all the DDL/DML in files makes data migration very simple. I
will typically have a development environment where I develop my DDL/DM
which is separate from the production environment. This can be very
important even in data analysis and data mining type applications as it
allows you to develop complex and possibly resource hungry DML in an
environment where mistakes won't impact production systems. It also
means you can have a dev environment which is populated with specific
data sets which have been defined to help in the development process
e.g. perhaps smaller, so tests run faster or perhaps ensuring all
possible data permutations are included etc. If all your DDL/DML are in
files, seting up a new environment is as simple as writing a basic
script and using psql (pg) or sqlplus (oracle) to load the DDL/DML. In
simpler environments, you can even use a naming scheme for the files
which sets the order - loading the data then becomes as easy as 'psql
*', avoiding the need to write scripts (even though writing the scripts
is typically trivial).

4. Having all your DDL/DML in files allows you to use the many powerful
text manipulation tools which exist on most platforms. While such tools
are not often required, when they are, it can be a real bonus. Being
able to use tools like sed, awk, perl, etc have saved my bacon more than
once. Over the years, I have also built up a very useful library of
techniques, templates etc. Being able to quickly and easily access this
library is very useful.

5. While the built in editors in tools like DBeaver and pgAdmin are OK,
I find they are rarely as good as my preferred editor and I often get
frustrated at having to know/learn the editors of different tools.
Having everything based on files means I can use my preferred editor,
which has support for things like completion, familiar syntax
highlighting and key bindings, templates etc. These days, many editors
even have built-in support for popular databases like pgsql and oracle,
so you can load the code and run it without having to leave your editor.

Having the code in the database can be useful. I've used this in oracle
to provide enhanced debugging support when developing stored procedures
and packages and enhanced reporting in unit testing. However, you really
don't want this to be the definitive master for your source code. Your
source code benefits from being in a location which makes backup,
restoration and migration easy/fast, where changes can be tracked, analysed and rolled
back, where re-factoring can use advanced tools and can work across
projects, not just on a single script, where code sharing is easy and
where people can use their preferred tool rather than being forced to
use something which understands the database.

--
Tim Cross