pgq

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

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

 Go to latest Published: Jun 2, 2022 License: Apache-2.0 Imports: 15 Imported by: 0

README

pgq - PostgreSql helpers

  • Generating PostgreSQL queries using templates
  • A simple wrapper around sql queries from a package github.com/jackc/pgx
  • Managing nested transactions

Build Status Test Status

Example of usage:

CREATE TABLE table1
(
    id bigserial NOT NULL,
    c_int bigint,
    c_double double precision,
    c_bool boolean,
    c_date date,
    c_time time without time zone,    
    c_timestamp timestamp without time zone,
    c_timestamp_zone timestamp with time zone,
    c_text text,
    c_bytes bytea,
    CONSTRAINT table_pkey PRIMARY KEY (id)
);
ALTER TABLE table1 ADD CONSTRAINT table_pkey PRIMARY KEY (id);

package main

import (
    "context"
    "fmt"
    "io/ioutil"
    "time"

    "github.com/jackc/pgx/v4"
    "github.com/n-r-w/pgq"
)

func main() {
    // ******************************** Sql Binder ********************************
    templateSql := `
    DELETE FROM public.table1;
    INSERT INTO public.table1(
    c_int, c_double, c_bool, c_date, c_time, c_timestamp, c_timestamp_zone, c_text, c_bytes)
    VALUES (:c_int, :c_double, :c_bool, :c_date, :c_time, :c_timestamp, :c_timestamp_zone, :c_text, :c_bytes);`

    binds := map[string]interface{}{
        "c_int":            123,
        "c_double":         123.456,
        "c_bool":           true,
        "c_date":           time.Now(),
        "c_time":           time.Duration(time.Hour*13 + time.Minute*41 + time.Second*2), // 13:41:02
        "c_timestamp":      time.Now(),
        "c_timestamp_zone": time.Now(),
        "c_text":           "test text",
        "c_bytes":          []byte("qwerty"),
    }

    binder := pgq.NewSqlBinder(templateSql, "")
    for variable, value := range binds {
        binder.Bind(variable, value)
    }

    sql, err := binder.Sql()
    if err != nil {
        panic(err)
    }
    fmt.Println(sql)
    /*
               INSERT INTO public.table1(
               c_int, c_double, c_bool, c_date, c_time, c_timestamp, c_timestamp_zone, c_text, c_bytes)
               VALUES (123, 123.456, TRUE, '2022-06-01 13:14:52.439 +0300', '13:41:2', '2022-06-01 13:14:52.439 +0300',
                          '2022-06-01 13:14:52.439 +0300', E'test text', E'\\x717765727479')
    */

    // ******************************** Sql Query Wrapper ********************************
    conn, err := pgx.Connect(context.Background(),
        "host=localhost user=postgres password=1 port=5433 dbname=demo")
    if err != nil {
        panic(err)
    }
    defer conn.Close(context.Background())

    query := pgq.NewQuery(conn, context.Background())

    err = query.ExecBind(templateSql, binds, "")
    if err != nil {
        panic(err)
    }

    err = query.Select("select * from table1")
    if err != nil {
        panic(err)
    }

    for query.Next() {
        fmt.Println("\n==========================================================================================================")
        fmt.Printf("int: %v, double: %v, bool: %v, date: %v, time: %v,  timestamp: %v, timestamp zone: %v, text: %v, bytes: %v",
            query.Value("c_int"), query.Value("c_double"), query.Value("c_bool"),
            query.Value("c_date"), query.Value("c_time"), query.Value("c_timestamp"),
            query.Value("c_timestamp_zone"), query.Value("c_text"), query.Value("c_bytes"))
        fmt.Println("\n----------------------------------------------------------------------------------------------------------")
        fmt.Printf("int: %v, double: %v, bool: %v, date: %v, time: %v, duration: %v,  timestamp: %v, timestamp zone: %v, text: %v, bytes: %v, bytes as string: %v",
            query.Int("c_int"), query.Float32("c_double"), query.Bool("c_bool"),
            query.Time("c_date"), query.Time("c_time"), query.Duration("c_time"), query.Time("c_timestamp"),
            query.Time("c_timestamp_zone"), query.String("c_text"), query.Bytes("c_bytes"), query.String("c_bytes"))
    }
    /*
        ==========================================================================================================
        int: 123, double: 123.456, bool: true, date: 2022-06-01 00:00:00 +0000 UTC, time: 49262000000,
            timestamp: 2022-06-01 13:14:52.439 +0000 UTC, timestamp zone: 2022-06-01 13:14:52.439 +0300 MSK,
            text: test text, bytes: [113 119 101 114 116 121]
        ----------------------------------------------------------------------------------------------------------
        int: 123, double: 123.456, bool: true, date: 2022-06-01 00:00:00 +0000 UTC, time: 0001-01-01 13:41:02 +0000 UTC,
            duration: 13h41m2s,  timestamp: 2022-06-01 13:14:52.439 +0000 UTC, timestamp zone: 2022-06-01 13:14:52.439 +0300 MSK,
            text: test text, bytes: [113 119 101 114 116 121], bytes as string: qwerty
    */

    // Large Objects
    fmt.Println("\n==========================================================================================================")
    fmt.Println("Large object processing...")

    data, err := ioutil.ReadFile("largefile.MP4")
    if err != nil {
        panic(err)
    }

    // save new
    oid, err := query.SaveLargeObject(nil, 0, data)
    if err != nil {
        panic(err)
    }
    fmt.Println("Large object created...")
    // load
    testData, err := query.LoadLargeObject(nil, oid)
    if err != nil {
        panic(err)
    }
    fmt.Println("Large object loaded...")
    // update
    oid, err = query.SaveLargeObject(nil, oid, data)
    if err != nil {
        panic(err)
    }
    fmt.Println("Large object updated...")

    fmt.Println("file size:", len(testData))
    // remove
    err = query.RemoveLargeObject(nil, oid)
    if err != nil {
        panic(err)
    }
    fmt.Println("Large object removed...")
    fmt.Println("Large object processing done")

    query.Exec("delete from table1")

    // ============== Nested transaction management
    nested := pgq.NewTxNested(conn, context.Background())
    _, err = nested.Begin()
    if err != nil {
        panic(err)
    }

    err = query.ExecBind(templateSql, binds, "")
    if err != nil {
        panic(err)
    }

    nested.Begin() // no new transactions, just increments the counter

    err = query.ExecBind(templateSql, binds, "")
    if err != nil {
        panic(err)
    }

    nested.Commit() // there is no commit, just decreases the counter
    // nested.Rollback() will roll back all changes

    err = nested.Commit() // commit is executed here
    if err != nil {
        panic(err)
    }
}

