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
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.
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
Looks like you've put a lot of work into this project so far! looks good :)
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.
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
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.
Quote from: Jose Roca on May 02, 2017, 09:56: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.
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.
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.
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.
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.
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
Quote from: TechSupport on May 03, 2017, 12:33:55 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
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
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.
Quote from: Richard Kelly on May 05, 2017, 12:58:51 AM
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;"
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
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?
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
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.
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?
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.
QuoteI 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.
QuoteIn 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.
QuoteSafely 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.
QuoteI 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.
QuoteI see that you are storing the database handles as DWORD
Stuck on Windows stuff. I'll change them to ANY.
QuotecCTSQLiteClass.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.
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.
Quote from: Jose Roca on May 05, 2017, 05:15:53 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
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.
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.
' =====================================================================================
' 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
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
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
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
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
Quote from: Richard Kelly on May 09, 2017, 01:38: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
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.
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
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
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
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.
Quote from: TechSupport on May 10, 2017, 08:04: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.
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
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.
Quote from: TechSupport on May 10, 2017, 09:19: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.
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
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
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
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
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
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
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
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
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
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
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
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
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
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.
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
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
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.