stmts

Documentation

Index

• Variables
• type Statement
  • func (stmt Statement[R]) Prepare(args any) (gorqlite.ParameterizedStatement, error)
  • func (stmt Statement[R]) Write(ctx context.Context, conn *gorqlite.Connection, args any) error

Variables

var (
	GetUserAddressesByID = Statement[models.Address]{
		Query: `SELECT * FROM user_addresses WHERE user_id = ?`,
		Args:  []string{"user_id"},
	}

	GetAddressByID = Statement[models.Address]{
		Query: `SELECT * FROM user_addresses WHERE user_id = ? AND id = ?`,
		Args:  []string{"user_id", "id"},
	}

	CreateAddress = Statement[any]{
		Query: `INSERT INTO user_addresses (
			id,
			user_id,
			city_code,
			city_name,
			street,
			extra
		)
		VALUES (?, ?, ?, ?, ?, ?)`,
		Args: []string{"id", "user_id", "city_code", "city_name", "street", "extra"},
	}

	UpdateAddress = Statement[any]{
		Query: `UPDATE user_addresses SET city_code = ?, city_name = ?, street = ?, extra = ? WHERE id = ? AND user_id = ?`,
		Args:  []string{"city_code", "city_name", "street", "extra", "id", "user_id"},
	}

	DeleteAddress = Statement[any]{
		Query: `DELETE FROM user_addresses WHERE id = ? AND user_id = ?`,
		Args:  []string{"id", "user_id"},
	}
)

var (
	CreateEMail = Statement[any]{
		Query: `INSERT INTO user_emails (
			id,
			user_id,
			address,
			verified
		)
		VALUES (?, ?, ?, ?)`,
		Args: []string{"id", "user_id", "address", "verified"},
	}

	GetEmailsForUserByID = Statement[models.EMail]{
		Query: `SELECT * FROM user_emails WHERE user_id = ?`,
		Args:  []string{"user_id"},
	}

	GetEmailByID = Statement[models.EMail]{
		Query: `SELECT * FROM user_emails WHERE user_id = ? AND id = ?`,
		Args:  []string{"user_id", "id"},
	}

	GetPrimaryEmailForUserByID = Statement[models.EMail]{
		Query: `SELECT * FROM user_emails WHERE user_id = ? and is_primary = true LIMIT 1`,
		Args:  []string{"user_id"},
	}

	DeleteEMailFromUser = Statement[any]{
		Query: `DELETE FROM user_emails WHERE id = ? AND user_id = ?`,
		Args:  []string{"id", "user_id"},
	}

	MarkEmailVerified = Statement[any]{
		Query: `UPDATE user_emails SET verified = ? WHERE id = ? AND user_id = ?`,
		Args:  []string{"verified", "id", "user_id"},
	}

	MarkEmailAsPrimary = Statement[any]{
		Query: `UPDATE user_emails SET is_primary = (id == ?) WHERE user_id = ?`,
		Args:  []string{"id", "user_id"},
	}

	MarkEmailAsVerified = Statement[any]{
		Query: `UPDATE user_emails SET verified = true WHERE user_id = ? AND id = ?`,
		Args:  []string{"user_id", "id"},
	}
)

var (
	CreateFeatureTable = Statement[any]{
		Query: `CREATE TABLE IF NOT EXISTS features (
			name TEXT PRIMARY KEY UNIQUE NOT NULL,
			description TEXT,
		)`,
	}

	CreateUserFeatureTable = Statement[any]{
		Query: `CREATE TABLE IF NOT EXISTS user_features (
			user_id TEXT NOT NULL,
			feature_name TEXT NOT NULL,
			CONSTRAINT fk_feature_user_id
				FOREIGN KEY (user_id) REFERENCES(users.id)
				ON DELETE CASCADE,
			CONSTRAINT fk_feature_feature_name
				FOREIGN KEY (feature_name) REFERENCES(features.name)
				ON DELETE CASCADE
		)`,
	}

	CreateFeature = Statement[any]{
		Query: `INSERT INTO features SET name = ?, description = ?`,
		Args:  []string{"name", "description"},
	}

	EnableFeature = Statement[any]{
		Query: `INSERT INTO user_features SET feature_name = ?, user_id = ?`,
		Args:  []string{"feature_name", "user_id"},
	}

	DisableFeature = Statement[any]{
		Query: `DELETE FROM user_features WHERE feature_name = ? AND user_id = ?`,
		Args:  []string{"feature_name", "user_id"},
	}

	GetEnabledFeatures = Statement[any]{
		Query: `SELECT * from features
			JOIN user_features ON feature_name = name
			WHERE name = ? AND user_id = ?`,
		Args: []string{"name", "user_id"},
	}
)

