sqltemplate

package
v1.24.4 Latest Latest
Warning

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

 Go to latest Published: Nov 29, 2022 License: MIT Imports: 9 Imported by: 0

Documentation

Overview

Package sqltemplate

Templates are used for creation of the SQL used when creating and modifying tables. These templates are specified within the configuration as the parameters 'create_templates', 'add_column_templates', 'tag_table_create_templates', and 'tag_table_add_column_templates'.

The templating functionality behaves the same in all cases. However, the variables will differ.

Variables

The following variables are available within all template executions:

  • table - A Table object referring to the current table being created/modified.

  • columns - A Columns object of the new columns being added to the table (all columns in the case of a new table, and new columns in the case of existing table).

  • allColumns - A Columns object of all the columns (both old and new) of the table. In the case of a new table, this is the same as `columns`.

  • metricTable - A Table object referring to the table containing the fields. In the case of TagsAsForeignKeys and `table` is the tag table, then `metricTable` is the table using this one for its tags.

  • tagTable - A Table object referring to the table containing the tags. In the case of TagsAsForeignKeys and `table` is the metrics table, then `tagTable` is the table containing the tags for it.

Each object has helper methods that may be used within the template. See the documentation for the appropriate type.

When the object is interpolated without a helper, it is automatically converted to a string through its String() method.

Functions

