Documentation
¶
Overview ¶
Package db is WebThings' small layer over SQL. It keeps one connection pool for SQLite, Postgres or MySQL, so queries are written once with `?` placeholders and work on all three. A transaction travels in the context (see DB.Tx), so code called inside one joins it without passing it along. Core and every module bring their own schema files, applied at start (see DB.ApplySchema).
Index ¶
- Variables
- func Affected(result sql.Result, err error) error
- func AfterCommit(ctx context.Context, fn func())
- func AfterWrite(ctx context.Context, err error, fn func()) error
- func InTx(ctx context.Context) bool
- func IsForeignKey(err error) bool
- func IsUnique(err error) bool
- func JSONValue(value any) (driver.Value, error)
- func NewID() string
- func ScanJSON(src, target any) error
- func WithActor(ctx context.Context, userID string) context.Context
- type DB
- func (db *DB) ApplySchema(ctx context.Context, owner string, files fs.FS) error
- func (db *DB) Close() error
- func (db *DB) Dialect() Dialect
- func (db *DB) Exec(ctx context.Context, query string, args ...any) (sql.Result, error)
- func (db *DB) Get(ctx context.Context, dest any, query string, args ...any) error
- func (db *DB) In(query string, args ...any) (string, []any, error)
- func (db *DB) Ping(ctx context.Context) error
- func (db *DB) Query(ctx context.Context, query string, args ...any) (*sqlx.Rows, error)
- func (db *DB) SchemaVersion(files fs.FS) (int64, error)
- func (db *DB) SchemaVersions(ctx context.Context) (map[string]int64, error)
- func (db *DB) Select(ctx context.Context, dest any, query string, args ...any) error
- func (db *DB) Statements() uint64
- func (db *DB) Stats() sql.DBStats
- func (db *DB) Tx(ctx context.Context, fn func(context.Context) error) error
- type Dialect
- type Ref
- type SoftTable
- func (t SoftTable) Delete(ctx context.Context, id string) error
- func (t SoftTable) DeleteWith(ctx context.Context, id, set string, args ...any) error
- func (t SoftTable) GetTrashed(ctx context.Context, dest any, columns, id string) error
- func (t SoftTable) Purge(ctx context.Context, id string, steps ...func(ctx context.Context) error) error
- func (t SoftTable) Trashed(ctx context.Context, dest any, columns string, limit, offset int) (int, error)
- func (t SoftTable) Undelete(ctx context.Context, id string, checks ...func(ctx context.Context) error) error
Constants ¶
This section is empty.
Variables ¶
var ErrNotFound = errors.New("not_found")
ErrNotFound is returned by Get when no row matches.
var ErrSchemaChanged = errors.New("schema file changed after it was applied")
ErrSchemaChanged is returned by ApplySchema for a schema file whose statements changed after it was applied to the database.
Functions ¶
func Affected ¶
Affected checks an UPDATE or DELETE meant for rows that must exist, such as one record by its ID. It returns err when the statement failed, the error of RowsAffected when the count is unknown, and ErrNotFound when no row matched. The results of Exec go in directly:
return db.Affected(database.Exec(ctx, `DELETE FROM wt_note WHERE id=?`, id))
Rows count when they match, also when the statement leaves their values as they were: Open sets MySQL up to count like SQLite and Postgres.
func AfterCommit ¶
AfterCommit calls fn once what was written with ctx is committed: at once outside a transaction, and after the commit of the transaction ctx carries (see Tx), so nothing that reads committed data, such as a cache that is told to forget, sees the state before the write again. When the transaction rolls back, fn is not called.
func AfterWrite ¶
AfterWrite calls fn after the commit of the write that err reports on (see AfterCommit), when the write succeeded and fn is set, and returns err. Stores use it to tell a cache that what it keeps changed, such as the output cache through webthings.Env.Changed.
func InTx ¶
InTx reports whether ctx carries a transaction. Caches use it to avoid keeping data that is not committed yet.
func IsForeignKey ¶
IsForeignKey reports a foreign key violation on any supported driver.
func JSONValue ¶
JSONValue encodes value as JSON text, for the Value method of a type stored as JSON.
func NewID ¶
func NewID() string
NewID returns a new time-ordered identifier (UUIDv7) in its 36-character text form. IDs are stored as VARCHAR(36) on SQLite and MySQL and as the native UUID type on Postgres; they travel as plain strings in every dialect.
Types ¶
type DB ¶
type DB struct {
// contains filtered or unexported fields
}
DB is the site's database. It is safe for concurrent use; every method takes part in the transaction its context carries, if any (see Tx).
func Open ¶
Open connects and pings. Driver is one of sqlite, pgx or mysql; "sqlite3" and "postgres" are accepted as aliases. SQLite uses the pure-Go modernc.org/sqlite driver, so no cgo is needed for any dialect.
func (*DB) ApplySchema ¶
Schema files sit in one folder, named <datetime>[_description].<flavour>.sql:
schema/20261004000000_init.sqlite.sql schema/20261004000000_init.postgres.sql schema/20261004000000_init.mysql.sql schema/20261120093000_add_avatar.sqlite.sql ...
The datetime (YYYYMMDDHHMMSS) is the version. Every version must exist for every flavour. ApplySchema runs the current flavour's files that are newer than the version recorded for owner in wt_schema, each in a transaction.
Before the first release every owner has only its _init files: edit them and recreate the development database. After a release, add new files instead of changing applied ones.
wt_schema_step keeps a checksum of each applied file's statements. A file whose statements changed after it was applied, also an _init file, makes ApplySchema return ErrSchemaChanged: the database does not have what the file now says. Changes of only whitespace or comments do not count. A database from before the checksums gets those of the files as they are.
func (*DB) Dialect ¶
Dialect returns the kind of database, for the queries that differ per database.
func (*DB) Exec ¶
Exec runs a statement that returns no rows, such as an INSERT, UPDATE or DELETE. Errors for values a column type rejects become ErrNotFound (see Get); see Affected to check that a row was there to change.
func (*DB) Get ¶
Get scans one row into dest and returns ErrNotFound for no rows, and for values a column type rejects, such as text that is not a UUID in Postgres.
func (*DB) Query ¶
Query runs a query and returns its rows to be read one at a time, for results too large to scan at once with Select. The caller closes the rows. Errors for values a column type rejects become ErrNotFound (see Get).
func (*DB) SchemaVersion ¶
SchemaVersion is the newest version among the schema files of this database's dialect, the version ApplySchema brings an owner to; 0 when there are none.
func (*DB) SchemaVersions ¶
SchemaVersions returns the applied schema version of every owner, as recorded in wt_schema, in one query; readiness checks compare them with SchemaVersion.
func (*DB) Select ¶
Select scans all rows into dest. Errors for values a column type rejects, such as an invalid UUID, become ErrNotFound (see Get).
func (*DB) Statements ¶
Statements is the number of statements this DB has sent so far. Tests use it to check that cached work needs no queries; it is cheap enough to keep for diagnostics.
type Dialect ¶
type Dialect string
Dialect is the kind of database behind a DB. It decides which schema files apply, and lets the few queries that differ per database choose their SQL.
type Ref ¶
type Ref string
Ref is an optional reference to a row, such as a created_by user ID. It is stored as NULL when empty and reads NULL as empty, so structs can use it for nullable ID columns.
func Actor ¶
Actor is the user ID WithActor put in ctx, as a Ref: empty, and so NULL in the database, for visitors and the system.
func Stamp ¶
Stamp returns the time and the author of a change, for the audit columns: the current time in UTC, cut to the microsecond that every supported database keeps, and Actor(ctx). A store takes one stamp per change and writes it to every row the change touches, so rows changed together carry equal times:
at, by := db.Stamp(ctx) _, err := database.Exec(ctx, ` UPDATE wt_note SET body=?, updated_at=?, updated_by=? WHERE id=? AND deleted_at IS NULL`, body, at, by, id, )
type SoftTable ¶
type SoftTable struct {
// DB is the database the table is in.
DB *DB
// Table is the name of the table, such as "wt_redirect". It goes into
// the SQL as it is, so it never comes from input.
Table string
// Scope limits every statement to a part of the table, such as the rows
// of one module instance: an SQL condition with ? placeholders, such as
// "instance=?", that Args fill. Empty covers the whole table.
Scope string
// Args are the values of Scope's placeholders, in order.
Args []any
}
SoftTable is a table whose rows go to the trash before they are removed for good. Every row has an id column and the deleted_at and deleted_by audit columns (see audit.go): NULL while the row is live, set while it is in the trash. Queries for live rows add "deleted_at IS NULL" themselves.
SoftTable writes the statements that every such table needs, so a store keeps only its own rules:
notes := db.SoftTable{DB: database, Table: "wt_note", Scope: "instance=?", Args: []any{key}}
err := notes.Delete(ctx, id)
err = notes.Undelete(ctx, id, func(ctx context.Context) error {
// Refuse a note whose title another note took meanwhile.
return checkTitle(ctx, id)
})
err = notes.Purge(ctx, id)
Every method returns ErrNotFound for an ID that is not where it should be: not live for Delete, not in the trash for the others. The methods join a transaction in ctx (see DB.Tx).
func (SoftTable) Delete ¶
Delete moves the live row with id to the trash, recording when and by whom in deleted_at and deleted_by (see Stamp).
func (SoftTable) DeleteWith ¶
DeleteWith is Delete with more assignments in the same statement, such as "publish_at=NULL" to cancel what was planned for the row. The ? placeholders of set take args.
func (SoftTable) GetTrashed ¶
GetTrashed reads the row with id from the trash into dest, a pointer to a struct, with the given columns, such as "id, name, deleted_at".
func (SoftTable) Purge ¶
func (t SoftTable) Purge(ctx context.Context, id string, steps ...func(ctx context.Context) error) error
Purge removes the row with id from the trash for good. Steps run first, in one transaction with the delete and only for a row in the trash, for what goes with the row, such as releasing the media it uses or removing rows that refer to it; the first error of a step is returned as it is.
func (SoftTable) Trashed ¶
func (t SoftTable) Trashed(ctx context.Context, dest any, columns string, limit, offset int) (int, error)
Trashed reads rows in the trash into dest, a pointer to a slice, with the given columns: the most recently deleted first, at most limit of them from offset. A limit of 0 or less reads them all. It returns how many rows the trash holds in all, for paging.
func (SoftTable) Undelete ¶
func (t SoftTable) Undelete(ctx context.Context, id string, checks ...func(ctx context.Context) error) error
Undelete brings the row with id back from the trash, recording when and by whom in updated_at and updated_by. Checks run first, in one transaction with the change, so a store can keep a row in the trash that may not come back, such as a redirect whose path another redirect uses now; the first error of a check is returned as it is.
A unique index may still refuse the row; db.IsUnique reports that error.
Source Files
Directories