Tables

The Table class in Weasel.Firebird.Tables provides a fluent API for defining Firebird tables with columns, primary keys, foreign keys, and indexes.

Defining a Table

var table = new Table("users");

table.AddColumn<int>("id").AsPrimaryKey().AutoIncrement();
table.AddColumn<string>("name").NotNull();
table.AddColumn<string>("email").NotNull().AddIndex(idx => idx.IsUnique = true);
table.AddColumn<DateTime>("created_at");

^{snippet source | anchor}

A name without a schema lives in PUBLIC, the only schema Firebird 3, 4 and 5 have. See No schemas before Firebird 6.

Column Configuration

The AddColumn method returns a ColumnExpression with these options:

• AsPrimaryKey() -- marks the column as part of the primary key
• AutoIncrement() -- makes it an identity column: GENERATED BY DEFAULT AS IDENTITY NOT NULL
• NotNull() / AllowNulls() -- controls nullability
• DefaultValue(value) / DefaultValueByString(value) -- sets a default value
• DefaultValueByExpression(expr) -- sets a default using a SQL expression
• AddIndex(configure?) -- adds an index on this column, named idx_{table}_{column}
• ForeignKeyTo(table, column) -- adds a foreign key, named fk_{table}_{column}
• ComputedBy(expression) -- makes it a computed column: see Computed Columns

Every name counts against the identifier limit, derived ones included: see Identifiers.

Computed Columns

table.AddColumn<int>("quantity");
table.AddColumn<decimal>("price");
table.AddColumn("total", "NUMERIC(18,4)").ComputedBy("quantity * price");
// total NUMERIC(18,4) COMPUTED BY (quantity * price)

^{snippet source | anchor}

Firebird's computed columns are virtual: COMPUTED BY evaluates the expression each time the row is read, and nothing is stored.

You set	Result
ComputedBy(expression)	COMPUTED BY (expression); the type is the column's
ComputedColumnIsStored = true	NotSupportedException
A default, an identity or a COLLATE as well	InvalidOperationException; put a collation in the expression

The expression is compared ignoring case and whitespace outside literals and delimited names. A changed expression or type is altered in place, and a computed column can be added to a table with rows.

Foreign Keys

var orders = new Table("orders");
orders.AddColumn<int>("id").AsPrimaryKey().AutoIncrement();
orders.AddColumn<int>("user_id").NotNull()
    .ForeignKeyTo("users", "id", onDelete: CascadeAction.Cascade);

^{snippet source | anchor}

Each foreign key is a separate, guarded ALTER TABLE … ADD CONSTRAINT. Firebird records a key declared without an action as RESTRICT, which reads as NoAction. Firebird has no DROP TABLE … CASCADE, so dropping a table drops the foreign keys that reference it first.

Index Direction

A Firebird index is ascending or descending as a whole:

var index = new IndexDefinition("idx_orders_placed")
{
    Columns = ["placed_at"],
    SortOrder = SortOrder.Desc
};
table.Indexes.Add(index);
// CREATE DESCENDING INDEX idx_orders_placed ON orders (placed_at)

^{snippet source | anchor}

You set	Result
SortOrder = SortOrder.Desc	CREATE DESCENDING INDEX
DescendingColumns naming every key column	CREATE DESCENDING INDEX
DescendingColumns naming some key columns	InvalidOperationException when the DDL is rendered
Expression	COMPUTED BY (expression) in place of columns
Predicate	a partial index; Firebird 5 and later
Method, IncludeColumns	NotSupportedException

Direction, uniqueness, columns, expression and condition are read back and compared. An index switched off with ALTER INDEX … INACTIVE differs from the model and is rebuilt.

Partitioning and Check Constraints

Firebird has no table partitioning, so Table has nothing to set. Check constraints are refused: Firebird has them, but Weasel does not emit them there yet (#488), so AddCheckConstraint throws rather than leaving the constraint out.

Delta Detection

await using var conn = new FbConnection(connectionString);
await conn.OpenAsync();

var delta = await table.FindDeltaAsync(conn);

^{snippet source | anchor}

A column type is compared as Firebird stores it, so a synonym in the model is not drift:

Declared	Compared as
INT	INTEGER
DEC	DECIMAL
REAL	FLOAT
DOUBLE, LONG FLOAT	DOUBLE PRECISION
FLOAT(p)	DOUBLE PRECISION from p = 8 on Firebird 3, from p = 25 on 4 and later
CHARACTER, CHARACTER VARYING, CHAR VARYING	CHAR, VARCHAR
NCHAR, NATIONAL CHAR [VARYING]	CHAR, VARCHAR CHARACTER SET ISO8859_1
BINARY(n), VARBINARY(n)	CHAR(n), VARCHAR(n) CHARACTER SET OCTETS
BLOB, BLOB SUB_TYPE 0 / 1, BLOB(n, 1)	BLOB SUB_TYPE BINARY / TEXT
TIMESTAMP WITHOUT TIME ZONE	TIMESTAMP

A character length is compared. A NUMERIC/DECIMAL precision and scale, a character set and a collation are compared only when the model states them. Defaults and nullability are compared only with DetectColumnDrift, and DEFAULT NULL is no default. Whether a column is an identity is not compared.

What a Delta Can Change

Firebird changes a column's type in place only to widen it:

Change	Result
Add a nullable column, or a NOT NULL one with a default	ALTER TABLE … ADD
Add a NOT NULL column without a default, or an identity column	Invalid
Widen a CHAR/VARCHAR, an integer, or a NUMERIC precision	ALTER … TYPE
Narrow a column, or change a character column to a number	Invalid
Change the type of a primary key, unique or foreign key column, widening included	Invalid
Change a computed column's type or expression	ALTER … TYPE … COMPUTED BY
Turn an ordinary column into a computed one, or back	Invalid
A default or nullability, with DetectColumnDrift	ALTER … SET/DROP DEFAULT, SET/DROP NOT NULL
An index or a foreign key	dropped and recreated

TableDelta.InvalidReason names the column. Weasel never drops a key to make room for a type change.

Generating DDL

var migrator = new FirebirdMigrator();
var writer = new StringWriter();
table.WriteCreateStatement(migrator, writer);

^{snippet source | anchor}

Every CREATE and ADD is guarded, so the script runs again cleanly and two appliers racing each other both succeed:

SET TERM ^ ;
EXECUTE BLOCK AS
BEGIN
  IF (NOT EXISTS(SELECT 1 FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'USERS' AND RDB$VIEW_BLR IS NULL)) THEN
    EXECUTE STATEMENT 'CREATE TABLE users (
    id            INTEGER         GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    name          VARCHAR(255)    NOT NULL,
    email         VARCHAR(255)    NOT NULL,
    created_at    TIMESTAMP,
    CONSTRAINT pk_users PRIMARY KEY (id)
)';
END
^
SET TERM ; ^

The index follows as a guarded CREATE UNIQUE INDEX idx_users_email ON users (email).