sqlreflect

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

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

Go to latest
Published: Mar 21, 2017 License: MIT Imports: 7 Imported by: 0

README

SQL Reflect

THIS IS HIGHLY EXPERIMENTAL AND UNDER ACTIVE DEVELOPMENT

This library provides access to the structure of a SQL database, allowing developers to "reflect" on the database itself. Why would you need to do this?

  • Find out all of the tables available in a database
  • For a table, find out what columns it has
  • Find out about the relationships between tables
    • Discover foreign keys, and automatically relate tables
    • Discover primary keys

How It Works

This uses the information_schema database defined in the SQL standard. The library is developed against PostgreSQL, but it should work on any database that provides an approximately compliant implementation.

The information_schema tables (or views) provide information about the structure of a database, and are designed to enable this sort of reflection.

As the name of the library suggests, the code is designed to feel roughly similar to Go's own reflection package. However, there is not a one-to-one mapping between a concept like a table or column and Go's concepts like type and value.

Under the hood, it uses squirrel and structable 4.

Tested on:

  • Postgres 9.4, 9.6

Terminology

For the most part, this library follows the terminology of the SQL standard (e.g. catalog usually means database). Because I am most familiar with PostgreSQL, it is likely that some Postgres terminology slipped in here too.

Most of my understanding of the information schema comes from the PostgreSQL documentation.

TODO

  • Enable querying CHECK constraints to actually return the constraint. (See information_schame.check_constraints)

Documentation

Index

Constants

View Source
const (
	// YesNoYes is the SQL defined string for YES in a yes_or_no type.
	YesNoYes = "YES"
	// YesNoNo is the SQL-defined string for NO in a yes_or_no type.
	YesNoNo = "NO"
)

Variables

This section is empty.

Functions

This section is empty.

Types

type Column

type Column struct {
	TableCatalog   string `stbl:"table_catalog"`
	TableSchema    string `stbl:"table_schema"`
	TableNameField string `stbl:"table_name"`

	Name            string `stbl:"column_name"`
	OrdinalPosition int    `stbl:"ordinal_position"`
	// Default is a description of the default value, not the actual default value.
	Default                string `stbl:"column_default"`
	IsNullable             YesNo  `stbl:"is_nullable"` // yes_or_no
	DataType               string `stbl:"data_type"`
	CharacterMaximumLength int    `stbl:"character_maximum_length"`
	CharacterOctetLength   int    `stbl:"character_octet_length"`
	NumericPrecision       int    `stbl:"numeric_precision"`
	NumericPrecisionRadix  int    `stbl:"numeric_precision_radix"`
	NumericScale           int    `stbl:"numeric_scale"`
	DatetimePrecision      int    `stbl:"datetime_precision"`
	IntervalType           string `stbl:"interval_type"`
	IntervalPrecision      int    `stbl:"interval_precision"`
	CharacterSetCatalog    string `stbl:"character_set_catalog"`
	CharacterSetSchema     string `stbl:"character_set_schema"`
	CharacterSetName       string `stbl:"character_set_name"`
	CollationCatalog       string `stbl:"collation_catalog"`
	CollationSchema        string `stbl:"collation_schema"`
	CollationName          string `stbl:"collation_name"`
	DomainCatalog          string `stbl:"domain_catalog"`
	DomainSchema           string `stbl:"domain_schema"`
	DomainName             string `stbl:"domain_name"`
	UDTCatalog             string `stbl:"udt_catalog"`
	UDTSchema              string `stbl:"udt_schema"`
	UDTName                string `stbl:"udt_name"`
	ScopeCatalog           string `stbl:"scope_catalog"`
	ScopeSchema            string `stbl:"scope_schema"`
	ScopeName              string `stbl:"scope_name"`
	MaximumCardinality     int    `stbl:"maximum_cardinality"`
	IsSelfReferencing      YesNo  `stbl:"is_self_referencing"`
	IsIdentity             YesNo  `stbl:"is_identity"`
	IdentityGeneration     string `stbl:"identity_generation"`
	IdentityStart          string `stbl:"identity_start"`
	IdentityIncrement      string `stbl:"identity_increment"`
	IdentityMaximum        string `stbl:"identity_maximum"` // PG docs say string
	IdentityMinimum        string `stbl:"identity_minimum"`
	IdentityCycle          YesNo  `stbl:"identity_cycle"`
	IsGenerated            string `stbl:"is_generated"` // PG docs say string
	GenerationExpression   string `stbl:"generation_expression"`
	IsUpdatable            YesNo  `stbl:"is_updatable"`
	// contains filtered or unexported fields
}

