0
# Schema Management
1

2
Schema management classes provide MySQL-specific implementations for database schema and table operations. They handle MySQL-specific behavior for schema existence checks, table operations, and database object management.
3

4
## Capabilities
5

6
### MySQL Schema
7

8
MySQL-specific schema implementation providing schema operations and table management.
9

10
```java { .api }
11
/**
12
 * MySQL-specific schema implementation
13
 * Handles MySQL schema operations and table management
14
 */
15
class MySQLSchema extends Schema<MySQLDatabase, MySQLTable> {
16
    
17
    /**
18
     * Creates a new MySQL schema instance
19
     * Package-private constructor used internally by MySQLDatabase
20
     * @param jdbcTemplate JDBC template for database operations
21
     * @param database MySQL database instance
22
     * @param name Schema name
23
     */
24
    MySQLSchema(JdbcTemplate jdbcTemplate, MySQLDatabase database, String name);
25
    
26
    /**
27
     * Gets a table instance for the specified table name
28
     * @param tableName Name of the table
29
     * @return MySQLTable instance for the specified table
30
     */
31
    public Table getTable(String tableName);
32
    
33
    /**
34
     * Checks if the schema exists in the database
35
     * @return true if schema exists
36
     */
37
    @Override
38
    protected boolean doExists();
39
    
40
    /**
41
     * Checks if the schema is empty (contains no tables)
42
     * @return true if schema contains no tables
43
     */
44
    @Override
45
    protected boolean doEmpty();
46
    
47
    /**
48
     * Creates the schema in the database
49
     * Executes CREATE SCHEMA statement
50
     */
51
    @Override
52
    protected void doCreate();
53
    
54
    /**
55
     * Drops the schema from the database
56
     * Executes DROP SCHEMA statement
57
     */
58
    @Override
59
    protected void doDrop();
60
    
61
    /**
62
     * Cleans the schema by dropping all contained objects
63
     * Removes all tables, views, procedures, functions, etc.
64
     */
65
    @Override
66
    protected void doClean();
67
    
68
    /**
69
     * Returns all tables in the schema
70
     * @return Array of MySQLTable instances for all tables
71
     */
72
    @Override
73
    protected MySQLTable[] doAllTables();
74
    
75
    /**
76
     * Returns DROP statements for all events in the schema
77
     * @return List of DROP EVENT statements
78
     * @throws SQLException if query fails
79
     */
80
    private List<String> cleanEvents() throws SQLException;
81
    
82
    /**
83
     * Returns DROP statements for all routines (procedures/functions) in the schema
84
     * @return List of DROP PROCEDURE/FUNCTION statements
85
     * @throws SQLException if query fails
86
     */
87
    private List<String> cleanRoutines() throws SQLException;
88
    
89
    /**
90
     * Returns DROP statements for all views in the schema
91
     * @return List of DROP VIEW statements
92
     * @throws SQLException if query fails
93
     */
94
    private List<String> cleanViews() throws SQLException;
95
    
96
    /**
97
     * Returns DROP statements for all sequences in the schema (MariaDB 10.3+)
98
     * @return List of DROP SEQUENCE statements
99
     * @throws SQLException if query fails
100
     */
101
    private List<String> cleanSequences() throws SQLException;
102
}
103
```
104

105
**Usage Examples:**
106

107
```java
108
// Schema access via connection
109
MySQLConnection connection = database.getConnection();
110
MySQLSchema schema = (MySQLSchema) connection.getSchema("myschema");
111

112
// Check schema existence
113
if (schema.exists()) {
114
    System.out.println("Schema exists");
115
}
116

117
// Check if schema is empty
118
if (schema.empty()) {
119
    System.out.println("Schema has no tables");
120
}
121

122
// Get table instance
123
MySQLTable table = (MySQLTable) schema.getTable("users");
124

125
// Get all tables
126
Table[] allTables = schema.getAllTables();
127
for (Table table : allTables) {
128
    System.out.println("Table: " + table.getName());
129
}
130
```
131

132
### MySQL Table
133

134
MySQL-specific table implementation providing table operations and locking support.
135

