Download latest build

MessageHandler is a database application, which can be used to save different kinds of messages. They can each be translated in any language you want. This can be used as internationalization for another project. Some basics you have to understand before you can get started:

• You can define which languages are available for translation
• It is possible to translate each message in each language
• Have placeholders in the message translations, which can be replaced when the message is requested and displayed.
  • For example: In the database, the following message translation is stored: "Welcome back, #!". When the message is requested from the database, you can define which text should be replacing the "#!" in the message. So when you call the getMsg procedure from your application, you can specify the username to be put in the welcome message. You can also define in which language the message should be translated.
• Outputs are a method of sending messages to other services. There are several types of outputs: Log file, Twitter and E-mail. It is possible to attach messages to an output. So when a message is requested, that message is automatically outputted through the attached outputs. For example: a certain error message should always be saved to a log file.
• User management: keeps track of who created which messages

• The project is now completely transaction-safe
• Added install script, to guide through installation
• Added Twitter output possibility
• Added triggers to disallow certain characters in data
• Added backup script

To use this project, you need a MySQL Server 5.7 with root permissions. To use the web interface, you also need a web server with PHP and MySQLi Support.

PHP should be in your path variable. This means you should be able to execute the command "php" anywhere.

To use the outputs, you need one of the following:

• A Unix system with cronjobs
• Windows system with Task Scheduler

Extract the zip file containing the compiled version of MessageHandler. Navigate to the "build" folder using a terminal and execute the following command inside that folder:

$ php install.php

You will be prompted to enter a administrative account for the MySQL server to install the script. Typically this is root. Then you will be prompted for the password of that user account. If that user has no password, just press enter.

If the install was ok, it will ask you if you would like to change the defautl MessageHandler user account password. It is highly recommended to do this as the default password is unsecure.

To use the outputs, you need to get the composer dependencies first. To get these dependencies, you need to download Composer: https://getcomposer.org/

Navigate to the build directory using a terminal, which is also going to be the folder where the cron script should be executed from. Run the following command:

$ composer install

This will download all the needed dependencies for the outputs.

Open a terminal and run the command crontab –e. This opens an editor window. You now have to enter this line to execute the script every minute each day. You probably need to change the path to your scripts.

\* \* \* \* \* php /home/messagehandler/cron.php

Illustrations for this step are in the chapter 6. Appendix

• Open Task Scheduler, and click Create Task in the right siderbar
• Press on the Triggers tab, and click the New… Button at the bottom (illustration 1)
  • Select to begin the task at startup
  • Click the checkbox to repeat the task, and write 1 minute in the dropdown.
  • Select Indefinitely in the duration dropdown. When you are done, click OK
• Press on the Actions tab, and click the New… Button at the bottom (illustration 2)
  • Program/script should be the path to your PHP executable
  • In the arguments text field, put cron.php
  • In the Start in field, enter the path to the build directory, in which the cron.php resides

To use the backup script, you just need to run the file backup.php in the build folder at the desired time. Have a look at the two examples above (Unix and Windows), but replace cron.php by backup.php.

It is recommended to run the backup script at least once per week, but it is entirely up to you.

To start using the project, you need to add a few things first. Enter the following commands in a MySQL prompt.

All the procedures have at least two output parameters. These are @code and @message. They can be used to see if the execution of the procedure was successful. If not, @message contains a more detailed description about what went wrong.

If you want to have more information about the individual procedures, have a look at 4. Procedures explained.

1. Create a user

mysql> CALL sp\_addUser('_username_', '_password'_, 1, @code, @message);

The 3

parameter is the admin flag. If 1, the user is going to be an admin. If 0, a regular user will be created.

1. Add Languages

mysql> CALL sp_addLanguage('de', 'German', 'Deutsch', @code, @message);

This adds a language, with the first parameter being the ID ( ISO 639-1), second parameter being the name of the language in english, and the third is the language name in the language itself.

1. Add Message Types

mysql> CALL sp_addMessageType('I', 'en', 'Information', @code, @msg);

