Can a Base macro initiate a backup of its entire .odb file?

[hbquikcomjamesl]

It would be nice if I had a way, in the year-end processing of my “Charities” database, to automatically create a backup of the entire ODB file, both for archive purposes, and for “oops-recovery” purposes (since the year-end processing is irreversible).

The database is HSQLDB, so the ODB file is entirely self-contained, as evidenced by the fact that I was able to post an emptied-out copy with dummy records when I was having trouble with calculated totals showing up in the form

Can a LibreOffice BASIC macro initiate such an automated backup?

[Wanderer]

@hbquikcomjamesl

a way, in the year-end processing of my “Charities” database, to automatically create a backup of the entire ODB file

If this implies, you don’t have any backups during the year, I’d recommend to change this.

@hbquikcomjamesl

Can a LibreOffice BASIC macro do this?

Even, if possible, I don’t think it is a good idea. In my usual setups the .odb has not the data but only forms, reports and a recipe to connect to the real database.
.
If ,you use an embedded. database contents have to be unpacked for access, so actual data may not reside in the open odb-File.

[hbquikcomjamesl]

Original question updated.

[Wanderer]

When opening an embedded database, data is decompressed to a temporary file outside the odb. It is packed again, when you close. That’s the reason an embedded database is more likely to have problems, if a crash occurs during closing the file.
.
And as data is outside the file, you may not have current data, when you copy the odb (in some read-only manner).
.
IMHO, you can write a backup-macro, but it is not recommend to work on open files. So don’t place the marco to save it’s own file.
.
As there are lots of easier possibilities to get a backup of a .odb, I will not waste my time with this macro. And it is not easy to backup an open file:
https://help.libreoffice.org/latest/en-US/text/sbasic/shared/03020406.html
.
As a PS: there is already an extension for time-stamped backups, but as I remember, not recommended for Base.

[RobertG]

I have created such a macro a long time ago. It’s published in Base Guide. Here a version, which also works with something like external file databases. I use it for Firebird as external file database. Saves the content when opening the database in backup folder of LibreOffice.

SUB DatabaseStart
	DatabaseBackup(10)
END SUB


SUB DatabaseBackup(inMax AS INTEGER)
	DIM stTitel AS STRING
	DIM stUrl_Ziel AS STRING
	DIM stUrl_Start AS STRING
	stUrl_Start = ThisDatabaseDocument.DataSource.URL
	IF InStr(stUrl_Start, "file:/") THEN
		ar() = Split(stUrl_Start, "file:/")
		stUrl_Start = "file:/" & ar(1)
		ar1() = Split(stUrl_Start,"/")
		stTitel = ar1(UBound(ar1))
		stUrl_Ziel = BackupTarget(stTitel,inMax)
		FileCopy(stUrl_Start,stUrl_Ziel)	
	END IF	
	oDoc = ThisComponent
	stTitel = oDoc.Title
	stUrl_Start = oDoc.URL
	DO WHILE stUrl_Start = ""	
		oDoc = oDoc.Parent
		stTitel = oDoc.Title
		stUrl_Start = oDoc.URL
	LOOP
	stUrl_Ziel = BackupTarget(stTitel,inMax)
	FileCopy(stUrl_Start,stUrl_Ziel)	
END SUB

FUNCTION BackupTarget(stTitel AS STRING, inMax AS INTEGER) AS STRING
	DIM oPath AS OBJECT
	DIM i AS INTEGER
	DIM k AS INTEGER
	oPath = createUnoService("com.sun.star.util.PathSettings")
	MkDir(oPath.Backup)
	FOR i = 1 TO inMax + 1
		IF NOT FileExists(oPath.Backup & "/" & i & "_" & stTitel) THEN
			IF i > inMax THEN
				FOR k = inMax - 1 TO 1 STEP -1
					IF FileDateTime(oPath.Backup & "/" & k & "_" & stTitel) <=  FileDateTime(oPath.Backup & "/" & k+1 & "_" & stTitel) THEN
						IF k = 1 THEN
							i = k
							EXIT FOR
						END IF
					ELSE
						i = k+1
						EXIT FOR
					END IF
				NEXT
			END IF
			EXIT FOR
		END IF
	NEXT
	BackupTarget = oPath.Backup & "/" & i &"_" & stTitel
END FUNCTION

Have deleted all the German comments.
Procedure “DatabaseStart” has to be connected to the database document - event “open document”. It will a backup every time the *.odb-file will be opened. Number of the backups is ‘10’ - could be changed. If ‘10’ is reached it will take the oldest file and will overwrite this file.

[hbquikcomjamesl]

I like it.

It addresses the concerns raised by @Wanderer (which were concerns I had as well), and I was also able to roll @Renel’s solution to my automatic form-open question into the DatabaseStart sub:

ThisDatabaseDocument.CurrentController.connect("","")
ThisDatabaseDocument.FormDocuments.getByName("Name of the form").open

My original concern, though, was to do a separate automatic backup, to a user-selected name, right before end-of-year processing, which does a lot of things that can’t be undone.

The end-of-year process for my “Charities” database is as follows:

Given that each organization’s record has seven sets of date, note, and amount, as well as “most recent date” and “total amount” fields for last year, and for prior to last year,

1. A message box is brought up for confirmation.
2. The date of the most recent contribution from last year (if any) is copied to the date of the most recent contribution prior to last year, for each organization’s record
3. The date of the most recent contribution in the current year is copied to the date of the most recent contribution from last year, for each organization’s record.
4. The total for last year is added to the total prior to last year, for each organization.
5. The calculated total for the current year is copied to the total for last year, for each organization.
6. The seven date, note, and amount fields are nulled out for each organization.

[Renel]

@RobertG,
.
I find this procedure and function really interesting. An almost essential utility.
.
The .odb file is backed up in the AppData/Roaming/LibreOffice/4/user/backup system folder.
.
Could these macros be modified to ask the user where they would like to run this backup. Does this possibility exist with LibreOffice Base?

[RobertG]

@RobertG

oPath = createUnoService("com.sun.star.util.PathSettings")
MkDir(oPath.Backup)

Here is defined the path. Path could be set in Tools → Options → LibreOffice → Paths.

You could define any other path instead of oPath.Backup. Have a look at the help, special ConvertToURL(filename). Write the path as it is needed for your system and convert it.

[Renel]

Thanks @RobertG!
.
I never thought it was that simple. The path defined in the LibreOffice options suits me perfectly. I’ll still see about ConvertToURL(Filename).