136
```java { .api }
137
/**
138
 * MySQL-specific table implementation
139
 * Handles MySQL table operations, existence checks, and locking
140
 */
141
class MySQLTable extends Table<MySQLDatabase, MySQLSchema> {
142
    
143
    /**
144
     * Creates a new MySQL table instance
145
     * Package-private constructor used internally by MySQLSchema
146
     * @param jdbcTemplate JDBC template for database operations
147
     * @param database MySQL database instance
148
     * @param schema MySQL schema containing this table
149
     * @param name Table name
150
     */
151
    MySQLTable(JdbcTemplate jdbcTemplate, MySQLDatabase database, MySQLSchema schema, String name);
152
    
153
    /**
154
     * Drops the table from the database
155
     * Executes DROP TABLE statement with MySQL-specific options
156
     */
157
    @Override
158
    protected void doDrop();
159
    
160
    /**
161
     * Checks if the table exists in the schema
162
     * @return true if table exists
163
     */
164
    @Override
165
    protected boolean doExists();
166
    
167
    /**
168
     * Locks the table for exclusive access
169
     * Uses MySQL-specific table locking mechanisms
170
     */
171
    @Override
172
    protected void doLock();
173
}
174
```
175

176
**Usage Examples:**
177

178
```java
179
// Table access via schema
180
MySQLSchema schema = (MySQLSchema) connection.getSchema("myschema");
181
MySQLTable table = (MySQLTable) schema.getTable("users");
182

183
// Check table existence
184
if (table.exists()) {
185
    System.out.println("Table exists");
186
}
187

188
// Lock table for exclusive access
189
table.lock();
190
try {
191
    // Perform operations requiring exclusive access
192
    performCriticalTableOperations();
193
} finally {
194
    // Lock is automatically released
195
}
196

197
// Drop table
198
table.drop();
199
```
200

201
## Schema Operations
202

203
### Schema Existence
204

205
MySQL schema existence is checked using `INFORMATION_SCHEMA`:
206

207
```sql
208
SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA 
209
WHERE SCHEMA_NAME = ?
210
```
211

212
**Behavior:**
213
- Returns `true` if schema exists
214
- Case-sensitive comparison
215
- Handles MySQL naming rules
216

217
### Schema Creation
218

219
Creates schemas using MySQL syntax:
220

221
```sql
222
CREATE SCHEMA `schema_name`
223
```
224

225
**Features:**
226
- Proper identifier quoting with backticks
227
- Error handling for existing schemas
228
- Character set and collation inheritance
229

230
### Schema Emptiness Check
231

232
Determines if schema contains any tables:
233

234
```sql
235
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES 
236
WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'
237
```
238

239
**Behavior:**
240
- Counts only base tables (not views)
241
- Excludes system tables
242
- Returns `true` for zero table count
243

244
### Schema Cleaning
245

246
Comprehensive schema cleaning removes all objects:
247

248
1. **Drop all tables** (including foreign key dependencies)
249
2. **Drop all views** 
250
3. **Drop all stored procedures**
251
4. **Drop all functions**
252
5. **Drop all events** (if event scheduler is queryable)
253
6. **Drop all triggers** (handled with table drops)
254

255
**Order of Operations:**
256
```java
257
// Cleaning order for dependency resolution
258
1. Drop foreign key constraints
259
2. Drop tables
260
3. Drop views  
261
4. Drop routines (procedures/functions)
262
5. Drop events
263
```
264

265
### Schema Dropping
266

267
Drops entire schema and all contents:
268

269
```sql
270
DROP SCHEMA `schema_name`
271
```
272

273
**Features:**
274
- Cascades to all contained objects
275
- Handles foreign key dependencies
276
- Proper cleanup of MySQL-specific objects
277

278
## Table Operations
279

280
### Table Existence
281

282
Checks table existence using `INFORMATION_SCHEMA`:
283

284
```sql
285
SELECT 1 FROM INFORMATION_SCHEMA.TABLES 
286
WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND TABLE_TYPE = 'BASE TABLE'
287
```
288

289
**Behavior:**
290
- Checks specific schema context
291
- Excludes views and temporary tables
292
- Case-sensitive table name matching
293

294
### Table Locking
295

296
MySQL table locking for exclusive access:
297

298
```sql
299
LOCK TABLES `schema`.`table` WRITE
300
```
301

302
**Features:**
303
- Exclusive write lock
304
- Blocks other connections
305
- Automatically released on connection close
306
- Used for critical operations like schema history updates
307

308
**Lock Types:**
309
- `WRITE` - Exclusive access for modifications
310
- Session-level locking
311
- Automatic cleanup on connection termination
312

313
### Table Dropping
314

315
Drops tables with MySQL-specific handling:
316

317
```sql
318
DROP TABLE `schema`.`table`
319
```
320

