samples.cc

// Copyright 2019 Google LLC
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     https://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

//! [START spanner_quickstart]
#include "google/cloud/spanner/client.h"
//! [END spanner_quickstart]
#include "google/cloud/spanner/admin/database_admin_client.h"
#include "google/cloud/spanner/admin/database_admin_options.h"
#include "google/cloud/spanner/admin/instance_admin_client.h"
#include "google/cloud/spanner/admin/instance_admin_options.h"
#include "google/cloud/spanner/backoff_policy.h"
#include "google/cloud/spanner/backup.h"
#include "google/cloud/spanner/create_instance_request_builder.h"
#include "google/cloud/spanner/row.h"
#include "google/cloud/spanner/testing/debug_log.h"  // TODO(#4758): remove
#include "google/cloud/spanner/testing/instance_location.h"
#include "google/cloud/spanner/testing/pick_instance_config.h"
#include "google/cloud/spanner/testing/pick_random_instance.h"
#include "google/cloud/spanner/testing/random_backup_name.h"
#include "google/cloud/spanner/testing/random_database_name.h"
#include "google/cloud/spanner/testing/random_instance_name.h"
#include "google/cloud/spanner/testing/singer.pb.h"
#include "google/cloud/spanner/update_instance_request_builder.h"
#include "google/cloud/internal/getenv.h"
#include "google/cloud/internal/random.h"
#include "google/cloud/kms_key_name.h"
#include "google/cloud/log.h"
#include "google/cloud/project.h"
#include "absl/strings/match.h"
#include "absl/strings/string_view.h"
#include "absl/time/clock.h"
#include "absl/time/time.h"
#include "absl/types/optional.h"
#include <chrono>
#include <iomanip>
#include <iterator>
#include <string>
#include <tuple>
#include <utility>
#include <vector>

