Active Record PostgreSQL guide examples.

01_pg_guide_examples.md

Active Record PostgreSQL Examples

These are the examples used in the Active Record PostgreSQL guide. They are executable and verifiable.

pg_array.rb

require 'bundler'
Bundler.setup(:default)

require 'active_record'
require 'logger'

ActiveRecord::Base.establish_connection(adapter: 'postgresql', database: 'rails_pg_guide')
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table(:books, force: true) do |t|
    t.string 'title'
    t.string 'tags', array: true
    t.integer 'ratings', array: true
  end
end

class Book < ActiveRecord::Base
end

Book.create(title: "Brave New World",
            tags: ["fantasy", "fiction"],
            ratings: [4, 5])

p Book.where("'fantasy' = ANY (tags)").any? # => true
p Book.where("tags @> ARRAY[?]::varchar[]", ["fantasy", "fiction"]).any? # => true
p Book.where("array_length(ratings, 1) >= 3").any? # => false

pg_bit.rb

# -*- coding: utf-8 -*-
require 'bundler'
Bundler.setup(:default)

require 'active_record'
require 'logger'

ActiveRecord::Base.establish_connection(adapter: 'postgresql', database: 'rails_pg_guide')
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table :users, force: true do |t|
    t.column :settings, "bit(8)"
  end
end

class User < ActiveRecord::Base
end

User.create settings: "01010011"
user = User.first
p user.settings # => "(Paris,Champs-Élysées)"
user.settings = "0xAF"
p user.settings # => 10101111
user.save!

pg_composite.rb

# -*- coding: utf-8 -*-
require 'bundler'
Bundler.setup(:default)

require 'active_record'
require 'logger'

ActiveRecord::Base.establish_connection(adapter: 'postgresql', database: 'rails_pg_guide')
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  execute 'DROP TABLE IF EXISTS contacts'
  execute 'DROP TYPE IF EXISTS full_address'
  execute <<-SQL
   CREATE TYPE full_address AS
   (
     city VARCHAR(90),
     street VARCHAR(90)
   );
  SQL
  create_table(:contacts) do |t|
    t.column :address, :full_address
  end
end

class Contact < ActiveRecord::Base
end

Contact.create address: "(Paris,Champs-Élysées)"
contact = Contact.first
p contact.address # => "(Paris,Champs-Élysées)"
contact.address = "(Paris,Rue Basse)"
contact.save!
p contact.address

pg_enum.rb

# -*- coding: utf-8 -*-
require 'bundler'
Bundler.setup(:default)

require 'active_record'
require 'logger'

# This connection will do for database-independent bug reports.
ActiveRecord::Base.establish_connection(adapter: 'postgresql', database: 'rails_pg_guide')
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  execute 'DROP TABLE IF EXISTS articles'
  execute 'DROP TYPE IF EXISTS article_status'
  execute <<-SQL
    CREATE TYPE article_status AS ENUM ('draft', 'published');
  SQL
  create_table :articles do |t|
    t.column :status, :article_status
  end
end

class Article < ActiveRecord::Base
end

Article.create status: "draft"
article = Article.first
p article.status # => "draft"

article.status = "published"
article.save!
p article.status

pg_full_text_search.rb

require 'bundler'
Bundler.setup(:default)

require 'active_record'
require 'logger'

# This connection will do for database-independent bug reports.
ActiveRecord::Base.establish_connection(adapter: 'postgresql', database: 'rails_pg_guide')
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table(:documents, force: true) do |t|
    t.string 'title'
    t.string 'body'
  end

  execute "CREATE INDEX documents_idx ON documents USING gin(to_tsvector('english', title || ' ' || body));"
end

class Document < ActiveRecord::Base
end

Document.create(title: "Cats and Dogs", body: "are nice!")

p Document.where("to_tsvector('english', title || ' ' || body) @@ to_tsquery(?)",
                 "cat & dog").to_a

pg_hstore.rb

require 'bundler'
Bundler.setup(:default)

require 'active_record'
require 'logger'

ActiveRecord::Base.establish_connection(adapter: 'postgresql', database: 'rails_pg_guide')
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table(:profiles, force: true) do |t|
    t.hstore 'settings'
  end
