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)
25
26func doordie(db *sql.DB, s string, args ...interface{}) {
27 _, err := db.Exec(s, args...)
28 if err != nil {
29 log.Fatalf("can't run %s: %s", s, err)
30 }
31}
32
33func upgradedb() {
34 db := opendatabase()
35 dbversion := 0
36 getconfig("dbversion", &dbversion)
37 getconfig("servername", &serverName)
38
39 if dbversion < 13 {
40 log.Fatal("database is too old to upgrade")
41 }
42 switch dbversion {
43 case 13:
44 doordie(db, "alter table honks add column flags integer")
45 doordie(db, "update honks set flags = 0")
46 doordie(db, "update config set value = 14 where key = 'dbversion'")
47 fallthrough
48 case 14:
49 doordie(db, "create table onts (ontology text, honkid integer)")
50 doordie(db, "create index idx_ontology on onts(ontology)")
51 doordie(db, "update config set value = 15 where key = 'dbversion'")
52 fallthrough
53 case 15:
54 doordie(db, "delete from onts")
55 ontmap := make(map[int64][]string)
56 rows, err := db.Query("select honkid, noise from honks")
57 if err != nil {
58 log.Fatalf("can't query honks: %s", err)
59 }
60 re_more := regexp.MustCompile(`#<span>[[:alpha:]][[:alnum:]-]*`)
61 for rows.Next() {
62 var honkid int64
63 var noise string
64 err := rows.Scan(&honkid, &noise)
65 if err != nil {
66 log.Fatalf("can't scan honks: %s", err)
67 }
68 onts := ontologies(noise)
69 mo := re_more.FindAllString(noise, -1)
70 for _, o := range mo {
71 onts = append(onts, "#"+o[7:])
72 }
73 if len(onts) > 0 {
74 ontmap[honkid] = oneofakind(onts)
75 }
76 }
77 rows.Close()
78 tx, err := db.Begin()
79 if err != nil {
80 log.Fatalf("can't begin: %s", err)
81 }
82 stmtOnts, err := tx.Prepare("insert into onts (ontology, honkid) values (?, ?)")
83 if err != nil {
84 log.Fatal(err)
85 }
86 for honkid, onts := range ontmap {
87 for _, o := range onts {
88 _, err = stmtOnts.Exec(strings.ToLower(o), honkid)
89 if err != nil {
90 log.Fatal(err)
91 }
92 }
93 }
94 err = tx.Commit()
95 if err != nil {
96 log.Fatalf("can't commit: %s", err)
97 }
98 doordie(db, "update config set value = 16 where key = 'dbversion'")
99 fallthrough
100 case 16:
101 doordie(db, "alter table files add column description text")
102 doordie(db, "update files set description = name")
103 doordie(db, "update config set value = 17 where key = 'dbversion'")
104 fallthrough
105 case 17:
106 doordie(db, "create table forsaken (honkid integer, precis text, noise text)")
107 doordie(db, "update config set value = 18 where key = 'dbversion'")
108 fallthrough
109 case 18:
110 doordie(db, "create index idx_onthonkid on onts(honkid)")
111 doordie(db, "update config set value = 19 where key = 'dbversion'")
112 fallthrough
113 case 19:
114 doordie(db, "create table places (honkid integer, name text, latitude real, longitude real)")
115 doordie(db, "create index idx_placehonkid on places(honkid)")
116 fallthrough
117 case 20:
118 doordie(db, "alter table places add column url text")
119 doordie(db, "update places set url = ''")
120 doordie(db, "update config set value = 21 where key = 'dbversion'")
121 fallthrough
122 case 21:
123 // here we go...
124 initblobdb()
125 blobdb, err := sql.Open("sqlite3", blobdbname)
126 if err != nil {
127 log.Fatal(err)
128 }
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 for rows.Next() {
212 var userid int64
213 var name, wherefore string
214 err = rows.Scan(&userid, &name, &wherefore)
215 if err != nil {
216 log.Fatalf("error scanning zonker: %s", err)
217 }
218 f := new(Filter)
219 switch wherefore {
220 case "zord":
221 f.Text = name
222 f.Hide = true
223 case "zilence":
224 f.Text = name
225 f.Collapse = true
226 case "zoggle":
227 f.Actor = name
228 f.SkipMedia = true
229 case "zonker":
230 f.Actor = name
231 f.IncludeAudience = true
232 f.Reject = true
233 case "zomain":
234 f.Actor = name
235 f.IncludeAudience = true
236 f.Reject = true
237 }
238 filtmap[userid] = append(filtmap[userid], f)
239 }
240 rows.Close()
241 tx, err := db.Begin()
242 if err != nil {
243 log.Fatalf("can't begin: %s", err)
244 }
245 for userid, filts := range filtmap {
246 for _, f := range filts {
247 j, err := jsonify(f)
248 if err == nil {
249 _, err = tx.Exec("insert into hfcs (userid, json) values (?, ?)", userid, j)
250 }
251 if err != nil {
252 log.Fatalf("can't save filter: %s", err)
253 }
254 }
255 }
256 err = tx.Commit()
257 if err != nil {
258 log.Fatalf("can't commit: %s", err)
259 }
260 doordie(db, "delete from zonkers where wherefore in ('zord', 'zilence', 'zoggle', 'zonker', 'zomain')")
261 doordie(db, "update config set value = 24 where key = 'dbversion'")
262 fallthrough
263 case 24:
264
265 default:
266 log.Fatalf("can't upgrade unknown version %d", dbversion)
267 }
268 os.Exit(0)
269}