wizards/source/scriptforge/python/scriptforge.py | 9
wizards/source/sfdatabases/SF_Database.xba | 332 +++++++++++++++++++++--
wizards/source/sfdatabases/SF_Dataset.xba | 11
wizards/source/sfdatabases/SF_Register.xba | 2
4 files changed, 337 insertions(+), 17 deletions(-)
New commits:
commit 9b5b313a2f2980f9a10295aabdd696f58af03302
Author: Jean-Pierre Ledure <j...@ledure.be>
AuthorDate: Thu Nov 23 17:59:33 2023 +0100
Commit: Jean-Pierre Ledure <j...@ledure.be>
CommitDate: Fri Nov 24 09:54:59 2023 +0100
ScriptForge (SF_Database) manage transactions
Transactions are managed by next UNO properties:
XConnection.AutoCommit
XConnection.TransactionIsolation
(They seem very easy to use but, in practice,
are not easy at all)
Usually all transactions are in auto-commit mode,
that means, a commit takes place after each
single SQL command.
Switching auto-commit off means however that
the connection needs to be of type "ISOLATED"
and not of the default type "SHARED".
=> The usual shared connection must be closed.
As a consequence, all rowsets/resultsets linked
to the shared connection need to be closed as well.
=> Additionally the buffers must be emptied (flushed)
to make committed data visible in the Base user
interface.
All above aspects and constraints are managed in the
database.SetTransactionMode(transactionmode)
database.Commit()
database.Rollback()
methods transparently for the user scripts.
The [transactionmode] argument has as value one of
the TransactionIsolation constants.
Without argument, database.SetTransactionMode()
restores the automatic mode.
The manual transaction mode is available for both
Basic and Python scripts.
Its implementation will require an update of the
documentation about the Database service.
Change-Id: I214bd91a1744d6d24609bc5efc987152c6e946c9
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/159867
Reviewed-by: Jean-Pierre Ledure <j...@ledure.be>
Tested-by: Jenkins
diff --git a/wizards/source/scriptforge/python/scriptforge.py
b/wizards/source/scriptforge/python/scriptforge.py
index 77e0119809da..998d57d066a8 100644
--- a/wizards/source/scriptforge/python/scriptforge.py
+++ b/wizards/source/scriptforge/python/scriptforge.py
@@ -1794,6 +1794,9 @@ class SFDatabases:
def CloseDatabase(self):
return self.ExecMethod(self.vbMethod, 'CloseDatabase')
+ def Commit(self):
+ return self.ExecMethod(self.vbMethod, 'Commit')
+
def CreateDataset(self, sqlcommand, directsql = False, filter = '',
orderby = ''):
return self.ExecMethod(self.vbMethod, 'CreateDataset', sqlcommand,
directsql, filter, orderby)
@@ -1831,9 +1834,15 @@ class SFDatabases:
def OpenTable(self, tablename):
return self.ExecMethod(self.vbMethod, 'OpenTable', tablename)
+ def Rollback(self):
+ return self.ExecMethod(self.vbMethod, 'Rollback')
+
def RunSql(self, sqlcommand, directsql = False):
return self.ExecMethod(self.vbMethod, 'RunSql', sqlcommand,
directsql)
+ def SetTransactionMode(self, transactionmode = 0):
+ return self.ExecMethod(self.vbMethod, 'SetTransactionMode',
transactionmode)
+
# #########################################################################
# SF_Dataset CLASS
# #########################################################################
diff --git a/wizards/source/sfdatabases/SF_Database.xba
b/wizards/source/sfdatabases/SF_Database.xba
index cf970ea980fc..141a5bade393 100644
--- a/wizards/source/sfdatabases/SF_Database.xba
+++ b/wizards/source/sfdatabases/SF_Database.xba
@@ -25,6 +25,12 @@ Option Explicit
'''
''' The provided interfaces include simple tables,
queries and fields lists, and access to database metadata.
'''
+''' Tranaction handling
+''' Changes to data remain reversible until
the moment the running script instructs the database to commit them.
+''' The implicit (default) behaviour is
that commit takes place after the execution of every single SQL statement.
+''' The choice can be made
(SetTranactionMode()) to take commitments manually.
+''' The Commit() and Rollback() statements
delimit transactions.
+'''
''' Service invocation and usage:
''' 1) To access any database at anytime
''' Dim myDatabase As Object
@@ -67,6 +73,9 @@ Private _URL As
String ' Text on status bar
Private _Location As String '
File name
Private _ReadOnly As Boolean
Private _MetaData As Object
' com.sun.star.sdbc.XDatabaseMetaData
+Private _User As String '
Connection parameters to enable a reconnection
+Private _Password As String
+Private _Datasets As Variant '
Array of open datasets
REM ============================================================ MODULE
CONSTANTS
@@ -86,6 +95,9 @@ Private Sub Class_Initialize()
_Location = ""
_ReadOnly = True
Set _MetaData = Nothing
+ _User = ""
+ _Password = ""
+ _Datasets = Array()
End Sub ' SFDatabases.SF_Database Constructor
REM
-----------------------------------------------------------------------------
@@ -139,20 +151,57 @@ Const cstSubArgs = ""
Check:
ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs)
+Try:
+ _CloseConnection()
+ Dispose()
+
+Finally:
+ ScriptForge.SF_Utils._ExitFunction(cstThisSub)
+ Exit Sub
+End Sub ' SFDatabases.SF_Database.CloseDatabase
+
+REM
-----------------------------------------------------------------------------
+Public Sub Commit()
+''' Commit all updates done since the previous Commit or
Rollback
+''' The statement is ignored if the commits are done
automatically after each SQL statement.
+''' Args:
+''' Returns:
+''' Exceptions:
+''' DBREADONLYERROR The method is
not applicable on a read-only database
+''' Example:
+''' db.SetTransactionMode(4) '
Highest transaction level
+''' db.RunSql("UPDATE ...")
+''' db.Commit()
+''' db.RunSql(DELETE ...")
+''' If ...something happened... Then db.Rollback()
Else db.Commit()
+''' db.SetTransactionMode() '
Back to the automatic mode
+
+Const cstThisSub = "SFDatabases.Database.Commit"
+Const cstSubArgs = ""
+
+ On Local Error GoTo Finally
+
+Check:
+ ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs)
+ If _ReadOnly Then GoTo Catch_ReadOnly
+
Try:
With _Connection
- If Not IsNull(_Connection) Then
- If ScriptForge.SF_Session.HasUnoMethod(_Connection,
"flush") Then .flush()
- .close()
- .dispose()
+ If Not .AutoCommit Then
+ .commit()
+ ' To make updates potentially visible in the user
interface ...
+ _FlushConnection()
End If
- Dispose()
End With
Finally:
+ On Local Error GoTo 0
ScriptForge.SF_Utils._ExitFunction(cstThisSub)
Exit Sub
-End Sub
+Catch_ReadOnly:
+ ScriptForge.SF_Exception.RaiseFatal(DBREADONLYERROR)
+ GoTo Finally
+End Sub ' SFDatabases.SF_Database.Commit
REM
-----------------------------------------------------------------------------
Public Function CreateDataset(Optional ByVal SQLCommand As Variant _
@@ -497,6 +546,7 @@ Public Function Methods() As Variant
Methods = Array( _
"CloseDatabase" _
+ , "Commit" _
, "CreateDataset" _
, "DAvg" _
, "DCount" _
@@ -509,7 +559,9 @@ Public Function Methods() As Variant
, "OpenQuery" _
, "OpenSql" _
, "OpenTable" _
+ , "Rollback" _
, "RunSql" _
+ , "SetTransactionMode" _
)
End Function ' SFDatabases.SF_Database.Methods
@@ -602,7 +654,7 @@ Finally:
Exit Function
Catch:
GoTo Finally
-End Function ' SFDocuments.SF_Base.OpenQuery
+End Function 'SFDatabases.SF_Database.OpenQuery
REM
-----------------------------------------------------------------------------
Public Function OpenSql(Optional ByRef Sql As Variant _
@@ -642,7 +694,7 @@ Finally:
Exit Function
Catch:
GoTo Finally
-End Function ' SFDocuments.SF_Base.OpenSql
+End Function ' SFDatabases.SF_Database.OpenSql
REM
-----------------------------------------------------------------------------
Public Function OpenTable(Optional ByVal TableName As Variant) As Object
@@ -678,7 +730,7 @@ Finally:
Exit Function
Catch:
GoTo Finally
-End Function ' SFDocuments.SF_Base.OpenTable
+End Function ' SFDatabases.SF_Database.OpenTable
REM
-----------------------------------------------------------------------------
Public Function Properties() As Variant
@@ -693,6 +745,45 @@ Public Function Properties() As Variant
End Function ' SFDatabases.SF_Database.Properties
+REM
-----------------------------------------------------------------------------
+Public Sub Rollback()
+''' Cancel all updates done since the previous Commit or
Rollback
+''' The statement is ignored if the commits are done
automatically after each SQL statement.
+''' Args:
+''' Returns:
+''' Exceptions:
+''' DBREADONLYERROR The method is
not applicable on a read-only database
+''' Example:
+''' db.SetTransactionMode(4) '
Highest transaction level
+''' db.RunSql("UPDATE ...")
+''' db.Commit()
+''' db.RunSql(DELETE ...")
+''' If ...something happened... Then db.Rollback()
Else db.Commit()
+''' db.SetTransactionMode() '
Back to the automatic mode
+
+Const cstThisSub = "SFDatabases.Database.Rollback"
+Const cstSubArgs = ""
+
+ On Local Error GoTo Finally
+
+Check:
+ ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs)
+ If _ReadOnly Then GoTo Catch_ReadOnly
+
+Try:
+ With _Connection
+ If Not .AutoCommit Then .rollback()
+ End With
+
+Finally:
+ On Local Error GoTo 0
+ ScriptForge.SF_Utils._ExitFunction(cstThisSub)
+ Exit Sub
+Catch_ReadOnly:
+ ScriptForge.SF_Exception.RaiseFatal(DBREADONLYERROR)
+ GoTo Finally
+End Sub ' SFDatabases.SF_Database.Rollback
+
REM
-----------------------------------------------------------------------------
Public Function RunSql(Optional ByVal SQLCommand As Variant _
, Optional ByVal
DirectSQL As Variant _
@@ -788,8 +879,187 @@ Catch:
GoTo Finally
End Function ' SFDatabases.SF_Database.SetProperty
+REM
-----------------------------------------------------------------------------
+Public Function SetTransactionMode(Optional ByVal TransactionMode As Variant)
As Boolean
+''' Configure the handling of transactions.
+''' Usually all transactions are in auto-commit mode, that
means, a commit takes place
+''' after each single SQL command. Therefore to control a
transaction manually, implies to switch auto-commit off.
+''' The first SQL command starts a transaction that is
active until the corresponding
+''' methods have been committed or rolled back.
+'''
+''' The transaction mode remains valid until the next call
of the method with a different value,
+''' or until the closure of the actual Database instance,
+''' or until a call to SetTransactionMode() without
argument, which cancels the manual transaction mode.
+'''
+''' The method may close and replace the actual
connection. This means that all open datasets
+''' are first closed. Open datasheets might see their
content changed or vanish.
+''' The easiest is to set the transaction mode immediately
after the creation of the Database instance.
+'''
+''' Args:
+''' TransactionMode: one of the
com.sun.star.sdbc.TransactionIsolation constants:
+''' (0) NONE
Indicates that transactions are not supported. Default: cancel the transaction
mode.
+''' (1) READ_UNCOMMITTED Dirty reads,
non-repeatable reads and phantom reads can occur.
+'''
This level allows a row changed by one transaction to be read by
another transaction
+'''
before any changes in that row have been committed (a "dirty
read").
+'''
If any of the changes are rolled back, the second transaction will have
retrieved an invalid row.
+''' (2) READ_COMMITTED Dirty
reads are prevented; non-repeatable reads and phantom reads can occur.
+'''
This level only prohibits a transaction from reading a row with
uncommitted changes in it.
+''' (4) REPEATABLE_READ Dirty
reads and non-repeatable reads are prevented; phantom reads can occur.
+'''
This level prohibits a transaction from reading a row with uncommitted
changes in it,
+'''
and it also prohibits the situation where one transaction reads a row,
+'''
a second transaction alters the row, and the first transaction rereads
the row,
+'''
getting different values the second time (a "non-repeatable
read").
+''' (8) SERIALIZABLE Dirty
reads, non-repeatable reads and phantom reads are prevented.
+'''
This level includes the prohibitions in REPEATABLE_READ and further
prohibits
+'''
the situation where one transaction reads all rows that satisfy a WHERE
condition,
+'''
a second transaction inserts a row that satisfies that WHERE condition,
+'''
and the first transaction rereads for the same condition, retrieving
+'''
the additional "phantom" row in the second read.
+''' Returns:
+''' True when successful.
+''' Exceptions:
+''' DBREADONLYERROR The method is
not applicable on a read-only database
+''' Example:
+'''
oDb.SetTransactionMode(com.sun.star.sdbc.TransactionIsolation.SERIALIZABLE)
' 8
+
+Dim bSet As Boolean ' Return value
+Dim bCommit As Boolean ' To compare with AutoCommit
+Const cstThisSub = "SFDatabases.Database.SetTransactionMode"
+Const cstSubArgs = "TransactionMode=0|1|2|4|8"
+
+ If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
+ bSet = False
+
+Check:
+ If IsMissing(TransactionMode) Or IsEmpty(TransactionMode) Then
TransactionMode = 0
+ If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
+ If Not ScriptForge.SF_Utils._Validate(TransactionMode,
"TransactionMode", ScriptForge.V_NUMERIC, Array(0, 1, 2, 4, 8)) Then
GoTo Finally
+ End If
+ If _ReadOnly Then GoTo Catch_ReadOnly
+
+Try:
+ With _Connection
+ bCommit = ( TransactionMode >
com.sun.star.sdbc.TransactionIsolation.NONE )
+ ' Replace the existing connection
+ If Not IsNull(_Connection) Then
+ If .AutoCommit And bCommit Then
+ _CloseConnection()
+ Set _Connection =
_DataSource.getIsolatedConnection(_User, _Password)
+ ElseIf Not .AutoCommit And Not bCommit Then
+ _CloseConnection()
+ Set _Connection =
_DataSource.getConnection(_User, _Password)
+ End If
+ End If
+
+ ' Set the transaction mode
+ If bCommit Then
+ .SetTransactionIsolation(CLng(TransactionMode))
+ .setAutoCommit(Not bCommit)
+ End If
+ End With
+
+ bSet = True
+
+Finally:
+ SetTransactionMode = bSet
+ ScriptForge.SF_Utils._ExitFunction(cstThisSub)
+ Exit Function
+Catch:
+ GoTo Finally
+Catch_ReadOnly:
+ ScriptForge.SF_Exception.RaiseFatal(DBREADONLYERROR)
+ GoTo Finally
+End Function ' SFDatabases.SF_Database.SetTransactionMode
+
REM =========================================================== PRIVATE
FUNCTIONS
+REM
-----------------------------------------------------------------------------
+Public Function _AddToDatasets(ByRef poDataset As Object) As Long
+''' Insert a newly created Dataset instance in the open
datasets array
+''' and return the index of the used entry.
+''' Empty space is reused.
+''' Args:
+''' poDataset: the dataset instance to insert
+
+Dim lIndex As Long ' Return value
+Dim lSize As Long ' UBound of the _datasets array
+Dim i As Long
+
+Check:
+ lIndex = -1
+ If IsNull(poDataset) Then Exit Function
+ On Local Error GoTo Finally
+ lSize = UBound(_Datasets)
+
+Try:
+ ' Can an empty entry be reused ?
+ For i = 0 To lSize
+ If IsNull(_Datasets(i)) Then
+ lIndex = i
+ Exit For
+ End If
+ Next i
+
+ ' Resize _Datasets if no empty space
+ If lIndex < 0 Then
+ lSize = lSize + 1
+ If lSize > 0 Then
+ ReDim Preserve _Datasets(0 To lSize)
+ Else
+ ReDim _Datasets (0 To 0)
+ End If
+ lIndex = lSize
+ End If
+
+ ' Insert new object
+ Set _Datasets(lIndex) = poDataset
+
+Finally:
+ _AddToDatasets = lIndex
+ Exit Function
+End Function ' SFDatabases.SF_Database._AddToDatasets
+
+REM
-----------------------------------------------------------------------------
+Private Sub _CloseConnection()
+''' Close the actual connection
+''' To enable transaction modes, it is necessary to
reinitialize the connection to the datasource.
+''' The reinit includes
+''' - the closure of all open datasets
+''' - flushing the buffered and committed updates
+''' - the effective closure
+''' Otherwise the experience showed undesired side-effects.
+
+Dim oParent As Object ' Parent of actual connection
+Dim oDataset As Object ' Single dataset in the _Datasets
array
+Dim oSession As Object : Set oSession =
ScriptForge.SF_Session
+Dim i As Long
+
+ On Local Error GoTo Finally ' Never abort
+
+Check:
+ If IsNull(_Connection) Then Exit Sub
+
+Try:
+ ' Close datasets
+ For i = 0 To UBound(_Datasets)
+ Set oDataset = _Datasets(i)
+ If Not IsNull(oDataset) Then oDataset.CloseDataset()
+ Set _Datasets(i) = Nothing
+ Next i
+ _Datasets = Array()
+
+ ' Flush buffers
+ _FlushConnection()
+
+ ' Close the connection
+ _Connection.close()
+ _Connection.dispose()
+
+Finally:
+ On Local Error GoTo 0
+ Exit Sub
+End Sub ' SFDatabases.SF_Database._CloseConnection
+
REM
-----------------------------------------------------------------------------
Private Function _CollectFormDocuments(ByRef poContainer As Object) As String
''' Returns a token-separated string of all hierarchical
formdocument names
@@ -826,7 +1096,7 @@ Finally:
_CollectFormDocuments = ""
End If
Exit Function
-End Function ' SFDocuments.SF_Base._CollectFormDocuments
+End Function ' SFDatabases.SF_Database._CollectFormDocuments
REM
-----------------------------------------------------------------------------------------------------------------------
Private Function _DFunction(ByVal psFunction As String _
@@ -967,6 +1237,34 @@ Catch:
GoTo Finally
End Function ' SFDatabases.SF_Database._ExecuteSql
+REM
-----------------------------------------------------------------------------
+Private Sub _FlushConnection()
+''' Empties the buffers of the actual connection
+''' Sub called after each commit and at connection closure..
+
+Dim oParent As Object ' Parent of actual connection
+Dim oSession As Object : Set oSession =
ScriptForge.SF_Session
+
+ On Local Error GoTo Finally ' Never abort
+
+Check:
+ If IsNull(_Connection) Then Exit Sub
+
+Try:
+ ' Flush buffers
+ With oSession
+ If .HasUnoMethod(_Connection, "getParent") Then
+ Set oParent = _Connection.getParent()
+ If .HasUnoMethod(oParent, "flush") Then
oParent.flush()
+ ElseIf .HasUnoMethod(_Connection, "flush") Then
+ _Connection.flush()
+ End If
+ End With
+
+Finally:
+ Exit Sub
+End Sub ' SFDatabases.SF_Database._FlushConnection
+
REM
-----------------------------------------------------------------------------
Private Function _GetColumnValue(ByRef poResultSet As Object _
, ByVal
plColIndex As Long _
@@ -1003,13 +1301,17 @@ Const cstMaxBinlength = 2 * 65535
Case .ARRAY
: vValue = poResultSet.getArray(plColIndex)
Case .BINARY, .VARBINARY, .LONGVARBINARY, .BLOB
Set oStream =
poResultSet.getBinaryStream(plColIndex)
- If bNullable Then
- If Not poResultSet.wasNull() Then lSize
= CLng(oStream.getLength()) Else lSize = 0
+ If IsNull(oStream) Then
+ lSize = 0
Else
- lSize = CLng(oStream.getLength())
+ If bNullable Then
+ If Not poResultSet.wasNull()
Then lSize = CLng(oStream.getLength()) Else lSize = 0
+ Else
+ lSize =
CLng(oStream.getLength())
+ End If
+ oStream.closeInput()
End If
vValue = lSize ' Return length of field,
not content
- If Not IsNull(oStream) Then oStream.closeInput()
Case .BIT, .BOOLEAN :
vValue = poResultSet.getBoolean(plColIndex)
Case .DATE
vDateTime = poResultSet.getDate(plColIndex)
@@ -1170,4 +1472,4 @@ Private Function _Repr() As String
End Function ' SFDatabases.SF_Database._Repr
REM ============================================ END OF SFDATABASES.SF_DATABASE
-</script:module>
+</script:module>
\ No newline at end of file
diff --git a/wizards/source/sfdatabases/SF_Dataset.xba
b/wizards/source/sfdatabases/SF_Dataset.xba
index 28091d9368b8..e9eb050d93f0 100644
--- a/wizards/source/sfdatabases/SF_Dataset.xba
+++ b/wizards/source/sfdatabases/SF_Dataset.xba
@@ -106,6 +106,8 @@ Private _UpdatableFields As Variant '
Array of updatable field names
Private _DefaultValues As Variant ' Array of field
default values // _Fields
Private _AutoValue As Long ' Index of
AutoValue field. None = -1
+Private _DatasetIndex As Long ' Index of the
dataset in the _Datasets array of the parent database
+
REM ============================================================ MODULE
CONSTANTS
REM ======================================================
CONSTRUCTOR/DESTRUCTOR
@@ -127,6 +129,7 @@ Private Sub Class_Initialize()
_UpdatableFields = Array()
_DefaultValues = Array()
_AutoValue = -1
+ _DatasetIndex = -1
End Sub ' SFDatabases.SF_Dataset Constructor
REM
-----------------------------------------------------------------------------
@@ -280,6 +283,7 @@ Try:
.close()
.dispose()
End With
+ If _DatasetIndex >= 0 Then Set
_ParentDatabase._Datasets(_DatasetIndex) = Nothing
Dispose()
bClose = True
End If
@@ -1350,8 +1354,11 @@ Try:
If Len(sUpdatableFields) <= 1 Then _UpdatableFields
= Array() Else _UpdatableFields = Split(Mid(sUpdatableFields, 2), ",")
End With
End With
+
+ ' Insert the instance in the _Datasets array of the parent
database
+ _DatasetIndex = _ParentDatabase._AddToDatasets([Me])
- bDataset = True
+ bDataset = ( _DatasetIndex >= 0 )
Finally:
_Initialize = bDataset
@@ -1661,4 +1668,4 @@ CatchError:
End Function ' SFDatabases.SF_Dataset._SetColumnValue
REM ============================================ END OF SFDATABASES.SF_DATASET
-</script:module>
+</script:module>
\ No newline at end of file
diff --git a/wizards/source/sfdatabases/SF_Register.xba
b/wizards/source/sfdatabases/SF_Register.xba
index cee09f94f3f3..e1b752f7f107 100644
--- a/wizards/source/sfdatabases/SF_Register.xba
+++ b/wizards/source/sfdatabases/SF_Register.xba
@@ -121,6 +121,8 @@ Try:
If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error
GoTo CatchConnect
Set ._Connection = ._DataSource.getConnection(vUser, vPassword)
If IsNull(._Connection) Then GoTo CatchConnect
+ ._User = vUser
+ ._Password = vPassword
._ReadOnly = vReadOnly
Set ._MetaData = ._Connection.MetaData
._URL = ._MetaData.URL