namespace {

//! [START spanner_get_instance_config] [get-instance-config]
void GetInstanceConfig(google::cloud::spanner_admin::InstanceAdminClient client,
                       std::string const& project_id,
                       std::string const& config_id) {
  auto project = google::cloud::Project(project_id);
  auto config = client.GetInstanceConfig(project.FullName() +
                                         "/instanceConfigs/" + config_id);
  if (!config) throw std::move(config).status();
  std::cout << "The instanceConfig " << config->name()
            << " exists and its metadata is:\n"
            << config->DebugString();
}
//! [END spanner_get_instance_config] [get-instance-config]

void GetInstanceConfigCommand(std::vector<std::string> argv) {
  if (argv.size() != 2) {
    throw std::runtime_error("get-instance-config <project-id> <config-id>");
  }
  google::cloud::spanner_admin::InstanceAdminClient client(
      google::cloud::spanner_admin::MakeInstanceAdminConnection());
  GetInstanceConfig(std::move(client), argv[0], argv[1]);
}

//! [START spanner_list_instance_configs] [list-instance-configs]
void ListInstanceConfigs(
    google::cloud::spanner_admin::InstanceAdminClient client,
    std::string const& project_id) {
  int count = 0;
  auto project = google::cloud::Project(project_id);
  for (auto& config : client.ListInstanceConfigs(project.FullName())) {
    if (!config) throw std::move(config).status();
    ++count;
    std::cout << "Instance config [" << count << "]:\n"
              << config->DebugString();
  }
  if (count == 0) {
    std::cout << "No instance configs found in project " << project_id << "\n";
  }
}
//! [END spanner_list_instance_configs] [list-instance-configs]

void ListInstanceConfigsCommand(std::vector<std::string> argv) {
  if (argv.size() != 1) {
    throw std::runtime_error("list-instance-configs <project-id>");
  }
  google::cloud::spanner_admin::InstanceAdminClient client(
      google::cloud::spanner_admin::MakeInstanceAdminConnection());
  ListInstanceConfigs(std::move(client), argv[0]);
}

// [START spanner_create_instance_config]
void CreateInstanceConfig(
    google::cloud::spanner_admin::InstanceAdminClient client,
    std::string const& project_id, std::string const& user_config_id,
    std::string const& base_config_id) {
  auto project = google::cloud::Project(project_id);
  auto base_config = client.GetInstanceConfig(
      project.FullName() + "/instanceConfigs/" + base_config_id);
  if (!base_config) throw std::move(base_config).status();
  if (base_config->optional_replicas().empty()) {
    throw std::runtime_error("No optional replicas in base config");
  }
  google::spanner::admin::instance::v1::CreateInstanceConfigRequest request;
  request.set_parent(project.FullName());
  request.set_instance_config_id(user_config_id);
  auto* request_config = request.mutable_instance_config();
  request_config->set_name(project.FullName() + "/instanceConfigs/" +
                           user_config_id);
  request_config->set_display_name("My instance config");
  // The user-managed instance config must contain all the replicas
  // of the base config plus at least one of the optional replicas.
  *request_config->mutable_replicas() = base_config->replicas();
  for (auto const& replica : base_config->optional_replicas()) {
    *request_config->add_replicas() = replica;
  }
  request_config->set_base_config(base_config->name());
  *request_config->mutable_leader_options() = base_config->leader_options();
  request.set_validate_only(false);
  auto user_config = client.CreateInstanceConfig(request).get();
  if (!user_config) throw std::move(user_config).status();
  std::cout << "Created instance config [" << user_config_id << "]:\n"
            << user_config->DebugString();
}
// [END spanner_create_instance_config]

void CreateInstanceConfigCommand(std::vector<std::string> argv) {
  if (argv.size() != 3) {
    throw std::runtime_error(
        "create-instance-config <project-id> <config-id> <base-config-id>");
  }
  google::cloud::spanner_admin::InstanceAdminClient client(
      google::cloud::spanner_admin::MakeInstanceAdminConnection());
  CreateInstanceConfig(std::move(client), argv[0], argv[1], argv[2]);
}

// [START spanner_update_instance_config]
void UpdateInstanceConfig(
    google::cloud::spanner_admin::InstanceAdminClient client,
    std::string const& project_id, std::string const& config_id) {
  auto project = google::cloud::Project(project_id);
  auto config = client.GetInstanceConfig(project.FullName() +
                                         "/instanceConfigs/" + config_id);
  if (!config) throw std::move(config).status();
  google::spanner::admin::instance::v1::UpdateInstanceConfigRequest request;
  auto* request_config = request.mutable_instance_config();
  request_config->set_name(config->name());
  request_config->mutable_labels()->insert({"key", "value"});
  request.mutable_update_mask()->add_paths("labels");
  request_config->set_etag(config->etag());
  request.set_validate_only(false);
  auto updated_config = client.UpdateInstanceConfig(request).get();
  if (!updated_config) throw std::move(updated_config).status();
  std::cout << "Updated instance config [" << config_id << "]:\n"
            << updated_config->DebugString();
}
// [END spanner_update_instance_config]

void UpdateInstanceConfigCommand(std::vector<std::string> argv) {
  if (argv.size() != 2) {
    throw std::runtime_error("update-instance-config <project-id> <config-id>");
  }
  google::cloud::spanner_admin::InstanceAdminClient client(
      google::cloud::spanner_admin::MakeInstanceAdminConnection());
  UpdateInstanceConfig(std::move(client), argv[0], argv[1]);
}

// [START spanner_delete_instance_config]
void DeleteInstanceConfig(
    google::cloud::spanner_admin::InstanceAdminClient client,
    std::string const& project_id, std::string const& config_id) {
  auto project = google::cloud::Project(project_id);
  auto config_name = project.FullName() + "/instanceConfigs/" + config_id;
  auto status = client.DeleteInstanceConfig(config_name);
  if (!status.ok()) throw std::move(status);
  std::cout << "Instance config " << config_name << " successfully deleted\n";
}
// [END spanner_delete_instance_config]

void DeleteInstanceConfigCommand(std::vector<std::string> argv) {
  if (argv.size() != 2) {
    throw std::runtime_error("delete-instance-config <project-id> <config-id>");
  }
  google::cloud::spanner_admin::InstanceAdminClient client(
      google::cloud::spanner_admin::MakeInstanceAdminConnection());
  DeleteInstanceConfig(std::move(client), argv[0], argv[1]);
}

// [START spanner_list_instance_config_operations]
void ListInstanceConfigOperations(
    google::cloud::spanner_admin::InstanceAdminClient client,
    std::string const& project_id) {
  auto project = google::cloud::Project(project_id);

  google::spanner::admin::database::v1::ListBackupOperationsRequest request;
  request.set_parent(project.FullName());

  request.set_filter(
      std::string("metadata.@type=type.googleapis.com/") +
      "google.spanner.admin.instance.v1.CreateInstanceConfigMetadata");
  for (auto& operation :
       client.ListInstanceConfigOperations(project.FullName())) {
    if (!operation) throw std::move(operation).status();
    google::spanner::admin::instance::v1::CreateInstanceConfigMetadata metadata;
    operation->metadata().UnpackTo(&metadata);
    std::cout << "CreateInstanceConfig metadata is:\n"
              << metadata.DebugString();
  }

  request.set_filter(
      std::string("metadata.@type=type.googleapis.com/") +
      "google.spanner.admin.instance.v1.UpdateInstanceConfigMetadata");
  for (auto& operation :
       client.ListInstanceConfigOperations(project.FullName())) {
    if (!operation) throw std::move(operation).status();
    google::spanner::admin::instance::v1::UpdateInstanceConfigMetadata metadata;
    operation->metadata().UnpackTo(&metadata);
    std::cout << "UpdateInstanceConfig metadata is:\n"
              << metadata.DebugString();
  }
}
// [END spanner_list_instance_config_operations]

void ListInstanceConfigOperationsCommand(std::vector<std::string> argv) {
  if (argv.size() != 1) {
    throw std::runtime_error("list-instance-config-operations <project-id>");
  }
  google::cloud::spanner_admin::InstanceAdminClient client(
      google::cloud::spanner_admin::MakeInstanceAdminConnection());
  ListInstanceConfigOperations(std::move(client), argv[0]);
}

//! [get-instance]
void GetInstance(google::cloud::spanner_admin::InstanceAdminClient client,
                 std::string const& project_id,
                 std::string const& instance_id) {
  google::cloud::spanner::Instance in(project_id, instance_id);
  auto instance = client.GetInstance(in.FullName());
  if (!instance) throw std::move(instance).status();

  std::cout << "The instance " << instance->name()
            << " exists and its metadata is:\n"
            << instance->DebugString();
}
//! [get-instance]

void GetInstanceCommand(std::vector<std::string> argv) {
  if (argv.size() != 2) {
    throw std::runtime_error("get-instance <project-id> <instance-id>");
  }
  google::cloud::spanner_admin::InstanceAdminClient client(
      google::cloud::spanner_admin::MakeInstanceAdminConnection());
  GetInstance(std::move(client), argv[0], argv[1]);
}

//! [list-instances]
void ListInstances(google::cloud::spanner_admin::InstanceAdminClient client,
                   std::string const& project_id) {
  int count = 0;
  auto project = google::cloud::Project(project_id);
  for (auto& instance : client.ListInstances(project.FullName())) {
    if (!instance) throw std::move(instance).status();
    ++count;
    std::cout << "Instance [" << count << "]:\n" << instance->DebugString();
  }
  if (count == 0) {
    std::cout << "No instances found in project " << project_id << "\n";
  }
}
//! [list-instances]

void ListInstancesCommand(std::vector<std::string> argv) {
  if (argv.size() != 1) {
    throw std::runtime_error("list-instances <project-id>");
  }
  google::cloud::spanner_admin::InstanceAdminClient client(
      google::cloud::spanner_admin::MakeInstanceAdminConnection());
  ListInstances(std::move(client), argv[0]);
}

//! [START spanner_create_instance] [create-instance]
void CreateInstance(google::cloud::spanner_admin::InstanceAdminClient client,
                    std::string const& project_id,
                    std::string const& instance_id,
                    std::string const& display_name,
                    std::string const& config_id) {
  namespace spanner = ::google::cloud::spanner;
  spanner::Instance in(project_id, instance_id);

  auto project = google::cloud::Project(project_id);
  std::string config_name =
      project.FullName() + "/instanceConfigs/" + config_id;
  auto instance =
      client
          .CreateInstance(spanner::CreateInstanceRequestBuilder(in, config_name)
                              .SetDisplayName(display_name)
                              .SetNodeCount(1)
                              .SetLabels({{"cloud_spanner_samples", "true"}})
                              .Build())
          .get();
  if (!instance) throw std::move(instance).status();
  std::cout << "Created instance [" << in << "]:\n" << instance->DebugString();
}
//! [END spanner_create_instance] [create-instance]

// [START spanner_create_instance_with_processing_units]
void CreateInstanceWithProcessingUnits(
    google::cloud::spanner_admin::InstanceAdminClient client,
    std::string const& project_id, std::string const& instance_id,
    std::string const& display_name, std::string const& config_id) {
  namespace spanner = ::google::cloud::spanner;
  spanner::Instance in(project_id, instance_id);

  auto project = google::cloud::Project(project_id);
  std::string config_name =
      project.FullName() + "/instanceConfigs/" + config_id;
  auto instance =
      client
          .CreateInstance(spanner::CreateInstanceRequestBuilder(in, config_name)
                              .SetDisplayName(display_name)
                              .SetProcessingUnits(500)
                              .SetLabels({{"cloud_spanner_samples", "true"}})
                              .Build())
          .get();
  if (!instance) throw std::move(instance).status();
  std::cout << "Created instance [" << in << "]:\n" << instance->DebugString();

  instance = client.GetInstance(in.FullName());
  if (!instance) throw std::move(instance).status();
  std::cout << "Instance " << in << " has " << instance->processing_units()
            << " processing units.\n";
}
// [END spanner_create_instance_with_processing_units]

// [START spanner_create_instance_with_autoscaling_config]
void CreateInstanceWithAutoscalingConfig(
    google::cloud::spanner_admin::InstanceAdminClient client,
    std::string const& project_id, std::string const& instance_id,
    std::string const& display_name, std::string const& config_id) {
  auto pr = google::cloud::Project(project_id);
  auto in = google::cloud::spanner::Instance(pr, instance_id);
  auto config = pr.FullName() + "/instanceConfigs/" + config_id;

  google::spanner::admin::instance::v1::CreateInstanceRequest request;
  request.set_parent(pr.FullName());
  request.set_instance_id(instance_id);
  request.mutable_instance()->set_name(in.FullName());
  request.mutable_instance()->set_config(config);
  request.mutable_instance()->set_display_name(display_name);

  auto* autoscaling = request.mutable_instance()->mutable_autoscaling_config();
  auto* limits = autoscaling->mutable_autoscaling_limits();
  limits->set_min_nodes(1);
  limits->set_max_nodes(2);
  auto* targets = autoscaling->mutable_autoscaling_targets();
  targets->set_high_priority_cpu_utilization_percent(65);
  targets->set_storage_utilization_percent(95);

  auto instance = client.CreateInstance(request).get();
  if (!instance) throw std::move(instance).status();
  std::cout << "Created instance " << in << " with autoscaling config\n";

  instance = client.GetInstance(in.FullName());
  if (!instance) throw std::move(instance).status();
  std::cout << "Instance " << in << ":\n"
            << instance->autoscaling_config().DebugString();
}
// [END spanner_create_instance_with_autoscaling_config]

void CreateInstanceCommand(std::vector<std::string> argv) {
  bool auto_scaler = !argv.empty() && argv.front() == "--auto-scaler";
  bool low_cost = !argv.empty() && argv.front() == "--low-cost";
  if (auto_scaler || low_cost) argv.erase(argv.begin());
  if (argv.size() != 3 && argv.size() != 4) {
    throw std::runtime_error(
        "create-instance [--auto-scaler|--low-cost] <project-id>"
        " <instance-id> <display-name> [config-id]");
  }
  google::cloud::spanner_admin::InstanceAdminClient client(
      google::cloud::spanner_admin::MakeInstanceAdminConnection());
  std::string config_id = argv.size() == 4 ? argv[3] : "regional-us-central1";
  if (auto_scaler) {
    CreateInstanceWithAutoscalingConfig(std::move(client), argv[0], argv[1],
                                        argv[2], config_id);
  } else if (low_cost) {
    CreateInstanceWithProcessingUnits(std::move(client), argv[0], argv[1],
                                      argv[2], config_id);
  } else {
    CreateInstance(std::move(client), argv[0], argv[1], argv[2], config_id);
  }
}

//! [update-instance]
void UpdateInstance(google::cloud::spanner_admin::InstanceAdminClient client,
                    std::string const& project_id,
                    std::string const& instance_id,
                    std::string const& new_display_name) {
  google::cloud::spanner::Instance in(project_id, instance_id);

  auto f = client.UpdateInstance(
      google::cloud::spanner::UpdateInstanceRequestBuilder(in)
          .SetDisplayName(new_display_name)
          .Build());
  auto instance = f.get();
  if (!instance) throw std::move(instance).status();
  std::cout << "Updated instance [" << in << "]\n";
}
//! [update-instance]

void UpdateInstanceCommand(std::vector<std::string> argv) {
  if (argv.size() != 3) {
    throw std::runtime_error(
        "update-instance <project-id> <instance-id> <new-display-name>");
  }
  google::cloud::spanner_admin::InstanceAdminClient client(
      google::cloud::spanner_admin::MakeInstanceAdminConnection());
  UpdateInstance(std::move(client), argv[0], argv[1], argv[2]);
}

//! [delete-instance]
void DeleteInstance(google::cloud::spanner_admin::InstanceAdminClient client,
                    std::string const& project_id,
                    std::string const& instance_id) {
  google::cloud::spanner::Instance in(project_id, instance_id);

  auto status = client.DeleteInstance(in.FullName());
  if (!status.ok()) throw std::move(status);
  std::cout << "Deleted instance [" << in << "]\n";
}
//! [delete-instance]

void DeleteInstanceCommand(std::vector<std::string> argv) {
  if (argv.size() != 2) {
    throw std::runtime_error("delete-instance <project-id> <instance-id>");
  }
  google::cloud::spanner_admin::InstanceAdminClient client(
      google::cloud::spanner_admin::MakeInstanceAdminConnection());
  DeleteInstance(std::move(client), argv[0], argv[1]);
}

//! [instance-get-iam-policy]
void InstanceGetIamPolicy(
    google::cloud::spanner_admin::InstanceAdminClient client,
    std::string const& project_id, std::string const& instance_id) {
  google::cloud::spanner::Instance in(project_id, instance_id);
  auto actual = client.GetIamPolicy(in.FullName());
  if (!actual) throw std::move(actual).status();

  std::cout << "The IAM policy for instance " << instance_id << " is:\n"
            << actual->DebugString();
}
//! [instance-get-iam-policy]

void InstanceGetIamPolicyCommand(std::vector<std::string> argv) {
  if (argv.size() != 2) {
    throw std::runtime_error(
        "instance-get-iam-policy <project-id> <instance-id>");
  }
  google::cloud::spanner_admin::InstanceAdminClient client(
      google::cloud::spanner_admin::MakeInstanceAdminConnection());
  InstanceGetIamPolicy(std::move(client), argv[0], argv[1]);
}

//! [add-database-reader]
void AddDatabaseReader(google::cloud::spanner_admin::InstanceAdminClient client,
                       std::string const& project_id,
                       std::string const& instance_id,
                       std::string const& new_reader) {
  google::cloud::spanner::Instance in(project_id, instance_id);
  auto result = client.SetIamPolicy(
      in.FullName(),
      [&new_reader](google::iam::v1::Policy current)
          -> absl::optional<google::iam::v1::Policy> {
        // Find (or create) the binding for "roles/spanner.databaseReader".
        auto& binding = [&current]() -> google::iam::v1::Binding& {
          auto role_pos = std::find_if(
              current.mutable_bindings()->begin(),
              current.mutable_bindings()->end(),
              [](google::iam::v1::Binding const& b) {
                return b.role() == "roles/spanner.databaseReader" &&
                       !b.has_condition();
              });
          if (role_pos != current.mutable_bindings()->end()) {
            return *role_pos;
          }
          auto& binding = *current.add_bindings();
          binding.set_role("roles/spanner.databaseReader");
          return binding;
        }();

        auto member_pos = std::find(binding.members().begin(),
                                    binding.members().end(), new_reader);
        if (member_pos != binding.members().end()) {
          std::cout << "The entity " << new_reader
                    << " is already a database reader:\n"
                    << current.DebugString();
          return {};
        }
        binding.add_members(new_reader);
        return current;
      });

  if (!result) throw std::move(result).status();
  std::cout << "Successfully added " << new_reader
            << " to the database reader role:\n"
            << result->DebugString();
}
//! [add-database-reader]

void AddDatabaseReaderCommand(std::vector<std::string> argv) {
  if (argv.size() != 3) {
    throw std::runtime_error(
        "add-database-reader <project-id> <instance-id> <new-reader>");
  }
  google::cloud::spanner_admin::InstanceAdminClient client(
      google::cloud::spanner_admin::MakeInstanceAdminConnection());
  AddDatabaseReader(std::move(client), argv[0], argv[1], argv[2]);
}

//! [remove-database-reader]
void RemoveDatabaseReader(
    google::cloud::spanner_admin::InstanceAdminClient client,
    std::string const& project_id, std::string const& instance_id,
    std::string const& reader) {
  google::cloud::spanner::Instance in(project_id, instance_id);

  auto result = client.SetIamPolicy(
      in.FullName(),
      [&reader](google::iam::v1::Policy current)
          -> absl::optional<google::iam::v1::Policy> {
        // Find the binding for "roles/spanner.databaseReader".
        auto role_pos =
            std::find_if(current.mutable_bindings()->begin(),
                         current.mutable_bindings()->end(),
                         [](google::iam::v1::Binding const& b) {
                           return b.role() == "roles/spanner.databaseReader" &&
                                  !b.has_condition();
                         });
        if (role_pos == current.mutable_bindings()->end()) {
          std::cout << "Nothing to do as the roles/spanner.databaseReader role "
                       "is empty\n";
          return {};
        }
        role_pos->mutable_members()->erase(
            std::remove(role_pos->mutable_members()->begin(),
                        role_pos->mutable_members()->end(), reader),
            role_pos->mutable_members()->end());

        return current;
      });

  if (!result) throw std::move(result).status();
  std::cout << "Successfully removed " << reader
            << " from the database reader role:\n"
            << result->DebugString();
}
//! [remove-database-reader]

void RemoveDatabaseReaderCommand(std::vector<std::string> argv) {
  if (argv.size() != 3) {
    throw std::runtime_error(
        "remove-database-reader <project-id> <instance-id> <existing-reader>");
  }
  google::cloud::spanner_admin::InstanceAdminClient client(
      google::cloud::spanner_admin::MakeInstanceAdminConnection());
  RemoveDatabaseReader(std::move(client), argv[0], argv[1], argv[2]);
}

//! [instance-test-iam-permissions]
void InstanceTestIamPermissions(
    google::cloud::spanner_admin::InstanceAdminClient client,
    std::string const& project_id, std::string const& instance_id) {
  google::cloud::spanner::Instance in(project_id, instance_id);
  auto actual =
      client.TestIamPermissions(in.FullName(), {"spanner.databases.list"});
  if (!actual) throw std::move(actual).status();

  char const* msg = actual->permissions().empty() ? "does not" : "does";

  std::cout
      << "The caller " << msg
      << " have permission to list databases on the Cloud Spanner instance "
      << in.instance_id() << "\n";
}
//! [instance-test-iam-permissions]

void InstanceTestIamPermissionsCommand(std::vector<std::string> argv) {
  if (argv.size() != 2) {
    throw std::runtime_error(
        "instance-test-iam-permissions <project-id> <instance-id>");
  }
  google::cloud::spanner_admin::InstanceAdminClient client(
      google::cloud::spanner_admin::MakeInstanceAdminConnection());
  InstanceTestIamPermissions(std::move(client), argv[0], argv[1]);
}

google::cloud::spanner::Timestamp DatabaseNow(
    google::cloud::spanner::Client client) {
  auto rows = client.ExecuteQuery(
      google::cloud::spanner::SqlStatement("SELECT CURRENT_TIMESTAMP()"));
  using RowType = std::tuple<google::cloud::spanner::Timestamp>;
  auto row = google::cloud::spanner::GetSingularRow(
      google::cloud::spanner::StreamOf<RowType>(rows));
  if (!row) throw std::move(row).status();
  return std::get<0>(*row);
}

google::cloud::spanner::Timestamp TimestampAdd(
    google::cloud::spanner::Timestamp ts, absl::Duration d) {
  auto const t = ts.get<absl::Time>().value();
  return google::cloud::spanner::MakeTimestamp(t + d).value();
}

google::cloud::spanner::Client MakeSampleClient(
    std::string const& project_id, std::string const& instance_id,
    std::string const& database_id) {
  return google::cloud::spanner::Client(google::cloud::spanner::MakeConnection(
      google::cloud::spanner::Database(project_id, instance_id, database_id)));
}

//! [create-database] [START spanner_create_database]
void CreateDatabase(google::cloud::spanner_admin::DatabaseAdminClient client,
                    std::string const& project_id,
                    std::string const& instance_id,
                    std::string const& database_id) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  google::spanner::admin::database::v1::CreateDatabaseRequest request;
  request.set_parent(database.instance().FullName());
  request.set_create_statement("CREATE DATABASE `" + database.database_id() +
                               "`");
  request.add_extra_statements(R"""(
      CREATE TABLE Singers (
          SingerId   INT64 NOT NULL,
          FirstName  STRING(1024),
          LastName   STRING(1024),
          SingerInfo BYTES(MAX),
          FullName   STRING(2049)
              AS (ARRAY_TO_STRING([FirstName, LastName], " ")) STORED
      ) PRIMARY KEY (SingerId))""");
  request.add_extra_statements(R"""(
      CREATE TABLE Albums (
          SingerId     INT64 NOT NULL,
          AlbumId      INT64 NOT NULL,
          AlbumTitle   STRING(MAX)
      ) PRIMARY KEY (SingerId, AlbumId),
          INTERLEAVE IN PARENT Singers ON DELETE CASCADE)""");
  auto db = client.CreateDatabase(request).get();
  if (!db) throw std::move(db).status();
  std::cout << "Database " << db->name() << " created.\n";
}
//! [create-database] [END spanner_create_database]

