Hi,

2009/8/7 Derek Smith <derekbellnersmith / yahoo.com>:
> steve wrote:
>> Derek Smith wrote:
>>>   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="tester.sq3"
>> FileUtils::rm(fn) if File.exist?(fn) # start with a clean slate
>> db=Amalgalite::Database.new(fn)
>> db.execute("create table test ( colone, coltwo, colthree, colfour )")
>> db.commit
>> insert_sql="insert into test values (?,?,?,?)"
>> stmt=db.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=1;
>
> mailog = File.read("/home/derek/Desktop/maillog")
> mailog2 = "/home/derek/Desktop/maillog"
> runlog = "/usr/local/vrep/logs/mail_log_miner.log"
> db =
> 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|
>  if mlog.flock(File::LOCK_SH) == 0
>    #mlog.each do |line|
>      insert_sql = "insert into maillog values '#{mailog}'"
>      stmt=db.prepare(insert_sql)
>    #end ### END DO ###
>  else
>    string = '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 ###
>
>
> 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...120d20ddd5a4616169c5d0b9f4b9cdb97d6465e929690c7561159010ad410590e405ad2951adad29c0ad40ed75f004c07175d971042d152581...
> (Amalgalite::SQLi 'Jul 14 21:09:39 test postfix/smtp[39630]: 926B34608F:
> to=<holveyj / us.panasoni.com>, relay=none, delay=38352,
> delays=38322/0.02/30/0, dsn=4.4.1, status=deferred (connect to
> us.panasoni.com[216.8.179.24]:25: Operation timed out)
> '": syntax error
> from
> /home/derek/.gem/ruby/1.8/gems/amalgalite-0.10.1/lib/amalgalite/statement.rb:33:in
> `send'
> from
> /home/derek/.gem/ruby/1.8/gems/amalgalite-0.10.1/lib/amalgalite/statement.rb:33:in
> `initialize'
> from
> /home/derek/.gem/ruby/1.8/gems/amalgalite-0.10.1/lib/amalgalite/database.rb:264:in
> `new'
> from
> /home/derek/.gem/ruby/1.8/gems/amalgalite-0.10.1/lib/amalgalite/database.rb:264:in
> `prepare'
> from mail_log_miner.rb:22
> from mail_log_miner.rb:18:in `open'
> from 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. This 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=1

mailog  = File.read("/home/derek/Desktop/maillog")
mailog2 = "/home/derek/Desktop/maillog"
runlog  = "/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 NULL \
 )")
db.commit

insert_sql = "insert into maillog values (?,?,?,?)"
stmt = db.prepare(insert_sql)
File.open(mailog2, 'r+') do |mlog|
   if mlog.flock(File::LOCK_SH) == 0
       mlog.each do |line|
          stmt.execute(*line.chomp.scan(/^(.{15})\s+(\S+)\s+(\S+)\s+(.+)$/).first)
          db.commit
       end ### END DO ###
   else
       string = '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