granitic: github.com/graniticio/granitic/rdbms Index | Files

package rdbms

import "github.com/graniticio/granitic/rdbms"

Package rdms provides types for interacting with relational (SQL) databases.

A full explanation of Grantic's patterns for relational database access management system (RDBMS) access can be found at http://granitic.io/1.0/ref/rdbms-access A brief explanation follows:

Principles

Granitic's RDBMS access types and components adhere to a number of principles:

1. Application developers should be given the option to keep query definitions separate from application code.

2. Boilerplate code for generating queries and mapping results should be minimised.

3. Transactional and non-transactional DB access should not require different coding patterns.

4. Code that is executing queries against a database should be readable and have obvious intent.

Facilities

To use Granitic's RDBMS access, your application will need to enable both the QueryManager and RdbmsAccess facilities. See http://granitic.io/1.0/ref/facilities for more details.

Components and types

Enabling these facilities creates the QueryManager and RdbmsClientManager components. Your application must provide a DatabaseProvider (see below)

DatabaseProvider    A component able to provide connections to an RDBMS by creating
                    and managing Go sql.DB objects.

QueryManager        A component that loads files containing template queries from a filesystem
                    and can populate than with provided variables to create a complete SQL query.

RdbmsClient         A type providing methods to execute templated queries against an RDBMS.

RdbmsClientManager  A component able to create RdbmsClient objects.

RowBinder           A type able to map SQL query results into Go structs.

DatabaseProvider

Because of the way Go applications are built (statically linked), drivers for individual RDBMSs cannot be dynamically loaded at runtime. To avoid the Granitic framework importing large numbers of third-party libraries, Granitic application developers must create a component that implements rdbms.DatabaseProvider and imports the driver for the database in use.

A simple implementation of DatabaseProvider for a MySQL database can be found in the granitic-examples repo on GitHub in the recordstore/database/provider.go file.

Once you have an implementation, you will need to create a component for it in your application's component definition file similar to:

{
  "dbProvider": {
    "type": "database.DBProvider"
  }
}

As long as you only have one implementation of DatabaseProvider registered as a component, it will automatically be injected into the RdbmsClientManager component.

QueryManager

Refer to the dsquery package for more details on how QueryManagers work.5

RdbmsClientManager

Granitic applications are discouraged from directly interacting with sql.DB objects (although of course they are free to do so). Instead, they use instances of RdbmsClient. RdbmsClient objects are not reusable across goroutines, instead your application will need to ask for a new one to be created for each new goroutine (e.g. for each request in a web services application).

The component that is able to provide these clients is RdbmsClientManager.

Auto-injection of an RdbmsClientManager

Any component that needs an RdbmsClient should have a field:

DbClientManager rdbms.RdbmsClientManager

The name DbClientManager is a default. You can change the field that Granitic looks for by setting the following in your application configuration.

{
  "RdbmsAccess":{
    "InjectFieldNames": ["DbClientManager", "MyAlternateFieldName"]
  }
}

Your code then obtains an RdbmsClient in a manner similar to:

if rc, err := id.DBClientManager.Client(); err != nil {
  return err
}

RdbmsClient

Application code executes SQL (either directly or via a templated query) and interacts with transactions via an instance of RdbmsClient. Refer to the GoDoc for RdbmsClient for information on the methods available, but the general pattern for the methods available on RdbmsClient is:

SQLVerb[BindingType]QID[ParameterSource]

Where

SQLVerb           Is Select, Delete, Update or Insert
BindingType       Is optional and can be Bind or BindSingle
ParameterSource   Is optional and can be either Param or Params

QID

A QID is the ID of query stored in the QueryManager.

Parameter sources

Parameters to populate template queries can either be supplied via a pair of values (Param), a map[string]interface{} (Params) or a struct whose fields are optionally annotated with the `dbparam` tag. If the dbparam tag is not present on a field, the field's name is used a the parameter key.

Binding

RdbmsClient provides a mechanism for automatically copying result data into structs or slices of structs. If the RdbmsClient method name contains BindSingle, you will pass a pointer to a struct into the method and its fields will be populated:

ad := new(ArtistDetail)

