# Query Syntax Translator Guide

## Overview

The MyGB system includes an automatic query syntax translator that converts SQLite-specific SQL syntax to MySQL-compatible syntax. This allows the application code to use SQLite syntax while seamlessly working with MySQL databases.

## How It Works

When the database type is set to MySQL in `config/database.php`, all SQL queries are automatically translated before execution. The translation happens transparently in the `MySQLiPDOWrapper` class methods:

- `prepare()` - Translates queries before preparing statements
- `exec()` - Translates queries before executing DDL statements
- `query()` - Translates queries before executing simple queries

## Supported Translations

### 1. INSERT OR IGNORE → INSERT IGNORE

**SQLite Syntax:**
```sql
INSERT OR IGNORE INTO students (nis, name) VALUES (?, ?)
```

**MySQL Translation:**
```sql
INSERT IGNORE INTO students (nis, name) VALUES (?, ?)
```

**Use Case:** Insert a record only if it doesn't already exist (based on unique constraints).

### 2. INSERT OR REPLACE → REPLACE INTO

**SQLite Syntax:**
```sql
INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)
```

**MySQL Translation:**
```sql
REPLACE INTO settings (key, value) VALUES (?, ?)
```

**Use Case:** Insert a new record or replace an existing one with the same primary/unique key.

### 3. PRAGMA Statements

#### PRAGMA foreign_keys

**SQLite Syntax:**
```sql
PRAGMA foreign_keys = ON
```

**MySQL Translation:**
```sql
SET foreign_key_checks = 1
```

**Note:** InnoDB engine has foreign keys enabled by default.

#### PRAGMA table_info

**SQLite Syntax:**
```sql
PRAGMA table_info(students)
PRAGMA table_info('students')
```

**MySQL Translation:**
```sql
SHOW COLUMNS FROM students
```

**Use Case:** Get information about table columns.

#### PRAGMA table_list

**SQLite Syntax:**
```sql
PRAGMA table_list
```

**MySQL Translation:**
```sql
SHOW TABLES
```

**Use Case:** List all tables in the database.

#### PRAGMA index_list

**SQLite Syntax:**
```sql
PRAGMA index_list(students)
```

**MySQL Translation:**
```sql
SHOW INDEX FROM students
```

**Use Case:** List all indexes on a table.

### 4. Identifier Quoting

**SQLite Syntax (double quotes):**
```sql
SELECT "name", "grade" FROM students
```

**MySQL Translation (backticks):**
```sql
SELECT `name`, `grade` FROM students
```

**Use Case:** Quote identifiers, especially MySQL reserved keywords.

### 5. Date/Time Functions

#### datetime('now')

**SQLite Syntax:**
```sql
INSERT INTO logs (message, created_at) VALUES (?, datetime('now'))
```

**MySQL Translation:**
```sql
INSERT INTO logs (message, created_at) VALUES (?, NOW())
```

#### date('now')

**SQLite Syntax:**
```sql
SELECT * FROM logs WHERE date = date('now')
```

**MySQL Translation:**
```sql
SELECT * FROM logs WHERE date = CURDATE()
```

#### time('now')

**SQLite Syntax:**
```sql
SELECT * FROM logs WHERE time = time('now')
```

**MySQL Translation:**
```sql
SELECT * FROM logs WHERE time = CURTIME()
```

### 6. AUTOINCREMENT Keyword

**SQLite Syntax:**
```sql
ALTER TABLE students MODIFY id INTEGER AUTOINCREMENT
```

**MySQL Translation:**
```sql
ALTER TABLE students MODIFY id INTEGER AUTO_INCREMENT
```

## Usage Examples

### Example 1: Using INSERT OR IGNORE

```php
<?php
require_once 'includes/db.php';

$db = get_db();

// This works with both SQLite and MySQL
// When using MySQL, it's automatically translated to INSERT IGNORE
$stmt = $db->prepare('INSERT OR IGNORE INTO students (nis, name, grade) VALUES (?, ?, ?)');
$stmt->execute(['12345', 'John Doe', '10A']);
```

### Example 2: Using PRAGMA for Table Info

```php
<?php
require_once 'includes/db.php';

$db = get_db();

// This works with both SQLite and MySQL
// When using MySQL, it's automatically translated to SHOW COLUMNS
$result = $db->query('PRAGMA table_info(students)');
$columns = $result->fetchAll();

foreach ($columns as $column) {
    echo $column['name'] . "\n";
}
```

### Example 3: Complex Query with Multiple Translations

```php
<?php
require_once 'includes/db.php';

$db = get_db();

// Multiple SQLite-specific features in one query
// All are automatically translated when using MySQL
$stmt = $db->prepare('
    INSERT OR IGNORE INTO "students" (nis, name, created_at) 
    VALUES (?, ?, datetime(\'now\'))
');
$stmt->execute(['12345', 'John Doe']);
```

**Translated to (when using MySQL):**
```sql
INSERT IGNORE INTO `students` (nis, name, created_at) 
VALUES (?, ?, NOW())
```

