| Next revision | Previous revision |
| en:entwickler:aenderungen_in_der_datenbank_einbauen [2015/11/04 22:19] – created thomas-rcv | en:entwickler:aenderungen_in_der_datenbank_einbauen [2021/11/15 14:24] (current) – [How is editing the XML file?] fasse |
|---|
| ====== Installing changes to the database ====== | ====== Installing changes to the database ====== |
| ==== Where do I document changes? ==== | |
| WWe have created a graphical representation of database model using the free software [[http://www.mysql.de/products/workbench/|MySQL-Workbench]] ([[http://dev.mysql.com/downloads/workbench/|Download]]). The current project file with our data model for MySQL Workbench can be downloaded via the following link Subversion: <code> https://admidio.svn.sourceforge.net/svnroot/admidio/trunk/documentation/admidio.mwb </code> | |
| For subsequent versions, you can download the data model here: | |
| * {{:de:entwickler:datenbank:admidio_datenmodell_2-0.pdf|Data model for Admidio Version 2.0}} | |
| * {{:de:entwickler:datenbank:admidio_datenmodell_2-1.pdf|Data model for Admidio Version 2.1}} | |
| * {{:de:entwickler:datenbank:admidio_datenmodell_2-3.pdf|Data model for Admidio Version 2.3}} | |
| **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? ==== | ==== 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. | 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. | 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 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. | 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. |
| | |
| 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? ==== | ==== 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: <code xml><?xml version="1.0"encoding="UTF-8"?> | 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> | <update> |
| <step id="10">1. SQL-Statement</step> | <step id="10">1. SQL-Statement</step> |
| <step id="30">usw...</step> | <step id="30">usw...</step> |
| <step>stop</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 = 'http://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. | </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> |
| |
| 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> |
| |
| 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**. <code xml><step id="550">ComponentUpdate::updateStepDeleteDateRoles</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> |
