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.Fatal(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, "alter table honks add column onts text")
126 doordie(db, "create table onts (ontology text, honkid integer)")
127 doordie(db, "create index idx_ontology on onts(ontology)")
128 doordie(db, "update config set value = 15 where key = 'dbversion'")
129 fallthrough
130 case 15:
131 doordie(db, "update honks set onts = ''")
132 doordie(db, "delete from onts")
133 ontmap := make(map[int64][]string)
134 rows, err := db.Query("select honkid, noise from honks")
135 if err != nil {
136 log.Fatalf("can't query honks: %s", err)
137 }
138 re_more := regexp.MustCompile(`#<span>[[:alpha:]][[:alnum:]-]*`)
139 for rows.Next() {
140 var honkid int64
141 var noise string
142 err := rows.Scan(&honkid, &noise)
143 if err != nil {
144 log.Fatalf("can't scan honks: %s", err)
145 }
146 onts := ontologies(noise)
147 mo := re_more.FindAllString(noise, -1)
148 for _, o := range mo {
149 onts = append(onts, "#"+o[7:])
150 }
151 if len(onts) > 0 {
152 ontmap[honkid] = oneofakind(onts)
153 }
154 }
155 rows.Close()
156 tx, err := db.Begin()
157 if err != nil {
158 log.Fatalf("can't begin: %s", err)
159 }
160 stmtHonk, err := tx.Prepare("update honks set onts = ? where honkid = ?")
161 if err != nil {
162 log.Fatal(err)
163 }
164 stmtOnts, err := tx.Prepare("insert into onts (ontology, honkid) values (?, ?)")
165 if err != nil {
166 log.Fatal(err)
167 }
168 for honkid, onts := range ontmap {
169 _, err = stmtHonk.Exec(strings.Join(onts, " "), honkid)
170 if err != nil {
171 log.Fatal(err)
172 }
173 for _, o := range onts {
174 _, err = stmtOnts.Exec(strings.ToLower(o), honkid)
175 if err != nil {
176 log.Fatal(err)
177 }
178 }
179 }
180 err = tx.Commit()
181 if err != nil {
182 log.Fatalf("can't commit: %s", err)
183 }
184 doordie(db, "update config set value = 16 where key = 'dbversion'")
185 fallthrough
186 case 16:
187 doordie(db, "alter table files add column description text")
188 doordie(db, "update files set description = name")
189 doordie(db, "update config set value = 17 where key = 'dbversion'")
190 fallthrough
191 case 17:
192 default:
193 log.Fatalf("can't upgrade unknown version %d", dbversion)
194 }
195 os.Exit(0)
196}