I have some code which I have to write again in order to be production ready. The code is messy and I have a few lines that indicate sql queries, but I can not resolve the exact statements.
qs = 'DEVICENAME1|DEVICENAME2|DEVICENAME3'
qstring='SELECT "lat", "lon", "device", "location" FROM "locations" WHERE ("device" =~ /^('+qs+')/) AND time > now()-60h'
qstring='SELECT "device", "lat" FROM "locations" WHERE ("location"=~ /^'+loc+'$/) AND time > now()-60h'
I would greatly appreciate if someone could resolve the sql query statements for me. To be clear I am interested in these parts:
("device" =~ /^('+qs+')/)
("location"=~ /^'+loc+'$/)
Conceptually, "device" =~ /^('+qs+')/ means: check if the device that starts with qs (^ represents the beginning of the string), and "location"=~ /^'+loc+'$/ checks that the location is equal to loc ($ is the end of string).
So, in almost any database, these conditions could be rewritten without the use of regexes:
"device" like ' + qs+ '% -- influxdb does not support like
"location" = ' + loc + '
Side note: you should be using prepared statements and parameterized queries everywhere, for you security and efficiency. Since you are rewriting legacy code, this is a good spot to do so now.