PHP: полный мануал с примерами, функциями и ООП

<?php
declare(strict_types=1);
require __DIR__.'/se114-helper.php';
$db=se114Connect();
$runtime=['php'=>PHP_VERSION,'client'=>$db->getAttribute(PDO::ATTR_CLIENT_VERSION),'server'=>$db->getAttribute(PDO::ATTR_SERVER_VERSION)];
$settings=se114Rows($db,'SELECT @@SESSION.sql_mode AS sql_mode,@@SESSION.foreign_key_checks AS fk_checks')[0];
$modes=explode(',', $settings['sql_mode']);
if(!array_intersect($modes,['STRICT_TRANS_TABLES','STRICT_ALL_TABLES']) || (int)$settings['fk_checks']!==1){throw new RuntimeException('Use strict SQL mode and enabled foreign-key checks in the teaching database');}
$prefix='mysql114_schema_'.bin2hex(random_bytes(8));
$products=$prefix.'_products'; $categories=$prefix.'_categories'; $progress=$prefix.'_progress'; $journal=$prefix.'_migrations';
$created=[]; $details=[];
try {
    se114Create($db,$products,'id INT UNSIGNED NOT NULL PRIMARY KEY,title VARCHAR(80) NOT NULL,sku VARCHAR(32) NOT NULL,city VARCHAR(40) NOT NULL,category_id INT UNSIGNED NULL',$created);
    se114Seed($db,$products); $pt=se114Name($products);
$oldBefore=se114Rows($db,"SELECT id,title,sku FROM $pt ORDER BY id");
    $db->exec("ALTER TABLE $pt ADD COLUMN delivery_region VARCHAR(16) NULL DEFAULT NULL");
    $oldAfter=se114Rows($db,"SELECT id,title,sku FROM $pt ORDER BY id");
    $initialNulls=se114Count($db,"SELECT COUNT(*) AS total FROM $pt WHERE delivery_region IS NULL");
    $db->prepare("INSERT INTO $pt (id,title,sku,city,category_id) VALUES (?,?,?,?,?)")->execute([201,'Кружка зелёная','MUG-C','Москва',10]);
    $db->prepare("INSERT INTO $pt (id,title,sku,city,category_id,delivery_region) VALUES (?,?,?,?,?,?)")->execute([202,'Кружка серая','MUG-D','Москва',10,'Столица']);
    $selected=se114Rows($db,"SELECT id,title,delivery_region FROM $pt WHERE id IN (101,104,201,202) ORDER BY id");
    $rows=array_map(fn(array $row)=>[(int)$row['id'],$row['title'],$row['delivery_region']],$selected);
    $checks=['six_old_values_preserved'=>$oldBefore===$oldAfter && count($oldAfter)===6,'old_rows_start_with_null'=>$initialNulls===6,'old_insert_still_works'=>$rows[2][2]===null,'new_insert_sets_region'=>$rows[3][2]==='Столица','eight_rows_preserved'=>se114Count($db,"SELECT COUNT(*) AS total FROM $pt")===8];
    $details=['old_row_count'=>count($oldBefore),'initial_null_count'=>$initialNulls,'new_column'=>se114Column($db,$products,'delivery_region')];
} finally {
    if($db->inTransaction()){$db->rollBack();}
    foreach([$products,$categories,$progress,$journal] as $name){
        if($created[$name]??false){$db->exec('DROP TABLE '.se114Name($name));}
    }
}
$remaining=se114Count($db,'SELECT COUNT(*) AS total FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME IN (?,?,?,?)',[$products,$categories,$progress,$journal]);
$checks['owned_tables_removed']=$remaining===0;
$db=null;
se114Report('mysql114-schema-compatible-column', ['ID', 'Товар', 'delivery_region'], $rows, $checks, $runtime, $details);
echo 'mysql114-schema-compatible-column: saved result.html and result.json
';

mysql114-schema-compatible-column: saved result.html and result.json