If you created multiple languages, you need to run this procedure for each language, replacing each time the language parameter and the translation for that language.

1. Add Messages

mysql> CALL sp_addMessage('Description', 'I', 'username', @id, @code, @message);

This adds a message with a given description, Type of message, and username. @id returns the ID for the created message.

1. Add translations for the message

mysql> CALL sp_addMessageInLanguage(@id, 'en', 'Translation for msg', @code, @message);

This adds a translation for the message created in step 4. You can reuse the @id param. You should run this command for every language and for every message, so that there is a translation for every message. The translation can contain a placeholder, which can be replaced when the message is requested. Placeholder text is #! And can be used multiple times.

After you executed these steps, you can already get a message translation using this simple command:

mysql> CALL sp_getMsg(@id, 'en', NULL, @translation, @code, @message);

This requests the translation of the message in the language and writes it in the variable @ translation. The 3

parameter is skipped using NULL, because this parameter is used for replacing placeholders that are saved in the message translation. When this replace string is given and the translation contains a placeholder, this string replaces the placeholder. If multiple placeholders are set and you want to replace them, separate your replace string using the same delimiter as in the placeholder: #!

As you can see, the output parameter @id can be reused for other procedure calls, if they are within the same connection. If you reconnect, your user variables will be removed. Watch out for this when including MessageHandler in your project.

Have a look at this example:

mysql> CALL sp_addMessageInLanguage(2, 'en', 'Dear #!, welcome to #!.', @code, @message);

mysql> CALL sp_getMsg(2, 'en', 'User#!This Site', @translation , @code, @message);

@translation now contains the following string: 'Dear User, welcome to This Site.'.

To create an output, you must issue the command CALL sp_addOutput in the mysql prompt. It has 3 input parameters: fiUser, fiLanguage, dtName. Assigning a user is optional, but is recommended. Its purpose is to keep track of who created which output. The language parameter however is mandatory and defines a language in which every language should be translated into before getting output. Also, you have to give it a name. This will make it easier to find it once you have several different outputs.

In this case, I want to have a log file which logs several messages. But first, a normal output needs to be created. So I'll give it a name which says that it writes to a logfile.

