Diff

/var/www/vhosts/3dshawn.com/abforge.3dshawn.com/_site_id_link_migration.sql

deleted on local at 2026-07-12 01:52:04

Added

lines

Removed

-35

lines

Context

unchanged

Blobs

from d145a8b3473a
to

Unified diff

Naive LCS-based line diff. Additions in emerald, deletions in rose. Both sides decompressed live from BLOB_STORE.

1		-- =====================================================================
2		-- ABForge migration :: 2026-07-05
3		-- Purpose: link visitor tracking rows to the customer's site (sites.id)
4		-- so /analytics.cgi can render per-site KPIs, globe, and country drill.
5		--
6		-- Idempotent: guarded by information_schema so it can run repeatedly
7		-- (rules.md sync invariant).
8		-- =====================================================================
9
10		-- 1. Add mkt_tracking_sessions.site_id (nullable so pre-migration rows
11		-- keep working; new rows get stamped from public_token in ingest).
12		SET @has := (SELECT COUNT(*) FROM information_schema.columns
13		WHERE table_schema = DATABASE()
14		AND table_name = 'mkt_tracking_sessions'
15		AND column_name = 'site_id');
16		SET @sql := IF(@has = 0,
17		'ALTER TABLE mkt_tracking_sessions ADD COLUMN site_id INT NULL AFTER buyer_account_id',
18		'SELECT 1');
19		PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
20
21		-- 2. Add index on site_id so per-site queries (WHERE site_id=X + range
22		-- on last_seen_at) hit a proper index.
23		SET @has_idx := (SELECT COUNT(*) FROM information_schema.statistics
24		WHERE table_schema = DATABASE()
25		AND table_name = 'mkt_tracking_sessions'
26		AND index_name = 'idx_site_id');
27		SET @sql := IF(@has_idx = 0,
28		'ALTER TABLE mkt_tracking_sessions ADD INDEX idx_site_id (site_id)',
29		'SELECT 1');
30		PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
31
32		-- Note: mkt_tracking_events does NOT get a site_id column. Per-site
33		-- event queries join sessions.site_id which is cheap because the
34		-- sessions table stays small relative to events, and the index above
35		-- makes the JOIN filter fast.