moxylib

README

MoxyLib

This is a small *sql.DB SQLite wrapper to provide extra functionality. The focus of the lib is to:

• Utilizes Go github.com/ncruces/go-sqlite3 as the default, but can accept other drivers.
• Take SQLite commands as text and get an output that is renderable to HTML.
• Transaction wrapper and Up only automatic migration system using PRAGMA user_version.
• Store and retrieve settings/config in SQLite using a Key/Value table.
• Key/Value storage functions for Value, Set, List, Hash.

Key/Value methods

Note: all values are saved as strings and use '\n' character as the separator. This makes any method other than the simple KVV not suitable for storage of data containing '\n' characters unless the data is Base64 encoded first.

• KVV: Basic Key Value methods. Everything in the Value column is a string and will be retrieved and set as such.
• KVH: Hash Values with fields. Field and Value is separated by '|' and only splits on first occurence of separator.
• KVL: List Values allows for repeats and has push pop functionality.
• KVS: Sets are lists except they don't allow for duplicates and is sorted.

Complete Usage Example:

Complete
Using MoxyLib
Migration File

This repository has an EXAMPLE/BOILERPLATE of how to use moxylib. A fair bit of JS/CSS libraries are used to help with the interface implementation. Those are listed at the bottom. Example code should be freely copied/integrated from here to create your own Web interface. Remember to implement the route behind an admin route with a key/proxy to protect/deny the interface from malicious users.

Important files for the example Go/SQL portion:

• db.go: This contains an example of using the moxylib lib.
• migrations.sql: This contains an example of making a migration file. "--moxylib" is the required separator for migrate up transactions. Each use of --moxylib increases the user_version number so don't remove previous transactions. Do note that using the settings methods requires a key/value table to already be created like in this example.

Important folders for the example HTML/JS portion:

• html: layout.html is what you'd expect and shows all the imported JS/CSS files. sql.html is the initial interface and result.html is a fragment returned by HTMX to create the tables.
• static/js: The real important file here is common.js. This has the data table implementation for searching, sorting, and exporting the table to a CSV file.

FAQ

Q: Why make MoxyLib?

A: The original plan was to make a GoRedisAdmin, but they changed their licensing. Mostly wanted a nice interface for interacting with SQLite to use between projects.

Special Thanks to the Developers of:

go-sqlite3 - https://github.com/ncruces/go-sqlite3
HTMX - https://htmx.org
Surreal - https://github.com/gnat/surreal
PicoCSS - https://picocss.com
Code-Input - https://github.com/WebCoder49/code-input
Highlightjs - https://highlightjs.org

And of course the Go Standard Lib.

Documentation

Index

• Constants
• Variables
• func CipherDecrypt(encrypted []byte) ([]byte, error)
• func CipherEncrypt(plain []byte) (string, error)
• func Close(err error)
• func DeleteFile(path, name string) error
• func ExecSQLite(cmd string) [][]string
• func FilterSQL(sql string) []string
• func GetUserVersion() (int, error)
• func GoRangeWait[E any](list []E, gofunc func(int, E) error) []error
• func KVDel(t KVTable, key string) error
• func KVGet(t KVTable, key string) (string, error)
• func KVGetAll(t KVTable) (map[string]string, error)
• func KVHDel(t KVTable, key string, fields ...string) error
• func KVHGet(t KVTable, key string, fields ...string) (map[string]string, error)
• func KVHSet(t KVTable, key string, kvs map[string]any) error
• func KVLExist(t KVTable, key, value string) (bool, error)
• func KVLGet(t KVTable, key string) ([]string, error)
• func KVLPop(t KVTable, key string, end bool) (string, error)
• func KVLPush(t KVTable, key, field string, end bool) error
• func KVSDel(t KVTable, key string, values ...string) error
• func KVSSet(t KVTable, key string, values ...string) error
• func KVSet(t KVTable, kvs map[string]any) error
• func LimitersDelete(ip string)
• func LimitersLoadStore(ip string) *bLimiter
• func LimitersStore(ip string, ban bool)
• func Log(next http.Handler) http.Handler
• func Migrate(path string) error
• func NewLimiter(ban bool) *bLimiter
• func PasswordHash(key string) (string, error)
• func PasswordVerify(key, hash string) (bool, *argon2id.Params, error)
• func QuerySQLite(cmd string, noCalc bool) [][]string
• func RandomString(length int) string
• func RateLimiter(next http.Handler) http.Handler
• func ReadMigrations(path string) ([]string, error)
• func RemoveBan(ip string) error
• func RowsScan(rows *sql.Rows, dest any) error
• func RunSQLite(sql string) [][][]string
• func SendHTMLEmail(subject, html string, to ...string) error
• func Setup(newDB *sql.DB) error
• func SplitIntoMap(s, sep string) map[string]string
• func Transaction(execs ...string) error
• func Transform(v any) error
• func UpdateSettings(confmap map[string]any) error
• type Ban
  • func GetAllBans() ([]*Ban, error)
  • func (b *Ban) NewBan() error
  • func (b *Ban) UpdateBan() error
