4

I have been working with a small team on a small project about fiction world building. I have been assigned with the task of managing triggers/chained behavior of entities (rocks/places/items) that can be triggered in many ways such as throwing a magic rock into the lake and monster X will appear, and continue to trigger the things in a chain until it reaches the end.

I have tried this

$Trigger_123 = new stdClass();
$Trigger_123->name = "Name";
$Trigger_123->renderOn = ? // (object_345->throwedInLakeX) ?

How can I store this in MySQL? Can I have it checked if its a chain part? Also I tried MySQL triggers but I can't find a way to execute PHP on those triggers. Running PHP code on update or delete for example.

Cron jobs was not a option because many things will be added in the future and cron jobs will take a lot of time to finish, I was hoping of finding a more php-based solution.

Edited (adding some additional information)

I tried to implement this in many ways. I ended up with a system of dependecies pretty much like debian packages which I believe is not suited for this.

Database structure

Table "object"
--------------
ID (int)
Name (varchar)

Table "triggers"
----------------
ID (int)
Name (varchar)
Data (blob) // usually, I store php code and use eval to run

Table "attributes"
------------------
ID (int)
attribute (varchar)
value (blob)

Table "object_has_triggers"
---------------------------
ID (int)
ObjectID (int)
TriggerID (int)

Table "object_has_attributes"
-----------------------------
ID (int)
ObjectID (int)
AttributeID (int)

What I want as a result is to make a PHP code snippet execute each time

  • A database transaction , before is submitted and after to database
  • A object that has X triggers attached to it, resolve them
  • Each Trigger that is triggered by X be checked if all dependecies to it are satisfied

Question: Is something like this even possible to build with PHP Or should I try other scripting languages like python?

Bailey Parker
  • 15,599
  • 5
  • 53
  • 91
Gntem
  • 6,949
  • 2
  • 35
  • 48
  • There is no built-in method to achieve what you're describing. So you might need to look for a library to support your needs or implement such behaviour yourself. – Yoshi Jul 19 '12 at 10:06
  • all my attempts of implementing this, have failed, i saw this as a object that must have X dependency satisfied and the X dependency have its own dependencies satisfied and keeps going on but i fall into a never-ending dependency check and object behavior resolving. – Gntem Jul 19 '12 at 10:12
  • They are interacting with database directly and you need to "see" this events and do something with PHP about them? Is that correct? – ewooycom Jul 23 '12 at 13:37
  • @user1188570 thats correct, i want to implement triggers in MySQL to trigger certain queries and also triggers in PHP application, for example X code with trigger Y code. – Gntem Jul 23 '12 at 18:36

2 Answers2

2

what i want as a result is to make a PHP code snippet execute each time

  • A database transaction , before is submitted and after to database

You should call PHP function in trigger. Write all logic in PHP which required to invoke.

  • A object that has X triggers attached to it, resolve them

A object that has X triggers attached to it, rather convert it into PHP code or resolve it in PHP.

  • Each Trigger that is triggered by X be checked if all dependecies to it are satisfied

You can make one database table for saving responses after successful completing trigger. And at last you can check all dependencies are satisfied or not.

For calling PHP function from trigger, see different answers of following posts for different types of solutions.

Invoking a PHP script from a MySQL trigger

Community
  • 1
  • 1
Somnath Muluk
  • 55,015
  • 38
  • 216
  • 226
  • yes, nice answer, i was suspecting that i needed to build a PHP code editor with php code templates and execute with eval, the information with creating database table from response of trigger just what i was looking for to check triggers results, thanks a lot – Gntem Jul 23 '12 at 19:22
1

A PHP code snippet execute each time a database transaction , before is submitted and after to database

Don't reinvent the wheel, this has an incredible simple solution: have a layer on top of your database calls.

Instead of querying your database directly, call a function (perhaps in an object) that handles the database insertion of triggers. And it is right there that you can add your code to pre and post- process your triggers in whichever way you please.

function processDatabaseInsertion($trigger) {
    //Preceding code goes here

    //Database transaction goes here

    //Post-processing code goes here
}

$Trigger_123 = new stdClass();
$Trigger_123->name = "Name";
$Trigger_123->renderOn = $object_345->throwedInLakeX;

processDatabaseInsertion($Trigger_123);

Over simplified, but you get the idea. I would recommend writing a custom class for your triggers but I wrote it in a procedural style since I don't know if you are familiar with OOP.

A PHP code snippet execute each time a object that has X triggers attached to it, resolve them

