Skip to content

data-modeling.md

Latest commit

 

History

History
127 lines (100 loc) · 5.52 KB

data-modeling.md

File metadata and controls

127 lines (100 loc) · 5.52 KB
Raw

4. Data Modeling

Types, keys, constraints, collation, and schema management. Deep dives: docs/features/AUTO_ROWID.md · docs/collation/COLLATION_GUIDE.md · docs/serialization/README.md

4.1 Data types

SQL type .NET type Notes
TEXT string UTF-8, collation-aware
INTEGER int 32-bit; INTEGER PRIMARY KEY acts as rowid alias (SQLite-compatible)
LONG long 64-bit
REAL double IEEE-754
DECIMAL decimal 28-digit fixed point
BOOLEAN bool stored as 0/1
DATETIME DateTime UTC, tick precision
GUID Guid 16-byte
ULID Ulid 16-byte sortable ID
BLOB byte[] binary payloads
ROWREF internal row reference/pointer

4.1.1 Type affinity and type aliases

The declared type of a column is resolved once, in SqlTypeAffinity (src/SharpCoreDB/Services/SqlTypeAffinity.cs), and every DDL path uses that resolver — CREATE TABLE, ALTER TABLE … ADD COLUMN in both the multi-file and single-file engines. A size argument is ignored for the mapping (VARCHAR(255) and DECIMAL(10,2) behave like VARCHAR and DECIMAL), and a column declared without a type gets BLOB affinity, as in SQLite.

Recognised aliases (same .NET type as the canonical name above):

Declared type Maps to
INT, INT2, INT4, INT16, INT32, SMALLINT, TINYINT, MEDIUMINT INTEGER UseSqliteIntegerAffinity also applies here
INT8, INT64, BIGINT LONG
VARCHAR, NVARCHAR, NCHAR, CHARACTER, CLOB, CHAR TEXT
DOUBLE, FLOAT, FLOAT4, FLOAT8, DOUBLE PRECISION REAL
BOOL BOOLEAN
DATE, TIMESTAMP, TIMESTAMPTZ DATETIME
NUMERIC, NUMBER DECIMAL
UUID GUID
BYTEA, BINARY, VARBINARY BLOB
VECTOR(1536) VECTOR dimension is kept in the declaration

Any other name is classified with SQLite's affinity rules: a name containing INT gets INTEGER affinity (so INT8, SMALLINT, UNSIGNED BIG INT and even POINT land in the integer bucket), one containing CHAR, CLOB or TEXT gets TEXT, one containing BLOB gets BLOB, and one containing REAL, FLOA or DOUB gets REAL. A name in none of those buckets keeps the engine's historical TEXT mapping rather than SQLite's NUMERIC affinity: a SQLite NUMERIC column may hold a number or non-numeric text, while an engine column has exactly one .NET type, so TEXT is the honest choice for an unknown name (it is also what every previous release did, so existing schemas are unaffected).

Only newly created columns are affected by a change here. The column types of an existing table are stored in its metadata (multi-file: the table JSON; single-file: the SCDB table entry) and are read back on reopen, so a database written by an earlier release keeps the types it was written with.

Regression coverage: DeclaredTypeAffinityTests (the affinity and extraction tables, CREATE TABLE, ALTER TABLE on both storage modes, and the views that report data_type).

4.2 Primary keys & auto-generated IDs

-- SQLite-style rowid alias (INTEGER PRIMARY KEY)
CREATE TABLE t1 (id INTEGER PRIMARY KEY, name TEXT);

-- Integer auto-increment (persisted across restarts)
CREATE TABLE t2 (id INTEGER PRIMARY KEY AUTO, name TEXT);

-- Hidden _rowid: when a table has NO explicit PRIMARY KEY,
-- SharpCoreDB injects a hidden _rowid (ULID, AUTO, PRIMARY KEY, NOT NULL)
-- exactly like SQLite's rowid — but ULID-based instead of monotonic integer:
CREATE TABLE t3 (name TEXT, email TEXT);   -- _rowid ULID auto-PK added automatically

-- Explicit ULID key (set values yourself, or leave null to auto-generate)
CREATE TABLE t4 (id ULID PRIMARY KEY, name TEXT);
  • _rowid is a hidden auto-generated primary key of type ULID (Ulid.NewUlid()) — sortable by time, collision-free across nodes, and index-friendly.
  • id INTEGER PRIMARY KEY AUTO gives the classic monotonic integer counter, persisted across restarts.
  • Explicit GUID/ULID columns auto-generate their value when inserted with a null/missing key.

See docs/features/AUTO_ROWID.md.

4.3 Constraints

CREATE TABLE orders (
  id        LONG PRIMARY KEY,
  customer  LONG NOT NULL,
  total     REAL DEFAULT 0,
  status    TEXT CHECK (status IN ('open','closed')),
  created   DATETIME DEFAULT current_timestamp,
  UNIQUE (customer, created)
);

Foreign keys, ON DELETE/ON UPDATE cascade rules, and deferrable constraints are supported.

4.4 Collation

Collation Behavior
BINARY byte-wise (default, fastest)
NOCASE ASCII case-insensitive
RTRIM ignores trailing spaces
UNICODE_CI Unicode case-insensitive
LOCALE("xx_XX") ICU/OS locale-aware 
CREATE TABLE users (name TEXT COLLATE NOCASE);
SELECT * FROM users WHERE name = 'ADA' COLLATE NOCASE;  -- matches 'ada'

⚡ BINARY collation is the only one that can be matched via pure SIMD byte compares. Prefer it on hot lookup columns. See docs/collation/COLLATION_GUIDE.md.

4.5 Schema management APIs

  • db.CreateTable(...), db.DropTable(...), db.RenameTable(...), db.AlterTable(...)
  • db.AddColumn, db.DropColumn, db.RenameColumn
  • db.GetTableMetadata(name) for column/index introspection
  • db.GetTables() enumerates the catalog