Provider Trait Matrix
Weasel supports five database providers — PostgreSQL, SQL Server, Oracle, MySQL, and SQLite — each with subtly different feature sets. The audit at #270 documented this matrix explicitly so users can write provider-agnostic schema code with confidence about what works everywhere versus what's provider-specific.
At a glance
| Feature | PostgreSQL | SQL Server | Oracle | MySQL | SQLite |
|---|---|---|---|---|---|
| Default schema | public | dbo | WEASEL | public | main |
| Identifier quoting | "name" | [name] | "NAME" | `name` | "name" |
| Identifier case | Sensitive | Folded | Folded (upper) | Folded (lower) | Folded |
| Max identifier length | 63 | 128 | 128 | 64 | (unlimited) |
| Auto-increment SQL | SERIAL | IDENTITY(1,1) | GENERATED…AS IDENTITY | AUTO_INCREMENT | AUTOINCREMENT |
ALTER TABLE ADD COLUMN | ✓ | ✓ | ✓ | ✓ | ✓ |
ALTER TABLE DROP COLUMN | ✓ | ✓ | ✓ | ✓ | ✓ (3.35+) |
ALTER TABLE ALTER COLUMN | ✓ | ✓ | ✓ | ✓ | ✗ (recreate) |
ALTER TABLE ADD CONSTRAINT FK | ✓ | ✓ | ✓ | ✓ | ✗ (inline only) |
ALTER TABLE DROP CONSTRAINT FK | ✓ | ✓ | ✓ | ✓ | ✗ (recreate) |
Cascading actions: RESTRICT | ✓ | ✗ (→ NoAction) | ✗ (→ NoAction) | ✓ | ✓ |
| Native sequences | ✓ | ✓ | ✓ | Table-emulated | Table-emulated |
| Views | ✓ + materialized | ✓ | ✓ | ✓ | ✓ (read-only) |
| Functions / Stored Procedures | ✓ (PL/pgSQL) | ✓ (T-SQL) | ✓ (PL/SQL) | (limited) | Connection-scoped |
| Partitioning | ✓ (hash/range/list) | ✓ (range) | ✓ | ✓ | ✗ |
| JSON storage | jsonb native | nvarchar(max) | CLOB | JSON | TEXT (JSON1) |
| Array columns | ✓ | ✗ | ✗ | ✗ | ✗ (except byte[]) |
| Statement terminator | ; | ; | / (PL/SQL blocks) | ; | ; |
Identity / auto-increment
In 9.0 every provider exposes a canonical .AutoIncrement() fluent method on Table.ColumnExpression. Previously each provider used a different spelling; the old names are kept as [Obsolete] aliases for one major-version cycle.
| Provider | Canonical 9.0 | Pre-9.0 alias ([Obsolete]) | SQL Emitted |
|---|---|---|---|
| PostgreSQL | AutoIncrement() | Serial() | SERIAL |
| PostgreSQL | BigAutoIncrement() | BigSerial() | BIGSERIAL |
| PostgreSQL | SmallAutoIncrement() | SmallSerial() | SMALLSERIAL |
| SQL Server | AutoIncrement() | AutoNumber() | IDENTITY(1,1) |
| Oracle | AutoIncrement() | AutoNumber() | GENERATED BY DEFAULT AS IDENTITY |
| MySQL | AutoIncrement() | AutoNumber() | AUTO_INCREMENT |
| SQLite | AutoIncrement() | (no change) | AUTOINCREMENT |
// Same code compiles against any of the five providers
table.AddColumn<int>("id").AsPrimaryKey().AutoIncrement();Identifier comparison
Table.ColumnFor / HasColumn / IndexFor / HasIndex use a per-provider NameComparison:
| Provider | NameComparison | Rationale |
|---|---|---|
| PostgreSQL | Ordinal | PG is case-sensitive when quoted (the default for Weasel-generated DDL) |
| SQL Server | OrdinalIgnoreCase | SS identifier comparison is case-insensitive by default |
| Oracle | OrdinalIgnoreCase | Oracle folds unquoted identifiers to upper case |
| MySQL | OrdinalIgnoreCase | MySQL identifier comparison is case-insensitive on most platforms |
| SQLite | OrdinalIgnoreCase | SQLite identifiers are case-folded |
RemoveColumn is always case-insensitive on every provider — every concrete provider used EqualsIgnoreCase historically.
Primary-key name defaults
When you don't supply a PrimaryKeyName, each provider generates one with its own convention:
| Provider | Default PrimaryKeyName |
|---|---|
| PostgreSQL | pkey_{table}_{cols} |
| SQL Server | pkey_{table}_{cols} |
| Oracle | pk_{table}_{cols} |
| MySQL | pk_{table}_{cols} |
| SQLite | pk_{table} |
Schema-object base types
Most schema objects share a base class in Weasel.Core:
| Object | Core base | Notes |
|---|---|---|
| Tables | TableBase<TColumn, TIndex, TForeignKey> | New in 9.0 (#270 step 9) |
| Foreign keys | ForeignKeyBase | Owns Parse, LinkColumns, Equals, ReadReferentialActions |
| Sequences | SequenceBase | PG / SS / Oracle native, MySQL / SQLite table-emulated |
| Functions | FunctionBase | PG + SS only; Oracle uses procedures; SQLite functions are connection-scoped |
| Views | ViewBase | All providers; SQLite is read-only |
| Indexes | INamed only | Provider-specific (PG is ~4× richer than the others) |
| Table columns | ITableColumn only | Provider-specific declaration formatting |
DDL syntax strategy
The shared CREATE / DROP algorithm is gradually migrating to consume a IDdlSyntaxStrategy object per provider (#270 step 8). Currently routed through it:
WriteDropTable(PG appendsCASCADE, SQLite doesn't)WriteCreateTableHeader(with / withoutIF NOT EXISTS)
The remainder of WriteCreateStatement is still per-provider while the strategy shape settles. Plan is to migrate column / PK / FK emission as a follow-up.
Constructor pattern
All five Table classes share the same constructor signatures:
new Table(DbObjectName identifier); // identifier wrapped in provider-specific subclass internally
new Table(string tableName); // parsed via provider's DbObjectName.ParseThe PostgreSQL and SQLite implementations wrap the supplied DbObjectName in PostgresqlObjectName / SqliteObjectName respectively; SS / Oracle / MySQL pass through the DbObjectName directly. Either way, the public surface is uniform — polymorphic schema-building code doesn't need to know which provider it's targeting until DDL is emitted.
When you do need provider-specific code
Provider-specific extensions stay on the concrete Table / ColumnExpression:
- PostgreSQL:
MaterializedView,Partitioning(hash / range / list),IgnorePartitionsInMigration,FullTextIndex,UpsertFunction,NpgsqlRange<T>columns - SQL Server:
SqlServerPartitioning(range),PartitionByRange - Oracle:
PartitionStrategy,PartitionExpressions - MySQL:
Engine,Charset,Collation,PartitionCount,AddFulltextIndex - SQLite:
WithoutRowId,StrictTypes,GeneratedAs, JSON expression indexes viaForJsonPath
Polymorphic code uses the shared ITable / TableBase surface; code that needs provider extras casts to the concrete Table type.