The ActiveRecord Spanner adapter should support creating and using ARRAY typed columns.

Cloud Spanner can set a column of a record to the commit timestamp of the last transaction that updated the record. This feature can be used to create a change log for the table. It should be possible to set the attribute of a model to the commit timestamp of the transaction that last updated the record using ActiveRecord.

PDML could be supported through a custom optimizer hint in the same way as we support stale reads.

Users will need to have functional support for Calculations and we'll need to generate appropriate SQL for

I've started enumerating the methods given here https://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/AbstractAdapter.html and let's use the research and table below and in https://docs.google.com/document/d/1b2l6bxJktrJUFdjQSf4z2K0dtGtOGNLU1v2lHTenV70/edit#heading=h.bo5jfmjjyija

Ref :
https://cloud.google.com/spanner/docs/lexical#string_and_bytes_literals

The schema dumper does not recognize all Spanner-specific features. At least the following features seem to be problematic:

• allow_commit_timestamp: true is not generated
• TIMESTAMP columns are registered with type time
• Interleaved in?

Daniel Azuma finally got the response from Yugui and they added Google Cloud Developers as owners of activerecord-spanner. We now need to reflect that change here.

This issue lists Renovate updates and detected dependencies. Read the Dependency Dashboard docs to learn more.

Open

These updates have all been created already. Click a checkbox below to force a retry/rebase of any.

• chore(deps): update dependency minitest to "~> 5.24.0"

Ignored or Blocked

These are blocked by an existing closed PR and will not be recreated unless you click a checkbox below.

• chore(deps): update dependency pry to "~> 0.14.0"
• chore(deps): update dependency pry-byebug to "~> 3.10.0"

Detected dependencies

• Check this box to trigger a request for Renovate to run again on this repository

Saves/updates outside of a transaction block should use Mutations instead of DML, as the changes cannot be read back in the same transaction anyways. Mutations are a lot faster than DML for (small) updates.

Summary

I am trying to introduce the Spanner Emulator for acceptance test. While doing that, I noticed that the acceptance test on the Cloud Spanner is significantly slower than that on the Spanner Emulator.

I would like to know whether or not this is the expected result, in other words, whether or not the problem is due to my GCP environment or not.

Since I know few people using this gem, I thought this is the best place to create an issue.

Hopefully, I would like to know the possible options to improve the test speed. They can be converted into Issues or Pull Requests.

Details

• Cloud Spanner from GCE or my MacBook Pro
  • bundle exec rake acceptance
  • it takes more than several hours
• Spanner Emulator on docker-compose on MacBook Pro
  • below 1 minute

Following are the partial output from the test. You can see creating table takes much longer time when running on Cloud Spanner followed by very slow examples. Although the test is executed on shared resource of Cloud Spanner instance, the approximate maginitude did not change against the CPU usage.

Spanner Emulator

Loading test schema...
-- create_table(:firms)
   -> 0.0379s
-- create_table(:customers)
   -> 0.0015s
-- create_table(:accounts)
   -> 0.0015s
-- create_table(:transactions)
   -> 0.0014s
-- create_table(:departments)
   -> 0.0016s
-- create_table(:member_types)
   -> 0.0014s
-- create_table(:members)
   -> 0.0014s
-- create_table(:memberships)
   -> 0.0014s
-- create_table(:clubs)
   -> 0.0014s
-- create_table(:authors)
   -> 0.0015s
-- create_table(:posts)
   -> 0.0018s
-- create_table(:comments)
   -> 0.0018s
-- create_table(:addresses)
   -> 0.0023s
-- create_table(:organizations)
   -> 0.0017s
Run options: --seed 36288

# Running:

...................E..F...S.....S...EEEEEEEEEEEEEEEEE......................E.EEEEEE.......................................................................E...............................................EE.......................EEE....E.EE.E.EEE..E........E..E.E.E...........................................EE...........E....

Finished in 47.569106s, 6.8111 runs/s, 9.0605 assertions/s.

##### there are many omitted error results here ######

324 runs, 431 assertions, 1 failures, 46 errors, 2 skips

Cloud Spanner

'ar-test-74603537' test db created.
Loading test schema...
-- create_table(:firms)
   -> 24.1521s
-- create_table(:customers)
   -> 23.5265s
-- create_table(:accounts)
   -> 23.2956s
-- create_table(:transactions)
   -> 23.3205s
-- create_table(:departments)
   -> 23.3046s
-- create_table(:member_types)
   -> 23.3518s
-- create_table(:members)
   -> 23.2980s
-- create_table(:memberships)
   -> 23.3585s
-- create_table(:clubs)
   -> 23.3070s
-- create_table(:authors)
   -> 23.3006s
-- create_table(:posts)
   -> 23.3243s
-- create_table(:comments)
   -> 23.3511s
-- create_table(:addresses)
   -> 23.3598s
-- create_table(:organizations)
   -> 23.3078s
Run options: --seed 57206

# Running:

......................................................................................^Crake aborted!
Interrupt: 
Interrupted. Exiting...

#####  there are omitted error message with backtrace ##### 

Finished in 7433.238541s, 0.0116 runs/s, 0.0186 assertions/s.
Tasks: TOP => run

(See full trace by running task with --trace)
86 runs, 138 assertions, 0 failures, 0 errors, 0 skips

Summary

According to the best practices on Cloud Spanner, it is recommended to use the query parameters to effectively use query plan cache as follows.

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = @title AND a.ReleaseDate >= '2017-01-01'

When checking ruby-spanner-activerecord locally in my environment, following Ruby code did not generate query with parameters, but I would like to generate queries with parameters.

Sample.where(id: [1, 2, 3]).to_a
# => D, [2021-03-06T04:54:08.308896 #28539] DEBUG -- :   Sample Load (271.6ms)  SELECT `samples`.* FROM `samples` WHERE `samples`.`id` IN ('1', '2', '3')

Then, I have found that the following code disables prepared_statements.

Unlike traditional RDBMS, Cloud Spanner does NOT currently support a prepared statement. However, the objective of using the query parameters is the same; that is, to cache query plan and improve performance. I think the difference is to retrieve the cached query plan by some identifiers (Traditional RDBMS) or by full queries (Cloud Spanner).

Proposed Solution

In light of the above considerations, IMHO, I think it is intuitive to use query parameters when prepared_statements = true. When Cloud Spanner actually introduces prepared statements in the future, we can seamlessly migrate adapter to using real prepared statements.

References

https://cloud.google.com/spanner/docs/sql-best-practices?hl=en#query-parameters
https://www.slideshare.net/HammoudiSamir/cloud-spanner-78081604#25 # Sorry but written in Japanese

The current implementation is not compatible with Rails 6.1.

Additional context
This is a related change in upstream ActiveRecord:
rails/rails@b744372#diff-414d80c69e0a30eb66d50bc71af4e80ceee02b666003e7a99a65f1c79a778c98

At #8, I heard that Google Cloud Developers became the co-owners of https://rubygems.org/gems/activerecord-spanner-adapter.

Now, I am wondering when and how to migrate the gem on rubygems.org.

The reason behind this question is that I think releasing the alpha-version of this repository would increase developers' recognition of this repository and ease their development process. (e.g. install from rubygems.org, GitHub links from rubygems.org, and so on)

Also, it would ease the contribution process to this repository.

The Spanner ActiveRecord adapter should support optimistic locking, also when the adapter uses mutations instead of DML.

https://api.rubyonrails.org/v6.1.3.2/classes/ActiveRecord/Locking/Optimistic.html

ActiveRecord requires primary keys to only contain one column. This is incompatible with interleaved tables in Cloud Spanner, as a child table must include all the columns of the primary key of the parent table in its own primary key, in addition to any of its own primary key columns. The Spanner ActiveRecord provider works around this by ensuring that the values of a child id is always globally unique within the table (as opposed to unique for each parent key), and uses only the child id as a primary key when fetching rows. This means that fetching a single child row using only the key value can cause a full table scan, as there is no index on only the child id column. The Spanner ActiveRecord provider should therefore:

1. Automatically create a unique index on the child id column when creating interleaved tables in migrations.
2. Document that existing interleaved tables that are to be used with ActiveRecord should also be altered to include a unique index on the child id column.
3. Alter the existing interleaved tables sample to include the above.

Matching the logic in https://github.com/rails/rails/blob/master/activerecord/MIT-LICENSE of the parent, let's ensure that the license mirrors that of the parent project i.e. MIT license

/cc @vmanghnani

Issue: The migration version will not be inserted in "schema_migrations" if any error occurs in the schema migration task. So next time on migration run migration task will not resume from the last failure but start from the first migration script or from if last version value is available in schema_migrations.

Rails migration doc for reference

Schema migration will create a batch of DDL statement and execute in one go. Related issues for this scenario.

1. Any error occurs in the execution of one of the DDL statement then migration task is not able to figure out which migration file has this error based on GRPC error response received from the Google Spanner service, because of this it is a schema
2. Information schema will not be available in certain cases if there is DQL or DML query executed using Model.

