1 package main 2 3 import ( 4 "crypto/sha1" 5 "database/sql" 6 "encoding/json" 7 "fmt" 8 "log" 9 ) 10 11 func upgradeQueries(db *sql.DB, queries ...string) error { 12 for _, query := range queries { 13 if _, err := db.Exec(query); err != nil { 14 return err 15 } 16 } 17 return nil 18 } 19 20 type eventJSON struct { 21 ID int 22 Note, ClientRef, InvoiceNote, InvoiceFrom, InvoiceTo string 23 } 24 25 type driverJSON struct { 26 ID int 27 Note string 28 Pos int 29 Show bool 30 } 31 32 func upgradeDB(db *sql.DB) error { 33 var version int 34 db.QueryRow("SELECT [Version] FROM [Settings];").Scan(&version) 35 if version == 0 { 36 log.Println("Upgrading to database version 1") 37 if err := upgradeQueries(db, 38 "ALTER TABLE [Settings] ADD [Version] INTEGER;", 39 "ALTER TABLE [Settings] ADD [InvoiceHeader] TEXT NOT NULL DEFAULT '';", 40 "ALTER TABLE [Settings] ADD [EmailSMTP] TEXT NOT NULL DEFAULT '';", 41 "ALTER TABLE [Settings] ADD [EmailUsername] TEXT NOT NULL DEFAULT '';", 42 "ALTER TABLE [Settings] ADD [EmailPassword] TEXT NOT NULL DEFAULT '';", 43 "ALTER TABLE [Settings] ADD [EmailTemplate] TEXT NOT NULL DEFAULT '';", 44 "ALTER TABLE [Event] ADD [InvoiceNote] TEXT NOT NULL DEFAULT '';", 45 "ALTER TABLE [Event] ADD [InvoiceFrom] TEXT NOT NULL DEFAULT '';", 46 "ALTER TABLE [Event] ADD [InvoiceTo] TEXT NOT NULL DEFAULT '';", 47 "ALTER TABLE [Event] ADD [Other] TEXT NOT NULL DEFAULT '';", 48 "ALTER TABLE [Driver] ADD [Pos] INTEGER NOT NULL DEFAULT 0;", 49 "ALTER TABLE [Driver] ADD [Show] BOOLEAN NOT NULL DEFAULT TRUE;", 50 "ALTER TABLE [Client] ADD [Email] TEXT NOT NULL DEFAULT '';", 51 "CREATE TABLE [Users]([Username] TEXT, [Password] BLOB);", 52 "INSERT INTO [Users]([Username], [Password]) VALUES (\"admin\", x'"+fmt.Sprintf("%x", sha1.Sum([]byte("password")))+"');", 53 ); err != nil { 54 return err 55 } 56 log.Println(" Updated Table Structures") 57 r, err := db.Query("SELECT [ID], [Note] FROM [Event];") 58 if err != nil { 59 return err 60 } 61 var eventTodo []eventJSON 62 for r.Next() { 63 var ( 64 id int 65 note string 66 ) 67 if err = r.Scan(&id, ¬e); err != nil { 68 return err 69 } 70 if len(note) > 0 && note[0] == '{' { 71 var noteParts eventJSON 72 if err = json.Unmarshal([]byte(note), ¬eParts); err != nil { 73 continue 74 } 75 noteParts.ID = id 76 eventTodo = append(eventTodo, noteParts) 77 } 78 } 79 80 if err = r.Close(); err != nil { 81 return err 82 } 83 log.Println(" Updating Event table") 84 eventTx, err := db.Begin() 85 if err != nil { 86 return err 87 } 88 eventUpdate, err := eventTx.Prepare("UPDATE [Event] SET [Note] = ?, [InvoiceNote] = ?, [InvoiceFrom] = ?, [InvoiceTo] = ? WHERE [ID] = ?;") 89 if err != nil { 90 return err 91 } 92 for _, noteParts := range eventTodo { 93 if _, err := eventUpdate.Exec(noteParts.Note, noteParts.InvoiceNote, noteParts.InvoiceFrom, noteParts.InvoiceTo, noteParts.ID); err != nil { 94 return err 95 } 96 } 97 eventUpdate.Close() 98 if err = eventTx.Commit(); err != nil { 99 return err 100 } 101 102 log.Println(" Completed updating Event table") 103 104 log.Println(" Updating Driver table") 105 r, err = db.Query("SELECT [ID], [Note] FROM [Driver];") 106 if err != nil { 107 return err 108 } 109 var driverTodo []driverJSON 110 for r.Next() { 111 var ( 112 id int 113 note string 114 ) 115 if err = r.Scan(&id, ¬e); err != nil { 116 return err 117 } 118 if len(note) > 0 && note[0] == '{' { 119 var noteParts driverJSON 120 if err = json.Unmarshal([]byte(note), ¬eParts); err != nil { 121 continue 122 } 123 noteParts.ID = id 124 driverTodo = append(driverTodo, noteParts) 125 } 126 } 127 if err = r.Close(); err != nil { 128 return err 129 } 130 driverTx, err := db.Begin() 131 if err != nil { 132 return err 133 } 134 driverUpdate, err := driverTx.Prepare("UPDATE [Driver] SET [Note] = ?, [Pos] = ?, [Show] = ? WHERE [ID] = ?;") 135 if err != nil { 136 return err 137 } 138 for _, noteParts := range driverTodo { 139 if _, err = driverUpdate.Exec(noteParts.Note, noteParts.Pos, noteParts.Show, noteParts.ID); err != nil { 140 return err 141 } 142 } 143 144 driverUpdate.Close() 145 if err = driverTx.Commit(); err != nil { 146 return err 147 } 148 149 log.Println(" Completed updating Driver table") 150 151 db.Exec("UPDATE [Settings] SET [Version] = 1;") 152 version = 1 153 154 log.Println("Completed updating to version 1") 155 } 156 if version == 1 { 157 log.Println("Upgrading to database version 2") 158 if err := upgradeQueries(db, 159 "ALTER TABLE [Company] ADD [InvoiceNo] INTEGER;", 160 "ALTER TABLE [Client] ADD [Address] TEXT NOT NULL DEFAULT '';", 161 ); err != nil { 162 return err 163 } 164 db.Exec("UPDATE [Settings] SET [Version] = 2;") 165 version = 2 166 167 log.Println("Completed updating to version 2") 168 } 169 if version == 2 { 170 log.Println("Upgrading to database version 3") 171 if err := upgradeQueries(db, 172 "ALTER TABLE [Event] ADD [Booker] TEXT NOT NULL DEFAULT '';", 173 "ALTER TABLE [Event] ADD [FlightTime] TEXT NOT NULL DEFAULT '';", 174 "ALTER TABLE [Event] ADD [ClientReference] TEXT NOT NULL DEFAULT '';", 175 "UPDATE [Event] SET [ClientReference] = (SELECT [Client].[Reference] FROM [Client] WHERE [Client].[ID] = [Event].[ClientID]);", 176 ); err != nil { 177 return err 178 } 179 180 db.Exec("UPDATE [Settings] SET [Version] = 3;") 181 version = 3 182 183 log.Println("Completed updating to version 3") 184 } 185 if version == 3 { 186 log.Println("Upgrading to database version 4") 187 188 if err := upgradeQueries(db, 189 "CREATE TABLE [Profiles]([ID] INTEGER PRIMARY KEY AUTOINCREMENT, [Name] TEXT NOT NULL DEFAULT '', [InvoiceHeader] TEXT NOT NULL DEFAULT '', [VATPercent] REAL, [AdminPercent] REAL);", 190 "ALTER TABLE [Event] ADD [Profile] INTEGER DEFAULT 0;", 191 "INSERT INTO [Profiles]([ID], [Name], [InvoiceHeader], [VATPercent], [AdminPercent]) SELECT 0, \"DEFAULT\", [InvoiceHeader], [VATPercent], [AdminPercent] FROM [Settings];", 192 "CREATE TABLE [NEW_Settings]([TMUsername] TEXT, [TMPassword] TEXT, [TMTemplate] TEXT, [TMUseNumber] BOOLEAN DEFAULT 0 NOT NULL CHECK ([TMUseNumber] IN (0,1)), [TMFrom] TEXT, [VATPercent] REAL, [AdminPercent] REAL, [Port] INTEGER, [Unassigned] INTEGER, [AlarmTime] INTEGER, [Version] INTEGER, [InvoiceHeader] TEXT NOT NULL DEFAULT '', [EmailSMTP] TEXT NOT NULL DEFAULT '', [EmailUsername] TEXT NOT NULL DEFAULT '', [EmailPassword] TEXT NOT NULL DEFAULT '', [EmailTemplate] TEXT NOT NULL DEFAULT '');", 193 "INSERT INTO [NEW_Settings] ([TMUsername], [TMPassword], [TMTemplate], [TMUseNumber], [TMFrom], [VATPercent], [AdminPercent], [Port], [Unassigned], [AlarmTime], [Version], [InvoiceHeader], [EmailSMTP], [EmailUsername], [EmailPassword], [EmailTemplate]) SELECT [TMUsername], [TMPassword], [TMTemplate], [TMUseNumber], [TMFrom], [VATPercent], [AdminPercent], [Port], [Unassigned], [AlarmTime], [Version], [InvoiceHeader], [EmailSMTP], [EmailUsername], [EmailPassword], [EmailTemplate] FROM [Settings];", 194 "DROP TABLE [Settings];", 195 "ALTER TABLE [NEW_Settings] RENAME TO [Settings];", 196 ); err != nil { 197 return err 198 } 199 200 db.Exec("UPDATE [Settings] SET [Version] = 4;") 201 version = 4 202 log.Println("Completed updating to version 4") 203 } 204 if version == 4 { 205 log.Println("Upgrading to database version 5") 206 207 if err := upgradeQueries(db, 208 "ALTER TABLE [Settings] ADD [DefaultProfile] INTEGER DEFAULT 0;", 209 "ALTER TABLE [Profiles] ADD [InvoiceFooter] TEXT NOT NULL DEFAULT '';", 210 ); err != nil { 211 return err 212 } 213 214 db.Exec("UPDATE [Settings] SET [Version] = 5;") 215 version = 5 216 log.Println("Completed updating to version 5") 217 } 218 return nil 219 }