magicsql

package module
v0.11.0 Latest Latest
Warning

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

 Go to latest Published: May 6, 2021 License: CC0-1.0 Imports: 9 Imported by: 4

README

magicsql

magicsql wraps database/sql types and functions in a way that can simplify certain operations.

This is still a work in progress and I don't claim this package will be most people's preferred approach.

Versions

This project will follow the Semantic Versioning specification, v2.0.0, and all tags will be prefixed with "v" to allow gb to pull this package as an unvendored dependency.

Example

See the go documentation for API information and examples.

Or view the runnable example files directly:

LICENSE

This is licensed under CC0, a very permissive public domain license

Documentation

Overview

Package magicsql is a wrapper around a database handle with added magic to ease common SQL operations, and reflection for automatic reading and writing of data

Example (ConfigTags)

This example showcases some of the ways SQL can be magically generated even without having a tagged structure 

package main

import (
	"fmt"
	"github.com/Nerdmaster/magicsql"
	_ "github.com/mattn/go-sqlite3"
)

// UntaggedFoo is a mostly untagged structure, but we can still use magicsql on it via
// manual ConfigTag setup
//
// Note that the tagging on TwO will be ignored since we're providing custom
// tags - even though our custom tags won't include a mapping for "TwO".  Any
// time config is explicitly provided, struct tags are ignored in full.
type UntaggedFoo struct {
	ID    int
	ONE   string
	TwO   int `sql:"blargh"`
	Three bool
	Four  int
	Five  int
	six   string
}

// This example showcases some of the ways SQL can be magically generated even
// without having a tagged structure
func main() {
	// Set up a simple sqlite database
	var db, err = magicsql.Open("sqlite3", "./test.db")
	if err != nil {
		panic(err)
	}

	// Tie the "foos" table to the UntaggedFoo type
	var op = db.Operation()
	var t = op.Table("foos", &UntaggedFoo{})
	t.Reconfigure(magicsql.ConfigTags{
		"ID":    ",primary",
		"Three": "tree",
		"Five":  "-",
	})

	// Create table schema
	op.Exec("DROP TABLE IF EXISTS foos")
	op.Exec(`
		CREATE TABLE foos (
			id INTEGER NOT NULL PRIMARY KEY,
			one TEXT,
			tw_o INT,
			tree BOOL,
			four INT
		);
	`)

	// Insert four rows
	op.BeginTransaction()
	t.Save(&UntaggedFoo{ONE: "one", TwO: 2, Three: true, Four: 4})
	t.Save(&UntaggedFoo{ONE: "thing", TwO: 5, Three: false, Four: 7})
	t.Save(&UntaggedFoo{ONE: "blargh", TwO: 1, Three: true, Four: 5})
	t.Save(&UntaggedFoo{ONE: "sploop", TwO: 2, Three: true, Four: 4})
	op.EndTransaction()
	if op.Err() != nil {
		panic(op.Err())
	}

	var fooList []*UntaggedFoo
	t.Select().Where("tw_o > 1").Limit(2).Offset(1).AllObjects(&fooList)

	for _, f := range fooList {
		fmt.Printf("UntaggedFoo {%d,%s,%d,%#v,%d}\n", f.ID, f.ONE, f.TwO, f.Three, f.Four)
	}
}

Output:

UntaggedFoo {2,thing,5,false,7}
UntaggedFoo {4,sploop,2,true,4}
Example (WithMagic)

This example showcases some of the ways SQL can be magically generated to populate registered structures 

package main

import (
	"fmt"
	"github.com/Nerdmaster/magicsql"
	_ "github.com/mattn/go-sqlite3"
)

// Foo demonstrates some of the optional database magic
type Foo struct {
	// ID is the primary key, but not explicitly given a field name, so it'll be "id"
	ID int `sql:",primary"`
	// ONE turns into "one" for field name, as we auto-lowercase anything not tagged
	ONE string
	// TwO is explicitly set so it doesn't underscorify
	TwO int `sql:"two"`
	// Three is explicitly set to "tree"
	Three bool `sql:"tree"`
	// Four is just lowercased to "four"
	Four int
	// FourPointFive gets turned into underscores
	FourPointFive int
	// Five is explicitly skipped
	Five int `sql:"-"`
	// six isn't exported, so is implicitly skipped
	six string
	// Seven is read-only, so it can be selected but not stored
	Seven string `sql:",readonly"`
	// NoInsert can't be written on insert, but can be written on update; it's
	// useful when we want the database to specify the initial value, but still
	// be able to change it later
	NoInsert int `sql:",noinsert"`
	// NoUpdate can't be written on update, but can be written on insert; it's useful
	// for values that should never change, such as a username
	NoUpdate int `sql:",noupdate"`
}