// [START spanner_create_database_with_version_retention_period]
void CreateDatabaseWithVersionRetentionPeriod(
    google::cloud::spanner_admin::DatabaseAdminClient client,
    std::string const& project_id, std::string const& instance_id,
    std::string const& database_id) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  google::spanner::admin::database::v1::CreateDatabaseRequest request;
  request.set_parent(database.instance().FullName());
  request.set_create_statement("CREATE DATABASE `" + database.database_id() +
                               "`");
  request.add_extra_statements("ALTER DATABASE `" + database.database_id() +
                               "` " +
                               "SET OPTIONS (version_retention_period='2h')");
  request.add_extra_statements(R"""(
      CREATE TABLE Singers (
          SingerId   INT64 NOT NULL,
          FirstName  STRING(1024),
          LastName   STRING(1024),
          SingerInfo BYTES(MAX),
          FullName   STRING(2049)
              AS (ARRAY_TO_STRING([FirstName, LastName], " ")) STORED
      ) PRIMARY KEY (SingerId))""");
  request.add_extra_statements(R"""(
      CREATE TABLE Albums (
          SingerId     INT64 NOT NULL,
          AlbumId      INT64 NOT NULL,
          AlbumTitle   STRING(MAX)
      ) PRIMARY KEY (SingerId, AlbumId),
          INTERLEAVE IN PARENT Singers ON DELETE CASCADE)""");
  auto db = client.CreateDatabase(request).get();
  if (!db) throw std::move(db).status();
  std::cout << "Database " << db->name() << " created.\n";

  auto ddl = client.GetDatabaseDdl(db->name());
  if (!ddl) throw std::move(ddl).status();
  std::cout << "Database DDL is:\n" << ddl->DebugString();
}
// [END spanner_create_database_with_version_retention_period]

