ad.install in Advertisement 5.2

File

ad.install

<?php

/**
 * Ad module database schema.
 * Copyright (c) 2005-2007 Jeremy Andrews <[e-mail]>.  
 * All rights reserved.
 */
function ad_install() {
  switch ($GLOBALS['db_type']) {
    case 'pgsql':

      /* The ad table stores administrative information about each ad.  The
       * actual ad itself can be found in the appropriate ad type table.
       */
      db_query("CREATE TABLE {ads} (\n       aid INT NOT NULL UNIQUE DEFAULT '0' PRIMARY KEY,\n       uid INT NOT NULL DEFAULT '0',\n \n       adstatus VARCHAR(255) NOT NULL DEFAULT '',\n       adtype VARCHAR(255) NOT NULL DEFAULT '',\n\n       redirect VARCHAR(255) NOT NULL DEFAULT '',\n\n       autoactivate INT NOT NULL DEFAULT '0',\n       autoactivated INT NOT NULL DEFAULT '0',\n       autoexpire INT NOT NULL DEFAULT '0',\n       autoexpired INT NOT NULL DEFAULT '0',\n\n       activated INT NOT NULL DEFAULT '0',\n       maxviews INT NOT NULL DEFAULT '0',\n       maxclicks INT NOT NULL DEFAULT '0',\n       expired INT NOT NULL DEFAULT '0'\n     );");

      /**
       * This table counts each time a given action occurs on an ad.  Actions
       * include when the ad is viewed, clicked, enabled and disabled.
       * Statistics are collected at an hourly granularity.
       *
       * The source column is used for tracking statistics for externally
       * hosted ads.
       *
       * Actions:
       *  'view', 'click', 'enable', 'disable'
       */
      db_query("CREATE TABLE {ad_statistics} (\n       sid SERIAL NOT NULL PRIMARY KEY,\n       aid INT NOT NULL DEFAULT '0',\n\n       date INT NOT NULL DEFAULT '0',\n       action VARCHAR(255) NOT NULL DEFAULT '',\n       adgroup VARCHAR(255) NULL DEFAULT '',\n       hostid VARCHAR(32) NULL DEFAULT '',\n       count INT NOT NULL DEFAULT '0'\n     );");

      /**
       * The ad_clicks table tracks when a given advertisement was clicked,
       * who clicked it (uid if any and IP address), and what page they were
       * on when they clicked it.
       */
      db_query("CREATE TABLE {ad_clicks} (\n        cid SERIAL NOT NULL PRIMARY KEY,\n        aid INT NOT NULL DEFAULT '0',\n        uid INT NOT NULL DEFAULT '0',\n\n        status INT NOT NULL DEFAULT '0',\n \n        hostname varchar(128) NOT NULL DEFAULT '',\n        user_agent varchar(255) NOT NULL DEFAULT '',\n        adgroup varchar(255) NOT NULL DEFAULT '',\n        hostid varchar(32) NOT NULL DEFAULT '',\n        url varchar(255) DEFAULT '',\n        timestamp INT NOT NULL DEFAULT '0'\n      );");
      break;
    case 'mysql':
    case 'mysqli':
    default:

      /* The ad table stores administrative information about each ad.  The
       * actual ad itself can be found in the appropriate ad type table.
       */
      db_query("CREATE TABLE {ads} (\n        aid INT(10) UNSIGNED NOT NULL DEFAULT '0',\n        uid INT(10) UNSIGNED NOT NULL DEFAULT '0',\n  \n        adstatus VARCHAR(255) NOT NULL DEFAULT '',\n        adtype VARCHAR(255) NOT NULL DEFAULT '',\n\n        redirect VARCHAR(255) NOT NULL DEFAULT '',\n\n        autoactivate INT UNSIGNED NOT NULL DEFAULT '0',\n        autoactivated INT UNSIGNED NOT NULL DEFAULT '0',\n        autoexpire INT UNSIGNED NOT NULL DEFAULT '0',\n        autoexpired INT UNSIGNED NOT NULL DEFAULT '0',\n\n        activated INT UNSIGNED NOT NULL DEFAULT '0',\n        maxviews INT UNSIGNED NOT NULL DEFAULT '0',\n        maxclicks INT UNSIGNED NOT NULL DEFAULT '0',\n        expired INT UNSIGNED NOT NULL DEFAULT '0',\n\n        PRIMARY KEY  (aid),\n        KEY (uid),\n        KEY (autoactivate),\n        KEY (autoexpire)\n      ) /*!40100 DEFAULT CHARACTER SET UTF8 */ ");

      /**
       * This table counts each time a given action occurs on an ad.  Actions
       * include when the ad is viewed, clicked, enabled and disabled.
       * Statistics are collected at an hourly granularity.
       *
       * The source column is used for tracking statistics for externally
       * hosted ads.
       *
       * Actions:
       *  'view', 'click', 'enable', 'disable'
       */
      db_query("CREATE TABLE {ad_statistics} (\n        sid INT UNSIGNED NOT NULL AUTO_INCREMENT,\n        aid INT UNSIGNED NOT NULL DEFAULT '0',\n\n        date INT(10) UNSIGNED NOT NULL DEFAULT '0',\n        action VARCHAR(255) NOT NULL DEFAULT '',\n        adgroup VARCHAR(255) NULL DEFAULT '',\n        hostid VARCHAR(32) NULL DEFAULT '',\n        count INT(11) UNSIGNED NOT NULL DEFAULT '0',\n\n        PRIMARY KEY  (sid),\n        KEY (aid),\n        KEY (date),\n        KEY (action),\n        KEY (adgroup),\n        KEY (hostid)\n      ) /*!40100 DEFAULT CHARACTER SET UTF8 */ ");

      /**
       * The ad_clicks table tracks when a given advertisement was clicked,
       * who clicked it (uid if any and IP address), and what page they were
       * on when they clicked it.
       */
      db_query("CREATE TABLE {ad_clicks} (\n        cid INT UNSIGNED NOT NULL AUTO_INCREMENT,\n        aid INT UNSIGNED NOT NULL DEFAULT '0',\n        uid int(10) UNSIGNED NOT NULL DEFAULT '0',\n\n        status INT(2) NOT NULL DEFAULT '0',\n\n        hostname varchar(128) NOT NULL DEFAULT '',\n        user_agent varchar(255) NOT NULL DEFAULT '',\n        adgroup varchar(255) NOT NULL DEFAULT '',\n        hostid varchar(32) NOT NULL DEFAULT '',\n        url varchar(255) DEFAULT '',\n        timestamp INT(11) UNSIGNED NOT NULL DEFAULT '0',\n\n        PRIMARY KEY  (cid),\n        KEY  (aid),\n        KEY  (status),\n        KEY  (hostname),\n        KEY  (user_agent),\n        KEY  (adgroup),\n        KEY  (hostid),\n        KEY  (url)\n      ) /*!40100 DEFAULT CHARACTER SET UTF8 */ ");
      break;
  }
  drupal_set_message(t('The necessary ad module tables have been created.'));
}

