Taming of the Shrew

Do you like to develop databases? No, not newfangled NoSQL, but good old relational ones, where you can describe relationships and stored procedures for data access and logic. Maybe you are developing databases for PostgreSQL? If yes, then great - this post is definitely for you.

There is probably no point in describing the benefits of PostgreSQL . Let me just say in a nutshell that this is a modern, fast, richly capable DBMS that is fully capable of competing with commercial database management systems. The fact that PostgreSQL is distributed under a free license , similar to the BSD license (which allows you to use it without license fees in commercial projects and opens up full access to the source code without having to open your own when changes are made), is being actively developed now (most recently , in January 2016, version 9.5 was released with some very nice improvements ), which allows us to state that PostgreSQL could be one of the best DBMS available today. But what prevents gaining popularity among the PostgreSQL developers?


')
One of these factors is a fairly small number of tools for both database development and further maintenance. Of course, there are JDBC drivers for PostgreSQL, and all those tools that use JDBC work with it, but these tools are usually universal and can not always use the features of a particular DBMS.

What features of development are we talking about? For example, quite often, when you modify existing database objects (usually tables or views), you may encounter an object modification error, because PostgreSQL does not allow this, because there are dependent objects.

ags=# create table t1 (f1 text); CREATE TABLE ags=# create view v1 as select * from t1; CREATE VIEW ags=# alter table t1 alter column f1 type char(5); ERROR: cannot alter type of a column used by a view or rule : rule _RETURN on view v1 depends on column "f1" 

This feature has already bothered many developers, is fixed in TODO wiki.postgresql.org/wiki/Todo#Views_and_Rules and has some possible solutions (for example: mwenus.blogspot.nl/2014/04/postgresql-how-to-handle-table- and-view.html ).

Or another example. Did this happen to you: you created a stored procedure, began to actively use it, and after a while, as is usually the case, you wanted to modify it. And only when you start to understand why the logic partially works, and partially not, do you notice that you actually created an additional procedure with a new signature (which is used by a part of the code), while the other part of the code uses the old procedure that was accidentally left due to oversight?

 ags=# \df f1    |  |    |    |  --------+-----+-----------------------+------------------------+--------- public | f1 | void | |  public | f1 | void | p1 integer |  (2 ) 

To face this situation is very simple, and this is facilitated, in my opinion, by the absence of grouping objects like Oracle packages, which logically combine stored procedures and roll onto the base, usually in one package, which eliminates the appearance of “forgotten” objects. Yes, of course, if you test your code, then the probability of hitting such objects in battle is small, but this behavior increases the complexity of tracking.

What to do if you want to compare two databases and create a script for converting one to another? Most likely, you will want to use Liquibase , however, you will be unpleasantly upset when you learn that Liquibase does not know anything about how to solve emerging problems with dependencies, which I mentioned a little earlier.

We faced similar issues when we started the migration process from MSSQL to PostgreSQL about three years ago. At that time, we used Redgate SQL Source Control to work with MSSQL and were terribly annoyed by the lack of such a tool for working with PostgreSQL. They were so annoyed that they decided to create their own tool capable of tracking changes in the database and create scripts for migrating selected objects both interactively and automatically.

Since we are programming mostly in Java, the decision to choose a development platform came quickly enough. The application began to evolve, after several iterations of trial and error, as a set of Eclipse add-ons.

After a while, our product, pgCodeKeeper, was born.

In short, his work can be described as follows: the database objects are saved to disk as an Eclipse project, which can later (if desired) be placed in the version storage system. After comparing the database with the project or any of the branches of the project, you can create a migration script from the database (to transfer the state of objects from the project to the database), and in the opposite direction. And due to the fact that we use our own ANTLR grammars to parse objects, this allows us to build well-developed object dependency graphs, which leads to the creation of correct migration scripts (taking into account the existing problems with dependencies that I voiced above).

