table_integration_test.go

Documentation: cloud.google.com/go/bigquery

     1  // Copyright 2022 Google LLC
     2  //
     3  // Licensed under the Apache License, Version 2.0 (the "License");
     4  // you may not use this file except in compliance with the License.
     5  // You may obtain a copy of the License at
     6  //
     7  //      http://www.apache.org/licenses/LICENSE-2.0
     8  //
     9  // Unless required by applicable law or agreed to in writing, software
    10  // distributed under the License is distributed on an "AS IS" BASIS,
    11  // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    12  // See the License for the specific language governing permissions and
    13  // limitations under the License.
    14  
    15  package bigquery
    16  
    17  import (
    18  	"context"
    19  	"fmt"
    20  	"net/http"
    21  	"strings"
    22  	"testing"
    23  	"time"
    24  
    25  	"cloud.google.com/go/iam"
    26  	"cloud.google.com/go/internal"
    27  	"cloud.google.com/go/internal/pretty"
    28  	"cloud.google.com/go/internal/testutil"
    29  	gax "github.com/googleapis/gax-go/v2"
    30  	"google.golang.org/api/iterator"
    31  )
    32  
    33  func TestIntegration_TableInvalidSchema(t *testing.T) {
    34  	// Check that creating a record field with an empty schema is an error.
    35  	if client == nil {
    36  		t.Skip("Integration tests skipped")
    37  	}
    38  	table := dataset.Table("t_bad")
    39  	schema := Schema{
    40  		{Name: "rec", Type: RecordFieldType, Schema: Schema{}},
    41  	}
    42  	err := table.Create(context.Background(), &TableMetadata{
    43  		Schema:         schema,
    44  		ExpirationTime: testTableExpiration.Add(5 * time.Minute),
    45  	})
    46  	if err == nil {
    47  		t.Fatal("want error, got nil")
    48  	}
    49  	if !hasStatusCode(err, http.StatusBadRequest) {
    50  		t.Fatalf("want a 400 error, got %v", err)
    51  	}
    52  }
    53  
    54  func TestIntegration_TableValidSchema(t *testing.T) {
    55  	if client == nil {
    56  		t.Skip("Integration tests skipped")
    57  	}
    58  	ctx := context.Background()
    59  	table := dataset.Table("t_bad")
    60  	schema := Schema{
    61  		{
    62  			Name: "range_dt",
    63  			Type: RangeFieldType,
    64  			RangeElementType: &RangeElementType{
    65  				Type: DateTimeFieldType,
    66  			},
    67  		},
    68  		{Name: "rec", Type: RecordFieldType, Schema: Schema{
    69  			{Name: "inner", Type: IntegerFieldType},
    70  		}},
    71  	}
    72  	err := table.Create(ctx, &TableMetadata{
    73  		Schema: schema,
    74  	})
    75  	if err != nil {
    76  		t.Fatalf("table.Create: %v", err)
    77  	}
    78  
    79  	meta, err := table.Metadata(ctx)
    80  	if err != nil {
    81  		t.Fatalf("table.Metadata: %v", err)
    82  	}
    83  	if diff := testutil.Diff(meta.Schema, schema); diff != "" {
    84  		t.Fatalf("got=-, want=+:\n%s", diff)
    85  	}
    86  }
    87  
    88  func TestIntegration_TableCreateWithConstraints(t *testing.T) {
    89  	if client == nil {
    90  		t.Skip("Integration tests skipped")
    91  	}
    92  	table := dataset.Table("constraints")
    93  	schema := Schema{
    94  		{Name: "str_col", Type: StringFieldType, MaxLength: 10},
    95  		{Name: "bytes_col", Type: BytesFieldType, MaxLength: 150},
    96  		{Name: "num_col", Type: NumericFieldType, Precision: 20},
    97  		{Name: "bignumeric_col", Type: BigNumericFieldType, Precision: 30, Scale: 5},
    98  	}
    99  	err := table.Create(context.Background(), &TableMetadata{
   100  		Schema:         schema,
   101  		ExpirationTime: testTableExpiration.Add(5 * time.Minute),
   102  	})
   103  	if err != nil {
   104  		t.Fatalf("table create error: %v", err)
   105  	}
   106  
   107  	meta, err := table.Metadata(context.Background())
   108  	if err != nil {
   109  		t.Fatalf("couldn't get metadata: %v", err)
   110  	}
   111  
   112  	if diff := testutil.Diff(meta.Schema, schema); diff != "" {
   113  		t.Fatalf("got=-, want=+:\n%s", diff)
   114  	}
   115  }
   116  
   117  func TestIntegration_TableCreateWithDefaultValues(t *testing.T) {
   118  	if client == nil {
   119  		t.Skip("Integration tests skipped")
   120  	}
   121  	ctx := context.Background()
   122  	table := dataset.Table("defaultvalues")
   123  	schema := Schema{
   124  		{Name: "str_col", Type: StringFieldType, DefaultValueExpression: "'FOO'"},
   125  		{Name: "timestamp_col", Type: TimestampFieldType, DefaultValueExpression: "CURRENT_TIMESTAMP()"},
   126  	}
   127  	err := table.Create(ctx, &TableMetadata{
   128  		Schema:         schema,
   129  		ExpirationTime: testTableExpiration.Add(5 * time.Minute),
   130  	})
   131  	if err != nil {
   132  		t.Fatalf("table create error: %v", err)
   133  	}
   134  
   135  	meta, err := table.Metadata(ctx)
   136  	if err != nil {
   137  		t.Fatalf("couldn't get metadata: %v", err)
   138  	}
   139  
   140  	if diff := testutil.Diff(meta.Schema, schema); diff != "" {
   141  		t.Fatalf("got=-, want=+:\n%s", diff)
   142  	}
   143  
   144  	// SQL creation
   145  	id, _ := table.Identifier(StandardSQLID)
   146  	sql := fmt.Sprintf(`
   147  	    CREATE OR REPLACE TABLE %s (
   148  			str_col STRING DEFAULT 'FOO',
   149  			timestamp_col TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
   150  		)`, id)
   151  	_, _, err = runQuerySQL(ctx, sql)
   152  	if err != nil {
   153  		t.Fatal(err)
   154  	}
   155  	meta, err = table.Metadata(ctx)
   156  	if err != nil {
   157  		t.Fatalf("couldn't get metadata after sql: %v", err)
   158  	}
   159  
   160  	if diff := testutil.Diff(meta.Schema, schema); diff != "" {
   161  		t.Fatalf("sql create: got=-, want=+:\n%s", diff)
   162  	}
   163  }
   164  
   165  func TestIntegration_TableCreateView(t *testing.T) {
   166  	if client == nil {
   167  		t.Skip("Integration tests skipped")
   168  	}
   169  	ctx := context.Background()
   170  	table := newTable(t, schema)
   171  	tableIdentifier, _ := table.Identifier(StandardSQLID)
   172  	defer table.Delete(ctx)
   173  
   174  	// Test that standard SQL views work.
   175  	view := dataset.Table("t_view_standardsql")
   176  	query := fmt.Sprintf("SELECT APPROX_COUNT_DISTINCT(name) FROM %s", tableIdentifier)
   177  	err := view.Create(context.Background(), &TableMetadata{
   178  		ViewQuery:      query,
   179  		UseStandardSQL: true,
   180  	})
   181  	if err != nil {
   182  		t.Fatalf("table.create: Did not expect an error, got: %v", err)
   183  	}
   184  	if err := view.Delete(ctx); err != nil {
   185  		t.Fatal(err)
   186  	}
   187  }
   188  
   189  func TestIntegration_TableMetadata(t *testing.T) {
   190  
   191  	if client == nil {
   192  		t.Skip("Integration tests skipped")
   193  	}
   194  	ctx := context.Background()
   195  	table := newTable(t, schema)
   196  	defer table.Delete(ctx)
   197  	// Check table metadata.
   198  	md, err := table.Metadata(ctx)
   199  	if err != nil {
   200  		t.Fatal(err)
   201  	}
   202  	// TODO(jba): check md more thorougly.
   203  	if got, want := md.FullID, fmt.Sprintf("%s:%s.%s", dataset.ProjectID, dataset.DatasetID, table.TableID); got != want {
   204  		t.Errorf("metadata.FullID: got %q, want %q", got, want)
   205  	}
   206  	if got, want := md.Type, RegularTable; got != want {
   207  		t.Errorf("metadata.Type: got %v, want %v", got, want)
   208  	}
   209  	if got, want := md.ExpirationTime, testTableExpiration; !got.Equal(want) {
   210  		t.Errorf("metadata.Type: got %v, want %v", got, want)
   211  	}
   212  
   213  	// Check that timePartitioning is nil by default
   214  	if md.TimePartitioning != nil {
   215  		t.Errorf("metadata.TimePartitioning: got %v, want %v", md.TimePartitioning, nil)
   216  	}
   217  
   218  	// Create tables that have time partitioning
   219  	partitionCases := []struct {
   220  		timePartitioning TimePartitioning
   221  		wantExpiration   time.Duration
   222  		wantField        string
   223  		wantPruneFilter  bool
   224  	}{
   225  		{TimePartitioning{}, time.Duration(0), "", false},
   226  		{TimePartitioning{Expiration: time.Second}, time.Second, "", false},
   227  		{TimePartitioning{RequirePartitionFilter: true}, time.Duration(0), "", true},
   228  		{
   229  			TimePartitioning{
   230  				Expiration:             time.Second,
   231  				Field:                  "date",
   232  				RequirePartitionFilter: true,
   233  			}, time.Second, "date", true},
   234  	}
   235  
   236  	schema2 := Schema{
   237  		{Name: "name", Type: StringFieldType},
   238  		{Name: "date", Type: DateFieldType},
   239  	}
   240  
   241  	clustering := &Clustering{
   242  		Fields: []string{"name"},
   243  	}
   244  
   245  	// Currently, clustering depends on partitioning.  Interleave testing of the two features.
   246  	for i, c := range partitionCases {
   247  		table := dataset.Table(fmt.Sprintf("t_metadata_partition_nocluster_%v", i))
   248  		clusterTable := dataset.Table(fmt.Sprintf("t_metadata_partition_cluster_%v", i))
   249  
   250  		// Create unclustered, partitioned variant and get metadata.
   251  		err = table.Create(context.Background(), &TableMetadata{
   252  			Schema:           schema2,
   253  			TimePartitioning: &c.timePartitioning,
   254  			ExpirationTime:   testTableExpiration,
   255  		})
   256  		if err != nil {
   257  			t.Fatal(err)
   258  		}
   259  		defer table.Delete(ctx)
   260  		md, err := table.Metadata(ctx)
   261  		if err != nil {
   262  			t.Fatal(err)
   263  		}
   264  
   265  		// Created clustered table and get metadata.
   266  		err = clusterTable.Create(context.Background(), &TableMetadata{
   267  			Schema:           schema2,
   268  			TimePartitioning: &c.timePartitioning,
   269  			ExpirationTime:   testTableExpiration,
   270  			Clustering:       clustering,
   271  		})
   272  		if err != nil {
   273  			t.Fatal(err)
   274  		}
   275  		clusterMD, err := clusterTable.Metadata(ctx)
   276  		if err != nil {
   277  			t.Fatal(err)
   278  		}
   279  
   280  		for _, v := range []*TableMetadata{md, clusterMD} {
   281  			got := v.TimePartitioning
   282  			want := &TimePartitioning{
   283  				Type:                   DayPartitioningType,
   284  				Expiration:             c.wantExpiration,
   285  				Field:                  c.wantField,
   286  				RequirePartitionFilter: c.wantPruneFilter,
   287  			}
   288  			if !testutil.Equal(got, want) {
   289  				t.Errorf("metadata.TimePartitioning: got %v, want %v", got, want)
   290  			}
   291  			// Manipulate RequirePartitionFilter at the table level.
   292  			mdUpdate := TableMetadataToUpdate{
   293  				RequirePartitionFilter: !want.RequirePartitionFilter,
   294  			}
   295  
   296  			newmd, err := table.Update(ctx, mdUpdate, "")
   297  			if err != nil {
   298  				t.Errorf("failed to invert RequirePartitionFilter on %s: %v", table.FullyQualifiedName(), err)
   299  			}
   300  			if newmd.RequirePartitionFilter == want.RequirePartitionFilter {
   301  				t.Errorf("inverting table-level RequirePartitionFilter on %s failed, want %t got %t", table.FullyQualifiedName(), !want.RequirePartitionFilter, newmd.RequirePartitionFilter)
   302  			}
   303  			// Also verify that the clone of RequirePartitionFilter in the TimePartitioning message is consistent.
   304  			if newmd.RequirePartitionFilter != newmd.TimePartitioning.RequirePartitionFilter {
   305  				t.Errorf("inconsistent RequirePartitionFilter.  Table: %t, TimePartitioning: %t", newmd.RequirePartitionFilter, newmd.TimePartitioning.RequirePartitionFilter)
   306  			}
   307  
   308  		}
   309  
   310  		if md.Clustering != nil {
   311  			t.Errorf("metadata.Clustering was not nil on unclustered table %s", table.TableID)
   312  		}
   313  		got := clusterMD.Clustering
   314  		want := clustering
   315  		if clusterMD.Clustering != clustering {
   316  			if !testutil.Equal(got, want) {
   317  				t.Errorf("metadata.Clustering: got %v, want %v", got, want)
   318  			}
   319  		}
   320  	}
   321  }
   322  
   323  func TestIntegration_TableMetadataOptions(t *testing.T) {
   324  	if client == nil {
   325  		t.Skip("Integration tests skipped")
   326  	}
   327  	ctx := context.Background()
   328  	testTable := dataset.Table(tableIDs.New())
   329  	id, _ := testTable.Identifier(StandardSQLID)
   330  	sql := "CREATE TABLE %s AS SELECT num FROM UNNEST(GENERATE_ARRAY(0,5)) as num"
   331  	q := client.Query(fmt.Sprintf(sql, id))
   332  	if _, err := q.Read(ctx); err != nil {
   333  		t.Fatalf("failed to create table: %v", err)
   334  	}
   335  
   336  	defaultMeta, err := testTable.Metadata(ctx)
   337  	if err != nil {
   338  		t.Fatalf("failed to get default metadata: %v", err)
   339  	}
   340  	if defaultMeta.NumBytes <= 0 {
   341  		t.Errorf("expected default positive NumBytes, got %d", defaultMeta.NumBytes)
   342  	}
   343  	if defaultMeta.LastModifiedTime.IsZero() {
   344  		t.Error("expected default LastModifiedTime to be populated, is zero value")
   345  	}
   346  	// Specify a subset of metadata.
   347  	basicMeta, err := testTable.Metadata(ctx, WithMetadataView(BasicMetadataView))
   348  	if err != nil {
   349  		t.Fatalf("failed to get basic metadata: %v", err)
   350  	}
   351  	if basicMeta.NumBytes != 0 {
   352  		t.Errorf("expected basic NumBytes to be zero, got %d", defaultMeta.NumBytes)
   353  	}
   354  	if !basicMeta.LastModifiedTime.IsZero() {
   355  		t.Errorf("expected basic LastModifiedTime to be zero, is %v", basicMeta.LastModifiedTime)
   356  	}
   357  }
   358  
   359  func TestIntegration_TableUpdateLabels(t *testing.T) {
   360  	if client == nil {
   361  		t.Skip("Integration tests skipped")
   362  	}
   363  	ctx := context.Background()
   364  	table := newTable(t, schema)
   365  	defer table.Delete(ctx)
   366  
   367  	var tm TableMetadataToUpdate
   368  	tm.SetLabel("label", "value")
   369  	md, err := table.Update(ctx, tm, "")
   370  	if err != nil {
   371  		t.Fatal(err)
   372  	}
   373  	if got, want := md.Labels["label"], "value"; got != want {
   374  		t.Errorf("got %q, want %q", got, want)
   375  	}
   376  	tm = TableMetadataToUpdate{}
   377  	tm.DeleteLabel("label")
   378  	md, err = table.Update(ctx, tm, "")
   379  	if err != nil {
   380  		t.Fatal(err)
   381  	}
   382  	if _, ok := md.Labels["label"]; ok {
   383  		t.Error("label still present after deletion")
   384  	}
   385  }
   386  
   387  func TestIntegration_Tables(t *testing.T) {
   388  	if client == nil {
   389  		t.Skip("Integration tests skipped")
   390  	}
   391  	ctx := context.Background()
   392  	table := newTable(t, schema)
   393  	defer table.Delete(ctx)
   394  	wantName := table.FullyQualifiedName()
   395  
   396  	// This test is flaky due to eventual consistency.
   397  	ctx, cancel := context.WithTimeout(ctx, 10*time.Second)
   398  	defer cancel()
   399  	err := internal.Retry(ctx, gax.Backoff{}, func() (stop bool, err error) {
   400  		// Iterate over tables in the dataset.
   401  		it := dataset.Tables(ctx)
   402  		var tableNames []string
   403  		for {
   404  			tbl, err := it.Next()
   405  			if err == iterator.Done {
   406  				break
   407  			}
   408  			if err != nil {
   409  				return false, err
   410  			}
   411  			tableNames = append(tableNames, tbl.FullyQualifiedName())
   412  		}
   413  		// Other tests may be running with this dataset, so there might be more
   414  		// than just our table in the list. So don't try for an exact match; just
   415  		// make sure that our table is there somewhere.
   416  		for _, tn := range tableNames {
   417  			if tn == wantName {
   418  				return true, nil
   419  			}
   420  		}
   421  		return false, fmt.Errorf("got %v\nwant %s in the list", tableNames, wantName)
   422  	})
   423  	if err != nil {
   424  		t.Fatal(err)
   425  	}
   426  }
   427  
   428  func TestIntegration_TableIAM(t *testing.T) {
   429  	if client == nil {
   430  		t.Skip("Integration tests skipped")
   431  	}
   432  	ctx := context.Background()
   433  	table := newTable(t, schema)
   434  	defer table.Delete(ctx)
   435  
   436  	// Check to confirm some of our default permissions.
   437  	checkedPerms := []string{"bigquery.tables.get",
   438  		"bigquery.tables.getData", "bigquery.tables.update"}
   439  	perms, err := table.IAM().TestPermissions(ctx, checkedPerms)
   440  	if err != nil {
   441  		t.Fatalf("IAM().TestPermissions: %v", err)
   442  	}
   443  	if len(perms) != len(checkedPerms) {
   444  		t.Errorf("mismatch in permissions, got (%s) wanted (%s)", strings.Join(perms, " "), strings.Join(checkedPerms, " "))
   445  	}
   446  
   447  	// Get existing policy, add a binding for all authenticated users.
   448  	policy, err := table.IAM().Policy(ctx)
   449  	if err != nil {
   450  		t.Fatalf("IAM().Policy: %v", err)
   451  	}
   452  	wantedRole := iam.RoleName("roles/bigquery.dataViewer")
   453  	wantedMember := "allAuthenticatedUsers"
   454  	policy.Add(wantedMember, wantedRole)
   455  	if err := table.IAM().SetPolicy(ctx, policy); err != nil {
   456  		t.Fatalf("IAM().SetPolicy: %v", err)
   457  	}
   458  
   459  	// Verify policy mutations were persisted by refetching policy.
   460  	updatedPolicy, err := table.IAM().Policy(ctx)
   461  	if err != nil {
   462  		t.Fatalf("IAM.Policy (after update): %v", err)
   463  	}
   464  	foundRole := false
   465  	for _, r := range updatedPolicy.Roles() {
   466  		if r == wantedRole {
   467  			foundRole = true
   468  			break
   469  		}
   470  	}
   471  	if !foundRole {
   472  		t.Errorf("Did not find added role %s in the set of %d roles.",
   473  			wantedRole, len(updatedPolicy.Roles()))
   474  	}
   475  	members := updatedPolicy.Members(wantedRole)
   476  	foundMember := false
   477  	for _, m := range members {
   478  		if m == wantedMember {
   479  			foundMember = true
   480  			break
   481  		}
   482  	}
   483  	if !foundMember {
   484  		t.Errorf("Did not find member %s in role %s", wantedMember, wantedRole)
   485  	}
   486  }
   487  
   488  func TestIntegration_TableUpdate(t *testing.T) {
   489  	if client == nil {
   490  		t.Skip("Integration tests skipped")
   491  	}
   492  	ctx := context.Background()
   493  	table := newTable(t, schema)
   494  	defer table.Delete(ctx)
   495  
   496  	// Test Update of non-schema fields.
   497  	tm, err := table.Metadata(ctx)
   498  	if err != nil {
   499  		t.Fatal(err)
   500  	}
   501  	wantDescription := tm.Description + "more"
   502  	wantName := tm.Name + "more"
   503  	wantExpiration := tm.ExpirationTime.Add(time.Hour * 24)
   504  	got, err := table.Update(ctx, TableMetadataToUpdate{
   505  		Description:    wantDescription,
   506  		Name:           wantName,
   507  		ExpirationTime: wantExpiration,
   508  	}, tm.ETag)
   509  	if err != nil {
   510  		t.Fatal(err)
   511  	}
   512  	if got.Description != wantDescription {
   513  		t.Errorf("Description: got %q, want %q", got.Description, wantDescription)
   514  	}
   515  	if got.Name != wantName {
   516  		t.Errorf("Name: got %q, want %q", got.Name, wantName)
   517  	}
   518  	if got.ExpirationTime != wantExpiration {
   519  		t.Errorf("ExpirationTime: got %q, want %q", got.ExpirationTime, wantExpiration)
   520  	}
   521  	if !testutil.Equal(got.Schema, schema) {
   522  		t.Errorf("Schema: got %v, want %v", pretty.Value(got.Schema), pretty.Value(schema))
   523  	}
   524  
   525  	// Blind write succeeds.
   526  	_, err = table.Update(ctx, TableMetadataToUpdate{Name: "x"}, "")
   527  	if err != nil {
   528  		t.Fatal(err)
   529  	}
   530  	// Write with old etag fails.
   531  	_, err = table.Update(ctx, TableMetadataToUpdate{Name: "y"}, got.ETag)
   532  	if err == nil {
   533  		t.Fatal("Update with old ETag succeeded, wanted failure")
   534  	}
   535  
   536  	// Test schema update.
   537  	// Columns can be added. schema2 is the same as schema, except for the
   538  	// added column in the middle.
   539  	nested := Schema{
   540  		{Name: "nested", Type: BooleanFieldType},
   541  		{Name: "other", Type: StringFieldType},
   542  	}
   543  	schema2 := Schema{
   544  		schema[0],
   545  		{Name: "rec2", Type: RecordFieldType, Schema: nested},
   546  		schema[1],
   547  		schema[2],
   548  	}
   549  
   550  	got, err = table.Update(ctx, TableMetadataToUpdate{Schema: schema2}, "")
   551  	if err != nil {
   552  		t.Fatal(err)
   553  	}
   554  
   555  	// Wherever you add the column, it appears at the end.
   556  	schema3 := Schema{schema2[0], schema2[2], schema2[3], schema2[1]}
   557  	if !testutil.Equal(got.Schema, schema3) {
   558  		t.Errorf("add field:\ngot  %v\nwant %v",
   559  			pretty.Value(got.Schema), pretty.Value(schema3))
   560  	}
   561  
   562  	// Updating with the empty schema succeeds, but is a no-op.
   563  	got, err = table.Update(ctx, TableMetadataToUpdate{Schema: Schema{}}, "")
   564  	if err != nil {
   565  		t.Fatal(err)
   566  	}
   567  	if !testutil.Equal(got.Schema, schema3) {
   568  		t.Errorf("empty schema:\ngot  %v\nwant %v",
   569  			pretty.Value(got.Schema), pretty.Value(schema3))
   570  	}
   571  
   572  	// Error cases when updating schema.
   573  	for _, test := range []struct {
   574  		desc   string
   575  		fields Schema
   576  	}{
   577  		{"change from optional to required", Schema{
   578  			{Name: "name", Type: StringFieldType, Required: true},
   579  			schema3[1],
   580  			schema3[2],
   581  			schema3[3],
   582  		}},
   583  		{"add a required field", Schema{
   584  			schema3[0], schema3[1], schema3[2], schema3[3],
   585  			{Name: "req", Type: StringFieldType, Required: true},
   586  		}},
   587  		{"remove a field", Schema{schema3[0], schema3[1], schema3[2]}},
   588  		{"remove a nested field", Schema{
   589  			schema3[0], schema3[1], schema3[2],
   590  			{Name: "rec2", Type: RecordFieldType, Schema: Schema{nested[0]}}}},
   591  		{"remove all nested fields", Schema{
   592  			schema3[0], schema3[1], schema3[2],
   593  			{Name: "rec2", Type: RecordFieldType, Schema: Schema{}}}},
   594  	} {
   595  		_, err = table.Update(ctx, TableMetadataToUpdate{Schema: Schema(test.fields)}, "")
   596  		if err == nil {
   597  			t.Errorf("%s: want error, got nil", test.desc)
   598  		} else if !hasStatusCode(err, 400) {
   599  			t.Errorf("%s: want 400, got %v", test.desc, err)
   600  		}
   601  	}
   602  }
   603  
   604  func TestIntegration_TableUseLegacySQL(t *testing.T) {
   605  	// Test UseLegacySQL and UseStandardSQL for Table.Create.
   606  	if client == nil {
   607  		t.Skip("Integration tests skipped")
   608  	}
   609  	ctx := context.Background()
   610  	table := newTable(t, schema)
   611  	defer table.Delete(ctx)
   612  	for i, test := range useLegacySQLTests {
   613  		view := dataset.Table(fmt.Sprintf("t_view_%d", i))
   614  		tm := &TableMetadata{
   615  			ViewQuery:      fmt.Sprintf("SELECT word from %s", test.t),
   616  			UseStandardSQL: test.std,
   617  			UseLegacySQL:   test.legacy,
   618  		}
   619  		err := view.Create(ctx, tm)
   620  		gotErr := err != nil
   621  		if gotErr && !test.err {
   622  			t.Errorf("%+v:\nunexpected error: %v", test, err)
   623  		} else if !gotErr && test.err {
   624  			t.Errorf("%+v:\nsucceeded, but want error", test)
   625  		}
   626  		_ = view.Delete(ctx)
   627  	}
   628  }
   629  
   630  func TestIntegration_TableDefaultCollation(t *testing.T) {
   631  	// Test DefaultCollation for Table.Create and Table.Update
   632  	if client == nil {
   633  		t.Skip("Integration tests skipped")
   634  	}
   635  	ctx := context.Background()
   636  	table := dataset.Table(tableIDs.New())
   637  	caseInsensitiveCollation := "und:ci"
   638  	caseSensitiveCollation := ""
   639  	err := table.Create(context.Background(), &TableMetadata{
   640  		Schema:           schema,
   641  		DefaultCollation: caseInsensitiveCollation,
   642  		ExpirationTime:   testTableExpiration,
   643  	})
   644  	if err != nil {
   645  		t.Fatal(err)
   646  	}
   647  	defer table.Delete(ctx)
   648  	md, err := table.Metadata(ctx)
   649  	if err != nil {
   650  		t.Fatal(err)
   651  	}
   652  	if md.DefaultCollation != caseInsensitiveCollation {
   653  		t.Fatalf("expected default collation to be %q, but found %q", caseInsensitiveCollation, md.DefaultCollation)
   654  	}
   655  	for _, field := range md.Schema {
   656  		if field.Type == StringFieldType {
   657  			if field.Collation != caseInsensitiveCollation {
   658  				t.Fatalf("expected all columns to have collation %q, but found %q on field :%v", caseInsensitiveCollation, field.Collation, field.Name)
   659  			}
   660  		}
   661  	}
   662  
   663  	// Update table DefaultCollation to case-sensitive
   664  	md, err = table.Update(ctx, TableMetadataToUpdate{
   665  		DefaultCollation: caseSensitiveCollation,
   666  	}, "")
   667  	if err != nil {
   668  		t.Fatal(err)
   669  	}
   670  	if md.DefaultCollation != caseSensitiveCollation {
   671  		t.Fatalf("expected default collation to be %q, but found %q", caseSensitiveCollation, md.DefaultCollation)
   672  	}
   673  
   674  	// Add a field with different case-insensitive collation
   675  	updatedSchema := md.Schema
   676  	updatedSchema = append(updatedSchema, &FieldSchema{
   677  		Name:      "another_name",
   678  		Type:      StringFieldType,
   679  		Collation: caseInsensitiveCollation,
   680  	})
   681  	md, err = table.Update(ctx, TableMetadataToUpdate{
   682  		Schema: updatedSchema,
   683  	}, "")
   684  	if err != nil {
   685  		t.Fatal(err)
   686  	}
   687  	if md.DefaultCollation != caseSensitiveCollation {
   688  		t.Fatalf("expected default collation to be %q, but found %q", caseSensitiveCollation, md.DefaultCollation)
   689  	}
   690  	for _, field := range md.Schema {
   691  		if field.Type == StringFieldType {
   692  			if field.Collation != caseInsensitiveCollation {
   693  				t.Fatalf("expected all columns to have collation %q, but found %q on field :%v", caseInsensitiveCollation, field.Collation, field.Name)
   694  			}
   695  		}
   696  	}
   697  }
   698  
   699  func TestIntegration_TableConstraintsPK(t *testing.T) {
   700  	// Test Primary Keys for Table.Create and Table.Update
   701  	if client == nil {
   702  		t.Skip("Integration tests skipped")
   703  	}
   704  	ctx := context.Background()
   705  	table := dataset.Table(tableIDs.New())
   706  	err := table.Create(context.Background(), &TableMetadata{
   707  		Schema: schema,
   708  		TableConstraints: &TableConstraints{
   709  			PrimaryKey: &PrimaryKey{
   710  				Columns: []string{"name"},
   711  			},
   712  		},
   713  		ExpirationTime: testTableExpiration,
   714  	})
   715  	if err != nil {
   716  		t.Fatal(err)
   717  	}
   718  	defer table.Delete(ctx)
   719  	md, err := table.Metadata(ctx)
   720  	if err != nil {
   721  		t.Fatal(err)
   722  	}
   723  	if md.TableConstraints.PrimaryKey.Columns[0] != "name" {
   724  		t.Fatalf("expected table primary key to contain column `name`, but found %q", md.TableConstraints.PrimaryKey.Columns)
   725  	}
   726  
   727  	md, err = table.Update(ctx, TableMetadataToUpdate{
   728  		TableConstraints: &TableConstraints{
   729  			PrimaryKey: &PrimaryKey{}, // clean primary keys
   730  		},
   731  	}, "")
   732  	if err != nil {
   733  		t.Fatal(err)
   734  	}
   735  	if md.TableConstraints != nil {
   736  		t.Fatalf("expected table primary keys to be removed, but found %v", md.TableConstraints.PrimaryKey)
   737  	}
   738  
   739  	tableNoPK := dataset.Table(tableIDs.New())
   740  	err = tableNoPK.Create(context.Background(), &TableMetadata{
   741  		Schema:         schema,
   742  		ExpirationTime: testTableExpiration,
   743  	})
   744  	if err != nil {
   745  		t.Fatal(err)
   746  	}
   747  	defer tableNoPK.Delete(ctx)
   748  	md, err = tableNoPK.Metadata(ctx)
   749  	if err != nil {
   750  		t.Fatal(err)
   751  	}
   752  	if md.TableConstraints != nil {
   753  		t.Fatalf("expected table to not have a PK, but found %v", md.TableConstraints.PrimaryKey.Columns)
   754  	}
   755  
   756  	md, err = tableNoPK.Update(ctx, TableMetadataToUpdate{
   757  		TableConstraints: &TableConstraints{
   758  			PrimaryKey: &PrimaryKey{
   759  				Columns: []string{"name"},
   760  			},
   761  		},
   762  	}, "")
   763  	if err != nil {
   764  		t.Fatal(err)
   765  	}
   766  	if md.TableConstraints.PrimaryKey == nil || md.TableConstraints.PrimaryKey.Columns[0] != "name" {
   767  		t.Fatalf("expected table primary key to contain column `name`, but found %v", md.TableConstraints.PrimaryKey)
   768  	}
   769  }
   770  
   771  func TestIntegration_TableConstraintsFK(t *testing.T) {
   772  	// Test Foreign keys for Table.Create and Table.Update
   773  	if client == nil {
   774  		t.Skip("Integration tests skipped")
   775  	}
   776  	ctx := context.Background()
   777  	tableA := dataset.Table(tableIDs.New())
   778  	schemaA := []*FieldSchema{
   779  		{Name: "id", Type: IntegerFieldType},
   780  		{Name: "name", Type: StringFieldType},
   781  	}
   782  	err := tableA.Create(context.Background(), &TableMetadata{
   783  		Schema: schemaA,
   784  		TableConstraints: &TableConstraints{
   785  			PrimaryKey: &PrimaryKey{
   786  				Columns: []string{"id"},
   787  			},
   788  		},
   789  		ExpirationTime: testTableExpiration,
   790  	})
   791  	if err != nil {
   792  		t.Fatal(err)
   793  	}
   794  	defer tableA.Delete(ctx)
   795  
   796  	tableB := dataset.Table(tableIDs.New())
   797  	schemaB := []*FieldSchema{
   798  		{Name: "id", Type: IntegerFieldType},
   799  		{Name: "name", Type: StringFieldType},
   800  		{Name: "parent", Type: IntegerFieldType},
   801  	}
   802  	err = tableB.Create(context.Background(), &TableMetadata{
   803  		Schema: schemaB,
   804  		TableConstraints: &TableConstraints{
   805  			PrimaryKey: &PrimaryKey{
   806  				Columns: []string{"id"},
   807  			},
   808  			ForeignKeys: []*ForeignKey{
   809  				{
   810  					Name:            "table_a_fk",
   811  					ReferencedTable: tableA,
   812  					ColumnReferences: []*ColumnReference{
   813  						{
   814  							ReferencingColumn: "parent",
   815  							ReferencedColumn:  "id",
   816  						},
   817  					},
   818  				},
   819  			},
   820  		},
   821  		ExpirationTime: testTableExpiration,
   822  	})
   823  	if err != nil {
   824  		t.Fatal(err)
   825  	}
   826  	defer tableB.Delete(ctx)
   827  	md, err := tableB.Metadata(ctx)
   828  	if err != nil {
   829  		t.Fatal(err)
   830  	}
   831  	if len(md.TableConstraints.ForeignKeys) >= 0 && md.TableConstraints.ForeignKeys[0].Name != "table_a_fk" {
   832  		t.Fatalf("expected table to contains fk `table_a_fk`, but found %v", md.TableConstraints.ForeignKeys)
   833  	}
   834  
   835  	md, err = tableB.Update(ctx, TableMetadataToUpdate{
   836  		TableConstraints: &TableConstraints{
   837  			ForeignKeys: []*ForeignKey{}, // clean foreign keys
   838  		},
   839  	}, "")
   840  	if err != nil {
   841  		t.Fatal(err)
   842  	}
   843  	if len(md.TableConstraints.ForeignKeys) > 0 {
   844  		t.Fatalf("expected table foreign keys to be removed, but found %v", md.TableConstraints.ForeignKeys)
   845  	}
   846  
   847  	tableNoFK := dataset.Table(tableIDs.New())
   848  	err = tableNoFK.Create(context.Background(), &TableMetadata{
   849  		Schema: schemaB,
   850  		TableConstraints: &TableConstraints{
   851  			PrimaryKey: &PrimaryKey{
   852  				Columns: []string{"id"},
   853  			},
   854  		},
   855  		ExpirationTime: testTableExpiration,
   856  	})
   857  	if err != nil {
   858  		t.Fatal(err)
   859  	}
   860  	defer tableNoFK.Delete(ctx)
   861  	md, err = tableNoFK.Update(ctx, TableMetadataToUpdate{
   862  		TableConstraints: &TableConstraints{
   863  			ForeignKeys: []*ForeignKey{
   864  				{
   865  					Name:            "table_a_fk",
   866  					ReferencedTable: tableA,
   867  					ColumnReferences: []*ColumnReference{
   868  						{
   869  							ReferencedColumn:  "id",
   870  							ReferencingColumn: "parent",
   871  						},
   872  					},
   873  				},
   874  			},
   875  		},
   876  	}, "")
   877  	if err != nil {
   878  		t.Fatal(err)
   879  	}
   880  	if len(md.TableConstraints.ForeignKeys) == 0 || md.TableConstraints.ForeignKeys[0].Name != "table_a_fk" {
   881  		t.Fatalf("expected table to contains fk `table_a_fk`, but found %v", md.TableConstraints.ForeignKeys)
   882  	}
   883  }
   884
View as plain text