Random postgresql tasks

Getting the foreign key references to a table

SELECT
    tc.constraint_name, tc.table_name, kcu.column_name, 
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND ccu.table_name='table_name';

Getting the numbers of connections to the server

select datname, client_addr, usename, Count(1) As connections
from pg_stat_activity
group by datname, client_addr, usename
order by datname, client_addr

Magento debugging thoughts

Random thoughts on debugging in magento

Logging magento queries

Sometimes you need to take a look at the queries generated by magento while loading different objects or collections. To log queries edit lib/Varien/Db/Adapter/Pdo/Mysql.php and change the following line:

protected $_debug               = false;

to

protected $_debug               = true;

The log file is specified in:

protected $_debugFile           = 'var/debug/pdo_mysql.log';

Logging ALL magento queries

Even with logging enabled not all queries are  logged. To enable logging all queries change:

protected $_logAllQueries       = false;

to

protected $_logAllQueries       = true;

Logging all magento events

To log all the events magento throws edit app/Mage.php and add the following line to the dispatchEvent function:

Mage::log($name, null, 'events.log');

After the change the function should look something like:

public static function dispatchEvent($name, array $data = array())
    {
        Mage::log($name, null, 'events.log');
        Varien_Profiler::start('DISPATCH EVENT:'.$name);
        $result = self::app()->dispatchEvent($name, $data);
        #$result = self::registry('events')->dispatch($name, $data);
        Varien_Profiler::stop('DISPATCH EVENT:'.$name);
        return $result;
    }

Events will be logged to var/log/events.log

Magento queries list

Quick reference list of Magento queries

Getting store information

Load store object ($identifier can be store id or code):

$store = Mage::getModel('core/store')->load($identifier);

Get (admin) Store Id using store code:

$adminStoreId = Mage::getModel('core/store')->load('admin')->getId();

Filtering by null fields

Get items with a NULL field:

$collection = Mage::getModel('your/model')->getCollection();
$collection->addFieldToFilter('field_code',array('null' => true));
$collection->load();

Get items with non NULL field:

$collection = Mage::getModel('your/model')->getCollection();
$collection->addFieldToFilter('field_code',array('neq' => 'NULL'));
$collection->load();