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.
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!
Yes, it is used in companies in Pakistan, USA, Italy, Switzerland, India 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!
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).
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.
This movie 
is an introduction tutorial on how synchronization works.
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.
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. Any contributions (also from newbies) is welcome :-) Open Source is the best way to learn the fascinating world of Computer Science!
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.
This movie steps through the install process, too.
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.
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.
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://deltasql.git.sourceforge.net/gitroot/deltasql/deltasql. To keep the repository updated, run from time to time git pull.
We provide technical support for following activities:
Please send a mail for bug reports, suggestions and inquiries.
If you get errors similar to the following one right after installing deltasql server:
Notice: Undefined index: rights in D:\xampplite\htdocs\deltasql\utils\utils.inc.php on line 185
or
Strict Standards: Assigning the return value of new by reference is deprecated...
you should modify php.ini (in XAMPP for example it is located under C:\xampp\php) and change the variable error_reporting from
E_ALL | E_STRICT
to
error_reporting = E_ALL & ~E_NOTICE
After that you need to restart the Apache webserver. This will solve the issue.
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.
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.
This movie shows how to turn a development schema into a production schema.
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.
This movie shows how to turn a production schema back into a development 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.
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!!
This movie explains how the verification step works.
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.
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.
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.
Deltasql collects usage statistics and sends it to the deltasql.org homepage. When you install Deltasql, you are asked if you want Deltasql to submit usage statistics: each 100 scripts submitted to the homepage, deltasql server contacts deltasql.org and sends row counts of important tables in deltasql. The current deltasql version is also sent over the wire.
Why is deltasql.org collecting this data? We just would like to get an idea of how many scripts deltasql is managing overall. We would like to publish this information on the homepage for advertisment purposes, in the hope that the user basis increases. Additionally, some row counts tell us if our users are using advanced functionality like branches or complex project-modules structures. The frequency of upgrades to deltasql server can be revealed as well.
deltasql.org will not use the collected data to mail advertisement or to harm any deltasql user.
If you still want to disable this functionality, add or modify the row submit_usage_stats=false; in conf/config.inc.php.
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.
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: test@test.com To: youremail@test.com 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="admin@deltasql.org"; $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.
You can submit your question to the GPU mailing list. We appreciate your feedback! Have fun with deltasql :-)
Back to main page