pqutil

package module
v0.0.0-...-6f26d34 Latest Latest
Warning

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

 Go to latest Published: May 10, 2014 License: MIT Imports: 10 Imported by: 0

README

What is pql?

pql is a go (golang) package to compliment the standard database/sql package when working with PostgreSQL.

Key Features:

  • Value types for handling all pg types from simple integer columns to complex composite/array constructions.
  • Keep your schema defined in one place (in your database) and automatically "model" your rows as RecordValues
  • Thin wrapper types around the standard database/sql types. (with some convinience functions for working with RecordValues
  • A simple ORMish API for querying the db and returning RecordValues

What is isn't

  • pql is not a driver for postgres. Use lib/pq for that.
  • pql is not a full-on ORM-style library. It provides helpers for very common cases (The Query builder for SELECT and INSERT, UPDATE, DELETE methods for working with RecordValues). It is expected that you ultize PostgreSQL itself for building complex queries as Views, Functions etc, then use pql to access those relations with simple queries. Let postgres and go each do what they are best at.

Why use this?

The main reason this library was created was a need to work with Arrays, HStore and Composite types. With just the standard database/sql package you quickly find you need help when you need to read something like:

	SELECT ARRAY[
			ROW(1, '"key" => "value1"'::hstore, 'jeff'),
			ROW(2, '"key" => "value2"'::hstore, 'bob')
		] as complexthing;

With pql you can define a Value to handle such a column like:

	MyComplexValue := Array(Record(
		Col("id",   BigInt),
		Col("tags", HStore),
		Col("name", Text),
	)

Then use either the standard database/sql's, or pql's DB/Rows to Scan into the value:

	...
	v := MyComplexValue(nil) // create a NULL MyComplexValue
	rows.Scan(v)             // use Scan to read data into it

	for _, el := range v.Values() {
		fmt.Println("name", el.Get("name"))  // prints jeff/bob
		fmt.Println("tags", el.Map())        // prints out representation of map of tags
	}

Since most of the time you are probably not building complex anonymous types, but using types defined within relations you can skip the step of defining Values upfront and let pql do it for you. See "Working with Query & RecordValues" example below.

Documentation

See godoc

Examples

Using Values for base types

(TODO)

Using Values for array types

(TODO)

Using Values for complex composite types

(TODO)

Working with Query & RecordValues

RecordValue is an interface type for working with a database row (record). Think of it like a lightweight model. Various *DB and other methods return (or work with) RecordValues.

For a simple example we'll assume we have a database with the following setup:

    CREATE TABLE person (
		name text,
		age integer
    );
	INSERT INTO person (name,age) VALUES ('bob', 25);
	INSERT INTO person (name,age) VALUES ('jeff', 35);
	INSERT INTO person (name,age) VALUES ('alice', 26);

Now if we wanted a little go cmd to print out all the names of people over 25. Our minimal (sans error checking) code might look something like:

	package main

	import(
		_ "github.com/lib/pq"
		"bitbucket.org/pkg/pql"
		"fmt"
	)

	func main(){
		db, _ := pql.Open("")
		rs, _ := db.From("person").Where("age > $1", 25).Fetch()
		for _, r := range rs {
			fmt.Println("%s is over 25", r.Get("name"))
		}
	}

Development

Testing

  • You will need to create a db called pqgotest
  • You will need to install the HStore extension (part of the contrib package)
  • run CREATE EXTENSION hstore; on your db

To run tests use the ENV variables like PGHOST. (see lib/pq for options).

cd to the package directory and run:

	PGHOST=/var/run/postgresql go test

Authors

Chris Farmiloe

License

MIT, See LICENSE.md file

Documentation

Overview

utility package for working with PostgreSQL arrays and composite types.

Index

Constants

This section is empty.

Variables

View Source 
var (
	Decimal   = Numeric
	Int       = Integer
	Int2      = SmallInt
	Int4      = Integer
	Int8      = BigInt
	Serial    = Integer
	BigSerial = BigInt
)

Value aliases

Functions

func Col 

func Col(name string, k Valstructor) *col

allows you name fields before passing them to the Record constructor 

myRecordKind := Record( Col("name", Text) )
v := myRecordKind()

Then any values created with names can be fetched by name from the value: 

name := v.Get("name").String()

Types

type DB 

type DB struct {
	*sql.DB
	// contains filtered or unexported fields
}

wrapper type around sql.DB adds methods for getting meta infomation from the db (via Relations), automatically building Value types and convience functions for dealing with RecordValues (via *Rowss)

func Open 

func Open(connstr string) (*DB, error)

Analog of sql.Open that returns a *DB requires a "postgres" driver (lib/pq) is registered

func (*DB) Begin 

func (db *DB) Begin() (*Tx, error)

same as sql.DB.Begin() only returns our *Tx not *sql.Tx

func (*DB) Delete 

func (db *DB) Delete(vs ...RecordValue) error

DELETE the given RecordValue(s) into the db runs multiple INSERTs within a transaction

func (*DB) From 

func (db *DB) From(name string) *Query

Create a Query for a named relation any errors are defered until an actual query is performed

func (*DB) Insert 

func (db *DB) Insert(vs ...RecordValue) error

INSERT the given RecordValue(s) into the db runs multiple INSERTs within a transaction

func (*DB) New 

func (db *DB) New(name string, args interface{}) (RecordValue, error)

Create a new RecordValue for the named relation

func (*DB) Query 

func (db *DB) Query(q string, vals ...interface{}) (*Rows, error)

like sql.DB.Query only returns a *Rows rather than sql.Rows

func (*DB) Relation 

func (db *DB) Relation(name string) (*Relation, error)

Get Relation info by name

func (*DB) Relations 

func (db *DB) Relations() (rels map[string]*Relation, err error)

Return all the Relations from the database

func (*DB) Update 

func (db *DB) Update(vs ...RecordValue) error

UPDATE the given RecordValue(s) into the db runs multiple INSERTs within a transaction

func (*DB) Upsert 

func (db *DB) Upsert(vs ...RecordValue) error

INSERT OR UPDATE the given RecordValue(s) into the db runs multiple INSERTs within a transaction

type IterValue 

type IterValue interface {
	// IterValue also fulfils Value
	Value
	// Fetch all sub-values as a slice
	Values() []Value
	// Fetch a sub-value by slice index
	ValueAt(int) Value
	// Add a value to the list of sub-values
	Append(interface{}) error
}

values that have a slice of sub-values will impliment this interface

type MapValue 

type MapValue interface {
	// MapValue but also fulfil Value
	Value
	// fetch all sub-values as a map
	Map() map[string]Value
	// Fetch a sub-value by name
	ValueBy(name string) Value
	// Equivilent to calling ValueBy(name).Val()
	Get(name string) interface{}
	// Equivilent to calling ValueBy(name).Scan(src)
	Set(name string, src interface{}) error
}

values that have named sub-values (like Record) this interface will be implimented

type Query 

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

the Query type is used to build simple/common queries most methods return a new Query so they can be chained with any errors being defered until a call that causes a db.Query

func (*Query) And 

func (q *Query) And(w string, params ...interface{}) *Query

syntantic sugar alias for Where

func (*Query) ArrayAgg 

func (q *Query) ArrayAgg(name string) (Value, error)

perform a "SELECT array_agg(x)" query. Returns an array value

func (*Query) Avg 

func (q *Query) Avg(name string) (Value, error)

perform a "SELECT avg(x)" query

func (*Query) Count 

func (q *Query) Count() (int64, error)

perform a "SELECT count(*)" query for this Query

func (*Query) Fetch 

func (q *Query) Fetch() ([]RecordValue, error)

perform a SELECT for the current query and return a slice of RecordValues

func (*Query) FetchOne 

func (q *Query) FetchOne() (RecordValue, error)

perform a SELECT and return a single RecordValue for this query will return nil if no rows where returned

func (*Query) For 

func (q *Query) For(v RecordValue) *Query

Return a new Query with

func (*Query) Get 

func (q *Query) Get(pk interface{}) (RecordValue, error)

create a new Query with a WHERE filter for the relation's primary key and the call FetchOne

func (*Query) Limit 

func (q *Query) Limit(n int) *Query

Return a new Query with a LIMIT set

func (*Query) Max 

func (q *Query) Max(name string) (Value, error)

perform a "SELECT max(x)" query

func (*Query) Min 

func (q *Query) Min(name string) (Value, error)

perform a "SELECT avg(x)" query

func (*Query) Offset 

func (q *Query) Offset(n int) *Query

Return a new Query with an OFFSET set

func (*Query) Sum 

func (q *Query) Sum(name string) (Value, error)

perform a "SELECT sum(x)" query

func (*Query) Where 

func (q *Query) Where(w string, params ...interface{}) *Query

Return a new Query based on this query with an additional (WHERE) filter.

type RecordValue 

type RecordValue interface {
	IterValue
	// fetch all sub-values as a map
	Map() map[string]Value
	// Fetch a sub-value by name
	ValueBy(name string) Value
	// Equivilent to calling ValueBy(name).Val()
	Get(name string) interface{}
	// Equivilent to calling ValueBy(name).Scan(src)
	Set(name string, src interface{}) error
	// Return the relation (if any) that this Record belongs to
	Relation() *Relation
	// Set the parent relation for this RecordValue
	SetRelation(*Relation)
}

RecordValuess are both MapValues and IterValues

type Relation 

type Relation struct {
	Name string
	// contains filtered or unexported fields
}

Relation holds column and reference info about a relation. Usually inferred from the database. See Relation methods on DB

func (*Relation) Cols 

func (r *Relation) Cols() []*col

return list of column data in the order postgresql expects them

func (*Relation) New 

func (r *Relation) New(data interface{}) (RecordValue, error)

return a new RecordValue that represents a row from this relation

type Rows 

type Rows struct {
	*sql.Rows
}

wrapper type around sql.Rows adds the ScanRecord method to make it easier to Scan Row Values

func (*Rows) ScanRecord 

func (rs *Rows) ScanRecord(v RecordValue) error

Similar to sql.Rows#Scan but scans all values into a RecordValue

type Tx 

type Tx struct {
	*sql.Tx
	// contains filtered or unexported fields
}

wrapper type around sql.Tx Adds methods for INSERTing, UPDATEing and DELETEing RecordValues

func (*Tx) Delete 

func (tx *Tx) Delete(vs ...RecordValue) error

DELETE RecordValue(s)

func (*Tx) From 

func (tx *Tx) From(name string) *Query

Create a Query for a named relation any errors are defered until an actual query is performed

func (*Tx) Insert 

func (tx *Tx) Insert(vs ...RecordValue) error

INSERT RecordValue(s)

func (*Tx) Query 

func (tx *Tx) Query(q string, vals ...interface{}) (*Rows, error)

like sql.Tx.Query only returns a *Rows rather than *sql.Rows

func (*Tx) Relations 

func (tx *Tx) Relations() (rels map[string]*Relation, err error)

func (*Tx) Update 

func (tx *Tx) Update(vs ...RecordValue) error

UPDATE RecordValue(s)

func (*Tx) Upsert 

func (tx *Tx) Upsert(vs ...RecordValue) (err error)

UPDATE or INSERT RecordValue(s)

type Valstructor 

type Valstructor func(data interface{}) (Value, error)

A `Valstructor` creates and initializes a new `Value`

func Array 

func Array(el Valstructor) Valstructor

func Char 

func Char(n int) Valstructor

func Enum 

func Enum(labels ...string) Valstructor

Text field with limited values

func Numeric 

func Numeric(prec int, scale int) Valstructor

stored as string currently TODO: use some Value of arbitary precision for this

func Record 

func Record(cols ...*col) Valstructor

same as Row, but takes a list of Col's as arguments that allow you to name the fields

func Row 

func Row(ks ...Valstructor) Valstructor

a generic record or composite type constructor

func VarChar 

func VarChar(n int) Valstructor

type Value 

type Value interface {
	// Return wether this Value is currently NULL
	IsNull() bool
	// Return a representation of this Value as a string
	String() string

	// Return the underlying data as an interface
	Val() interface{}
	// Values are Valuable
	driver.Valuer
	// Values are Scannable
	sql.Scanner
	// contains filtered or unexported methods
}

Value is the interface for all value kinds this interface along with IterValue and MapValue will allow you to access any combination of types

func BigInt 

func BigInt(data interface{}) (Value, error)

func Bool 

func Bool(data interface{}) (Value, error)

func Bytea 

func Bytea(data interface{}) (Value, error)

func Double 

func Double(data interface{}) (Value, error)

func HStore 

func HStore(data interface{}) (Value, error)

func Integer 

func Integer(data interface{}) (Value, error)

func Real 

func Real(data interface{}) (Value, error)

func SmallInt 

func SmallInt(data interface{}) (Value, error)

func Text 

func Text(data interface{}) (Value, error)

func Timestamp 

func Timestamp(data interface{}) (Value, error)

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.