EzDevInfo.com

Sequel in conjunction with ActiveRecord any gotchas?

I'm considering using Sequel for some of my hairier SQL that I find too hard to craft in Active Record.

Are there any things I need to be aware of when using Sequel and ActiveRecord on the same project? (Besides the obvious ones like no AR validations in sequel etc...)

Heroku Problem During Database Pull of Rails App: Mysql::Error MySQL server has gone away

Attempting to pull my database from Heroku gives an error partway through the process (below).

Using: Snow Leopard; heroku-1.8.2; taps-0.2.26; rails-2.3.5; mysql-5.1.42. Database is smallish, as you can see from the error message.

Heroku tech support says it's a problem on my system, but offers nothing in the way of how to solve it.

I've seen the issue reported before - for example here. How can I get around this problem?

The error:

$ heroku db:pull
Auto-detected local database: mysql://[...]@localhost/[...]?encoding=utf8
Receiving schema
Receiving data
17 tables, 9,609 records
[...]
/Library/Ruby/Gems/1.8/gems/sequel-3.0.0/lib/sequel/adapters/mysql.rb:166:in `query': Mysql::Error MySQL server has gone away (Sequel::DatabaseError)
    from /Library/Ruby/Gems/1.8/gems/sequel-3.0.0/lib/sequel/adapters/mysql.rb:166:in `_execute'
    from /Library/Ruby/Gems/1.8/gems/sequel-3.0.0/lib/sequel/adapters/mysql.rb:125:in `execute'
    from /Library/Ruby/Gems/1.8/gems/sequel-3.0.0/lib/sequel/connection_pool.rb:101:in `hold'
    from /Library/Ruby/Gems/1.8/gems/sequel-3.0.0/lib/sequel/database.rb:461:in `synchronize'
    from /Library/Ruby/Gems/1.8/gems/sequel-3.0.0/lib/sequel/adapters/mysql.rb:125:in `execute'
    from /Library/Ruby/Gems/1.8/gems/sequel-3.0.0/lib/sequel/database.rb:296:in `execute_dui'
    from /Library/Ruby/Gems/1.8/gems/sequel-3.0.0/lib/sequel/dataset.rb:276:in `execute_dui'
    from /Library/Ruby/Gems/1.8/gems/sequel-3.0.0/lib/sequel/adapters/mysql.rb:365:in `execute_dui'
    from /Library/Ruby/Gems/1.8/gems/sequel-3.0.0/lib/sequel/dataset/convenience.rb:126:in `import'
    from /Library/Ruby/Gems/1.8/gems/sequel-3.0.0/lib/sequel/dataset/convenience.rb:126:in `each'
    from /Library/Ruby/Gems/1.8/gems/sequel-3.0.0/lib/sequel/dataset/convenience.rb:126:in `import'
    from /Library/Ruby/Gems/1.8/gems/sequel-3.0.0/lib/sequel/adapters/mysql.rb:144:in `transaction'
    from /Library/Ruby/Gems/1.8/gems/sequel-3.0.0/lib/sequel/connection_pool.rb:108:in `hold'
    from /Library/Ruby/Gems/1.8/gems/sequel-3.0.0/lib/sequel/database.rb:461:in `synchronize'
    from /Library/Ruby/Gems/1.8/gems/sequel-3.0.0/lib/sequel/adapters/mysql.rb:138:in `transaction'
    from /Library/Ruby/Gems/1.8/gems/sequel-3.0.0/lib/sequel/dataset/convenience.rb:126:in `import'
    from /Library/Ruby/Gems/1.8/gems/taps-0.2.26/lib/taps/client_session.rb:211:in `cmd_receive_data'
    from /Library/Ruby/Gems/1.8/gems/taps-0.2.26/lib/taps/client_session.rb:203:in `loop'
    from /Library/Ruby/Gems/1.8/gems/taps-0.2.26/lib/taps/client_session.rb:203:in `cmd_receive_data'
    from /Library/Ruby/Gems/1.8/gems/taps-0.2.26/lib/taps/client_session.rb:196:in `each'
    from /Library/Ruby/Gems/1.8/gems/taps-0.2.26/lib/taps/client_session.rb:196:in `cmd_receive_data'
    from /Library/Ruby/Gems/1.8/gems/taps-0.2.26/lib/taps/client_session.rb:175:in `cmd_receive'
    from /Library/Ruby/Gems/1.8/gems/heroku-1.8.2/bin/../lib/heroku/commands/db.rb:17:in `pull'
    from /Library/Ruby/Gems/1.8/gems/heroku-1.8.2/bin/../lib/heroku/commands/db.rb:119:in `taps_client'
    from /Library/Ruby/Gems/1.8/gems/taps-0.2.26/lib/taps/client_session.rb:21:in `start'
    from /Library/Ruby/Gems/1.8/gems/heroku-1.8.2/bin/../lib/heroku/commands/db.rb:115:in `taps_client'
    from /Library/Ruby/Gems/1.8/gems/heroku-1.8.2/bin/../lib/heroku/commands/db.rb:16:in `pull'
    from /Library/Ruby/Gems/1.8/gems/heroku-1.8.2/bin/../lib/heroku/command.rb:45:in `send'
    from /Library/Ruby/Gems/1.8/gems/heroku-1.8.2/bin/../lib/heroku/command.rb:45:in `run_internal'
    from /Library/Ruby/Gems/1.8/gems/heroku-1.8.2/bin/../lib/heroku/command.rb:17:in `run'
    from /Library/Ruby/Gems/1.8/gems/heroku-1.8.2/bin/heroku:14
    from /usr/bin/heroku:19:in `load'
    from /usr/bin/heroku:19

