SQL challenge yet again - Printable Version

SQL challenge yet again - Printable Version

+- Logic Machine Forum (https://forum.logicmachine.net)
+-- Forum: LogicMachine eco-system (https://forum.logicmachine.net/forumdisplay.php?fid=1)
+--- Forum: Scripting (https://forum.logicmachine.net/forumdisplay.php?fid=8)
+--- Thread: SQL challenge yet again (/showthread.php?tid=2241)

SQL challenge yet again - Trond Hoyem - 16.09.2019

Hi

I am again struggeling with the SQL lookup...

What I need is to create a table with all objects within a certain address range. I am already able to calculate the start and stop ID from the address table, but when I try to look it up with the SQL, I am not as successful.

The code I am using is;

Code:
  adresser = db:getall('SELECT ID FROM objects WHERE ID >= "%' .. startID .. '%" AND ID <= "%' .. stopID .. '%"')

  log(adresser)

The log gives an empty table.
Any hints as to what I am doing wrong?

RE: SQL challenge yet again - Daniel - 16.09.2019

Hi
I'm not sure what is your end game here but you can take an idea from this script how to grab range of objects from DB and do some action on them.
This script will create tags on object based on the range and the separators selected and the object name.

Code:
seperators = '.,_ =+'

-- Start address of objects (min: 0/0/1 = main/sub/address)

start_main = 0

start_sub = 0

start_address = 1

-- End address of objects (max: 31/7/255 = main/sub/address)

end_main = 15

end_sub = 7

end_address = 255

------------------------------------------ End Parameters ------------------------------------------

------------------------------ DON'T CHANGE ANYTHING UNDER THIS LINE -------------------------------

function split(source, delimiters)

        local elements = {}

        local pattern = '([^'..delimiters..']+)'

        string.gsub(source, pattern, function(value) elements[#elements + 1] =     value;  end);

        return elements

end

-- Calculate start address to DB format

start_objectaddress = ((start_main * 2048) + (start_sub * 256) + start_address)

-- Calculate end address to DB format

end_objectaddress = ((end_main * 2048) + (end_sub * 256) + end_address)

-- Get all objects from DB

all_objects = db:getall('SELECT address, name, tagcache FROM objects')

for _, object in ipairs(all_objects) do

  -- Check if object is inside given range

  if object.address >= start_objectaddress and object.address <= end_objectaddress then

     currentrowaddress = object.address

    myobject = grp.find(currentrowaddress) 

    nameTable = split(myobject.name, seperators)  --spliting  object string in to table

  --  log(nameTable)

    for _, tag in ipairs(nameTable) do

      tagvalue = tag

        -- Check if object tag already exists inside DB

     current_object_tag = db:getall('SELECT object, tag FROM objecttags WHERE object = ' .. currentrowaddress .. ' AND tag = "' .. tagvalue .. '"')

     object_tagcache = db:getone('SELECT tagcache FROM objects WHERE address = ' .. currentrowaddress .. '')

     if #current_object_tag == 0 then

        -- Add object to table objecttags

        db:insert('objecttags', {object = currentrowaddress, tag = tagvalue, })

          -- Check if object already has other tag values inside DB and add new values to tagcache

        if object_tagcache == nil or object_tagcache == "" then

          db:update('objects', { tagcache = tagvalue }, { address = currentrowaddress })

            else

          tag_string = "" .. object_tagcache .. ", " .. tagvalue .. ""

               log(tag_string)

          db:update('objects', { tagcache = tag_string }, { address = currentrowaddress })

          end

     end

    end

  end

end

script.disable(_SCRIPTNAME)

RE: SQL challenge yet again - admin - 16.09.2019

You can use ? placeholders which are replaced by additional arguments passed to getall(). This will properly escape values to prevent SQL injections when parameters come from external sources. There's also SQL BETWEEN operator which is more readable than two comparisons.

Code:
adresser = db:getall('SELECT id FROM objects WHERE id BETWEEN ? AND ?', startID, stopID)

RE: SQL challenge yet again - Trond Hoyem - 17.09.2019

Thanks for tips.

I have not been at the project today, so not been able to test anything, but will have a look at this.

@Daniel; what I want to do is to delete a range of objects from the SL by script. The reason is that in a project I have 30 SpaceLYnks as BACnet GW, and I only want a certain range of group addresses on each SL. But I also do not want to edit the ESF-file before importing it to the different SL's, but simply have a script that removes the surplus objects once a day or something.

RE: SQL challenge yet again - Trond Hoyem - 20.09.2019

OK, so I was able to do it with the tips I got. Thanks for help!

If anyone alse need this function, just copy this code and modify to your liking;

Code:
start = '26/0/0'

stop = '26/0/255'

------------------------------------------------------------------------------------

--------------------NO CHANGES BELOW THE LINE---------------------------------------

startHG = tonumber(string.split(start, '/')[1])

startMG = tonumber(string.split(start, '/')[2])

startUG = tonumber(string.split(start, '/')[3])

startID = (startHG * 2048) + (startMG * 256) + startUG

stopHG = tonumber(string.split(stop, '/')[1])

stopMG = tonumber(string.split(stop, '/')[2])

stopUG = tonumber(string.split(stop, '/')[3])

stopID = (stopHG * 2048) + (stopMG * 256) + stopUG

if stopID < startID then

  log('Start has higher value than stop. Delete is not possible')

else

  log('Addresses within range are deleted')

  adresser = db:getall('SELECT address, name FROM objects WHERE ID BETWEEN "' .. startID .. '" AND  "' .. stopID .. '"')

  for _, addr in ipairs(adresser) do

    log(addr.name)

    grp.delete(addr.name)

  end

end

RE: SQL challenge yet again - Daniel - 20.09.2019

You will have quite a lot of logs there Wink

RE: SQL challenge yet again - Trond Hoyem - 20.09.2019

(20.09.2019, 12:02)Daniel. Wrote: You will have quite a lot of logs there

Yep, I used it during debugging and forgot to remove it :-)

It is no longer there in my live script.

Updated version, now as function so that one can add several ranges to delete.

Now without extensive logging Smile

Code:
function deleteGA(start, stop)

    startHG = tonumber(string.split(start, '/')[1])

    startMG = tonumber(string.split(start, '/')[2])

    startUG = tonumber(string.split(start, '/')[3])

    startID = (startHG * 2048) + (startMG * 256) + startUG

    stopHG = tonumber(string.split(stop, '/')[1])

    stopMG = tonumber(string.split(stop, '/')[2])

    stopUG = tonumber(string.split(stop, '/')[3])

    stopID = (stopHG * 2048) + (stopMG * 256) + stopUG

    if stopID < startID then

      log('Start has higher value than stop. Delete is not possible')

    else

      log('Addresses within range are deleted')

      adresser = db:getall('SELECT address, name FROM objects WHERE ID BETWEEN "' .. startID .. '" AND  "' .. stopID .. '"')

      for _, addr in ipairs(adresser) do

        --log(addr.name)

        grp.delete(addr.name)

      end

    end

end

start = '26/0/0'

stop = '26/0/10'

deleteGA(start, stop)

start = '26/0/51'

stop = '26/0/70'

deleteGA(start, stop)

RE: SQL challenge yet again - Erwin van der Zwart - 20.09.2019

Hi,

Here is a simplified version (:

Code:
function deleteGA(start, stop)

  startID = knxlib.encodega(start)

  stopID = knxlib.encodega(stop)

  if stopID < startID then

    log('Start has higher value than stop. Delete is not possible')

  else

    result = db:query('DELETE FROM objects WHERE ID BETWEEN ? AND ?', startID, stopID)

    log(result .. ' address(es) within range is/are deleted')

  end

end

deleteGA('26/0/0', '26/0/10')

deleteGA('26/0/51', '26/0/70')

BR,

Erwin

RE: SQL challenge yet again - Trond Hoyem - 25.09.2019

(20.09.2019, 19:40)Erwin van der Zwart Wrote: Hi,

Here is a simplified version (:

Code:
function deleteGA(start, stop) startID = knxlib.encodega(start) stopID = knxlib.encodega(stop) if stopID < startID then log('Start has higher value than stop. Delete is not possible') else result = db:query('DELETE FROM objects WHERE ID BETWEEN ? AND ?', startID, stopID) log(result .. ' address(es) within range is/are deleted') end end deleteGA('26/0/0', '26/0/10') deleteGA('26/0/51', '26/0/70')

BR,

Erwin

OK, that is a little more elegant. Smile

Is there any place where one can find all these commands that are used for LM/SL? Like knxlib.encodega()? That would be really helpful to have a list of those, like we have already for the lua commands (https://openrb.com/docs/lua.htm).

RE: SQL challenge yet again - Erwin van der Zwart - 25.09.2019

Hi,

I don't have a list, this is a command that is used in a lot of samples on this site, that is also where i got the info, when i see a new command like knxlib i usually log it log(knxlib) to see the included sub functions.

BR,

Erwin