Developing an MVC Component/Using the database
From Joomla! Documentation
< J3.x talk:Developing an MVC ComponentRevision as of 16:50, 23 March 2018 by MarkRS (Suggested improvement for install and update file explanation)
Could I suggest an improvement.
Where it says
create two files called admin/sql/install.mysql.utf8.sql and admin/sql/updates/mysql/0.0.6.sql. They should both have the same content
it is at best unhelpful.
As I understand it, the SQL install route is to set up the component's tables "from scratch" and the update route is to incrementally add to that with subsequent releases. Therefore, the install file needs to always represent the full definition of the component's tables, incorporating any changes that the complete set of update files have introduced "along the way". That way a fresh install is always correct, and any update from any released version also works correctly.
If the component developer adds database functionality after releasing version without it, then what the line above says is required, otherwise it isn't.
So what should it say? Here's my suggestion (can we drop the "with your favourite..."? It was funny many years ago, but now...)
Create a file called admin/sql/install.mysql.utf8.sql as follows (SQL as before) Decide what version you want to give to this schema and create an update file, so if you want to call this schema version 0.0.6 create an update file admin/sql/updates/mysql/0.0.6.sql. If you have already released versions of your component, as we have simulated in this tutorial series, then this update file must have exactly the same content as the install file. If you have not, then it should be empty. As you make subsequent releases of your component the database install file must always contain the full schema and the update files only need to contain any changes you have made to the schema. The schema numbers don't have to relate to the component version numbers at all, Joomla takes your schema numbers from the names of the update files. That's why it is vital that there should be an initial update file, even if it's empty. If you want to keep your schema numbers in step with the component version numbers when you update your code but not the database schema, you simply include an update file to go with the new release number, and that update file, too, will be empty.