Gostore.go -rw-r--r-- 4.1 KiB
1package main
2
3import (
4 "database/sql"
5 "strings"
6 "time"
7
8 _ "github.com/mattn/go-sqlite3"
9)
10
11// 2018-01-16 19:05:21.752851759+08:00
12const datetimeFmt = "2006-01-02 15:04:05.999999999-07:00"
13
14type StoreFunc func(tx *sql.Tx) error
15
16type Store struct {
17 db *sql.DB
18}
19
20func NewStore(path string) (*Store, error) {
21 db, err := sql.Open("sqlite3", path)
22 if err != nil {
23 return nil, err
24 }
25 return &Store{db: db}, nil
26}
27
28// With applies all of the given functions with
29// a single transaction, rolling back on failure
30// and commiting on success.
31func (s Store) With(fns ...func(tx *sql.Tx) error) error {
32 tx, err := s.db.Begin()
33 if err != nil {
34 return err
35 }
36 for _, fn := range fns {
37 err = fn(tx)
38 if err != nil {
39 tx.Rollback()
40 return err
41 }
42 }
43 return tx.Commit()
44}
45
46func (s Store) CreateTask(tx *sql.Tx, task Task) (int, error) {
47 var taskID int
48 _, err := tx.Exec(
49 "INSERT INTO task (message,pomodoros,duration,tags) VALUES ($1,$2,$3,$4)",
50 task.Message, task.NPomodoros, task.Duration.String(), strings.Join(task.Tags, ","))
51 if err != nil {
52 return -1, err
53 }
54 err = tx.QueryRow("SELECT last_insert_rowid() FROM task").Scan(&taskID)
55 if err != nil {
56 return -1, err
57 }
58 err = tx.QueryRow("SELECT last_insert_rowid() FROM task").Scan(&taskID)
59 if err != nil {
60 return -1, err
61 }
62 return taskID, nil
63}
64
65func (s Store) ReadTasks(tx *sql.Tx) ([]*Task, error) {
66 rows, err := tx.Query(`SELECT rowid,message,pomodoros,duration,tags FROM task`)
67 if err != nil {
68 return nil, err
69 }
70 tasks := []*Task{}
71 for rows.Next() {
72 var (
73 tags string
74 strDuration string
75 )
76 task := &Task{Pomodoros: []*Pomodoro{}}
77 err = rows.Scan(&task.ID, &task.Message, &task.NPomodoros, &strDuration, &tags)
78 if err != nil {
79 return nil, err
80 }
81 duration, _ := time.ParseDuration(strDuration)
82 task.Duration = duration
83 if tags != "" {
84 task.Tags = strings.Split(tags, ",")
85 }
86 pomodoros, err := s.ReadPomodoros(tx, task.ID)
87 if err != nil {
88 return nil, err
89 }
90 for _, pomodoro := range pomodoros {
91 task.Pomodoros = append(task.Pomodoros, pomodoro)
92 }
93 tasks = append(tasks, task)
94 }
95 return tasks, nil
96}
97
98func (s Store) DeleteTask(tx *sql.Tx, taskID int) error {
99 _, err := tx.Exec("DELETE FROM task WHERE rowid = $1", &taskID)
100 if err != nil {
101 return err
102 }
103 _, err = tx.Exec("DELETE FROM pomodoro WHERE task_id = $1", &taskID)
104 if err != nil {
105 return err
106 }
107 return nil
108}
109
110func (s Store) ReadTask(tx *sql.Tx, taskID int) (*Task, error) {
111 task := &Task{}
112 var (
113 tags string
114 strDuration string
115 )
116 err := tx.QueryRow(`SELECT rowid,message,pomodoros,duration,tags FROM task WHERE rowid = $1`, &taskID).
117 Scan(&task.ID, &task.Message, &task.NPomodoros, &strDuration, &tags)
118 if err != nil {
119 return nil, err
120 }
121 duration, _ := time.ParseDuration(strDuration)
122 task.Duration = duration
123 if tags != "" {
124 task.Tags = strings.Split(tags, ",")
125 }
126 return task, nil
127}
128
129func (s Store) CreatePomodoro(tx *sql.Tx, taskID int, pomodoro Pomodoro) error {
130 _, err := tx.Exec(
131 `INSERT INTO pomodoro (task_id, start, end) VALUES ($1, $2, $3)`,
132 taskID,
133 pomodoro.Start,
134 pomodoro.End,
135 )
136 return err
137}
138
139func (s Store) ReadPomodoros(tx *sql.Tx, taskID int) ([]*Pomodoro, error) {
140 rows, err := tx.Query(`SELECT start,end FROM pomodoro WHERE task_id = $1`, &taskID)
141 if err != nil {
142 return nil, err
143 }
144 pomodoros := []*Pomodoro{}
145 for rows.Next() {
146 var (
147 startStr string
148 endStr string
149 )
150 pomodoro := &Pomodoro{}
151 err = rows.Scan(&startStr, &endStr)
152 if err != nil {
153 return nil, err
154 }
155 start, _ := time.Parse(datetimeFmt, startStr)
156 end, _ := time.Parse(datetimeFmt, endStr)
157 pomodoro.Start = start
158 pomodoro.End = end
159 pomodoros = append(pomodoros, pomodoro)
160 }
161 return pomodoros, nil
162}
163
164func (s Store) DeletePomodoros(tx *sql.Tx, taskID int) error {
165 _, err := tx.Exec("DELETE FROM pomodoro WHERE task_id = $1", &taskID)
166 return err
167}
168
169func (s Store) Close() error { return s.db.Close() }
170
171func initDB(db *Store) error {
172 stmt := `
173 CREATE TABLE task (
174 message TEXT,
175 pomodoros INTEGER,
176 duration TEXT,
177 tags TEXT
178 );
179 CREATE TABLE pomodoro (
180 task_id INTEGER,
181 start DATETTIME,
182 end DATETTIME
183 );
184 `
185 _, err := db.db.Exec(stmt)
186 return err
187}