SQLite extender replacing advanced array functions - real world example

[cssyphus]

This post is a follow-up to the post SQLite extender to replace/augment array functions

Here is my real-world example.

The use case is part of a script that automates logging into multiple switches and APs (approx 65 switches and 1500 APs), and finding the control IP used by the Auvik Collector (among many other tasks). Each device will have at least one IP, but each should have the control IP as one of the IPs. There may be other IPs that are repeated. Some devices do not have a control IP.

This was a challenge using arrays - *IF ONLY* we could schlep the info into a MySQL table and use its powerful functions... (And to have that SQL table only exist in memory... a masterstroke)

This demo is merely an expansion of the previous example posted above by Jim, using helpful commentary and tips provided by Stan and Tony. Of course, the demo requires the free WB SQLite extender.

This demo proves how easy it is to use in-memory SQLite tables instead of arrays to perform advanced array-like functions, like: sorting on multiple columns, identifying the most-repeated value, searching for partial cell data, etc.

Voila:

Code Select Expand

addExtender(`ilcsl44i.dll`)
goSub UDFs

_arrIn = udfCreateDummySampleDataArray()
_ctrlIP = udfGetControlIP(_arrIn)
message(`Control IP:`, _ctrlIP)
EXIT

:UDFs ;==================================================================================================
    #DEFINEFUNCTION udfGetControlIP(_arrIn) ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        _ctrlIP = ``
        _tmpDb = slConnect(':memory:')
        _SQL = 'CREATE TABLE "ips" ("id"    INTEGER NOT NULL, "ip"    TEXT NOT NULL, PRIMARY KEY("id" AUTOINCREMENT) );'
        slExecute(_tmpDb, _SQL)

        FOR _n = 0 to arrInfo(_arrIn, 1)-1
            _ln = _arrIn[_n]
            _arrLine = arrayize(_ln, `|`)
            _ipField = _arrLine[4]
            for _z = 1 to itemCount(_ipField, `,`)
                _ip = itemExtract(_z, _ipField, `,`)
                _SQL = "INSERT INTO `ips` (ip) VALUES ('%_ip%');"
                slExecute(_tmpDb, _SQL)
            next
        NEXT

        _SQL = "SELECT ip, COUNT(ip) AS `num_vals` FROM `ips` GROUP BY `ip` ORDER BY `num_vals` DESC" ; LIMIT 1"
        _result = slExecute(_tmpDb, _SQL)
        slClose(_tmpDb)
        _ctrlIP = _result[0,0]
        return _ctrlIP
    #ENDFUNCTION ;udfGetControlIP ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    #DEFINEFUNCTION udfCreateDummySampleDataArray() ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        debugTrace(22)
        _input  = `dummy|dummy|dummy|dummy|192.168.12.7,172.16.145.21|dummy` : @TAB
        _input := `dummy|dummy|dummy|dummy|192.168.12.7,172.16.145.22|dummy` : @TAB
        _input := `dummy|dummy|dummy|dummy|192.168.12.7,172.16.145.25|dummy` : @TAB
        _input := `dummy|dummy|dummy|dummy|192.168.12.7,172.16.145.26|dummy` : @TAB
        _input := `dummy|dummy|dummy|dummy|192.168.12.7,172.16.145.23|dummy` : @TAB
        _input := `dummy|dummy|dummy|dummy|192.168.12.7,172.16.145.27,10.0.1.131|dummy` : @TAB
        _input := `dummy|dummy|dummy|dummy|192.168.12.7,172.16.145.31|dummy` : @TAB
        _input := `dummy|dummy|dummy|dummy|192.168.12.7,172.16.145.32|dummy` : @TAB
        _input := `dummy|dummy|dummy|dummy|192.168.12.7,172.16.145.37|dummy` : @TAB
        _input := `dummy|dummy|dummy|dummy|20.231.239.246|dummy` : @TAB
        _input := `dummy|dummy|dummy|dummy|192.168.12.7,172.16.145.34|dummy` : @TAB
        _input := `dummy|dummy|dummy|dummy|172.16.145.39,192.168.12.7,10.0.1.131|dummy` : @TAB
        _input := `dummy|dummy|dummy|dummy|192.168.12.7,172.16.145.42|dummy` : @TAB
        _input := `dummy|dummy|dummy|dummy|192.168.12.7,172.16.145.33|dummy` : @TAB
        _input := `dummy|dummy|dummy|dummy|192.168.12.7,172.16.145.24|dummy` : @TAB
        _input := `dummy|dummy|dummy|dummy|52.94.236.248|dummy` : @TAB
        _input := `dummy|dummy|dummy|dummy|172.16.145.14,192.168.12.7|dummy` : @TAB
        _input := `dummy|dummy|dummy|dummy|192.168.12.7,172.16.145.41|dummy` : @TAB
        _input := `dummy|dummy|dummy|dummy|192.168.12.7,172.16.145.06|dummy` : @TAB
        _input := `dummy|dummy|dummy|dummy|10.0.1.131,192.168.12.7,172.16.145.12|dummy` : @TAB
        _input := `dummy|dummy|dummy|dummy|192.168.12.7,172.16.145.17|dummy` : @TAB
        _input := `dummy|dummy|dummy|dummy|192.168.12.7,172.16.145.08|dummy`
        _arr = arrayize(_input, @TAB)
        return _arr
    #ENDFUNCTION ;udfCreateDummySampleDataArray ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Return ;UDFs ============================================================================================



