After trying out Firebird initially, I figured I’d might as well use it for a proper project, namely, converting Komorebi, the small linklog engine I use for my Inklings linklog from using SQLite to FirebirdSQL. This proved to be a mildly awkward. Now, I don’t blame Firebird for much of this. There are certainly some quirks that reveal its origins, but mostly these are just things that are different from the likes of MySQL/MariaDB, PostgreSQL, and SQLite rather than things that are wrong or bad.
I’m not going to do into detail on the code changes: you can read the PR for that. Mostly, it was just code cleanup and expected changes from the fact that the time fields could now be assigned a proper data type. I did hit some odd quirks when converting it though.
UNIQUE constrants and page sizes
First had do to with the UNIQUE constraint. I’d expected this to be implemented not as an index, but with something more akin to a Bloom filter for the first approximation to detect possible violations, but it turns out that assumption was incorrect. It’s an actual unique index. This, it turns out, has some unexpected interactions with the length of the field with the UNIQUE constraint and the page size you assign when first creating the database. If you’ve used UTF-8 (or any other encoding that can potentially take up to 4 bytes per codepoint) as the character set, then the maximum string length is limited to 509 characters if you use the default page size of 8192 bytes. The maximum key length is page_size / 4 - 9, and then if you’re using UTF-8, each codepoint is treated as if it could potentially take 4 bytes, so for a page size of 8192, you’re stuck a maximum length for UNIQUE VARCHAR columns of floor((8192 / 4 - 9) / 4) = 509, which was not what I expected at all. As there’s no way to post-hoc change the page size other than dumping and recreating the database with gbak, I figured the best thing was to drop and recreate the database:
CREATE DATABASE 'localhost:/var/db/firebird/komorebi.fdb' PAGE_SIZE = 16384 DEFAULT CHARACTER SET UTF8;
This at least let me increase the field length for link field to 1021, which is a little more reasonable. A better solution would probably to just treat URLs as ASCII and use Punycode for the domain name where necessary.
Altering field types
I made a mistake when originally configuring the links table by using TIMESTAMP rather than TIMESTAMP WITH TIME ZONE. Now, the former probably would’ve been just fine for my purposes, as just like the original SQLite database, but I also didn’t want to have to deal with naive datetime. It turned out not to be possible to change the type of the fields, even though the table was empty, and I had to drop the fields (and their related indices) before readding them. A little awkward. It would’ve been nice to have been able to change this so long as the table was empty.
Identity fields
IDENTITY fields bit me too. I specified the table index as:
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
I did it with GENERATED BY DEFAULT AS IDENTITY because I wanted to be able to import the existing dump of the SQLite database. Only because of me being still a bit MySQL-brained a decade of avoiding it where possible that I expected it to behave a bit more like its autoincrement fields. As soon as I put everything live, I realised I’d made a mistake as I started getting integrity errors when inserting entries. The fix was more or less this:
ALTER TABLE links ALTER COLUMN id RESTART WITH (SELECT MAX(id) + 1 FROM links);
Now, I’m not sure if that subselect is something that could be used in ALTER TABLE that basic methods is what fixed the issue.
Co-administrators
The SYSDBA user is special: it always has admin access over everything. However, if you create another admin user, it has to be granted the RDB$ADMIN role for each database individually. As I didn’t want to use SYSDBA when setting things up, instead using my own admin user, not realising this lead to a lot of frustrated attempts to get things working in the first place, but I eventually figured it out and granted by own user that role with GRANT RDB$ADMIN TO keith on the database. Which leads me to…
CREATE DATABASE
This bit me in a funny way, and it could just be a matter of me misreading documentation. When I first attempted to create the database, I wanted the user I’d created for the app to own it, so I attempted to do:
CREATE DATABASE 'localhost:/var/db/firebird/komorebi.fdb' USER 'komorebi' PASSWORD '*redacted*' PAGE_SIZE = 16384 DEFAULT CHARACTER SET UTF8;
Which lead to all kinds of permissions issues, even though I was doing it in isql as an admin user. Dropping the USER and PASSWORD clauses fixed this, but meant that the database was owned by the admin user I used. I suspect that using USER 'komorebi' ROLE RDB$ADMIN might’ve worked. I didn’t want to grant the komorebi user the ability to create databases, just ownership over this particular one.
Actual complaints
databases.conf is a somewhat outdated idea. It’s one of these things that would’ve made sense back in the InterBase days, but it makes significantly less sense these days. If you don’t specify a full path, it’ll create the database in whatever the temporary directory is by default. I think that, in addition to the DatabaseAccess setting, there should be a DefaultDatabaseDirectory setting. In my case, I would’ve made it /var/db/firebird, which would allow for the hiding of some implementation details in manner that doesn’t involve giving the daemon a kick because you now need to add an alias to databases.conf.
The error messages are not good. A lot of work should really be put into improving this aspect of Firebird. The issues I had with permissions took longer to resolve than they should’ve, and I kept hitting weird issues with GRANT where it’d give a parsing error and a token, which little indication of context beyond a position. Now, I’m not looking for Rust and Python levels of identifying exactly where the lexer got confused, but even showing some of the leading and trailing characters would’ve helped. Even beyond parsing errors, the errors are horribly terse. The issue I hit with the link field is a good example as while key size exceeds implementation restriction for index is certainly technically correct (the best kind of correct), it’s not helpful because there’s no explanation of why. Adding : database page size is 8196, restricting UTF8 VARCHAR indexes to 509 would’ve helped tremendously. A bonus would a a link to an actual webpage.
A more minor nit would be with isql: I really think that the -d flag should autoconnect you to a database if -extract isn’t specified.
Every time I used the gsec command (firstly because it’s the most convenient way to change the password of SYSDBA, and subsequently because CREATE USER wasn’t behaving as expected) it complained that it was deprecated, but didn’t specify an alternative. I assume the alternative was to use DDL via isql, but stating that in the deprecation message would be good.