sp_addOutput('username', 'en', Default logfile output to home.', @id, @code, @message);

Now I create a logfile output and attach it to the created output.

sp_addOutputLogfile(@id, '/home/messagehandler/default.log', @code, @message);

This has now created an output that redirects the messages into log files. The next step is to assign the messages that should be logged.

sp_assignMessageToOutput(1, @id, @code, @message);

The return value of the procedure can also be viewed in order to see if this command worked. But as this is the first message we assigned, there shouldn't be any issues.

Every time the message with id 2 is requested, it is redirected to the output we just created.

This chapter will cover the usage of all the procedures that are available to use. Some of them are used primarily for display purposes of the web interface. The procedures are listed in alphabetical order.

All the procedures have at least two output parameters. These are @statusCode and @statusMessage. They can be used to see if the execution of the procedure was successful. If not, @message contains a more detailed description about what went wrong.

This procedure adds a language to the DB. A language has a 2-character long ID, which is the ISO 639‑1 code of the language. A language also has a name in English and a name in the same language as the language itself.

• 0: No error
• 1: Deadlock/Timeout
• 1062: ID already exists | | OUT | statusMessage | VARCHAR(100) | Status message from the procedure |

This procedure adds a message to the DB. A message is defined only by its description. It is the general description of the message. Every message can later be translated into every language. So the description defines what the language should contain.

A message also has a message type. This defines what type the message is. For example, you could add an Information Type with the identifier 'I'

• 0: No error

• 1: Deadlock/Timeout

• 1062: Description already exists

• 1452: Foreign key error | | OUT | statusMessage | VARCHAR(100) | Status message from the procedure |

This procedure adds a message in a language (translation), for a given message and for a given language. The message field can contain placeholders (#!), which can be replaced when the message is requested. So you may insert a translation with as many placeholders as you like. It is strongly recommended that you configure your messages in such a ways that for every translation, the number of placeholders is the same.

• 0: No error

• 1: Deadlock/Timeout

• 1062: Translation already exists

• 1452: language or message not found | | OUT | statusMessage | VARCHAR(100) | Status message from the procedure |

This procedure creates a new message type. There should be a translation for each message type in each language. This translation is the prefix for the message translation. Each type should should be translated in each language. The ID of the message type is a single character.

• 0: No error
• 1: Deadlock/Timeout
• 1062: ID already exists | | OUT | statusMessage | VARCHAR(100) | Status message from the procedure |

This procedure creates a new output. An output is relatively abstract, as you do not see any difference when you assign a message to it. But you can assign as many different sub-outputs (e.g Logfile or email) of any kind to this output. So every time the message is called, it is automatically sent to all the outputs that it is assigned to.

• 0: No error
• 1: Deadlock/Timeout
• 1062: Name already used | | OUT | statusMessage | VARCHAR(100) | Status message from the procedure |

Adds an email sub-output. It can be assigned to a normal output. So every time the normal output is called, every sub-output that is assigned to that normal output is also called. The information of the recipient is saved when the output is created.

NB : You need to have the cronjobs installed in order to use this output.

• 0: No error

• 1: Deadlock/Timeout

• 1452: Foreign key error | | OUT | statusMessage | VARCHAR(100) | Status message from the procedure |

This procedure adds a log file sub-output. Each time a message that is assigned to this output will be saved in a log file which you can define in this procedure.

NB : You need to have the cronjobs installed in order to use this output.

• 0: No error

• 1: Deadlock/Timeout

• 1452: Foreign key error | | OUT | statusMessage | VARCHAR(100) | Status message from the procedure |

This procedure adds a new Twitter sub-output. Used for sending messages to a Twitter account.

NB : You need to have the cronjobs installed in order to use this output.

• 0: No error
• 1: Deadlock/Timeout
• 1452: Foreign key error | | OUT | statusMessage | VARCHAR(100) | Status message from the procedure |

This procedure adds a new user to the MessageHandler. The password is saved encrypted and salted. These tasks done by this procedure. A user can also be defined as an admin. These users are needed for the web interface login. You do not need to create a user as the columns where the user is referenced can be NULL. But it is a nice convenience nonetheless to see who created a message for example. The username must be unique.

• 0: No error

• 1: Deadlock/Timeout

• 1062: ID already exists | | OUT | statusMessage | VARCHAR(100) | Status message from the procedure |

This procedure allows you to assign a message to an output. When the message is then requested, it is also sent to the outputs it is assigned to. You can only assign messages to parent outputs, not to sub-outputs directly.

• 0: No error

• 1: Deadlock/Timeout

• 1062: Message already assigned

• 1452: Message or output doesn't exist | | OUT | statusMessage | VARCHAR(100) | Status message from the procedure |

This procedure allows you to check if the username and password combination matches one in the database.

• 0: No error
• 1: Deadlock/Timeout | | OUT | statusMessage | VARCHAR(100) | Status message from the procedure |

This deletes a language. When a language is deleted, all message translations, message types and outputs will also be deleted, as they are dependant of the language.

• 0: No error
• 1: Deadlock/Timeout | | OUT | statusMessage | VARCHAR(100) | Status message from the procedure |

This deletes a message. When a message is deleted, all translations of that message are also removed.

• 0: No error
• 1: Deadlock/Timeout | | OUT | statusMessage | VARCHAR(100) | Status message from the procedure |

This deletes the translation of a message.

• 0: No error
• 1: Deadlock/Timeout | | OUT | statusMessage | VARCHAR(100) | Status message from the procedure |

This deletes a message type. All messages of that type are also removed.

• 0: No error
• 1: Deadlock/Timeout | | OUT | statusMessage | VARCHAR(100) | Status message from the procedure |

This procedure is used to delete an Output. All sub-outputs (Twitter, Log file, E-mail) are also deleted because they don't have a parent any more.

• 0: No error
• 1: Deadlock/Timeout | | OUT | statusMessage | VARCHAR(100) | Status message from the procedure |

sp_deleteOutputLogfile, sp_deleteOutputTwitter

This deletes a sub-output. As the syntax is the same for the three procedures, the parameters below work for all three.

• 0: No error
• 1: Deadlock/Timeout | | OUT | statusMessage | VARCHAR(100) | Status message from the procedure |

This procedure removes the assignment from a message to an output, meaning that the message will no longer be handled by the given output when the message is requested.

• 0: No error
• 1: Deadlock/Timeout
• 1062: ID already exists | | OUT | statusMessage | VARCHAR(100) | Status message from the procedure |

Updates the description of a message. The description is limited to 255 characters.

• 0: No error
• 1: Deadlock/Timeout | | OUT | statusMessage | VARCHAR(100) | Status message from the procedure |

Updates a translation for a message in a language.

• 0: No error
• 1: Deadlock/Timeout | | OUT | statusMessage | VARCHAR(100) | Status message from the procedure |

Updates the message type of a messge.

• 0: No error
• 1: Deadlock/Timeout | | OUT | statusMessage | VARCHAR(100) | Status message from the procedure |

Updates the user setting admin permissions. isAdmin is a BOOLEAN.

• 0: No error
• 1: Deadlock/Timeout | | OUT | statusMessage | VARCHAR(100) | Status message from the procedure |

Updates a users' password. Hashes it and adds a salt for extra security.

• 0: No error
• 1: Deadlock/Timeout | | OUT | statusMessage | VARCHAR(100) | Status message from the procedure |

All procedures that start with sp_get are special. As it is not possible save multiple rows of data in a variable, all get methods use the same way of saving the data in a variable. A CSV String is formed, but with special characters delimiting rows and columns. Rows are delimited by ^ and values by ~.

For example: sp_getLanguages returns idLanguage, dtName, dtLocalizedName. If there is a single row, the following string will be built: enEnglishEnglish. If there are multiple rows, a result might look like this: enEnglishEnglish^deGermanDeutsch^frFrenchFrancais.

This is the primary procedure used for getting translations, which also sends them to their outputs. You can specify the replacement strings, which are used to replace the placeholders in the message.

sp_getMsg(idMessage, idLanguage, replace, @translation, @code, @message);

Here is an example to further illustrate the placeholders. Suppose you have a message translation for the idMessage: 4 and the idLanguage: en. The translation looks like this: "Dear #! , your last login was on #! .".

If you wanted to get this message with two replacement strings, for example Jack and 2016-06-16, the call for the message would look like this:

sp_getMessageInLanguage(4, 'en', 'Jack#!2016-06-16', @translation, @code, @message);

This procedure returns the language of an output:

sp_getLanguageOfOutput(idOutput, @language, @code, @message);

This procedure returns all languages:

sp_getLanguages(@languages, @code, @message);

This procedure returns all the languages for a message which don't have a translation yet:

sp_getLanguagesNotUsedInMessage(idMessage, @languages, @code, @message);

This returns all languages that have been translated for a message.

sp_getMessageLanguages(idMessage, @languages, @code, @message);

This returns all languages that have been translated for a message.

sp_getMessages(@messages, @code, @message);

This returns the translation of the message type for the language.

sp_getMessageTypeInLanguage(idMessageType, idLanguage, @type, @code, @message);

This returns the type of message for a given message.

sp_getMessageTypeOfMessage(idMessage, @msgtype, @code, @message);

This returns all message types from the db.

sp_getMessageTypes(@types, @code, @message);

This returns all languages that have been translated for a message.

sp_getMessageTypesInLanguage(idLanguage, @msgtypes, @code, @message);

Returns the log and email outputs that need to be handled through the cronjob script. It returns the messages, along with the information they need to be sent.

sp_getPendingLog(@types, @code, @message);

sp_getMessageTypes(@types, @code, @message);

sp_getMessageTypes(@types, @code, @message);

Illustation 1

Illustation 2