Same principle as before. If you use PHP >= 5.3, you can spice it up a bit using closures:

function processDatabaseInsertion($trigger) {
    //Preceding code goes here
    $trigger->renderOn();

    //Database transaction goes here

    //Post-processing code goes here
}

$Trigger_123 = new stdClass();
$Trigger_123->name = "Name";
$Trigger_123->renderOn = function() use ($Trigger_123) { doAwesomeThing($Trigger_123); }

processDatabaseInsertion($Trigger_123);

Or otherwise go for a more traditional approach:

function processDatabaseInsertion($trigger) {
    //Preceding code goes here
    switch($trigger->renderOn) {
        case "awesomeThing":
            doAwesomeThing($trigger);
            break;
        case "anotherThing":
            break;
        default:
            break;
    }


    //Database transaction goes here

    //Post-processing code goes here
}

$Trigger_123 = new stdClass();
$Trigger_123->name = "Name";
$Trigger_123->renderOn = "awesomeThing";

processDatabaseInsertion($Trigger_123);

Each Trigger that is triggered by X be checked if all dependecies to it are satisfied

Can easily be handled by the methods above with some more PHP logic as you will be able to tell. You may want to have for instance a generic function called every time you need to process a trigger which in turns check if dependencies are satistifed and runs the specific trigger if so.

Either way there are better ways to tackle this problem than to use eval or some mysql trigger hacking as you can see :)

Mahn
  • 16,261
  • 16
  • 62
  • 78
  • nice approach, you use more PHP than MySQL, which makes available a ton of functionality, thanks – Gntem Jul 25 '12 at 04:54
  • @GeoPhoenix: I have given same opinion for 2nd question. Only the case I haven't wrote code for you. – Somnath Muluk Jul 25 '12 at 05:10
  • @SomnathMuluk i want to avoid any possible conflct between users that give answer this looked more solid with a small snippet of code, i didn't demand it, but the user provided some logic with OOP code. its not final though, i'll choose the final answer near the date when bounty ends. – Gntem Jul 25 '12 at 09:57
  • @GeoPhoenix the logic I provided isn't OOP though, but it's not hard to imagine a "master" Trigger class that handles all this within its own properties and methods. – Mahn Jul 25 '12 at 10:16
  • @Mahn yes i understand that,i find it difficult keeping track of triggers in database (store) and resolve fetched triggers. for that i queried the whole database over and over again for bulding depedencies needed for triggers. – Gntem Jul 25 '12 at 10:22
  • @GeoPhoenix do I understand right that you want to store a list of all possible types of triggers in the database, and parallely the actual triggers as they happen, which in turn reference the type of the trigger listed in the other table of the dabatase? – Mahn Jul 25 '12 at 14:42
  • If so: you don't need to. You see, the list of types of triggers is static data, it doesn't/should not change that often, and therefore storing them in the database is unnecessary — hardcode the triggers with a switch like I show in the answer and you won't need to keep a list of types of triggers in the database nor rely on dangeours evals. – Mahn Jul 25 '12 at 14:46
  • And even if you have lots and lots of triggers and you are constantly adding new ones, I would still advise to keep it all to PHP — databases are not designed for this otherwise. The only reason to store your triggers code/type list in the database would be if you let the user create/delete new kinds of triggers as they go, but this is a very rare case; and you probably don't want an user to freely write the code for a new trigger anyway. So, if I'm correct in my assumptions, skip storing the trigger types in the database altogether :) – Mahn Jul 25 '12 at 14:52
  • Also, you can simply have an array that specifies the trigger dependencies in PHP aswell — (eg. `array("TriggerA" => array("TriggerDependency1", "TriggerDepedency2"))`) this data like the types of triggers is probably not meant to change that often either, and with this method you have the problem of constantly querying the database to resolve the dependencies solved. – Mahn Jul 25 '12 at 14:59
  • @Mahn actually basic type of triggers exists such as "inArea trigger","BattleAction trigger","HasFlying trigger". The whole idea is meant to be an application used by small group and not let many users insert new types of triggers. I'm storing in to database because i think is a better solution than flat php files.That array is something that i build with queries and assign to an object. – Gntem Jul 25 '12 at 15:06
  • @GeoPhoenix why do you think it is better? If you feel you need something that can be edited "faster" or "easier" than PHP, consider storing this data in a json formatted file for instance. Either way, using the database for this kind of data is IMHO an overkill; if your data is static there's no reason to force it to go through the overhead of a database. – Mahn Jul 25 '12 at 15:17