Change integer to text

[Richard238]

And how might you change “Amt” form an integer, to a message saying “Too High” or Too low"? I am actually measuring temperature and lux levels, but the principle’s the same.

My setup: Pi + Enviro Phat, readings to MySQLi DB on same pi. MM PI uses MMM-MysqlQuery to read that data. Rather than lux figures, I would like it to show a simple 'light' or 'dark' based on how low/high the lux figure is.

Thank you.

[ralberth]

(arguably) the best way is to code the conversion in the SQL SELECT statement. That's what the SQL language was built for: the theory is that you transform the raw DB data into whatever the application needs via SELECT language, instead of writing code after your select gives you data.

How about this:

create table lux (
   device   varchar(60),
   amt      integer
);

insert into lux (device, amt)
values ('Abcd', 12),
       ('Efgh', 25),
       ('Ijkl', 83),
       ('Mnop', null);

select device,
       case
          when amt > 70 then 'high'
          when amt > 20 then 'medium'
          when amt >  0 then 'low'
          else 'n/a'
       end as level
from lux
order by device;

+--------+--------+
| device | level  |
+--------+--------+
| Abcd   | low    |
| Efgh   | medium |
| Ijkl   | high   |
| Mnop   | n/a    |
+--------+--------+

[Richard238]

Tried this for a couple of hours but just get errors from Phpmyadmin.

Table name = readings

I cannot figure out how to do it.

module: 'MMM-MysqlQuery', position: 'bottom_right', header: 'Garage Status',
config: { connection: { host: "192.168.1.41", port: 3306, user: "#######", password: "########", database: "######" }, query: select * from readings order by ID desc limit 1, intervalSeconds: 15 * 60, emptyMessage: "No data!", columns: [ { name: "Added", title: "At", cssClass: "left", dateFormat: "time", dateLocale: "en-GB" },
{ name: "Temp", title: "Temp", cssClass: "left", suffix: "°C", nullValue: "???" }, { name: "Yaxis", title: "Door", cssClass: "left", nullValue: "???" }, { name: "Lux", title: "Lux", cssClass: "left" }, ] }

[ralberth]

Sorry you haven't heard from me yet on this. Been busy at work (yea, we all are), and are now on a week vacation. Your question is still nagging at me and I'll reproduce it and get an answer back to you!

[Richard238]

Hey, no worries. I appreciate your time and efforts. Just been off for a week myself, getting back up to speed now. Thanks!

[ralberth]

I tried a few things, and did manage to cause a failure, but it's strange. I started with a fresh MagicMirror and MM-MySqlQuery download from Github and "npm install"ed everywhere.

I'm seeing this against a Mysqld version 8.0.16:

code: 'ER_NOT_SUPPORTED_AUTH_MODE',
errno: 1251,
sqlMessage: 'Client does not support authentication protocol requested by server; consider upgrading MySQL client',
sqlState: '08004',
fatal: true

This would explain it. Can you try a node serveronly and try it out, then let me know if you see anything strange on the log? What version is your mysql server?

The strangeness is that mysqlquery module's package.json shows the version of mysql as "latest", so it's not clear how to fix this immediately.

FWIW, I created a table from your spec and ran your SELECT statement, and I'm seeing data from mysql command-line tool.

[Richard238]

serveronly: From mm-error-0.log

SyntaxError: Unexpected token < in JSON at position 0 at JSON.parse () at IncomingMessage. (/home/pi/MagicMirror/modules/MMM-MyTemps/node_helper.js:20:41) at emitNone (events.js:111:20) at IncomingMessage.emit (events.js:208:7) at endReadableNT (_stream_readable.js:1056:12) at _combinedTickCallback (internal/process/next_tick.js:138:11) at process._tickCallback (internal/process/next_tick.js:180:9)

php -v PHP 7.0.33-0+deb9u1

mysql --version mysql Ver 15.1 Distrib 10.1.37-MariaDB, for debian-linux-gnueabihf (armv8l) using readline 5.2

Hope that helps. Thank you, Richard.

[ralberth]

For background, this is the message format sent between the server component of MagicMirror (node_helper.js) and the browser-resident JavaScript portion (MMM-MysqlQuery.js). MagicMirror handles this marshalling and unmarshalling for us, and delivers the messages by calling methods like socketNotificationReceived(). I have a hunch the message isn't usable that we're receiving.

My current code for node_helper.js on line 20 is just "socketNotificationReceived: function(notification, payload) {" so I'm not sure what's happening in your clone of it. Can you include your actual code, or just push whatever changes you have to git and point me at the Git project?

