elements. * * Filled after embedding, from the same text that was embedded; nothing leaves the site. */ final class LinkIndex { public const DB_VERSION = 2; // 2: aipcf_terms_df public const VERSION_OPTION = 'aipcf_links_db'; /** * Distinctive words kept per post: title words first, then the most used. Enough for anchors, * and it keeps the table at about 60 MB for 50,000 posts. */ private const MAX_TERMS = 25; /** When the word counts were last rebuilt (Unix time). */ private const DF_OPTION = 'aipcf_terms_df_at'; /** * Words too common or too generic to be an anchor. Anything in 40% of posts is dropped * anyway; these are the ones that survive that in small sites. */ private const STOP_WORDS = 'about above after again against also always another anything around because been before being below best better between both came come could does doing done down during each either else enough even every from further getting give given goes going good great have having here into itself just know last like little make makes making many might more most much must need needs never next only other ours over really said same should since some something still such take than that their theirs them themselves then there these they thing things think this those though through time times today under until very want ways well were what when where which while will with within without work works would year years your yours using used uses website websites site sites post posts page pages blog people first second help helps start started simple easy able already another since latest check checks read reading click list lists example examples however would could things someone everyone anyone something because'; public static function terms(): string { global $wpdb; return $wpdb->prefix . 'aipcf_terms'; } public static function df(): string { global $wpdb; return $wpdb->prefix . 'aipcf_terms_df'; } public static function links(): string { global $wpdb; return $wpdb->prefix . 'aipcf_links'; } public static function install(): void { if ((int) get_option(self::VERSION_OPTION) === self::DB_VERSION) { return; } global $wpdb; require_once ABSPATH . 'wp-admin/includes/upgrade.php'; $charset = $wpdb->get_charset_collate(); $terms = self::terms(); $links = self::links(); dbDelta("CREATE TABLE {$terms} ( object_id bigint(20) unsigned NOT NULL, term varchar(64) NOT NULL, PRIMARY KEY (term,object_id), KEY object (object_id) ) {$charset};"); $df = self::df(); dbDelta("CREATE TABLE {$df} ( term varchar(64) NOT NULL, n int(10) unsigned NOT NULL DEFAULT 0, PRIMARY KEY (term) ) {$charset};"); dbDelta("CREATE TABLE {$links} ( source_id bigint(20) unsigned NOT NULL, target_id bigint(20) unsigned NOT NULL, PRIMARY KEY (source_id,target_id), KEY target (target_id) ) {$charset};"); update_option(self::VERSION_OPTION, self::DB_VERSION, false); } public static function drop(): void { global $wpdb; $wpdb->query($wpdb->prepare('DROP TABLE IF EXISTS %i', self::terms())); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange $wpdb->query($wpdb->prepare('DROP TABLE IF EXISTS %i', self::links())); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange $wpdb->query($wpdb->prepare('DROP TABLE IF EXISTS %i', self::df())); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange delete_option(self::VERSION_OPTION); delete_option(self::DF_OPTION); delete_option(self::DF_OPTION . '_total'); delete_option(self::DF_OPTION . '_dirty'); } /** * Records posts whose terms and links are due, until the deadline. */ public static function refresh(float $deadline): int { self::install(); if (!Lock::acquire('links')) { return 0; // another run is recording them } try { return self::refreshLocked($deadline); } finally { Lock::release('links'); } } private static function refreshLocked(float $deadline): int { $done = 0; while (microtime(true) < $deadline) { $ids = IndexTable::linksDue(50); if ($ids === []) { break; } foreach ($ids as $id) { try { self::indexPost($id); } catch (\Throwable $e) { unset($e); // e.g. a third-party content filter: skip this post, not every later one } IndexTable::markLinked($id); $done++; if (microtime(true) >= $deadline) { break 2; } } } if ($done > 0) { update_option(self::DF_OPTION . '_dirty', 1, false); } $at = (int) get_option(self::DF_OPTION); // Hourly after changes, and daily anyway (deleted posts leave without a change here). if ((get_option(self::DF_OPTION . '_dirty') || $at < time() - DAY_IN_SECONDS) && $at < time() - HOUR_IN_SECONDS && IndexTable::linksDue(1) === []) { self::prune(); // posts no longer embedded leave the counts too self::rebuildCounts(); } return $done; } /** * How many posts use each word, in one grouped pass (a few seconds at 50,000 posts), so the * link planner reads counts instead of counting on every request. */ public static function rebuildCounts(): void { global $wpdb; self::install(); $wpdb->query($wpdb->prepare('DELETE FROM %i', self::df())); // phpcs:ignore WordPress.DB.DirectDatabaseQuery $wpdb->query($wpdb->prepare('INSERT INTO %i (term, n) SELECT term, COUNT(*) FROM %i GROUP BY term', self::df(), self::terms())); // phpcs:ignore WordPress.DB.DirectDatabaseQuery update_option(self::DF_OPTION, time(), false); update_option(self::DF_OPTION . '_total', (int) $wpdb->get_var($wpdb->prepare('SELECT COUNT(DISTINCT object_id) FROM %i', self::terms())), false); // phpcs:ignore WordPress.DB.DirectDatabaseQuery delete_option(self::DF_OPTION . '_dirty'); } public static function indexPost(int $postId): void { global $wpdb; $post = get_post($postId); $wpdb->delete(self::terms(), ['object_id' => $postId], ['%d']); // phpcs:ignore WordPress.DB.DirectDatabaseQuery $wpdb->delete(self::links(), ['source_id' => $postId], ['%d']); // phpcs:ignore WordPress.DB.DirectDatabaseQuery if (!$post instanceof \WP_Post) { return; } $doc = Extractor::extract($post); self::insertPairs('terms', $postId, self::distinctiveWords($doc['title'], $doc['body'])); self::insertPairs('links', $postId, self::linkedPosts((string) $post->post_content, $postId)); } /** * @param 'terms'|'links' $kind * @param list $values */ private static function insertPairs(string $kind, int $postId, array $values): void { global $wpdb; $terms = $kind === 'terms'; foreach (array_chunk($values, 200) as $chunk) { $args = [$terms ? self::terms() : self::links()]; foreach ($chunk as $v) { array_push($args, $postId, $v); } $sql = $terms ? 'INSERT IGNORE INTO %i (object_id, term) VALUES ' : 'INSERT IGNORE INTO %i (source_id, target_id) VALUES '; // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- literal SQL and a literal placeholder group per value; every value goes through prepare() $wpdb->query($wpdb->prepare($sql . implode(',', array_fill(0, count($chunk), $terms ? '(%d, %s)' : '(%d, %d)')), ...$args)); } } /** * A post's distinctive words: in its title, or used at least twice in the text. Rarest-first * ranking would otherwise favour a word used once in passing over what the post is about. * * @return list */ public static function distinctiveWords(string $title, string $body): array { $titleWords = self::words($title); $counts = array_count_values(self::words($body, false)); arsort($counts); // most used first, so a long post keeps its main words $out = $titleWords; foreach ($counts as $word => $n) { if ($n > 1) { $out[] = (string) $word; } } return array_slice(array_values(array_unique($out)), 0, self::MAX_TERMS); } /** * Lower-case words of four or more letters (any script), without stop words. * * @return list */ public static function words(string $text, bool $unique = true): array { static $stop = null; if ($stop === null) { $stop = array_flip(preg_split('/\s+/', trim(self::STOP_WORDS)) ?: []); } // \p{M}: combining marks, e.g. Devanagari vowel signs, are part of a word. preg_match_all('/\p{L}[\p{L}\p{M}\p{N}\-]{3,}/u', mb_strtolower($text), $m); $out = []; foreach ($m[0] as $w) { $w = trim($w, '-'); if (mb_strlen($w) >= 4 && mb_strlen($w) <= 64 && !isset($stop[$w])) { $out[] = $w; } } return $unique ? array_values(array_unique($out)) : $out; } /** * Posts this content links to: real elements pointing at this site. * * @return list */ public static function linkedPosts(string $html, int $self): array { if (stripos($html, 'loadHTML('

' . $html . '

', LIBXML_NONET); libxml_clear_errors(); libxml_use_internal_errors($prev); $host = (string) wp_parse_url(home_url(), PHP_URL_HOST); $out = []; foreach ($doc->getElementsByTagName('a') as $a) { $href = trim((string) $a->getAttribute('href')); if ($href === '' || $href[0] === '#') { continue; } $h = (string) wp_parse_url($href, PHP_URL_HOST); if ($h !== '' && strcasecmp($h, $host) !== 0) { continue; } $id = url_to_postid($href[0] === '/' ? home_url($href) : $href); if ($id > 0 && $id !== $self) { $out[$id] = true; } } return array_keys($out); } // ================================================================ reads /** * @return list */ public static function termsOf(int $postId): array { global $wpdb; return array_map('strval', (array) $wpdb->get_col($wpdb->prepare('SELECT term FROM %i WHERE object_id = %d', self::terms(), $postId))); // phpcs:ignore WordPress.DB.DirectDatabaseQuery } /** * How many posts use each of these words. * * @param list $terms * @return array */ public static function frequency(array $terms): array { global $wpdb; if ($terms === []) { return []; } $out = []; $counts = (int) get_option(self::DF_OPTION) > 0; foreach (array_chunk(array_values(array_unique($terms)), 500) as $chunk) { $in = implode(',', array_fill(0, count($chunk), '%s')); // phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- one literal %s per term; every term goes through prepare() $rows = $counts ? $wpdb->get_results($wpdb->prepare('SELECT term, n FROM %i WHERE term IN (' . $in . ')', self::df(), ...$chunk), ARRAY_A) : $wpdb->get_results($wpdb->prepare('SELECT term, COUNT(*) AS n FROM %i WHERE term IN (' . $in . ') GROUP BY term', self::terms(), ...$chunk), ARRAY_A); // phpcs:enable foreach ((array) $rows as $r) { $out[(string) $r['term']] = (int) $r['n']; } } return $out; } /** * Posts with terms recorded: the corpus size for "too common". */ public static function postCount(): int { global $wpdb; $total = (int) get_option(self::DF_OPTION . '_total'); if ($total > 0) { return $total; } return (int) $wpdb->get_var($wpdb->prepare('SELECT COUNT(DISTINCT object_id) FROM %i', self::terms())); // phpcs:ignore WordPress.DB.DirectDatabaseQuery } public static function linksTo(int $source, int $target): bool { global $wpdb; return (bool) $wpdb->get_var($wpdb->prepare('SELECT 1 FROM %i WHERE source_id = %d AND target_id = %d', self::links(), $source, $target)); // phpcs:ignore WordPress.DB.DirectDatabaseQuery } /** * Embedded, published posts no other embedded post links to, newest first. * * @return list */ public static function orphans(int $limit = 20): array { global $wpdb; // Three simple queries, each table used once (MySQL cannot use a temporary table twice // in one query, and the SQLite integration prefers plain statements). $linked = array_flip(array_map('intval', (array) $wpdb->get_col($wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery "SELECT DISTINCT target_id FROM %i WHERE source_id <> target_id AND source_id IN (SELECT object_id FROM %i WHERE object_type = 'post' AND status = %s)", self::links(), IndexTable::name(), IndexTable::INDEXED )))); $ids = array_map('intval', (array) $wpdb->get_col($wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery "SELECT p.ID FROM %i i JOIN {$wpdb->posts} p ON p.ID = i.object_id WHERE i.object_type = 'post' AND i.status = %s AND i.linked_at IS NOT NULL AND p.post_status = 'publish' ORDER BY p.post_date DESC", IndexTable::name(), IndexTable::INDEXED ))); $orphans = array_slice(array_values(array_filter($ids, static fn(int $id): bool => !isset($linked[$id]))), 0, $limit); $out = []; foreach ($orphans as $id) { $out[] = [ 'id' => $id, 'title' => html_entity_decode(get_the_title($id), ENT_QUOTES, 'UTF-8'), 'url' => (string) get_permalink($id), 'outbound' => (int) $wpdb->get_var($wpdb->prepare('SELECT COUNT(*) FROM %i WHERE source_id = %d', self::links(), $id)), // phpcs:ignore WordPress.DB.DirectDatabaseQuery ]; } return $out; } /** * Drops rows of posts that are no longer embedded. */ public static function prune(): void { global $wpdb; // Plain subqueries rather than multi-table DELETE, which the SQLite integration does not translate. $wpdb->query($wpdb->prepare("DELETE FROM %i WHERE object_id NOT IN (SELECT object_id FROM %i WHERE object_type = 'post')", self::terms(), IndexTable::name())); // phpcs:ignore WordPress.DB.DirectDatabaseQuery $wpdb->query($wpdb->prepare("DELETE FROM %i WHERE source_id NOT IN (SELECT object_id FROM %i WHERE object_type = 'post')", self::links(), IndexTable::name())); // phpcs:ignore WordPress.DB.DirectDatabaseQuery } }