# MySQL Migration Summary

## ✅ Completed Tasks

### 1. Database Dump Conversion
- ✅ Created `database_dump/convert_mssql_to_mysql.py` - Converts existing MSSQL dumps to MySQL format
- ✅ Created `scripts/dump_to_mysql.py` - Direct MySQL dump from MSSQL source
- ✅ Converted all existing dump files to MySQL format
- ✅ Output location: `database_dump/mysql/`

### 2. Database Connection Updates
- ✅ Updated `aumentum_browser_service.py` to support both MSSQL and MySQL
  - Added `_get_mysql_connection()` method
  - Updated `_get_db_connection()` to detect database type
  - Supports both `pymysql` and `mysql.connector` drivers
- ✅ Updated `DEFAULT_DB_CONFIG` with MySQL configuration options

### 3. Documentation
- ✅ Created `DATABASE_CONNECTIONS.md` - Lists all 40+ database connection points
- ✅ Created `MYSQL_MIGRATION_SUMMARY.md` - This file

---

## 📁 MySQL Dump Files

All MySQL-compatible files are in: `database_dump/mysql/`

### Schema Files
- `schema_mysql_20251113_185155.sql` - Complete schema for all 168 tables

### Data Files (Sample - 1000 rows each)
- `data_mysql_alf_content_data.sql`
- `data_mysql_alf_content_url.sql`
- `data_mysql_alf_node.sql`
- `data_mysql_alf_node_properties.sql`
- `data_mysql_alf_qname.sql`
- `data_mysql_lr_dictionary.sql`
- `data_mysql_lr_party.sql`
- `data_mysql_lr_source_document.sql`
- `data_mysql_lr_transaction.sql`
- `data_mysql_lr_transaction_document.sql`

---

## 🔧 Configuration Changes

### To Use MySQL Instead of MSSQL

Update `aumentum_browser_service.py` DEFAULT_DB_CONFIG:

```python
DEFAULT_DB_CONFIG = {
    "type": "mysql",  # Changed from "mssql"
    "host": "localhost",  # or MySQL server IP
    "port": 3306,  # Changed from 1433
    "database": "LRS43",
    "username": "root",
    "password": "your_mysql_password",
    "charset": "utf8mb4",
}
```

### Install MySQL Driver

```bash
# Option 1: pymysql (recommended)
pip install pymysql

# Option 2: mysql-connector-python
pip install mysql-connector-python
```

---

## 📋 Next Steps

### 1. Create MySQL Database

```bash
mysql -u root -p -e "CREATE DATABASE LRS43 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
```

### 2. Import Schema

```bash
cd database_dump/mysql
mysql -u root -p LRS43 < schema_mysql_20251113_185155.sql
```

### 3. Import Data (Sample)

```bash
cd database_dump/mysql
for f in data_mysql_*.sql; do
    echo "Importing $f..."
    mysql -u root -p LRS43 < "$f"
done
```

### 4. Update Application Configuration

Edit `aumentum_browser_service.py`:
- Change `"type": "mssql"` to `"type": "mysql"`
- Update connection parameters (host, port, password)

### 5. Update All Script Files

See `DATABASE_CONNECTIONS.md` for list of 40+ files that need updating:
- Update `DB_CONFIG` dictionaries
- Change connection functions to use MySQL
- Replace `pyodbc` with `pymysql` or `mysql.connector`

### 6. Test Connection

```python
from aumentum_browser_service import AumentumBrowserService, DEFAULT_DB_CONFIG

# Update config for MySQL
DEFAULT_DB_CONFIG["type"] = "mysql"
DEFAULT_DB_CONFIG["host"] = "localhost"
DEFAULT_DB_CONFIG["port"] = 3306

service = AumentumBrowserService(db_config=DEFAULT_DB_CONFIG)
# Test connection
```

---

## 🔄 Conversion Details

### MSSQL → MySQL Syntax Changes

| MSSQL | MySQL |
|-------|-------|
| `[column_name]` | `` `column_name` `` |
| `IDENTITY(1,1)` | `AUTO_INCREMENT` |
| `nvarchar(MAX)` | `TEXT` |
| `nvarchar(n)` | `VARCHAR(n/2)` |
| `numeric(p,s)` | `DECIMAL(p,s)` |
| `uniqueidentifier` | `CHAR(36)` |
| `GO` | (removed) |
| `LRSAdmin.table` | `table` |

### Additional MySQL Features

- Added `ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`
- Added `SET FOREIGN_KEY_CHECKS=0/1` for imports
- Converted datetime formats
- Proper string escaping for MySQL

---

## 📝 Files Created/Modified

### New Files
1. `database_dump/convert_mssql_to_mysql.py` - Conversion script
2. `scripts/dump_to_mysql.py` - Direct MySQL dump script
3. `DATABASE_CONNECTIONS.md` - Connection points documentation
4. `MYSQL_MIGRATION_SUMMARY.md` - This file
5. `database_dump/mysql/` - All converted MySQL files

### Modified Files
1. `aumentum_browser_service.py` - Added MySQL support

---

## ⚠️ Important Notes

1. **Sample Data Only**: Current dumps contain only 1000 rows per table (intentional for testing)
2. **Full Migration**: For production, modify `scripts/dump_to_mysql.py` to remove `limit=1000`
3. **Connection Testing**: Test MySQL connection before updating all scripts
4. **Data Validation**: Verify data integrity after migration
5. **Backup**: Always backup MSSQL database before migration

---

## 🐛 Troubleshooting

### MySQL Connection Errors

**Error: "MySQL driver not found"**
```bash
pip install pymysql
```

**Error: "Access denied"**
- Check MySQL user permissions
- Verify password
- Ensure user has access to LRS43 database

**Error: "Unknown database"**
```bash
mysql -u root -p -e "CREATE DATABASE LRS43 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
```

### Import Errors

**Error: "Foreign key constraint fails"**
- Ensure `SET FOREIGN_KEY_CHECKS=0` is at the start of import files
- Import tables in dependency order

**Error: "Invalid datetime"**
- Check datetime format conversions
- MySQL may need explicit timezone handling

---

## 📚 Resources

- [pymysql Documentation](https://pymysql.readthedocs.io/)
- [MySQL Connector/Python](https://dev.mysql.com/doc/connector-python/en/)
- [MySQL Migration Guide](https://dev.mysql.com/doc/refman/8.0/en/migration.html)