/**
 * Allow complete uninstallation of the ad module.
 */
function ad_uninstall() {

  // Delete all ad content.
  $result = db_query("SELECT nid FROM {node} WHERE type = 'ad'");
  while ($node = db_fetch_object($result)) {
    node_delete($node->nid);
    variable_del("ad_autoactivate_warning_{$node->nid}");
  }

  // Drop all ad module tables.
  db_query('DROP TABLE {ad_clicks}');
  db_query('DROP TABLE {ad_statistics}');
  db_query('DROP TABLE {ads}');

  // Delete all remaining ad module variables.
  $variables = array(
    'ad_cron_timestamp',
    'ad_link_target',
    'ad_cache',
    'ad_cache_file',
    'adserve',
    'ad_group_vid',
    'ad_groups',
    'ad_validate_url',
    'ad_display',
  );
  foreach ($variables as $variable) {
    variable_del($variable);
  }

  // TODO: "ad_block_quantity_$delta"
}

/**
 * Introduce fields for specifying an optional maximum number of view or clicks
 * for each ad.  Also introduce fields for tracking the last time the ad was
 * activated or expired.
 */
function ad_update_1() {
  $ret = array();
  switch ($GLOBALS['db_type']) {
    case 'pgsql':
      db_add_column($ret, 'ads', 'activated', 'int', array(
        'not null' => TRUE,
        'default' => "'0'",
      ));
      db_add_column($ret, 'ads', 'maxviews', 'int', array(
        'not null' => TRUE,
        'default' => "'0'",
      ));
      db_add_column($ret, 'ads', 'maxclicks', 'int', array(
        'not null' => TRUE,
        'default' => "'0'",
      ));
      db_add_column($ret, 'ads', 'expired', 'int', array(
        'not null' => TRUE,
        'default' => "'0'",
      ));
      break;
    case 'mysql':
    case 'mysqli':
    default:
      $ret[] = update_sql("ALTER TABLE {ads} ADD activated INT UNSIGNED NOT NULL DEFAULT '0' AFTER autoexpired");
      $ret[] = update_sql("ALTER TABLE {ads} ADD maxviews INT UNSIGNED NOT NULL DEFAULT '0' AFTER activated");
      $ret[] = update_sql("ALTER TABLE {ads} ADD maxclicks INT UNSIGNED NOT NULL DEFAULT '0' AFTER maxviews");
      $ret[] = update_sql("ALTER TABLE {ads} ADD expired INT UNSIGNED NOT NULL DEFAULT '0' AFTER maxclicks");
  }
  return $ret;
}