As far as logic goes, here's what's happening internally: the node_helper.js code is actually the client to the browser code. MMM-MysqlQuery.js has a timer started from startTimer(). This calls the node_helper method on a schedule passing in the query it should run and the connection information. The node_helper.js file just takes whatever query and connection given to it and runs it, returning the fetched database rows. If this seems more work than needed, it's because the MagicMirror server code could live entirely on a different machine from the browser, and the server is the box that should be initiating database connections, not a browser. At my house, I have a central server that runs MagicMirrors for multiple displays, and each display is a kiosk that only knows how to connect a browser to the server.

I have a hunch the JSON sent from this.sendSocketNotification() in file MMM-MysqlQuery.js (your clone of this) is sending something other than the expected JSON, or the node_helper.js isn't receiving it correctly.

[Richard238]

Crikey, that's very technical, but equally impressive!

Here's my MMM-MysqlQuery.js

/* global Module */

/* MMM-MysqlQuery.js
 *
 * Magic Mirror
 * Module: MMM-MysqlQuery
 * MIT Licensed.
 *
 * See README.md for details on this.
 */
Module.register("MMM-MysqlQuery", {

    debuglog: function(msg) {
        //console.log("[DEBUG][MMM-MysqlQuery] " + msg);
    },

    start: function() {
        this.debuglog("Starting up");
        var me = this;
        var c = this.config;
        this.validate(c.query,               "query",               [ "notnull" ]);
        this.validate(c.intervalSeconds,     "intervalSeconds",     [ "notnull", "positive" ]);
        this.validate(c.emptyMessage,        "emptyMessage",        [ "notnull" ]);

        this.validate(c.connection,          "connection",          [ "notnull" ]);
        this.validate(c.connection.host,     "connection.host",     [ "notnull" ]);
        this.validate(c.connection.port,     "connection.port",     [ "notnull", "positive" ]);
        this.validate(c.connection.database, "connection.database", [ "notnull" ]);

        this.validate(c.columns,             "columns",             [ "notnull" ]);
        this.validate(c.columns.length,      "columns length",      [ "notnull", "positive" ]);
        c.columns.forEach(function(col, indx) {
            var realIndex = indx + 1; // so error messages show 1st as "1"
            me.validate(col.name,       "columns[" + realIndex + "].name",       [ "notnull" ]);
            me.validate(col.precision,  "columns[" + realIndex + "].precision",  [ "nonnegative" ]);
        });
    },

    validate: function(val, name, tests) {
        var me = this;
        tests.forEach(function(test) {
            switch(test) {
            case "notnull":     me.assert(val,                        name + " cannot be null");          break;
            case "notempty":    me.assert(!val || val.length,         name + " cannot be empty");         break;
            case "positive":    me.assert(!val || parseInt(val) >= 1, name + " must be 1 or greater");    break;
            case "nonnegative": me.assert(!val || parseInt(val) >= 0, name + " must be zero or greater"); break;
            }
        });
    },

    assert: function(test, msg) {
        if (!test) throw new Error(msg);
    },

    getStyles: function() {
        return [ "MMM-MysqlQuery.css" ];
    },

    getDom: function() {
        this.topDiv = this.createEle(null, "div", "mysqlQuery");
        var table = this.createEle(this.topDiv, "table");
        var thead = this.createEle(table, "thead");
        var tr = this.createEle(thead, "tr");
        var helper = this;
        this.config.columns.forEach(function(col) {
            helper.createEle(tr, "th", null, col.title || col.name);
        });
        this.tbody = this.createEle(table, "tbody");

        return this.topDiv;
    },

    createEle: function(parentEle, eleType, name, innerHtml) {
        var div = document.createElement(eleType);
        if (name) {
            div.className = name;
        }
        if (innerHtml) {
            div.innerHTML = innerHtml;
        }
        if (parentEle) {
            parentEle.appendChild(div);
        }
        return div;
    },

    notificationReceived: function(notification, payload, sender) {
        switch(notification) {
        case "DOM_OBJECTS_CREATED":
            this.debuglog("Received notification " + notification + ", payload=" + payload + ", from " + sender);
            this.triggerHelper();
            this.startTimer();
            break;
        }
    },

    triggerHelper: function() {
        this.debuglog("Sending MYSQLQUERY id=" + this.identifier + ", query=" + this.config.query);
        this.sendSocketNotification("MYSQLQUERY", {
            identifier: this.identifier,
            connection: this.config.connection,
            query:      this.config.query
        });
    },

    startTimer: function() {
        var self = this;
        if (! this.timer) {
            this.debuglog("Start timer");
            this.timer = setInterval(
                function() { self.triggerHelper(); },
                self.config.intervalSeconds * 1000
            );
        }
    },

    socketNotificationReceived: function(notification, payload) {
        if (payload.identifier === this.identifier) {
            switch(notification) {
            case "MYSQLQUERY_RESULT":
                this.replaceTableRows(this.tbody, payload.rows);
                break;
            }
        }
    },

    replaceTableRows: function(parent, rowsToAdd) {
        this.debuglog("Replacing table with new server results:");
        var helper = this;
        while (parent.firstChild) parent.removeChild(parent.firstChild);
        if (rowsToAdd && rowsToAdd.length) {
            rowsToAdd.forEach(function(dbRow) {
                helper.debuglog("   Adding row to table: " + JSON.stringify(dbRow, null, 2));
                var tr = helper.createEle(parent, "tr");
                helper.config.columns.forEach(function(colDef) {
                    var rawVal = dbRow[colDef.name];
                    var displayVal = helper.formatCell(rawVal, colDef);
                    helper.debuglog("      Col " + colDef.name + ": raw value=\"" + rawVal +
                                    "\", display value=\"" + displayVal + "\"");
                    var td = helper.createEle(tr, "td", colDef.cssClass);
                    if (colDef.displayType == "html") {
                        td.innerHTML = displayVal;
                    } else {
                        td.innerText = displayVal;
                    }
                });
            });
        } else {
            this.debuglog("   No rows returned");
            if (helper.config.emptyMessage) {
                var tr = helper.createEle(parent, "tr");
                var td = helper.createEle(tr, "td");
                td.colSpan = helper.config.columns.length;
                td.innerHTML = helper.config.emptyMessage;
            }
        }
    },

    formatCell: function(value, cellConf) {
        if (value) {
            if (cellConf.precision) {
                value = value.toFixed(cellConf.precision);
            }

            if (cellConf.thousandsSeparator) {
                value = this.addSeparators(value, cellConf.thousandsSeparator);
            }

            switch (cellConf.dateFormat) {
            case "date":
                value = new Date(value).toLocaleDateString(cellConf.dateLocale);
                break;
            case "time":
                value = new Date(value).toLocaleTimeString(cellConf.dateLocale);
                break;
            case "datetime":
                value = new Date(value).toLocaleString(cellConf.dateLocale);
                break;
            }

            if (cellConf.prefix) {
                value = cellConf.prefix + value;
            }

            if (cellConf.suffix) {
                value = value + cellConf.suffix;
            }
        } else if (cellConf.nullValue) {
            value = cellConf.nullValue;
        }

        return value;
    },

    // http://www.mredkj.com/javascript/numberFormat.html
    addSeparators: function(nStr, sep) {
        nStr += '';
        x = nStr.split('.');
        x1 = x[0];
        x2 = x.length > 1 ? '.' + x[1] : '';
        var rgx = /(\d+)(\d{3})/;
        while (rgx.test(x1)) {
            x1 = x1.replace(rgx, '$1' + sep + '$2');
        }
        return x1 + x2;
    },

    suspend: function() {
        if (!!this.timer) {
            this.debuglog("Suspending");
            clearInterval(this.timer);
            this.timer = null;
        }
    },

    resume: function() {
        this.triggerHelper();
        this.startTimer();
        this.debuglog("Resuming");
    }
});

