Documentation ¶
Index ¶
- type SQLite
- func (svr *SQLite) Begin() error
- func (svr *SQLite) Close() error
- func (svr *SQLite) Commit() error
- func (svr *SQLite) ExecByNamedMapParam(actionQuery string, args map[string]interface{}) SQLiteResult
- func (svr *SQLite) ExecByOrdinalParams(actionQuery string, args ...interface{}) SQLiteResult
- func (svr *SQLite) ExecByStructParam(actionQuery string, args interface{}) SQLiteResult
- func (svr *SQLite) GetDsn() (string, error)
- func (svr *SQLite) GetRowsByNamedMapParam(query string, args map[string]interface{}) (*sqlx.Rows, error)
- func (svr *SQLite) GetRowsByOrdinalParams(query string, args ...interface{}) (*sqlx.Rows, error)
- func (svr *SQLite) GetRowsByStructParam(query string, args interface{}) (*sqlx.Rows, error)
- func (svr *SQLite) GetScalarNullString(query string, args ...interface{}) (retVal sql.NullString, retNotFound bool, retErr error)
- func (svr *SQLite) GetScalarString(query string, args ...interface{}) (retVal string, retNotFound bool, retErr error)
- func (svr *SQLite) GetSingleRow(query string, args ...interface{}) (*sqlx.Row, error)
- func (svr *SQLite) GetStruct(dest interface{}, query string, args ...interface{}) (notFound bool, retErr error)
- func (svr *SQLite) GetStructSlice(dest interface{}, query string, args ...interface{}) (notFound bool, retErr error)
- func (svr *SQLite) Open() error
- func (svr *SQLite) Ping() error
- func (svr *SQLite) Rollback() error
- func (svr *SQLite) ScanColumnsByRow(row *sqlx.Row, dest ...interface{}) (notFound bool, err error)
- func (svr *SQLite) ScanSlice(rows *sqlx.Rows, dest []interface{}) (endOfRows bool, err error)
- func (svr *SQLite) ScanSliceByRow(row *sqlx.Row, dest []interface{}) (notFound bool, err error)
- func (svr *SQLite) ScanStruct(rows *sqlx.Rows, dest interface{}) (endOfRows bool, err error)
- func (svr *SQLite) ScanStructByRow(row *sqlx.Row, dest interface{}) (notFound bool, err error)
- type SQLiteResult
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
This section is empty.
Types ¶
type SQLite ¶
type SQLite struct { // SQLite database connection properties DatabasePath string // including path, file name, and extension Mode string // mode=ro: readOnly; rw: readwrite; rwc: readWriteCreate; memory: inMemoryOnly (set default to rwc) JournalMode string // _journal_mode=DELETE, MEMORY, WAL (set default to WAL) Synchronous string // _synchronous=0: OFF; 1: NORMAL; 2: FULL; 3: EXTRA (set default to NORMAL) BusyTimeoutMS int // _busy_timeout=milliseconds // contains filtered or unexported fields }
SQLite struct encapsulates the SQLite database access functionality (using sqlx package)
DatabasePath = full path to the sqlite db file with file name and extension Mode = ro (ReadOnly), rw (ReadWrite), rwc (ReadWriteCreate < Default), memory (In-Memory) JournalMode = DELETE, MEMORY, WAL (< Default) Synchronous = 0 (OFF), 1 (NORMAL < Default), 2 (FULL), 3 (EXTRA) BusyTimeoutMS = 0 if not specified; > 0 if specified
func (*SQLite) Begin ¶
Begin starts a database transaction, and stores the transaction object until commit or rollback
func (*SQLite) ExecByNamedMapParam ¶
func (svr *SQLite) ExecByNamedMapParam(actionQuery string, args map[string]interface{}) SQLiteResult
ExecByNamedMapParam executes action query string with named map containing parameters to return result, if error, returns error object within result [ Syntax ]
- in sql = instead of defining ordinal parameters ?, each parameter in sql does not need to be ordinal, rather define with :xyz (must have : in front of param name), where xyz is name of parameter, such as :customerID
- in go = setup a map variable: var p = make(map[string]interface{})
- in go = to set values into map variable: p["xyz"] = abc where xyz is the parameter name matching the sql :xyz (do not include : in go map "xyz") where abc is the value of the parameter value, whether string or other data types note: in using map, just add additional map elements using the p["xyz"] = abc syntax note: if parameter value can be a null, such as nullint, nullstring, use util.ToNullTime(), ToNullInt(), ToNullString(), etc.
- in go = when calling this function passing the map variable, simply pass the map variable p into the args parameter
[ Parameters ]
actionQuery = sql action query, with named parameters using :xyz syntax args = required, the map variable of the named parameters
[ Return Values ]
- SQLiteResult = represents the sql action result received (including error info if applicable)
func (*SQLite) ExecByOrdinalParams ¶
func (svr *SQLite) ExecByOrdinalParams(actionQuery string, args ...interface{}) SQLiteResult
ExecByOrdinalParams executes action query string and parameters to return result, if error, returns error object within result [ Parameters ]
actionQuery = sql action query, optionally having parameters marked as ?, where each represents a parameter position args = conditionally required if positioned parameters are specified, must appear in the same order as the positional parameters
[ Return Values ]
- SQLiteResult = represents the sql action result received (including error info if applicable)
func (*SQLite) ExecByStructParam ¶
func (svr *SQLite) ExecByStructParam(actionQuery string, args interface{}) SQLiteResult
ExecByStructParam executes action query string with struct containing parameters to return result, if error, returns error object within result, the struct fields' struct tags must match the parameter names, such as: struct tag `db:"customerID"` must match parameter name in sql as ":customerID" [ Syntax ]
- in sql = instead of defining ordinal parameters ?, each parameter in sql does not need to be ordinal, rather define with :xyz (must have : in front of param name), where xyz is name of parameter, such as :customerID
- in go = using a struct to contain fields to match parameters, make sure struct tags match to the sql parameter names, such as struct tag `db:"customerID"` must match parameter name in sql as ":customerID" (the : is not part of the match)
[ Parameters ]
actionQuery = sql action query, with named parameters using :xyz syntax args = required, the struct variable, whose fields having struct tags matching sql parameter names
[ Return Values ]
- SQLiteResult = represents the sql action result received (including error info if applicable)
func (*SQLite) GetDsn ¶
GetDsn serializes SQLite database dsn to connection string, for use in database connectivity
func (*SQLite) GetRowsByNamedMapParam ¶
func (svr *SQLite) GetRowsByNamedMapParam(query string, args map[string]interface{}) (*sqlx.Rows, error)
GetRowsByNamedMapParam performs query with named map containing parameters to get ROWS of result, and returns *sqlx.Rows [ Syntax ]
- in sql = instead of defining ordinal parameters ?, each parameter in sql does not need to be ordinal, rather define with :xyz (must have : in front of param name), where xyz is name of parameter, such as :customerID
- in go = setup a map variable: var p = make(map[string]interface{})
- in go = to set values into map variable: p["xyz"] = abc where xyz is the parameter name matching the sql :xyz (do not include : in go map "xyz") where abc is the value of the parameter value, whether string or other data types note: in using map, just add additional map elements using the p["xyz"] = abc syntax note: if parameter value can be a null, such as nullint, nullstring, use util.ToNullTime(), ToNullInt(), ToNullString(), etc.
- in go = when calling this function passing the map variable, simply pass the map variable p into the args parameter
[ Parameters ]
query = sql query, optionally having parameters marked as :xyz for each parameter name, where each represents a named parameter args = required, the map variable of the named parameters
[ Return Values ]
- *sqlx.Rows = pointer to sqlx.Rows; or nil if no rows yielded
- if error != nil, then error is encountered (if error == sql.ErrNoRows, then error is treated as nil, and sqlx.Rows is returned as nil)
[ Ranged Loop & Scan ]
- to loop, use: for _, r := range rows
- to scan, use: r.Scan(&x, &y, ...), where r is the row struct in loop, where &x &y etc are the scanned output value (scan in order of select columns sequence)
[ Continuous Loop & Scan ]
- Continuous loop until endOfRows = true is yielded from ScanSlice() or ScanStruct()
- ScanSlice(): accepts *sqlx.Rows, scans rows result into target pointer slice (if no error, endOfRows = true is returned)
- ScanStruct(): accepts *sqlx.Rows, scans current single row result into target pointer struct, returns endOfRows as true of false; if endOfRows = true, loop should stop
func (*SQLite) GetRowsByOrdinalParams ¶
GetRowsByOrdinalParams performs query with optional variadic parameters to get ROWS of result, and returns *sqlx.Rows [ Parameters ]
query = sql query, optionally having parameters marked as ?, where each represents a parameter position args = conditionally required if positioned parameters are specified, must appear in the same order as the positional parameters
[ Return Values ]
- *sqlx.Rows = pointer to sqlx.Rows; or nil if no rows yielded
- if error != nil, then error is encountered (if error == sql.ErrNoRows, then error is treated as nil, and sqlx.Rows is returned as nil)
[ Ranged Loop & Scan ]
- to loop, use: for _, r := range rows
- to scan, use: r.Scan(&x, &y, ...), where r is the row struct in loop, where &x &y etc are the scanned output value (scan in order of select columns sequence)
[ Continuous Loop & Scan ]
- Continuous loop until endOfRows = true is yielded from ScanSlice() or ScanStruct()
- ScanSlice(): accepts *sqlx.Rows, scans rows result into target pointer slice (if no error, endOfRows = true is returned)
- ScanStruct(): accepts *sqlx.Rows, scans current single row result into target pointer struct, returns endOfRows as true of false; if endOfRows = true, loop should stop
func (*SQLite) GetRowsByStructParam ¶
GetRowsByStructParam performs query with a struct as parameter input to get ROWS of result, and returns *sqlx.Rows [ Syntax ]
- in sql = instead of defining ordinal parameters ?, each parameter in sql does not need to be ordinal, rather define with :xyz (must have : in front of param name), where xyz is name of parameter, such as :customerID
- in sql = important: the :xyz defined where xyz portion of parameter name must batch the struct tag's `db:"xyz"`
- in go = a struct containing struct tags that matches the named parameters will be set with values, and passed into this function's args parameter input
- in go = when calling this function passing the struct variable, simply pass the struct variable into the args parameter
[ Parameters ]
query = sql query, optionally having parameters marked as :xyz for each parameter name, where each represents a named parameter args = required, the struct variable where struct fields' struct tags match to the named parameters
[ Return Values ]
- *sqlx.Rows = pointer to sqlx.Rows; or nil if no rows yielded
- if error != nil, then error is encountered (if error == sql.ErrNoRows, then error is treated as nil, and sqlx.Rows is returned as nil)
[ Ranged Loop & Scan ]
- to loop, use: for _, r := range rows
- to scan, use: r.Scan(&x, &y, ...), where r is the row struct in loop, where &x &y etc are the scanned output value (scan in order of select columns sequence)
[ Continuous Loop & Scan ]
- Continuous loop until endOfRows = true is yielded from ScanSlice() or ScanStruct()
- ScanSlice(): accepts *sqlx.Rows, scans rows result into target pointer slice (if no error, endOfRows = true is returned)
- ScanStruct(): accepts *sqlx.Rows, scans current single row result into target pointer struct, returns endOfRows as true of false; if endOfRows = true, loop should stop
func (*SQLite) GetScalarNullString ¶
func (svr *SQLite) GetScalarNullString(query string, args ...interface{}) (retVal sql.NullString, retNotFound bool, retErr error)
GetScalarNullString performs query with optional variadic parameters, and returns the first row and first column value in sql.NullString{} data type [ Parameters ]
query = sql query, optionally having parameters marked as ?, where each represents a parameter position args = conditionally required if positioned parameters are specified, must appear in the same order as the positional parameters
[ Return Values ]
- retVal = string value of scalar result, if no value, sql.NullString{} is returned
- retNotFound = now row found
- if error != nil, then error is encountered (if error == sql.ErrNoRows, then error is treated as nil, and retVal is returned as sql.NullString{})
func (*SQLite) GetScalarString ¶
func (svr *SQLite) GetScalarString(query string, args ...interface{}) (retVal string, retNotFound bool, retErr error)
GetScalarString performs query with optional variadic parameters, and returns the first row and first column value in string data type [ Parameters ]
query = sql query, optionally having parameters marked as ?, where each represents a parameter position args = conditionally required if positioned parameters are specified, must appear in the same order as the positional parameters
[ Return Values ]
- retVal = string value of scalar result, if no value, blank is returned
- retNotFound = now row found
- if error != nil, then error is encountered (if error == sql.ErrNoRows, then error is treated as nil, and retVal is returned as blank)
func (*SQLite) GetSingleRow ¶
GetSingleRow performs query with optional variadic parameters to get a single ROW of result, and returns *sqlx.Row (This function returns SINGLE ROW) [ Parameters ]
query = sql query, optionally having parameters marked as ?, where each represents a parameter position args = conditionally required if positioned parameters are specified, must appear in the same order as the positional parameters
[ Return Values ]
- *sqlx.Row = pointer to sqlx.Row; or nil if no row yielded
- if error != nil, then error is encountered (if error = sql.ErrNoRows, then error is treated as nil, and sqlx.Row is returned as nil)
[ Scan Values ]
- Use row.Scan() and pass in pointer or address of variable to receive scanned value outputs (Scan is in the order of column sequences in select statement)
[ WARNING !!! ]
WHEN USING Scan(), MUST CHECK Scan Result Error for sql.ErrNoRow status SUGGESTED TO USE ScanColumnsByRow() Instead of Scan()
func (*SQLite) GetStruct ¶
func (svr *SQLite) GetStruct(dest interface{}, query string, args ...interface{}) (notFound bool, retErr error)
GetStruct performs query with optional variadic parameters, and unmarshal single result row into single target struct, such as: Customer struct where one row of data represent a customer [ Parameters ]
dest = pointer to struct or address of struct, this is the result of row to be marshaled into this struct query = sql query, optionally having parameters marked as ?, where each represents a parameter position args = conditionally required if positioned parameters are specified, must appear in the same order as the positional parameters
[ Return Values ]
- notFound = indicates no rows found in query (aka sql.ErrNoRows), if error is detected, notFound is always false
- if error != nil, then error is encountered (if error == sql.ErrNoRows, then error is treated as nil, and dest is nil)
func (*SQLite) GetStructSlice ¶
func (svr *SQLite) GetStructSlice(dest interface{}, query string, args ...interface{}) (notFound bool, retErr error)
GetStructSlice performs query with optional variadic parameters, and unmarshal result rows into target struct slice, in essence, each row of data is marshaled into the given struct, and multiple struct form the slice, such as: []Customer where each row represent a customer, and multiple customers being part of the slice [ Parameters ]
dest = pointer to the struct slice or address of struct slice, this is the result of rows to be marshaled into struct slice query = sql query, optionally having parameters marked as ?, where each represents a parameter position args = conditionally required if positioned parameters are specified, must appear in the same order as the positional parameters
[ Return Values ]
- notFound = indicates no rows found in query (aka sql.ErrNoRows), if error is detected, notFound is always false
- if error != nil, then error is encountered (if error == sql.ErrNoRows, then error is treated as nil, and dest is nil)
[ Notes ]
- if error == nil, and len(dest struct slice) == 0 then zero struct slice result
func (*SQLite) Open ¶
Open a database by connecting to it, using the dsn properties defined in the struct fields
func (*SQLite) Rollback ¶
Rollback cancels pending database changes for the current transaction and clears out transaction object
func (*SQLite) ScanColumnsByRow ¶
ScanColumnsByRow accepts a *sqlx row, and scans specific columns into dest outputs, this is different than ScanSliceByRow or ScanStructByRow because this function allows specific extraction of column values into target fields, (note: this function must extra all row column values to dest variadic parameters as present in the row parameter) [ Parameters ]
row = *sqlx.Row representing the row containing columns to extract, note that this function MUST extract all columns from this row dest = MUST BE pointer (or &variable) to target variable to receive the column value, data type must match column data type value, and sequence of dest must be in the order of columns sequence
[ Return Values ]
- notFound = true if no row is found in current scan
- if error != nil, then error is encountered (if error == sql.ErrNoRows, then error is treated as nil, and dest is set as nil and notFound is true)
[ Example ]
- assuming: Select CustomerID, CustomerName, Address FROM Customer Where CustomerPhone='123';
- assuming: row // *sqlx.Row derived from GetSingleRow() or specific row from GetRowsByOrdinalParams() / GetRowsByNamedMapParam() / GetRowsByStructParam()
- assuming: var CustomerID int64 var CustomerName string var Address string
- notFound, err := svr.ScanColumnsByRow(row, &CustomerID, &CustomerName, &Address)
func (*SQLite) ScanSlice ¶
ScanSlice takes in *sqlx.Rows as parameter, will invoke the rows.Next() to advance to next row position, and marshals current row's column values into a pointer reference to a slice, this enables us to quickly retrieve a slice of current row column values without knowing how many columns or names or columns (columns appear in select columns sequence), to loop thru all rows, use range, and loop until endOfRows = true; the dest is nil if no columns found; the dest is pointer of slice when columns exists [ Parameters ]
rows = *sqlx.Rows dest = pointer or address to slice, such as: variable to "*[]string", or variable to "&cList for declaration cList []string"
[ Return Values ]
- endOfRows = true if this action call yielded end of rows, meaning stop further processing of current loop
- if error != nil, then error is encountered (if error == sql.ErrNoRows, then error is treated as nil, and dest is set as nil)
func (*SQLite) ScanSliceByRow ¶
ScanSliceByRow takes in *sqlx.Row as parameter, and marshals current row's column values into a pointer reference to a slice, this enables us to quickly retrieve a slice of current row column values without knowing how many columns or names or columns (columns appear in select columns sequence) [ Parameters ]
row = *sqlx.Row dest = pointer or address to slice, such as: variable to "*[]string", or variable to "&cList for declaration cList []string"
[ Return Values ]
- notFound = true if no row is found in current scan
- if error != nil, then error is encountered (if error == sql.ErrNoRows, then error is treated as nil, and dest is set as nil and notFound is true)
func (*SQLite) ScanStruct ¶
ScanStruct takes in *sqlx.Rows, will invoke the rows.Next() to advance to next row position, and marshals current row's column values into a pointer reference to a struct, the struct fields and row columns must match for both name and sequence position, this enables us to quickly convert the row's columns into a defined struct automatically, to loop thru all rows, use range, and loop until endOfRows = true; the dest is nil if no columns found; the dest is pointer of struct when mapping is complete [ Parameters ]
rows = *sqlx.Rows dest = pointer or address to struct, such as: variable to "*Customer", or variable to "&c for declaration c Customer"
[ Return Values ]
- endOfRows = true if this action call yielded end of rows, meaning stop further processing of current loop
- if error != nil, then error is encountered (if error == sql.ErrNoRows, then error is treated as nil, and dest is set as nil)
func (*SQLite) ScanStructByRow ¶
ScanStructByRow takes in *sqlx.Row, and marshals current row's column values into a pointer reference to a struct, the struct fields and row columns must match for both name and sequence position, this enables us to quickly convert the row's columns into a defined struct automatically, the dest is nil if no columns found; the dest is pointer of struct when mapping is complete [ Parameters ]
row = *sqlx.Row dest = pointer or address to struct, such as: variable to "*Customer", or variable to "&c for declaration c Customer"
[ Return Values ]
- notFound = true if no row is found in current scan
- if error != nil, then error is encountered (if error == sql.ErrNoRows, then error is treated as nil, and dest is set as nil and notFound is true)
type SQLiteResult ¶
type SQLiteResult struct { RowsAffected int64 NewlyInsertedID int64 // ONLY FOR INSERT, ONLY IF AUTO_INCREMENT PRIMARY KEY (Custom PK ID Will Have This Field as 0 Always) Err error }
SQLiteResult defines sql action query result info [ Notes ]
NewlyInsertedID = ONLY FOR INSERT, ONLY IF AUTO_INCREMENT PRIMARY KEY (Custom PK ID Will Have This Field as 0 Always)