I have a web server which contains a sql database. There are two "users" to this server/database. One is the actual user where they will submit changes through a UnityWebRequest using HTTP:Post. This will make changes to the database.
I have another system that is also in Unity, that needs to be notified, or somehow monitor for whenever a change is made to a specific table in a database. I don't know how to monitor the table for changes without constantly making select calls to the database.
What I've tried
I have a unity function which calls the webserver through HTTP:Post. The webserver goes into an infinite while loop making calls to the database something like
$variable = $_REQUEST['variable_to_monitor'];
$stmt = $pdo->prepare("SELECT variable_to_monitor FROM table_name;")
while(true){
$stmt->execute();
results = $stmt->fetchAll()[0];
if ($variable != results['variable_to_monitor']){
die(results['variable_to_monitor']);
}
}
This holds up the webserver and is making too many calls to the database.
I would like for Unity to be able to make a single call to a web server giving it a given state, the web server will compare said state to database until the database changes, then once the db changes respond to unity with the updated state. I want to be able to do this without making a million SELECT calls to a database per second.
Unity Code
void Update()
{
if(hasResponse)
{
hasResponse = false;
StartCoroutine(SendRequest());
}
}
IEnumerator SendRequest(WWWForm form = null, Action<string> callback = null)
{
if(null == form) form = new WWWForm();
form.AddField("request", "monitor_variable");
form.AddField("variable_to_monitor", this.variable_to_monitor);
UnityWebRequest www = UnityWebRequest.Post(url, form);
yield return www.SendWebRequest();
if (www.isNetworkError)
{
Debug.Log("Network Error");
}
else if (www.isHttpError)
{
Debug.Log("Http Error");
}
else
{
if(null == callback)
{
Debug.Log(www.downloadHandler.text);
}
else
{
if (!www.downloadHandler.text.Contains("New Request Started"))
{
hasResponse = true;
callback(www.downloadHandler.text);
}
else
{
Debug.Log(www.downloadHandler.text);
}
}
}
}