ruby database interface

[LMHmedchem]

Hello to the forum,

I am starting a long process of developing a database application bases on SQLite and ruby. I am using ruby because the interface will eventually be browser based and ruby on rails or Sinatra seems like a good way to get the working. I have looked at some tutorials, but they are either too simple or too complicated. My first step is to do a simple import of a tab delimited text file to create a SQLlite database. There would be one database and several tables, so I need to know how to map each column in the file to table/column. It could be just one table for now if that is simpler.

Can some point me in the right direction to get me going? I have worked through allot of this tutorial,
http://zetcode.com/db/sqliteruby/

This code,

#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.open "test.db"
    db.execute "CREATE TABLE IF NOT EXISTS Cars(Id INTEGER PRIMARY KEY, 
        Name TEXT, Price INT)"
    db.execute "INSERT INTO Cars VALUES(1,'Audi',52642)"
    db.execute "INSERT INTO Cars VALUES(2,'Mercedes',57127)"
    db.execute "INSERT INTO Cars VALUES(3,'Skoda',9000)"
    db.execute "INSERT INTO Cars VALUES(4,'Volvo',29000)"
    db.execute "INSERT INTO Cars VALUES(5,'Bentley',350000)"
    db.execute "INSERT INTO Cars VALUES(6,'Citroen',21000)"
    db.execute "INSERT INTO Cars VALUES(7,'Hummer',41400)"
    db.execute "INSERT INTO Cars VALUES(8,'Volkswagen',21600)"
    
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
    
ensure
    db.close if db
end

inserts the data, but a hard coded comma separated list is a long way from a delimited text file. I think it's fine for the mapping to be hard coded for now, but this is something that would eventually be entered from a browser interface.

I can post some sample files if that would help.

Thanks for the advice,

LMHmedchem

[LMHmedchem]

At the moment, I am trying to get the ruby-sqlite working under windows cygwin. The libsqlite3-ruby package does not appear to be in the current cygwin package manager. I have tried installing it with gem, but I don't appear to have gem, or there is something wrong with the configuration.

gem install sqlite3-ruby

gives,
-bash: gem: command not found

I presume this is a cygwin issue, but I need to get the sqlite3-ruby package working before I can get any further, or give up on a ruby interface and try some other interpreter. Can anyone give advice on how to get this working? I don't have the windows ruby package installed, just the cygwin version.

LMHmedchem

[LMHmedchem]

I was able to get SQLite Ruby installed and working under cygwin using the following method.

1. Install the following cygwin packages,
Ruby > ruby (1.8.7-p358-2)
Database > sqlite3
Database > libsqlite3_0
Database > libsqlite3-devel

2. download the ruby gems src from,
http://rubyforge.org/frs/?group_id=1...ems-1.8.24.tgz
save the tar file anywhere in the cygwin tree (I used /home)

3. unzip and unpack the tar,
gzip -d rubygems-1.8.24.tgz
tar xvf rubygems-1.8.24.tar

4. cd to /rubygems-1.8.24 and run,
ruby setup.rb install

5. update gems, (this didn't do anything for me)
gem update --system

6. install sqlite3-ruby with gems,
gem install sqlite3-ruby

This appeared to work, but I need to do some additional testing.

Now I need to get back to importing data from a text file. I will try to post something more in a day or so.

LMHmedchem