Column represents a column (attribute) attached to a table. A column can exist on exactly one table.

func (*Column) Constraints

func (this *Column) Constraints() ([]*TableConstraint, error)

Constrains returns a record of constraints that reference this column.

For instance, if another table references this column as a foreign key, this will return information about that constraint. It will also include constraints on this table that reference this column (e.g. primary key).

func (*Column) DomainUsage

func (this *Column) DomainUsage() []*ColumnDomainUsage

func (*Column) Keys

func (this *Column) Keys() []*KeyColumnUsage

Keys the restrictions placed on this column due to its use as a key.

For example, if the column is a foreign key, this will return information about that foreign key relationship.

This returns for primary key, foreign key, and uniqueness constraints.

func (*Column) Options

func (this *Column) Options() ([]*ColumnOption, error)

Options returns a list of column options set for this column.

func (*Column) Privileges

func (this *Column) Privileges() []*ColumnPrivilege

func (Column) TableName

func (this Column) TableName() string

func (*Column) UDTUsage

func (this *Column) UDTUsage() []*ColumnUDTUsage

type ColumnDomainUsage

type ColumnDomainUsage struct{}

type ColumnOption

type ColumnOption struct {
	TableCatalog   string `stbl:"table_catalog"`
	TableSchema    string `stbl:"table_schema"`
	TableNameField string `stbl:"table_name"`

	ColumnName  string `sql:"column_name"`
	OptionName  string `sql:"option_name"`
	OptionValue string `sql:"option_value"`
	// contains filtered or unexported fields
}

func (ColumnOption) TableName

func (this ColumnOption) TableName() string

type ColumnPrivilege

type ColumnPrivilege struct {
	TableLocator
}

TODO

type ColumnUDTUsage

type ColumnUDTUsage struct{}

TODO

type ConstraintColumnUsage

type ConstraintColumnUsage struct {
	//TableLocator
	TableCatalog   string `stbl:"table_catalog"`
	TableSchema    string `stbl:"table_schema"`
	TableNameField string `stbl:"table_name"`

	ColumnName string `stbl:"column_name"`

	// ConstraintLocator
	ConstraintCatalog string `stbl:"constraint_catalog"`
	ConstraintSchema  string `stbl:"constraint_schema"`
	ConstraintName    string `stbl:"constraint_name"`
}

type ConstraintLocator

type ConstraintLocator struct {
	ConstraintCatalog string `stbl:"constraint_catalog"`
	ConstraintSchema  string `stbl:"constraint_schema"`
	ConstraintName    string `stbl:"constraint_name"`
}

type ConstraintType

type ConstraintType string

ConstraintType represents a type of constraint.

The defined types, expressed as constants, are CHECK, PRIMARY KEY, FOREIGN KEY, and UNIQUE.

const (
	ConstraintCheck      ConstraintType = "CHECK"
	ConstraintPrimaryKey ConstraintType = "PRIMARY KEY"
	ConstraintForeignKey ConstraintType = "FOREIGN KEY"
	ConstraintUnique     ConstraintType = "UNIQUE"
)

type DBOptions

type DBOptions struct {
	// Driver is the string name of the registered driver.
	Driver string

	// Querier is the runner that can execute database queries.
	// Often, this is just a *sql.DB. If the given queryer also implements
	// Preparer, this will use cached prepared statements instead of
	// directly executed queries. To disable the use of a query cache,
	// set DisableCache to true.
	//
	// FIXME: Right now, this is a squirrel.StmtCacheProxy
	Queryer Queryer

	// DisableCache disables using a query cache and prepared statements.
	DisableCache bool
}

DBOptions describes the database queries are to be executed against.

func NewDBOptions

func NewDBOptions(db *sql.DB, driver string) DBOptions

func (*DBOptions) DbRecorder

func (d *DBOptions) DbRecorder() *structable.DbRecorder

DbRecorder returns a new structable.DbRecorder ready to be bound to a table.

type KeyColumnUsage

