Table-Aware Caching Layer (Planned)

[N3XT0R]

📌 Task: Implement Table-Aware Caching for resolveTableSchema(...)

Goal:
Improve performance of schema resolution by caching ResultEntity objects per table, using a deterministic hash derived from native database metadata.

Why:
Each time resolveTableSchema(...) is invoked, it performs full schema introspection via Doctrine – even for unchanged tables. On large legacy schemas, this introduces avoidable overhead.

Approach:

• 🔍 Hash Calculation
  Introduce per-database TableHashResolverInterface implementations that query INFORMATION_SCHEMA (or DB-native equivalent) to build a stable hash of table structure (columns, types, nullability, defaults, etc.).

• 🧠 Cache Storage
  Store result entities in Laravel cache (e.g., file, Redis) using schema hash as key.

• 🪝 Integration Point
  Hook into resolveTableSchema(...):

  • Check cache before resolving
  • Save result to cache if no hit
  • Optionally allow cache bypass (e.g. --no-cache flag)

Deliverables:

• TableHashResolverInterface + implementations for:
  • MySQL
  • PostgreSQL
  • SQL Server
• Configurable cache store (default: file)
• Integration into generator service
• Optional CLI flag to disable cache
• Unit + integration tests

Impact:
✅ Improved performance
✅ Avoids redundant introspection
✅ Deterministic cache invalidation via schema fingerprinting

Planned for: v8.3.x

---

interface TableHashResolverInterface {
    public function getTableHash(string $schema, string $table): string;
}

class MysqlTableHashResolver implements TableHashResolverInterface {
    public function getTableHash(string $schema, string $table): string {
        $sql = <<<SQL
            SELECT 
              MD5(GROUP_CONCAT(
                COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_KEY
                ORDER BY ORDINAL_POSITION SEPARATOR '|'
              )) AS schema_hash
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = :schema
              AND TABLE_NAME = :table
            GROUP BY TABLE_NAME
        SQL;

        return $connection->fetchOne($sql, [
            'schema' => $schema,
            'table' => $table,
        ]);
    }
}

class PostgresTableHashResolver implements TableHashResolverInterface {
    public function getTableHash(string $schema, string $table): string {
        $sql = <<<SQL
            SELECT 
              md5(string_agg(
                column_name || data_type || is_nullable || coalesce(column_default, ''),
                '|'
                ORDER BY ordinal_position
              )) AS schema_hash
            FROM information_schema.columns
            WHERE table_schema = :schema
              AND table_name = :table
            GROUP BY table_name
        SQL;

        return $connection->fetchOne($sql, [
            'schema' => $schema,
            'table' => $table,
        ]);
    }
}

class SqlServerTableHashResolver implements TableHashResolverInterface {
    public function getTableHash(string $schema, string $table): string {
        $sql = <<<SQL
            SELECT 
              CONVERT(varchar(32), HASHBYTES('MD5', STRING_AGG(
                COLUMN_NAME + DATA_TYPE + IS_NULLABLE + ISNULL(COLUMN_DEFAULT, ''),
                '|'
              )), 2) AS schema_hash
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = :schema
              AND TABLE_NAME = :table
            GROUP BY TABLE_NAME
        SQL;

        return $connection->fetchOne($sql, [
            'schema' => $schema,
            'table' => $table,
        ]);
    }
}

public function getTableHash(string $schema, string $table): string
{
    $row = DB::selectOne("
        SELECT 
          MD5(GROUP_CONCAT(
              COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_KEY
              ORDER BY ORDINAL_POSITION SEPARATOR '|'
          )) AS schema_hash
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
        GROUP BY TABLE_NAME
    ", [$schema, $table]);

    return $row->schema_hash ?? '';
}

public function hasChanged(string $schema, string $table, string $cachedHash): bool
{
    return $this->getHashForTable($schema, $table) !== $cachedHash;
}