Skip to content

Database diagrams editor that allows you to visualize and design your DB with a single query.

10.9 KB
Raw Blame
/**
 * Detects PostgreSQL features that cannot be fully converted to target dialects.
 * Used to generate warning comments in cross-dialect exports.
 */

import type { Diagram } from '@/lib/domain/diagram';
import type { DBTable } from '@/lib/domain/db-table';
import type { DBField } from '@/lib/domain/db-field';
import type { DBIndex } from '@/lib/domain/db-index';
import type { DBCustomType } from '@/lib/domain/db-custom-type';
import { DatabaseType } from '@/lib/domain/database-type';
import {
    getTypeMapping,
    postgresqlIndexTypeToMySQL,
    postgresqlIndexTypeToSQLServer,
} from './postgresql/type-mappings';

export type UnsupportedFeatureType =
    | 'type'
    | 'index'
    | 'constraint'
    | 'default'
    | 'custom_type'
    | 'array'
    | 'schema';

export interface UnsupportedFeature {
    type: UnsupportedFeatureType;
    tableName?: string;
    objectName: string;
    feature: string;
    recommendation: string;
}

/**
 * Detect all unsupported PostgreSQL features when converting to a target dialect
 */
export function detectUnsupportedFeatures(
    diagram: Diagram,
    targetDialect: DatabaseType
): UnsupportedFeature[] {
    const features: UnsupportedFeature[] = [];
    const dialectKey =
        targetDialect === DatabaseType.SQL_SERVER ? 'sqlserver' : 'mysql';

    // Check custom types (ENUMs and composites)
    if (diagram.customTypes && diagram.customTypes.length > 0) {
        features.push(
            ...detectCustomTypeIssues(diagram.customTypes, dialectKey)
        );
    }

    // Check each table
    if (diagram.tables) {
        for (const table of diagram.tables) {
            if (table.isView) continue;

            // Check fields
            features.push(...detectFieldIssues(table, dialectKey));

            // Check indexes
            features.push(...detectIndexIssues(table, dialectKey));
        }
    }

    return features;
}

/**
 * Detect issues with custom types (ENUMs, composites)
 */
function detectCustomTypeIssues(
    customTypes: DBCustomType[],
    dialectKey: 'mysql' | 'sqlserver'
): UnsupportedFeature[] {
    const features: UnsupportedFeature[] = [];

    for (const customType of customTypes) {
        const typeName = customType.schema
            ? `${customType.schema}.${customType.name}`
            : customType.name;

        if (customType.kind === 'enum') {
            const values = customType.values?.join("', '") || '';
            features.push({
                type: 'custom_type',
                objectName: typeName,
                feature: `ENUM type with values: '${values}'`,
                recommendation:
                    dialectKey === 'mysql'
                        ? `Converted to VARCHAR(255). Consider using MySQL ENUM or CHECK constraint.`
                        : `Converted to NVARCHAR(255). Consider using CHECK constraint.`,
            });
        } else if (customType.kind === 'composite') {
            const fields =
                customType.fields?.map((f) => `${f.field}: ${f.type}`) || [];
            features.push({
                type: 'custom_type',
                objectName: typeName,
                feature: `Composite type with fields: ${fields.join(', ')}`,
                recommendation:
                    dialectKey === 'mysql'
                        ? `Converted to JSON. Consider restructuring as separate columns or JSON.`
                        : `Converted to NVARCHAR(MAX) as JSON. Consider restructuring as separate columns.`,
            });
        }
    }

    return features;
}

/**
 * Detect issues with field types and defaults
 */
function detectFieldIssues(
    table: DBTable,
    dialectKey: 'mysql' | 'sqlserver'
): UnsupportedFeature[] {
    const features: UnsupportedFeature[] = [];
    const tableName = table.schema
        ? `${table.schema}.${table.name}`
        : table.name;

    for (const field of table.fields) {
        const typeName = field.type.name.toLowerCase();

        // Check for array types
        if (field.isArray || typeName.endsWith('[]')) {
            features.push({
                type: 'array',
                tableName,
                objectName: field.name,
                feature: `Array type: ${typeName}`,
                recommendation:
                    dialectKey === 'mysql'
                        ? `Converted to JSON. Use JSON_ARRAY() for inserts.`
                        : `Converted to NVARCHAR(MAX) as JSON array.`,
            });
        }

        // Check type mapping for conversion notes
        const mapping = getTypeMapping(typeName, dialectKey);
        if (mapping?.conversionNote) {
            features.push({
                type: 'type',
                tableName,
                objectName: field.name,
                feature: `Type: ${typeName}`,
                recommendation: mapping.conversionNote,
            });
        }

        // Check for PostgreSQL-specific defaults
        if (field.default) {
            const defaultLower = field.default.toLowerCase();

            // Sequences
            if (defaultLower.includes('nextval')) {
                const match = field.default.match(/nextval\('([^']+)'/);
                const seqName = match ? match[1] : 'unknown';
                features.push({
                    type: 'default',
                    tableName,
                    objectName: field.name,
                    feature: `Sequence: ${seqName}`,
                    recommendation:
                        dialectKey === 'mysql'
                            ? `Converted to AUTO_INCREMENT.`
                            : `Converted to IDENTITY(1,1).`,
                });
            }

            // PostgreSQL-specific functions
            if (
                defaultLower.includes('gen_random_uuid') ||
                defaultLower.includes('uuid_generate')
            ) {
                features.push({
                    type: 'default',
                    tableName,
                    objectName: field.name,
                    feature: `UUID generation function`,
                    recommendation:
                        dialectKey === 'mysql'
                            ? `Use UUID() function in MySQL.`
                            : `Use NEWID() function in SQL Server.`,
                });
            }

            // Array constructors
            if (
                defaultLower.includes('array[') ||
                defaultLower.includes("'{}")
            ) {
                features.push({
                    type: 'default',
                    tableName,
                    objectName: field.name,
                    feature: `Array default value`,
                    recommendation:
                        dialectKey === 'mysql'
                            ? `Converted to JSON array literal.`
                            : `Converted to JSON array string.`,
                });
            }
        }
    }

    return features;
}