All the functions provided by the Sprig library (http://masterminds.github.io/sprig/) are available within template executions.

In addition, the following functions are also available:

  • quoteIdentifier - Quotes the input string as a Postgres identifier.

  • quoteLiteral - Quotes the input string as a Postgres literal.

Examples

The default templates show basic usage. When left unconfigured, it is the equivalent of: 

[outputs.postgresql]
  create_templates = [
    '''CREATE TABLE {{.table}} ({{.columns}})''',
  ]
  add_column_templates = [
    '''ALTER TABLE {{.table}} ADD COLUMN IF NOT EXISTS {{.columns|join ", ADD COLUMN IF NOT EXISTS "}}''',
  ]
  tag_table_create_templates = [
    '''CREATE TABLE {{.table}} ({{.columns}}, PRIMARY KEY (tag_id))'''
  ]
  tag_table_add_column_templates = [
    '''ALTER TABLE {{.table}} ADD COLUMN IF NOT EXISTS {{.columns|join ", ADD COLUMN IF NOT EXISTS "}}''',
  ]

A simple example for usage with TimescaleDB would be: 

[outputs.postgresql]
  create_templates = [
    '''CREATE TABLE {{ .table }} ({{ .allColumns }})''',
    '''SELECT create_hypertable({{ .table|quoteLiteral }}, 'time', chunk_time_interval => INTERVAL '1d')''',
    '''ALTER TABLE {{ .table }} SET (timescaledb.compress, timescaledb.compress_segmentby = 'tag_id')''',
    '''SELECT add_compression_policy({{ .table|quoteLiteral }}, INTERVAL '2h')''',
  ]

...where the defaults for the other templates would be automatically applied.

A very complex example for versions of TimescaleDB which don't support adding columns to compressed hypertables (v<2.1.0), using views and unions to emulate the functionality, would be: 

[outputs.postgresql]
  schema = "telegraf"
  create_templates = [
    '''CREATE TABLE {{ .table }} ({{ .allColumns }})''',
    '''SELECT create_hypertable({{ .table|quoteLiteral }}, 'time', chunk_time_interval => INTERVAL '1d')''',
    '''ALTER TABLE {{ .table }} SET (timescaledb.compress, timescaledb.compress_segmentby = 'tag_id')''',
    '''SELECT add_compression_policy({{ .table|quoteLiteral }}, INTERVAL '2d')''',
    '''CREATE VIEW {{ .table.WithSuffix "_data" }} AS
         SELECT {{ .allColumns.Selectors | join "," }} FROM {{ .table }}''',
    '''CREATE VIEW {{ .table.WithSchema "public" }} AS
         SELECT time, {{ (.tagTable.Columns.Tags.Concat .allColumns.Fields).Identifiers | join "," }}
         FROM {{ .table.WithSuffix "_data" }} t, {{ .tagTable }} tt
         WHERE t.tag_id = tt.tag_id''',
  ]
  add_column_templates = [
    '''ALTER TABLE {{ .table }} RENAME TO {{ (.table.WithSuffix "_" .table.Columns.Hash).WithSchema "" }}''',
    '''ALTER VIEW {{ .table.WithSuffix "_data" }} RENAME TO {{ (.table.WithSuffix "_" .table.Columns.Hash "_data").WithSchema "" }}''',
    '''DROP VIEW {{ .table.WithSchema "public" }}''',

    '''CREATE TABLE {{ .table }} ({{ .allColumns }})''',
    '''SELECT create_hypertable({{ .table|quoteLiteral }}, 'time', chunk_time_interval => INTERVAL '1d')''',
    '''ALTER TABLE {{ .table }} SET (timescaledb.compress, timescaledb.compress_segmentby = 'tag_id')''',
    '''SELECT add_compression_policy({{ .table|quoteLiteral }}, INTERVAL '2d')''',
    '''CREATE VIEW {{ .table.WithSuffix "_data" }} AS
         SELECT {{ .allColumns.Selectors | join "," }}
         FROM {{ .table }}
         UNION ALL
         SELECT {{ (.allColumns.Union .table.Columns).Selectors | join "," }}
         FROM {{ .table.WithSuffix "_" .table.Columns.Hash "_data" }}''',
    '''CREATE VIEW {{ .table.WithSchema "public" }}
         AS SELECT time, {{ (.tagTable.Columns.Tags.Concat .allColumns.Fields).Identifiers | join "," }}
         FROM {{ .table.WithSuffix "_data" }} t, {{ .tagTable }} tt
         WHERE t.tag_id = tt.tag_id''',
  ]

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func QuoteIdentifier 

func QuoteIdentifier(name interface{}) string

QuoteIdentifier quotes the given string as a Postgres identifier (double-quotes the value).

QuoteIdentifier is accessible within templates as 'quoteIdentifier'.

func QuoteLiteral 

func QuoteLiteral(str interface{}) string

QuoteLiteral quotes the given string as a Postgres literal (single-quotes the value).

QuoteLiteral is accessible within templates as 'quoteLiteral'.

Types

type Column 

type Column utils.Column

A Column is an object which represents a Postgres column.

func (Column) Definition 

func (tc Column) Definition() string

Definition returns the column's definition (as used in a CREATE TABLE statement). E.G: 

"my_column" bigint

func (Column) Identifier 

func (tc Column) Identifier() string

Identifier returns the column's quoted identifier.

func (Column) IsField 

func (tc Column) IsField() bool

IsField returns true if the column is a field column. Otherwise, false.

func (Column) IsTag 

func (tc Column) IsTag() bool

IsTag returns true if the column is a tag column. Otherwise, false.

func (Column) Selector 

func (tc Column) Selector() string

Selector returns the selector for the column. For most cases this is the same as Identifier. However, in some cases, such as a UNION, this may return a statement such as `NULL AS "foo"`.

func (Column) String 

func (tc Column) String() string

String returns the column's definition (as used in a CREATE TABLE statement). E.G: 

"my_column" bigint

type Columns 

type Columns []Column

Columns represents an ordered list of Column objects, with convenience methods for operating on the list.

func NewColumns 

func NewColumns(cols []utils.Column) Columns

func (Columns) Concat 

func (cols Columns) Concat(tcsList ...Columns) Columns

Concat returns a copy of Columns with the given tcsList appended to the end.

func (Columns) Definitions 

func (cols Columns) Definitions() []string

Definitions returns the list of column definitions.

func (Columns) Fields 

func (cols Columns) Fields() Columns

Fields returns a Columns list of the columns which are fields.

func (Columns) Hash 

func (cols Columns) Hash() string

Hash returns a hash of the column names. The hash is base-32 encoded string, up to 7 characters long with no padding.

This can be useful as an identifier for supporting table renaming + unions in the case of non-modifiable tables.

func (Columns) Identifiers 

func (cols Columns) Identifiers() []string

Identifiers returns the list of quoted column identifiers.

func (Columns) Keys 

func (cols Columns) Keys() Columns

Keys returns a Columns list of the columns which are not fields (e.g. time, tag_id, & tags).

func (Columns) List 

func (cols Columns) List() []Column

List returns the Columns object as a slice of Column.

func (Columns) Selectors 

func (cols Columns) Selectors() []string

Selectors returns the list of column selectors.

func (Columns) Sorted 

func (cols Columns) Sorted() Columns

Sorted returns a sorted copy of Columns.

Columns are sorted so that they are in order as: [Time, Tags, Fields], with the columns within each group sorted alphabetically.

func (Columns) String 

func (cols Columns) String() string

String returns the comma delimited list of column identifiers.

func (Columns) Tags 

func (cols Columns) Tags() Columns

Tags returns a Columns list of the columns which are tags.

func (Columns) Union 

func (cols Columns) Union(tcsFrom Columns) Columns

Union generates a list of SQL selectors against the given columns.

For each column in tcs, if the column also exist in tcsFrom, it will be selected. If the column does not exist NULL will be selected.

type Table 

type Table struct {
	Schema  string
	Name    string
	Columns Columns
}

Table is an object which represents a Postgres table.

func NewTable 

func NewTable(schemaName, tableName string, columns []utils.Column) *Table

func (*Table) Identifier 

func (tbl *Table) Identifier() string

Identifier returns the table's fully qualified & quoted identifier (schema+table).

If schema is empty, it is omitted from the result.

func (*Table) String 

func (tbl *Table) String() string

String returns the table's fully qualified & quoted identifier (schema+table).

func (*Table) WithName 

func (tbl *Table) WithName(name string) *Table

WithName returns a copy of the Table object, but with the name replaced by the given value.

func (*Table) WithSchema 

func (tbl *Table) WithSchema(name string) *Table

WithSchema returns a copy of the Table object, but with the schema replaced by the given value.

func (*Table) WithSuffix 

func (tbl *Table) WithSuffix(suffixes ...string) *Table

WithSuffix returns a copy of the Table object, but with the name suffixed with the given value.

type Template 

type Template template.Template

func (*Template) Render 

func (t *Template) Render(table *Table, newColumns []utils.Column, metricTable *Table, tagTable *Table) ([]byte, error)

func (*Template) UnmarshalText 

func (t *Template) UnmarshalText(text []byte) 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.