Generate a SQL check constraint benchmark

check_gen.go

package main

import (
	"log"
	"math/rand"
	"os"
	"text/template"
	"time"
)

const sql = `--Check constraint benchmark
BEGIN;
--
CREATE TABLE checks (
  col1 TEXT NOT NULL CHECK (TRIM(col1) <> ''),
  col2 INTEGER NOT NULL CHECK (col2 >= 0)
);
CREATE TABLE null_checks (
  col1 TEXT NOT NULL,
  col2 INTEGER NOT NULL
);
CREATE TABLE no_checks (
  col1 TEXT,
  col2 INTEGER
);
--
\timing on
INSERT INTO checks(col1, col2) VALUES
{{range  $i, $e := .}}{{if $i}},{{println}}{{end}}('{{$e.Col1}}',{{$e.Col2}}){{end}};
INSERT INTO null_checks(col1,col2) VALUES
{{range  $i, $e := .}}{{if $i}},{{println}}{{end}}('{{$e.Col1}}',{{$e.Col2}}){{end}};
INSERT INTO no_checks(col1,col2) VALUES
{{range  $i, $e := .}}{{if $i}},{{println}}{{end}}('{{$e.Col1}}',{{$e.Col2}}){{end}};
\timing off
--
ROLLBACK;`

const maxRows = 1000000

type random struct {
	rand    *rand.Rand
	charset string
}

func (r *random) String(length int) string {
	b := make([]byte, length)
	for i := range b {
		b[i] = r.charset[r.rand.Intn(len(r.charset))]
	}
	return string(b)
}

func (r *random) Intn(n int) int {
	return r.rand.Intn(n)
}

func main() {
	random := &random{
		rand:    rand.New(rand.NewSource(time.Now().UnixNano())),
		charset: "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",
	}

	type row struct {
		Col1 string
		Col2 int
	}

	data := make([]*row, maxRows)
	for i := 0; i < maxRows; i++ {
		data[i] = &row{
			random.String(30),
			random.Intn(1000000),
		}
	}

	tmpl := template.Must(template.New("benchmark").Parse(sql))
	err := tmpl.Execute(os.Stdout, data)
	if err != nil {
		log.Fatal(err)
	}
}