How to split a firebird embedded database

[parsely]

There are technical considerations to separate the data from all other things to manipulate the data (forms-query-macro-reports). A practical considerations arises when f.i. you run the financial administration for some legal identities.

In the past splitting of HSQLDB bases has been recommended. Auto migration to firebird leaves you with an embedded FB database.To split that i followed the scheme:

new database > connect to existing > create new ! > enter a dbBackEnd name > register Yes > save as dbFrontend name. ( this creates a dbBackEnd.fdb and a registered dbFrontEnd.odb file )

open the embedded and new dbFrontEnd side by side

drop and drag all tables to the dbFrontEnd

this gives rise to the known issue “identity violation”, because during drop and drag of filled tables the autonumbering field is not updated.

with Create Query in SQL View a list of tables and names can be obtained,

SELECT “RDB$RELATION_FIELDS”.“RDB$RELATION_NAME”, “RDB$RELATION_FIELDS”.“RDB$FIELD_NAME”, “RDB$RELATION_FIELDS”.“RDB$GENERATOR_NAME” FROM “RDB$RELATION_FIELDS” JOIN “RDB$RELATIONS” ON “RDB$RELATION_FIELDS”.“RDB$RELATION_NAME” = “RDB$RELATIONS”.“RDB$RELATION_NAME” AND “RDB$RELATIONS”.“RDB$VIEW_BLR” IS NULL AND ( “RDB$RELATIONS”.“RDB$SYSTEM_FLAG” IS NULL OR “RDB$RELATIONS”.“RDB$SYSTEM_FLAG” = 0 ) AND NOT ( “RDB$GENERATOR_NAME” IS NULL ) ORDER BY “RDB$GENERATOR_NAME”

The autonumbering field can be updated With

SELECT MAX("ID") FROM "TABLENAME"
Observe NUMBER in output field. 
ALTER TABLE "TABLENAME" ALTER "ID" RESTART WITH NUMBER 

( I could not get this going in a macro only in the TOOLS>SQL window )


Following code can be used to connect to another BackEnd.fdb data content base

Sub linkToOtherBackEnd()

        Dim  sName as string
	Dim filePicker As Object
	Dim FPtype(0) As Integer

	FilePicker = CreateUnoService("com.sun.star.ui.dialogs.FilePicker")
	FPtype(0)=com.sun.star.ui.dialogs.TemplateDescription.FILEOPEN_LINK_PREVIEW_IMAGE_TEMPLATE 'FILEOPEN_SIMPLE
	FilePicker.initialize(FPtype())

	If FilePicker.execute() Then
	   sName =  "sdbc:firebird:" & ConvertToURL(FilePicker.Files(0))
	EndIf

	ThisDatabaseDocument.DataSource.URL = sName

End Sub

Are there easier methods to split an embedded base?

[Ratslinger]

Hello,

This method is done on Ubuntu 20.04 and different OS’s will have their variants.

You should always make a copy for backup in case of problems.

Extract a copy of the firebird.fbk file. Unzip the .odb (may need to rename to xxx.zip). File is in database folder. You can also do this with a macro (run from document):

Option Explicit

Sub FBsave
  Dim oFileAccess As Variant
  Dim oDocumentSubStorage As Variant
  Dim oElementNames As Variant
  Dim oObj1 As Variant
  Dim oInputStream As Variant
  Dim sPath As String
	oFileAccess = createUnoService("com.sun.star.ucb.SimpleFileAccess")
    oDocumentSubStorage = ThisComponent.getDocumentSubStorage("database", 1)
    oElementNames = oDocumentSubStorage.getElementNames()
    oObj1 = oDocumentSubStorage.getByName("firebird.fbk")
    oInputStream = oObj1.getInputStream()
    sPath = "ENTER_YOUR_SAVE_PATH/DB_NAME_WANTED.fbk"
    oFileAccess.WriteFile(sPath, oInputStream)
    oInputStream.closeInput()
End Sub

Make sure to change location of where file is saved.

Since this file is a backup file of the database, you need to restore it. For this use the Firebird utility gbak (not included with LO Base - get from server package or Firebird utilities).

Restore with terminal command (run from directory backup file is stored in):

sudo gbak -c -v -user SYSDBA firebird.fbk firebird.fdb

Change permissions of firebird.fdb - will be currently set to root.

Change main content.xml file in Base. Change from:

href="sbdc:embedded:firebird"

To:

href="sdbc:firebird:file://YOUR_LOCATION/DB_NAME_USED.fdb"

Save the change and test. If all OK, you should be able to delete the embedded version to reduce file size.

You can also make this more portable with a macro. For that, see answer in this post → How can I create a portable split embedded/local firebird database in the manner that is done with HSQLDB?

[parsely]

thanks Ratslinger. I did see the other post, but it was a bit unrecognizable to me due to the term portable and server, this worked out all right for me.

Indeed my OS is LinuxMint 20 <> Ubuntu 20, for this OS:


sudo /usr/bin/gbak -c -v -user SYSDBA firebird.fbk firebird.fdb

sudo chown username:username firebird.fdb to change root settings

href=“sdbc:firebird:file:///home/username/firebird.fdb” so three times /

[Ratslinger]

@parsely,

With some of the related links, you may start to notice that the Firebird database (fdb or backup fbk) is used in the server, embedded and as a freestanding (unfortunately no real security here) accessible DB. I originally used a server DB to convert a personal financial system to Firebird embedded. This saved many headaches with data conversion problems which some are still obvious today. It used to be much worse.