// [START spanner_create_database_with_default_leader]
void CreateDatabaseWithDefaultLeader(
    google::cloud::spanner_admin::DatabaseAdminClient client,
    std::string const& project_id, std::string const& instance_id,
    std::string const& database_id, std::string const& default_leader) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  google::spanner::admin::database::v1::CreateDatabaseRequest request;
  request.set_parent(database.instance().FullName());
  request.set_create_statement("CREATE DATABASE `" + database.database_id() +
                               "`");
  request.add_extra_statements("ALTER DATABASE `" + database_id + "` " +
                               "SET OPTIONS (default_leader='" +
                               default_leader + "')");
  auto db = client.CreateDatabase(request).get();
  if (!db) throw std::move(db).status();
  std::cout << "Database " << db->name() << " created.\n" << db->DebugString();
}
// [END spanner_create_database_with_default_leader]

// [START spanner_update_database_with_default_leader]
void UpdateDatabaseWithDefaultLeader(
    google::cloud::spanner_admin::DatabaseAdminClient client,
    std::string const& project_id, std::string const& instance_id,
    std::string const& database_id, std::string const& default_leader) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  std::vector<std::string> statements;
  statements.push_back("ALTER DATABASE `" + database_id + "` " +
                       "SET OPTIONS (default_leader='" + default_leader + "')");
  auto metadata =
      client.UpdateDatabaseDdl(database.FullName(), std::move(statements))
          .get();
  google::cloud::spanner_testing::LogUpdateDatabaseDdl(  //! TODO(#4758)
      client, database, metadata.status());              //! TODO(#4758)
  if (!metadata) throw std::move(metadata).status();
  std::cout << "`default_leader` altered, new DDL metadata:\n"
            << metadata->DebugString();
}
// [END spanner_update_database_with_default_leader]

