frama-c

[ai-review] / critic / sql-error-handling.md

# SQL Error Handling Critic Framework (ISO/IEC 9075:2023)

This framework guides the Critic role when evaluating SQL code, stored procedures, and database applications for error handling, robustness, and reliability. This critic focuses on consistent error checking, proper error reporting, resource cleanup, and graceful failure recovery to ensure reliable database operations.

## SQL Error Handling Evaluation Areas

### 1. Error Detection and Checking
**What to Look For:**
- Consistent error checking in procedures and triggers
- Use of standard SQLSTATE codes for error reporting
- Proper handling of constraint violations and transaction failures
- Comprehensive error condition detection
- Clear error propagation strategies

**Common Problems:**
- Ignoring errors in stored procedures or triggers
- Inconsistent error handling patterns
- Missing error checking for critical operations
- Poor error condition detection
- Unclear error propagation strategies

**Evaluation Questions:**
- Are all errors detected and handled consistently?
- Are SQLSTATE codes used for error reporting?
- Are constraint violations handled gracefully?
- Are error conditions detected comprehensively?
- Are error propagation strategies clear and consistent?

### 2. Error Reporting and Logging
**What to Look For:**
- Clear and actionable error messages
- Proper use of SQLSTATE codes and error information
- Comprehensive error logging and audit trails
- User-friendly error messages for application users
- Detailed error information for debugging and troubleshooting

**Common Problems:**
- Unclear or inconsistent error messages
- Missing error information for debugging
- Poor error logging and audit trails
- Non-actionable error messages
- Inadequate error context information

**Evaluation Questions:**
- Are error messages clear and actionable?
- Is comprehensive error information provided?
- Are errors logged for audit and debugging?
- Are user-friendly error messages provided?
- Is error context information captured?

### 3. Resource Cleanup and Recovery
**What to Look For:**
- Proper resource cleanup on error paths
- Transaction rollback on failure
- Cursor and connection cleanup
- Memory and temporary object cleanup
- Graceful degradation on errors

**Common Problems:**
- Resource leaks or uncommitted transactions on error paths
- Missing cursor cleanup
- Inadequate memory cleanup
- Poor transaction rollback handling
- No graceful degradation strategies

**Evaluation Questions:**
- Are transactions rolled back on failure?
- Are cursors and connections properly cleaned up?
- Is memory and temporary object cleanup performed?
- Are resources cleaned up on all error paths?
- Is graceful degradation implemented?

### 4. Transaction Error Handling
**What to Look For:**
- Proper transaction boundary management
- Consistent rollback strategies
- Error handling within transaction blocks
- Proper isolation level handling
- Deadlock detection and resolution

**Common Problems:**
- Inconsistent transaction rollback on errors
- Poor deadlock handling
- Missing error handling within transactions
- Inappropriate isolation level usage
- Incomplete transaction cleanup

**Evaluation Questions:**
- Are transactions rolled back consistently on errors?
- Is deadlock detection and resolution implemented?
- Are errors handled properly within transactions?
- Are appropriate isolation levels used?
- Is transaction cleanup performed correctly?

### 5. Application Error Handling
**What to Look For:**
- Consistent error handling across application layers
- Proper error propagation from database to application
- User-friendly error presentation
- Error recovery and retry mechanisms
- Comprehensive error documentation

**Common Problems:**
- Inconsistent error handling across application layers
- Poor error propagation from database
- Non-user-friendly error presentation
- Missing error recovery mechanisms
- Inadequate error documentation

**Evaluation Questions:**
- Is error handling consistent across application layers?
- Is error information properly propagated from database?
- Are errors presented in a user-friendly manner?
- Are error recovery and retry mechanisms implemented?
- Is error handling comprehensively documented?

## ISO/IEC Standards-Specific Criticism Process

### Step 1: Error Detection Analysis
1. **Check Error Detection**: Are all errors detected and handled?
2. **Evaluate Error Checking**: Is error checking implemented consistently?
3. **Assess Error Propagation**: Are error propagation strategies clear?
4. **Review Error Conditions**: Are error conditions detected comprehensively?

### Step 2: Error Reporting Assessment
1. **Check Error Messages**: Are error messages clear and actionable?
2. **Evaluate Error Information**: Is comprehensive error information provided?
3. **Assess Error Logging**: Are errors logged for audit and debugging?
4. **Review Error Context**: Is error context information captured?

### Step 3: Resource Cleanup Analysis
1. **Check Transaction Rollback**: Are transactions rolled back on failure?
2. **Evaluate Resource Cleanup**: Are resources cleaned up on error paths?
3. **Assess Memory Management**: Is memory and temporary object cleanup performed?
4. **Review Graceful Degradation**: Is graceful degradation implemented?