После php example.php откройте result.html и сопоставьте строки с таблицей выше. Данные, helper, README и проверка собственных таблиц входят в самостоятельный пример.

<?php
declare(strict_types=1);
require __DIR__.'/se114-helper.php';
$db=se114Connect();
$runtime=['php'=>PHP_VERSION,'client'=>$db->getAttribute(PDO::ATTR_CLIENT_VERSION),'server'=>$db->getAttribute(PDO::ATTR_SERVER_VERSION)];
$settings=se114Rows($db,'SELECT @@SESSION.sql_mode AS sql_mode,@@SESSION.foreign_key_checks AS fk_checks')[0];
$modes=explode(',', $settings['sql_mode']);
if(!array_intersect($modes,['STRICT_TRANS_TABLES','STRICT_ALL_TABLES']) || (int)$settings['fk_checks']!==1){throw new RuntimeException('Use strict SQL mode and enabled foreign-key checks in the teaching database');}
$prefix='mysql114_schema_'.bin2hex(random_bytes(8));
$products=$prefix.'_products'; $categories=$prefix.'_categories'; $progress=$prefix.'_progress'; $journal=$prefix.'_migrations';
$created=[]; $details=[];
try {
    se114Create($db,$products,'id INT UNSIGNED NOT NULL PRIMARY KEY,title VARCHAR(80) NOT NULL,sku VARCHAR(32) NOT NULL,city VARCHAR(40) NOT NULL,category_id INT UNSIGNED NULL',$created);
    se114Seed($db,$products); $pt=se114Name($products);
$db->exec("ALTER TABLE $pt ADD COLUMN delivery_region VARCHAR(16) NULL DEFAULT NULL");
    $db->prepare("UPDATE $pt SET delivery_region=? WHERE id=?")->execute(['Особый',120]);
    se114Create($db,$progress,'job VARCHAR(40) NOT NULL PRIMARY KEY,last_id INT UNSIGNED NOT NULL',$created);
    $jt=se114Name($progress); $db->prepare("INSERT INTO $jt (job,last_id) VALUES (?,?)")->execute(['delivery_region',0]);
    $first=se114BackfillBatch($db,$products,$progress,2);
    $cursorAfterFirst=(int)se114Rows($db,"SELECT last_id FROM $jt WHERE job=?",['delivery_region'])[0]['last_id'];
    $nullsAfterFirst=se114Count($db,"SELECT COUNT(*) AS total FROM $pt WHERE delivery_region IS NULL");
    // Controlled pause between committed batches; the process is not killed.
    $resumed=[]; $updatedAfterResume=0;
    do{$batch=se114BackfillBatch($db,$products,$progress,2); $resumed[]=$batch; $updatedAfterResume+=$batch['updated'];}while($batch['ids']);
    $replay=se114BackfillBatch($db,$products,$progress,2);
    $final=se114Rows($db,"SELECT id,delivery_region FROM $pt ORDER BY id");
    $rows=[['После первой порции',(string)$cursorAfterFirst,'Осталось NULL: '.$nullsAfterFirst],['Продолжение с checkpoint','104','Изменено строк: '.$updatedAfterResume],['Повтор после завершения',(string)$replay['last_id'],'Изменено строк: '.$replay['updated']]];
    foreach($final as $row){$rows[]=['Товар',(string)$row['id'],$row['delivery_region']];}
    $checks=['first_batch_committed'=>$first['ids']===[101,104] && $cursorAfterFirst===104 && $nullsAfterFirst===3,'resume_starts_after_checkpoint'=>$resumed[0]['ids']===[108,115],'only_missing_values_filled'=>$updatedAfterResume===3 && $final[4]['delivery_region']==='Особый','all_six_rows_present'=>count($final)===6,'no_nulls_left'=>se114Count($db,"SELECT COUNT(*) AS total FROM $pt WHERE delivery_region IS NULL")===0,'replay_changes_nothing'=>$replay===['ids'=>[],'last_id'=>140,'updated'=>0]];
    $details=['first'=>$first,'resume_batches'=>$resumed,'repeat'=>$replay,'checkpoint_after_first'=>$cursorAfterFirst];
} finally {
    if($db->inTransaction()){$db->rollBack();}
    foreach([$products,$categories,$progress,$journal] as $name){
        if($created[$name]??false){$db->exec('DROP TABLE '.se114Name($name));}
    }
}
$remaining=se114Count($db,'SELECT COUNT(*) AS total FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME IN (?,?,?,?)',[$products,$categories,$progress,$journal]);
$checks['owned_tables_removed']=$remaining===0;
$db=null;
se114Report('mysql114-schema-resumable-backfill', ['Шаг', 'ID / курсор', 'Результат'], $rows, $checks, $runtime, $details);
echo 'mysql114-schema-resumable-backfill: saved result.html and result.json
';

