package db import ( "database/sql" "log" "time" _ "github.com/mattn/go-sqlite3" "github.com/Cameron-Reed1/todo-web/types" ) var db *sql.DB func Open(path string) { if db != nil { log.Fatal("Cannot init DB twice!") } var err error db, err = sql.Open("sqlite3", path) if err != nil { log.Fatal(err) } err = db.Ping() if err != nil { log.Fatal(err) } query := ` CREATE TABLE IF NOT EXISTS items ( id INTEGER PRIMARY KEY AUTOINCREMENT, start INTEGER, due INTEGER, text TEXT NOT NULL, completed INTEGER NOT NULL );` _, err = db.Exec(query) } func AddTodo(todo *types.Todo) error { res, err := db.Exec("INSERT INTO items(start, due, text, completed) values(?, ?, ?, 0)", toNullInt64(todo.Start), toNullInt64(todo.Due), todo.Text) if err != nil { return err } todo.Id, err = res.LastInsertId() if err != nil { return err } return nil } func GetTodo(id int) (types.Todo, error) { var todo types.Todo var start sql.NullInt64 var due sql.NullInt64 row := db.QueryRow("SELECT * FROM items WHERE id=?", id) err := row.Scan(&todo.Id, &start, &due, &todo.Text, &todo.Completed) todo.Start = fromNullInt64(start) todo.Due = fromNullInt64(due) return todo, err } func GetAllTodos() ([]types.Todo, error) { var todos []types.Todo rows, err := db.Query("SELECT * FROM items") if err != nil { return nil, err } for rows.Next() { var todo types.Todo var start sql.NullInt64 var due sql.NullInt64 err = rows.Scan(&todo.Id, &start, &due, &todo.Text, &todo.Completed) if err != nil { return nil, err } todo.Start = fromNullInt64(start) todo.Due = fromNullInt64(due) todos = append(todos, todo) } if err = rows.Err(); err != nil { return nil, err } return todos, nil } func GetOverdueTodos() ([]types.Todo, error) { var todos []types.Todo rows, err := db.Query("SELECT * FROM items WHERE due < ? AND due IS NOT NULL ORDER BY completed, due", time.Now().Unix()) if err != nil { return nil, err } for rows.Next() { var todo types.Todo var start sql.NullInt64 var due sql.NullInt64 err = rows.Scan(&todo.Id, &start, &due, &todo.Text, &todo.Completed) if err != nil { return nil, err } todo.Start = fromNullInt64(start) todo.Due = fromNullInt64(due) todos = append(todos, todo) } if err = rows.Err(); err != nil { return nil, err } return todos, nil } func GetTodayTodos() ([]types.Todo, error) { var todos []types.Todo now := time.Now() year, month, day := now.Date() today := time.Date(year, month, day, 0, 0, 0, 0, time.Local) rows, err := db.Query("SELECT * FROM items WHERE (start <= ? OR start IS NULL) AND (due >= ? OR due IS NULL) ORDER BY completed, due NULLS LAST", today.Unix(), now.Unix()) if err != nil { return nil, err } for rows.Next() { var todo types.Todo var start sql.NullInt64 var due sql.NullInt64 err = rows.Scan(&todo.Id, &start, &due, &todo.Text, &todo.Completed) if err != nil { return nil, err } todo.Start = fromNullInt64(start) todo.Due = fromNullInt64(due) todos = append(todos, todo) } if err = rows.Err(); err != nil { return nil, err } return todos, nil } func GetUpcomingTodos() ([]types.Todo, error) { var todos []types.Todo year, month, day := time.Now().Date() today := time.Date(year, month, day, 0, 0, 0, 0, time.Local) rows, err := db.Query("SELECT * FROM items WHERE start > ? ORDER BY completed, start", today.Unix()) if err != nil { return nil, err } for rows.Next() { var todo types.Todo var start sql.NullInt64 var due sql.NullInt64 err = rows.Scan(&todo.Id, &start, &due, &todo.Text, &todo.Completed) if err != nil { return nil, err } todo.Start = fromNullInt64(start) todo.Due = fromNullInt64(due) todos = append(todos, todo) } if err = rows.Err(); err != nil { return nil, err } return todos, nil } func UpdateTodo(newValues types.Todo) error { _, err := db.Exec("UPDATE items SET start=?, due=?, text=? WHERE id=?", toNullInt64(newValues.Start), toNullInt64(newValues.Due), newValues.Text, newValues.Id) return err; } func SetCompleted(id int, completed bool) error { _, err := db.Exec("UPDATE items SET completed=? WHERE id=?", completed, id) return err } func DeleteTodo(id int) error { _, err := db.Exec("DELETE FROM items WHERE id=?", id) return err } func Close() { db.Close() } func toNullInt64(num int64) sql.NullInt64 { if num == 0 { return sql.NullInt64{Int64: 0, Valid: false} } return sql.NullInt64{Int64: num, Valid: true} } func fromNullInt64(num sql.NullInt64) int64 { if num.Valid { return num.Int64 } return 0 }