// [START spanner_create_table_with_datatypes]
void CreateTableWithDatatypes(
    google::cloud::spanner_admin::DatabaseAdminClient client,
    std::string const& project_id, std::string const& instance_id,
    std::string const& database_id) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  auto metadata = client
                      .UpdateDatabaseDdl(database.FullName(), {R"""(
                        CREATE TABLE Venues (
                            VenueId         INT64 NOT NULL,
                            VenueName       STRING(100),
                            VenueInfo       BYTES(MAX),
                            Capacity        INT64,
                            AvailableDates  ARRAY<DATE>,
                            LastContactDate DATE,
                            OutdoorVenue    BOOL,
                            PopularityScore FLOAT64,
                            LastUpdateTime  TIMESTAMP NOT NULL OPTIONS
                                (allow_commit_timestamp=true)
                        ) PRIMARY KEY (VenueId))"""})
                      .get();
  google::cloud::spanner_testing::LogUpdateDatabaseDdl(  //! TODO(#4758)
      client, database, metadata.status());              //! TODO(#4758)
  if (!metadata) throw std::move(metadata).status();
  std::cout << "`Venues` table created, new DDL:\n" << metadata->DebugString();
}
// [END spanner_create_table_with_datatypes]

// [START spanner_create_table_with_timestamp_column]
void CreateTableWithTimestamp(
    google::cloud::spanner_admin::DatabaseAdminClient client,
    std::string const& project_id, std::string const& instance_id,
    std::string const& database_id) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  auto metadata = client
                      .UpdateDatabaseDdl(database.FullName(), {R"""(
                        CREATE TABLE Performances (
                            SingerId        INT64 NOT NULL,
                            VenueId         INT64 NOT NULL,
                            EventDate       Date,
                            Revenue         INT64,
                            LastUpdateTime  TIMESTAMP NOT NULL OPTIONS
                                (allow_commit_timestamp=true)
                        ) PRIMARY KEY (SingerId, VenueId, EventDate),
                            INTERLEAVE IN PARENT Singers ON DELETE CASCADE)"""})
                      .get();
  google::cloud::spanner_testing::LogUpdateDatabaseDdl(  //! TODO(#4758)
      client, database, metadata.status());              //! TODO(#4758)
  if (!metadata) throw std::move(metadata).status();
  std::cout << "`Performances` table created, new DDL:\n"
            << metadata->DebugString();
}
// [END spanner_create_table_with_timestamp_column]

