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 "strings"
24)
25
26func doordie(db *sql.DB, s string, args ...interface{}) {
27 _, err := db.Exec(s, args...)
28 if err != nil {
29 log.Fatal(err)
30 }
31}
32
33func upgradedb() {
34 db := opendatabase()
35 dbversion := 0
36 getconfig("dbversion", &dbversion)
37 getconfig("servername", &serverName)
38
39 switch dbversion {
40 case 0:
41 doordie(db, "insert into config (key, value) values ('dbversion', 1)")
42 fallthrough
43 case 1:
44 doordie(db, "create table doovers(dooverid integer primary key, dt text, tries integer, username text, rcpt text, msg blob)")
45 doordie(db, "update config set value = 2 where key = 'dbversion'")
46 fallthrough
47 case 2:
48 doordie(db, "alter table honks add column convoy text")
49 doordie(db, "update honks set convoy = ''")
50 doordie(db, "create index idx_honksconvoy on honks(convoy)")
51 doordie(db, "create table xonkers (xonkerid integer primary key, xid text, ibox text, obox text, sbox text, pubkey text)")
52 doordie(db, "insert into xonkers (xid, ibox, obox, sbox, pubkey) select xid, '', '', '', pubkey from honkers where flavor = 'key'")
53 doordie(db, "delete from honkers where flavor = 'key'")
54 doordie(db, "create index idx_xonkerxid on xonkers(xid)")
55 doordie(db, "create table zonkers (zonkerid integer primary key, userid integer, name text, wherefore text)")
56 doordie(db, "create index idx_zonkersname on zonkers(name)")
57 doordie(db, "update config set value = 3 where key = 'dbversion'")
58 fallthrough
59 case 3:
60 doordie(db, "alter table honks add column whofore integer")
61 doordie(db, "update honks set whofore = 0")
62 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)")
63 doordie(db, "update config set value = 4 where key = 'dbversion'")
64 fallthrough
65 case 4:
66 doordie(db, "alter table honkers add column combos text")
67 doordie(db, "update honkers set combos = ''")
68 doordie(db, "update config set value = 5 where key = 'dbversion'")
69 fallthrough
70 case 5:
71 doordie(db, "delete from donks where honkid in (select honkid from honks where what = 'zonk')")
72 doordie(db, "delete from honks where what = 'zonk'")
73 doordie(db, "update config set value = 6 where key = 'dbversion'")
74 fallthrough
75 case 6:
76 doordie(db, "alter table honks add column format")
77 doordie(db, "update honks set format = 'html'")
78 doordie(db, "alter table honks add column precis")
79 doordie(db, "update honks set precis = ''")
80 doordie(db, "alter table honks add column oonker")
81 doordie(db, "update honks set oonker = ''")
82 doordie(db, "update config set value = 7 where key = 'dbversion'")
83 fallthrough
84 case 7:
85 users := allusers()
86 for _, u := range users {
87 h := fmt.Sprintf("https://%s/u/%s", serverName, u.Username)
88 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)
89 doordie(db, "update honks set honker = ?, whofore = 2 where userid = ? and honker = '' and what = 'bonk'", h, u.UserID)
90 }
91 doordie(db, "update config set value = 8 where key = 'dbversion'")
92 fallthrough
93 case 8:
94 doordie(db, "alter table files add column local integer")
95 doordie(db, "update files set local = 1")
96 doordie(db, "update config set value = 9 where key = 'dbversion'")
97 fallthrough
98 case 9:
99 doordie(db, "drop table xonkers")
100 doordie(db, "create table xonkers (xonkerid integer primary key, name text, info text, flavor text)")
101 doordie(db, "create index idx_xonkername on xonkers(name)")
102 doordie(db, "update config set value = 10 where key = 'dbversion'")
103 fallthrough
104 case 10:
105 doordie(db, "update zonkers set wherefore = 'zomain' where wherefore = 'zurl'")
106 doordie(db, "update zonkers set wherefore = 'zord' where wherefore = 'zword'")
107 doordie(db, "update config set value = 11 where key = 'dbversion'")
108 fallthrough
109 case 11:
110 doordie(db, "alter table users add column options text")
111 doordie(db, "update users set options = ''")
112 doordie(db, "update config set value = 12 where key = 'dbversion'")
113 fallthrough
114 case 12:
115 doordie(db, "create index idx_honksoonker on honks(oonker)")
116 doordie(db, "update config set value = 13 where key = 'dbversion'")
117 fallthrough
118 case 13:
119 doordie(db, "alter table honks add column flags integer")
120 doordie(db, "update honks set flags = 0")
121 doordie(db, "update config set value = 14 where key = 'dbversion'")
122 fallthrough
123 case 14:
124 doordie(db, "alter table honks add column onts text")
125 doordie(db, "update honks set onts = ''")
126 doordie(db, "create table onts (ontology text, honkid integer)")
127 doordie(db, "create index idx_ontology on onts(ontology)")
128 ontmap := make(map[int64][]string)
129 rows, err := db.Query("select honkid, noise from honks")
130 if err != nil {
131 log.Fatalf("can't query honks: %s", err)
132 }
133 for rows.Next() {
134 var honkid int64
135 var noise string
136 err := rows.Scan(&honkid, &noise)
137 if err != nil {
138 log.Fatalf("can't scan honks: %s", err)
139 }
140 o := ontologies(noise)
141 if len(o) > 0 {
142 ontmap[honkid] = o
143 }
144 }
145 rows.Close()
146 tx, err := db.Begin()
147 if err != nil {
148 log.Fatalf("can't begin: %s", err)
149 }
150 stmtHonk, err := tx.Prepare("update honks set onts = ? where honkid = ?")
151 if err != nil {
152 log.Fatal(err)
153 }
154 stmtOnts, err := tx.Prepare("insert into onts (ontology, honkid) values (?, ?)")
155 if err != nil {
156 log.Fatal(err)
157 }
158 for honkid, onts := range ontmap {
159 _, err = stmtHonk.Exec(strings.Join(onts, " "), honkid)
160 if err != nil {
161 log.Fatal(err)
162 }
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 = 15 where key = 'dbversion'")
175 fallthrough
176 case 15:
177 default:
178 log.Fatalf("can't upgrade unknown version %d", dbversion)
179 }
180 cleanupdb("30")
181 os.Exit(0)
182}