How do I monitor changes in a database from unity without flooding the NIC

Question

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);
                }
            }
        }
    }

Answer 1

Of course you can and always should wait in Unity for the UnityWebRequest to finish! You should allways use e.g. a Coroutine

And than you can as well e.g. add a time offset so you don't make a new request right ahead but maybe wait a few seconds.

I personally would also add callbacks for the response which makes it way more flexible and reusable e.g.

public void MakeRepeatedRequests(string url, float timeOffset, Action<string> successCallback, Action<string> errorCallback)
{
    StartCoroutine(RepeatedRequestRoutine(url, timeOffset, successCallback, errorCallback);
}

private IEnumerator RepeatedRequestRoutine(string url, float timeOffset, Action<string> successCallback, Action<string> errorCallback)
{
    // No worries this still looks like a while loop (well it is ^^)
    // but the yield makes sure it doesn't block Unity's UI thread
    while(true)
    {
        // Configure your request
        var request = UnityWebRequest.Post(url);

        // Make the request and wait until the it has finished (has a response or timeout)
        // a yield kind of tells this routine to leave, let Unity render this frame
        // and continue from here in the next frame
        yield return request.SendWebRequest();

        if(request.isNetworkError || request.isHttpError) 
        {
            errorCallback?.Invoke(request.error);
        }
        else 
        {
            successCallback?.Invoke(request.downloadHandler.text);
        }

        // Now wait for the timeOffset
        yield return new WaitForSeconds(timeOffset);
    }
}

---

Now you can call it e.g. for waiting 2 seconds after the last request finished

// ...

    MakeRepeatedRequests ("http://example.com", 2.0f, OnSuccess, OnError);

// ...

private void OnSuccess(string success)
{
    Debug.LogFormat(this, "Nice it worked! Server said:/"{0}/"", success);

    // Do something e.g. using success
}

private void OnError(string error)
{
    Debug.LogFormat(this, "Oh no, request failed with \"{0}\"", error);
}

or also do the same thing using lambda expressions

MakeRepeatedRequests ("http://example.com", 2.0f, 

    success =>
    {
        Debug.LogFormat(this, "Nice it worked! Server said:/"{0}/"", success);

        // Do something e.g. using success
    }, 

    error =>
    {
        Debug.LogFormat(this, "Oh no, request failed with \"{0}\"", error);

        // Do something else
    });

---

Even if you don't want the timeOffset this at least waits for every request to finish before starting the next one so your server only has to make one DB query and either return a success or an error.

In your case I would respond with a success (200) anyway (because error should be sent only if the server really had an error) but either send an empty string or some predefined one you can check for in Unity (something like e.g. "!NO_CHANGE!").

so on the server side without a loop something like e.g.

results = "SELECT * FROM table_name;"
if ($_REQUEST['variable_name'] != results['variable_name']){
    echo results['variable_name'];
} else {
    echo "!NO_CHANGE!";
}

than you could later in Unity check it

if(string.Equals(success, "!NO_CHANGE!")) return;