if found, err := rc.SelectBindSingleQIdParams("ARTIST_DETAIL", rid, ad); found {
  return ad, err
} else {
  return nil, err
}

If the method contains the word Bind (not BindSingle), you will supply an example 'template' instance of a struct and the method will return a slice of that type:

ar := new(ArtistSearchResult)

if r, err := rc.SelectBindQIdParams("ARTIST_SEARCH_BASE", make(map[string]interface{}), ar); err != nil {
  return nil, err
} else {
  return id.artistResults(r), nil
}

Transactions

To call start a transaction, invoke the StartTransaction method on the RDBMSCLient like:

db.StartTransaction()
defer db.Rollback()

and end your method with:

db.CommitTransaction()

The deferred Rollback call will do nothing if the transaction has previously been commited.

Direct access to Go DB methods

RdbmsClient provides pass-through access to sql.DB's Exec, Query and QueryRow methods. Note that these methods are compatible with Granitic's transaction pattern as described above.

Multiple databases

This iteration of Granitic is optimised for the most common use-case for RDBMS access, where a particular Granitic application will access a single logical database. It is fully acknowledged that there are many situations where an application needs to access mutiple logical databases.

Facility support for that use-case will be added in later versions of Granitic, but for now you have two options:

Option 1: use this facility to provide support for your application's 'main' database and manually add components of type rdbms.DefaultRDBMSClientManager to your component definition file to support your other database.

Option 2: disable this facility and manually add components of type rdbms.GraniticRdbmsClientManager to your component definition file to support all of your databases.

Index

Package Files

client.go insertfunctions.go manager.go rowbind.go tags.go

Constants

const (
    Unset = iota
    NilBool
    NilString
    NilInt
    NilFloat
)
const (
    // The name of a Go tag on struct fields that can be used to map that field to a parameter name
    DBParamTag = "dbparam"
)

func DefaultInsertWithReturnedId Uses

func DefaultInsertWithReturnedId(query string, client *RdbmsClient, target *int64) error

An implementation of InsertWithReturnedId that will work with any Go database driver that implements LastInsertId

func ParamsFromFieldsOrTags Uses

func ParamsFromFieldsOrTags(sources ...interface{}) (map[string]interface{}, error)

ParamsFromFieldsOrTags takes one or more objects (that must be a map[string]interface{} or a pointer to a struct) and returns a single map[string]interface{}. Keys and values are copied from supplied map[string]interface{}s as-is. For pointers to structs, the object will have its fields added to the map using field names as keys (unless the dbparam tag is set) and the field value as the map value.

An error is returned if one of the arguments is not a map[string]interface{} pointer to a struct.

func ParamsFromTags Uses

func ParamsFromTags(sources ...interface{}) (map[string]interface{}, error)

ParamsFromTags takes one or more structs whose fields might have the dbparam tag set. Those fields that do have the tag set are added to the the returned map, where the tag value is used as the map key and the field value is used as the map value.

type ContextAwareDatabaseProvider Uses

type ContextAwareDatabaseProvider interface {
    DatabaseFromContext(context.Context) (*sql.DB, error)
}

Implemented by DatabaseProvider implementations that need to be given a context when establishing a database connection

type DatabaseProvider Uses

type DatabaseProvider interface {
    // Database returns a Go sql.DB object
    Database() (*sql.DB, error)
}

Implemented by an object able to create a sql.DB object to connect to an instance of an RDBMS. The implementation is expected to manage connection pooling and failover as required

type GraniticRdbmsClientManager Uses

type GraniticRdbmsClientManager struct {
    // Set to true if you are creating an instance of GraniticRdbmsClientManager manually
    DisableAutoInjection bool

    // Auto-injected if the QueryManager facility is enabled
    QueryManager dsquery.QueryManager

    Configuration *RdbmsClientManagerConfig

    // Injected by Granitic.
    FrameworkLogger logging.Logger

    SharedLog logging.Logger
    // contains filtered or unexported fields
}

Granitic's default implementation of RdbmsClientManager. An instance of this will be created when you enable the

RdbmsAccess access facility and will be injected into any component that needs database access - see the package documentation for facilty/rdbms for more details.

