# Skill: Database Operations

## Identity
- **Skill ID**: `database-operations`
- **Domain**: WordPress Database Layer
- **Technologies**: MySQL/MariaDB, WordPress $wpdb
- **Source Agent**: `database-expert.md`

## When to Load This Skill
- Task involves database queries
- Creating or modifying custom tables
- Working with data retrieval or storage
- Implementing search functionality
- Files matching: `includes/**/Database/*.php`, `includes/**/Query.php`

## Core Patterns

### Table Naming Convention
```php
global $wpdb;
$table = $wpdb->prefix . 'presszone_international_languages';

// Actual table names:
// wp_presszone_international_languages
// wp_presszone_international_translations
// wp_presszone_international_settings
```

### Prepared Statements (MANDATORY)
```php
// CORRECT - Always use $wpdb->prepare()
$result = $wpdb->get_row($wpdb->prepare(
    "SELECT * FROM {$table_name} WHERE id = %d AND status = %s",
    $id,
    $status
));

// WRONG - SQL Injection vulnerability - PLUGIN REJECTION
$results = $wpdb->get_results("SELECT * FROM $table WHERE id = $id");
```

### LIKE Queries (Escape Required)
```php
$search_term = sanitize_text_field($search);
$search_like = '%' . $wpdb->esc_like($search_term) . '%';

$results = $wpdb->get_results($wpdb->prepare(
    "SELECT * FROM {$table_name} WHERE title LIKE %s",
    $search_like
));
```

### Table Creation (dbDelta)
```php
public static function install() {
    global $wpdb;
    $charset_collate = $wpdb->get_charset_collate();
    $table_name = $wpdb->prefix . 'presszone_international_languages';

    $sql = "CREATE TABLE {$table_name} (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        code varchar(10) NOT NULL,
        name varchar(100) NOT NULL,
        native_name varchar(100) NOT NULL,
        is_default tinyint(1) DEFAULT 0,
        is_active tinyint(1) DEFAULT 1,
        sort_order int(11) DEFAULT 0,
        created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
        PRIMARY KEY  (id),
        UNIQUE KEY code (code),
        KEY is_active (is_active)
    ) {$charset_collate};";

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql);
}
```

### Batch Operations with Transactions
```php
function presszone_international_batch_update($items, $status) {
    global $wpdb;

    $wpdb->query('START TRANSACTION');

    try {
        $table_name = $wpdb->prefix . 'presszone_international_translations';

        foreach ($items as $id) {
            $result = $wpdb->update(
                $table_name,
                ['status' => $status, 'updated_at' => current_time('mysql')],
                ['id' => absint($id)],
                ['%s', '%s'],
                ['%d']
            );

            if ($result === false) {
                throw new Exception($wpdb->last_error);
            }
        }

        $wpdb->query('COMMIT');
        return true;

    } catch (Exception $e) {
        $wpdb->query('ROLLBACK');
        error_log('Batch update error: ' . $e->getMessage());
        return new WP_Error('batch_failed', 'Batch update failed');
    }
}
```

## Anti-Patterns (Forbidden)

| Mistake | Fix |
|---------|-----|
| No `$wpdb->prepare()` | ALWAYS use prepare() for dynamic queries |
| Hardcoded table names | Use `$wpdb->prefix . 'presszone_international_*'` |
| Missing formats in insert | Specify `['%d', '%s']` in `insert()` calls |
| dbDelta syntax errors | PRIMARY KEY must have 2 spaces before it |
| Forgetting wildcard escape | Use `$wpdb->esc_like()` for LIKE queries |
| No error checking | Check `$wpdb->last_error` after operations |
| Dynamic ORDER BY | Whitelist validate or use `sanitize_sql_orderby()` |

## WordPress.org Compliance

### Security Requirements
- ALL queries with user input MUST use `$wpdb->prepare()`
- Table names MUST use WordPress prefix
- Charset MUST use `$wpdb->get_charset_collate()`
- Input validation before ANY database operation

### Placeholder Reference

| Placeholder | Type | Example |
|-------------|------|---------|
| `%d` | Integer | `$wpdb->prepare("...id = %d", 123)` |
| `%s` | String | `$wpdb->prepare("...slug = %s", 'slug')` |
| `%f` | Float | `$wpdb->prepare("...val = %f", 1.23)` |

## Integration with Other Skills
- **Often combined with**: `wordpress-php-integration`, `wordpress-security`
- **For caching**: Consider transients or object cache
- **For complex queries**: May need `api-integration` for external data

## Quick Reference

### Common Query Methods
```php
$wpdb->get_var($query);      // Single value
$wpdb->get_row($query);      // Single row (object)
$wpdb->get_results($query);  // Multiple rows
$wpdb->get_col($query);      // Single column
$wpdb->insert($table, $data, $format);
$wpdb->update($table, $data, $where, $format, $where_format);
$wpdb->delete($table, $where, $where_format);
$wpdb->query($query);        // Generic query
```

### Error Handling
```php
$result = $wpdb->insert($table_name, $data, $formats);

if ($result === false) {
    error_log('Database error: ' . $wpdb->last_error);
    return new WP_Error('db_error', 'Database operation failed');
}

return $wpdb->insert_id;
```

## Validation Checklist
- [ ] All queries use `$wpdb->prepare()` for dynamic values
- [ ] Table names use `$wpdb->prefix`
- [ ] LIKE queries use `$wpdb->esc_like()`
- [ ] ORDER BY values are whitelisted
- [ ] Error handling with `$wpdb->last_error`
- [ ] Transactions for multi-table operations
- [ ] Input sanitized before queries