main.go

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/cznic/ql/driver"
)

func check(err error) {
	if err != nil {
		panic(err)
	}
}

const (
	dbPositionCreateTable = `CREATE TABLE Positions (
	Y float64,
	Z float64,
	Alpha float64,
	Beta float64,
	Comment string
);
CREATE INDEX PositionId on Positions (id());`

	dbPositionUpdate = `
		UPDATE Positions
		SET
			Y = $1, Z = $2,
			Alpha = $3,	Beta = $4,
			Comment = $5
		WHERE id() == $6;`

	dbPositionUpdateTypeMissmatch = `
		UPDATE Positions
		SET
			Comment = $2,
			Y = $3, Z = $4,
			Alpha = $5,	Beta = $6
		WHERE id() == $1;`

	dbPositionInsert = `INSERT INTO Positions (Y,Z,Alpha,Beta,Comment) VALUES($1,$2,$3,$4,$5);`
)

type position struct {
	ID          int64
	Y, Z        float64
	Alpha, Beta float64
	Comment     string
}

func main() {
	db, err := sql.Open("ql-mem", "mem.test")
	check(err)

	var pos position
	pos.Y = 0.1
	pos.Z = 0.2
	pos.Alpha = 0.3
	pos.Beta = 0.4
	pos.Comment = "small"

	// prepare db
	tx, err := db.Begin()
	check(err)
	_, err = tx.Exec(dbPositionCreateTable)
	check(err)
	insStmt, err := tx.Prepare(dbPositionInsert)
	check(err)
	defer insStmt.Close()
	res, err := insStmt.Exec(pos.Y, pos.Z, pos.Alpha, pos.Beta, pos.Comment)
	check(err)
	pid, err := res.LastInsertId()
	check(err)
	pos.ID = pid
	err = tx.Commit()
	check(err)

	// run working
	tx, err = db.Begin()
	check(err)
	stmt, err := tx.Prepare(dbPositionUpdate)
	check(err)
	defer stmt.Close()
	res, err = stmt.Exec(pos.Y, pos.Z, pos.Alpha, pos.Beta, pos.Comment, pos.ID)
	check(err)
	cnt, err := res.RowsAffected()
	check(err)
	err = tx.Commit()
	check(err)
	if cnt != 1 {
		fmt.Printf("affected: %d\n", cnt)
	}

	// confusing
	tx, err = db.Begin()
	check(err)
	stmt, err = tx.Prepare(dbPositionUpdateTypeMissmatch)
	check(err)
	defer stmt.Close()
	res, err = stmt.Exec(pos.ID, pos.Comment, pos.Y, pos.Z, pos.Alpha, pos.Beta)
	check(err)
	cnt, err = res.RowsAffected()
	check(err)
	err = tx.Commit()
	check(err)
	if cnt != 1 {
		fmt.Printf("affected: %d\n", cnt)
	}

	check(db.Close())
}