all repos — honk @ f3ea6e57843e1724ab523ca99595414c842db99a

my fork of honk

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 = 27
 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
307	default:
308		log.Fatalf("can't upgrade unknown version %d", dbversion)
309	}
310	os.Exit(0)
311}