SQLitening Support Forum

Support Forums => Download SQLitening (latest version - with full source code) => Topic started by: Fred Meier on October 20, 2009, 12:41:36 PM

Title: Preview 1.3
Post by: Fred Meier on October 20, 2009, 12:41:36 PM
I thought it would be appropriate to give you a preview of the major changes coming in 1.3. 
Depending on your feed back, SQLitening 1.3 should be available around Nov 1. 


           **** Named Locks ****
If you google "named locks" you will get many pages rerturned.  Both Oracle and MySql support named locks.
Below is SQLitening proposed implementation for named locks:

'======================<[ Sel Rel Named Locks ]>=======================
Function slSetRelNamedLocks (byref rsLockNames as String, _
                             optional byval rsModChars as String, _
                             optional byval rsSelStatement as String, _
                             optional byval rlSelSetNumber as Long, _
                             optional byval rsSelModChars as String) as Long
'   Will set or release named lock(s).  This can be used to
'   implement application locks or to simulate record locks.  Names
'   are locked on a server-wide basis.  If a name has been locked by
'   one client, the server blocks any request by another client for
'   a lock with the same name.  This allows clients that agree on a
'   given lock name to use the name to perform cooperative named
'   locking.  But be aware that it also allows a client that is not
'   among the set of cooperating clients to lock a name and thus
'   prevent any of the cooperating clients from locking that name.
'   One way to reduce the likelihood of this is to use lock names
'   that are database-specific or application-specific.  Named locks
'   are only used in remote mode, ignored when running in local mode.
'   Will also optionally do a Sel command but only if the lock request
'   was successful.
'   LockNames:
'      This is a vertical tab ($VT) delimited list of names to set or release.
'      Be sure these names uniquely identify the lock you want to set.  If, for
'      example, you know the RowID of a record you want to lock you would need
'      to include some type of table ID within the name. The first character of
'      each name must be one of the following:
'         + (plus)  = Set lock
'         - (minus) = Release lock.
'      All named locks owned by a connection are automatically released when
'      the connection is no longer active.
'   ModChars:
'      Tn = Where n is the number of milliseconds to wait for a named
'           lock to be set before returning as unsuccessful.  If omitted
'           or is zero then will return immediately if a named lock can
'           not be set because it is owned by another connection.
'      R =  Release all named locks owned by this connection before setting
'           or releasing any named locks in LockNames.
'   SelStatement:
'      If the SelStatement is not empty and locking was successful then
'      will do the Sel command.  The Statement should be Select or Pragma
'      or any other that returns rows (Insert, Update, and Delete do not
'      return rows).
'   SelSetNumber:
'      SelSetNumber can be omitted or be any value from 0 to 32767. If omitted
'      then will use zero.  If SelStatement is empty then this is ignored.
'      SelSetNumber is used as an array index, no gaps is best, which will result
'      in a smaller array.  You can have as many unique sets open/active at same
'      time as your memory will allow.
'   SelModChars:
'      This value is ignored it SelStatement is empty.
'      D  = Allow duplicate column names. Not recommended if using slFN or slFNX.
'           because you will always get the first value returned.  SQLite does
'           not normally return qualified column names.  SQLite will return C1
'           twice if you Select T1.C1, T2.C1. So the solution is to alias one of
'           them with the As clause as follows Select T1.C1, T2.C1 as C1Again.
'           There is a Pragma called "full_column_names" which forces SQLite to
'           return qualified names, but does not seem to work if you Select *.
'           Read up on it and use if you like.  I like using an alias because it
'           is less code and more clear.
'      Em = Return errors. This will override the global return errors flag.
'           m is the optional message display modifier and can be:
'              0 = No message is displayed.  This is the default.
'              1 = Display a warning message with OK button.  Error is
'                  returned when OK pressed.
'              2 = Display a question message with OK and Cancel buttons.
'                  If they press OK, error is returned.  If they press
'                  Cancel, will exit process.
'      e  = Do not return errors, display message and exit process. This
'           will override the global return errors flag.
'      Bn = Do a Begin Transaction before doing the Sel command. The type
'           of Begin is controlled by the value of n as follows:
'              0 = Deferred. This is the default if n is omitted.
'              1 = Immediate.
'              2 = Exclusive.
'           This allows for database locking and selecting in one trip to the server.
'           CAUTION: If the Begin or the Select returns Busy then will restart with
'                    the Begin.  Use Begin Immediate to prevent this or set ProcessMods
'                    to %gbfDoNotRetryIfBusy.
'      R =  Release all named locks owned by this connection after doing
'           the Sel.
'   ReturnCode:
'      Zero if locking was successful. If locking failed then depending on
'      ModChars and the global return errors flag, will either display error
'      and exit or will return %SQLitening_LockTimeout.  If locking failed then
'      no locks are set nor are any released and no Sel is run. If locking was
'      successful and there is an optional SelStatement then will return zero if
'      processed OK. Else, depending on ModChars and the global return errors flag,
'      will either display error and exit or will return the error number.
'   Examples:  (No error checking shown)
'      slSetRelNamedLocks("+Cust1395" & $VT & +"Cust40928")  --  Will set two named locks.
'      slSetRelNamedLocks("+Cust1395" & $VT & "-Cust40928")  --  Will set one named locks and release another.
'      slSetRelNamedLocks("-Cust1395")  --  Will release one named locks.
'      slSetRelNamedLocks("+Cust1395", "T15000R", "Select * from Cust where Num=1395")
'          Will first release all locks for this connection then set one named lock,
'          waiting up to 15 seconds. If lock was set OK then will select the customer.
'      slSetRelNamedLocks("", "R")   --  Will release all locks for this connection.
'      slSetRelNamedLocks("")   --  Will do nothing.



           **** Get Status ****