end

class Profile < ActiveRecord::Base
end

Profile.create(settings: { "color" => "blue", "resolution" => "800x600" })

profile = Profile.first
profile.settings # => {"color"=>"blue", "resolution"=>"800x600"}

profile.settings = {"color" => "yellow", "resulution" => "1280x1024"}
profile.save!

p profile.reload.settings

profile.settings["color"] = "green"
profile.settings_will_change!
profile.save!

p profile.reload.settings

pg_json.rb

require 'bundler'
Bundler.setup(:default)

require 'active_record'
require 'logger'

ActiveRecord::Base.establish_connection(adapter: 'postgresql', database: 'rails_pg_guide')
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table(:notifications, force: true) do |t|
    t.json 'payload'
  end
end

class Notification < ActiveRecord::Base
end

Notification.create(payload: { kind: "user_renamed", change: ["jack", "john"]})

event = Notification.first
p event.payload # => {"kind"=>"user_renamed", "change"=>["jack", "john"]}

# Query based on JSON document
p Notification.where("payload->'kind' = ?", "user_renamed")

pg_range.rb

require 'bundler'
Bundler.setup(:default)

require 'active_record'
require 'logger'

ActiveRecord::Base.establish_connection(adapter: 'postgresql', database: 'rails_pg_guide')
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table(:events, force: true) do |t|
    t.daterange 'duration'
  end
end

class Event < ActiveRecord::Base
end

Event.create(duration: Date.new(2014, 2, 11)..Date.new(2014, 2, 12))

event = Event.first
p event.duration # => Tue, 11 Feb 2014...Thu, 13 Feb 2014

# All Events on a given date
p Event.where("duration @> ?::date", Date.new(2014, 2, 12))

# Working with range bounds
event = Event.
  select("lower(duration) AS starts_at").
  select("upper(duration) AS ends_at").first
p event.starts_at # => Tue, 11 Feb 2014
p event.ends_at # => Thu, 13 Feb 2014

pg_uuid.rb

# -*- coding: utf-8 -*-
require 'bundler'
Bundler.setup(:default)

require 'active_record'
require 'logger'

ActiveRecord::Base.establish_connection(adapter: 'postgresql', database: 'rails_pg_guide')
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  enable_extension 'uuid-ossp' unless extension_enabled?('uuid-ossp')

  create_table(:revisions, force: true) do |t|
    t.column :identifier, :uuid
  end

  create_table :devices, id: :uuid, default: 'uuid_generate_v4()', force: true do |t|
    t.string :kind
  end
end

class Revision < ActiveRecord::Base
end

class Device < ActiveRecord::Base
end

Revision.create identifier: "A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11"
revision = Revision.first
p revision.identifier # => "(Paris,Champs-Élysées)"

device = Device.create
p device.id

pg_view.rb

require 'bundler'
Bundler.setup(:default)

require 'active_record'
require 'logger'

ActiveRecord::Base.establish_connection(adapter: 'postgresql', database: 'rails_pg_guide')
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  execute "DROP VIEW IF EXISTS articles"

  create_table("TBL_ART", id: false, force: true) do |t|
    t.integer "INT_ID"
    t.string 'STR_TITLE'
    t.string 'STR_STAT', default: "draft"
    t.timestamp 'DT_PUBL_AT'
    t.boolean 'BL_ARCH', default: "f"
    t.primary_key "INT_ID"
  end

  execute <<-SQL
CREATE VIEW articles AS
  SELECT "INT_ID" AS id,
         "STR_TITLE" AS title,
         "STR_STAT" AS status,
         "DT_PUBL_AT" AS published_at,
         "BL_ARCH" AS archived
  FROM "TBL_ART"
  WHERE "BL_ARCH" = 'f'
  SQL
end

class Article < ActiveRecord::Base
  self.primary_key = "id"
  def archive!
    update_attribute :archived, true
  end
end


first = Article.create! title: "Winter is coming", status: "published", published_at: 1.year.ago
second = Article.create! title: "Brace yourself", status: "draft", published_at: 1.month.ago

p Article.count
first.archive!
p Article.count