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