# Skill: Database Operations

Skill ID: `database-operations`

Rules
- Tables: `{$wpdb->prefix}presszone_translate_*`.
- Table DDL: `$wpdb->get_charset_collate()` and dbDelta-compatible SQL.
- Engine: InnoDB where supported.
- Dynamic SQL: `$wpdb->prepare()` only — this includes ALL dynamic SQL, even `SHOW TABLES LIKE` and `SHOW COLUMNS FROM`. No exceptions.
- LIKE filters: `$wpdb->esc_like()` then prepare.
- ORDER BY/status: strict allowlists; `sanitize_sql_orderby()` only after allowlist.
- Never concatenate request data into SQL.
- NEVER use `esc_sql()` — it is deprecated and insufficient. Always use `$wpdb->prepare()` instead.
- Validate IDs/language/status types before writes.
- Multi-step mutations: use transactions where supported.
- Check `$wpdb->last_error` after writes; return `WP_Error` on failure.
- Add indexes for hot paths; use `EXPLAIN` for slow queries.
- Add proper indexes for performance on frequently queried columns.
- Use `LIMIT/OFFSET` for pagination.
- UI/layout rule from prior guidance: do not use table-based layout; use Grid/Flex.
- Never use `<table>` elements for layout; use `<div>` with CSS Grid/Flexbox.
- Use explicit `%d/%s/%f` format arrays in insert/update/delete helpers.

Mistakes to avoid
| Mistake | Fix |
|---|---|
| Raw SQL with variables | Use `$wpdb->prepare()` |
| `esc_sql()` for escaping | Replace with `$wpdb->prepare()` — `esc_sql()` is deprecated |
| `SHOW TABLES`/`SHOW COLUMNS` without prepare | Wrap with `$wpdb->prepare()` like any other dynamic SQL |
| Hardcoded table names | Use `$wpdb->prefix` + plugin table suffix |
| Missing insert/update formats | Pass `%d/%s/%f` format arrays |
| LIKE without escaping | Use `$wpdb->esc_like()` |
| Invalid dbDelta syntax | Keep dbDelta key/index syntax |
| Unbounded query scans | Add pagination and indexes |
| Missing write format definitions | Provide explicit `%d/%s/%f` arrays |
| String-concatenated ORDER BY | Use allowlist + optional `sanitize_sql_orderby()` |
| `$wpdb->update()` without column allowlist | Filter `$data` keys through explicit allowlist before write |