Go + sqlite example: use custom function in trigger to validate insert

alternate.go

package main

// functionally same as `main.go`, with different trigger definition
import (
	"database/sql"
	"fmt"
	sqlite "github.com/mattn/go-sqlite3"
	"log"
	"os"
)

func foobar(y int64) string {
	if 0 != y%2 {
		return "as if, not even"
	}
	return ""
}

func main() {
	sql.Register("sqlite3_custom", &sqlite.SQLiteDriver{
		ConnectHook: func(conn *sqlite.SQLiteConn) error {
			if err := conn.RegisterFunc("validate", foobar, false); err != nil {
				return err
			}
			return nil
		},
	})

	db, err := sql.Open("sqlite3_custom", ":memory:")
	if err != nil {
		log.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	var i string
	err = db.QueryRow("SELECT validate(3)").Scan(&i)
	if err != nil {
		log.Fatal("POW query error:", err)
	}
	fmt.Println("validate(3) =", i) // 0

	err = db.QueryRow("SELECT validate(442)").Scan(&i)
	if err != nil {
		log.Fatal("POW query error:", err)
	}
	fmt.Println("validate(442) =", i) // 0

	_, err = db.Exec("create table foo (department integer, profits integer)")
	if err != nil {
		log.Fatal("Failed to create table:", err)
	}

	mkTrigger := `
	CREATE TRIGGER insert_trigger
	BEFORE INSERT ON foo 
	BEGIN
        SELECT CASE
          WHEN ( (SELECT validate(NEW.department) as error_string) != '')
	      THEN RAISE(ABORT, "invalid department" )
	    END;
	END;
	`
	_, err = db.Exec(mkTrigger)
	if err != nil {
		log.Printf("error creating trigger %q: %s", err, mkTrigger)
		os.Exit(1)
		return
	}

	log.Println("inserting 1st")
	_, err = db.Exec("insert into foo values (10, 10)")
	if err != nil {
		log.Println("Failed to insert first:", err)
	}

	log.Println("inserting 2nd")
	_, err = db.Exec("insert into foo values (11, 10)")
	if err != nil {
		log.Println("Failed to insert 2nd:", err)
	}
}

main.go

package main

import (
	"database/sql"
	"fmt"
	sqlite "github.com/mattn/go-sqlite3"
	"log"
)

func validate(y int64) int64 {
	return y % 2
}

func main() {
	sql.Register("sqlite3_custom", &sqlite.SQLiteDriver{
		ConnectHook: func(conn *sqlite.SQLiteConn) error {
			if err := conn.RegisterFunc("validate", validate, false); err != nil {
				return err
			}
			return nil
		},
	})

	db, err := sql.Open("sqlite3_custom", ":memory:")
	if err != nil {
		log.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	var i int64
	err = db.QueryRow("SELECT validate(3)").Scan(&i)
	if err != nil {
		log.Fatal("POW query error:", err)
	}
	fmt.Println("validate(3) =", i) // 0

	err = db.QueryRow("SELECT validate(442)").Scan(&i)
	if err != nil {
		log.Fatal("POW query error:", err)
	}
	fmt.Println("validate(442) =", i) // 0

	_, err = db.Exec("create table foo (department integer, profits integer)")
	if err != nil {
		log.Fatal("Failed to create table:", err)
	}

	// https://stackoverflow.com/q/27214529/864438 sez error-message must be constant
	mkTrigger := `
	CREATE TRIGGER insert_trigger
	BEFORE INSERT ON foo
	WHEN validate(NEW.department) != 0
	BEGIN
	  SELECT RAISE(ABORT, 'bad validate');
	END;
	`
	_, err = db.Exec(mkTrigger)
	if err != nil {
		log.Fatalf("error creating trigger %q: %s", err, mkTrigger)
		return
	}

	log.Println("inserting 1st")
	_, err = db.Exec("insert into foo values (10, 10)")
	if err != nil {
		log.Println("Failed to insert first:", err)
	}

	log.Println("inserting 2nd")
	_, err = db.Exec("insert into foo values (11, 10)")
	if err != nil {
		log.Println("Failed to insert 2nd:", err)
	}
}

very nice stuff !, do you know if it is working with mysql and postgres ?

This gist is 5 years old (i.e. ancient). I don't even know if it currently works with sqlite! 🤷

This gist is 5 years old (i.e. ancient). I don't even know if it currently works with sqlite! 🤷

I think still workin with sqlite, and i didn't find something similar for mysql and postgres