This is an old revision of the document!
Installing changes to the database
Where do I document changes?
We have created a graphical representation of database model using the free software MySQL-Workbench (Download). The current project file with our data model for MySQL Workbench can be downloaded via the following link Subversion:
https://admidio.svn.sourceforge.net/svnroot/admidio/trunk/documentation/admidio.mwb
For subsequent versions, you can download the data model here:
Changes or extensions to the data model must first be maintained in the admidio.mwb on the MySQL Workbench! Only after that changes should be done in the installation scripts of Admidio and used in the source code.
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 and is identical to the data model from the MySQL Workbench. Here are the changes need to be implemented from the data model 1:1.
For the update there are up to version 2.x 2 files in folder adm_program/installation/db_scripts. There is a SQL file upd_2_4_0_db.sql. There only SQL statements can be entered separated by semicolon. The file may have to created with the current version number, in which the changes are to be installed. Additionally, there is a PHP file upd_2_4_0_conv.php which eg. enables data conversions or may include additional logic. These may need to be still created with the current version number.
Starting with version 3.0, there is only one files in the folder adm_program/installation/ db_scripts. The two files have been replaced by a more flexible xml file. For this purpose in the next chapter is a detailed description.
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 3.0.0 that would be update_3_0.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.
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 Component Update. 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">ComponentUpdate::updateStepDeleteDateRoles</step>
