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