prefix . $table; } /** * Create any missing tables/columns on existing installs after an update * (no reactivation needed). dbDelta is safe to re-run — it only adds what * is missing. Cheap version-gated so it's effectively a no-op once current. */ public static function maybe_upgrade() { if ( get_option( self::VERSION_OPTION ) === self::DB_VERSION ) { return; } self::ensure_tables(); update_option( self::VERSION_OPTION, self::DB_VERSION ); } /** * Create all plugin tables via dbDelta, under the names table() gives. * * Safe to call repeatedly — dbDelta only creates missing tables * and adds missing columns. Works with both MySQL and SQLite. */ public static function ensure_tables() { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; foreach ( array_keys( self::columns() ) as $table ) { dbDelta( self::create_sql( $table, self::table( $table ) ) ); } } /** * The CREATE TABLE statement for one table, in dbDelta's format. * * @param string $table Table constant, without the prefix. * @param string $name Full table name to create it under. * @return string */ public static function create_sql( $table, $name ) { global $wpdb; $columns = self::columns(); return "CREATE TABLE {$name} (\n{$columns[ $table ]}) {$wpdb->get_charset_collate()};"; } /** * Every table's columns and keys, by table constant. * * @return array */ private static function columns() { return array( self::CAMPAIGNS => "id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, title VARCHAR(255) NOT NULL DEFAULT '', subject VARCHAR(255) DEFAULT NULL, preheader VARCHAR(255) DEFAULT NULL, status VARCHAR(20) NOT NULL DEFAULT 'draft', audience_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, audience_type VARCHAR(20) NOT NULL DEFAULT '', content_html LONGTEXT DEFAULT NULL, content_blocks LONGTEXT DEFAULT NULL, email_type VARCHAR(20) NOT NULL DEFAULT 'html', type VARCHAR(20) NOT NULL DEFAULT 'broadcast', sender_name VARCHAR(255) DEFAULT NULL, sender_email VARCHAR(255) DEFAULT NULL, reply_to VARCHAR(255) DEFAULT NULL, scheduled_at DATETIME DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, sent_at DATETIME DEFAULT NULL, PRIMARY KEY (id), KEY user_id (user_id), KEY status (status), KEY type (type) ", self::CAMPAIGN_SENDS => "id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, campaign_id BIGINT(20) UNSIGNED NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'pending', total_recipients INT(11) UNSIGNED NOT NULL DEFAULT 0, sent_count INT(11) UNSIGNED NOT NULL DEFAULT 0, failed_count INT(11) UNSIGNED NOT NULL DEFAULT 0, scheduled_at DATETIME DEFAULT NULL, rate_limit INT(11) UNSIGNED NOT NULL DEFAULT 10, batch_size SMALLINT(5) UNSIGNED NOT NULL DEFAULT 10, batch_pause INT(11) UNSIGNED NOT NULL DEFAULT 5, open_tracking TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, source VARCHAR(20) NOT NULL DEFAULT 'broadcast', source_ref VARCHAR(64) NOT NULL DEFAULT '', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY campaign_id (campaign_id), KEY status (status), KEY source_ref (source_ref) ", self::SEND_QUEUE => "id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, batch_id BIGINT(20) UNSIGNED NOT NULL, contact_id BIGINT(20) UNSIGNED NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'pending', claim_token VARCHAR(36) DEFAULT NULL, error_message TEXT DEFAULT NULL, sent_at DATETIME DEFAULT NULL, PRIMARY KEY (id), KEY batch_id (batch_id), KEY contact_id (contact_id), KEY status (status), KEY batch_status (batch_id, status), KEY sent_at (sent_at) ", self::SUBSCRIBERS => "id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, email VARCHAR(200) NOT NULL DEFAULT '', first_name VARCHAR(100) DEFAULT NULL, last_name VARCHAR(100) DEFAULT NULL, status VARCHAR(20) NOT NULL DEFAULT 'subscribed', customer_type VARCHAR(20) DEFAULT NULL, gross_revenue DECIMAL(10,2) DEFAULT NULL, source VARCHAR(100) DEFAULT NULL, unsubscribe_token VARCHAR(64) DEFAULT NULL, email_hash CHAR(64) NOT NULL DEFAULT '', confirm_hash CHAR(64) NOT NULL DEFAULT '', confirmed_at DATETIME DEFAULT NULL, consent_source VARCHAR(100) NOT NULL DEFAULT '', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY email (email), UNIQUE KEY unsubscribe_token (unsubscribe_token), KEY status (status), KEY email_hash (email_hash), KEY consent_source (consent_source) ", self::LISTS => "id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(200) NOT NULL DEFAULT '', description TEXT DEFAULT NULL, visibility VARCHAR(10) NOT NULL DEFAULT 'internal', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY name (name) ", self::SUBSCRIBER_LISTS => 'contact_id BIGINT(20) UNSIGNED NOT NULL, group_id BIGINT(20) UNSIGNED NOT NULL, added_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (contact_id, group_id), KEY group_id (group_id) ', self::SEGMENTS => "id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(200) NOT NULL DEFAULT '', filter LONGTEXT DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY name (name) ", self::TRACKING => 'id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, batch_id BIGINT(20) UNSIGNED NOT NULL, contact_id BIGINT(20) UNSIGNED NOT NULL, opened_at DATETIME DEFAULT NULL, open_count INT(11) UNSIGNED NOT NULL DEFAULT 0, click_count INT(11) UNSIGNED NOT NULL DEFAULT 0, unsubscribed_at DATETIME DEFAULT NULL, last_clicked_at DATETIME DEFAULT NULL, tracked TINYINT(1) UNSIGNED NOT NULL DEFAULT 1, PRIMARY KEY (id), UNIQUE KEY batch_contact (batch_id, contact_id), KEY contact_id (contact_id) ', // GDPR suppression list — opted-out / erased emails that must never be // re-added. Stores a SHA-256 of the // lowercased email, not the raw address, to keep it minimal/pseudonymous. self::SUPPRESSIONS => "email_hash CHAR(64) NOT NULL, reason VARCHAR(20) NOT NULL DEFAULT '', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (email_hash) ", // Arbitrary per-contact custom fields (postmeta-shaped). The set of keys // users have defined lives in the `mailpuff_custom_fields` option; this // table holds the values. Single-value-per-key in practice (set_meta // upserts), but no UNIQUE on (contact_id, meta_key) keeps it flexible. self::SUBSCRIBER_META => "id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, contact_id BIGINT(20) UNSIGNED NOT NULL, meta_key VARCHAR(191) NOT NULL DEFAULT '', meta_value LONGTEXT DEFAULT NULL, PRIMARY KEY (id), KEY contact_meta (contact_id, meta_key) ", // One row per CSV import run. Drives the async (Action Scheduler) chunked // importer and the progress UI. `mapping`/`options`/`rules` are JSON; the // uploaded file at `file_path` is deleted once the job completes. self::IMPORT_JOBS => "id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, file_path TEXT DEFAULT NULL, file_offset BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, mapping LONGTEXT DEFAULT NULL, options LONGTEXT DEFAULT NULL, rules LONGTEXT DEFAULT NULL, total INT(11) UNSIGNED NOT NULL DEFAULT 0, processed INT(11) UNSIGNED NOT NULL DEFAULT 0, imported INT(11) UNSIGNED NOT NULL DEFAULT 0, updated INT(11) UNSIGNED NOT NULL DEFAULT 0, skipped INT(11) UNSIGNED NOT NULL DEFAULT 0, failed INT(11) UNSIGNED NOT NULL DEFAULT 0, status VARCHAR(20) NOT NULL DEFAULT 'pending', errors LONGTEXT DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY status (status) ", // The links of a campaign that someone clicked, one row per URL, and // who clicked each and how often. Personal data: the exporter and the // eraser include link clicks. self::LINKS => 'id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, campaign_id BIGINT(20) UNSIGNED NOT NULL, url_hash CHAR(64) NOT NULL, url TEXT NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY campaign_url (campaign_id, url_hash) ', self::LINK_CLICKS => 'link_id BIGINT(20) UNSIGNED NOT NULL, contact_id BIGINT(20) UNSIGNED NOT NULL, clicks INT(11) UNSIGNED NOT NULL DEFAULT 0, first_at DATETIME DEFAULT NULL, PRIMARY KEY (link_id, contact_id), KEY contact_id (contact_id) ', ); } /** * Upsert a single custom-field value for a contact (one value per key). * * @param int $contact_id Contact ID. * @param string $key Meta key. * @param mixed $value Meta value (stored as string). */ public static function set_meta( $contact_id, $key, $value ) { global $wpdb; $table = self::table( self::SUBSCRIBER_META ); $key = sanitize_key( $key ); if ( ! $contact_id || '' === $key ) { return; } $existing = $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE contact_id = %d AND meta_key = %s', $table, (int) $contact_id, $key ) ); if ( $existing ) { $wpdb->update( $table, array( 'meta_value' => (string) $value ), array( 'id' => (int) $existing ), array( '%s' ), array( '%d' ) ); return; } $wpdb->insert( $table, array( 'contact_id' => (int) $contact_id, 'meta_key' => $key, 'meta_value' => (string) $value, ), array( '%d', '%s', '%s' ) ); } /** * Read a custom-field value for a contact. * * @param int $contact_id Contact ID. * @param string $key Meta key. * @return string|null */ public static function get_meta( $contact_id, $key ) { global $wpdb; $table = self::table( self::SUBSCRIBER_META ); return $wpdb->get_var( $wpdb->prepare( 'SELECT meta_value FROM %i WHERE contact_id = %d AND meta_key = %s', $table, (int) $contact_id, sanitize_key( $key ) ) ); } /** * Delete all custom-field values for a contact (used when a contact is removed). * * @param int $contact_id Contact ID. */ public static function delete_meta( $contact_id ) { global $wpdb; $table = self::table( self::SUBSCRIBER_META ); $wpdb->delete( $table, array( 'contact_id' => (int) $contact_id ), array( '%d' ) ); } /** * Delete one value of a contact's. * * @param int $contact_id Contact ID. * @param string $key Meta key. */ public static function delete_meta_key( $contact_id, $key ) { global $wpdb; $wpdb->delete( self::table( self::SUBSCRIBER_META ), array( 'contact_id' => (int) $contact_id, 'meta_key' => sanitize_key( $key ), // phpcs:ignore WordPress.DB.SlowDBQuery.slow_query_meta_key -- Mailpuff's own meta table, by contact. ), array( '%d', '%s' ) ); } /** * SHA-256 of a normalised (trimmed, lowercased) email — the key used in * the suppression list. * * @param string $email Raw email address. * @return string 64-char hex hash. */ public static function email_hash( $email ) { return hash( 'sha256', strtolower( trim( (string) $email ) ) ); } /** * Add an email to the suppression list (idempotent). * * @param string $email Raw email. * @param string $reason Short reason, e.g. 'unsubscribed' or 'erased'. */ public static function suppress( $email, $reason = '' ) { $reason = substr( (string) $reason, 0, 20 ); // An unsubscribe never replaces a stronger reason (a bounce, complaint // or erasure): only an `unsubscribed` row can be lifted by a confirm. $set_sql = 'unsubscribed' === $reason ? "reason = CASE WHEN reason = '' THEN %s ELSE reason END" : 'reason = %s'; self::upsert( self::SUPPRESSIONS, array( 'email_hash' => self::email_hash( $email ), 'reason' => $reason, ), array( 'email_hash' ), $set_sql, array( $reason ) ); } /** * Insert a row, or update it when its key is already there. The portable * stand-in for `INSERT … ON DUPLICATE KEY UPDATE`, which SQLite does not run. * * It updates first, so a counter change (`n = n + 1`) is one atomic * statement and two requests never lose an increment. A row that exists but * did not change (an update that sets nothing new) is left as it is. Only a * missing row is inserted, and when two requests insert the same key the * loser updates instead. * * @param string $table Table constant, without the prefix. * @param array $row Column => value for a new row. Key columns included. * @param string[] $keys The columns that identify the row. * @param string $set_sql The SET clause for an existing row, with placeholders. Trusted code, not input. * @param array $set_args Values for the placeholders in $set_sql. * @return void */ public static function upsert( $table, array $row, array $keys, $set_sql, array $set_args = array() ) { global $wpdb; $full = self::table( $table ); $where = array(); $args = array(); foreach ( $keys as $key ) { $where[] = '%i = ' . ( is_int( $row[ $key ] ) ? '%d' : '%s' ); $args[] = $key; $args[] = $row[ $key ]; } $where = implode( ' AND ', $where ); // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- $set_sql and $where are trusted fragments with their own placeholders. $update = static function () use ( $wpdb, $full, $set_sql, $set_args, $where, $args ) { $wpdb->query( $wpdb->prepare( "UPDATE %i SET {$set_sql} WHERE {$where}", array_merge( array( $full ), $set_args, $args ) ) ); return (int) $wpdb->rows_affected; }; if ( $update() > 0 ) { return; } // Matched but nothing to change, or no row yet. if ( $wpdb->get_var( $wpdb->prepare( "SELECT 1 FROM %i WHERE {$where}", array_merge( array( $full ), $args ) ) ) ) { return; } // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare $formats = array(); foreach ( $row as $value ) { $formats[] = is_int( $value ) ? '%d' : '%s'; } $suppress = $wpdb->suppress_errors( true ); $inserted = $wpdb->insert( $full, $row, $formats ); $wpdb->suppress_errors( $suppress ); if ( false === $inserted ) { // Someone else inserted this key between our check and the insert. $update(); } } /** * Why an email is on the suppression list. * * @param string $email Raw email. * @return string The reason (unsubscribed, bounced, complained, erased), or '' when not suppressed. */ public static function suppression_reason( $email ) { global $wpdb; $table = self::table( self::SUPPRESSIONS ); $reason = $wpdb->get_var( $wpdb->prepare( 'SELECT reason FROM %i WHERE email_hash = %s', $table, self::email_hash( $email ) ) ); return null === $reason ? '' : (string) $reason; } /** * Lift a suppression, but only when it has the given reason. A bounce, a * complaint or an erasure is never lifted this way. * * @param string $email Raw email. * @param string $reason The only reason that may be lifted. * @return bool True when a row was removed. */ public static function unsuppress( $email, $reason ) { global $wpdb; return (bool) $wpdb->delete( self::table( self::SUPPRESSIONS ), array( 'email_hash' => self::email_hash( $email ), 'reason' => substr( (string) $reason, 0, 20 ), ), array( '%s', '%s' ) ); } /** * Whether an email is on the suppression list. * * @param string $email Raw email. * @return bool */ public static function is_suppressed( $email ) { global $wpdb; $table = self::table( self::SUPPRESSIONS ); return (bool) $wpdb->get_var( $wpdb->prepare( 'SELECT 1 FROM %i WHERE email_hash = %s', $table, self::email_hash( $email ) ) ); } }