func (*GraniticRdbmsClientManager) BlockAccess Uses

func (cm *GraniticRdbmsClientManager) BlockAccess() (bool, error)

BlockAccess returns true if BlockUntilConnected is set to true and a connection to the underlying RDBMS has not yet been established.

func (*GraniticRdbmsClientManager) Client Uses

func (cm *GraniticRdbmsClientManager) Client() (*RdbmsClient, error)

See RdbmsClientManager.Client

func (*GraniticRdbmsClientManager) ClientFromContext Uses

func (cm *GraniticRdbmsClientManager) ClientFromContext(ctx context.Context) (*RdbmsClient, error)

See RdbmsClientManager.ClientFromContext

func (*GraniticRdbmsClientManager) PrepareToStop Uses

func (cm *GraniticRdbmsClientManager) PrepareToStop()

PrepareToStop transitions component to stopping state, prevent new Client objects from being created.

func (*GraniticRdbmsClientManager) ReadyToStop Uses

func (cm *GraniticRdbmsClientManager) ReadyToStop() (bool, error)

ReadyToStop always returns true, nil

func (*GraniticRdbmsClientManager) StartComponent Uses

func (cm *GraniticRdbmsClientManager) StartComponent() error

StartComponent selects a DatabaseProvider to use

func (*GraniticRdbmsClientManager) Stop Uses

func (cm *GraniticRdbmsClientManager) Stop() error

Stop always returns nil

type InsertWithReturnedId Uses

type InsertWithReturnedId func(string, *RdbmsClient, *int64) error

A function able execute an insert statement and return an RDBMS generated ID as an int64. If your implementation requires access to the context, it is available on the *RdbmsClient

type NonStandardInsertProvider Uses

type NonStandardInsertProvider interface {
    InsertIDFunc() InsertWithReturnedId
}

Optional interface for DatabaseProvider implementations when the prepared statement->exec->insert pattern does not yield the last inserted ID as part of its result.

type ProviderComponentReceiver Uses

type ProviderComponentReceiver interface {
    RegisterProvider(p *ioc.Component)
}

Implemented by components that are interested in having visibility of all DatabaseProvider implementations available to an application.

type RdbmsClient Uses

type RdbmsClient struct {
    FrameworkLogger logging.Logger
    // contains filtered or unexported fields
}

The interface application code should use to execute SQL against a database. See the package overview for the rdbms package for usage.

RdbmsClient is stateful and MUST NOT be shared across goroutines

func (*RdbmsClient) BuildQueryFromQIdParams Uses

func (rc *RdbmsClient) BuildQueryFromQIdParams(qid string, p ...interface{}) (string, error)

BuildQueryFromQIdParams returns a populated SQL query that can be manually executed later.

func (*RdbmsClient) CommitTransaction Uses

func (rc *RdbmsClient) CommitTransaction() error

CommitTransaction commits the open transaction - does nothing if no transaction is open.

func (*RdbmsClient) DeleteQIdParam Uses

func (rc *RdbmsClient) DeleteQIdParam(qid string, name string, value interface{}) (sql.Result, error)

DeleteQIdParam executes the supplied query with the expectation that it is a 'DELETE' query.

func (*RdbmsClient) DeleteQIdParams Uses

func (rc *RdbmsClient) DeleteQIdParams(qid string, params ...interface{}) (sql.Result, error)

DeleteQIdParams executes the supplied query with the expectation that it is a 'DELETE' query.

func (*RdbmsClient) Exec Uses

func (rc *RdbmsClient) Exec(query string, args ...interface{}) (sql.Result, error)

Exec is a pass-through to its sql.DB equivalent (or sql.Tx equivalent is a transaction is open)

func (*RdbmsClient) ExistingIdOrInsertParams Uses

func (rc *RdbmsClient) ExistingIdOrInsertParams(checkQueryId, insertQueryId string, idTarget *int64, p ...interface{}) error

ExistingIdOrInsertParams finds the ID of record or if the record does not exist, inserts a new record and retrieves the newly assigned ID

func (*RdbmsClient) FindFragment Uses

