README

English | Česky

Firebird driver for Nette Database, on top of PDO_Firebird.

Tested on Firebird 3, 4 and 5 with PHP 8.5. It works with Explorer and with phloxcz/entity-database.

⚠️ This package is under active development. The API is not yet stable.

Installation

composer require phloxcz/firebird-database

database:
    default:
        dsn: 'firebird:dbname=localhost/3050:/data/app.fdb;charset=UTF8'
        user: SYSDBA
        password: masterkey
        options:
            driverClass: Phlox\FirebirdDatabase\FirebirdDriver

Use charset=UTF8 even for a database in another character set. Firebird converts the text, and an API can't send anything but UTF-8 as JSON anyway.

What PDO_Firebird gets wrong, and what's done about it

An integer written to a column with a scale. PDO stores 6 in NUMERIC(9,2) as 0.06, silently, and price < 6 compares with 0.06. The driver binds integers as strings, which PDO converts correctly. This holds for code using $connection->getPdo()->prepare() as well.

Types of columns in a result. PDO only tells the length in bytes and the precision.

Column	Value
`SMALLINT`, `INTEGER`, `BIGINT`, `NUMERIC(x,0)`	`int`
`NUMERIC`, `DECIMAL` with decimals	`float`
`FLOAT`, `DOUBLE PRECISION`	`float`
`BOOLEAN`	`bool`
`DATE`, `TIMESTAMP`	`Nette\Utils\DateTime`
`TIME`	`string`, it has no date
`CHAR`, `VARCHAR`, `BLOB`	`string`

FLOAT and DOUBLE PRECISION can't be told by length from CHAR(4), CHAR(8) or a BLOB in a single-byte character set. So when such a value looks like a number, the column is looked up in the system tables, by the tables its query names, and the answer is remembered for the connection. A column that's text in any of those tables stays text, so '0123' keeps its zero.

An expression or a column under another name can't be looked up, and it's judged by its form: SUM(PRICE) giving 7.5 is a float, and '0123' stays a string, but so does a whole number like '5' from DOUBLE PRECISION. Cast it to NUMERIC in the query, or read it by its own name, if that matters.

Limits of Firebird and PDO

The ID of a new row. PDO_Firebird has no lastInsertId(). An insert with the key given returns its row, as usual. Without it, phloxcz/entity-database asks by RETURNING on its own, so its insert() gives the entity, whether the key comes from an identity column or from a trigger. Plain Nette Database returns the number of rows, so use RETURNING there to get the key:
```
$id = $connection->fetchField('INSERT INTO BOOKS (TITLE) VALUES (?) RETURNING ID', $title);
```
For the same reason, an identity column isn't reported as autoincrement, because Nette would ask PDO for the last ID after every insert. You can tell it by $column['vendor']['IDENTITY_TYPE'].
Several rows in one insert. Firebird has no multi-row VALUES, so insert([[...], [...]]) fails. Insert one row at a time.
A date with a time, to a DATE column. Firebird refuses it, even where other databases cut the time off. Midnight is written without a time, so a plain date works for both DATE and TIMESTAMP.
A TIME column reads as a string, and intervals aren't supported as values (\DateInterval).
Limit and offset use FIRST and SKIP, so a query has to begin with SELECT, UPDATE or DELETE, not WITH.
A parameter is as long as its column. Comparing VARCHAR(10) with a longer text fails, with "string right truncation". Cast it if a value can be longer: NAME = CAST(? AS VARCHAR(100)). phloxcz/api-query does this on its own.

Errors

UniqueConstraintViolationException, ForeignKeyConstraintViolationException, NotNullConstraintViolationException and ConnectionException come as they do on other databases, and anything else as DriverException.

Names

Firebird folds names that aren't quoted to uppercase, and Nette quotes what it writes. So $explorer->table('USERS') and $row->EMAIL are in uppercase unless a table was created with a quoted lowercase name. Entities generated by phloxcz/entity-database can use nicer names through rename: in entities.neon.

Native types are reported as they were declared, such as VARCHAR, NUMERIC, DOUBLE PRECISION and TIMESTAMP, so entities get the right PHP types. The raw values of the system tables are in $column['vendor'].

Migrating from Abra\FirebirdDatabase

Replace Abra\FirebirdDatabase\FirebirdDriver with Phlox\FirebirdDatabase\FirebirdDriver.

Changes that need checking in an existing project:

Integers in NUMERIC columns are now right. Rows written through PDO with a whole number before this may hold a hundredth of the value. Check columns with a scale which got whole numbers, such as UPDATE ... SET PRICE = ? with an int. Filters comparing them with a whole number start to find what they should.
Text that looks like a number stays text. In a single-byte character set, CHAR(4), CHAR(8) and BLOB used to turn into float, and a code like '0123' into 123.0.
Unique, foreign key and not-null errors have their own exceptions. They used to come as a plain DriverException, because the driver expected other codes than PDO reports.
nativetype is what was declared, like VARCHAR, and no longer what the system tables call it, like VARYING. Regenerate entities.
formatLike() now escapes properly, with ESCAPE.

Tests

The tests needing a server take it from TEST_DSN:

docker run -d -p 3055:3050 -e FIREBIRD_ROOT_PASSWORD=masterkey -e FIREBIRD_DATABASE=test.fdb -e FIREBIRD_DATABASE_DEFAULT_CHARSET=UTF8 firebirdsql/firebird:5
TEST_DSN='firebird:dbname=127.0.0.1/3055:/var/lib/firebird/data/test.fdb;charset=UTF8' composer test

License

MIT

phloxcz / firebird-database

Package info

Security

Statistics