# Data Model: System Information Schema

This document defines the schema for the virtual tables that will dynamically expose the database's internal memory state.

## Schemas
- `system`: Virtual tables exposing raw, internal database state and metadata for debugging.
- `information_schema`: Virtual read-only views conforming to SQL standards, exposing public structural metadata.

## Virtual System Tables (`system` schema)

These are **virtual tables** dynamically generated by the query executor. They do not persist to disk independently; rather, they provide a real-time SQL window into the `MVCCEngine::schemas` HashMap.

### `system.tables`
Exposes the raw `Schema` structs held in memory.

| Column Name | Data Type | Nullable | Primary Key | Description |
| :--- | :--- | :--- | :--- | :--- |
| `id` | `UUID` | No | Yes | Unique internal identifier for the table. |
| `catalog_name` | `VARCHAR` | No | No | The database catalog (e.g., 'def' or 'default'). |
| `schema_name` | `VARCHAR` | No | No | The schema containing the table (e.g., 'public', 'system'). |
| `table_name` | `VARCHAR` | No | No | The name of the table. |
| `table_type` | `VARCHAR` | No | No | 'BASE TABLE', 'VIEW', or 'SYSTEM TABLE'. |
| `created_at` | `TIMESTAMP`| No | No | When the table was created. |
| `updated_at` | `TIMESTAMP`| No | No | When the table definition was last modified. |

*Unique Constraint*: (`catalog_name`, `schema_name`, `table_name`)

### `system.columns`
Stores the definition of all columns for all tables.

| Column Name | Data Type | Nullable | Primary Key | Description |
| :--- | :--- | :--- | :--- | :--- |
| `id` | `UUID` | No | Yes | Unique internal identifier for the column. |
| `table_id` | `UUID` | No | No | Foreign key referencing `system.tables.id`. |
| `column_name` | `VARCHAR` | No | No | The name of the column. |
| `ordinal_position` | `INTEGER` | No | No | 1-based position of the column in the table. |
| `data_type` | `VARCHAR` | No | No | The internal data type string (e.g., 'INTEGER', 'VARCHAR'). |
| `is_nullable` | `BOOLEAN` | No | No | True if the column accepts NULL values. |
| `is_primary_key`| `BOOLEAN` | No | No | True if the column is part of the primary key. |
| `column_default`| `VARCHAR` | Yes | No | The default expression or value for the column. |
| `max_length` | `INTEGER` | Yes | No | Maximum length for character types. |
| `numeric_precision`| `INTEGER` | Yes | No | Precision for numeric types. |
| `numeric_scale` | `INTEGER` | Yes | No | Scale for numeric types. |
| `datetime_precision`| `INTEGER` | Yes | No | Precision for datetime types. |

*Unique Constraint*: (`table_id`, `column_name`)

## Virtual Tables (`information_schema` schema)

These remain virtual tables generated dynamically by the executor (in `src/executor/information_schema.rs`). They query the exact same in-memory structures (`MVCCEngine::schemas`) as the `system` tables, but map the output strictly to SQL standard column names.

### `information_schema.tables`
Standard SQL representation.

- `table_catalog` (from `system.tables.catalog_name`)
- `table_schema` (from `system.tables.schema_name`)
- `table_name` (from `system.tables.table_name`)
- `table_type` (from `system.tables.table_type`)

### `information_schema.columns`
Standard SQL representation.

- `table_catalog`
- `table_schema`
- `table_name`
- `column_name`
- `ordinal_position`
- `column_default`
- `is_nullable` (Mapped from boolean to "YES" / "NO")
- `data_type`
- `character_maximum_length` (from `max_length`)
- `character_octet_length` (from `max_length` * 4, assuming UTF-8 max width)
- `numeric_precision`
- `numeric_scale`
- `datetime_precision`

## Validation Rules
1. Tables in the `system` schema and `information_schema` are read-only virtual representations. Direct DML (`INSERT`, `UPDATE`, `DELETE`, `DROP TABLE`) against them must be intercepted and rejected by the query executor or planner.