upgradedb.go (view raw)
1//
2// Copyright (c) 2019 Ted Unangst <tedu@tedunangst.com>
3//
4// Permission to use, copy, modify, and distribute this software for any
5// purpose with or without fee is hereby granted, provided that the above
6// copyright notice and this permission notice appear in all copies.
7//
8// THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
9// WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
10// MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
11// ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
12// WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
13// ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
14// OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
15
16package main
17
18import (
19 "database/sql"
20 "log"
21 "os"
22 "regexp"
23 "strings"
24 "time"
25)
26
27func doordie(db *sql.DB, s string, args ...interface{}) {
28 _, err := db.Exec(s, args...)
29 if err != nil {
30 log.Fatalf("can't run %s: %s", s, err)
31 }
32}
33
34func upgradedb() {
35 db := opendatabase()
36 dbversion := 0
37 getconfig("dbversion", &dbversion)
38 getconfig("servername", &serverName)
39
40 if dbversion < 13 {
41 log.Fatal("database is too old to upgrade")
42 }
43 switch dbversion {
44 case 13:
45 doordie(db, "alter table honks add column flags integer")
46 doordie(db, "update honks set flags = 0")
47 doordie(db, "update config set value = 14 where key = 'dbversion'")
48 fallthrough
49 case 14:
50 doordie(db, "create table onts (ontology text, honkid integer)")
51 doordie(db, "create index idx_ontology on onts(ontology)")
52 doordie(db, "update config set value = 15 where key = 'dbversion'")
53 fallthrough
54 case 15:
55 doordie(db, "delete from onts")
56 ontmap := make(map[int64][]string)
57 rows, err := db.Query("select honkid, noise from honks")
58 if err != nil {
59 log.Fatalf("can't query honks: %s", err)
60 }
61 re_more := regexp.MustCompile(`#<span>[[:alpha:]][[:alnum:]-]*`)
62 for rows.Next() {
63 var honkid int64
64 var noise string
65 err := rows.Scan(&honkid, &noise)
66 if err != nil {
67 log.Fatalf("can't scan honks: %s", err)
68 }
69 onts := ontologies(noise)
70 mo := re_more.FindAllString(noise, -1)
71 for _, o := range mo {
72 onts = append(onts, "#"+o[7:])
73 }
74 if len(onts) > 0 {
75 ontmap[honkid] = oneofakind(onts)
76 }
77 }
78 rows.Close()
79 tx, err := db.Begin()
80 if err != nil {
81 log.Fatalf("can't begin: %s", err)
82 }
83 stmtOnts, err := tx.Prepare("insert into onts (ontology, honkid) values (?, ?)")
84 if err != nil {
85 log.Fatal(err)
86 }
87 for honkid, onts := range ontmap {
88 for _, o := range onts {
89 _, err = stmtOnts.Exec(strings.ToLower(o), honkid)
90 if err != nil {
91 log.Fatal(err)
92 }
93 }
94 }
95 err = tx.Commit()
96 if err != nil {
97 log.Fatalf("can't commit: %s", err)
98 }
99 doordie(db, "update config set value = 16 where key = 'dbversion'")
100 fallthrough
101 case 16:
102 doordie(db, "alter table files add column description text")
103 doordie(db, "update files set description = name")
104 doordie(db, "update config set value = 17 where key = 'dbversion'")
105 fallthrough
106 case 17:
107 doordie(db, "create table forsaken (honkid integer, precis text, noise text)")
108 doordie(db, "update config set value = 18 where key = 'dbversion'")
109 fallthrough
110 case 18:
111 doordie(db, "create index idx_onthonkid on onts(honkid)")
112 doordie(db, "update config set value = 19 where key = 'dbversion'")
113 fallthrough
114 case 19:
115 doordie(db, "create table places (honkid integer, name text, latitude real, longitude real)")
116 doordie(db, "create index idx_placehonkid on places(honkid)")
117 fallthrough
118 case 20:
119 doordie(db, "alter table places add column url text")
120 doordie(db, "update places set url = ''")
121 doordie(db, "update config set value = 21 where key = 'dbversion'")
122 fallthrough
123 case 21:
124 // here we go...
125 initblobdb()
126 blobdb, err := sql.Open("sqlite3", blobdbname)
127 if err != nil {
128 log.Fatal(err)
129 }
130 tx, err := blobdb.Begin()
131 if err != nil {
132 log.Fatalf("can't begin: %s", err)
133 }
134 doordie(db, "drop index idx_filesxid")
135 doordie(db, "drop index idx_filesurl")
136 doordie(db, "create table filemeta (fileid integer primary key, xid text, name text, description text, url text, media text, local integer)")
137 doordie(db, "insert into filemeta select fileid, xid, name, description, url, media, local from files")
138 doordie(db, "create index idx_filesxid on filemeta(xid)")
139 doordie(db, "create index idx_filesurl on filemeta(url)")
140
141 rows, err := db.Query("select xid, media, content from files where local = 1")
142 if err != nil {
143 log.Fatal(err)
144 }
145 for rows.Next() {
146 var xid, media string
147 var data []byte
148 err = rows.Scan(&xid, &media, &data)
149 if err == nil {
150 _, err = tx.Exec("insert into filedata (xid, media, content) values (?, ?, ?)", xid, media, data)
151 }
152 if err != nil {
153 log.Fatalf("can't save filedata: %s", err)
154 }
155 }
156 rows.Close()
157 err = tx.Commit()
158 if err != nil {
159 log.Fatalf("can't commit: %s", err)
160 }
161 doordie(db, "drop table files")
162 doordie(db, "vacuum")
163 doordie(db, "update config set value = 22 where key = 'dbversion'")
164 fallthrough
165 case 22:
166 doordie(db, "create table honkmeta (honkid integer, genus text, json text)")
167 doordie(db, "create index idx_honkmetaid on honkmeta(honkid)")
168 doordie(db, "drop table forsaken") // don't bother saving this one
169 rows, err := db.Query("select honkid, name, latitude, longitude, url from places")
170 if err != nil {
171 log.Fatal(err)
172 }
173 places := make(map[int64]*Place)
174 for rows.Next() {
175 var honkid int64
176 p := new(Place)
177 err = rows.Scan(&honkid, &p.Name, &p.Latitude, &p.Longitude, &p.Url)
178 if err != nil {
179 log.Fatal(err)
180 }
181 places[honkid] = p
182 }
183 rows.Close()
184 tx, err := db.Begin()
185 if err != nil {
186 log.Fatalf("can't begin: %s", err)
187 }
188 for honkid, p := range places {
189 j, err := jsonify(p)
190 if err == nil {
191 _, err = tx.Exec("insert into honkmeta (honkid, genus, json) values (?, ?, ?)",
192 honkid, "place", j)
193 }
194 if err != nil {
195 log.Fatal(err)
196 }
197 }
198 err = tx.Commit()
199 if err != nil {
200 log.Fatalf("can't commit: %s", err)
201 }
202 doordie(db, "update config set value = 23 where key = 'dbversion'")
203 fallthrough
204 case 23:
205 doordie(db, "create table hfcs (hfcsid integer primary key, userid integer, json text)")
206 doordie(db, "create index idx_hfcsuser on hfcs(userid)")
207 rows, err := db.Query("select userid, name, wherefore from zonkers where wherefore in ('zord', 'zilence', 'zoggle', 'zonker', 'zomain')")
208 if err != nil {
209 log.Fatalf("can't query zonkers: %s", err)
210 }
211 filtmap := make(map[int64][]*Filter)
212 now := time.Now().UTC()
213 for rows.Next() {
214 var userid int64
215 var name, wherefore string
216 err = rows.Scan(&userid, &name, &wherefore)
217 if err != nil {
218 log.Fatalf("error scanning zonker: %s", err)
219 }
220 f := new(Filter)
221 f.Date = now
222 switch wherefore {
223 case "zord":
224 f.Name = "hide " + name
225 f.Text = name
226 f.Hide = true
227 case "zilence":
228 f.Name = "silence " + name
229 f.Text = name
230 f.Collapse = true
231 case "zoggle":
232 f.Name = "skip " + name
233 f.Actor = name
234 f.SkipMedia = true
235 case "zonker":
236 f.Name = "reject " + name
237 f.Actor = name
238 f.IncludeAudience = true
239 f.Reject = true
240 case "zomain":
241 f.Name = "reject " + name
242 f.Actor = name
243 f.IncludeAudience = true
244 f.Reject = true
245 }
246 filtmap[userid] = append(filtmap[userid], f)
247 }
248 rows.Close()
249 tx, err := db.Begin()
250 if err != nil {
251 log.Fatalf("can't begin: %s", err)
252 }
253 for userid, filts := range filtmap {
254 for _, f := range filts {
255 j, err := jsonify(f)
256 if err == nil {
257 _, err = tx.Exec("insert into hfcs (userid, json) values (?, ?)", userid, j)
258 }
259 if err != nil {
260 log.Fatalf("can't save filter: %s", err)
261 }
262 }
263 }
264 err = tx.Commit()
265 if err != nil {
266 log.Fatalf("can't commit: %s", err)
267 }
268 doordie(db, "delete from zonkers where wherefore in ('zord', 'zilence', 'zoggle', 'zonker', 'zomain')")
269 doordie(db, "update config set value = 24 where key = 'dbversion'")
270 fallthrough
271 case 24:
272 doordie(db, "update honks set convoy = 'missing-' || abs(random() % 987654321) where convoy = ''")
273 doordie(db, "update config set value = 25 where key = 'dbversion'")
274 fallthrough
275 case 25:
276 default:
277 log.Fatalf("can't upgrade unknown version %d", dbversion)
278 }
279 os.Exit(0)
280}