Monitor usage of a certain database field

Question

Hi guys,

I have a nasty problem. I want to get rid of a certain database field, but I'm not sure in which bits of code it's called. Is there a way to find out where this field is used/called from (except for text searching the code; this is fairly useless seeing as how the field is named 'email')?

Cheers

Answer 1

setting mysql to log all queries for some time might help. the queries will give you the tip where to look

Answer 2

brute force - set up a test instance - remove the column - and excercise your test suite.

Answer 3

You should do this in PHP i would expect

For example:

<?php
class Query
{
    var $command;
    var $resource;
    function __construct($sql_command = '')
    {
       $this->command = $sql_command;
    }

    public function setResource($resource)
    {
        $this->resource = $resource;
    }
}

//then you would have some kind of database class, but here we would modify the query method.

class Database
{
    function query(Query $query)
    {
       $resource = mysql_query($query->command);
       $query->setResource($resource);

       //Then you can send the class to the monitor
       QueryMonitor::Monitor($query);
    }
}

abstract class QueryMonitor
{
    public static Monitor(Query $query)
    {
       //here you use $query->resource to do monitoring of queryies
       //You can also parse the query and gather what query type it was:-
       //Select or Delete, you can also mark what tables were in the Query
       //Even meta data so
       $total_found = mysql_num_rows($query->resource);
       $field_table = mysql_field_table ($query->resource);
       //Just an example..
    }
}
?>

Obviously it would be more advanced than that but you can set up a system to monitor every query and every queries meta data in a log file or w.e

Answer 4

I would first text search the files for the table name, then only search the tables that contain the table name for the field name.

I wrote a program to do this for my own purposes. It builds an in-memory listing of tables and fields and relates the tables to the fields. Then it loops through tables, searching for the code files that contain the table names, and then searches those files for the fields in the tables found. I'd recommend a similar methodology in your case.

Answer 5

create a before insert trigger on that table that monitors the insertion on that column.

at the same time create another table called monitor with only one column email

make that table insert the value of NEW.email field into monitor.email as well as in real table.

so you can run your application and check for the existence of any non-null value in monitor table