/**
 * Ad notifications are being moved to an external module, so remove all
 * references from the core module.
 */
function ad_update_2() {
  $ret = array();
  switch ($GLOBALS['db_type']) {
    default:
      $ret[] = update_sql("DROP TABLE {ad_notifications}");
      $ret[] = update_sql("ALTER TABLE {ads} DROP expire_notified");
      $ret[] = update_sql("ALTER TABLE {ads} DROP renew_notified");
  }
  return $ret;
}

/**
 * Redirect urls need to be absolute to work when hosting ads remotely.
 */
function ad_update_3() {
  $ret = array();
  switch ($GLOBALS['db_type']) {
    default:
      $result = db_query('SELECT aid FROM {ads}');
      while ($ad = db_fetch_object($result)) {
        $ret[] = update_sql("UPDATE {ads} SET redirect = '" . url("ad/redirect/{$ad->aid}", NULL, NULL, TRUE) . "' WHERE aid = {$ad->aid}");
      }
  }
  return $ret;
}

/**
 * Move ad groups from custom tables into taxonomy tables.
 */
function ad_update_4() {
  $ret = array();
  switch ($GLOBALS['db_type']) {
    default:

      // Move groups from ad_groups into taxonomy.
      $result = db_query('SELECT * FROM {ad_groups}');
      while ($group = db_fetch_object($result)) {

        // The default group is now no group at all.
        if ($group->gid == 1) {
          continue;
        }
        $edit = array(
          'vid' => _ad_get_vid(),
          'name' => $group->name,
          'description' => $group->description,
        );
        taxonomy_save_term($edit);
        $tid[$group->gid] = $edit['tid'];
      }

      // Assign ads to new ad groups.
      $result = db_query('SELECT aid,gid FROM {ads}');
      while ($ad = db_fetch_object($result)) {
        if ($tid[$ad->gid]) {
          $ret[] = update_sql("INSERT INTO {term_node} (nid, tid) VALUES ({$ad->aid}, " . $tid[$ad->gid] . ')');
        }
      }

      // Fix the deltas of any group blocks.
      $result = db_query("SELECT * from {blocks} WHERE module = 'ad'");
      $ret[] = update_sql("DELETE FROM {blocks} WHERE module = 'ad'");
      while ($block = db_fetch_object($result)) {
        if ($block->delta == 1) {

          // The old "default" group block now as a delta of 0.
          $ret[] = update_sql("INSERT INTO {blocks} VALUES('ad', 0, '{$block->theme}', {$block->status}, {$block->weight}, '{$block->region}', {$block->custom}, {$block->throttle}, {$block->visibility}, '{$block->pages}', '{$block->title}')");
        }
        else {

          // Switch from a delta of "gid" to a delta of "tid".
          $ret[] = update_sql("INSERT INTO {blocks} VALUES('ad', " . $tid[$block->delta] . ", '{$block->theme}', {$block->status}, {$block->weight}, '{$block->region}', {$block->custom}, {$block->throttle}, {$block->visibility}, '{$block->pages}', '{$block->title}')");
        }
      }

      // Remove old groups table and column.
      $ret[] = update_sql("DROP TABLE {ad_groups}");
      $ret[] = update_sql("ALTER TABLE {ads} DROP gid");
  }
  return $ret;
}

