SQLite Client Server Source

[Richard Kelly]

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

[Paul Squires]

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.

[Richard Kelly]

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

[Paul Squires]

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.

[Richard Kelly]

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

[Richard Kelly]

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

[Richard Kelly]

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

[Richard Kelly]

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....

Rick

[Richard Kelly]

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

Rick

[Richard Kelly]

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

[Richard Kelly]

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

[Richard Kelly]

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

[Richard Kelly]

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

[Richard Kelly]

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

[Richard Kelly]

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