MergeTree engines
On this page
On ClickHouse:
- ReplacingMergeTree(version) keeps the highest version per sorting key when parts merge; FINAL applies that at read time
- SummingMergeTree(kilos) adds up kilos across rows with the same key; FINAL sums at read time
- AggregatingMergeTree columns hold AggregateFunction states, written with sumState and maxState and read with sumMerge and maxMerge
- PREWHERE filters on one column before the others are read
- A -Merge result has the type its function returns and is never NULL: sumMerge of a sum(UInt64) state is uint64, and maxMerge of a max(UInt32) state is uint32
ClickHouse does not update rows in place. Every INSERT writes a new part, and the server merges parts in the background, when it chooses. The MergeTree family of table engines decides what happens to rows that share a sorting key when their parts merge: a ReplacingMergeTree keeps only the newest, a SummingMergeTree adds them up, and an AggregatingMergeTree combines partial aggregates. Designing with these engines is how ClickHouse does upserts, counters and roll-ups.
Seagull reads each table's engine from the migration, and FINAL, PREWHERE and the aggregate state functions from the queries, and types the results as usual.
The example keeps a fishing harbour's records in four tables, one for each behaviour.
The tables
db/migrations/0001_harbour.up.sql
CREATE TABLE landings (
id UInt64,
day Date,
boat String,
species String,
kilos UInt32
) ENGINE = MergeTree() ORDER BY (species, day, id);
CREATE TABLE berths (
boat String,
berth String,
version UInt32
) ENGINE = ReplacingMergeTree(version) ORDER BY boat;
CREATE TABLE daily_catch (
day Date,
species String,
kilos UInt64
) ENGINE = SummingMergeTree(kilos) ORDER BY (day, species);
CREATE TABLE species_stats (
species String,
kilos AggregateFunction(sum, UInt64),
heaviest AggregateFunction(max, UInt32)
) ENGINE = AggregatingMergeTree() ORDER BY species;
Latest version wins: ReplacingMergeTree
berths is a ReplacingMergeTree(version) ordered by boat. When parts merge, it keeps only the row with the highest version for each boat, so moving a boat is an INSERT with a higher version, not an UPDATE.
db/queries/harbour.sql
-- sgl.query(MoveBoat, exec)
INSERT INTO berths (boat, berth, version) VALUES ({boat:String}, {berth:String}, {version:UInt32});
A merge may not have happened yet when you read, so FINAL applies it as the query reads, and CurrentBerths sees one row for each boat straight away.
db/queries/harbour.sql
-- sgl.query(CurrentBerths, many)
SELECT boat, berth, version FROM berths FINAL ORDER BY boat;
Running totals: SummingMergeTree
daily_catch is a SummingMergeTree(kilos) ordered by day and species. Each landing adds a row, and a merge adds up kilos across rows with the same day and species. FINAL again does it at read time.
db/queries/harbour.sql
-- sgl.query(DailyCatch, many)
SELECT day, species, kilos FROM daily_catch FINAL ORDER BY day, species;
Partial aggregates: AggregatingMergeTree
species_stats holds partial aggregates rather than values: its columns are AggregateFunction(sum, UInt64) and AggregateFunction(max, UInt32). RollUpDay writes one day's states with sumState and maxState:
db/queries/harbour.sql
-- sgl.query(RollUpDay, exec)
INSERT INTO species_stats
SELECT species, sumState(toUInt64(kilos)), maxState(kilos)
FROM landings
WHERE day = {day:Date}
GROUP BY species;
SpeciesStats combines every day's states with sumMerge and maxMerge, giving totals and maxima over both days without reading the landings again:
db/queries/harbour.sql
-- sgl.query(SpeciesStats, many)
SELECT species, sumMerge(kilos) AS kilos, maxMerge(heaviest) AS heaviest
FROM species_stats
GROUP BY species
ORDER BY species;
A -Merge function returns what its aggregate would: sumMerge of a sum(UInt64) state is a UInt64, and maxMerge of a max(UInt32) state is a UInt32. Neither is NULL, so Kilos is a plain uint64 and Heaviest a plain uint32.
internal/store
type SpeciesStatsRow struct {
Species string `json:"species"`
Kilos uint64 `json:"kilos"`
Heaviest uint32 `json:"heaviest"`
}
func (queries *Queries) SpeciesStats(ctx context.Context) ([]SpeciesStatsRow, error) {
rows, err := queries.reader.QueryContext(ctx, speciesStatsSQL)
if err != nil {
return nil, err
}
defer rows.Close()
var results []SpeciesStatsRow
if rows.Next() {
var row SpeciesStatsRow
scanArgs := []any{&row.Species, &row.Kilos, &row.Heaviest}
for more := true; more; more = rows.Next() {
row = SpeciesStatsRow{}
if err := rows.Scan(scanArgs...); err != nil {
return nil, err
}
results = append(results, row)
}
}
if err := rows.Err(); err != nil {
return nil, err
}
return results, nil
}
Filtering early: PREWHERE
landings is a plain MergeTree. PREWHERE reads the species column first, and only then reads the other columns of the rows that match, which saves reading data that a selective filter would throw away.
db/queries/harbour.sql
-- sgl.query(BigLandings, many)
SELECT id, boat, kilos
FROM landings
PREWHERE species = {species:String}
WHERE kilos >= {min_kilos:UInt32}
ORDER BY id;
The two {name:Type} parameters become a BigLandingsParams with a uint32 for min_kilos, typed from the placeholder.
Using it
example.go
package main
import (
"context"
"database/sql"
"fmt"
"io"
"time"
"seagull.sh/seagull/examples/clickhouse/034_clickhouse_mergetree/internal/store"
)
// run keeps a fishing harbour's records in four tables, one for each way a MergeTree
// engine treats rows that share a sorting key. Every INSERT writes a new part, and
// ClickHouse merges parts in the background at a time of its choosing:
//
// - berths is a ReplacingMergeTree(version): a merge keeps only the row with the
// highest version for each boat, so moving a boat is an INSERT, not an UPDATE. FINAL
// applies that merge as the query reads, so CurrentBerths sees one row a boat
// straight away;
// - daily_catch is a SummingMergeTree(kilos): a merge adds up kilos across rows with
// the same day and species, and FINAL again does it at read time;
// - species_stats is an AggregatingMergeTree whose columns hold partial aggregates.
// RollUpDay stores sumState and maxState for one day's landings; SpeciesStats
// combines every day's states with sumMerge and maxMerge;
// - landings is a plain MergeTree, and BigLandings filters it with PREWHERE, which
// reads the species column first and only then the rest of the rows that match.
//
// Seagull reads each engine, FINAL and PREWHERE from the SQL and types the results as
// usual. A -Merge combinator returns what its function would: the kilos column holds
// AggregateFunction(sum, UInt64) states, so sumMerge gives a UInt64, and heaviest holds
// AggregateFunction(max, UInt32) states, so maxMerge gives a UInt32. Neither is NULL, so
// Kilos is a uint64 and Heaviest a uint32.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each table's view.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
queries := store.New(database)
moves := []store.MoveBoatParams{
{Boat: "Kittiwake", Berth: "north quay", Version: 1},
{Boat: "Salty Chip", Berth: "slipway", Version: 1},
{Boat: "Kittiwake", Berth: "fish market", Version: 2},
{Boat: "Gull Wing", Berth: "north quay", Version: 1},
{Boat: "Kittiwake", Berth: "outer harbour", Version: 3},
}
for _, move := range moves {
if err := queries.MoveBoat(ctx, move); err != nil {
return fmt.Errorf("moving %s: %w", move.Boat, err)
}
}
berths, err := queries.CurrentBerths(ctx)
if err != nil {
return fmt.Errorf("reading berths: %w", err)
}
fmt.Fprintln(output, "berths, latest version of each boat:")
for _, berth := range berths {
fmt.Fprintf(output, " %-10s %-13s v%d\n", berth.Boat, berth.Berth, berth.Version)
}
landings := []store.LandCatchParams{
{ID: 1, Day: day(1), Boat: "Kittiwake", Species: "mackerel", Kilos: 120},
{ID: 2, Day: day(1), Boat: "Salty Chip", Species: "crab", Kilos: 30},
{ID: 3, Day: day(1), Boat: "Gull Wing", Species: "mackerel", Kilos: 80},
{ID: 4, Day: day(2), Boat: "Kittiwake", Species: "mackerel", Kilos: 95},
{ID: 5, Day: day(2), Boat: "Kittiwake", Species: "pollock", Kilos: 45},
{ID: 6, Day: day(2), Boat: "Salty Chip", Species: "crab", Kilos: 25},
}
for _, landing := range landings {
if err := queries.LandCatch(ctx, landing); err != nil {
return fmt.Errorf("landing catch %d: %w", landing.ID, err)
}
tally := store.TallyCatchParams{Day: landing.Day, Species: landing.Species, Kilos: uint64(landing.Kilos)}
if err := queries.TallyCatch(ctx, tally); err != nil {
return fmt.Errorf("tallying catch %d: %w", landing.ID, err)
}
}
daily, err := queries.DailyCatch(ctx)
if err != nil {
return fmt.Errorf("reading the daily catch: %w", err)
}
fmt.Fprintln(output, "daily catch, summed by day and species:")
for _, row := range daily {
fmt.Fprintf(output, " %s %-9s %3d kg\n", row.Day.Format(time.DateOnly), row.Species, row.Kilos)
}
for _, date := range []int{1, 2} {
if err := queries.RollUpDay(ctx, day(date)); err != nil {
return fmt.Errorf("rolling up day %d: %w", date, err)
}
}
stats, err := queries.SpeciesStats(ctx)
if err != nil {
return fmt.Errorf("reading species stats: %w", err)
}
fmt.Fprintln(output, "species stats, merged from both days' states:")
for _, row := range stats {
fmt.Fprintf(output, " %-9s %3d kg in all, heaviest landing %d kg\n", row.Species, row.Kilos, row.Heaviest)
}
big, err := queries.BigLandings(ctx, store.BigLandingsParams{Species: "mackerel", MinKilos: 90})
if err != nil {
return fmt.Errorf("finding big landings: %w", err)
}
fmt.Fprintln(output, "mackerel landings of 90 kg or more:")
for _, landing := range big {
fmt.Fprintf(output, " #%d %-10s %d kg\n", landing.ID, landing.Boat, landing.Kilos)
}
return nil
}
// day returns a date in August 2026, at midnight UTC. A Date parameter binds the calendar
// date a time has in its own location.
//
// Takes date (int) which is the day of the month.
//
// Returns time.Time which is the date.
func day(date int) time.Time {
return time.Date(2026, time.August, date, 0, 0, 0, 0, time.UTC)
}
What it prints
Kittiwake was moved three times and appears once, at its third berth. Day 1's two mackerel landings appear as one 200 kg row, and the species totals cover both days.
berths, latest version of each boat:
Gull Wing north quay v1
Kittiwake outer harbour v3
Salty Chip slipway v1
daily catch, summed by day and species:
2026-08-01 crab 30 kg
2026-08-01 mackerel 200 kg
2026-08-02 crab 25 kg
2026-08-02 mackerel 95 kg
2026-08-02 pollock 45 kg
species stats, merged from both days' states:
crab 55 kg in all, heaviest landing 30 kg
mackerel 295 kg in all, heaviest landing 120 kg
pollock 45 kg in all, heaviest landing 45 kg
mackerel landings of 90 kg or more:
#1 Kittiwake 120 kg
#4 Kittiwake 95 kg
Try it
Remove FINAL from CurrentBerths and run the example. The query then reads whatever parts exist, so until ClickHouse merges them, Kittiwake can appear once for each move, the old berths included. FINAL costs some work at read time; it is what makes the answer correct before the merge.
Run it locally
From the directory you cloned the Seagull repository into:
cd seagull/examples/clickhouse && go run ./034_clickhouse_mergetree
Its test runs it too, and checks what it prints against output.txt:
cd seagull/examples/clickhouse && SGL_EXAMPLE_DSN=clickhouse://localhost:9000/default go test ./034_clickhouse_mergetree
Needs a running ClickHouse server: it connects to clickhouse://localhost:9000/default, or pass -dsn with the address of an empty database.
The files
Every file of the example, as it is in the Seagull repository.
sgl.yaml
# Copyright 2026 PolitePixels Limited
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
# This project stands against fascism, authoritarianism, and all forms of
# oppression. We built this to empower people, not to enable those who would
# strip others of their rights and dignity.
version: 1
databases:
- name: harbour
dialect: clickhouse
migrations: db/migrations
queries: db/queries
go:
package: store
out: internal/store
db/migrations/0001_harbour.up.sql
CREATE TABLE landings (
id UInt64,
day Date,
boat String,
species String,
kilos UInt32
) ENGINE = MergeTree() ORDER BY (species, day, id);
CREATE TABLE berths (
boat String,
berth String,
version UInt32
) ENGINE = ReplacingMergeTree(version) ORDER BY boat;
CREATE TABLE daily_catch (
day Date,
species String,
kilos UInt64
) ENGINE = SummingMergeTree(kilos) ORDER BY (day, species);
CREATE TABLE species_stats (
species String,
kilos AggregateFunction(sum, UInt64),
heaviest AggregateFunction(max, UInt32)
) ENGINE = AggregatingMergeTree() ORDER BY species;
db/migrations/0001_harbour.down.sql
DROP TABLE species_stats;
DROP TABLE daily_catch;
DROP TABLE berths;
DROP TABLE landings;
db/queries/harbour.sql
-- sgl.query(LandCatch, exec)
INSERT INTO landings (id, day, boat, species, kilos)
VALUES ({id:UInt64}, {day:Date}, {boat:String}, {species:String}, {kilos:UInt32});
-- sgl.query(MoveBoat, exec)
INSERT INTO berths (boat, berth, version) VALUES ({boat:String}, {berth:String}, {version:UInt32});
-- sgl.query(CurrentBerths, many)
SELECT boat, berth, version FROM berths FINAL ORDER BY boat;
-- sgl.query(TallyCatch, exec)
INSERT INTO daily_catch (day, species, kilos) VALUES ({day:Date}, {species:String}, {kilos:UInt64});
-- sgl.query(DailyCatch, many)
SELECT day, species, kilos FROM daily_catch FINAL ORDER BY day, species;
-- sgl.query(RollUpDay, exec)
INSERT INTO species_stats
SELECT species, sumState(toUInt64(kilos)), maxState(kilos)
FROM landings
WHERE day = {day:Date}
GROUP BY species;
-- sgl.query(SpeciesStats, many)
SELECT species, sumMerge(kilos) AS kilos, maxMerge(heaviest) AS heaviest
FROM species_stats
GROUP BY species
ORDER BY species;
-- sgl.query(BigLandings, many)
SELECT id, boat, kilos
FROM landings
PREWHERE species = {species:String}
WHERE kilos >= {min_kilos:UInt32}
ORDER BY id;
example.go
package main
import (
"context"
"database/sql"
"fmt"
"io"
"time"
"seagull.sh/seagull/examples/clickhouse/034_clickhouse_mergetree/internal/store"
)
// run keeps a fishing harbour's records in four tables, one for each way a MergeTree
// engine treats rows that share a sorting key. Every INSERT writes a new part, and
// ClickHouse merges parts in the background at a time of its choosing:
//
// - berths is a ReplacingMergeTree(version): a merge keeps only the row with the
// highest version for each boat, so moving a boat is an INSERT, not an UPDATE. FINAL
// applies that merge as the query reads, so CurrentBerths sees one row a boat
// straight away;
// - daily_catch is a SummingMergeTree(kilos): a merge adds up kilos across rows with
// the same day and species, and FINAL again does it at read time;
// - species_stats is an AggregatingMergeTree whose columns hold partial aggregates.
// RollUpDay stores sumState and maxState for one day's landings; SpeciesStats
// combines every day's states with sumMerge and maxMerge;
// - landings is a plain MergeTree, and BigLandings filters it with PREWHERE, which
// reads the species column first and only then the rest of the rows that match.
//
// Seagull reads each engine, FINAL and PREWHERE from the SQL and types the results as
// usual. A -Merge combinator returns what its function would: the kilos column holds
// AggregateFunction(sum, UInt64) states, so sumMerge gives a UInt64, and heaviest holds
// AggregateFunction(max, UInt32) states, so maxMerge gives a UInt32. Neither is NULL, so
// Kilos is a uint64 and Heaviest a uint32.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each table's view.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
queries := store.New(database)
moves := []store.MoveBoatParams{
{Boat: "Kittiwake", Berth: "north quay", Version: 1},
{Boat: "Salty Chip", Berth: "slipway", Version: 1},
{Boat: "Kittiwake", Berth: "fish market", Version: 2},
{Boat: "Gull Wing", Berth: "north quay", Version: 1},
{Boat: "Kittiwake", Berth: "outer harbour", Version: 3},
}
for _, move := range moves {
if err := queries.MoveBoat(ctx, move); err != nil {
return fmt.Errorf("moving %s: %w", move.Boat, err)
}
}
berths, err := queries.CurrentBerths(ctx)
if err != nil {
return fmt.Errorf("reading berths: %w", err)
}
fmt.Fprintln(output, "berths, latest version of each boat:")
for _, berth := range berths {
fmt.Fprintf(output, " %-10s %-13s v%d\n", berth.Boat, berth.Berth, berth.Version)
}
landings := []store.LandCatchParams{
{ID: 1, Day: day(1), Boat: "Kittiwake", Species: "mackerel", Kilos: 120},
{ID: 2, Day: day(1), Boat: "Salty Chip", Species: "crab", Kilos: 30},
{ID: 3, Day: day(1), Boat: "Gull Wing", Species: "mackerel", Kilos: 80},
{ID: 4, Day: day(2), Boat: "Kittiwake", Species: "mackerel", Kilos: 95},
{ID: 5, Day: day(2), Boat: "Kittiwake", Species: "pollock", Kilos: 45},
{ID: 6, Day: day(2), Boat: "Salty Chip", Species: "crab", Kilos: 25},
}
for _, landing := range landings {
if err := queries.LandCatch(ctx, landing); err != nil {
return fmt.Errorf("landing catch %d: %w", landing.ID, err)
}
tally := store.TallyCatchParams{Day: landing.Day, Species: landing.Species, Kilos: uint64(landing.Kilos)}
if err := queries.TallyCatch(ctx, tally); err != nil {
return fmt.Errorf("tallying catch %d: %w", landing.ID, err)
}
}
daily, err := queries.DailyCatch(ctx)
if err != nil {
return fmt.Errorf("reading the daily catch: %w", err)
}
fmt.Fprintln(output, "daily catch, summed by day and species:")
for _, row := range daily {
fmt.Fprintf(output, " %s %-9s %3d kg\n", row.Day.Format(time.DateOnly), row.Species, row.Kilos)
}
for _, date := range []int{1, 2} {
if err := queries.RollUpDay(ctx, day(date)); err != nil {
return fmt.Errorf("rolling up day %d: %w", date, err)
}
}
stats, err := queries.SpeciesStats(ctx)
if err != nil {
return fmt.Errorf("reading species stats: %w", err)
}
fmt.Fprintln(output, "species stats, merged from both days' states:")
for _, row := range stats {
fmt.Fprintf(output, " %-9s %3d kg in all, heaviest landing %d kg\n", row.Species, row.Kilos, row.Heaviest)
}
big, err := queries.BigLandings(ctx, store.BigLandingsParams{Species: "mackerel", MinKilos: 90})
if err != nil {
return fmt.Errorf("finding big landings: %w", err)
}
fmt.Fprintln(output, "mackerel landings of 90 kg or more:")
for _, landing := range big {
fmt.Fprintf(output, " #%d %-10s %d kg\n", landing.ID, landing.Boat, landing.Kilos)
}
return nil
}
// day returns a date in August 2026, at midnight UTC. A Date parameter binds the calendar
// date a time has in its own location.
//
// Takes date (int) which is the day of the month.
//
// Returns time.Time which is the date.
func day(date int) time.Time {
return time.Date(2026, time.August, date, 0, 0, 0, 0, time.UTC)
}
main.go
// Command 034_clickhouse_mergetree applies its migrations to a ClickHouse database, then
// runs the example in example.go: a harbour's berths, catches and species totals are kept
// in ReplacingMergeTree, SummingMergeTree and AggregatingMergeTree tables and read with
// FINAL and PREWHERE.
//
// Run it with `go run .` against a local server, or `go run . -dsn <dsn>` to choose the
// database; give it an empty one.
package main
import (
"context"
"database/sql"
"embed"
"flag"
"fmt"
"log"
"os"
_ "github.com/ClickHouse/clickhouse-go/v2"
"seagull.sh/seagull/sdk/migrate"
"seagull.sh/seagull/sdk/migrate/migrate_backend_clickhouse"
)
// migrations holds the migration files, compiled into the binary.
//
//go:embed db/migrations
var migrations embed.FS
func main() {
dsn := flag.String("dsn", "clickhouse://localhost:9000/default", "ClickHouse DSN of an empty database")
flag.Parse()
if err := execute(context.Background(), *dsn); err != nil {
log.Fatal(err)
}
}
// execute opens and migrates the database the DSN names, and runs the example against it.
//
// Takes dsn (string) which names the ClickHouse database.
//
// Returns error when the database cannot be prepared or the example fails.
func execute(ctx context.Context, dsn string) error {
database, err := sql.Open("clickhouse", dsn)
if err != nil {
return err
}
defer func() { _ = database.Close() }()
migrator, err := migrate.NewMigrator(database, migrate_backend_clickhouse.New(), migrations, "db/migrations")
if err != nil {
return err
}
if _, err := migrator.Up(ctx); err != nil {
return fmt.Errorf("migrating: %w", err)
}
return run(ctx, database, os.Stdout)
}
internal/store/clickhouse_values.go
// Code generated by sgl. DO NOT EDIT.
package store
import (
"errors"
"fmt"
"math"
"net"
"net/netip"
"reflect"
"slices"
"strconv"
"strings"
"time"
)
const (
sglClickHouseMaxDepth = 32
sglClickHouseNanosecondDigits = 9
sglClickHouseDigitGroup = 3
)
var (
sglClickHouseTextEscaper = strings.NewReplacer("\\", "\\\\", "\t", "\\t", "\n", "\\n", "\r", "\\r", "\x00", "\\0")
sglClickHouseQuotedEscaper = strings.NewReplacer("\\", "\\\\", "'", "\\'", "\t", "\\t", "\n", "\\n", "\r", "\\r", "\x00", "\\0")
sglClickHouseRefusalCleaner = strings.NewReplacer("\t", " ", "\n", " ", "\r", " ")
errSglClickHouseShape = errors.New("sgl: malformed ClickHouse parameter shape")
errSglClickHouseValue = errors.New("sgl: cannot write the value of a ClickHouse parameter")
)
type sglClickHouseShape struct {
names []string
elements []sglClickHouseShape
digits int
prepared bool
checked bool
driverMap bool
kind byte
}
var sglClickHouseShapes = []sglClickHouseShape{{kind: 113, digits: -1, prepared: true, checked: false, driverMap: false, names: []string{}, elements: []sglClickHouseShape{}}, {kind: 110, digits: -1, prepared: true, checked: false, driverMap: false, names: []string{}, elements: []sglClickHouseShape{}}, {kind: 100, digits: -1, prepared: true, checked: false, driverMap: false, names: []string{}, elements: []sglClickHouseShape{}}}
func sglClickHouseValue(value any, shape string) string {
parsed, err := sglClickHouseParseShape(shape)
if err == nil {
return sglClickHouseShapedValue(value, parsed)
}
return sglClickHouseRefusalCleaner.Replace(err.Error()) + "\t"
}
func sglClickHouseShapedValue(value any, shape sglClickHouseShape) string {
text, err := sglClickHouseWhole(reflect.ValueOf(value), shape)
if err != nil {
return sglClickHouseRefusalCleaner.Replace(err.Error()) + "\t"
}
return text
}
func sglClickHouseWhole(value reflect.Value, shape sglClickHouseShape) (string, error) {
current, err := sglClickHouseFollow(value, 0)
if err != nil {
return "", err
}
if !current.IsValid() {
return "\\N", nil
}
shape = sglClickHouseResolve(current, shape)
switch shape.kind {
case '[', '{', '(':
return sglClickHouseComposite(current, shape, 0)
}
text, err := sglClickHouseScalar(current, shape)
if err != nil {
return "", err
}
return sglClickHouseTextEscaper.Replace(text), nil
}
func sglClickHouseAppendElement(text []byte, value reflect.Value, shape sglClickHouseShape, depth int) ([]byte, error) {
current, err := sglClickHouseFollow(value, depth)
if err != nil {
return nil, err
}
if !current.IsValid() {
return append(text, "NULL"...), nil
}
shape = sglClickHouseResolve(current, shape)
switch shape.kind {
case '[', '{', '(':
return sglClickHouseAppendComposite(text, current, shape, depth)
}
if shape.kind == 'n' && sglClickHouseIsNumber(current) {
return sglClickHouseAppendNumber(text, current), nil
}
scalar, err := sglClickHouseScalar(current, shape)
if err != nil {
return nil, err
}
if shape.kind == 'n' {
return append(text, scalar...), nil
}
text = append(text, '\'')
text = append(text, sglClickHouseQuotedEscaper.Replace(scalar)...)
return append(text, '\''), nil
}
func sglClickHouseAppendNumber(text []byte, value reflect.Value) []byte {
const decimalBase = 10
switch value.Kind() {
case reflect.Bool:
return strconv.AppendBool(text, value.Bool())
case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
return strconv.AppendInt(text, value.Int(), decimalBase)
case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64, reflect.Uintptr:
return strconv.AppendUint(text, value.Uint(), decimalBase)
}
number := value.Float()
switch {
case math.IsNaN(number):
return append(text, "nan"...)
case math.IsInf(number, 1):
return append(text, "inf"...)
case math.IsInf(number, -1):
return append(text, "-inf"...)
}
return strconv.AppendFloat(text, number, 'g', -1, value.Type().Bits())
}
func sglClickHouseFollow(value reflect.Value, depth int) (reflect.Value, error) {
for value.IsValid() && (value.Kind() == reflect.Pointer || value.Kind() == reflect.Interface) {
if value.IsNil() {
return reflect.Value{}, nil
}
depth++
if depth > sglClickHouseMaxDepth {
return reflect.Value{}, fmt.Errorf("%w: it nests more than %d levels deep or refers to itself", errSglClickHouseValue, sglClickHouseMaxDepth)
}
value = value.Elem()
}
return value, nil
}
func sglClickHouseResolve(value reflect.Value, shape sglClickHouseShape) sglClickHouseShape {
if shape.kind != 'a' {
return shape
}
unknown := sglClickHouseShape{names: nil, elements: nil, digits: -1, kind: 'a', checked: false, driverMap: false, prepared: false}
if _, moment := sglClickHouseMoment(value); moment {
return sglClickHouseShape{names: nil, elements: nil, digits: -1, kind: 't', checked: false, driverMap: false, prepared: false}
}
if sglClickHouseIsNumber(value) {
return sglClickHouseShape{names: nil, elements: nil, digits: -1, kind: 'n', checked: false, driverMap: false, prepared: false}
}
if sglClickHouseHasText(value) {
return sglClickHouseShape{names: nil, elements: nil, digits: -1, kind: 'q', checked: false, driverMap: false, prepared: false}
}
switch value.Kind() {
case reflect.Slice, reflect.Array:
if value.Type().Elem().Kind() != reflect.Uint8 {
return sglClickHouseShape{names: nil, elements: []sglClickHouseShape{unknown}, digits: -1, kind: '[', checked: false, driverMap: false, prepared: false}
}
case reflect.Map:
return sglClickHouseShape{names: nil, elements: []sglClickHouseShape{unknown, unknown}, digits: -1, kind: '{', checked: false, driverMap: false, prepared: false}
case reflect.Struct:
fields := make([]sglClickHouseShape, value.NumField())
for index := range fields {
fields[index] = unknown
}
return sglClickHouseShape{names: nil, elements: fields, digits: -1, kind: '(', checked: false, driverMap: false, prepared: false}
}
return sglClickHouseShape{names: nil, elements: nil, digits: -1, kind: 'q', checked: false, driverMap: false, prepared: false}
}
func sglClickHouseComposite(value reflect.Value, shape sglClickHouseShape, depth int) (string, error) {
const initialCapacity = 64
text, err := sglClickHouseAppendComposite(make([]byte, 0, initialCapacity), value, shape, depth)
return string(text), err
}
func sglClickHouseAppendComposite(text []byte, value reflect.Value, shape sglClickHouseShape, depth int) ([]byte, error) {
if depth >= sglClickHouseMaxDepth {
return nil, fmt.Errorf("%w: it nests more than %d levels deep or refers to itself", errSglClickHouseValue, sglClickHouseMaxDepth)
}
depth++
switch shape.kind {
case '[':
return sglClickHouseAppendArray(text, value, shape, depth)
case '{':
return sglClickHouseAppendMap(text, value, shape, depth)
default:
return sglClickHouseAppendTuple(text, value, shape, depth)
}
}
func sglClickHouseAppendArray(text []byte, value reflect.Value, shape sglClickHouseShape, depth int) ([]byte, error) {
if value.Kind() != reflect.Slice && value.Kind() != reflect.Array {
return nil, fmt.Errorf("%w: an array needs a slice or an array, not %s", errSglClickHouseValue, value.Type())
}
text = append(text, '[')
for index := 0; index < value.Len(); index++ {
if index > 0 {
text = append(text, ',')
}
var err error
text, err = sglClickHouseAppendElement(text, value.Index(index), shape.elements[0], depth)
if err != nil {
return nil, err
}
}
return append(text, ']'), nil
}
func sglClickHouseAppendMap(text []byte, value reflect.Value, shape sglClickHouseShape, depth int) ([]byte, error) {
if value.Kind() != reflect.Map {
return nil, fmt.Errorf("%w: a map needs a Go map, not %s", errSglClickHouseValue, value.Type())
}
parts := make([]string, 0, value.Len())
entries := value.MapRange()
for entries.Next() {
part, err := sglClickHouseAppendElement(nil, entries.Key(), shape.elements[0], depth)
if err != nil {
return nil, err
}
part = append(part, ':')
part, err = sglClickHouseAppendElement(part, entries.Value(), shape.elements[1], depth)
if err != nil {
return nil, err
}
parts = append(parts, string(part))
}
slices.Sort(parts)
text = append(text, '{')
for index, part := range parts {
if index > 0 {
text = append(text, ',')
}
text = append(text, part...)
}
return append(text, '}'), nil
}
func sglClickHouseAppendTuple(text []byte, value reflect.Value, shape sglClickHouseShape, depth int) ([]byte, error) {
elements, err := sglClickHouseTupleElements(value, shape)
if err != nil {
return nil, err
}
text = append(text, '(')
for index, element := range elements {
if index > 0 {
text = append(text, ", "...)
}
text, err = sglClickHouseAppendElement(text, element, shape.elements[index], depth)
if err != nil {
return nil, err
}
}
return append(text, ')'), nil
}
func sglClickHouseTupleElements(value reflect.Value, shape sglClickHouseShape) ([]reflect.Value, error) {
var elements []reflect.Value
switch value.Kind() {
case reflect.Struct:
for index := range value.NumField() {
if !value.Type().Field(index).IsExported() {
return nil, fmt.Errorf("%w: a tuple cannot be read from %s, which has unexported fields", errSglClickHouseValue, value.Type())
}
elements = append(elements, value.Field(index))
}
case reflect.Map:
if value.Type().Key().Kind() != reflect.String || slices.Contains(shape.names, "") {
return nil, fmt.Errorf("%w: a tuple is read from a map only by field names", errSglClickHouseValue)
}
for _, name := range shape.names {
elements = append(elements, value.MapIndex(reflect.ValueOf(name).Convert(value.Type().Key())))
}
case reflect.Slice, reflect.Array:
for index := range value.Len() {
elements = append(elements, value.Index(index))
}
default:
return nil, fmt.Errorf("%w: a tuple needs a struct, a map or a slice, not %s", errSglClickHouseValue, value.Type())
}
if len(elements) != len(shape.elements) {
return nil, fmt.Errorf("%w: the tuple has %d fields but the value holds %d", errSglClickHouseValue, len(shape.elements), len(elements))
}
return elements, nil
}
func sglClickHouseScalar(value reflect.Value, shape sglClickHouseShape) (string, error) {
if shape.kind == '4' || shape.kind == '6' {
address, isAddress, failure := sglClickHouseAddress(value, shape.kind)
if failure != "" {
return "", fmt.Errorf("%w: %s", errSglClickHouseValue, failure)
}
if isAddress {
return address.String(), nil
}
}
if moment, ok := sglClickHouseMoment(value); ok {
return sglClickHouseTime(moment, shape)
}
if shape.kind == 'n' && sglClickHouseIsNumber(value) {
return sglClickHouseNumber(value), nil
}
text, err := sglClickHouseText(value)
if err == nil && shape.kind == 'n' && !sglClickHouseIsNumberText(text) {
err = fmt.Errorf("%w: %q is not a number", errSglClickHouseValue, text)
}
return text, err
}
func sglClickHouseAddress(value reflect.Value, kind byte) (netip.Addr, bool, string) {
var address netip.Addr
switch value.Type() {
case reflect.TypeFor[netip.Addr]():
reflect.ValueOf(&address).Elem().Set(value)
if !address.IsValid() {
return address, true, "the zero netip.Addr holds no address"
}
case reflect.TypeFor[net.IP]():
bytes := value.Bytes()
if len(bytes) == 0 {
return address, true, "an empty net.IP holds no address"
}
parsed, valid := netip.AddrFromSlice(bytes)
if !valid {
return address, true, fmt.Sprintf("a net.IP of %d bytes is not an IP address", len(bytes))
}
address = parsed
default:
return address, false, ""
}
address = address.WithZone("")
if kind != '4' {
return address, true, ""
}
if address.Is6() && !address.Is4In6() {
return address, true, address.String() + " is an IPv6 address, which an IPv4 value cannot hold"
}
return address.Unmap(), true, ""
}
func sglClickHouseMoment(value reflect.Value) (time.Time, bool) {
return reflect.TypeAssert[time.Time](value)
}
func sglClickHouseTime(moment time.Time, shape sglClickHouseShape) (string, error) {
switch shape.kind {
case 'd':
return moment.Format("2006-01-02"), nil
case 't':
return sglClickHouseEpoch(moment, shape.digits), nil
case 'q':
return moment.Format(time.RFC3339Nano), nil
default:
return "", fmt.Errorf("%w: a time.Time cannot be written as a number", errSglClickHouseValue)
}
}
func sglClickHouseEpoch(moment time.Time, digits int) string {
const decimalBase = 10
nanoseconds := int64(moment.Nanosecond())
if digits < 0 {
digits = sglClickHouseNanosecondDigits
for digits > 0 && nanoseconds%int64(math.Pow10(sglClickHouseNanosecondDigits-digits+sglClickHouseDigitGroup)) == 0 {
digits -= sglClickHouseDigitGroup
}
}
seconds := moment.Unix()
if digits == 0 {
return strconv.FormatInt(seconds, decimalBase)
}
scale := int64(math.Pow10(digits))
fraction := nanoseconds / int64(math.Pow10(sglClickHouseNanosecondDigits-digits))
sign := ""
if seconds < 0 {
sign = "-"
seconds = -seconds
if fraction > 0 {
seconds--
fraction = scale - fraction
}
}
return fmt.Sprintf("%s%d.%0*d", sign, seconds, digits, fraction)
}
func sglClickHouseIsNumber(value reflect.Value) bool {
switch value.Kind() {
case reflect.Bool, reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64,
reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64, reflect.Uintptr,
reflect.Float32, reflect.Float64:
return true
}
return false
}
func sglClickHouseNumber(value reflect.Value) string {
const decimalBase = 10
switch value.Kind() {
case reflect.Bool:
return strconv.FormatBool(value.Bool())
case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
return strconv.FormatInt(value.Int(), decimalBase)
case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64, reflect.Uintptr:
return strconv.FormatUint(value.Uint(), decimalBase)
}
number := value.Float()
switch {
case math.IsNaN(number):
return "nan"
case math.IsInf(number, 1):
return "inf"
case math.IsInf(number, -1):
return "-inf"
}
return strconv.FormatFloat(number, 'g', -1, value.Type().Bits())
}
func sglClickHouseMethods(value reflect.Value) []any {
if value.CanAddr() {
return []any{value.Interface(), value.Addr().Interface()}
}
return []any{value.Interface()}
}
func sglClickHouseHasText(value reflect.Value) bool {
for _, candidate := range sglClickHouseMethods(value) {
switch candidate.(type) {
case fmt.Stringer, interface{ String() (string, error) }, error:
return true
}
}
return false
}
func sglClickHouseText(value reflect.Value) (string, error) {
for _, candidate := range sglClickHouseMethods(value) {
switch typed := candidate.(type) {
case fmt.Stringer:
return typed.String(), nil
case interface{ String() (string, error) }:
text, err := typed.String()
if err != nil {
return "", fmt.Errorf("%w: its String method failed: %w", errSglClickHouseValue, err)
}
return text, nil
case error:
return "", fmt.Errorf("%w: an error is not a value", errSglClickHouseValue)
}
}
switch value.Kind() {
case reflect.String:
return value.String(), nil
case reflect.Slice, reflect.Array:
if value.Type().Elem().Kind() == reflect.Uint8 {
bytes := make([]byte, value.Len())
reflect.Copy(reflect.ValueOf(bytes), value)
return string(bytes), nil
}
}
if sglClickHouseIsNumber(value) {
return sglClickHouseNumber(value), nil
}
return "", fmt.Errorf("%w: a %s cannot be written as text", errSglClickHouseValue, value.Type())
}
func sglClickHouseIsNumberText(text string) bool {
if text == "true" || text == "false" {
return true
}
lower := strings.ToLower(sglClickHouseUnsigned(text))
if lower == "nan" || lower == "inf" {
return true
}
mantissa, exponent, scientific := strings.Cut(lower, "e")
if scientific {
exponent = sglClickHouseUnsigned(exponent)
if exponent == "" || strings.Trim(exponent, "0123456789") != "" {
return false
}
}
whole, fraction, _ := strings.Cut(mantissa, ".")
return whole+fraction != "" && strings.Trim(whole+fraction, "0123456789") == ""
}
func sglClickHouseUnsigned(text string) string {
if strings.HasPrefix(text, "+") || strings.HasPrefix(text, "-") {
return text[1:]
}
return text
}
func sglClickHouseParseShape(text string) (sglClickHouseShape, error) {
shape, rest, err := sglClickHouseReadShape(text, 0)
if err == nil && rest != "" {
err = fmt.Errorf("%w: %q has %q left over", errSglClickHouseShape, text, rest)
}
return shape, err
}
func sglClickHouseReadShape(text string, depth int) (sglClickHouseShape, string, error) {
shape := sglClickHouseShape{names: nil, elements: nil, digits: -1, kind: 0, checked: false, driverMap: false, prepared: false}
if text == "" || depth > sglClickHouseMaxDepth {
return shape, "", fmt.Errorf("%w: a shape is missing or nests too deeply", errSglClickHouseShape)
}
shape.kind, text = text[0], text[1:]
switch shape.kind {
case 'q', 'n', 'd', 'a', '4', '6':
return shape, text, nil
case 't':
shape.digits = 0
if text != "" && text[0] >= '0' && text[0] <= '9' {
shape.digits, text = int(text[0]-'0'), text[1:]
}
return shape, text, nil
case '[':
return sglClickHouseReadElements(shape, text, depth, ']')
case '{':
return sglClickHouseReadElements(shape, text, depth, '}')
case '(':
return sglClickHouseReadElements(shape, text, depth, ')')
}
return shape, "", fmt.Errorf("%w: %q is not a shape", errSglClickHouseShape, shape.kind)
}
func sglClickHouseReadElements(shape sglClickHouseShape, text string, depth int, closing byte) (sglClickHouseShape, string, error) {
for {
var err error
if shape, text, err = sglClickHouseReadElement(shape, text, depth); err != nil {
return shape, "", err
}
if text == "" {
return shape, "", fmt.Errorf("%w: %q is not closed", errSglClickHouseShape, string(shape.kind))
}
switch {
case text[0] == closing && (shape.kind != '{' || len(shape.elements) == 2):
return shape, text[1:], nil
case shape.kind == '(' && text[0] == ',', shape.kind == '{' && text[0] == ':' && len(shape.elements) == 1:
text = text[1:]
default:
return shape, "", fmt.Errorf("%w: %q is out of place", errSglClickHouseShape, text[:1])
}
}
}
func sglClickHouseReadElement(shape sglClickHouseShape, text string, depth int) (sglClickHouseShape, string, error) {
name := ""
if shape.kind == '(' && strings.HasPrefix(text, "'") {
var err error
if name, text, err = sglClickHouseReadName(text); err != nil {
return shape, "", err
}
}
element, rest, err := sglClickHouseReadShape(text, depth+1)
if err != nil {
return shape, "", err
}
shape.elements = append(shape.elements, element)
shape.names = append(shape.names, name)
return shape, rest, nil
}
func sglClickHouseReadName(text string) (name string, rest string, err error) {
var written strings.Builder
index := 1
for index < len(text) {
if text[index] != '\'' {
written.WriteByte(text[index])
index++
continue
}
if index+1 < len(text) && text[index+1] == '\'' {
written.WriteByte('\'')
index += 2
continue
}
if index+1 < len(text) && text[index+1] == '=' {
return written.String(), text[index+2:], nil
}
break
}
return "", "", fmt.Errorf("%w: a tuple field's name is not closed by '=", errSglClickHouseShape)
}
internal/store/harbour.sql.go
// Code generated by sgl. DO NOT EDIT.
package store
import (
"context"
"time"
"github.com/ClickHouse/clickhouse-go/v2"
)
const bigLandingsSQL = `SELECT id, boat, kilos
FROM landings
PREWHERE species = {p_species:String}
WHERE kilos >= {p_min_kilos:UInt32}
ORDER BY id;`
type BigLandingsParams struct {
Species string
MinKilos uint32
}
type BigLandingsRow struct {
ID uint64 `json:"id"`
Boat string `json:"boat"`
Kilos uint32 `json:"kilos"`
}
func (queries *Queries) BigLandings(ctx context.Context, params BigLandingsParams) ([]BigLandingsRow, error) {
rows, err := queries.reader.QueryContext(ctx, bigLandingsSQL, clickhouse.Named("p_species", sglClickHouseShapedValue(params.Species, sglClickHouseShapes[0])), clickhouse.Named("p_min_kilos", sglClickHouseShapedValue(params.MinKilos, sglClickHouseShapes[1])))
if err != nil {
return nil, err
}
defer rows.Close()
var results []BigLandingsRow
if rows.Next() {
var row BigLandingsRow
scanArgs := []any{&row.ID, &row.Boat, &row.Kilos}
for more := true; more; more = rows.Next() {
row = BigLandingsRow{}
if err := rows.Scan(scanArgs...); err != nil {
return nil, err
}
results = append(results, row)
}
}
if err := rows.Err(); err != nil {
return nil, err
}
return results, nil
}
const currentBerthsSQL = `SELECT boat, berth, version FROM berths FINAL ORDER BY boat;`
type CurrentBerthsRow struct {
Boat string `json:"boat"`
Berth string `json:"berth"`
Version uint32 `json:"version"`
}
func (queries *Queries) CurrentBerths(ctx context.Context) ([]CurrentBerthsRow, error) {
rows, err := queries.reader.QueryContext(ctx, currentBerthsSQL)
if err != nil {
return nil, err
}
defer rows.Close()
var results []CurrentBerthsRow
if rows.Next() {
var row CurrentBerthsRow
scanArgs := []any{&row.Boat, &row.Berth, &row.Version}
for more := true; more; more = rows.Next() {
row = CurrentBerthsRow{}
if err := rows.Scan(scanArgs...); err != nil {
return nil, err
}
results = append(results, row)
}
}
if err := rows.Err(); err != nil {
return nil, err
}
return results, nil
}
const dailyCatchSQL = `SELECT day, species, kilos FROM daily_catch FINAL ORDER BY day, species;`
type DailyCatchRow struct {
Day time.Time `json:"day"`
Species string `json:"species"`
Kilos uint64 `json:"kilos"`
}
func (queries *Queries) DailyCatch(ctx context.Context) ([]DailyCatchRow, error) {
rows, err := queries.reader.QueryContext(ctx, dailyCatchSQL)
if err != nil {
return nil, err
}
defer rows.Close()
var results []DailyCatchRow
if rows.Next() {
var row DailyCatchRow
scanArgs := []any{&row.Day, &row.Species, &row.Kilos}
for more := true; more; more = rows.Next() {
row = DailyCatchRow{}
if err := rows.Scan(scanArgs...); err != nil {
return nil, err
}
results = append(results, row)
}
}
if err := rows.Err(); err != nil {
return nil, err
}
return results, nil
}
const landCatchSQL = `INSERT INTO landings (id, day, boat, species, kilos)
VALUES ({p_id:UInt64}, {p_day:Date}, {p_boat:String}, {p_species:String}, {p_kilos:UInt32});`
type LandCatchParams struct {
ID uint64
Day time.Time
Boat string
Species string
Kilos uint32
}
func (queries *Queries) LandCatch(ctx context.Context, params LandCatchParams) error {
_, err := queries.writer.ExecContext(ctx, landCatchSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(params.ID, sglClickHouseShapes[1])), clickhouse.Named("p_day", sglClickHouseShapedValue(params.Day, sglClickHouseShapes[2])), clickhouse.Named("p_boat", sglClickHouseShapedValue(params.Boat, sglClickHouseShapes[0])), clickhouse.Named("p_species", sglClickHouseShapedValue(params.Species, sglClickHouseShapes[0])), clickhouse.Named("p_kilos", sglClickHouseShapedValue(params.Kilos, sglClickHouseShapes[1])))
return err
}
const moveBoatSQL = `INSERT INTO berths (boat, berth, version) VALUES ({p_boat:String}, {p_berth:String}, {p_version:UInt32});`
type MoveBoatParams struct {
Boat string
Berth string
Version uint32
}
func (queries *Queries) MoveBoat(ctx context.Context, params MoveBoatParams) error {
_, err := queries.writer.ExecContext(ctx, moveBoatSQL, clickhouse.Named("p_boat", sglClickHouseShapedValue(params.Boat, sglClickHouseShapes[0])), clickhouse.Named("p_berth", sglClickHouseShapedValue(params.Berth, sglClickHouseShapes[0])), clickhouse.Named("p_version", sglClickHouseShapedValue(params.Version, sglClickHouseShapes[1])))
return err
}
const rollUpDaySQL = `INSERT INTO species_stats
SELECT species, sumState(toUInt64(kilos)), maxState(kilos)
FROM landings
WHERE day = {p_day:Date}
GROUP BY species;`
func (queries *Queries) RollUpDay(ctx context.Context, day time.Time) error {
_, err := queries.writer.ExecContext(ctx, rollUpDaySQL, clickhouse.Named("p_day", sglClickHouseShapedValue(day, sglClickHouseShapes[2])))
return err
}
const speciesStatsSQL = `SELECT species, sumMerge(kilos) AS kilos, maxMerge(heaviest) AS heaviest
FROM species_stats
GROUP BY species
ORDER BY species;`
type SpeciesStatsRow struct {
Species string `json:"species"`
Kilos uint64 `json:"kilos"`
Heaviest uint32 `json:"heaviest"`
}
func (queries *Queries) SpeciesStats(ctx context.Context) ([]SpeciesStatsRow, error) {
rows, err := queries.reader.QueryContext(ctx, speciesStatsSQL)
if err != nil {
return nil, err
}
defer rows.Close()
var results []SpeciesStatsRow
if rows.Next() {
var row SpeciesStatsRow
scanArgs := []any{&row.Species, &row.Kilos, &row.Heaviest}
for more := true; more; more = rows.Next() {
row = SpeciesStatsRow{}
if err := rows.Scan(scanArgs...); err != nil {
return nil, err
}
results = append(results, row)
}
}
if err := rows.Err(); err != nil {
return nil, err
}
return results, nil
}
const tallyCatchSQL = `INSERT INTO daily_catch (day, species, kilos) VALUES ({p_day:Date}, {p_species:String}, {p_kilos:UInt64});`
type TallyCatchParams struct {
Day time.Time
Species string
Kilos uint64
}
func (queries *Queries) TallyCatch(ctx context.Context, params TallyCatchParams) error {
_, err := queries.writer.ExecContext(ctx, tallyCatchSQL, clickhouse.Named("p_day", sglClickHouseShapedValue(params.Day, sglClickHouseShapes[2])), clickhouse.Named("p_species", sglClickHouseShapedValue(params.Species, sglClickHouseShapes[0])), clickhouse.Named("p_kilos", sglClickHouseShapedValue(params.Kilos, sglClickHouseShapes[1])))
return err
}
internal/store/models.go
// Code generated by sgl. DO NOT EDIT.
package store
import "time"
type Berths struct {
Boat string `json:"boat"`
Berth string `json:"berth"`
Version uint32 `json:"version"`
}
type DailyCatch struct {
Day time.Time `json:"day"`
Species string `json:"species"`
Kilos uint64 `json:"kilos"`
}
type Landings struct {
ID uint64 `json:"id"`
Day time.Time `json:"day"`
Boat string `json:"boat"`
Species string `json:"species"`
Kilos uint32 `json:"kilos"`
}
type SpeciesStats struct {
Species string `json:"species"`
Kilos []byte `json:"kilos"`
Heaviest []byte `json:"heaviest"`
}
internal/store/querier.go
// Code generated by sgl. DO NOT EDIT.
package store
import (
"context"
"database/sql"
)
type DBTX interface {
ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}
type dbtxWrapper interface {
WrapDBTX(inner any) any
}
type Queries struct {
reader DBTX
writer DBTX
wrapper dbtxWrapper
}
func New(db DBTX) *Queries {
wrapper, _ := db.(dbtxWrapper)
return &Queries{reader: db, writer: db, wrapper: wrapper}
}
func NewWithReplica(writer DBTX, reader DBTX) *Queries {
wrapper, _ := writer.(dbtxWrapper)
return &Queries{reader: reader, writer: writer, wrapper: wrapper}
}
func (queries *Queries) WithTx(transaction *sql.Tx) *Queries {
if queries == nil {
return &Queries{reader: transaction, writer: transaction, wrapper: nil}
}
db := queries.wrapDBTX(transaction)
return &Queries{reader: db, writer: db, wrapper: queries.wrapper}
}
func (queries *Queries) wrapDBTX(db DBTX) DBTX {
if queries.wrapper == nil {
return db
}
wrapped, ok := queries.wrapper.WrapDBTX(db).(DBTX)
if !ok {
return db
}
return wrapped
}
func (queries *Queries) RunInTx(ctx context.Context, db *sql.DB, fn func(*Queries) error) error {
transaction, err := db.BeginTx(ctx, nil)
if err != nil {
return err
}
defer transaction.Rollback()
if err = fn(queries.WithTx(transaction)); err != nil {
return err
}
return transaction.Commit()
}
output.txt
berths, latest version of each boat:
Gull Wing north quay v1
Kittiwake outer harbour v3
Salty Chip slipway v1
daily catch, summed by day and species:
2026-08-01 crab 30 kg
2026-08-01 mackerel 200 kg
2026-08-02 crab 25 kg
2026-08-02 mackerel 95 kg
2026-08-02 pollock 45 kg
species stats, merged from both days' states:
crab 55 kg in all, heaviest landing 30 kg
mackerel 295 kg in all, heaviest landing 120 kg
pollock 45 kg in all, heaviest landing 45 kg
mackerel landings of 90 kg or more:
#1 Kittiwake 120 kg
#4 Kittiwake 95 kg