SQLite Client Server Source

[Richard Kelly]

We're moving into the nitty gritty. To keep the uploads centralized, I'll keep the attachment here updated with the latest. It includes all the class includes as well as copies of test scripts.

I ran into http://www.freebasic.net/forum/viewtopic.php?f=8&t=25251&p=227089&hilit=zstr+function#p227089 and now I revamped the includes so that you always work with them using STRING and each class grabs a ZSTRING pointer when one is needed.

All the includes are now named with the *.bi suffix. On my system they are in the standard FB include folder under subfolder cCTSQL and I'll be including the classes from there when needed. The SQLite class has the backup api's added although I have yet to test the progress callbacks.

Rick

[Paul Squires]

I assume you need to #INCLUDE the *.bi into your test scripts. I just started trying the ConnectionPool and ran into various issues. I'll post here:

Need to add:

#Include Once "string.bi"
#include once "\cCTSQL\cCTConnectionPool.bi"

Declare Sub thread( ByVal userdata as Any Ptr )

Dim Shared oPool as cCTConnectionPool


Lots of Warning 38 that occur when mixing boolean and non-boolean compound expressions such as:
        If This.arConnection(iIndex).DatabaseID = iDatabaseID AndAlso _
           This.arConnection(iIndex).ConnectionID = iConnectionID AndAlso _
           This.arConnection(iIndex).SlotFree = False AndAlso _
           This.arConnection(iIndex).ConnectionInUse = False Then

You will need to use CBOOL() to fix those.

The line:
ThreadDetach(ThreadCreate(@thread,iPoolID))

Should be:
ThreadDetach(ThreadCreate(@thread,cast(any ptr, iPoolID)))


Maybe you should test using WinFBE rather than FireFly because WinFBE will catch many more errors and warnings.

[Paul Squires]

Curious as to why you structure your TYPE classes with your DIM's exposes as PUBLIC (the default member access for TYPEs is Public).

Type cCTConnectionPool Extends Object

Dim lpCriticalSection as CRITICAL_SECTION
Dim arConnection(0 To 99) as POOL_CONNECTION

    Private:

    Public:

    Declare Function AddPoolConnection(ByVal iDatabaseID as Long, _
                                       ByVal iConnectionID as Long) as BOOLEAN
   etc...


I would move those two DIM's under the PRIVATE access because you want it to be private to the class and all of its instances.
...and you can omit the DIM and simply say:

lpCriticalSection as CRITICAL_SECTION
arConnection(0 To 99) as POOL_CONNECTION

[Paul Squires]

Looks like you've put a lot of work into this project so far! looks good 

[Paul Squires]

I see that you are using the sqlite3_get_table interface for your SQLExec functionality. I suggest that you re-evaluate that choice because sqlite3_get_table does not scale well in situations with very large result sets. I believe that the sqlite documentation even suggests that it is deprecated. I would use the Prepare/Step/Finalize sequence instead.

[Richard Kelly]

Thank you Paul for looking at the source. When my dog licks my feet, strange things happen...and....freely admit making more mistakes than most. Why, when I could be drawing social security and living off my roth 401k's, I'm still doing this is.?.?. I only know I still feel good when slinging some code around.   

I downloaded WinFBE and built new scripts, updated some of the classes, and, updated the attachment at the top.

I know, I got lazy with SQLite using the wrapper. I first started using it about 6-7 years ago or so and never looked backed. I'll dig in and see if I understand the prepare/step/finish loop. I originally thought getting the results all at once was a better deal than having to redim an array over and over. I'll look at Jose's CWstrArray class as it likely will perform better than what I can do with FB arrays.

Rick

[José Roca]

CWstrArray isn't a good candidate for speed. It is a wrapper on top of a safe array and I wrote for when I absolutely need an array of dynamic unicode strings. Since SQLite uses UTF-8, you don't need dynamic unicode strings.

[Richard Kelly]

CWstrArray isn't a good candidate for speed. It is a wrapper on top of a safe array and I wrote for when I absolutely need an array of dynamic unicode strings. Since SQLite uses UTF-8, you don't need dynamic unicode strings.

