TIPS: 如遇兼容性问题,可提交github issue以获取帮助:https://github.com/cockroachdb/cockroach/issues/new

本小节将展示如何使用pg驱动或是ActiveRecord框架进行编程交互。用户需要预先安装、配置CockroachDB。

使用pg驱动

Step 1: 安装ruby的pg驱动

gem install pg

Step 2: 启动节点

cockroach start --insecure --store=hello-1 --host=localhost

Step 3: 创建用户

执行cockroach user命令创建maxroach用户:

cockroach user set maxroach --insecure

Step 4: 创建数据库并授权

root用户启动内置的SQL客户端,创建bank数据库并授权maxroach用户。

cockroach sql --insecure -e 'CREATE DATABASE bank'
cockroach sql --insecure -e 'GRANT ALL ON DATABASE bank TO maxroach'

Step 5: 编写执行Ruby代码

基本操作

Ruby代码:

# Import the driver.
require 'pg'

# Connect to the "bank" database.
conn = PG.connect(
  user: 'maxroach',
  dbname: 'bank',
  host: 'localhost',
  port: 26257,
  #非安全模式
  sslmode: 'disable'
  #安全模式
  #sslmode: 'require',
  #sslrootcert: 'certs/ca.crt',
  #sslkey:'certs/client.maxroach.key',
  #sslcert:'certs/client.maxroach.crt'
)

# Create the "accounts" table.
conn.exec('CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, balance INT)')

# Insert two rows into the "accounts" table.
conn.exec('INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250)')

# Print out the balances.
puts 'Initial balances:'
conn.exec('SELECT id, balance FROM accounts') do |res|
  res.each do |row|
    puts row
  end
end

# Close communication with the database.
conn.close()

执行:

ruby basic-sample.rb

输出:

Initial balances:
{"id"=>"1", "balance"=>"1000"}
{"id"=>"2", "balance"=>"250"}

创建事务(带重试逻辑)

Ruby代码:

# Import the driver.
require 'pg'

# Wrapper for a transaction.
# This automatically re-calls "op" with the open transaction as an argument
# as long as the database server asks for the transaction to be retried.
def run_transaction(conn)
  conn.transaction do |txn|
    txn.exec('SAVEPOINT cockroach_restart')
    while
      begin
        # Attempt the work.
        yield txn

        # If we reach this point, commit.
        txn.exec('RELEASE SAVEPOINT cockroach_restart')
        break
      rescue PG::TRSerializationFailure
        txn.exec('ROLLBACK TO SAVEPOINT cockroach_restart')
      end
    end
  end
end

def transfer_funds(txn, from, to, amount)
  txn.exec_params('SELECT balance FROM accounts WHERE id = $1', [from]) do |res|
    res.each do |row|
      raise 'insufficient funds' if Integer(row['balance']) < amount
    end
  end
  txn.exec_params('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, from])
  txn.exec_params('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, to])
end

# Connect to the "bank" database.
conn = PG.connect(
  user: 'maxroach',
  dbname: 'bank',
  host: 'localhost',
  port: 26257,
  #非安全模式
  sslmode: 'disable'
  #安全模式
  #sslmode: 'require',
  #sslrootcert: 'certs/ca.crt',
  #sslkey:'certs/client.maxroach.key',
  #sslcert:'certs/client.maxroach.crt'
)

run_transaction(conn) do |txn|
  transfer_funds(txn, 1, 2, 100)
end

# Close communication with the database.
conn.close()

执行:

ruby txn-sample.rb

执行查询:

cockroach sql --insecure -e 'SELECT id, balance FROM accounts' --database=bank

+----+---------+
| id | balance |
+----+---------+
|  1 |     900 |
|  2 |     350 |
+----+---------+
(2 rows)

NOTE: 在默认的隔离级别SERIALIZABLE下,因读写冲突导致事务执行失败,需要用户主动地重新提交事务。用户也实现通用的retry函数在事务内部重新执行事务。

使用ActiveRecord框架

NOTE: 更多细节可以查看example-orms项目。

Step 1: 安装ActiveRecord ORM框架

//需要注意Active Record和adapter之间的版本匹配问题
gem install activerecord pg activerecord-cockroachdb-adapter

NOTE: 以上命令可能随着ActiveRecord版本有所差异。 例如:4.2.x版本的ActiveRecord需要配合0.1.x版本的adapter进行使用,而5.1.x版本的ActiveRecord需要配合0.2.x版本的adapter。

Step 2: 启动节点

以非安全模式启动节点:

cockroach start --insecure --store=hello-1 --host=localhost

Step 3: 创建用户

执行cockroach user命令创建maxroach用户:

cockroach user set maxroach --insecure

Step 4: 创建数据库并授权

root用户启动内置的SQL客户端,创建bank数据库并授权maxroach用户。

cockroach sql --insecure -e 'CREATE DATABASE bank'
cockroach sql --insecure -e 'GRANT ALL ON DATABASE bank TO maxroach'

Step 5: 编写执行Ruby代码

基本操作

Ruby代码:

require 'active_record'
require 'activerecord-cockroachdb-adapter'
require 'pg'

# Connect to CockroachDB through ActiveRecord.
# In Rails, this configuration would go in config/database.yml as usual.
ActiveRecord::Base.establish_connection(
  adapter:     'cockroachdb',
  username:    'maxroach',
  database:    'bank',
  host:        'localhost',
  port:        26257,
  #非安全模式
  sslmode:     'disable'
  #安全模式
  #sslmode:     'require',
  #sslrootcert: 'certs/ca.crt',
  #sslkey:      'certs/client.maxroach.key',
  #sslcert:     'certs/client.maxroach.crt'
)

# Define the Account model.
# In Rails, this would go in app/models/ as usual.
class Account < ActiveRecord::Base
  validates :id, presence: true
  validates :balance, presence: true
end

# Define a migration for the accounts table.
# In Rails, this would go in db/migrate/ as usual.
class Schema < ActiveRecord::Migration[5.0]
  def change
    create_table :accounts, force: true do |t|
      t.integer :balance
    end
  end
end

# Run the schema migration by hand.
# In Rails, this would be done via rake db:migrate as usual.
Schema.new.change()

# Create two accounts, inserting two rows into the accounts table.
Account.create(id: 1, balance: 1000)
Account.create(id: 2, balance: 250)

# Retrieve accounts and print out the balances
Account.all.each do |acct|
  puts "#{acct.id} #{acct.balance}"
end

执行:

ruby activerecord-basic-sample.rb

输出:

-- create_table(:accounts, {:force=>true})
-> 0.0361s
1 1000
2 250

执行查询:

cockroach sql --insecure -e 'SELECT id, balance FROM accounts' --database=bank
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |     250 |
+----+---------+
(2 rows)