// This example showcases some of the ways SQL can be magically generated to
// populate registered structures
func main() {
	// Set up a simple sqlite database
	var db, err = magicsql.Open("sqlite3", "./test.db")
	if err != nil {
		panic(err)
	}

	var op = db.Operation()

	// Create table schema
	op.Exec("DROP TABLE IF EXISTS foos")
	op.Exec(`
		CREATE TABLE foos (
			id INTEGER NOT NULL PRIMARY KEY,
			one TEXT,
			two INT,
			tree BOOL,
			four INT,
			four_point_five INT,
			seven TEXT DEFAULT "blargh",
			no_insert INT,
			no_update INT
		);
	`)

	// Insert four rows
	op.BeginTransaction()
	var mt = magicsql.Table("foos", &Foo{})
	var ot = op.OperationTable(mt)

	ot.Save(&Foo{ONE: "one", TwO: 2, Three: true, Four: 4, FourPointFive: 9})
	ot.Save(&Foo{ONE: "thing", TwO: 5, Three: false, Four: 7, FourPointFive: -1})
	ot.Save(&Foo{ONE: "blargh", TwO: 1, Three: true, Four: 5})

	// Fields "Five" and "six" won't be preserved since there's no place to put
	// them, so we won't see their values below.  Field "Seven" is readonly and
	// so will retain its default value.  Field "Eight" won't get set on insert,
	// so will be 0 until we update.  Field "Nine" will be set on insert, but
	// can't be updated.
	ot.Save(&Foo{
		ONE:      "sploop",
		TwO:      2,
		Three:    true,
		Four:     4,
		Five:     29,
		six:      "twenty-nine",
		Seven:    "nope",
		NoInsert: 1010,
		NoUpdate: 1010,
	})

	op.EndTransaction()
	if op.Err() != nil {
		panic(op.Err())
	}

	var fooList []*Foo
	ot.Select().Where("two > 1").Limit(2).Offset(1).Order("four_point_five DESC").AllObjects(&fooList)

	for _, f := range fooList {
		fmt.Printf("Foo {%d,%s,%d,%#v,%d,%d,%d,%q,%q,%d,%d}\n",
			f.ID, f.ONE, f.TwO, f.Three, f.Four, f.FourPointFive, f.Five, f.six, f.Seven, f.NoInsert, f.NoUpdate)
	}

	// Try out an update on the first result in the list, which is id 4
	var f = fooList[0]
	f.NoInsert = 99
	f.NoUpdate = 99
	ot.Save(f)

	var sel = ot.Select().Where("id = ?", 4)
	var cnt = sel.Count()
	fmt.Printf("Number of rows with id of 4: %d\n", cnt.RowCount())
	sel.First(f)
	fmt.Printf("Foo {%d,%s,%d,%#v,%d,%d,%d,%q,%q,%d,%d}\n",
		f.ID, f.ONE, f.TwO, f.Three, f.Four, f.FourPointFive, f.Five, f.six, f.Seven, f.NoInsert, f.NoUpdate)

}

Output:

Foo {4,sploop,2,true,4,0,0,"","blargh",0,1010}
Foo {2,thing,5,false,7,-1,0,"","blargh",0,0}
Number of rows with id of 4: 1
Foo {4,sploop,2,true,4,0,0,"","blargh",99,1010}
Example (WithoutMagic)

This example showcases how you can use magicsql to simplify database access without relying on magically generated SQL or structure registration 

package main

import (
	"fmt"

	"github.com/Nerdmaster/magicsql"
	_ "github.com/mattn/go-sqlite3"
)

