Installing changes to the database [Admidio-Wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== 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: <code xml><?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></code>You can already see how the update works. Showing with ascending ID just one command written in a **step**. <code xml><step id="40">UPDATE %PREFIX%_organizations SET org_homepage = 'https://www.example.com' WHERE org_homepage IS NULL</step></code> 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: <code sql><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>
</code>

=== 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. <code xml><step id="50" database="mysql"> ALTER TABLE %PREFIX% _organizations ADD COLUMN org_example varchar (255)</step></code>


=== 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**. <code xml><step id="130" error="ignore">ALTER TABLE %PREFIX%_announcements DROP INDEX %PREFIX%_FK_ANN_ORG</step></code>

=== 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 [[https://www.admidio.org/dokusource/class_component_update_steps.html|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**. <code xml><step id="550">ComponentUpdateSteps::updateStep41AddUuid</step></code>