Whose's this Jose Guy? Of course, I'll heed your voice and do the array results the old fashioned way. I haven't see anything like the PB redim at.

[José Roca]

I don't have used SQLite, except for a few tests, but the problem with sqlite3_get_table with large result sets is obvious. It builds an array of null terminated strings and returns a pointer to that array. Building the array wastes time, and building yet another array of strings to copy the original one, wastes more time.

[José Roca]

If I find the time, what I will try is to write some classes to wrap the SQLite "16" (unicode) functions. Since a long time ago, I don't write anything that is not unicode aware.

[Paul Squires]

Rick, I think that you'll find that the most challenging part of the project will be dealing with SELECT queries where you have to receive the client request, generate the recordset, and send results back to the client. You need to code it so that it can handle very large recordsets without bogging down the incoming server requests. If you look at the sqlitening source code for this area of the program you'll see that it involves using a specially designed header for each set of records send back to the client. Records are sent back in chunks and the client needs to be able to reassemble those chucks and present them to the user as valid records. If data is large then the server (and sometimes the client also) creates temporary disk files to hold the recordset data. This allows the server to release database locks and move on while subsequent TCP calls to the server and back to the client work on the temporary file.

Here is some very early code that I wrote that started to go down the line of special format for send/receive of data. Using JSON or XML probably would have been better. I am basically getting a SELECT query from the client, decoding the message, preparing the query, determining what columns are obtained from the query, iterating over the results, finalizing the query, and then sending the data back to the client. Very primitive (and ugly) code that doesn't handle situations where the data is too large for the buffer or the creation of temp files, etc.

Code Select Expand


      case REQUEST_SELECT
         ' Decode the message.
         ' 1-2          Request code
         ' 3-4-5-6      Database handle
         ' 7-8-9-10     Client pStmt (passed back to client as server creates recordset)
         ' 11-12-13-14  Length of SQL statement
         ' 15+          SQL statement
         db = cptr(sqlite3 Ptr, cvL(mid(TData, 3, 4)))
         pStmt = cvL(mid(TData, 7, 4))
         nLen  = cvL(mid(TData, 11, 4))
         st = mid(TData, 15, nLen)
         nResult = sqlite3_prepare_v2( db, st, nLen, @hStmt, 0 )

         ' Prepare a listing of column names and types to send back to client
         ' along with the recordset.
         dim as long colCount = sqlite3_column_count(hStmt)
         for i as long = 0 to colCount - 1
            st = *sqlite3_column_name(hStmt, i)
            sColData = sColData & mkshort(len(st)) & st
            st = *sqlite3_column_decltype(hStmt, i)
            sColData = sColData & mkshort(len(st)) & st
         next   

         dim nColDataLen as long

         dim nPos as long = 0  ' zero based b/c using MemCpy api
         dim sLen as string
         dim nRecSize as long
         dim nRecSizePos as long
         dim as string sRecords = space(1024 * 3000)   '1MB 250K

         nPos = 1
         do until sqlite3_step(hStmt) <> SQLITE_ROW
            nRecSize = 0
            ' Skip over where record length will be written
            nRecSizePos = nPos
            nPos = nPos + 4
            for i as long = 0 to colCount - 1
               nColDataLen = sqlite3_column_bytes(hStmt, i)
               ' Max value of a Short is 32767 bytes
               if (nColDataLen > 0) and (nColDataLen <= 32765) THEN
                  mid(sRecords, nPos, 2) = mkshort(nColDataLen)
                  nPos = nPos + 2
                  dim as string sTemp = space(ncolDataLen)
                  MemCpy(strptr(sTemp), sqlite3_column_blob(hStmt, i), nColDataLen)
                  mid(sRecords, nPos, nColDataLen) = sTemp
                  nPos = nPos + nColDataLen
                  nRecSize = nRecSize + (2 + nColDataLen)
               elseif nColDataLen = 0 then
                  ' Its zero so check type for NULL
                  if sqlite3_column_type(hStmt, i) = SQLITE_NULL then
                     sLen = mkshort(32766)
                  else
                     sLen = mkshort(0)
                  end if
                  mid(sRecords, nPos, 2) = sLen
                  nPos = nPos + 2
               else
