Validated ORDER BY: CockroachDB and TimescaleDB
CockroachDB and TimescaleDB run the same files for this example: the SQL and the Go are identical.
Configuration
sgl.yamlidentical
CockroachDB
TimescaleDB
1
version: 11
version: 12
2
3
databases:3
databases:4
- name: regatta4
- name: regatta5
dialect: postgres5
dialect: postgres6
migrations: db/migrations6
migrations: db/migrations7
queries: db/queries7
queries: db/queries8
go:8
go:9
package: store9
package: store10
out: internal/store10
out: internal/store11
driver: database/sql11
driver: database/sqlMigrations
db/migrations/0001_entries.up.sqlidentical
CockroachDB
TimescaleDB
1
CREATE TABLE entries (1
CREATE TABLE entries (2
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,2
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,3
boat TEXT NOT NULL,3
boat TEXT NOT NULL,4
crew BIGINT NOT NULL,4
crew BIGINT NOT NULL,5
finish_seconds BIGINT NOT NULL5
finish_seconds BIGINT NOT NULL6
);6
);db/migrations/0001_entries.down.sqlidentical
CockroachDB
TimescaleDB
1
DROP TABLE entries;1
DROP TABLE entries;Queries
EnterBoatidentical
CockroachDB · db/queries/entries.sql
TimescaleDB · db/queries/entries.sql
1
-- sgl.query(EnterBoat, exec)1
-- sgl.query(EnterBoat, exec)2
INSERT INTO entries (boat, crew, finish_seconds) VALUES ($1, $2, $3);2
INSERT INTO entries (boat, crew, finish_seconds) VALUES ($1, $2, $3);ListEntriesidentical
CockroachDB · db/queries/entries.sql
TimescaleDB · db/queries/entries.sql
1
-- sgl.query(ListEntries, many)1
-- sgl.query(ListEntries, many)2
-- sgl.sortable(order_by, [boat, crew, finish_seconds])2
-- sgl.sortable(order_by, [boat, crew, finish_seconds])3
SELECT id, boat, crew, finish_seconds FROM entries3
SELECT id, boat, crew, finish_seconds FROM entriesWhat sgl generates
EnterBoatidentical
CockroachDB
TimescaleDB
1
func (queries *Queries) EnterBoat(ctx context.Context, params EnterBoatParams) error1
func (queries *Queries) EnterBoat(ctx context.Context, params EnterBoatParams) error2
2
3
type EnterBoatParams struct {3
type EnterBoatParams struct {4
Boat string4
Boat string5
Crew int645
Crew int646
FinishSeconds int646
FinishSeconds int647
}7
}ListEntriesidentical
CockroachDB
TimescaleDB
1
func (queries *Queries) ListEntries(ctx context.Context, params ListEntriesParams) ([]ListEntriesRow, error)1
func (queries *Queries) ListEntries(ctx context.Context, params ListEntriesParams) ([]ListEntriesRow, error)2
2
3
type ListEntriesParams struct {3
type ListEntriesParams struct {4
OrderBy ListEntriesOrderBy4
OrderBy ListEntriesOrderBy5
OrderByDirection OrderDirection5
OrderByDirection OrderDirection6
}6
}7
7
8
type ListEntriesRow struct {8
type ListEntriesRow struct {9
ID int64 `json:"id"`9
ID int64 `json:"id"`10
Boat string `json:"boat"`10
Boat string `json:"boat"`11
Crew int64 `json:"crew"`11
Crew int64 `json:"crew"`12
FinishSeconds int64 `json:"finish_seconds"`12
FinishSeconds int64 `json:"finish_seconds"`13
}13
}14
14
15
type ListEntriesOrderBy string15
type ListEntriesOrderBy string16
16
17
type OrderDirection string17
type OrderDirection stringThe program
example.goidentical
CockroachDB
TimescaleDB
1
package main1
package main2
2
3
import (3
import (4
"context"4
"context"5
"database/sql"5
"database/sql"6
"fmt"6
"fmt"7
"io"7
"io"8
"time"8
"time"9
9
10
"seagull.sh/seagull/examples/postgres/012_sortable/internal/store"10
"seagull.sh/seagull/examples/postgres/012_sortable/internal/store"11
)11
)12
12
13
// run lists a regatta's entries in four orders. sgl.sortable(order_by, [boat, crew,13
// run lists a regatta's entries in four orders. sgl.sortable(order_by, [boat, crew,14
// finish_seconds]) has no placeholder: it gives ListEntriesParams an OrderBy field, typed14
// finish_seconds]) has no placeholder: it gives ListEntriesParams an OrderBy field, typed15
// with one constant per allowed column, and an OrderByDirection. The method appends ORDER15
// with one constant per allowed column, and an OrderByDirection. The method appends ORDER16
// BY only when OrderBy is one of the allowed columns, so a value that is not, such as raw16
// BY only when OrderBy is one of the allowed columns, so a value that is not, such as raw17
// text from a request, adds nothing to the SQL and the rows come back in whatever order17
// text from a request, adds nothing to the SQL and the rows come back in whatever order18
// the server reads them, here the order they were entered. A sortable column the query18
// the server reads them, here the order they were entered. A sortable column the query19
// does not select makes sgl vet report Q011.19
// does not select makes sgl vet report Q011.20
//20
//21
// Takes database (*sql.DB) which is a migrated database.21
// Takes database (*sql.DB) which is a migrated database.22
// Takes output (io.Writer) which receives each listing.22
// Takes output (io.Writer) which receives each listing.23
//23
//24
// Returns error when a query fails.24
// Returns error when a query fails.25
func run(ctx context.Context, database *sql.DB, output io.Writer) error {25
func run(ctx context.Context, database *sql.DB, output io.Writer) error {26
queries := store.New(database)26
queries := store.New(database)27
27
28
entries := []store.EnterBoatParams{28
entries := []store.EnterBoatParams{29
{Boat: "Kittiwake", Crew: 4, FinishSeconds: 3725},29
{Boat: "Kittiwake", Crew: 4, FinishSeconds: 3725},30
{Boat: "Salty Chip", Crew: 3, FinishSeconds: 3410},30
{Boat: "Salty Chip", Crew: 3, FinishSeconds: 3410},31
{Boat: "Gull Wing", Crew: 5, FinishSeconds: 3990},31
{Boat: "Gull Wing", Crew: 5, FinishSeconds: 3990},32
{Boat: "Driftwood", Crew: 2, FinishSeconds: 3588},32
{Boat: "Driftwood", Crew: 2, FinishSeconds: 3588},33
}33
}34
for _, entry := range entries {34
for _, entry := range entries {35
if err := queries.EnterBoat(ctx, entry); err != nil {35
if err := queries.EnterBoat(ctx, entry); err != nil {36
return fmt.Errorf("entering %s: %w", entry.Boat, err)36
return fmt.Errorf("entering %s: %w", entry.Boat, err)37
}37
}38
}38
}39
39
40
orders := []struct {40
orders := []struct {41
label string41
label string42
parameters store.ListEntriesParams42
parameters store.ListEntriesParams43
}{43
}{44
{label: "results", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByFinishSeconds, OrderByDirection: store.OrderAsc}},44
{label: "results", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByFinishSeconds, OrderByDirection: store.OrderAsc}},45
{label: "by boat", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByBoat, OrderByDirection: store.OrderAsc}},45
{label: "by boat", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByBoat, OrderByDirection: store.OrderAsc}},46
{label: "largest crew first", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByCrew, OrderByDirection: store.OrderDesc}},46
{label: "largest crew first", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByCrew, OrderByDirection: store.OrderDesc}},47
{label: "raw text, ignored", parameters: store.ListEntriesParams{OrderBy: "boat; DROP TABLE entries", OrderByDirection: store.OrderAsc}},47
{label: "raw text, ignored", parameters: store.ListEntriesParams{OrderBy: "boat; DROP TABLE entries", OrderByDirection: store.OrderAsc}},48
}48
}49
for _, order := range orders {49
for _, order := range orders {50
listed, err := queries.ListEntries(ctx, order.parameters)50
listed, err := queries.ListEntries(ctx, order.parameters)51
if err != nil {51
if err != nil {52
return fmt.Errorf("listing entries %s: %w", order.label, err)52
return fmt.Errorf("listing entries %s: %w", order.label, err)53
}53
}54
fmt.Fprintf(output, "%s:\n", order.label)54
fmt.Fprintf(output, "%s:\n", order.label)55
for _, entry := range listed {55
for _, entry := range listed {56
finish := time.Duration(entry.FinishSeconds) * time.Second56
finish := time.Duration(entry.FinishSeconds) * time.Second57
fmt.Fprintf(output, " %-10s crew %d %s\n", entry.Boat, entry.Crew, finish)57
fmt.Fprintf(output, " %-10s crew %d %s\n", entry.Boat, entry.Crew, finish)58
}58
}59
}59
}60
return nil60
return nil61
}61
}