Cloud Spanner Client - Class Database (1.56.0)

Reference documentation and code samples for the Cloud Spanner Client class Database.

Represents a Cloud Spanner Database.

Example:

use Google\Cloud\Spanner\SpannerClient;

$spanner = new SpannerClient();

$database = $spanner->connect('my-instance', 'my-database');
// Databases can also be connected to via an Instance.
use Google\Cloud\Spanner\SpannerClient;

$spanner = new SpannerClient();

$instance = $spanner->instance('my-instance');
$database = $instance->database('my-database');

Methods

__construct

Create an object representing a Database.

Parameters
NameDescription
connection Google\Cloud\Spanner\Connection\ConnectionInterface

The connection to the Cloud Spanner Admin API.

instance Google\Cloud\Spanner\Instance

The instance in which the database exists.

lroConnection Google\Cloud\Core\LongRunning\LongRunningConnectionInterface

An implementation mapping to methods which handle LRO resolution in the service.

lroCallables array
projectId string

The project ID.

name string

The database name or ID.

sessionPool Google\Cloud\Spanner\Session\SessionPoolInterface

[optional] The session pool implementation.

returnInt64AsObject bool

[optional If true, 64 bit integers will be returned as a {@see \Google\Cloud\Spanner\Google\Cloud\Core\Int64} object for 32 bit platform compatibility. Defaults to false.

info array
databaseRole string

The user created database role which creates the session.

state

Return the database state.

When databases are created or restored, they may take some time before they are ready for use. This method allows for checking whether a database is ready. Note that this value is cached within the class instance, so if you are polling it, first call Google\Cloud\Spanner\Google\Cloud\Spanner\Database::reload() to refresh the cached value.

Example:

if ($database->state() === Database::STATE_READY) {
    echo 'Database is ready!';
}
Parameter
NameDescription
options array

[optional] Configuration options.

Returns
TypeDescription
int|null

backups

List completed and pending backups belonging to this database.

Example:

$backups = $database->backups();
Parameters
NameDescription
options array

Configuration options.

↳ filter string

The standard list filter. NOTE: This method always sets the database filter as a name of this database. User may provide additional filter expressions which would be appended in the form of "(database:

↳ pageSize int

Maximum number of results to return per request.

↳ resultLimit int

Limit the number of results returned in total. Defaults to 0 (return all results).

↳ pageToken string

A previously-returned page token used to resume the loading of results from a specific point.

Returns
TypeDescription
Google\Cloud\Core\Iterator\ItemIterator<\google\cloud\spanner\backup>

createBackup

Create a backup for this database.

Example:

$operation = $database->createBackup('my-backup', new \DateTime('+7 hours'));
Parameters
NameDescription
name string

The backup name.

expireTime DateTimeInterface

​The expiration time of the backup, with microseconds granularity that must be at least 6 hours and at most 366 days. Once the expireTime has passed, the backup is eligible to be automatically deleted by Cloud Spanner.

options array

[optional] Configuration options.

Returns
TypeDescription
Google\Cloud\Core\LongRunning\LongRunningOperation<\google\cloud\spanner\backup>

name

Return the fully-qualified database name.

Example:

$name = $database->name();
Returns
TypeDescription
string

info

Get the database info

Example:

$info = $database->info();
Parameter
NameDescription
options array

[optional] Configuration options.

Returns
TypeDescription
array

reload

Reload the database info from the Cloud Spanner API.

Example:

$info = $database->reload();
Parameter
NameDescription
options array

[optional] Configuration options.

Returns
TypeDescription
array

exists

Check if the database exists.

This method sends a service request.

NOTE: Requires https://www.googleapis.com/auth/spanner.admin scope.

Example:

if ($database->exists()) {
    echo 'Database exists!';
}
Parameter
NameDescription
options array

[optional] Configuration options.

Returns
TypeDescription
bool

create

Create a new Cloud Spanner database.

Example:

$operation = $database->create();
Parameters
NameDescription
options array

Configuration Options

↳ statements string[]

Additional DDL statements.

Returns
TypeDescription
Google\Cloud\Core\LongRunning\LongRunningOperation<\google\cloud\spanner\database>

restore

Restores to this database from a backup.

NOTE: A restore operation can only be made to a non-existing database.

Example:

$operation = $database->restore($backup);
Parameters
NameDescription
backup Google\Cloud\Spanner\Backup|string

The backup to restore, given as a Backup instance or a string of the form projects/<project>/instances/<instance>/backups/<backup>.

options array

[optional] Configuration options.

Returns
TypeDescription
Google\Cloud\Core\LongRunning\LongRunningOperation<\google\cloud\spanner\database>

updateDdl

Update the Database schema by running a SQL statement.

NOTE: Requires https://www.googleapis.com/auth/spanner.admin scope.

Example:

$database->updateDdl(
    'CREATE TABLE Users (
        id INT64 NOT NULL,
        name STRING(100) NOT NULL
        password STRING(100) NOT NULL
    )'
);
Parameters
NameDescription
statement string

A DDL statements to run against a database.

options array

[optional] Configuration options.

Returns
TypeDescription
Google\Cloud\Core\LongRunning\LongRunningOperation