/**
 * Convert to utf8 character set for all tables to allow for proper 
 * internationalization.
 */
function ad_update_5() {
  $ret = array();
  switch ($GLOBALS['db_type']) {
    case 'pgsql':

      // Not sure if anything needs to be done for utf8 support in PostgreSQL.
      break;
    default:
      $tables = array(
        'ads',
        'ad_clicks',
        'ad_hosts',
        'ad_owners',
        'ad_permissions',
        'ad_statistics',
      );
      foreach ($tables as $table) {
        $ret[] = update_sql('ALTER TABLE {' . $table . '} CONVERT TO CHARACTER SET utf8');
      }
  }
  return $ret;
}

/**
 * Update embed-ad-#-count variable to use "-1" instead of "9999" to auto embed 
 * ads after the last paragraph.
 */
function ad_update_6() {
  $types = node_get_types();
  foreach ($types as $key => $type) {
    $count = variable_get("embed-ad-{$key}-count", 0);
    if ($count == 9999) {
      variable_set("embed-ad-{$key}-count", -1);
    }
  }
  return array();
}

/**
 * Add a user_agent column to the ad_clicks table so it's possible to filter
 * out clicks based on the agent (ie, if it reports itself as a bot).
 */
function ad_update_7() {
  $ret = array();
  switch ($GLOBALS['db_type']) {
    case 'mysql':
    case 'mysqli':
      $ret[] = update_sql("ALTER TABLE {ad_clicks} ADD user_agent varchar(255) NOT NULL DEFAULT '' AFTER hostname");
      $ret[] = update_sql("ALTER TABLE {ad_clicks} ADD KEY (user_agent)");
      break;
    case 'pgsql':
      db_add_column($ret, 'ad_clicks', 'user_agent', 'varchar(255)', array(
        'not null' => TRUE,
        'default' => "''",
      ));
      break;
  }
  return $ret;
}

/**
 * Introduce "status" for remote hosts, allowing them to be disabled.
 */
function ad_update_8() {
  $ret = array();
  switch ($GLOBALS['db_type']) {
    case 'mysql':
    case 'mysqli':
      $ret[] = update_sql("ALTER TABLE {ad_hosts} ADD status INT NOT NULL DEFAULT '0' AFTER hostid");
      break;
    case 'pgsql':
      db_add_column($ret, 'ad_hosts', 'status', 'int', array(
        'not null' => TRUE,
        'default' => "'0'",
      ));
      break;
  }
  return $ret;
}

/**
 * Introduce "status" for ad clicks, allowing duplicates to be filtered out.
 */
function ad_update_9() {
  $ret = array();
  switch ($GLOBALS['db_type']) {
    case 'mysql':
    case 'mysqli':
      $ret[] = update_sql("ALTER TABLE {ad_clicks} ADD status INT(2) NOT NULL DEFAULT '0' AFTER uid");
      break;
    case 'pgsql':
      db_add_column($ret, 'ad_clicks', 'status', 'int', array(
        'not null' => TRUE,
        'default' => "'0'",
      ));
      break;
  }
  return $ret;
}

/**
 * Introduce "adgroup" for ad statistics and clicks, providing per-group 
 * granularity.
 */
function ad_update_10() {
  $ret = array();
  switch ($GLOBALS['db_type']) {
    case 'mysql':
    case 'mysqli':
      $ret[] = update_sql("ALTER TABLE {ad_statistics} ADD adgroup VARCHAR(255) NULL DEFAULT '' AFTER action");
      $ret[] = update_sql("ALTER TABLE {ad_statistics} ADD KEY (adgroup)");
      $ret[] = update_sql("ALTER TABLE {ad_clicks} ADD adgroup VARCHAR(255) NULL DEFAULT '' AFTER user_agent");
      $ret[] = update_sql("ALTER TABLE {ad_clicks} ADD KEY (adgroup)");
      break;
    case 'pgsql':
      db_add_column($ret, 'ad_statistics', 'adgroup', 'VARCHAR(255)', array(
        'default' => "''",
      ));
      db_add_column($ret, 'ad_clicks', 'adgroup', 'VARCHAR(255)', array(
        'default' => "''",
      ));
      break;
  }
  return $ret;
}

/**
 * Update the cached menu to incorporate new ad type settings.
 */
function ad_update_5111() {
  $ret = array();
  cache_clear_all('*', 'cache_menu', TRUE);
  return $ret;
}

/**
 * Introduce "extra" for ad statistics and clicks, optionally allowing add-on
 * modules to provide additional granularity.
 */
function ad_update_5112() {
  $ret = array();
  switch ($GLOBALS['db_type']) {
    case 'mysql':
    case 'mysqli':
      $ret[] = update_sql("ALTER TABLE {ad_statistics} ADD extra VARCHAR(255) NULL DEFAULT '' AFTER adgroup");
      $ret[] = update_sql("ALTER TABLE {ad_statistics} ADD KEY (extra)");
      $ret[] = update_sql("ALTER TABLE {ad_clicks} ADD extra VARCHAR(255) NULL DEFAULT '' AFTER adgroup");
      $ret[] = update_sql("ALTER TABLE {ad_clicks} ADD KEY (extra)");
      break;
    case 'pgsql':
      db_add_column($ret, 'ad_statistics', 'extra', 'VARCHAR(255)', array(
        'default' => "''",
      ));
      db_add_column($ret, 'ad_clicks', 'extra', 'VARCHAR(255)', array(
        'default' => "''",
      ));
      break;
  }
  return $ret;
}

Functions

Name	Description
ad_install	Ad module database schema. Copyright (c) 2005-2007 Jeremy Andrews <[e-mail]>. All rights reserved.
ad_uninstall	Allow complete uninstallation of the ad module.
ad_update_1	Introduce fields for specifying an optional maximum number of view or clicks for each ad. Also introduce fields for tracking the last time the ad was activated or expired.
ad_update_10	Introduce "adgroup" for ad statistics and clicks, providing per-group granularity.
ad_update_2	Ad notifications are being moved to an external module, so remove all references from the core module.
ad_update_3	Redirect urls need to be absolute to work when hosting ads remotely.
ad_update_4	Move ad groups from custom tables into taxonomy tables.
ad_update_5	Convert to utf8 character set for all tables to allow for proper internationalization.
ad_update_5111	Update the cached menu to incorporate new ad type settings.
ad_update_5112	Introduce "extra" for ad statistics and clicks, optionally allowing add-on modules to provide additional granularity.
ad_update_6	Update embed-ad-#-count variable to use "-1" instead of "9999" to auto embed ads after the last paragraph.
ad_update_7	Add a user_agent column to the ad_clicks table so it's possible to filter out clicks based on the agent (ie, if it reports itself as a bot).
ad_update_8	Introduce "status" for remote hosts, allowing them to be disabled.
ad_update_9	Introduce "status" for ad clicks, allowing duplicates to be filtered out.