Custom SQL – TigerForge

Read(), Write(), Update() and Delete() methods can be used to work with Custom SQL queries. Instead, the ReadOne() method can’t be used because the Custom SQL Read operation always replies with an array, regardless of whether you are using a select query that returns only one value.

Classic use

If your Custom SQL query just does operations on the selected table, it will reply with a standard package of data and, for this reason, the above four methods can be used in the same way they are used for classic APIs.

Example » UniREST Server

Imagine you have a table named test with a simple structure like this one:

What you want to do is to select all the records with a value greater than or equal to 50. This kind of reading is not possible with the classic API interface, but you can easily do it with this Custom SQL Read query:

SELECT	*	FROM	*[[]]**	WHERE	value	>=	{value}
This is the MySQL command for reading records.	This is the MySQL symbol for “all” (records)	This is the MySQL command for selecting a table where the records must be read from.	This is a UniREST convention. It means to read the currently selected table.	This is the MySQL command for defining conditions.	This is the name of the column I want to use for the records selection.	This is the MySQL operator for “greater than or equal to”.	This is a UniREST convention. It means that the number of the parameter “value” sent by the UniREST Client will be used.

Basically, this query says: select all the records from the table test where the number inside the column value is greater than or equal to the value property I’m going to send from my Unity game.

Example » UniREST Client

In your Unity game, an API like this can be simply called with the Read() method:

_ = UniRESTClient.Async.Read<DB.Test>(
     API.test_read_byvalue, 
     new DB.Test{ value = 50 },
     (DB.Test[] results) =>
     {
          if (results.length == 0)
          {
               Debug.Log("Nothing to read!");
          }
          else
          {
               var r = results[0];
               Debug.Log("First record: name = " + r.name + " value = " + r.value);
          }
     }
);

As result, in the Unity Console panel you should see these messages (with debugMode enabled):

In the third message, you can see the Custom SQL Read query correctly converted in a MySQL query:

FROM	SELECT * FROM [[*]] WHERE value>={value}
TO	SELECT * FROM tfur_test WHERE value>=? ? is replaced with 50 by MySQL during the query execution.

Note

In a MySQL command, the * symbol means “all the columns”. So, in the query of this example, the returned records will contain all the values of all the columns, as you can see in the Unity Console screenshot in the second message. However, the use of * is not mandatory and you can specify the columns name you want to read. The advantage is that the amount of data is reduced to only what you need and the reply from the Server is lighter, improving the internet bandwidth and memory usage.

So, the Custom SQL Read query modified in this way:

SELECT name, value FROM [[*]] WHERE value>={value}

will produce the same result in your Unity game, but using fewer resources, as you can see in the second message compared to the previous Unity Console screenshot:

Complex use

The big advantage of using Custom SQL queries is that you can create complex database operations. There is only one thing you need to consider when using a Custom SQL query for reading.

The Write, Update, and Delete operations always return the same data type regardless of whether you are using the Custom SQL query or the API’s interface. In these cases, you can create any type of custom query and use the Write(), Update() and Delete() methods without any special adaptation.

Instead, if you are creating a complex SELECT for reading data, you may change something in your C# code in Unity. The main reason is that the Read() method expects to receive a data pack aligned to the structure of the read table. However, if your query returns data with a different structure, the Read() method fails (or the response is incomplete) because it cannot recognize this new structure. Basically, in C# you have to create a new data structure, aligned with your complex query response, to be supplied to the Read() method so that it can read your data.

Example » UniREST Server

Imagine you have some users…

…and a table named test where the id is the user id and the name column contains the user’s weapon:

Now, what I want to do is to create a query that joins the users and the test tables, and replies to my Unity game with the list of all usernames with their weapon’s name and value. This is the complex Custom SQL query I’m going to use:

SELECT	[[users]].username, [[test]].name, [[test]].value	FROM	[[test]]	INNER JOIN	[[users]]	ON	[[users]].id = [[test]].id
This is the MySQL command for reading records.	I have specified the columns I want to read and in which tables they are. Note that the tables name are inside double square brackets (UniREST convention)	This is the MySQL command for selecting a table where the records must be read from.	This is a UniREST convention. This is the first table I want to use.	This is the MySQL command for joining two tables’ data.	This is the second table I want to use	This is the MySQL operator that is similar to WHERE but works with INNER JOIN.	This is the rule to use for joining data from users and test tables (they must have the same id).

Example » UniREST Client

In my Unity game I use this C# script:

_ = UniRESTClient.Read<DB.Test>(
     API.test_read_byvalue, 
     new DB.Test(), // <- Here I've no values because my Query doesn't require values from my Unity project.
     (DB.Test[] results) =>
     {
          if (results.length == 0)
          {
               Debug.Log("Nothing to read!");
          }
          else
          {
               var r = results[0];
               Debug.Log("First record: name = " + r.name + " value = " + r.value);
          }
     }
);

When I run my game, the Read() methods works:

In the first Unity Console message, I can see the correct result of my complex query: I have an array of records and each record contains username, name and value.
In the second message, I can see that my Custom SQL query has been “transformed” into a fully functional MySQL query.

However, in the third message, I can only show the name and value, and not the username. This because I have used the DB.Test data structure in the Read() method, and it doesn’t contain the username property:

To resolve this issue, I have to create a new class that contains both the Test properties and the username property:

[System.Serializable]
public class MyNewData {
     public int id = 0;
     public string name = "";
     public float value = 0;
     public string json = "";
     public string username = "";
}

. . .

_ = UniRESTClient.Async.Read<MyNewData>(
     API.test_read_byvalue, 
     new DB.Test(),
     (MyNewData[] results) =>
     {
          if (results.length == 0)
          {
               Debug.Log("Nothing to read!");
          }
          else
          {
               var r = results[0];
               Debug.Log("First record: username = " + r.username + " name = " + r.name + " value = " + r.value);
          }
     }
);

Now, my Read() method can recognize the username value because I’ve created the MyNewData class that contains it and this is the result:

Final note

Because with a Custom SQL query you can have access to all your tables (the tables created by UniREST, but also the tables created by WordPress!), the selection of a table in the interface is just a mere convention. For example, you may have the test table selected, but then create a query that uses a different table (in the example above, the query uses the users table):

The selected table is just used by the [[*]] symbol from the UniREST conventions.

UniREST Solution 3.5

Classic use

Example » UniREST Server

Example » UniREST Client

Note

Complex use

Example » UniREST Server

Example » UniREST Client

Final note