• Welcome, Guest. Please login.

SQLitening Version 1.40 Released (July 12, 2010)

Started by Fred Meier, July 09, 2010, 11:04:48 am

Previous topic - Next topic

Fred Meier

July 09, 2010, 11:04:48 am Last Edit: February 06, 2013, 03:39:50 pm by Paul Squires
SQLitening Version 1.40

                     *** Highlights ***
   1. SQLitening.Dll is now thread-safe.

   2. Added slCopyDatebase.  This new function will allow you to make copies/backups
      of a database while it is active without locking it.

   3. Added slSelStr.  This new function returns selected rows as a delimited text string.
      I really like this feature for small volume selects.

   4. Enhanced slExeBind to be able to Insert or Update multiple records. This feature
      can greatly reduce the time to affect multiple records like bulk loads.

  Below are the details of the changes:
Quote#===================<[ Version 1.4  July 12, 2010 ]>===================
1. SQLitening.Dll is now thread-safe.  You can now have multiple threads
    accessing your SQLite database in both local and remote mode.  This
    will allow you to have multiple connections per client to the server,
    one for each thread.  This can greatly increase response time for
    situations where you can take advantage of multiple threads.  A new
    example program (ExampleD.Bas) is included using multiple theads.
    This new feature also allows database Procs to use SQLitening.Dll
    rather than have to call SQlite direct (makes coding Procs much easier). 
    There is a new Proc (SQLiteningProcB.Bas) included using this new feature.

2. Added slCopyDatebase.  This new function will allow you to make copies/backups
    of a database.  Normally you make copies/backups by locking the database and then
    copying the database by using an external tool like FileCopy.  This method works well
    but has the following shortcomings:
      -- Any database clients wishing to write to the database file while a backup is
         being created must wait until the shared lock is relinquished.
      -- It cannot be used to copy data to or from :memory: nor Temp databases.
      -- If a power failure or operating system failure occurs while copying the resulting
         copied database may be corrupt.
    The slCopyDatabase function uses the new SQLite OnlineBackup API which was created
    to address these shortcomings. slCopyDatabase allows the contents of one database to be
    copied into another database, overwriting the original contents of the target database.
    The copy operation may be done incrementally, in which case the source database does not
    need to be locked for the duration of the copy, only for the brief periods of time when
    it is actually being read from. This allows other database users to continue uninterrupted
    while the copy is made.  See http://www.sqlite.org/backup.html for more info about the
    OnlineBackup API.

3. Added slSelStr.  This new function returns selected rows as a delimited text string. 
    Each field and record is delimited by a single character.  The default field delimiter
    is $BS while the default record delimiter is $VT.  These defaults can be changed. This
    is easier to use than slSelAry and can be very handy for small amounts of returning data.
    Is excellent for testing the occurrence of a condition and processing Pragma returns.
    Lets say you wanted to know if there were any rows that had an
    'X' in column F1 of table T1:
          if len(slSelStr(Select 1 from T1 where F1='X' Limit 1) then
               ' if the above is true then you have at least 1 with 'X'
          end if
    This will display the page size -- ? slSelStr("Pragma page_size")

4. Updated Example C to include examples of the new slCopyDatabase and slSelStr. 
    Added a new Example D to demo multi-threading.

5. Enhanced slExeBind to be able to Insert or Update multiple records.  In
    prior releases slExeBind was only needed to work with blobs or text values
    that contained nulls.  Now it can be used to greatly improve Insert or
    Update speed.  My tests show it can improve them by as much as 45%.  Check
    out ExampleB.Bas for a sample that will Insert 50,000 records two
    different ways in either local or remote mode.  One way is to use
    slExeBind while the other slower way is to us slExe and stack the SQL
    insert statements.  Also note in this example the use of an array and the
    Join$ command to greatly improve concatenation speed. 

6. Added ModChars i,I,D,Z to slBuildBindDat which will allow for the
    building of dats to bind Integer, Integer64, Double, and Null values. 

7. Added the ability in slGetFile to pass a get position and a get length.
    Using a position and length is useful when you only want to get a
    portion of the file.  This also may be needed for very large files
    even when you want to get the whole file.

8. Added the ability in slPutFile to pass a put position. Using a
    position is useful when you only want to put a portion of the file. 
    This also may be needed for very large files even when you want to
    put the whole file.  Removed the create file in remote mode
    restriction.  Added D and T ModChars which allow you to delete a
    file and control when the file is truncated.

9. Changed slSel so that when no set is passed (defaults to zero) or
    the passed set number is zero it will first close that set. This
    will prevent error -14 (%SQLitening_InvalidSetNumber).

10. Changed slOpen to automatically enable SQLite foreign key support.
    SQLite disables this feature by default (for backwards compatibility),
    so must be enabled separately for each database connection. Also
    added the f ModChar to allow you to not enable foreign key support.

11. Added the C ModChar to slSel.  This will first close the passed set
    number.  This will prevent error -14 (%SQLitening_InvalidSetNumber)
    but should be used with caution.

12. Changed slConnect to check that the client and server versions are
    same.  If not then %SQLitening_AccessDenied is returned.

13. Changed slConnect so that when a second connect request is made it
    will just return if the current connection is still active.  Before
    it would just return without checking if still active.

14. Added a new request to slGetStatus.  Request 4 will check if the
    current remote connection is active and return either "Yes" or "No"
    This is like a 'ping' which will cause a trip to serve.

15. Add a quiet mode parm to SQLiteningServerAdmin so it can be run
    from a script without any user messages to answer. Pass the Q command
    line parm proceeded with a / or - (OS standard).  The Q must be followed
    by a numerical value for the action requested as follows:
         1 = Install
         2 = Start --- will first Install if required
         3 = Install and Start
         4 = Stop
         8 = Uninstall --- will first Stop if required
        12 = Stop and Uninstall
        16 = Reload Config
    This program will return the following codes if in Quiet mode:
        0 = All OK
        1 = Can't perform request, service in wrong status
        2 = Request failed.
        9 = Service in unknown or invalid status

16. Fixed bug in slSel when running in local mode and using the "B" ModChar.

17. Added a new sample proc called SQLiteningProcsB.Bas.  This proc uses
    SQLitening for all processsing.

18. Update SQLite3.Dll to the version dated March 30 2010.

[attachment deleted by admin]

Fred Meier

Oops!!  The SQLiteningServer.Cfg file was released with the
ConnectionTimeOut set to 1 (time out after one minute).  My mistake, it
should have been set to blank (time out after thirty minutes). 
ConnectionTimeOut controls the number of minutes the server will wait to
receive a message from an active connection.  Default (blank) is 30.  Set
to -1 if you want to never timeout.