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
27var myVersion = 33
28
29func doordie(db *sql.DB, s string, args ...interface{}) {
30 _, err := db.Exec(s, args...)
31 if err != nil {
32 log.Fatalf("can't run %s: %s", s, err)
33 }
34}
35
36func upgradedb() {
37 db := opendatabase()
38 dbversion := 0
39 getconfig("dbversion", &dbversion)
40 getconfig("servername", &serverName)
41
42 if dbversion < 13 {
43 log.Fatal("database is too old to upgrade")
44 }
45 switch dbversion {
46 case 13:
47 doordie(db, "alter table honks add column flags integer")
48 doordie(db, "update honks set flags = 0")
49 doordie(db, "update config set value = 14 where key = 'dbversion'")
50 fallthrough
51 case 14:
52 doordie(db, "create table onts (ontology text, honkid integer)")
53 doordie(db, "create index idx_ontology on onts(ontology)")
54 doordie(db, "update config set value = 15 where key = 'dbversion'")
55 fallthrough
56 case 15:
57 doordie(db, "delete from onts")
58 ontmap := make(map[int64][]string)
59 rows, err := db.Query("select honkid, noise from honks")
60 if err != nil {
61 log.Fatalf("can't query honks: %s", err)
62 }
63 re_more := regexp.MustCompile(`#<span>[[:alpha:]][[:alnum:]-]*`)
64 for rows.Next() {
65 var honkid int64
66 var noise string
67 err := rows.Scan(&honkid, &noise)
68 if err != nil {
69 log.Fatalf("can't scan honks: %s", err)
70 }
71 onts := ontologies(noise)
72 mo := re_more.FindAllString(noise, -1)
73 for _, o := range mo {
74 onts = append(onts, "#"+o[7:])
75 }
76 if len(onts) > 0 {
77 ontmap[honkid] = oneofakind(onts)
78 }
79 }
80 rows.Close()
81 tx, err := db.Begin()
82 if err != nil {
83 log.Fatalf("can't begin: %s", err)
84 }
85 stmtOnts, err := tx.Prepare("insert into onts (ontology, honkid) values (?, ?)")
86 if err != nil {
87 log.Fatal(err)
88 }
89 for honkid, onts := range ontmap {
90 for _, o := range onts {
91 _, err = stmtOnts.Exec(strings.ToLower(o), honkid)
92 if err != nil {
93 log.Fatal(err)
94 }
95 }
96 }
97 err = tx.Commit()
98 if err != nil {
99 log.Fatalf("can't commit: %s", err)
100 }
101 doordie(db, "update config set value = 16 where key = 'dbversion'")
102 fallthrough
103 case 16:
104 doordie(db, "alter table files add column description text")
105 doordie(db, "update files set description = name")
106 doordie(db, "update config set value = 17 where key = 'dbversion'")
107 fallthrough
108 case 17:
109 doordie(db, "create table forsaken (honkid integer, precis text, noise text)")
110 doordie(db, "update config set value = 18 where key = 'dbversion'")
111 fallthrough
112 case 18:
113 doordie(db, "create index idx_onthonkid on onts(honkid)")
114 doordie(db, "update config set value = 19 where key = 'dbversion'")
115 fallthrough
116 case 19:
117 doordie(db, "create table places (honkid integer, name text, latitude real, longitude real)")
118 doordie(db, "create index idx_placehonkid on places(honkid)")
119 fallthrough
120 case 20:
121 doordie(db, "alter table places add column url text")
122 doordie(db, "update places set url = ''")
123 doordie(db, "update config set value = 21 where key = 'dbversion'")
124 fallthrough
125 case 21:
126 // here we go...
127 initblobdb()
128 blobdb := openblobdb()
129 tx, err := blobdb.Begin()
130 if err != nil {
131 log.Fatalf("can't begin: %s", err)
132 }
133 doordie(db, "drop index idx_filesxid")
134 doordie(db, "drop index idx_filesurl")
135 doordie(db, "create table filemeta (fileid integer primary key, xid text, name text, description text, url text, media text, local integer)")
136 doordie(db, "insert into filemeta select fileid, xid, name, description, url, media, local from files")
137 doordie(db, "create index idx_filesxid on filemeta(xid)")
138 doordie(db, "create index idx_filesurl on filemeta(url)")
139
140 rows, err := db.Query("select xid, media, content from files where local = 1")
141 if err != nil {
142 log.Fatal(err)
143 }
144 for rows.Next() {
145 var xid, media string
146 var data []byte
147 err = rows.Scan(&xid, &media, &data)
148 if err == nil {
149 _, err = tx.Exec("insert into filedata (xid, media, content) values (?, ?, ?)", xid, media, data)
150 }
151 if err != nil {
152 log.Fatalf("can't save filedata: %s", err)
153 }
154 }
155 rows.Close()
156 err = tx.Commit()
157 if err != nil {
158 log.Fatalf("can't commit: %s", err)
159 }
160 doordie(db, "drop table files")
161 doordie(db, "vacuum")
162 doordie(db, "update config set value = 22 where key = 'dbversion'")
163 fallthrough
164 case 22:
165 doordie(db, "create table honkmeta (honkid integer, genus text, json text)")
166 doordie(db, "create index idx_honkmetaid on honkmeta(honkid)")
167 doordie(db, "drop table forsaken") // don't bother saving this one
168 rows, err := db.Query("select honkid, name, latitude, longitude, url from places")
169 if err != nil {
170 log.Fatal(err)
171 }
172 places := make(map[int64]*Place)
173 for rows.Next() {
174 var honkid int64
175 p := new(Place)
176 err = rows.Scan(&honkid, &p.Name, &p.Latitude, &p.Longitude, &p.Url)
177 if err != nil {
178 log.Fatal(err)
179 }
180 places[honkid] = p
181 }
182 rows.Close()
183 tx, err := db.Begin()
184 if err != nil {
185 log.Fatalf("can't begin: %s", err)
186 }
187 for honkid, p := range places {
188 j, err := jsonify(p)
189 if err == nil {
190 _, err = tx.Exec("insert into honkmeta (honkid, genus, json) values (?, ?, ?)",
191 honkid, "place", j)
192 }
193 if err != nil {
194 log.Fatal(err)
195 }
196 }
197 err = tx.Commit()
198 if err != nil {
199 log.Fatalf("can't commit: %s", err)
200 }
201 doordie(db, "update config set value = 23 where key = 'dbversion'")
202 fallthrough
203 case 23:
204 doordie(db, "create table hfcs (hfcsid integer primary key, userid integer, json text)")
205 doordie(db, "create index idx_hfcsuser on hfcs(userid)")
206 rows, err := db.Query("select userid, name, wherefore from zonkers where wherefore in ('zord', 'zilence', 'zoggle', 'zonker', 'zomain')")
207 if err != nil {
208 log.Fatalf("can't query zonkers: %s", err)
209 }
210 filtmap := make(map[int64][]*Filter)
211 now := time.Now().UTC()
212 for rows.Next() {
213 var userid int64
214 var name, wherefore string
215 err = rows.Scan(&userid, &name, &wherefore)
216 if err != nil {
217 log.Fatalf("error scanning zonker: %s", err)
218 }
219 f := new(Filter)
220 f.Date = now
221 switch wherefore {
222 case "zord":
223 f.Name = "hide " + name
224 f.Text = name
225 f.Hide = true
226 case "zilence":
227 f.Name = "silence " + name
228 f.Text = name
229 f.Collapse = true
230 case "zoggle":
231 f.Name = "skip " + name
232 f.Actor = name
233 f.SkipMedia = true
234 case "zonker":
235 f.Name = "reject " + name
236 f.Actor = name
237 f.IncludeAudience = true
238 f.Reject = true
239 case "zomain":
240 f.Name = "reject " + name
241 f.Actor = name
242 f.IncludeAudience = true
243 f.Reject = true
244 }
245 filtmap[userid] = append(filtmap[userid], f)
246 }
247 rows.Close()
248 tx, err := db.Begin()
249 if err != nil {
250 log.Fatalf("can't begin: %s", err)
251 }
252 for userid, filts := range filtmap {
253 for _, f := range filts {
254 j, err := jsonify(f)
255 if err == nil {
256 _, err = tx.Exec("insert into hfcs (userid, json) values (?, ?)", userid, j)
257 }
258 if err != nil {
259 log.Fatalf("can't save filter: %s", err)
260 }
261 }
262 }
263 err = tx.Commit()
264 if err != nil {
265 log.Fatalf("can't commit: %s", err)
266 }
267 doordie(db, "delete from zonkers where wherefore in ('zord', 'zilence', 'zoggle', 'zonker', 'zomain')")
268 doordie(db, "update config set value = 24 where key = 'dbversion'")
269 fallthrough
270 case 24:
271 doordie(db, "update honks set convoy = 'missing-' || abs(random() % 987654321) where convoy = ''")
272 doordie(db, "update config set value = 25 where key = 'dbversion'")
273 fallthrough
274 case 25:
275 doordie(db, "delete from auth")
276 doordie(db, "alter table auth add column expiry text")
277 doordie(db, "update config set value = 26 where key = 'dbversion'")
278 fallthrough
279 case 26:
280 s := ""
281 getconfig("servermsg", &s)
282 if s == "" {
283 setconfig("servermsg", "<h2>Things happen.</h2>")
284 }
285 s = ""
286 getconfig("aboutmsg", &s)
287 if s == "" {
288 setconfig("aboutmsg", "<h3>What is honk?</h3><p>Honk is amazing!")
289 }
290 s = ""
291 getconfig("loginmsg", &s)
292 if s == "" {
293 setconfig("loginmsg", "<h2>login</h2>")
294 }
295 d := -1
296 getconfig("debug", &d)
297 if d == -1 {
298 setconfig("debug", 0)
299 }
300 doordie(db, "update config set value = 27 where key = 'dbversion'")
301 fallthrough
302 case 27:
303 createserveruser(db)
304 doordie(db, "update config set value = 28 where key = 'dbversion'")
305 fallthrough
306 case 28:
307 doordie(db, "drop table doovers")
308 doordie(db, "create table doovers(dooverid integer primary key, dt text, tries integer, userid integer, rcpt text, msg blob)")
309 doordie(db, "update config set value = 29 where key = 'dbversion'")
310 fallthrough
311 case 29:
312 doordie(db, "alter table honkers add column owner text")
313 doordie(db, "update honkers set owner = xid")
314 doordie(db, "update config set value = 30 where key = 'dbversion'")
315 fallthrough
316 case 30:
317 tx, err := db.Begin()
318 if err != nil {
319 log.Fatal(err)
320 }
321 rows, err := tx.Query("select userid, options from users")
322 if err != nil {
323 log.Fatal(err)
324 }
325 m := make(map[int64]string)
326 for rows.Next() {
327 var userid int64
328 var options string
329 err = rows.Scan(&userid, &options)
330 if err != nil {
331 log.Fatal(err)
332 }
333 var uo UserOptions
334 uo.SkinnyCSS = strings.Contains(options, " skinny ")
335 m[userid], err = jsonify(uo)
336 if err != nil {
337 log.Fatal(err)
338 }
339 }
340 rows.Close()
341 for u, o := range m {
342 _, err = tx.Exec("update users set options = ? where userid = ?", o, u)
343 if err != nil {
344 log.Fatal(err)
345 }
346 }
347 err = tx.Commit()
348 if err != nil {
349 log.Fatal(err)
350 }
351 doordie(db, "update config set value = 31 where key = 'dbversion'")
352 fallthrough
353 case 31:
354 doordie(db, "create table tracks (xid text, fetches text)")
355 doordie(db, "create index idx_trackhonkid on tracks(xid)")
356 doordie(db, "update config set value = 32 where key = 'dbversion'")
357 fallthrough
358 case 32:
359 doordie(db, "alter table xonkers add column dt text")
360 doordie(db, "update xonkers set dt = ?", time.Now().UTC().Format(dbtimeformat))
361 doordie(db, "update config set value = 33 where key = 'dbversion'")
362 fallthrough
363 case 33:
364
365 default:
366 log.Fatalf("can't upgrade unknown version %d", dbversion)
367 }
368 os.Exit(0)
369}