sqlitehench

package module
v2.0.1+incompatible Latest Latest
Warning

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

 Go to latest Published: Aug 22, 2023 License: MIT Imports: 21 Imported by: 0

README

A Go environment for SQLite

Make database calls with the all too familiar functions

Database Driver

This package uses the SQLite driver written by Yasuhiro Matsumoto/G.J.R. Timmer, although you can use go-sqlitehench with an SQLite Go driver of your choice.

Opening/Closing database files

Most functions come in two folds: keeping the db open, or close it, after each write operation. To avoid file corruption or lingering locks (where a db lock persists even after the db is 'closed'), the database file can be closed after each operation. This may not be suitable for every scenario (i.e. while importing large number of records, shared env.,...).

In most cases, you would not have to close an SQLite database after each operation. However, in a high volume situation, a database can get locked (due to SQLite's single-write mechanism); and (in some situations) may even get corrupted (i.e. if journal file(s) get out-of-sync)...

Functions

Functions are basically wrapped into the following:

Close database after operation is completed
  • ExecuteScalare..................... gets one value
  • ExecuteNonQuery................ executes an SQL statement
  • GetDataMap......................... gets a []map of rows/cols
Keep database open after operation is completed
  • ExecuteScalarePointToDB............ gets one value
  • ExecuteNonQueryPointToDB........ executes an SQL statement
  • GetDataMapPointToDB................. gets a []map of rows/cols
Other
  • GetDataTable........................ gets a table snapshot in form of rows/cols.
  • InsertDataTable..................... inserts collections.Table into a table.
  • BulkInsert.............................. inserts large sets of data into a database.
  • CloneDatabase..................... creates a (local) copy of a database.
  • GetDataTableLongQuery.......goes through a query page-by-page and keeps adding results to a DataTable; it also notifies the caller via an event.
Performance

The default journal mode is WAL (PRAGMA journal_mode = WAL). This is applied when a database is opened -- via

// PRAGMA journal_mode = WAL;
db.Exec(d.PRAGMA)

The WAL mode is proportionately faster than the default journal mode, however, coming up with a performance benchmark depends on many factors...please, see https://sqlite.org/pragma.html#pragma_journal_mode for more details on the SQLite journal modes.

Shrinking database files

Shrinking is done automatically, when ShrinkDatabaseFiles = true; so there is no need to shrink a db file explicitly - as every database file that interacts with the package is added to a watchlist. Each db file is shrunk accordingly within a short period of time. If a db file is not used for more than ~one hour, it will be excluded from the watchlist. Note that if PRAGMA auto_vacuum is set, the shrink-daemon will not be started.

Usage Example
package main

import (
	"fmt"

	"github.com/kambahr/go-sqlitehench"
)

var pragma []string = []string{
	"PRAGMA journal_mode = WAL",
	"PRAGMA temp_store = MEMORY",
	"PRAGMA synchronous = OFF",
}

func main() {
	mysqlite := sqlitehench.NewDBAccess(
		sqlitehench.DBAccess{
			ShrinkDatabaseFiles: false,
			PRAGMA:              pragma,
		})
	dbPathSrc := "<path to the source SQLite database>"
	dbPathDest := "<path to the destination SQLite database>"

	mysqlite.CloneDatabase(dbPathSrc, dbPathDest, notify)

	sqlx := `select <columns> from <table name> limit 20000 offset 1000`
	mytbl, _ := mysqlite.GetDataTable(sqlx, dbPathSrc)

	mysqlite.BulkInsert(mytbl, dbPathSrc, notify)

}
func notify(status string) {
	fmt.Println(status)
}

Documentation

Overview

Copyright (c) 2021 Kamiar Bahri

Index

Constants

View Source 
const (
	Err_DatabaseIsLocked      = "database is locked"
	Err_FileIsNotDatabase     = "file is not a database"
	Err_DatabaseFileNotExists = "database file does not exist"
	Err_NoRowsFound           = "no rows found"
)

Variables

This section is empty.

Functions

func Decrypt 

func Decrypt(data []byte, passphrase string) ([]byte, error)

Decrypt decryptes an array of bytes using the AES algorythm.

func DecryptFile 

func DecryptFile(p string, pwdPhrase string) error

DecryptFile decryptes a file using the AES algorythm.

func Encrypt 

func Encrypt(plainData []byte, passphrase string) ([]byte, error)

Encrypt encryptes an array of bytes using the AES algorythm.

func EncryptFile 

func EncryptFile(p string, pwdPhrase string) error

Encrypt encryptes a file using the AES algorythm.

Types

type CollectionInfo 

type CollectionInfo struct {
	RecordCount  int
	TotalPages   int
	PageSize     int
	PageNo       int
	PositionFrom int
	PositionTo   int
}

CollectionInfo holds Grid info for use in the client javascript.

type DBAccess 

type DBAccess struct {
	MaxIdleConns        uint
	MaxOpenConns        uint
	PRAGMA              []string
	ShrinkDatabaseFiles bool

	// ShrinkWatchList keeps a list of sqlite database
	// file paths that are to be shrinked in a set internval.
	ShrinkWatchList []string
	// contains filtered or unexported fields
}

func NewDBAccess 

func NewDBAccess(d DBAccess) *DBAccess

func (*DBAccess) AddDBFileToShrinkWatchList 

func (d *DBAccess) AddDBFileToShrinkWatchList(dbFilePath string)

AddDBFileToShrinkWatchList adds an SQLite database file path to a watch list for monitoring.

func (*DBAccess) AppendDataTable 

func (d *DBAccess) AppendDataTable(dtSrce *collc.Table, dtDest *collc.Table) (*collc.Table, error)

func (*DBAccess) BulkInsert 

func (dc *DBAccess) BulkInsert(dtSrc *collc.Table, dbFilePath string, notify func(status string)) error

BulkInsert inserts a DataTable into a database.

func (*DBAccess) CloneDataTable 

func (d *DBAccess) CloneDataTable(dtSrce *collc.Table) (*collc.Table, error)

CloneDataTable retuns a copy of a DataTable.

func (*DBAccess) CloneDatabase 

func (dc *DBAccess) CloneDatabase(srcFilePath string, destFilePath string, notify func(status string)) error

CloneDatabase copies one database to the other.

func (*DBAccess) CreateNewDatabase 

func (d *DBAccess) CreateNewDatabase(tbl *collc.Table, dbFilePath string) (int64, error)

func (*DBAccess) DatabaseExists 

func (d *DBAccess) DatabaseExists(path string) bool

DatabaseExists checks existance of the db file.

func (*DBAccess) Decrypt 

func (d *DBAccess) Decrypt(data []byte, pwdPhrase string) ([]byte, error)

func (*DBAccess) DecryptDatabase 

func (d *DBAccess) DecryptDatabase(dbFilePath string, pwdPhrase string) error

func (*DBAccess) Encrypt 

func (d *DBAccess) Encrypt(data []byte, pwdPhrase string) ([]byte, error)

func (*DBAccess) EncryptDatabase 

func (d *DBAccess) EncryptDatabase(dbFilePath string, pwdPhrase string) error

func (*DBAccess) ExecuteNonQuery 

func (d *DBAccess) ExecuteNonQuery(sqlStatement string, dbFilePath string) (int64, error)

ExecuteNonQuery inserts data. It uses a transaction context so that the operation is rolled back on failures and then closes the database. Closing the databases has the following advantages for an SQLite database:

  1. It reduces chances of file corruption.
  2. It reduces chances of "database locked" errors.
  3. It reduces lingering locks, where the database file stays locked albite closing all database handles.

func (*DBAccess) ExecuteNonQueryNoTx 

func (d *DBAccess) ExecuteNonQueryNoTx(sqlStatement string, dbFilePath string) (int64, error)

ExecuteNonQueryNoTx uses no transaction context to insert data.

func (*DBAccess) ExecuteNonQueryNoTxPointToDB 

func (d *DBAccess) ExecuteNonQueryNoTxPointToDB(sqlStatement string, db *sql.DB) (int64, error)

func (*DBAccess) ExecuteNonQueryPointToDB 

func (d *DBAccess) ExecuteNonQueryPointToDB(sqlStatement string, db *sql.DB) (int64, error)

ExecuteNonQueryPointToDB inserts data. It does not close the database after operation is completed.

func (*DBAccess) ExecuteScalare 

func (d *DBAccess) ExecuteScalare(sqlStatement string, dbFilePath string) (interface{}, error)

ExecuteScalare returns one value and closes the database.

func (*DBAccess) ExecuteScalarePointToDB 

func (d *DBAccess) ExecuteScalarePointToDB(sqlStatement string, db *sql.DB) (interface{}, error)

ExecuteScalare returns one value and closes the database.

func (*DBAccess) ExportDataTableToDatabase 

func (d *DBAccess) ExportDataTableToDatabase(tbl *collc.Table, dbFilePath string) (int64, error)

ExportDataTableToDatabase creates a new table based on a Table. It creates a new table if table not exists. By default if the db file does not exist, it will be created.

func (*DBAccess) GetColumnNames 

func (d *DBAccess) GetColumnNames(dbFilePath string, tblName string) ([]string, error)

GetColumnNames gets the column names of a table.

func (*DBAccess) GetDB 

func (d *DBAccess) GetDB(dbFilePath string) (*sql.DB, error)

GetDB opens a database, while attempting to clear a lingering lock on an sqlite database file. If the db is locked or the previous call did not close the db after writing, this will close the db -- and reset the db mode for read/write operations.

func (*DBAccess) GetDataMap 

func (d *DBAccess) GetDataMap(sqlQuery string, dbFilePath string) ([]map[string]interface{}, error)

GetDataMap gets a selected range of table in form of rows and columns.

func (*DBAccess) GetDataMapPage added in v1.0.2 

func (d *DBAccess) GetDataMapPage(sqlQuery string, pageNo int, pageSize int, dbFilePath string) ([]map[string]interface{}, error)

GetDataMapPage returns a map of query by page.

func (*DBAccess) GetDataMapPointToDB 

func (d *DBAccess) GetDataMapPointToDB(sqlQuery string, db *sql.DB) ([]map[string]interface{}, error)

GetDataMapPointToDB gets a selected range of table in form of rows and columns and keep the keeps the database open.

func (*DBAccess) GetDataTable 

func (d *DBAccess) GetDataTable(sqlQuery string, dbFilePath string) (*collc.Table, error)

func (*DBAccess) GetDataTableJSON 

func (d *DBAccess) GetDataTableJSON(tbl *collc.Table) string

func (*DBAccess) GetDataTableLongQuery 

func (dc *DBAccess) GetDataTableLongQuery(sqlQuery string, dbFilePath string, pageSize int, notify func(status LonqQueryArgs)) (*collc.Table, error)

func (*DBAccess) GetDataTableRange 

func (d *DBAccess) GetDataTableRange(dtSrc *collc.Table, dtDest *collc.Table, from int, to int) error

GetDataTableRange selects a range from a DataTable.

func (*DBAccess) GetDataTableWithTag 

func (d *DBAccess) GetDataTableWithTag(sqlQuery string, dbFilePath string, tag string) (*collc.Table, error)

func (*DBAccess) GetPageInfoFromQuery 

func (d *DBAccess) GetPageInfoFromQuery(r *http.Request) (int, int)

GetPageInfoFromQuery --

func (*DBAccess) GetPageOffset 

func (d *DBAccess) GetPageOffset(recordCount int, pageSize int, pageNo int) (int, int, int)

GetPageOffset returns totalPages, offset, pageNo

func (*DBAccess) GetPagingInfo 

func (d *DBAccess) GetPagingInfo(pageSize int, pageNo int, tableName string,
	countColName string, filter string, dbFilePath string) (int, int, CollectionInfo)

GetPagingInfo returns pageSize, offset, and collection info.

func (*DBAccess) GetShrinkWatchList 

func (d *DBAccess) GetShrinkWatchList() []string

GetShrinkWatchList return the global string array of the db file paths.

func (*DBAccess) GetTableCount 

func (d *DBAccess) GetTableCount(tableName string, dbFilePath string) (int64, error)

func (*DBAccess) InsertDataTable 

func (d *DBAccess) InsertDataTable(t *collc.Table, dbFilePath string, wg *sync.WaitGroup) (int64, error)

InsertDataTable inserts a DataTable collection into a datbase table. The table has to exists and all columns must match the table from the arg.

func (*DBAccess) InsertSingleRow 

func (d *DBAccess) InsertSingleRow(t *collc.Table, rowInx int, dbFilePath string) (int64, error)

func (*DBAccess) ShrinkDB 

func (d *DBAccess) ShrinkDB(dbFilePath string) error

ShrinkDB compresses an SQLite database file by removing spaces.

type LonqQueryArgs 

type LonqQueryArgs struct {
	ResultTable  *collections.Table
	RowsFetched  int64
	TotalToFetch int64
	TotalPages   int
	PageSize     int
	Status       string
}

Source Files

View all Source files

Directories

Path Synopsis
(c) Kamiar Bahri
 (c) Kamiar Bahri

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.