table.gotmpl

// Code generated by gnorm, DO NOT EDIT!

package {{toLower .Table.Name}}

import (
	"{{.Params.RootImport}}"
	"{{.Params.RootImport}}/{{toLower .Table.Schema.Name}}/enum"
	uuid "github.com/gofrs/uuid"
	"github.com/pkg/errors"
)

// TableName is the primary table that this particular gnormed file deals with.
const TableName = "{{.Table.DBName}}"

{{$rootPkg := .Params.RootPkg -}}
{{$table := .Table.DBName -}}
{{$schema := .Table.Schema.DBName -}}
{{$colsByName := .Table.ColumnsByName }}

{{- $nonPKDBNames := .Table.Columns.DBNames.Sorted.Except .Table.PrimaryKeys.DBNames}}


// Row represents a row from '{{ $table }}'.
type Row struct {
{{- range .Table.PrimaryKeys.DBNames.Sorted }}{{ with (index $colsByName .)}}
	{{ .Name }} {{ if .IsArray }}[]{{ end }}{{ .Type }}  // {{ .DBName }} (PK){{end}}
{{- end }}
{{- range $nonPKDBNames }}{{ with (index $colsByName .) }}
	{{ .Name }} {{ if .IsArray }}[]{{ end }}{{ .Type }}  // {{ .DBName }}{{end}}
{{- end }}
}


// Field values for every column in {{.Table.Name}}.
var (
{{- range .Table.Columns.DBNames.Sorted }}{{with index $colsByName .}}
	{{- if or (hasPrefix .Type (printf "%s." $rootPkg)) (hasPrefix .Type "enum.")}}
	{{.Name}}Col {{ .Type }}Field = "{{ .DBName }}"
	{{- else}}
	{{.Name}}Col {{$rootPkg}}.{{ title (replace .Type "." "" 1) }}Field = "{{ .DBName }}"
	{{- end}}{{end}}
{{- end}}
)

// All retrieves all rows from '{{ $table }}' as a slice of Row.
func All(ctx context.Context, db {{$rootPkg}}.DB) ([]*Row, error) {
	const sqlstr = `SELECT
		{{ join .Table.Columns.DBNames.Sorted ", " }}
		FROM {{$schema}}.{{ $table }}`

	var vals []*Row
	q, err := db.QueryContext(ctx, sqlstr)
	if err != nil {
		return nil, errors.Wrap(err, "query {{.Table.Name}}")
	}
	for q.Next() {
		r := Row{}
		err := q.Scan({{- range .Table.Columns.DBNames.Sorted}}{{with index $colsByName .}}
		{{- if .IsArray }}pq.Array(&r.{{ .Name }}),{{- else -}}&r.{{ .Name }},{{ end }}{{end}}
{{end -}})
		if err != nil {
			return nil, errors.Wrap(err, "all {{.Table.Name}}")
		}
		vals = append(vals, &r)
	}
	return vals, nil
}

// CountQuery retrieve one row from '{{ $table }}'.
func CountQuery(ctx context.Context, db gnorm.DB, where gnorm.WhereClause) (int, error) {
	const origsqlstr = `SELECT
		count(*) as count
		FROM {{$schema}}.{{ $table }} WHERE (`

	idx := 1
	sqlstr := origsqlstr + where.String(&idx) + ") "

	count := 0
	err := db.QueryRowContext(ctx, sqlstr, where.Values()...).Scan(&count)
	if err != nil {
		return 0, errors.Wrap(err, "count {{.Table.Name}}")
	}
	return count, nil
}

// Query retrieves rows from '{{ $table }}' as a slice of Row.
func Query(ctx context.Context, db {{$rootPkg}}.DB, where {{$rootPkg}}.WhereClause) ([]*Row, error) {
	const origsqlstr = `SELECT
		{{ join .Table.Columns.DBNames ", " }}
		FROM {{$schema}}.{{ $table }} WHERE (`

	idx := 1
	sqlstr := origsqlstr + where.String(&idx) + ") "

	var vals []*Row
	q, err := db.QueryContext(ctx, sqlstr, where.Values()...)
	if err != nil {
		return nil, errors.Wrap(err, "query {{.Table.Name}}")
	}
	for q.Next() {
		r := Row{}
		err := q.Scan({{- range .Table.Columns}}
		{{- if .IsArray }}pq.Array(&r.{{ .Name }}),{{- else -}}&r.{{ .Name }},{{ end }}
{{end -}})
		if err != nil {
			return nil, errors.Wrap(err, "query {{.Table.Name}}")
		}
		vals = append(vals, &r)
	}
	return vals, nil
}

