/
storage.go
123 lines (94 loc) · 2.15 KB
/
storage.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
/**
* @file storage.go
* @author Mikhail Klementyev jollheef<AT>riseup.net
* @license GNU GPLv3
* @date July, 2016
*/
package storage
import (
"database/sql"
_ "github.com/mattn/go-sqlite3"
)
func OpenDB(path string) (db *sql.DB, err error) {
db, err = sql.Open("sqlite3", path)
if err != nil {
return
}
_, err = db.Exec("CREATE TABLE IF NOT EXISTS `links` " +
"( `id` INTEGER PRIMARY KEY AUTOINCREMENT, `url` TEXT );")
if err != nil {
return
}
_, err = db.Exec("CREATE TABLE IF NOT EXISTS `history` " +
"( `id` INTEGER PRIMARY KEY AUTOINCREMENT, `url` TEXT );")
return
}
func AddLink(db *sql.DB, url string) (linkNo int64, err error) {
stmt, err := db.Prepare("INSERT INTO `links` (`url`) VALUES ($1);")
if err != nil {
return
}
defer stmt.Close()
r, err := stmt.Exec(url)
if err != nil {
return
}
linkNo, err = r.LastInsertId()
return
}
func GetLink(db *sql.DB, linkID int64) (url string, err error) {
stmt, err := db.Prepare("SELECT `url` FROM `links` WHERE id=$1;")
if err != nil {
return
}
defer stmt.Close()
err = stmt.QueryRow(linkID).Scan(&url)
return
}
func GetLinkID(db *sql.DB, url string) (linkID int64, err error) {
stmt, err := db.Prepare("SELECT `id` FROM `links` WHERE url=$1;")
if err != nil {
return
}
defer stmt.Close()
err = stmt.QueryRow(url).Scan(&linkID)
return
}
func AddHistoryURL(db *sql.DB, url string) (err error) {
stmt, err := db.Prepare("INSERT INTO `history` (`url`) VALUES ($1);")
if err != nil {
return
}
defer stmt.Close()
_, err = stmt.Exec(url)
return
}
type HistoryItem struct {
ID int64
URL string
}
func GetHistory(db *sql.DB) (history []HistoryItem, err error) {
rows, err := db.Query("SELECT `id`, `url` FROM `history`;")
if err != nil {
return
}
defer rows.Close()
for rows.Next() {
var h HistoryItem
err = rows.Scan(&h.ID, &h.URL)
if err != nil {
return
}
history = append(history, h)
}
return
}
func GetHistoryUrl(db *sql.DB, historyID int64) (url string, err error) {
stmt, err := db.Prepare("SELECT `url` FROM `history` WHERE id=$1;")
if err != nil {
return
}
defer stmt.Close()
err = stmt.QueryRow(historyID).Scan(&url)
return
}