// [START spanner_create_index]
void AddIndex(google::cloud::spanner_admin::DatabaseAdminClient client,
              std::string const& project_id, std::string const& instance_id,
              std::string const& database_id) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  auto metadata =
      client
          .UpdateDatabaseDdl(
              database.FullName(),
              {"CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)"})
          .get();
  google::cloud::spanner_testing::LogUpdateDatabaseDdl(  //! TODO(#4758)
      client, database, metadata.status());              //! TODO(#4758)
  if (!metadata) throw std::move(metadata).status();
  std::cout << "`AlbumsByAlbumTitle` Index successfully added, new DDL:\n"
            << metadata->DebugString();
}
// [END spanner_create_index]

//! [get-database]
void GetDatabase(google::cloud::spanner_admin::DatabaseAdminClient client,
                 std::string const& project_id, std::string const& instance_id,
                 std::string const& database_id) {
  namespace spanner = ::google::cloud::spanner;
  auto database = client.GetDatabase(
      spanner::Database(project_id, instance_id, database_id).FullName());
  if (!database) throw std::move(database).status();
  std::cout << "Database metadata is:\n" << database->DebugString();
}
//! [get-database]

//! [START spanner_get_database_ddl] [get-database-ddl]
void GetDatabaseDdl(google::cloud::spanner_admin::DatabaseAdminClient client,
                    std::string const& project_id,
                    std::string const& instance_id,
                    std::string const& database_id) {
  namespace spanner = ::google::cloud::spanner;
  auto database = client.GetDatabaseDdl(
      spanner::Database(project_id, instance_id, database_id).FullName());
  if (!database) throw std::move(database).status();
  std::cout << "Database metadata is:\n" << database->DebugString();
}
//! [END spanner_get_database_ddl] [get-database-ddl]

