connect to MySQL Database

[Jochen Kaechelin]

Hello list,

I run Mac OS X Tiger, ruby 1.8.4 (from sources) and MySQL 5.0.27
(from sources).

I wan't to create a script which is called by pure-ftpd's
uploadscript functionality - clamav scans each uploaded file - and
store the scan results directly into a MySQL database.

What's the recommended way to do this?

Thanx

 

[Kashia Buch]

Hi,

I run Mac OS X Tiger, ruby 1.8.4 (from sources) and MySQL 5.0.27 (from= =

sources).

I wan't to create a script which is called by pure-ftpd's uploadscript= =

functionality - clamav scans each uploaded file - and store the scan =

results directly into a MySQL database.

What's the recommended way to do this?

I would do it along these lines (untested):


require 'og'
# http://blade.nagaokaut.ac.jp/cgi-bin/scat.rb/ruby/ruby-talk/165163
require 'clamscanner'

class ClamavScanResult
is Timestamped
property ath, String
property :result, Result
end

Og.startstore =3D> :mysql, :name =3D> 'clamavdb', :user =3D> 'testuser=
',
assword =3D> 'private', :classes =3D> [ClamavScanResult])

# scan this file
file =3D ARGV[0] # first argument is the file/path ?
clamav =3D ClamScanner.new
res =3D clamav.scan( file )

ClamavScanResult.create_withpath =3D> file, :result =3D> res)

__END__

Of course, the 'recommended' way could way differ from my own opinion
on how this should be done.

This has the downside that using Og instead of a pure mysql lib that
the (startup) speed is a little worse. The plus side is, you get to
switch to postgresql very fast (if you ever get that idea ) and that
the script is quite small and easy to read.

Enjoy.

-- =

Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

 

[Jochen Kaechelin]

Hi,

I run Mac OS X Tiger, ruby 1.8.4 (from sources) and MySQL 5.0.27 (from
sources).

I wan't to create a script which is called by pure-ftpd's uploadscript
functionality - clamav scans each uploaded file - and store the scan
results directly into a MySQL database.

What's the recommended way to do this?

I would do it along these lines (untested):


require 'og'
# http://blade.nagaokaut.ac.jp/cgi-bin/scat.rb/ruby/ruby-talk/165163
require 'clamscanner'

class ClamavScanResult
is Timestamped
property ath, String
property :result, Result
end

Og.startstore => :mysql, :name => 'clamavdb', :user => 'testuser',
assword => 'private', :classes => [ClamavScanResult])

# scan this file
file = ARGV[0] # first argument is the file/path ?
clamav = ClamScanner.new
res = clamav.scan( file )

ClamavScanResult.create_withpath => file, :result => res)

__END__

Great, I will give it a try. Seems to be exactly what I need.

But what do I need to connect to a MySQL database?

<?

