Ask Your Question
0

how to split a firebird embedded database

asked 2020-12-19 09:47:07 +0100

parsely gravatar image

updated 2020-12-20 13:50:35 +0100

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?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2020-12-19 19:52:28 +0100

Ratslinger gravatar image

updated 2020-12-19 20:01:30 +0100

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?

edit flag offensive delete link more

Comments

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 /

parsely gravatar imageparsely ( 2020-12-20 13:36:31 +0100 )edit

@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.

Ratslinger gravatar imageRatslinger ( 2020-12-20 17:52:44 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-12-19 09:47:07 +0100

Seen: 81 times

Last updated: Dec 20 '20