-
Notifications
You must be signed in to change notification settings - Fork 0
/
database.go
140 lines (116 loc) · 2.95 KB
/
database.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
package database
import (
"database/sql"
"fmt"
"github.com/buckket/strahlemann/utils"
_ "github.com/mattn/go-sqlite3"
)
type StrahlemannDatabase struct {
*sql.DB
}
type Post struct {
ID int64
LastTweet int64
Content string
Position int
Complete bool
}
type Status struct {
AllEntries int
AllEntriesLength int
DoneEntries int
DoneEntiresLength int
LastTweet int64
CurrentPosition int
CurrentLenght int
NextTweet string
}
func (db *StrahlemannDatabase) CreateSchema() error {
schema := `
CREATE TABLE IF NOT EXISTS blog (
id INTEGER PRIMARY KEY AUTOINCREMENT,
last_tweet INTEGER,
content TEXT,
position INTEGER,
complete BOOLEAN
);
`
_, err := db.Exec(schema)
return err
}
func (db *StrahlemannDatabase) InsertPost(post *Post) error {
tx, err := db.Begin()
if err != nil {
return err
}
defer tx.Rollback()
insertPost := `INSERT INTO blog (last_tweet, content, position, complete) VALUES (?, ?, ?, ?)`
_, err = tx.Exec(insertPost, post.LastTweet, post.Content, post.Position, post.Complete)
if err != nil {
return err
}
if err = tx.Commit(); err != nil {
return err
}
return nil
}
func (db *StrahlemannDatabase) UpdatePost(post *Post) error {
tx, err := db.Begin()
if err != nil {
return err
}
defer tx.Rollback()
updatePost := `UPDATE blog SET last_tweet = ?, position = ?, complete = ? WHERE id = ?`
_, err = tx.Exec(updatePost, post.LastTweet, post.Position, post.Complete, post.ID)
if err != nil {
return err
}
if err = tx.Commit(); err != nil {
return err
}
return nil
}
func (db *StrahlemannDatabase) GetNextPost() (*Post, error) {
post := Post{}
qry := `SELECT id, last_tweet, content, position FROM blog WHERE complete = FALSE ORDER BY id ASC`
err := db.QueryRow(qry).Scan(&post.ID, &post.LastTweet, &post.Content, &post.Position)
if err != nil {
return nil, err
}
return &post, nil
}
func (db *StrahlemannDatabase) GetStatus() (*Status, error) {
status := Status{}
qry := `SELECT COUNT(*), COALESCE(SUM(LENGTH(content)), 0) FROM blog`
err := db.QueryRow(qry).Scan(&status.AllEntries, &status.AllEntriesLength)
if err != nil {
return nil, err
}
qry = `SELECT COUNT(*), COALESCE(SUM(LENGTH(content)), 0) FROM blog WHERE complete = TRUE`
err = db.QueryRow(qry).Scan(&status.DoneEntries, &status.DoneEntiresLength)
if err != nil {
return nil, err
}
if status.AllEntries == status.DoneEntries {
return &status, nil
}
post, err := db.GetNextPost()
if err != nil {
return nil, err
}
status.LastTweet = post.LastTweet
status.CurrentPosition = post.Position
status.CurrentLenght = len(post.Content)
status.NextTweet, _ = utils.ExtractTweet(post.Content[post.Position:])
return &status, nil
}
func New(target string) (*StrahlemannDatabase, error) {
db, err := sql.Open("sqlite3", fmt.Sprintf("%s?&_fk=true", target))
if err != nil {
return nil, err
}
if err = db.Ping(); err != nil {
return nil, err
}
return &StrahlemannDatabase{db}, nil
}