func getdb() *magicsql.DB {
	// Set up a simple sqlite database
	var db, err = magicsql.Open("sqlite3", "./test.db")
	if err != nil {
		panic(err)
	}

	var sqlStmt = `
		CREATE TABLE IF NOT EXISTS foos (
			id   INTEGER NOT NULL PRIMARY KEY,
			one  TEXT,
			two  INT,
			tree BOOL,
			four INT
		);
		DELETE FROM foos;
	`
	_, err = db.DataSource().Exec(sqlStmt)
	if err != nil {
		panic(err)
	}

	return db
}

// This example showcases how you can use magicsql to simplify database access
// without relying on magically generated SQL or structure registration
func main() {
	// Start an operation
	var db = getdb()
	var op = db.Operation()

	var count = -1
	var rows = op.Query("SELECT count(*) FROM foos")
	for rows.Next() {
		rows.Scan(&count)
	}
	fmt.Printf("Row count at start: %d\n", count)

	// Create a transaction because hey, why not?
	op.BeginTransaction()
	var stmt = op.Prepare("INSERT INTO foos (one,two,tree,four) VALUES (?, ?, ?, ?)")
	stmt.Exec("one", 2, true, 4)
	stmt.Exec("thing", 5, false, 7)
	stmt.Exec("blargh", 1, true, 5)
	stmt.Exec("sploop", 2, true, 4)
	stmt.Close()

	// Instead of calling commit/rollback, we let the transaction figure it out
	// based on its error state
	op.EndTransaction()

	// Create a transaction and force it to fail
	op.BeginTransaction()
	stmt = op.Prepare("INSERT INTO foos (one,two,tree,four) VALUES (?, ?, ?, ?)")
	stmt.Exec("one+", 2, true, 4)
	stmt.Exec("thing+", 5, false, 7)
	stmt.Close()

	rows = op.Query("SELECT COUNT(*) FROM foos")
	count = -1
	for rows.Next() {
		rows.Scan(&count)
	}
	fmt.Println("Count in transaction:", count)

	op.SetErr(fmt.Errorf("forcing rollback"))
	op.EndTransaction()

	// Reset the error state so we can continue working
	op.Reset()

	rows = op.Query("SELECT COUNT(*) FROM foos")
	count = -1
	for rows.Next() {
		rows.Scan(&count)
	}
	fmt.Println("Count after forced rollback:", count)

	rows = op.Query("SELECT one,two,tree,four FROM foos WHERE two > 1", true)
	var one string
	var two, four int
	var tree bool
	for rows.Next() {
		rows.Scan(&one, &two, &tree, &four)
		fmt.Printf("one: %s, two: %d, tree: %#v, four: %d\n", one, two, tree, four)
	}

}

Output:

Row count at start: 0
Count in transaction: 6
Count after forced rollback: 4
one: one, two: 2, tree: true, four: 4
one: thing, two: 5, tree: false, four: 7
one: sploop, two: 2, tree: true, four: 4

Index

Examples

Constants

This section is empty.

Variables

This section is empty.

Functions

This section is empty.

Types

type ConfigTags 

type ConfigTags map[string]string

ConfigTags is a string-to-string map for treating untagged structures as if they were tagged at runtime

type Count 

type Count struct {
	Select
	// contains filtered or unexported fields
}

Count is a subset of a Select used explicitly for counting records

func (Count) RowCount 

func (c Count) RowCount() uint64

type Counter 

type Counter struct {
	RowCount uint64
}

Counter is a one-off type for mapping SELECT COUNT(...) statements

type DB 

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

DB wraps an sql.DB, providing the Operation spawner for deferred-error database operations. Like sql.DB, this DB type is meant to live more or less globally and be a long-living object.

func Open 

func Open(driverName, dataSourceName string) (*DB, error)

Open attempts to connect to a database, wrapping the sql.Open call, returning the new magicsql.DB and error if any. This isn't storing the error for later, as there's nothing which can happen if the database can't be opened.

func Wrap 

func Wrap(db *sql.DB) *DB

Wrap is used to create a new DB from an existing connection

func (*DB) DataSource 

func (db *DB) DataSource() *sql.DB

DataSource returns the underlying sql.DB pointer so the caller can do lower-level work which isn't wrapped in this package

func (*DB) Operation 

func (db *DB) Operation() *Operation

Operation returns an Operation instance, suitable for a short-lived task. This is the entry point for any of the sql wrapped magic. An Operation should be considered a short-lived object which is not safe for concurrent access since it needs to be able to halt on any error with any operation it performs. Concurrent access could be extremely confusing in this context due to the possibility of Operation.Err() returning an error from a different goroutine than the one doing the checking.

