<?php
// Prevent loading this file directly
if (!defined('SENDPRESS_VERSION')) {
header('HTTP/1.0 403 Forbidden');
die;
}
global $wpdb;
// Create Stats Table
$subscriber_table = SendPress_DB_Tables::subscriber_table();
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
$wpdb->flush();
if ($wpdb->get_var("show tables like '{$subscriber_table}'") != $subscriber_table) {
$sql2 = "CREATE TABLE " . $subscriber_table . " (\n\t\t subscriberID bigint(20) unsigned NOT NULL AUTO_INCREMENT,\n\t\t email varchar(100) NOT NULL DEFAULT '',\n\t\t join_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00',\n\t\t status int(1) NOT NULL DEFAULT '1',\n\t\t registered datetime NOT NULL DEFAULT '0000-00-00 00:00:00',\n\t\t registered_ip varchar(20) NOT NULL DEFAULT '',\n\t\t identity_key varchar(60) NOT NULL DEFAULT '',\n\t\t bounced int(1) NOT NULL DEFAULT '0',\n\t\t firstname varchar(250) NOT NULL DEFAULT '',\n\t\t lastname varchar(250) NOT NULL DEFAULT '',\n\t\t wp_user_id bigint(20) DEFAULT NULL,\n\t\t PRIMARY KEY (`subscriberID`),\n\t\t UNIQUE KEY (`email`) ,\n\t\t UNIQUE KEY (`identity_key`),\n\t\t UNIQUE KEY `wp_user_id` (`wp_user_id`)\n\t\t)";
dbDelta($sql2);
}
$subscriber_list_subscribers = SendPress_DB_Tables::list_subcribers_table();
$wpdb->flush();
if ($wpdb->get_var("show tables like '{$subscriber_list_subscribers}'") != $subscriber_list_subscribers) {
$sql3 = "CREATE TABLE " . $subscriber_list_subscribers . " (\n\t\t `id` int(11) unsigned NOT NULL AUTO_INCREMENT,\n\t\t `listID` int(11) DEFAULT NULL,\n\t\t `subscriberID` int(11) DEFAULT NULL,\n\t\t `status` int(1) DEFAULT NULL,\n\t\t `updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',\n\t\t PRIMARY KEY (`id`),\n\t\t KEY (`listID`) ,\n\t\t KEY (`subscriberID`) ,\n\t\t KEY (`status`) ,\n\t\t UNIQUE KEY `listsub` (`subscriberID`,`listID`)\n\t\t)";
dbDelta($sql3);
}
$subscriber_queue = SendPress_DB_Tables::queue_table();
$wpdb->flush();
if ($wpdb->get_var("show tables like '{$subscriber_queue}'") != $subscriber_queue) {
$sql5 = "CREATE TABLE " . $subscriber_queue . " (\n\t `id` int(11) NOT NULL AUTO_INCREMENT,\n\t `subscriberID` int(11) DEFAULT NULL,\n\t `listID` int(11) DEFAULT NULL,\n\t `from_name` varchar(64) DEFAULT NULL,\n\t `from_email` varchar(128) NOT NULL,\n\t `to_email` varchar(128) NOT NULL,\n\t `subject` varchar(255) NOT NULL,\n\t `messageID` varchar(400) NOT NULL,\n\t `emailID` int(11) NOT NULL,\n\t `max_attempts` int(11) NOT NULL DEFAULT '3',\n\t `attempts` int(11) NOT NULL DEFAULT '0',\n\t `success` tinyint(1) NOT NULL DEFAULT '0',\n\t `date_published` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',\n\t `inprocess` int(1) DEFAULT '0',\n\t `last_attempt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',\n\t `date_sent` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',\n\t PRIMARY KEY (`id`),\n\t KEY `to_email` (`to_email`),\n\t KEY `subscriberID` (`subscriberID`),\n\t KEY `listID` (`listID`),\n\t KEY `inprocess` (`inprocess`),\n\t KEY `success` (`success`),\n\t KEY `max_attempts` (`max_attempts`),\n\t KEY `attempts` (`attempts`),\n\t KEY `last_attempt` (`last_attempt`)\n\t)";
dbDelta($sql5);
}
add_option("sendpress_db_version", SendPress_DB_Tables::$db_version);
<?php
// Prevent loading this file directly
if (!defined('SENDPRESS_VERSION')) {
header('HTTP/1.0 403 Forbidden');
die;
}
global $wpdb;
$table_to_update = SendPress_DB_Tables::list_subcribers_table();
if ($wpdb->get_var("SHOW COLUMNS FROM " . $table_to_update . " LIKE 'updated'") == false) {
$wpdb->query("ALTER TABLE " . $table_to_update . " ADD COLUMN `updated` datetime DEFAULT NULL");
}
public function jaiminho_fix_tables_html()
{
var_dump(isset($_POST['fix_tables']));
global $wpdb;
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
//$wpdb->hide_errors();
$collate = '';
if ($wpdb->has_cap('collation')) {
if (!empty($wpdb->charset)) {
$collate .= "DEFAULT CHARACTER SET {$wpdb->charset}";
}
if (!empty($wpdb->collate)) {
$collate .= " COLLATE {$wpdb->collate}";
}
}
?>
<form method="post">
<input type="hidden" name="fix_tables" value="true" />
<?php
submit_button(__('Corrigir Tabelas', 'jaiminho'));
?>
</form><?php
$args = array('network_id' => null, 'public' => null, 'archived' => null, 'mature' => null, 'spam' => null, 'deleted' => null, 'limit' => null, 'offset' => 0);
$blogs = wp_get_sites($args);
echo '<pre>';
//var_dump($blogs);
echo '</pre>';
foreach ($blogs as $blog) {
switch_to_blog($blog['blog_id']);
echo '<br>';
echo get_bloginfo('name');
echo '<br>';
echo "<b>Database Tables</b>: <br>";
// primera tabela
$subscriber_events_table = new SendPress_DB_Subscribers_Tracker();
$subscriber_events_table = $subscriber_events_table->table_name;
if ($wpdb->get_var("show tables like '{$subscriber_events_table}'") != $subscriber_events_table) {
echo $subscriber_events_table . " Not Installed<br>";
if (isset($_POST['fix_tables'])) {
$command = " CREATE TABLE {$subscriber_events_table} (\nsubscriberID int(11) unsigned NOT NULL,\nemailID int(11) unsigned NOT NULL,\nsent_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00', \nopened_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',\nstatus tinyint(4) NOT NULL DEFAULT '0',\nPRIMARY KEY (subscriberID,emailID)\n) {$collate};\n";
$return = dbDelta($command);
echo $return["wp_sendpress_report_url"];
}
} else {
echo $subscriber_events_table . " OK<br>";
}
// segunda tabela
$subscriber_events_table = new SendPress_DB_Subscribers_Url();
$subscriber_events_table = $subscriber_events_table->table_name;
if ($wpdb->get_var("show tables like '{$subscriber_events_table}'") != $subscriber_events_table) {
echo $subscriber_events_table . " Not Installed<br>";
if (isset($_POST['fix_tables'])) {
$command = " CREATE TABLE {$subscriber_events_table} (\nsubscriberID int(11) unsigned NOT NULL,\nemailID int(11) unsigned NOT NULL,\nurlID int(11) unsigned NOT NULL,\nclicked_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00', \nclick_count int(11) unsigned NOT NULL,\nPRIMARY KEY ( subscriberID , emailID , urlID )\n) {$collate};\n";
$return = dbDelta($command);
echo $return["wp_sendpress_report_url"];
}
} else {
echo $subscriber_events_table . " OK<br>";
}
//terceira tabela
$subscriber_events_table = new SendPress_DB_Url();
$subscriber_events_table = $subscriber_events_table->table_name;
if ($wpdb->get_var("show tables like '{$subscriber_events_table}'") != $subscriber_events_table) {
echo $subscriber_events_table . " Not Installed<br>";
if (isset($_POST['fix_tables'])) {
$command = " CREATE TABLE {$subscriber_events_table} (urlID int(11) unsigned NOT NULL AUTO_INCREMENT, url text,hash varchar(255) DEFAULT NULL, PRIMARY KEY (urlID), KEY hash (hash)) {$collate};\n";
$return = dbDelta($command);
echo $return["wp_sendpress_report_url"];
}
} else {
echo $subscriber_events_table . " OK<br>";
}
// quarta tabela
$report_url_table = SendPress_DB_Tables::report_url_table();
if ($wpdb->get_var("show tables like '{$report_url_table}'") != $report_url_table) {
echo $report_url_table . " Not Installed<br>";
if (isset($_POST['fix_tables'])) {
$command = " CREATE TABLE {$report_url_table} (\nurlID int(11) unsigned NOT NULL AUTO_INCREMENT,\nurl varchar(2000) DEFAULT NULL,\nreportID int(11) DEFAULT NULL,\nPRIMARY KEY (urlID),\nKEY reportID (reportID),\nKEY url (url(255))\n) {$collate};\n";
$return = dbDelta($command);
echo $return["wp_sendpress_report_url"];
}
} else {
echo $report_url_table . " OK<br>";
}
// quinta tabela
$subscriber_status_table = SendPress_DB_Tables::subscriber_status_table();
if ($wpdb->get_var("show tables like '{$subscriber_status_table}'") != $subscriber_status_table) {
echo $subscriber_status_table . " Not Installed<br>";
if (isset($_POST['fix_tables'])) {
$command = " CREATE TABLE {$subscriber_status_table} (\nstatusid int(11) unsigned NOT NULL AUTO_INCREMENT, \nstatus varchar(255) DEFAULT NULL, \nPRIMARY KEY (statusid)\n) {$collate};\n";
$return = dbDelta($command);
echo $return["wp_sendpress_report_url"];
}
} else {
echo $subscriber_status_table . " OK<br>";
}
// sexta tabela
$subscriber_table = SendPress_DB_Tables::subscriber_table();
if ($wpdb->get_var("show tables like '{$subscriber_table}'") != $subscriber_table) {
echo $subscriber_table . " Not Installed<br>";
if (isset($_POST['fix_tables'])) {
$command = " CREATE TABLE {$subscriber_table} (\nsubscriberID bigint(20) unsigned NOT NULL AUTO_INCREMENT, \nemail varchar(100) NOT NULL DEFAULT '', \njoin_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00', \nstatus int(1) NOT NULL DEFAULT '1', \nregistered datetime NOT NULL DEFAULT '0000-00-00 00:00:00', \nregistered_ip varchar(20) NOT NULL DEFAULT '', \nidentity_key varchar(60) NOT NULL DEFAULT '', \nbounced int(1) NOT NULL DEFAULT '0', \nfirstname varchar(250) NOT NULL DEFAULT '', \nlastname varchar(250) NOT NULL DEFAULT '', \nwp_user_id bigint(20) DEFAULT NULL, \nphonenumber varchar(12) DEFAULT NULL, \nsalutation varchar(40) DEFAULT NULL,\nPRIMARY KEY (subscriberID), \nUNIQUE KEY email (email) , \nUNIQUE KEY identity_key (identity_key), \nUNIQUE KEY wp_user_id (wp_user_id)\n) {$collate};\n";
$return = dbDelta($command);
echo $return["wp_sendpress_report_url"];
}
} else {
echo $subscriber_table . " OK<br>";
}
//setima tabela
$subscriber_list_subscribers = SendPress_DB_Tables::list_subcribers_table();
if ($wpdb->get_var("show tables like '{$subscriber_list_subscribers}'") != $subscriber_list_subscribers) {
echo $subscriber_list_subscribers . " Not Installed<br>";
if (isset($_POST['fix_tables'])) {
$command .= " CREATE TABLE {$subscriber_list_subscribers} (\nid int(11) unsigned NOT NULL AUTO_INCREMENT, \nlistID int(11) DEFAULT NULL, \nsubscriberID int(11) DEFAULT NULL, \nstatus int(1) DEFAULT NULL, \nupdated datetime NOT NULL DEFAULT '0000-00-00 00:00:00', \nPRIMARY KEY (id), \nKEY listID (listID) , \nKEY subscriberID (subscriberID) , \nKEY status (status), \nUNIQUE KEY listsub (subscriberID,listID)\n) {$collate};\n";
$return = dbDelta($command);
echo $return["wp_sendpress_report_url"];
}
} else {
echo $subscriber_list_subscribers . " OK<br>";
}
//oitava tabela
$subscriber_queue = SendPress_DB_Tables::queue_table();
if ($wpdb->get_var("show tables like '{$subscriber_queue}'") != $subscriber_queue) {
echo $subscriber_queue . " Not Installed<br>";
if (isset($_POST['fix_tables'])) {
$command .= " CREATE TABLE {$subscriber_queue} (\nid int(11) NOT NULL AUTO_INCREMENT, \nsubscriberID int(11) DEFAULT NULL, \nlistID int(11) DEFAULT NULL, \nfrom_name varchar(64) DEFAULT NULL, \nfrom_email varchar(128) NOT NULL, \nto_email varchar(128) NOT NULL, \nsubject varchar(255) NOT NULL, \nmessageID varchar(400) NOT NULL, \nemailID int(11) NOT NULL, \nmax_attempts int(11) NOT NULL DEFAULT '3', \nattempts int(11) NOT NULL DEFAULT '0', \nsuccess tinyint(1) NOT NULL DEFAULT '0', \ndate_published datetime NOT NULL DEFAULT '0000-00-00 00:00:00', \ninprocess int(1) DEFAULT '0', \nlast_attempt datetime NOT NULL DEFAULT '0000-00-00 00:00:00', \ndate_sent datetime NOT NULL DEFAULT '0000-00-00 00:00:00', \nPRIMARY KEY (id), \nKEY to_email (to_email), \nKEY subscriberID (subscriberID), \nKEY listID (listID), \nKEY inprocess (inprocess), \nKEY success (success), \nKEY max_attempts (max_attempts), \nKEY attempts (attempts), \nKEY last_attempt (last_attempt),\nKEY date_sent (date_sent),\nKEY success_date (success,last_attempt,max_attempts,attempts,inprocess,date_sent)\n) {$collate};\n";
$return = dbDelta($command);
echo $return["wp_sendpress_report_url"];
}
} else {
echo $subscriber_queue . " OK<br>";
}
echo "<br>";
echo '<br>';
}
restore_current_blog();
}
static function install()
{
global $wpdb;
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
$wpdb->hide_errors();
$collate = '';
if ($wpdb->has_cap('collation')) {
if (!empty($wpdb->charset)) {
$collate .= "DEFAULT CHARACTER SET {$wpdb->charset}";
}
if (!empty($wpdb->collate)) {
$collate .= " COLLATE {$wpdb->collate}";
}
}
/*
THE RULES FOR DBDELTA
You must put each field on its own line in your SQL statement.
You must have two spaces between the words PRIMARY KEY and the definition of your primary key.
You must use the key word KEY rather than its synonym INDEX and you must include at least one KEY.
You must not use any apostrophes or backticks around field names.
*/
// Create Stats Table
$subscriber_table = SendPress_DB_Tables::subscriber_table();
//if($wpdb->get_var("show tables like '$subscriber_table'") != $subscriber_table) {
$command = '';
$command .= " CREATE TABLE {$subscriber_table} (\nsubscriberID bigint(20) unsigned NOT NULL AUTO_INCREMENT, \nemail varchar(100) NOT NULL DEFAULT '', \njoin_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00', \nstatus int(1) NOT NULL DEFAULT '1', \nregistered datetime NOT NULL DEFAULT '0000-00-00 00:00:00', \nregistered_ip varchar(20) NOT NULL DEFAULT '', \nidentity_key varchar(60) NOT NULL DEFAULT '', \nbounced int(1) NOT NULL DEFAULT '0', \nfirstname varchar(250) NOT NULL DEFAULT '', \nlastname varchar(250) NOT NULL DEFAULT '', \nwp_user_id bigint(20) DEFAULT NULL, \nPRIMARY KEY (subscriberID), \nUNIQUE KEY email (email) , \nUNIQUE KEY identity_key (identity_key), \nUNIQUE KEY wp_user_id (wp_user_id)\n) {$collate};\n";
//dbDelta($command);
//}
$subscriber_list_subscribers = SendPress_DB_Tables::list_subcribers_table();
$command .= " CREATE TABLE {$subscriber_list_subscribers} (\nid int(11) unsigned NOT NULL AUTO_INCREMENT, \nlistID int(11) DEFAULT NULL, \nsubscriberID int(11) DEFAULT NULL, \nstatus int(1) DEFAULT NULL, \nupdated datetime NOT NULL DEFAULT '0000-00-00 00:00:00', \nPRIMARY KEY (id), \nKEY listID (listID) , \nKEY subscriberID (subscriberID) , \nKEY status (status), \nUNIQUE KEY listsub (subscriberID,listID)\n) {$collate};\n";
//dbDelta($command);
$subscriber_meta = SendPress_DB_Tables::subscriber_meta_table();
$command .= " CREATE TABLE {$subscriber_meta} (\nsmeta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, \nsubscriberID bigint(20) unsigned NOT NULL DEFAULT '0', \nlistID bigint(20) unsigned NULL DEFAULT '0', \nmeta_key varchar(255) DEFAULT NULL, \nmeta_value longtext, \nPRIMARY KEY (smeta_id), \nKEY listID (listID), \nKEY subscriberID (subscriberID), \nKEY meta_key (meta_key)\n) {$collate};\n";
//dbDelta($command);
$subscriber_queue = SendPress_DB_Tables::queue_table();
$command .= " CREATE TABLE {$subscriber_queue} (\nid int(11) NOT NULL AUTO_INCREMENT, \nsubscriberID int(11) DEFAULT NULL, \nlistID int(11) DEFAULT NULL, \nfrom_name varchar(64) DEFAULT NULL, \nfrom_email varchar(128) NOT NULL, \nto_email varchar(128) NOT NULL, \nsubject varchar(255) NOT NULL, \nmessageID varchar(400) NOT NULL, \nemailID int(11) NOT NULL, \nmax_attempts int(11) NOT NULL DEFAULT '3', \nattempts int(11) NOT NULL DEFAULT '0', \nsuccess tinyint(1) NOT NULL DEFAULT '0', \ndate_published datetime NOT NULL DEFAULT '0000-00-00 00:00:00', \ninprocess int(1) DEFAULT '0', \nlast_attempt datetime NOT NULL DEFAULT '0000-00-00 00:00:00', \ndate_sent datetime NOT NULL DEFAULT '0000-00-00 00:00:00', \nPRIMARY KEY (id), \nKEY to_email (to_email), \nKEY subscriberID (subscriberID), \nKEY listID (listID), \nKEY inprocess (inprocess), \nKEY success (success), \nKEY max_attempts (max_attempts), \nKEY attempts (attempts), \nKEY last_attempt (last_attempt),\nKEY date_sent (date_sent),\nKEY success_date (success,last_attempt,max_attempts,attempts,inprocess,date_sent)\n) {$collate};\n";
// dbDelta($command);
$subscriber_events_table = SendPress_DB_Tables::subscriber_event_table();
$command .= " CREATE TABLE {$subscriber_events_table} (\neventID int(11) unsigned NOT NULL AUTO_INCREMENT, \nsubscriberID int(11) unsigned NOT NULL, \nreportID int(11) unsigned DEFAULT NULL, \nurlID int(11) unsigned DEFAULT NULL, \nlistID int(11) unsigned DEFAULT NULL, \neventdate datetime NOT NULL DEFAULT '0000-00-00 00:00:00', \nip varchar(400) DEFAULT NULL, \ndevicetype varchar(50) DEFAULT NULL, \ndevice varchar(50) DEFAULT NULL, \ntype varchar(50) DEFAULT NULL, \nPRIMARY KEY (eventID), \nKEY subscriberID (subscriberID), \nKEY reportID (reportID), \nKEY urlID (urlID), \nKEY listID (listID), \nKEY eventdate (eventdate), \nKEY type (type)\n) {$collate};\n";
//dbDelta($command);
$report_url_table = SendPress_DB_Tables::report_url_table();
$command .= " CREATE TABLE {$report_url_table} (\nurlID int(11) unsigned NOT NULL AUTO_INCREMENT, \nurl varchar(2000) DEFAULT NULL, \nreportID int(11) DEFAULT NULL, \nPRIMARY KEY (urlID), \nKEY reportID (reportID), \nKEY url (url(255))\n) {$collate};\n";
// dbDelta($command);
$subscriber_status_table = SendPress_DB_Tables::subscriber_status_table();
$command .= " CREATE TABLE {$subscriber_status_table} (\nstatusid int(11) unsigned NOT NULL AUTO_INCREMENT, \nstatus varchar(255) DEFAULT NULL, \nPRIMARY KEY (statusid)\n) {$collate};\n";
/*
$subscriber_tracker_table = SendPress_DB_Tables::subscriber_tracker_table();
$command .= " CREATE TABLE $subscriber_tracker_table (
subscriberID int(11) unsigned NOT NULL,
emailID int(11) unsigned NOT NULL,
sent_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
opened_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
status tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (subscriberID,emailID)
) $collate;\n";
*/
/*
$subscriber_url_table = SendPress_DB_Tables::subscriber_url_table();
$command .= " CREATE TABLE $subscriber_url_table (
subscriberID int(11) unsigned NOT NULL,
emailID int(11) unsigned NOT NULL,
urlID int(11) unsigned NOT NULL,
clicked_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
click_count int(11) unsigned NOT NULL,
PRIMARY KEY ( subscriberID , emailID , urlID )
) $collate;\n";
*/
/*
$url_table = SendPress_DB_Tables::url_table();
$command .= " CREATE TABLE $url_table (
urlID int(11) unsigned NOT NULL AUTO_INCREMENT,
url text,
hash varchar(255) DEFAULT NULL,
PRIMARY KEY (urlID),
KEY hash (hash)
) $collate;\n";
*/
dbDelta($command);
$unconfirmed = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$subscriber_status_table} WHERE statusid = %d", 1));
if ($unconfirmed != null) {
$wpdb->update($subscriber_status_table, array('status' => 'Unconfirmed'), array('statusid' => 1), array('%s'), array('%d'));
} else {
$wpdb->insert($subscriber_status_table, array('statusid' => 1, 'status' => 'Unconfirmed'), array('%d', '%s'));
}
$active = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$subscriber_status_table} WHERE statusid = %d", 2));
if ($active != null) {
$wpdb->update($subscriber_status_table, array('status' => 'Active'), array('statusid' => 2), array('%s'), array('%d'));
} else {
$wpdb->insert($subscriber_status_table, array('statusid' => 2, 'status' => 'Active'), array('%d', '%s'));
}
$unsubscribed = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$subscriber_status_table} WHERE statusid = %d", 3));
if ($unsubscribed != null) {
$wpdb->update($subscriber_status_table, array('status' => 'Unsubscribed'), array('statusid' => 3), array('%s'), array('%d'));
} else {
$wpdb->insert($subscriber_status_table, array('statusid' => 3, 'status' => 'Unsubscribed'), array('%d', '%s'));
}
$bounced = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$subscriber_status_table} WHERE statusid = %d", 4));
if ($bounced != null) {
$wpdb->update($subscriber_status_table, array('status' => 'Bounced'), array('statusid' => 4), array('%s'), array('%d'));
} else {
$wpdb->insert($subscriber_status_table, array('statusid' => 4, 'status' => 'Bounced'), array('%d', '%s'));
}
}