mysql114-schema-resumable-backfill: saved result.html and result.json

После php example.php откройте result.html и сопоставьте строки с таблицей выше. Данные, helper, README и проверка собственных таблиц входят в самостоятельный пример.

<?php
declare(strict_types=1);
require __DIR__.'/se114-helper.php';
$db=se114Connect();
$runtime=['php'=>PHP_VERSION,'client'=>$db->getAttribute(PDO::ATTR_CLIENT_VERSION),'server'=>$db->getAttribute(PDO::ATTR_SERVER_VERSION)];
$settings=se114Rows($db,'SELECT @@SESSION.sql_mode AS sql_mode,@@SESSION.foreign_key_checks AS fk_checks')[0];
$modes=explode(',', $settings['sql_mode']);
if(!array_intersect($modes,['STRICT_TRANS_TABLES','STRICT_ALL_TABLES']) || (int)$settings['fk_checks']!==1){throw new RuntimeException('Use strict SQL mode and enabled foreign-key checks in the teaching database');}
$prefix='mysql114_schema_'.bin2hex(random_bytes(8));
$products=$prefix.'_products'; $categories=$prefix.'_categories'; $progress=$prefix.'_progress'; $journal=$prefix.'_migrations';
$created=[]; $details=[];
try {
    se114Create($db,$products,'id INT UNSIGNED NOT NULL PRIMARY KEY,title VARCHAR(80) NOT NULL,sku VARCHAR(32) NOT NULL,city VARCHAR(40) NOT NULL,category_id INT UNSIGNED NULL',$created);
    se114Seed($db,$products); $pt=se114Name($products);
$db->exec("ALTER TABLE $pt ADD COLUMN delivery_region VARCHAR(16) NULL DEFAULT NULL");
    $db->exec("UPDATE $pt SET delivery_region=CASE WHEN city='Москва' THEN 'Столица' ELSE 'Регион' END WHERE id NOT IN (104,115)");
    $missing=se114Rows($db,"SELECT id,city FROM $pt WHERE delivery_region IS NULL ORDER BY id");
    $failedCode=null;
    try{$db->exec("ALTER TABLE $pt MODIFY COLUMN delivery_region VARCHAR(16) NOT NULL, ALGORITHM=INPLACE");}
    catch(PDOException $error){$code=(int)($error->errorInfo[1]??0);if(!in_array($code,[1138,1265],true)){throw $error;}$failedCode=$code;}
    $stillNullable=se114Column($db,$products,'delivery_region')['IS_NULLABLE']==='YES';
    $db->exec("UPDATE $pt SET delivery_region='Регион' WHERE delivery_region IS NULL AND city IN ('Казань','Самара')");
    $remainingNulls=se114Count($db,"SELECT COUNT(*) AS total FROM $pt WHERE delivery_region IS NULL");
    if($remainingNulls!==0){throw new RuntimeException('Resolve every missing region before NOT NULL');}
    $db->exec("ALTER TABLE $pt MODIFY COLUMN delivery_region VARCHAR(16) NOT NULL, ALGORITHM=INPLACE");
    $newNullCode=null;
    try{$db->prepare("UPDATE $pt SET delivery_region=? WHERE id=?")->execute([null,101]);}
    catch(PDOException $error){if((int)($error->errorInfo[1]??0)!==1048){throw $error;}$newNullCode=1048;}
    $rows=[['Перед миграцией','NULL у ID '.implode(', ',array_column($missing,'id'))],['Первая попытка ALTER',$failedCode!==null?'Отклонена: в данных есть NULL':'НЕОЖИДАННО ПРИНЯТА'],['После исправления','NULL: '.$remainingNulls],['Схема после повторного ALTER','IS_NULLABLE='.se114Column($db,$products,'delivery_region')['IS_NULLABLE']],['Новая попытка записать NULL',(string)$newNullCode.': запрещена']];
    $checks=['two_missing_values_found'=>array_map('intval',array_column($missing,'id'))===[104,115],'alter_rejected_and_schema_preserved'=>$failedCode!==null && $stillNullable,'all_missing_values_resolved'=>$remainingNulls===0,'not_null_is_installed'=>se114Column($db,$products,'delivery_region')['IS_NULLABLE']==='NO','new_null_rejected'=>$newNullCode===1048,'six_rows_preserved'=>se114Count($db,"SELECT COUNT(*) AS total FROM $pt")===6];
    $details=['failed_alter_driver_code'=>$failedCode,'missing_before'=>$missing,'null_update_driver_code'=>$newNullCode];
} finally {
    if($db->inTransaction()){$db->rollBack();}
    foreach([$products,$categories,$progress,$journal] as $name){
        if($created[$name]??false){$db->exec('DROP TABLE '.se114Name($name));}
    }
}
$remaining=se114Count($db,'SELECT COUNT(*) AS total FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME IN (?,?,?,?)',[$products,$categories,$progress,$journal]);
$checks['owned_tables_removed']=$remaining===0;
$db=null;
se114Report('mysql114-schema-require-value', ['Шаг', 'Результат'], $rows, $checks, $runtime, $details);
echo 'mysql114-schema-require-value: saved result.html and result.json
';

