Compare · 039

MySQL dialect features: MySQL and MariaDB

MySQL and MariaDB run the same files for this example: the SQL and the Go are identical.

Compare MySQL withMariaDB

Configuration

sgl.yamlidentical
MySQL
MariaDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: harbour
4 - name: harbour
5 dialect: mysql
5 dialect: mysql
6 migrations: db/migrations
6 migrations: db/migrations
7 queries: db/queries
7 queries: db/queries
8 go:
8 go:
9 package: store
9 package: store
10 out: internal/store
10 out: internal/store
11 doc_comments: true
11 doc_comments: true

Migrations

db/migrations/0001_harbour.up.sqlidentical
MySQL
MariaDB
1CREATE TABLE boats (
1CREATE TABLE boats (
2 registration CHAR(6) NOT NULL PRIMARY KEY,
2 registration CHAR(6) NOT NULL PRIMARY KEY,
3 name VARCHAR(50) NOT NULL,
3 name VARCHAR(50) NOT NULL,
4 kit SET('radio', 'radar', 'liferaft', 'winch') NOT NULL DEFAULT '' COMMENT 'The equipment on board, any of the SET''s values.',
4 kit SET('radio', 'radar', 'liferaft', 'winch') NOT NULL DEFAULT '' COMMENT 'The equipment on board, any of the SET''s values.',
5 crossings INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'How many times the boat has left the harbour.'
5 crossings INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'How many times the boat has left the harbour.'
6) COMMENT = 'Boats on the harbour register, one per registration.';
6) COMMENT = 'Boats on the harbour register, one per registration.';
7
7
8CREATE TABLE crew (
8CREATE TABLE crew (
9 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
9 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
10 registration CHAR(6) NOT NULL,
10 registration CHAR(6) NOT NULL,
11 name VARCHAR(50) NOT NULL,
11 name VARCHAR(50) NOT NULL,
12 `rank` VARCHAR(20) NOT NULL,
12 `rank` VARCHAR(20) NOT NULL,
13 FOREIGN KEY (registration) REFERENCES boats (registration)
13 FOREIGN KEY (registration) REFERENCES boats (registration)
14);
14);
15
15
16CREATE TABLE berths (
16CREATE TABLE berths (
17 berth TINYINT UNSIGNED NOT NULL PRIMARY KEY,
17 berth TINYINT UNSIGNED NOT NULL PRIMARY KEY,
18 registration CHAR(6) NOT NULL UNIQUE
18 registration CHAR(6) NOT NULL UNIQUE
19);
19);
db/migrations/0001_harbour.down.sqlidentical
MySQL
MariaDB
1DROP TABLE berths;
1DROP TABLE berths;
2DROP TABLE crew;
2DROP TABLE crew;
3DROP TABLE boats;
3DROP TABLE boats;

Queries