Attached is a screen-shot from my Magic Mirror

It works, but I would like the 'Door' and 'Lux' values displayed as text rather than meaningless (at least to anyone else in the house) numbers.

Thank you.

[ralberth]

Wow, sorry, I thought it wasn't displaying anything.

Getting numbers to display as text is simpler!

How about this in your config/config.js file:

query: "select Added, \
               Temp, \
               case
                  when Yaxis = 11589 then 'front'
                  when Yaxis = 12345 then 'back'
                  else 'N/A'
               end as Yaxis,
               case
                  when Lux > 8 then 'bright'
                  when Lux > 4 then 'normal'
                  when Lux > 0 then 'low'
                  else 'off'
               end as Lux \
        from readings \
        order by ID \
        desc limit 1",

Running this directly in mysql looks like this:

+---------------------+------+-------+--------+
| Added               | Temp | Door  | Lux    |
+---------------------+------+-------+--------+
| 2019-07-29 23:09:31 | 99.5 | front | bright |
+---------------------+------+-------+--------+

[Richard238]

No worries, I though the solution was wandering off a bit!

I'll have a go with the 'case' suggestion over the weekend. Thank you. Richard.

[Richard238]

Just tried this in Phpmyadmin:

select Added, Yaxis, Temp, Lux,
case
when Yaxis = 11589 then 'front' 
when Yaxis = 12345 then 'back' 
else 'N/A'
end as 'Yaxis'