/**
 * Detect issues with index types
 */
function detectIndexIssues(
    table: DBTable,
    dialectKey: 'mysql' | 'sqlserver'
): UnsupportedFeature[] {
    const features: UnsupportedFeature[] = [];
    const tableName = table.schema
        ? `${table.schema}.${table.name}`
        : table.name;

    const indexTypeMap =
        dialectKey === 'mysql'
            ? postgresqlIndexTypeToMySQL
            : postgresqlIndexTypeToSQLServer;

    for (const index of table.indexes) {
        if (index.isPrimaryKey) continue;

        const indexType = (index.type || 'btree').toLowerCase();
        const mapping = indexTypeMap[indexType];

        if (mapping?.note) {
            features.push({
                type: 'index',
                tableName,
                objectName: index.name,
                feature: `${indexType.toUpperCase()} index`,
                recommendation: mapping.note,
            });
        }
    }

    return features;
}

/**
 * Format unsupported features as a warning comment block for SQL output
 */
export function formatWarningsHeader(
    features: UnsupportedFeature[],
    sourceDialect: string,
    targetDialect: string
): string {
    if (features.length === 0) {
        return `-- ${sourceDialect} to ${targetDialect} conversion\n-- Generated by ChartDB\n`;
    }

    let header = `-- ${sourceDialect} to ${targetDialect} conversion\n`;
    header += `-- Generated by ChartDB\n`;
    header += `--\n`;
    header += `-- CONVERSION NOTES (${features.length} items):\n`;

    // Group by type
    const grouped = groupFeaturesByType(features);

    for (const [type, items] of Object.entries(grouped)) {
        header += `--\n`;
        header += `-- ${formatTypeLabel(type as UnsupportedFeatureType)}:\n`;
        for (const item of items) {
            const location = item.tableName
                ? `${item.tableName}.${item.objectName}`
                : item.objectName;
            header += `--   - ${location}: ${item.feature}\n`;
        }
    }

    header += `--\n\n`;
    return header;
}

/**
 * Group features by their type for organized output
 */
function groupFeaturesByType(
    features: UnsupportedFeature[]
): Record<string, UnsupportedFeature[]> {
    const grouped: Record<string, UnsupportedFeature[]> = {};

    for (const feature of features) {
        if (!grouped[feature.type]) {
            grouped[feature.type] = [];
        }
        grouped[feature.type].push(feature);
    }

    return grouped;
}

/**
 * Format type label for display
 */
function formatTypeLabel(type: UnsupportedFeatureType): string {
    switch (type) {
        case 'custom_type':
            return 'Custom Types (ENUM/Composite)';
        case 'array':
            return 'Array Fields';
        case 'type':
            return 'Type Conversions';
        case 'index':
            return 'Index Type Changes';
        case 'default':
            return 'Default Value Conversions';
        case 'constraint':
            return 'Constraint Changes';
        case 'schema':
            return 'Schema Changes';
        default:
            return type;
    }
}

/**
 * Get inline comment for a specific field conversion
 */
export function getFieldInlineComment(
    field: DBField,
    dialectKey: 'mysql' | 'sqlserver'
): string | null {
    const typeName = field.type.name.toLowerCase();

    // Array types
    if (field.isArray || typeName.endsWith('[]')) {
        return `Was: ${field.type.name} (PostgreSQL array)`;
    }

    // Check type mapping
    const mapping = getTypeMapping(typeName, dialectKey);
    if (mapping?.includeInlineComment && mapping.conversionNote) {
        return `Was: ${field.type.name}`;
    }

    return null;
}

/**
 * Get inline comment for an index conversion
 */
export function getIndexInlineComment(
    index: DBIndex,
    dialectKey: 'mysql' | 'sqlserver'
): string | null {
    const indexType = (index.type || 'btree').toLowerCase();
    const indexTypeMap =
        dialectKey === 'mysql'
            ? postgresqlIndexTypeToMySQL
            : postgresqlIndexTypeToSQLServer;
    const mapping = indexTypeMap[indexType];

    if (mapping?.note) {
        return mapping.note;
    }

    return null;
}