Component database update scripts

[AJH]

The Joomla! docs describe where to place component database scripts (https://docs.joomla.org/J3.x:Developing_a_MVC_Component/Using_the_database) which is fine.

What I'd like to know if the component is required to maintain a "clean" install script, as well as incremental update scripts.

E.g.  If I have three schema changes (three versions), does the "install" SQL Script need to reflect the schema at version 3?

Ideally, I would like my install script to reflect version "1", and then just have to add version "2" and version "3" incremental update scripts. 

[Mathew Lenning]

Nice question and the answer is yes your install awl file should reflect the DB for the latest version.

This is because the update SQL files are only executed on updates, not initial installs.

One way to make your life easier, is to set up a copy of your DB on a dev server, but don't replace the #__

Then before every new release run your lasted update SQL on that DB (this will catch any issues) then dump the structure and place it in your install SQL.

Happy Joomla!ng

[Ove Eriksson]

As I didn't agree with Mathew I had to make a small test. Please correct me if I'm, wrong.

I always use method upgrade in the manifest xml file as this checks if it is a new installation or upgrade. There might be special cases where you need installation instead of upgrade but I never did.

I allways had a basic sql file for the installaion and incremental files for the updates. Those are run also on first installation.

Be aware that you need at least one incremental file with your first version otherwise the incremental files will not run on updaes/upgrades. This can though be empty .

I now tested to move the original basic sql file from the install to the incremental (update - schemas) path. This worked as well!

As the db versions have no relation to the components version I named the three files
 0.0.1.sql 
 0.0.2.sql
 0.0.3.sql

i.e. You don't have to change your first/basic sql. Only add new files with the changes as they are needed.

[Aaron Hudon]

Thanks Ove, this is what I was hoping to do with the update files (use them for installation) but I found that joomla was only using the last file to set the version number during install.

Is this a trick based on your file naming convention...? Naming the files with 0. ?

--
You received this message because you are subscribed to a topic in the Google Groups "Joomla! General Development" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/joomla-dev-general/034aO9exYtw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to joomla-dev-gene...@googlegroups.com.
To post to this group, send email to joomla-de...@googlegroups.com.
Visit this group at http://groups.google.com/group/joomla-dev-general.
For more options, visit https://groups.google.com/d/optout.

[Ove Eriksson]

What do you mean by "only using the last file to set the version number during install. "?

In my case above. the number "0.0.3" is stored in the db table schemas by the installation. If I now add a 0.0.4.sql and run an upgrade only schemas with names larger than 0.0.3 will run (-> 0.0.4) and 0.0.4 will be written to the table. I think it's added keeping 0.0.3 as well that might be the reason for your confusion.

I don't know of any naming convention other than that they have to be sorted. a, b, c /  000, 001, 002 a s o

The Jooma installation for the test added 3.4.0-2015-01-21 as version number in the schemas table but there are 48 sql update files .

[Bakual]

See https://docs.joomla.org/J2.5:Managing_Component_Updates_(Update_SQL_files). That explains it quite well.

The installation.sql file you specify in the manifest is onyl used in the initial installation of the component. The update SQL files aren't applied at that point. So it always has to be the most current version of your database schema.
Every change you make to the schema should be in the update.sql files related to the version of your component. While it's true that the version numbers don't really have to match, it's certainly good practice. This way during an update, Joomla will automatically make the needed changes to the database tables.

[Ove Eriksson]

@Bakual
Did you test it? I did and it works. See below.
Your link doesn't "explains it quite well.". Did you click on it?
------------------------
- I removed the <install> <sql> part from the manifest. (method upgrade)
- I moved the initial installaion sql file to the sql update folder, renaming it. This was and is not a current version!
- I ran an initial installation on a fresh J 3.4
- All tables were created and modified according to the update sql files.

I think I'll use this idea for any future development, if I don't find a valid reason to keep my old handling..

[Mathew Lenning]

@Eriksson,

Admittedly, it has been awhile since I tinkered with the installation package, so something might have changed between then and now. Having a single install process for both would be AWESOME! So I'll have to give it a try.

Just to make sure I understand what you are doing:

1. Removing The install node from the manifest file.

<install> <!-- Runs on install -->
     <sql>
          <file driver="mysql" charset="utf8">sql/install.mysql.utf8.sql</file>
     </sql>
</install>

2. Moving the content of install.sql to the 0.0.1.sql file

3. Then you just add your schema changes in the update sql files from that point forward.

Is that correct? If this works, you just made my day =^P 

@AJH

Although there might not be any "rules" as to what to name the files in the update folder, do your really want to have to remember two sets of version numbers? Also they don't have to sequential. What I mean is that if you make a change in 0.0.2 and don't make any changes in 0.0.3, you don't have to include an empty 0.0.3.sql. I only add update files, if I actually change something. So this is what my Babel-U-Exams updates folder looks like.

• 0.0.1.sql
  
• 1.0.9.sql
  
• 1.1.0.sql
  
• 1.1.3.sql
• 1.2.0.sql
• 1.5.0.sql

There are more, but I'm sure you get the point right.

One more gotcha that you might want to look out for is removing files from your update folder. DON'T DO IT!  

It might be OK for a custom extension, where you always know what version you're updating from. 

But if you're distributing you extension, then you have no clue which version are still in use.

Nothing runes a perfectly good day, than an SQL error on update. 

Anyway, I'm off to experiment with this new possibility. Happy Joomla!ng 

[Ove Eriksson]

Hi Mathew,

Great if you can test and confirm my findings.
The steps you mention is correct. I suppose you already use the method upgrade in the manifest file.

<extension type="component" version="3.0" method="upgrade">

I knew that the upfate sql files are run also on initial installation but I never tried to move the initial sql to the update folder. I thought the initial sql was mandatoy.

------------------
If you use an updated installation sql and your update files run, you can break the installation with sql errors. I'm not sure what happens but ie removing a non existent field (removed already in the initial sql) probably stops the execution. It is though needed for the users updating your component. Remember what I wrote above, you allways need an update sql. This can be empty.

To naming: Using the component version number might be a good idea but not that obvious. If, in your examlpe, the 1.5.0.sql is still valid for the component version 2.6.0 I think it can be a little confusing. Well, its up to the developer.

Ove

[Aaron Hudon]

This worked out great! I'm glad I stirred the pot. 

Thanks very much this is going to simplify installs / updates. 

--

[Ove Eriksson]

Thanks for the idea! I hope Mathew will come to the same conclusion.

A comment;

This part of the manifest file is optional.
 

  <install>
        <sql>
            <file driver="mysql" charset="utf8">sql/install.mysql.sql</file>
        </sql>
   </install>

While this part was and is mandatory. Including at least one sql file in the directory. Can be empty. Even if you do not have any use of a table in your first version, you should include it. You might want to be able to add a table in a later version without a lot of coding.

    <update>
        <schemas>
            <schemapath type="mysql">sql/updates/mysql</schemapath>
        </schemas>
    </update>

[Bakual]

Your process may work. If I understand it right, at least in my component it would be very stupid to do it that way. Because I would create 5 tables, and drop one again during the update SQL files. It also runs a lot of unnecessary queries during the installation. So I don't really see a reason to do it your way. But if it works, feel free to do it that way. Just don't complain if it breaks sometime in the future because it sure isn't the documented way ;-)

If the doc page isn't written well enough, feel free to improve it. It's a wiki. For me it was sufficient enough. But people are different and sometimes what is clear for me isn't clear for others and vice versa.

[Daniel Dimitrov]

I was never really a fan how we do this in the Joomla core. My advise if you want to spare yourself some trouble have a look how it's done in FOF:
https://github.com/akeeba/fof/blob/development/fof/database/installer.php
And either user fof or copy the class in your own library. I've opted for copying the class myself, since I don't yet use fof. You can see how I've done it in this extension:
https://github.com/compojoom/cmc
Basically instead of dealing with .sql files you just now have a single .xml file where you specify your tables for installation, and your conditions on which the database schema should be updated.

Regards,
Daniel

On Tuesday, March 3, 2015 at 5:42:30 AM UTC+1, AJH wrote: