Gostore.go -rw-r--r-- 3.2 KiB
1package main
2
3import (
4 "database/sql"
5 _ "github.com/mattn/go-sqlite3"
6 "os"
7 "strings"
8 "time"
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 Store struct {
15 db *sql.DB
16}
17
18func NewStore(path string) (*Store, error) {
19 os.Mkdir(path, 0755)
20 db, err := sql.Open("sqlite3", path+"/pomo.db")
21 if err != nil {
22 return nil, err
23 }
24 return &Store{db: db}, nil
25}
26
27func (s Store) CreateTask(task Task) (int, error) {
28 var taskID int
29 tx, err := s.db.Begin()
30 if err != nil {
31 return -1, err
32 }
33 _, err = tx.Exec(
34 "INSERT INTO task (message,pomodoros,duration,tags) VALUES ($1,$2,$3,$4)",
35 task.Message, task.NPomodoros, task.Duration.String(), strings.Join(task.Tags, ","))
36 if err != nil {
37 tx.Rollback()
38 return -1, err
39 }
40 err = tx.QueryRow("SELECT last_insert_rowid() FROM task").Scan(&taskID)
41 if err != nil {
42 tx.Rollback()
43 return -1, err
44 }
45 return taskID, tx.Commit()
46}
47
48func (s Store) CreatePomodoro(taskID int, pomodoro Pomodoro) error {
49 _, err := s.db.Exec(
50 `INSERT INTO pomodoro (task_id, start, end) VALUES ($1, $2, $3)`,
51 taskID,
52 pomodoro.Start,
53 pomodoro.End,
54 )
55 return err
56}
57
58func (s Store) ReadTasks() ([]*Task, error) {
59 rows, err := s.db.Query(`SELECT rowid,message,pomodoros,duration,tags FROM task`)
60 if err != nil {
61 return nil, err
62 }
63 tasks := []*Task{}
64 for rows.Next() {
65 var (
66 tags string
67 strDuration string
68 )
69 task := &Task{Pomodoros: []*Pomodoro{}}
70 err = rows.Scan(&task.ID, &task.Message, &task.NPomodoros, &strDuration, &tags)
71 if err != nil {
72 return nil, err
73 }
74 duration, _ := time.ParseDuration(strDuration)
75 task.Duration = duration
76 if tags != "" {
77 task.Tags = strings.Split(tags, ",")
78 }
79 pomodoros, err := s.ReadPomodoros(task.ID)
80 if err != nil {
81 return nil, err
82 }
83 for _, pomodoro := range pomodoros {
84 task.Pomodoros = append(task.Pomodoros, pomodoro)
85 }
86 tasks = append(tasks, task)
87 }
88 return tasks, nil
89}
90
91func (s Store) ReadPomodoros(taskID int) ([]*Pomodoro, error) {
92 rows, err := s.db.Query(`SELECT start,end FROM pomodoro WHERE task_id = $1`, &taskID)
93 if err != nil {
94 return nil, err
95 }
96 pomodoros := []*Pomodoro{}
97 for rows.Next() {
98 var (
99 startStr string
100 endStr string
101 )
102 pomodoro := &Pomodoro{}
103 err = rows.Scan(&startStr, &endStr)
104 if err != nil {
105 return nil, err
106 }
107 start, _ := time.Parse(datetimeFmt, startStr)
108 end, _ := time.Parse(datetimeFmt, endStr)
109 pomodoro.Start = start
110 pomodoro.End = end
111 pomodoros = append(pomodoros, pomodoro)
112 }
113 return pomodoros, nil
114}
115
116func (s Store) DeleteTask(taskID int) error {
117 tx, err := s.db.Begin()
118 if err != nil {
119 return err
120 }
121 _, err = tx.Exec("DELETE FROM task WHERE rowid = $1", &taskID)
122 if err != nil {
123 tx.Rollback()
124 return err
125 }
126 _, err = tx.Exec("DELETE FROM record WHERE task_id = $1", &taskID)
127 if err != nil {
128 tx.Rollback()
129 return err
130 }
131 return tx.Commit()
132}
133
134func (s Store) Close() error { return s.db.Close() }
135
136func initDB(db *Store) error {
137 stmt := `
138 CREATE TABLE task (
139 message TEXT,
140 pomodoros INTEGER,
141 duration TEXT,
142 tags TEXT
143 );
144 CREATE TABLE pomodoro (
145 task_id INTEGER,
146 start DATETTIME,
147 end DATETTIME
148 );
149 `
150 _, err := db.db.Exec(stmt)
151 return err
152}