lore

package module
v0.0.0-...-c48946c Latest Latest
Warning

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

 Go to latest Published: Oct 21, 2017 License: MIT Imports: 6 Imported by: 0

README

LORE

GoDoc Build Status Go Report Card gocover.io

Light Object-Relational Environment (LORE) provides a simple and lightweight pseudo-ORM/pseudo-struct-mapping environment for Go.

Motivation

With LORE, you weave your own lore and control your own magic (... bear with me, I'll explain). LORE provides a thin veil that abstracts away some of the inconveniences of object-relational mapping in general, but intentionally and explicitly avoids doing any hand-wavy magic tricks that place someone else's mystery black box between you and your data.

To do so, LORE acts as a simple layer gluing together some wonderful and powerful libraries, providing a minimal abstraction above them for convenience:

  • Squirrel (https://github.com/Masterminds/squirrel) for SQL generation.
    • In particular, the entrypoints for building queries via LORE (Query.BuildSqlSelect/Insert/Update/Delete and related Query methods) directly return squirrel SQL builders, so you get all of the power of Squirrel with some added convenience (tablename handling, common related queries, etc.) via LORE.
  • SQLX (http://jmoiron.github.io/sqlx/) for running DB SQL queries and parsing results.

Aside from this, you're in charge of your own schema, migration, etc. - no full ORM and cruft to get in the way of your lore!

Lastly, while I'm sure it could still be improved, I've tried (and will continue to try) to ensure documentation provides full transparency of what is going on, so that your lore is entirely in your control.

Guide and examples

Below is a run-through of getting set up and constructing some simple examples. Check GoDoc/source documentation for more information on any item, and look through the test source files for more thorough examples of usage. I also recommend using string constants everywhere for your db field names (string literals everywhere are literally the devil and should be banned from your lore IMO - pun intended), but am just using literals here ONLY for simplicity and succinctness.

Config

When initializing your app, you should inform LORE of your desired config before constructing any LORE queries. To do so, you can use the SetConfig function and pass in a *lore.Config. Currently, this only determines the SQL placeholder format used in all Squirrel queries.

lore.SetConfig(&lore.Config{
    SQLPlaceholderFormat: lore.SQLPlaceholderFormatDollar, // Or: SQLPlaceholderFormatQuestion
})

// Or you can use the default config, which currently sets to the dollar-sign/$# placeholder format,
// but it's probably best to call SetConfig explicitly as above.
lore.SetConfigDefault()
Model interface

To use LORE with your model, your model should implement lore.ModelInterface; see an example of this below. Note that you can use "db:" tags for sqlx directly; LORE currently doesn't do any specific handling of tags, as it delegates this all to sqlx.

Please check out the GoDoc for ModelInterface for much more detail for each method you need to implement, in particular DbFieldMap (which shouldn't include auto-managed keys, such as serial keys).

type Legend struct {
    Id      int     `db:"id"` // Serial/auto-incrementing primary key
    Name    string  `db:"name"`
    Culture string  `db:"culture"`
}

var _ lore.ModelInterface = (*Legend)(nil)

func (*Legend) DbTableName() string {
    return "legends"
}

func (l *Legend) DbFieldMap() map[string]interface{} {
    // Note that Id is purposefully left out, since this is a serial/auto-incrementing field!
    return map[string]interface{}{
        "name": l.Name,
        "culture": l.Culture,
    }
}

func (*Legend) DbPrimaryFieldKey() string {
    return "id"
}

func (l *Legend) DbPrimaryFieldValue() interface{} {
    return l.Id
}
Build SQL query

Now we can use LORE to build a SQL query for your model. This involves 3 steps:

  1. Create new *lore.Query instance, passing in your model (implementing ModelInterface).
  2. Build SQL query via the lore.Query.BuildSql* methods, which wrap Squirrel builders.
  3. Pass the completed SQL builder you made in step (2) back to the Query from (1) via lore.Query.SetSqlBuilder. Note that this can be combined with step (2) in simple cases as in the example below.

Let's try building the following query on the Legend model we defined above:

SELECT * FROM legends WHERE name = 'Mars' AND culture = 'Roman' LIMIT 1;

/*
1. Create new *lore.Query with our Legend model.
*/
q := lore.NewQuery(&Legend{})

/*
2. Build the SQL query; here we use the BuildSqlSelectStar entrypoint and finish building via
Squirrel, then set it back into the Query via SetSqlBuilder in the same command. This tells LORE
that this is the SQl that will be run when the Query is executed later.

Note that BuildSqlSelectStar is an example of just one convenience wrapper that LORE provides;
alternatively, you can just build the SQL via Squirrel, or use BuildSqlSelect and pass in your
own column names, etc.
*/
q.SetSqlBuilder(
    q.BuildSqlSelectStar(). // This returns a Squirrel builder directly now, so the rest of this chain here is purely Squirrel.
    Where(squirrel.Eq{
        "name": "Mars",
        "culture": "Roman",
    }).
    Limit(1),
)

// If you want, you can use ToSql to get the Squirrel ToSql representation of the Query at any time.
qSql, qArgs, err := q.ToSql()
Execute SQL query on your DB

Now that we've built a *lore.Query and attached our SQL builder to it, we can execute this as a query against our DB. LORE currently provides 3 methods for doing so:

  1. *lore.Query.Execute - wraps sqlx.DB.Exec
  2. *lore.Query.ExecuteThenParseSingle - wraps sqlx.DB.Get
  3. *lore.Query.ExecuteThenParseList - wraps sqlx.DB.List

All of these methods attempt to return the number of rows affected by the query, along with of course an error if one was encountered. See GoDoc/source for more details, especially regarding numRowsAffected (see comments for the Execute functions in particular).

/*
Execute and parse to list. Note that we pass in a POINTER to a list of structs we want to
scan the DB rows back into - when passing into the Execute* Query methods, LORE assumes you're
passing in a pointer (either to a list or a single struct), and will return an error if it
detects otherwise.
*/
db := getDb() // ... Your own lore should conjure up a *sqlx.DB instance here.
discoveredLegend := &Legend{}
// See notes for numRowsAffected in Query.Execute documentation.
numRowsAffected, err := q.ExecuteThenParseSingle(db, discoveredLegend)
if err != nil {
    // Handle errors here.
}
// Row matching the SQL query is written into discoveredLegend. Do whatever with it now.
discoveredLegend.GetCorrespondingCelestialBody()
discoveredLegend.TellUsYourTale()

...

/*
Also try the other query wrappers listed below for creating your SQL... (See GoDoc/source for more
details and possibly more functions)
*/
q.SetSqlBuilder(
    q.BuildSqlSelect(columns ...string)
    q.BuildSqlSelectStar()

    q.BuildSqlInsert()
    q.BuildSqlInsertColumnsAndValues(columns []string, values []interface{})
    q.BuildSqlInsertModel()

    q.BuildSqlUpdate()
    q.BuildSqlUpdateModelByPrimaryKey()

    q.BuildSqlDelete()
    q.BuildSqlDeleteModelByPrimaryKey()
)
Convenience wrappers

The functions below handle both building a SQL statement and executing it on the DB you supply, all in one easy call. For many typical use cases, you might find you can just call these and not worry about the intermediate steps (note that you don't even need to explicitly create a Query, though you still need to supply a config on app init!). See the GoDoc/source for more details of any particular function.

lore.SelectModelByPrimaryKey
lore.SelectModelsWhere
lore.InsertNewModel
lore.UpdateModelByPrimaryKey
lore.UpdateSetMapWhere
lore.DeleteModelByPrimaryKey
lore.DeleteModelsWhere

DANGER: WIP

LORE is a major WIP. Contributions are welcome, but use in production is cautioned against at the moment unless you know full well what you're doing!

TODO

  • Augment convenience SQL-and-execute functions to call only Execute with no parse result if resultPtr is nil.
  • Augment convenience SQL-and-execute functions with ORDER BY support.
  • Allow using sqlx QueryRow/QueryRowX for large/unrestricted-length queries instead of just Get/Select.
  • Better tests, especially for Execute* methods and SQL-and-execute functions.
  • Consider better way to relate updates to SQL-builders to the parent query without having to call SetSqlBuilder every time.
  • Dedicated examples in GoDoc.

Final notes

Thanks for looking, and please feel free to message me if you're having any problems with LORE! I'm also always open to suggestions on ways to improve LORE, whether minor changes/fixes or even large rewrites - always happy to learn of better ways to do things!

Documentation

Index

Constants

View Source 
const (
	// STAR provides a hard-coded constant string representing the common star/* character in SQL.
	STAR string = "*"
	// RETURNING_STAR provides a hard-coded constant string representing the common "RETURNING *"
	// clause in SQL.
	RETURNING_STAR string = "RETURNING *"
)

Variables

View Source 
var SQLPlaceholderFormatDollar = squirrel.Dollar

SQLPlaceholderFormatDollar aliases squirrel.Dollar.

View Source 
var SQLPlaceholderFormatQuestion = squirrel.Question

SQLPlaceholderFormatQuestion aliases squirrel.Question.

Functions

func DeleteModelByPrimaryKey 

func DeleteModelByPrimaryKey(mi ModelInterface, db *sqlx.DB, resultSinglePtr interface{}) (found bool, err error)

DeleteModelByPrimaryKey builds and executes a SQL statement on the db similar to the following SQL, scanning the result into resultSinglePtr. This is essentially a convenience wrapper around BuildSqlDeleteModelByPrimaryKey and ExecuteThenParseSingle.

`DELETE FROM <table> WHERE <primary field key> = <primary field value> RETURNING * ;`

func DeleteModelsWhere 

func DeleteModelsWhere(mi ModelInterface, db *sqlx.DB, where *sqlPart, resultListPtr interface{}) (numRowsAffected uint64, err error)

DeleteModelsWhere builds and executes a SQL statement on the db similar to the following SQL, scanning the result into resultListPtr. This is essentially a convenience wrapper around BuildSqlDelete and ExecuteThenParseList, applying the WHERE clause accordingly.

Note that where is a pointer; if no where is desired, pass nil instead. When desired, this is typically something like a squirrel.Eq instance, etc. For your convenience, you can use lore.Where to build a single Where-ish object around a squirrel.Eq/squirrel.Gt/etc.

`DELETE FROM <table> WHERE <where conditions> RETURNING * ;`

func InsertNewModel 

func InsertNewModel(mi ModelInterface, db *sqlx.DB, resultSinglePtr interface{}) error

InsertNewModel builds and executes a SQL statement on the db similar to the following SQL, scanning the result into resultSinglePtr. This is essentially a convenience wrapper around BuildSqlInsertModel and ExecuteThenParseSingle.

`INSERT INTO <table> (<columns from DbFieldMap>) VALUES (<values from DbFieldMap>) RETURNING * ;`

func SelectModelByPrimaryKey 

func SelectModelByPrimaryKey(mi ModelInterface, db *sqlx.DB, resultSinglePtr interface{}) (found bool, err error)

SelectModelByPrimaryKey builds and executes a SQL statement on the db similar to the following SQL, scanning the result into resultSinglePtr. This is essentially a convenience wrapper around BuildSqlSelectStar and ExecuteThenParseSingle.

`SELECT * FROM <table> WHERE <primary field key> = <primary field value> LIMIT 1;`

func SelectModelWhere 

func SelectModelWhere(mi ModelInterface, db *sqlx.DB, where *sqlPart, resultSinglePtr interface{}) (found bool, err error)

SelectModelWhere builds and executes a SQL statement on the db similar to the following SQL, scanning the result into resultSinglePtr. This is essentially a convenience wrapper around BuildSqlSelectStar and ExecuteThenParseSingle, applying the WHERE clause accordingly.

Note that where is a pointer; if no where is desired, pass nil instead. When desired, this is typically something like a squirrel.Eq instance, etc. For your convenience, you can use lore.Where to build a single Where-ish object around a squirrel.Eq/squirrel.Gt/etc.

`SELECT * FROM <table> WHERE <where conditions> LIMIT 1;`

func SelectModelsWhere 

func SelectModelsWhere(mi ModelInterface, db *sqlx.DB, where *sqlPart, limit *uint64, resultListPtr interface{}) (numRowsAffected uint64, err error)

SelectModelsWhere is analogous to SelectModelWhere, but wraps ExecuteThenParseList instead of ExecuteThenParseSingle, and allows appying a LIMIT clause too.

Note that limit is a pointer here - if nil is supplied, no limit is set on the SQL statement; otherwise, the underlying limit uint64 is applied.

`SELECT * FROM <table> WHERE <where conditions> LIMIT <limit>;`

func SetConfig 

func SetConfig(c *Config)

SetConfig sets the current config for all future LORE queries.

func SetConfigDefault 

func SetConfigDefault()

SetConfigDefault sets a default config for all future LORE queries.

func UpdateModelByPrimaryKey 

func UpdateModelByPrimaryKey(mi ModelInterface, db *sqlx.DB, resultSinglePtr interface{}) (found bool, err error)

UpdateModelByPrimaryKey builds and executes a SQL statement on the db similar to the following SQL, scanning the result into resultSinglePtr. This is essentially a convenience wrapper around BuildSqlUpdateModelByPrimaryKey and ExecuteThenParseSingle.

`UPDATE <table> SET <columns and values from DbFieldMap> 

WHERE <primary field key> = <primary field value> RETURNING * ;`

func UpdateSetMapWhere 

func UpdateSetMapWhere(mi ModelInterface, db *sqlx.DB, m map[string]interface{}, where *sqlPart, resultListPtr interface{}) (numRowsAffected uint64, err error)

UpdateSetMapWhere builds and executes a SQL statement on the db similar to the following SQL, scanning the result into resultListPtr. This is essentially a convenience wrapper around BuildSqlUpdateSetMap and ExecuteThenParseList, applying the WHERE clause accordingly.

Note that where is a pointer; if no where is desired, pass nil instead. When desired, this is typically something like a squirrel.Eq instance, etc. For your convenience, you can use lore.Where to build a single Where-ish object around a squirrel.Eq/squirrel.Gt/etc.

`UPDATE <table> SET <columns and values from map> WHERE <where conditions> RETURNING * ;`

func Where 

func Where(pred interface{}, args ...interface{}) *sqlPart

Where returns a new part as a Sqlizer interface, allowing this to be called in typical WHERE clauses via Squirrel.

Types

type Config 

type Config struct {
	/*
		DB driver's placeholder format for injecting query parameters via SQL.
	*/
	SQLPlaceholderFormat SQLPlaceholderFormat
}

Config provides a struct for configuring all LORE queries.

func GetConfig 

func GetConfig() *Config

GetConfig returns the current config object. If no config already exists, a default is given.

func GetConfigDefault 

func GetConfigDefault() *Config

GetConfigDefault returns the default config object.

type ModelInterface 

type ModelInterface interface {
	/*
		DbTableName provides the name of the corresponding table in the database for this model.
	*/
	DbTableName() string
	/*
		DbFieldMap builds a map for this model instance from field/column name to this instance's
		current value for that field/column. Note that this should NOT include auto-managed db
		fields, such as SERIAL/AUTO-INCREMENTING KEYS, unless you want to try to specifically
		override these each time (typically not the case).

		This is used to provide Insert and Update functionality convenience by providing all of the
		columns and values to write into the table row.
	*/
	DbFieldMap() map[string]interface{}
	/*
		DbPrimaryFieldKey returns a string indicating the column name of the primary field of the
		model. This is used for Update and Delete queries for the WHERE condition.

		If the implementing model does not have such a field, an empty string can be returned, which
		will result in an error being thrown if any methods are called that require a non-empty
		field (such as an UPDATE that should have a non-empty WHERE condition for updating by
		primary key). If multiple columns define the primary field, you will have to implement the
		condition yourself instead.
	*/
	DbPrimaryFieldKey() string
	/*
		DbPrimaryFieldValue returns the current value of the primary field of the model. This is
		used for Update and Delete queries for the WHERE condition in conjunction with
		DbPrimaryFieldKey.

		If the implementing model does not have such a primary field, nil can be returned. Note that
		an error will be thrown if the DbPrimaryFieldKey method returns an empty string (see
		DbPrimaryFieldKey), but an error is not necessarily thrown if DbPrimaryFieldKey is
		non-empty and DbPrimaryFieldValue is nil (i.e., nil is a valid value to set for a primary
		field value if you really want).
	*/
	DbPrimaryFieldValue() interface{}
}

ModelInterface provides a generic interface for enabling external models to interface with internal machinery here.

type Query 

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

Query provides a generic, chainable query instance for callers to configure a specific query.

func NewQuery 

func NewQuery(modelInterface ModelInterface) *Query

NewQuery instantiates a new Query instance based on the given ModelInterface.

func (*Query) BuildSqlDelete 

func (q *Query) BuildSqlDelete() squirrel.DeleteBuilder

BuildSqlDelete provides the entrypoint for specializing a generic Query as a DELETE query on the table for the given ModelInterface. This directly returns a new squirrel.DeleteBuilder that can be placed back into the Query instance via SetSqlBuilder; the underlying SQL has the form: `DELETE FROM <DbTableName>`.

func (*Query) BuildSqlDeleteModelByPrimaryKey 

func (q *Query) BuildSqlDeleteModelByPrimaryKey() (squirrel.DeleteBuilder, error)

BuildSqlDeleteModelByPrimaryKey wraps BuildSqlDelete to perform the delete on the table row with the matching primary key/value of this Query's ModelInterface's model instance. Alias for `query.BuildSqlDelete().Where(<primary key and value>)`.

func (*Query) BuildSqlInsert 

func (q *Query) BuildSqlInsert() squirrel.InsertBuilder

BuildSqlInsert provides the entrypoint for specializing a generic Query as an INSERT query on the table for the given ModelInterface. This directly returns a new squirrel.InsertBuilder that can be placed back into the Query instance via SetSqlBuilder; the underlying SQL has the form: "INSERT INTO <DbTableName>".

func (*Query) BuildSqlInsertColumnsAndValues 

func (q *Query) BuildSqlInsertColumnsAndValues(columns []string, values []interface{}) squirrel.InsertBuilder

BuildSqlInsertColumnsAndValues wraps BuildSqlInsert with the given columns and values. Alias for query.BuildSqlInsert().Columns(<columns...>).Values(<values...>).

func (*Query) BuildSqlInsertModel 

func (q *Query) BuildSqlInsertModel() (squirrel.InsertBuilder, error)

BuildSqlInsertModel wraps BuildSqlInsert with the given ModelInterface's DbFieldMap as the INSERT query's columns and values. Uses the ModelInterface the Query was originally created with. Alias for query.BuildSqlInsertColumnsAndValues(<model's DbFieldMap keys>, <model's DbFieldMap values>).

func (*Query) BuildSqlSelect 

func (q *Query) BuildSqlSelect(columns ...string) squirrel.SelectBuilder

BuildSqlSelect provides the entrypoint for specializing a generic Query as a SELECT query on the table for the given ModelInterface. This directly returns a new squirrel.SelectBuilder that can be placed back into the Query instance via SetSqlBuilder; the underlying SQL has the form: "SELECT <columns> FROM <DbTableName>".

func (*Query) BuildSqlSelectStar 

func (q *Query) BuildSqlSelectStar() squirrel.SelectBuilder

BuildSqlSelectStar wraps BuildSqlSelect as a SELECT * query. Alias for query.BuildSqlSelect("*").

func (*Query) BuildSqlUpdate 

func (q *Query) BuildSqlUpdate() squirrel.UpdateBuilder

BuildSqlUpdate provides the entrypoint for specializing a generic Query as an UPDATE query on the table for the given ModelInterface. This directly returns a new squirrel.UpdateBuilder that can be placed back into the Query instance via SetSqlBuilder; the underlying SQL has the form: `UPDATE <DbTableName>`.

func (*Query) BuildSqlUpdateModelByPrimaryKey 

func (q *Query) BuildSqlUpdateModelByPrimaryKey() (squirrel.UpdateBuilder, error)

BuildSqlUpdateModelByPrimaryKey wraps BuildSqlUpdate to perform the update with the given columns and values defined by the Query's ModelInterface's DbFieldMap on the table row with the matching primary key/value for this ModelInterface's model instance. Alias for `query.BuildSqlUpdate().Where(<primary key and value>).Set(<columns and values according to DbFieldMap>)`.

func (*Query) BuildSqlUpdateSetMap 

func (q *Query) BuildSqlUpdateSetMap(m map[string]interface{}) squirrel.UpdateBuilder

BuildSqlUpdateSetMap wraps BuildSqlUpdate with the columns and values in the given map. Alias for `query.BuildSqlUpdate().SetMap(<map of columns to values>)`.

func (*Query) Execute 

func (q *Query) Execute(db *sqlx.DB) (numRowsAffected uint64, err error)

Execute wraps sqlx.DB.Exec, and does not parse the result into any struct. However, it still returns the basic info of numRowsAffected IN SOME CASES (see below).

NOTE: As mentioned at http://jmoiron.github.io/sqlx/#exec access to numRowsAffected (via sqlx.Result.RowsAffected) is db-driver-dependent.

func (*Query) ExecuteThenParseList 

func (q *Query) ExecuteThenParseList(db *sqlx.DB, resultListPtr interface{}) (numRowsAffected uint64, err error)

ExecuteThenParseList wraps sqlx.DB.Select to execute the query and then parse the result into the given list of structs in resultSinglePtr. Scanning the db results into the result interface is done according to typical sqlx scanning behavior for sqlx.DB.Select.

Note that resultListPtr should be a POINTER TO A LIST OF STRUCTS that you want to scan the results into. An error will be returned if resultListPtr is not detected as a pointer to a list.

This function returns numRowsAffected equal to the length derived from the result slice after the query has been run.

NOTE: Any callers of this function should ensure the underlying query is appropriately bounded, as sqlx.DB.Select will load the entire result set into memory at once; see http://jmoiron.github.io/sqlx/#getAndSelect for more information.

func (*Query) ExecuteThenParseSingle 

func (q *Query) ExecuteThenParseSingle(db *sqlx.DB, resultSinglePtr interface{}) (found bool, err error)

ExecuteThenParseSingle wraps sqlx.DB.Get to execute the query and then parse the result into the given single struct in resultSinglePtr. Scanning the db results into the result interface is done according to typical sqlx scanning behavior for sqlx.DB.Get.

When sql.ErrNoRows is encountered by the underlying sqlx.DB.Get query (any time no matching row is found for this query), this function returns with found equal to false (naturally), but DOES NOT RETURN ANY ERROR (even though the sql package did).

Note that resultSinglePtr should be a POINTER TO A SINGLE STRUCT that you want to scan the results into. An error will be returned if resultSinglePtr is not detected as a pointer to a single struct.

func (*Query) SetSqlBuilder 

func (q *Query) SetSqlBuilder(sqlBuilder SqlBuilderInterface)

SetSqlBuilder sets the Query instance's internal sqlBuilder to the given SqlBuilderInterface instance.

func (*Query) ToSql 

func (q *Query) ToSql() (sql string, args []interface{}, err error)

ToSql wraps the ToSql method of the internal sqlBuilder, returning the SQL-with-args form of the query according to the internal sqlBuilder.

This requires that SetSqlBuilder has already been called, typically with a sqlBuilder/squirrel builder. If not, an error is returned.

type SQLPlaceholderFormat 

type SQLPlaceholderFormat squirrel.PlaceholderFormat

SQLPlaceholderFormat aliases squirrel.PlaceholderFormat.

type SqlBuilderInterface 

type SqlBuilderInterface interface {
	ToSql() (sql string, args []interface{}, err error)
}

SqlBuilderInterface provides a generic interface for SQL-izing. This mirrors the squirrel.Sqlizer interface.

type SquirrelStatementBuilder 

type SquirrelStatementBuilder SqlBuilderInterface

SquirrelStatementBuilder provides a generic interface for squirrel statement builders. Wraps SqlBuilderInterface.

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.