Location: PHPKode > projects > deltasql > deltasql-1.0.8/manual.php
<title>deltasql - Help</title>
<link rel="stylesheet" type="text/css" href="deltasql.css">
echo "<h1>Manual deltasql $deltasql_version</h1>";
<a href="index.php">Back to main page</a><br><br>

<li><a href="#purpose">Purpose</a></li>
<li><a href="#install-guides">Install Guides</a></li>
	<li><a href="#install-server">How to install deltasql Server</a></li>
	<li><a name="install-clients" /><a href="#install-dbredactor-client">How to install dbredactor client (optional)</a></li>
    <li><a href="#install-ant-client">How to install the ant client into Eclipse (optional)</a></li>
    <li><a href="#install-google-gadget">How to install the Google Gadget (optional)</a></li>
<li><a href="#usage">Usage</a></li>
	<li><a href="#usermanagement">User Management</a></li>
	<li><a href="#concepts">Concepts</a></li>
    <li><a href="#quickguide">Quick Guide</a></li>
    <li><a href="#tips">Tips</a></li>
       <li><a href="#maintenance">Define script's collections for maintenance tasks</a></li>
       <li><a href="#filter">Filter script - output particular synchronization scripts</a></li>
<li><a href="#advanced">Advanced Topics</a></li>
        <li><a href="#insights">Insights into the deltasql Algorithm</a></li>
        <li><a href="#write-client">How to write your own client</a></li>
        <li><a href="#structure">Directory structure of the deltasql_thunder_1.xxx package</a></li>
        <li><a href="#errors">List of error codes from deltasql Server</a></li>

<h2><a name="purpose"></a>Purpose</h2>

