exploring sqlite3 + tractor engine

tractor-todos-sqlite.go

package main

import (
	"database/sql"
	"embed"
	"log"

	"github.com/manifold/tractor/toolkit/engine"
	"github.com/mattn/go-sqlite3"
)

//go:embed *.html
var ui embed.FS
var db *sql.DB

func ensureDB() {
	sqlite3conn := []*sqlite3.SQLiteConn{}

	sql.Register("sqlite3_with_hook_example",
		&sqlite3.SQLiteDriver{
			ConnectHook: func(conn *sqlite3.SQLiteConn) error {
				sqlite3conn = append(sqlite3conn, conn)
				conn.RegisterUpdateHook(func(op int, db string, table string, rowid int64) {
					switch op {
					case sqlite3.SQLITE_INSERT:

						// FIXME(ja): load values for rowid, add to our main struct todos
						log.Println("Notified of insert on db", db, "table", table, "rowid", rowid)
					case sqlite3.SQLITE_DELETE:

						// FIXME(ja): m isn't defined here... 
						// need to figure how how this whole m thing works?

						rowIdx := -1
						for idx, t := range m.Todos {
							if t.ID == rowid {
								rowIdx = idx
							}
						}

						if rowIdx != -1 {
							m.Todos = append(m.Todos[:rowIdx], m.Todos[rowIdx+1:]...)
						}

						// FIXME(ja): remove rowid from main struct of todos
						log.Println("Notified of delete on db", db, "table", table, "rowid", rowid)
					case sqlite3.SQLITE_UPDATE:
						// FIXME(ja): update rowid in main struct of todos
						log.Println("Notified of update on db", db, "table", table, "rowid", rowid)
					}
				})
				return nil
			},
		})

	db, _ = sql.Open("sqlite3_with_hook_example", "./todos.db")

	sqlStmt := `
	CREATE TABLE IF NOT EXISTS todos (id INTEGER PRIMARY KEY, title TEXT, completed BOOLEAN);
	`
	_, err := db.Exec(sqlStmt)
	if err != nil {
		log.Fatal("%q: %s\n", err, sqlStmt)
	}

}

func main() {
	ensureDB()

	todos := loadAll()

	engine.Run(&Main{Todos: todos},
		&engine.WebView{UI: ui})

	defer db.Close()
}

type Todo struct {
	ID        int
	Title     string
	Completed bool
}

type Main struct {
	Todos []Todo
}

func (m *Main) WindowBuilder(w *engine.Window) {
	w.Size = engine.Size{W: 400, H: 300}
	w.Title = "Todos"
	w.AlwaysOnTop = true
	w.Center = true
}

func loadAll() []Todo {
	rows, err := db.Query("SELECT id, title, completed FROM todos")

	if err != nil {
		log.Fatal(err)
	}
	var id int
	var title string
	var completed int
	var todos []Todo

	for rows.Next() {
		rows.Scan(&id, &title, &completed)
		todos = append(todos, Todo{ID: id, Title: title, Completed: completed != 0})
	}
	return todos
}

func (m *Main) AddTodo(s string) {
	stmt, err := db.Prepare("insert into todos (title) values(?)")
	if err != nil {
		log.Fatal(err)
	}

	stmt.Exec(s)
	stmt.Close()

	m.Todos = append(m.Todos, Todo{Title: s})
}

func (m *Main) CompleteTodo(id int, b bool) {
	stmt, err := db.Prepare("update todos set completed = (?) where id = (?)")
	if err != nil {
		log.Fatal(err)
	}

	stmt.Exec(b, id)
	stmt.Close()
}

func (m *Main) RemoveTodo(id int) {
	stmt, err := db.Prepare("delete from todos where id = (?)")
	if err != nil {
		log.Fatal(err)
	}

	stmt.Exec(id)
	stmt.Close()

	// rowIdx := -1;
	// for idx, t := range m.Todos {
	// 	if t.ID == id {
	// 		rowIdx = idx
	// 	}
	// }

	// if rowIdx != -1 {
	// 	m.Todos = append(m.Todos[:rowIdx], m.Todos[rowIdx+1:]...)
	// }
}