Using pgCodeKeeper can help build a workflow for making changes to the database. At home we successfully use the following scheme:

1. The developer makes changes to the developer database (without thinking about creating a migration script that will be needed when running into a combat database). By the way, a developer database can be one shared between several developers. When applying changes, pgCodeKeeper allows you to transfer only selected objects.
   
2. Changes (if necessary, changes only parts of the objects) made by the developer using pgCodeKeeper are transferred to the development branch of the project and a request to merge with the main branch is created.
   
3. The merger request is verified and accepted by the responsible person.
   
4. After accepting the merge request, pgCodeKeeper generates a migration script (it warns you if instructions are formed in the script that can lead to data loss) from the main branch to the combat database.
   
5. The created script rolls onto the combat database.
   

The first two points are performed by the developer, the third is performed by the inspector, the fourth and fifth can be performed by the person accompanying the database. The fourth and fifth paragraphs can also be performed automatically, and this can be very convenient for building the processes of continuous delivery [Continuous Delivery], but in the current article we will not talk about this.

In the described workflow, not all actions are performed via pgCodeKeeper. For example, the creation of a new branch in the version control system is performed by the EGit add- on for Eclipse. While checking the request for merging code [merge request code review] we perform using the capabilities of GitLab .

Those. The main goal of pgCodeKeeper is to compare a previously created project with a database instance, identify modified objects and apply changes either to a project from a database (modification of project files) or to a database from a project (by creating a migration script).

The time has passed, the project started successfully developed and at some point there was no doubt that the product copes with current requirements for maintaining our internal databases excellently, but the product backlog is not at all empty. And it is filled with those features or errors that could potentially be found in databases of other developers. It became clear that the project will either remain within the framework of a corporate project (and, possibly, will go into a stagnation stage, because the current requested features have been completed almost all), or will try to enter the public market and receive feedback on the following questions through feedback:

1. Is the product currently in demand in the software market?
   
2. If it is claimed, which product distribution / licensing model may be the most interesting both for us and the market?
   

Now we are ready to release pgCodeKeeper to a public beta test with the only condition of use - providing feedback. Is there anyone willing to try the product at work?

We try in

So, I hope that if you have read this far, you are already interested enough to try pgCodeKeeper in the work. Ready? Getting started.

As I wrote earlier, at the end of its evolutionary path, pgCodeKeeper began to be a set of add-ons for the Eclipse platform. This means that everything is excellent with multiplatform, and it works both under Linux and under Windows. Naturally, it will work under other platforms on which the Eclispe platform can be run, but we use only these two.

For pgCodeKeeper to work correctly, Eclipse Juno platform version or higher is required, however, this note applies to those who want to install into an existing Eclipse instance. For new Eclipse installations, get the latest version from eclipse.org/downloads . You can install the Eclipse IDE for Java Developers (because, among other things, it has already installed add-ons for integration with Git and relatively small size), or choose any that you like (do not forget: Eclipse is a Java application , and for it to work, you must first install the JRE / JDK).



Select the menu items Help - Install new software and enter the URL of the update site: pgcodekeeper.ru/update/release

Select and install the pgCodeKeeper add-on. We are overloading Eclispe and, if we see the pgCodeKeeper project in the list of wizards, we consider the installation mission to be completed.

The job of pgCodeKeeper comes down to comparing objects in a project and a database, at the time of creating a project you should already have a database with which you want to make an initial “cast” of the project. Let's create a database and try to go through the database development process.


Well, we have a database, it's time to have some fun and write some code. Change table t1:

 (ags@10.84.0.6:5432) 15:12:28 [dev] =# alter table t1 alter column f1 type char(5); ERROR: cannot alter type of a column used by a view or rule : rule _RETURN on view v1 depends on column "f1" : 2,393  