How to write this better? Ruby Sequel chaining OR

In SQL it should look like this:

SELECT * FROM `categories_description_old` WHERE ((`categories_description` = '') OR (`categories_name` = '') OR (`categories_heading_title` = ''))

My (ugly) solution:

conditions = [:categories_name, :categories_heading_title, :categories_description]
b = table_categories_description_old.filter(conditions.pop => "")
conditions.each do |m|
 b = b.or(m => "")
end

Is there a better solution to chain the or conditions?

How to round an average to 2 decimal places in PostgreSQL?

I am using PostgreSQL via the Ruby gem 'sequel'.

I'm trying to round to two decimal places.

Here's my code:

SELECT ROUND(AVG(some_column),2)    
FROM table

I get the following error:

PG::Error: ERROR:  function round(double precision, integer) does 
not exist (Sequel::DatabaseError)

I get no error when I run the following code:

SELECT ROUND(AVG(some_column))
FROM table

Does anyone know what I am doing wrong?

sequel never returns utf-8, just ascii-8bit

There is this mysql database I'm trying to connect to. DataMapper fetches everything nicely in UTF-8 but Sequel always returns strings in ASCII-8bit which produces errors with .to_json.

I have tried several things in order to get it to work.

Encoding.default_external = Encoding::UTF_8  
Encoding.default_internal = Encoding::UTF_8  
DB.run 'set names utf8'  
Sequel.mysql 'db', (...), :encoding => 'utf-8'  

I have gems: mysql (2.9.0) (tried without), mysql2 (0.3.11) and sequel (3.42.0)

The only thing that works is manually forcing the encoding on every string which is MUCH less than ideal.

When to use an ORM (Sequel, Datamapper, AR, etc.) vs. pure SQL for querying

A colleague of mine is currently designing SQL queries like the one below to produce reports, which are displayed in excel files through an external data query. At present, only reporting processes on the DB are required (no CRUD operations).

I am trying to convince him that it would be better to use a ruby ORM in order to be able to display the data in a rails/sinatra app.

Despite the obvious advantages in displaying the data, what advantages are there for him in learning to use an ORM like Sequel or Datamapper?

The SQL queries he is writing are clearly quite complex, and being relatively new to SQL, he often complains that it is very time-consuming and confusing. Is it possible to write extremely complex queries with an ORM? and if so, which is the most suitable(I have heard Sequel is good for legacy dbs)? and what are the advantages of learning ruby and using an ORM versus sticking with plain SQL, in making complex database queries?

Encoding::UndefinedConversionError

I keep getting an "Encoding::UndefinedConversionError - "\xC2" from ASCII-8BIT to UTF-8" every time I try to convert a hash to JSON string. I tryed with [.encode | .force_encoding](["UTF-8" | "ASCII-8BIT" ]), chaining .encode with .force_encoding, backwards, switching parameters, whatever, nothing seemed to change so I caught the error like this:

begin
  menu.to_json
rescue Encoding::UndefinedConversionError
  puts $!.error_char.dump
  p $!.error_char.encoding
end

Where menu is a sequel's dataset.to_hash with content from a MySQL DB, utf8_general_ci encoding and returned this:

"\xC2"

<#Encoding:ASCII-8BIT>

The encoding never changs, no matter what .encode/.force_encoding I use. I've even tried to replace the string .gsub!(/\\xC2/) but nothing happens.

Any help?

Are there any Ruby ORMs which use cursors or smart fetch?