//! [START spanner_add_and_drop_database_role]
void AddAndDropDatabaseRole(
    google::cloud::spanner_admin::DatabaseAdminClient client,
    std::string const& project_id, std::string const& instance_id,
    std::string const& database_id, std::string const& role_parent,
    std::string const& role_child) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  std::vector<std::string> grant_statements = {
      "CREATE ROLE " + role_parent,
      "GRANT SELECT ON TABLE Singers TO ROLE " + role_parent,
      "CREATE ROLE " + role_child,
      "GRANT ROLE " + role_parent + " TO ROLE " + role_child,
  };
  auto metadata =
      client.UpdateDatabaseDdl(database.FullName(), grant_statements).get();
  google::cloud::spanner_testing::LogUpdateDatabaseDdl(  //! TODO(#4758)
      client, database, metadata.status());              //! TODO(#4758)
  if (!metadata) throw std::move(metadata).status();
  std::cout << "Created roles " << role_parent << " and " << role_child
            << " and granted privileges\n";

  std::vector<std::string> revoke_statements = {
      "REVOKE ROLE " + role_parent + " FROM ROLE " + role_child,
      "DROP ROLE " + role_child,
  };
  metadata =
      client.UpdateDatabaseDdl(database.FullName(), revoke_statements).get();
  google::cloud::spanner_testing::LogUpdateDatabaseDdl(  //! TODO(#4758)
      client, database, metadata.status());              //! TODO(#4758)
  if (!metadata) throw std::move(metadata).status();
  std::cout << "Revoked privileges and dropped role " << role_child << "\n";
}
//! [END spanner_add_and_drop_database_role]

