tomazos has asked for the wisdom of the Perl Monks concerning the following question:

Suppose there is a mysql table that has new records inserted into it on an ongoing basis by someone else's code. You may not modify that code or the table. The table has no unique ID field, nor any sort of timestamp.

You have DBI access to the table and you want to have a subroutine that you have written called whenever a new record is inserted into the table.

The subroutine must be called with the new record's fields as it's arguments.

What is the most efficient way of setting this up?

Replies are listed 'Best First'.
Re: Tailing a database table via DBI
by dws (Chancellor) on Apr 21, 2002 at 19:00 UTC
    You have DBI access to the table and you want to have a subroutine that you have written called whenever a new record is inserted into the table.

    You're facing a couple of immovable objects here.

    First, DBI/DBD is fundamentally request-response. There's no facility for asynchronous notifications.

    Second, MySQL doesn't have stored procedures. You are, however, free to invest time and effort adding them, but it's not a trivial project.

    Third, unless you're able to add a notification layer between the database and clients who are adding data, you're going to have to poll, and efficient polling is going to require either a unique ID field or a timestamp on the table, so that can avoid a full linear scan time you poll.

    Bottom line: If the requirements are non-negotiable, walk away from this one.

[reply]
Re: Tailing a database table via DBI
by TheHobbit (Pilgrim) on Apr 21, 2002 at 17:14 UTC

    Hi,
    I do not think this would be possible. Infact, the DBI module connects to the Mysql server as a client. It is then impossible for the server to signal the client that something has changed. This would even be contrary to the basic philosophy of Client-Server paradigm.

    I do not know for mysql, but in postgresql you can add triggers, and even write them in Perl; then again you have to have access to the database structure...

    Cheers
    Leo TheHobbit
    GED/CS d? s-:++ a+ C++ UL+++ P+++>+++++ E+ W++ N+ o K? !w O? M V PS+++
    PE-- Y+ PPG+ t++ 5? X-- R+ tv+ b+++ DI? D G++ e*(++++) h r++ y+++(*)
[reply]
Re: Tailing a database table via DBI
by thor (Priest) on Apr 21, 2002 at 18:42 UTC
    When you say you can't alter the table, does this mean that you can't add a trigger? If you can add a trigger, then your problem is easy money. Create an "on insert" trigger that inserts the same row in to another table. Now, your program acceses this other table, reads a row, and now knows what was inserted on the original table. When you are done processing the row, you delete it. This way, you know what your program hasn't processed, because it is the contents of the second table.
[reply]
Re: Tailing a database table via DBI
by graff (Chancellor) on Apr 21, 2002 at 22:51 UTC
    Can you find out whether mysqld (the server process) keeps log files? (and if so, do you have access to the log data?) The MySql manual.html (chapter 23) describes the log files.

    If that's a negative, and if earlier suggestions about triggers are not feasible for you, then the level of "efficiency" depends on how fast things are happening with the table, how big it is, and how closely you want to keep up...

    ... Because the only other solution I can see is to poll the table at some regular interval to see how big it is now, compared to how big it was the last time you polled it (i.e. "select count(*) from table"). Then, I guess you'd have to read the table and just hand the last/newest record(s) to your subroutine. (There is a "mysql_data_seek()" call in the MySql C API, but I don't know if that's accessible via DBI.) Good luck.

[reply]
Re: Tailing a database table via DBI
by chromatic (Archbishop) on Apr 22, 2002 at 00:24 UTC
    Run a program from cron that selects the count(*) of rows from that table. If that differs from the number stored from the last run, use a LIMIT clause to choose the new rows.

    Update: Yes, this idea does rely on insertion order. Instead of thinking it, I should have said that it is a fragile hack.

[reply]
      I don't think a limit modifier will work to provide the 'most recently added' records in this instance since the location of the new rows is not guaranteed to be at the bottom of the table.

      I imagine (and may well be corrected on this) that MySQL adds new rows to any 'previously deleted' row locations, and if there are none, MySQL then adds them to the end of the table. If you're very lucky and rows are added to the table and never deleted, your method may work correctly, but I don't believe that it's a 'production ready' method.

      --t. alex

      "Nyahhh (munch, munch) What's up, Doc?" --Bugs Bunny

[reply]
[d/l]
A reply falls below the community's threshold of quality. You may see it by logging in.

Back to Seekers of Perl Wisdom