function forward_update_1 in Forward 6

Same name and namespace in other branches

1. 5 forward.install \forward_update_1()
2. 7 forward.install \forward_update_1()

Add statistics table and populate with data from forward_log

File

./forward.install, line 61

Code

function forward_update_1() {

  // initialize table
  $schema['forward_statistics'] = array(
    'fields' => array(
      'nid' => array(
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
      ),
      'last_forward_timestamp' => array(
        'type' => 'int',
        'not null' => TRUE,
        'default' => 0,
      ),
      'forward_count' => array(
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
      ),
      'clickthrough_count' => array(
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
      ),
    ),
    'index' => array(
      'last_forward_timestamp',
    ),
    'primary key' => array(
      'nid',
    ),
  );
  $ret = array();
  db_create_table($ret, 'forward_statistics', $schema['forward_statistics']);
  db_query("INSERT INTO {forward_statistics} (nid, last_forward_timestamp, forward_count, clickthrough_count) values(0, 0, 0, 0)");
  db_query("INSERT INTO {forward_statistics} (nid, last_forward_timestamp, forward_count, clickthrough_count) SELECT n.nid, 0, 0, 0 FROM {node} n");

  // fill table
  $forwards = db_query("SELECT f.nid, f.timestamp, COUNT(f.nid) as forward_count FROM {node} n LEFT JOIN {forward_log} f ON f.nid = n.nid WHERE f.type = 'SENT' GROUP BY f.nid, f.timestamp");
  while ($forward = db_fetch_object($forwards)) {
    $forward_count = db_result(db_query("SELECT COUNT(nid) FROM {forward_log} WHERE nid = %d AND type = '%s'", $forward->nid, 'SENT'));
    $clickthrough_count = db_result(db_query("SELECT COUNT(nid) FROM {forward_log} WHERE nid = %d AND type = '%s'", $forward->nid, 'REF'));
    db_query("UPDATE {forward_statistics} SET forward_count = %d, clickthrough_count = %d, last_forward_timestamp = %d WHERE nid = %d", $forward_count, $clickthrough_count, $forward->timestamp, $forward->nid);
  }
  return $ret;
}