github.com/nakagami/firebirdsql@v0.9.10/transaction_test.go (about) 1 /******************************************************************************* 2 The MIT License (MIT) 3 4 Copyright (c) 2016-2019 Hajime Nakagami 5 6 Permission is hereby granted, free of charge, to any person obtaining a copy of 7 this software and associated documentation files (the "Software"), to deal in 8 the Software without restriction, including without limitation the rights to 9 use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of 10 the Software, and to permit persons to whom the Software is furnished to do so, 11 subject to the following conditions: 12 13 The above copyright notice and this permission notice shall be included in all 14 copies or substantial portions of the Software. 15 16 THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR 17 IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS 18 FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR 19 COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER 20 IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN 21 CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. 22 *******************************************************************************/ 23 24 package firebirdsql 25 26 import ( 27 "database/sql" 28 "testing" 29 "time" 30 ) 31 32 func TestTransaction(t *testing.T) { 33 var n int 34 test_dsn := GetTestDSN("test_transaction_") 35 conn, err := sql.Open("firebirdsql_createdb", test_dsn) 36 if err != nil { 37 t.Fatalf("Error sql.Open(): %v", err) 38 } 39 conn.Exec("CREATE TABLE test_trans (s varchar(2048))") 40 conn.Close() 41 42 time.Sleep(1 * time.Second) 43 44 conn, err = sql.Open("firebirdsql", test_dsn) 45 if err != nil { 46 t.Fatalf("Error sql.Open(): %v", err) 47 } 48 err = conn.QueryRow("SELECT Count(*) FROM test_trans").Scan(&n) 49 if err != nil { 50 t.Fatalf("Error SELECT: %v", err) 51 } 52 if n != 0 { 53 t.Fatalf("Incorrect count: %v", n) 54 } 55 conn.Exec("INSERT INTO test_trans (s) values ('A')") 56 conn.Close() 57 58 time.Sleep(1 * time.Second) 59 60 conn, err = sql.Open("firebirdsql", test_dsn) 61 if err != nil { 62 t.Fatalf("sql.Open(): %v", err) 63 } 64 err = conn.QueryRow("SELECT Count(*) FROM test_trans").Scan(&n) 65 if err != nil { 66 t.Fatalf("Error SELECT: %v", err) 67 } 68 if n != 1 { 69 t.Fatalf("Incorrect count: %v", n) 70 } 71 72 // Transaction 73 tx, err := conn.Begin() 74 if err != nil { 75 t.Fatalf("Begin: %v", err) 76 } 77 78 // Rollback 79 err = tx.QueryRow("SELECT Count(*) FROM test_trans").Scan(&n) 80 if err != nil { 81 t.Fatalf("Error SELECT: %v", err) 82 } 83 if n != 1 { 84 t.Fatalf("Incorrect count: %v", n) 85 } 86 _, err = tx.Exec("INSERT INTO test_trans (s) values ('B')") 87 if err != nil { 88 t.Fatalf("Error Insert: %v", err) 89 } 90 err = tx.QueryRow("SELECT Count(*) FROM test_trans").Scan(&n) 91 if err != nil { 92 t.Fatalf("Error SELECT: %v", err) 93 } 94 if n != 2 { 95 t.Fatalf("Incorrect count: %v", n) 96 } 97 err = tx.Rollback() 98 if err != nil { 99 t.Fatalf("Error Rollback: %v", err) 100 } 101 102 tx, err = conn.Begin() 103 err = tx.QueryRow("SELECT Count(*) FROM test_trans").Scan(&n) 104 if err != nil { 105 t.Fatalf("Error SELECT: %v", err) 106 } 107 if n != 1 { 108 t.Fatalf("Incorrect count: %v", n) 109 } 110 111 // Commit 112 _, err = tx.Exec("INSERT INTO test_trans (s) values ('C')") 113 err = tx.Commit() 114 if err != nil { 115 t.Fatalf("Error Commit: %v", err) 116 } 117 tx, err = conn.Begin() 118 err = tx.QueryRow("SELECT Count(*) FROM test_trans").Scan(&n) 119 if err != nil { 120 t.Fatalf("Error SELECT: %v", err) 121 } 122 if n != 2 { 123 t.Fatalf("Incorrect count: %v", n) 124 } 125 126 // without Commit (Need commit manually) 127 _, err = tx.Exec("INSERT INTO test_trans (s) values ('D')") 128 tx, err = conn.Begin() 129 if err != nil { 130 t.Fatalf("Error Begin: %v", err) 131 } 132 133 err = tx.QueryRow("SELECT Count(*) FROM test_trans").Scan(&n) 134 if err != nil { 135 t.Fatalf("Error SELECT: %v", err) 136 } 137 if n != 2 { 138 t.Fatalf("Incorrect count: %v", n) 139 } 140 141 // Connection (autocommit) 142 _, err = conn.Exec("INSERT INTO test_trans (s) values ('E')") 143 if err != nil { 144 t.Fatalf("Error Insert: %v", err) 145 } 146 conn.Close() 147 148 time.Sleep(1 * time.Second) 149 150 conn, err = sql.Open("firebirdsql", test_dsn) 151 err = tx.QueryRow("SELECT Count(*) FROM test_trans").Scan(&n) 152 if err != nil { 153 t.Fatalf("Error SELECT: %v", err) 154 } 155 if n != 3 { 156 t.Fatalf("Incorrect count: %v", n) 157 } 158 159 conn.Close() 160 } 161 162 func TestIssue35(t *testing.T) { 163 test_dsn := GetTestDSN("test_issue35_") 164 conn, err := sql.Open("firebirdsql_createdb", test_dsn) 165 166 if err != nil { 167 t.Fatalf("Error connecting: %v", err) 168 } 169 170 tx, err := conn.Begin() 171 172 if err != nil { 173 t.Fatalf("Error Begin: %v", err) 174 } 175 176 err = tx.Commit() 177 178 if err != nil { 179 t.Fatalf("Error Commit: %v", err) 180 } 181 182 _, err = conn.Exec("CREATE TABLE test_issue35 (s varchar(2048))") 183 184 if err != nil { 185 t.Fatalf("Error CREATE TABLE: %v", err) 186 } 187 conn.Close() 188 189 time.Sleep(1 * time.Second) 190 191 conn, err = sql.Open("firebirdsql", test_dsn) 192 var n int 193 err = conn.QueryRow("SELECT Count(*) FROM test_issue35").Scan(&n) 194 if err != nil { 195 t.Fatalf("Error SELECT: %v", err) 196 } 197 if n != 0 { 198 t.Fatalf("Incorrect count: %v", n) 199 } 200 201 conn.Close() 202 } 203 204 func TestIssue38(t *testing.T) { 205 test_dsn := GetTestDSN("test_issue38_") 206 conn, err := sql.Open("firebirdsql_createdb", test_dsn) 207 208 if err != nil { 209 t.Fatalf("Error connecting: %v", err) 210 } 211 conn.Exec(` 212 CREATE TABLE test_issue38 ( 213 id INTEGER NOT NULL, 214 key VARCHAR(64), 215 value VARCHAR(64) 216 ) 217 `) 218 if err != nil { 219 t.Fatalf("Error CREATE TABLE: %v", err) 220 } 221 conn.Close() 222 223 time.Sleep(1 * time.Second) 224 225 conn, err = sql.Open("firebirdsql", test_dsn) 226 tx, err := conn.Begin() 227 228 if err != nil { 229 t.Fatalf("Error Begin: %v", err) 230 } 231 232 var rowId = sql.NullInt64{} 233 234 err = tx.QueryRow( 235 "INSERT INTO test_issue38 (id, key, value) VALUES (?, ?, ?) RETURNING id", 1, "testKey", "testValue").Scan(&rowId) 236 if err == nil { 237 t.Fatalf("'Dynamic SQL Error' is not occuerd.") 238 } 239 err = tx.Rollback() 240 if err != nil { 241 t.Fatalf("Error Rollback: %v", err) 242 } 243 244 conn.Close() 245 } 246 247 func TestIssue39(t *testing.T) { 248 conn, err := sql.Open("firebirdsql_createdb", GetTestDSN("test_issue39_")) 249 tx, err := conn.Begin() 250 251 if err != nil { 252 t.Fatalf("Error Begin: %v", err) 253 } 254 var rowId = sql.NullInt64{} 255 err = tx.QueryRow("select 5 / 0 from rdb$database").Scan(&rowId) 256 if err == nil { 257 t.Fatalf("'Dynamic SQL Error' is not occured.") 258 } 259 err = tx.Rollback() 260 if err != nil { 261 t.Fatalf("broken transaction, but error is not occured.") 262 } 263 264 conn.Close() 265 } 266 267 func TestIssue67(t *testing.T) { 268 test_dsn := GetTestDSN("test_issue67_") 269 conn, _ := sql.Open("firebirdsql_createdb", test_dsn) 270 var n int 271 conn.QueryRow("SELECT Count(*) FROM rdb$relations").Scan(&n) 272 err := conn.Close() 273 if err != nil { 274 t.Fatalf("Error Close: %v", err) 275 } 276 277 conn, _ = sql.Open("firebirdsql", test_dsn) 278 tx, _ := conn.Begin() 279 tx.QueryRow("SELECT Count(*) FROM rdb$relations").Scan(&n) 280 281 tx.Commit() 282 283 err = conn.Close() 284 if err != nil { 285 t.Fatalf("Error Close: %v", err) 286 } 287 288 } 289 290 func TestIssue89(t *testing.T) { 291 292 var noconn1, numberTrans, numberrelations int 293 294 // test transaction open on connection open 295 test_dsn := GetTestDSN("test_issue89_") 296 conn1, _ := sql.Open("firebirdsql_createdb", test_dsn) 297 298 conn2, _ := sql.Open("firebirdsql", test_dsn) 299 300 conn2.QueryRow("select count(*) from mon$transactions where mon$attachment_id <> current_connection").Scan(&numberTrans) 301 if numberTrans > 0 { 302 t.Fatalf("Transaction open without query runned") 303 } 304 305 conn2.Close() 306 307 // test if are more than 1 transaction open on first query 308 conn1.QueryRow("select mon$attachment_id from mon$attachments where mon$attachment_id = current_connection").Scan(&noconn1) 309 310 conn2, _ = sql.Open("firebirdsql", test_dsn) 311 conn2.QueryRow("select count(*) from mon$transactions where mon$attachment_id <> current_connection").Scan(&numberTrans) 312 313 if numberTrans > 2 { 314 t.Fatalf("More than 1 transaction open") 315 } 316 317 conn1.Close() 318 conn2.Close() 319 320 // test autocommit when rows is closed 321 conn1, _ = sql.Open("firebirdsql", test_dsn) 322 323 rows, _ := conn1.Query("select first 3 rdb$relation_id from rdb$relations") 324 325 rows.Next() 326 rows.Next() 327 328 rows.Close() 329 330 conn2, _ = sql.Open("firebirdsql", test_dsn) 331 conn2.QueryRow("select count(*) from mon$transactions where mon$attachment_id <> current_connection").Scan(&numberTrans) 332 333 if numberTrans != 1 { 334 t.Fatalf("Autocommit don't work") 335 } 336 337 conn1.Close() 338 conn2.Close() 339 340 // test autocommit on prepare statement 341 conn1, _ = sql.Open("firebirdsql", test_dsn) 342 stmt, _ := conn1.Prepare("select count(*) from rdb$relations") 343 err := stmt.QueryRow().Scan(&numberrelations) 344 345 if err != nil { 346 t.Fatalf("Error QueryRow of Prepare: %v", err) 347 } 348 349 stmt.Close() 350 351 stmt, _ = conn1.Prepare("select count(*) from rdb$relations") 352 353 rows, _ = stmt.Query("select first 3 rdb$relation_id from rdb$relations") 354 355 rows.Next() 356 rows.Next() 357 358 rows.Close() 359 360 conn2, _ = sql.Open("firebirdsql", test_dsn) 361 conn2.QueryRow("select count(*) from mon$transactions where mon$attachment_id <> current_connection").Scan(&numberTrans) 362 363 if numberTrans != 1 { 364 t.Fatalf("Autocommit in prepare don't work") 365 } 366 367 // test autocommit on prepare statement 368 conn1, _ = sql.Open("firebirdsql", test_dsn) 369 conn1.Exec("create table testprepareinsert (id integer)") 370 371 stmt, _ = conn1.Prepare("insert into testprepareinsert (id) values (?)") 372 373 stmt.Exec(1) 374 /* _, err = stmt.Exec(2) 375 if err == nil { 376 t.Fatalf("Autocommit in prepare don't work") 377 } 378 */ 379 stmt.Close() 380 381 conn2, _ = sql.Open("firebirdsql", test_dsn) 382 conn2.QueryRow("select count(*) from mon$transactions where mon$attachment_id <> current_connection").Scan(&numberTrans) 383 384 conn1, _ = sql.Open("firebirdsql", test_dsn) 385 txp, _ := conn1.Begin() 386 stmt, _ = txp.Prepare("insert into testprepareinsert (id) values (?)") 387 388 for i := 1; i <= 6; i++ { 389 _, err = stmt.Exec(i) 390 if err != nil { 391 t.Fatalf("Multiple execute of a prepared statement in same transaction don't work: %v", err) 392 } 393 } 394 395 txp.Commit() 396 conn2, _ = sql.Open("firebirdsql", test_dsn) 397 conn2.QueryRow("select count(*) from mon$transactions where mon$attachment_id <> current_connection").Scan(&numberTrans) 398 399 // test transaction open after a commit of another transaction 400 conn1, _ = sql.Open("firebirdsql", test_dsn) 401 conn2, _ = sql.Open("firebirdsql", test_dsn) 402 403 tx, err := conn1.Begin() 404 405 if err != nil { 406 t.Fatalf("Error opening new transaction: %v", err) 407 } 408 409 tx.QueryRow("select mon$attachment_id from mon$attachments where mon$attachment_id = current_connection").Scan(&noconn1) 410 411 tx.Commit() 412 413 err = conn1.QueryRow("select mon$attachment_id from mon$attachments where mon$attachment_id = current_connection").Scan(&noconn1) 414 if err != nil { 415 t.Fatalf("Error opening new transaction after last one committed or rollback: %v", err) 416 } 417 418 conn2, _ = sql.Open("firebirdsql", test_dsn) 419 conn2.QueryRow("select count(*) from mon$transactions where mon$attachment_id <> current_connection").Scan(&numberTrans) 420 421 conn1.Close() 422 conn2.Close() 423 424 }