-
Notifications
You must be signed in to change notification settings - Fork 0
/
db.go
112 lines (91 loc) · 2.23 KB
/
db.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
// Package db provides helper functionality for interacting with the database.
package db
import (
"database/sql"
"fmt"
"github.com/matthewjwhite/spotty/spotify"
_ "github.com/mattn/go-sqlite3"
)
const (
tracksTable = "tracks"
uriCol = "uri"
dataCol = "data"
)
// DB is a wrapper for *sql.DB, for the purpose of "inheriting" methods like Close
// that can be used alongside the custom ones here.
type DB struct {
*sql.DB
}
// New returns DB struct, given a valid path to the local database file.
func New(path string) (DB, error) {
db, err := sql.Open("sqlite3", path)
if err != nil {
return DB{}, err
}
return DB{db}, nil
}
// Init initializes the database with the tracks table.
func (d DB) Init() error {
statement, err := d.Prepare(
fmt.Sprintf("CREATE TABLE IF NOT EXISTS %s "+
"(id INTEGER PRIMARY KEY, "+
"%s TEXT NOT NULL UNIQUE, %s TEXT NOT NULL UNIQUE)",
tracksTable, uriCol, dataCol))
if err != nil {
return err
}
_, err = statement.Exec()
if err != nil {
return err
}
return nil
}
// Insert inserts a track into the database.
func (d DB) Insert(t spotify.Track) error {
statement, err := d.Prepare(
fmt.Sprintf("INSERT INTO %s (%s, %s) VALUES (?, ?)",
tracksTable, uriCol, dataCol))
if err != nil {
return err
}
_, err = statement.Exec(t.URI, t.Data)
if err != nil {
return err
}
return nil
}
// Get retrieves the first track matching the specified pattern.
func (d DB) Get(match string) (spotify.Track, error) {
row := d.QueryRow(
fmt.Sprintf("SELECT %s, %s FROM %s WHERE %s LIKE ?",
uriCol, dataCol, tracksTable, dataCol),
"%"+match+"%")
var uri string
var data string
err := row.Scan(&uri, &data)
if err != nil {
return spotify.Track{}, err
}
return spotify.Track{URI: uri, Data: data}, nil
}
// GetAll gets all tracks.
func (d DB) GetAll() ([]spotify.Track, error) {
row, err := d.Query(
fmt.Sprintf("SELECT %s, %s FROM %s",
uriCol, dataCol, tracksTable))
if err != nil {
return nil, err
}
defer row.Close()
tracks := make([]spotify.Track, 0)
for row.Next() {
var uri string
var data string
err = row.Scan(&uri, &data)
if err != nil {
return nil, err
}
tracks = append(tracks, spotify.Track{URI: uri, Data: data})
}
return tracks, nil
}