get_blog_prefix( $blog_id ) . self::TABLE; return $tables; } /** * The names in $tables that are queue tables of the network with the * table prefix $base_prefix. Uninstall drops them. The list holds the table * of site 1 and the table of each site with a number. * * Site 1 has no number in its prefix, so a numbered table needs an id of * 2 or more. Another install in the same database can use a prefix such * as wp_7_. So a numbered table must also have an id no higher than * $max_id, the highest site id of the network. Null means no limit. */ public static function network_tables( array $tables, $base_prefix, $max_id = null ) { $pattern = '/^' . preg_quote( $base_prefix, '/' ) . '(?:([1-9]\d*)_)?' . preg_quote( self::TABLE, '/' ) . '$/i'; $out = array(); foreach ( $tables as $table ) { if ( ! preg_match( $pattern, (string) $table, $match ) ) { continue; } if ( isset( $match[1] ) && '' !== $match[1] ) { $id = (int) $match[1]; if ( $id < 2 || ( null !== $max_id && $id > (int) $max_id ) ) { continue; } } $out[] = (string) $table; } return $out; } public static function table() { global $wpdb; return $wpdb->prefix . self::TABLE; } public static function maybe_install() { if ( (int) get_option( self::DB_OPTION, 0 ) >= self::DB_VERSION ) { return; } if ( get_transient( self::RETRY ) ) { add_action( 'admin_notices', array( __CLASS__, 'copy_notice' ) ); return; } if ( ! self::install() ) { add_action( 'admin_notices', array( __CLASS__, 'missing_notice' ) ); } elseif ( (int) get_option( self::DB_OPTION, 0 ) < self::DB_VERSION ) { add_action( 'admin_notices', array( __CLASS__, 'copy_notice' ) ); } } /** * Create or update the table, then move 1.1.0's queued ids into it. * True if the table then exists. install() saves the schema version * only after all of 1.1.0's data is in the table, so a failed copy runs again. */ public static function install() { if ( self::$installing || isset( self::$failed[ self::table() ] ) ) { return false; } self::$installing = true; try { return self::create(); } finally { self::$installing = false; } } private static function create() { global $wpdb; require_once ABSPATH . 'wp-admin/includes/upgrade.php'; $table = self::table(); // Two requests can create the table at the same time. Then the // CREATE TABLE of one of them fails with "already exists". That is // not an error, so dbDelta() runs quietly and exists() decides. $quiet = $wpdb->suppress_errors( true ); $errors = self::error_count(); $error = ''; $sql = ''; if ( self::exists() && ! self::has_seq() ) { // A table of an earlier build has no seq. dbDelta() would add the // column and its key in two statements. MySQL refuses an // AUTO_INCREMENT column with no key, so one ALTER adds both. $wpdb->query( "ALTER TABLE {$wpdb->prefix}kothok_queue ADD COLUMN seq bigint(20) unsigned NOT NULL AUTO_INCREMENT AFTER object_id, ADD UNIQUE KEY seq (seq)" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange // Read the error before has_seq(): its query resets it. $alter_error = $wpdb->last_error; $alter_sql = $wpdb->last_query; // "Duplicate column": a parallel request added seq first. if ( '' !== $alter_error && ! self::has_seq() ) { $error = $alter_error; $sql = $alter_sql; } } // dbDelta() needs two spaces after PRIMARY KEY and none inside the // key list, or it adds the key again on every run. MySQL accepts an // AUTO_INCREMENT column only with an index on it. So seq is UNIQUE. if ( '' === $error ) { dbDelta( "CREATE TABLE {$table} ( kind varchar(16) NOT NULL, object_id bigint(20) unsigned NOT NULL, seq bigint(20) unsigned NOT NULL AUTO_INCREMENT, action varchar(8) NOT NULL DEFAULT '', version int(10) unsigned NOT NULL DEFAULT 1, noted_at int(10) unsigned NOT NULL DEFAULT 0, payload longtext NULL, PRIMARY KEY (kind,object_id), UNIQUE KEY seq (seq), KEY kind_noted (kind,noted_at) ) {$wpdb->get_charset_collate()};" ); $error = $wpdb->last_error; $sql = $wpdb->last_query; } $wpdb->suppress_errors( $quiet ); self::forget_errors( $errors ); if ( ! self::exists() || ! self::has_seq() ) { self::$failed[ $table ] = true; if ( '' !== $error ) { // print_error() names $wpdb->last_query. Put back the query // that failed, not the query of exists(). $wpdb->last_query = $sql; $wpdb->print_error( $error ); } return false; } if ( self::migrate() ) { update_option( self::DB_OPTION, self::DB_VERSION, true ); delete_transient( self::RETRY ); } else { set_transient( self::RETRY, 1, self::RETRY_TTL ); } return true; } /** True if the table has the seq column. */ private static function has_seq() { global $wpdb; return null !== $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM {$wpdb->prefix}kothok_queue LIKE %s", 'seq' ) ); } public static function exists() { global $wpdb; $table = self::table(); // With lower_case_table_names = 1, MySQL returns the name in lower case. return 0 === strcasecmp( $table, (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) ) ); } public static function missing_notice() { if ( ! current_user_can( 'manage_options' ) ) { return; } echo '

' . esc_html__( 'Kothok could not create its database table, so product and page changes are not synced. Check that the database user may create tables, then reload this page.', 'kothok' ) . '

'; } public static function copy_notice() { if ( ! current_user_can( 'manage_options' ) ) { return; } echo '

' . esc_html__( 'Kothok could not move the changes that the previous version queued into its database table. Kothok tries again soon. If PHP error logging is on, the log has the database error.', 'kothok' ) . '

'; } /** * 1.1.0 kept the queues in options. Copy them in once, then delete them. * migrate() deletes an option only after its copy is in the table. True when * all of them are copied. */ private static function migrate() { $ok = true; $products = get_option( Kothok_Products::DIRTY ); if ( false !== $products ) { $copied = true; if ( is_array( $products ) && ! empty( $products['ids'] ) ) { $gone = isset( $products['gone'] ) ? (array) $products['gone'] : array(); $copied = self::note( Kothok_Products::QUEUE, (array) $products['ids'], $gone ); } // The key of a failed batch: the retry must send it again. The old // option kept it in the same shape as Kothok_Products::state(). if ( $copied && is_array( $products ) && false === get_option( Kothok_Products::STATE ) && ! empty( $products['batch']['key'] ) && isset( $products['batch']['hash'] ) ) { $state = Kothok_Products::state(); $state['batch'] = $products['batch']; $state['attempt'] = isset( $products['attempt'] ) ? (int) $products['attempt'] : 0; Kothok_Products::save_state( $state ); } if ( $copied ) { delete_option( Kothok_Products::DIRTY ); } $ok = $ok && $copied; } $content = get_option( Kothok_Content::DIRTY ); if ( false !== $content ) { $copied = ! is_array( $content ) || ! $content || self::note( Kothok_Content::QUEUE, $content ); if ( $copied ) { delete_option( Kothok_Content::DIRTY ); } $ok = $ok && $copied; } // Copy the seen nonces too, or a request seen just before the update // could be sent again. Each row gets the current time, so it stays // a little longer than needed. That is safe. $nonces = get_option( Kothok_Orders::NONCES ); if ( false !== $nonces ) { $ids = array(); foreach ( array_keys( (array) $nonces ) as $nonce ) { $ids[ Kothok_Orders::nonce_id( (string) $nonce ) ] = ''; } $copied = self::note( Kothok_Orders::NONCE_QUEUE, $ids ); if ( $copied ) { delete_option( Kothok_Orders::NONCES ); } $ok = $ok && $copied; } return $ok; } /** * Queue objects: id => 'upsert' or 'delete'. One statement for all of them. * $payloads holds more data by id, stored as JSON. With $overwrite * false, an object already queued keeps its row as it is. False if the * query failed. */ public static function note( $kind, array $actions, array $payloads = array(), $overwrite = true ) { global $wpdb; if ( ! $actions ) { return true; } // Sort the ids. Then every note locks its rows in the same order. // The same order prevents most deadlocks between two notes. ksort( $actions ); $args = array(); $now = time(); foreach ( $actions as $id => $action ) { // An object with no payload sends '', and NULLIF stores it as NULL. array_push( $args, $kind, (int) $id, (string) $action, $now, isset( $payloads[ $id ] ) ? (string) wp_json_encode( $payloads[ $id ] ) : '' ); } // VALUES() in the UPDATE part works on MySQL and on MariaDB. The newer // row alias does not work on MariaDB. $result = self::run( function () use ( $wpdb, $args, $actions, $overwrite ) { if ( $overwrite ) { return $wpdb->query( $wpdb->prepare( "INSERT INTO {$wpdb->prefix}kothok_queue (kind, object_id, action, version, noted_at, payload) VALUES " . implode( ', ', array_fill( 0, count( $actions ), "(%s, %d, %s, 1, %d, NULLIF(%s, ''))" ) ) . ' ON DUPLICATE KEY UPDATE action = VALUES(action), version = version + 1, noted_at = VALUES(noted_at), payload = COALESCE(VALUES(payload), payload)', ...$args ) ); // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- one set of placeholders per row; $args holds one set of values per row. } return $wpdb->query( $wpdb->prepare( "INSERT INTO {$wpdb->prefix}kothok_queue (kind, object_id, action, version, noted_at, payload) VALUES " . implode( ', ', array_fill( 0, count( $actions ), "(%s, %d, %s, 1, %d, NULLIF(%s, ''))" ) ) . ' ON DUPLICATE KEY UPDATE object_id = object_id', ...$args ) ); // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- one set of placeholders per row; $args holds one set of values per row. } ); return false !== $result; } /** * Up to $limit queued objects, oldest first: * id => array( action, version, seq, payload ). The seq is a string, so * it stays exact on 32-bit PHP. */ public static function take( $kind, $limit ) { global $wpdb; $rows = self::run( function () use ( $wpdb, $kind, $limit ) { return $wpdb->get_results( $wpdb->prepare( "SELECT object_id, action, version, seq, payload FROM {$wpdb->prefix}kothok_queue WHERE kind = %s ORDER BY noted_at, object_id LIMIT %d", $kind, (int) $limit ) ); } ); $out = array(); foreach ( is_array( $rows ) ? $rows : array() as $row ) { $payload = null === $row->payload || '' === $row->payload ? null : json_decode( $row->payload, true ); $out[ (int) $row->object_id ] = array( 'action' => $row->action, 'version' => (int) $row->version, 'seq' => (string) $row->seq, 'payload' => is_array( $payload ) ? $payload : null, ); } return $out; } /** * Remove pushed rows. Remove a row only if nobody noted it again after * take(): the seq and the version must be the same as in take(). */ public static function done( $kind, array $taken ) { global $wpdb; if ( ! $taken ) { return; } ksort( $taken ); $args = array( $kind ); foreach ( $taken as $id => $row ) { array_push( $args, (int) $id, (string) $row['seq'], (int) $row['version'] ); } self::run( function () use ( $wpdb, $args, $taken ) { return $wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->prefix}kothok_queue WHERE kind = %s AND (" . implode( ' OR ', array_fill( 0, count( $taken ), '(object_id = %d AND seq = CAST(%s AS UNSIGNED) AND version = %d)' ) ) . ')', ...$args ) ); // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- one set of placeholders per row; $args holds one set of values per row. } ); } public static function has( $kind ) { global $wpdb; return (bool) self::run( function () use ( $wpdb, $kind ) { return $wpdb->get_var( $wpdb->prepare( "SELECT 1 FROM {$wpdb->prefix}kothok_queue WHERE kind = %s LIMIT 1", $kind ) ); } ); } public static function count( $kind ) { global $wpdb; return (int) self::run( function () use ( $wpdb, $kind ) { return $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$wpdb->prefix}kothok_queue WHERE kind = %s", $kind ) ); } ); } /** * Add 1 to the counter ($kind, $id) and return the new count. One statement * does it. LAST_INSERT_ID(expr) gives each request its own result, so * parallel requests get different counts. Returns 0 if the query failed. */ public static function bump( $kind, $id ) { global $wpdb; $affected = self::run( function () use ( $wpdb, $kind, $id ) { return $wpdb->query( $wpdb->prepare( "INSERT INTO {$wpdb->prefix}kothok_queue (kind, object_id, action, version, noted_at) VALUES (%s, %d, '', 1, %d) ON DUPLICATE KEY UPDATE version = LAST_INSERT_ID(version + 1)", $kind, (int) $id, time() ) ); } ); // MySQL counts 1 affected row for an insert and 2 for an update. if ( 2 === $affected ) { return (int) $wpdb->insert_id; } if ( 1 !== $affected ) { return 0; } // A new row is the first count. Remove the counters of older minutes. self::run( function () use ( $wpdb, $kind, $id ) { return $wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->prefix}kothok_queue WHERE kind = %s AND object_id < %d", $kind, (int) $id - 1 ) ); } ); return 1; } /** * Claim ($kind, $id) for $ttl seconds. True for exactly one caller in that * time. A claim made at T holds until T + $ttl, that second included. * * First INSERT IGNORE: 1 affected row is a new claim. Then an UPDATE gives * the row a new time, but only if the claim has ended. Of two parallel * updates, only the first one matches the row. If the UPDATE matches no * row, one more INSERT IGNORE runs. Each step counts only rows that it * adds or matches. So the CLIENT_FOUND_ROWS flag does not change the * result. * * @param string $kind Kind of the claim. * @param int $id Id of the claim. * @param int $ttl Seconds that the claim holds. * @param int|null $now The current time. Null: time(). A caller that * checked a time limit gives the same time here. * @return bool|null True: claimed. False: another caller holds it. Null: * a query failed, so the result is not known. */ public static function claim( $kind, $id, $ttl, $now = null ) { global $wpdb; $now = null === $now ? time() : (int) $now; $insert = function () use ( $wpdb, $kind, $id, $now ) { return $wpdb->query( $wpdb->prepare( "INSERT IGNORE INTO {$wpdb->prefix}kothok_queue (kind, object_id, action, version, noted_at) VALUES (%s, %d, '', 1, %d)", $kind, (int) $id, $now ) ); }; $inserted = self::run( $insert ); if ( false === $inserted ) { return null; } if ( 1 !== $inserted ) { $updated = self::run( function () use ( $wpdb, $kind, $id, $now, $ttl ) { return $wpdb->query( $wpdb->prepare( "UPDATE {$wpdb->prefix}kothok_queue SET noted_at = %d WHERE kind = %s AND object_id = %d AND noted_at < %d", $now, $kind, (int) $id, $now - (int) $ttl ) ); } ); if ( false === $updated ) { return null; } if ( 1 === $updated ) { return true; } // No match: another caller holds the claim, or another claim // removed the expired row between the two steps. Insert the row // again. If nobody holds the claim, this insert gets it. $inserted = self::run( $insert ); if ( 1 !== $inserted ) { return false === $inserted ? null : false; } } // A new row. Remove the claims of this kind that have ended. self::run( function () use ( $wpdb, $kind, $now, $ttl ) { return $wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->prefix}kothok_queue WHERE kind = %s AND noted_at < %d", $kind, $now - (int) $ttl ) ); } ); return true; } /** Remove the claim ($kind, $id), so the next caller gets it. */ public static function release( $kind, $id ) { global $wpdb; self::run( function () use ( $wpdb, $kind, $id ) { return $wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->prefix}kothok_queue WHERE kind = %s AND object_id = %d", $kind, (int) $id ) ); } ); } /** The current value of a counter, or 0 if there is no counter. */ public static function counter( $kind, $id ) { global $wpdb; return (int) self::run( function () use ( $wpdb, $kind, $id ) { return $wpdb->get_var( $wpdb->prepare( "SELECT version FROM {$wpdb->prefix}kothok_queue WHERE kind = %s AND object_id = %d", $kind, (int) $id ) ); } ); } /** Remove all rows of $kind. */ public static function clear( $kind ) { global $wpdb; self::run( function () use ( $wpdb, $kind ) { return $wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->prefix}kothok_queue WHERE kind = %s", $kind ) ); } ); } /** * Run one query of this class and return its result, or false if it * failed. If the table is missing, create it and run the query again. * The table is missing after an update until an admin page loads. After * a deadlock, run the query one more time. */ private static function run( callable $query ) { global $wpdb; $installed = false; $retried = false; while ( true ) { // Quiet: a missing table or a deadlock is not an error to show yet. $quiet = $wpdb->suppress_errors( true ); $errors = self::error_count(); $result = $query(); $wpdb->suppress_errors( $quiet ); $error = $wpdb->last_error; if ( '' === $error ) { return $result; } $sql = $wpdb->last_query; // wpdb adds each error to the $EZSQL_ERROR list, also a quiet one. // Remove the entry of this try. A final failure goes back on the // list once, below. The drop-in of Query Monitor keeps its own // list, which can still show the first try. self::forget_errors( $errors ); if ( ! $installed && ( self::missing( $error ) || self::old_schema( $error ) ) ) { $installed = true; if ( self::install() ) { continue; } } elseif ( ! $retried && self::deadlock( $error ) ) { $retried = true; continue; } // The error log gets the error as for any other query. With // show_errors on, wpdb also prints it, as it does for any query. // install() ran other queries. Put back the failed query first, // because print_error() names $wpdb->last_query. $wpdb->last_query = $sql; $wpdb->last_error = $error; $wpdb->print_error( $error ); return false; } } /** The number of entries in wpdb's error list ($EZSQL_ERROR). */ private static function error_count() { return isset( $GLOBALS['EZSQL_ERROR'] ) && is_array( $GLOBALS['EZSQL_ERROR'] ) ? count( $GLOBALS['EZSQL_ERROR'] ) : 0; } /** Remove the entries added to wpdb's error list after it had $count entries. */ private static function forget_errors( $count ) { if ( self::error_count() > $count ) { array_splice( $GLOBALS['EZSQL_ERROR'], $count ); } } /** True if the last query failed because the table does not exist (MySQL error 1146). */ private static function missing( $error ) { $errno = self::errno(); if ( $errno ) { return 1146 === $errno; } // A database drop-in with no mysqli link: read the message. return false !== stripos( $error, "doesn't exist" ) && false !== stripos( $error, self::table() ); } /** * True if the last query failed on the seq column (MySQL error 1054): a * table of an earlier build. install() then adds the column. */ private static function old_schema( $error ) { $errno = self::errno(); if ( $errno && 1054 !== $errno ) { return false; } return false !== stripos( $error, "Unknown column 'seq'" ); } /** True if MySQL stopped the last query to end a deadlock (MySQL error 1213). */ private static function deadlock( $error ) { $errno = self::errno(); if ( $errno ) { return 1213 === $errno; } return false !== stripos( $error, 'Deadlock found' ); } /** The MySQL error number of the last query, or 0 if wpdb has no mysqli link. */ private static function errno() { global $wpdb; if ( isset( $wpdb->dbh ) && $wpdb->dbh instanceof mysqli ) { return (int) $wpdb->dbh->errno; } return 0; } } // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching