Rails for just querying the tables

Hi friends,

I have an application build on java and I was considering writing a

small application on Jruby on rails to manage the java application. I am
interested in just writing the rails application to query the tables and
build some reports (Queries will only be read mode and not update
queries). I have not intention to update the tables. Can i write this
application in rails considering that the tables are not following the
rails conventions. some tables does not have a primary key also…

Can i stil go with rails for writing this application?

I have written several large report CLI systems that use JRuby for
querying multiple massive tables (140+ million rows left joined) with
no issues.
The trick is to create a rails app, then create scripts that leverage
the rails eco system.
I am using the “script” folder but you can use anything of course.

For example, I have scripts that have this at the top:

#!/usr/bin/env jruby

require ‘date’
require ‘active_record’


Load models


require ‘app/models/legacy_db1/media’
require ‘app/models/logging/track_data’
require ‘app/models/legacy_db2/tracks_label_x’
require ‘app/models/legacy_db3/demographics’
require ‘app/models/legacy_db4/users’


Load helpers


require ‘script/helpers’
require ‘script/caching’

Which use a database.yml like this:


Connection to legacy_db1


legacy_db1:
adapter: jdbc
encoding: utf8
username: someuser
password: somepass
driver: com.mysql.jdbc.Driver
url: jdbc:mysql://172.16.30.3:3306/legacy_db1
reconnect: false
pool: 5
socket: /tmp/mysql.sock


Connection to legacy_db2


legacy_db2:
adapter: jdbc
encoding: utf8
username: someuser
password: somepass
driver: com.mysql.jdbc.Driver
url: jdbc:mysql://172.16.30.4:3306/legacy_db2
reconnect: false
pool: 5
socket: /var/run/mysqld-something/mysqld.sock

and so on.
The models are like this:

class Users < ActiveRecord::Base
self.table_name = ‘users’
db = YAML::load(File.read(‘config/database.yml’))[‘legacy_db1’]
establish_connection(db)
attr_accessible :username, :password, :status, :primary_email,
:terms_accepted,
:contact_allowed, :third_party_allowed, :registered_at,
:validated_at, :closed_at,
:created_at, :updated_at
end

The attr_accessible is purely for me and not required per se.

I then have nasty sql code like this:


Build list of user_ids that are from somedomain.com


ignored_users = []
Users.where(“primary_email like ‘[email protected]’”).each { |user|
ignored_users << user.id }


Now do the run

REAL sql would be multiple joins and what not, but simplified here

for brevity


sql = %{
  SELECT    *
  FROM      tracks
  WHERE     some_column='#{some_value}'
  AND       territory='#{territory}'
  AND       CAST(created_at AS DATE) BETWEEN '#{report_from}' AND 

‘#{report_to}’
AND platform=‘XXX’
AND media_type IN (‘YYY’)
ORDER BY created_at, user_id
}
puts “-> SQL #{sql}”
rows = Tracks.connection.execute(sql)
# Drop all rows where user is from ignored.com
rows.delete_if { |row| ignored_users.include? row[‘user_id’] }
# And continue
fmt_row_count = sprintf(’%07d’, rows.size)
row_number = 1
rows.each do |row|
# do stuff
puts " -> #{sprintf(’%07d’,row_number)} of #{fmt_row_count}
#{row[‘created_at’]} #{some_field} #{another_field}
row_number += 1
end

I then run it using:

time jruby -S script/somescript.rb

I’ve somewhat over simplified the example for corporate IP sake, but the
principal should be clear.

Due to heap issues on the legacy systems I sometimes have to add
“-J-Xmx1000m” (or higher) after jruby but before -S to get it to run
reasonably.

Does that help?

Kimbo

On 24/11/2012, at 7:09 PM, Pradeep Kumar wrote:

Can i stil go with rails for writing this application?


Posted via http://www.ruby-forum.com/.


To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

Ms Kimberley Scott
Senior Software Engineer
http://www.randomactsofsentience.com/
http://www.facebook.com/kimberley.scott.14

Tim U. wrote in post #1086198:

If all you are doing is querying the database you may want to consider
using sequel and bypass rails altogether. http://sequel.rubyforge.org/
This will save you lots of RAM and also run faster.

Also here is a neat substitute for this construct

Users.where(“primary_email like ‘[email protected]’”).each { |user|
ignored_users << user.id }

You can do this instead.

ignored_users = Users.where(“primary_email like
[email protected]’”).pluck(:id)

On Sun, Nov 25, 2012 at 12:47 AM, Kimberley Scott

HI, I need to run with a web application MVC. Also I need to integrate
with Java modules which is external dependencies. For this reason, I am
planning to go with Rails.

If all you are doing is querying the database you may want to consider
using sequel and bypass rails altogether. http://sequel.rubyforge.org/
This will save you lots of RAM and also run faster.

Also here is a neat substitute for this construct

Users.where(“primary_email like ‘[email protected]’”).each { |user|
ignored_users << user.id }

You can do this instead.

ignored_users = Users.where(“primary_email like
[email protected]’”).pluck(:id)

On Sun, Nov 25, 2012 at 12:47 AM, Kimberley Scott