PlanetSquires Forums

Support Forums => General Board => Topic started by: Richard Kelly on May 02, 2017, 02:07:39 AM

Title: SQLite Client Server Source
Post by: Richard Kelly on May 02, 2017, 02:07:39 AM
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 (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
Title: Re: SQLite Client Server Source
Post by: Paul Squires on May 02, 2017, 04:34:19 PM
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.
Title: Re: SQLite Client Server Source
Post by: Paul Squires on May 02, 2017, 04:41:06 PM
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
Title: Re: SQLite Client Server Source
Post by: Paul Squires on May 02, 2017, 04:55:35 PM
Looks like you've put a lot of work into this project so far! looks good  :)
Title: Re: SQLite Client Server Source
Post by: Paul Squires on May 02, 2017, 05:02:38 PM
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.
Title: Re: SQLite Client Server Source
Post by: Richard Kelly on May 02, 2017, 09:13:43 PM
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.   :o

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
Title: Re: SQLite Client Server Source
Post by: Josť Roca on May 02, 2017, 09:26:36 PM
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.
Title: Re: SQLite Client Server Source
Post by: Richard Kelly on May 02, 2017, 09:39:47 PM
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.
Title: Re: SQLite Client Server Source
Post by: Josť Roca on May 02, 2017, 09:46:25 PM
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.
Title: Re: SQLite Client Server Source
Post by: Josť Roca on May 02, 2017, 10:33:53 PM
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.
Title: Re: SQLite Client Server Source
Post by: Paul Squires on May 03, 2017, 12:03:55 PM
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]
      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



Title: Re: SQLite Client Server Source
Post by: Richard Kelly on May 03, 2017, 07:21:58 PM
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
Title: SQLite Client Server Source Update
Post by: Richard Kelly on May 05, 2017, 12:28:51 AM
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
Title: Re: SQLite Client Server Source
Post by: Paul Squires on May 05, 2017, 12:17:06 PM
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.
 
Title: Re: SQLite Client Server Source
Post by: Paul Squires on May 05, 2017, 12:18:28 PM
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;"
Title: Re: SQLite Client Server Source
Post by: Paul Squires on May 05, 2017, 12:23:40 PM
In your code you might want to apply a function to the incoming SQL statement to ensure that it handles embedded quotes correctly, or at least provide such a function to the programmer. Here is the one that I use:

''
''  Safely escape embedded quotes within an sql statement
''
Function clsSqlite.SafeSql( ByVal sql As String ) As String
   If Len(sql) = 0 Then Exit Function   ' prevents mprintf from returning "(NULL)" literal string
   Dim pzSql As ZString Ptr
   pzSql = sqlite3_mprintf("%q", sql)
   Function = *pzSql
   sqlite3_free(pzSql) 
End Function
 
Title: Re: SQLite Client Server Source
Post by: Paul Squires on May 05, 2017, 12:30:10 PM
I see that you wrapped all of the SQLITE constants in a NameSpace. I am thinking that an ENUM may be more appropriate than a NameSpace?

Code: [Select]
ENUM cCTSQLiteClass
   SQLITE_OK                  = &h00000000
   SQLITE_TRUE                = &h00000001
   SQLITE_OPEN_READONLY       = &h00000001 
   SQLITE_OPEN_READWRITE      = &h00000002 
   SQLITE_OPEN_CREATE         = &h00000004 
   SQLITE_OPEN_URI            = &h00000040 
   SQLITE_OPEN_MEMORY         = &h00000080 
   SQLITE_OPEN_NOMUTEX        = &h00008000 
   SQLITE_OPEN_FULLMUTEX      = &h00010000 
   SQLITE_OPEN_SHAREDCACHE    = &h00020000 
   SQLITE_OPEN_PRIVATECACHE   = &h00040000 
   SQLITE_OPEN_WAL            = &h00080000
   SQLITE_ROW                 = 100
   SQLITE_DONE                = 101
   SQLITE_CONFIG_LOG          = 16
   SQLITE_BLOB                = 4
   SQLITE_NULL                = 5
End ENUM

Title: Re: SQLite Client Server Source
Post by: Paul Squires on May 05, 2017, 12:38:58 PM
I see that you are storing the database handles as DWORD. In the documentation they technically should be sqlite3 ptr. Whether that is a DWORD on all platforms I am not sure (I would be surprised if it is).

