does it work? SQL transactions?

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 740
Joined: 2019-05-21 22:42
Location: Germany
Contact:

does it work? SQL transactions?

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

Code: Select all

$result = sqlValue('BEGIN;');
and after everything is actually done, I would send a "COMMIT" in the _after_delete:

Code: Select all

$result = sqlValue('COMMIT;');
Doing this, we can do some additional checks etc. and maybe rollback the transactions if something smells funny

Code: Select all

$result = sqlValue('ROLLBACK;');
Olaf

* https://www.mysqltutorial.org/mysql-transaction.aspx
Some postings I was involved, you might find useful:
Backup your database (viewtopic.php?f=4&t=3341); Improve security (viewtopic.php?f=4&t=3168); Field Permissions (viewtopic.php?f=4&t=3308); Custom (error) message (viewtopic.php?f=7&t=1740&p=10871#p10906); Audit Log (viewtopic.php?f=4&t=1369&p=10407); Two Factor Authentication (viewtopic.php?f=7&t=3306&p=11478); Add 2nd SAVE CHANGES button (viewtopic.php?f=2&t=3242&p=11104); Place a search on details view (viewtopic.php?f=2&t=3479&p=12484#p12484); Column-Value-Based-Permissions (viewtopic.php?f=4&t=3498)

pfrumkin
Veteran Member
Posts: 71
Joined: 2020-02-18 17:58
Location: Albuquerque, New Mexico

Re: does it work? SQL transactions?

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
AppGini Super Hero
Posts: 740
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: does it work? SQL transactions?

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
Some postings I was involved, you might find useful:
Backup your database (viewtopic.php?f=4&t=3341); Improve security (viewtopic.php?f=4&t=3168); Field Permissions (viewtopic.php?f=4&t=3308); Custom (error) message (viewtopic.php?f=7&t=1740&p=10871#p10906); Audit Log (viewtopic.php?f=4&t=1369&p=10407); Two Factor Authentication (viewtopic.php?f=7&t=3306&p=11478); Add 2nd SAVE CHANGES button (viewtopic.php?f=2&t=3242&p=11104); Place a search on details view (viewtopic.php?f=2&t=3479&p=12484#p12484); Column-Value-Based-Permissions (viewtopic.php?f=4&t=3498)

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 899
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: does it work? SQL transactions?

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 />

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 899
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: does it work? SQL transactions?

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 />

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 740
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: does it work? SQL transactions?

Post by onoehring » 2021-01-13 10:56

Hi Jan,

thank you for the starter-code.
Olaf
Some postings I was involved, you might find useful:
Backup your database (viewtopic.php?f=4&t=3341); Improve security (viewtopic.php?f=4&t=3168); Field Permissions (viewtopic.php?f=4&t=3308); Custom (error) message (viewtopic.php?f=7&t=1740&p=10871#p10906); Audit Log (viewtopic.php?f=4&t=1369&p=10407); Two Factor Authentication (viewtopic.php?f=7&t=3306&p=11478); Add 2nd SAVE CHANGES button (viewtopic.php?f=2&t=3242&p=11104); Place a search on details view (viewtopic.php?f=2&t=3479&p=12484#p12484); Column-Value-Based-Permissions (viewtopic.php?f=4&t=3498)

pfrumkin
Veteran Member
Posts: 71
Joined: 2020-02-18 17:58
Location: Albuquerque, New Mexico

Re: does it work? SQL transactions?

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

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 899
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: does it work? SQL transactions?

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 />

pfrumkin
Veteran Member
Posts: 71
Joined: 2020-02-18 17:58
Location: Albuquerque, New Mexico

Re: does it work? SQL transactions?

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

Post Reply