## Testing the Translator

### Unit Tests

Run the unit test script to verify all translation rules:

```bash
php test_query_translator.php
```

This tests all translation patterns without requiring a database connection.

### Integration Tests

Run the integration test to verify translations work with actual MySQL operations:

```bash
php test_query_translator_integration.php
```

**Prerequisites:**
- MySQL server must be running
- `config/database.php` must be configured with MySQL settings
- Database must be accessible

## Best Practices

### 1. Write SQLite-Compatible Queries

Write your queries using SQLite syntax. The translator will handle MySQL conversion automatically:

```php
// GOOD - SQLite syntax, works everywhere
$stmt = $db->prepare('INSERT OR IGNORE INTO students (nis, name) VALUES (?, ?)');

// AVOID - MySQL-specific syntax won't work with SQLite
$stmt = $db->prepare('INSERT IGNORE INTO students (nis, name) VALUES (?, ?)');
```

### 2. Use Prepared Statements

Always use prepared statements for queries with user input:

```php
// GOOD - Prepared statement
$stmt = $db->prepare('INSERT OR IGNORE INTO students (nis, name) VALUES (?, ?)');
$stmt->execute([$nis, $name]);

// BAD - String concatenation (SQL injection risk)
$db->exec("INSERT OR IGNORE INTO students (nis, name) VALUES ('$nis', '$name')");
```

### 3. Test with Both Databases

If possible, test your application with both SQLite and MySQL to ensure compatibility:

```php
// config/database.php
define('DB_TYPE', 'sqlite'); // Test with SQLite
// define('DB_TYPE', 'mysql'); // Test with MySQL
```

### 4. Handle Database-Specific Features Carefully

Some features may not translate perfectly. Be aware of differences:

- **Transactions:** Both support transactions, but behavior may differ
- **Data Types:** Some type conversions are automatic, but verify data integrity
- **Performance:** MySQL may perform differently than SQLite for complex queries

## Limitations

### 1. Complex PRAGMA Statements

Only common PRAGMA statements are translated. Complex or less common PRAGMA commands may not be supported.

### 2. Advanced Date/Time Functions

Only basic date/time functions are translated. Complex date arithmetic may require database-specific code.

### 3. Full-Text Search

SQLite's FTS and MySQL's FULLTEXT search have different syntax and are not automatically translated.

### 4. JSON Functions

SQLite and MySQL have different JSON function syntax. These are not automatically translated.

## Troubleshooting

### Query Not Translating

**Problem:** A SQLite query doesn't work with MySQL.

**Solution:**
1. Check if the syntax is supported (see Supported Translations above)
2. Enable error reporting to see the actual error
3. Test the translation manually using `translate_query_to_mysql()`

```php
$sqliteQuery = 'INSERT OR IGNORE INTO test (id) VALUES (1)';
$mysqlQuery = translate_query_to_mysql($sqliteQuery);
echo "Translated: $mysqlQuery\n";
```

### Syntax Error After Translation

**Problem:** MySQL reports a syntax error after translation.

**Solution:**
1. Verify the original SQLite query is valid
2. Check for unsupported SQLite features
3. Review the translated query for issues
4. Consider writing database-specific code for complex cases

### Performance Issues

**Problem:** Queries are slower after migrating to MySQL.

**Solution:**
1. Add appropriate indexes to MySQL tables
2. Optimize queries for MySQL's query planner
3. Use EXPLAIN to analyze query execution
4. Consider MySQL-specific optimizations

## Advanced Usage

### Manual Translation

You can manually translate queries if needed:

```php
$sqliteQuery = 'INSERT OR IGNORE INTO test (id) VALUES (1)';
$mysqlQuery = translate_query_to_mysql($sqliteQuery);
echo $mysqlQuery; // INSERT IGNORE INTO test (id) VALUES (1)
```

### Conditional Database-Specific Code

For features that can't be automatically translated:

```php
$dbType = defined('DB_TYPE') ? DB_TYPE : 'sqlite';

if ($dbType === 'mysql') {
    // MySQL-specific code
    $db->exec('SET SESSION sql_mode = "STRICT_ALL_TABLES"');
} else {
    // SQLite-specific code
    $db->exec('PRAGMA foreign_keys = ON');
}
```

### Extending the Translator

To add custom translation rules, edit the `translate_query_to_mysql()` function in `includes/db.php`:

```php
function translate_query_to_mysql($sqliteQuery) {
    $mysqlQuery = $sqliteQuery;
    
    // Add your custom translation rules here
    $mysqlQuery = preg_replace('/YOUR_PATTERN/', 'YOUR_REPLACEMENT', $mysqlQuery);
    
    // ... existing translations ...
    
    return $mysqlQuery;
}
```

## See Also

- [MySQL Migration Guide](MYSQL_MIGRATION_GUIDE.md) - Complete migration instructions
- [Database Configuration](config/database.php.example) - Configuration template
- [Schema Converter](includes/db.php) - Schema conversion function