type MagicTable 

type MagicTable struct {
	Object interface{}
	Name   string
	RType  reflect.Type
	// contains filtered or unexported fields
}

MagicTable represents a named database table for reading data from a single table into a tagged structure

func Table 

func Table(name string, obj interface{}) *MagicTable

Table registers a table name and an object's type for use in database operations. The returned MagicTable is pre-configured using the object's structure tags.

func (*MagicTable) Configure 

func (t *MagicTable) Configure(conf ConfigTags)

Configure traverses the wrapped structure to figure out which fields map to database table columns and how. If conf is non-nil, that is used in place of struct tags. This is run if a table is created with Table(), but can be useful for reconfiguring a table with explicit ConfigTags.

func (*MagicTable) FieldNames 

func (t *MagicTable) FieldNames() []string

FieldNames returns all known table field names based on the tag parsing done in newMagicTable

func (*MagicTable) InsertArgs 

func (t *MagicTable) InsertArgs(source interface{}) []interface{}

InsertArgs sets up and returns an array suitable for passing to an SQL Exec call for doing an insert

func (*MagicTable) InsertSQL 

func (t *MagicTable) InsertSQL() string

InsertSQL returns the SQL string for inserting a record into this table. This makes the assumption that the primary key is not being set, so it isn't part of the fields list of values placeholder.

func (*MagicTable) ScanStruct 

func (t *MagicTable) ScanStruct(dest interface{}) []interface{}

ScanStruct sets up a structure suitable for calling Scan to populate dest

func (*MagicTable) UpdateArgs 

func (t *MagicTable) UpdateArgs(source interface{}) []interface{}

UpdateArgs sets up and returns an array suitable for passing to an SQL Exec call for doing an update. Returns nil if there's no primary key.

func (*MagicTable) UpdateSQL 

func (t *MagicTable) UpdateSQL() string

UpdateSQL returns the SQL string for updating a record in this table. Returns an empty string if there's no primary key.

type NullableField 

type NullableField struct {
	Value interface{}
}

NullableField implements the sql Scanner interface to make null values suck a little less. When a null value is encountered, it's simply ignored, so the actual source can be set to a value that represents null or left at its default. Data loss can happen if the source fields aren't of proper size, and not all types are supported.

func (*NullableField) Scan 

func (nf *NullableField) Scan(src interface{}) error

Scan implements the Scanner interface. Always returns a nil error. Only works with primitive types or simple mappings of time.Time fields.

type Operation 

type Operation struct {
	Dbg bool
	// contains filtered or unexported fields
}

Operation represents a short-lived single-purpose combination of database calls. On the first failure, its internal error is set, which all "children" (statements, transactions, etc) will see. All children will refuse to perform any functions once an error has occurred, making it safe to perform a chain of related database calls and only check for an error when it makes sense.

When a transaction is started, the operation will route all database calls through the transaction instead of the global database handler. At this time, only one transaction at a time is supported (i.e., no nesting transactions).

func NewOperation 

func NewOperation(db *DB) *Operation

NewOperation creates an operation in its default state: its parent is the passed-in DB instance, and it defaults to using direct database calls until a transaction is started.

func (*Operation) BeginTransaction 

func (op *Operation) BeginTransaction()

BeginTransaction wraps sql's Begin and uses a wrapped sql.Tx to dispatch Query, Exec, and Prepare calls. When the transaction is complete, instead of manually rolling back or committing, simply call op.EndTransaction() and it will rollback / commit based on the error state. If you need to force a rollback, set an error manually with Operation.SetErr().

If a transaction is started while one is already in progress, the operation gets into an error state (i.e., nested transactions are not supported).

func (*Operation) EndTransaction 

func (op *Operation) EndTransaction()

EndTransaction commits the transaction if no errors occurred, or rolls back if there was an error

func (*Operation) Err 

func (op *Operation) Err() error

Err returns the *first* error which occurred on any database call owned by the Operation

func (*Operation) Exec 

func (op *Operation) Exec(query string, args ...interface{}) *Result

Exec wraps sql's DB.Exec, returning a wrapped Result

func (*Operation) OperationTable 

