2. PHPRunner General questions
  3. Initial values in DB tables on app install/update
This topic is locked

Initial values in DB tables on app install/update

 		11/17/2022 5:55:46 AM
PHPRunner General questions
dageci authorDevClub member

Hello,
when you create an app that would be used by more than 1 client, how do you manage the initial data in some tables?

For example if we have a table for example "pay_codes" and you want to prepopulate it when a user installs your app or update it if some data changes when a user installs an update of your app.

Would be ok to do that in "AfterAppInit" event and (if we use MySQL/MariaDB) the INSERT IGNORE, DUPLICATE KEY UPDATE and(or) REPLACE sql scripts like described in this article:
https://sebhastian.com/mysql-insert-if-not-exists

Or do you have any other approach for this?

Thanks,
Davor
mbintex 11/17/2022

We add standard values for some value list tables and settings in the after registration event - In this case it is clear, that no data can exist for this user.

In some cases we have custom buttons, that add standard data optionally in case the user wants this and uses a module, for example holidays in a calendar table.

We have a revision number field in the user table and after successful login we check this revision number. If we have a higher number in our programming, we can for example add some fields to the database or delete and modify fields and then set the revision to the new version. Smaller updates for the database are easy this way and can be fully automated.
admin 11/17/2022

When you create a new customer you need to execute a SQL script that creates new data for them. Can be an external SQL file or just some code in events. Either way will work.

dageci authorDevClub member 11/17/2022

Thanks guys for your responses,
@mbintex unfortunately users are added manually, so I don't have a registration page.

I see (from the manual) that in "AfterAppInit" event there is no connection established yet, so I changed it to "BeforeProcessLogin".

@admin is that ok, the script is:

function BeforeProcessLogin($pageObject)
{
//Occurs when the page processing starts and the database connection is established.

//********** Insert records if theey don't exist ************
DB::Exec("INSERT IGNORE INTO `att_counter_type` (`code`, `name`) VALUES ('EXC', 'Exception');");
DB::Exec("INSERT IGNORE INTO `att_counter_type` (`code`, `name`) VALUES ('LEA', 'Leave');");
DB::Exec("INSERT IGNORE INTO `att_counter_type` (`code`, `name`) VALUES ('OVT', 'Overtime');");
DB::Exec("INSERT IGNORE INTO `att_counter_type` (`code`, `name`) VALUES ('REG', 'Regular');");
DB::Exec("INSERT IGNORE INTO `att_counter_type` (`code`, `name`) VALUES ('TRA', 'Training');");
}

I use IGNORE, if the record already exists to ignore the insert.