sqlite

package
v0.0.0-...-c120236 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Apr 25, 2024 License: MIT Imports: 16 Imported by: 2

Documentation

Overview

Package sqlite provides func specific to SQLite-based implementations¨ of package [repo].

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func DoInsertGeneric 

func DoInsertGeneric[T DRM.RowModel](pSR *SqliteRepo, pRM T) (int, error)

DoInsertGeneric takes a generic RowModel and "does" a simple (not prepared) SQL INSERT dtatement, returning the inserted item's ID (i.e. primary key). A statement is created, then envalued & executed using regular parameter substitution with Exec(..).

API notes on this:

  • [Exec](..) returns ([Result], error)
  • ONLY Exec returns a Result
  • ONLY a Result has LastInsertId() (is an int64)

// - QueryRow(..) returns (*Row) // - Query(..) returns (*Rows, error)

API for [Row] (and [Rows]):

  • func (r *Row/s) Scan(dest ...any) error
  • Scan(..) can get the inserted-ID of INSERT...RETURNING
  • func (r *Row/s) Err() error
  • Use Err() to check for query errors without calling Scan(..)
  • Err() returns any error hit when running the query
  • If not nil, the error is also returned from Scan(..)

.

func DoSelectByIdGeneric 

func DoSelectByIdGeneric[T DRM.RowModel](pSR *SqliteRepo, anID int, pDest T) (bool, error)

DoSelectByIdGeneric returns true/false indicating whether the ID was found.

In order to process the ID, the func needs a generic argument that can be used in the body of the func. A [RowModel] is a pointer - having write access - so we kill two birds with one stone by passing in a RowModel pointing to a destination buffer to Scan the DB fetch. .

Types

type SqliteDBManager 

type SqliteDBManager struct {
	D.DB_type // D.DB_SQLite
}
var SQLite_DB_Manager *SqliteDBManager

SQLite_DB_Manager is a global singleton (!)

func (*SqliteDBManager) DBType 

func (p *SqliteDBManager) DBType() D.DB_type

func (*SqliteDBManager) InitznPragmas 

func (p *SqliteDBManager) InitznPragmas() string

func (*SqliteDBManager) NewAtPath 

func (p *SqliteDBManager) NewAtPath(aPath string) (*SqliteRepo, error)

NewAtPath creates a DB at the filepath, opens it, and runs standard initialization pragma(s). It does not create any tables in it. If a file or dir already exists at the filepath, the func returns an error. The filepath can be a relative path, but not "".

The repo type will be "sqlite" (equivalent to "sqlite3"). .

func (*SqliteDBManager) OpenAtPath 

func (p *SqliteDBManager) OpenAtPath(path string) (*SqliteRepo, error)

func (*SqliteDBManager) OpenExistingAtPath 

func (p *SqliteDBManager) OpenExistingAtPath(aPath string) (*SqliteRepo, error)

OpenExistingAtPath opens an existing DB at the filepath. It is assumed to have any default initializations (i.e. pragmas etc.). If no file already exists at the filepath, the func returns an error. The filepath can be a relative path, but may not be "".

The repo type will be "sqlite" (equivalent to "sqlite3"). .

func (*SqliteDBManager) ReadonlyPragma 

func (p *SqliteDBManager) ReadonlyPragma() string

type SqliteRepo 

type SqliteRepo struct {
	*sql.DB

	// SBs map[string]DR.StatementBuilder
	// SEs map[string]StatementEngine
	// TODO: Tx should be protected by a Mutex
	*sql.Tx
	// contains filtered or unexported fields
}

SqliteRepo implements datarepo/SimpleRepo

InitznPragmas IsInTx Tx Tx for write Conn only

func (*SqliteRepo) Begin 

func (p *SqliteRepo) Begin() error

Begin checks that there is no open Tx and then opens one. If one is already open, it returns an error "SQLITE_BUSY". (This could easily be replaced with a short wait of some tens of milliseconds, but AFAIK it already does this, and if it is still busy after a "long enough" wait, then throw a panic that says that we probably have deadlock caused by a failure to end a transaction.)

Note tho that in effect this is a queue for transactions, which cannot run concurrently. This is not really optimal, but it could be improved upon in the future. Perhaps an explicit queue for transactions. .

func (*SqliteRepo) BeginImmed 

func (p *SqliteRepo) BeginImmed() error

func (*SqliteRepo) BuildQueryStmt 

func (p *SqliteRepo) BuildQueryStmt(qs *DRU.QuerySpec) (string, error)

func (*SqliteRepo) Close 

func (p *SqliteRepo) Close() error

Close remembers the path.

