sqlite

package
v1.0.1 Latest Latest
Warning

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

 Go to latest Published: Aug 29, 2020 License: Apache-2.0 Imports: 6 Imported by: 0

Documentation

Index

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 

func (svr *SQLite) Begin() error

Begin starts a database transaction, and stores the transaction object until commit or rollback

func (*SQLite) Close 

func (svr *SQLite) Close() error

Close will close the database connection and set db to nil

func (*SQLite) Commit 

func (svr *SQLite) Commit() error

Commit finalizes a database transaction, and commits changes to database

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 ]

  1. 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
  2. in go = setup a map variable: var p = make(map[string]interface{})
  3. 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.
  4. 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 ]

  1. 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 ]

  1. 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 ]

  1. 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
  2. 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 ]

  1. SQLiteResult = represents the sql action result received (including error info if applicable)

func (*SQLite) GetDsn 

func (svr *SQLite) GetDsn() (string, error)

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 ]

  1. 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
  2. in go = setup a map variable: var p = make(map[string]interface{})
  3. 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.
  4. 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 ]

  1. *sqlx.Rows = pointer to sqlx.Rows; or nil if no rows yielded
  2. 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 ]

  1. to loop, use: for _, r := range rows
  2. 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 ]

  1. Continuous loop until endOfRows = true is yielded from ScanSlice() or ScanStruct()
  2. ScanSlice(): accepts *sqlx.Rows, scans rows result into target pointer slice (if no error, endOfRows = true is returned)
  3. 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 

func (svr *SQLite) GetRowsByOrdinalParams(query string, args ...interface{}) (*sqlx.Rows, error)

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 ]

  1. *sqlx.Rows = pointer to sqlx.Rows; or nil if no rows yielded
  2. 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 ]

  1. to loop, use: for _, r := range rows
  2. 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 ]

  1. Continuous loop until endOfRows = true is yielded from ScanSlice() or ScanStruct()
  2. ScanSlice(): accepts *sqlx.Rows, scans rows result into target pointer slice (if no error, endOfRows = true is returned)
  3. 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 

func (svr *SQLite) GetRowsByStructParam(query string, args interface{}) (*sqlx.Rows, error)

GetRowsByStructParam performs query with a struct as parameter input to get ROWS of result, and returns *sqlx.Rows [ Syntax ]

  1. 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
  2. in sql = important: the :xyz defined where xyz portion of parameter name must batch the struct tag's `db:"xyz"`
  3. 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
  4. 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 ]

  1. *sqlx.Rows = pointer to sqlx.Rows; or nil if no rows yielded
  2. 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 ]

  1. to loop, use: for _, r := range rows
  2. 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 ]

  1. Continuous loop until endOfRows = true is yielded from ScanSlice() or ScanStruct()
  2. ScanSlice(): accepts *sqlx.Rows, scans rows result into target pointer slice (if no error, endOfRows = true is returned)
  3. 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 ]

  1. retVal = string value of scalar result, if no value, sql.NullString{} is returned
  2. retNotFound = now row found
  3. 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 ]

  1. retVal = string value of scalar result, if no value, blank is returned
  2. retNotFound = now row found
  3. 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 

func (svr *SQLite) GetSingleRow(query string, args ...interface{}) (*sqlx.Row, error)

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 ]

  1. *sqlx.Row = pointer to sqlx.Row; or nil if no row yielded
  2. 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 ]

  1. 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 ]

  1. notFound = indicates no rows found in query (aka sql.ErrNoRows), if error is detected, notFound is always false
  2. 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 ]

  1. notFound = indicates no rows found in query (aka sql.ErrNoRows), if error is detected, notFound is always false
  2. if error != nil, then error is encountered (if error == sql.ErrNoRows, then error is treated as nil, and dest is nil)

[ Notes ]

  1. if error == nil, and len(dest struct slice) == 0 then zero struct slice result

func (*SQLite) Open 

func (svr *SQLite) Open() error

Open a database by connecting to it, using the dsn properties defined in the struct fields

func (*SQLite) Ping 

func (svr *SQLite) Ping() error

Ping tests if current database connection is still active and ready

func (*SQLite) Rollback 

func (svr *SQLite) Rollback() error

Rollback cancels pending database changes for the current transaction and clears out transaction object

func (*SQLite) ScanColumnsByRow 

func (svr *SQLite) ScanColumnsByRow(row *sqlx.Row, dest ...interface{}) (notFound bool, err error)

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 ]

  1. notFound = true if no row is found in current scan
  2. 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 ]

  1. assuming: Select CustomerID, CustomerName, Address FROM Customer Where CustomerPhone='123';
  2. assuming: row // *sqlx.Row derived from GetSingleRow() or specific row from GetRowsByOrdinalParams() / GetRowsByNamedMapParam() / GetRowsByStructParam()
  3. assuming: var CustomerID int64 var CustomerName string var Address string
  4. notFound, err := svr.ScanColumnsByRow(row, &CustomerID, &CustomerName, &Address)

func (*SQLite) ScanSlice 

func (svr *SQLite) ScanSlice(rows *sqlx.Rows, dest []interface{}) (endOfRows bool, err error)

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 ]

  1. endOfRows = true if this action call yielded end of rows, meaning stop further processing of current loop
  2. 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 

func (svr *SQLite) ScanSliceByRow(row *sqlx.Row, dest []interface{}) (notFound bool, err error)

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 ]

  1. notFound = true if no row is found in current scan
  2. 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 

func (svr *SQLite) ScanStruct(rows *sqlx.Rows, dest interface{}) (endOfRows bool, err error)

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 ]

  1. endOfRows = true if this action call yielded end of rows, meaning stop further processing of current loop
  2. 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 

func (svr *SQLite) ScanStructByRow(row *sqlx.Row, dest interface{}) (notFound bool, err error)

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 ]

  1. notFound = true if no row is found in current scan
  2. 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)

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.