1/* 2Copyright 2017 Google LLC 3 4Licensed under the Apache License, Version 2.0 (the "License"); 5you may not use this file except in compliance with the License. 6You may obtain a copy of the License at 7 8 http://www.apache.org/licenses/LICENSE-2.0 9 10Unless required by applicable law or agreed to in writing, software 11distributed under the License is distributed on an "AS IS" BASIS, 12WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13See the License for the specific language governing permissions and 14limitations under the License. 15*/ 16 17package spanner_test 18 19import ( 20 "context" 21 "errors" 22 "fmt" 23 "sync" 24 "time" 25 26 "cloud.google.com/go/spanner" 27 "google.golang.org/api/iterator" 28 sppb "google.golang.org/genproto/googleapis/spanner/v1" 29) 30 31func ExampleNewClient() { 32 ctx := context.Background() 33 const myDB = "projects/my-project/instances/my-instance/database/my-db" 34 client, err := spanner.NewClient(ctx, myDB) 35 if err != nil { 36 // TODO: Handle error. 37 } 38 _ = client // TODO: Use client. 39} 40 41const myDB = "projects/my-project/instances/my-instance/database/my-db" 42 43func ExampleNewClientWithConfig() { 44 ctx := context.Background() 45 const myDB = "projects/my-project/instances/my-instance/database/my-db" 46 client, err := spanner.NewClientWithConfig(ctx, myDB, spanner.ClientConfig{ 47 NumChannels: 10, 48 }) 49 if err != nil { 50 // TODO: Handle error. 51 } 52 _ = client // TODO: Use client. 53 client.Close() // Close client when done. 54} 55 56func ExampleClient_Single() { 57 ctx := context.Background() 58 client, err := spanner.NewClient(ctx, myDB) 59 if err != nil { 60 // TODO: Handle error. 61 } 62 iter := client.Single().Query(ctx, spanner.NewStatement("SELECT FirstName FROM Singers")) 63 _ = iter // TODO: iterate using Next or Do. 64} 65 66func ExampleClient_ReadOnlyTransaction() { 67 ctx := context.Background() 68 client, err := spanner.NewClient(ctx, myDB) 69 if err != nil { 70 // TODO: Handle error. 71 } 72 t := client.ReadOnlyTransaction() 73 defer t.Close() 74 // TODO: Read with t using Read, ReadRow, ReadUsingIndex, or Query. 75} 76 77func ExampleClient_ReadWriteTransaction() { 78 ctx := context.Background() 79 client, err := spanner.NewClient(ctx, myDB) 80 if err != nil { 81 // TODO: Handle error. 82 } 83 _, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error { 84 var balance int64 85 row, err := txn.ReadRow(ctx, "Accounts", spanner.Key{"alice"}, []string{"balance"}) 86 if err != nil { 87 // This function will be called again if this is an IsAborted error. 88 return err 89 } 90 if err := row.Column(0, &balance); err != nil { 91 return err 92 } 93 94 if balance <= 10 { 95 return errors.New("insufficient funds in account") 96 } 97 balance -= 10 98 m := spanner.Update("Accounts", []string{"user", "balance"}, []interface{}{"alice", balance}) 99 return txn.BufferWrite([]*spanner.Mutation{m}) 100 // The buffered mutation will be committed. If the commit fails with an 101 // IsAborted error, this function will be called again. 102 }) 103 if err != nil { 104 // TODO: Handle error. 105 } 106} 107 108func ExampleUpdate() { 109 ctx := context.Background() 110 client, err := spanner.NewClient(ctx, myDB) 111 if err != nil { 112 // TODO: Handle error. 113 } 114 _, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error { 115 row, err := txn.ReadRow(ctx, "Accounts", spanner.Key{"alice"}, []string{"balance"}) 116 if err != nil { 117 return err 118 } 119 var balance int64 120 if err := row.Column(0, &balance); err != nil { 121 return err 122 } 123 return txn.BufferWrite([]*spanner.Mutation{ 124 spanner.Update("Accounts", []string{"user", "balance"}, []interface{}{"alice", balance + 10}), 125 }) 126 }) 127 if err != nil { 128 // TODO: Handle error. 129 } 130} 131 132// This example is the same as the one for Update, except for the use of UpdateMap. 133func ExampleUpdateMap() { 134 ctx := context.Background() 135 client, err := spanner.NewClient(ctx, myDB) 136 if err != nil { 137 // TODO: Handle error. 138 } 139 _, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error { 140 row, err := txn.ReadRow(ctx, "Accounts", spanner.Key{"alice"}, []string{"balance"}) 141 if err != nil { 142 return err 143 } 144 var balance int64 145 if err := row.Column(0, &balance); err != nil { 146 return err 147 } 148 return txn.BufferWrite([]*spanner.Mutation{ 149 spanner.UpdateMap("Accounts", map[string]interface{}{ 150 "user": "alice", 151 "balance": balance + 10, 152 }), 153 }) 154 }) 155 if err != nil { 156 // TODO: Handle error. 157 } 158} 159 160// This example is the same as the one for Update, except for the use of 161// UpdateStruct. 162func ExampleUpdateStruct() { 163 ctx := context.Background() 164 client, err := spanner.NewClient(ctx, myDB) 165 if err != nil { 166 // TODO: Handle error. 167 } 168 type account struct { 169 User string `spanner:"user"` 170 Balance int64 `spanner:"balance"` 171 } 172 _, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error { 173 row, err := txn.ReadRow(ctx, "Accounts", spanner.Key{"alice"}, []string{"balance"}) 174 if err != nil { 175 return err 176 } 177 var balance int64 178 if err := row.Column(0, &balance); err != nil { 179 return err 180 } 181 m, err := spanner.UpdateStruct("Accounts", account{ 182 User: "alice", 183 Balance: balance + 10, 184 }) 185 if err != nil { 186 return err 187 } 188 return txn.BufferWrite([]*spanner.Mutation{m}) 189 }) 190 if err != nil { 191 // TODO: Handle error. 192 } 193} 194 195func ExampleClient_Apply() { 196 ctx := context.Background() 197 client, err := spanner.NewClient(ctx, myDB) 198 if err != nil { 199 // TODO: Handle error. 200 } 201 m := spanner.Update("Users", []string{"name", "email"}, []interface{}{"alice", "a@example.com"}) 202 _, err = client.Apply(ctx, []*spanner.Mutation{m}) 203 if err != nil { 204 // TODO: Handle error. 205 } 206} 207 208func ExampleInsert() { 209 m := spanner.Insert("Users", []string{"name", "email"}, []interface{}{"alice", "a@example.com"}) 210 _ = m // TODO: use with Client.Apply or in a ReadWriteTransaction. 211} 212 213func ExampleInsertMap() { 214 m := spanner.InsertMap("Users", map[string]interface{}{ 215 "name": "alice", 216 "email": "a@example.com", 217 }) 218 _ = m // TODO: use with Client.Apply or in a ReadWriteTransaction. 219} 220 221func ExampleInsertStruct() { 222 type User struct { 223 Name, Email string 224 } 225 u := User{Name: "alice", Email: "a@example.com"} 226 m, err := spanner.InsertStruct("Users", u) 227 if err != nil { 228 // TODO: Handle error. 229 } 230 _ = m // TODO: use with Client.Apply or in a ReadWriteTransaction. 231} 232 233func ExampleDelete() { 234 m := spanner.Delete("Users", spanner.Key{"alice"}) 235 _ = m // TODO: use with Client.Apply or in a ReadWriteTransaction. 236} 237 238func ExampleDelete_keyRange() { 239 m := spanner.Delete("Users", spanner.KeyRange{ 240 Start: spanner.Key{"alice"}, 241 End: spanner.Key{"bob"}, 242 Kind: spanner.ClosedClosed, 243 }) 244 _ = m // TODO: use with Client.Apply or in a ReadWriteTransaction. 245} 246 247func ExampleRowIterator_Next() { 248 ctx := context.Background() 249 client, err := spanner.NewClient(ctx, myDB) 250 if err != nil { 251 // TODO: Handle error. 252 } 253 iter := client.Single().Query(ctx, spanner.NewStatement("SELECT FirstName FROM Singers")) 254 defer iter.Stop() 255 for { 256 row, err := iter.Next() 257 if err == iterator.Done { 258 break 259 } 260 if err != nil { 261 // TODO: Handle error. 262 } 263 var firstName string 264 if err := row.Column(0, &firstName); err != nil { 265 // TODO: Handle error. 266 } 267 fmt.Println(firstName) 268 } 269} 270 271func ExampleRowIterator_Do() { 272 ctx := context.Background() 273 client, err := spanner.NewClient(ctx, myDB) 274 if err != nil { 275 // TODO: Handle error. 276 } 277 iter := client.Single().Query(ctx, spanner.NewStatement("SELECT FirstName FROM Singers")) 278 err = iter.Do(func(r *spanner.Row) error { 279 var firstName string 280 if err := r.Column(0, &firstName); err != nil { 281 return err 282 } 283 fmt.Println(firstName) 284 return nil 285 }) 286 if err != nil { 287 // TODO: Handle error. 288 } 289} 290 291func ExampleRow_Size() { 292 ctx := context.Background() 293 client, err := spanner.NewClient(ctx, myDB) 294 if err != nil { 295 // TODO: Handle error. 296 } 297 row, err := client.Single().ReadRow(ctx, "Accounts", spanner.Key{"alice"}, []string{"name", "balance"}) 298 if err != nil { 299 // TODO: Handle error. 300 } 301 fmt.Println(row.Size()) // 2 302} 303 304func ExampleRow_ColumnName() { 305 ctx := context.Background() 306 client, err := spanner.NewClient(ctx, myDB) 307 if err != nil { 308 // TODO: Handle error. 309 } 310 row, err := client.Single().ReadRow(ctx, "Accounts", spanner.Key{"alice"}, []string{"name", "balance"}) 311 if err != nil { 312 // TODO: Handle error. 313 } 314 fmt.Println(row.ColumnName(1)) // "balance" 315} 316 317func ExampleRow_ColumnIndex() { 318 ctx := context.Background() 319 client, err := spanner.NewClient(ctx, myDB) 320 if err != nil { 321 // TODO: Handle error. 322 } 323 row, err := client.Single().ReadRow(ctx, "Accounts", spanner.Key{"alice"}, []string{"name", "balance"}) 324 if err != nil { 325 // TODO: Handle error. 326 } 327 index, err := row.ColumnIndex("balance") 328 if err != nil { 329 // TODO: Handle error. 330 } 331 fmt.Println(index) 332} 333 334func ExampleRow_ColumnNames() { 335 ctx := context.Background() 336 client, err := spanner.NewClient(ctx, myDB) 337 if err != nil { 338 // TODO: Handle error. 339 } 340 row, err := client.Single().ReadRow(ctx, "Accounts", spanner.Key{"alice"}, []string{"name", "balance"}) 341 if err != nil { 342 // TODO: Handle error. 343 } 344 fmt.Println(row.ColumnNames()) 345} 346 347func ExampleRow_ColumnByName() { 348 ctx := context.Background() 349 client, err := spanner.NewClient(ctx, myDB) 350 if err != nil { 351 // TODO: Handle error. 352 } 353 row, err := client.Single().ReadRow(ctx, "Accounts", spanner.Key{"alice"}, []string{"name", "balance"}) 354 if err != nil { 355 // TODO: Handle error. 356 } 357 var balance int64 358 if err := row.ColumnByName("balance", &balance); err != nil { 359 // TODO: Handle error. 360 } 361 fmt.Println(balance) 362} 363 364func ExampleRow_Columns() { 365 ctx := context.Background() 366 client, err := spanner.NewClient(ctx, myDB) 367 if err != nil { 368 // TODO: Handle error. 369 } 370 row, err := client.Single().ReadRow(ctx, "Accounts", spanner.Key{"alice"}, []string{"name", "balance"}) 371 if err != nil { 372 // TODO: Handle error. 373 } 374 var name string 375 var balance int64 376 if err := row.Columns(&name, &balance); err != nil { 377 // TODO: Handle error. 378 } 379 fmt.Println(name, balance) 380} 381 382func ExampleRow_ToStruct() { 383 ctx := context.Background() 384 client, err := spanner.NewClient(ctx, myDB) 385 if err != nil { 386 // TODO: Handle error. 387 } 388 row, err := client.Single().ReadRow(ctx, "Accounts", spanner.Key{"alice"}, []string{"name", "balance"}) 389 if err != nil { 390 // TODO: Handle error. 391 } 392 393 type Account struct { 394 Name string 395 Balance int64 396 } 397 398 var acct Account 399 if err := row.ToStruct(&acct); err != nil { 400 // TODO: Handle error. 401 } 402 fmt.Println(acct) 403} 404 405func ExampleReadOnlyTransaction_Read() { 406 ctx := context.Background() 407 client, err := spanner.NewClient(ctx, myDB) 408 if err != nil { 409 // TODO: Handle error. 410 } 411 iter := client.Single().Read(ctx, "Users", 412 spanner.KeySets(spanner.Key{"alice"}, spanner.Key{"bob"}), 413 []string{"name", "email"}) 414 _ = iter // TODO: iterate using Next or Do. 415} 416 417func ExampleReadOnlyTransaction_ReadUsingIndex() { 418 ctx := context.Background() 419 client, err := spanner.NewClient(ctx, myDB) 420 if err != nil { 421 // TODO: Handle error. 422 } 423 iter := client.Single().ReadUsingIndex(ctx, "Users", 424 "UsersByEmail", 425 spanner.KeySets(spanner.Key{"a@example.com"}, spanner.Key{"b@example.com"}), 426 []string{"name", "email"}) 427 _ = iter // TODO: iterate using Next or Do. 428} 429 430func ExampleReadOnlyTransaction_ReadWithOptions() { 431 ctx := context.Background() 432 client, err := spanner.NewClient(ctx, myDB) 433 if err != nil { 434 // TODO: Handle error. 435 } 436 // Use an index, and limit to 100 rows at most. 437 iter := client.Single().ReadWithOptions(ctx, "Users", 438 spanner.KeySets(spanner.Key{"a@example.com"}, spanner.Key{"b@example.com"}), 439 []string{"name", "email"}, &spanner.ReadOptions{ 440 Index: "UsersByEmail", 441 Limit: 100, 442 }) 443 _ = iter // TODO: iterate using Next or Do. 444} 445 446func ExampleReadOnlyTransaction_ReadRow() { 447 ctx := context.Background() 448 client, err := spanner.NewClient(ctx, myDB) 449 if err != nil { 450 // TODO: Handle error. 451 } 452 row, err := client.Single().ReadRow(ctx, "Users", spanner.Key{"alice"}, 453 []string{"name", "email"}) 454 if err != nil { 455 // TODO: Handle error. 456 } 457 _ = row // TODO: use row 458} 459 460func ExampleReadOnlyTransaction_Query() { 461 ctx := context.Background() 462 client, err := spanner.NewClient(ctx, myDB) 463 if err != nil { 464 // TODO: Handle error. 465 } 466 iter := client.Single().Query(ctx, spanner.NewStatement("SELECT FirstName FROM Singers")) 467 _ = iter // TODO: iterate using Next or Do. 468} 469 470func ExampleNewStatement() { 471 stmt := spanner.NewStatement("SELECT FirstName, LastName FROM SINGERS WHERE LastName >= @start") 472 stmt.Params["start"] = "Dylan" 473 // TODO: Use stmt in Query. 474} 475 476func ExampleNewStatement_structLiteral() { 477 stmt := spanner.Statement{ 478 SQL: `SELECT FirstName, LastName FROM SINGERS WHERE LastName = ("Lea", "Martin")`, 479 } 480 _ = stmt // TODO: Use stmt in Query. 481} 482 483func ExampleStructParam() { 484 stmt := spanner.Statement{ 485 SQL: "SELECT * FROM SINGERS WHERE (FirstName, LastName) = @singerinfo", 486 Params: map[string]interface{}{ 487 "singerinfo": struct { 488 FirstName string 489 LastName string 490 }{"Bob", "Dylan"}, 491 }, 492 } 493 _ = stmt // TODO: Use stmt in Query. 494} 495 496func ExampleArrayOfStructParam() { 497 stmt := spanner.Statement{ 498 SQL: "SELECT * FROM SINGERS WHERE (FirstName, LastName) IN UNNEST(@singerinfo)", 499 Params: map[string]interface{}{ 500 "singerinfo": []struct { 501 FirstName string 502 LastName string 503 }{ 504 {"Ringo", "Starr"}, 505 {"John", "Lennon"}, 506 }, 507 }, 508 } 509 _ = stmt // TODO: Use stmt in Query. 510} 511 512func ExampleReadOnlyTransaction_Timestamp() { 513 ctx := context.Background() 514 client, err := spanner.NewClient(ctx, myDB) 515 if err != nil { 516 // TODO: Handle error. 517 } 518 txn := client.Single() 519 row, err := txn.ReadRow(ctx, "Users", spanner.Key{"alice"}, 520 []string{"name", "email"}) 521 if err != nil { 522 // TODO: Handle error. 523 } 524 readTimestamp, err := txn.Timestamp() 525 if err != nil { 526 // TODO: Handle error. 527 } 528 fmt.Println("read happened at", readTimestamp) 529 _ = row // TODO: use row 530} 531 532func ExampleReadOnlyTransaction_WithTimestampBound() { 533 ctx := context.Background() 534 client, err := spanner.NewClient(ctx, myDB) 535 if err != nil { 536 // TODO: Handle error. 537 } 538 txn := client.Single().WithTimestampBound(spanner.MaxStaleness(30 * time.Second)) 539 row, err := txn.ReadRow(ctx, "Users", spanner.Key{"alice"}, []string{"name", "email"}) 540 if err != nil { 541 // TODO: Handle error. 542 } 543 _ = row // TODO: use row 544 readTimestamp, err := txn.Timestamp() 545 if err != nil { 546 // TODO: Handle error. 547 } 548 fmt.Println("read happened at", readTimestamp) 549} 550 551func ExampleGenericColumnValue_Decode() { 552 // In real applications, rows can be retrieved by methods like client.Single().ReadRow(). 553 row, err := spanner.NewRow([]string{"intCol", "strCol"}, []interface{}{42, "my-text"}) 554 if err != nil { 555 // TODO: Handle error. 556 } 557 for i := 0; i < row.Size(); i++ { 558 var col spanner.GenericColumnValue 559 if err := row.Column(i, &col); err != nil { 560 // TODO: Handle error. 561 } 562 switch col.Type.Code { 563 case sppb.TypeCode_INT64: 564 var v int64 565 if err := col.Decode(&v); err != nil { 566 // TODO: Handle error. 567 } 568 fmt.Println("int", v) 569 case sppb.TypeCode_STRING: 570 var v string 571 if err := col.Decode(&v); err != nil { 572 // TODO: Handle error. 573 } 574 fmt.Println("string", v) 575 } 576 } 577 // Output: 578 // int 42 579 // string my-text 580} 581 582func ExampleClient_BatchReadOnlyTransaction() { 583 ctx := context.Background() 584 var ( 585 client *spanner.Client 586 txn *spanner.BatchReadOnlyTransaction 587 err error 588 ) 589 if client, err = spanner.NewClient(ctx, myDB); err != nil { 590 // TODO: Handle error. 591 } 592 defer client.Close() 593 if txn, err = client.BatchReadOnlyTransaction(ctx, spanner.StrongRead()); err != nil { 594 // TODO: Handle error. 595 } 596 defer txn.Close() 597 598 // Singer represents the elements in a row from the Singers table. 599 type Singer struct { 600 SingerID int64 601 FirstName string 602 LastName string 603 SingerInfo []byte 604 } 605 stmt := spanner.Statement{SQL: "SELECT * FROM Singers;"} 606 partitions, err := txn.PartitionQuery(ctx, stmt, spanner.PartitionOptions{}) 607 if err != nil { 608 // TODO: Handle error. 609 } 610 // Note: here we use multiple goroutines, but you should use separate 611 // processes/machines. 612 wg := sync.WaitGroup{} 613 for i, p := range partitions { 614 wg.Add(1) 615 go func(i int, p *spanner.Partition) { 616 defer wg.Done() 617 iter := txn.Execute(ctx, p) 618 defer iter.Stop() 619 for { 620 row, err := iter.Next() 621 if err == iterator.Done { 622 break 623 } else if err != nil { 624 // TODO: Handle error. 625 } 626 var s Singer 627 if err := row.ToStruct(&s); err != nil { 628 // TODO: Handle error. 629 } 630 _ = s // TODO: Process the row. 631 } 632 }(i, p) 633 } 634 wg.Wait() 635} 636 637func ExampleCommitTimestamp() { 638 ctx := context.Background() 639 client, err := spanner.NewClient(ctx, myDB) 640 if err != nil { 641 // TODO: Handle error. 642 } 643 644 type account struct { 645 User string 646 Creation spanner.NullTime // time.Time can also be used if column isNOT NULL 647 } 648 649 a := account{User: "Joe", Creation: spanner.NullTime{spanner.CommitTimestamp, true}} 650 m, err := spanner.InsertStruct("Accounts", a) 651 if err != nil { 652 // TODO: Handle error. 653 } 654 _, err = client.Apply(ctx, []*spanner.Mutation{m}, spanner.ApplyAtLeastOnce()) 655 if err != nil { 656 // TODO: Handle error. 657 } 658 659 if r, e := client.Single().ReadRow(ctx, "Accounts", spanner.Key{"Joe"}, []string{"User", "Creation"}); e != nil { 660 // TODO: Handle error. 661 } else { 662 var got account 663 if err := r.ToStruct(&got); err != nil { 664 // TODO: Handle error. 665 } 666 _ = got // TODO: Process row. 667 } 668} 669 670func ExampleStatement_regexpContains() { 671 // Search for accounts with valid emails using regexp as per: 672 // https://cloud.google.com/spanner/docs/functions-and-operators#regexp_contains 673 stmt := spanner.Statement{ 674 SQL: `SELECT * FROM users WHERE REGEXP_CONTAINS(email, @valid_email)`, 675 Params: map[string]interface{}{ 676 "valid_email": `\Q@\E`, 677 }, 678 } 679 _ = stmt // TODO: Use stmt in a query. 680} 681 682func ExampleKeySets() { 683 ctx := context.Background() 684 client, err := spanner.NewClient(ctx, myDB) 685 if err != nil { 686 // TODO: Handle error. 687 } 688 // Get some rows from the Accounts table using a secondary index. In this case we get all users who are in Georgia. 689 iter := client.Single().ReadUsingIndex(context.Background(), "Accounts", "idx_state", spanner.Key{"GA"}, []string{"state"}) 690 691 // Create a empty KeySet by calling the KeySets function with no parameters. 692 ks := spanner.KeySets() 693 694 // Loop the results of a previous query iterator. 695 for { 696 row, err := iter.Next() 697 if err == iterator.Done { 698 break 699 } else if err != nil { 700 // TODO: Handle error. 701 } 702 var id string 703 err = row.ColumnByName("User", &id) 704 if err != nil { 705 // TODO: Handle error. 706 } 707 ks = spanner.KeySets(spanner.KeySets(spanner.Key{id}, ks)) 708 } 709 710 _ = ks //TODO: Go use the KeySet in another query. 711 712} 713