How to receive any changes in MySQL database in real time from client code in a timed function?

Question

I have a database with a log record table which looks like this:

+-----------+------------------------+--------+
| Timestamp | Symbol_name            | Status |
+-----------+------------------------+--------+
|         1 | Group2                 |      1 |
|         2 | Group1-Device3-Signal1 |      1 |
|         3 | Group2-Device1-Signa13 |      0 |
+-----------+------------------------+--------+

Where Timestamp is a double, Symbol_name is a varchar, and Status is an int.

Log records which contain the above data are going to be inserted into the table in real time, and my client code is supposed to query those records and analyze them. The problem I am having right now is reading a unique record each query. Currently, I have this function:

/* Called every 1000 ms (1 second). */
gboolean app_models_timed_query(gpointer data) {
    FwApp *app = data;

    char query[APP_MAXIMUM_STRING_CHARS];

    strncpy(query, "SELECT * FROM ", APP_MAXIMUM_STRING_CHARS);
    strncat(query, app->query_db_table_name, APP_MAXIMUM_STRING_CHARS);
    strncat(query, " WHERE Timestamp <> @lastSeenTimestamp AND Symbol_name <> @lastSeenSymbolName AND Status <> @lastSeenStatus;", APP_MAXIMUM_STRING_CHARS);

    if (mysql_query(app->query_db_con, query))
    {
        printf("Unable to retrieve data from query table.\n");
        return TRUE;
    }

    MYSQL_RES *result = mysql_store_result(app->query_db_con);

    if (result == NULL) return TRUE;

    /* Analyze the resulting row(s) here. */

    /* How to set @lastSeenTimestamp, @lastSeenSymbolName and @lastSeenStatus here? */

    return TRUE;
}

The function gets called every second, and in it, I query the database using the following statement:

SELECT * FROM table1 WHERE Timestamp <> @lastSeenTimestamp AND Symbol_name <> @lastSeenSymbolName AND Status <> @lastSeenStatus;

No two records will ever be exactly the same, but they can have the same timestamp, status, or symbol name.

Note that before I enable app_models_timed_query to be called each second, I set the user-defined variables like so:

SET @lastSeenTimestamp = -1, @lastSeenSymbolName = '', @lastSeenStatus = 0;

And since timestamps will never be negative, the first time app_models_timed_query is called, the first row will be in the result of the query.

However, my question is how to set the user-defined variables to the last row of the result of my query. Also, I want to know if there is a better way of reading only newly inserted rows each time app_models_timed_query is called.

Many thanks, Vikas

Answer 1

You should be using a message queue like RabbitMQ for this sort of application. Message queues have an API associated with fetching from the top of a queue. Even if you store the main data in MySQL, you can use the message queue for the primary key. With this choice of suited infrastructure your application doesn't need to preserve state.