73 lines
1.8 KiB
Markdown
73 lines
1.8 KiB
Markdown
# Database Pattern Notes
|
|
|
|
This file keeps examples and rationale. The normative rules live in `contract.md`.
|
|
|
|
## Cursor Safety
|
|
|
|
Wrong:
|
|
|
|
```go
|
|
rows, _ := tx.Query("SELECT id FROM machines")
|
|
for rows.Next() {
|
|
var id string
|
|
rows.Scan(&id)
|
|
tx.Exec("UPDATE machines SET processed=1 WHERE id=?", id)
|
|
}
|
|
```
|
|
|
|
Correct:
|
|
|
|
```go
|
|
rows, _ := tx.Query("SELECT id FROM machines")
|
|
var ids []string
|
|
for rows.Next() {
|
|
var id string
|
|
rows.Scan(&id)
|
|
ids = append(ids, id)
|
|
}
|
|
rows.Close()
|
|
|
|
for _, id := range ids {
|
|
tx.Exec("UPDATE machines SET processed=1 WHERE id=?", id)
|
|
}
|
|
```
|
|
|
|
## GORM Virtual Fields
|
|
|
|
```go
|
|
Count int `gorm:"-"`
|
|
DisplayName string `gorm:"-:migration"`
|
|
```
|
|
|
|
## SQL Header Example
|
|
|
|
```sql
|
|
-- Tables affected: supplier, lot_log
|
|
-- recovery.not-started: No action required.
|
|
-- recovery.partial: DELETE FROM parts_log WHERE created_by = 'migration';
|
|
-- recovery.completed: Same as partial.
|
|
-- verify: No orphaned supplier_code | SELECT supplier_code FROM parts_log pl LEFT JOIN supplier s ON s.supplier_code = pl.supplier_code WHERE s.supplier_code IS NULL AND pl.supplier_code IS NOT NULL AND pl.supplier_code != '' LIMIT 1
|
|
```
|
|
|
|
## Docker Validation Example
|
|
|
|
```bash
|
|
docker run -d --name pf_test \
|
|
-e MYSQL_ROOT_PASSWORD=test -e MYSQL_DATABASE=RFQ_LOG \
|
|
mariadb:11.8 --character-set-server=utf8mb4 --collation-server=utf8mb4_uca1400_ai_ci
|
|
|
|
docker exec -i pf_test mariadb -uroot -ptest RFQ_LOG < prod_dump.sql
|
|
|
|
./pfs -migrate-dsn "root:test@tcp(127.0.0.1:3306)/RFQ_LOG?parseTime=true&charset=utf8mb4&multiStatements=true" \
|
|
-no-backup -verbose
|
|
```
|
|
|
|
## Legacy FK Repair Pattern
|
|
|
|
```sql
|
|
INSERT IGNORE INTO parent (name)
|
|
SELECT DISTINCT c.fk_col FROM child c
|
|
LEFT JOIN parent p ON p.name = c.fk_col
|
|
WHERE p.name IS NULL AND c.fk_col IS NOT NULL AND c.fk_col != '';
|
|
```
|