func (op *Operation) OperationTable(mt *MagicTable) *OperationTable

OperationTable allows tying a stored MagicTable to this specific operation, rather than passing table name and an empty interface to Select() and Save()

func (*Operation) Prepare 

func (op *Operation) Prepare(query string) *Stmt

Prepare wrap's sql's DB.Prepare, returning a wrapped Stmt. The statement must be closed by the caller or eventually MySQL will run out of prepared statements.

func (*Operation) Query 

func (op *Operation) Query(query string, args ...interface{}) *Rows

Query wraps sql's Query, returning a wrapped Rows object

func (*Operation) Reset 

func (op *Operation) Reset()

Reset clears the error if any is present

func (*Operation) Rollback 

func (op *Operation) Rollback()

Rollback tries to roll back the transaction even if there is no error

func (*Operation) Save 

func (op *Operation) Save(tableName string, obj interface{}) *Result

Save wraps Operation.Table() and Table.Save(). It creates an INSERT or UPDATE statement for the given object based on whether its primary key is zero. Stores any errors the database returns, and fails if obj isn't tagged with a primary key field.

func (*Operation) Select 

func (op *Operation) Select(tableName string, obj interface{}) Select

Select wraps Operation.Table() and Table.Select(). It creates a Select object for further refining.

func (*Operation) SetErr 

func (op *Operation) SetErr(err error)

SetErr tells the Operation to stop handling any more queries. It shouldn't usually be called directly, but it can be if you need to tell the object "here's a thing that may be an error; don't do any more work if it is".

func (*Operation) Table 

func (op *Operation) Table(tableName string, obj interface{}) *OperationTable

Table creates an OperationTable tied to the given table name and reflecting on obj's type to auto-build certain SQL statements

type OperationTable 

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

OperationTable ties together a memorized MagicTable definition with an in-progress SQL operation

func (*OperationTable) Insert 

func (ot *OperationTable) Insert(obj interface{}) *Result

Insert forces an insert, ignoring any primary key tagging. Note that directly inserting via this method will *not* auto-set the primary key to the last insert id.

TODO: make the DB querying backend more flexible so it doesn't care about tagging for generating SQL and arg lists, instead relying on field and column name mappings

func (*OperationTable) Reconfigure 

func (ot *OperationTable) Reconfigure(conf ConfigTags)

Reconfigure sends explicit ConfigTags data to the underlying MagicTable in order to override the reflected structure's tags

func (*OperationTable) Save 

func (ot *OperationTable) Save(obj interface{}) *Result

Save determines if an INSERT or UPDATE is necessary (primary key of 0 means INSERT here), generates the SQL and arguments, and runs the Exec call on the database. Behavior may be unpredictable if a MagicTable was manually registered with a structure of a different type than obj.

func (*OperationTable) Select 

func (ot *OperationTable) Select() Select

Select simply instantiates a Select instance with the OperationTable set up for it to use for gathering fields and running the query

type Querier 

type Querier interface {
	Query(string, ...interface{}) (*sql.Rows, error)
	Exec(string, ...interface{}) (sql.Result, error)
	Prepare(string) (*sql.Stmt, error)
}

Querier defines an interface for top-level sql types that can run SQL and prepare statements

type Result 

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

Result is a wrapper for sql.Result

func (*Result) Err 

func (r *Result) Err() error

Err returns the first error encountered on any operation the parent DB object oversees

func (*Result) LastInsertId 

func (r *Result) LastInsertId() int64

LastInsertId returns the wrapped Result's LastInsertId() unless an error has occurred, in which case 0 is returned

func (*Result) RowsAffected 

func (r *Result) RowsAffected() int64

RowsAffected returns the wrapped Result's RowsAffected() unless an error has occurred, in which case 0 is returned

type Rows 

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

Rows is a light wrapper for sql.Rows

func (*Rows) Close 

func (r *Rows) Close()

Close wraps sql.Rows.Close()

func (*Rows) Columns 

func (r *Rows) Columns() []string

Columns wraps sql.Rows.Columns(). If an error exists, nil is returned.

func (*Rows) Err 

func (r *Rows) Err() error

Err returns the first error encountered on any operation the parent DB object oversees

func (*Rows) Next 

func (r *Rows) Next() bool

Next wraps sql.Rows.Next(). If an error exists, false is returned.