// QueryOrder retrieves rows from '{{ $table }}' as a slice of Row in a particular order.
func QueryOrder(ctx context.Context, db {{$rootPkg}}.DB, where {{$rootPkg}}.WhereClause, orderby {{$rootPkg}}.OrderBy) ([]*Row, error) {
	const origsqlstr = `SELECT
		{{ join .Table.Columns.DBNames ", " }}
		FROM {{$schema}}.{{ $table }} WHERE (`

	idx := 1
	sqlstr := origsqlstr + where.String(&idx) + ") " + orderby.String()

	var vals []*Row
	q, err := db.QueryContext(ctx, sqlstr, where.Values()...)
	if err != nil {
		return nil, errors.Wrap(err, "query {{.Table.Name}}")
	}
	for q.Next() {
		r := Row{}
		err := q.Scan({{- range .Table.Columns}}
		{{- if .IsArray }}pq.Array(&r.{{ .Name }}),{{- else -}}&r.{{ .Name }},{{ end }}
{{end -}})
		if err != nil {
			return nil, errors.Wrap(err, "query {{.Table.Name}}")
		}
		vals = append(vals, &r)
	}
	return vals, nil
}

// One retrieve one row from '{{ $table }}'.
func One(ctx context.Context, db {{$rootPkg}}.DB, where {{$rootPkg}}.WhereClause) (*Row, error) {
	const origsqlstr = `SELECT
		{{ join .Table.Columns.DBNames ", " }}
		FROM {{$schema}}.{{ $table }} WHERE (`

	idx := 1
	sqlstr := origsqlstr + where.String(&idx) + ") "

	r := &Row{}
	err := db.QueryRowContext(ctx, sqlstr, where.Values()...).Scan({{- range .Table.Columns}}
		{{- if .IsArray }}pq.Array(&r.{{ .Name }}),{{- else -}}&r.{{ .Name }},{{ end }}
{{end -}})
	if err != nil {
		return nil, errors.Wrap(err, "queryOne {{.Table.Name}}")
	}
	return r, nil
}

// First retrieve one row from '{{ $table }}' when sorted by orderby.
func First(ctx context.Context, db {{$rootPkg}}.DB, where {{$rootPkg}}.WhereClause, orderby {{$rootPkg}}.OrderBy) (*Row, error) {
	const origsqlstr = `SELECT
		{{ join .Table.Columns.DBNames ", " }}
		FROM {{$schema}}.{{ $table }} WHERE (`

	idx := 1
	sqlstr := origsqlstr + where.String(&idx) + ") " + orderby.String()

	r := &Row{}
	err := db.QueryRowContext(ctx, sqlstr, where.Values()...).Scan({{- range .Table.Columns}}
		{{- if .IsArray }}pq.Array(&r.{{ .Name }}),{{- else -}}&r.{{ .Name }},{{ end }}
{{end -}})
	if err != nil {
		return nil, errors.Wrap(err, "queryFirst {{.Table.Name}}")
	}
	return r, nil
}

{{/* Takes the number of values to produce and produces a list of postgres
placeholders of the form $1, $2, etc */}}
{{- define "values" -}}
	{{$nums := numbers 1 . -}}
	{{$indices := $nums.Sprintf "$%s" -}}
	{{join $indices ", " -}}
{{end}}

{{$hasUpdatedAt := ne (len (.Table.Columns.DBNames.Except (makeSlice "updated_at"))) (len .Table.Columns.DBNames)}}

{{- $insertCols := plugin "plugin" "insertCols" .Table.Columns}}
{{- $insertFields := join (plugin "plugin" "insertFields" .Table.Columns) "," }}
{{- $PKFields := join (.Table.PrimaryKeys.Names.Sorted.Sprintf "r.%s") ", "}}
{{- $PKScanFields := join (.Table.PrimaryKeys.Names.Sorted.Sprintf "&r.%s") ", "}}

{{- $numNonPKs := sub (len .Table.Columns) (len .Table.PrimaryKeys)}}


{{if .Table.HasPrimaryKey }}
// Find retrieves a row from '{{ $table }}' by its primary key(s).
func Find(ctx context.Context, db {{$rootPkg}}.DB,
{{- range .Table.PrimaryKeys.DBNames.Sorted}}
	{{- with index $colsByName .}}
	{{camel .DBName}} {{.Type}},{{end}}
{{end -}}) (*Row, error) {
	const sqlstr = `SELECT
		{{ join .Table.Columns.DBNames.Sorted ", " }}
	FROM {{$schema}}.{{ $table }} WHERE ( {{join .Table.PrimaryKeys.DBNames.Sorted ", "}} = {{template "values" (len .Table.PrimaryKeys)}} )`

	r := &Row{}
	err := db.QueryRowContext(ctx, sqlstr,
	{{- range .Table.PrimaryKeys.DBNames.Sorted}}
		{{camel .}},
	{{end -}}).Scan({{- range .Table.Columns.DBNames.Sorted}}{{with index $colsByName .}}
		{{- if .IsArray }}pq.Array(&r.{{ .Name }}),{{- else -}}&r.{{ .Name }},{{ end }}{{end}}
{{end -}})
	if err != nil {
		return nil, errors.Wrap(err, "find {{.Table.Name}}")
	}
	return r, nil
}
{{end}}