type KeyColumnUsage struct {
	// ConstraintLocator
	ConstraintCatalog string `stbl:"constraint_catalog"`
	ConstraintSchema  string `stbl:"constraint_schema"`
	ConstraintName    string `stbl:"constraint_name"`

	//TableLocator
	TableCatalog   string `stbl:"table_catalog"`
	TableSchema    string `stbl:"table_schema"`
	TableNameField string `stbl:"table_name"`

	ColumnName                 string `stbl:"column_name"`
	OrdinalPosition            int    `stbl:"ordinal_position"`
	PositionInUniqueConstraint int    `stbl:"position_in_unique_constraint"`
	// contains filtered or unexported fields
}

func (KeyColumnUsage) Column

func (this KeyColumnUsage) Column() (*Column, error)

Column gets the column associated with this usage record.

func (KeyColumnUsage) TableName

func (this KeyColumnUsage) TableName() string

type Preparer

type Preparer interface {
	Prepare(query string) (*sql.Stmt, error)
}

Preparer provides support for prepared statements.

type Queryer

type Queryer interface {
	squirrel.DBProxyBeginner
}

Queryer defines an interface for querying databases

type Schema

type Schema struct {
	CatalogName string `sql:"catalog_name"`
	SchemaName  string `sql:"schema_name"`
	SchemaOwner string `sql:"schema_owner"`

	DefaultCharacterSetCatalog string `sql:"default_character_set_catalog"`
	DefaultCharacterSetSchema  string `sql:"default_character_set_schema"`
	DefaultCharacterSetName    string `sql:"default_character_set_name"`

	SQLPath string `sql:"sql_path"`
}

type SchemaInfo

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

SchemaInfo provides access to the database schemata.

func New

func New(opts DBOptions) *SchemaInfo

New creates a new SchemaInfo.

func (*SchemaInfo) Select

func (s *SchemaInfo) Select(columns ...string) squirrel.SelectBuilder

func (*SchemaInfo) Supported

func (s *SchemaInfo) Supported() bool

Supported returns true if the given database supports Schema Info.

When running on an unknown database or driver, this can be used as a general mechanism to report whether any of the functions in this library can return useful results.

func (*SchemaInfo) Table

func (s *SchemaInfo) Table(name, catalog, schema string) (*Table, error)

Table gets a table by name (required). If catalog and schema are set, those will be used as additional constraints. Constraining by catalog (e.g. database) is highly recommended.

func (*SchemaInfo) Tables

func (s *SchemaInfo) Tables(catalog, schema string) ([]*Table, error)

Tables gets all tables. Setting catalog and schema to something other than an empty string will constrain by those.

func (*SchemaInfo) View

func (s *SchemaInfo) View(name, catalog, schema string) (*View, error)

func (*SchemaInfo) Views

func (s *SchemaInfo) Views(catalog, schema string) ([]*View, error)

type Table

type Table struct {
	//TableLocator
	TableCatalog   string `stbl:"table_catalog"`
	TableSchema    string `stbl:"table_schema"`
	TableNameField string `stbl:"table_name"`
	// TableType is normally one of BASE TABLE, VIEW, FOREIGN TABLE, or LOCAL TEMPORARY
	TableType                 string `stbl:"table_type"`
	SelfReferencingColumnName string `stbl:"self_referencing_column_name"`
	ReferenceGeneration       string `stbl:"reference_generation"`
	UserDefinedTypeCatalog    string `stbl:"user_defined_type_catalog"`
	UserDefinedTypeSchema     string `stbl:"user_defined_type_schema"`
	IsInsertableInto          bool   `stbl:"is_insertable_into"` // actual type is yes_no
	IsTyped                   bool   `stbl:"is_typed"`           // also yes_or_no
	CommitAction              string `stbl:"commit_action"`
	// contains filtered or unexported fields
}

func (*Table) Column

func (this *Table) Column(name string) (*Column, error)

Column returns a column by name.

func (*Table) Columns

func (this *Table) Columns() ([]*Column, error)

Columns returns the columns contained by this table.

func (*Table) Constraint

func (this *Table) Constraint(name string) (*TableConstraint, error)

func (*Table) Constraints

func (this *Table) Constraints() ([]*TableConstraint, error)

Constraints returns the constraints imposed on this table.

func (*Table) ConstraintsByType

func (this *Table) ConstraintsByType(name ConstraintType) ([]*TableConstraint, error)

func (*Table) ForeignKeys

func (this *Table) ForeignKeys() ([]*TableConstraint, error)

ForeignKeys returns a list of foreign key table constraints.