<p>deltasql Server allows developers to submit their SQL scripts in a central place. Each time a script is submitted, deltasql assigns 
 a number to it (the script's version). Database administrators can check at which version the database table is, by checking the last inserted row
  in the table TBSYNCHRONIZE. They also can look at the project name in this table. They can then <a href="dbsync.php">ask</a> the deltasql Server 
  for the currently missing scripts.</p>

<img src="docs/devel-with-deltasql.jpg" border="0"><br>
<p><i>With deltasql:</i> A developer sends the script to the deltasql server only, 
the exact database state is always known and can be synchronized to any state.</p><br>

<p>When scripts are sent, a database module has to be chosen. A project can consist of one or several modules, as in some companies, they tipically
 use a three-level structure for their source code modules. The first database module is the lowest level and represents
tables which are common for all projects in a company. The middle database module represents the tables of the application which is sold.
 The highest level module contains tables which are needed for the customization of one particular customer, and which are not
needed for other customers.</p>
<p>For simple applications, it is enough to specify one module for each project, although deltasql was developed as help in a 
much more complex development environment.</p>

<p>deltasql also supports database branches, which are similar to source code branches. It is possible to choose,
 if a script belongs also to a particular branch. And it is possible to update a branch database to a newer branch, or to the
  development schema represented by the branch named <tt>HEAD</tt>.

<p>deltasql Server is written in such a way, that clients can interface to it and query the database automatically.
 Deltasql is currently shipped with a Java client called dbredactor that is able to interface with mySQL, Oracle, and postgreSQL
 databases. People in the Open Source community are encouraged to write their own clients, or to partecipate in
  improvements of the server itself.</p>  

<img src="docs/devel-no-deltasql.jpg" border="0"><br>
<p><i>Without deltasql:</i> A developer has to send his/her scripts to everyone, 
the exact database state is known only to the database owner.</p><br>


 if (file_exists($configurationfile)) {
 if ($enterprise_edition==false) {
	echo "<p>If you find the project useful, or if you use deltasql for managing your Open Source project, ";
	echo "you are encouraged to add this button <img src=\"pictures/deltasql-button.jpg\" border=0> to your homepage. Link it to ";
	echo "the project wiki at <a href=\"http://www.deltasql.org/wiki/\">http://www.deltasql.org/wiki/</a>."; 
	echo "This might bring some additional traffic and maybe involve others in the project who will improve the quality of ";
	echo "this software. Thanks! </p>";
<h2><a name="install-guides"></a>Install Guides</a></h2>
<h3><a name="install-server"></a>Install steps for the Server</h3>

<p>deltasql runs on Apache webserver with the PHP module backed by a mySQL database.</p>

Therefore, you should first download from the Apachefriends.org website <a href="http://www.apachefriends.org/en/xampp.html">XAMPP</a>,
a package which bundles Apache, mySQL and phpMyAdmin (and more... :-) for Linux and Windows. deltasql was developed with XAMPP 1.5.5, but any newer
version might work as well. Then you should install XAMPP. Using the <i>XAMPP Control Panel</i> start Apache and mySQL.

Download the <a href="http://sourceforge.net/projects/deltasql">deltasql PHP scripts</a>, unzip them and copy
them to the <code>htdocs</code> directory created by XAMPP, so that you get a <code>htdocs/deltasql</code> directory
full of PHP scripts. 

If you visit the main page index.php, there is a link called <a href="install.php">Please Setup Deltasql...</a>. Click on this link and follow the instructions there.
Set a password for the <b>admin</b> user of deltasql. If you installed XAMPP, you do not need to define a mySQL password for the root account,
 as XAMPP sets the empty password for root by default. Else please use the correct root password of mySQL. Pressing the "Submit" button will
  install everything you need to run your deltasql server.

<p>If something should go wrong, you can read also through the next section.</p>

<h4>Manual install steps for the server</h4>

Go to the directory <code>htdocs/deltasql/config</code>, take <tt>example_config.inc.php</tt>, copy it to
 <tt>config.inc.php</tt>. Edit the variables in <tt>config.inc.php</tt> depending on your hardware 
 and network configuration.

As next step, you should visit <a href="http://localhost/phpmyadmin">http://localhost/phpmyadmin</a>, to access phpMyAdmin,
an interface that speaks with the mySQL database. In phpMyAdmin, create a user <code>delta_user</code> with a password you can set in <code>htdocs/deltasql/conf/config.inc.php</code>.
If the deltasql server is published on the Internet, you should also modify the constant <code>$dns_name</code> with the IP number of your
 computer or with its DNS name.</p>

<p>Now take <code>htdocs/deltasql/db/script.sql</code> and go back to 
<a href="http://localhost/phpmyadmin">http://localhost/phpmyadmin</a>. 
Execute the SQL script with the phpMyAdmin interface. It will create a <code>deltasql</code> database.</p>.

If you did all steps right, you should get a working deltasql Server as in <a href="http://www.deltasql.org/deltasql">this webpage</a> if you visit
<a href="http://localhost/deltasql">http://localhost/deltasql</a>

Log as <i>Administrator</i> with username <b><?php echo "$admin_user"; ?></b> and password <b><?php echo "$admin_pwd"; ?></b>, so that you can create your own users.
Do not forget to change the <b>admin</b> password shortly after with this <a href="change_password.php">form</a>.

<h3><a name="install-dbredactor-client"></a>Install steps for the dbredactor client (optional step)</h3>

<p>Download first dbredactor.zip from the deltasql main page (at the bottom!). Unzip the file in the Eclipse workspace. Add the build.xml
 into the Ant window by right clicking and choosing 'Add build files...'. Copy sample-build.properties to build.properties and set the correct URL for the deltasql server in the
  <tt>deltasql.server.url</tt> property.
 Create in the <tt>/config</tt> directory a configuration for the database schema as they are done under <tt>/config/examples</tt>.
 Next, update <tt>config.set</tt> with the path to the directory you created.
<img src="pictures/ant-dbredactor.png" border="0"><br>
<i>Picture: </i>the two Ant targets of dbredactor
 If everything is configured correctly, you'll be able to check if a schema update is needed with a double click on the ant target
  <tt>dbsync info</tt> and to get the script needed to update with <tt>dbsync update</tt>.
<h3><a name="install-ant-client"></a>Install steps for the ant client into Eclipse (optional step)</h3>
Download first ant-client.zip from the deltasql main page (at the bottom!) and unzip it somewhere. This zip file contains only two tiny files:
 deltasql-build.xml and sample-deltasql.properties. 
<img src="pictures/ant-client.png" border="0"><br>
<i>Picture: </i>the target of the Ant Client
Copy deltasql-build.xml into the Eclipse
 project where you are working (at best in the root directory of the Eclipse project).  Add the deltasql-build.xml file
 into the Ant window by right clicking and choosing 'Add build files...'. Copy sample-deltasql.properties to deltasql.properties. Read through
  deltasql properties and adjust all properties. Create a lib folder in the Eclipse project (if not already there) and copy all the .jars you can find in the 
  clients\java\dbredactor\lib directory of this package (or only the ones you enabled in deltasql.properties, at your wish).
Et voilĂ , now you should have a working ant client. By pressing on "RetrieveUpdates" you will receive the script with the latest db updates. The ant client
 is also easy to integrate in an existing build.xml!

<h3><a name="install-google-gadget"></a>How to install the Google Gadget (optional step)</h3>
<img src="pictures/deltasql-google-gadget.png" border="0"><br>
<i>Picture: </i>the Google Gadget of deltasql showing the latest submitted scripts
To install the Google Gadget is fairly simple: just click on the following button: 
<a href="http://www.google.ch/ig/adde?hl=en&moduleurl=http://www.gpu-grid.net/deltasql/deltasql_google_gadget.xml&source=imag" target=_blank><img src="pictures/add_google_gadget.gif" border="0" /></a>.
A new page will open. Follow instructions there to get the deltasql gadget on your iGoogle start page. 
After that, the Google Gadget still points to the deltasql server which is used as showcase for new users. With the arrow that points down on the top right of the deltasql gadget, 
you should choose the menu entry "Change settings", and enter an URL that points to your local deltasql install. Look at the screenshot:
<img src="pictures/gadget-change-settings.png" border="0"><br>

Finally, remember to save your settings. <b>If deleting cookies forces you to reenter each time a new URL</b>, just modify the <tt>default_value</tt> string in <tt>deltasql/deltasql_google_gadget.xml</tt>.
For an explanation of what coloured rows in the gadget mean, see this <a href="faq.php#colors">F.A.Q. question</a>.

<h2><a name="usage"></a>Usage</h2>

<h3><a name="usermanagement"></a>User Management</h3>
There are four types of users: <i>Guests</i>, <i>Developers</i>, <i>Project Managers</i> and <i>Administrators</i>. 
<i>Guests</i> can only browse through project's scripts, search among them and compute synchronizations for schemas.
<i>Developers</i> can do everything <i>Guests</i> can do, but they also can submit new scripts, and edit old ones.
 <i>Project Managers</i> can create projects and add modules. They can create branches as well. <i>Administrators</i>
 can add new users to the deltabase.

<h3><a name="concepts"></a>deltasql Concepts</h3>

<p>Developers submit scripts which belong to a module. A project consists of one or several modules. 
A database schema is always related to a single project. To each script a unique version number is assigned. 
If a database shows a given version in TBSYNCHRONIZE, deltasql can compute which scripts are necessary to
 reach another version.</p>
<p>Normally, database schemas and in particular development ones, need the entire chain of module-related scripts
 to be updated. This chain, as in the everywhere known Control Version System (cvs), is called <tt>HEAD</tt>.
 However, deltasql allows to create and name branches for a project. Once a branch is created, developers can choose if a particular
  scripts belongs only to <tt>HEAD</tt>, or if it belongs also to the newly created branch. Internally, deltasql
   simply stores the version number when the branch was created.
 When updating with the <a href="dbsync.php">form</a>, one has to choose the target branch. The target branch can be
  <tt>HEAD</tt>, if all scripts need to be executed, and therefore if the database schema has to get a development one.
 Specifying a new target branch which is not <tt>HEAD</tt> will update the database schema to the version number assigned
  to this branch (by inserting a new row in TBSYNCHRONIZE with the version number), 
  no matter if the source branch is the same branch, or if it is a previous one. In principle,
   it is also possible to update a long forgotten development schema (source branch = <tt>HEAD</tt>), to another target branch.

<h3><a name="quickguide"></a>Quick Guide</h3>

<p>Login first with a user which has 'Project Manager' or 'Administrator' rights.</p>
<p>Define a module 'mymodule' with <a href="create_module.php">Create DB Module</a>. Then define 
a project 'myproject' with  <a href="create_module.php">Create a project</a>. Under <a href="list_projects.php">List projects</a>
 it is possible to add the module 'mymodule' to the project 'myproject' with the link 'Add Module'.</p>

<p>Now choose 'Table' under  <a href="list_projects.php">List projects</a>. Specify your database type. This will
 generate a table which needs to be created in all involved database schemas, you would like to keep under version control
  with deltasql. Create this table in these database schemas with a sql client.</p>
<p>Developers can now submit scripts with this <a href="submit_script.php">form</a>. They have to choose to which module
 the script belongs. If branches are created, they can also choose if a script is
 only a script for development schemas (a <tt>HEAD</tt> script), or if it needs to be applied to a previously created branch. 
 They can list all <a href="list_scripts.php">previously submitted scripts</a>. In this list,
  they can also modify a submitted script, if it contains errors, or if it is incomplete.

<p>To update a database schema with the submitted scripts, you should first query the database schema with
 <tt>SELECT * FROM TBSYNCHRONIZE ORDER BY VERSIONNR DESC</tt>. Retrieve in the first row the project name (in <tt>PROJECTNAME</tt> column), its current version
 (in <tt>VERSIONNR</tt>) and its source branch value (in <tt>BRANCHNAME</tt>). The source branch value <tt>HEAD</tt> 
  is used for development schemas. For production databases (typically installed to a customer), the source
   branch value can be the value set when a branch is created. Just look at the first row and disregard
    the other rows, they are kept so that you have a little bit history of what happened to that database schema (it can be helpful
     in case you run a script intended for another schema by mistake).

<p>Once the three parameters are retrieved from the database schema, it is possible to ask for a database synchronization
 with the form <a href="dbsync.php">Synchronize database schema</a>. The field 'From:' should be filled with the value found
  in TBSYNCHRONIZE.BRANCHNAME. The field 'To:' (the so called target branch) should be decided as follows: choose
   <tt>HEAD</tt> if you would like to update a development schema (= 'From: HEAD') or if you would like to turn
    a production schema (= 'From:'= name of a created branch) into a development schema. Choose defined branch name for
	 the 'To:' field, if you would like to update a production schema with scripts sent to the production branch, or if 
	  you would like to update an older development schema to a newer production branch.

<p><a href="dbsync.php">Synchronize database schema</a> will return a page with the missing SQL scripts for that given
 database schema. Choose 'Edit->Select All' from your browser and then 'Edit->Copy'. Paste the script into your
  favourite SQL client, which is connected to the database schema and execute it.

<p>The script from <a href="dbsync.php">Synchronize database schema</a> will create at the end an UPDATE statement,
 which will update the information in TBSYNCHRONIZE for the next time.</p>
 Please note, that if something goes wrong, deltasql server reports an error according to the following <a href="manual_errormessages.php">list</a>.
 <p>This is deltasql in short. Have fun!</p>
<h3><a name="tips"></a>Tips</h3>

<h4><a name="maintenance"></a>Define script's collections for maintenance tasks</h4>

 With deltasql it is also possible to keep a collection of scripts which are occasionally used for maintenance tasks.
 It is sufficient to create a database module with a name like 'collection of scripts for myProject' without adding
  the module to any project. Modules which are not added to a project do not partecipate in the synchronization process.
  So you can submit maintenace scripts to this module without fear that they will appear in the synchronization sequence. 
  Typically, you will change the default title <i>db update</i> to what the script effectively does. deltasql underlines titles which are not default
  and allows to search also in the title field of a script.
<p>By using the function <a href="search_scripts.php">Search among scripts</a>, you can then specifically select the module
  'collection of scripts for myProject' to search among the maintenance SQL scripts.

<h4><a name="filter"></a>Filter script - output particular synchronization scripts</h4>
When listing scripts (in <a href="list_scripts.php">List Scripts</a>), it is possible to filter the scripts (e.g. by date, module, author and more)
 by using the command <a href="search_scripts.php">Search among scripts</a> on top of the page. Normally, the scripts are filtered and shown
  as a list. By checking the checkbox <b>Output as text list</b> before clicking on Submit, the scripts will be shown as a normal text file
   that can be copy pasted into your SQL client of choice. This can be sometimes useful, if you need to repair a database schema by performing this
    special operation.
<h2><a name="advanced"></a>Advanced Topics</h2>

<h3><a name="insights"></a>Insights into the deltasql Algorithm</h3>

<p>First, it is important to understand that deltasql does not support branches of branches. This keeps things easy.</p>

<p>The main synchronization logic is contained in the file <tt>dbsync_update.inc.php</tt>. The main query that drives HEAD to HEAD, or BRANCH to BRANCH
 updates is: (we call it the "standard query")
      SELECT DISTINCT s.* from tbscript s, tbscriptbranch sb where 
       (s.versionnr>$fromversionnr) and (s.versionnr<=$toversionnr) and 
       (s.module_id in 
              (select module_id from tbmoduleproject where project_id=$projectid) 
               and (s.id=sb.script_id) and (sb.branch_id in ($includeheadid, $frombranchid, $tobranchid)))

<p>The query shows that the table tbscriptbranch contains a relation between a given script (stored in tbscript) and the branches 
 (stored in tbbranch) to which it was submitted. tbbranch contains a particular row named HEAD, too. Therefore also the HEAD is represented
  as a row in tbbranch and can be considered logically as a branch. <tt>$frombranchid</tt> and <tt>$tobranchid</tt> are equal in case of a BRANCH to BRANCH update or in case of a HEAD to HEAD
   update. In both cases, <tt>$includeheadid</tt> is empty and not used.</p>
<p><tt>$fromversionnr</tt> is the value as it is retrieved in the table TBSYNCHRONIZE, and represents the current synchronization point of the database schema.
 <tt>$toversionnr</tt> is the version number of the latest script on deltasql,
 when HEAD is the target branch, or the version number when the BRANCH was created when BRANCH is the target.</p>

<p> In case of a HEAD to BRANCH update, the variable <tt>$addbranchscriptsafterbranch</tt> is set to 1 and therefore an additional query is done after the standard query (please see the source code to see the query).
 The standard query adds the HEAD scripts between the current synchronization point on the schema and the point where the branch was created on deltasql. The additional query simply adds all further
  BRANCH scripts after the branch was created. We call this additional query the "final query". For the standard query, the variable <tt>$includeheadid</tt> is empty and not used.</p>
  The table tbmoduleproject contains the relation between modules and one project. <tt>$project_id</tt> contains the value of the project we would
   like to synchronize. Therefore, the query selects only scripts that belong to modules of the project to be synchronized. 

<p>In case of a BRANCH to HEAD update, the variable <tt>$addheadscriptstoupdatedbranch</tt> is set to 1 and thefore an additional query is done before the standard query (also here, please see the source code to see the query).
 This additional query adds all HEAD scripts between the BRANCH creation point and the current synchronization point on the schema.
  We call this additional query the "initial query". For the standard qury, the variable <tt>$includeheadid</tt> is empty and not used.

<p>The most complex case is a BRANCH to NEW_BRANCH update. Both <tt>$addheadscriptstoupdatedbranch</tt> and <tt>$addbranchscriptsafterbranch</tt> are set to 1:
 the "initial query" adds all HEAD scripts between the synchronization point on the schema and the creation point of BRANCH. The standard query
  does a HEAD to HEAD update between the creation point of BRANCH and NEW_BRANCH (this time the standard query has the variable <tt>$includeheadid</tt>
 set to the id of the row named HEAD in the table tbbranch. After that, the "final query" adds all NEW_BRANCH scripts between the creation
   of NEW_BRANCH and the current HEAD on deltasql. 
<p>If you install deltasql with the option <tt>Test System (with Test data)</tt>, you can verify the deltasql correctness with this 
<a href="docs/Tests_To_Verify_deltasql_correctness.txt">file</a>.

<h3><a name="write-client"></a>How to write your own deltasql client</h3>

<p>deltasql Server is written in PHP and can run whenever Apache runs. You might want to write your own deltasql client
 that integrates better into your own development environment. A client called <tt>dbredactor</tt> for the Java
  environment can be download on the bottom of the <a href="index.php">main page</a>.</p>

<p>A deltasql client should perform the following operations:</p>

<li>1. Connect to the database schema and retrieve project name, version and branch</li>
<p>This can be simply done by executing the query <tt>SELECT * FROM TBSYNCHRONIZE ORDER BY VERSIONNR DESC</tt>. The query is so simple
 as the table has one row per update which was already done. The latest database update has the highest version number. The important columns in this row are <tt>PROJECTNAME</tt>, <tt>VERSIONNR</tt> 
 and <tt>BRANCHNAME</tt>. As you can imagine, <tt>PROJECTNAME</tt> is the name of the project of this database
 schema and <tt>VERSIONNR</tt> is the number of the latest script executed in this database schema. <tt>BRANCHNAME</tt>
  tells if the database schema is a development schema (if the value is <tt>HEAD</tt>), or if it is a production branch.
   If it is a production branch, the value will be the name of the branch.</p>
  <p>If you prefer a query that returns only the row of interest, you might use this one:
 select * from TBSYNCHRONIZE where versionnr = (select max (versionnr) from TBSYNCHRONIZE);
<li>2. Check on deltasql Server if there is a newer version for this project</li>
The client should touch the URL below and store the resulting page in a file called <tt>projectversion.properties</tt>.
The parameter <tt>project</tt> in the URL is the value <tt>PROJECTNAME</tt> retrieved in step one.
 echo "$dns_name/dbsync_automated_currentversion.php?project=TestProject";
In the <tt>projectversion.properties</tt> file, there will be a line <tt>project.version=</tt> which contains the current
latest version on deltasql server for this project. The client should now compare the latest version on deltasql
 and the <tt>VERSIONNR</tt> value in the database schema and decide if it is necessary to continue with step 3.
  If not, the user should be informed that there is no new version, that the datamodel did not change since last time.
<li>3. Retrieve Script from deltasql Server</li>
The client should now ask the deltasql server for the script to be executed in the database schema. It should touch
 the URL below and store the result as text in a file <tt>script.sql</tt>, for example.
 echo "$dns_name/dbsync_automated_update.php?project=TestProject&version=22&frombranch=TST1&tobranch=HEAD";
<tt>project</tt> is the <tt>PROJECTNAME</tt> column retrieved in step one and <tt>version</tt> is the column
 <tt>VERSIONNR</tt>. <tt>frombranch</tt> is the value stored in <tt>BRANCHNAME</tt>. <tt>tobranch</tt> should be <tt>HEAD</tt>
  if you want to upgrade a schema (branch or development) to the latest development scheman. It should be set to the
   value contained in <tt>BRANCHNAME</tt> if the production schema is updated without becoming a development schema.
If the parameters <tt>frombranch</tt> or <tt>tobranch</tt> are not specified, they default to <tt>HEAD</tt>.
<p>The parameters described in the previous paragraph are the most important ones, but there are more <a href="manual_clientoptions.php">here</a>, to allow customization
 and even XML export. If something goes wrong, deltasql server reports an error according to the following <a href="manual_errormessages.php">list</a>.</p>
<li>4. Visualize or execute script in database schema</li>
<p>Depending if your client includes drivers that are capable of executing complex scripts like packages
 and stored procedures (JDBC drivers are not), you can either directly execute <tt>script.sql</tt> in the database schema
 or show the script to the user and ask him/her to execute it.
The script always contains an UPDATE statement, that sets the new version and branch into the table queried in step one.

<h3><a name="structure"></a>Directory structure of the deltasql_1.x.y  package</h3>

<p>The structure itself is described in this <a href="docs/directory_structure_for_this_package.txt">text file</a>.</p>

<h3><a name="errors"></a>List of error codes for deltasql server</h3>

<p>The entire list is <a href="manual_errormessages.php">here</a>.</p>

<h2>Feedback on this document</h2>
If you need help in setting up your own deltasql server,
 you can contact us at the <a href="mailto:gpu-hide@address.com">deltasql mailing list</a>.
 We appreciate your feedback.

<p>Have fun!</p>
Return current item: deltasql