//! [START spanner_read_data_with_database_role]
void ReadDataWithDatabaseRole(std::string const& project_id,
                              std::string const& instance_id,
                              std::string const& database_id,
                              std::string const& role) {
  namespace spanner = ::google::cloud::spanner;
  auto client = spanner::Client(spanner::MakeConnection(
      spanner::Database(project_id, instance_id, database_id),
      google::cloud::Options{}.set<spanner::SessionCreatorRoleOption>(role)));
  spanner::SqlStatement select_star("SELECT * FROM Singers");
  auto rows = client.ExecuteQuery(std::move(select_star));
  using RowType =
      std::tuple<std::int64_t, std::string, std::string, spanner::Bytes>;
  for (auto& row : spanner::StreamOf<RowType>(rows)) {
    if (!row) throw std::move(row).status();
    std::cout << "SingerId: " << std::get<0>(*row) << ", "
              << "FirstName: " << std::get<1>(*row) << ", "
              << "LastName: " << std::get<2>(*row) << "\n";
  }
}
//! [END spanner_read_data_with_database_role]

//! [START spanner_list_database_roles]
void ListDatabaseRoles(google::cloud::spanner_admin::DatabaseAdminClient client,
                       std::string const& project_id,
                       std::string const& instance_id,
                       std::string const& database_id) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  std::cout << "Database Roles are:\n";
  for (auto& role : client.ListDatabaseRoles(database.FullName())) {
    if (!role) throw std::move(role).status();
    std::cout << role->name() << "\n";
  }
}
//! [END spanner_list_database_roles]

//! [START spanner_enable_fine_grained_access]
void EnableFineGrainedAccess(
    google::cloud::spanner_admin::DatabaseAdminClient client,
    std::string const& project_id, std::string const& instance_id,
    std::string const& database_id, std::string const& iam_member,
    std::string const& database_role, std::string const& title) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);

  google::iam::v1::GetIamPolicyRequest request;
  request.set_resource(database.FullName());
  request.mutable_options()->set_requested_policy_version(3);
  auto policy = client.GetIamPolicy(request);
  if (!policy) throw std::move(policy).status();
  if (policy->version() < 3) policy->set_version(3);

  auto& binding = *policy->add_bindings();
  binding.set_role("roles/spanner.fineGrainedAccessUser");
  binding.add_members(iam_member);
  auto& condition = *binding.mutable_condition();
  condition.set_expression("resource.name.endsWith(\"/databaseRoles/" +
                           database_role + "\")");
  condition.set_title(title);

  auto new_policy =
      client.SetIamPolicy(database.FullName(), *std::move(policy));
  if (!new_policy) throw std::move(new_policy).status();
  std::cout << "Enabled fine-grained access in IAM. New policy has version "
            << new_policy->version() << "\n";
}
//! [END spanner_enable_fine_grained_access]

//! [add-column] [START spanner_add_column]
void AddColumn(google::cloud::spanner_admin::DatabaseAdminClient client,
               std::string const& project_id, std::string const& instance_id,
               std::string const& database_id) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  auto metadata =
      client
          .UpdateDatabaseDdl(
              database.FullName(),
              {"ALTER TABLE Albums ADD COLUMN MarketingBudget INT64"})
          .get();
  google::cloud::spanner_testing::LogUpdateDatabaseDdl(  //! TODO(#4758)
      client, database, metadata.status());              //! TODO(#4758)
  if (!metadata) throw std::move(metadata).status();
  std::cout << "Added MarketingBudget column\n";
}
//! [add-column] [END spanner_add_column]

// [START spanner_add_timestamp_column]
void AddTimestampColumn(
    google::cloud::spanner_admin::DatabaseAdminClient client,
    std::string const& project_id, std::string const& instance_id,
    std::string const& database_id) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  auto metadata =
      client
          .UpdateDatabaseDdl(
              database.FullName(),
              {"ALTER TABLE Albums ADD COLUMN LastUpdateTime TIMESTAMP "
               "OPTIONS (allow_commit_timestamp=true)"})
          .get();
  google::cloud::spanner_testing::LogUpdateDatabaseDdl(  //! TODO(#4758)
      client, database, metadata.status());              //! TODO(#4758)
  if (!metadata) throw std::move(metadata).status();
  std::cout << "Added LastUpdateTime column\n";
}
// [END spanner_add_timestamp_column]

//! [drop-column]
void DropColumn(google::cloud::spanner_admin::DatabaseAdminClient client,
                std::string const& project_id, std::string const& instance_id,
                std::string const& database_id) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  auto metadata =
      client