rdbms

package
v2.2.2 Latest Latest
Warning

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

 Go to latest Published: Sep 11, 2020 License: Apache-2.0 Imports: 11 Imported by: 0

Documentation

Overview

Package rdbms 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 https://granitic.io/ref/relational-databases

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. 

implements https://granitic.io/ref/facilities for more details.

Components and types

Enabling these facilities creates the QueryManager and ClientManager components. Your application must provide a DatabaseProvider ( implements 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.

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

ClientManager  A component able to create ManagedClient 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 ClientManager component.

QueryManager

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

ClientManager

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 ManagedClient. ManagedClient 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 ClientManager.

Auto-injection of an ClientManager

Any component that needs an ManagedClient should have a field: 

DbClientManager rdbms.ClientManager

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 ManagedClient in a manner similar to: 

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

ManagedClient

Application code executes SQL (either directly or via a templated query) and interacts with transactions via an instance of ManagedClient. Refer to the GoDoc for ManagedClient for information on the methods available, but the general pattern for the methods available on ManagedClient 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

ManagedClient provides a mechanism for automatically copying result data into structs or slices of structs. If the ManagedClient 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 committed.

Direct access to Go DB methods

ManagedClient 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 (
	// DBParamTag is 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 Client, target *int64) error

DefaultInsertWithReturnedID is 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 Client 

type Client interface {
	FindFragment(qid string) (string, error)
	BuildQueryFromQIDParams(qid string, p ...interface{}) (string, error)
	DeleteQIDParams(qid string, params ...interface{}) (sql.Result, error)
	DeleteQIDParam(qid string, name string, value interface{}) (sql.Result, error)
	RegisterTempQuery(qid string, query string)
	ExistingIDOrInsertParams(checkQueryID, insertQueryID string, idTarget *int64, p ...interface{}) error
	InsertQIDParams(qid string, params ...interface{}) (sql.Result, error)
	InsertCaptureQIDParams(qid string, target *int64, params ...interface{}) error
	SelectBindSingleQID(qid string, target interface{}) (bool, error)
	SelectBindSingleQIDParam(qid string, name string, value interface{}, target interface{}) (bool, error)
	SelectBindSingleQIDParams(qid string, target interface{}, params ...interface{}) (bool, error)
	SelectBindQID(qid string, template interface{}) ([]interface{}, error)
	SelectBindQIDParam(qid string, name string, value interface{}, template interface{}) ([]interface{}, error)
	SelectBindQIDParams(qid string, template interface{}, params ...interface{}) ([]interface{}, error)
	SelectQID(qid string) (*sql.Rows, error)
	SelectQIDParam(qid string, name string, value interface{}) (*sql.Rows, error)
	SelectQIDParams(qid string, params ...interface{}) (*sql.Rows, error)
	UpdateQIDParams(qid string, params ...interface{}) (sql.Result, error)
	UpdateQIDParam(qid string, name string, value interface{}) (sql.Result, error)
	StartTransaction() error
	StartTransactionWithOptions(opts *sql.TxOptions) error
	Rollback()
	CommitTransaction() error
	Exec(query string, args ...interface{}) (sql.Result, error)
	Query(query string, args ...interface{}) (*sql.Rows, error)
	QueryRow(query string, args ...interface{}) *sql.Row
}

Client provides access to methods for executing SQL queries and managing transactions

type ClientManager 

type ClientManager interface {
	// ManagedClient returns an ManagedClient that is ready to use.
	Client() (Client, error)

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

ClientManager is implemented by a component that can create ManagedClient objects that application code will use to execute SQL statements.

type ClientManagerConfig 

type ClientManagerConfig struct {
	Provider DatabaseProvider

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

	BlockUntilConnected bool

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

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

ClientManagerConfig 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 ContextAwareDatabaseProvider 

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

ContextAwareDatabaseProvider is 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)
}

DatabaseProvider is 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 *ClientManagerConfig

	// Injected by Granitic.
	FrameworkLogger logging.Logger

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

GraniticRdbmsClientManager is Granitic's default implementation of ClientManager. 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 - implements 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() (Client, error)

Client implements ClientManager.Client

func (*GraniticRdbmsClientManager) ClientFromContext 

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

ClientFromContext implements ClientManager.ClientFromContext

func (*GraniticRdbmsClientManager) PrepareToStop 

func (cm *GraniticRdbmsClientManager) PrepareToStop()

PrepareToStop transitions component to stopping state, prevent new ManagedClient 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, Client, *int64) error

InsertWithReturnedID is 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 *ManagedClient

type ManagedClient 

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

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

ManagedClient is stateful and MUST NOT be shared across goroutines

func (*ManagedClient) BuildQueryFromQIDParams 

func (rc *ManagedClient) BuildQueryFromQIDParams(qid string, p ...interface{}) (string, error)

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

func (*ManagedClient) CommitTransaction 

func (rc *ManagedClient) CommitTransaction() error

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

func (*ManagedClient) DeleteQIDParam 

func (rc *ManagedClient) 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 (*ManagedClient) DeleteQIDParams 

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

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

func (*ManagedClient) Exec 

func (rc *ManagedClient) 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 (*ManagedClient) ExistingIDOrInsertParams 

func (rc *ManagedClient) 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 (*ManagedClient) FindFragment 

func (rc *ManagedClient) 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 (*ManagedClient) InsertCaptureQIDParams 

func (rc *ManagedClient) 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 (*ManagedClient) InsertQIDParams 

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

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

func (*ManagedClient) Query 

func (rc *ManagedClient) 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 (*ManagedClient) QueryRow 

func (rc *ManagedClient) 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 (*ManagedClient) RegisterTempQuery 

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

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

func (*ManagedClient) Rollback 

func (rc *ManagedClient) Rollback()

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

func (*ManagedClient) SelectBindQID 

func (rc *ManagedClient) 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 (*ManagedClient) SelectBindQIDParam 

func (rc *ManagedClient) 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 (*ManagedClient) SelectBindQIDParams 

func (rc *ManagedClient) 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 (*ManagedClient) SelectBindSingleQID 

func (rc *ManagedClient) 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 (*ManagedClient) SelectBindSingleQIDParam 

func (rc *ManagedClient) 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 (*ManagedClient) SelectBindSingleQIDParams 

func (rc *ManagedClient) 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 (*ManagedClient) SelectQID 

func (rc *ManagedClient) SelectQID(qid string) (*sql.Rows, error)

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

func (*ManagedClient) SelectQIDParam 

func (rc *ManagedClient) 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 (*ManagedClient) SelectQIDParams 

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

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

func (*ManagedClient) StartTransaction 

func (rc *ManagedClient) 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 (*ManagedClient) StartTransactionWithOptions 

func (rc *ManagedClient) 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 (*ManagedClient) UpdateQIDParam 

func (rc *ManagedClient) 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 (*ManagedClient) UpdateQIDParams 

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

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

type NonStandardInsertProvider 

type NonStandardInsertProvider interface {
	InsertIDFunc() InsertWithReturnedID
}

NonStandardInsertProvider is an 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)
}

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

type RowBinder 

type RowBinder struct {
}

RowBinder is 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)

BindRows 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.

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.