• type FileInfo
  • func CreateBackup(path, name string) (*FileInfo, error)
  • func GetFile(path, name string) (*FileInfo, error)
  • func ListFiles(path string) ([]*FileInfo, error)
• type KVTable
• type LimiterStat
  • func LimitersStats(ignore string) []LimiterStat
• type MDB

Constants

const (
	URIOptimizations = "" /* 199-byte string literal not displayed */
	BackupPath       = "sqlite/backup"
)

URIOptimizations for other SQLite3 drivers.

Variables

var (
	LimiterBanCount int
	LimiterBurst    int
	LimiterRate     int
)

var (
	// DB is the global database connection.
	DB        *MDB
	EmailAuth smtp.Auth
	// Settings is a map of key-value pairs from the setting table.
	Settings  = make(map[string]string)
	KVSetting = KVTable{
		Table: "setting",
		CK:    "Key",
		CV:    "Value",
	}
)

Functions

func CipherDecrypt

func CipherDecrypt(encrypted []byte) ([]byte, error)

func CipherEncrypt

func CipherEncrypt(plain []byte) (string, error)

func Close

func Close(err error)

Closes the database connection and logs any errors.

func DeleteFile

func DeleteFile(path, name string) error

func ExecSQLite

func ExecSQLite(cmd string) [][]string

execSQLite executes a given SQL command and returns the results in a 2D string slice. The result slice contains information about the execution, such as errors, rows affected, and last insert ID.

func FilterSQL

func FilterSQL(sql string) []string

Remove leading/trailing whitespace/comments and split the SQL string into individual commands.

func GetUserVersion

func GetUserVersion() (int, error)

Retrieves the user_version PRAGMA from the database and returns it as an integer. The user_version is used to track the current version of the database schema.

func GoRangeWait

func GoRangeWait[E any](list []E, gofunc func(int, E) error) []error

GoRangeWait is a concurrent function that waits at the end. It will run the gofunc for each element in the list concurrently. This is suitable for mutexed operations in gofunc.

func KVDel

func KVDel(t KVTable, key string) error

DELETES ROW FROM TABLE. Only this function will delete the row from the table. All other functions will empty the value field.

func KVGet

func KVGet(t KVTable, key string) (string, error)

Get Value from a given table and key.

func KVGetAll

func KVGetAll(t KVTable) (map[string]string, error)

Retrieves all key-value pairs from a given table and returns them in a map.

func KVHDel

func KVHDel(t KVTable, key string, fields ...string) error

Delete fields from a hash in a given table.

func KVHGet

func KVHGet(t KVTable, key string, fields ...string) (map[string]string, error)

Retrieves the values of fields from a hash in a given table. If no fields are specified, all fields are returned.

func KVHSet

func KVHSet(t KVTable, key string, kvs map[string]any) error

Sets the values of fields in a hash in a given table.

func KVLExist

func KVLExist(t KVTable, key, value string) (bool, error)

Checks if a field exists in a set/list in a given table.

func KVLGet

func KVLGet(t KVTable, key string) ([]string, error)

Get list from a given table and key.