I have attached an old SQLite class that I wrote in FreeBasic that might give you some ideas as well.
Title: Re: SQLite Client Server Source
Post by: Paul Squires on May 05, 2017, 12:42:27 PM
In cases where you need to specify flags (say, for example when opening/creating a database) I am pretty sure that you need to OR the flags rather than add them. I noticed you have code like this:

cCTSQLiteClass.SQLITE_OPEN_READWRITE + cCTSQLiteClass.SQLITE_OPEN_CREATE

When maybe it sould be:

cCTSQLiteClass.SQLITE_OPEN_READWRITE OR cCTSQLiteClass.SQLITE_OPEN_CREATE

Adding may cause side effects?

Title: Re: SQLite Client Server Source
Post by: Richard Kelly on May 05, 2017, 04:01:42 PM
Thank you Paul for taking the time to contribute to this effort. It's really helpful to hear from a pro.

I'm going to provide feedback on all your responses here.

Quote
I  don't think using an array as the means of capturing the query results is a good long-term strategy.

The FB extensions has a JSON library that can covert to BSON and from BSON to JSON. I'm looking into having the class deliver BSON as its 'native' results class.

Quote
In your code you also CRLF which is not needed.

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

Run a test and put a comment in as the the only thing on the last line without a CRLF. I included the begin transaction similiarly so that when I build in the trace facility, the code would be easier to read.

Quote
Safely escape embedded quotes within an sql statement...

I was planning on the server class doing that but there is no harm to moving it down to the SQlite class.

Quote
I see that you wrapped all of the SQLITE constants in a NameSpace. I am thinking that an ENUM may be more appropriate than a NameSpace?

Since the constant names are used in other *.bi files, I put them in the namespace to avoid conflicts. If I build a enum outside of the namespace, you won't gain much since you'll have to qualify each reference to be safe.

Quote
I see that you are storing the database handles as DWORD

Stuck on Windows stuff. I'll change them to ANY.

Quote
cCTSQLiteClass.SQLITE_OPEN_READWRITE + cCTSQLiteClass.SQLITE_OPEN_CREATE

When maybe it sould be:

cCTSQLiteClass.SQLITE_OPEN_READWRITE OR cCTSQLiteClass.SQLITE_OPEN_CREATE

They are equivalent, and, I'll switch over to OR to be consistent with the 'C' examples floating around.
Title: Re: SQLite Client Server Source
Post by: Josť Roca on May 05, 2017, 04:45:53 PM
Apparently, the is not a SQLite3 library for Free Baisc 64 bit. Am I wrong?

Must be because there are some variadic functions and FB 64 bit does not support them.

Therefore, Paul's sqlite3_mprintf method won't work in 64 bit, because it used the variadic function sqlite3_mprintf.
Title: Re: SQLite Client Server Source
Post by: Richard Kelly on May 05, 2017, 04:49:45 PM
Apparently, the is not a SQLite3 library for Free Baisc 64 bit. Am I wrong?

I do plan on compiling to 64 bit and ironing any incompatabilities. First I want to get the classes working and close to their final form in 32 bit...

Rick
Title: Re: SQLite Client Server Source
Post by: Richard Kelly on May 05, 2017, 10:02:23 PM
Paul...

I don't know yet how to proceed with a non array results format. I could not find a FB BSON library and really don't see any advantage in building with the JSON library and converting to BSON on the server side and then back to JSON on the client side. More thought needed...Since BSON is mostly a key/value pairing, it might be easy enough to write a BSON class with just the stuff that we would need for SQLite results.

I switched over to the enum and just kept it in the namespace.

Code: [Select]
Namespace cCTSQLiteClass

Enum cCTSqliteConstants

SQLITE_OK                  = &h00000000
SQLITE_TRUE                = &h00000001
SQLITE_OPEN_READONLY       = &h00000001 
SQLITE_OPEN_READWRITE      = &h00000002 
SQLITE_OPEN_CREATE         = &h00000004 
SQLITE_OPEN_URI            = &h00000040 
SQLITE_OPEN_MEMORY         = &h00000080 
SQLITE_OPEN_NOMUTEX        = &h00008000 
SQLITE_OPEN_FULLMUTEX      = &h00010000 
SQLITE_OPEN_SHAREDCACHE    = &h00020000 
SQLITE_OPEN_PRIVATECACHE   = &h00040000 
SQLITE_OPEN_WAL            = &h00080000
SQLITE_ROW                 = 100
SQLITE_DONE                = 101
SQLITE_CONFIG_LOG          = 16
SQLITE_BLOB                = 4
SQLITE_NULL                = 5

