PDO::ERRMODE_EXCEPTION]; $cms = new PDO("mysql:host={$host};dbname={$src};charset=utf8mb4", $user, $pass, $opt); $new = new PDO("mysql:host={$host};dbname={$dst};charset=utf8mb4", $user, $pass, $opt); // Read every table inside ONE repeatable-read snapshot — the equivalent of // mysqldump --single-transaction. Without it a child row can reference a parent // inserted after the parent table was read. $cms->exec('SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ'); $cms->exec('START TRANSACTION WITH CONSISTENT SNAPSHOT'); $outDir = __DIR__; // --------------------------------------------------------------------------- // Work out which rows are worth carrying into dev. // --------------------------------------------------------------------------- /** @return list */ $ids = static function (PDO $db, string $sql): array { return array_map('intval', array_column($db->query($sql)->fetchAll(PDO::FETCH_ASSOC), 'id')); }; // A website is worth keeping only if it is active AND its owner still exists. // The orphan matters: cms has a website on user_id=1, and user 1 does not exist // there — but in webcrawlerdb id 1 IS the seeded Koushik owner, so copying it // would silently reassign a dead test site to a real account. $keepWebsites = $ids($cms, 'SELECT w.id FROM wc_websites w JOIN wc_users u ON u.id = w.user_id WHERE w.status = "active"'); $dropWebsites = $ids($cms, 'SELECT w.id FROM wc_websites w LEFT JOIN wc_users u ON u.id = w.user_id WHERE w.status <> "active" OR u.id IS NULL'); $inList = static fn (array $v): string => $v === [] ? '(NULL)' : '(' . implode(',', $v) . ')'; $W = $inList($keepWebsites); // Derived keep-sets. Filtering children by these also drops pre-existing orphans // (cms carries 20 wc_audit_pages and 6 wc_audit_issues on deleted audits). $keepAudits = $ids($cms, "SELECT id FROM wc_audits WHERE website_id IN {$W}"); $keepProjects = $ids($cms, "SELECT id FROM wc_rank_projects WHERE website_id IN {$W}"); $A = $inList($keepAudits); $P = $inList($keepProjects); $keepTracked = $ids($cms, "SELECT id FROM wc_tracked_keywords WHERE project_id IN {$P}"); $keepAnalyses = $ids($cms, "SELECT id FROM wc_ai_page_analyses WHERE website_id IN {$W}"); $K = $inList($keepTracked); $N = $inList($keepAnalyses); // --------------------------------------------------------------------------- // Per-table policy. // // mode 'skip' — omitted entirely; $why is written into the dump // mode 'ignore' — no TRUNCATE, INSERT IGNORE (protects seeded / existing rows) // mode 'replace' — TRUNCATE then INSERT (exact filtered mirror) // where — optional row filter // --------------------------------------------------------------------------- $policy = [ // -- reference data: owned by the seeders, deliberately NOT duplicated --- // CountrySeeder / ModuleSeeder / SubscriptionPlanSeeder already load these, // and were generated from these very rows. Copying them here too would be a // no-op at best and a second, silently-diverging source of truth at worst. 'wc_countries' => ['skip', null, 'seeded by CountrySeeder (197 rows)'], 'wc_modules' => ['skip', null, 'seeded by ModuleSeeder (46 rows)'], 'wc_module_permission_mapping' => ['skip', null, 'seeded by ModuleSeeder (94 rows)'], 'wc_subscription_plans' => ['skip', null, 'seeded by SubscriptionPlanSeeder (4 rows)'], // -- users: NOT seeded, and NOT truncated ------------------------------- // Unlike the tables above, these rows are not available from any seeder: the // copied websites, leads and subscriptions belong to cms users 15-23. Not // truncated so the three '+wc' owner accounts at ids 1-3 survive. 'wc_users' => ['ignore', null, 'cms users that the copied data belongs to; keeps the seeded +wc owners'], // -- transient auth artefacts: no value in dev -------------------------- 'wc_otp_codes' => ['skip', null, 'single-use sign-in codes, all long expired'], 'wc_password_resets' => ['skip', null, 'single-use reset tokens, all consumed or expired'], 'wc_dataforseo_logs' => ['skip', null, 'raw API request/response JSON — ~292 MB of the ~327 MB in cms, append-only, never read by the app'], // -- site-scoped data, filtered to live websites ------------------------ 'wc_websites' => ['replace', "id IN {$W}"], 'wc_audits' => ['replace', "website_id IN {$W}"], 'wc_audit_pages' => ['replace', "audit_id IN {$A}"], 'wc_audit_issues' => ['replace', "audit_id IN {$A}"], 'wc_automation_queue' => ['replace', "website_id IN {$W}"], 'wc_pixel_views' => ['replace', "website_id IN {$W}"], 'wc_pixel_events' => ['replace', "website_id IN {$W}"], 'wc_pixel_vitals' => ['replace', "website_id IN {$W}"], 'wc_website_integrations' => ['replace', "website_id IN {$W}"], 'wc_rank_projects' => ['replace', "id IN {$P}"], 'wc_tracked_keywords' => ['replace', "id IN {$K}"], 'wc_keyword_rankings' => ['replace', "tracked_keyword_id IN {$K}"], 'wc_ranking_competitors' => ['replace', "project_id IN {$P}"], 'wc_site_overview_cache' => ['replace', "website_id IN {$W}"], 'wc_gap_analysis_results' => ['replace', "website_id IN {$W}"], 'wc_keyword_recommendations' => ['replace', "website_id IN {$W}"], // website_id is nullable here — a saved keyword need not belong to a site. 'wc_user_saved_keywords' => ['replace', "website_id IS NULL OR website_id IN {$W}"], // soft-deleted drafts are not worth carrying. 'wc_ai_content' => ['replace', "website_id IN {$W} AND deleted_at IS NULL"], 'wc_ai_page_analyses' => ['replace', "id IN {$N}"], 'wc_ai_page_suggestions' => ['replace', "analysis_id IN {$N}"], 'wc_ai_page_analysis_events' => ['replace', "analysis_id IN {$N}"], // -- not site-scoped ----------------------------------------------------- // Domain-keyed DataForSEO cache. Kept whole on purpose: it holds researched // and competitor domains that were never registered as websites, and having // it populated saves burning API credits in dev. 'wc_seo_keywords' => ['replace', null], 'wc_keyword_research_cache' => ['replace', null], 'wc_leads' => ['replace', null], 'wc_invites_log' => ['replace', null], 'wc_subscription' => ['replace', null], 'wc_subscription_history' => ['replace', null], 'wc_cancel_subscriptions' => ['replace', null], ]; /** Load order: parents before children, so the dump works even with FK checks on. */ $order = [ 'wc_countries', 'wc_users', 'wc_websites', 'wc_audits', 'wc_audit_pages', 'wc_audit_issues', 'wc_automation_queue', 'wc_pixel_views', 'wc_pixel_events', 'wc_pixel_vitals', 'wc_website_integrations', 'wc_rank_projects', 'wc_tracked_keywords', 'wc_keyword_rankings', 'wc_ranking_competitors', 'wc_seo_keywords', 'wc_keyword_research_cache', 'wc_keyword_recommendations', 'wc_user_saved_keywords', 'wc_gap_analysis_results', 'wc_site_overview_cache', 'wc_ai_content', 'wc_ai_page_analyses', 'wc_ai_page_suggestions', 'wc_ai_page_analysis_events', 'wc_modules', 'wc_module_permission_mapping', 'wc_otp_codes', 'wc_password_resets', 'wc_invites_log', 'wc_leads', 'wc_subscription_plans', 'wc_subscription', 'wc_subscription_history', 'wc_cancel_subscriptions', 'wc_dataforseo_logs', ]; $q = $cms->prepare( 'SELECT table_name FROM information_schema.tables WHERE table_schema = ? AND table_name LIKE "wc\_%"' ); $q->execute([$src]); $srcTables = array_column($q->fetchAll(PDO::FETCH_ASSOC), 'table_name'); // Fail loudly rather than silently dropping a table added to cms since this was // written. This is how wc_cancel_subscriptions was caught. if ($unknown = array_diff($srcTables, $order)) { fwrite(STDERR, 'cms table(s) missing from $order: ' . implode(', ', $unknown) . "\n"); exit(1); } if ($unpoliced = array_diff($srcTables, array_keys($policy))) { fwrite(STDERR, 'cms table(s) missing from $policy: ' . implode(', ', $unpoliced) . "\n"); exit(1); } $order = array_values(array_intersect($order, $srcTables)); // --------------------------------------------------------------------------- /** @return array{0: list, 1: list} [columns in both schemas, columns only in source] */ function sharedColumns(PDO $cms, PDO $new, string $table, string $src, string $dst): array { $cols = static function (PDO $db, string $schema) use ($table): array { $st = $db->prepare( 'SELECT column_name FROM information_schema.columns WHERE table_schema = ? AND table_name = ? ORDER BY ordinal_position' ); $st->execute([$schema, $table]); return array_column($st->fetchAll(PDO::FETCH_ASSOC), 'column_name'); }; $a = $cols($cms, $src); $b = $cols($new, $dst); return [array_values(array_intersect($a, $b)), array_values(array_diff($a, $b))]; } function quoteRow(PDO $db, array $row): string { $out = []; foreach ($row as $v) { $out[] = $v === null ? 'NULL' : $db->quote((string) $v); } return '(' . implode(',', $out) . ')'; } $path = $outDir . '/webcrawlerdb_dev_data.sql'; $fh = fopen($path, 'w'); $stamp = $cms->query('SELECT NOW()')->fetchColumn(); $dropped = $cms->query('SELECT w.id, w.domain, w.status, u.id AS uid FROM wc_websites w LEFT JOIN wc_users u ON u.id = w.user_id WHERE w.status <> "active" OR u.id IS NULL ORDER BY w.id')->fetchAll(PDO::FETCH_ASSOC); $dropLines = ''; foreach ($dropped as $d) { $reason = $d['uid'] === null ? 'orphaned — owner user no longer exists' : 'status = ' . $d['status']; $dropLines .= sprintf("-- #%-3s %-24s %s\n", $d['id'], $d['domain'], $reason); } $skipLines = ''; foreach ($policy as $t => $p) { if ($p[0] === 'skip') { $skipLines .= sprintf("-- %-24s %s\n", $t, $p[2]); } } fwrite($fh, << {$dst}) -- ===================================================================== -- -- DEV / LOCAL USE ONLY. Do NOT run this on staging or production. -- Those environments get their data from the seeders instead: -- CI_ENVIRONMENT=development php8.2 spark db:seed DatabaseSeeder -- -- PREREQUISITES — this file does NOT stand alone. Run both first: -- CI_ENVIRONMENT=development php8.2 spark migrate (no CREATE TABLE here) -- CI_ENVIRONMENT=development php8.2 spark db:seed DatabaseSeeder -- -- The seeders own the reference data (countries, modules, module permissions, -- plans) and the three '+wc' owner accounts. None of that is duplicated below, -- so loading this onto an unseeded database leaves wc_websites.country_id -- pointing at an empty wc_countries. -- -- Source : {$src} (single consistent snapshot, taken {$stamp}) -- Target : {$dst} -- -- Load with: -- mysql --default-character-set=utf8mb4 -u dev -p {$dst} < webcrawlerdb_dev_data.sql -- -- The --default-character-set=utf8mb4 flag matters: without it the emoji in -- wc_countries.flag_emoji get mangled in transit. -- -- --------------------------------------------------------------------- -- NOT TRUNCATED (loaded with INSERT IGNORE, so existing rows win) -- --------------------------------------------------------------------- -- wc_users The copied websites, leads and subscriptions belong to {$src} -- users, and no seeder provides those — so they are added here. -- Not truncated, so the three seeded '+wc' owner accounts at -- ids 1-3 survive the load untouched. -- -- Everything else is TRUNCATEd before its INSERTs, so re-loading is safe and -- repeatable. -- -- --------------------------------------------------------------------- -- TABLES SKIPPED ENTIRELY -- --------------------------------------------------------------------- {$skipLines}-- -- --------------------------------------------------------------------- -- WEBSITES EXCLUDED, with all of their audits, crawl pages, issues, -- pixel data, rank projects, AI content and caches -- --------------------------------------------------------------------- {$dropLines}-- -- Rows pointing at an already-deleted parent are dropped too, so unlike a -- straight mysqldump this load leaves no orphans. -- --------------------------------------------------------------------- SET NAMES utf8mb4; SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO'; SET @OLD_FK := @@FOREIGN_KEY_CHECKS; SET FOREIGN_KEY_CHECKS = 0; SET @OLD_UC := @@UNIQUE_CHECKS; SET UNIQUE_CHECKS = 0; SET AUTOCOMMIT = 0; START TRANSACTION; TXT); $report = []; $totalRows = 0; foreach ($order as $t) { [$mode, $where, $why] = $policy[$t] + [2 => null]; if ($mode === 'skip') { fwrite($fh, "\n--\n-- {$t} — SKIPPED: {$why}\n--\n"); $report[$t] = ['skip', 0]; continue; } [$shared, $onlyInSrc] = sharedColumns($cms, $new, $t, $src, $dst); $quoted = '`' . implode('`,`', $shared) . '`'; $filter = $where === null ? '' : " WHERE {$where}"; $n = (int) $cms->query("SELECT COUNT(*) FROM `{$t}`{$filter}")->fetchColumn(); $all = (int) $cms->query("SELECT COUNT(*) FROM `{$t}`")->fetchColumn(); fwrite($fh, "\n--\n-- {$t} — {$n} row(s)"); fwrite($fh, $n === $all ? "\n" : " of {$all} in {$src}\n"); if ($onlyInSrc !== []) { fwrite($fh, '-- NOTE: in ' . $src . ' but not in ' . $dst . ', not copied: ' . implode(', ', $onlyInSrc) . "\n"); } if ($where !== null) { fwrite($fh, "-- filter: {$where}\n"); } if ($mode === 'ignore') { fwrite($fh, "-- not truncated: {$why}\n"); $verb = 'INSERT IGNORE INTO'; } else { fwrite($fh, "TRUNCATE TABLE `{$t}`;\n"); $verb = 'INSERT INTO'; } $report[$t] = [$mode, $n, $all]; $totalRows += $n; if ($n === 0) { fwrite($fh, "-- (no rows)\n"); continue; } $limit = 512 * 1024; $buf = []; $bytes = 0; $stmt = $cms->prepare("SELECT {$quoted} FROM `{$t}`{$filter}"); $stmt->execute(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $v = quoteRow($cms, $row); $buf[] = $v; $bytes += strlen($v); if ($bytes >= $limit) { fwrite($fh, "{$verb} `{$t}` ({$quoted}) VALUES\n" . implode(",\n", $buf) . ";\n"); $buf = []; $bytes = 0; } } if ($buf !== []) { fwrite($fh, "{$verb} `{$t}` ({$quoted}) VALUES\n" . implode(",\n", $buf) . ";\n"); } } fwrite($fh, <<<'TXT' COMMIT; SET FOREIGN_KEY_CHECKS = @OLD_FK; SET UNIQUE_CHECKS = @OLD_UC; SET AUTOCOMMIT = 1; TXT); $cms->exec('COMMIT'); // release the read snapshot fclose($fh); // ------------------------------------------------------------------ report printf("wrote %s\n %.2f MB · %d rows\n\n", $path, filesize($path) / 1048576, $totalRows); printf("websites kept: %d excluded: %d (%s)\n\n", count($keepWebsites), count($dropWebsites), implode(',', $dropWebsites)); printf(" %-30s %-8s %s\n", 'TABLE', 'MODE', 'ROWS'); foreach ($report as $t => $r) { if ($r[0] === 'skip') { printf(" %-30s %-8s —\n", $t, 'skip'); continue; } [$mode, $n, $all] = $r; printf(" %-30s %-8s %d%s\n", $t, $mode, $n, $n === $all ? '' : " of {$all}"); }