This new function will return different data depending on the passed request.

'===========================<[ Get Status ]>===========================
Function slGetStatus alias "slGetStatus" (byval rlRequest as Long)Export as String
'   Returns the requested status which is normally a $NUL delimited list.
'   If an error occurs then will return an empty string.  You may call
'   slGetError or slGetErrorNumber to determine the reason.
'   The following requests are currently valid:
'      1 = Return all named locks.  Will always return an empty string
'          if running in local mode.  Each named lock will be delimited
'          by vertical tabs ($VT). Lock data will be delimited by
'          backspaces ($BS) as follows:
'              1 = Tcp file number that owns the lock
'              2 = Lock value
'              3 = Status (0 = Locked, <>0 = Waiting)
'              4 = Time lock set (Milliseconds after midnight)
'              5 = User
'              6 = Computer
'              7 = IPAddress
'              8 = Connect Date-Time
'      2 = Return all connections.  Will always return an empty string
'          if running in local mode.  Each connection will be delimited
'          by vertical tabs ($VT).  Connection data will be delimited by
'          backspaces ($BS) as follows:
'              1 = Tcp file number
'              2 = User
'              3 = Computer
'              4 = IPAddress
'              5 = Connect Date-Time
'      3 = Return SQLitening flag settings.  Will return a comma delimited
'          list of one or zero for each flag as follows:
'              1 = One if AreRunningRemote is on.
'              2 = One if AreConnected is on.
'              3 = One if ReturnAllErrors is on.
'              4 = One if DisplayErrorMessage is on.
'              5 = One if DisplayErrorMessageQuestion is on.
'              6 = One if DoNotRetryIfBusy is on.



           **** SQLiteningServerAdmin Tools ****
There is a new dialog available which allows for the following tools:

QuoteRefresh the Config Flags and FACT
         List all Active Connectons
         Kill one Active Connection
You already have the ability to "Refresh" but the other two are new.


           **** Other Important Changes ****
QuoteChange SQLiteningServer.Exe to timeout a connection after 30 minutes of
    no activity.  You can do an empty string slExe if you need to keep a
    connection active.  Error %SQLitening_SendOrReceiveError will occur if
    you attempt to use a connection that has timed out.

   Added the following ModChars to slSel, slSelAry:
      Bn = Do a Begin Transaction before doing the Sel command. The type
           of Begin is controlled by the value of n as follows:
              0 = Deferred. This is the default if n is omitted.
              1 = Immediate.
              2 = Exclusive.
           This allows for database locking and selecting in one trip to the server.


Your feed back is welcome.



Title: Re: Preview 1.3
Post by: Paul Squires on October 22, 2009, 01:03:50 PM
Hi Fred,

It all looks perfect to me. I especially like the approach to Named Locks (I learned something new... i didn't know the industry had such a term already in use). I think that the Named Locks approach is robust and efficient - especially since you can add locks, remove locks, and even retrieve a SELECT, all in one function call. Very cool.

One question: Is the connection timeout value hard coded, or will it be part of the server config file? I expect that many users will want to be able to use values different than 30 minutes. You know how picky users can be. :D
Title: Re: Preview 1.3
Post by: Fred Meier on October 22, 2009, 04:34:56 PM
The timeout value will default to 30 minutes, changeable in the config file.