updateDdlBatch

Update the Database schema by running a set of SQL statements.

NOTE: Requires https://www.googleapis.com/auth/spanner.admin scope.

Example:

$database->updateDdlBatch([
    'CREATE TABLE Users (
        id INT64 NOT NULL,
        name STRING(100) NOT NULL,
        password STRING(100) NOT NULL
    ) PRIMARY KEY (id)',
    'CREATE TABLE Posts (
        id INT64 NOT NULL,
        title STRING(100) NOT NULL,
        content STRING(MAX) NOT NULL
    ) PRIMARY KEY(id)'
]);
Parameters
NameDescription
statements string[]

A list of DDL statements to run against a database.

options array

[optional] Configuration options.

Returns
TypeDescription
Google\Cloud\Core\LongRunning\LongRunningOperation

drop

Drop the database.

Please note that after a database is dropped, all sessions attached to it will be invalid and unusable. Calls to this method will clear any session pool attached to this database class instance and delete any sessions attached to the database class instance.

NOTE: Requires https://www.googleapis.com/auth/spanner.admin scope.

Example:

$database->drop();
Parameter
NameDescription
options array

[optional] Configuration options.

Returns
TypeDescription
void

ddl

Get a list of all database DDL statements.

NOTE: Requires https://www.googleapis.com/auth/spanner.admin scope.

Example:

$statements = $database->ddl();
Parameter
NameDescription
options array

[optional] Configuration options.

Returns
TypeDescription
array

iam

Manage the database IAM policy

Example:

$iam = $database->iam();
Returns
TypeDescription
Google\Cloud\Core\Iam\Iam

snapshot

Create a snapshot to read from a database at a point in time.

If no configuration options are provided, transaction will be opened with strong consistency.

Snapshots are executed behind the scenes using a Read-Only Transaction.

Example:

$snapshot = $database->snapshot();
// Take a shapshot with a returned timestamp.
$snapshot = $database->snapshot([
    'returnReadTimestamp' => true
]);

$timestamp = $snapshot->readTimestamp();
Parameters
NameDescription
options array

Configuration Options