func (rc *RdbmsClient) FindFragment(qid string) (string, error)

FindFragment returns a partial query from the underlying QueryManager. Fragments are no different that ordinary template queries, except they are not expected to contain any variable placeholders.

func (*RdbmsClient) InsertCaptureQIdParams Uses

func (rc *RdbmsClient) InsertCaptureQIdParams(qid string, target *int64, params ...interface{}) error

InsertCaptureQIdParams executes the supplied query with the expectation that it is an 'INSERT' query and captures the new row's server generated ID in the target int64

func (*RdbmsClient) InsertQIdParams Uses

func (rc *RdbmsClient) InsertQIdParams(qid string, params ...interface{}) (sql.Result, error)

InsertQIdParams executes the supplied query with the expectation that it is an 'INSERT' query.

func (*RdbmsClient) Query Uses

func (rc *RdbmsClient) Query(query string, args ...interface{}) (*sql.Rows, error)

Query is a pass-through to its sql.DB equivalent (or sql.Tx equivalent is a transaction is open)

func (*RdbmsClient) QueryRow Uses

func (rc *RdbmsClient) QueryRow(query string, args ...interface{}) *sql.Row

QueryRow is a pass-through to its sql.DB equivalent (or sql.Tx equivalent is a transaction is open)

func (*RdbmsClient) RegisterTempQuery Uses

func (rc *RdbmsClient) RegisterTempQuery(qid string, query string)

RegisterTempQuery stores the supplied query in the RdbmsClient so that it can be used with methods that expect a QID. Note that the query is NOT stored in the underlying QueryManager.

func (*RdbmsClient) Rollback Uses

func (rc *RdbmsClient) Rollback()

Rollback rolls the open transaction back - does nothing if no transaction is open.

func (*RdbmsClient) SelectBindQId Uses

func (rc *RdbmsClient) SelectBindQId(qid string, template interface{}) ([]interface{}, error)

SelectBindQId executes the supplied query with the expectation that it is a 'SELECT' query. Results of the query are returned in a slice of the same type as the supplied template struct.

func (*RdbmsClient) SelectBindQIdParam Uses

func (rc *RdbmsClient) SelectBindQIdParam(qid string, name string, value interface{}, template interface{}) ([]interface{}, error)

SelectBindQIdParam executes the supplied query with the expectation that it is a 'SELECT' query. Results of the query are returned in a slice of the same type as the supplied template struct.

func (*RdbmsClient) SelectBindQIdParams Uses

func (rc *RdbmsClient) SelectBindQIdParams(qid string, template interface{}, params ...interface{}) ([]interface{}, error)

SelectBindQIdParams executes the supplied query with the expectation that it is a 'SELECT' query. Results of the query are returned in a slice of the same type as the supplied template struct.

func (*RdbmsClient) SelectBindSingleQId Uses

func (rc *RdbmsClient) SelectBindSingleQId(qid string, target interface{}) (bool, error)

SelectBindSingleQId executes the supplied query with the expectation that it is a 'SELECT' query that returns 0 or 1 rows. Results of the query are bound into the target struct. Returns false if no rows were found.

func (*RdbmsClient) SelectBindSingleQIdParam Uses

func (rc *RdbmsClient) SelectBindSingleQIdParam(qid string, name string, value interface{}, target interface{}) (bool, error)

SelectBindSingleQIdParam executes the supplied query with the expectation that it is a 'SELECT' query that returns 0 or 1 rows. Results of the query are bound into the target struct. Returns false if no rows were found.

func (*RdbmsClient) SelectBindSingleQIdParams Uses

func (rc *RdbmsClient) SelectBindSingleQIdParams(qid string, target interface{}, params ...interface{}) (bool, error)

SelectBindSingleQIdParams executes the supplied query with the expectation that it is a 'SELECT' query that returns 0 or 1 rows. Results of the query are bound into the target struct. Returns false if no rows were found.

func (*RdbmsClient) SelectQId Uses

func (rc *RdbmsClient) SelectQId(qid string) (*sql.Rows, error)

SelectQId executes the supplied query with the expectation that it is a 'SELECT' query.