var (
	GetPhoneNumbersByUserID = Statement[models.PhoneNumber]{
		Query: `SELECT * FROM user_phone_numbers WHERE user_id = ?`,
		Args:  []string{"user_id"},
	}

	GetUserPrimaryPhoneNumber = Statement[models.PhoneNumber]{
		Query: `SELECT * FROM user_phone_numbers WHERE user_id = ? AND is_primary = TRUE`,
		Args:  []string{"user_id"},
	}

	GetPhoneNumberByID = Statement[models.PhoneNumber]{
		Query: `SELECT * FROM user_phone_numbers WHERE user_id = ? AND id = ?`,
		Args:  []string{"user_id", "id"},
	}

	CreateUserPhoneNumber = Statement[any]{
		Query: `INSERT INTO user_phone_numbers (id, user_id, phone_number, is_primary, verified) VALUES (?, ?, ?, ?, ?)`,
		Args:  []string{"id", "user_id", "phone_number", "is_primary", "verified"},
	}

	DeleteUserPhoneNumber = Statement[any]{
		Query: `DELETE FROM user_phone_numbers WHERE user_id = ? AND id = ?`,
		Args:  []string{"user_id", "id"},
	}

	MarkPhoneNumberVerified = Statement[any]{
		Query: `UPDATE user_phone_numbers SET verified = ? WHERE id = ? AND user_id = ?`,
		Args:  []string{"verified", "id", "user_id"},
	}

	MarkPhoneNumberAsPrimary = Statement[any]{
		Query: `UPDATE user_phone_numbers SET is_primary = (id == ?) WHERE user_id = ?`,
		Args:  []string{"id", "user_id"},
	}

	MarkPhoneNumberAsVerified = Statement[any]{
		Query: `UPDATE user_phone_numbers SET verified = TRUE WHERE user_id = ? AND id = ?`,
		Args:  []string{"user_id", "id"},
	}
)

var (
	GetRoleByName = Statement[models.Role]{
		Query: `SELECT * FROM roles WHERE name = ?`,
		Args:  []string{"name"},
	}

	GetRoleByID = Statement[models.Role]{
		Query: `SELECT * FROM roles WHERE id = ?`,
		Args:  []string{"id"},
	}

	GetRoles = Statement[models.Role]{
		Query: `SELECT * FROM roles`,
	}

	CreateRole = Statement[any]{
		Query: `INSERT INTO roles (id, name, description, delete_protected) VALUES (?, ?, ?, ?)`,
		Args:  []string{"id", "name", "description", "delete_protected"},
	}

	UpdateRole = Statement[any]{
		Query: `UPDATE roles SET name = ?, description = ?, delete_protected = ? WHERE id = ?`,
		Args:  []string{"name", "description", "delete_protected", "id"},
	}

	AssignRoleToUser = Statement[any]{
		Query: `INSERT INTO role_assignments (user_id, role_id) VALUES (?, ?)`,
		Args:  []string{"user_id", "role_id"},
	}

	UnassignRoleFromUser = Statement[any]{
		Query: `DELETE FROM role_assignments WHERE user_id = ? AND role_id = ?`,
		Args:  []string{"user_id", "role_id"},
	}

	GetRolesForUser = Statement[models.Role]{
		Query: `SELECT
				roles.id as id, roles.name as name, roles.description as description
			FROM role_assignments
			JOIN roles ON roles.id = role_id
			WHERE user_id = ?`,
		Args: []string{"user_id"},
	}

	GetUsersByRole = Statement[models.User]{
		Query: `SELECT * FROM role_assignments
		JOIN users ON users.id = user_id
		WHERE role_id = ?`,
		Args: []string{"role_id"},
	}

	DeleteRole = Statement[any]{
		Query: `DELETE FROM roles WHERE id = ?`,
		Args:  []string{"id"},
	}
)