Documentation

Overview

Package pgq - generating PostgreSql queries using a template

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func Bind 

func Bind(template string, variable string, value interface{}, key string) (string, error)

BindOne - replace the format bind in the Sql string :bind to the value of the value variable

Types

type Query 

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

Query - wrapper for pgx Query/Exec

func NewQuery 

func NewQuery(conn *pgx.Conn, context context.Context) *Query

NewQuery - create a Query based on pg.Conn

func NewQueryPool 

func NewQueryPool(pool *pgxpool.Pool, context context.Context) *Query

NewQueryPool - create a Query based on pgxpool.Conn

func (*Query) Bool 

func (q *Query) Bool(field string) bool

Int64 - field value by name, converted to int64 (only for Select and after a successful Next call)

func (*Query) Bytes 

func (q *Query) Bytes(field string) []byte

Bytes - field value by name, converted to []byte (only for Select and after a successful Next call)

func (*Query) Close 

func (q *Query) Close()

Close - close the selection. Use for Select in case we don't get to the end of Next

func (*Query) Connection 

func (t *Query) Connection() *pgx.Conn

Connection - active connection

func (*Query) Contains 

func (q *Query) Contains(field string) bool

Contains - does the specified field contain (Select only)

func (*Query) Context 

func (t *Query) Context() context.Context

Context - active context

func (*Query) Duration 

func (q *Query) Duration(field string) time.Duration

Duration - field value by name, converted to time.Duration (only for Select and after a successful Next call)

func (*Query) Exec 

func (q *Query) Exec(sql string) error

Exec - executing the insert, update, delete command

