While working with Databases (hereinafter – the DB), one knows that the programs processing data are set up, operated, they get out of date and are renewed. The data always remain and are only added. In addition, the DB structure doesn’t stay preserved in aspic. New tables are created; the current ones are revised, etc.
Maintaining the DB schema on the production server, especially if there are changes from more than one developer, is always a real source of major concern for those who carry the responsibility. Generally, developers code a sort of «incremental updates» and a certain staff member tries to combine them without bringing any damage. Sometimes he even gets it right.
Such a maintaining becomes very complicated if there are a significant number of remote clients with their own DBs. In this case, there can be different versions of one DB. At worst, the number of the incremental updates begins to increase proportionally with our DB published versions multiplied by client’s amount.
The other method is dbProjector. Its usage ensures all the DB objects as a set of text files which keep the whole data about the DB current objects. DbProjector is able to generate and run such a set of scripts that will convert the DB schema from the current client’s DB to the developer’s one.
This set of text files may (must) be stored in the Version control system used by developers. Further on, there always will be answers to the most important questions ‘WHO did and WHAT EXACTLY did this person in the DB two years ago that resulted in the current falldawn’. If it is provided in the check-in policy to fill the comment fields, there will be an answer to a question ‘what was the purpose in doing that’. And as is well-known, questions ‘why was it done and who does in need now’ are the most difficult while developing the enterprise software.
Together with the DB objects schema, the table data synchronization is also maintained. This makes it possible to keep either data required for the user’s software or various referenced data in the consistent state with the express aim of this DB version.
dbProjector supported the project management for the following DBMS:
- Microsoft SQL Server – all 2008 to 2016 versions
- PostgreSQL – all 9.1 to 9.6 versions
dbProjector only requires .NET Framework 4.5 (it is part of all current versions of Windows), or Mono (http://www.mono-project.com/) for Linux systems.
To set up a new front-end DB or to update the current one to a new version, the dbpDeploy utility in two versions is used:
- dbpDeploy.exe – a console application with setup options set by command-line parameters.
- dbpDeployGui.exe – a GUI wizard application where the user can set the DB update options (e.g., a server name and authorization parameters) in the interactive mode.
Via these means, a complicated updating procedure with any DB version available (with an undefined number of the client’s own modifications) becomes as easy as common installation of new software and there is no need in highly qualified administration staff.
Work with DbProjector begins with creating a new project through the menu option: «File → New Database Project», where one has to select a DB type (MSSQL Project or PostgreSQL Project), a DB name (this name will be used in DbProjectorInstaller), and a DB project name.
Upon the DB project creation, one has to import the DB schema from the current expanded DB to the selected DBMS; then to save the imported DB objects to disk. The DB object’s scripts may be added to the used by developers Version Control System (VCS).
The scripts generated by DbProjector are not common text files but the can be viewed in the text editor and further compared via Diff of the used VCS.
A typical scenario with DbProjector for the developer consists of the following stages:
- Update a local copy of the project to the latest version from VCS
- Synchronize a developer’s local DB with the DB project (Tools → Export Schema to Database)
- Create or edit any objects on the local DB from any tool convenient for the developer (EM, QA, pgAdmin etc.)
- Import the DB changed objects to the project (Tools → Import Schema to Project)
- Save the project changed objects to the VCS. (Before that, it’s better to make sure that these updates can generally be completed. For example, if one adds a column NOT NULL without Default to the existing table, in case of real Deploy on the front end this instruction can’t be fulfilled without a special tricks)
DB objects as files
Each DB object of the first level (these are the DB objects belongs directly to the schema: tables, procedures, etc., and on the contrary, the objects of the second level – columns in the tables, keys, indexes, grants – with parent objects, they are always belong to a table or another object of this level) is one file that includes all the sub-objects of this file. I.e., in one file with the table definition, all the objects belonging to the table are also saved: columns, keys, constraints, indexes, grants etc. serve as a single storage unit.
Under the project’s deploy (hereinafter – synchronization of the real DB objects with the DB project objects’ versions), a correspondence of the project objects with the DB objects is set up on the real DB.
The following correspondence categories set up:
- In the project only. To run a command in the DB to create such an object.
- In both, but the objects differ in characteristics. To generate such a SQL-script for modifying this object on the DB that will ensure its correspondence with the type availablein the project.
- In both, and the objects are identical. Do nothing.
- In the DB. To generate a delete command for such an object.
It is necessary to understand that converting one DB schema (in this case, to the saved one as the DB project) to the other one available on the DB is a complicated process. There are many dependencies among DB objects. For example, you cannot create a Foreign Key to a table that has not been created yet and there are a lot of rules like that.
In PostgreSQL, it is completely impossible to update the object if it has any dependencies. All the dependent objects are firstly to be deleted one by one ranging from the last in the dependencies chain, and secondly to be neatly re-created in reverse order.
DbProjector is able to cope with almost all these situations alone, on a real time basis, depending on what was found on the target DB.
There are also other situations to keep in mind which DbProjector isn’t able to cope with. For example, if the developer adds a new column to the current table with NOT NULL specifier and doesn’t define DEFAULT. This instruction will cause an exception and all the whole transaction with fulfilled updating instructions will be canceled.
Special objects of DbProject
In the DbProject, there can also be other objects that are not generated by the DB schema import. The developer creates them via the DbProjector shell, and they only exist throughout the DbProject. These are the objects such as DeplymentScript and TableData.
DeploymentScript is an arbitrary set of SQL instructions which are fulfilled before reading the DB schema (BeforDeploy) or after the main deploy (PostDeploy). Its general application is to perform operations with the DB which are not directly supported by DbProjector. For example, carrying out the checking that is required in accordance with business logic or performing operations on the DB schema which are not directly supported by DbProjector itself. So, removing the identity from the current table with future substitution with Sequence may be done using the BeforDeploy script to re-create a column and save the current data therein. (incidentally also link banged it Foreign key). In the next step of the DB schemes comparison, all the necessary instructions for completion of missing links and creation of a new Sequence will be generated.
Remark: While running Export command from the IDE DbProjector, BeforDeploy|PostDeploy Scripts are not executed. For executing these scripts, one of the following can be used: either the DbProjectorInstaller program itself or via «Tools → Deploy Solution» menu command, which is the same.
In addition to the DB object schema, an important part of the DB is to fill the tables with the data which lack will cause a fail of the client software. These are the data for the software object initialization or different reference data.
Data is distributed in the tables through the DataTable object that can be created using the menu command “Project\Add Object\Table data …”. In the appeared dialog box, one has to select the name of the table for which the object is created. The created object will be a separate object, but its structure is based on the table object while created. The DataTable object operation depends on a number of settings.
Table rows copy type – copy type with the following modes:
- Add new rows on table create – to copy the data from the project while creating a table (always checked).
- Add new rows on database update – to add new data to the table while updating.
- Rewrite existing rows – to update the existing data in the database table by the data from the project. The entries are compared by the Primary Key that must be defined for the table.
- Delete absent rows – to delete data not included into the project from the client table. The entries are compared by the Primary Key as well.
For the «Rewrite existing rows» mode, there is also an extended configuration – «Rewrite columns» – a list of that need to be updated.
With the combination of these flags, one can get various modes of updating data in the tables. For example, if you need an exact copy of the data in the table, it is necessary to include all the flags. And if at the time of deploy the client adds new data to the table or changes the existing data, these entries will be deleted or overwritten respectively.
If one uses only Add new rows on database update, the missing data will be identified using the primary key; only new entries created by the developer will be copied to the client DB and those already created or modified by the client won’t be affected.
It should also be noted that if there is a dependency in the tables on the Foreign Key, the dependent data is also likely to include in the project. The order of the addition, modification, deleting of data is built automatically depending on the actual DB schema by the client under deploy.
To add data to the TableData object after a new object of TableData type was added, data has to be imported. To do this, the import operation must be called and the new object of TableData type with the table name appeared in the import list must be marked with a flag. All the rows of data contained in the table at the moment will be imported into the new object.
It should be noted that each data row is monitored, so the processing of a large array of data may result in substantial delays of the deploy procedure on the client side.
The current project properties are available via the menu option «Project → Project Properties».
On the first tabbed page, «Project Version» point is worth to explain. It is a special object created by DbProjector while the DB project establishing. Technically speaking, it is the DB «dbprojector_version» function which returns the project installed version. It is crucial and strongly recommended to increase while developing the project.
The tabbed page «Ignore» has a list of objects that will be ignored whatever export or import operation. It can be used for elimination of the DB objects that are unnecessary in the project, any system objects, objects that won’t be transmitted from the developer’s DB to the client, as well as client objects that you do not want to involve whatever the reason (one can add by a mask).
To deploy the projects on a client-side, there are applications:
- dbpDeploy.exe – console application.
- dbpDeployGui.exe – GUI wizard.
One can create a new database or update the current one using these applications. These applications work with so-called installation packages projects. To get such a package, one one should run a command «Tools → Publish Deploy Pack» from IDE dbProjector. The result of this command is a «ProjectName.dbpack» file in the «$Home/dbProjector/DeployPacks/» folder.
The package is a zip archive of all the files included in the project. For the release of the package, it is necessary to obtain a digital signature of the project. The project signature is carried out by software through an appeal to the API of website https://dbprojector.net. On the website, it is only transmitted a computed checksum of the project and the project Guid that can be seen in the properties of the project from IDE dbProjector.
If one unzips the installation package, changes any file in the project and zips it back, dbpDeploy will find a violation of the project digital signature and it will refuse to work with this package.
All actions performed with the DB via dbpDeploy are logged in the folder:
- For Windows –
- For Linux –
Command line parameter list for dbpDeploy:
- +create – is a flag indicating that one needs to create the DB. CREATE DATABASE instruction with all the default parameters will be fulfilled. If the specified DB already exists, the error will occur and the process will be stopped.
- -dbpack – is a setting of the project pack file path («ProjectName.dbpack»).
- -host – the DBMS host name (as well as Instance for MSSQL, port for Pg).
- -database – is set only if one needs to redefine the DB name specified in the project.
- -uid – the DB login with the DBMS admin permission.
- -pwd – password.
For MSSQL on Windows:
dbpDeploy.exe -dbpack "C:\DbProjects\TestDb1\TestDb1.dbpack" +create -host testserver1\sqlexpress -uid sa -pwd testpwd
For Postgres on Linux:
exec /usr/bin/mono dbpDeploy.exe -dbpack "/home/user1/dbProjector/Projects/TestDb1/TestDb1.dbpack" -host localhost:5432 -uid postgres -pwd testpwd
Remark1: If these parameters are set for dbpDeployGui.exe, the entered data fills in the relevant fields, and the user can einther change it at own discretion or click Next.
Remark2: For dbpDeploy on Linux, to install a mono package is required. For Debian, Ubuntu and derivatives for mono installation, one can use the dbProjector installer. A process to install mono for other distributions is described here.