/
main.go
169 lines (137 loc) · 3.66 KB
/
main.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 db
import (
"database/sql"
"fmt"
log "github.com/sirupsen/logrus"
_ "github.com/mattn/go-sqlite3"
)
type Database interface {
OpenDatabase() error
CreateTable() error
DisplayAllTodos() ([]Todo, error)
GetTodo(id string) (Todo, error)
InsertTodo(name, description, tasks string) error
UpdateTodo(id int, todo Todo) error
DeleteTodo(id string) error
}
type data struct {
logger *log.Entry
dbDriver string
dbPath string
dbConnection *sql.DB
}
func NewDatabase() Database {
return &data{
logger: log.WithFields(log.Fields{"package": "db"}),
dbDriver: "sqlite3",
dbPath: "./sqlite-database.db",
}
}
func (d *data) OpenDatabase() error {
database, databaseOpenErr := sql.Open(d.dbDriver, d.dbPath)
if databaseOpenErr != nil {
return databaseOpenErr
}
d.dbConnection = database
return database.Ping()
}
func (d *data) CreateTable() error {
createTableSQL := `CREATE TABLE IF NOT EXISTS todos (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"name" TEXT,
"description" TEXT,
"tasks" TEXT
);`
statement, err := d.dbConnection.Prepare(createTableSQL)
if err != nil {
d.logger.Fatal(err.Error())
return err
}
statement.Exec()
d.logger.Debugf("todos table created")
return nil
}
func (d *data) InsertTodo(name, description, tasks string) error {
statement, err := d.dbConnection.Prepare(`INSERT INTO todos(name, description, tasks) VALUES (?, ?, ?)`)
defer statement.Close()
if err != nil {
d.logger.Fatalln(err)
}
_, err = statement.Exec(name, description, tasks)
if err != nil {
d.logger.Fatalln(err)
return err
}
d.logger.Debugln("Inserted todo successfully")
return nil
}
func (d *data) DisplayAllTodos() ([]Todo, error) {
result := make([]Todo, 0)
row, err := d.dbConnection.Query("SELECT * FROM todos ORDER BY id")
defer row.Close()
if err != nil {
d.logger.Fatal(err)
return result, err
}
for row.Next() {
var id int
var name, description, tasks string
row.Scan(&id, &name, &description, &tasks)
result = append(result, Todo{Id: id, Name: name, Description: description, Tasks: tasks})
d.logger.Println(id, "[", name, "] ", description, "—", tasks)
}
return result, err
}
func (d *data) GetTodo(todoId string) (Todo, error) {
queryResult, err := d.dbConnection.Query(fmt.Sprintf(`SELECT * from todos WHERE id = %s`, todoId))
defer queryResult.Close()
if err != nil {
d.logger.Fatalln(err)
return Todo{}, err
}
found := queryResult.Next()
if found {
var id int
var name, description, tasks string
queryResult.Scan(&id, &name, &description, &tasks)
d.logger.Println(name, description, tasks)
return Todo{id, name, description, tasks}, nil
}
d.logger.Info("todo not found")
return Todo{}, fmt.Errorf("todo not found")
}
func (d *data) UpdateTodo(id int, todo Todo) error {
statement, errPrepare := d.dbConnection.Prepare("UPDATE todos set name=?, description=?, tasks=? where id=?")
defer statement.Close()
if errPrepare != nil {
d.logger.Fatalln(errPrepare)
return errPrepare
}
result, errExec := statement.Exec(todo.Name, todo.Description, todo.Tasks, id)
if errExec != nil {
d.logger.Fatalln(errExec)
return errExec
}
affect, errRowsAffected := result.RowsAffected()
if errRowsAffected != nil {
d.logger.Fatalln(errRowsAffected)
return errRowsAffected
}
log.Infof("%d rows affected", affect)
return nil
}
func (d *data) DeleteTodo(id string) error {
statement, err := d.dbConnection.Prepare(`DELETE from todos WHERE id = ?`)
defer statement.Close()
if err != nil {
d.logger.Fatalln(err)
return err
}
_, err = statement.Exec(id)
if err != nil {
d.logger.Fatalln(err)
return err
}
d.logger.Debugln("Todo deleted successfully")
return nil
}