func (*Query) ExecBind 

func (q *Query) ExecBind(sqlTemplate string, values map[string]interface{}, key string) error

ExecBind - execution of the insert, update, delete command with the substitution of values in the template

func (*Query) ExecBindCon 

func (q *Query) ExecBindCon(sqlTemplate string, c dcon.ContainerReader, values map[string]dcon.PropertyID, key string) error

ExecBindCon - execution of the insert, update, delete command with the substitution of values in the template. For dcon.StrictContainer

func (*Query) FieldName 

func (q *Query) FieldName(index int) string

FieldName - field name by index

func (*Query) FieldType 

func (q *Query) FieldType(name string) uint32

FieldType - field type by name. Result: pgtype.BoolOID, ... etc

func (*Query) FieldTypeIndex 

func (q *Query) FieldTypeIndex(index int) uint32

FieldTypeIndex - field type by index. Result: pgtype.BoolOID, ... etc

func (*Query) FieldTypeName 

func (q *Query) FieldTypeName(name string) string

FieldType - field type by name. Result: type name

func (*Query) FieldTypeNameIndex 

func (q *Query) FieldTypeNameIndex(index int) string

FieldTypeIndex - field type by index. Result: type name

func (*Query) Fields 

func (q *Query) Fields() []pgproto3.FieldDescription

Fields - list of fields (Select only)

func (*Query) Float32 

func (q *Query) Float32(field string) float32

Float32 - field value by name, converted to float32 (only for Select and after a successful Next call)

func (*Query) Float64 

func (q *Query) Float64(field string) float64

Float64 - field value by name, converted to float64 (only for Select and after a successful Next call)

func (*Query) Int 

func (q *Query) Int(field string) int

Int - field value by name, converted to int64 (only for Select and after a successful Next call)

func (*Query) Int64 

func (q *Query) Int64(field string) int64

Int64 - field value by name, converted to int64 (only for Select and after a successful Next call)

func (*Query) IsCommand 

func (q *Query) IsCommand() bool

IsCommand - request type Insert, Delete, Update

func (*Query) IsSelect 

func (q *Query) IsSelect() bool

IsSelect - Select request type

func (*Query) LoadLargeObject 

func (q *Query) LoadLargeObject(transaction pgx.Tx, oid uint32) ([]byte, error)

LoadLargeObject - read Large Object from the database. If transaction == nil, then creates and closes the transaction automatically

func (*Query) Next 

func (q *Query) Next() bool

Next - get the next row (Select only)

func (*Query) Pool 

func (t *Query) Pool() *pgxpool.Pool

Pool - active connection pool

func (*Query) RemoveLargeObject 

func (q *Query) RemoveLargeObject(transaction pgx.Tx, oid uint32) error

RemoveLargeObject - remove Large Object from the database. If transaction == nil, then creates and closes the transaction automatically

func (*Query) RowsAffected 

func (q *Query) RowsAffected() int64

RowsAffected - the number of processed rows, for Select closes the query (Next will not work)

func (*Query) SaveLargeObject 

func (q *Query) SaveLargeObject(transaction pgx.Tx, oid uint32, data []byte) (uint32, error)

SaveLargeObject - write Large Object to the database. If oid == 0 then creates a new object. If transaction == nil, then creates and closes the transaction automatically Returns the id of the created or updated object

func (*Query) Select 

func (q *Query) Select(sql string) error

Select - executing the select command

func (*Query) SelectBind 

func (q *Query) SelectBind(sqlTemplate string, values map[string]interface{}, key string) error

SelectBind - executing the select command with the substitution of values in the template

func (*Query) SelectBindCon 

func (q *Query) SelectBindCon(sqlTemplate string, c dcon.ContainerReader, values map[string]dcon.PropertyID, key string) error

SelectBindCon - executing the select command with the substitution of values in the template. For dcon.StrictContainer

func (*Query) String 

func (q *Query) String(field string) string

String - field value by name, converted to string (only for Select and after a successful Next call)

func (*Query) Time 

func (q *Query) Time(field string) time.Time

Time - field value by name, converted to time.Time (only for Select and after a successful Next call)

func (*Query) Value 