mysql114-schema-require-value: saved result.html and result.json

После php example.php откройте result.html и сопоставьте строки с таблицей выше. Данные, helper, README и проверка собственных таблиц входят в самостоятельный пример.

<?php
declare(strict_types=1);
require __DIR__.'/se114-helper.php';
$db=se114Connect();
$runtime=['php'=>PHP_VERSION,'client'=>$db->getAttribute(PDO::ATTR_CLIENT_VERSION),'server'=>$db->getAttribute(PDO::ATTR_SERVER_VERSION)];
$settings=se114Rows($db,'SELECT @@SESSION.sql_mode AS sql_mode,@@SESSION.foreign_key_checks AS fk_checks')[0];
$modes=explode(',', $settings['sql_mode']);
if(!array_intersect($modes,['STRICT_TRANS_TABLES','STRICT_ALL_TABLES']) || (int)$settings['fk_checks']!==1){throw new RuntimeException('Use strict SQL mode and enabled foreign-key checks in the teaching database');}
$prefix='mysql114_schema_'.bin2hex(random_bytes(8));
$products=$prefix.'_products'; $categories=$prefix.'_categories'; $progress=$prefix.'_progress'; $journal=$prefix.'_migrations';
$created=[]; $details=[];
try {
    se114Create($db,$products,'id INT UNSIGNED NOT NULL PRIMARY KEY,title VARCHAR(80) NOT NULL,sku VARCHAR(32) NOT NULL,city VARCHAR(40) NOT NULL,category_id INT UNSIGNED NULL',$created);
    se114Seed($db,$products); $pt=se114Name($products);
$db->prepare("UPDATE $pt SET sku=? WHERE id=?")->execute(['MUG-A',104]);
    $duplicates=se114Rows($db,"SELECT sku,COUNT(*) AS occurrences FROM $pt GROUP BY sku HAVING COUNT(*)>1 ORDER BY sku");
    $conflictRows=se114Rows($db,"SELECT id,title,sku FROM $pt WHERE sku=? ORDER BY id",['MUG-A']);
    $failedCode=null;
    try{$db->exec("ALTER TABLE $pt ADD UNIQUE KEY sku_unique (sku)");}
    catch(PDOException $error){if((int)($error->errorInfo[1]??0)!==1062){throw $error;}$failedCode=1062;}
    $indexAfterFailure=se114Count($db,'SELECT COUNT(*) AS total FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? AND INDEX_NAME=?',[$products,'sku_unique']);
    // The warehouse source confirms MUG-B for the blue mug, ID 104.
    $db->prepare("UPDATE $pt SET sku=? WHERE id=? AND sku=?")->execute(['MUG-B',104,'MUG-A']);
    $duplicatesAfter=se114Rows($db,"SELECT sku,COUNT(*) AS occurrences FROM $pt GROUP BY sku HAVING COUNT(*)>1");
    if($duplicatesAfter){throw new RuntimeException('Duplicate article codes remain');}
    $db->exec("ALTER TABLE $pt ADD UNIQUE KEY sku_unique (sku)");
    $insertCode=null;
    try{$db->prepare("INSERT INTO $pt (id,title,sku,city,category_id) VALUES (?,?,?,?,?)")->execute([999,'Ещё одна кружка','MUG-A','Москва',10]);}
    catch(PDOException $error){if((int)($error->errorInfo[1]??0)!==1062){throw $error;}$insertCode=1062;}
    $fixed=se114Rows($db,"SELECT id,sku FROM $pt WHERE id IN (101,104) ORDER BY id");
    $rows=[['Конфликт до изменения','MUG-A: '.implode(', ',array_column($conflictRows,'id'))],['Первый ADD UNIQUE',(string)$failedCode.': индекс не добавлен'],['Проверенное исправление','104 → '.$fixed[1]['sku']],['Дубликатов после исправления',(string)count($duplicatesAfter)],['Новая попытка повторить MUG-A',(string)$insertCode.': строка не добавлена'],['Товаров осталось',(string)se114Count($db,"SELECT COUNT(*) AS total FROM $pt")]];
    $checks=['duplicate_pair_detected'=>count($duplicates)===1 && (int)$duplicates[0]['occurrences']===2 && array_map('intval',array_column($conflictRows,'id'))===[101,104],'add_unique_rejected'=>$failedCode===1062 && $indexAfterFailure===0,'confirmed_code_restored'=>$fixed[1]['sku']==='MUG-B' && $fixed[0]['sku']==='MUG-A','unique_index_installed'=>se114Count($db,'SELECT COUNT(*) AS total FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? AND INDEX_NAME=? AND NON_UNIQUE=0',[$products,'sku_unique'])===1,'duplicate_insert_rejected'=>$insertCode===1062,'all_products_preserved'=>se114Count($db,"SELECT COUNT(*) AS total FROM $pt")===6];
    $details=['conflict_rows'=>$conflictRows,'failed_alter_driver_code'=>$failedCode,'duplicate_insert_driver_code'=>$insertCode,'unique_index_absent_after_failure'=>$indexAfterFailure===0];
} finally {
    if($db->inTransaction()){$db->rollBack();}
    foreach([$products,$categories,$progress,$journal] as $name){
        if($created[$name]??false){$db->exec('DROP TABLE '.se114Name($name));}
    }
}
$remaining=se114Count($db,'SELECT COUNT(*) AS total FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME IN (?,?,?,?)',[$products,$categories,$progress,$journal]);
$checks['owned_tables_removed']=$remaining===0;
$db=null;
se114Report('mysql114-schema-unique-after-cleanup', ['Шаг', 'Результат'], $rows, $checks, $runtime, $details);
echo 'mysql114-schema-unique-after-cleanup: saved result.html and result.json
';