func (*Rows) Scan 

func (r *Rows) Scan(dest ...interface{})

Scan wraps sql.Rows.Scan()

type Scannable 

type Scannable interface {
	Scan(...interface{})
}

Scannable allows passing around the Rows struct, or something like it, strictly for the purpose of pulling data out

type Select 

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

Select defines the table, where clause, and potentially other elements of an in-progress SELECT statement

func NewSelect 

func NewSelect(ot *OperationTable) Select

NewSelect creates a new Select instance tied to the given OperationTable

func (Select) AllObjects 

func (s Select) AllObjects(ptr interface{})

AllObjects builds the SQL statement, executes it through the parent OperationTable, and returns the resulting objects into ptr, which must be a pointer to a slice of the type tied to this Select.

func (Select) Count 

func (s Select) Count() Count

Count returns a count object built from this select statement

func (Select) EachObject 

func (s Select) EachObject(dest interface{}, cb func())

EachObject mimics AllObjects, but yields each item to the callback instead of requiring a slice in which to put all of them at once

Example 
package main

import (
	"fmt"
	"github.com/Nerdmaster/magicsql"

	_ "github.com/mattn/go-sqlite3"
)

func main() {
	var db, err = magicsql.Open("sqlite3", "./test.db")
	if err != nil {
		panic(err)
	}
	var op = db.Operation()

	op.Exec("DROP TABLE IF EXISTS people; CREATE TABLE people (name TEXT, age INT)")
	op.Exec("INSERT INTO people VALUES ('Joe', 100), ('Jill', 101), ('Doug', 102), ('Deb', 103)")

	var person struct {
		Name string
		Age  int
	}

	// The "&person" duplication is a necessary evil - we have to tell the Select
	// shortcut what structure to magick up, and then we have to tell EachObject
	// where to put its data
	op.Select("people", &person).EachObject(&person, func() {
		fmt.Printf("%s is %d dog years old\n", person.Name, person.Age*7)
		fmt.Printf("That's %d in people years!\n", person.Age)
	})

	// This makes more sense when using an OperationTable rather than a
	// one-off select.  For instance:
	var t = op.Table("people", &person)
	person.Name = "Pat"
	person.Age = 1
	t.Insert(&person)

	t.Select().Where("Age < ?", 100).EachObject(&person, func() {
		fmt.Printf("%s is only %d dog years old\n", person.Name, person.Age*7)
	})

}

Output:

Joe is 700 dog years old
That's 100 in people years!
Jill is 707 dog years old
That's 101 in people years!
Doug is 714 dog years old
That's 102 in people years!
Deb is 721 dog years old
That's 103 in people years!
Pat is only 7 dog years old

func (Select) EachRow 

func (s Select) EachRow(cb func(Scannable))

EachRow wraps Query, yielding a Scannable per row to the callback instead of returning a *Rows object

Example 
package main

import (
	"fmt"
	"github.com/Nerdmaster/magicsql"

	_ "github.com/mattn/go-sqlite3"
)

func main() {
	var db, err = magicsql.Open("sqlite3", "./test.db")
	if err != nil {
		panic(err)
	}
	var op = db.Operation()

	op.Exec("DROP TABLE IF EXISTS people; CREATE TABLE people (name TEXT, age INT)")
	op.Exec("INSERT INTO people VALUES ('Joe', 100), ('Jill', 101), ('Doug', 102), ('Deb', 103)")

	var person struct {
		Name string
		Age  int
	}
	op.Select("people", &person).EachRow(func(r magicsql.Scannable) {
		r.Scan(&person.Name, &person.Age)
		fmt.Printf("%s is %d years old\n", person.Name, person.Age)
		fmt.Printf("That's %d in dog years!\n", person.Age*7)
	})

}

Output:

Joe is 100 years old
That's 700 in dog years!
Jill is 101 years old
That's 707 in dog years!
Doug is 102 years old
That's 714 in dog years!
Deb is 103 years old
That's 721 in dog years!

func (Select) First 

func (s Select) First(dest interface{}) (ok bool)

First builds the SQL statement, executes it through the parent OperationTable, and returns the first object into dest. If there are no rows, ok is false.

Example

This shows pulling a single record from the database without having to deal with any looping 

package main

