when deploy my Joomla Component I put in an Update File for the Database. Even if there are no changes for the Database. Is this neccessary?
AND: What are the contents? Do I have to put only these SQL-Statements for the Changes from Version 3 to Version 4 or must there be also the changes from Version 1 and Version 2?
Best Regards DreiBaer
I've done some tests and it doesn't look like you need a SQL update file for a version of your component which doesn't need to make any database changes. However, this may not have always been the case in older Joomla version, see below.
When you install your component for on a Joomla site which doesn't already have a version of your component, the only sql file which is run is the one referenced in the install node of your manifest file. The file may be something like sql/install.mysql.utf8.sql.
So long as the site doesn't have any version of your component, then even if you are installing a later version eg, 2.3.1, and this later version does have some SQL update files, those files will not be executed, only the install file for this version (2.3.1). However in such a situation, whist the SQL update files are not executed, their version numbers are parsed and the greatest version number is stored against the component in Joomla's schemas table. If for example version 2.3.1 of your component contained the SQL update files 0.0.1.sql, 1.0.1.sql, 1.2.1.sql and 2.0.4.sql, then on first installation of this component, the schemas table will have a version_id of 2.0.4.
When updating a component the install SQL file is not executed. Joomla checks the schema version of the installed component and then executes, in order, each SQL update file which has a schema version number greater than the installed schema version number. For example if you have your component installed and it has a schema version of 1.5.2 and you're update contains the SQL update files 1.4.1, 1.5.0, 1.5.2, 1.5.19 and 1.6.0, then only SQL scripts 1.5.19 and 1.6.0 are executed and in that order.
If you install a component for the first time and it has no update file, then the schema table contains a record with a blank version_id field. If you then update that component with a version which does contain SQL update files, then the original schema version is calculated as zero and all update files are executed.
Together what this means, is that for each version of your component, the SQL install file must have all the necessary SQL statements to get the database into the correct state for your component version. On installation, even installation of a later version of your component, no updates will run, so the only SQL you can use to manipulate the database is the SQL in the install file.
So that you component can be upgraded correctly, you must have SQL update files which can take whatever the installed version is up to the version you are trying to update to. If for example an administrator has fallen behind in updating your component, and they have version 1.0.0 installed and want to update directly to your latest version, say 4.0.0, then version 4.0.0 needs to contain the updates from 1 to 2, 2 to 3, 3 to 4 and any subversions between.
There is this old article from Joomla's documentation which discusses exactly the questions you're asking, however its outdated (for version 2.5) and I think the information isn't accurate any longer.
This article says that it is necessary to have blank SQL update files for versions which don't have any SQL updates, otherwise subsequent updates with SQL update files will not be executed, however I've tested this and it doesn't seem to be a problem. This may have been true in older versions of Joomla, but is not true as of the version I tested on (3.5.7).
Its probably best practice to stick to the convention described in the documentation - including the empty SQL update files as it can't hurt, and as the behavior I found in testing is undocumented it could change without warning in the future.
The documentation also says that component version needs to match the update SQL file version, but again I found that this is no longer correct. You can use a SQL schema versioning which is totally independent from the component versioning without any problems. However, again its probably a good idea to keep schema and component version in step with each other - I can't think of a good reason why you wouldn't do this.