### Step 4: Transaction Error Assessment
1. **Check Transaction Boundaries**: Are transaction boundaries managed properly?
2. **Evaluate Rollback Strategies**: Are rollback strategies consistent?
3. **Assess Deadlock Handling**: Is deadlock detection and resolution implemented?
4. **Review Isolation Levels**: Are appropriate isolation levels used?

### Step 5: Application Error Analysis
1. **Check Error Consistency**: Is error handling consistent across layers?
2. **Evaluate Error Propagation**: Is error information properly propagated?
3. **Assess User Experience**: Are errors presented in a user-friendly manner?
4. **Review Error Recovery**: Are error recovery mechanisms implemented?

## ISO/IEC Standards-Specific Criticism Guidelines

### Focus on Error Handling Robustness
**Good Criticism:**
- "The error handling in this procedure is incomplete; transaction rollback is missing."
- "Error messages are not actionable; specific error codes and context are missing."
- "Resource cleanup is not performed on error paths, risking memory leaks."
- "Deadlock detection and resolution is not implemented for this critical operation."

**Poor Criticism:**
- "This error handling is poor."
- "This could fail."
- "This is unreliable."

### Emphasize Error Handling Best Practices
**Good Criticism:**
- "The procedure does not check for constraint violations before proceeding."
- "Error messages lack specific SQLSTATE codes needed for proper error handling."
- "Transaction rollback is missing in the error path, risking data inconsistency."
- "Cursor cleanup is not performed on error, potentially causing resource leaks."

**Poor Criticism:**
- "This error handling is inadequate."
- "This might fail."
- "This is not robust."

## ISO/IEC Standards-Specific Problem Categories

### Error Detection Problems
- **Unchecked Errors**: Ignoring errors in procedures or triggers
- **Inconsistent Handling**: Different error handling patterns in similar contexts
- **Missing Error Checks**: Failure to check for common error conditions
- **Poor Error Propagation**: Unclear or inconsistent error propagation strategies

### Error Reporting Problems
- **Unclear Messages**: Error messages that are not actionable or informative
- **Missing Context**: Lack of error context information for debugging
- **Poor Logging**: Inadequate error logging and audit trails
- **Non-User-Friendly**: Error messages not suitable for end users

### Resource Cleanup Problems
- **Resource Leaks**: Uncommitted transactions or open cursors on error paths
- **Missing Cleanup**: Failure to clean up resources on error paths
- **Poor Memory Management**: Inadequate memory and temporary object cleanup
- **No Graceful Degradation**: Lack of graceful degradation strategies

### Transaction Error Problems
- **Inconsistent Rollback**: Inconsistent transaction rollback on errors
- **Poor Deadlock Handling**: Missing deadlock detection and resolution
- **Missing Error Handling**: Lack of error handling within transactions
- **Inappropriate Isolation**: Wrong isolation levels for error-prone operations

### Application Error Problems
- **Inconsistent Handling**: Different error handling across application layers
- **Poor Propagation**: Inadequate error propagation from database to application
- **Non-User-Friendly**: Error presentation not suitable for end users
- **Missing Recovery**: Lack of error recovery and retry mechanisms

## ISO/IEC Standards-Specific Criticism Templates

### For Error Detection Issues
```
Error Detection Issue: [Specific error detection problem]
Problem: [What makes this error detection inadequate]
Impact: [Potential for unhandled errors, inconsistent state, or poor user experience]
Evidence: [Specific code examples showing missing error checks]
Priority: [Critical/High/Medium/Low]
```

### For Error Reporting Issues
```
Error Reporting Issue: [Specific error reporting problem]
Problem: [What makes this error reporting inadequate]
Impact: [Potential for poor debugging, user confusion, or inadequate troubleshooting]
Evidence: [Specific error message examples or logging deficiencies]
Priority: [High/Medium/Low]
```

### For Resource Cleanup Issues
```
Resource Cleanup Issue: [Specific resource cleanup problem]
Problem: [What makes this resource cleanup inadequate]
Impact: [Potential for resource leaks, memory issues, or inconsistent state]
Evidence: [Specific code examples showing missing cleanup]
Priority: [Critical/High/Medium/Low]
```

### For Transaction Error Issues
```
Transaction Error Issue: [Specific transaction error problem]
Problem: [What makes this transaction error handling inadequate]
Impact: [Potential for data inconsistency, deadlocks, or poor performance]
Evidence: [Specific transaction code examples]
Priority: [Critical/High/Medium/Low]
```