import (
	"fmt"
	"github.com/Nerdmaster/magicsql"

	_ "github.com/mattn/go-sqlite3"
)

func main() {
	var db, err = magicsql.Open("sqlite3", "./test.db")
	if err != nil {
		panic(err)
	}
	var op = db.Operation()

	op.Exec("DROP TABLE IF EXISTS people; CREATE TABLE people (name TEXT, age INT)")
	op.Exec("INSERT INTO people VALUES ('Joe', 0), ('Jill', 1), ('Doug', 2), ('Deb', 3)")

	var person struct {
		Name string
		Age  int
	}

	var ok = op.Select("people", &person).Order("age desc").First(&person)
	fmt.Printf("%#v, %#v\n", ok, person)

}

Output:

true, struct { Name string; Age int }{Name:"Deb", Age:3}
Example (NoData)

This shows Select.First when there's no data to pull 

package main

import (
	"fmt"
	"github.com/Nerdmaster/magicsql"

	_ "github.com/mattn/go-sqlite3"
)

func main() {
	var db, err = magicsql.Open("sqlite3", "./test.db")
	if err != nil {
		panic(err)
	}
	var op = db.Operation()

	op.Exec("DROP TABLE IF EXISTS people; CREATE TABLE people (name TEXT, age INT)")

	var person struct {
		Name string
		Age  int
	}

	person.Name = "Not initialized"
	var ok = op.Select("people", &person).Order("age desc").First(&person)
	fmt.Printf("%#v, %#v\n", ok, person)

}

Output:

false, struct { Name string; Age int }{Name:"Not initialized", Age:0}

func (Select) Limit 

func (s Select) Limit(l uint64) Select

Limit sets (or overwrites) the limit value

func (Select) Offset 

func (s Select) Offset(o uint64) Select

Offset sets (or overwrites) the offset value

func (Select) Order 

func (s Select) Order(o string) Select

Order sets (or overwrites) the order clause

func (Select) Query 

func (s Select) Query() *Rows

Query builds the SQL statement, executes it through the parent OperationTable, and returns the resulting rows

Example

This is an example of using Select.Query to work with raw rows 

package main

import (
	"fmt"
	"github.com/Nerdmaster/magicsql"

	_ "github.com/mattn/go-sqlite3"
)

func main() {
	var db, err = magicsql.Open("sqlite3", "./test.db")
	if err != nil {
		panic(err)
	}
	var op = db.Operation()

	op.Exec("DROP TABLE IF EXISTS people; CREATE TABLE people (name TEXT, age INT)")
	op.Exec("INSERT INTO people VALUES ('Joe', 0), ('Jill', 1), ('Doug', 2), ('Deb', 3)")

	var person struct {
		Name string
		Age  int
	}

	var r = op.Select("people", &person).Query()
	for r.Next() {
		r.Scan(&person.Name, &person.Age)
		fmt.Printf("%s is %d years old\n", person.Name, person.Age)

		// Let's use a more scientific computation for dog years here
		var dy = person.Age * 4
		if person.Age >= 1 {
			dy += 8
		}
		if person.Age >= 2 {
			dy += 8
		}
		fmt.Printf("That's %d in dog years!\n", dy)
	}

}

Output:

Joe is 0 years old
That's 0 in dog years!
Jill is 1 years old
That's 12 in dog years!
Doug is 2 years old
That's 24 in dog years!
Deb is 3 years old
That's 28 in dog years!

func (Select) SQL 

func (s Select) SQL() string

SQL returns the raw query this Select represents

func (Select) Where 

func (s Select) Where(w string, args ...interface{}) Select

Where sets (or overwrites) the where clause information

type Stmt 

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

Stmt is a light wrapper for sql.Stmt

func (*Stmt) Close 

func (s *Stmt) Close()

Close wraps sql.Stmt.Close()

func (*Stmt) Err 

func (s *Stmt) Err() error

Err returns the first error encountered on any operation the parent DB object oversees

func (*Stmt) Exec 

func (s *Stmt) Exec(args ...interface{}) *Result

Exec wraps sql.Stmt.Exec(), returning a wrapped Result

func (*Stmt) Query 

func (s *Stmt) Query(args ...interface{}) *Rows

Query wraps sql.Stmt.Query(), returning a wrapped Rows

Source Files

View all Source files

Directories

Path Synopsis

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.