RegisterBoatidentical
MySQL · db/queries/harbour.sql
MariaDB · db/queries/harbour.sql
1-- sgl.query(RegisterBoat, execrows)
1-- sgl.query(RegisterBoat, execrows)
2INSERT IGNORE INTO boats (registration, name, kit) VALUES (?, ?, ?);
2INSERT IGNORE INTO boats (registration, name, kit) VALUES (?, ?, ?);
LogCrossingidentical
MySQL · db/queries/harbour.sql
MariaDB · db/queries/harbour.sql
1-- sgl.query(LogCrossing, execrows)
1-- sgl.query(LogCrossing, execrows)
2UPDATE boats SET crossings = crossings + 1 WHERE registration = ?;
2UPDATE boats SET crossings = crossings + 1 WHERE registration = ?;
SignOnidentical
MySQL · db/queries/harbour.sql
MariaDB · db/queries/harbour.sql
1-- sgl.query(SignOn, exec)
1-- sgl.query(SignOn, exec)
2INSERT INTO crew (registration, name, `rank`) VALUES (?, ?, ?);
2INSERT INTO crew (registration, name, `rank`) VALUES (?, ?, ?);
AssignBerthidentical
MySQL · db/queries/harbour.sql
MariaDB · db/queries/harbour.sql
1-- sgl.query(AssignBerth, execrows)
1-- sgl.query(AssignBerth, execrows)
2REPLACE INTO berths (berth, registration) VALUES (?, ?);
2REPLACE INTO berths (berth, registration) VALUES (?, ?);
ListBerthsidentical
MySQL · db/queries/harbour.sql
MariaDB · db/queries/harbour.sql
1-- sgl.query(ListBerths, many)
1-- sgl.query(ListBerths, many)
2SELECT berths.berth, boats.name
2SELECT berths.berth, boats.name
3FROM berths
3FROM berths
4JOIN boats ON boats.registration = berths.registration
4JOIN boats ON boats.registration = berths.registration
5ORDER BY berths.berth;
5ORDER BY berths.berth;
BoatsWithKitidentical
MySQL · db/queries/harbour.sql
MariaDB · db/queries/harbour.sql
1-- sgl.query(BoatsWithKit, many)
1-- sgl.query(BoatsWithKit, many)
2-- ?1 as sgl.param(item, type: text)
2-- ?1 as sgl.param(item, type: text)
3SELECT registration, name, kit FROM boats WHERE FIND_IN_SET(?1, kit) > 0 ORDER BY registration;
3SELECT registration, name, kit FROM boats WHERE FIND_IN_SET(?1, kit) > 0 ORDER BY registration;
Harbouridentical
MySQL · db/queries/harbour.sql
MariaDB · db/queries/harbour.sql
1-- sgl.query(Harbour, many)
1-- sgl.query(Harbour, many)
2SELECT
2SELECT
3 boats.registration,
3 boats.registration,
4 boats.name,
4 boats.name,
5 boats.kit,
5 boats.kit,
6 boats.crossings,
6 boats.crossings,
7 GROUP_CONCAT(CONCAT(crew.name, ' the ', crew.`rank`) ORDER BY crew.id SEPARATOR ', ') AS crew
7 GROUP_CONCAT(CONCAT(crew.name, ' the ', crew.`rank`) ORDER BY crew.id SEPARATOR ', ') AS crew
8FROM boats
8FROM boats
9LEFT JOIN crew ON crew.registration = boats.registration
9LEFT JOIN crew ON crew.registration = boats.registration
10GROUP BY boats.registration
10GROUP BY boats.registration
11ORDER BY boats.registration;
11ORDER BY boats.registration;

What sgl generates

RegisterBoatidentical
MySQL
MariaDB
1func (queries *Queries) RegisterBoat(ctx context.Context, params RegisterBoatParams) (int64, error)
1func (queries *Queries) RegisterBoat(ctx context.Context, params RegisterBoatParams) (int64, error)
2
2
3type RegisterBoatParams struct {
3type RegisterBoatParams struct {
4 Registration string
4 Registration string
5 Name string
5 Name string
6 Kit string
6 Kit string
7}
7}
LogCrossingidentical
MySQL
MariaDB
1func (queries *Queries) LogCrossing(ctx context.Context, registration string) (int64, error)
1func (queries *Queries) LogCrossing(ctx context.Context, registration string) (int64, error)
SignOnidentical
MySQL
MariaDB
1func (queries *Queries) SignOn(ctx context.Context, params SignOnParams) error
1func (queries *Queries) SignOn(ctx context.Context, params SignOnParams) error
2
2
3type SignOnParams struct {
3type SignOnParams struct {
4 Registration string
4 Registration string
5 Name string
5 Name string
6 Rank string
6 Rank string
7}
7}
AssignBerthidentical
MySQL
MariaDB
1func (queries *Queries) AssignBerth(ctx context.Context, params AssignBerthParams) (int64, error)
1func (queries *Queries) AssignBerth(ctx context.Context, params AssignBerthParams) (int64, error)
2
2
3type AssignBerthParams struct {
3type AssignBerthParams struct {
4 Berth uint8
4 Berth uint8
5 Registration string
5 Registration string
6}
6}
ListBerthsidentical
MySQL
MariaDB
1func (queries *Queries) ListBerths(ctx context.Context) ([]ListBerthsRow, error)
1func (queries *Queries) ListBerths(ctx context.Context) ([]ListBerthsRow, error)
2
2
3type ListBerthsRow struct {
3type ListBerthsRow struct {
4 Berth uint8 `json:"berth"`
4 Berth uint8 `json:"berth"`
5 Name string `json:"name"`
5 Name string `json:"name"`
6}
6}
BoatsWithKitidentical
MySQL
MariaDB
1func (queries *Queries) BoatsWithKit(ctx context.Context, item string) ([]BoatsWithKitRow, error)
1func (queries *Queries) BoatsWithKit(ctx context.Context, item string) ([]BoatsWithKitRow, error)
2
2
3type BoatsWithKitRow struct {
3type BoatsWithKitRow struct {
4 Registration string `json:"registration"`
4 Registration string `json:"registration"`
5 Name string `json:"name"`
5 Name string `json:"name"`
6 Kit string `json:"kit"`
6 Kit string `json:"kit"`
7}
7}
Harbouridentical
MySQL
MariaDB
1func (queries *Queries) Harbour(ctx context.Context) ([]HarbourRow, error)
1func (queries *Queries) Harbour(ctx context.Context) ([]HarbourRow, error)
2
2
3type HarbourRow struct {
3type HarbourRow struct {
4 Registration string `json:"registration"`
4 Registration string `json:"registration"`
5 Name string `json:"name"`
5 Name string `json:"name"`
6 Kit string `json:"kit"`
6 Kit string `json:"kit"`
7 Crossings uint32 `json:"crossings"`
7 Crossings uint32 `json:"crossings"`
8 Crew *string `json:"crew"`
8 Crew *string `json:"crew"`
9}
9}

