sqlite

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) (DRP.SimpleRepo, 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 (p *SqliteDBManager) NewAtPath(aPath string) (*SqliteRepo, error) {

func (*SqliteDBManager) OpenAtPath ¶

func (p *SqliteDBManager) OpenAtPath(path string) (DRP.SimpleRepo, error)

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

func (*SqliteDBManager) OpenExistingAtPath ¶

func (p *SqliteDBManager) OpenExistingAtPath(aPath string) (DRP.SimpleRepo, 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 (p *SqliteDBManager) OpenExistingAtPath(aPath string) (*SqliteRepo, error) {

func (*SqliteDBManager) ReadonlyPragma ¶

func (p *SqliteDBManager) ReadonlyPragma() string

func (*SqliteRepo) Close ¶

func (p *SqliteRepo) Close() error

Close remembers the path.

func (*SqliteRepo) CloseLogWriter ¶

func (p *SqliteRepo) CloseLogWriter()

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.

NOTE: Errors appeared here because each table was in map twice, under both StorNam and DispName, so that was rolled back. .

func (*SqliteRepo) EngineUnique ¶

func (pSR *SqliteRepo) EngineUnique(dbOp string, tableName string, anID int, pRMbuf DRM.RowModel) (int, error)

EngineUnique acts on a single DB record, based on the value of the primary key (except of course for INSERT).

(It was thought that instead of using only the ID, there could be a WHERE clause that uses any column specified to be UNIQUE, but actually that approach makes no sense.)

The basic signature is (int,error) = func(op,table,int,buffer): One of four basic actions is performed (listed as SQL/CRUD/HTTP):

• INSERT / Create / POST (record in) (returns new-ID)
• UPDATE / Update / PUT (record in) (returns 0/1)
• SELECT / Retriv / GET (ID in) (returns 0/1 + record)
• DELETE / Delete / DELETE (ID in) (returns 0/1)
• let nAR = nr of records affected
• (newID,e) = insert(0,inbuffer) // optimize for use in batches
• (nAR,e) = update(anID,inbuffer) // anID can be -1, else match buffer's
• (nAR,e) = select(anID,outbuffer) // anID >= 0
• (nAR,e) = delete(anID,nil) // anID >= 0; buffer OK, then ID's match

It takes four input arguments:

• The DB operation, one of the four listed above; the dbOp is specified by the first letter (only!) of the argument dbOp
• The name of the DB table (case-insensitive)
• A "whereSpec" of column name and column value (not used for INSERT; as a convenience, if the name is "ID", it is modified to be {tableNeme}_ID (NOTE: We might just use an ID passed in in the buffer.)
• A pointer to a buffer, used for input (if INSERT or UPDATE) or output (if SELECT); for INSERT, the buffer is left unmodified and the ID of the new record is returned in the second return value (the int)
• Note that the buffer pointer is an interface, implemented by a pointer to a struct; this may be usable by GO generics

It returns two values:

• An error: if it is non-nil, the other (i.e. int) return value is invalid
• An int that is (if INSERT) the newly-added row ID (else) 0 or 1 to indicate how many records were (i.e. whether a record was) affected.

If the call to this func is inside a Transaction, we probably need to make it available to this func.

NOTE: When using whereSpec, if a record is not found, this is indicated by the first return value (the int), NOT by the second return value (the error, which is reserved for when the DB rejects the SQL.

NOTE: If anID >= 0 and buffer is non-nil, and anID does not match the ID of the record in the buffer, the function panics.

NOTE: Also implement COUNT(*) ? Perhaps as "K" = Kount.

TODO: TD.pCSVs could also use DB.Prepare to gather sql.Stmt's, but then we have to pass in a Connection, which screws up modularity pretty severely.

TODO: switch on dbOp to call a new mini func that assembles the SQL statement.

TODO: Use Result.RowsAffected https://pkg.go.dev/database/sql#Result RowsAffected returns the number of rows affected by an update, insert, or delete. Not every DB or driver supports it. [altho mattn/SQLite does.] RowsAffected() (int64, error)

NOTE: When writing the multi-row version of this, be sure to call Rows.Cloe() .

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) Handle ¶

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

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

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) LogWriter ¶

func (p *SqliteRepo) LogWriter() io.Writer

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) RegisterAppTables ¶

func (p *SqliteRepo) RegisterAppTables(appName string, pTDs []*DRM.TableDetails) error

RegisterAppTables processes the schemata of the specified app's tables, which this interface creates and/or manages. This includes filling many fields in struct [TableDetails].

It registers them in a singleton map in package datarepo/sqlite. However this func is left as a method of a SimpleRepo so that future corrections are easier.

Multiple calls to this do not conflict, whether with tables previously specified or not before; if a table name is repeated but with a different schema, the result is undefined.

• StorName: the name of the table IN THE DB - a "long name"
• DispName: a short name (3 ltrs!) for use in building up names

.

func (*SqliteRepo) RestoreFromMostRecentBackup ¶

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

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).