connect to MySQL Database

Discussion in 'Ruby' started by Jochen Kaechelin, Dec 12, 2006.

  1. 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
    Jochen Kaechelin, Dec 12, 2006
    #1
    1. Advertising

  2. Jochen Kaechelin

    Kashia Buch Guest

    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 :path, String
    property :result, Result
    end

    Og.start:)store =3D> :mysql, :name =3D> 'clamavdb', :user =3D> 'testuser=
    ',
    :password =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:)path =3D> file, :result =3D> res)

    __END__

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

    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 :p) and that
    the script is quite small and easy to read.

    Enjoy.

    -- =

    Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
    Kashia Buch, Dec 12, 2006
    #2
    1. Advertising

  3. 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/scat.rb/ruby/ruby-talk/165163
    > require 'clamscanner'
    >
    > class ClamavScanResult
    > is Timestamped
    > property :path, String
    > property :result, Result
    > end
    >
    > Og.start:)store => :mysql, :name => 'clamavdb', :user => 'testuser',
    > :password => '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:)path => 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
    Jochen Kaechelin, Dec 12, 2006
    #3
  4. Jochen Kaechelin

    Kashia Buch Guest

    Hi,

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


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

    >> Og.start:)store =3D> :mysql, :name =3D> 'clamavdb', :user =3D> 'test=

    user',
    >> :password =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 :path, String
    property :result, String
    end
    Og.start:)store =3D> :mysql, :name =3D> 'clamavdb', :user =3D> 'testuser=
    ',
    :password =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/
    Kashia Buch, Dec 12, 2006
    #4
  5. --------------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+xJRiP1SzQ1BqywwCeMJY8
    xnCUSoPcYVRc7yhLExYp5+w=
    =Kj8i
    -----END PGP SIGNATURE-----

    --------------enigA8C52C322627B8B3674EC453--
    David Vallner, Dec 12, 2006
    #5
  6. Jochen Kaechelin

    Kashia Buch Guest

    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. :p
    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. :p

    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. :p

    Have a nice day, everyone :)

    --
    Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
    Kashia Buch, Dec 13, 2006
    #6
  7. > Well... I'm not sure if a database is the right way to 'log' virus
    > occurances, but I try to be helpful. :p



    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
    Jochen Kaechelin, Dec 13, 2006
    #7
  8. Jochen Kaechelin

    Kashia Buch Guest

    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/
    Kashia Buch, Dec 13, 2006
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Marcus Krieger

    jdbc connect to mysql database

    Marcus Krieger, Dec 10, 2004, in forum: Java
    Replies:
    2
    Views:
    13,187
    Ian T
    Dec 11, 2004
  2. com
    Replies:
    1
    Views:
    8,731
    Bruce Lee
    Apr 1, 2006
  3. pythonbrian
    Replies:
    1
    Views:
    377
    Steve Holden
    Feb 7, 2008
  4. Allen Marshall
    Replies:
    1
    Views:
    180
    Gunnar Hjalmarsson
    Jan 24, 2004
  5. Replies:
    2
    Views:
    267
Loading...

Share This Page