321
**Features:**
322
- Handles foreign key constraints
323
- Cascade behavior for dependent objects
324
- Proper cleanup of MySQL-specific table features
325

326
## MySQL-Specific Behaviors
327

328
### Catalog vs Schema
329

330
MySQL treats database names as schema names:
331

332
```java
333
// MySQL behavior
334
database.catalogIsSchema(); // Returns true
335

336
// This means:
337
// - Database name = Schema name
338
// - No separate catalog concept
339
// - Schema operations work on database level
340
```
341

342
### Identifier Quoting
343

344
All identifiers use MySQL backtick quoting:
345

346
```sql
347
-- Schema operations
348
CREATE SCHEMA `my schema`;
349
DROP SCHEMA `my schema`;
350

351
-- Table operations  
352
CREATE TABLE `my schema`.`my table` (...);
353
DROP TABLE `my schema`.`my table`;
354
```
355

356
### Foreign Key Handling
357

358
MySQL foreign key constraints affect operation order:
359

360
**During Cleaning:**
361
1. Disable foreign key checks: `SET foreign_key_checks = 0`
362
2. Drop all tables
363
3. Restore foreign key checks: `SET foreign_key_checks = 1`
364

365
**During Individual Drops:**
366
- Foreign key constraints may prevent table drops
367
- Dependency resolution required
368
- Proper error handling for constraint violations
369

370
## Information Schema Integration
371

372
### System Table Queries
373

374
Heavy use of `INFORMATION_SCHEMA` for metadata:
375

376
**Schema Information:**
377
```sql
378
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
379
WHERE SCHEMA_NAME = ?
380
```
381

382
**Table Information:**
383
```sql
384
SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES
385
WHERE TABLE_SCHEMA = ?
386
```
387

388
**Routine Information:**
389
```sql
390
SELECT ROUTINE_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES
391
WHERE ROUTINE_SCHEMA = ?
392
```
393

394
**Event Information:**
395
```sql
396
SELECT EVENT_NAME FROM INFORMATION_SCHEMA.EVENTS
397
WHERE EVENT_SCHEMA = ?
398
```
399

400
### Performance Considerations
401

402
**Query Optimization:**
403
- Indexed queries on system tables
404
- Minimal data transfer
405
- Efficient filtering conditions
406

407
**Caching Strategy:**
408
- Results cached where appropriate
409
- Invalidation on schema changes
410
- Connection-level caching
411

412
## Error Handling
413

414
### Common Schema Errors
415

416
**Schema Already Exists:**
417
```sql
418
ERROR 1007 (HY000): Can't create database 'schema'; database exists
419
```
420

421
**Schema Not Found:**
422
```sql
423
ERROR 1049 (42000): Unknown database 'schema'
424
```
425

426
**Permission Denied:**
427
```sql
428
ERROR 1044 (42000): Access denied for user 'user'@'host' to database 'schema'
429
```
430

431
### Common Table Errors
432

433
**Table Already Exists:**
434
```sql
435
ERROR 1050 (42S01): Table 'table' already exists
436
```
437

438
**Table Not Found:**
439
```sql
440
ERROR 1146 (42S02): Table 'schema.table' doesn't exist
441
```
442

443
**Foreign Key Constraint:**
444
```sql
445
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
446
```
447

448
### Error Recovery
449

450
**Retry Strategies:**
451
- Temporary lock failures
452
- Connection timeout issues
453
- Transient permission problems
454

455
**Fallback Approaches:**
456
- Alternative query methods
457
- Reduced functionality modes
458
- Graceful degradation
459

460
## Integration with Connection Management
461

462
### Transaction Behavior
463

464
MySQL schema operations and DDL:
465

466
```java
467
// DDL operations auto-commit in MySQL
468
database.supportsDdlTransactions(); // Returns false
469

470
// This affects:
471
// - Schema creation/dropping
472
// - Table creation/dropping  
473
// - No rollback capability for DDL
474
```
475

476
### Connection State Management
477

478
Schema operations preserve connection state:
479

480
- Foreign key checks restoration
481
- SQL safe updates restoration
482
- User variable cleanup
483
- Session-level settings preservation
484

485
### Named Lock Integration
486

487
For concurrent schema operations:
488

489
```java
490
// Use named locks for coordination
491
MySQLNamedLockTemplate lockTemplate = connection.getNamedLockTemplate();
492
lockTemplate.execute(() -> {
493
    // Schema operations protected by named lock
494
    schema.clean();
495
    return null;
496
});
497
```