sqla

package module
v0.1.6 Latest Latest
Warning

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

 Go to latest Published: Jan 9, 2023 License: MIT Imports: 17 Imported by: 0

README

sqla Go (Golang) package

Package sqla provides some specific functions to extend standard Go database/sql package. These functions can be used in any SQL-driven applications, although the package initially was created for the EDM project [see https://github.com/alecxcode/edm for the most complete example of sqla usage]. Basic example code (how to use) can be found below.

Complete documentation available at: https://pkg.go.dev/github.com/alecxcode/sqla

This package is intended to provide more convenient methods for accessing SQL databases: creating, updating, deleting and selecting objects. Standard Go database/sql functions are not changed. All new functions works with them, and usual database/sql should be used when necessary.

Features and functions:

The package supports the following RDBMS:
  • SQLite
  • Microsoft SQL Server
  • MySQL(MariaDB)
  • Oracle
  • PostgreSQL
  • working with different RDBMS seamlessly;
  • constructing select statement with multiple filters programmatically and arguments list protected from SQL injection;
  • easier (than with bare database/sql) inserting, updating, deleting objects.

Installation and use:

How to use in your project:

Add "github.com/alecxcode/sqla" to you import section in a *.go file.

Run in the package folder:

go mod init nameofyourproject  
go mod tidy
Basic use code example:
package main

import (
	"database/sql"
	"fmt"

	"github.com/alecxcode/sqla"
)

// Book is a type to represent books
type Book struct {
	ID            int
	BookTitle     string
	Author        string
	YearPublished int
}

func (b *Book) create(db *sql.DB, DBType byte) (lastid int, rowsaff int) {
	var args sqla.AnyTslice
	args = args.AppendNonEmptyString("BookTitle", b.BookTitle)
	args = args.AppendNonEmptyString("Author", b.Author)
	args = args.AppendInt("YearPublished", b.YearPublished)
	lastid, rowsaff = sqla.InsertObject(db, DBType, "books", args)
	return lastid, rowsaff
}
func (b *Book) update(db *sql.DB, DBType byte) (rowsaff int) {
	var args sqla.AnyTslice
	args = args.AppendStringOrNil("BookTitle", b.BookTitle)
	args = args.AppendStringOrNil("Author", b.Author)
	args = args.AppendInt("YearPublished", b.YearPublished)
	rowsaff = sqla.UpdateObject(db, DBType, "books", args, b.ID)
	return rowsaff
}
func (b *Book) load(db *sql.DB, DBType byte) error {
	row := db.QueryRow(
		"SELECT ID, BookTitle, Author, YearPublished FROM books WHERE ID = "+
			sqla.MakeParam(DBType, 1),
		b.ID)
	var BookTitle, Author sql.NullString
	var YearPublished sql.NullInt64
	err := row.Scan(&b.ID, &BookTitle, &Author, &YearPublished)
	if err != nil {
		return err
	}
	b.BookTitle = BookTitle.String
	b.Author = Author.String
	b.YearPublished = int(YearPublished.Int64)
	return nil
}

// App is a type to represent computer software
type App struct {
	ID           int
	AppName      string
	Author       string
	YearReleased int
}

func (a *App) create(db *sql.DB, DBType byte) (lastid int, rowsaff int) {
	var args sqla.AnyTslice
	args = args.AppendNonEmptyString("AppName", a.AppName)
	args = args.AppendNonEmptyString("Author", a.Author)
	args = args.AppendInt("YearReleased", a.YearReleased)
	lastid, rowsaff = sqla.InsertObject(db, DBType, "apps", args)
	return lastid, rowsaff
}
func (a *App) update(db *sql.DB, DBType byte) (rowsaff int) {
	var args sqla.AnyTslice
	args = args.AppendStringOrNil("AppName", a.AppName)
	args = args.AppendStringOrNil("Author", a.Author)
	args = args.AppendInt("YearReleased", a.YearReleased)
	rowsaff = sqla.UpdateObject(db, DBType, "apps", args, a.ID)
	return rowsaff
}
func (a *App) load(db *sql.DB, DBType byte) error {
	row := db.QueryRow(
		"SELECT ID, AppName, Author, YearReleased FROM apps WHERE ID = "+
			sqla.MakeParam(DBType, 1),
		a.ID)
	var AppName, Author sql.NullString
	var YearReleased sql.NullInt64
	err := row.Scan(&a.ID, &AppName, &Author, &YearReleased)
	if err != nil {
		return err
	}
	a.AppName = AppName.String
	a.Author = Author.String
	a.YearReleased = int(YearReleased.Int64)
	return nil
}

func main() {

	// Initializing database
	const DBType = sqla.SQLITE
	var db *sql.DB
	db = sqla.OpenSQLConnection(DBType, "file::memory:?cache=shared&_foreign_keys=true")
	db.Exec("CREATE TABLE books (ID INTEGER PRIMARY KEY, BookTitle TEXT, Author TEXT, YearPublished INTEGER);")
	db.Exec("CREATE TABLE apps (ID INTEGER PRIMARY KEY, AppName TEXT, Author TEXT, YearReleased INTEGER);")

	// Creating objects and inserting into database
	var someBook = Book{BookTitle: "Alice's Adventures in Wonderland", Author: "Lewis Carroll", YearPublished: 1865}
	var someApp = App{AppName: "Linux", Author: "Linus Torvalds", YearReleased: 1991}
	bookID, res := someBook.create(db, DBType)
	if res > 0 {
		fmt.Println("Inserted book into DB")
		someBook.ID = bookID
	}
	appID, res := someApp.create(db, DBType)
	if res > 0 {
		fmt.Println("Inserted app into DB")
		someApp.ID = appID
	}

	// Updating object in the database
	someBook.BookTitle = "Some Updated Book Title"
	someBook.Author = ""
	someBook.YearPublished = 1900
	res = someBook.update(db, DBType)
	if res > 0 {
		fmt.Println("Updated book in the DB")
	}

	// Loading objects from database
	bookFromDB := Book{ID: bookID}
	appFromDB := App{ID: appID}
	bookFromDB.load(db, DBType)
	appFromDB.load(db, DBType)
	fmt.Printf("Book loaded from DB: %#v\n", bookFromDB)
	fmt.Printf("App loaded from DB: %#v\n", appFromDB)

	// Deleting objects from database
	res = sqla.DeleteObjects(db, DBType, "books", "ID", []int{bookFromDB.ID})
	if res > 0 {
		fmt.Println("Deleted book from DB")
	}
	res = sqla.DeleteObjects(db, DBType, "apps", "ID", []int{appFromDB.ID})
	if res > 0 {
		fmt.Println("Deleted app from DB")
	}

	db.Close()

}

Documentation

Overview

Package sqla provides some specific functions to extend standard Go database/sql package. These functions can be used in any SQL-driven applications, although the package initially was created for the EDM project [see https://github.com/alecxcode/edm for the most complete example of sqla usage]. Basic example code (how to use) can be found at: https://github.com/alecxcode/sqla

This package is intended to provide more convenient methods for accessing SQL databases: creating, updating, deleting and selecting objects. Standard Go database/sql functions are not changed. All new functions works with them, and usual database/sql should be used when necessary.

The package supports the following RDBMS: SQLite, Microsoft SQL Server, MySQL(MariaDB), Oracle, PostgreSQL.

The key functions of this package are related to the following: working with different RDBMS seamlessly; constructing select statement with multiple filters programmatically and arguments list protected from SQL injection; easier (than with bare database/sql) inserting, updating, deleting objects.

Index

Constants

View Source 
const (
	SQLITE = iota
	MSSQL
	MYSQL
	ORACLE
	POSTGRESQL
)

SQLITE, MSSQL, MYSQL, ORACLE POSTGRESQL - are database types supported.

View Source 
const DEBUG = false

DEBUG may be set to true to print SQL queries

Variables

This section is empty.

Functions

func BuildDSN 

func BuildDSN(DBType string, DBName string, DBHost string, DBPort string, DBUser string, DBPassword string) (DSN string)

BuildDSN creates DSN for database connection. Then, DSN is used in CreateDB and OpenSQLConnection. Accepted db types are: "sqlite", "mssql" (or "sqlserver"), "mysql" (or "mariadb"), "oracle", "postgresql" (or "postgres"). Other arguments are self-descripting. For Oracle DBName is a service name.

func BuildSQLIN 

func BuildSQLIN(DBType byte, sq string, argsCounter int, column string, valueList []int) (counter int, resquery string, args []interface{})

BuildSQLIN makes and adds a part of SQL statement with all numbered parameters supplied in a valueList. sq argument should be complete SQL statement, as BuildSQLIN returns augmented statement, not part of it. argsCounter is required to define from what number to start count positional parameters. The added part of a statement will look something like 'WHERE/AND column IN(a list of placeholders by MakeParam func, e.g. $1, $2)'. BuildSQLIN returns counter as the number of added parameters for use in other routine and args as []interface{} of payload arguments which may be supplied to Go sql functions.

func BuildSQLINNOT 

func BuildSQLINNOT(DBType byte, sq string, argsCounter int, column string, valueList []int) (counter int, resquery string, args []interface{})

BuildSQLINNOT makes and adds a part of SQL statement with all numbered placeholders for arguments supplied in a valueList. sq argument should be complete SQL statement, as BuildSQLINNOT returns augmented statement, not part of it. argsCounter is required to define from what number to start count positional parameters. The added part of a statement will look something like 'WHERE/AND column NOT IN(a list of placeholders by MakeParam func, e.g. $1, $2)'. BuildSQLINNOT returns counter as the number of added parameters for use in other routine, statement, and args as []interface{} of payload arguments which may be supplied to Go sql functions.

func BuildSQLINOR 

func BuildSQLINOR(DBType byte, sq string, argsCounter int, column string, valueList []int, FirstIter bool, LastIter bool) (counter int, resquery string, args []interface{})

BuildSQLINOR makes and adds a part of SQL statement with all numbered placeholders for arguments supplied in a valueList. sq argument should be complete SQL statement, as BuildSQLINOR returns augmented statement, not part of it. argsCounter is required to define from what number to start count positional parameters. The added part of a statement will look something like 'WHERE/AND (column IN($1, $2) OR another_column IN($1, $2), etc...)'. Although the 'WHERE/AND (' part will be added only when FirstIter argument is true and the closing ')' will be added only when LastIter argument is true. BuildSQLINOR returns counter as the number of added parameters for use in other routine, statement, and args as []interface{} of payload arguments which may be supplied to Go sql functions.

func ConstructSELECTquery 

func ConstructSELECTquery(
	DBType byte,
	tableName string,
	columnsToSelect string,
	columnsToCount string,
	joins string,
	F Filter,
	orderBy string,
	orderHow int,
	limit int,
	offset int,
	distinct bool,
	seek Seek) (sq string, sqcount string, args []interface{}, argscount []interface{})

ConstructSELECTquery is the most fundamental for this package. It constructs and returns SQL statement for select query, SQL statement for COUNT(), and arguments slice to use in Go sql functions. The function takes the following arguments: DBType (see constants); tableName to paste after FROM; columnsToSelect as a comma-separated columns; columnsToCount - to put as an argument for a COUNT(), e.g. "*" will be COUNT(*); joins as usual joins part of an SQL statement; Filter - is the main thing to counstruct query based on different filters. See Filter type and its methods; orderBy is a column name or column's names to order result; limit, offset - are usual values for sql statement; distinct as bool defines whether you need to add DISTINCT keyword in your statement.

Seek is used to avoid offsetting when dealing with big tables and to implement so-called seek method of pagination. See Seek type. Seek method of pagination requires additional coding in your app, and algorithms are not so simple as with offset.

func CreateDB 

func CreateDB(DBType byte, DSN string, sqlStmt string)

CreateDB creates database for SQLITE and schema for all databases, based on provided sql script (sqlStmt argument). CreateDB automatically opens database connection and then closes the connection after creation is complete. For DBType see constants, for DSN see BuildDSN.

func DeleteObject 

func DeleteObject(db *sql.DB, DBType byte, table string, column string, id int) (rowsaff int)

DeleteObject just deletes one specific object which id is in column specified.

func DeleteObjects 

func DeleteObjects(db *sql.DB, DBType byte, table string, column string, ids []int) (rowsaff int)

DeleteObjects just deletes any object which id is present in ids list and in column specified.

func InsertObject 

func InsertObject(db *sql.DB, DBType byte, table string, iargs []anyT) (lastid int, rowsaff int)

InsertObject creates an SQL statement and executes it to insert an object into the specified table. It returns the ID of created record and the number of affected rows. ID column should be named 'ID'.

func MakeParam 

func MakeParam(DBType byte, argsCounter int) string

MakeParam takes database type (see constants) and parameter positional number to create ordial (or positional) placeholder for a parameter in your SQL statement. E.g. for the parameter #1 they are: $1 for SQLITE, @p1 for MSSQL, ? for MYSQL, :1 for ORACLE, $1 for POSTGRESQL. The function return only this placeholder.

func OpenSQLConnection 

func OpenSQLConnection(DBType byte, DSN string) (db *sql.DB)

OpenSQLConnection onpens connection to a database and renurns standard Go *sql.DB type. For DBType see constants, for DSN see BuildDSN.

func ReturnDBType 

func ReturnDBType(dbtype string) byte

ReturnDBType gives digital representation of RDBMS type based on string. Accepted db types are: "sqlite", "mssql" (or "sqlserver"), "mysql" (or "mariadb"), "oracle", "postgresql" (or "postgres").

func SetToNull 

func SetToNull(db *sql.DB, DBType byte, table string, column string, list []int) (rowsaff int)

SetToNull sets to NULL the column of any object which has a value from a list in that column.

func SetToNullOneByID added in v0.1.6 

func SetToNullOneByID(db *sql.DB, dbType byte, table string, column string, id int) (rowsaff int)

SetToNullOneByID sets to NULL the column of an object which has a specified ID.

func UnmarshalNonEmptyJSONList 

func UnmarshalNonEmptyJSONList(s string) (jsonList []string)

UnmarshalNonEmptyJSONList returns []string slice made before from this kind of slice.

func UnmarshalNonEmptyJSONListInt 

func UnmarshalNonEmptyJSONListInt(s string) (jsonList []int)

UnmarshalNonEmptyJSONListInt returns []int slice made before from this kind of slice.

func UpdateMultipleWithOneInt 

func UpdateMultipleWithOneInt(db *sql.DB, DBType byte, table string, column string, val int, timecol string, timestamp int64, ids []int) (rowsaff int)

UpdateMultipleWithOneInt updates with val the column of an object which id is present in ids list and in 'ID' column. Rows which already have val in the column will not be updated. If necessary you can provide timestamp and a column for timestamp; if you don't need to update any timestamp column use empty string as the argument for that column.

func UpdateObject 

func UpdateObject(db *sql.DB, DBType byte, table string, iargs []anyT, ID int) (rowsaff int)

UpdateObject creates an SQL statement and executes it to update an object in the specified table. The function returns the number of affected rows. Update will be done on the object where column 'ID' contains ID value.

func UpdateSingleInt 

func UpdateSingleInt(db *sql.DB, DBType byte, table string, column string, valueToSet int, ID int) (rowsaff int)

UpdateSingleInt creates an SQL statement and executes it to update only one value of an object in database. It executes UpdateObject.

func UpdateSingleJSONListInt 

func UpdateSingleJSONListInt(db *sql.DB, DBType byte, table string, column string, valueToSet []int, ID int) (rowsaff int)

UpdateSingleJSONListInt creates an SQL statement and executes it to update only one value of an object in database. It executes UpdateObject.

func UpdateSingleJSONListStr 

func UpdateSingleJSONListStr(db *sql.DB, DBType byte, table string, column string, valueToSet []string, ID int) (rowsaff int)

UpdateSingleJSONListStr creates an SQL statement and executes it to update only one value of an object in database. It executes UpdateObject.

func UpdateSingleJSONStruct 

func UpdateSingleJSONStruct(db *sql.DB, DBType byte, table string, column string, valueToSet interface{}, ID int) (rowsaff int)

UpdateSingleJSONStruct creates an SQL statement and executes it to update only one value of an object in database. It executes UpdateObject.

func UpdateSingleStr 

func UpdateSingleStr(db *sql.DB, DBType byte, table string, column string, valueToSet string, ID int) (rowsaff int)

UpdateSingleStr creates an SQL statement and executes it to update only one value of an object in database. It executes UpdateObject.

func VerifyRemovalPermissions 

func VerifyRemovalPermissions(db *sql.DB, DBType byte, table string, Owner int, AdminPrivileges bool, RemoveAllowed bool, ids []int) bool

VerifyRemovalPermissions makes query to check if a user has the right to delete an object. The returned result will be truth if either an Owner matches id in column 'Creator' or have AdminPrivileges is true. RemoveAllowed flag defines if any remove allowed at all by non-admin user. This function is somewhat specific to EDM project. You might need to modify it for your app.

Types

type AnyTslice 

type AnyTslice []anyT

AnyTslice can contain different values (strings, including JSON, integers, nils), although the value type should be specified when adding. This slice later used with insert or update to database. Appending to this slice is made with defined below functions. You should provide column name of SQL table to most of them.

func (AnyTslice) AppendInt 

func (a AnyTslice) AppendInt(column string, i int) AnyTslice

AppendInt appends int to AnyTslice

func (AnyTslice) AppendInt64 

func (a AnyTslice) AppendInt64(column string, i int64) AnyTslice

AppendInt64 appends int64 to AnyTslice

func (AnyTslice) AppendJSONList 

func (a AnyTslice) AppendJSONList(column string, sList []string) AnyTslice

AppendJSONList appends JSON made from []string slice if it is not empty. If slice is empty nil will be appended.

func (AnyTslice) AppendJSONListInt 

func (a AnyTslice) AppendJSONListInt(column string, iList []int) AnyTslice

AppendJSONListInt appends JSON made from []int slice if it is not empty. If slice is empty nil will be appended.

func (AnyTslice) AppendJSONStruct 

func (a AnyTslice) AppendJSONStruct(column string, sStruct interface{}) AnyTslice

AppendJSONStruct appends JSON made from any struct. It may not be nil pointer.

func (AnyTslice) AppendNil 

func (a AnyTslice) AppendNil(column string) AnyTslice

AppendNil appends nil to AnyTslice

func (AnyTslice) AppendNonEmptyString 

func (a AnyTslice) AppendNonEmptyString(column string, s string) AnyTslice

AppendNonEmptyString appends string if it is not empty. If string is empty nothing will be appended (slice unchanged).

func (AnyTslice) AppendStringOrNil 

func (a AnyTslice) AppendStringOrNil(column string, s string) AnyTslice

AppendStringOrNil appends string if it is not empty. If string is empty nil will be appended.

type ClassFilter 

type ClassFilter struct {
	Name     string
	Selector string
	InJSON   bool
	Column   string
	List     []int
}

ClassFilter to filter types, statuses, etc. Selector defines some options list name in user interface, e.g. id of a <select> element. InJSON allows to search for an integer value inside JSON stored in a text-type or varchar-type column.

type DateFilter 

type DateFilter struct {
	Name     string
	Column   string
	Relation string
	Dates    []int64
	DatesStr []string
}

DateFilter to filter dates and datetime. Dates should be stored as timestamps with value type of int64. DatesStr contains strings to represent values in user interface.

type Filter 

type Filter struct {
	ClassFilter       []ClassFilter
	ClassFilterOR     []ClassFilter
	DateFilter        []DateFilter
	SumFilter         []SumFilter
	TextFilterName    string
	TextFilter        string
	TextFilterColumns []string
}

Filter for a page. This Filter type is like a socket to connect database, backend, and even frontend parts.

ClassFilter allows to filter by a list of sevaral integers. ClassFilterOR has the same functionality, however is allows to put OR operator in SQL statement between different ClassFilterOR filters (which have the same name but different columns). See descriptions of other filter types for details.

func (*Filter) ClearColumnsValues 

func (f *Filter) ClearColumnsValues()

ClearColumnsValues removes names of (an sql table) columns form a filter if you are going to pass the filter entirely into a response and do not wish to show these names.

func (*Filter) GetFilterFromForm 

func (f *Filter) GetFilterFromForm(r *http.Request,
	dateConvFunc func(string) int64,
	dateTimeConvFunc func(string) int64,
	keywords map[string]int)

GetFilterFromForm analyses http.Request and fills the Filter by reqired values (lists, dates, sums, textfilter) from HTML form. Any filters with empty lists will be removed from Filter. Before executing this method some initial values should be set: filter names and table's columns.

Developer is required to provide dateConvFunc and dateTimeConvFunc. They used to convert string-typed dates from a form to int64-datestamps or int64-timestamps. These may be the same - it is a developer's choice. keywords allow to replace some string from related HTML form with integer value for any ClassFilter.

func (*Filter) GetFilterFromJSON 

func (f *Filter) GetFilterFromJSON(JSON []byte,
	dateConvFunc func(string) int64,
	dateTimeConvFunc func(string) int64)

GetFilterFromJSON unmarshals JSON to Filter struct and then only converts dates and sums from strings to integer representation. dateConvFunc and dateTimeConvFunc - are used to convert string-typed dates to int64-datestamps or int64-timestamps. These may be the same - it is a developer's choice.

type Seek 

type Seek struct {
	UseSeek      bool
	Value        int
	ValueInclude bool
}

Seek type allows to implement so-called seek method of pagination. If UseSeek is true, then ConstructSELECTquery will use seek method (instead of offsetting) which just selects values greater or less than Seek.Value.

If ValueInclude is true, then seek method uses greater or equal (or less or equal) operator. This behavior may be useful when reloading the same page.

type SumFilter 

type SumFilter struct {
	Name           string
	Column         string
	CurrencyColumn string
	CurrencyCode   int
	Relation       string
	Sums           []int
	SumsStr        []string
}

SumFilter to filter currency amounts. Sums are stored as integers to avoid loss of accuracy (due to the nature of floats). They all are multiplied by 100. E.g. 0.1 in UI will be searched as 10 in DB and 1 in UI will be searched as 100 in DB. SumsStr contains strings to represent values in user interface.

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.