Changeset 3790318 for codersly-visitor-tracker/trunk/includes/Install.php

Timestamp:

11/08/2026 12:50:59 AM (21 hours ago)

Author:

codersly

Message:

Tracking Updated

File:

• codersly-visitor-tracker/trunk/includes/Install.php (modified) (7 diffs)

codersly-visitor-tracker/trunk/includes/Install.php

@@ -17 +17 @@
 final class Install {
         /** Internal schema marker. */
-        private const DB_VERSION = '1.1.1-schema3';
+        private const DB_VERSION = '1.1.3-schema6';
         private const OPT_DB_VERSION = 'vtrt_db_version';
 
@@ -26 +26 @@
         }
 
-        public static function deactivate() : void {}
+        public static function deactivate() : void {
+                wp_clear_scheduled_hook( 'vtrt_async_geo_lookup' );
+        }
 
         public static function maybe_upgrade() : void {
@@ -32 +34 @@
                         self::activate();
                 }
+        }
+
+        public static function geo_cache_table() : string {
+                global $wpdb;
+                return $wpdb->prefix . 'vtrt_geo_cache';
+        }
+
+        public static function geo_lock_name() : string {
+                global $wpdb;
+                return 'vtrt_geo_' . md5( DB_NAME . '|' . $wpdb->prefix );
+        }
+
+        public static function geo_cache_ready() : bool {
+                return (bool) get_option( 'vtrt_geo_cache_initialized', false );
         }
 
@@ -51 +67 @@
                         region VARCHAR(120) NULL,
                         city VARCHAR(120) NULL,
+                        geo_retry_at DATETIME NULL,
+                        geo_source VARCHAR(40) NULL,
                         timezone VARCHAR(80) NULL,
                         path VARCHAR(2048) NOT NULL DEFAULT '',
@@ -77 +95 @@
                         KEY ts (ts),
                         KEY ip (ip),
+                        KEY ip_geo_source (ip,geo_source),
                         KEY user_id (user_id),
                         KEY is_bot (is_bot),
@@ -87 +106 @@
                 ) {$charset};";
                 dbDelta( $sql );
+                $geo = self::geo_cache_table();
+                dbDelta( "CREATE TABLE {$geo} (
+                        ip VARCHAR(64) NOT NULL,
+                        cache_scope VARCHAR(68) NOT NULL,
+                        country VARCHAR(100) NOT NULL DEFAULT '',
+                        country_code VARCHAR(2) NOT NULL DEFAULT '',
+                        region VARCHAR(120) NULL,
+                        city VARCHAR(120) NULL,
+                        timezone VARCHAR(80) NULL,
+                        geo_source VARCHAR(40) NULL,
+                        provider VARCHAR(128) NULL,
+                        expires_at BIGINT UNSIGNED NOT NULL DEFAULT 0,
+                        PRIMARY KEY  (ip,cache_scope),
+                        KEY expires_at (expires_at)
+                ) {$charset};" );
         }
 
@@ -119 +153 @@
         private static function run_migrations() : void {
                 self::widen_url_columns();
-                // Enable approximate location once for this requested build. The setting can
-                // still be turned off manually from Visitors Tracker → Settings.
-                if ( null === get_option( 'vtrt_geoip_auto_enabled_110', null ) ) {
-                        update_option( 'vtrt_enable_geoip', true, false );
-                        add_option( 'vtrt_geoip_auto_enabled_110', 1, '', false );
-                }
-                update_option( self::OPT_DB_VERSION, self::DB_VERSION, false );
+                global $wpdb;
+                $table = $wpdb->prefix . ( defined( 'VTRT_TBL_VISITORS' ) ? \VTRT_TBL_VISITORS : 'vtrt_visitors' );
+                if ( 'geo_retry_at' === $wpdb->get_var( $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $table, $wpdb->esc_like( 'geo_retry_at' ) ) )
+                        && 'geo_source' === $wpdb->get_var( $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $table, $wpdb->esc_like( 'geo_source' ) ) )
+                        && self::initialize_geo_cache( $table ) ) {
+                        delete_option( 'vtrt_geo_cursor_113' );
+                        update_option( self::OPT_DB_VERSION, self::DB_VERSION, false );
+                }
+        }
+
+        private static function initialize_geo_cache( string $visitors ) : bool {
+                global $wpdb;
+                $table = self::geo_cache_table();
+                if ( $table !== $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) ) ) {
+                        return false;
+                }
+                if ( self::geo_cache_ready() ) {
+                        return true;
+                }
+
+                $lock = self::geo_lock_name();
+                if ( 1 !== (int) $wpdb->get_var( $wpdb->prepare( 'SELECT GET_LOCK(%s, 10)', $lock ) ) ) {
+                        return false;
+                }
+
+                try {
+                        // Another request may have completed the migration while we waited.
+                        wp_cache_delete( 'notoptions', 'options' );
+                        wp_cache_delete( 'vtrt_geo_cache_initialized', 'options' );
+                        if ( self::geo_cache_ready() ) {
+                                return true;
+                        }
+                        $copied = $wpdb->query( $wpdb->prepare(
+                                "INSERT IGNORE INTO %i (ip, cache_scope, country, country_code, region, city, timezone, geo_source, provider)
+                                SELECT v.ip, v.geo_source, v.country, v.country_code, v.region, v.city, v.timezone, v.geo_source, v.geo_source
+                                FROM %i v
+                                INNER JOIN (
+                                        SELECT MAX(id) AS id FROM %i
+                                        WHERE ip <> '' AND geo_source <> '' AND city <> '' AND country <> '' AND country_code <> ''
+                                        GROUP BY ip, geo_source
+                                ) AS latest ON v.id = latest.id",
+                                $table, $visitors, $visitors
+                        ) );
+                        if ( false === $copied ) {
+                                return false;
+                        }
+
+                        // These legacy result transients are superseded by the separate table.
+                        $cleaned = $wpdb->query( $wpdb->prepare(
+                                'DELETE FROM %i WHERE option_name LIKE %s OR option_name LIKE %s OR option_name LIKE %s OR option_name LIKE %s',
+                                $wpdb->options,
+                                $wpdb->esc_like( '_transient_vtrt_geo_city_113_' ) . '%',
+                                $wpdb->esc_like( '_transient_timeout_vtrt_geo_city_113_' ) . '%',
+                                $wpdb->esc_like( '_transient_vtrt_geo_ipgeo_' ) . '%',
+                                $wpdb->esc_like( '_transient_timeout_vtrt_geo_ipgeo_' ) . '%'
+                        ) );
+                        if ( false === $cleaned ) {
+                                return false;
+                        }
+                        return update_option( 'vtrt_geo_cache_initialized', true, false );
+                } finally {
+                        $wpdb->get_var( $wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', $lock ) );
+                }
         }