speed up order by int DESC, text ASC;

[jchd]

I believe you try another path: derive modified versions of _SQLite_GetTable2d and _SQLite_FetchData into a single, streamlined function to perform the json formatting right from the output of SQLite. At that moment you can take care of sorting out correctly strings vs. numbers and keywords (null, true, false).

I recognize that current version of _SQLite_FetchData is deceptive as it formats everything in strings.

[argumentum]

... I was gonna use just what you said. That idea, is what brought _SQLite_FetchTypes() to life....

1 hour ago, jchd said:

I recognize that current version of _SQLite_FetchData is deceptive as it formats everything in strings.

..but then I thought that in the end, just like you said: "unsuspecting readers to fall into the trap", and that got me to search for more ( I could get me into a trap was one of my strong thoughts ).
...also found the use of json_object(). 

All this thanks to multiple tabs in the browser while testing different code on different PCs.
( I tend to act disorganized, but is all organized in my head while evaluating possibilities )

Since the JSON straight out of the query was fast, I disregarded modifying _SQLite_GetTable2d(), as adding more evaluation ( if, then , else, switch, etc. ) to the function would have made it slower.

So: speed vs. practical. I chose speed. But I guess I could put together the _SQLite_GetTableJson() and share it in the forum. But it would always return:
[{"name1":"value1"},{"name2":"value2"}]
as I can't imagine a more flexible output. I don't know. What do you think @jchd ?

Afterthought: 