Static analysis:

2 errors were found during analysis.

    Unrecognized keyword. (near "as" at position 122)
    Unexpected token. (near "'Yaxis'" at position 125)

SQL query: Documentation

select Added, Yaxis, Temp, Lux, case when Yaxis = 11589 then 'front' when Yaxis = 12345 then 'back' else 'N/A' end as 'Yaxis'

MySQL said: Documentation
#1054 - Unknown column 'Added' in 'field list'

Have tried various types of quotes etc in the end as, but always that same #1054 error.

[ralberth]

Wow, we're really up against something strange!

mysql> select Added, Yaxis, Temp, Lux, case when Yaxis = 11589 then 'front'  when Yaxis = 12345 then 'back'  else 'N/A' end as 'Yaxis' from readings;
+---------------------+-------+------+-----+-------+
| Added               | Yaxis | Temp | Lux | Yaxis |
+---------------------+-------+------+-----+-------+
| 2019-07-29 23:09:31 |   108 | 99.5 |  55 | N/A   |
+---------------------+-------+------+-----+-------+
1 row in set (0.00 sec)

I'm literally pasting what you did and getting a good result. Ugh! Sorry about this.

The Unknown column 'Added' in 'field list' is because you need the rest of the statement that picks the table name:

select Added, Yaxis, Temp, Lux, case when Yaxis = 11589 then 'front' when Yaxis = 12345 then 'back' else 'N/A' end as 'Yaxis' from readings

Let's try another attack. I'm wondering if phpmyadmin is getting in the way somehow, or affecting the results.

There is a helper node script shipped as part of MM-MysqlQuery for this type of thing. Try this with appropriate substitutions:

% cd modules/MM-MysqlQuery
% node ./cmdline_test.js 192.168.1.41 3306 joeuser mypasswd mydatabs \
   "select Added, Yaxis, Temp, Lux, case when Yaxis = 11589 then 'front' when Yaxis = 12345 then 'back' else 'N/A' end as 'Yaxis' from readings"

[
  RowDataPacket {
    Added: 2019-07-30T03:09:31.000Z,
    Yaxis: 'N/A',
    Temp: 99.5,
    Lux: 55
  }
]
^C

This uses the native driver inside MMM-MysqlQuery to do the connection. If this works, it points to something about how phpmyadmin works. If it fails for the same reason, then we're dealing with an incompatibility between the mysql driver inside MMM-MysqlQuery and the MariaDB you're running.

[Richard238]

Hi,

When I paste this:

query: "select Added, \
               Temp, \
               case
                  when Yaxis = 11589 then 'front'
                  when Yaxis = 12345 then 'back'
                  else 'N/A'
               end as Yaxis,
               case
                  when Lux > 8 then 'bright'
                  when Lux > 4 then 'normal'
                  when Lux > 0 then 'low'
                  else 'off'
               end as Lux \
        from readings \
        order by ID \
        desc limit 1",

