Extending ActiveRecord for csv export... a better way?

[Adam Boyle]

I accidentally posted this to the RoR forum; my apologies to anyone who
receives this twice.

As a new Ruby user (JRuby, actually), I've picked up this language with
a very specific purpose: to export data from a JDBC database into flat
files. I've figured out how to get the data out of the DB and into a
file (using ActiveRecord-JDBC), but it requires a bit too much
configuration. My first stab implementation requires me to define an
activerecord class for each table I want to export, and to define a
method all_columns in each class
that lays out the columns in the correct order. I've also setup a
module (CSVable) that extends ActiveRecord so I can call the export
function directly on each class. Here is the code:
-----------------------------------------
# Module definition
module CSVable
extend ActiveRecord
class Base < ActiveRecord::Base
def self.export_table_to_csv(delimiter = ",", options = nil)
File.open("#{self.table_name}.dat",
File::WRONLY|File::TRUNC|File::CREAT) do |file|
self.findall, options).each do |trow|
file.puts trow.all_columns.join(delimiter)
end
end
end
end
end

# Class definitions
class USR_USER_ACCOUNT < CSVable::Base
set_table_name "USR.USER_ACCOUNT"
def all_columns
[ self.UID, self.USER_NAME, self.USER_INIT, self.ACTIVE_FLAG,
self.LAST_MODFD_TS ]
end
end

class GRP_GRP < CSVable::Base
set_table_name "GRP.GRP"
def all_columns
[ self.PARTY_ROLE_ASSIGN_PID, self.GRP_NBR_ID,
self.LAST_MODIFIED_TIMESTAMP ]
end
end
...

# Usage for the module
USR_USER_ACCOUNT.export_table_to_csv("|")
-----------------------------------------

The major problem with this approach is that I have to setup all the
column info for each of the 36 tables. Is there an easier way to get an
array of the columns while still retaining the original order?

I've tried using the attributes method, but since it returns a hash, the
columns are not in order. My dabbles with the columns method have not
been successful either. Any ideas?

I also have a feeling there is a much better way to implement the export
function...

Any help or suggestions are most appreciated!

-Adam

 

[yermej]

I accidentally posted this to the RoR forum; my apologies to anyone who
receives this twice.

As a new Ruby user (JRuby, actually), I've picked up this language with
a very specific purpose: to export data from a JDBC database into flat
files. I've figured out how to get the data out of the DB and into a
file (using ActiveRecord-JDBC), but it requires a bit too much
configuration. My first stab implementation requires me to define an
activerecord class for each table I want to export, and to define a
method all_columns in each class
that lays out the columns in the correct order. I've also setup a
module (CSVable) that extends ActiveRecord so I can call the export
function directly on each class. Here is the code:
-----------------------------------------
# Module definition
module CSVable
extend ActiveRecord
class Base < ActiveRecord::Base
def self.export_table_to_csv(delimiter = ",", options = nil)
File.open("#{self.table_name}.dat",
File::WRONLY|File::TRUNC|File::CREAT) do |file|
self.findall, options).each do |trow|
file.puts trow.all_columns.join(delimiter)
end
end
end
end
end

# Class definitions
class USR_USER_ACCOUNT < CSVable::Base
set_table_name "USR.USER_ACCOUNT"
def all_columns
[ self.UID, self.USER_NAME, self.USER_INIT, self.ACTIVE_FLAG,
self.LAST_MODFD_TS ]
end
end

class GRP_GRP < CSVable::Base
set_table_name "GRP.GRP"
def all_columns
[ self.PARTY_ROLE_ASSIGN_PID, self.GRP_NBR_ID,
self.LAST_MODIFIED_TIMESTAMP ]
end
end
..

# Usage for the module
USR_USER_ACCOUNT.export_table_to_csv("|")
-----------------------------------------

The major problem with this approach is that I have to setup all the
column info for each of the 36 tables. Is there an easier way to get an
array of the columns while still retaining the original order?

I've tried using the attributes method, but since it returns a hash, the
columns are not in order. My dabbles with the columns method have not
been successful either. Any ideas?

I also have a feeling there is a much better way to implement the export
function...

Any help or suggestions are most appreciated!

-Adam

If you're always doing (essentially) "select *", won't Ruby's dbi
work? I don't know if it's guaranteed, but the columns always seem to
be returned in the order they are defined in the database.

 

[Adam Boyle]

If you're always doing (essentially) "select *", won't Ruby's dbi
work? I don't know if it's guaranteed, but the columns always seem to
be returned in the order they are defined in the database.

Hmm... unfortunately I'm using JRuby and ActiveRecord-JDBC because my
database is only accessible via JDBC (I'm sure there are other ways to
access a Caché database, but not many), so I can't use DBI, unless
someone has developed access to Caché with DBI!

I'll look into it anyway!

 

[Daniel Sheppard]

As a new Ruby user (JRuby, actually), I've picked up this=20

