Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[Paul Squires]

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
 

[Paul Squires]

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



[Paul Squires]

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.

[Paul Squires]

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?

[Richard Kelly]

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.

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.

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.

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.

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.

I see that you are storing the database handles as DWORD

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

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.

[José Roca]

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.

[Richard Kelly]

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

[Richard Kelly]

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

[Richard Kelly]

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

[Richard Kelly]

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

Paul was right about the scaling....

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

[Richard Kelly]

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

[Richard Kelly]

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

[Paul Squires]

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.

[Richard Kelly]

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/

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

[Richard Kelly]

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