// Insert inserts the row into the database.
func Insert(ctx context.Context, db {{$rootPkg}}.DB, r *Row) error {
	const sqlstr = `INSERT INTO {{$schema}}.{{ $table }} ` +
		{{- if gt (len $insertCols) 0}}
		`(
			{{ join $insertCols ", " }}
		) VALUES (
			{{template "values" (len $insertCols) }}
		) ` +
		{{- else}}
		`DEFAULT VALUES ` +
		{{- end}}
		`RETURNING
	{{- if $hasUpdatedAt}}
			created_at, updated_at, {{join .Table.PrimaryKeys.DBNames.Sorted ", "}}
	{{- else}}
			created_at, {{join .Table.PrimaryKeys.DBNames.Sorted ", "}}
	{{- end}}
		`

	{{if $hasUpdatedAt}}
	err := db.QueryRowContext(ctx, sqlstr,
	{{- range $insertCols}}{{with index $colsByName .}}
		{{- if .IsArray }}pq.Array(&r.{{ .Name }}),{{- else -}}&r.{{ .Name }},{{ end }}
	{{end}}
{{end -}}).Scan(&r.CreatedAt, &r.UpdatedAt, {{$PKScanFields}})
	{{else}}
	err := db.QueryRowContext(ctx, sqlstr,
	{{- range $insertCols}}{{with index $colsByName .}}
		{{- if .IsArray }}pq.Array(&r.{{ .Name }}),{{- else -}}&r.{{ .Name }},{{ end }}
	{{end}}
{{end -}}).Scan(&r.CreatedAt, {{$PKScanFields}})
	{{end}}
	return errors.Wrap(err, "insert {{.Table.Name}}")
}

{{if and $hasUpdatedAt (gt (len $insertCols) 0)}}
// Update updates the Row in the database.
func Update(ctx context.Context, db {{$rootPkg}}.DB, r *Row) error {
	const sqlstr = `UPDATE {{$schema}}.{{ $table }} SET (
			{{join $insertCols ", "}}
		) = (
			{{ template "values" (len $insertCols) }}
		) WHERE
	        {{$last := sum (len .Table.PrimaryKeys) (len $insertCols)}} 
	  {{- $first := inc (len $insertCols)}}
	  {{- range $x, $name := .Table.PrimaryKeys.DBNames.Sorted -}}
		{{$name}} = ${{sum $first $x}}{{if lt (sum $x $first) $last}} AND {{end}}
	  {{- end}}
		RETURNING
			created_at, updated_at
		`

		err := db.QueryRowContext(ctx, sqlstr, {{$insertFields}}, {{$PKFields}}).Scan(&r.CreatedAt, &r.UpdatedAt)
		return errors.Wrap(err, "update {{.Table.Name}}:")
}
{{end}}

// InsertIgnore inserts the row into the database but ignores conflicts
func InsertIgnore(ctx context.Context, db gnorm.DB, r *Row, constraint string) error {
	sqlstr := `INSERT INTO {{$schema}}.{{ $table }} ` +
		{{- if gt (len $insertCols) 0}}
		`(
			{{ join $insertCols ", " }}
		) VALUES (
			{{template "values" (len $insertCols) }}
		) ` +
		{{- else}}
		`DEFAULT VALUES ` +
		{{- end}}
		`ON CONFLICT ON CONSTRAINT ` + constraint + ` DO NOTHING `
	_, err := db.ExecContext(ctx, sqlstr, {{$insertFields}})
	return errors.Wrap(err, "insert ignore {{.Table.Name}}")
}

// Set sets a single column on an existing row in the database.
func Set(ctx context.Context, db {{$rootPkg}}.DB, set {{$rootPkg}}.Where, where {{$rootPkg}}.WhereClause) (int64, error) {
	idx := 2
	sqlstr := `UPDATE {{$schema}}.{{ $table }} SET ` +
		set.Field + " = $1 " +
		` WHERE ` +
		where.String(&idx)

	res, err := db.ExecContext(ctx, sqlstr, append([]interface{}{ set.Value }, where.Values()...)...)
	if err != nil {
		return 0, errors.Wrap(err, "set {{.Table.Name}}")
	}
	return res.RowsAffected()
}

