/** * Create tables and prepare data. */ function affiliates_setup() { global $wpdb, $wp_roles; _affiliates_set_default_capabilities(); $charset_collate = ''; if (!empty($wpdb->charset)) { $charset_collate = "DEFAULT CHARACTER SET {$wpdb->charset}"; } if (!empty($wpdb->collate)) { $charset_collate .= " COLLATE {$wpdb->collate}"; } $affiliates_table = _affiliates_get_tablename('affiliates'); if ($wpdb->get_var("SHOW TABLES LIKE '" . $affiliates_table . "'") != $affiliates_table) { $queries[] = "CREATE TABLE " . $affiliates_table . "(\n\t\t\t\taffiliate_id bigint(20) unsigned NOT NULL auto_increment,\n\t\t\t\tname varchar(100) NOT NULL,\n\t\t\t\temail varchar(512) default NULL,\n\t\t\t\tfrom_date date NOT NULL,\n\t\t\t\tthru_date date default NULL,\n\t\t\t\tstatus varchar(10) NOT NULL DEFAULT 'active',\n\t\t\t\ttype varchar(10) NULL,\n\t\t\t\tPRIMARY KEY (affiliate_id),\n\t\t\t\tINDEX affiliates_afts (affiliate_id, from_date, thru_date, status),\n\t\t\t\tINDEX affiliates_sft (status, from_date, thru_date)\n\t\t\t) {$charset_collate};"; } // email @see http://tools.ietf.org/html/rfc5321 // 2.3.11. Mailbox and Address // ... The standard mailbox naming convention is defined to // be "local-part@domain"; ... // 4.1.2. Command Argument Syntax // ... // Mailbox = Local-part "@" ( Domain / address-literal ) // ... // 4.5.3. Sizes and Timeouts // 4.5.3.1.1. Local-part // The maximum total length of a user name or other local-part is 64 octets. // 4.5.3.1.2. Domain // The maximum total length of a domain name or number is 255 octets. // 4.5.3.1.3. Path // The maximum total length of a reverse-path or forward-path is 256 // octets (including the punctuation and element separators). // So the maximum size of an email address is ... ? // 64 + 1 + 255 = 320 octets // Then again, people change their minds ... we'll assume 512 as sufficient. // Note: WP's user.user_email is varchar(100) @see wp-admin/includes/schema.php // IPv6 addr // FFFF:FFFF:FFFF:FFFF:FFFF:FFFF:FFFF:FFFF = 340282366920938463463374607431768211455 // Note that ipv6 is not part of the PK but can be handled using // the lower bits of the ipv6 address to fill in ip and using the // complete IPv6 address on ipv6. // Note also, that currently Affiliates does NOT use ipv6. $referrals_table = _affiliates_get_tablename('referrals'); if ($wpdb->get_var("SHOW TABLES LIKE '" . $referrals_table . "'") != $referrals_table) { $queries[] = "CREATE TABLE " . $referrals_table . "(\n\t\t\t\treferral_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,\n\t\t\t\taffiliate_id bigint(20) unsigned NOT NULL default '0',\n\t\t\t\tcampaign_id bigint(20) UNSIGNED DEFAULT NULL,\n\t\t\t\tpost_id bigint(20) unsigned NOT NULL default '0',\n\t\t\t\tdatetime datetime NOT NULL,\n\t\t\t\tdescription varchar(5000),\n\t\t\t\tip int(10) unsigned default NULL,\n\t\t\t\tipv6 decimal(39,0) unsigned default NULL,\n\t\t\t\tuser_id bigint(20) unsigned default NULL,\n\t\t\t\tamount decimal(18,2) default NULL,\n\t\t\t\tcurrency_id char(3) default NULL,\n\t\t\t\tdata longtext default NULL,\n\t\t\t\tstatus varchar(10) NOT NULL DEFAULT '" . AFFILIATES_REFERRAL_STATUS_ACCEPTED . "',\n\t\t\t\ttype varchar(10) NULL,\n\t\t\t\treference VARCHAR(100) DEFAULT NULL,\n\t\t\t\tPRIMARY KEY (referral_id),\n\t\t\t\tINDEX aff_referrals_apd (affiliate_id, post_id, datetime),\n\t\t\t\tINDEX aff_referrals_da (datetime, affiliate_id),\n\t\t\t\tINDEX aff_referrals_sda (status, datetime, affiliate_id),\n\t\t\t\tINDEX aff_referrals_tda (type, datetime, affiliate_id),\n\t\t\t\tINDEX aff_referrals_ref (reference(20)),\n\t\t\t\tINDEX aff_referrals_ac (affiliate_id, campaign_id),\n\t\t\t\tINDEX aff_referrals_c (campaign_id)\n\t\t\t) {$charset_collate};"; // @see http://bugs.mysql.com/bug.php?id=27645 as of now (2011-03-19) NOW() can not be specified as the default value for a datetime column } // A note about whether or not to record information about // http_user_agent here: No, we don't do that! // Our business is to record hits on affiliate links, not // to gather statistical data about user agents. For our // purpose, it does not add value to record that and if // one wishes to do so, there are better solutions anyhow. // IMPORTANT: // datetime -- records the datetime with respect to the server's timezone // date and time are are also with respect to the server's timezone // date -- we do NOT use this to display information to the user // time -- same applies to this here // date and time (and currently only date really) are used for better // performance on certain queries. // We DO use the datetime (adjusted to the user's timezone using // DateHelper's s2u() function to display the date and time // in accordance to the user's date and time. $hits_table = _affiliates_get_tablename('hits'); if ($wpdb->get_var("SHOW TABLES LIKE '" . $hits_table . "'") != $hits_table) { $queries[] = "CREATE TABLE " . $hits_table . "(\n\t\t\t\taffiliate_id BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',\n\t\t\t\tcampaign_id BIGINT(20) UNSIGNED DEFAULT NULL,\n\t\t\t\tdate DATE NOT NULL,\n\t\t\t\ttime TIME NOT NULL,\n\t\t\t\tdatetime DATETIME NOT NULL,\n\t\t\t\tip INT(10) UNSIGNED DEFAULT NULL,\n\t\t\t\tipv6 DECIMAL(39,0) UNSIGNED DEFAULT NULL,\n\t\t\t\tis_robot TINYINT(1) DEFAULT 0,\n\t\t\t\tuser_id BIGINT(20) UNSIGNED DEFAULT NULL,\n\t\t\t\tcount INT DEFAULT 1,\n\t\t\t\ttype VARCHAR(10) DEFAULT NULL,\n\t\t\t\tPRIMARY KEY (affiliate_id, date, time, ip),\n\t\t\t\tINDEX aff_hits_ddt (date, datetime),\n\t\t\t\tINDEX aff_hits_dtd (datetime, date),\n\t\t\t\tINDEX aff_hits_acm (affiliate_id, campaign_id)\n\t\t\t) {$charset_collate};"; } $robots_table = _affiliates_get_tablename('robots'); if ($wpdb->get_var("SHOW TABLES LIKE '" . $robots_table . "'") != $robots_table) { $queries[] = "CREATE TABLE " . $robots_table . "(\n\t\t\t\trobot_id bigint(20) unsigned NOT NULL auto_increment,\n\t\t\t\tname varchar(100) NOT NULL,\n\t\t\t\tPRIMARY KEY (robot_id),\n\t\t\t\tINDEX aff_robots_n (name)\n\t\t\t) {$charset_collate};"; } $affiliates_users_table = _affiliates_get_tablename('affiliates_users'); if ($wpdb->get_var("SHOW TABLES LIKE '" . $affiliates_users_table . "'") != $affiliates_users_table) { $queries[] = "CREATE TABLE " . $affiliates_users_table . "(\n\t\t\t\taffiliate_id bigint(20) unsigned NOT NULL,\n\t\t\t\tuser_id bigint(20) unsigned NOT NULL,\n\t\t\t\tPRIMARY KEY (affiliate_id, user_id)\n\t\t\t) {$charset_collate};"; } if (!empty($queries)) { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; dbDelta($queries); } if ($wpdb->get_var("SHOW TABLES LIKE '" . $affiliates_table . "'") == $affiliates_table) { $today = date('Y-m-d', time()); $direct = intval($wpdb->get_var("SELECT COUNT(affiliate_id) FROM {$affiliates_table} WHERE type = '" . AFFILIATES_DIRECT_TYPE . "';")); if ($direct <= 0) { $wpdb->query("INSERT INTO {$affiliates_table} (name, from_date, type) VALUES ('" . AFFILIATES_DIRECT_NAME . "','{$today}','" . AFFILIATES_DIRECT_TYPE . "');"); } } affiliates_update(); affiliates_update_rewrite_rules(); }
function affiliates_version_check() { global $affiliates_version, $affiliates_admin_messages; $previous_version = get_option('affiliates_plugin_version', null); $affiliates_version = AFFILIATES_CORE_VERSION; if (strcmp($previous_version, $affiliates_version) < 0) { if (affiliates_update($previous_version)) { update_option('affiliates_plugin_version', $affiliates_version); } else { $affiliates_admin_messages[] = '<div class="error">Updating the Affiliates core FAILED.</div>'; } } }