language with
a very specific purpose: to export data from a JDBC database into flat
files. I've figured out how to get the data out of the DB and into a
file (using ActiveRecord-JDBC), but it requires a bit too much
configuration. My first stab implementation requires me to define an
activerecord class for each table I want to export, and to define a
method all_columns in each class
that lays out the columns in the correct order. I've also setup a
module (CSVable) that extends ActiveRecord so I can call the export
function directly on each class. Here is the code:

require 'fastercsv'

def to_csv(records, filename=3Dnil, columns=3Dnil)
filename ||=3D "#{records.first.class.table_name}.dat"
columns ||=3D records.first.class.columns.map {|x| x.name }
FasterCSV.open(filename, 'w') do |csv|
records.each do |record|
csv << columns.map {|column| record.attributes[column] }
end
end
end

class UsrUserAccount < ActiveRecord::Base
set_table_name "USR.USER_ACCOUNT"
end

to_csv(UsrUserAccount.findall))

 

[yermej]

Hmm... unfortunately I'm using JRuby and ActiveRecord-JDBC because my
database is only accessible via JDBC (I'm sure there are other ways to
access a Caché database, but not many), so I can't use DBI, unless
someone has developed access to Caché with DBI!

I'll look into it anyway!

I see. I don't know much about either JDBC or Caché, but this might
help:

http://ola-bini.blogspot.com/2007/09/ruby-dbi-and-jdbc.html

 

[briefcase.speakers]

require 'fastercsv'

def to_csv(records, filename=nil, columns=nil)
filename ||= "#{records.first.class.table_name}.dat"
columns ||= records.first.class.columns.map {|x| x.name }
FasterCSV.open(filename, 'w') do |csv|
records.each do |record|
csv << columns.map {|column| record.attributes[column] }
end
end
end

Daniel, thank you for your reply and effort. I tried this function out
with no avail. For some reason the output file is always empty (I have
confirmed the table is not empty). The file has the right number of
lines, but no data. I have come to the same end with this code:

#within the class inheriting ActiveRecord::Base
def self.export_table_to_csv(delimiter = ",", options = nil)
file = File.open("#{self.table_name}.dat",
File::WRONLY|File::TRUNC|File::CREAT)
rec = self.findall, options)
for row in rec do
line = columns.inject([]) { |arr, col| arr << row[col]; arr}
file.puts(line.join("|"))
end
end

I am beginning to suspect that there is a problem with the columns logic
in AR as related to the AR-JDBC extension (or perhaps specifically with
the driver I'm using), but that is purely speculation on my part. My
Rubyism is not advanced enough to know how to debug that, but I'll look
at the code for AR-JDBC and see if anything jumps out at me.

The link from (e-mail address removed) looks promising for setting up JRuby DBI
with JDBC. I'm going to try it out if I am unable to resolve this using
AR-JDBC soon!

Thank you both for all your help!

 

[briefcase.speakers]

I see. I don't know much about either JDBC or Cach�, but this might
help:

http://ola-bini.blogspot.com/2007/09/ruby-dbi-and-jdbc.html

I think the DBI solution suggested is going to work. Once I have some
clean code to play with, I'll post my solution. Thanks again.

 

[briefcase.speakers]

http://ola-bini.blogspot.com/2007/09/ruby-dbi-and-jdbc.html

After following the instructions found at the URL above, I was able to
finally do what I needed! Thanks for all your help! My full
implementation follows:

#####################################################################
# Export_ODS.rb
# jdbc drivers must be located in the CLASSPATH or in $JRUBY_HOME/lib
#####################################################################
require 'rubygems'
require 'dbi'

# Load DB configuration - yml file in same directory
def connect()
cfg = YAML::load(File.open('dbinfo.yml'))
DBI.connect('DBI:' + cfg["url"], cfg["username"], cfg["password"],
'driver'=>cfg["driver"])
end

# Connect to the database
dbh = connect

# Define the delimiter used in the CSV output files
delim = '|'

export_tables = File.open('schema_table_list.dat', 'r').readlines.each
do |line|
# build an array from the schema/table file in the form schema.table
line.strip!.gsub!(delim, '.')
end

# for each table in the list...
export_tables.each do |table|
# open a file named after the table and...
File.open("#{table}.dat", File::WRONLY|File::TRUNC|File::CREAT) do
|file|
# write out each row from the table to the aptly named file.
dbh.select_all("select * from #{table}").each do |row|
file.puts row.join(delim)
end
end
end

# End Export.rb


#####################################################################
# dbinfo.yml
#####################################################################
driver: com.intersys.jdbc.CacheDriver
url: jdbc:Cache://cache-server:56773/cachedb
username: _system
password: mypass

#####################################################################
# schema_table_list.dat
# Currently generated from Oracle, may write a ruby script to pull it
# out of the Cache side.
#####################################################################
ENROLL|ENROLLMENT
GRP|GRP
MEMBER|MEMBER
MEMBER|MEMBER_DX
ORGROLE|EMPLOYERGROUP
...