class AddTimestampToFirm < ActiveRecord::Migration[6.0]
  def change
    add_column :firm, :created_at, :time
    
    Firm.update_all(created_at: Time.now)
  end
end

Here at a time of the Firm.update_all(created_at: Time.now) call created_at is not available so it will throw an error.

Need to do is,
Translate error and find out last failed DDL statement and based on this find out-migration file version.

The annotate query method should be translated to a query tag: https://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-annotate

Code: https://github.com/solidusio/solidus
Site: http://solidus.io/

Cloud Spanner has support for read-only transactions that can read data from a consistent snapshot at a specific timestamp. These transactions should also be usable with ActiveRecord.

Ref doc: https://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html

Policy Bot found one or more issues with this repository.

• Default branch is 'main'
• Branch protection is enabled
• Renovate bot is enabled
• Merge commits disabled
• There is a CODEOWNERS file
• There is a valid LICENSE.md
• There is a CODE_OF_CONDUCT.md
• There is a CONTRIBUTING.md
• There is a SECURITY.md

Cloud Spanner's server side lacks AUTO_INCREMENT support and requires that any field whose DDL statement contained an AUTO_INCREMENT should be manipulated by the client side and generate most ideally a UUID to avoid "hotspots" as per https://cloud.google.com/spanner/docs/schema-design#uuid_primary_key

This creates a complication however for apps that might be using the column key to peek for the next, or sorting by ID assuming that every ID will be sorted by magnitude, but also for every single INSERT, we'll need to cross reference with the table DDL statements to examine which field needs an ID automatically generated for it.

We MUST pass in a user-agent into our connection for the purpose of usage tracking for example as per

x-goog-api-client': \
'spanner-activerecord/0.0.1 gl-ruby/<VERSION> grpc/<gRPC_VERSION> gax/<GAX_VERSION>' 

Support creating interleaved indexes using ActiveRecord.

Each connection uses one session. That session will be garbage collected by the backend if it is not used for more than 60 minutes, but could also occasionally be cleaned up by the backend for other reasons. The ActiveRecord adapter should therefore retry any Session not found errors. The exact retry strategy depends on whether it happens during an explicit read/write transaction or not.

See https://github.com/googleapis/ruby-spanner-activerecord/pull/98/checks?check_run_id=2996223338

  1) Error:
