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 "fmt"
21 "log"
22 "os"
23 "regexp"
24 "strings"
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 switch dbversion {
41 case 0:
42 doordie(db, "insert into config (key, value) values ('dbversion', 1)")
43 fallthrough
44 case 1:
45 doordie(db, "create table doovers(dooverid integer primary key, dt text, tries integer, username text, rcpt text, msg blob)")
46 doordie(db, "update config set value = 2 where key = 'dbversion'")
47 fallthrough
48 case 2:
49 doordie(db, "alter table honks add column convoy text")
50 doordie(db, "update honks set convoy = ''")
51 doordie(db, "create index idx_honksconvoy on honks(convoy)")
52 doordie(db, "create table xonkers (xonkerid integer primary key, xid text, ibox text, obox text, sbox text, pubkey text)")
53 doordie(db, "insert into xonkers (xid, ibox, obox, sbox, pubkey) select xid, '', '', '', pubkey from honkers where flavor = 'key'")
54 doordie(db, "delete from honkers where flavor = 'key'")
55 doordie(db, "create index idx_xonkerxid on xonkers(xid)")
56 doordie(db, "create table zonkers (zonkerid integer primary key, userid integer, name text, wherefore text)")
57 doordie(db, "create index idx_zonkersname on zonkers(name)")
58 doordie(db, "update config set value = 3 where key = 'dbversion'")
59 fallthrough
60 case 3:
61 doordie(db, "alter table honks add column whofore integer")
62 doordie(db, "update honks set whofore = 0")
63 doordie(db, "update honks set whofore = 1 where honkid in (select honkid from honks join users on honks.userid = users.userid where instr(audience, username) > 0)")
64 doordie(db, "update config set value = 4 where key = 'dbversion'")
65 fallthrough
66 case 4:
67 doordie(db, "alter table honkers add column combos text")
68 doordie(db, "update honkers set combos = ''")
69 doordie(db, "update config set value = 5 where key = 'dbversion'")
70 fallthrough
71 case 5:
72 doordie(db, "delete from donks where honkid in (select honkid from honks where what = 'zonk')")
73 doordie(db, "delete from honks where what = 'zonk'")
74 doordie(db, "update config set value = 6 where key = 'dbversion'")
75 fallthrough
76 case 6:
77 doordie(db, "alter table honks add column format")
78 doordie(db, "update honks set format = 'html'")
79 doordie(db, "alter table honks add column precis")
80 doordie(db, "update honks set precis = ''")
81 doordie(db, "alter table honks add column oonker")
82 doordie(db, "update honks set oonker = ''")
83 doordie(db, "update config set value = 7 where key = 'dbversion'")
84 fallthrough
85 case 7:
86 users := allusers()
87 for _, u := range users {
88 h := fmt.Sprintf("https://%s/u/%s", serverName, u.Username)
89 doordie(db, fmt.Sprintf("update honks set xid = '%s/h/' || xid, honker = ?, whofore = 2 where userid = ? and honker = '' and (what = 'honk' or what = 'tonk')", h), h, u.UserID)
90 doordie(db, "update honks set honker = ?, whofore = 2 where userid = ? and honker = '' and what = 'bonk'", h, u.UserID)
91 }
92 doordie(db, "update config set value = 8 where key = 'dbversion'")
93 fallthrough
94 case 8:
95 doordie(db, "alter table files add column local integer")
96 doordie(db, "update files set local = 1")
97 doordie(db, "update config set value = 9 where key = 'dbversion'")
98 fallthrough
99 case 9:
100 doordie(db, "drop table xonkers")
101 doordie(db, "create table xonkers (xonkerid integer primary key, name text, info text, flavor text)")
102 doordie(db, "create index idx_xonkername on xonkers(name)")
103 doordie(db, "update config set value = 10 where key = 'dbversion'")
104 fallthrough
105 case 10:
106 doordie(db, "update zonkers set wherefore = 'zomain' where wherefore = 'zurl'")
107 doordie(db, "update zonkers set wherefore = 'zord' where wherefore = 'zword'")
108 doordie(db, "update config set value = 11 where key = 'dbversion'")
109 fallthrough
110 case 11:
111 doordie(db, "alter table users add column options text")
112 doordie(db, "update users set options = ''")
113 doordie(db, "update config set value = 12 where key = 'dbversion'")
114 fallthrough
115 case 12:
116 doordie(db, "create index idx_honksoonker on honks(oonker)")
117 doordie(db, "update config set value = 13 where key = 'dbversion'")
118 fallthrough
119 case 13:
120 doordie(db, "alter table honks add column flags integer")
121 doordie(db, "update honks set flags = 0")
122 doordie(db, "update config set value = 14 where key = 'dbversion'")
123 fallthrough
124 case 14:
125 doordie(db, "create table onts (ontology text, honkid integer)")
126 doordie(db, "create index idx_ontology on onts(ontology)")
127 doordie(db, "update config set value = 15 where key = 'dbversion'")
128 fallthrough
129 case 15:
130 doordie(db, "delete from onts")
131 ontmap := make(map[int64][]string)
132 rows, err := db.Query("select honkid, noise from honks")
133 if err != nil {
134 log.Fatalf("can't query honks: %s", err)
135 }
136 re_more := regexp.MustCompile(`#<span>[[:alpha:]][[:alnum:]-]*`)
137 for rows.Next() {
138 var honkid int64
139 var noise string
140 err := rows.Scan(&honkid, &noise)
141 if err != nil {
142 log.Fatalf("can't scan honks: %s", err)
143 }
144 onts := ontologies(noise)
145 mo := re_more.FindAllString(noise, -1)
146 for _, o := range mo {
147 onts = append(onts, "#"+o[7:])
148 }
149 if len(onts) > 0 {
150 ontmap[honkid] = oneofakind(onts)
151 }
152 }
153 rows.Close()
154 tx, err := db.Begin()
155 if err != nil {
156 log.Fatalf("can't begin: %s", err)
157 }
158 stmtOnts, err := tx.Prepare("insert into onts (ontology, honkid) values (?, ?)")
159 if err != nil {
160 log.Fatal(err)
161 }
162 for honkid, onts := range ontmap {
163 for _, o := range onts {
164 _, err = stmtOnts.Exec(strings.ToLower(o), honkid)
165 if err != nil {
166 log.Fatal(err)
167 }
168 }
169 }
170 err = tx.Commit()
171 if err != nil {
172 log.Fatalf("can't commit: %s", err)
173 }
174 doordie(db, "update config set value = 16 where key = 'dbversion'")
175 fallthrough
176 case 16:
177 doordie(db, "alter table files add column description text")
178 doordie(db, "update files set description = name")
179 doordie(db, "update config set value = 17 where key = 'dbversion'")
180 fallthrough
181 case 17:
182 doordie(db, "create table forsaken (honkid integer, precis text, noise text)")
183 doordie(db, "update config set value = 18 where key = 'dbversion'")
184 fallthrough
185 case 18:
186 doordie(db, "create index idx_onthonkid on onts(honkid)")
187 doordie(db, "update config set value = 19 where key = 'dbversion'")
188 fallthrough
189 case 19:
190 doordie(db, "create table places (honkid integer, name text, latitude real, longitude real)")
191 doordie(db, "create index idx_placehonkid on places(honkid)")
192 fallthrough
193 case 20:
194 doordie(db, "alter table places add column url text")
195 doordie(db, "update places set url = ''")
196 doordie(db, "update config set value = 21 where key = 'dbversion'")
197 fallthrough
198 case 21:
199 // here we go...
200 initblobdb()
201 blobdb, err := sql.Open("sqlite3", blobdbname)
202 if err != nil {
203 log.Fatal(err)
204 }
205 tx, err := blobdb.Begin()
206 if err != nil {
207 log.Fatalf("can't begin: %s", err)
208 }
209 doordie(db, "drop index idx_filesxid")
210 doordie(db, "drop index idx_filesurl")
211 doordie(db, "create table filemeta (fileid integer primary key, xid text, name text, description text, url text, media text, local integer)")
212 doordie(db, "insert into filemeta select fileid, xid, name, description, url, media, local from files")
213 doordie(db, "create index idx_filesxid on filemeta(xid)")
214 doordie(db, "create index idx_filesurl on filemeta(url)")
215
216 rows, err := db.Query("select xid, media, content from files where local = 1")
217 if err != nil {
218 log.Fatal(err)
219 }
220 for rows.Next() {
221 var xid, media string
222 var data []byte
223 err = rows.Scan(&xid, &media, &data)
224 if err == nil {
225 _, err = tx.Exec("insert into filedata (xid, media, content) values (?, ?, ?)", xid, media, data)
226 }
227 if err != nil {
228 log.Fatalf("can't save filedata: %s", err)
229 }
230 }
231 rows.Close()
232 err = tx.Commit()
233 if err != nil {
234 log.Fatalf("can't commit: %s", err)
235 }
236 doordie(db, "drop table files")
237 doordie(db, "vacuum")
238 doordie(db, "update config set value = 22 where key = 'dbversion'")
239 fallthrough
240 case 22:
241 doordie(db, "create table honkmeta (honkid integer, genus text, json text)")
242 doordie(db, "create index idx_honkmetaid on honkmeta(honkid)")
243 doordie(db, "drop table forsaken") // don't bother saving this one
244 rows, err := db.Query("select honkid, name, latitude, longitude, url from places")
245 if err != nil {
246 log.Fatal(err)
247 }
248 places := make(map[int64]*Place)
249 for rows.Next() {
250 var honkid int64
251 p := new(Place)
252 err = rows.Scan(&honkid, &p.Name, &p.Latitude, &p.Longitude, &p.Url)
253 if err != nil {
254 log.Fatal(err)
255 }
256 places[honkid] = p
257 }
258 rows.Close()
259 tx, err := db.Begin()
260 if err != nil {
261 log.Fatalf("can't begin: %s", err)
262 }
263 for honkid, p := range places {
264 j, err := jsonify(p)
265 if err == nil {
266 _, err = tx.Exec("insert into honkmeta (honkid, genus, json) values (?, ?, ?)",
267 honkid, "place", j)
268 }
269 if err != nil {
270 log.Fatal(err)
271 }
272 }
273 err = tx.Commit()
274 if err != nil {
275 log.Fatalf("can't commit: %s", err)
276 }
277 doordie(db, "update config set value = 23 where key = 'dbversion'")
278 fallthrough
279 case 23:
280 doordie(db, "create table hfcs (hfcsid integer primary key, userid integer, json text)")
281 doordie(db, "create index idx_hfcsuser on hfcs(userid)")
282 rows, err := db.Query("select userid, name, wherefore from zonkers where wherefore in ('zord', 'zilence', 'zoggle', 'zonker', 'zomain')")
283 if err != nil {
284 log.Fatalf("can't query zonkers: %s", err)
285 }
286 filtmap := make(map[int64][]*Filter)
287 for rows.Next() {
288 var userid int64
289 var name, wherefore string
290 err = rows.Scan(&userid, &name, &wherefore)
291 if err != nil {
292 log.Fatal("error scanning zonker: %s", err)
293 }
294 f := new(Filter)
295 switch wherefore {
296 case "zord":
297 f.Text = name
298 f.Hide = true
299 case "zilence":
300 f.Text = name
301 f.Collapse = true
302 case "zoggle":
303 f.Actor = name
304 f.SkipMedia = true
305 case "zonker":
306 f.Actor = name
307 f.IncludeAudience = true
308 f.Reject = true
309 case "zomain":
310 f.Actor = name
311 f.IncludeAudience = true
312 f.Reject = true
313 }
314 filtmap[userid] = append(filtmap[userid], f)
315 }
316 rows.Close()
317 tx, err := db.Begin()
318 if err != nil {
319 log.Fatalf("can't begin: %s", err)
320 }
321 for userid, filts := range filtmap {
322 for _, f := range filts {
323 j, err := jsonify(f)
324 if err == nil {
325 _, err = tx.Exec("insert into hfcs (userid, json) values (?, ?)", userid, j)
326 }
327 if err != nil {
328 log.Fatalf("can't save filter: %s", err)
329 }
330 }
331 }
332 err = tx.Commit()
333 if err != nil {
334 log.Fatalf("can't commit: %s", err)
335 }
336 doordie(db, "delete from zonkers where wherefore in ('zord', 'zilence', 'zoggle', 'zonker', 'zomain')")
337 doordie(db, "update config set value = 24 where key = 'dbversion'")
338 fallthrough
339 case 24:
340
341 default:
342 log.Fatalf("can't upgrade unknown version %d", dbversion)
343 }
344 os.Exit(0)
345}