Quite expected - we can not perform the requested action, because representation v1 depends on the table t1. Moreover, in order to remove v1, we will have to delete both v2 and f1.

 (ags@10.84.0.6:5432) 15:16:05 [dev] * =# drop view v1; ERROR: cannot drop view v1 because other objects depend on it : view v2 depends on view v1 function f1(integer) depends on type v2 : Use DROP ... CASCADE to drop the dependent objects too. : 1,631  

Yes ... it looks like we are in a small trap, now we have to resort to using miracle scripts (for example, similar to the one I wrote about above ... true, they do not work with all the objects that can fall into the dependency graph), or use pgCodeKeeper (there is another option to delete the view and dependent objects in a cascade and then restore the lost objects from the previously saved dump, but we will not use this “opportunity”).

It's time to create a project to maintain the database.



Enter the name of the new project:



We are setting up the source for the database (yes, you may encounter a slightly unobvious moment of adding a new source, but be patient, we will definitely improve this in the future). I personally usually store passwords in .pgpass and pgCodeKeeper can take them from it, but if you don’t use it, fill in the password field in the wizard.



Creating a project is completed, click the “Finish” button. If you did everything correctly, the new project will be initialized and populated with database objects.



The database object in the project is a readable file, which can also be edited.



Currently, pgCodeKeeper is not positioned as a full-fledged SQL editor or PL / pgSQL code, but sometimes - like, for example, in this case - there is nothing better than editing the file directly in the editor. Let's change the type of the only field from text to char (5).



Let's go to the main project panel (the bottom tab is “Update DB”) and click the “Get changes” button. pgCodeKeeper displayed a list of objects that differ both in the database and in the project. Diff panel shows detailed changes in the database objects.



Well, now just a little remains, tick the database objects that we want to synchronize with the project objects and press the “Generate Script” button. pgCodeKeeper will helpfully report that the generated script contains dangerous instructions that can lead to data loss and will generate the following script:


Hooray!!! A couple of mouse clicks (except for project initialization) and we can form migration scripts! The resulting script can be rolled on its own through pgAdmin / psql, or through pgCodeKeeper. Since in PostgreSQL, DDL instructions are transactional, I specify the necessity of executing a script in one transaction (key -1 in psql) when rolling in such scripts in order to prevent the inconsistent database state from occurring in case an error occurs during the execution of the script.

If we now re-perform a comparison of the project and the database, we will see that the objects in the project and the database ... Are different?

Without panic, this is due to the fact that when we made changes to the project with our hands, we specified the abbreviated form of character type as char, in the database it is now displayed in full form.



To update the project, switch to the lower tab “Update project”, click “Get changes”, select the necessary objects and click on the “Apply selected changes” button. After performing these steps, the database objects and the project will become identical.

Famous product features

Since the product was originally developed for internal databases, only those object types that we use were first tested, some are not yet supported (for example, FOREIGN TABLE). pgCodeKeeper does not support work with all versions of PostgreSQL, currently it is guaranteed to work for versions 9.3 and above (you need to check - it is possible that it will work with 9.1-9.2, but not earlier).

Conclusion

Today you met a new product that makes it easier to work with PostgreSQL, I told you about the main features of the product, without mentioning such topics as: manually adding dependencies (if our parser did not cope, you can always tell it), work with version control systems, work pgCodeKeeper in automatic, non-interactive mode.

The article did not go without neologisms, jargon or a few outdated turns of speech, in all cases I indicated the meaning of a word or expression, when it was first used, in English most accurately defines it.

Currently, pgCodeKeeper is in the process of registering in the domestic software registry in accordance with Government Decree No. 1236 of November 16, 2015 “On imposing a ban on the admission of software originating from foreign countries for procurement purposes for meeting state and municipal needs” www. garant.ru/hotlaw/federal/671898 and this means that in the near future pgCodeKeeper will be able to participate in the program of import substitution.

Using pgCodeKeeper makes it easier to maintain PostgreSQL databases (even the addictive effect was noticed).

How interesting was pgCodeKeeper?