var (
	CreateUserTable = Statement[any]{
		Query: `CREATE TABLE IF NOT EXISTS users (
			id TEXT NOT NULL PRIMARY KEY UNIQUE,
			username TEXT NOT NULL UNIQUE,
			display_name TEXT,
			first_name TEXT,
			last_name TEXT,
			extra TEXT,
			avatar TEXT,
			birthday TEXT,
			password TEXT NOT NULL,
			totp_secret TEXT NULL
		)`,
	}

	CreateAddressTable = Statement[any]{
		Query: `CREATE TABLE IF NOT EXISTS user_addresses (
			id TEXT NOT NULL PRIMARY KEY,
			user_id TEXT NOT NULL,
			city_code TEXT,
			city_name TEXT,
			street TEXT,
			extra TEXT,
			CONSTRAINT fk_user_address
				FOREIGN KEY(user_id) REFERENCES users(id)
				ON DELETE CASCADE
		)`,
	}

	CreateEMailTable = Statement[any]{
		Query: `CREATE TABLE IF NOT EXISTS user_emails (
			id TEXT NOT NULL PRIMARY KEY,
			user_id TEXT NOT NULL,
			address TEXT UNIQUE NOT NULL,
			verified BOOL,
			is_primary BOOL,
			CONSTRAINT fk_user_mail
				FOREIGN KEY(user_id) REFERENCES users(id)
				ON DELETE CASCADE
		)`,
	}

	CreatePhoneNumberTable = Statement[any]{
		Query: `CREATE TABLE IF NOT EXISTS user_phone_numbers (
			id TEXT NOT NULL PRIMARY KEY,
			user_id TEXT NOT NULL,
			phone_number TEXT NOT NULL,
			is_primary BOOL,
			verified BOOL,
			CONSTRAINT fk_user_phone_number
				FOREIGN KEY(user_id) REFERENCES users(id)
				ON DELETE CASCADE
		)`,
	}

	CreateRoleTable = Statement[any]{
		Query: `CREATE TABLE IF NOT EXISTS roles (
			id TEXT NOT NULL PRIMARY KEY UNIQUE,
			name TEXT NOT NULL UNIQUE,
			description TEXT,
			delete_protected BOOL
		)`,
	}

	CreateRoleAssignmentTable = Statement[any]{
		Query: `CREATE TABLE IF NOT EXISTS role_assignments (
			user_id TEXT NOT NULL,
			role_id TEXT NOT NULL,
			CONSTRAINT fk_user_role_user
				FOREIGN KEY(user_id) REFERENCES users(id)
				ON DELETE CASCADE,
			CONSTRAINT fk_user_role_role
				FOREIGN KEY(role_id) REFERENCES roles(id)
				ON DELETE CASCADE
		)`,
	}

	CreateTokenInvalidationTable = Statement[any]{
		Query: `CREATE TABLE IF NOT EXISTS token_invalidation (
			token_id TEXT NOT NULL PRIMARY KEY UNIQUE,
			user_id TEXT NOT NULL,
			issued_at NUMBER NOT NULL,
			expires_at NUMBER NOT NULL,
			CONSTRAINT fk_token_user
				FOREIGN KEY(user_id) REFERENCES users(id)
				ON DELETE CASCADE
		)`,
	}

	CreateRegistrationTokenTable = Statement[any]{
		Query: `CREATE TABLE IF NOT EXISTS registration_tokens (
			token TEXT NOT NULL PRIMARY KEY,
			expires NUMBER NULL,
			allowed_usage NUMBER NULL,
			initial_roles TEXT NULL,
			created_by STRING,
			created_at NUMBER
		)`,
	}

	CreateRegistrationTokenCleanupTrigger = Statement[any]{
		Query: `CREATE TRIGGER IF NOT EXISTS registration_token_cleanup AFTER UPDATE ON registration_tokens
		BEGIN
			DELETE FROM registration_tokens
			WHERE
				allowed_usage IS NOT NULL
				AND allowed_usage = 0;
		END;`,
	}

	Create2FABackupCodeTable = Statement[any]{
		Query: `CREATE TABLE IF NOT EXISTS mfa_backup_codes (
			code TEXT NOT NULL,
			user_id TEXT,
			CONSTRAINT fk_mfa_backup_codes_user
				FOREIGN KEY(user_id) REFERENCES users(id)
				ON DELETE CASCADE
		)`,
	}

	CreateWebauthnCredsTable = Statement[any]{
		Query: `CREATE TABLE IF NOT EXISTS webauthn_creds (
			id TEXT PRIMARY KEY,
			user_id TEXT NOT NULL,
			cred TEXT,
			cred_type TEXT,
			client_name TEXT,
			client_os TEXT,
			client_device TEXT,
			CONSTRAINT fk_webauth_creds_user
				FOREIGN KEY(user_id) REFERENCES users(id)
				ON DELETE CASCADE
		)`,
	}

	CreateWebPushSubTable = Statement[any]{
		Query: `CREATE TABLE IF NOT EXISTS webpush_subscriptions (
			id TEXT PRIMARY KEY,
			user_id TEXT NOT NULL,
			user_agent TEXT,
			endpoint TEXT NOT NULL UNIQUE,
			auth TEXT NOT NULL,
			key TEXT NOT NULL,
			token_id TEXT NOT NULL,
			CONSTRAINT fk_webpush_subscription_user
				FOREIGN KEY(user_id) REFERENCES users(id)
				ON DELETE CASCADE
		)
		`,
	}
)