mysql114-schema-unique-after-cleanup: saved result.html and result.json

После php example.php откройте result.html и сопоставьте строки с таблицей выше. Данные, helper, README и проверка собственных таблиц входят в самостоятельный пример.

<?php
declare(strict_types=1);
require __DIR__.'/se114-helper.php';
$db=se114Connect();
$runtime=['php'=>PHP_VERSION,'client'=>$db->getAttribute(PDO::ATTR_CLIENT_VERSION),'server'=>$db->getAttribute(PDO::ATTR_SERVER_VERSION)];
$settings=se114Rows($db,'SELECT @@SESSION.sql_mode AS sql_mode,@@SESSION.foreign_key_checks AS fk_checks')[0];
$modes=explode(',', $settings['sql_mode']);
if(!array_intersect($modes,['STRICT_TRANS_TABLES','STRICT_ALL_TABLES']) || (int)$settings['fk_checks']!==1){throw new RuntimeException('Use strict SQL mode and enabled foreign-key checks in the teaching database');}
$prefix='mysql114_schema_'.bin2hex(random_bytes(8));
$products=$prefix.'_products'; $categories=$prefix.'_categories'; $progress=$prefix.'_progress'; $journal=$prefix.'_migrations';
$created=[]; $details=[];
try {
    se114Create($db,$products,'id INT UNSIGNED NOT NULL PRIMARY KEY,title VARCHAR(80) NOT NULL,sku VARCHAR(32) NOT NULL,city VARCHAR(40) NOT NULL,category_id INT UNSIGNED NULL',$created);
    se114Seed($db,$products); $pt=se114Name($products);
se114Create($db,$categories,'id INT UNSIGNED NOT NULL PRIMARY KEY,title VARCHAR(80) NOT NULL',$created);
    $ct=se114Name($categories);
    $db->prepare("INSERT INTO $ct (id,title) VALUES (?,?),(?,?)")->execute([10,'Посуда',20,'Освещение']);
    $db->prepare("UPDATE $pt SET category_id=? WHERE id=?")->execute([999,104]);
    $orphans=se114Rows($db,"SELECT p.id,p.title,p.category_id FROM $pt AS p LEFT JOIN $ct AS c ON c.id=p.category_id WHERE p.category_id IS NOT NULL AND c.id IS NULL ORDER BY p.id");
    $failedCode=null;
    try{$db->exec("ALTER TABLE $pt ADD FOREIGN KEY(category_id) REFERENCES $ct(id) ON DELETE RESTRICT ON UPDATE RESTRICT");}
    catch(PDOException $error){if((int)($error->errorInfo[1]??0)!==1452){throw $error;}$failedCode=1452;}
    $fkAfterFailure=se114Count($db,'SELECT COUNT(*) AS total FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? AND COLUMN_NAME=? AND REFERENCED_TABLE_NAME IS NOT NULL',[$products,'category_id']);
    // Confirmed mapping from this import: obsolete category 999 means category 10.
    $db->prepare("UPDATE $pt SET category_id=? WHERE id=? AND category_id=?")->execute([10,104,999]);
    $orphansAfter=se114Count($db,"SELECT COUNT(*) AS total FROM $pt AS p LEFT JOIN $ct AS c ON c.id=p.category_id WHERE p.category_id IS NOT NULL AND c.id IS NULL");
    if($orphansAfter!==0){throw new RuntimeException('Resolve orphaned category references first');}
    $db->exec("ALTER TABLE $pt ADD FOREIGN KEY(category_id) REFERENCES $ct(id) ON DELETE RESTRICT ON UPDATE RESTRICT");
    $newOrphanCode=null;
    try{$db->prepare("UPDATE $pt SET category_id=? WHERE id=?")->execute([999,101]);}
    catch(PDOException $error){if((int)($error->errorInfo[1]??0)!==1452){throw $error;}$newOrphanCode=1452;}
    $fixed=se114Rows($db,"SELECT p.id,p.title,c.title AS category FROM $pt AS p JOIN $ct AS c ON c.id=p.category_id WHERE p.id=?",[104])[0];
    $rows=[['Потерянная связь','104 → '.$orphans[0]['category_id']],['Первый ADD FOREIGN KEY',(string)$failedCode.': связь не добавлена'],['Исправленный товар',$fixed['id'].' '.$fixed['title'].' → '.$fixed['category']],['Потерянных связей после исправления',(string)$orphansAfter],['Новая попытка указать 999',(string)$newOrphanCode.': запрещена'],['Товаров осталось',(string)se114Count($db,"SELECT COUNT(*) AS total FROM $pt")]];
    $checks=['orphan_identified'=>count($orphans)===1 && (int)$orphans[0]['id']===104 && (int)$orphans[0]['category_id']===999,'fk_add_rejected'=>$failedCode===1452 && $fkAfterFailure===0,'orphan_resolved_without_deletion'=>$orphansAfter===0 && $fixed['category']==='Посуда','fk_installed'=>se114Count($db,'SELECT COUNT(*) AS total FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? AND COLUMN_NAME=? AND REFERENCED_TABLE_NAME=?',[$products,'category_id',$categories])===1,'new_orphan_rejected'=>$newOrphanCode===1452,'six_products_preserved'=>se114Count($db,"SELECT COUNT(*) AS total FROM $pt")===6];
    $details=['orphan_rows'=>$orphans,'failed_alter_driver_code'=>$failedCode,'bad_update_driver_code'=>$newOrphanCode,'foreign_key_absent_after_failure'=>$fkAfterFailure===0];
} finally {
    if($db->inTransaction()){$db->rollBack();}
    foreach([$products,$categories,$progress,$journal] as $name){
        if($created[$name]??false){$db->exec('DROP TABLE '.se114Name($name));}
    }
}
$remaining=se114Count($db,'SELECT COUNT(*) AS total FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME IN (?,?,?,?)',[$products,$categories,$progress,$journal]);
$checks['owned_tables_removed']=$remaining===0;
$db=null;
se114Report('mysql114-schema-foreign-key-after-cleanup', ['Шаг', 'Результат'], $rows, $checks, $runtime, $details);
echo 'mysql114-schema-foreign-key-after-cleanup: saved result.html and result.json
';

