The Custom SQL feature allows the creation of complex queries for the 4 operations. It is intended to be used by developers who have a good knowledge of Database queries and who need to perform more complex queries such as, for example, joining the data of two tables.
When activated, this section requests to manually write the query for the operations you want to support. You can freely choose which operation has to be performed with a Custom SQL or the classic method. However, the Custom SQL operation has the priority: for example, if you check the Read operation and then you write a Read Custom SQL, only the Custom SQL Read query will be performed.
API Reply to Unity Game
Once called, this API will reply as expected from each operation. The Read operation will always reply with a list of records, also when only one record is found. See the “UniREST Client / Custom SQL” chapter for details.
Double quotes
Double quotes must be escaped when you use them in a query string. The API saving process will check if your queries contain double quotes and if they are escaped. If they’re not, an alert will inform you and the wrong queries will be highlighted in red.
| WRONG | UPDATE my_table SET name=’Conan “The Barbarian”‘ |
| OK | UPDATE my_table SET name=’Conan \”The Barbarian\”‘ |
SQL Query conventions
The Custom SQL fields must contain a valid MySQL query. For this reason, you should use these features only if you have a good knowledge of Database queries or if you are pretty sure about what you are going to write in those fields.
Since these queries have to communicate with the UniREST Server environment, there are two conventions you must respect in order to have everything properly working.
Table name
When you refer to a table in your query, you have to write the table name between double square brackets. For example, if you are going to write a SELECT query on a table named “player”, you should write the table name in this way:
SELECT * FROM [[player]] WHERE id=10
You can also use the short version [[*]] to indicate the currently selected table.
SELECT * FROM [[*]] WHERE id=10
This because the UniREST Server application creates the Database tables adding the “tfur_” prefix to their name. Using those square brackets, the prefix is added by the system. Otherwise, if you prefer not to use this convention, you must remember to write the table name including the suffix:
SELECT * FROM tfur_player WHERE id=10
Column’s value
When you need to use a column value you are going to receive from your Unity game, you must specify it using the column name between curly brackets. For example, if you are going to read one record of the “player” table, located by the “player_id” and this value is provided by your Unity game, the SELECT query should be something like:
SELECT * FROM [[player]] WHERE player_id={player_id}
In this example, player_id is the column name, whereas the {player_id} is the value received from your Unity game.
Why use Custom SQL
Because your database is a MySQL Database, the UniREST APIs uses MySQL queries to perform operations in your tables. Whether you use the graphical interface or the Custom SQL fields, the UniREST Server application will create and execute a MySQL query.
However, the Custom SQL feature offers some advantages, that can be summarized in the following schema:
| PROS | CONS | |
| API’s GUI | Very simple to use. You can easily and quickly create MySQL queries with just a few clicks. | The options exposed by the graphical interface cover most needs, but it’s limited. |
| Custom SQL | You have no limits in creating MySQL queries. You can perform simple but strongly optimized queries and very complex queries as well. | The use of Custom SQL queries requires a good knowledge of MySQL. Incorrect use can both cause malfunctions in UniREST and compromise the data in the tables. |
Memory issues
When you use custom SQL queries, you have no limits on what you can read and write. However, this also means that you could encounter a lot of problems, especially related to memory limits.
PHP itself has a lot of memory restrictions, like limits about the scripts used memory, the number of bytes you can upload, the size of HTTP requests, and so on.
For example, if you try to read a Database table with columns of LONGTEXT data-type, you will probably get a memory error.
In a situation like that, you may consider using the mySQL CAST function in your query to convert a “too big” value in a smaller format.
Example: SELECT id, CAST (big_value as TEXT) FROM my_table