var (
	ErrInvalidArgCount = errors.New("invalid number of arguments")
	ErrMissingArgument = errors.New("missing argument value")
	ErrNoResults       = errors.New("no results")
	ErrNoRowsAffected  = errors.New("no rows affected")
)

var (
	CreateRejectedToken = Statement[any]{
		Query: `INSERT INTO token_invalidation (token_id, user_id, expires_at, issued_at) VALUES (?, ?, ?, ?)`,
		Args:  []string{"token_id", "user_id", "expires_at", "issued_at"},
	}

	IsTokenRejected = Statement[models.RejectedToken]{
		Query: `SELECT * FROM token_invalidation WHERE token_id = ?`,
		Args:  []string{"token_id"},
	}

	DeleteExpiredTokens = Statement[any]{
		Query: `DELETE FROM token_invalidation WHERE expires_at < ?`,
		Args:  []string{"expires_at"},
	}

	CreateRegistrationToken = Statement[any]{
		Query: `INSERT INTO registration_tokens (
			token,
			expires,
			allowed_usage,
			initial_roles,
			created_by,
			created_at
		) VALUES (?, ?, ?, ?, ?, ?)`,
		Args: []string{"token", "expires", "allowed_usage", "initial_roles", "created_by", "created_at"},
	}

	GetRegistrationToken = Statement[models.RegistrationToken]{
		Query: `SELECT * FROM registration_tokens WHERE token = ? AND allowed_usage > 0 AND (expires IS NULL OR expires > ?)`,
		Args:  []string{"token", "expires"},
	}

	MarkRegistrationTokenUsed = Statement[models.RegistrationToken]{
		Query: `UPDATE registration_tokens SET allowed_usage = (
			CASE 
				WHEN allowed_usage IS NOT NULL THEN allowed_usage - 1
				ELSE NULL
			END
		) WHERE token = ? AND (expires IS NULL OR expires > ?) RETURNING *`,
		Args: []string{"token", "expires"},
	}

	InsertRecoveryCodes = Statement[any]{
		Query: `INSERT INTO mfa_backup_codes (code, user_id) VALUES (?, ?)`,
		Args:  []string{"code", "user_id"},
	}

	CheckAndDeleteRecoveryCode = Statement[any]{
		Query: `DELETE FROM mfa_backup_codes WHERE user_id = ? AND code = ?`,
		Args:  []string{"user_id", "code"},
	}

	RemoveAllRecoveryCodes = Statement[any]{
		Query: `DELETE FROM mfa_backup_codes WHERE user_id = ?`,
		Args:  []string{"user_id"},
	}

	LoadUserRecoveryCodes = Statement[any]{
		Query: `SELECT * FROM mfa_backup_codes WHERE user_id = ?`,
		Args:  []string{"user_id"},
	}
)

