Asp Forum - SQLite and ruby

unbewusst.sein

10/11/2007 7:33:00 PM

i need to convert three fields of a SQLite db :

lastName, firstName, fullLastName

the conversion is a capitalisation ie :

dupond, jules, dupondjules

needs to become :

Dupond, Jules, DupondJules

i now how to capitalize with ruby but i've never use ruby with SQLite.

what should i install, as a gem for example, in order to play with such
a database ?

--
Une Bévue

5 Answers

Sharon Rosner

10/11/2007 8:24:00 PM

> what should i install, as a gem for example, in order to play with such
> a database ?
>

If you have the sqlite3 libs installed, you can install the sqlite3
gem:

sudo gem install sqlite3-ruby

May I also suggest Sequel for a nice & simple API for 'playing' with
the database:

sudo gem install sequel

Sequel lets you write stuff like this:

require 'sequel/sqlite'

# Open the database
DB = Sequel.sqlite('mydb.sqlite')

# Assuming the table name is 'people'
class Person < Sequel::Model(:people)
def capitalize
p[:lastName].capitalize!
p[:firstName].capitalize!
p[:fullName].capitalize!
save
end
end

Person.each {|p| p.capitalize}

You can find out more about Sequel here:

http://code.google.com/p/ru...

The API is here:

http://sequel.ruby...

best
Sharon

unbewusst.sein

10/11/2007 9:11:00 PM

Une Bévue <unbewusst.sein@weltanschauung.com.invalid> wrote:

>
> i now how to capitalize with ruby but i've never use ruby with SQLite.

After installing sqlite3-ruby by gem i'm trying :

#! /usr/bin/env ruby

require 'sqlite3'

db = SQLite3::Database.new( "backpb.db" )
db.execute( "select distinct UID, lastName, firstName, fullLastName from
devicephonebook" ) do | row |
p row
db.execute( "UPDATE devicephonebook SET lastName =
'#{row[1].capitalize}', firstName = '#{row[2].capitalize}', fullLastName
= '#{row[1].capitalize}#{row[2].capitalize}' WHERE UID = #{row[0]}")
end

where i get the following error :

SQLite3::SQLException: no such function: get_zy_string
method check
in errors.rb at line 94
method initialize
in statement.rb at line 71
method new
in database.rb at line 184
method prepare
in database.rb at line 184
method execute
in database.rb at line 211
at top level
in test.rb at line 8
method execute
in database.rb at line 214
method each
in resultset.rb at line 162
method execute
in database.rb at line 214
method prepare
in database.rb at line 187
method execute
in database.rb at line 211
at top level
in test.rb at line 6
Program exited.

line 6 being db.execute( "select distinct UID, ...")
line 8 being db.execute( "UPDATE devicephonebook SET ...")
--
Une Bévue

unbewusst.sein

10/11/2007 9:17:00 PM

Sharon Rosner <ciconia@gmail.com> wrote:

> If you have the sqlite3 libs installed, you can install the sqlite3
> gem:
>
> sudo gem install sqlite3-ruby

already done, thanks ;-)

>
> May I also suggest Sequel for a nice & simple API for 'playing' with
> the database:
>
> sudo gem install sequel

[...]

>
> You can find out more about Sequel here:
>
> http://code.google.com/p/ru...
>
> The API is here:
>
> http://sequel.ruby...

OK thanks a lot about your advice upon "sequel" i'll look for.

i've already done a first test using sqlite3-ruby only however i got an
error (see my last post) )))

--
Une Bévue

unbewusst.sein

10/11/2007 10:56:00 PM

Sharon Rosner <ciconia@gmail.com> wrote:

> Sequel lets you write stuff like this:
>
> require 'sequel/sqlite'
>
> # Open the database
> DB = Sequel.sqlite('mydb.sqlite')
>
> # Assuming the table name is 'people'
> class Person < Sequel::Model(:people)
> def capitalize
> p[:lastName].capitalize!
> p[:firstName].capitalize!
> p[:fullName].capitalize!
> save
> end
> end

I had to re-arrange your script the following way :

class Person < Sequel::Model(:devicephonebook)
def capitalize
@values[:lastName] = @values[:lastName].name_capitalize
@values[:firstName] = @values[:firstName].name_capitalize
@values[:fullLastName] = @values[:lastName] + @values[:firstName]
p "[ " + @values[:lastName] + ", " + @values[:firstName] + ", " +
@values[:fullLastName] + " ]"
#save
end
end

i can't use String#capitalize! for three reasons :

- 1 - if a name is already capitalize String#capitalize! returns nil ;
- 2 - "aristocratic" people having name like that :

di Girolamo
Pavin de Lafarge
von Beethoven
van Houthen

or composite name :

- 3 - Harley-Davidson

then i wrote :

class String
def name_capitalize
if self.include?("-")
l = self.split("-")
l.each { |ll| ll = ll.capitalize }
return l.join("-")
elsif self.include?(" ")
l = self.split(" ")
l.each { |ll| ll = ll.capitalize if !TRUC.include? ll }
return l.join(" ")
else
return self.capitalize
end
end
end

everything works well until i've uncomented "#save" where i get the same
error as another script not using Sequel :

RuntimeError: no such function: get_zy_string
method hold
in connection_pool.rb at line 68
method execute_insert
in sqlite.rb at line 42
method insert
in sqlite.rb at line 121
method save
in record.rb at line 82
method capitalize
in test-sequel.rb at line 38
at top level
in test-sequel.rb at line 42
method []
in dataset.rb at line 330
method each
in dataset.rb at line 330
method call
in sqlite.rb at line 115
method fetch_rows
in sqlite.rb at line 115
method each
in resultset.rb at line 162
method fetch_rows
in sqlite.rb at line 112
method query
in database.rb at line 278
method execute_select
in sqlite.rb at line 52
method hold
in connection_pool.rb at line 62
method execute_select
in sqlite.rb at line 52
method fetch_rows
in sqlite.rb at line 109
method each
in dataset.rb at line 330
method each
in untitled document at line 1
method send
in model.rb at line 60
method method_missing
in model.rb at line 60
at top level
in test-sequel.rb at line 42
Program exited.

This appeared AFTER the first p in Person.each {|p| p.capitalize } ???

may be better to ask to the Sequel google-list but the prob does exist
in sqlite3-ruby...
--
Une Bévue

unbewusst.sein

10/11/2007 11:13:00 PM

Une Bévue <unbewusst.sein@weltanschauung.com.invalid> wrote:

> class String
> def name_capitalize
> if self.include?("-")
> l = self.split("-")
> l.each { |ll| ll = ll.capitalize }
> return l.join("-")
> elsif self.include?(" ")
> l = self.split(" ")
> l.each { |ll| ll = ll.capitalize if !TRUC.include? ll }
> return l.join(" ")
> else
> return self.capitalize
> end
> end
> end

OOOPPPSSS !!!!!

this has to be read as :

PARTICULES = [ "de", "di", "von", "van" ]

class String
def name_capitalize
if self.include?("-")
l = self.split("-")
lo = []
l.each { |ll| lo << ll.capitalize }
return lo.join("-")
elsif self.include?(" ")
l = self.split(" ")
lo = []
l.each { |ll|
if PARTICULES.include? ll
lo << ll
else
lo << ll.capitalize
end
}
return lo.join(" ")
else
return self.capitalize
end
end
end

--
Une Bévue

comp.lang.ruby

SQLite and ruby

unbewusst.sein

Sharon Rosner

unbewusst.sein

unbewusst.sein

unbewusst.sein

unbewusst.sein

x Login to ForumsZone