I'm looking for a Ruby ORM to replace ActiveRecord. I've been looking at Sequel and DataMapper. They look pretty good however none of them seems to do the basic: not loading everything in memory when you don't need it.

I mean I've tried the following (or equivalent) on ActiveRecord and Sequel on table with lots of rows:

 posts.each { |p| puts p }

Both of them go crazy on memory. They seem to load everything in memory rather than fetching stuff when needed. I used the find_in_batches in ActiveRecord, but it's not an acceptable solution:

1. ActiveRecord is not an acceptable solution because we had too many problems with it.

2. Why should my code be aware of a paging mechanism? I'm happy to configure somewhere the size of the page but that's it. With find_in_batches you need to do something like:

   post.find_in_batches { |batch| batch.each { |p| puts p } }

But that should be transparent.

So is there somewhere a reliable Ruby ORM which does the fetch properly?

Update:

As Sergio mentioned, in Rails 3 you can use find_each which exactly what I want. However as ActiveRecord is not an option, except if someone can really convince me to use it, the questions are:

1. Which ORMs support the equivalent of find_each?
2. How to do it?
3. Why do we need a find_each, while find should do it, shouldn't it?

How to update or insert on Sequel dataset?

I just started using Sequel in a really small Sinatra app. Since I've got only one DB table, I don't need to use models.

I want to update a record if it exists or insert a new record if it does not. I came up with the following solution:

  rec = $nums.where(:number => n, :type => t)
  if $nums.select(1).where(rec.exists)
    rec.update(:counter => :counter + 1)
  else
    $nums.insert(:number => n, :counter => 1, :type => t)
  end

Where $nums is DB[:numbers] dataset.

I believe that this way isn't the most elegant implementation of "update or insert" behavior.

How should it be done?

Slicing params hash for specific values

Summary

Given a Hash, what is the most efficient way to create a subset Hash based on a list of keys to use?

h1 = { a:1, b:2, c:3 }        # Given a hash...
p foo( h1, :a, :c, :d )       # ...create a method that...
#=> { :a=>1, :c=>3, :d=>nil } # ...returns specified keys...
#=> { :a=>1, :c=>3 }          # ...or perhaps only keys that exist

Details

The Sequel database toolkit allows one to create or update a model instance by passing in a Hash:

foo = Product.create( hash_of_column_values )
foo.update( another_hash )

The Sinatra web framework makes available a Hash named params that includes form variables, querystring parameters and also route matches.

If I create a form holding only fields named the same as the database columns and post it to this route, everything works very conveniently:

post "/create_product" do
  new_product = Product.create params
  redirect "/product/#{new_product.id}"
end

However, this is both fragile and dangerous. It's dangerous because a malicious hacker could post a form with columns not intended to be changed and have them updated. It's fragile because using the same form with this route will not work:

post "/update_product/:foo" do |prod_id|
  if product = Product[prod_id]
    product.update(params)
    #=> <Sequel::Error: method foo= doesn't exist or access is restricted to it>
  end
end

So, for robustness and security I want to be able to write this:

post "/update_product/:foo" do |prod_id|
  if product = Product[prod_id]
    # Only update two specific fields
    product.update(params.slice(:name,:description))
    # The above assumes a Hash (or Sinatra params) monkeypatch
    # I will also accept standalone helper methods that perform the same
  end
end

...instead of the more verbose and non-DRY option:

post "/update_product/:foo" do |prod_id|
  if product = Product[prod_id]
    # Only update two specific fields
    product.update({
      name:params[:name],
      description:params[:description]
    })
  end
end

Update: Benchmarks

Here are the results of benchmarking the (current) implementations:

                    user     system      total        real
sawa2           0.250000   0.000000   0.250000 (  0.269027)
phrogz2         0.280000   0.000000   0.280000 (  0.275027)
sawa1           0.297000   0.000000   0.297000 (  0.293029)
phrogz3         0.296000   0.000000   0.296000 (  0.307031)
phrogz1         0.328000   0.000000   0.328000 (  0.319032)
activesupport   0.639000   0.000000   0.639000 (  0.657066)
mladen          1.716000   0.000000   1.716000 (  1.725172)

The second answer by @sawa is the fastest of all, a hair in front of my tap-based implementation (based on his first answer). Choosing to add the check for has_key? adds very little time, and is still more than twice as fast as ActiveSupport.

Here is the benchmark code:

h1 = Hash[ ('a'..'z').zip(1..26) ]
keys = %w[a z c d g A x]
n = 60000

require 'benchmark'
Benchmark.bmbm do |x|
  %w[ sawa2 phrogz2 sawa1 phrogz3 phrogz1 activesupport mladen ].each do |m|
    x.report(m){ n.times{ h1.send(m,*keys) } }
  end