var (
	GetUserByName = Statement[models.User]{
		Query: `SELECT * FROM users WHERE username = ?`,
		Args:  []string{"username"},
	}

	GetUserByEMail = Statement[struct {
		models.User  `mapstructure:",squash"`
		MailVerified bool `mapstructure:"verified"`
	}]{
		Query: `SELECT users.*, user_emails.verified FROM users
			JOIN user_emails ON user_emails.user_id = users.id WHERE user_emails.address = ?`,
		Args: []string{"mail"},
	}

	GetUserByID = Statement[models.User]{
		Query: `SELECT * FROM users WHERE id = ?`,
		Args:  []string{"id"},
	}

	GetAllUsers = Statement[models.User]{
		Query: `SELECT * FROM users`,
	}

	DeleteUser = Statement[any]{
		Query: `DELETE FROM users WHERE id = ?`,
		Args:  []string{"id"},
	}

	CreateUser = Statement[any]{
		Query: `INSERT INTO users (
			id,
			username,
			display_name,
			first_name,
			last_name,
			extra,
			avatar,
			birthday,
			password
		)
		VALUES (
			?, ?, ?, ?, ?, ?, ?, ?, ?
		)`,
		Args: []string{
			"id",
			"username",
			"display_name",
			"first_name",
			"last_name",
			"extra",
			"avatar",
			"birthday",
			"password",
		},
	}

	UpdateUser = Statement[any]{
		Query: `UPDATE users SET
			username = ?,
			display_name = ?,
			first_name = ?,
			last_name = ?,
			extra = ?,
			avatar = ?,
			birthday = ?
		WHERE id = ?
			`,
		Args: []string{
			"username",
			"display_name",
			"first_name",
			"last_name",
			"extra",
			"avatar",
			"birthday",
			"id",
		},
	}

	EnrollUserTOTPSecret = Statement[any]{
		Query: `UPDATE users SET totp_secret = ? WHERE id = ? AND totp_secret IS NULL`,
		Args:  []string{"totp_secret", "id"},
	}

	RemoveUserTOTPSecret = Statement[any]{
		Query: `UPDATE users SET totp_secret = NULL WHERE id = ? AND totp_secret IS NOT NULL`,
		Args:  []string{"id"},
	}

	SetUserPassword = Statement[any]{
		Query: `UPDATE users SET password = ? WHERE id = ?`,
		Args:  []string{"password", "id"},
	}

	AddWebauthnCred = Statement[any]{
		Query: `INSERT INTO webauthn_creds (id, user_id, cred, client_name, client_os, client_device, cred_type) VALUES (?, ?, ?, ?, ?, ?, ?)`,
		Args:  []string{"id", "user_id", "cred", "client_name", "client_os", "client_device", "cred_type"},
	}

	GetWebauthnCreds = Statement[models.Passkey]{
		Query: `SELECT * FROM webauthn_creds WHERE user_id = ?`,
		Args:  []string{"user_id"},
	}

	RemoveWebauthnCred = Statement[any]{
		Query: `DELETE FROM webauthn_creds WHERE user_id = ? AND id = ?`,
		Args:  []string{"user_id", "id"},
	}
)

var (
	GetWebPushSubscriptionsForUser = Statement[models.Webpush]{
		Query: `SELECT * FROM webpush_subscriptions WHERE user_id = ?`,
		Args:  []string{"user_id"},
	}

	DeleteWebPushSubscriptionForToken = Statement[any]{
		Query: `DELETE FROM webpush_subscriptions WHERE token_id = ?`,
		Args:  []string{"token_id"},
	}

	DeleteWebPushSubscriptionByID = Statement[any]{
		Query: `DELETE FROM webpush_subscriptions WHERE id = ?`,
		Args:  []string{"id"},
	}

	CreateWebPushSubscriptionForUser = Statement[any]{
		Query: `INSERT OR REPLACE INTO webpush_subscriptions (
			id,
			user_id,
			user_agent,
			endpoint,
			auth,
			key,
			token_id
		)
		VALUES ( 
			?, ?, ?, ?, ?, ?, ?
		)`,
		Args: []string{
			"id",
			"user_id",
			"user_agent",
			"endpoint",
			"auth",
			"key",
			"token_id",
		},
	}
)

Types

type Statement

type Statement[R any] struct {
	Query  string
	Args   []string
	Result R
}

func (Statement[R]) Prepare

func (stmt Statement[R]) Prepare(args any) (gorqlite.ParameterizedStatement, error)

func (Statement[R]) Write

func (stmt Statement[R]) Write(ctx context.Context, conn *gorqlite.Connection, args any) error