End Enum

End Namespace

I changed all the DWORD's over to ANY. FB win defines DWORD as a uLong.

I put your safesql in as a public function. I changed it a bit.

Code: [Select]
' =====================================================================================
' Escape SQL for single apostrophe
' =====================================================================================
Sub cCTSQLite.SafeSQL (ByRef sSQL as String)

Dim pzSQL as ZString Ptr

   If Len(sSQL) > 0 Then

      pzSql = sqlite3_mprintf("%q",ZStringPointer(sSQL))
   
      sSQL = *Cast(ZString Ptr,pzSQL)

      sqlite3_free(pzSql)
     
   End If
     
End Sub

I'm going to be focused on the SQLite class until I get it with all the abilities I have in my notes, and, then test it with 32 and 64 compiles.

Rick
Title: Re: SQLite Client Server Source
Post by: Richard Kelly on May 06, 2017, 05:55:34 PM
Attachment updates. A bit of an overhaul of SQLite class. I now get clean compiles and successful tests on both 32 and 64 bit. Jose prompted me to remember to test 64 bit... :)

Take a look. I think this is close to the final with the exception of either validating Paul's array scaling concern or picking a BSON/JSON format. I've removed all the SQLite options I preconfigured in and the global error log is supported.

Should our server class be "application centric"? By that, I mean when the server is running, it is there primarily to serve a single application instead of a one server concept that can serve multiple applications. An application centric server can have lots of application specific logic stuffed in it and backup/trace logging is much simplified since an application can open one main database with attached secondary databases.

Rick
Title: Re: SQLite Client Server Source
Post by: Richard Kelly on May 07, 2017, 06:04:08 PM
Last cleanup of the SQLite class before I run some stress testing to evaluate using an array to return results. The attachment at the top of the thread has been updated. The SQLite test script was tested successfully on both 32 and 64 bit compiles.

Changes

1. Build a wrapper around the backup api that includes support for a progress callback
2. Added a pragma function intended to be used after a database open to override SQLite library defaults.

I've constructed a database with multiple tables and a view with several joins that a select * will return around 46.5K rows and I'm going to throw 20 threads with a connection pool at it and see what happens. I'll report results here.

*** Update ***

There is a limitation on array size that means we will have to design another alternative to using arrays. My testing blows up at around 8000 rows of 9 columns.

http://freebasic.net/forum/viewtopic.php?f=3&t=19651 (http://freebasic.net/forum/viewtopic.php?f=3&t=19651)

Paul was right about the scaling.... :o

Even when I changed to passing the array back to a callback function one row at a time, I blew up at about the same place. About the only advantage I can see over using sqlite3_exec is that I can return the values in the "native" format including binary string blobs. It must be the stack allocation space. I'll look into passing back the array with three uLongs per column - the SQLite type, size, and the pointer I can get to the underlying value. Since the array is all uLongs and only gets DIM'd once and I reuse the contents, perhaps that might work and won't keep allocating more space. That pushes the issue of the final delivery format to the caller.

*** Update ***

The more I dug into this memory issue, the more I became convinced that spilling results to the file system is the only way to have scaling for large result sets. I can use the windows temp file folder, a UUID type file name and just return the full path and name to the requestor. The format is uncertain, it would be quite easy to build an INI type of structure and let Jose's/Windows INI api's step in or some custom format encapsulated into a class, of course into a SQLite results class that would efficiently return a row at a time.

Thoughts?

Rick
Title: Re: SQLite Client Server Source
Post by: Richard Kelly on May 08, 2017, 03:35:39 PM
Let's see what kind of performance is available spooling off results.

1. Get a temporary file using GetTempPath and GetTempFileName api's.
2. Write one row per sqlite3.step. For each column in the row
     a. Total Column size = Long
     b. Column Type = SQLite value type
     c. Column Data Length = Long
     d. Column Data = ???
3. Close temporary file
4. Return full name of temporary file to caller along with the row and column count.

This can be accomplished without creating a duplicate copy of the column data value for BLOBs or TEXT

1. Get the data value length using sqlite3_column_bytes

1. sqlite3_column_type returns SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_TEXT, SQLITE_BLOB, or SQLITE_NULL

