Documentation

Overview

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

Constants

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

Variables

This section is empty.

Functions

func DefaultInsertWithReturnedId

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

    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

      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.

        Types

        type ContextAwareDatabaseProvider

        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

          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

            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

              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

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

                  See RdbmsClientManager.Client

                  func (*GraniticRdbmsClientManager) ClientFromContext

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

                    See RdbmsClientManager.ClientFromContext

                    func (*GraniticRdbmsClientManager) PrepareToStop

                    func (cm *GraniticRdbmsClientManager) PrepareToStop()

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

                      func (*GraniticRdbmsClientManager) ReadyToStop

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

                        ReadyToStop always returns true, nil

                        func (*GraniticRdbmsClientManager) StartComponent

                        func (cm *GraniticRdbmsClientManager) StartComponent() error

                          StartComponent selects a DatabaseProvider to use

                          func (*GraniticRdbmsClientManager) Stop

                          func (cm *GraniticRdbmsClientManager) Stop() error

                            Stop always returns nil

                            type InsertWithReturnedId

                            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

                              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

                                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

                                  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

                                    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

                                      func (rc *RdbmsClient) CommitTransaction() error

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

                                        func (*RdbmsClient) DeleteQIdParam

                                        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

                                          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

                                            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

                                              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

                                                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

                                                  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

                                                    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

                                                      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

                                                        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

                                                          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

                                                            func (rc *RdbmsClient) Rollback()

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

                                                              func (*RdbmsClient) SelectBindQId

                                                              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

                                                                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

                                                                  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

                                                                    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

                                                                      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

                                                                        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

                                                                          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

                                                                            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

                                                                              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

                                                                                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

                                                                                  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

                                                                                    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

                                                                                      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

                                                                                        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

                                                                                          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

                                                                                            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

                                                                                              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

                                                                                                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.