; #FUNCTION# ====================================================================================================================
; Author ........: piccaso (Fida Florian), blink314
; Modified.......: jchd
; ===============================================================================================================================
Func _SQLite_GetTable2d( ...

...would you wanna have a go at it ?, ..if time permits, you are much more qualified than I am at this moment. You may take into consideration something I miss, like: should I take an INT type and return it as [{"name1":value1},{"name2":value2}] or [{"name1":"value1"},{"name2":"value2"}] ???, etc.

If you don't want to code it but feel it should exist, I'll code it, but again, you are more experienced.

Your move, what do you say  

[jchd]

I have a lot of pans on the fire, many urgent and time-killing. I also start to experience issues have Chinese-only electronic components delivered due to COVID19.

I'll try to code something when schedule allows.

[Georgio12]

In the thread above a few people hit on the idea of synthesizing a separate column to be indexed, named like Date_and_Name-Sequence-Number. I see how this could improve the sort time on a large set of Selected records. The problem is the potential (read: likelihood) that the fake-indexed column will become wrong/out of date as compared with the real data. As in, you have introduced a new data-integrity nightmare.

That manual column now must be maintained across all rows having that Date / in that Sequence. Whenever the Date column is inserted or updated in any row having that Date, the entire set of records with that Date must be re-sequenced, or at least checked for the need to re-sequence. I can't believe there is no way to index the Date and Name columns to get the required sort performance, regardless of what database engine we're talking about.

However! I understand some bigger client-server SQL engines have this kind of feature built in. A 'virtual' column can be defined at the table level, where a synthesized column returns the result of an expression that evaluates other columns in that row. In this way, the column is kind of like a View, except it's at the Column level instead of at the Table level. This may or may not solve the slow-sort problem, but it would certainly avoid introducing a foreseeable data integrity problem.

Another exception would be the enterprise-level case where there are two sets of tables: one set optimized for normalization/efficient storage focused on integrity, and one set optimized for fast selecting and sorting for output purposes. One tradeoff is data staleness: your output is only as fresh as your periodic publish process, where the data is copied and de-normalized from the Storage database to the Reporting tables/database. From experience at this level of IT, we always had a third set of tables too, which were optimized for input: zero normalization, with intake processing to reject invalid data.
 

[jchd]

Maintaining extra indices (simple or complex) without jeopardizing data integrity is easily done with triggers. Or I might have misunderstood you.

Even SQLite offers indices on expression, partial indices, reindex table or a specific index, ...

Edited May 12, 2020 by jchd

[Georgio12]

Agree advanced indexing like you mention is first choice. For the rare cases that is not good enough, agree triggers can be a good solution. Disagree any of that is quite so easily done. If it were easy, then people would not be tempted to dream up their own (non-optimal) solutions. Cheers

[dmob]

On 3/10/2020 at 6:06 AM, argumentum said:

But I guess I could put together the _SQLite_GetTableJson()

@argumentum did you get around to developing this idea..?

[jchd]

Thanks for the reminder!  Here you are (just baked, bugs lurking):

expandcollapse popup

; returns the result of a select query as JSON string; @extended is row count
Func _SQLite_GetTableJSON($hDB, $sSQL)
    If __SQLite_hChk($hDB, 1) Then Return SetError(@error, 0, $SQLITE_MISUSE)
    Local $hQuery
    Local $r = _SQLite_Query($hDB, $sSQL, $hQuery)
    If @error Then Return SetError(2, @error, $r)
    If $r <> $SQLITE_OK Then
        __SQLite_ReportError($hDB, "_SQLite_GetTableJSON", $sSQL)
        _SQLite_QueryFinalize($hQuery)
        Return SetError(-1, 0, $r)
    EndIf
    Local $aDataNames
    $r = _SQLite_FetchNames($hQuery, $aDataNames)
    If @error Then
        $iError = @error
        _SQLite_QueryFinalize($hQuery)
        Return SetError(5, $iError, $r)
    EndIf
    $iColumns = UBound($aDataNames)
    If $iColumns <= 0 Then
        _SQLite_QueryFinalize($hQuery)
        Return SetError(-1, 0, $SQLITE_DONE)
    Else
        For $i = 0 To $iColumns - 1
            $aDataNames[$i] = _StringToJson($aDataNames[$i])
        Next
    EndIf
    Local $iRval_Step, $iError, $iRval_coltype, $sResult = '{"result":[', $Rval, $iRows
    While True
        $iRval_Step = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_step", "ptr", $hQuery)
        If @error Then
            $iError = @error
            _SQLite_QueryFinalize($hQuery)
            $sResult = ""
            Return SetError(3, $iError, $SQLITE_MISUSE) ; DllCall error
        EndIf
        Switch $iRval_Step[0]
            Case $SQLITE_ROW
                $iRows += 1
                $sResult &= "{"
                For $i = 0 To $iColumns - 1
                    $iRval_coltype = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_column_type", "ptr", $hQuery, "int", $i)
                    If @error Then Return SetError(4, @error, $SQLITE_MISUSE) ; DllCall error
                    $sResult &= $aDataNames[$i] & ':'
                    Switch $iRval_coltype[0]
                        Case $SQLITE_TYPE_INTEGER
                            $Rval = DllCall($__g_hDll_SQLite, "int64:cdecl", "sqlite3_column_int64", "ptr", $hQuery, "int", $i)
                            If @error Then Return SetError(3, @error, $SQLITE_MISUSE) ; DllCall error
                            $sResult &= $Rval[0]
                        Case $SQLITE_TYPE_TEXT
                            $Rval = DllCall($__g_hDll_SQLite, "wstr:cdecl", "sqlite3_column_text16", "ptr", $hQuery, "int", $i)
                            If @error Then Return SetError(3, @error, $SQLITE_MISUSE) ; DllCall error
                            $sResult &= _StringToJson($Rval[0])
                        Case $SQLITE_TYPE_FLOAT
                            $Rval = DllCall($__g_hDll_SQLite, "double:cdecl", "sqlite3_column_double", "ptr", $hQuery, "int", $i)
                            If @error Then Return SetError(3, @error, $SQLITE_MISUSE) ; DllCall error
                            $sResult &= $Rval[0]
                        Case $SQLITE_TYPE_NULL
                            $sResult &= "null"
                        Case Else       ; Blob or unknown datatype at date of writing: output hex as "0x0123456789ABCDEF..."
                            Local $vResult = DllCall($__g_hDll_SQLite, "ptr:cdecl", "sqlite3_column_blob", "ptr", $hQuery, "int", $i)
                            If @error Then Return SetError(6, @error, $SQLITE_MISUSE) ; DllCall error
                            Local $iColBytes = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_column_bytes", "ptr", $hQuery, "int", $i)
                            If @error Then Return SetError(5, @error, $SQLITE_MISUSE) ; DllCall error
                            Local $tResultStruct = DllStructCreate("byte[" & $iColBytes[0] & "]", $vResult[0])
                            $sResult &= '"' & Binary(DllStructGetData($tResultStruct, 1)) & '"'
                    EndSwitch
                    If $i < $iColumns - 1 Then $sResult &= ","
                Next
                $sResult &= "},"
            Case $SQLITE_DONE
                If $iRows Then $sResult = StringTrimRight($sResult, 1)
                $sResult &= "]}"
                ExitLoop
            Case Else
                _SQLite_QueryFinalize($hQuery)
                Return SetError(3, $iError, $iRval_Step[0])
        EndSwitch
    WEnd
    _SQLite_QueryFinalize($hQuery)
    Return SetError(0, $iRows, $sResult)
EndFunc   ;==>_SQLite_GetTableJSON


; escape characters in strings w.r.t all relevant RFCs. Check https://jsonformatter.curiousconcept.com/
Func _StringToJson($s)
    $s = StringRegExpReplace($s, '([\\/])', '\\$1') ; escape \ and / first
    $s = StringReplace($s, '"', '\""')              ; escape "
    $s = StringReplace($s, Chr(0x08), '\b')         ; escape backspace
    $s = StringReplace($s, Chr(0x0C), '\f')         ; escape formfeed
    $s = StringReplace($s, @TAB, '\t')              ; escape @TAB
    $s = StringReplace($s, @LF, '\n')               ; escape @LF
    $s = StringReplace($s, @CR, '\r')               ; escape @CR
    $s = Execute('"' & StringRegExpReplace($s, '([\x00-\x1F\x7F-\x9F])', '" & "\\u" & Hex(AscW("$1"), 4) & "') & '"')   ; escape other ctrl chars
    Return '"' & $s & '"'
EndFunc   ;==>_StringToJson

Example use (cw is an advanced consolewrite for UTF8 and more):

expandcollapse popup

#include <SQLite.au3>

Const $SQLITE_DLL = "C:\SQLite\bin\sqlite3.dll" ;<-- Change to the location of your sqlite dll
;~ Const $SQLITE_DLL = "C:\SQLite\bin\system.data.sqlite.dll" ;<-- Change to the location of your sqlite dll

; Init sqlite
_SQLite_Startup($SQLITE_DLL, False, 1)
If @error Then Exit MsgBox($MB_ICONERROR, "SQLite Error", "Unable to start SQLite. Check existence of DLL")
ConsoleWrite("SQlite version " & _SQLite_LibVersion() & @LF & @LF)

Local $hDB = _SQLite_Open()
Local $sOut

_SQLite_Exec($hDB, "create table T (Id integer primary key, FirstName text, LastName text, Birth text, Death text)")
_SQLite_Exec($hDB, "insert into T values (10, 'Bohr', 'Niels', '1885/10/07', '1962/11/18')")
_SQLite_Exec($hDB, "insert into T values (11, 'Fermi', 'Enrico', '1901/09/29', '1954/11/28')")
_SQLite_Exec($hDB, "insert into T values (15, 'Albert', 'Einstein', '1879/03/14', '1955/04/18')")
_SQLite_Exec($hDB, "insert into T values (19, 'Rovelli', 'Carlo', '1956/05/03', null)")
_SQLite_Exec($hDB, "insert into T values (7, 'Schrödinger', 'Erwin', '1887/08/12', '1961/01/04')")
_SQLite_Exec($hDB, "insert into T values (39, 'Higgs', 'Peter', '1929/05/29', null)")


$sOut = _SQLite_GetTableJSON($hDB, "SELECT * from T")
cw(@extended & " row" & (@extended ? "s" : ""))
cw($sOut & @LF)

$sOut = _SQLite_GetTableJSON($hDB, "SELECT +555555555555555 col_int, null col_null, '' col_str0, 3.1415926 col_float, -0.00031415926e117 col_floatexp, cast('xyz' as blob) col_blob, 'Árvíztűrőñ tükörfúrógépçô ŵƂǚȨǽϋϔӪӢӂ' col_str")
cw(@extended & " row" & (@extended ? "s" : ""))
cw($sOut & @LF)

$sOut = _SQLite_GetTableJSON($hDB, "SELECT 'abc\" & Chr(0x0B) & "def'")
cw(@extended & " row" & (@extended ? "s" : ""))
cw($sOut & @LF)

$sOut = _SQLite_GetTableJSON($hDB, "SELECT null, 0, 'aa""bb' [abc" & @TAB & "def]")
cw(@extended & " row" & (@extended ? "s" : ""))
cw($sOut & @LF)

$sOut = _SQLite_GetTableJSON($hDB, "SELECT * from T where id = 0")
cw(@extended & " row" & (@extended ? "s" : ""))
cw($sOut)

_SQLite_Close($hDB)
_SQLite_Shutdown()

Giving:

SQlite version 3.32.3

6 rows
{"result":[{"Id":7,"FirstName":"Schrödinger","LastName":"Erwin","Birth":"1887\/08\/12","Death":"1961\/01\/04"},{"Id":10,"FirstName":"Bohr","LastName":"Niels","Birth":"1885\/10\/07","Death":"1962\/11\/18"},{"Id":11,"FirstName":"Fermi","LastName":"Enrico","Birth":"1901\/09\/29","Death":"1954\/11\/28"},{"Id":15,"FirstName":"Albert","LastName":"Einstein","Birth":"1879\/03\/14","Death":"1955\/04\/18"},{"Id":19,"FirstName":"Rovelli","LastName":"Carlo","Birth":"1956\/05\/03","Death":null},{"Id":39,"FirstName":"Higgs","LastName":"Peter","Birth":"1929\/05\/29","Death":null}]}

1 rows
{"result":[{"col_int":555555555555555,"col_null":null,"col_str0":"","col_float":3.1415926,"col_floatexp":-3.1415926e+113,"col_blob":"0x78797A","col_str":"Árvíztűrőñ tükörfúrógépçô ŵƂǚȨǽϋϔӪӢӂ"}]}

1 rows
{"result":[{"'abc\\\u000Bdef'":"abc\\\u000Bdef"}]}

1 rows
{"result":[{"null":null,"0":0,"abc\tdef":"aa\"bb"}]}

0 row
{"result":[]}

JSON doesn't support binary values; if fetched from a table, they are output as strings, e.g. "0x0123456789ABCDEF..."
OTOH, SQLite has no boolean datatype, so json true and false are never used.

Edited July 16, 2020 by jchd

[dmob]

Woow! That was quick.

Thank you for creating / sharing this function; now to dig in & learn...

[jchd]

It's directly pruned from _SQLiteGetTable2d without the useless bells & whistles. I just forgot to handle an empty resultset properly, or at least didn't test that case even if I was thinking it was dealt with. So I have fixed both code, example and its result.

Barring gross mistake that should now work in all cases.

[jchd]

Edit: the cloudflare thing for this forum went toast before I could finish updating my code yesterday.  Sorry again for posting unpolished code.

It should now be complete and json-compliant.  I refer to https://www.json.org/json-en.html and https://jsonformatter.curiousconcept.com/

Note that this version is more respectful of DB datatypes than is _SQLiteGetTable and friends.

Last point: test it extensively and report. It could be a useful addon to the SQLite UDF.

Edited July 16, 2020 by jchd

[jchd]

Here's  complementary code to unescape JSON strings:

expandcollapse popup

; unescape escape sequences in JSON strings
Func _JsonTextToString($s)
    $s = StringTrimLeft(StringTrimRight($s, 1), 1)
    $s = StringReplace($s, '\"', '"')               ; unescape "
    $s = StringReplace($s, '\b', Chr(0x08))         ; unescape backspace
    $s = StringReplace($s, '\f', Chr(0x0C))         ; unescape formfeed
    $s = StringReplace($s, '\t', @TAB)              ; unescape @TAB
    $s = StringReplace($s, '\n', @LF)               ; unescape @LF
    $s = StringReplace($s, '\r', @CR)               ; unescape @CR
    $s = Execute("'" & StringRegExpReplace($s, "\\u([[:xdigit:]]{4})", "' & ChrW(0x$1) & '") & "'") ; unescape other chars in hex form \uXXXX
    $s = StringRegExpReplace($s, '\\([\\/])', '$1') ; unescape \ and / last
    Return $s
EndFunc   ;==>_JsonTextToString


; escape characters in strings w.r.t all relevant RFCs. Check https://jsonformatter.curiousconcept.com/
Func _StringToJsonText($s)
    $s = StringRegExpReplace($s, '([\\/])', '\\$1') ; escape \ and / first
    $s = StringReplace($s, '"', '\""')              ; escape "
    $s = StringReplace($s, Chr(0x08), '\b')         ; escape backspace
    $s = StringReplace($s, Chr(0x0C), '\f')         ; escape formfeed
    $s = StringReplace($s, @TAB, '\t')              ; escape @TAB
    $s = StringReplace($s, @LF, '\n')               ; escape @LF
    $s = StringReplace($s, @CR, '\r')               ; escape @CR
    $s = Execute('"' & StringRegExpReplace($s, '([\x00-\x1F\x7F-\x9F])', '" & "\\u" & Hex(AscW("$1"), 4) & "') & '"')   ; escape other ctrl chars
    Return '"' & $s & '"'
EndFunc   ;==>_StringToJson

; unescape escape sequences in JSON strings
Func _JsonTextToString($s)
    $s = StringTrimLeft(StringTrimRight($s, 1), 1)
    $s = StringReplace($s, '\"', '"')               ; unescape "
    $s = StringReplace($s, '\b', Chr(0x08))         ; unescape backspace
    $s = StringReplace($s, '\f', Chr(0x0C))         ; unescape formfeed
    $s = StringReplace($s, '\t', @TAB)              ; unescape @TAB
    $s = StringReplace($s, '\n', @LF)               ; unescape @LF
    $s = StringReplace($s, '\r', @CR)               ; unescape @CR
    $s = Execute("'" & StringRegExpReplace($s, "\\u([[:xdigit:]]{4})", "' & ChrW(0x$1) & '") & "'") ; unescape other chars in hex form \uXXXX
    $s = StringRegExpReplace($s, '\\([\\/])', '$1') ; unescape \ and / last
    Return $s
EndFunc   ;==>_JsonTextToString

Escaping function named changed for symetry. Control chars appear in console if using a "good" font (e.g. DejaVu sans mono) in UTF8 mode.

Please test and report. Might end up in less confidential thread.

[Skysnake]

@jchd impressive as always

Will these SQLiteJSON functions make it into the standard UDF? Should we keep these separate?

You really should make a SQLite sample code page and put all these there for ease of reference.

Thanks again

Skysnake

[jchd]

OK, then please vote for the "96h/day" bill next month, applicable to France.

Yes I think it'll be a valuable addon to the existing UDF.  Also it would be useful to plan a revised and faster GetData2D function which would deal with actual datatypes (NULLs), but without the extra features of the current functions (size limit, pivoting), which I doubt many people use.

I'd also wish I can update unifuzz to current Unicode and add/fix some functions there.

All of this needs time, sorry.

[Skysnake]

2 hours ago, jchd said:

OK, then please vote for the "96h/day" bill next month, applicable to France.

I would, except it is not enough