|
|
|
package database
|
|
|
|
|
|
|
|
import (
|
|
|
|
"log"
|
|
|
|
|
|
|
|
"github.com/jmoiron/sqlx"
|
|
|
|
_ "modernc.org/sqlite"
|
|
|
|
)
|
|
|
|
|
|
|
|
type DB struct {
|
|
|
|
db *sqlx.DB
|
|
|
|
}
|
|
|
|
|
|
|
|
func New() *DB {
|
|
|
|
d := &DB{}
|
|
|
|
con, err := sqlx.Open("sqlite", "./todogo.db")
|
|
|
|
if err != nil {
|
|
|
|
log.Fatal(err)
|
|
|
|
}
|
|
|
|
d.db = con
|
|
|
|
|
|
|
|
err = d.init()
|
|
|
|
if err != nil {
|
|
|
|
log.Fatal(err)
|
|
|
|
}
|
|
|
|
|
|
|
|
return d
|
|
|
|
}
|
|
|
|
|
|
|
|
func (d *DB) init() error {
|
|
|
|
d.migrate()
|
|
|
|
|
|
|
|
return nil
|
|
|
|
}
|
|
|
|
|
|
|
|
func (d *DB) InsertTodo(title string) error {
|
|
|
|
_, err := d.db.Exec("INSERT INTO todos(title, completed) VALUES(?, ?)", title, 0)
|
|
|
|
return err
|
|
|
|
}
|
|
|
|
|
|
|
|
func (d *DB) ToggleTodo(id int) error {
|
|
|
|
_, err := d.db.Exec(`
|
|
|
|
UPDATE todos
|
|
|
|
SET completed = (CASE WHEN completed = 0 THEN 1 ELSE 0 END)
|
|
|
|
WHERE id = ?
|
|
|
|
`, id)
|
|
|
|
return err
|
|
|
|
}
|
|
|
|
|
|
|
|
func (d *DB) DeleteTodo(id int) error {
|
|
|
|
_, err := d.db.Exec("DELETE FROM todos WHERE id = ?", id)
|
|
|
|
return err
|
|
|
|
}
|
|
|
|
|
|
|
|
func (d *DB) GetAllTodos() ([]Todo, error) {
|
|
|
|
todos := []Todo{}
|
|
|
|
err := d.db.Select(&todos, "SELECT id, title, completed FROM todos")
|
|
|
|
if err != nil {
|
|
|
|
return nil, err
|
|
|
|
}
|
|
|
|
|
|
|
|
return todos, nil
|
|
|
|
}
|
|
|
|
|
|
|
|
func (d *DB) GetTodosByCompleted(completed bool) ([]Todo, error) {
|
|
|
|
completedInt := 0
|
|
|
|
if completed {
|
|
|
|
completedInt = 1
|
|
|
|
}
|
|
|
|
todos := []Todo{}
|
|
|
|
err := d.db.Select(&todos, "SELECT id, title, completed FROM todos WHERE completed = ?", completedInt)
|
|
|
|
if err != nil {
|
|
|
|
return nil, err
|
|
|
|
}
|
|
|
|
|
|
|
|
return todos, nil
|
|
|
|
}
|
|
|
|
|
|
|
|
func (d *DB) GetIncompleteTodosCount() int {
|
|
|
|
var count int
|
|
|
|
err := d.db.Get(&count, "SELECT COUNT(*) FROM todos WHERE completed = 0")
|
|
|
|
if err != nil {
|
|
|
|
log.Printf("failed to get incomplete count")
|
|
|
|
return 0
|
|
|
|
}
|
|
|
|
|
|
|
|
return count
|
|
|
|
}
|
|
|
|
|
|
|
|
func (d *DB) DeleteCompletedTodos() error {
|
|
|
|
_, err := d.db.Exec("DELETE FROM todos WHERE completed = 1")
|
|
|
|
return err
|
|
|
|
}
|
|
|
|
|
|
|
|
type Todo struct {
|
|
|
|
Id int
|
|
|
|
Title string
|
|
|
|
Completed bool
|
|
|
|
}
|