See [ReadOnly](https://cloud.google.com/spanner/reference/rpc/google.spanner.v1#google.spanner.v1.TransactionOptions.ReadOnly)
for detailed description of available options.

Please note that only one of `$strong`, `$readTimestamp` or
`$exactStaleness` may be set in a request.
↳ returnReadTimestamp bool

If true, the Cloud Spanner-selected read timestamp is included in the Transaction message that describes the transaction.

↳ strong bool

Read at a timestamp where all previously committed transactions are visible.

↳ readTimestamp Timestamp

Executes all reads at the given timestamp.

↳ exactStaleness Duration

Represents a number of seconds. Executes all reads at a timestamp that is $exactStaleness old.

↳ minReadTimestamp Timestamp

Executes all reads at a timestamp >= min_read_timestamp. Only available when $options.singleUse is true.

↳ maxStaleness Duration

Read data at a timestamp >= NOW - max_staleness seconds. Guarantees that all writes that have committed more than the specified number of seconds ago are visible. Only available when $options.singleUse is true.

↳ singleUse bool

If true, a Transaction ID will not be allocated up front. Instead, the transaction will be considered "single-use", and may be used for only a single operation. Defaults to false.

↳ sessionOptions array

Session configuration and request options. Session labels may be applied using the labels key.

Returns
TypeDescription
Google\Cloud\Spanner\Snapshot

transaction

Create and return a new read/write Transaction.

When manually using a Transaction, it is advised that retry logic be implemented to reapply all operations when an instance of Google\Cloud\Spanner\Google\Cloud\Core\Exception\AbortedException is thrown.

If you wish Google Cloud PHP to handle retry logic for you (recommended for most cases), use Google\Cloud\Spanner\Google\Cloud\Spanner\Database::runTransaction().

Please note that once a transaction reads data, it will lock the read data, preventing other users from modifying that data. For this reason, it is important that every transaction commits or rolls back as early as possible. Do not hold transactions open longer than necessary.

Example:

$transaction = $database->transaction();
Parameters
NameDescription
options array

Configuration Options.

↳ singleUse bool

If true, a Transaction ID will not be allocated up front. Instead, the transaction will be considered "single-use", and may be used for only a single operation. Defaults to false.

↳ sessionOptions array

Session configuration and request options. Session labels may be applied using the labels key.

↳ tag string

A transaction tag. Requests made using this transaction will use this as the transaction tag.

Returns
TypeDescription
Google\Cloud\Spanner\Transaction

runTransaction

Execute Read/Write operations inside a Transaction.

Using this method and providing a callable operation provides certain benefits including automatic retry when a transaction fails. In case of a failure, all transaction operations, including reads, are re-applied in a new transaction.

If a transaction exceeds the maximum number of retries, Google\Cloud\Spanner\Google\Cloud\Core\Exception\AbortedException will be thrown. Any other exception types will immediately bubble up and will interrupt the retry operation.

Please note that once a transaction reads data, it will lock the read data, preventing other users from modifying that data. For this reason, it is important that every transaction commits or rolls back as early as possible. Do not hold transactions open longer than necessary.

Please also note that nested transactions are NOT supported by this client. Attempting to call runTransaction inside a transaction callable will raise a BadMethodCallException.

If a callable finishes executing without invoking Google\Cloud\Spanner\Google\Cloud\Spanner\Transaction::commit() or Google\Cloud\Spanner\Google\Cloud\Spanner\Transaction::rollback(), the transaction will automatically be rolled back and \RuntimeException thrown.

Example:

use Google\Cloud\Spanner\Timestamp;

$transaction = $database->runTransaction(function (Transaction $t) use ($username, $password) {
    $rows = $t->execute('SELECT * FROM Users WHERE Name = @name and PasswordHash = @password', [
        'parameters' => [
            'name' => $username,
            'password' => password_hash($password, PASSWORD_DEFAULT)
        ]
    ])->rows();
    $user = $rows->current();

    if ($user) {
        // Do something here to grant the user access.
        // Maybe set a cookie?

        $user['lastLoginTime'] = new Timestamp(new \DateTime);
        $user['loginCount'] = $user['loginCount'] + 1;
        $t->update('Users', $user);

        $t->commit();
    } else {
        $t->rollback();
    }
});
Parameters
NameDescription
operation callable

The operations to run in the transaction. Signature: function (Transaction $transaction).

options array

Configuration Options

↳ maxRetries int

The number of times to attempt to apply the operation before failing. **Defaults to ** 10.

↳ singleUse bool

If true, a Transaction ID will not be allocated up front. Instead, the transaction will be considered "single-use", and may be used for only a single operation. Note that in a single-use transaction, only a single operation may be executed, and rollback is not available. Defaults to false.

↳ sessionOptions array

Session configuration and request options. Session labels may be applied using the labels key.

↳ tag string

A transaction tag. Requests made using this transaction will use this as the transaction tag.

Returns
TypeDescription
mixedThe return value of `$operation`.

insert

Insert a row.

Mutations are committed in a single-use transaction.

Since this method does not feature replay protection, it may attempt to apply mutations more than once; if the mutations are not idempotent, this may lead to a failure being reported when the mutation was previously applied.

Example:

$database->insert('Posts', [
    'ID' => 1337,
    'postTitle' => 'Hello World!',
    'postContent' => 'Welcome to our site.'
]);
Parameters
NameDescription
table string

The table to mutate.

data array

The row data to insert.

options array

Configuration options.

↳ requestOptions array

Request options. For more information on available options, please see the upstream documentation. Please note, if using the priority setting you may utilize the constants available on Google\Cloud\Spanner\Google\Cloud\Spanner\V1\RequestOptions\Priority to set a value. Please note, the transactionTag setting will be ignored as it is not supported for single-use transactions.

Returns
TypeDescription
Google\Cloud\Spanner\TimestampThe commit Timestamp.

insertBatch

Insert multiple rows.

Mutations are committed in a single-use transaction.

Since this method does not feature replay protection, it may attempt to apply mutations more than once; if the mutations are not idempotent, this may lead to a failure being reported when the mutation was previously applied.

Example:

$database->insertBatch('Posts', [
    [
        'ID' => 1337,
        'postTitle' => 'Hello World!',
        'postContent' => 'Welcome to our site.'
    ], [
        'ID' => 1338,
        'postTitle' => 'Our History',
        'postContent' => 'Lots of people ask about where we got started.'
    ]
]);
Parameters
NameDescription
table string

The table to mutate.

dataSet array

The row data to insert.

options array

Configuration options.

↳ requestOptions array

Request options. For more information on available options, please see the upstream documentation. Please note, if using the priority setting you may utilize the constants available on Google\Cloud\Spanner\Google\Cloud\Spanner\V1\RequestOptions\Priority to set a value. Please note, the transactionTag setting will be ignored as it is not supported for single-use transactions.

Returns
TypeDescription
Google\Cloud\Spanner\TimestampThe commit Timestamp.

update

Update a row.

Only data which you wish to update need be included. The list of columns must contain enough columns to allow Cloud Spanner to derive values for all primary key columns in the row to be modified.

Mutations are committed in a single-use transaction.

Example:

$database->update('Posts', [
    'ID' => 1337,
    'postContent' => 'Thanks for visiting our site!'
]);
Parameters
NameDescription
table string

The table to mutate.

data array

The row data to update.

options array

Configuration options.

↳ requestOptions array

Request options. For more information on available options, please see the upstream documentation. Please note, if using the priority setting you may utilize the constants available on Google\Cloud\Spanner\Google\Cloud\Spanner\V1\RequestOptions\Priority to set a value. Please note, the transactionTag setting will be ignored as it is not supported for single-use transactions.

Returns
TypeDescription
Google\Cloud\Spanner\TimestampThe commit Timestamp.

updateBatch

Update multiple rows.

Only data which you wish to update need be included. The list of columns must contain enough columns to allow Cloud Spanner to derive values for all primary key columns in the row(s) to be modified.

Mutations are committed in a single-use transaction.

Example:

$database->updateBatch('Posts', [
    [
        'ID' => 1337,
        'postContent' => 'Thanks for visiting our site!'
    ], [
        'ID' => 1338,
        'postContent' => 'A little bit about us!'
    ]
]);
Parameters
NameDescription
table string

The table to mutate.

dataSet array

The row data to update.

options array

Configuration options.

↳ requestOptions array

Request options. For more information on available options, please see the upstream documentation. Please note, if using the priority setting you may utilize the constants available on Google\Cloud\Spanner\Google\Cloud\Spanner\V1\RequestOptions\Priority to set a value. Please note, the transactionTag setting will be ignored as it is not supported for single-use transactions.

Returns
TypeDescription
Google\Cloud\Spanner\TimestampThe commit Timestamp.

insertOrUpdate

Insert or update a row.

If a row already exists (determined by comparing the Primary Key to existing table data), the row will be updated. If not, it will be created.

Mutations are committed in a single-use transaction.

Example:

$database->insertOrUpdate('Posts', [
    'ID' => 1337,
    'postTitle' => 'Hello World!',
    'postContent' => 'Thanks for visiting our site!'
]);
Parameters
NameDescription
table string

The table to mutate.

data array

The row data to insert or update.

options array

Configuration options.

↳ requestOptions array

Request options. For more information on available options, please see the upstream documentation. Please note, if using the priority setting you may utilize the constants available on Google\Cloud\Spanner\Google\Cloud\Spanner\V1\RequestOptions\Priority to set a value. Please note, the transactionTag setting will be ignored as it is not supported for single-use transactions.

Returns
TypeDescription
Google\Cloud\Spanner\TimestampThe commit Timestamp.

insertOrUpdateBatch

Insert or update multiple rows.

If a row already exists (determined by comparing the Primary Key to existing table data), the row will be updated. If not, it will be created.

Mutations are committed in a single-use transaction.

Example:

$database->insertOrUpdateBatch('Posts', [
    [
        'ID' => 1337,
        'postTitle' => 'Hello World!',
        'postContent' => 'Thanks for visiting our site!'
    ], [
        'ID' => 1338,
        'postTitle' => 'Our History',
        'postContent' => 'A little bit about us!'
    ]
]);
Parameters
NameDescription
table string

The table to mutate.

dataSet array

The row data to insert or update.

options array

Configuration options.

↳ requestOptions array

Request options. For more information on available options, please see the upstream documentation. Please note, if using the priority setting you may utilize the constants available on Google\Cloud\Spanner\Google\Cloud\Spanner\V1\RequestOptions\Priority to set a value. Please note, the transactionTag setting will be ignored as it is not supported for single-use transactions.

Returns
TypeDescription
Google\Cloud\Spanner\TimestampThe commit Timestamp.

replace

Replace a row.

Provide data for the entire row. Cloud Spanner will attempt to find a record matching the Primary Key, and will replace the entire row. If a matching row is not found, it will be inserted.

Mutations are committed in a single-use transaction.

Example:

$database->replace('Posts', [
    'ID' => 1337,
    'postTitle' => 'Hello World!',
    'postContent' => 'Thanks for visiting our site!'
]);
Parameters
NameDescription
table string

The table to mutate.

data array

The row data to replace.

options array

Configuration options.

↳ requestOptions array

Request options. For more information on available options, please see the upstream documentation. Please note, if using the priority setting you may utilize the constants available on Google\Cloud\Spanner\Google\Cloud\Spanner\V1\RequestOptions\Priority to set a value. Please note, the transactionTag setting will be ignored as it is not supported for single-use transactions.

Returns
TypeDescription
Google\Cloud\Spanner\TimestampThe commit Timestamp.

replaceBatch

Replace multiple rows.

Provide data for the entire row. Cloud Spanner will attempt to find a record matching the Primary Key, and will replace the entire row. If a matching row is not found, it will be inserted.

Mutations are committed in a single-use transaction.

Example:

$database->replaceBatch('Posts', [
    [
        'ID' => 1337,
        'postTitle' => 'Hello World!',
        'postContent' => 'Thanks for visiting our site!'
    ], [
        'ID' => 1338,
        'postTitle' => 'Our History',
        'postContent' => 'A little bit about us!'
    ]
]);
Parameters
NameDescription
table string

The table to mutate.

dataSet array

The row data to replace.

options array

Configuration options.

↳ requestOptions array

Request options. For more information on available options, please see the upstream documentation. Please note, if using the priority setting you may utilize the constants available on Google\Cloud\Spanner\Google\Cloud\Spanner\V1\RequestOptions\Priority to set a value. Please note, the transactionTag setting will be ignored as it is not supported for single-use transactions.

Returns
TypeDescription
Google\Cloud\Spanner\TimestampThe commit Timestamp.

delete

Delete one or more rows.

Mutations are committed in a single-use transaction.

Since this method does not feature replay protection, it may attempt to apply mutations more than once; if the mutations are not idempotent, this may lead to a failure being reported when the mutation was previously applied.

Example:

$keySet = new KeySet([
    'keys' => [
        1337, 1338
    ]
]);

$database->delete('Posts', $keySet);
Parameters
NameDescription
table string

The table to mutate.

keySet Google\Cloud\Spanner\KeySet

The KeySet to identify rows to delete.

options array

Configuration options.

↳ requestOptions array

Request options. For more information on available options, please see the upstream documentation. Please note, if using the priority setting you may utilize the constants available on Google\Cloud\Spanner\Google\Cloud\Spanner\V1\RequestOptions\Priority to set a value. Please note, the transactionTag setting will be ignored as it is not supported for single-use transactions.

Returns
TypeDescription
Google\Cloud\Spanner\TimestampThe commit Timestamp.

execute

Run a query.

Google Cloud PHP will infer parameter types for all primitive types and all values implementing Google\Cloud\Spanner\Google\Cloud\Spanner\ValueInterface, with the exception of null. Non-associative arrays will be interpreted as a Spanner ARRAY type, and must contain only a single type of value. Associative arrays or values of type Google\Cloud\Spanner\Google\Cloud\Spanner\StructValue will be interpreted as Spanner STRUCT type. Structs MUST always explicitly define their field types.

In any case where the value of a parameter may be null, you MUST explicitly define the parameter's type.

With the exception of arrays and structs, types are defined using a type constant defined on Google\Cloud\Spanner\Google\Cloud\Spanner\Database. Examples include but are not limited to Database::TYPE_STRING and Database::TYPE_INT64.

Arrays, when explicitly typing, should use an instance of Google\Cloud\Spanner\Google\Cloud\Spanner\ArrayType to declare their type and the types of any values contained within the array elements.

Structs must always declare their type using an instance of Google\Cloud\Spanner\Google\Cloud\Spanner\StructType. Struct values may be expressed as an associative array, however if the struct contains any unnamed fields, or any fields with duplicate names, the struct must be expressed using an instance of Google\Cloud\Spanner\Google\Cloud\Spanner\StructValue. Struct value types may be inferred with the same caveats as top-level parameters (in other words, so long as they are not nullable and do not contain nested structs).

Example:

$result = $database->execute('SELECT * FROM Posts WHERE ID = @postId', [
    'parameters' => [
        'postId' => 1337
    ]
]);

$firstRow = $result->rows()->current();
// Parameters which may be null must include an expected parameter type.
use Google\Cloud\Spanner\Database;
use Google\Cloud\Spanner\Timestamp;

$values = [
    new Timestamp(new \DateTimeImmutable),
    null
];

$result = $database->execute('SELECT @timestamp as timestamp', [
    'parameters' => [
        'timestamp' => array_rand($values)
    ],
    'types' => [
        'timestamp' => Database::TYPE_TIMESTAMP
    ]
]);

$timestamp = $result->rows()->current()['timestamp'];
// Array parameters which may be null or empty must include the array value type.
use Google\Cloud\Spanner\ArrayType;
use Google\Cloud\Spanner\Database;

$result = $database->execute('SELECT @emptyArrayOfIntegers as numbers', [
    'parameters' => [
        'emptyArrayOfIntegers' => []
    ],
    'types' => [
        'emptyArrayOfIntegers' => new ArrayType(Database::TYPE_INT64)
    ]
]);

$row = $result->rows()->current();
$emptyArray = $row['numbers'];
// Struct parameters provide a type definition. Fields within a Struct may
// be inferred following the same rules as top-level parameters. Any
// nested structs must be an instance of `Google\Cloud\Spanner\StructType`,
// and any values which could be of type `null` must explicitly specify
// their type.
use Google\Cloud\Spanner\Database;
use Google\Cloud\Spanner\StructType;

$result = $database->execute('SELECT @userStruct.firstName, @userStruct.lastName', [
    'parameters' => [
        'userStruct' => [
            'firstName' => 'John',
            'lastName' => 'Testuser'
        ]
    ],
    'types' => [
        'userStruct' => (new StructType())
            ->add('firstName', Database::TYPE_STRING)
            ->add('lastName', Database::TYPE_STRING)
    ]
]);

$row = $result->rows()->current();
$fullName = $row['firstName'] . ' ' . $row['lastName']; // `John Testuser`
// If a struct contains unnamed fields, or multiple fields with the same
// name, it must be defined using <xref uid="\Google\Cloud\Spanner\Google\Cloud\Spanner\StructValue">Google\Cloud\Spanner\Google\Cloud\Spanner\StructValue</xref>.
use Google\Cloud\Spanner\Database;
use Google\Cloud\Spanner\Result;
use Google\Cloud\Spanner\StructValue;
use Google\Cloud\Spanner\StructType;

$res = $database->execute('SELECT * FROM UNNEST(ARRAY(SELECT @structParam))', [
    'parameters' => [
        'structParam' => (new StructValue)
            ->add('foo', 'bar')
            ->add('foo', 2)
            ->addUnnamed('this field is unnamed')
    ],
    'types' => [
        'structParam' => (new StructType)
            ->add('foo', Database::TYPE_STRING)
            ->add('foo', Database::TYPE_INT64)
            ->addUnnamed(Database::TYPE_STRING)
    ]
])->rows(Result::RETURN_NAME_VALUE_PAIR)->current();

echo $res[0]['name'] . ': ' . $res[0]['value'] . PHP_EOL; // "foo: bar"
echo $res[1]['name'] . ': ' . $res[1]['value'] . PHP_EOL; // "foo: 2"
echo $res[2]['name'] . ': ' . $res[2]['value'] . PHP_EOL; // "2: this field is unnamed"
// Execute a read and return a new Snapshot for further reads.
use Google\Cloud\Spanner\Session\SessionPoolInterface;

$result = $database->execute('SELECT * FROM Posts WHERE ID = @postId', [
     'parameters' => [
        'postId' => 1337
    ],
    'begin' => true,
    'transactionType' => SessionPoolInterface::CONTEXT_READ
]);

$result->rows()->current();

$snapshot = $result->snapshot();
// Execute a read and return a new Transaction for further reads and writes.
use Google\Cloud\Spanner\Session\SessionPoolInterface;

$result = $database->execute('SELECT * FROM Posts WHERE ID = @postId', [
     'parameters' => [
        'postId' => 1337
    ],
    'begin' => true,
    'transactionType' => SessionPoolInterface::CONTEXT_READWRITE
]);

$result->rows()->current();

$transaction = $result->transaction();
Parameters
NameDescription
sql string

The query string to execute.

options array

Configuration Options. See TransactionOptions for detailed description of available transaction options. Please note that only one of $strong, $minReadTimestamp, $maxStaleness, $readTimestamp or $exactStaleness may be set in a request.

↳ parameters array

A key/value array of Query Parameters, where the key is represented in the query string prefixed by a @ symbol.

↳ types array

A key/value array of Query Parameter types. Generally, Google Cloud PHP can infer types. Explicit type declarations are required in the case of struct parameters, or when a null value exists as a parameter. Accepted values for primitive types are defined as constants on Google\Cloud\Spanner\Google\Cloud\Spanner\Database, and are as follows: Database::TYPE_BOOL, Database::TYPE_INT64, Database::TYPE_FLOAT64, Database::TYPE_TIMESTAMP, Database::TYPE_DATE, Database::TYPE_STRING, Database::TYPE_BYTES. If the value is an array, use Google\Cloud\Spanner\Google\Cloud\Spanner\ArrayType to declare the array parameter types. Likewise, for structs, use Google\Cloud\Spanner\Google\Cloud\Spanner\StructType.

↳ returnReadTimestamp bool

If true, the Cloud Spanner-selected read timestamp is included in the Transaction message that describes the transaction.

↳ strong bool

Read at a timestamp where all previously committed transactions are visible.

↳ minReadTimestamp Timestamp

Execute reads at a timestamp >= the given timestamp. Only available in single-use transactions.

↳ maxStaleness Duration

Read data at a timestamp >= NOW - the given timestamp. Only available in single-use transactions.

↳ readTimestamp Timestamp

Executes all reads at the given timestamp.

↳ exactStaleness Duration

Represents a number of seconds. Executes all reads at a timestamp that is $exactStaleness old.

↳ begin bool

If true, will begin a new transaction. If a read/write transaction is desired, set the value of $transactionType. If a transaction or snapshot is created, it will be returned as $result->transaction() or $result->snapshot(). Defaults to false.

↳ transactionType string

One of SessionPoolInterface::CONTEXT_READ or SessionPoolInterface::CONTEXT_READWRITE. If read/write is chosen, any snapshot options will be disregarded. If $begin is false, transaction type MUST be SessionPoolInterface::CONTEXT_READ. Defaults to SessionPoolInterface::CONTEXT_READ.

↳ sessionOptions array

Session configuration and request options. Session labels may be applied using the labels key.

↳ queryOptions array

Query optimizer configuration.

↳ queryOptions string

.optimizerVersion An option to control the selection of optimizer version. This parameter allows individual queries to pick different query optimizer versions. Specifying "latest" as a value instructs Cloud Spanner to use the latest supported query optimizer version. If not specified, Cloud Spanner uses optimizer version set at the client level options or set by the SPANNER_OPTIMIZER_VERSION environment variable. Any other positive integer (from the list of supported optimizer versions) overrides the default optimizer version for query execution. Executing a SQL statement with an invalid optimizer version will fail with a syntax error (INVALID_ARGUMENT) status.

↳ requestOptions array

Request options. For more information on available options, please see the upstream documentation. Please note, if using the priority setting you may utilize the constants available on Google\Cloud\Spanner\Google\Cloud\Spanner\V1\RequestOptions\Priority to set a value. Please note, the transactionTag setting will be ignored as it is not supported for read-only transactions.

Returns
TypeDescription
Google\Cloud\Spanner\Result

executePartitionedUpdate

Execute a partitioned DML update.

Returns the lower bound of rows modified by the DML statement.

PLEASE NOTE Most use cases for DML are better served by using Google\Cloud\Spanner\Google\Cloud\Spanner\Transaction::executeUpdate(). Please read and understand the documentation for partitioned DML before implementing it in your application.

Data Manipulation Language (DML) allows you to execute statements which modify the state of the database (i.e. inserting, updating or deleting rows).

To execute a SELECT statement, use Google\Cloud\Spanner\Google\Cloud\Spanner\Database::execute().

The method will block until the update is complete. Running a DML statement with this method does not offer exactly once semantics, and therefore the DML statement should be idempotent. The DML statement must be fully-partitionable. Specifically, the statement must be expressible as the union of many statements which each access only a single row of the table. Partitioned DML partitions the key space and runs the DML statement over each partition in parallel using separate, internal transactions that commit independently.

Partitioned DML is good fit for large, database-wide, operations that are idempotent. Partitioned DML enables large-scale changes without running into transaction size limits or accidentally locking the entire table in one large transaction. Smaller scoped statements, such as an OLTP workload, should prefer using Google\Cloud\Spanner\Google\Cloud\Spanner\Transaction::executeUpdate().

  • The DML statement must be fully-partitionable. Specifically, the statement must be expressible as the union of many statements which each access only a single row of the table.
  • The statement is not applied atomically to all rows of the table. Rather, the statement is applied atomically to partitions of the table, in independent internal transactions. Secondary index rows are updated atomically with the base table rows.
  • Partitioned DML does not guarantee exactly-once execution semantics against a partition. The statement will be applied at least once to each partition. It is strongly recommended that the DML statement should be idempotent to avoid unexpected results. For instance, it is potentially dangerous to run a statement such as UPDATE table SET column = column + 1 as it could be run multiple times against some rows.
  • The partitions are committed automatically - there is no support for Commit or Rollback. If the call returns an error, or if the client issuing the DML statement dies, it is possible that some rows had the statement executed on them successfully. It is also possible that the statement was never executed against other rows.
  • If any error is encountered during the execution of the partitioned DML operation (for instance, a UNIQUE INDEX violation, division by zero, or a value that cannot be stored due to schema constraints), then the operation is stopped at that point and an error is returned. It is possible that at this point, some partitions have been committed (or even committed multiple times), and other partitions have not been run at all.

Given the above, Partitioned DML is good fit for large, database-wide, operations that are idempotent, such as deleting old rows from a very large table.

Please refer to the TransactionOptions documentation referenced below in order to fully understand the semantics and intended use case for partitioned DML updates.

Example:

use Google\Cloud\Spanner\Date;

$deactivatedUserCount = $database->executePartitionedUpdate(
    'UPDATE Users u SET u.activeSubscription = false, u.subscriptionEndDate = @date ' .
    'WHERE TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), u.lastBillDate, DAY) > 365',
    [
        'parameters' => [
            'date' => new Date(new \DateTime)
        ]
    ]
);
Parameters
NameDescription
statement string

The DML statement to execute.

options array

Configuration Options.

↳ parameters array

A key/value array of Query Parameters, where the key is represented in the statement prefixed by a @ symbol.

↳ types array

A key/value array of Query Parameter types. Generally, Google Cloud PHP can infer types. Explicit type declarations are required in the case of struct parameters, or when a null value exists as a parameter. Accepted values for primitive types are defined as constants on Google\Cloud\Spanner\Google\Cloud\Spanner\Database, and are as follows: Database::TYPE_BOOL, Database::TYPE_INT64, Database::TYPE_FLOAT64, Database::TYPE_TIMESTAMP, Database::TYPE_DATE, Database::TYPE_STRING, Database::TYPE_BYTES. If the value is an array, use Google\Cloud\Spanner\Google\Cloud\Spanner\ArrayType to declare the array parameter types. Likewise, for structs, use Google\Cloud\Spanner\Google\Cloud\Spanner\StructType.

↳ requestOptions array

Request options. For more information on available options, please see the upstream documentation. Please note, if using the priority setting you may utilize the constants available on Google\Cloud\Spanner\Google\Cloud\Spanner\V1\RequestOptions\Priority to set a value. Please note, the transactionTag setting will be ignored as it is not supported for partitioned DML.

Returns
TypeDescription
intThe number of rows modified.

read

Lookup rows in a table.

Example:

use Google\Cloud\Spanner\KeySet;

$keySet = new KeySet([
    'keys' => [1337]
]);

$columns = ['ID', 'title', 'content'];

$result = $database->read('Posts', $keySet, $columns);

$firstRow = $result->rows()->current();
// Execute a read and return a new Snapshot for further reads.
use Google\Cloud\Spanner\KeySet;
use Google\Cloud\Spanner\Session\SessionPoolInterface;

$keySet = new KeySet([
    'keys' => [1337]
]);

$columns = ['ID', 'title', 'content'];

$result = $database->read('Posts', $keySet, $columns, [
    'begin' => true,
    'transactionType' => SessionPoolInterface::CONTEXT_READ
]);

$result->rows()->current();

$snapshot = $result->snapshot();
// Execute a read and return a new Transaction for further reads and writes.
use Google\Cloud\Spanner\KeySet;
use Google\Cloud\Spanner\Session\SessionPoolInterface;

$keySet = new KeySet([
    'keys' => [1337]
]);

$columns = ['ID', 'title', 'content'];

$result = $database->read('Posts', $keySet, $columns, [
    'begin' => true,
    'transactionType' => SessionPoolInterface::CONTEXT_READWRITE
]);

$result->rows()->current();

$transaction = $result->transaction();
Parameters
NameDescription
table string

The table name.

keySet Google\Cloud\Spanner\KeySet

The KeySet to select rows.

columns array

A list of column names to return.

options array

Configuration Options.

See [TransactionOptions](https://cloud.google.com/spanner/docs/reference/rpc/google.spanner.v1#google.spanner.v1.TransactionOptions)
for detailed description of available transaction options.

Please note that only one of `$strong`, `$minReadTimestamp`,
`$maxStaleness`, `$readTimestamp` or `$exactStaleness` may be set in
a request.
↳ index string

The name of an index on the table.

↳ limit int

The number of results to return.

↳ returnReadTimestamp bool

If true, the Cloud Spanner-selected read timestamp is included in the Transaction message that describes the transaction.

↳ strong bool

Read at a timestamp where all previously committed transactions are visible.

↳ minReadTimestamp Timestamp

Execute reads at a timestamp >= the given timestamp. Only available in single-use transactions.

↳ maxStaleness Duration

Read data at a timestamp >= NOW - the given timestamp. Only available in single-use transactions.

↳ readTimestamp Timestamp

Executes all reads at the given timestamp.

↳ exactStaleness Duration

Represents a number of seconds. Executes all reads at a timestamp that is $exactStaleness old.

↳ begin bool

If true, will begin a new transaction. If a read/write transaction is desired, set the value of $transactionType. If a transaction or snapshot is created, it will be returned as $result->transaction() or $result->snapshot(). Defaults to false.

↳ transactionType string

One of SessionPoolInterface::CONTEXT_READ or SessionPoolInterface::CONTEXT_READWRITE. If read/write is chosen, any snapshot options will be disregarded. If $begin is false, transaction type MUST be SessionPoolInterface::CONTEXT_READ. Defaults to SessionPoolInterface::CONTEXT_READ.

↳ sessionOptions array

Session configuration and request options. Session labels may be applied using the labels key.

↳ requestOptions array

Request options. For more information on available options, please see the upstream documentation. Please note, if using the priority setting you may utilize the constants available on Google\Cloud\Spanner\Google\Cloud\Spanner\V1\RequestOptions\Priority to set a value. Please note, the transactionTag setting will be ignored as it is not supported for read-only transactions.

Returns
TypeDescription
Google\Cloud\Spanner\Result

sessionPool

Get the underlying session pool implementation.

Example:

$pool = $database->sessionPool();
Returns
TypeDescription
Google\Cloud\Spanner\Session\SessionPoolInterface|null

close

Closes the database connection by returning the active session back to the session pool queue or by deleting the session if there is no pool associated.

It is highly important to ensure this is called as it is not always safe to rely soley on Google\Cloud\Spanner\Google\Cloud\Spanner\Database::__destruct().

Example:

$database->close();

__destruct

Closes the database connection.

createSession

Create a new session.

Sessions are handled behind the scenes and this method does not need to be called directly.

Parameter
NameDescription
options array

[optional] Configuration options.

Returns
TypeDescription
Google\Cloud\Spanner\Session\Session

session

Lazily instantiates a session. There are no network requests made at this point. To see the operations that can be performed on a session please see Google\Cloud\Spanner\Session\Session.

Sessions are handled behind the scenes and this method does not need to be called directly.

Parameter
NameDescription
sessionName string

The session's name.

Returns
TypeDescription
Google\Cloud\Spanner\Session\Session

identity

Retrieves the database's identity.

Returns
TypeDescription
array

connection

Returns the underlying connection.

Returns
TypeDescription
Google\Cloud\Spanner\Connection\ConnectionInterface

__debugInfo

Represent the class in a more readable and digestable fashion.

Constants

STATE_CREATING

Value: \Google\Cloud\Spanner\Admin\Database\V1\Database\State::CREATING

STATE_READY

Value: \Google\Cloud\Spanner\Admin\Database\V1\Database\State::READY

STATE_READY_OPTIMIZING

Value: \Google\Cloud\Spanner\Admin\Database\V1\Database\State::READY_OPTIMIZING

MAX_RETRIES

Value: 10

TYPE_BOOL

Value: \Google\Cloud\Spanner\V1\TypeCode::BOOL

TYPE_INT64

Value: \Google\Cloud\Spanner\V1\TypeCode::INT64

TYPE_FLOAT64

Value: \Google\Cloud\Spanner\V1\TypeCode::FLOAT64

TYPE_TIMESTAMP

Value: \Google\Cloud\Spanner\V1\TypeCode::TIMESTAMP

TYPE_DATE

Value: \Google\Cloud\Spanner\V1\TypeCode::DATE

TYPE_STRING

Value: \Google\Cloud\Spanner\V1\TypeCode::STRING

TYPE_BYTES

Value: \Google\Cloud\Spanner\V1\TypeCode::BYTES

TYPE_ARRAY

Value: \Google\Cloud\Spanner\V1\TypeCode::PBARRAY

TYPE_STRUCT

Value: \Google\Cloud\Spanner\V1\TypeCode::STRUCT

TYPE_NUMERIC

Value: \Google\Cloud\Spanner\V1\TypeCode::NUMERIC

TYPE_PG_NUMERIC

Value: 'pgNumeric'

TYPE_PG_JSONB

Value: 'pgJsonb'

TYPE_JSON

Value: \Google\Cloud\Spanner\V1\TypeCode::JSON