func (*Table) InViews

func (this *Table) InViews() ([]*View, error)

InViews returns a list of views that use this table.

Not that this might not be the only table used by that view.

func (*Table) PrimaryKey

func (this *Table) PrimaryKey() (*TableConstraint, error)

PrimaryKey returns the primary key for this table.

TODO: Is it ever possible to have two table constraints for one primary key?

func (*Table) Privileges

func (this *Table) Privileges() ([]*TablePrivilege, error)

Privileges returns the table privileges for this table.

func (Table) TableName

func (t Table) TableName() string

type TableConstraint

type TableConstraint struct {
	//TableLocator
	TableCatalog   string `stbl:"table_catalog"`
	TableSchema    string `stbl:"table_schema"`
	TableNameField string `stbl:"table_name"`

	// ConstraintLocator
	ConstraintCatalog string `stbl:"constraint_catalog"`
	ConstraintSchema  string `stbl:"constraint_schema"`
	ConstraintName    string `stbl:"constraint_name"`

	ConstraintType    ConstraintType `stbl:"constraint_type"`
	IsDeferrable      YesNo          `stbl:"is_deferrable"`
	InitiallyDeferred YesNo          `stbl:"initially_deferred"`
	// contains filtered or unexported fields
}

TableConstraint defines a constraint (e.g. primary key, foreign key...) placed on a table.

func (*TableConstraint) ColumnUsage

func (this *TableConstraint) ColumnUsage() ([]*KeyColumnUsage, error)

ColumnUsage returns information about which columns are used in this key.

func (*TableConstraint) TableName

func (t *TableConstraint) TableName() string

type TableLocator

type TableLocator struct {
	TableCatalog   string `sql:"table_catalog"`
	TableSchema    string `sql:"table_schema"`
	TableNameField string `sql:"table_name"`
}

TableLocator describes common fields used to locate a table.

type TablePrivilege

type TablePrivilege struct {
	//TableLocator
	TableCatalog   string `stbl:"table_catalog"`
	TableSchema    string `stbl:"table_schema"`
	TableNameField string `stbl:"table_name"`
	// Core firelds
	Grantor       string `stbl:"grantor"`
	Grantee       string `stbl:"grantee"`
	PrivilegeType string `stbl:"privilege_type"`
	IsGrantable   YesNo  `stbl:"is_grantable"`
	WithHierarchy YesNo  `stbl:"with_hierarchy"`
	// contains filtered or unexported fields
}

TODO

func (*TablePrivilege) TableName

func (t *TablePrivilege) TableName() string

type View

type View struct {
	//TableLocator
	TableCatalog   string `stbl:"table_catalog"`
	TableSchema    string `stbl:"table_schema"`
	TableNameField string `stbl:"table_name"`
	// View definition
	ViewDefinition          string `stbl:"view_definition"`
	CheckOption             string `stbl:"check_option"`
	IsUpdatable             YesNo  `stbl:"is_updatable"`
	IsInsertableInto        YesNo  `stbl:"is_insertable_into"`
	IsTriggerUpdatable      YesNo  `stbl:"is_trigger_updatable"`
	IsTriggerDeletable      YesNo  `stbl:"is_trigger_deletable"`
	IsTriggerInsertableInto YesNo  `stbl:"is_trigger_insertable_into"`
	// contains filtered or unexported fields
}

func (*View) Columns

func (this *View) Columns() []*Column

Columns returns a list of columns for this view.

Columns may be from different tables.

func (*View) TableName

func (v *View) TableName() string

func (*View) Tables

func (v *View) Tables() []*Table

type ViewLocator

type ViewLocator struct {
	ViewCatalog string `stbl:"view_catalog"`
	ViewSchema  string `stbl:"view_schema"`
	ViewName    string `stbl:"view_name"`
}

type YesNo

type YesNo struct {
	// Bool is the boolean value of this flag.
	Bool bool
}

YesNo takes an information schema's yes_or_no type and manages it as a bool.

func (*YesNo) Scan

func (y *YesNo) Scan(v interface{}) error

Scan implements the database/sql.Scanner interface.

func (*YesNo) String

func (y *YesNo) String() string

String returns the YES/NO version of this flag.

String implements fmt.Stringer.

func (*YesNo) Value

func (y *YesNo) Value() (driver.Value, error)

Value iplementes the database/sql/driver.Valuer interface.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL