If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
-
onoehring
- AppGini Super Hero
- Posts: 1167
- Joined: 2019-05-21 22:42
- Location: Germany
-
Contact:
Post
by onoehring » 2021-01-11 14:03
Hi,
I just thought I quickly ask ( did not try yet myself): Can we use SQL transactions* in the hooks?
Did anyone do this already? What was your experience?
Example:
In hooks/tablename.php -> _before_delete I send a "BEGIN" to the database like
and after everything is actually done, I would send a "COMMIT" in the _after_delete:
Doing this, we can do some additional checks etc. and maybe rollback the transactions if something smells funny
Olaf
*
https://www.mysqltutorial.org/mysql-transaction.aspx
-
pfrumkin
- Veteran Member
- Posts: 157
- Joined: 2020-02-18 17:58
- Location: Albuquerque, New Mexico USA
Post
by pfrumkin » 2021-01-11 18:40
Hi Olaf,
I would guess that the AG SQL functions are stateless so I'm thinking the answer is no. I am new to MySQL so I didn't know, I was looking at creating a stored procedure, so you could encapsulate all of your transaction logic into a single stored procedure call (in theory). I did just create a function in MySQL and that works so I suspect that the stored proc is doable, i just never followed up.
~Paul
-
onoehring
- AppGini Super Hero
- Posts: 1167
- Joined: 2019-05-21 22:42
- Location: Germany
-
Contact:
Post
by onoehring » 2021-01-13 07:13
Hi,
well, I can imagine, not every developer has permissions to create this or that in MySQL. On one server I am working, I can not create functions, so I am guessing creating stored procedures are also a problem.
I guess I will have to try transactions and report back.
Olaf
-
jsetzer
- AppGini Super Hero
- Posts: 1817
- Joined: 2018-07-06 06:03
- Location: Kiel, Germany
-
Contact:
Post
by jsetzer » 2021-01-13 07:37
Just a reminder:
When, during development, you make use of additional database-specific features (like triggers, stored procedures, specific constraints), always remember that you will have to migrate those to your production databases manually. This can be done using (manually coded) SQL scripts, an additional migration tool or any database workbench tool like Adminer or phpMyAdmin.
Those will not be applied to the target databases by the AppGini-integrated migration script.
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readability
AppGini 24.10 Revision 1579 + all AppGini Helper tools
-
jsetzer
- AppGini Super Hero
- Posts: 1817
- Joined: 2018-07-06 06:03
- Location: Kiel, Germany
-
Contact:
Post
by jsetzer » 2021-01-13 08:58
I am not liable for loss of data
Transactions in mysqli
Code: Select all
function templates_init(&$options, $memberInfo, &$args)
{
// Please test this for yourself before using it in production environments.
// I give no guarantee and I am not liable for loss of data.
// Remember to make regular backups.
$db = db_link();
$db->autocommit(FALSE); // Important
// do your SQL inserts/updates/deletes here
$db->query("INSERT INTO templates(name) VALUES ('Test1')");
// validate: number of templates named "Test1" should be 1 within transaction
$count = sqlValue("SELECT count(*) FROM templates WHERE name='Test1'");
assert($count == 1, "Number of records inside transaction");
// commit transaction
// $db->commit();
// validate: number of templates named "Test1" should be 1 after commit
// assert($count == 1, "Number of records after commit");
// or
// rollback transaction
$db->rollback();
// validate: number of templates named "Test1" should be 0 after rollback
$count = sqlValue("SELECT count(*) FROM templates WHERE name='Test1'");
assert($count == 0, "Number of records after rollback");
$db->autocommit(TRUE); // Important!
return TRUE;
}
Recommendation
If you want to use something like this more often, I would put it in a PHP-function or PHP-class instead of copy & paste.
Links
https://www.php.net/manual/de/mysqli.be ... action.php
https://www.php.net/manual/de/mysqli.autocommit.php
https://www.php.net/manual/de/mysqli.commit.php
https://www.php.net/manual/de/mysqli.rollback.php
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readability
AppGini 24.10 Revision 1579 + all AppGini Helper tools
-
onoehring
- AppGini Super Hero
- Posts: 1167
- Joined: 2019-05-21 22:42
- Location: Germany
-
Contact:
Post
by onoehring » 2021-01-13 10:56
Hi Jan,
thank you for the starter-code.
Olaf
-
pfrumkin
- Veteran Member
- Posts: 157
- Joined: 2020-02-18 17:58
- Location: Albuquerque, New Mexico USA
Post
by pfrumkin » 2021-01-13 19:44
Hi Jan,
This is a great idea. Do you know that it works, to implement a state-aware kind of function in PHP? That would solve that problem of having to manually update the database outside of AG.
~Paul
-
jsetzer
- AppGini Super Hero
- Posts: 1817
- Joined: 2018-07-06 06:03
- Location: Kiel, Germany
-
Contact:
Post
by jsetzer » 2021-01-13 21:40
Sorry, I do not understand the question. What do you mean by outside AppGini?
This is mysqli functionality. It should work wherever mysqli is used.
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readability
AppGini 24.10 Revision 1579 + all AppGini Helper tools
-
pfrumkin
- Veteran Member
- Posts: 157
- Joined: 2020-02-18 17:58
- Location: Albuquerque, New Mexico USA
Post
by pfrumkin » 2021-01-13 23:12
My bad, I didn't look closely enough at the code before. I am less familiar with the PHP/MySQL but I see what this is doing.
I meant to your point about functions, stored procedures, et al, having to migrate those to the database manually, they are not managed by the AppGini-integrated migration script. The less of that the better.
~Paul