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 = 30
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, err := sql.Open("sqlite3", blobdbname)
129 if err != nil {
130 log.Fatal(err)
131 }
132 tx, err := blobdb.Begin()
133 if err != nil {
134 log.Fatalf("can't begin: %s", err)
135 }
136 doordie(db, "drop index idx_filesxid")
137 doordie(db, "drop index idx_filesurl")
138 doordie(db, "create table filemeta (fileid integer primary key, xid text, name text, description text, url text, media text, local integer)")
139 doordie(db, "insert into filemeta select fileid, xid, name, description, url, media, local from files")
140 doordie(db, "create index idx_filesxid on filemeta(xid)")
141 doordie(db, "create index idx_filesurl on filemeta(url)")
142
143 rows, err := db.Query("select xid, media, content from files where local = 1")
144 if err != nil {
145 log.Fatal(err)
146 }
147 for rows.Next() {
148 var xid, media string
149 var data []byte
150 err = rows.Scan(&xid, &media, &data)
151 if err == nil {
152 _, err = tx.Exec("insert into filedata (xid, media, content) values (?, ?, ?)", xid, media, data)
153 }
154 if err != nil {
155 log.Fatalf("can't save filedata: %s", err)
156 }
157 }
158 rows.Close()
159 err = tx.Commit()
160 if err != nil {
161 log.Fatalf("can't commit: %s", err)
162 }
163 doordie(db, "drop table files")
164 doordie(db, "vacuum")
165 doordie(db, "update config set value = 22 where key = 'dbversion'")
166 fallthrough
167 case 22:
168 doordie(db, "create table honkmeta (honkid integer, genus text, json text)")
169 doordie(db, "create index idx_honkmetaid on honkmeta(honkid)")
170 doordie(db, "drop table forsaken") // don't bother saving this one
171 rows, err := db.Query("select honkid, name, latitude, longitude, url from places")
172 if err != nil {
173 log.Fatal(err)
174 }
175 places := make(map[int64]*Place)
176 for rows.Next() {
177 var honkid int64
178 p := new(Place)
179 err = rows.Scan(&honkid, &p.Name, &p.Latitude, &p.Longitude, &p.Url)
180 if err != nil {
181 log.Fatal(err)
182 }
183 places[honkid] = p
184 }
185 rows.Close()
186 tx, err := db.Begin()
187 if err != nil {
188 log.Fatalf("can't begin: %s", err)
189 }
190 for honkid, p := range places {
191 j, err := jsonify(p)
192 if err == nil {
193 _, err = tx.Exec("insert into honkmeta (honkid, genus, json) values (?, ?, ?)",
194 honkid, "place", j)
195 }
196 if err != nil {
197 log.Fatal(err)
198 }
199 }
200 err = tx.Commit()
201 if err != nil {
202 log.Fatalf("can't commit: %s", err)
203 }
204 doordie(db, "update config set value = 23 where key = 'dbversion'")
205 fallthrough
206 case 23:
207 doordie(db, "create table hfcs (hfcsid integer primary key, userid integer, json text)")
208 doordie(db, "create index idx_hfcsuser on hfcs(userid)")
209 rows, err := db.Query("select userid, name, wherefore from zonkers where wherefore in ('zord', 'zilence', 'zoggle', 'zonker', 'zomain')")
210 if err != nil {
211 log.Fatalf("can't query zonkers: %s", err)
212 }
213 filtmap := make(map[int64][]*Filter)
214 now := time.Now().UTC()
215 for rows.Next() {
216 var userid int64
217 var name, wherefore string
218 err = rows.Scan(&userid, &name, &wherefore)
219 if err != nil {
220 log.Fatalf("error scanning zonker: %s", err)
221 }
222 f := new(Filter)
223 f.Date = now
224 switch wherefore {
225 case "zord":
226 f.Name = "hide " + name
227 f.Text = name
228 f.Hide = true
229 case "zilence":
230 f.Name = "silence " + name
231 f.Text = name
232 f.Collapse = true
233 case "zoggle":
234 f.Name = "skip " + name
235 f.Actor = name
236 f.SkipMedia = true
237 case "zonker":
238 f.Name = "reject " + name
239 f.Actor = name
240 f.IncludeAudience = true
241 f.Reject = true
242 case "zomain":
243 f.Name = "reject " + name
244 f.Actor = name
245 f.IncludeAudience = true
246 f.Reject = true
247 }
248 filtmap[userid] = append(filtmap[userid], f)
249 }
250 rows.Close()
251 tx, err := db.Begin()
252 if err != nil {
253 log.Fatalf("can't begin: %s", err)
254 }
255 for userid, filts := range filtmap {
256 for _, f := range filts {
257 j, err := jsonify(f)
258 if err == nil {
259 _, err = tx.Exec("insert into hfcs (userid, json) values (?, ?)", userid, j)
260 }
261 if err != nil {
262 log.Fatalf("can't save filter: %s", err)
263 }
264 }
265 }
266 err = tx.Commit()
267 if err != nil {
268 log.Fatalf("can't commit: %s", err)
269 }
270 doordie(db, "delete from zonkers where wherefore in ('zord', 'zilence', 'zoggle', 'zonker', 'zomain')")
271 doordie(db, "update config set value = 24 where key = 'dbversion'")
272 fallthrough
273 case 24:
274 doordie(db, "update honks set convoy = 'missing-' || abs(random() % 987654321) where convoy = ''")
275 doordie(db, "update config set value = 25 where key = 'dbversion'")
276 fallthrough
277 case 25:
278 doordie(db, "delete from auth")
279 doordie(db, "alter table auth add column expiry text")
280 doordie(db, "update config set value = 26 where key = 'dbversion'")
281 fallthrough
282 case 26:
283 s := ""
284 getconfig("servermsg", &s)
285 if s == "" {
286 setconfig("servermsg", "<h2>Things happen.</h2>")
287 }
288 s = ""
289 getconfig("aboutmsg", &s)
290 if s == "" {
291 setconfig("aboutmsg", "<h3>What is honk?</h3>\n<p>Honk is amazing!")
292 }
293 s = ""
294 getconfig("loginmsg", &s)
295 if s == "" {
296 setconfig("loginmsg", "<h2>login</h2>")
297 }
298 d := -1
299 getconfig("debug", &d)
300 if d == -1 {
301 setconfig("debug", 0)
302 }
303 doordie(db, "update config set value = 27 where key = 'dbversion'")
304 fallthrough
305 case 27:
306 createserveruser(db)
307 doordie(db, "update config set value = 28 where key = 'dbversion'")
308 fallthrough
309 case 28:
310 doordie(db, "drop table doovers")
311 doordie(db, "create table doovers(dooverid integer primary key, dt text, tries integer, userid integer, rcpt text, msg blob)")
312 doordie(db, "update config set value = 29 where key = 'dbversion'")
313 fallthrough
314 case 29:
315 doordie(db, "alter table honkers add column owner text")
316 doordie(db, "update honkers set owner = xid")
317 doordie(db, "update config set value = 30 where key = 'dbversion'")
318 fallthrough
319 case 30:
320
321 default:
322 log.Fatalf("can't upgrade unknown version %d", dbversion)
323 }
324 os.Exit(0)
325}