I get an unclosed string error (tested on JSLint, if that's a reliable tool?)

Geany on Pi highlights a couple of lines - screen-shot attached.

Tried changing " to ', tabs to spaces etc, but still it fails.

[ralberth]

That's on me...I think my editor stripped out the needed backslashes. In Javascript, you can have a string on more than one line if you use "\" at the end. There are a bunch of "\" missing above.

Here's the corrected snippet with a "\" on the end of each line.

Sorry!

query: "select Added, \
               Temp, \
               case \
                  when Yaxis = 11589 then 'front' \
                  when Yaxis = 12345 then 'back' \
                  else 'N/A' \
               end as Yaxis, \
               case \
                  when Lux > 8 then 'bright' \
                  when Lux > 4 then 'normal' \
                  when Lux > 0 then 'low' \
                  else 'off' \
               end as Lux \
        from readings \
        order by ID \
        desc limit 1",

[Richard238]

No worries, we got there in the end!

        {
            module: 'MMM-MysqlQuery',
            position: 'bottom_right',
            header: 'Garage Status',            
            config: {
                connection: {
                    host: "##########",
                    port: ####,
                    user: "####",
                    password: "########",
                    database: "########"
                },
/*              query: `select *
                        from readings
                        order by ID desc
                        limit 1`,
*/
query: "select Added, \
               Temp, \
               case \
                  when Yaxis <= 6000 then 'OPEN!' \
                  when Yaxis >= 10000 then 'Closed' \
                  else 'Go Check!' \
               end as Yaxis, \
               case \
                  when Lux >= 1000 then 'Bright' \
                  when Lux >= 4 then 'Normal' \
                  when Lux <= 10 then 'Off' \
                  else 'Go Check!' \
               end as Lux \
        from readings \
        order by ID \
        desc limit 1",

                intervalSeconds: 15 * 60,
                emptyMessage: "No data!",
                columns: [
                    { name: "Added",     title: "At", cssClass: "left", dateFormat: "time", dateLocale: "en-GB" },                     
                    { name: "Temp",     title: "Temp", cssClass: "left", suffix: "°C", nullValue: "???" },
                    { name: "Yaxis",     title: "Door", cssClass: "left", nullValue: "???" },
                    { name: "Lux",     title: "Light", cssClass: "left" },
               ]
            }
        },

Interesting to learn how to put mysql queries into javascript. And, that phpmyadmin isn't a good test bench, not for case queries anyway. My next challenge, is to get CSS to change the colour of the door status, i.e., red text if I've left the door open.

Thanks for your help, very much appreciated.

[ralberth]

Woo hoo! Glad to see this finally work, and thanks for sticking with me through this big ticket. I'm glad for the help you provided along the way.

As for CSS styling, that's going to be interesting...however, there is another feature you will like instead: "displayType: 'html'". When you turn this on, the string from the database is inserted into the HTML page without any escaping. Literally every byte is taken on face value. This means you can embed HTML into the database response to handle coloring. The right way to do this is obviously with css styling and adding class names to the <td> elements, but we're not building something enterprise-y here :-)

query: "select Added, \
               Temp, \
               case \
                  when Yaxis <= 6000 then 'OPEN!' \
                  when Yaxis >= 10000 then 'Closed' \
                  else 'Go Check!' \
               end as Yaxis, \
               case \
                  when Lux >= 1000 then '<font color="red">Bright</font>' \
                  when Lux >= 4 then '<font color="green">Normal</font>' \
                  when Lux <= 10 then 'Off' \
                  else '<blink>Go Check!</blink>' \
               end as Lux \
        from readings \
        order by ID \
        desc limit 1",

You'll notice the playful <blink> tag above too. Warning, this is terrible HTML practice, the element is deprecated and likely not supported (eventually), and you'll get laughed at by any programmers who write HTML for a living. However, it's funny and dumb but will catch your eye without me adding features to MMM-MysqlQuery and you waiting months :-). Try it out and don't blame me!

Change columns in the config.js file:

columns: [
   { name: "Added",     title: "At", cssClass: "left", dateFormat: "time", dateLocale: "en-GB" },                     
   { name: "Temp",     title: "Temp", cssClass: "left", suffix: "°C", nullValue: "???" },
   { name: "Yaxis",     title: "Door", cssClass: "left", nullValue: "???" },
   { name: "Lux",     title: "Light", cssClass: "left", displayType: "html" },
]

[Richard238]

I've just glanced at MM, and noticed the module isn't updating dynamically, or perhaps not often enough? Well, it is, the figures are good, but they don't change to text unless I hit F5. The database is updated every two minutes. I guess then that the module is using intervalSeconds: for its update frequency? But it is, it's just not converting it to text without an F5 refresh.

Haha - HTML blink! I've not seen that used in aeons. File alongside marquee scrolling, maybe?! Yes, I do like chuckle when 'real'(?) programmers frown on stack overflow, they forget we're not all pro and some of us are just tinkering for a hobby.

[Richard238]

Quick question, how does intervalSeconds: work? Running it at 3 * 3 seems to be OK, but is probably overkill.

[ralberth]

Go for it! If that gets you what you need. MySQL is great at caching tables and rows that are requested often, so a higher rate of query won't even bother to hit the disk of the server. Network packets are cheap. Are you seeing good results with intervalSeconds at 9s?

[Richard238]

Results seem good so far, thank you, yes. How does the maths work, 15 * 60 - is that really 900 seconds? For 9 seconds, what difference would 1x9, or 9x1 make, for example?

[ralberth]

The math happens by JavaScript (Node.JS actually) before MagicMirror or MMM-MysqlQuery ever get it. All my code sees is the result of the math.

That's a general convention to make it simpler to maintain. "Seconds" is tough to reason about or recognize when you see things like "900". It's a lot easier to see "15 60" and reason "that's 15 minutes, the '60' is the conversion to seconds."