2. If SQLITE_INTEGER or SQLITE_FLOAT get the value using sqlite3_column_double or sqlite3_column_int64 or sqlite3_column_int

3. If SQLITE_BLOB get the BLOB pointer

4. If SQLITE_NULL, nothing is spooled out

5. If SQLITE_TEXT get the ZString ptr

6. Calculate the column total size 4+4+data value length

7. Spool out everything
Title: Re: SQLite Client Server Source
Post by: Richard Kelly on May 09, 2017, 01:08:00 AM
The other hat I wear during my "jawb" is a database developer dealing mostly with Teradata and some with Oracle, IBM and Microsoft servers. I'm "borrowing" the concept of spool space. When you execute a query, you'll get a file handle returned along with the number of rows and columns and the total spool space used. The file handle will be rewound to the beginning of the file after flushing buffers. A new class cCTSQLiteSpool will support walking through each column results with the open file handle for TCP send as well as walking through a stream such as what the client will receive from the server over TCP. When you close the file handle, the assoicated temporary file will be deleted. I'll have this coded and tested out this week. I'm hoping that letting windows manage all the memory/caching will let us scale up as far as disk space will take us and we'll see what kind of performance we can get using native file api's and avoid using the stack as FB does.

Rick
Title: Re: SQLite Client Server Source
Post by: Paul Squires on May 09, 2017, 08:53:31 AM
The other hat I wear during my "jawb" is a database developer dealing mostly with Teradata and some with Oracle, IBM and Microsoft servers. I'm "borrowing" the concept of spool space. When you execute a query, you'll get a file handle returned along with the number of rows and columns and the total spool space used. The file handle will be rewound to the beginning of the file after flushing buffers. A new class cCTSQLiteSpool will support walking through each column results with the open file handle for TCP send as well as walking through a stream such as what the client will receive from the server over TCP. When you close the file handle, the assoicated temporary file will be deleted. I'll have this coded and tested out this week. I'm hoping that letting windows manage all the memory/caching will let us scale up as far as disk space will take us and we'll see what kind of performance we can get using native file api's and avoid using the stack as FB does.

Rick

I applaud this approach. It is similar to the concept applied in sqlitening and seems to work extremely well. I hope that you can pull it off as it will make the library very robust.
Title: Re: SQLite Client Server Source
Post by: Richard Kelly on May 09, 2017, 09:06:01 AM
I agree. The SQLite3 class should handle just about anything you throw at it. Each column result spooled out is a simple group:

SQLite Data Type
Data Length
Data

Well return the "native" SQLite data. I'm thinking that some of that will be integers representing dates and I might have to provide functions to get it into the YYYY-MM-DD HH:MM:SS format or just return the windows system date/time structure.

*** Update ***

I don't have to worry about dates. Each developer already the ability to handle it.

http://www.sqlitetutorial.net/sqlite-date/ (http://www.sqlitetutorial.net/sqlite-date/)

The TCP result stream received by the client class also needs to be spooled to maintain scaling and the client class can use the same functions as the sqlite3 class.

Rick
Title: SQLite Client Server Spooler Proof of Concept
Post by: Richard Kelly on May 10, 2017, 12:56:26 AM
Attached is rough proof of concept simulating what the SQLite class will do during the Prepare/Step/Finalize loop(s) for review and comment. I did notice I have a warning on the GetFileSizeEx api to work out. Each result from the Step process will spool out a result object of three values and return the spool file handle and the size of the spool file to use during a TCP send. There are still functions to add for the client side to recreate the spool file and parse out the results. We are dealing with the base SQLite3 data values without any conversions.

Rick
Title: Re: SQLite Spooler Proof of Concept
Post by: Richard Kelly on May 10, 2017, 07:25:30 PM
When SQLite steps into a record, all integer values are expanded to 64 bits.

You cannot know how large an integer value is before reading it. If you want to be sure that integer values are never truncated,  you have to always use sqlite3_column_int64. I can always store integers as 64 bits in the spooler. Is it worthwhile to check the value for ranges supported by BYTE, WORD, LONG and only store 1,2, or 4 bytes instead? It probably only makes a difference on large result sets and maybe it's not significant.

Thoughts?