ActiveRecord::Associations::HasManyUsingInterleavedTest#test_successful_build_association:
Google::Cloud::AbortedError: 10:Transaction was aborted.. debug_error_string:***"created":"@1625555571.311616789","description":"Error received from peer ipv4:142.250.73.234:443","file":"src/core/lib/surface/call.cc","file_line":1066,"grpc_message":"Transaction was aborted.","grpc_status":10***
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/google-cloud-spanner-2.8.0/lib/google/cloud/spanner/results.rb:314:in `rescue in from_enum'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/google-cloud-spanner-2.8.0/lib/google/cloud/spanner/results.rb:305:in `from_enum'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/google-cloud-spanner-2.8.0/lib/google/cloud/spanner/results.rb:332:in `execute_query'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/google-cloud-spanner-2.8.0/lib/google/cloud/spanner/session.rb:331:in `execute_query'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/lib/activerecord_spanner_adapter/connection.rb:203:in `execute_query'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/lib/activerecord_spanner_adapter/base.rb:207:in `_execute_version_check'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/lib/activerecord_spanner_adapter/base.rb:166:in `_delete_row'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/lib/activerecord_spanner_adapter/base.rb:160:in `destroy_row'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/activerecord-6.0.3.7/lib/active_record/persistence.rb:535:in `destroy'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/activerecord-6.0.3.7/lib/active_record/callbacks.rb:309:in `block in destroy'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/activesupport-6.0.3.7/lib/active_support/callbacks.rb:135:in `run_callbacks'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/activesupport-6.0.3.7/lib/active_support/callbacks.rb:825:in `_run_destroy_callbacks'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/activerecord-6.0.3.7/lib/active_record/callbacks.rb:309:in `destroy'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/activerecord-6.0.3.7/lib/active_record/transactions.rb:310:in `block in destroy'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/activerecord-6.0.3.7/lib/active_record/transactions.rb:375:in `block in with_transaction_returning_status'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/activerecord-6.0.3.7/lib/active_record/connection_adapters/abstract/database_statements.rb:278:in `transaction'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/lib/active_record/connection_adapters/spanner/database_statements.rb:111:in `transaction'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/activerecord-6.0.3.7/lib/active_record/transactions.rb:212:in `transaction'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/activerecord-6.0.3.7/lib/active_record/transactions.rb:366:in `with_transaction_returning_status'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/activerecord-6.0.3.7/lib/active_record/transactions.rb:310:in `destroy'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/lib/activerecord_spanner_adapter/base.rb:83:in `block in destroy'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/activerecord-6.0.3.7/lib/active_record/connection_adapters/abstract/database_statements.rb:280:in `block in transaction'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/activerecord-6.0.3.7/lib/active_record/connection_adapters/abstract/transaction.rb:280:in `block in within_new_transaction'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/activesupport-6.0.3.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 levels) in synchronize'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/activesupport-6.0.3.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/activesupport-6.0.3.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/activesupport-6.0.3.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/activesupport-6.0.3.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/activerecord-6.0.3.7/lib/active_record/connection_adapters/abstract/transaction.rb:278:in `within_new_transaction'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/activerecord-6.0.3.7/lib/active_record/connection_adapters/abstract/database_statements.rb:280:in `transaction'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/lib/active_record/connection_adapters/spanner/database_statements.rb:116:in `transaction'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/activerecord-6.0.3.7/lib/active_record/transactions.rb:212:in `transaction'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/activerecord-6.0.3.7/lib/active_record/transactions.rb:306:in `transaction'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/lib/activerecord_spanner_adapter/base.rb:82:in `destroy'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/activerecord-6.0.3.7/lib/active_record/relation.rb:544:in `each'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/activerecord-6.0.3.7/lib/active_record/relation.rb:544:in `destroy_all'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/vendor/bundle/ruby/3.0.0/gems/activerecord-6.0.3.7/lib/active_record/querying.rb:21:in `destroy_all'
    /home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/acceptance/cases/interleaved_associations/has_many_associations_using_interleaved_test.rb:36:in `teardown'

  2) Failure:
ActiveRecord::Associations::HasManyUsingInterleavedTest#test_create_and_delete_nested_associated_records [/home/runner/work/ruby-spanner-activerecord/ruby-spanner-activerecord/acceptance/cases/interleaved_associations/has_many_associations_using_interleaved_test.rb:138]:
Expected: 6
  Actual: 8

Spanner now supporting table creation without primary key

• Update schema statement
• Update and add test cases

As each connection has its own session, the BeginTransaction calls could be inlined with the first statement of a transaction, instead of executing a separate BeginTransaction RPC for each new transaction. This should reduce the number of round trips by one for most transactions.

• where
• select
• delete, delete_all
• destroy, destroy_all
• update, update_all
• join
• select

Implement the following batching support (already supported by the Java Connection API):

START BATCH DDL;

-- Execute multiple DDL statements. These are automatically buffered in the connection.
CREATE TABLE Foo ...
CREATE TABLE Bar ...
CREATE INDEX Idx_Foo_ ...

-- This will execute all the buffered DDL statements
RUN BATCH

This feature can be used by applications in migrations and other DDL scripts to batch DDL statements together for better performance.

Sub step of #63

Currently we don't have any meaningful presubmits that run when attempting to merge PRs. It would be nice if we could use GitHub Actions to run the unit and acceptance tests.

I would like to use Cloud Spanner Emulator to develop this gem and to develop Rails application using gem. Although, there are limitations and differences, the emulator does not cost money 💸 and the separated environments are suited for local development.

https://cloud.google.com/spanner/docs/emulator

Then, I've found out that the current implementation of this library does not support emulator.
This is because Google::Cloud.spanner is used instead of Google::Cloud::Spanner.new. The former method pass the limited portion of parameters to the latter.

If we directly call Google::Cloud::Spanner.new(config), we can pass the emulator_host parameter in config, which enable developers to use Cloud Spanner Emulator.

https://github.com/googleapis/google-cloud-ruby/blob/06c73b3b4b09e5f4dded27b61662fe110fea57bb/google-cloud-spanner/lib/google/cloud/spanner.rb#L91-L101

Although, this breaks config[:project], config[:credentials] compatibility, I think keeping consistency with Google::Cloud::Spanner.new reduces maintenance cost.
Of course, If required, I can write backward-compatible code.

If the above strategy is O.K., I am willing to work on this issue.

• google-cloud-spanner : 2.2.0

The ActiveRecord adapter allows a user to specify an optimizer hint for setting a stale read mode. It is however not possible to set a 'normal' query hint through the same method. This should also be added to the adapter.

• Fix timestamp casting on set and after save the record.

1. Correct typos
2. Bring README up to date.

Related: b/186672491