Developing an MVC Component/Using the database
From Joomla! Documentation
< J3.x talk:Developing an MVC ComponentRevision as of 03:52, 29 March 2018 by Robbiej (response to improvement suggestion)
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.
Yes, I think there are a lot of places in this tutorial series where the explanation in the text could be improved, and I wondering whether to do that when I was doing the videos, but in the end didn't tackle it. So if you're up for doing any of that, then that would be great!
I haven't found any group of people who are actively maintaining and overseeing the technical descriptions in these tutorial pages, so it's not like an arrangement where you submit a change request for modifications; as far as I can see, you just go ahead and make the improvements yourself.
As far as your specific suggestion, would you fancy writing a short section below the code snippet which answers the question "How does Joomla handle schema versions?" And then include there your comments above, and to align with the "important note" text which is there already? I think if readers understood how Joomla handles the updates, then they're in a better position to appreciate why they need to or should do certain things. What do you think?