sqlctrl

package module
v0.0.0-...-778b8f4 Latest Latest
Warning

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

 Go to latest Published: Apr 15, 2024 License: GPL-3.0 Imports: 12 Imported by: 0

README

Golang SQL Control

This module provides a simple interface for interacting with SQL databases. Testing was conducted with MySQL and SQLite3

The module implements the following functionality:

  • Tables:
    • Creation
    • Migration (automatic or manual)
    • Deletion
  • Values:
    • Select
    • Insert
    • Update
    • Delete

Installation

go get github.com/GlshchnkLx/go_mod_sqlctrl

import (
	sqlctrl "github.com/GlshchnkLx/go_mod_sqlctrl"
)

Examples

package main

import (
	"fmt"

	sqlctrl "github.com/GlshchnkLx/go_mod_sqlctrl"
	_ "github.com/go-sql-driver/mysql"
	_ "modernc.org/sqlite"
)

// tagging structures for the module
type User struct {
	ID   int64  `sql:"NAME=id, TYPE=INTEGER, PRIMARY_KEY, AUTO_INCREMENT"`
	Name string `sql:"NAME=name, TYPE=TEXT(32)"`
}

type Token struct {
	ID      int64  `sql:"NAME=id, TYPE=INTEGER, PRIMARY_KEY, AUTO_INCREMENT"`
	Content string `sql:"NAME=content, TYPE=TEXT(64), UNIQUE"`
}

type UserToken struct {
	ID int64 `sql:"NAME=id, TYPE=INTEGER, PRIMARY_KEY, AUTO_INCREMENT"`

	UserID  int64 `sql:"NAME=user_id"`
	TokenID int64 `sql:"NAME=token_id"`
}

type UserTokenView struct {
	UserID       int64  `sql:"NAME=user_id"`
	TokenID      int64  `sql:"NAME=token_id"`
	UserName     string `sql:"NAME=user_name"`
	TokenContent string `sql:"NAME=token_content"`
}

func main() {
	// connect the database and specify the schema file
	db, err := sqlctrl.NewDatabase("sqlite", "./example.db", "./example.json")
	_ = err

	// creating a table with the name
	userTable, err := sqlctrl.NewTable("users", User{})

	// starting the automatic migration procedure
	err = db.MigrationTable(userTable, nil)

	tokenTable, err := sqlctrl.NewTable("tokens", Token{})

	err = db.MigrationTable(tokenTable, nil)

	userTokenTable, err := sqlctrl.NewTable("user_token", UserToken{})

	err = db.MigrationTable(userTokenTable, nil)

	// creating a view without the name
	userTokenViewTable, err := sqlctrl.NewTable("", UserTokenView{})

	// inserting a single records into the table
	_, err = db.InsertValue(userTable, User{
		Name: "User 1",
	})

	_, err = db.InsertValue(userTable, User{
		Name: "User 2",
	})

	// inserting an array of records into a table
	_, err = db.InsertValue(tokenTable, []Token{
		{
			Content: "Token 1",
		}, {
			Content: "Token 2",
		}, {
			Content: "Token 3",
		},
	})

	_, err = db.InsertValue(userTokenTable, []UserToken{
		{
			UserID:  1,
			TokenID: 1,
		}, {
			UserID:  2,
			TokenID: 2,
		}, {
			UserID:  1,
			TokenID: 3,
		},
	})

	// making a request for view
	resultInterface, err := db.Query(userTokenViewTable, `
		SELECT
				users.id AS user_id,
				tokens.id AS token_id,
				users.name AS user_name,
				tokens.content AS token_content
		FROM users, tokens, user_token
		WHERE users.id = user_token.user_id AND tokens.id = user_token.token_id
	`)

	// output result
	resultArray := resultInterface.([]UserTokenView)
	for i, v := range resultArray {
		fmt.Println(i, v.UserName, v.TokenContent)
	}
}

Testing

go test -v

Documentation

Index

Constants

This section is empty.

Variables

