/
sqlite.go
169 lines (131 loc) · 4.1 KB
/
sqlite.go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
package sqlite
import (
"database/sql"
"errors"
"fmt"
"github.com/ZiganshinDev/scheduleVKBot/internal/storage"
_ "github.com/mattn/go-sqlite3"
)
type Storage struct {
db *sql.DB
}
func New(storagePath string) (*Storage, error) {
const op = "storage.sqlite.New"
db, err := sql.Open("sqlite3", storagePath)
if err != nil {
return nil, fmt.Errorf("%s: %w", op, err)
}
stmt, err := db.Prepare(`
CREATE TABLE IF NOT EXISTS users(
user_id INTEGER PRIMARY KEY,
institute TEXT,
course INTEGER,
group_number INTEGER,
peer_id INTEGER,
week TEXT);
`)
if err != nil {
return nil, fmt.Errorf("%s: %w", op, err)
}
_, err = stmt.Exec()
if err != nil {
return nil, fmt.Errorf("%s: %w", op, err)
}
stmt, err = db.Prepare(`
CREATE TABLE IF NOT EXISTS schedule(
lesson_id INTEGER PRIMARY KEY,
institute TEXT,
course INTEGER,
group_number INTEGER,
lesson_name TEXT,
lesson_type TEXT,
date_range TEXT,
day TEXT,
audience TEXT,
lesson_number INTEGER,
week TEXT);
`)
if err != nil {
return nil, fmt.Errorf("%s: %w", op, err)
}
_, err = stmt.Exec()
if err != nil {
return nil, fmt.Errorf("%s: %w", op, err)
}
return &Storage{db: db}, nil
}
func (s *Storage) GetSchedule(day string, peerId int) (string, error) {
const op = "storage.sqlite.GetSchedule"
stmt, err := s.db.Prepare("SELECT schedule.lesson_name, schedule.lesson_type, schedule.date_range, schedule.audience, schedule.lesson_number FROM schedule INNER JOIN users ON schedule.institute = users.institute AND schedule.course = users.course AND schedule.group_number = users.group_number AND schedule.week = users.week WHERE schedule.day = ? AND users.peer_id = ? ORDER BY schedule.lesson_number;")
if err != nil {
return "", fmt.Errorf("%s: prepare statement: %w", op, err)
}
//TODO change to response struct
var lesson_name, lesson_type, date_range, audience, lesson_number string
err = stmt.QueryRow(day, peerId).Scan(&lesson_name, &lesson_type, &date_range, &audience, &lesson_number)
if err != nil {
if errors.Is(err, sql.ErrNoRows) {
return "", storage.ErrNotFound
}
return "", fmt.Errorf("%s: execute statement: %w", op, err)
}
var result string
//TODO change to response struct
result = result + lesson_number + " " + lesson_type + ". " + lesson_name + " " + date_range + " " + audience
return result, nil
}
func (s *Storage) AddUser(institute string, course string, groupNumber string, peerId int) error {
const op = "storage.sqlite.AddUser"
stmt, err := s.db.Prepare("INSERT INTO users(institute, course, group_number, peer_id) VALUES(?, ?, ?, ?)")
if err != nil {
return fmt.Errorf("%s: %w", op, err)
}
_, err = stmt.Exec(institute, course, groupNumber, peerId)
if err != nil {
return fmt.Errorf("%s: %w", op, err)
}
return nil
}
func (s *Storage) CheckSchedule(institute string, course string, groupNumber string) (bool, error) {
const op = "storage.sqlite.CheckSchedule"
stmt, err := s.db.Prepare("SELECT COUNT(lesson_name) FROM schedule WHERE institute = ? AND course = ? AND group_number = ?")
if err != nil {
return false, fmt.Errorf("%s: %w", op, err)
}
var count int
err = stmt.QueryRow(institute, course, groupNumber).Scan(&count)
if err != nil {
if errors.Is(err, sql.ErrNoRows) {
return false, storage.ErrNotFound
}
return false, fmt.Errorf("%s: execute statement: %w", op, err)
}
if count == 0 {
return false, nil
}
return true, nil
}
func (s *Storage) UserAddWeek(week string, peerId int) error {
const op = "storage.sqlite.UserAddWeek"
stmt, err := s.db.Prepare("UPDATE users SET week = ? WHERE peer_id = ?")
if err != nil {
return fmt.Errorf("%s: execute statement: %w", op, err)
}
_, err = stmt.Exec(week, peerId)
if err != nil {
return fmt.Errorf("%s: execute statement: %w", op, err)
}
return nil
}
func (s *Storage) DeleteUser(peerId int) error {
const op = "storage.sqlite.DeleteUser"
stmt, err := s.db.Prepare("DELETE FROM users WHERE peer_id = ?")
if err != nil {
return fmt.Errorf("%s: execute statement: %w", op, err)
}
_, err = stmt.Exec(peerId)
if err != nil {
return fmt.Errorf("%s: execute statement: %w", op, err)
}
return nil
}