func KVLPop

func KVLPop(t KVTable, key string, end bool) (string, error)

Retrieve and remove the first or last element from a list in a given table.

func KVLPush

func KVLPush(t KVTable, key, field string, end bool) error

Add a field to the beginning or end of a list in a given table.

func KVSDel

func KVSDel(t KVTable, key string, values ...string) error

Deletes fields from a set in a given table.

func KVSSet

func KVSSet(t KVTable, key string, values ...string) error

Sets values in a set in a given table.

func KVSet

func KVSet(t KVTable, kvs map[string]any) error

Updates the table with the given key-value pairs. If map is nil then do nothing.

func LimitersDelete

func LimitersDelete(ip string)

func LimitersLoadStore

func LimitersLoadStore(ip string) *bLimiter

func LimitersStore

func LimitersStore(ip string, ban bool)

func Log

func Log(next http.Handler) http.Handler

Log logs the request method, status code, and path.

func Migrate

func Migrate(path string) error

Uses ReadMigrate to read the commands from the SQL file and applies them to the database.

func NewLimiter

func NewLimiter(ban bool) *bLimiter

func PasswordHash

func PasswordHash(key string) (string, error)

Hard to crack password hashing with Argon2id.

func PasswordVerify

func PasswordVerify(key, hash string) (bool, *argon2id.Params, error)

PasswordVerify checks if a key matches a hash.

func QuerySQLite

func QuerySQLite(cmd string, noCalc bool) [][]string

Executes a given SQL query and returns the results in a 2D string slice. The result slice contains the query results, including column headers and data rows. If noCalc is true, the column headers will not include the database type.

func RandomString

func RandomString(length int) string

func RateLimiter

func RateLimiter(next http.Handler) http.Handler

func ReadMigrations

func ReadMigrations(path string) ([]string, error)

Reads a SQL file containing multiple migration commands and returns a map of versioned SQL strings.

func RemoveBan

func RemoveBan(ip string) error

func RowsScan

func RowsScan(rows *sql.Rows, dest any) error

Takes SQL rows and scans them into a slice of structs.

func RunSQLite

func RunSQLite(sql string) [][][]string

Take any string and attempt to execute it as a SQLite command. Returns a 3D array of strings with the result of the command.

func SendHTMLEmail

func SendHTMLEmail(subject, html string, to ...string) error

SendEmail sends an email using the provided recipient with HTML content.

func Setup

func Setup(newDB *sql.DB) error

Connects to the SQLite3 database and sets the global DB variable. nil defaults to ncruces SQLite3 driver.

func SplitIntoMap

func SplitIntoMap(s, sep string) map[string]string

Helper function to split a string into a map of key-value pairs.

func Transaction

func Transaction(execs ...string) error

Executes a series of SQL commands within a single transaction.

func Transform

func Transform(v any) error

Post parser for form data.

func UpdateSettings

func UpdateSettings(confmap map[string]any) error

Updates the settings table with the given key-value pairs. Update Settings map with the new values.

Types

type Ban

type Ban struct {
	IP     string `mold:"trim" validate:"required,ip"`
	Date   int64
	Reason string `mold:"trim" validate:"required"`
}

func GetAllBans

func GetAllBans() ([]*Ban, error)

func (*Ban) NewBan

func (b *Ban) NewBan() error

func (*Ban) UpdateBan

func (b *Ban) UpdateBan() error

type FileInfo

type FileInfo struct {
	Name    string
	Path    string
	Size    int64
	Created int64
}

func CreateBackup

func CreateBackup(path, name string) (*FileInfo, error)

Backup Sqlite3 database to a file.

func GetFile

func GetFile(path, name string) (*FileInfo, error)

func ListFiles

func ListFiles(path string) ([]*FileInfo, error)

type KVTable

type KVTable struct {
	Table string
	CK    string
	CV    string
}

type LimiterStat

type LimiterStat struct {
	IP         string
	Requests   int
	Violations int
	Current    int64
}

func LimitersStats

func LimitersStats(ignore string) []LimiterStat

type MDB

type MDB struct{ *sql.DB }