Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Ruby > connect to MySQL Database

Reply
Thread Tools

connect to MySQL Database

 
 
Jochen Kaechelin
Guest
Posts: n/a
 
      12-12-2006
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

--
Jochen Kaechelin

 
Reply With Quote
 
 
 
 
Kashia Buch
Guest
Posts: n/a
 
      12-12-2006
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...by-talk/165163
require 'clamscanner'

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

Og.start(:store =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_with(ath =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/

 
Reply With Quote
 
 
 
 
Jochen Kaechelin
Guest
Posts: n/a
 
      12-12-2006
Kashia Buch schrieb:
> 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...by-talk/165163
> require 'clamscanner'
>
> class ClamavScanResult
> is Timestamped
> property ath, String
> property :result, Result
> end
>
> Og.start(:store => :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_with(ath => 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 ??

--
Jochen

 
Reply With Quote
 
Kashia Buch
Guest
Posts: n/a
 
      12-12-2006
Hi,

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


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

>> Og.start(:store =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 (assword) 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.start(:store =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/

 
Reply With Quote
 
David Vallner
Guest
Posts: n/a
 
      12-12-2006
--------------enigA8C52C322627B8B3674EC453
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: quoted-printable

Kashia Buch wrote:
> 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+xJRiP1S zQ1BqywwCeMJY8
xnCUSoPcYVRc7yhLExYp5+w=
=Kj8i
-----END PGP SIGNATURE-----

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

 
Reply With Quote
 
Kashia Buch
Guest
Posts: n/a
 
      12-13-2006
Hi,

>> 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 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

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

 
Reply With Quote
 
Jochen Kaechelin
Guest
Posts: n/a
 
      12-13-2006
> 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

 
Reply With Quote
 
Kashia Buch
Guest
Posts: n/a
 
      12-13-2006
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

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

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Database Database Database scott93727@gmail.com Computer Information 0 09-27-2012 02:43 AM
DataBase DataBase DataBase DataBase scott93727@gmail.com Computer Information 0 09-26-2012 09:40 AM
using mysql client to read a file to create a table in a database in a mysql server julian Ruby 8 04-06-2006 10:29 AM
jdbc connect to mysql database Marcus Krieger Java 2 12-11-2004 02:39 AM
Seen this message? Can't connect to local MySQL server through socket '/tmp/mysql.sock' Allen Marshall Perl Misc 1 01-24-2004 10:55 PM



Advertisments