// AppendInt64 adds a value to a field
func AppendInt64(ctx context.Context, db {{$rootPkg}}.DB, name string, value interface{}, where {{$rootPkg}}.WhereClause) (int64, error) {
	idx := 2
	sqlstr := `UPDATE {{$schema}}.{{ $table }} SET ` +
		name + " = array_append(" + name + ", $1::bigint) " +
		` WHERE ` +
		where.String(&idx)

	res, err := db.ExecContext(ctx, sqlstr, append([]interface{}{ value }, where.Values()...)...)
	if err != nil {
		return 0, errors.Wrap(err, "append_int64 {{.Table.Name}}")
	}
	return res.RowsAffected()
}

// Inc increments the value of a single column on an existing row in the database.
func Inc(ctx context.Context, db {{$rootPkg}}.DB, inc {{$rootPkg}}.Where, where {{$rootPkg}}.WhereClause) (int64, error) {
	idx := 2
	sqlstr := `UPDATE {{$schema}}.{{ $table }} SET ` +
		inc.Field + " = " + inc.Field + " + $1" +
		` WHERE ` +
		where.String(&idx)

	res, err := db.ExecContext(ctx, sqlstr, append([]interface{}{ inc.Value }, where.Values()...)...)
	if err != nil {
		return 0, errors.Wrap(err, "inc {{.Table.Name}}")
	}
	return res.RowsAffected()
}


{{if and .Table.HasPrimaryKey (gt (len $insertFields) 0)}}
// Upsert performs an insert-or-update in one DB call for {{ .Table.Name }}.
// Unlike insert, upsert requires that you have set any IDs on the row you're upserting.
// NOTE: PostgreSQL 9.5+ only
func Upsert(ctx context.Context, db {{$rootPkg}}.DB, r *Row) error {
	{{$upsertCols := .Table.Columns.DBNames.Sorted.Except (makeSlice "updated_at" "created_at")}}
	const sqlstr = `INSERT INTO {{$schema}}.{{ $table }} (
		{{join $upsertCols ", "}}
	) VALUES (
		{{template "values" (len $upsertCols) }}
	) ON CONFLICT ({{join .Table.PrimaryKeys.DBNames.Sorted ", " }}) DO UPDATE SET (
		{{join $upsertCols ", "}}
	) = (
		{{ template "values" (len $upsertCols) }}
	)`

	{{$upsertNames := .Table.Columns.Names.Sorted.Except (makeSlice "UpdatedAt" "CreatedAt")}}
	{{$upsertFields := join ($upsertNames.Sprintf "r.%s") ", " }}

	_, err := db.ExecContext(ctx, sqlstr, {{$upsertFields}})
	return errors.Wrap(err, "upsert {{.Table.Name}}")
}
{{end}}

{{if .Table.HasPrimaryKey }}
// Delete deletes the Row from the database. Returns the number of items deleted.
func Delete( ctx context.Context,
	db {{$rootPkg}}.DB,
{{- range .Table.PrimaryKeys.DBNames.Sorted}}{{with index $colsByName .}}
	{{camel .DBName}} {{.Type}},{{end}}
{{end -}}
) (int64, error) {
	const sqlstr = `DELETE FROM {{$schema}}.{{ $table }} 
	WHERE
	  {{$last := dec (len .Table.PrimaryKeys)}} 
	  {{- range $x, $name := .Table.PrimaryKeys.DBNames.Sorted -}}
		{{$name}} = ${{inc $x}}{{if lt $x $last}} AND {{end}}
	  {{- end}}
	`

	res, err := db.ExecContext(ctx, sqlstr,
	{{- range .Table.PrimaryKeys.DBNames.Sorted -}}
		{{camel .}},
	{{- end -}}
	)
	if err != nil {
		return 0, errors.Wrap(err, "delete {{.Table.Name}}")
	}
	rows, err := res.RowsAffected()
	if err != nil {
		return 0, err
	}
	return rows, nil
}
{{end}}

// DeleteWhere deletes Rows from the database and returns the number of rows deleted.
func DeleteWhere(ctx context.Context, db {{$rootPkg}}.DB, where {{$rootPkg}}.WhereClause) (int64, error) {
	const origsqlstr = `DELETE FROM {{$schema}}.{{ $table }} WHERE (`

	idx := 1
	sqlstr := origsqlstr + where.String(&idx) + ") "

	res, err := db.ExecContext(ctx, sqlstr, where.Values()...)
	if err != nil {
		return 0, errors.Wrap(err, "delete {{.Table.Name}}")
	}
	return res.RowsAffected()
}


// DeleteAll deletes all Rows from the database and returns the number of rows deleted.
func DeleteAll(ctx context.Context, db {{$rootPkg}}.DB) (int64, error) {
	const sqlstr = `DELETE FROM {{$schema}}.{{ $table }}`

	res, err := db.ExecContext(ctx, sqlstr)
	if err != nil {
		return 0, errors.Wrap(err, "deleteall {{.Table.Name}}")
	}
	return res.RowsAffected()
}