Rick
Title: Re: SQLite Client Server Source
Post by: Paul Squires on May 10, 2017, 07:34:11 PM
I haven't tried your new code yet but my initial reaction is that why not just store everything as text and let the client side programmer determine how to manipulate that received text. If text, no conversion.... if numeric, then Val(text). Saves you from having to deal with storing values as binary. Transmitting the recordset over tcp from the server to the client as text seems easier.
Title: Re: SQLite Client Server Source
Post by: Richard Kelly on May 10, 2017, 08:32:56 PM
I haven't tried your new code yet but my initial reaction is that why not just store everything as text and let the client side programmer determine how to manipulate that received text. If text, no conversion.... if numeric, then Val(text). Saves you from having to deal with storing values as binary. Transmitting the recordset over tcp from the server to the client as text seems easier.

That is easy to do. Just thinking about speed and size. Nulls are bit problematic unless I store everything as zstrings but that leads to BLOBS. I think I would have to retain a flag for BLOBS where a terminating 0 isn't going to work. A null would have a single 0 as it's value. Maybe just set the size stored as zero would do the trick in all cases and just return straight FB STRING's. The spooler would just have a size value followed by the value itself. Given the number of rows and the columns per row, you could step through the spooler in straight forward fashion.

Rick
Title: Re: SQLite Client Server Source
Post by: Paul Squires on May 10, 2017, 08:49:54 PM
Maybe preface each record's data with a two flags: One byte value that represents the data representation (null, text, etc) followed by an integer representing the data size....

...or, simply preface the data with one integer. Positive value represents size of normal string. Negative value represents size of binary blob, and zero represents a NULL value.

Just thinking out loud as I type this post.
Title: Re: SQLite Client Server Source
Post by: Richard Kelly on May 11, 2017, 12:42:17 AM
Maybe preface each record's data with a two flags: One byte value that represents the data representation (null, text, etc) followed by an integer representing the data size....

...or, simply preface the data with one integer. Positive value represents size of normal string. Negative value represents size of binary blob, and zero represents a NULL value.

Just thinking out loud as I type this post.

If we depend on the developer to know their values and when a result contains a BLOB, just a size followed by the string. During decoding, I'll read the size and return the string. Conversely, a one byte flag for BLOBS (TRUE/FALSE) could clarify things since SQLite can put anything in any column at any time.

Rick
Title: Re: SQLite Client Server Source
Post by: Richard Kelly on May 11, 2017, 01:23:33 PM
This is going to be a straightforward approach to using the spooler.

Spooler Layout

1. 4 byte signature block used after TCP receive for validation, if single local application, not referenced
2. Variable number of result blocks
   a. 1 byte BOOLEAN, True=BLOB,False=String
   b. 4 byte LONG for size of return string
   c. ?? return string of any size
3. 5 byte EOF block - should never be returned, can check if size returned is < 0.
   a. If a decode hits the EOF block there is something wrong either with spooler file or the row/count used
      to step through the result blocks.

Spooler file is a temp file that is automatically deleted when closed.


Public Functions:


CreateSpoolFile

Used by SQLite class, automatically adds a signature block


EndSpoolFile

Used by SQLite class to prepare spool file for TCP Send, adds the EOF block


CloseSpoolFile

Closes the spool file. Must be done by both client and server to avoid memory leaks


WriteSpoolerResultBlock

Used by SQLite class to save query results


ReadSpoolFile

Used by server to get spool contents for TCP Send


CreateSpoolerStreamFile

Used by client for TCP Receive streams, does not add a signature block


WriteSpoolerSteam

Used by client for TCP Receive streams


EndSpoolerStream

Rewind to beginning and check signature block. Used for TCP Receive Streams, but is also used in single local applications to prepare for reading result blocks


ReadSpoolerResultBlock

1. Read one byte, convert to BOOLEAN and return
2. Get size
3. If size < 1 then return ""
4. If size > 0, read size bytes from spooler to return string

Rick
Title: Re: SQLite Client Server Source
Post by: Richard Kelly on May 13, 2017, 06:13:43 PM
My stack space issue was resolved by adding CDECL to all the sqlite3 declares. I'm currently as 3 sec to spool up 47,000 rows 32bit. About 10% faster with 64bit.

Rick
Title: Re: SQLite Client Server Source
Post by: Richard Kelly on May 13, 2017, 08:54:41 PM
The load proof of class...I threw 100 threads using a connection pool all using the same SELECT * returning 46281 rows. That consumed on average 25 connection handles and it all ran in the default 1M stack space.

Shout out to Paul for pushing me this way.... ;D

