Logic Machine Forum
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 Wink

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