 |
Forums |
Admin Discussion Forums: help Start New Thread
By: Gavin Kistner
 RE: help with array to sqlite3 [ reply ]
2009-03-20 05:03
|
Alright, one final email. This one is tested and fixes a few bugs.
I really wish RubyForge had information on what markup, if any, was allowed in the forums. Or let you edit your posts after the fact. Wrapping this code in a few to see if any of it works:
pre.. @[pre]{{{
require 'rubygems'
require 'sqlite3'
SOURCE_FILE = 'words.txt'
DATABASE_FILE = 'words.db'
db = SQLite3::Database.new( DATABASE_FILE )
make_tables = <<ENDSQL
DROP TABLE parts_of_speech;
CREATE TABLE parts_of_speech (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
);
DROP TABLE words;
CREATE TABLE words (
id INTEGER PRIMARY KEY AUTOINCREMENT,
greek TEXT,
english TEXT,
part_of_speech_id INTEGER,
occurrences INTEGER
);
ENDSQL
PART_ID_BY_NAME = {}
db.execute_batch( make_tables );
db.transaction do
File.foreach( SOURCE_FILE ) do |line|
pieces = line.scan( /"([^"]+)"/ ).flatten
gr, en, part, hits = *pieces
if gr && en && part && hits then
part_id = PART_ID_BY_NAME[ part ]
unless part_id
db.execute( "insert into parts_of_speech (name) values (?)", part )
part_id = db.last_insert_row_id
PART_ID_BY_NAME[ part ] = part_id
end
db.execute(
"insert into words
( greek, english, part_of_speech_id, occurrences )
values (?,?,?,?)",
gr, en, part_id, hits.to_i
)
end
end
end
}}}[/pre]@
|
By: Gavin Kistner
RE: help with array to sqlite3 [ reply ]
2009-03-20 04:43
|
A) Oops, english should be text, not float. The beauty(?) of sqlite is that it doesn't really matter, though.
B) If you want reasonable performance for your 5,000 records, you should wrap all the inserts into a transaction. With Sequel, put:
DB.transaction do
...
end
around the File.foreach block.
BTW, I'm sure that Sequel (http://sequel.rubyforge.org/) is overkill for this simple case, but I happen to use it all the time instead of Active Record (which you'll likely use instead on Rails) and I didn't know off the top of my head what the sqlite3-ruby direct bindings look like.
In retrospect, seems rather rude to have used it at all in this forum. Untested, the code might look something like this:
db = SQLite3::Database.new( "test.db" )
make_tables = <<ENDSQL
CREATE TABLE parts_of_speech (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
);
CREATE TABLE words (
id INTEGER PRIMARY KEY AUTOINCREMENT,
greek TEXT,
english TEXT,
part_of_speech_id INTEGER,
occurrences INTEGER
);
ENDSQL
db.execute_batch( make_tables );
db.transaction do
File.foreach( SOURCE_FILE ) do |line|
pieces = line.scan( /"([^"]+)"/ ).flatten
gr, en, part, hits = *pieces
if gr then
part_id = PART_ID_BY_NAME[ part ]
unless part_id
db.execute( "insert into parts_of_speech (name) values (?)", part )
part_id = db.last_insert_row_id
PART_ID_BY_NAME[ part ] = part_id
end
db.execute( "insert into words values (?,?,?,?)", gr, en, part_id, hits.to_i )
end
end
end
|
By: Gavin Kistner
RE: help with array to sqlite3 [ reply ]
2009-03-20 04:09
|
1) Install SQLite3
2) sudo gem install sqlite3-ruby
3) sudo gem install sequel
Slim2:Desktop phrogz$ cat words.txt
["ἀβαρής", "not burdensome", "adjective", "1"]
["ascii", "easy", "noun", "0"]
["utf", "ftw", "noun", "0"]
["awesome","ruby","adjective","3"]
Slim2:Desktop phrogz$ cat convertor.rb
require 'rubygems'
require 'sequel'
SOURCE_FILE = 'words.txt'
DATABASE_FILE = 'words.db'
# This will create the file if it doesn't exist, or use it if it does
DB = Sequel.sqlite( DATABASE_FILE )
DB.drop_table :words
DB.create_table :words do
primary_key :id
column :greek, :text
column :english, :float
column :part_of_speech, :integer
column :occurrences, :integer
end
DB.drop_table :parts_of_speech
DB.create_table :parts_of_speech do
primary_key :id
column :name, :text
end
PART_ID_BY_NAME = {}
words = DB[:words]
parts = DB[:parts_of_speech]
File.foreach( SOURCE_FILE ) do |line|
pieces = line.scan( /"([^"]+)"/ ).flatten
gr, en, part, hits = *pieces
# This test to skip blank lines in the file
if gr then
part_id = PART_ID_BY_NAME[ part ]
unless part_id
part_id = ( parts << { :name => part } )
PART_ID_BY_NAME[ part ] = part_id
end
words << {
:greek => gr,
:english => en,
:part_of_speech => part_id,
:occurrences => hits.to_i
}
end
end
Slim2:Desktop phrogz$ ruby convertor.rb
Slim2:Desktop phrogz$ sqlite3 words.db
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> .schema
CREATE TABLE `parts_of_speech` (`id` integer PRIMARY KEY AUTOINCREMENT, `name` text);
CREATE TABLE `words` (`id` integer PRIMARY KEY AUTOINCREMENT, `greek` text, `english` float, `part_of_speech` integer, `occurrences` integer);
sqlite> .headers on
sqlite> select * from words;
id|greek|english|part_of_speech|occurrences
1|ἀβαρής|not burdensome|1|1
2|ascii|easy|2|0
3|utf|ftw|2|0
4|awesome|ruby|1|3
sqlite> select * from parts_of_speech;
id|name
1|adjective
2|noun
sqlite> .quit
|
By: k h
RE: help with array to sqlite3 [ reply ]
2009-03-20 00:09
|
Hi Gavin, thanks for your reply.
Each line looks something like this:
["ἀβαρής", "not burdensome", "adjective", "1"] where first item is a Greek word, second item the Greek word's English gloss, third item the term's part in speech, and the fourth item is the number of the times the term appears in the Greek New Testament. I have over 5000+ lines of different words that follow the same format.
There is nothing about this array that is ruby specific, but I am interested in using this to better learn Ruby - I've only read through one introductory guide on ruby so far - as well as saving it as a sqlite3 file, to later use with a rails project that I am learning with. That is, I am both new to ruby, rails, and sqlite3. Thanks for your response!
Ken
|
By: Gavin Kistner
RE: help with array to sqlite3 [ reply ]
2009-03-19 23:22
|
|
Must the lines be interpreted as pure Ruby? What sorts of values are in the array or file? Are you having trouble with the Ruby for parsing the file, or with the DB schema for SQLite, or with the Ruby for creating database rows?
|
By: k h
help with array to sqlite3 [ reply ]
2009-03-19 18:13
|
Hi.
I have a 5000+ lines file where each line is a ruby array with four items. I'm looking for ways to iterate through all the lines, and store them in a sqlite3 file. But I'm not sure if/where the relevant documentation or tutorial is. Any help will be appreciated. Thanks.
Ken
|
|
|
