Installing changes to the database
Where do I build the changes in the source code?
Changes in the database structure must be installed at two locations. Once the installation script, which sets up the database for new installations and, secondly, the update script, which adapts existing databases with the new features.
The installation script uses the folder adm_program/ínstallation/db_scripts and the file db.sql. This includes the structure of the complete Admidio database.
For the update there is since version 3.x one file in folder adm_program/installation/db_scripts. Its an XML file with the version name e.g. update_4_1.xml. The structure and editing possiblities are described in the next chapter.
How is editing the XML file?
The XML file is also stored in the folder adm_program/installation/db_scripts and follows the naming schema update_Major Version_ subversion.xml. For version 4.1.0 that would be update_4_1.xml. The XML structure looks like this:
<?xml version="1.0"encoding="UTF-8"?> <update> <step id="10">1. SQL-Statement</step> <step id="20">2. SQL-Statement</step> <step id="30">usw...</step> <step>stop</step> </update>
You can already see how the update works. Showing with ascending ID just one command written in a step.
<step id="40">UPDATE %PREFIX%_organizations SET org_homepage = 'https://www.example.com' WHERE org_homepage IS NULL</step>
For tables always here the placeholder %PREFIX% must be used to ensure that the specific table prefixes for installation is considered. Important also the last step with the content stop. The system detects the desired end and the script is not aborted somewhere.
Which specific parameters can be used within the SQL?
We have implemented a logic to replace some parameters within the SQL so you are more flexible to use simple SQL instead to write a little script.
The following parameters are available:
| Parameter | Minimum version | Description |
|---|---|---|
| %PREFIX% | 3.0 | The prefix of all tables that is set during installation of Admidio. You must use it at each table that is used within the SQL. |
| %UUID% | 4.1 | Some tables have a UUID column which must be filled. With this parameter a unique UUID will be created and replace this parameter. |
Example:
<step id="840">INSERT INTO %PREFIX%_menu (men_com_id, men_men_id_parent, men_uuid, men_order, men_name_intern, men_url, men_icon, men_name, men_description) VALUES (12, 1, %UUID%, 9, 1, 'messages', '/adm_program/modules/messages/messages.php', 'fa-list-ul', 'SYS_CATEGORY_REPORT', 'SYS_CATEGORY_REPORT_DESC')</step>
How can database dependent SQL be executed?
If you want to create database dependent SQL commands to be executed only on a particular database, so even the attribute database with the distinguished name of the database must be stored as a value.
<step id="50" database="mysql"> ALTER TABLE %PREFIX% _organizations ADD COLUMN org_example varchar (255)</step>
How can SQL error output be suppressed?
Sometimes you want to execute an SQL statement but don't want the update to be canceled if this SQL throws an error. For example if you want to delete an specific index but you are not sure if this index exists in every database of all Admidio installations. If you want to suppress the error you can add the attribute error to your step and give it the value ignore.
<step id="130" error="ignore">ALTER TABLE %PREFIX%_announcements DROP INDEX %PREFIX%_FK_ANN_ORG</step>
How can PHP code to run?
In addition to the SQL commands, it is also possible to run PHP code. This is done by calling a method of the class ComponentUpdateSteps. In this class you can add a new method that contains a snippet of code, for eg. loop over all organizations and execute an SQL statement for any organization. You can call this method then by calling this method as a static method in the XML file in a step.
<step id="550">ComponentUpdateSteps::updateStep41AddUuid</step>