func (*SqliteRepo) CloseLogWriter 

func (p *SqliteRepo) CloseLogWriter()

func (*SqliteRepo) Commit 

func (p *SqliteRepo) Commit() error

func (*SqliteRepo) CopyToBackup 

func (p *SqliteRepo) CopyToBackup() (string, error)

CopyToBackup makes a best effort but might fail if (for a file) the backup destination is a directory or has a permissions problem. The current DB is never affected.

FIXME: Use best practices to do this: make sure it is robust; this will need a check for the DB type. FIXME: If it is open, does it need to be closed - and then reopened after the copying ?

func (*SqliteRepo) CreateAppTables 

func (p *SqliteRepo) CreateAppTables() error

CreateAppTables creates the app's tables per already-supplied schema(ta); if the tables exist, they are emptied of data. It uses our simplified SQLite DB model wherein

  • Every column is either string ("TEXT") or int ("INTEGER"),
  • Every column is NOT NULL (because NULL is evil),
  • Every column has type checking (TBS), and
  • Every table has a primary index field, and
  • Every index (both primary and foreign) includes the full name of the table, which simplifies column creation and table cross-referencing (and in particular, JOINs).

.

func (*SqliteRepo) DBImplementationName 

func (p *SqliteRepo) DBImplementationName() D.DB_type

func (SqliteRepo) DbTblColsInDb 

func (pDB SqliteRepo) DbTblColsInDb(tableName string) ([]*D.DbColInDb, error)

DbTblColsInDb returns all column names & types for the specified table as found in the DB. .

func (*SqliteRepo) DoPragma 

func (p *SqliteRepo) DoPragma(s string)

func (*SqliteRepo) DoPragmas 

func (p *SqliteRepo) DoPragmas(s string) (string, error)

DoPragmas can handle multiline, but does expect the caller to provide all required instances of the PRAGMA keyword. The PRAGMA menu: https://www.sqlite.org/pragma.html

NOTE: "No error messages are generated if an unknown pragma is issued. Unknown pragmas are simply ignored. This means if there is a typo in a pragma statement, the library does not inform the user of the fact."

A pragma may have an optional schema-name before the pragma name.

PRAGMAs that return results and that have no side-effects can be accessed from ordinary SELECT statements as table-valued functions.

Arguments:

  • A pragma can take either zero or one argument.
  • The argument is may be either in parentheses or it may ae separated from the pragma name by an equal sign; the two syntaxes yield identical results.
  • In many pragmas, the argument is a boolean, and can be one of: 1 yes true on / 0 no false off
  • Keyword arguments can optionally appear in quotes. (Example: 'yes' [FALSE].)
  • Some pragmas takes a string literal as their argument. When pragma takes a keyword argument, it will usually also take a numeric equivalent as well. For example, "0" and "no" mean the same thing, as does "1" and "yes".
  • When querying the value of a setting, many pragmas return the number rather than the keyword.

.

func (SqliteRepo) DumpTableSchema_sqlite 

func (pDB SqliteRepo) DumpTableSchema_sqlite(tableName string) (string, error)

DumpTableSchema_sqlite returns the (SQLite) schema for the specified table as found in the DB. .

func (*SqliteRepo) EmptyAppTables 

func (p *SqliteRepo) EmptyAppTables() error

EmptyAllTables deletes (app-level) data from the app's tables but does not delete any tables (i.e. no DROP TABLE are done). The DB should be open when it is called (so that the connection object exists). The DB should have a path, but mainly just for error messages; the requirement could be removed.

NOTE: If a table does not exist, it has to be created. .

func (*SqliteRepo) Exec 

func (p *SqliteRepo) Exec(query string, args ...any) (sql.Result, error)

func (*SqliteRepo) Flush 

func (p *SqliteRepo) Flush() error

Flush forces WAL sync. Notes: https://turso.tech/blog/something-you-probably-want-to-know-about-if-youre-using-sqlite-in-golang-72547ad625f1

A "Rows" object represents a read operation to an SQLite DB. Unless rows is closed, explicitly by calling rows.Close() or implicitly by reading all the data from it, SQLite treats the operation as ongoing. It turns out that ongoing reads prevent checkpoint operation from advancing. As a result, one leaked Rows object can prevent the DB from ever transferring changes from the WAL file to the main DB file and truncating the WAL file. This means the WAL file grows indefinitely. At least until the process is restarted, which can take a long time for a server application. .

func (*SqliteRepo) GetTx 

func (p *SqliteRepo) GetTx() *sql.Tx

func (*SqliteRepo) Handle 

func (pSR *SqliteRepo) Handle() *sql.DB

