Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

Perl to monitor a table in database

by pid (Monk)
on Oct 26, 2009 at 14:29 UTC ( #803278=perlquestion: print w/ replies, xml ) Need Help??
pid has asked for the wisdom of the Perl Monks concerning the following question:

Hi fellow monks!

I am praying to know the answer to the following question:

Is there any way to monitor the write operation (errr...like, putting a new row in it) to a table in a certain database (can be runs on MySQL, SQL Server, more likely to be Oracle) in real time?

What I want to gather is the number of records added into the table in a given time period. And, display the number, or even draw a "what's goin' on" graph.

Thanks in advance!

Comment on Perl to monitor a table in database
Re: Perl to monitor a table in database
by moritz (Cardinal) on Oct 26, 2009 at 14:39 UTC
    Maybe you want a Database trigger that writes this information into another table, which you then can query periodically from Perl?
    Perl 6 - links to (nearly) everything that is Perl 6.
Re: Perl to monitor a table in database
by marto (Chancellor) on Oct 26, 2009 at 14:42 UTC

    I'm pretty sure Toad does this, by periodically updating the Toad system tables in a database with statistics about tables/schemas. Perhaps you could just replicate this functionality using DBI, and have a nice AJAX front end (using jQuery and flot maybe?) talking to a CGI back end for 'real time' (real time may not be wise) updates.

    Martin

Re: Perl to monitor a table in database
by keszler (Priest) on Oct 26, 2009 at 14:44 UTC
    Something like this?
    while (1) { my ($ct1) = $dbh->selectrow_array('select count(*) from TABLE'); sleep 60; my ($ct2) = $dbh->selectrow_array('select count(*) from TABLE'); print ($ct2 - $ct1)," rows added in the last minute\n"; }

    Update: While I fully agree that a database trigger would be far more efficient and functional than a simple rowcount query, my suggestion was based on two assumptions:

    1. The question was asked on PerlMonks, so presumably a Perl solution was the desired response.
    2. The desire to monitor a database does not imply the desire or permission to alter it. Adding triggers or otherwise altering the database created and used by many commercial applications is a very bad idea: it can cause the application to break and technical support to be denied or limited to "remove your changes".

    The original poster - an Initiate here - seems to have fully understood my suggestion, or rather the implications of it. I'm surprised, shocked even, at the non-Perl-based responses by people who have been here far longer.

      This will give you the difference in record counts, not the number of records added. For example:

      t + 1: add 5 rows t + 10: add 5 rows t + 20: del 20 rows t + 30: add 5 rows t + 50: add 10 rows

      Your code would give an answer of 5 if $ct1 was obtained at time t. moritz's trigger answer would give a more accurate answer if you actually need the could count [1] of items added, and not just the difference in the row counts.

      Updates:

      1. Fixed spelling mistakes.

      --MidLifeXis

      OK, fellows, (I thought there might be some modules available...forgive me), as I mentioned earlier, what I need now is a demo (or more exactly, a toy version), it's okay when it displays, so keszler's idea is applicable here. Later this grows to a larger one, I think I should use the triggers.

      Non-Perl-based ideas are welcomed here, too. :-p

      Once again, I'd like to thank everybody for your help, thank you.

Re: Perl to monitor a table in database
by pid (Monk) on Oct 26, 2009 at 15:19 UTC

    I appreciate all of you for your help! I was surprised at the replying speed. :)

    keszler gave me his simple-yet-powerful answer, as the monitoring product is just a demo now, and...it's aimed at a demo. (Sorry for not making it clear).

    It is a serious one being working on, and your helpful info will be with the development. ;)

Re: Perl to monitor a table in database
by mje (Deacon) on Oct 26, 2009 at 15:56 UTC

    If you are using Oracle you can avoid polling the database for changes by writing an insert trigger (or update or delete) and making that issue an alert - see the DBMS_ALERT package. The outside perl script can then just call DBMS_ALERT waitany and it will block for the alerts you registered for.

    As others have pointed out, counting the rows added might not be as straight forward if you delete rows as well.

      Hey mje, thanks for this! And, this table is somehow a 'add-only' one, so I don't have to worry about the deletion.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://803278]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (10)
As of 2014-08-28 01:32 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (255 votes), past polls