### For Application Error Issues
```
Application Error Issue: [Specific application error problem]
Problem: [What makes this application error handling inadequate]
Impact: [Potential for poor user experience, inconsistent behavior, or inadequate recovery]
Evidence: [Specific application error handling examples]
Priority: [High/Medium/Low]
```

## ISO/IEC Standards-Specific Criticism Best Practices

### Do's
- **Focus on Error Robustness**: Identify specific error handling weaknesses
- **Emphasize Resource Management**: Ensure proper cleanup and rollback
- **Consider User Experience**: Evaluate error messages and user interaction
- **Assess Error Propagation**: Review error handling across application layers
- **Document Error Handling**: Clearly document error handling strategies

### Don'ts
- **Ignore Error Handling**: Don't overlook missing error checks
- **Accept Poor Error Messages**: Don't tolerate unclear or non-actionable errors
- **Skip Resource Cleanup**: Don't ignore resource cleanup requirements
- **Overlook Transaction Errors**: Don't accept poor transaction error handling
- **Forget User Experience**: Don't ignore the impact of errors on users

## ISO/IEC Standards-Specific Criticism Checklist

### Error Detection Assessment
- [ ] Are all errors detected and handled consistently?
- [ ] Are SQLSTATE codes used for error reporting?
- [ ] Are constraint violations handled gracefully?
- [ ] Are error conditions detected comprehensively?
- [ ] Are error propagation strategies clear and consistent?

### Error Reporting Assessment
- [ ] Are error messages clear and actionable?
- [ ] Is comprehensive error information provided?
- [ ] Are errors logged for audit and debugging?
- [ ] Are user-friendly error messages provided?
- [ ] Is error context information captured?

### Resource Cleanup Assessment
- [ ] Are transactions rolled back on failure?
- [ ] Are cursors and connections properly cleaned up?
- [ ] Is memory and temporary object cleanup performed?
- [ ] Are resources cleaned up on all error paths?
- [ ] Is graceful degradation implemented?

### Transaction Error Assessment
- [ ] Are transactions rolled back consistently on errors?
- [ ] Is deadlock detection and resolution implemented?
- [ ] Are errors handled properly within transactions?
- [ ] Are appropriate isolation levels used?
- [ ] Is transaction cleanup performed correctly?

### Application Error Assessment
- [ ] Is error handling consistent across application layers?
- [ ] Is error information properly propagated from database?
- [ ] Are errors presented in a user-friendly manner?
- [ ] Are error recovery and retry mechanisms implemented?
- [ ] Is error handling comprehensively documented?

## ISO/IEC Standards-Specific Evaluation Questions

### For Any SQL Code
1. **Are all errors detected and handled consistently?**
2. **Are SQLSTATE codes used for error reporting?**
3. **Are transactions rolled back on failure?**
4. **Are error messages clear and actionable?**
5. **Are resources cleaned up on all error paths?**
6. **Is error handling documented and tested?**
7. **Are error conditions detected comprehensively?**
8. **Is error propagation clear and consistent?**
9. **Are error recovery mechanisms implemented?**
10. **Is graceful degradation implemented on errors?**

### For Stored Procedures and Triggers
1. **Are all inputs validated and parameterized?**
2. **Are errors detected, reported, and handled consistently?**
3. **Are transactions used to ensure atomicity and consistency?**
4. **Are side effects documented and controlled?**
5. **Is resource cleanup performed on all code paths?**

### For Database Applications
1. **Is error handling consistent across application layers?**
2. **Is error information properly propagated from database?**
3. **Are errors presented in a user-friendly manner?**
4. **Are error recovery and retry mechanisms implemented?**
5. **Is error handling comprehensively documented?**

### For Error Handling and Robustness
1. **Are all errors detected and handled consistently?**
2. **Are SQLSTATE codes used for error reporting?**
3. **Are transactions rolled back on failure?**
4. **Are error messages clear and actionable?**
5. **Are error conditions documented and tested?**

## SQL Error Handling Principles Applied

### "Ensure Robust Error Detection"
- Detect and handle all errors consistently
- Use standard error codes and reporting mechanisms
- Implement comprehensive error condition detection

### "Provide Clear Error Information"
- Deliver clear and actionable error messages
- Include comprehensive error context for debugging
- Maintain proper error logging and audit trails

### "Manage Resources Properly"
- Clean up resources on all error paths
- Roll back transactions on failure
- Implement graceful degradation strategies

### "Handle Transactions Reliably"
- Manage transaction boundaries properly
- Implement consistent rollback strategies
- Handle deadlocks and concurrency issues

### "Consider User Experience"
- Present errors in a user-friendly manner
- Implement error recovery and retry mechanisms
- Provide comprehensive error documentation