mysql114-schema-foreign-key-after-cleanup: saved result.html and result.json

После php example.php откройте result.html и сопоставьте строки с таблицей выше. Данные, helper, README и проверка собственных таблиц входят в самостоятельный пример.

<?php
declare(strict_types=1);
require __DIR__.'/se114-helper.php';
$db=se114Connect();
$runtime=['php'=>PHP_VERSION,'client'=>$db->getAttribute(PDO::ATTR_CLIENT_VERSION),'server'=>$db->getAttribute(PDO::ATTR_SERVER_VERSION)];
$settings=se114Rows($db,'SELECT @@SESSION.sql_mode AS sql_mode,@@SESSION.foreign_key_checks AS fk_checks')[0];
$modes=explode(',', $settings['sql_mode']);
if(!array_intersect($modes,['STRICT_TRANS_TABLES','STRICT_ALL_TABLES']) || (int)$settings['fk_checks']!==1){throw new RuntimeException('Use strict SQL mode and enabled foreign-key checks in the teaching database');}
$prefix='mysql114_schema_'.bin2hex(random_bytes(8));
$products=$prefix.'_products'; $categories=$prefix.'_categories'; $progress=$prefix.'_progress'; $journal=$prefix.'_migrations';
$created=[]; $details=[];
try {
    se114Create($db,$products,'id INT UNSIGNED NOT NULL PRIMARY KEY,title VARCHAR(80) NOT NULL,sku VARCHAR(32) NOT NULL,city VARCHAR(40) NOT NULL,category_id INT UNSIGNED NULL',$created);
    se114Seed($db,$products); $pt=se114Name($products);
se114Create($db,$journal,'version VARCHAR(80) NOT NULL PRIMARY KEY,fingerprint CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL',$created);
    $jt=se114Name($journal); $ddlRuns=0;
    // Controlled return after DDL and before the journal INSERT, not a process crash.
    $first=se114Migrate($db,$products,$journal,true,$ddlRuns);
    $journalAfterFirst=se114Count($db,"SELECT COUNT(*) AS total FROM $jt");
    $columnAfterFirst=se114Column($db,$products,'delivery_region')!==null;
    $recovered=se114Migrate($db,$products,$journal,false,$ddlRuns);
    $repeat=se114Migrate($db,$products,$journal,false,$ddlRuns);
    $journalAfterRecovery=se114Count($db,"SELECT COUNT(*) AS total FROM $jt");
    $beforeDrift=se114Column($db,$products,'delivery_region');
    // Separate controlled schema drift: the same name alone is not enough.
    $db->exec("ALTER TABLE $pt MODIFY COLUMN delivery_region VARCHAR(24) NULL DEFAULT NULL");
    $driftRejected=false;
    try{se114Migrate($db,$products,$journal,false,$ddlRuns);}
    catch(UnexpectedValueException $error){$driftRejected=true;}
    $rows=[['Управляемая остановка',$first,'Столбец: да; журнал: '.$journalAfterFirst],['Продолжение',$recovered,'ADD COLUMN выполнен: '.$ddlRuns.' раз'],['Повтор после завершения',$repeat,'Записей журнала: '.$journalAfterRecovery],['Проверка чужого изменения','varchar(24) вместо varchar(16)',$driftRejected?'Остановлено: схема не совпадает':'НЕОЖИДАННО ПРИНЯТО']];
    $checks=['ddl_applied_before_journal'=>$first==='column_ready_journal_pending' && $journalAfterFirst===0 && $columnAfterFirst,'recovery_without_second_ddl'=>$recovered==='journal_recorded' && $ddlRuns===1,'replay_idempotent'=>$repeat==='already_recorded' && $journalAfterRecovery===1,'full_column_contract_checked'=>$beforeDrift['COLUMN_TYPE']==='varchar(16)' && $beforeDrift['IS_NULLABLE']==='YES' && $beforeDrift['COLUMN_DEFAULT']===null,'schema_drift_rejected'=>$driftRejected && se114Column($db,$products,'delivery_region')['COLUMN_TYPE']==='varchar(24)','journal_not_duplicated'=>se114Count($db,"SELECT COUNT(*) AS total FROM $jt")===1,'six_rows_preserved'=>se114Count($db,"SELECT COUNT(*) AS total FROM $pt")===6];
    $details=['controlled_stop_not_process_crash'=>true,'ddl_add_column_count'=>$ddlRuns,'journal_after_controlled_stop'=>$journalAfterFirst,'journal_after_recovery'=>$journalAfterRecovery,'column_before_drift'=>$beforeDrift,'drift_rejected'=>$driftRejected];
} finally {
    if($db->inTransaction()){$db->rollBack();}
    foreach([$products,$categories,$progress,$journal] as $name){
        if($created[$name]??false){$db->exec('DROP TABLE '.se114Name($name));}
    }
}
$remaining=se114Count($db,'SELECT COUNT(*) AS total FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME IN (?,?,?,?)',[$products,$categories,$progress,$journal]);
$checks['owned_tables_removed']=$remaining===0;
$db=null;
se114Report('mysql114-schema-journal-recovery', ['Шаг', 'Состояние', 'Что наблюдаем'], $rows, $checks, $runtime, $details);
echo 'mysql114-schema-journal-recovery: saved result.html and result.json
';

mysql114-schema-journal-recovery: saved result.html and result.json

После php example.php откройте result.html и сопоставьте строки с таблицей выше. Данные, helper, README и проверка собственных таблиц входят в самостоятельный пример.