Handle (noun, not verb) returns the dbx handle to the DB.

func (*SqliteRepo) IsInTx 

func (p *SqliteRepo) IsInTx() bool

func (*SqliteRepo) IsOpen 

func (p *SqliteRepo) IsOpen() bool

IsOpen also, if possible, pings the DB as a health check.

func (*SqliteRepo) IsSingleFile 

func (pSR *SqliteRepo) IsSingleFile() bool

IsURL returns true for sqlite.

func (*SqliteRepo) IsURL 

func (pSR *SqliteRepo) IsURL() bool

IsURL returns false for sqlite.

func (*SqliteRepo) MoveToBackup 

func (p *SqliteRepo) MoveToBackup() (string, error)

MoveToBackup makes a best effort but might fail if (for a file) the backup destination is a directory or has a permissions problem. The current Repo is renamed, so it seems to "disappear" from production./

FIXME: Use best practices to do this: make sure it is robust; this will need a check for the DB type. FIXME: Before moving, close it if it is open.

func (*SqliteRepo) NewCreateTableStmt 

func (pSR *SqliteRepo) NewCreateTableStmt(pTD *DRM.TableDetails) (string, error)

NewCreateTableStmt expects [TableDetails.IDName] to be set. .

func (*SqliteRepo) Open 

func (p *SqliteRepo) Open() error

Open should not do pragma-style initialization.

func (*SqliteRepo) Path 

func (pSR *SqliteRepo) Path() string

Path returns the filepath (for a multi-file DB type it could return dir/URL).

func (*SqliteRepo) Query 

func (p *SqliteRepo) Query(query string, args ...any) (*sql.Rows, error)

func (*SqliteRepo) QueryRow 

func (p *SqliteRepo) QueryRow(query string, args ...any) *sql.Row

func (*SqliteRepo) RestoreFromMostRecentBackup 

func (p *SqliteRepo) RestoreFromMostRecentBackup() (string, error)

func (*SqliteRepo) Rollback 

func (p *SqliteRepo) Rollback() error

func (*SqliteRepo) RunQuery0 

func (p *SqliteRepo) RunQuery0(*DRU.QuerySpec) (any, error)

func (*SqliteRepo) RunQuery1 

func (p *SqliteRepo) RunQuery1(*DRU.QuerySpec) (any, error)

func (*SqliteRepo) RunQueryN 

func (p *SqliteRepo) RunQueryN(*DRU.QuerySpec) ([]any, error)

func (*SqliteRepo) SetAppTables 

func (p *SqliteRepo) SetAppTables(appName string, cfg []DRM.TableDetails) error

SetAppTables specifies the schemata of the specified app's tables, which this interface creates and/or manages. Multiple calls, whether with tables previously specified or not before seen do not conflict; if a table name is repeated but with a different schema, the result is undefined. .

func (*SqliteRepo) SetLogWriter 

func (p *SqliteRepo) SetLogWriter(wrtr io.Writer) io.Writer

func (*SqliteRepo) Type 

func (pSR *SqliteRepo) Type() D.DB_type

Type returns "sqlite" (equiv.to "sqlite3").

func (*SqliteRepo) Verify 

func (p *SqliteRepo) Verify() error

Verify runs app-level sanity & consistency checks (things like foreign key validity should be delegated to DB setup).

type TypedRepo 

type TypedRepo[T any] struct {
	// contains filtered or unexported fields
}

func (*TypedRepo[T]) Create 

func (r *TypedRepo[T]) Create(t T)

func (*TypedRepo[T]) Get 

func (r *TypedRepo[T]) Get(id uint) (*T, error)

type TypedRepoer 

type TypedRepoer[T any] interface {
	RM.RowModel // Must satisfy this interface
	*T          // Must be a pointer

	Insert(T) (int, error)
	Update(T) error
	Delete(T) error
	DeleteByID(int) error
	SelectByID(int) (T, error)

	SelectByQuery(DRU.QuerySpec) (T, error)
	SelectsByQuery(DRU.QuerySpec) ([]T, []error)

	Inserts([]T) ([]int, []error)
	Updates([]T) []error
	Deletes([]T) []error
	DeleteByIDs([]int) []error
	SelectByIDs([]int) ([]T, []error)

	SelectAll() ([]T, error)
	DeleteAll() error
}

TypedRepoer does

  • Insert (add,Create,save,store)
  • Select (get,Read, find,list)
  • Update (mod,Update,modify)
  • Delete (del,Delete,remove)

.

type Zork 

type Zork struct {
	I int
	S string
}

func (*Zork) GetByID 

func (p *Zork) GetByID(id int) Zork

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.