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 <jp@ledure.be>
Tested-by: Jenkins

4 files changed, 337 insertions, 17 deletions

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 -----------------------------------------------------------------------------
@@ -140,19 +152,56 @@ 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
@@ -694,6 +746,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 _
 							) As Boolean
@@ -788,9 +879,188 @@ 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
 '''	depending on the formdocuments container in argument
@@ -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 _
@@ -968,6 +1238,34 @@ Catch:
 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 _
 									) As Variant
@@ -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		&apos; Array of updatable field names
 Private _DefaultValues		As Variant		&apos; Array of field default values // _Fields
 Private _AutoValue			As Long			&apos; Index of AutoValue field. None = -1
 
+Private _DatasetIndex		As Long			&apos; 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		&apos;	SFDatabases.SF_Dataset Constructor
 
 REM -----------------------------------------------------------------------------
@@ -280,6 +283,7 @@ Try:
 			.close()
 			.dispose()
 		End With
+		If _DatasetIndex &gt;= 0 Then Set _ParentDatabase._Datasets(_DatasetIndex) = Nothing
 		Dispose()
 		bClose = True
 	End If
@@ -1350,8 +1354,11 @@ Try:
 			If Len(sUpdatableFields) &lt;= 1 Then _UpdatableFields = Array() Else _UpdatableFields = Split(Mid(sUpdatableFields, 2), &quot;,&quot;)
 		End With
 	End With
+	
+	&apos;	Insert the instance in the _Datasets array of the parent database
+	_DatasetIndex = _ParentDatabase._AddToDatasets([Me])
 
-	bDataset = True
+	bDataset = ( _DatasetIndex &gt;= 0 )
 
 Finally:
 	_Initialize = bDataset
@@ -1661,4 +1668,4 @@ CatchError:
 End Function	&apos;	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