func (*RdbmsClient) SelectQIdParam Uses

func (rc *RdbmsClient) SelectQIdParam(qid string, name string, value interface{}) (*sql.Rows, error)

SelectQIdParam executes the supplied query with the expectation that it is a 'SELECT' query.

func (*RdbmsClient) SelectQIdParams Uses

func (rc *RdbmsClient) SelectQIdParams(qid string, params ...interface{}) (*sql.Rows, error)

SelectQIdParams executes the supplied query with the expectation that it is a 'SELECT' query.

func (*RdbmsClient) StartTransaction Uses

func (rc *RdbmsClient) StartTransaction() error

StartTransaction opens a transaction on the underlying sql.DB object and re-maps all calls to non-transactional methods to their transactional equivalents.

func (*RdbmsClient) StartTransactionWithOptions Uses

func (rc *RdbmsClient) StartTransactionWithOptions(opts *sql.TxOptions) error

StartTransactionWithOptions opens a transaction on the underlying sql.DB object and re-maps all calls to non-transactional methods to their transactional equivalents.

func (*RdbmsClient) UpdateQIdParam Uses

func (rc *RdbmsClient) UpdateQIdParam(qid string, name string, value interface{}) (sql.Result, error)

UpdateQIdParam executes the supplied query with the expectation that it is an 'UPDATE' query.

func (*RdbmsClient) UpdateQIdParams Uses

func (rc *RdbmsClient) UpdateQIdParams(qid string, params ...interface{}) (sql.Result, error)

UpdateQIdParams executes the supplied query with the expectation that it is an 'UPDATE' query.

type RdbmsClientManager Uses

type RdbmsClientManager interface {
    // Client returns an RdbmsClient that is ready to use.
    Client() (*RdbmsClient, error)

    // ClientFromContext returns an RdbmsClient that is ready to use. Providing a context allows the underlying DatabaseProvider
    // to modify the connection to the RDBMS.
    ClientFromContext(ctx context.Context) (*RdbmsClient, error)
}

Implemented by a component that can create RdbmsClient objects that application code will use to execute SQL statements.

type RdbmsClientManagerConfig Uses

type RdbmsClientManagerConfig struct {
    Provider DatabaseProvider

    // The names of fields on a component that should have a reference to this component's associated RdbmsClientManager
    // automatically injected into them.
    InjectFieldNames []string

    BlockUntilConnected bool

    // A name that will be shared by any instances of RdbmsClient created by this manager - this is used for logging purposes
    ClientName string

    // Name that will be given to the RdbmsClientManager component that will be created. If not set, it will be set the value of ClientName + "Manager"
    ManagerName string
}

RdbmsClientManagerConfig is used to organise the various components that interact to manage a database connection when your application needs to connect to more that one database simultaneously.

type RowBinder Uses

type RowBinder struct {
}

Used to extract the data from the results of a SQL query and inject the data into a target data structure.

func (*RowBinder) BindRow Uses

func (rb *RowBinder) BindRow(r *sql.Rows, t interface{}) (bool, error)

BindRow takes results from a SQL query that has return zero rows or one row and maps the data into the target interface, which must be a pointer to a struct.

If the query results contain zero rows, BindRow returns false, nil.

If the query results contain one row, it is populated. See the GoDoc for BindRows for more detail.

func (*RowBinder) BindRows Uses

func (rb *RowBinder) BindRows(r *sql.Rows, t interface{}) ([]interface{}, error)

BindRow takes results from a SQL query that has return zero rows or one row and maps the data into the instances of the target interface, which must be a pointer to a struct.

If the query results contain zero rows, BindRow returns an empty slice of the target type

If the query results contain one or more rows, an instance of the target type is created for each row. Each column in a row is mapped to a field in the target type by either:

a) Finding a field whose name exactly matches the column name or alias.

b) Finding a field with the 'column' struct tag with a value that exactly matches the column name or alias.

A target field may be a bool, any native int/uint type, any native float type, a string or any of the Granitic nilable types.

Package rdbms imports 11 packages (graph) and is imported by 1 packages. Updated 2018-08-04. Refresh now. Tools for package owners.