SQLite and ruby

[Une Bévue]

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 ?

 

[Sharon Rosner]

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::Modelpeople)
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/ruby-sequel/

The API is here:

http://sequel.rubyforge.org/

best
Sharon

 

[Une Bévue]

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:atabase.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]

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/ruby-sequel/

The API is here:

http://sequel.rubyforge.org/

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]

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::Modelpeople)
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::Modeldevicephonebook)
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]

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