'                  ' Column data is larger than a Short so add a Long following the Short
'                  sColData = space(nColDataLen)
'                  MemCpy( strptr(sColData), sqlite3_column_blob(hStmt, i), nColDataLen)
'                  sRecord = sRecord & mkshort(32767) & mkl(nColDataLen) & sColData
               END IF
            next
            sLen = mkl(nRecSize)   
            mid(sRecords, nRecSizePos, 4) = sLen
         loop   
         sqlite3_finalize(hStmt)

         ' Send the resultset back to the client.
         ' 1-2      Request code
         ' 3-4      Result code
         ' 5-6-7-8  Client pStmt
         ' 9-10     Number columns in resultset
         ' For 1 to colCount
         ' 11-12    ColName length
         ' 13+n     ColName
         ' n+1      ColType length
         ' z+n      ColType
         ' Next
         ' xxxx     Number of records (record count)
         ' For 1 to NumRecs
         ' xxxx     Record length
         '    For 1 to colCount
         '       xx colLength
         '       x+ colData
         '    Next
         ' Next
         sMessage = mkshort(REQUEST_SELECT) & MKSHORT(nResult) & _
                    mkl(pStmt) & mkshort(colCount) & sColData & _
                    mkl(NumRecs) & left(sRecords, nPos)
         sMessage = mkl(len(sMessage)) & sMessage

         RV = TSNE_Data_Send(V_TSNEID, sMessage)
         If RV <> TSNE_Const_NoError Then
            Print "[CLIENT] [ERROR] " & TSNE_GetGURUCode(RV)
         End If

   END SELECT


[Richard Kelly]

Rick, I think that you'll find that the most challenging part of the project will be...

Yes, it will be interesting to see how far I can push SQLite. I have prepared a test table in advance with all USA and Canada postal codes totaling about 165,000 rows to use.

It is my goal to keep things as simple as possible, and while I might have to reengineer along the lines you've mentioned, I have in my notes an "unloaded" array with format:

NumberRows as Long
NumberColumns as Long
TotalDataSize as Long
Datasize as Long
Data as

I'm also building in compression so that will help in the total transmission size. The client will get a short message before the results are sent with the rows/columns/total datasize numbers to assist with any preallocations.

With multi-thread and SQLite connection isolation via the connection pool, I think busy/locks will not be an issue, particularly when all the databases are opened in WAL mode.

We'll see and there will be lots of testing. How about starting 10 threads all requesting 165,000 rows? There may be some tweaking to help TCP but I read some years ago about all the optimization Microsoft has built into their stack.

Rick

[Richard Kelly]

The SQLite class was updated to use prepare/step/finish in stead of exec_table. I've run a lot of code through it without any issues although I'm a bit nervous, since this is the first time I've broken away from the convenience wrappers.

I modeled the functions after sqlite3_exec which works with a callback. I just substituted a True/False flag instead to indicate whether results are returned.

You do have to terminate every SQL statement with a semi apostrophe since I wrap everything in transactions.

I still have to add the code to save BLOB's and another parameter that will contain the total size of all the contents of the results array.

The attachment at the top of the thread was updated.

Please hammer this thing so I can sleep better....

Rick

[Paul Squires]

I do have questions.... I'll post as they pop into my mind as I look at the code. 

I  don't think using an array as the means of capturing the query results is a good long-term strategy. It will not scale well and I am not 100% sure the array approach will be compatible across different programming languages should other programmers wish to adapt it to their language.

[Paul Squires]

You do have to terminate every SQL statement with a semi apostrophe since I wrap everything in transactions.

In your code you also CRLF which is not needed.

   sSQL = "BEGIN TRANSACTION; " _
        + Chr(13) + Chr(10) _
        + sSQL _
        + Chr(13) + Chr(10) _
        + " COMMIT;"