deltasql - Database Evolution under Control
Back to Website
Frequently Asked Questions (FAQ)
Questions about synchronization
deltasql is a tool which is suitable for the "Agile Development" model, where developers frequently change the data model. deltasql allows to propagate the data model changes to all team members, so that anyone in the development team has a consistent database schema that matches the current source code. When the software reaches a stable milestone, deltasql supports the branching of the development db schema to a production schema. deltasql is also able to transform a development schema into a production schema and viceversa.
We hope deltasql can help you in managing your database schemas, so that for example you have more time to drink coffee, to think over interesting problems or to go out in the evening :-) ... instead of debugging mismatchings between your data model and the source code you deployed to an important customer until late in the night!
deltasql is Open Source and licensed under the General Public License, so there aren't any fees or charge for using it.
The triangle represents the greek letter delta, which also means difference in mathematics. The cylinder is a symbol often used in IT charts to represent a database. The two symbols together identify a system which is able to handle differences on a database with mathematical precision. The logo was created in 2007 by Patrizia.
Unlike commercial tools that compare a source and target database schema and then automatically create a SQL script with the differences, deltasql assumes that you are very knowledgeable about SQL and that you are able to script single changes in SQL. For example, if your source code makes a new reference to a new column in a particular table, it assumes that you know about the command alter table x add column y. If you should not have this ability, then you probably can get most of it if you invest an afternoon of study on a good SQL tutorial.
A part of this initial difficulty, deltasql is easy to learn and has plenty to offer. As you will see, you can set it up in minutes on your laptop. You can start it as your little pet project well hidden on your machine and extend it so far, until it gets a powerful monster installed on the central server of your company, with as many tentacles as existing databases.
Dropping a special table on your database schema is enough to put it under version control. If you forget a schema for some time because you are doing something else, you will exactly know how many scripts you miss to get it to the latest version. Or if you dump the schema and copy it somewhere else, you still keep it under version control, because the synchronization table is copied along with the dump. When you generate a synchronization script with deltasql for a particular schema, the last statement of the sync script will always be an INSERT statement in the special table, to update the current version number of the schema. So simple.
In the beginning, you will use only one module and one project. But later on, especially if you have a base product which has different customization layers for each customer, you will appreciate a setup with several modules for one project.
If you develop alone, you will maybe create a module disjoint from projects to store your collection of scripts which is kind of your spell collection as DBA wizard. With the practical diff feature embedded in deltasql, you will see how your scripts change and improve over time.
If you have plenty of schemas to manage and fear to execute the synchronization script in the wrong schema, a verification stored procedure on top of the script will protect you from any wrongdoing.
If you develop in team, you will get your development schema synchronized with source code with four clicks of the mouse. If you are even lazier, you will use a deltasql client to synchronize your schema with the other developers. If you are old style and do not trust this crazy system, you still will receive an email when someone inserts a new script into deltasql server.
Your team will be motivated on one side by a collective statistics where the overall contribution is summarized, showing how the server grows in complexity over time. On the other side, competitiveness among developers will spark looking at whom will enter the top ten.
If you like how source code develops and you think at it as a fluid or as a tree which grows with plenty of branches and fruits, you can study the synchronization algorithm embedded in deltasql. When you branch in source code, you can also branch on deltasql. You can identify the gems on your tree by tagging important product releases. With some manual intervention you can transform a development schema into a production schema or the other way around. Or you can even try to develop your own client for deltasql server tailored to your needs.
If you are a Linux crack, you can go on implementing continouus integration for databases with the bash client provided. The entire source code is provided, so you can modify the system's behaviour at wish. Overnight, the source code will be checked out and rebuilt; the bash deltasql client will update the reference database schema. Shortly after, the whole battery of unit tests will be launched against the connected database. The entire work will be performed automatically while you are sleeping. In the morning, before the tedious bootstrapping of your local application server, you will know in advance if the latest source code on HEAD expects a missing column in the database :-)
On the other side, deltasql is unfinished and has many gray areas which need improvement and maintenace. Did you find a way to get the verification script working on your preferred database type? Did you extend the poor server's side API without breaking backward compatibility? Did you upgrade a client to get it working with newer libraries? We would be glad to hear about it. Not only we will publish your work and credit you properly, you will also spend less time integrating your features each time you upgrade to a new deltasql release. The whole community will benefit. If you contribute, your colleague leaving top down on the other side of the world will contribute as well. Even if deltasql will miss its mission, we will have learnt completely new ways to do amazing things :-)
No matter if you are a busy DBA, a developer with no private life, a stressed manager or a bored open source developer. No matter if you are a beginner or an experienced professional, deltasql will help you and will overhaul the way your organization thinks about databases!
Yes, it is used in companies in Switzerland, India, USA, Italy, and Spain. In some environments it manages more than 2000 scripts, 10 projects, 12 developers and 15 branches. From Google it can be seen that deltasql is popular in Brazil, Japan and South Korea as well. There are even pages in arabic language about deltasql!
deltasql is based on the LAMP stack (Linux, Apache, mySQL, PHP). It has the same difficulty as to setup a discussion forum on a webpage. deltasql has an automatic install page, though it can be installed step-by-step. Everything should be explained in the manual.
As you can see from this movie, you can get deltasql server installed on your own Windows computer or on a Windows server in about 10 minutes and with a low level of difficulty. If you have care to use a Linux distribution with the LAMP stack installed and running, the install process is straightforward on Linux, too.
Also productive environments can run with deltasql server on Windows, although for best performance Linux is recommended.
Deltasql is released under the GNU General Public License meaning that you can use this software free of charge in your commercial or Open Source software. However, if you significantly modify deltasql, you must report the changes back to the Open source community.
This is a list of achieved milestones:
Stability is also a matter of personal judgment. However, due to the deltasql release process, stable versions are generally the ones with the highest minor number. This is because when a new feature is introduced, the major number is increased. Over time the feature gets stabilized in successive releases. As an example, for the 1.5 line, the stablest version is probably 1.5.5, as no additional release 1.5.6 was published. Stable releases so far where: 1.7.3 (works only with PHP 5.x until PHP 5.4.4, PHP 5.5 unsupported), 1.6.4, 1.5.5 with patch 4 applied, 1.3.7 (works with PHP 4.x) and 1.0.9 (now obsolete).
Yes, there is one in the manual.
Deltasql can be tested on this page. On the login page the password for the administrator is provided. Feel free to experiment with this instance of deltasql. From time to time, the database is restored to an initial status, so that you can not break anything.
Deltasql can be downloaded at sourceforge.net on this page. Deltasql is available as .zip and as .tar.gz package. To unpack the tar package, execute gunzip deltasql-x.y.z.tar.gz first, then issue tar -xf deltasql-x.y.z.tar on your preferred Bash shell.
Along with releases, we publish patches on user request, or as backport from features of the development branch. You can recognize if there is a patch for a given release, if there is a zip file named patch_[patchnumber]_[short patch description].zip on the deltasql project page where you downloaded a release. Installation is simple: unzip the package and drop the files in the deltasql server folder. You will recognize, that the patch is installed on the main page: the release number receives a dash and the patch number. E.g. a patched deltasql 1.5.5 with patch one will become 1.5.5-1. Patches are incremental: if you install patch seven for a particular release, you will automatically install patches from one through six as well.
First, read through the Changelog to see if there is something interesting, or if some bug is fixed. Then, simply download from the webpage the latest deltasql package and unzip (or untar) it at the same place where you installed it. The published package will not overwrite your conf/config.inc.php file, and everything should still work as expected.
Occasionally, the schema of deltasql itself changes: to retrieve the script to be applied to the deltasql database schema you can visit this synchronization form and select as project deltasql-Server. Select the From: and To: fields as well. As an example, if you upgrade from 1.3.0 to 1.3.3, set in From: TAG_deltasql_1.3.0 and in To: TAG_deltasql_1.3.3. Then click on the Generate Script button. Now, you'll need to apply the generated script into your deltasql server instance.
If the synchronization form shouldn't be available (or if you upgrade from a version prior to 1.3.0), you need to read in ChangeLog if it is necessary to upgrade the deltasql schema (it will contain the ALTER TABLE commands you will execute on the deltasql schema at each release note).
If run in trouble, please contact the mailing list for further support.
If you are upgrading a production instance, take a backup copy and work on the backup, until you are sure everything works as expected! Anyway, if you are using deltasql, you should know the business ;-)!
Another way to keep updated with the development is to checkout the git repository of Deltasql and to issue from time to time git pull to update the repository as described in this FAQ.
We are mainly developers in Switzerland, but there are also contributors in India and other countries. We share the passion for Open Source and hope Deltasql can spare some time to people who have to manage several database schemas at a time in the same way it helped us. Deltasql should become the ultimate database versioning tool by hackers for hackers!
We provide technical support for following activities:
Please send an email to ask for technical support.
deltasql has a project page on sourceforge. Feel free to check out the code and experiment with it, or to submit bug and feature requests. However, please be aware that the source code as it is now was quickly developed to get something working and needs a careful rewrite in some professional framework. If you extended deltasql in some new way, you are welcome to share your source code with the deltasql community :-)
First, you need to install a git client. On Windows, you can use git extensions. On Gentoo, run emerge -av git. On Ubuntu, run sudo apt-get install git-core.
The command to checkout the deltasql repository is: git clone git://git.code.sourceforge.net/p/deltasql/code deltasql-code. To keep the repository updated, run from time to time git pull in the directory deltasql-code, which will be created by the checkout process issued by the previous command.
Questions about Synchronization
In Deltasql (since version 1.3.0), it is possible to create multiple branches. Branches are made from the original line of development which is represented in Deltasql by the special branch named HEAD or can be made from other branches and from branches of branches, too. Each control version system has a particular way to name the original line of development: e.g. Subversion calls it 'trunk', git calls it 'master', but Deltasql calls it HEAD after the old but reliable Concurrent Version System (CVS).
Picture: Deltasql timeline with source and target
Deltasql advances commit revision number (the version number) across all branches. When creating a new branch, Deltasql remembers at which version number and from which source branch branching occurs. The development tree shown in the picture above is stored.
When the user asks deltasql to generate a synchronization script for a particular database schema, the user provides the version number of the schema and its branch, and gives a target branch which should be achieved by the synchronization algorithm (the source circle in the picture). By providing the target branch, the user implicitly provides the target version number as the latest version number available in Deltasql at the moment (the target circle in the picture).
Alternatively, the user can provide a target tag, which contains target branch and target version number.
With this information, Deltasql first traverses back the tree of branches, from target (the leaf) to the source (the schema version number and its branch), recording the path in a particular table called TBSCRIPTGENERATION.
Deltasql then walks back along the path and for each segment of the tree, it generates a sequence of scripts which belong to that segment of the tree.
The synchronization script is then the collation of the sequences of scripts and follows the path from source to target.
There are more details on the synchronization algorithm in the manual.
There are both project and modules, and a project contains from one to several modules. Big projects might be splitted in several subprojects, and each subproject (=module) has some particular additional functionality that needs to be managed separately. Or in other configurations, companies like to define a module for the utilities common to all development done by the company (e.g. tables like TBUSER belong to this module), one module representing the main software itself, and one module represented by the customizations on the software done for one particular customer.
Adding and removing modules to a project is also done in the List Projects page.
A dedicated section of the manual explains how to setup your modules and projects.
You can find the script in the List Projects page of deltasql, if for the corresponding project you click on the Table link. After that you need to choose your database type, and if your database schema will follow HEAD or stay one of the available branches.
deltasql will then generate for you a) the table TBSYNCHRONIZE, b) the first row of this table and c) an additional stored procedure to protect your schema from wrong scripts (c is only for some database types).
The query is:
select * from tbsynchronize where versionnr = (select max(versionnr) from tbsynchronize);
The most important column is versionnr that contains the last version of the executed script. The interesting columns are projectname with the project name and branchname that contains either HEAD for a development schema, or another name for a production schema.
Initially, you prepare your database schema with some synchronization tables and data.
Each time you want to synchronize, you need to launch first a script which retrieves the current version of the database schema. With this information, you can visit the synchronization form of deltasql, fill the form with data retrieved from the query, hit the form button and enjoy the SQL synchronization script deltasql will generate for you.
The generated synchronization script will update your database schema with the scripts the developers submitted and it will also increase the current version of the database schema.
Deltasql is even a bit more sophisticated, for example a stored procedure at the beginning of the synchronization step verifies that the script is executed on the correct schema. Deltasql can handle database branches, it can transform a developer database into a production database and viceversa.
The purpose of the client is to speed up the upgrade of a database schema. The client normally implements two steps: 1) the lookup of the current version in a given database schema and 2) issuing the request to deltasql for generating the upgrade script. The third step of executing the script in the database schema is left to the user.
In the manual, you can find instructions how to install the available clients. Also there, you find information on how to write your own client. If you wrote one and would like to share it, contact please the mailing list. Thanks!
A development database schema is normally used by developers and contains the latest available scripts. Each day, the developer updates it with few scripts. In deltasql, a development database is said to follow HEAD.
A production database schema on the contrary is deployed to a customer. Updates to this database are less frequent but more bulky, normally done when new releases of the software are scheduled. In deltasql, a production database is said to follow a branch.
When you run the query that retrieves the current version of the database schema, check the value in the column branchname. If it contains HEAD, you know it has to be a development schema. If it contains something else than HEAD, you know it is a production schema. The value in branchname is the branch the production schema will follow when further synchronization scripts are executed on it.
Branches are defined at the project level. Therefore you should select the row in List Projects and click on the Branch link. You then give a name and a description to the branch. The description is just a mnemonic to remember in which circumstances you created the branch.
From now on, you can use the Synchronization Form to transform your current HEAD schema into the branch schema (also called the production schema). Remember to choose in the Synchronization Form for the field From: the value HEAD, and for the field Update To: the new branch you created in deltasql.
After launching the script generated by the Synchronization Form, the database scripts will be only the ones explicitely marked also for the branch.
It can be done in the the Synchronization Form as well. Choose in the Synchronization Form for the field From: the branch name and the version retrieved with this query, and for the field Update To: simply choose HEAD. Deltasql will generate a synchronization script which will turn the production schema back into a development schema.
This is the most advanced feature currently implemented in deltasql. Therefore, it is recommended to review the script manually before executing it on the copy of the production schema.
Yes, it is possible. In the List Branches page you can click on the Branch button (if you have rights as a Project Manager or as an Administrator). From now on, when submitting a new script, it is possible to decide if it belongs to the old, to the new or to both branches.
Yes, it is. In List Branches, there is a Tag action.
No, unfortunately not, as developers submit scripts like "ALTER TABLE ADD" or "INSERT INTO TB..." and they do not provide an SQL script that reverts the change. deltasql has not sufficient artificial intelligence to generate scripts that revert the database to the previous state. If reversal is necessary, developers need to provide the reverting scripts by adding them to deltasql.
When you dump a database schema which is under version control, it will contain the table TBSYNCHRONIZE. Therefore, the information about the last script executed and about being production or development schema is persisted inside the dump.
When you restore the database dump on other database instances, you can keep the restored database as it is, or very easily update it to the latest development (HEAD) or production schema (branch) by using the usual synchronization form of deltasql server.
For some database types, deltasql gives the usual TBSYNCHRONIZE table and provides an additional stored procedure called DELTASQL_VERIFY_SCHEMA. The stored procedure DELTASQL_VERIFY_SCHEMA is called on top of every synchronization script created by Deltasql server. Its purpose is to make sure that the synchronization script is executed on the correct schema.
To verify it, the stored procedure is called with the arguments which are found as last entries in TBSYNCHRONIZATION. Therefore the stored procedure verifies that the current schema belongs to the correct project, it is at the correct version number and is on the correct branch. If there is a mismatch, the stored procedure raises an exception which prevents the rest of the synchronization script to be executed. This feature therefore protects schemas from synchronization scripts which are not meant for them. Imagine to launch a sync script with more than 100 SQL statements on the wrong production schema! What a nightmare!
A call to DELTASQL_VERIFY_SCHEMA depends on the database type, and looks e.g. for Oracle in this way
-- this verifies that the present script is executed in the correct schema CALL DELTASQL_VERIFY_SCHEMA(1, 'HEAD', 'deltasql-Server');
If, for a particular database type, the development team was not able to define such a stored procedure, you will see this comment on top of the synchronization script:
-- Please make sure this script is executed on the correct schema!!
Under continouus integration, a developer normally understands the nightly checkout of the source code followed by a rebuild of the whole source code to verify if something is broken. Generally, it is possible to verify in the morning the status of the build in some logfiles or even on a website.
With the deltasql bash client (available at the main page for download) it is possible to improve this process. The deltasql bash client can be configured to upgrade each night a predefined database schema, if continouusintegration.sh is scheduled as cron job with crontab -e.
Assume you have a huge software application which runs on an application server, backed by a database server:
You could implement the following steps:
By implementing the above steps you achieve complete continouus integration which includes the database schema as well. Improving continouus integration in this way allows to detect mismatches between source code and data model at an early stage.
If you are interested in this feature, you can read this entry in the manual on how to install the bash client.
The format can be chosen at the bottom of the synchronization form. The most used is the HTML one which is pretty printed (SQL is highlighted with geshi library). This format is the preferred one to be copyed and pasted into the own database browser (like Toad, PL SQL Developer, Microsoft SQL Server Management Studio, etc).
It is possible to create a text version of the synchronization script or an XML version for further processing. In this case, you need the View->Page Source functionality of your browser, to see the script in the original format.
Additionally, it is possible to export the files as single scripts, so that they can be used in dbdeploy-like tools. When exporting scripts in this way, deltasql server will first generate the scripts into the output/scripts directory of the server, then zip it and finally serve the .zip for download. The idea of dbdeploy-like tools is to include the single scripts in a directory accessible by the setup executable. The setup executes then the single scripts when installing the application.
You can change the variable $default_script_title in the file config.inc.php in the directory conf.
The colored rows just show how old the first submission of a script was. The row is green if the script was submitted in the last 20 minutes, yellow if it is less than 5 hours old and blue if it is less than one day old.
If you lost the admin password (or if you messed up with the hash salt in TBPARAMETER), you can execute the following script into the deltasql database schema:
UPDATE tbuser SET password='log4admin',encrypted=0,passwhash='' where username='admin';
You can then login in deltasql with username admin and password log4admin. After that, you should change the password again to something more secure.
In case you messed up with the salt in TBPARAMETER, you should reset all passwords for the other users as well. A password reset can be issued in the List Users page.
deltasql server has more than 2000 scripts and starts to get slow. How can I increase its performance?
If deltasql is slow when inserting new scripts, make sure the feature of sending emails is disabled, by setting $emails_enable=false in conf/config.inc.php.
On the contrary, if deltasql is slow when generating the synchronization script, try to set the variable $disable_sql_highlighting=true; in conf/config.inc.php so that the SQL highlighting step is skipped.
Email notification of new scripts is an important feature of deltasql, so that users can slowly migrate from a development model where they are informed by email, to the new deltasql model. It is enough to ask all users to submit new scripts via deltasql server. Users who would like to continue working by managing their database schemas manually, can keep checking their inbox for new scripts. More advanced users can start experimenting with the database synchronization feature of deltasql, and their inbox will be free from any SQL script.
In the next paragraphs, we explain in detail how to prepare deltasql server, so that the email notification feature works.
First, you need to get sendmail up and running on your system. Linux users should refer to the documentation available on the Internet. XAMPP users can check the subfolder xampp/sendmail where a sendmail for Windows is available. All users need an account on a SMTP server whose details need to be stored in a configuration file.
Windows users need to edit sendmail.ini. Linux users need to edit a sendmail configuration file somewhere in the /etc/ folder structure, after they installed sendmail on their box.
You should test that sendmail is working as follows: create a text file with this structure first, and save it as test.txt:
From: email@example.com To: firstname.lastname@example.org Subject: A test email from sendmail This is the body
Then launch this command in a Linux shell or on the MS-DOS prompt: sendmail -t < test.txt and check that you received an email in your inbox.
If sendmail is working, you can proceed to configure the following variables in conf/config.inc.php:
$emails_enable=true; $emails_sender="email@example.com"; $emails_subject_identifier="[deltasql]";
The variable emails_enable simply tells if notification by email is enabled or not. emails_sender defines from whom the email is sent. In most cases you need here to configure an existing email address on the SMTP server. If you do not do it, the email might be rejected by the spam filter. The emails_subject_identifier is a string added in the email's subject to inform everyone that this email is automatically generated by deltasql.
Linux users need to define the path of sendmail and of deltasql as follows:
$sendmail_command="/usr/bin/sendmail -t <"; $deltasql_path="/var/www/deltasql/";
XAMPP users under Windows should define instead (double backward slashes are important and not a mistake!):
$sendmail_command="C:\\xampp\\sendmail\\sendmail.exe -t <"; $deltasql_path="C:\\xampp\\htdocs\\deltasql\\";
Once you defined the variables correctly, you can configure at least one user with a valid email address in the Preferences window. Finally, submit a new script to deltasql and check that the email is received correctly :-).
On the contrary, if you do not need this feature, you might want to disable it by setting $emails_enable=false; in the configuration file conf/config.inc.php. Following this action, the field to set an email address in the Preferences window will be hidden.
Although the functionality is very practical, for some browser (e.g. Internet Explorer) or some text editor (e.g. notepad of Windows) there might be problems with the copy and paste functionality introduced with version 1.5.2.
If single users would like to disable it, they can disable it on their 'Preferences' page. To disable the functionality also when not logged in, you need to set the variable $default_copypaste in conf/config.inc.php to 0.
deltasql is not suited to be published in Internet, as it is programmed in a simple fashion and is vulnerable to SQL injection attacks.
Still, deltasql provides a mechanism to authenticate users and store their passwords in a secure manner. If you publish the deltasql pages on Intranet and would like to restrict access, we recommend to serve them via https (e.g. by exposing only port 443 of Apache to the outside) and to set the option $keep_private=true in conf/config.inc.php. The latter option will show the content of deltasql only to authenticated users.
The document management feature is intended to occasionally add documents to a particular SQL script. They can be for example Excel files about a particular result of the SQL script, or they can be a Word document with a detailed explanation of the SQL script. They can also be an analysis which was created along with the SQL script. To add a document to a particular script show a script and then select "Add Docs".
The documents are generally for scripts used in collections and not for synchronization. Documents are intended only for later reference. They are never executed or attached when performing the synchronization step!
Documents are not saved in the database, but they are simply uploaded in the upload subdirectory of deltasql. There is a mechanism to avoid that the same filename is uploaded twice. The file is automatically renamed to prevent data loss.
Therefore, you need to make sure that the upload subfolder of deltasql has the proper rights for the apache user, in order for this feature to work.
Please visit the forum on the project homepage for bug reports, suggestions and inquiries. We appreciate your feedback! Have fun with deltasql :-)
Main | List Scripts | Synchronize | Statistics