prefix . $t ); $wpdb->query( "DROP TABLE IF EXISTS `$table`" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- the plugin's own tables, named here } foreach ( array( 'chatdozo_settings', 'chatdozo_presence', 'chatdozo_banned', 'chatdozo_db_version', 'chatdozo_pro_db_version', 'chatdozo_version', 'chatdozo_ai_key_checked', 'chatdozo_ai_key_auto', 'chatdozo_ai_key_error', 'chatdozo_vapid', 'chatdozo_flush', 'chatdozo_weekly_report' ) as $o ) { delete_option( $o ); } foreach ( array( 'chatdozo_ai_key_backoff', 'chatdozo_watching', 'chatdozo_vprune', 'chatdozo_ai_usage', 'chatdozo_arrive', 'chatdozo_operators', 'chatdozo_pruned' ) as $t ) { delete_transient( $t ); } $wpdb->query( "DELETE FROM {$wpdb->options} WHERE option_name LIKE '\_transient\_chatdozo\_rl\_%' OR option_name LIKE '\_transient\_timeout\_chatdozo\_rl\_%' OR option_name LIKE '\_transient\_chatdozo\_push\_%' OR option_name LIKE '\_transient\_timeout\_chatdozo\_push\_%'" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the plugin's own transients, on uninstall remove_role( 'chatdozo_operator' ); $admin = get_role( 'administrator' ); if ( $admin ) { $admin->remove_cap( 'chatdozo_operate' ); } foreach ( get_users( array( 'capability' => 'chatdozo_operate', 'fields' => array( 'ID' ), 'number' => 2000 ) ) as $u ) { $user = get_user_by( 'id', $u->ID ); if ( $user ) { $user->remove_cap( 'chatdozo_operate' ); } } foreach ( array( 'chatdozo_avatar', 'chatdozo_name', 'chatdozo_away', 'chatdozo_push', 'chatdozo_title', 'chatdozo_arrivals', 'chatdozo_reco_hide', 'chatdozo_pro_seen' ) as $m ) { delete_metadata( 'user', 0, $m, '', true ); } } public static function deactivate() { do_action( 'chatdozo_deactivate' ); // the Pro edition unschedules its Monday email; data stays until uninstall } public static function maybe_upgrade() { if ( get_option( 'chatdozo_version' ) === CHATDOZO_VERSION && get_option( 'chatdozo_db_version' ) === self::DB_VERSION ) { return; } self::migrate_prefix(); if ( get_option( 'chatdozo_db_version' ) !== self::DB_VERSION ) { self::tables(); update_option( 'chatdozo_db_version', self::DB_VERSION, false ); } self::migrate_tables(); $from = (string) get_option( 'chatdozo_version', '0' ); if ( $from !== CHATDOZO_VERSION ) { if ( version_compare( $from, '0.12.1', '<' ) ) { // before 0.12.1 "the logo" meant "the AI when it is on"; that choice is now its own tile, so carry it over $o = get_option( Options::KEY ); if ( is_array( $o ) && empty( $o['greeter'] ) ) { $o['greeter'] = -1; update_option( Options::KEY, $o ); } } if ( version_compare( $from, '0.13.0', '<' ) ) { self::fold_one_seat(); } Options::seed(); Roles::ensure(); Roles::forget(); update_option( 'chatdozo_version', CHATDOZO_VERSION, false ); update_option( 'chatdozo_flush', 1, false ); // the /chatdozo-app/ addresses (Pro) need the rewrite rules refreshed once do_action( 'chatdozo_upgraded', $from, CHATDOZO_VERSION ); } } /** * 0.13.0: everything the plugin stores is now prefixed chatdozo_ (it was cdz_). A site that ran an older * version keeps its data: the tables are renamed, the options and user meta are moved. Runs once; a fresh * install finds nothing to move. */ public static function migrate_prefix() { global $wpdb; if ( get_option( 'cdz_version' ) === false ) { return; } // nothing from before 0.13.0 here // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- a one-time rename of the plugin's own option and meta keys foreach ( array( 'settings', 'presence', 'banned', 'db_version', 'ai_key_checked', 'ai_key_auto', 'ai_key_error', 'vapid', 'flush', 'weekly_report' ) as $k ) { $v = get_option( 'cdz_' . $k, null ); if ( $v === null ) { continue; } if ( get_option( 'chatdozo_' . $k, null ) === null ) { add_option( 'chatdozo_' . $k, $v, '', $k === 'settings' ? 'yes' : 'no' ); } delete_option( 'cdz_' . $k ); } $keys = array( 'cdz_avatar' => 'chatdozo_avatar', 'cdz_name' => 'chatdozo_name', 'cdz_title' => 'chatdozo_title', 'cdz_away' => 'chatdozo_away', 'cdz_push' => 'chatdozo_push', 'cdz_arrivals' => 'chatdozo_arrivals', 'cdz_reco_hide' => 'chatdozo_reco_hide', 'cdz_trial_seen' => 'chatdozo_pro_seen' ); $in = implode( ', ', array_fill( 0, count( $keys ), '%s' ) ); $rows = $wpdb->get_results( $wpdb->prepare( "SELECT umeta_id, user_id, meta_key FROM {$wpdb->usermeta} WHERE meta_key IN ($in)", array_keys( $keys ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- $in is only %s placeholders, one per key foreach ( (array) $rows as $r ) { $to = $keys[ $r->meta_key ]; if ( metadata_exists( 'user', (int) $r->user_id, $to ) ) { $wpdb->delete( $wpdb->usermeta, array( 'umeta_id' => (int) $r->umeta_id ) ); } else { $wpdb->update( $wpdb->usermeta, array( 'meta_key' => $to ), array( 'umeta_id' => (int) $r->umeta_id ) ); } } $wpdb->update( $wpdb->postmeta, array( 'meta_key' => '_chatdozo_prompt' ), array( 'meta_key' => '_cdz_prompt' ) ); $wpdb->query( "DELETE FROM {$wpdb->options} WHERE option_name LIKE '\_transient\_cdz\_%' OR option_name LIKE '\_transient\_timeout\_cdz\_%'" ); // phpcs:enable $next = wp_next_scheduled( 'cdz_weekly_report' ); if ( $next ) { wp_unschedule_event( $next, 'cdz_weekly_report' ); } if ( get_option( 'chatdozo_version' ) === false ) { add_option( 'chatdozo_version', get_option( 'cdz_version' ), '', 'no' ); } delete_option( 'cdz_version' ); // done once; this is the switch wp_cache_flush(); } /** * The tables from before 0.13.0 (cdz_*) become chatdozo_*. Renamed in place where the database allows it; * where it does not (SQLite behind Playground, a host that withholds ALTER) the rows are copied into the new * table, which tables() has made by then, and the old table is dropped once the counts agree. Runs after * tables() on activation and upgrades; four SHOW TABLES when there is nothing to do. */ public static function migrate_tables() { global $wpdb; static $busy = false; if ( $busy ) { return; } // tables() below fires chatdozo_tables, whose Pro listener calls back here $busy = true; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared -- a one-time move of the plugin's own tables; the names are built from $wpdb->prefix and fixed words foreach ( array( 'conversations', 'messages', 'knowledge', 'visitors' ) as $t ) { $old = esc_sql( $wpdb->prefix . 'cdz_' . $t ); $new = esc_sql( $wpdb->prefix . 'chatdozo_' . $t ); if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $old ) ) !== $old ) { continue; } $has_new = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $new ) ) === $new; if ( $has_new && (int) $wpdb->get_var( "SELECT COUNT(*) FROM `$new`" ) > 0 ) { continue; } // the new table is in use; the old one is left alone, nothing is lost if ( $has_new ) { $wpdb->query( "DROP TABLE `$new`" ); } $wpdb->query( "ALTER TABLE `$old` RENAME TO `$new`" ); // MySQL, MariaDB and SQLite all take this form if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $old ) ) !== $old ) { continue; } // renamed // the rename did not take: make the new table and copy the rows across if ( $has_new ) { self::tables(); } if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $new ) ) !== $new ) { continue; } // the Pro tables are made by the Pro edition; they wait for it $cols = array_values( array_intersect( (array) $wpdb->get_col( $wpdb->prepare( 'SHOW COLUMNS FROM %i', $old ) ), (array) $wpdb->get_col( $wpdb->prepare( 'SHOW COLUMNS FROM %i', $new ) ) ) ); if ( ! $cols ) { continue; } // table and column names go in as %i identifiers (WordPress 6.2+), so the statement is fully prepared $ph = implode( ', ', array_fill( 0, count( $cols ), '%i' ) ); $args = array_merge( array( $new ), $cols, $cols, array( $old ) ); $wpdb->query( $wpdb->prepare( "INSERT INTO %i ($ph) SELECT $ph FROM %i", $args ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $ph is only %i placeholders, one per column, and $args lines up with them if ( (int) $wpdb->get_var( "SELECT COUNT(*) FROM `$new`" ) === (int) $wpdb->get_var( "SELECT COUNT(*) FROM `$old`" ) ) { $wpdb->query( "DROP TABLE `$old`" ); } } // phpcs:enable $busy = false; } /** 0.13.0: the free edition's one seat became the whole team. The person who held the seat joins the operators. */ private static function fold_one_seat() { $o = get_option( Options::KEY ); if ( ! is_array( $o ) ) { return; } $one = isset( $o['operator_one'] ) ? (int) $o['operator_one'] : 0; unset( $o['operator_one'] ); $ops = array_map( 'intval', (array) ( $o['operators'] ?? array() ) ); if ( $one && get_user_by( 'id', $one ) && ! in_array( $one, $ops, true ) ) { $ops[] = $one; } $o['operators'] = array_values( array_unique( array_filter( $ops ) ) ); update_option( Options::KEY, $o, true ); Roles::sync_operators( $o['operators'] ); } public static function tables() { global $wpdb; require_once ABSPATH . 'wp-admin/includes/upgrade.php'; $charset = $wpdb->get_charset_collate(); $c = $wpdb->prefix . 'chatdozo_conversations'; $m = $wpdb->prefix . 'chatdozo_messages'; dbDelta( "CREATE TABLE $c ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, token char(40) NOT NULL DEFAULT '', status varchar(10) NOT NULL DEFAULT 'open', visitor_name varchar(120) NOT NULL DEFAULT '', visitor_email varchar(190) NOT NULL DEFAULT '', page_url text NULL, page_title varchar(200) NOT NULL DEFAULT '', referrer text NULL, source varchar(60) NOT NULL DEFAULT '', device varchar(20) NOT NULL DEFAULT '', ua varchar(255) NOT NULL DEFAULT '', ip_hash char(40) NOT NULL DEFAULT '', country varchar(2) NOT NULL DEFAULT '', region varchar(80) NOT NULL DEFAULT '', city varchar(80) NOT NULL DEFAULT '', ip varchar(45) NOT NULL DEFAULT '', lang varchar(16) NOT NULL DEFAULT '', tz varchar(64) NOT NULL DEFAULT '', browser varchar(40) NOT NULL DEFAULT '', os varchar(40) NOT NULL DEFAULT '', tags varchar(255) NOT NULL DEFAULT '', rating tinyint(1) NOT NULL DEFAULT 0, rating_note varchar(500) NOT NULL DEFAULT '', banned tinyint(1) NOT NULL DEFAULT 0, visitor_draft varchar(500) NOT NULL DEFAULT '', transcript_at datetime NULL, assigned_to bigint(20) unsigned NOT NULL DEFAULT 0, last_message_id bigint(20) unsigned NOT NULL DEFAULT 0, last_message_at datetime NULL, last_from varchar(10) NOT NULL DEFAULT '', visitor_read_id bigint(20) unsigned NOT NULL DEFAULT 0, operator_read_id bigint(20) unsigned NOT NULL DEFAULT 0, visitor_seen_at datetime NULL, visitor_typing_at datetime NULL, operator_typing_at datetime NULL, operator_typing_user bigint(20) unsigned NOT NULL DEFAULT 0, visits int(10) unsigned NOT NULL DEFAULT 1, ai tinyint(1) NOT NULL DEFAULT 0, ai_turns smallint(5) unsigned NOT NULL DEFAULT 0, alerted_at datetime NULL, closed_at datetime NULL, created_at datetime NOT NULL, updated_at datetime NOT NULL, PRIMARY KEY (id), KEY token (token), KEY status_updated (status, updated_at), KEY assigned (assigned_to), KEY last_message_at (last_message_at) ) $charset;" ); dbDelta( "CREATE TABLE $m ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, conversation_id bigint(20) unsigned NOT NULL, sender varchar(10) NOT NULL DEFAULT 'visitor', user_id bigint(20) unsigned NOT NULL DEFAULT 0, body text NOT NULL, attachment varchar(500) NOT NULL DEFAULT '', attachment_type varchar(10) NOT NULL DEFAULT '', attachment_name varchar(120) NOT NULL DEFAULT '', emailed tinyint(1) NOT NULL DEFAULT 0, created_at datetime NOT NULL, PRIMARY KEY (id), KEY conv (conversation_id, id) ) $charset;" ); do_action( 'chatdozo_tables', $charset ); // the Pro edition adds its own } }