idb/innodb/
schema.rs

1//! Schema extraction and DDL reconstruction from SDI metadata.
2//!
3//! MySQL 8.0+ embeds complete table definitions (columns, indexes, foreign keys)
4//! as SDI JSON inside every `.ibd` file. This module parses that JSON into typed
5//! Rust structs and reconstructs human-readable `CREATE TABLE` DDL.
6//!
7//! For pre-8.0 tablespaces without SDI, a best-effort inference from INDEX page
8//! structure provides basic information about detected indexes.
9//!
10//! # Usage
11//!
12//! ```rust,ignore
13//! use idb::innodb::tablespace::Tablespace;
14//! use idb::innodb::sdi::{find_sdi_pages, extract_sdi_from_pages};
15//! use idb::innodb::schema::extract_schema_from_sdi;
16//!
17//! let mut ts = Tablespace::open("table.ibd").unwrap();
18//! let sdi_pages = find_sdi_pages(&mut ts).unwrap();
19//! let records = extract_sdi_from_pages(&mut ts, &sdi_pages).unwrap();
20//! for rec in &records {
21//!     if rec.sdi_type == 1 {
22//!         let schema = extract_schema_from_sdi(&rec.data).unwrap();
23//!         println!("{}", schema.ddl);
24//!     }
25//! }
26//! ```
27
28use serde::{Deserialize, Serialize};
29use std::collections::HashMap;
30
31use crate::IdbError;
32
33// ---------------------------------------------------------------------------
34// SDI JSON deserialization structs
35// ---------------------------------------------------------------------------
36
37/// Top-level SDI envelope wrapping a dd_object.
38#[derive(Debug, Deserialize)]
39pub struct SdiEnvelope {
40    /// MySQL server version ID (e.g., 90001 for 9.0.1).
41    #[serde(default)]
42    pub mysqld_version_id: u64,
43    /// Object type: "Table" or "Tablespace".
44    #[serde(default)]
45    pub dd_object_type: String,
46    /// The data dictionary object.
47    #[serde(default)]
48    pub dd_object: DdTable,
49}
50
51/// Data dictionary table definition.
52#[derive(Debug, Default, Deserialize)]
53pub struct DdTable {
54    /// Table name.
55    #[serde(default)]
56    pub name: String,
57    /// Schema (database) name.
58    #[serde(default)]
59    pub schema_ref: String,
60    /// Storage engine name.
61    #[serde(default)]
62    pub engine: String,
63    /// Default collation ID.
64    #[serde(default)]
65    pub collation_id: u64,
66    /// Row format code (1=FIXED, 2=DYNAMIC, 3=COMPRESSED, etc.).
67    #[serde(default)]
68    pub row_format: u64,
69    /// Table comment.
70    #[serde(default)]
71    pub comment: String,
72    /// Column definitions.
73    #[serde(default)]
74    pub columns: Vec<DdColumn>,
75    /// Index definitions.
76    #[serde(default)]
77    pub indexes: Vec<DdIndex>,
78    /// Foreign key definitions.
79    #[serde(default)]
80    pub foreign_keys: Vec<DdForeignKey>,
81    /// MySQL server version ID.
82    #[serde(default)]
83    pub mysql_version_id: u64,
84}
85
86/// Data dictionary column definition.
87#[derive(Debug, Default, Deserialize)]
88pub struct DdColumn {
89    /// Column name.
90    #[serde(default)]
91    pub name: String,
92    /// dd_type code (internal MySQL type enumeration).
93    #[serde(rename = "type", default)]
94    pub dd_type: u64,
95    /// SQL type string from MySQL (e.g., "varchar(255)", "int unsigned").
96    #[serde(default)]
97    pub column_type_utf8: String,
98    /// Position in the column list (1-based).
99    #[serde(default)]
100    pub ordinal_position: u64,
101    /// Hidden flag: 1=visible, 2=SE-hidden (DB_TRX_ID, DB_ROLL_PTR, DB_ROW_ID),
102    /// 3=SQL-hidden (functional index backing columns), 4=user INVISIBLE (MySQL 8.0.23+).
103    #[serde(default)]
104    pub hidden: u64,
105    /// Whether the column allows NULL.
106    #[serde(default)]
107    pub is_nullable: bool,
108    /// Whether the column is unsigned.
109    #[serde(default)]
110    pub is_unsigned: bool,
111    /// Whether the column is AUTO_INCREMENT.
112    #[serde(default)]
113    pub is_auto_increment: bool,
114    /// Whether the column is virtual (generated).
115    #[serde(default)]
116    pub is_virtual: bool,
117    /// Character length.
118    #[serde(default)]
119    pub char_length: u64,
120    /// Numeric precision.
121    #[serde(default)]
122    pub numeric_precision: u64,
123    /// Numeric scale.
124    #[serde(default)]
125    pub numeric_scale: u64,
126    /// Datetime fractional seconds precision.
127    #[serde(default)]
128    pub datetime_precision: u64,
129    /// Collation ID for this column.
130    #[serde(default)]
131    pub collation_id: u64,
132    /// Default value as UTF-8 string.
133    #[serde(default)]
134    pub default_value_utf8: String,
135    /// Whether default_value_utf8 is NULL.
136    #[serde(default)]
137    pub default_value_utf8_null: bool,
138    /// Whether the column has no default.
139    #[serde(default)]
140    pub has_no_default: bool,
141    /// Default option (e.g., "CURRENT_TIMESTAMP").
142    #[serde(default)]
143    pub default_option: String,
144    /// Update option (e.g., "CURRENT_TIMESTAMP").
145    #[serde(default)]
146    pub update_option: String,
147    /// Generation expression (raw).
148    #[serde(default)]
149    pub generation_expression: String,
150    /// Generation expression as UTF-8.
151    #[serde(default)]
152    pub generation_expression_utf8: String,
153    /// ENUM/SET value elements.
154    #[serde(default)]
155    pub elements: Vec<DdColumnElement>,
156    /// Column comment.
157    #[serde(default)]
158    pub comment: String,
159    /// Whether the column is zerofill.
160    #[serde(default)]
161    pub is_zerofill: bool,
162}
163
164/// ENUM or SET value element.
165#[derive(Debug, Default, Deserialize)]
166pub struct DdColumnElement {
167    /// The element name (value string).
168    #[serde(default)]
169    pub name: String,
170}
171
172/// Data dictionary index definition.
173#[derive(Debug, Default, Deserialize)]
174pub struct DdIndex {
175    /// Index name.
176    #[serde(default)]
177    pub name: String,
178    /// Index type: 1=PRIMARY, 2=UNIQUE, 3=MULTIPLE (non-unique), 4=FULLTEXT, 5=SPATIAL.
179    #[serde(rename = "type", default)]
180    pub index_type: u64,
181    /// Algorithm code (1=SE_SPECIFIC, 2=BTREE, 3=RTREE, 4=HASH, 5=FULLTEXT).
182    #[serde(default)]
183    pub algorithm: u64,
184    /// Whether the index is hidden.
185    #[serde(default)]
186    pub hidden: bool,
187    /// Index elements (columns).
188    #[serde(default)]
189    pub elements: Vec<DdIndexElement>,
190    /// Index comment.
191    #[serde(default)]
192    pub comment: String,
193    /// Whether the index is visible.
194    #[serde(default)]
195    pub is_visible: bool,
196}
197
198/// Data dictionary index element (column reference).
199#[derive(Debug, Default, Deserialize)]
200pub struct DdIndexElement {
201    /// 0-based index into the columns array as ordered in the SDI JSON.
202    #[serde(default)]
203    pub column_opx: u64,
204    /// Prefix length (4294967295 = full column).
205    #[serde(default)]
206    pub length: u64,
207    /// Sort order: 2=ASC, 1=DESC.
208    #[serde(default)]
209    pub order: u64,
210    /// Whether this element is hidden (internal).
211    #[serde(default)]
212    pub hidden: bool,
213}
214
215/// Data dictionary foreign key definition.
216#[derive(Debug, Default, Deserialize)]
217pub struct DdForeignKey {
218    /// Constraint name.
219    #[serde(default)]
220    pub name: String,
221    /// Referenced table's schema name.
222    #[serde(default)]
223    pub referenced_table_schema_name: String,
224    /// Referenced table name.
225    #[serde(default)]
226    pub referenced_table_name: String,
227    /// ON UPDATE rule (0=NO ACTION, 1=RESTRICT, 2=CASCADE, 3=SET NULL, 4=SET DEFAULT).
228    #[serde(default)]
229    pub update_rule: u64,
230    /// ON DELETE rule (same codes as update_rule).
231    #[serde(default)]
232    pub delete_rule: u64,
233    /// Foreign key elements (column mappings).
234    #[serde(default)]
235    pub elements: Vec<DdForeignKeyElement>,
236}
237
238/// Data dictionary foreign key element (column mapping).
239#[derive(Debug, Default, Deserialize)]
240pub struct DdForeignKeyElement {
241    /// 0-based index into the table's columns array as ordered in the SDI JSON.
242    #[serde(default)]
243    pub column_opx: u64,
244    /// Name of the referenced column.
245    #[serde(default)]
246    pub referenced_column_name: String,
247}
248
249// ---------------------------------------------------------------------------
250// Output structs (for text rendering and JSON serialization)
251// ---------------------------------------------------------------------------
252
253/// Reconstructed table schema from SDI or page inference.
254#[derive(Debug, Clone, Serialize)]
255pub struct TableSchema {
256    /// Database/schema name.
257    #[serde(skip_serializing_if = "Option::is_none")]
258    pub schema_name: Option<String>,
259    /// Table name.
260    pub table_name: String,
261    /// Storage engine.
262    pub engine: String,
263    /// Row format (DYNAMIC, COMPRESSED, etc.).
264    #[serde(skip_serializing_if = "Option::is_none")]
265    pub row_format: Option<String>,
266    /// Table collation name.
267    #[serde(skip_serializing_if = "Option::is_none")]
268    pub collation: Option<String>,
269    /// Character set name.
270    #[serde(skip_serializing_if = "Option::is_none")]
271    pub charset: Option<String>,
272    /// Table comment.
273    #[serde(skip_serializing_if = "Option::is_none")]
274    pub comment: Option<String>,
275    /// MySQL version string.
276    #[serde(skip_serializing_if = "Option::is_none")]
277    pub mysql_version: Option<String>,
278    /// Schema source: "sdi" or "inferred".
279    pub source: String,
280    /// Column definitions.
281    pub columns: Vec<ColumnDef>,
282    /// Index definitions.
283    pub indexes: Vec<IndexDef>,
284    /// Foreign key definitions.
285    #[serde(skip_serializing_if = "Vec::is_empty")]
286    pub foreign_keys: Vec<ForeignKeyDef>,
287    /// Reconstructed CREATE TABLE DDL.
288    pub ddl: String,
289}
290
291/// Column definition for output.
292#[derive(Debug, Clone, Serialize)]
293pub struct ColumnDef {
294    /// Column name.
295    pub name: String,
296    /// SQL type string (e.g., "varchar(100)").
297    pub column_type: String,
298    /// Whether the column allows NULL.
299    pub is_nullable: bool,
300    /// Default value expression (None if no default).
301    #[serde(skip_serializing_if = "Option::is_none")]
302    pub default_value: Option<String>,
303    /// Whether the column is AUTO_INCREMENT.
304    #[serde(skip_serializing_if = "is_false")]
305    pub is_auto_increment: bool,
306    /// Generation expression for virtual/stored generated columns.
307    #[serde(skip_serializing_if = "Option::is_none")]
308    pub generation_expression: Option<String>,
309    /// Whether the generated column is virtual (true) or stored (false).
310    #[serde(skip_serializing_if = "Option::is_none")]
311    pub is_virtual: Option<bool>,
312    /// Whether the column is INVISIBLE (MySQL 8.0.23+).
313    #[serde(skip_serializing_if = "is_false")]
314    pub is_invisible: bool,
315    /// Column comment.
316    #[serde(skip_serializing_if = "Option::is_none")]
317    pub comment: Option<String>,
318}
319
320fn is_false(v: &bool) -> bool {
321    !v
322}
323
324/// Index definition for output.
325#[derive(Debug, Clone, Serialize)]
326pub struct IndexDef {
327    /// Index name.
328    pub name: String,
329    /// Index type: "PRIMARY KEY", "UNIQUE KEY", "KEY", "FULLTEXT KEY", "SPATIAL KEY".
330    pub index_type: String,
331    /// Index columns with optional prefix length and sort order.
332    pub columns: Vec<IndexColumnDef>,
333    /// Index comment.
334    #[serde(skip_serializing_if = "Option::is_none")]
335    pub comment: Option<String>,
336    /// Whether the index is visible.
337    #[serde(skip_serializing_if = "is_true")]
338    pub is_visible: bool,
339}
340
341fn is_true(v: &bool) -> bool {
342    *v
343}
344
345/// Column reference within an index.
346#[derive(Debug, Clone, Serialize)]
347pub struct IndexColumnDef {
348    /// Column name.
349    pub name: String,
350    /// Prefix length (None for full column).
351    #[serde(skip_serializing_if = "Option::is_none")]
352    pub prefix_length: Option<u64>,
353    /// Sort order ("ASC" or "DESC"). Omitted if ASC (default).
354    #[serde(skip_serializing_if = "Option::is_none")]
355    pub order: Option<String>,
356}
357
358/// Foreign key definition for output.
359#[derive(Debug, Clone, Serialize)]
360pub struct ForeignKeyDef {
361    /// Constraint name.
362    pub name: String,
363    /// Columns in this table.
364    pub columns: Vec<String>,
365    /// Referenced schema.table.
366    pub referenced_table: String,
367    /// Referenced columns.
368    pub referenced_columns: Vec<String>,
369    /// ON UPDATE action.
370    pub on_update: String,
371    /// ON DELETE action.
372    pub on_delete: String,
373}
374
375/// Inferred schema from pre-8.0 tablespaces (no SDI).
376#[derive(Debug, Clone, Serialize)]
377pub struct InferredSchema {
378    /// Source description.
379    pub source: String,
380    /// Record format: "COMPACT" or "REDUNDANT".
381    pub record_format: String,
382    /// Detected indexes.
383    pub indexes: Vec<InferredIndex>,
384}
385
386/// Inferred index from page scanning.
387#[derive(Debug, Clone, Serialize)]
388pub struct InferredIndex {
389    /// Index ID from the INDEX page header.
390    pub index_id: u64,
391    /// Number of leaf pages.
392    pub leaf_pages: u64,
393    /// Maximum non-leaf level seen.
394    pub max_level: u16,
395}
396
397// ---------------------------------------------------------------------------
398// Collation and type mapping
399// ---------------------------------------------------------------------------
400
401/// Map common MySQL collation IDs to collation names.
402///
403/// Only covers the most widely-used collations. Returns None for unknown IDs.
404///
405/// # Examples
406///
407/// ```
408/// use idb::innodb::schema::collation_name;
409///
410/// assert_eq!(collation_name(255), Some("utf8mb4_0900_ai_ci"));
411/// assert_eq!(collation_name(63), Some("binary"));
412/// assert_eq!(collation_name(45), Some("utf8mb4_general_ci"));
413/// assert_eq!(collation_name(99999), None);
414/// ```
415pub fn collation_name(id: u64) -> Option<&'static str> {
416    match id {
417        2 => Some("latin2_czech_cs"),
418        8 => Some("latin1_swedish_ci"),
419        11 => Some("ascii_general_ci"),
420        33 => Some("utf8mb3_general_ci"),
421        45 => Some("utf8mb4_general_ci"),
422        46 => Some("utf8mb4_bin"),
423        47 => Some("latin1_bin"),
424        48 => Some("latin1_general_ci"),
425        63 => Some("binary"),
426        83 => Some("utf8mb3_bin"),
427        224 => Some("utf8mb4_unicode_ci"),
428        255 => Some("utf8mb4_0900_ai_ci"),
429        _ => None,
430    }
431}
432
433/// Map collation ID to character set name.
434///
435/// # Examples
436///
437/// ```
438/// use idb::innodb::schema::charset_from_collation;
439///
440/// assert_eq!(charset_from_collation(255), Some("utf8mb4"));
441/// assert_eq!(charset_from_collation(63), Some("binary"));
442/// assert_eq!(charset_from_collation(8), Some("latin1"));
443/// assert_eq!(charset_from_collation(99999), None);
444/// ```
445pub fn charset_from_collation(id: u64) -> Option<&'static str> {
446    match id {
447        2 => Some("latin2"),
448        8 | 47 | 48 => Some("latin1"),
449        11 => Some("ascii"),
450        33 | 83 => Some("utf8mb3"),
451        45 | 46 | 224 | 255 => Some("utf8mb4"),
452        63 => Some("binary"),
453        _ => None,
454    }
455}
456
457/// Map row_format code to name.
458///
459/// # Examples
460///
461/// ```
462/// use idb::innodb::schema::row_format_name;
463///
464/// assert_eq!(row_format_name(1), "FIXED");
465/// assert_eq!(row_format_name(2), "DYNAMIC");
466/// assert_eq!(row_format_name(3), "COMPRESSED");
467/// ```
468pub fn row_format_name(id: u64) -> &'static str {
469    match id {
470        1 => "FIXED",
471        2 => "DYNAMIC",
472        3 => "COMPRESSED",
473        4 => "REDUNDANT",
474        5 => "COMPACT",
475        _ => "UNKNOWN",
476    }
477}
478
479/// Map foreign key rule code to SQL action string.
480///
481/// # Examples
482///
483/// ```
484/// use idb::innodb::schema::fk_rule_name;
485///
486/// assert_eq!(fk_rule_name(0), "NO ACTION");
487/// assert_eq!(fk_rule_name(2), "CASCADE");
488/// ```
489pub fn fk_rule_name(rule: u64) -> &'static str {
490    match rule {
491        0 => "NO ACTION",
492        1 => "RESTRICT",
493        2 => "CASCADE",
494        3 => "SET NULL",
495        4 => "SET DEFAULT",
496        _ => "NO ACTION",
497    }
498}
499
500/// Fallback type mapping from dd_type code when `column_type_utf8` is empty.
501///
502/// This should rarely be needed — `column_type_utf8` is the authoritative source.
503///
504/// # Examples
505///
506/// ```
507/// use idb::innodb::schema::{DdColumn, dd_type_to_sql};
508///
509/// let col = DdColumn { dd_type: 4, numeric_precision: 10, ..Default::default() };
510/// assert_eq!(dd_type_to_sql(&col), "int");
511///
512/// let col = DdColumn { dd_type: 16, char_length: 400, collation_id: 255, ..Default::default() };
513/// assert_eq!(dd_type_to_sql(&col), "varchar(100)");
514/// ```
515pub fn dd_type_to_sql(col: &DdColumn) -> String {
516    match col.dd_type {
517        1 => "tinyint".to_string(),
518        2 => "smallint".to_string(),
519        3 => "mediumint".to_string(),
520        4 => "int".to_string(),
521        5 => "bigint".to_string(),
522        6 => format_decimal(col),
523        7 => "float".to_string(),
524        8 => "double".to_string(),
525        9 | 10 => "binary".to_string(), // internal types (ROLL_PTR, TRX_ID)
526        11 => "year".to_string(),
527        12 => "date".to_string(),
528        13 => "time".to_string(),
529        14 => "datetime".to_string(),
530        15 => "timestamp".to_string(),
531        16 => format_varchar(col),
532        17 => format_char(col),
533        18 => "bit".to_string(),
534        19 => "enum".to_string(),
535        20 => "set".to_string(),
536        23 => "tinyblob".to_string(),
537        24 => "mediumblob".to_string(),
538        25 => "longblob".to_string(),
539        26 => "blob".to_string(),
540        27 => format_text(col),
541        28 => "varbinary".to_string(),
542        29 => "binary".to_string(),
543        30 => "geometry".to_string(),
544        31 => "json".to_string(),
545        _ => format!("unknown_type({})", col.dd_type),
546    }
547}
548
549fn format_decimal(col: &DdColumn) -> String {
550    if col.numeric_precision > 0 {
551        if col.numeric_scale > 0 {
552            format!("decimal({},{})", col.numeric_precision, col.numeric_scale)
553        } else {
554            format!("decimal({})", col.numeric_precision)
555        }
556    } else {
557        "decimal".to_string()
558    }
559}
560
561fn format_varchar(col: &DdColumn) -> String {
562    // char_length is in bytes; divide by max bytes per char for the charset
563    let max_bytes_per_char = charset_max_bytes(col.collation_id);
564    let char_len = if max_bytes_per_char > 0 {
565        col.char_length / max_bytes_per_char
566    } else {
567        col.char_length
568    };
569    format!("varchar({})", char_len)
570}
571
572fn format_char(col: &DdColumn) -> String {
573    let max_bytes_per_char = charset_max_bytes(col.collation_id);
574    let char_len = if max_bytes_per_char > 0 {
575        col.char_length / max_bytes_per_char
576    } else {
577        col.char_length
578    };
579    format!("char({})", char_len.max(1))
580}
581
582fn format_text(col: &DdColumn) -> String {
583    match col.char_length {
584        0..=255 => "tinytext".to_string(),
585        256..=65535 => "text".to_string(),
586        65536..=16777215 => "mediumtext".to_string(),
587        _ => "longtext".to_string(),
588    }
589}
590
591/// Returns max bytes per character for a collation ID.
592fn charset_max_bytes(collation_id: u64) -> u64 {
593    match collation_id {
594        2 | 8 | 11 | 47 | 48 => 1, // latin1, latin2, ascii
595        33 | 83 => 3,              // utf8mb3
596        45 | 46 | 224 | 255 => 4,  // utf8mb4
597        63 => 1,                   // binary
598        _ => 4,                    // default to utf8mb4
599    }
600}
601
602/// Format MySQL version from version_id (e.g., 90001 -> "9.0.1").
603fn format_mysql_version(version_id: u64) -> String {
604    if version_id == 0 {
605        return "unknown".to_string();
606    }
607    let major = version_id / 10000;
608    let minor = (version_id % 10000) / 100;
609    let patch = version_id % 100;
610    format!("{}.{}.{}", major, minor, patch)
611}
612
613// ---------------------------------------------------------------------------
614// Schema extraction
615// ---------------------------------------------------------------------------
616
617/// Extract a [`TableSchema`] from raw SDI JSON (type=1 "Table" record).
618///
619/// Parses the JSON into typed structs, filters out hidden columns, builds
620/// column/index/FK definitions, and generates `CREATE TABLE` DDL.
621///
622/// # Examples
623///
624/// ```
625/// use idb::innodb::schema::extract_schema_from_sdi;
626///
627/// let json = r#"{
628///   "mysqld_version_id": 90001,
629///   "dd_object_type": "Table",
630///   "dd_object": {
631///     "name": "test_table",
632///     "schema_ref": "mydb",
633///     "engine": "InnoDB",
634///     "collation_id": 255,
635///     "row_format": 2,
636///     "columns": [
637///       {
638///         "name": "id",
639///         "type": 4,
640///         "column_type_utf8": "int",
641///         "ordinal_position": 1,
642///         "hidden": 1,
643///         "is_nullable": false,
644///         "is_auto_increment": true
645///       },
646///       {
647///         "name": "DB_TRX_ID",
648///         "type": 10,
649///         "ordinal_position": 2,
650///         "hidden": 2
651///       },
652///       {
653///         "name": "DB_ROLL_PTR",
654///         "type": 9,
655///         "ordinal_position": 3,
656///         "hidden": 2
657///       }
658///     ],
659///     "indexes": [
660///       {
661///         "name": "PRIMARY",
662///         "type": 1,
663///         "hidden": false,
664///         "is_visible": true,
665///         "elements": [
666///           { "column_opx": 0, "hidden": false, "length": 4, "order": 2 }
667///         ]
668///       }
669///     ],
670///     "foreign_keys": []
671///   }
672/// }"#;
673///
674/// let schema = extract_schema_from_sdi(json).unwrap();
675/// assert_eq!(schema.table_name, "test_table");
676/// assert_eq!(schema.columns.len(), 1); // DB_TRX_ID and DB_ROLL_PTR filtered
677/// assert!(schema.ddl.contains("CREATE TABLE"));
678/// ```
679pub fn extract_schema_from_sdi(sdi_json: &str) -> Result<TableSchema, IdbError> {
680    let envelope: SdiEnvelope = serde_json::from_str(sdi_json)
681        .map_err(|e| IdbError::Parse(format!("Failed to parse SDI JSON: {}", e)))?;
682
683    let dd = &envelope.dd_object;
684
685    // Build column name lookup (all columns, including hidden)
686    let all_columns: Vec<&DdColumn> = {
687        let mut cols: Vec<&DdColumn> = dd.columns.iter().collect();
688        cols.sort_by_key(|c| c.ordinal_position);
689        cols
690    };
691
692    // Build lookup from JSON array position to column (for column_opx lookups).
693    // column_opx is a 0-based index into the columns array as ordered in the JSON.
694    let column_by_index: HashMap<u64, &DdColumn> = dd
695        .columns
696        .iter()
697        .enumerate()
698        .map(|(i, c)| (i as u64, c))
699        .collect();
700
701    // Filter user-facing columns:
702    //   hidden=1 (HT_VISIBLE) — normal visible columns
703    //   hidden=4 (HT_HIDDEN_USER) — user INVISIBLE columns (MySQL 8.0.23+)
704    // Excludes hidden=2 (HT_HIDDEN_SE: DB_TRX_ID, DB_ROLL_PTR, DB_ROW_ID)
705    //      and hidden=3 (HT_HIDDEN_SQL: functional index backing columns)
706    let visible_columns: Vec<&DdColumn> = {
707        let mut cols: Vec<&DdColumn> = all_columns
708            .iter()
709            .copied()
710            .filter(|c| c.hidden == 1 || c.hidden == 4)
711            .collect();
712        cols.sort_by_key(|c| c.ordinal_position);
713        cols
714    };
715
716    // Build ColumnDef list
717    let columns: Vec<ColumnDef> = visible_columns
718        .iter()
719        .map(|c| build_column_def(c))
720        .collect();
721
722    // Build IndexDef list
723    let indexes: Vec<IndexDef> = dd
724        .indexes
725        .iter()
726        .filter(|idx| !idx.hidden)
727        .map(|idx| build_index_def(idx, &column_by_index))
728        .collect();
729
730    // Build ForeignKeyDef list
731    let foreign_keys: Vec<ForeignKeyDef> = dd
732        .foreign_keys
733        .iter()
734        .map(|fk| build_fk_def(fk, &column_by_index))
735        .collect();
736
737    let row_fmt = row_format_name(dd.row_format);
738    let coll = collation_name(dd.collation_id);
739    let cs = charset_from_collation(dd.collation_id);
740    let mysql_ver = format_mysql_version(envelope.mysqld_version_id);
741
742    let schema_name = if dd.schema_ref.is_empty() {
743        None
744    } else {
745        Some(dd.schema_ref.clone())
746    };
747
748    let comment = if dd.comment.is_empty() {
749        None
750    } else {
751        Some(dd.comment.clone())
752    };
753
754    let mut schema = TableSchema {
755        schema_name,
756        table_name: dd.name.clone(),
757        engine: dd.engine.clone(),
758        row_format: Some(row_fmt.to_string()),
759        collation: coll.map(|s| s.to_string()),
760        charset: cs.map(|s| s.to_string()),
761        comment,
762        mysql_version: Some(mysql_ver),
763        source: "sdi".to_string(),
764        columns,
765        indexes,
766        foreign_keys,
767        ddl: String::new(),
768    };
769
770    schema.ddl = generate_ddl(&schema);
771    Ok(schema)
772}
773
774fn build_column_def(col: &DdColumn) -> ColumnDef {
775    let column_type = if !col.column_type_utf8.is_empty() {
776        col.column_type_utf8.clone()
777    } else {
778        dd_type_to_sql(col)
779    };
780
781    let default_value = if !col.default_option.is_empty() {
782        // default_option has expressions like "CURRENT_TIMESTAMP"
783        Some(col.default_option.clone())
784    } else if !col.has_no_default && !col.default_value_utf8_null {
785        // Numeric types should not be quoted in DDL
786        let is_numeric = matches!(col.dd_type, 1..=8);
787        if is_numeric {
788            Some(col.default_value_utf8.clone())
789        } else {
790            Some(format!("'{}'", col.default_value_utf8.replace('\'', "''")))
791        }
792    } else if !col.has_no_default && col.is_nullable && col.default_value_utf8_null {
793        Some("NULL".to_string())
794    } else {
795        None
796    };
797
798    let generation_expression = if !col.generation_expression_utf8.is_empty() {
799        Some(col.generation_expression_utf8.clone())
800    } else {
801        None
802    };
803
804    let is_virtual = if generation_expression.is_some() {
805        Some(col.is_virtual)
806    } else {
807        None
808    };
809
810    let comment = if col.comment.is_empty() {
811        None
812    } else {
813        Some(col.comment.clone())
814    };
815
816    ColumnDef {
817        name: col.name.clone(),
818        column_type,
819        is_nullable: col.is_nullable,
820        default_value,
821        is_auto_increment: col.is_auto_increment,
822        generation_expression,
823        is_invisible: col.hidden == 4,
824        is_virtual,
825        comment,
826    }
827}
828
829fn build_index_def(idx: &DdIndex, columns: &HashMap<u64, &DdColumn>) -> IndexDef {
830    let index_type = match idx.index_type {
831        1 => "PRIMARY KEY",
832        2 => "UNIQUE KEY",
833        3 => "KEY",
834        4 => "FULLTEXT KEY",
835        5 => "SPATIAL KEY",
836        _ => "KEY",
837    };
838
839    let idx_columns: Vec<IndexColumnDef> = idx
840        .elements
841        .iter()
842        .filter(|e| !e.hidden)
843        .map(|e| {
844            let col_name = columns
845                .get(&e.column_opx)
846                .map(|c| c.name.clone())
847                .unwrap_or_else(|| format!("col_{}", e.column_opx));
848
849            let prefix_length = if e.length < 4294967295 {
850                // Detect prefix indexes: e.length is in bytes, char_length is in bytes
851                // for string types (but display width for numeric types). Convert to
852                // characters for comparison and DDL output.
853                let col = columns.get(&e.column_opx);
854                let full_len = col.map(|c| c.char_length).unwrap_or(0);
855                let max_bytes = col.map(|c| charset_max_bytes(c.collation_id)).unwrap_or(4);
856                let full_char_len = if max_bytes > 0 {
857                    full_len / max_bytes
858                } else {
859                    full_len
860                };
861                if e.length < full_char_len {
862                    // Convert byte-based e.length to characters for DDL
863                    let prefix_chars = if max_bytes > 0 {
864                        e.length / max_bytes
865                    } else {
866                        e.length
867                    };
868                    if prefix_chars > 0 {
869                        Some(prefix_chars)
870                    } else {
871                        Some(e.length)
872                    }
873                } else {
874                    None
875                }
876            } else {
877                None
878            };
879
880            let order = if e.order == 1 {
881                Some("DESC".to_string())
882            } else {
883                None
884            };
885
886            IndexColumnDef {
887                name: col_name,
888                prefix_length,
889                order,
890            }
891        })
892        .collect();
893
894    let comment = if idx.comment.is_empty() {
895        None
896    } else {
897        Some(idx.comment.clone())
898    };
899
900    IndexDef {
901        name: idx.name.clone(),
902        index_type: index_type.to_string(),
903        columns: idx_columns,
904        comment,
905        is_visible: idx.is_visible,
906    }
907}
908
909fn build_fk_def(fk: &DdForeignKey, columns: &HashMap<u64, &DdColumn>) -> ForeignKeyDef {
910    let fk_columns: Vec<String> = fk
911        .elements
912        .iter()
913        .map(|e| {
914            columns
915                .get(&e.column_opx)
916                .map(|c| c.name.clone())
917                .unwrap_or_else(|| format!("col_{}", e.column_opx))
918        })
919        .collect();
920
921    let ref_columns: Vec<String> = fk
922        .elements
923        .iter()
924        .map(|e| e.referenced_column_name.clone())
925        .collect();
926
927    let ref_table = if fk.referenced_table_schema_name.is_empty() {
928        format!("`{}`", fk.referenced_table_name)
929    } else {
930        format!(
931            "`{}`.`{}`",
932            fk.referenced_table_schema_name, fk.referenced_table_name
933        )
934    };
935
936    ForeignKeyDef {
937        name: fk.name.clone(),
938        columns: fk_columns,
939        referenced_table: ref_table,
940        referenced_columns: ref_columns,
941        on_update: fk_rule_name(fk.update_rule).to_string(),
942        on_delete: fk_rule_name(fk.delete_rule).to_string(),
943    }
944}
945
946// ---------------------------------------------------------------------------
947// DDL generation
948// ---------------------------------------------------------------------------
949
950/// Generate `CREATE TABLE` DDL from a [`TableSchema`].
951///
952/// Produces MySQL-compatible DDL including column definitions, primary key,
953/// secondary indexes, foreign keys, and table options.
954pub fn generate_ddl(schema: &TableSchema) -> String {
955    let mut ddl = format!("CREATE TABLE `{}` (\n", schema.table_name);
956    let mut parts: Vec<String> = Vec::new();
957
958    // Columns
959    for col in &schema.columns {
960        parts.push(format_column_ddl(col));
961    }
962
963    // Indexes
964    for idx in &schema.indexes {
965        parts.push(format_index_ddl(idx));
966    }
967
968    // Foreign keys
969    for fk in &schema.foreign_keys {
970        parts.push(format_fk_ddl(fk));
971    }
972
973    ddl.push_str(&parts.join(",\n"));
974    ddl.push_str("\n)");
975
976    // Table options
977    let mut options = Vec::new();
978    options.push(format!("ENGINE={}", schema.engine));
979    if let Some(ref cs) = schema.charset {
980        options.push(format!("DEFAULT CHARSET={}", cs));
981    }
982    if let Some(ref coll) = schema.collation {
983        options.push(format!("COLLATE={}", coll));
984    }
985    if let Some(ref fmt) = schema.row_format {
986        if fmt != "DYNAMIC" {
987            // DYNAMIC is the default, only show non-default
988            options.push(format!("ROW_FORMAT={}", fmt));
989        }
990    }
991    if let Some(ref comment) = schema.comment {
992        options.push(format!("COMMENT='{}'", comment.replace('\'', "''")));
993    }
994
995    if !options.is_empty() {
996        ddl.push(' ');
997        ddl.push_str(&options.join(" "));
998    }
999    ddl.push(';');
1000
1001    ddl
1002}
1003
1004fn format_column_ddl(col: &ColumnDef) -> String {
1005    let mut parts = vec![format!("  `{}` {}", col.name, col.column_type)];
1006
1007    if !col.is_nullable {
1008        parts.push("NOT NULL".to_string());
1009    }
1010
1011    if let Some(ref default) = col.default_value {
1012        parts.push(format!("DEFAULT {}", default));
1013    }
1014
1015    if col.is_auto_increment {
1016        parts.push("AUTO_INCREMENT".to_string());
1017    }
1018
1019    if let Some(ref expr) = col.generation_expression {
1020        let stored_or_virtual = if col.is_virtual == Some(true) {
1021            "VIRTUAL"
1022        } else {
1023            "STORED"
1024        };
1025        parts.push(format!(
1026            "GENERATED ALWAYS AS ({}) {}",
1027            expr, stored_or_virtual
1028        ));
1029    }
1030
1031    if col.is_invisible {
1032        parts.push("/*!80023 INVISIBLE */".to_string());
1033    }
1034
1035    if let Some(ref comment) = col.comment {
1036        parts.push(format!("COMMENT '{}'", comment.replace('\'', "''")));
1037    }
1038
1039    parts.join(" ")
1040}
1041
1042fn format_index_ddl(idx: &IndexDef) -> String {
1043    let cols = format_index_columns(&idx.columns);
1044
1045    let visibility = if !idx.is_visible {
1046        " /*!80000 INVISIBLE */"
1047    } else {
1048        ""
1049    };
1050
1051    let comment = if let Some(ref c) = idx.comment {
1052        format!(" COMMENT '{}'", c.replace('\'', "''"))
1053    } else {
1054        String::new()
1055    };
1056
1057    match idx.index_type.as_str() {
1058        "PRIMARY KEY" => format!("  PRIMARY KEY ({}){}{}", cols, comment, visibility),
1059        _ => format!(
1060            "  {} `{}` ({}){}{}",
1061            idx.index_type, idx.name, cols, comment, visibility
1062        ),
1063    }
1064}
1065
1066fn format_index_columns(columns: &[IndexColumnDef]) -> String {
1067    columns
1068        .iter()
1069        .map(|c| {
1070            let mut s = format!("`{}`", c.name);
1071            if let Some(len) = c.prefix_length {
1072                s.push_str(&format!("({})", len));
1073            }
1074            if let Some(ref ord) = c.order {
1075                s.push(' ');
1076                s.push_str(ord);
1077            }
1078            s
1079        })
1080        .collect::<Vec<_>>()
1081        .join(", ")
1082}
1083
1084fn format_fk_ddl(fk: &ForeignKeyDef) -> String {
1085    let cols = fk
1086        .columns
1087        .iter()
1088        .map(|c| format!("`{}`", c))
1089        .collect::<Vec<_>>()
1090        .join(", ");
1091    let ref_cols = fk
1092        .referenced_columns
1093        .iter()
1094        .map(|c| format!("`{}`", c))
1095        .collect::<Vec<_>>()
1096        .join(", ");
1097
1098    let mut s = format!(
1099        "  CONSTRAINT `{}` FOREIGN KEY ({}) REFERENCES {} ({})",
1100        fk.name, cols, fk.referenced_table, ref_cols
1101    );
1102
1103    if fk.on_delete != "NO ACTION" {
1104        s.push_str(&format!(" ON DELETE {}", fk.on_delete));
1105    }
1106    if fk.on_update != "NO ACTION" {
1107        s.push_str(&format!(" ON UPDATE {}", fk.on_update));
1108    }
1109
1110    s
1111}
1112
1113// ---------------------------------------------------------------------------
1114// Pre-8.0 inference
1115// ---------------------------------------------------------------------------
1116
1117/// Infer basic schema information from INDEX page structure (pre-8.0 fallback).
1118///
1119/// Scans all pages in the tablespace, collects INDEX page metadata (index_id,
1120/// level, compact/redundant format), and returns a summary of detected indexes.
1121pub fn infer_schema_from_pages(
1122    ts: &mut crate::innodb::tablespace::Tablespace,
1123) -> Result<InferredSchema, IdbError> {
1124    use crate::innodb::index::IndexHeader;
1125    use crate::innodb::page::FilHeader;
1126    use crate::innodb::page_types::PageType;
1127    use std::collections::BTreeMap;
1128
1129    let page_count = ts.page_count();
1130    let mut is_compact = true;
1131    let mut index_stats: BTreeMap<u64, (u64, u16)> = BTreeMap::new(); // index_id -> (leaf_count, max_level)
1132
1133    for page_num in 0..page_count {
1134        let page_data = match ts.read_page(page_num) {
1135            Ok(d) => d,
1136            Err(_) => continue,
1137        };
1138
1139        let header = match FilHeader::parse(&page_data) {
1140            Some(h) => h,
1141            None => continue,
1142        };
1143
1144        if header.page_type != PageType::Index {
1145            continue;
1146        }
1147
1148        let idx = match IndexHeader::parse(&page_data) {
1149            Some(h) => h,
1150            None => continue,
1151        };
1152
1153        if !idx.is_compact() {
1154            is_compact = false;
1155        }
1156
1157        let entry = index_stats.entry(idx.index_id).or_insert((0, 0));
1158        if idx.is_leaf() {
1159            entry.0 += 1;
1160        }
1161        if idx.level > entry.1 {
1162            entry.1 = idx.level;
1163        }
1164    }
1165
1166    let indexes = index_stats
1167        .into_iter()
1168        .map(|(index_id, (leaf_pages, max_level))| InferredIndex {
1169            index_id,
1170            leaf_pages,
1171            max_level,
1172        })
1173        .collect();
1174
1175    Ok(InferredSchema {
1176        source: "Inferred (no SDI metadata available)".to_string(),
1177        record_format: if is_compact { "COMPACT" } else { "REDUNDANT" }.to_string(),
1178        indexes,
1179    })
1180}
1181
1182// ---------------------------------------------------------------------------
1183// Tests
1184// ---------------------------------------------------------------------------
1185
1186#[cfg(test)]
1187mod tests {
1188    use super::*;
1189
1190    #[test]
1191    fn test_collation_name() {
1192        assert_eq!(collation_name(255), Some("utf8mb4_0900_ai_ci"));
1193        assert_eq!(collation_name(63), Some("binary"));
1194        assert_eq!(collation_name(45), Some("utf8mb4_general_ci"));
1195        assert_eq!(collation_name(46), Some("utf8mb4_bin"));
1196        assert_eq!(collation_name(33), Some("utf8mb3_general_ci"));
1197        assert_eq!(collation_name(0), None);
1198    }
1199
1200    #[test]
1201    fn test_charset_from_collation() {
1202        assert_eq!(charset_from_collation(255), Some("utf8mb4"));
1203        assert_eq!(charset_from_collation(63), Some("binary"));
1204        assert_eq!(charset_from_collation(8), Some("latin1"));
1205        assert_eq!(charset_from_collation(33), Some("utf8mb3"));
1206        assert_eq!(charset_from_collation(0), None);
1207    }
1208
1209    #[test]
1210    fn test_row_format_name() {
1211        assert_eq!(row_format_name(1), "FIXED");
1212        assert_eq!(row_format_name(2), "DYNAMIC");
1213        assert_eq!(row_format_name(3), "COMPRESSED");
1214        assert_eq!(row_format_name(99), "UNKNOWN");
1215    }
1216
1217    #[test]
1218    fn test_fk_rule_name() {
1219        assert_eq!(fk_rule_name(0), "NO ACTION");
1220        assert_eq!(fk_rule_name(1), "RESTRICT");
1221        assert_eq!(fk_rule_name(2), "CASCADE");
1222        assert_eq!(fk_rule_name(3), "SET NULL");
1223        assert_eq!(fk_rule_name(4), "SET DEFAULT");
1224    }
1225
1226    #[test]
1227    fn test_dd_type_to_sql_int() {
1228        let col = DdColumn {
1229            dd_type: 4,
1230            numeric_precision: 10,
1231            ..Default::default()
1232        };
1233        assert_eq!(dd_type_to_sql(&col), "int");
1234    }
1235
1236    #[test]
1237    fn test_dd_type_to_sql_varchar() {
1238        let col = DdColumn {
1239            dd_type: 16,
1240            char_length: 400,
1241            collation_id: 255, // utf8mb4 = 4 bytes/char
1242            ..Default::default()
1243        };
1244        assert_eq!(dd_type_to_sql(&col), "varchar(100)");
1245    }
1246
1247    #[test]
1248    fn test_dd_type_to_sql_decimal() {
1249        let col = DdColumn {
1250            dd_type: 6,
1251            numeric_precision: 10,
1252            numeric_scale: 2,
1253            ..Default::default()
1254        };
1255        assert_eq!(dd_type_to_sql(&col), "decimal(10,2)");
1256    }
1257
1258    #[test]
1259    fn test_dd_type_to_sql_text() {
1260        let col = DdColumn {
1261            dd_type: 27,
1262            char_length: 65535,
1263            ..Default::default()
1264        };
1265        assert_eq!(dd_type_to_sql(&col), "text");
1266
1267        let col = DdColumn {
1268            dd_type: 27,
1269            char_length: 16777215,
1270            ..Default::default()
1271        };
1272        assert_eq!(dd_type_to_sql(&col), "mediumtext");
1273    }
1274
1275    #[test]
1276    fn test_format_mysql_version() {
1277        assert_eq!(format_mysql_version(90001), "9.0.1");
1278        assert_eq!(format_mysql_version(80040), "8.0.40");
1279        assert_eq!(format_mysql_version(0), "unknown");
1280    }
1281
1282    #[test]
1283    fn test_extract_schema_from_sdi_minimal() {
1284        let json = r#"{
1285            "mysqld_version_id": 90001,
1286            "dd_object_type": "Table",
1287            "dd_object": {
1288                "name": "users",
1289                "schema_ref": "myapp",
1290                "engine": "InnoDB",
1291                "collation_id": 255,
1292                "row_format": 2,
1293                "columns": [
1294                    {
1295                        "name": "id",
1296                        "type": 4,
1297                        "column_type_utf8": "int unsigned",
1298                        "ordinal_position": 1,
1299                        "hidden": 1,
1300                        "is_nullable": false,
1301                        "is_auto_increment": true,
1302                        "has_no_default": true
1303                    },
1304                    {
1305                        "name": "email",
1306                        "type": 16,
1307                        "column_type_utf8": "varchar(255)",
1308                        "ordinal_position": 2,
1309                        "hidden": 1,
1310                        "is_nullable": false,
1311                        "has_no_default": true
1312                    },
1313                    {
1314                        "name": "DB_TRX_ID",
1315                        "type": 10,
1316                        "ordinal_position": 3,
1317                        "hidden": 2
1318                    },
1319                    {
1320                        "name": "DB_ROLL_PTR",
1321                        "type": 9,
1322                        "ordinal_position": 4,
1323                        "hidden": 2
1324                    }
1325                ],
1326                "indexes": [
1327                    {
1328                        "name": "PRIMARY",
1329                        "type": 1,
1330                        "hidden": false,
1331                        "is_visible": true,
1332                        "elements": [
1333                            { "column_opx": 0, "hidden": false, "length": 4, "order": 2 },
1334                            { "column_opx": 2, "hidden": true, "length": 4294967295, "order": 2 },
1335                            { "column_opx": 3, "hidden": true, "length": 4294967295, "order": 2 }
1336                        ]
1337                    },
1338                    {
1339                        "name": "idx_email",
1340                        "type": 2,
1341                        "hidden": false,
1342                        "is_visible": true,
1343                        "elements": [
1344                            { "column_opx": 1, "hidden": false, "length": 4294967295, "order": 2 },
1345                            { "column_opx": 0, "hidden": true, "length": 4294967295, "order": 2 }
1346                        ]
1347                    }
1348                ],
1349                "foreign_keys": []
1350            }
1351        }"#;
1352
1353        let schema = extract_schema_from_sdi(json).unwrap();
1354        assert_eq!(schema.table_name, "users");
1355        assert_eq!(schema.schema_name, Some("myapp".to_string()));
1356        assert_eq!(schema.engine, "InnoDB");
1357        assert_eq!(schema.source, "sdi");
1358        assert_eq!(schema.columns.len(), 2);
1359        assert_eq!(schema.columns[0].name, "id");
1360        assert_eq!(schema.columns[0].column_type, "int unsigned");
1361        assert!(schema.columns[0].is_auto_increment);
1362        assert_eq!(schema.columns[1].name, "email");
1363        assert_eq!(schema.columns[1].column_type, "varchar(255)");
1364        assert_eq!(schema.indexes.len(), 2);
1365        assert_eq!(schema.indexes[0].index_type, "PRIMARY KEY");
1366        assert_eq!(schema.indexes[0].columns.len(), 1); // hidden elements filtered
1367        assert_eq!(schema.indexes[1].index_type, "UNIQUE KEY");
1368        assert_eq!(schema.indexes[1].name, "idx_email");
1369        assert!(schema.ddl.contains("CREATE TABLE `users`"));
1370        assert!(schema
1371            .ddl
1372            .contains("`id` int unsigned NOT NULL AUTO_INCREMENT"));
1373        assert!(schema.ddl.contains("PRIMARY KEY (`id`)"));
1374        assert!(schema.ddl.contains("UNIQUE KEY `idx_email` (`email`)"));
1375    }
1376
1377    #[test]
1378    fn test_extract_schema_with_fk() {
1379        let json = r#"{
1380            "mysqld_version_id": 80040,
1381            "dd_object_type": "Table",
1382            "dd_object": {
1383                "name": "orders",
1384                "schema_ref": "shop",
1385                "engine": "InnoDB",
1386                "collation_id": 255,
1387                "row_format": 2,
1388                "columns": [
1389                    {
1390                        "name": "id",
1391                        "type": 4,
1392                        "column_type_utf8": "int",
1393                        "ordinal_position": 1,
1394                        "hidden": 1,
1395                        "is_nullable": false,
1396                        "is_auto_increment": true
1397                    },
1398                    {
1399                        "name": "user_id",
1400                        "type": 4,
1401                        "column_type_utf8": "int",
1402                        "ordinal_position": 2,
1403                        "hidden": 1,
1404                        "is_nullable": false
1405                    }
1406                ],
1407                "indexes": [
1408                    {
1409                        "name": "PRIMARY",
1410                        "type": 1,
1411                        "hidden": false,
1412                        "is_visible": true,
1413                        "elements": [
1414                            { "column_opx": 0, "hidden": false, "length": 4, "order": 2 }
1415                        ]
1416                    }
1417                ],
1418                "foreign_keys": [
1419                    {
1420                        "name": "fk_orders_user",
1421                        "referenced_table_schema_name": "shop",
1422                        "referenced_table_name": "users",
1423                        "update_rule": 0,
1424                        "delete_rule": 2,
1425                        "elements": [
1426                            { "column_opx": 1, "referenced_column_name": "id" }
1427                        ]
1428                    }
1429                ]
1430            }
1431        }"#;
1432
1433        let schema = extract_schema_from_sdi(json).unwrap();
1434        assert_eq!(schema.foreign_keys.len(), 1);
1435        let fk = &schema.foreign_keys[0];
1436        assert_eq!(fk.name, "fk_orders_user");
1437        assert_eq!(fk.columns, vec!["user_id"]);
1438        assert_eq!(fk.referenced_table, "`shop`.`users`");
1439        assert_eq!(fk.referenced_columns, vec!["id"]);
1440        assert_eq!(fk.on_delete, "CASCADE");
1441        assert_eq!(fk.on_update, "NO ACTION");
1442        assert!(schema.ddl.contains("CONSTRAINT `fk_orders_user` FOREIGN KEY (`user_id`) REFERENCES `shop`.`users` (`id`) ON DELETE CASCADE"));
1443    }
1444
1445    #[test]
1446    fn test_extract_schema_with_generated_column() {
1447        let json = r#"{
1448            "mysqld_version_id": 80040,
1449            "dd_object_type": "Table",
1450            "dd_object": {
1451                "name": "products",
1452                "schema_ref": "shop",
1453                "engine": "InnoDB",
1454                "collation_id": 255,
1455                "row_format": 2,
1456                "columns": [
1457                    {
1458                        "name": "price",
1459                        "type": 6,
1460                        "column_type_utf8": "decimal(10,2)",
1461                        "ordinal_position": 1,
1462                        "hidden": 1,
1463                        "is_nullable": false
1464                    },
1465                    {
1466                        "name": "tax",
1467                        "type": 6,
1468                        "column_type_utf8": "decimal(10,2)",
1469                        "ordinal_position": 2,
1470                        "hidden": 1,
1471                        "is_nullable": true,
1472                        "is_virtual": true,
1473                        "generation_expression_utf8": "`price` * 0.1"
1474                    }
1475                ],
1476                "indexes": [],
1477                "foreign_keys": []
1478            }
1479        }"#;
1480
1481        let schema = extract_schema_from_sdi(json).unwrap();
1482        assert_eq!(schema.columns.len(), 2);
1483        let tax = &schema.columns[1];
1484        assert_eq!(tax.generation_expression, Some("`price` * 0.1".to_string()));
1485        assert_eq!(tax.is_virtual, Some(true));
1486        assert!(schema
1487            .ddl
1488            .contains("GENERATED ALWAYS AS (`price` * 0.1) VIRTUAL"));
1489    }
1490
1491    #[test]
1492    fn test_ddl_generation_table_options() {
1493        let schema = TableSchema {
1494            schema_name: Some("mydb".to_string()),
1495            table_name: "test".to_string(),
1496            engine: "InnoDB".to_string(),
1497            row_format: Some("COMPRESSED".to_string()),
1498            collation: Some("utf8mb4_0900_ai_ci".to_string()),
1499            charset: Some("utf8mb4".to_string()),
1500            comment: None,
1501            mysql_version: Some("8.0.40".to_string()),
1502            source: "sdi".to_string(),
1503            columns: vec![ColumnDef {
1504                name: "id".to_string(),
1505                column_type: "int".to_string(),
1506                is_nullable: false,
1507                default_value: None,
1508                is_auto_increment: true,
1509                generation_expression: None,
1510                is_invisible: false,
1511                is_virtual: None,
1512                comment: None,
1513            }],
1514            indexes: vec![IndexDef {
1515                name: "PRIMARY".to_string(),
1516                index_type: "PRIMARY KEY".to_string(),
1517                columns: vec![IndexColumnDef {
1518                    name: "id".to_string(),
1519                    prefix_length: None,
1520                    order: None,
1521                }],
1522                comment: None,
1523                is_visible: true,
1524            }],
1525            foreign_keys: vec![],
1526            ddl: String::new(),
1527        };
1528
1529        let ddl = generate_ddl(&schema);
1530        assert!(ddl.contains("ENGINE=InnoDB"));
1531        assert!(ddl.contains("DEFAULT CHARSET=utf8mb4"));
1532        assert!(ddl.contains("COLLATE=utf8mb4_0900_ai_ci"));
1533        assert!(ddl.contains("ROW_FORMAT=COMPRESSED"));
1534    }
1535
1536    #[test]
1537    fn test_hidden_column_filtering() {
1538        let json = r#"{
1539            "mysqld_version_id": 90001,
1540            "dd_object_type": "Table",
1541            "dd_object": {
1542                "name": "t",
1543                "engine": "InnoDB",
1544                "collation_id": 255,
1545                "row_format": 2,
1546                "columns": [
1547                    { "name": "a", "type": 4, "column_type_utf8": "int", "ordinal_position": 1, "hidden": 1 },
1548                    { "name": "b", "type": 4, "column_type_utf8": "int", "ordinal_position": 2, "hidden": 1 },
1549                    { "name": "DB_TRX_ID", "type": 10, "ordinal_position": 3, "hidden": 2 },
1550                    { "name": "DB_ROLL_PTR", "type": 9, "ordinal_position": 4, "hidden": 2 },
1551                    { "name": "DB_ROW_ID", "type": 10, "ordinal_position": 5, "hidden": 2 }
1552                ],
1553                "indexes": [],
1554                "foreign_keys": []
1555            }
1556        }"#;
1557
1558        let schema = extract_schema_from_sdi(json).unwrap();
1559        assert_eq!(schema.columns.len(), 2);
1560        assert_eq!(schema.columns[0].name, "a");
1561        assert_eq!(schema.columns[1].name, "b");
1562    }
1563
1564    #[test]
1565    fn test_nullable_column_default_null() {
1566        let json = r#"{
1567            "mysqld_version_id": 90001,
1568            "dd_object_type": "Table",
1569            "dd_object": {
1570                "name": "t",
1571                "engine": "InnoDB",
1572                "collation_id": 255,
1573                "row_format": 2,
1574                "columns": [
1575                    {
1576                        "name": "notes",
1577                        "type": 16,
1578                        "column_type_utf8": "varchar(255)",
1579                        "ordinal_position": 1,
1580                        "hidden": 1,
1581                        "is_nullable": true,
1582                        "has_no_default": false,
1583                        "default_value_utf8": "",
1584                        "default_value_utf8_null": true
1585                    }
1586                ],
1587                "indexes": [],
1588                "foreign_keys": []
1589            }
1590        }"#;
1591
1592        let schema = extract_schema_from_sdi(json).unwrap();
1593        assert_eq!(schema.columns[0].default_value, Some("NULL".to_string()));
1594        assert!(schema.ddl.contains("DEFAULT NULL"));
1595    }
1596
1597    #[test]
1598    fn test_empty_string_default() {
1599        let json = r#"{
1600            "mysqld_version_id": 90001,
1601            "dd_object_type": "Table",
1602            "dd_object": {
1603                "name": "t",
1604                "engine": "InnoDB",
1605                "collation_id": 255,
1606                "row_format": 2,
1607                "columns": [
1608                    {
1609                        "name": "tag",
1610                        "type": 16,
1611                        "column_type_utf8": "varchar(50)",
1612                        "ordinal_position": 1,
1613                        "hidden": 1,
1614                        "is_nullable": false,
1615                        "has_no_default": false,
1616                        "default_value_utf8": "",
1617                        "default_value_utf8_null": false
1618                    }
1619                ],
1620                "indexes": [],
1621                "foreign_keys": []
1622            }
1623        }"#;
1624
1625        let schema = extract_schema_from_sdi(json).unwrap();
1626        assert_eq!(schema.columns[0].default_value, Some("''".to_string()));
1627        assert!(schema.ddl.contains("DEFAULT ''"));
1628    }
1629
1630    #[test]
1631    fn test_invisible_column() {
1632        let json = r#"{
1633            "mysqld_version_id": 80040,
1634            "dd_object_type": "Table",
1635            "dd_object": {
1636                "name": "t",
1637                "engine": "InnoDB",
1638                "collation_id": 255,
1639                "row_format": 2,
1640                "columns": [
1641                    {
1642                        "name": "id",
1643                        "type": 4,
1644                        "column_type_utf8": "int",
1645                        "ordinal_position": 1,
1646                        "hidden": 1,
1647                        "is_nullable": false,
1648                        "is_auto_increment": true,
1649                        "has_no_default": true
1650                    },
1651                    {
1652                        "name": "secret",
1653                        "type": 16,
1654                        "column_type_utf8": "varchar(100)",
1655                        "ordinal_position": 2,
1656                        "hidden": 4,
1657                        "is_nullable": true,
1658                        "has_no_default": false,
1659                        "default_value_utf8_null": true
1660                    },
1661                    {
1662                        "name": "DB_TRX_ID",
1663                        "type": 10,
1664                        "column_type_utf8": "",
1665                        "ordinal_position": 3,
1666                        "hidden": 2
1667                    }
1668                ],
1669                "indexes": [],
1670                "foreign_keys": []
1671            }
1672        }"#;
1673
1674        let schema = extract_schema_from_sdi(json).unwrap();
1675        // Should include both visible (hidden=1) and invisible (hidden=4), skip SE-hidden (hidden=2)
1676        assert_eq!(schema.columns.len(), 2);
1677        assert_eq!(schema.columns[0].name, "id");
1678        assert!(!schema.columns[0].is_invisible);
1679        assert_eq!(schema.columns[1].name, "secret");
1680        assert!(schema.columns[1].is_invisible);
1681        // DDL should contain INVISIBLE marker
1682        assert!(schema.ddl.contains("/*!80023 INVISIBLE */"));
1683        // DDL should not contain the SE-hidden column
1684        assert!(!schema.ddl.contains("DB_TRX_ID"));
1685    }
1686
1687    #[test]
1688    fn test_index_desc_order() {
1689        let json = r#"{
1690            "mysqld_version_id": 80040,
1691            "dd_object_type": "Table",
1692            "dd_object": {
1693                "name": "t",
1694                "engine": "InnoDB",
1695                "collation_id": 255,
1696                "row_format": 2,
1697                "columns": [
1698                    { "name": "a", "type": 4, "column_type_utf8": "int", "ordinal_position": 1, "hidden": 1 },
1699                    { "name": "b", "type": 4, "column_type_utf8": "int", "ordinal_position": 2, "hidden": 1 }
1700                ],
1701                "indexes": [
1702                    {
1703                        "name": "idx_b_desc",
1704                        "type": 3,
1705                        "hidden": false,
1706                        "is_visible": true,
1707                        "elements": [
1708                            { "column_opx": 1, "hidden": false, "length": 4294967295, "order": 1 }
1709                        ]
1710                    }
1711                ],
1712                "foreign_keys": []
1713            }
1714        }"#;
1715
1716        let schema = extract_schema_from_sdi(json).unwrap();
1717        assert_eq!(schema.indexes[0].columns[0].order, Some("DESC".to_string()));
1718        assert!(schema.ddl.contains("`b` DESC"));
1719    }
1720}
idb/innodb/schema.rs

idb/innodb/
schema.rs