Rick
Title: Re: SQLite Client Server Source
Post by: Richard Kelly on May 13, 2017, 09:55:43 PM
Latest source code uploaded first posting on first page. It's time to think about the client and server classes.

The SQLite, connection, crypto, and, spool classes are on GitHub at:

https://github.com/breacsealgaire/FreeBasic-32-64-Windows-SQLite-Class (https://github.com/breacsealgaire/FreeBasic-32-64-Windows-SQLite-Class)

Rick
Title: Re: SQLite Client Server Source
Post by: Richard Kelly on May 29, 2017, 02:34:49 AM
Here are the beginnings of the server and client classes. Save the cCTSQL folder in the root of the FB inc folder. Attachment can be found in the first post of this thread and has been updated.

To run the server and client scripts you'll need Jose's includes, and the console option checked. Execute the server program and then the client program. Click on the start button to request the client to find the server and do a very basic echo type message exchange. The server and client programs can be run on different computers in the same sub net. If the ports 8791 and 8792 conflict, you will have to update the source and use different ports. There are some properties to assist.

This represents the basic communications framework which I'll enrich with SQLite, Compression and Encryption. The server script is multithreaded with good scaling. Each class masks much of the underlying socket calls to allow the developer to just focus on their application.

When Paul has an visual designer layered on his IDE, I'll share an example of a full server GUI. I have to tweak my includes to be UNICODE aware to work with Jose.

Questions or issues, let me know.

Rick
Title: Re: SQLite Client Server Source
Post by: Richard Kelly on June 05, 2017, 11:01:05 PM
Here is the continuation of the server and client classes. Save the cCTSQL folder in the root of the FB inc folder. Attachment can be found in the first post of this thread and has been updated.

To run the server and client scripts you'll need Jose's includes, and the console option checked. Execute the server program and then the client program. Click on the start button to request the client to find the server and do a very basic echo type message exchange. The server and client programs can be run on different computers in the same sub net. If the ports 8791 and 8792 conflict, you will have to update the source and use different ports. There are some properties to assist.

This represents the basic communications framework with encryption layered in which I'll enrich with SQLite and  Compression. All that is necessary is for the client and server classes to set their mutual shared key via the ClientSharedKey and ServerSharedKey subs respectively. Encryption/Decryption is handled transparently. The client/server authentication/handshake is included.

Let me know of any issues.

Rick
Title: Re: SQLite Client Server Source
Post by: Richard Kelly on June 06, 2017, 04:44:36 PM
Going back to some of Paul's early comments, I'm coming around that SQLite3 support might involve only three things....

1. Perform a query (returning results is optional)
2. Perform some update (returning results is optional)
3. Perform a backup

Thoughts?

Rick
Title: Re: SQLite Client Server Source
Post by: Richard Kelly on June 13, 2017, 05:33:35 PM
At the risk of promoting vaporware, I have taken time this last week to build additional message pumps into the server class to allow the developer access to all kinds of events. Although I'm only putting messages out to the console, it seems that in a full GUI, you would populate a listview with the last x number of messages and I wanted to get potential GUI operations away from the main server message pump. Typical events are sqlite3 error logging, optional sql statement tracing, tcp connections, udp connections, etc.

The error logging and tracing can be important to debug issues on remote installs of your apps.

Rick
Title: Re: SQLite Client Server Source
Post by: Richard Kelly on June 14, 2017, 12:09:29 PM
A fair amount of testing waiting to be done on messaging. This is how I have designed messaging. It involves the message window message pump and multiple threads.

The message pump will receive event messages to act on with access to the following:

Timestamp

Type
   1. Broadcast Received
   2. Broadcast Rejected
   3. Broadcast Response
   4. Client Connection
   5. Client Authentication Failure
   6. Client Authenticated
   7. Server Response
   8. Client Response
   9. Client Request
  10. Database Registration
  11. Backup Request
  12. Backup Completed
  13. Backup Failure
  14. Backup Status
  15. SQL Error Log
  16. SQL Trace
  17. Connection Pool
  18. Thread Pool
  19. Other

Severity

   1. Normal
   2. Warning
   3. Failure

Client IP Address

Message ID

Message

Event messages are queued in a fixed size circular array with functions to add/retrieve event message details. A retrieved event message is cleared and the array slot marked as available for a new event. When a message is added, the array index is returned and passed via the WPARAM in a PostMessage call. If no free slot is available for adding an event message, the first slot (index 0) is overwritten.

If you think of other events, let me know. It's relatively painless to add more events.

