Diff

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

added on local at 2026-07-06 01:16:36

Added

+35

lines

Removed

-0

lines

Context

unchanged

Blobs

from
to d145a8b3473a

Restore this content →

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.