Hi,

2009/8/7 Derek Smith <derekbellnersmith / yahoo.com>:
> steve wrote:
>> Derek Smith wrote:
>>> =C2=A0 =C2=A0 insert line from mailfile into rows
>>> done
>>>
>>> close file
>>> close database
>>>
>>>
>>> Am I far off?
>>> Please advise with tips maybe and or actual code!
>>> Thank you
>>
>> # Load all required gems
>> require "rubygems"
>> require "Amalgalite"
>>
>> fn=3D"tester.sq3"
>> FileUtils::rm(fn) if File.exist?(fn) =C2=A0# start with a clean slate
>> db=3DAmalgalite::Database.new(fn)
>> db.execute("create table test ( colone, coltwo, colthree, colfour )")
>> db.commit
>> insert_sql=3D"insert into test values (?,?,?,?)"
>> stmt=3Ddb.prepare(insert_sql)
>> stmt.execute(*%w{one two three four})
>> stmt.execute(*%w(five six seven eight))
>> db.commit
>>
>> Hope this helps
>>
>> Steve.
>
> It did help, but I am having issues still.
>
>
> #!/usr/bin/env ruby
>
> require 'rubygems';
> require 'amalgalite';
> require 'date';
> $VERBOSE=3D1;
>
> mailog =C2=A0=3D File.read("/home/derek/Desktop/maillog")
> mailog2 =3D "/home/derek/Desktop/maillog"
> runlog =C2=A0=3D "/usr/local/vrep/logs/mail_log_miner.log"
> db =3D
> Amalgalite::Database.new("/usr/local/vrep/repo/db/development.sqlite3")
>
> db.execute("create table maillog ( month datetime DEFAULT NULL NULL, \
> day integer DEFAULT NULL NULL, time datetime DEFAULT NULL NULL, host \
> varchar(25) DEFAULT NULL NULL, \
> daemon varchar(25) DEFAULT NULL NULL, mailmsgs varchar(200) DEFAULT NULL
> NULL \ )")
> db.commit
>
> File.open(mailog2, 'r+') do |mlog|
> =C2=A0 =C2=A0if mlog.flock(File::LOCK_SH) =3D=3D 0
> =C2=A0 =C2=A0 =C2=A0 =C2=A0#mlog.each do |line|
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0insert_sql =3D "insert into mail=
log values '#{mailog}'"
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0stmt=3Ddb.prepare(insert_sql)
> =C2=A0 =C2=A0 =C2=A0 =C2=A0#end ### END DO ###
> =C2=A0 =C2=A0else
> =C2=A0 =C2=A0 =C2=A0 =C2=A0string =3D 'LOCK_SH was not obtained on /var/l=
og/maillog!'
> =C2=A0 =C2=A0 =C2=A0 =C2=A0File.open(runlog, 'a') { |rlog| rlog.puts Date=
.today.to_s,
> string }
> =C2=A0 =C2=A0end ### END IF ###
>
> =C2=A0 =C2=A0mlog.flock(File::LOCK_UN)
> end ### END DO mailog ###
>
>
> No matter how many lines the file is, I get this error.
>
> __ERROR__
> ruby mail_log_miner.rb
> /home/derek/.gem/ruby/1.8/gems/amalgalite-0.10.1/lib/amalgalite/statement=
.rb:33:in
> `prepare': Failure to prepare statement insert into maillog values 'Jul
> 14 21:09:09 test postfix/smtp[39626]: A186A46072: host
> mx.dca.untd.com[64.136.44.37] refused to talk to me: 550 Access
> denied...120d20ddd5a4616169c5d0b9f4b9cdb97d6465e929690c7561159010ad410590=
e405ad2951adad29c0ad40ed75f004c07175d971042d152581...
> (Amalgalite::SQLi 'Jul 14 21:09:39 test postfix/smtp[39630]: 926B34608F:
> to=3D<holveyj / us.panasoni.com>, relay=3Dnone, delay=3D38352,
> delays=3D38322/0.02/30/0, dsn=3D4.4.1, status=3Ddeferred (connect to
> us.panasoni.com[216.8.179.24]:25: Operation timed out)
> '": syntax error
> =C2=A0from
> /home/derek/.gem/ruby/1.8/gems/amalgalite-0.10.1/lib/amalgalite/statement=
.rb:33:in
> `send'
> =C2=A0from
> /home/derek/.gem/ruby/1.8/gems/amalgalite-0.10.1/lib/amalgalite/statement=
.rb:33:in
> `initialize'
> =C2=A0from
> /home/derek/.gem/ruby/1.8/gems/amalgalite-0.10.1/lib/amalgalite/database.=
rb:264:in
> `new'
> =C2=A0from
> /home/derek/.gem/ruby/1.8/gems/amalgalite-0.10.1/lib/amalgalite/database.=
rb:264:in
> `prepare'
> =C2=A0from mail_log_miner.rb:22
> =C2=A0from mail_log_miner.rb:18:in `open'
> =C2=A0from mail_log_miner.rb:18
> te3::Error)
>
> Also, when I File.read it sucks it into memory all at once and comes out
> as one line. =C2=A0This is probably not good for large files so will you
> recommend a better alternative such as .each do.... or is this OK?
>
> Each line in the file begins with the Month abbreviated such as "Jul"
> Thank you

Here is a working version for you:

require 'rubygems'
require 'amalgalite'
require 'date'
$VERBOSE=3D1

mailog  =3D File.read("/home/derek/Desktop/maillog")
mailog2 =3D "/home/derek/Desktop/maillog"
runlog  =3D "/usr/local/vrep/logs/mail_log_miner.log"

db.execute("create table maillog ( time datetime DEFAULT NULL NULL, \
host varchar(25) DEFAULT NULL NULL, \
daemon varchar(25) DEFAULT NULL NULL, mailmsgs varchar(200) DEFAULT NULL NU=
LL \
 )")
db.commit

insert_sql =3D "insert into maillog values (?,?,?,?)"
stmt =3D db.prepare(insert_sql)
File.open(mailog2, 'r+') do |mlog|
   if mlog.flock(File::LOCK_SH) =3D=3D 0
       mlog.each do |line|
          stmt.execute(*line.chomp.scan(/^(.{15})\s+(\S+)\s+(\S+)\s+(.+)$/)=
.first)
          db.commit
       end ### END DO ###
   else
       string =3D 'LOCK_SH was not obtained on /var/log/maillog!'
       File.open(runlog, 'a') { |rlog| rlog.puts Date.today.to_s,string }
   end ### END IF ###

   mlog.flock(File::LOCK_UN)
end ### END DO mailog ###

db.execute("select * from maillog") do |row|
p row
end


Regards,

Park Heesob