end

Why use SQL builders? Arel v. Sequel v. T-SQL

I'm trying to understand the benefits of building SQL via an object-oriented builder DSL vs. parameterizing a raw SQL string. After researching/implementing the same query three ways, I notice that the raw SQL is by far the easiest to read. This begs the question, "why jump through a hoop?" Why not just declare and use raw SQL?

Here's what I've come up:

First, I guess it makes the SQL more portable as it could then be utilized by any DB with an adapter. I guess this is the biggie, right? Still, isn't most T-SQL intelligible to most databases?

Second, it provides a query object that can be reused--as the basis for other queries, named-scope chaining, etc.

What's the main return on investment you realize by building your SQL instead of declaring it?

def instances_of_sql(ttype_id) #raw sql
  ttype_id = get(ttype_id).try(:id)
  ti   = get('tmdm:type-instance')
  inst = get('tmdm:instance')
  type = get('tmdm:type')

  self.class.send :sanitize_sql, [%{
    SELECT t.*
    FROM associations a
    JOIN roles type    ON type.association_id = a.id AND type.ttype_id = ?
    JOIN roles inst    ON inst.association_id = a.id AND inst.ttype_id = ?
    JOIN topics t      ON t.id = inst.topic_id
    WHERE a.topic_map_id IN (?)
    AND a.ttype_id    = ?
    AND type.topic_id = ?
  }, type.id, inst.id, self.ids, ti.id, ttype_id]
end

def instances_of_sql(ttype_id) #sequel
  ttype_id = get(ttype_id).try(:id)
  ti = get('tmdm:type-instance')
  ir = get('tmdm:instance')
  tr = get('tmdm:type')

  DB.from(:associations.as(:a)).
    join(:roles.as(:tr), :tr__association_id => :a__id, :tr__ttype_id => tr[:id]).
    join(:roles.as(:ir), :ir__association_id => :a__id, :ir__ttype_id => ir[:id]).
    join(:topics.as(:t), :t__id => :ir__topic_id).
    where(:a__topic_map_id => self.ids).
    where(:a__ttype_id => ti[:id]).
    where(:tr__topic_id => ttype_id).
    select(:t.*).sql
end

def instances_of_sql(ttype_id) #arel
  ttype_id = get(ttype_id).try(:id)
  ti   = get('tmdm:type-instance')
  inst = get('tmdm:instance')
  type = get('tmdm:type')

  #tables
  t    = Topic.arel_table
  a    = Association.arel_table
  tr   = Role.arel_table
  ir   = tr.alias

  a.
    join(tr).on(tr[:association_id].eq(a[:id]),tr[:ttype_id].eq(type[:id])).
    join(ir).on(ir[:association_id].eq(a[:id]),ir[:ttype_id].eq(inst[:id])).
    join(t).on(t[:id].eq(ir[:topic_id])).
    where(a[:topic_map_id].in(self.ids)).
    where(a[:ttype_id].eq(ti[:id])).
    where(tr[:topic_id].eq(ttype_id)).
    project('topics.*').to_sql
end

I totally appreciate named scopes and see how chaining them can be beneficial. I'm not worried about accessing related records via a model. I'm purely talking about building a complex query.

Defining Sequel models before connecting

In my (non-Rails) app, I'm trying to define a Sequel model:

class Foo < Sequel::Model
end

When I run my app, I'm getting the error:

No database associated with Sequel::Model: 
have you called Sequel.connect or Sequel::Model.db= ? (Sequel::Error)

In fact, I have not called connect, because the 'require Foo' is happening before my database code runs.

Of course, I could switch things around so that the require is done after the DB connects, but is there another option? Currently I have all my app's 'require' statements in one file and it would be nice not to have to break that for these model class files.

How do I pull heroku data into a local SQLite3 database? Running into problems

I'm trying to make a local backup of the data from my Rails app, which is deployed to Heroku, and running into problems. I followed the instructions here: http://docs.heroku.com/taps and installed Taps.

I get two types of errors. I created a SQLite db locally and tried pulling data with this command:

(sudo) heroku db:pull sqlite://Users/username/folder/testbackup.db

or

(sudo) heroku db:pull sqlite://username:password@localhost/Users/username/folder/testbackup.db

but either way I get this:

Failed to connect to database: Sequel::DatabaseConnectionError -> SQLite3::CantOpenException: could not open database: unable to open database file

Alternatively, I tried letting Taps auto-detect the development db in my app and rewrite it, though that isn't quite what I wanted. Then I start getting errors like:

/opt/local/lib/ruby/site_ruby/1.8/rubygems.rb:777:in report_activate_error': RubyGem version error: sequel(3.15.0 not ~> 3.13.0) (Gem::LoadError) from /opt/local/lib/ruby/site_ruby/1.8/rubygems.rb:211:inactivate' from /opt/local/lib/ruby/site_ruby/1.8/rubygems.rb:1056:in `gem' from /Library/Ruby/Gems/1.8/gems/taps-0.3.10/bin/schema:4

and eventually

/System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.2.4/lib/sqlite3/errors.rb:62:in `check': SQLite3::SQLException: PRIMARY KEY must be unique (Sequel::DatabaseError)

Thanks tons in advance, it's pretty important that I start backing up this data as soon as possible!

Unable to connect mysql from Sequel gem

When I try to connect to MySQL from Sequel. I am getting these errors:

require 'rubygems'
        require 'sequel'
        DB = Sequel.connect(:adapter => 'mysql', :user => 'root', :host => 'localhost', :database => 'scanty',:password=>'xx')
        DB.tables
    Sequel::DatabaseConnectionError: NameError uninitialized constant Mysql::CLIENT_MULTI_RESULTS
        from /opt/local/lib/ruby/gems/1.8/gems/sequel-3.2.0/lib/sequel/adapters/mysql.rb:98:in `connect'
        from /opt/local/lib/ruby/gems/1.8/gems/sequel-3.2.0/lib/sequel/database.rb:92:in `initialize'
        from /opt/local/lib/ruby/gems/1.8/gems/sequel-3.2.0/lib/sequel/connection_pool.rb:166:in `call'
        from /opt/local/lib/ruby/gems/1.8/gems/sequel-3.2.0/lib/sequel/connection_pool.rb:166:in `make_new'
        from /opt/local/lib/ruby/gems/1.8/gems/sequel-3.2.0/lib/sequel/connection_pool.rb:153:in `available'
        from /opt/local/lib/ruby/gems/1.8/gems/sequel-3.2.0/lib/sequel/connection_pool.rb:144:in `acquire'
        from /opt/local/lib/ruby/gems/1.8/gems/sequel-3.2.0/lib/sequel/connection_pool.rb:143:in `synchronize'
        from /opt/local/lib/ruby/gems/1.8/gems/sequel-3.2.0/lib/sequel/connection_pool.rb:143:in `acquire'
        from /opt/local/lib/ruby/gems/1.8/gems/sequel-3.2.0/lib/sequel/connection_pool.rb:105:in `hold'
        from /opt/local/lib/ruby/gems/1.8/gems/sequel-3.2.0/lib/sequel/database.rb:471:in `synchronize'
        from /opt/local/lib/ruby/gems/1.8/gems/sequel-3.2.0/lib/sequel/adapters/mysql.rb:128:in `execute'
        from /opt/local/lib/ruby/gems/1.8/gems/sequel-3.2.0/lib/sequel/dataset.rb:314:in `execute'
        from /opt/local/lib/ruby/gems/1.8/gems/sequel-3.2.0/lib/sequel/adapters/mysql.rb:342:in `execute'
        from /opt/local/lib/ruby/gems/1.8/gems/sequel-3.2.0/lib/sequel/adapters/mysql.rb:298:in `fetch_rows'
        from /opt/local/lib/ruby/gems/1.8/gems/sequel-3.2.0/lib/sequel/dataset.rb:185:in `each'
        from /opt/local/lib/ruby/gems/1.8/gems/sequel-3.2.0/lib/sequel/dataset/convenience.rb:156:in `map'
        from /opt/local/lib/ruby/gems/1.8/gems/sequel-3.2.0/lib/sequel/dataset/convenience.rb:156:in `map'
        from /opt/local/lib/ruby/gems/1.8/gems/sequel-3.2.0/lib/sequel/adapters/shared/mysql.rb:60:in `tables'
        from (irb):6irb(main):007:0> Sequel::DatabaseConnectionError: NameErro

'Sequel::Error: id is a restricted primary key' when creating record using Sequel

I have a model based on Sequel and Oracle adapter:

class Operation < Sequel::Model(DB[:operations]) 
end

If I try to create a record using Oracle's sequence.nextval as primary key:

Operation.create(
  :id=>:nextval.qualify(:Soperations), 
  :payee_id=>12345,
  :type=>"operation",
  :origin=>"user-12345",
  :parameters=>{}.to_s
)

I've got error: Sequel::Error: id is a restricted primary key. What's the correct way to create a record in such case or "map" Oracle's sequence to id column? Or maybe, I have to use unrestrict_primary_key?