[spl]

Nice. But if you don't mind the Devil's advocate

• could the insert UDF be written as a transaction
• if soucrce data already seems comma-delimited why not use Arrayfromcsv() and the array functions instead of loading an extender
• Could the UDF's be parametized to accept a source and interpret column names rather than hard-coding
• Finally, how would you persist the SQLite, if required, and recall it.

and ignore if not relevant

[td]

The SQLite extender has a function that saves (persists in MSFT speak) a memory only DB to disk.

[cssyphus]

Appreciate the input Stan.

• Unsure what you mean by "written as a transaction"
• In the actual app, I am using ArrayFromCsv() to load the Auvik output CSV, but in this example I am hard-coding some sample data that somewhat resembles the actual format (going for a (very slightly) "more realistic" example...) However, the extender is necessary b/c it is the SQLite extender
• Parametizing the UDF is a great idea, I didn't think of that. Good idea for future use
• I am a noob with both SQLite and the SQLite Extender so I don't know if the answer is as simple as just not closing the DB...? However, if I wanted to use SQLite again, for example to search for partial data in a different array element, I would probably just create a new/different table with the structure needed for that use case and proceed similarly to this example.

Did you have examples or sample code in mind for any of the above?

THANKS for the feedback / brainstorming -- iron sharpens iron.

[JTaylor]

If you are dealing with some large amounts of data and this process takes a long time you might consider some of the following.  If it is something that runs unattended and time really isn't an issue then ignore this.

This first part may or may not be better than the loop but a good chance it is. Load to Array from the file and use ArrayRemove() to leave only the IP Column. Save to file.  Do a FileGet() and then replace commas with @CRLF.   Then do a replace on @CRLF that generates the SQL for the insert.  Could do all in one go. See BEGIN TRANSACTION and COMMIT.

This next part will definitely improve performance.

During your insert Loop, save up the inserts and do 500 or so at a time.  May need to experiment to find the sweet spot.  Might look at BEGIN TRANSACTION and COMMIT.  Individual inserts are expensive.



Jim

[spl]

The SQLite extender has a function that saves (persists in MSFT speak) a memory only DB to disk.

Excellent. I have read about using ATTACH to persist an in-memory to another db, but seemed a bit complicated.

[td]

The SQLite extender's slStmExecMany function loads the contents of a rank 2 array into a DB all at once. The user only needs a prepared and parameterized SQL statement and an array.  An SQL statement is prepared (compiled) using the slStatement function.