Rick
Title: Re: SQLite Client Server Source
Post by: Richard Kelly on June 16, 2017, 11:17:34 AM
There is a bit of a dilemma on putting compression into the model. When I originally made the changes to use temporary files to deliver results, I also noted that I could then easily use the api TransmitFile to send it to the client and let windows figure out the best way to do that. For compression, I would have to do the iteration/chunking through the results file, compress and send. Some preliminary testing shows the TransmitFile api is quite good at what it does, and even, uncompressed, runs better that what I can do.

Another issue is that the results file is sent unencrypted. I might be able to overcome that by always creating the temporary file encrypted in some multiplier of 16 byte blocks.

How important do you think this all is? No compression or encrypted results? Forget the performance improvements offered by TransmitFile?

The results file could be encrypted as:

The signature block at the beginning and EOF block at the end are left as is.

The clear text result block looks like:

Variable number of result blocks

   a. 1 byte BOOLEAN, True=BLOB,False=String
   b. 4 byte LONG for size of return string
   c. ?? return string of any size

Encrypted variable number of result blocks

   a. 4 byte LONG for encrypted block size
   b. Encrypted result block


Decrypt the result block and return

   a. 1 byte BOOLEAN, True=BLOB,False=String
   b. ?? return string of any size (using the 4 byte LONG for size of return string)

This could be done transparently. The SQLite and Spooler classes would just need to know the encryption parameters which are available after the client authentication/handshake.

Sorry Paul, it's getting to be a bit more complicated.

Rick
Title: Re: SQLite Client Server Source
Post by: Richard Kelly on June 19, 2017, 12:57:37 PM
I went ahead and added encryption/decryption to the spooler class and updated the cCTSQLite_Script.txt to demonstrate/test. It only took about a dozen lines of code and now everything going back and forth between the client and server classes is encrypted.

Attachment updated.

Rick
Title: Re: SQLite Client Server Source
Post by: Richard Kelly on June 19, 2017, 04:50:24 PM
Recalling Paul's comments about keeping things simple, I'm looking to wrap things up in a straightforward way.

1. Adding functions to get, add, and release SQLite DB connection handles. You'll be responsible for opening connections.
2. Adding functions supporting database backups.
3. Adding a messaging subsystem of sorts fed to the main message pump window.
4. Adding functions for transmit and receive of spooler results files.
5. Adding functions for querying/updating database


Common Message UDT

Code: [Select]
Type SQLITE_LOG

   LocalTime   as SystemTime
   Event       as uShort
   Severity    as uShort
   IPAddress   as String
   ErrorCode   as Long
   Message     as String
   
END TYPE

The mail message pump will receive one of the following five messages. SQLITE_USER_MESSAGE is user generated, the others are created and posted by the server class.

Code: [Select]
Private Const SQLITE_SERVER_UDP_BROADCAST   = WM_USER + 1000
Private Const SQLITE_SERVER_TCP_CONNECT     = WM_USER + 1001
Private Const SQLITE_SQL_ERROR_LOG          = WM_USER + 1002
Private Const SQLITE_SQL_TRACE              = WM_USER + 1003
Private Const SQLITE_USER_MESSAGE           = WM_USER + 1004


Private Const SQLITE_SEVERITY_INFO          = 1
Private Const SQLITE_SEVERITY_WARNING       = 2
Private Const SQLITE_SEVERITY_FAILURE       = 3

The sequence and format of messages is left to the developer and can be whatever is needed. When a client connects, the server and client classes will authenticate and then the server will sit on a TCP receive waiting for marching orders.

Rick
Title: Re: SQLite Client Server Source
Post by: Richard Kelly on July 05, 2017, 03:31:52 AM
The basic server messaging framework has been added. It will continue to be fleshed out and shows you how the server class posts notifications of events. Although I am just pumping things out to the console window, in a gui server implementation, you could use a listview and color/icons for warnings and failure events.

Attachment in first message updated.

Rick
Title: Re: SQLite Client Server Source
Post by: Paul Squires on August 03, 2017, 07:54:55 PM
Hi Rick, just curious if you're still chugging along with this project? It shows a lot of promise. I see that the GitHub has not been updated in a while:  https://github.com/breacsealgaire/FreeBasic-32-64-Windows-SQLite-Class

Along with Jim's grid and print preview code, a great FB sourced sqlite client/server would certainly help round out the tool belt.