The program

example.goidentical
MySQL
MariaDB
1package main
1package main
2
2
3import (
3import (
4 "context"
4 "context"
5 "database/sql"
5 "database/sql"
6 "fmt"
6 "fmt"
7 "io"
7 "io"
8
8
9 "seagull.sh/seagull/examples/mysql/039_mysql_dialect_features/internal/store"
9 "seagull.sh/seagull/examples/mysql/039_mysql_dialect_features/internal/store"
10)
10)
11
11
12// run keeps a harbour's register with SQL that only MySQL and MariaDB speak, and prints
12// run keeps a harbour's register with SQL that only MySQL and MariaDB speak, and prints
13// what each statement did:
13// what each statement did:
14//
14//
15// - kit is a SET column, which holds any number of the values it lists. It maps to a
15// - kit is a SET column, which holds any number of the values it lists. It maps to a
16// string of the chosen values joined by commas, and MySQL stores them in the order
16// string of the chosen values joined by commas, and MySQL stores them in the order
17// the SET lists them, once each. FIND_IN_SET looks one up;
17// the SET lists them, once each. FIND_IN_SET looks one up;
18// - crossings is INT UNSIGNED, so Crossings is a uint32, and berth is TINYINT UNSIGNED,
18// - crossings is INT UNSIGNED, so Crossings is a uint32, and berth is TINYINT UNSIGNED,
19// so Berth is a uint8;
19// so Berth is a uint8;
20// - RegisterBoat is INSERT IGNORE: a registration already on the books counts 0 rows
20// - RegisterBoat is INSERT IGNORE: a registration already on the books counts 0 rows
21// instead of failing. IGNORE turns other errors into warnings too, so a kit item the
21// instead of failing. IGNORE turns other errors into warnings too, so a kit item the
22// SET does not list is dropped without a word;
22// SET does not list is dropped without a word;
23// - AssignBerth is REPLACE INTO, which deletes every row that clashes with the new one
23// - AssignBerth is REPLACE INTO, which deletes every row that clashes with the new one
24// on any unique key, then inserts it. execrows counts the deleted rows as well;
24// on any unique key, then inserts it. execrows counts the deleted rows as well;
25// - Harbour gathers each boat's crew into one string with GROUP_CONCAT, which is NULL
25// - Harbour gathers each boat's crew into one string with GROUP_CONCAT, which is NULL
26// for a boat with no crew. The crew's `rank` column is quoted with backticks, as RANK
26// for a boat with no crew. The crew's `rank` column is quoted with backticks, as RANK
27// is a reserved word since MySQL 8.0;
27// is a reserved word since MySQL 8.0;
28// - boats documents itself with MySQL's COMMENT, after a column's definition and as the
28// - boats documents itself with MySQL's COMMENT, after a column's definition and as the
29// table's COMMENT = option. sgl.yaml sets go.doc_comments, so they become the doc
29// table's COMMENT = option. sgl.yaml sets go.doc_comments, so they become the doc
30// comments of the Boats model and its Kit and Crossings fields: open internal/store
30// comments of the Boats model and its Kit and Crossings fields: open internal/store
31// to see them.
31// to see them.
32//
32//
33// Takes database (*sql.DB) which is a migrated database.
33// Takes database (*sql.DB) which is a migrated database.
34// Takes output (io.Writer) which receives what each statement did.
34// Takes output (io.Writer) which receives what each statement did.
35//
35//
36// Returns error when a query fails.
36// Returns error when a query fails.
37func run(ctx context.Context, database *sql.DB, output io.Writer) error {
37func run(ctx context.Context, database *sql.DB, output io.Writer) error {
38 queries := store.New(database)
38 queries := store.New(database)
39
39
40 boats := []store.RegisterBoatParams{
40 boats := []store.RegisterBoatParams{
41 {Registration: "PZ1001", Name: "Kittiwake", Kit: "radar,radio,radar"},
41 {Registration: "PZ1001", Name: "Kittiwake", Kit: "radar,radio,radar"},
42 {Registration: "PZ2002", Name: "Salty Chip", Kit: "liferaft"},
42 {Registration: "PZ2002", Name: "Salty Chip", Kit: "liferaft"},
43 {Registration: "PZ1001", Name: "Kittiwake II", Kit: "winch"},
43 {Registration: "PZ1001", Name: "Kittiwake II", Kit: "winch"},
44 {Registration: "PZ3003", Name: "Gull Wing", Kit: "radio,sonar"},
44 {Registration: "PZ3003", Name: "Gull Wing", Kit: "radio,sonar"},
45 }
45 }
46 for _, boat := range boats {
46 for _, boat := range boats {
47 registered, err := queries.RegisterBoat(ctx, boat)
47 registered, err := queries.RegisterBoat(ctx, boat)
48 if err != nil {
48 if err != nil {
49 return fmt.Errorf("registering %s: %w", boat.Name, err)
49 return fmt.Errorf("registering %s: %w", boat.Name, err)
50 }
50 }
51 if registered == 0 {
51 if registered == 0 {
52 fmt.Fprintf(output, "INSERT IGNORE %s is already registered, so %s was ignored\n", boat.Registration, boat.Name)
52 fmt.Fprintf(output, "INSERT IGNORE %s is already registered, so %s was ignored\n", boat.Registration, boat.Name)
53 continue
53 continue
54 }
54 }
55 fmt.Fprintf(output, "INSERT IGNORE registered %s as %s with kit %q\n", boat.Name, boat.Registration, boat.Kit)
55 fmt.Fprintf(output, "INSERT IGNORE registered %s as %s with kit %q\n", boat.Name, boat.Registration, boat.Kit)
56 }
56 }
57
57
58 for _, registration := range []string{"PZ1001", "PZ1001", "PZ3003"} {
58 for _, registration := range []string{"PZ1001", "PZ1001", "PZ3003"} {
59 if _, err := queries.LogCrossing(ctx, registration); err != nil {
59 if _, err := queries.LogCrossing(ctx, registration); err != nil {
60 return fmt.Errorf("logging a crossing for %s: %w", registration, err)
60 return fmt.Errorf("logging a crossing for %s: %w", registration, err)
61 }
61 }
62 }
62 }
63 crew := []store.SignOnParams{
63 crew := []store.SignOnParams{
64 {Registration: "PZ1001", Name: "Morwenna", Rank: "skipper"},
64 {Registration: "PZ1001", Name: "Morwenna", Rank: "skipper"},
65 {Registration: "PZ1001", Name: "Jago", Rank: "deckhand"},
65 {Registration: "PZ1001", Name: "Jago", Rank: "deckhand"},
66 {Registration: "PZ3003", Name: "Kerensa", Rank: "skipper"},
66 {Registration: "PZ3003", Name: "Kerensa", Rank: "skipper"},
67 }
67 }
68 for _, member := range crew {
68 for _, member := range crew {
69 if err := queries.SignOn(ctx, member); err != nil {
69 if err := queries.SignOn(ctx, member); err != nil {
70 return fmt.Errorf("signing on %s: %w", member.Name, err)
70 return fmt.Errorf("signing on %s: %w", member.Name, err)
71 }
71 }
72 }
72 }
73
73
74 // Moving Salty Chip to berth 1 clashes twice: berth 1 is the Kittiwake's, and
74 // Moving Salty Chip to berth 1 clashes twice: berth 1 is the Kittiwake's, and
75 // registration is UNIQUE, so Salty Chip's own berth 2 goes too.
75 // registration is UNIQUE, so Salty Chip's own berth 2 goes too.
76 berths := []store.AssignBerthParams{
76 berths := []store.AssignBerthParams{
77 {Berth: 1, Registration: "PZ1001"},
77 {Berth: 1, Registration: "PZ1001"},
78 {Berth: 2, Registration: "PZ2002"},
78 {Berth: 2, Registration: "PZ2002"},
79 {Berth: 1, Registration: "PZ2002"},
79 {Berth: 1, Registration: "PZ2002"},
80 }
80 }
81 for _, berth := range berths {
81 for _, berth := range berths {
82 affected, err := queries.AssignBerth(ctx, berth)
82 affected, err := queries.AssignBerth(ctx, berth)
83 if err != nil {
83 if err != nil {
84 return fmt.Errorf("assigning berth %d: %w", berth.Berth, err)
84 return fmt.Errorf("assigning berth %d: %w", berth.Berth, err)
85 }
85 }
86 fmt.Fprintf(output, "REPLACE INTO berth %d for %s, rows affected: %d\n", berth.Berth, berth.Registration, affected)
86 fmt.Fprintf(output, "REPLACE INTO berth %d for %s, rows affected: %d\n", berth.Berth, berth.Registration, affected)
87 }
87 }
88 assigned, err := queries.ListBerths(ctx)
88 assigned, err := queries.ListBerths(ctx)
89 if err != nil {
89 if err != nil {
90 return fmt.Errorf("listing berths: %w", err)
90 return fmt.Errorf("listing berths: %w", err)
91 }
91 }
92 for _, berth := range assigned {
92 for _, berth := range assigned {
93 fmt.Fprintf(output, " berth %d (%T): %s\n", berth.Berth, berth.Berth, berth.Name)
93 fmt.Fprintf(output, " berth %d (%T): %s\n", berth.Berth, berth.Berth, berth.Name)
94 }
94 }
95
95
96 withRadio, err := queries.BoatsWithKit(ctx, "radio")
96 withRadio, err := queries.BoatsWithKit(ctx, "radio")
97 if err != nil {
97 if err != nil {
98 return fmt.Errorf("finding boats with a radio: %w", err)
98 return fmt.Errorf("finding boats with a radio: %w", err)
99 }
99 }
100 for _, boat := range withRadio {
100 for _, boat := range withRadio {
101 fmt.Fprintf(output, "FIND_IN_SET %s has a radio among %q\n", boat.Name, boat.Kit)
101 fmt.Fprintf(output, "FIND_IN_SET %s has a radio among %q\n", boat.Name, boat.Kit)
102 }
102 }
103
103
104 harbour, err := queries.Harbour(ctx)
104 harbour, err := queries.Harbour(ctx)
105 if err != nil {
105 if err != nil {
106 return fmt.Errorf("listing the harbour: %w", err)
106 return fmt.Errorf("listing the harbour: %w", err)
107 }
107 }
108 for _, boat := range harbour {
108 for _, boat := range harbour {
109 crewList := "no crew"
109 crewList := "no crew"
110 if boat.Crew != nil {
110 if boat.Crew != nil {
111 crewList = *boat.Crew
111 crewList = *boat.Crew
112 }
112 }
113 fmt.Fprintf(output, "GROUP_CONCAT %s %-10s crossings %d (%T), crew: %s\n", boat.Registration, boat.Name, boat.Crossings, boat.Crossings, crewList)
113 fmt.Fprintf(output, "GROUP_CONCAT %s %-10s crossings %d (%T), crew: %s\n", boat.Registration, boat.Name, boat.Crossings, boat.Crossings, crewList)
114 }
114 }
115 return nil
115 return nil
116}
116}