mysql_connect(......
mysql_select_database(.....
mysql_query(-------

....display results in a html-table...
?>


Has ruby no native MySQL support. Do I need to install extra
modules, libs ??

 

[Kashia Buch]

Hi,

^^ that should've said String there, sorry.

Og.startstore =3D> :mysql, :name =3D> 'clamavdb', :user =3D> 'test= user',
assword =3D> 'private', :classes =3D> [ClamavScanResult])

Great, I will give it a try. Seems to be exactly what I need.

But what do I need to connect to a MySQL database?

<?

mysql_connect(......
mysql_select_database(.....
mysql_query(-------

....display results in a html-table...
?>


Has ruby no native MySQL support. Do I need to install extra modules, = =

libs ??

For my 'idea' you only need 'Og' (gem install og) and it's dependencies.=

It comes with a built in mysql lib, so for your case not even a mysql
lib is required.
Og just needs the username user), password password) and the table
name) to connect to any of its supported database systems.

My idea also includes, that you have _nothing_ do with the 'database',
which is handles by Og (silently). To use a database is merely a way
to structure data, which Og does quite nicely.


I see you're using erb/eruby/erubis/mod_ruby (unsure, never used either)=
?
Or are you using Rails (which I also never used and comment on either)?

This part would then just 'display' what is in the database. What I
posted you, would basically be the creating the data in the database.

On to how to display it using erb:

# hypothetical display.erb
<?
require 'og'
Og.create_schema =3D false
class ClamavScanResult
is Timestamped
property ath, String
property :result, String
end
Og.startstore =3D> :mysql, :name =3D> 'clamavdb', :user =3D> 'testuser=
',
assword =3D> 'private', :classes =3D> [ClamavScanResult])

# scans is just a array with ClamavScanResult instances
scans =3D ClamavScanResult.all
?>
<table>
<tr><th>File</th><th>Status</th></tr>
<? scans.each do |scan| ?>
<tr><td><?=3D scan.path ?></td><td><?=3D scan.result ?></td></tr>
<? end ?>
</table>

__END__

Well, or something along those lines.

Note that this is probably _not_ compatible with rails.

I can elaborate on I myself would do the display part if you want
me to.

Hope that helps.

-- =

Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

 

[David Vallner]

--------------enigA8C52C322627B8B3674EC453
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: quoted-printable

My idea also includes, that you have _nothing_ do with the 'database',
which is handles by Og (silently). To use a database is merely a way
to structure data, which Og does quite nicely.
=20

I feel a great disturbance in the Force, as if millions of DBAs voices
cried out in terror and then were silenced.

But in the context, I'll admit it's a Good Enough (tm) first approach.
SQL is one of the few programming languages I'm likely to routinely use
as part of my job that's actually interesting, so I'm biased in favour
of overrating databases

David Vallner


--------------enigA8C52C322627B8B3674EC453
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (MingW32)

iD8DBQFFfxbVy6MhrS8astoRApJKAJ9w9gfMFrLzCg+xJRiP1SzQ1BqywwCeMJY8
xnCUSoPcYVRc7yhLExYp5+w=
=Kj8i
-----END PGP SIGNATURE-----

--------------enigA8C52C322627B8B3674EC453--

 

[Kashia Buch]

Hi,

I feel a great disturbance in the Force, as if millions of DBAs voices
cried out in terror and then were silenced.

I wonder who silenced them.

But in the context, I'll admit it's a Good Enough (tm) first approach.
SQL is one of the few programming languages I'm likely to routinely use
as part of my job that's actually interesting, so I'm biased in favour
of overrating databases

Well... I'm not sure if a database is the right way to 'log' virus
occurances, but I try to be helpful.
But seeing as this data is used to present (as a table) to someone, this
actually has it's use. Not that I would want looking at 1000 entries of
'no virus' found... But that's another story.

As for databases... well, I didn't want to hurt any feelings. ;D
I feel similar about SQL, but if it's a simple task like the one in the
original context... A simple task calls for a simple answer imho.

Have a nice day, everyone

 

[Jochen Kaechelin]

Well... I'm not sure if a database is the right way to 'log' virus

occurances, but I try to be helpful.

My customer is running a ftp-server with about 6.000 accounts. we
need to log all activities concerning the ftp-server. uploads,
downloads, virus occurences etc.... there are about 10.000 new
entries in the database each day.

they will be used for statistics.

each day my customer will get an email with some points:

Total logins/unique users
Total uploads
MB transfered
found viruses

....

So I think it's useful to use a database to store alle the
information and run the statistic-queries.

My knowledga about databases is very poor.

Any other suggestions?

I was not sure how to handle this with ruby/ror because I'am (I
was!!!) a php man and it's the first project I want to do with ruby.
These are my first lines od ruby code - and I want to make it good.


Thanx

 

[Kashia Buch]

Hi,

My customer is running a ftp-server with about 6.000 accounts. we need
to log all activities concerning the ftp-server. uploads, downloads,
virus occurences etc.... there are about 10.000 new entries in the
database each day.

they will be used for statistics.

each day my customer will get an email with some points:

Total logins/unique users
Total uploads
MB transfered
found viruses

....

So I think it's useful to use a database to store alle the information
and run the statistic-queries.

Yes, I see your application better now.

My knowledge about databases is very poor.

Any other suggestions?

Yes, I surely hope that you get some other opinions than mine!

I was not sure how to handle this with ruby/ror because I'am (I was!!!)
a php man and it's the first project I want to do with ruby.
These are my first lines od ruby code - and I want to make it good.

So, seeing you're a php coder, and not used to ruby, I will try again.

You first have to chose between 2 options, do you want a real framwework
like Ruby on Rails, Nitro, IOWA or something similar, or stay with the
approach from php (erb/eruby/erubis).

My approach would best work within a framework (Og is in fact part of
Nitro).

I researched a bit and coded a little. As it's just scrap code and
it's a bit longish, I put it into a paste service. Feel free to copy
small parts here if something is unclear.

## get data from pure-uploadscript

http://pastie.caboo.se/27389

When called like `UploadedFile.parse_pure_uploadscript` it returns a
UploadedFile instance with your data.

## setting up the database

http://pastie.caboo.se/27391

This configures Og and creates a table to save your data. It can be
extended by adding more `propery :symbol, Type` entries.

## setting up the clamav 'parser'

http://pastie.caboo.se/27393

I extended the original a bit, allowing for a local clamavscan.

## Putting the parts together

http://pastie.caboo.se/27394

This, when run, expects the filename as the first argument, uses
the uploaded_file 'parser' to get all the data from the environment,
feeds that to the clamav scanner and saves the result by using Og.

Must be a bit confusing for a newcomer, it's not all 'newbie' code,
but I hope that helps you a little to see how this _could_ be done.

Anyway, maybe somebody else can jump in and propose another solution.
(What I didn't get into here, is on how to present the data. Sorry.
Tell me which road you want to go (framework or plain erb/eruby),
and I'll try to write something up for that.)

I apologize to everyone who sees this message in the future when the
pastie links are all dead.... just send me an email and I'll try to
dig it up again.

Enjoy