func (q *Query) Value(field string) interface{}

Value - field value by name (only for Select and after a successful Next call)

func (*Query) ValueIndex 

func (q *Query) ValueIndex(fieldIndex int) interface{}

ValueIndex - field value by index (only for Select and after a successful Next call)

type SqlBinder 

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

SqlBinder - substitution of values in the Sql query template

func NewSqlBinder 

func NewSqlBinder(template string, key string) *SqlBinder

NewSqlBinder - create SqlBinder key is used to exclude repeated parsing of identical queries. The result of parsing is saved

func (*SqlBinder) Bind 

func (b *SqlBinder) Bind(variable string, value interface{}) error

Bind - replace the format bind in the Sql string :bind to the value of the value variable

func (*SqlBinder) BindCon 

func (b *SqlBinder) BindCon(c dcon.ContainerReader, values map[string]dcon.PropertyID) error

BindCon - Bind for data container

func (*SqlBinder) BindValues 

func (b *SqlBinder) BindValues(values map[string]interface{}) error

Bind - replace the format bind in the Sql string :bind to the value of the value variable

func (*SqlBinder) Clear 

func (b *SqlBinder) Clear()

Clear - resets everything except the template

func (*SqlBinder) IsVariableParsed 

func (b *SqlBinder) IsVariableParsed(v string) bool

IsVariableParsed - checks whether there is such a variable in the list of parsed

func (*SqlBinder) ParcedVariables 

func (b *SqlBinder) ParcedVariables() []string

ParcedVariables - list of variables in an SQL expression

func (*SqlBinder) Sql 

func (b *SqlBinder) Sql() (string, error)

Sql - get the result of substituting variables into a template

type SqlBinderParser 

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

SqlBinderParser - parser for identifying variables of the form :var in an sql query

func NewSqlBinderParser 

func NewSqlBinderParser(sqlTemplate string) *SqlBinderParser

NewSqlBinderParser - create SqlBinderParser

func (*SqlBinderParser) Calculate 

func (p *SqlBinderParser) Calculate(values map[string]string) (string, error)

Calculate - substitute values into variables and get the result

func (*SqlBinderParser) IsVariableParsed 

func (p *SqlBinderParser) IsVariableParsed(v string) bool

IsVariableParsed - is the variable parsed

func (*SqlBinderParser) ParcedVariables 

func (p *SqlBinderParser) ParcedVariables() []string

ParcedVariables - list of variables in an SQL expression

func (*SqlBinderParser) Parse 

func (p *SqlBinderParser) Parse() error

func (*SqlBinderParser) SqlTemplate 

func (p *SqlBinderParser) SqlTemplate() string

SqlTemplate - SQL template

type TxNested 

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

TxNested - working with nested transactions

func NewTxNested 

func NewTxNested(conn *pgx.Conn, ctx context.Context) *TxNested

NewTxNested - create a nested transaction management object based on pgx.Conn

func NewTxNestedPool 

func NewTxNestedPool(pool *pgxpool.Conn, ctx context.Context) *TxNested

NewTxNestedPool - create a nested transaction management object based on pgxpool.Conn

func (*TxNested) Begin 

func (t *TxNested) Begin() (pgx.Tx, error)

Begin - start a transaction. If the transaction has already started, it is returned

func (*TxNested) Commit 

func (t *TxNested) Commit() error

Commit - complete the transaction. If there are nested transactions, the operation is ignored

func (*TxNested) Connection 

func (t *TxNested) Connection() *pgx.Conn

Connection - active connection

func (*TxNested) Context 

func (t *TxNested) Context() context.Context

Context - active context

func (*TxNested) Level 

func (t *TxNested) Level() int

Level - nesting level. 0 - no transaction

func (*TxNested) Pool 

func (t *TxNested) Pool() *pgxpool.Conn

Pool - active connection pool

func (*TxNested) Rollback 

func (t *TxNested) Rollback() error

Rollback - roll back the transaction. The counter of nested transactions is reset, because the rollback cannot be partial

func (*TxNested) Tx 

func (t *TxNested) Tx() pgx.Tx

Tx - active transaction

Source Files

View all Source files

Directories

Path Synopsis

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.