View Source 
var (
	ErrValueMustBeAStructure          = fmt.Errorf("value must be a structure")
	ErrValueMustBeAStructureOrPointer = fmt.Errorf("value must be a structure or pointer")

	ErrValueDoesNotMatchTableType = fmt.Errorf("value does not match a table type")
)
View Source 
var (
	ErrInvalidArgument      = fmt.Errorf("invalid argument received")
	ErrTableIsNull          = fmt.Errorf("table is null")
	ErrTableAlreadyExists   = fmt.Errorf("table already exists")
	ErrTableDoesNotExists   = fmt.Errorf("table does not exists")
	ErrTableDoesNotMigtated = fmt.Errorf("table does not migrated")

	ErrMigrationTableIsEmpty      = fmt.Errorf("migration table is empty")
	ErrMigrationTableFieldIsEmpty = fmt.Errorf("migration table field is empty")

	ErrTableDoesNotHaveAutoIncrement = fmt.Errorf("table does not have auto increment")
	ErrTableDidNotInsertTheValue     = fmt.Errorf("table did not insert the value")
	ErrTableDidNotReplaceTheValue    = fmt.Errorf("table did not replace the value")
	ErrTableDidNotUpdateTheValue     = fmt.Errorf("table did not update the value")
	ErrTableDidNotDeleteTheValue     = fmt.Errorf("table did not delete the value")

	ErrResponseLessThanRequested = fmt.Errorf("response less than requested")
	ErrResponseMoreThanRequested = fmt.Errorf("response more than requested")
)

Functions

func MigrationTableAuto 

func MigrationTableAuto(tableA, tableB *Table) (string, error)

Types

type DataBase 

type DataBase struct {
	// contains filtered or unexported fields
}

func NewDatabase 

func NewDatabase(sqlDriver, sqlSource string) (*DataBase, error)

Creates a Database object specified by its database driver @sqlDriver and a driver-specific data source @sqlSource.

func (*DataBase) CheckExistTable 

func (db *DataBase) CheckExistTable(table *Table) bool

Checks if received @table exist in database object

func (*DataBase) CheckHashTable 

func (db *DataBase) CheckHashTable(table *Table) bool

Compares hashes of argument table object and table object from database map. Returns result of "not equal".

func (*DataBase) CreateTable 

func (db *DataBase) CreateTable(table *Table) error

Creates @table argument in current database. Saves table object in database scheme map.

func (*DataBase) DeleteValue 

func (db *DataBase) DeleteValue(table *Table, value interface{}) error

Delete provided @value object (single or slice) from @table.

func (*DataBase) DropTable 

func (db *DataBase) DropTable(table *Table) error

Drops specified @table in database and removes from database object.

func (*DataBase) Exec 

func (db *DataBase) Exec(handler func(*DataBase, *sql.Tx) error) (err error)

Executes provided @handler closure with transaction control. If @handler returns non-nil error transaction rollback is executed.

func (*DataBase) ExecWithTable 

func (db *DataBase) ExecWithTable(table *Table, handler func(*DataBase, *sql.Tx, *Table) error) (err error)

Executes provided @handler closure with transaction control. Checks existence of @table in database. If @handler returns non-nil error transaction rollback is executed.

func (*DataBase) GetCount 

func (db *DataBase) GetCount(table *Table) (count int64, err error)

func (*DataBase) GetLastId 

func (db *DataBase) GetLastId(table *Table) (id int64, err error)

func (*DataBase) InsertValue 

func (db *DataBase) InsertValue(table *Table, value interface{}) (lastId int64, err error)

Inserts provided @value object (single or slice) into @table. Returns last inserted object id.

func (*DataBase) MigrationTable 

func (db *DataBase) MigrationTable(table *Table, handler func(*Table, *Table) (string, error), migrationNumber int64) error

func (*DataBase) NewTable 

func (db *DataBase) NewTable(migrationNumber int64, tableName string, tableStruct interface{}) (*Table, error)

func (*DataBase) Query 

func (db *DataBase) Query(table *Table, request string) (response interface{}, err error)

Executes provided @request query for specified @table. Does not check presence of @table in database. Returns slice of values in interface{} object.

func (*DataBase) QuerySingle 

func (db *DataBase) QuerySingle(table *Table, request string) (response interface{}, err error)

