Firebird database database driver internals

From LSDevLinux
Jump to: navigation, search

Notes needed to create setup for firebird DbAccess installer.

DbAccess Firebird database driver relies on the following settings:


Installing Firebird

Assuming Ubuntu Hardy.

install the firebird2.0-super package
Enter password for the SYSDBA user as:
inscrutable

IMPORTANT if you don't use the exact password then the DBAccess Database driver won't work.

Firebird settings:

UDF:

The following files need to placed in firebird UDF folder.

udf_GUID.dll, udf_GUID.sql udf_SIL.dll, udf_SIL.sql

Files:

BlankLangProj.fdb - created from firebirds version of newlangproj.sql

this needs to be placed in DistFiles\Templates (maybe other places?)

Users:

FWDeveloper careful

sysdba inscrutable


Creating FwDeveloper User

use gsec:

/usr/lib/firebird/2.0/bin/gsec -user SYSDBA -password inscrutable


# Add the FWDEVELOPER user
GSEC> add FWDEVELOPER -pw careful
#show the users to check it worked.
GSEC> display
#exit
GSEC> quit

System info database:

We may need to create a name lookup table: Table: DbAccess Table FbNameLookup

logical (text), physical (text)

This table would be needed to be created in a Master.fdb database.


As SQL server doesn't allow attaching multiple a database multiple times with different names, in reality this table will have a maximun of 1 row.

This is because SQL server can attach pysical database names as other names. EG. Attach CopyBlankLangProj as Dummy and hense forth reference the database a Dummy. This doesn't seem to be possible in firebird hence the need for the additional table. Attach add a row to the table, and detach removes a row from the table.


Create Database "Master.fdb"
CREATE TABLE FbNameLookup ( logical VARCHAR(255), physical VARCHAR(255) );

The Master.fdb should be created by DBAdmin automaticaly if it doesn't exist, so:

1. All DbAccess 'special' tables are created.

2. FWDeveloper automaticaly gets permistion to write to it.

Misc Issues

permistions

The Following observed issues may require some attention.

When running unit test

TestLangProj.fdb table didn't allow access to FWDeveloper user.

EXCEPTION_MESSAGES
CLASS$
FIELD$

As a tempory message increased priviliges to FWDeveloper.

This should properbly be done by the sql when creating BlankLangProj?


manually done by:

isql -user SYSDBA -password inscrutable
CONNECT "BlankLangProj.fdb";
GRANT ALL ON CLASS$ TO FWDEVELOPER;
GRANT ALL ON EXCEPTION_MESSAGES TO FWDEVELOPER;
GRANT ALL ON FIELD$ TO FWDEVELOPER;

database types

Class$

field ABSTRACT

CHAR(2) CHARACTER SET OCTETS Nullable

database shows NOT abstract as 3000 which is 0x30 == ascii '0'

database show abstract as 3100 which is 0x31 == ascii '1'

This behaviour is going to be left as it is, and the converstion to ComBool (int bool) has been done in the database driver.

inserting into class$

This statement generates the following error.

INSERT INTO Class$ ("ID", "MOD", "BASE", "ABSTRACT", "NAME") VALUES (0, 0, 0, 1, 'tom');

Concatenation overflow. Resulting string cannot exceed 32K in length.

-Concatenation overflow. Resulting string cannot exceed 32K in length.

-Concatenation overflow. Resulting string cannot exceed 32K in length. -Concatenation overflow. Resulting string cannot exceed 32K in length.

-Concatenation overflow. Resulting string cannot exceed 32K in length.

ect...

Investigate this


Running unit test suite

when running DbAccessFirebird test suite you will need to set the following env on unix. On windows these values are read from the registry.

Need to set $FW_ROOT to the directory containing the Templates directory. This Templates directory will contain BlankLangProj.fdb and (possibly) TestLangProj.fdb On unix firebird user must have rw permission on these files. $FW_ROOT will proberbly be .../FieldWorks/DistFiles/

Need to set $FW_ROOTCODE to ..../FieldWorks/DistFiles/

..../FieldWorks/DistFiles need to contain: BlankLangProj.fdb + GeneralTest.fdb


..../FieldWorks/DistFiles/Data need to contain: master.fdb Need to set $COMPONENTS_MAP_PATH to the directory containing components.map file.

problems with firebird 2.0 after rebooting on linux

firebird uses stuff in var run which gets cleans after reboot

added the following above start-stop-daemon line in the /etc/init.d/firebird??? start script

          if test ! -d /var/run/firebird/2.0; then
               mkdir -p /var/run/firebird/2.0
           fi
           chown -R firebird.firebird /var/run/firebird/2.0
           start-stop-daemon --start --quiet --oknodo --chuid $FBRunUser --pid $PIDFILE --exec $MANAGER -- $FB_OPTS > /dev/null 2>&1