Executes provided @request query for specified @table. Does not check presence of @table in database. Returns single object in interface{} object.

func (*DataBase) QuerySingleWithTable 

func (db *DataBase) QuerySingleWithTable(table *Table, request string) (response interface{}, err error)

Executes provided @request query for specified @table. Checks presence of @table in database. Returns single object in interface{} object.

func (*DataBase) QueryWithTable 

func (db *DataBase) QueryWithTable(table *Table, request string) (response interface{}, err error)

Executes provided @request query for specified @table. Checks presence of @table in database. Returns slice of objects in interface{} object.

func (*DataBase) ReplaceValue 

func (db *DataBase) ReplaceValue(table *Table, value interface{}) error

Replaces provided @value object (single or slice) in @table.

func (*DataBase) SchemeExportJson 

func (db *DataBase) SchemeExportJson(sqlSchemeFilePath string) error

func (*DataBase) SchemeImportJson 

func (db *DataBase) SchemeImportJson(sqlSchemeFilePath string) error

func (*DataBase) SelectAll 

func (db *DataBase) SelectAll(table *Table) (response interface{}, err error)

Selects from given @table all data as slice of objects. Result of select is returned as interface{} object Be careful with large dataset (not sure that driver will limit data chunk)

func (*DataBase) SelectValue 

func (db *DataBase) SelectValue(table *Table, where string) (response interface{}, err error)

Selects from given @table slice of objects with specified @where conditional string. Result of select is returned as interface{} object.

func (*DataBase) SelectValueById 

func (db *DataBase) SelectValueById(table *Table, id int64) (response interface{}, err error)

Selects from given @table single object with specified @id value.

func (*DataBase) SelectValueSingle 

func (db *DataBase) SelectValueSingle(table *Table, where string) (response interface{}, err error)

Selects from given @table single object with specified @where conditional string.

func (*DataBase) TruncateTable 

func (db *DataBase) TruncateTable(table *Table) error

Truncates specified @table in database (clear all rows).

func (*DataBase) UpdateValue 

func (db *DataBase) UpdateValue(table *Table, value interface{}) error

Updates provided @value object (single or slice) in @table.

type Table 

type Table struct {
	GoName string       `json:"goName"`
	GoType reflect.Type `json:"-"`

	SqlName string `json:"sqlName"`

	FieldNameArray []string               `json:"fieldNameArray"`
	FieldMap       map[string]*TableField `json:"fieldMap"`

	MigrationNumber int64 `json:"migrationNumber"`

	AutoIncrement *TableField `json:"autoIncrement"`
}

func NewTable 

func NewTable(tableName string, tableStruct interface{}) (table *Table, err error)

Creates a Table object with specified tableName and tableStruct. If tableName is not an empty string then it used as table name for sql queries in table methods. A tableStruct object must be a some custom struct object otherwise ErrValueMustBeAStructure will be returned. Each field of tableStruct is parsed and saved in Table object for future use.

func (*Table) GetHash 

func (table *Table) GetHash() string

returns current table hash

func (*Table) GetStruct 

func (table *Table) GetStruct(tableStruct interface{}) (tableStructPtr interface{}, fieldArrayPtr []interface{}, err error)

type TableField 

type TableField struct {
	GoName string       `json:"goName"`
	GoType reflect.Kind `json:"-"`

	SqlName string `json:"sqlName"`
	SqlType string `json:"sqlType"`

	IsPrimaryKey    bool `json:"isPrimaryKey"`
	IsAutoIncrement bool `json:"isAutoIncrement"`
	IsUnique        bool `json:"isUnique"`
	IsNotNull       bool `json:"isNotNull"`

	ValueDefault *string `json:"valueDefault"`
	ValueCheck   *string `json:"valueCheck"`
}

func (*TableField) GetHash 

func (field *TableField) GetHash() string

func (*TableField) ReflectParse 

func (field *TableField) ReflectParse(reflectStructField reflect.StructField) bool

Parses given struct field. Return value is true if operation was successful. This method tries to parse struct field tag at first. If NAME or TYPE was not found in tag then it retrives appropriate info from field throught reflection.

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.