generateCreateTableSql function

generateCreateTableSql.ts

/**
 * Generates a SQL CREATE TABLE statement for a given RETS metadata table.
 *
 * @param {RETSMetadataTable} tableMetadata - The metadata table to generate a SQL statement for
 * @returns {string} The generated SQL statement
 */
export const generateCreateTableSql = (tableMetadata: RETSMetadataTable) => {
    const tableName = `${tableMetadata.$.Resource}_${tableMetadata.$.Class}`;
    
    // Extract the first field to use as the primary key
    const keyField = tableMetadata.Field ? tableMetadata.Field[0].SystemName : null;
    
    const columns = tableMetadata.Field?.map((field) => {
        const columnName = Array.isArray(field.SystemName) ? field.SystemName[0] : field.SystemName || '';
        const dataType = Array.isArray(field.DataType) ? field.DataType[0] : field.DataType || '';  // Handle DataType as string or array
        const maxLength = Array.isArray(field.MaximumLength) ? field.MaximumLength[0] : field.MaximumLength || 255; // Handle MaximumLength as array or single number
        const precision = Array.isArray(field.Precision) ? field.Precision[0] : field.Precision || ''; // Handle Precision as array or single number
        const longName = (Array.isArray(field.LongName) ? field.LongName[0] : field.LongName || '').replace(/'/g, "''");

        let columnType = "VARCHAR(255)";  // Default column type

        // Logic for determining column type based on DataType
        switch (dataType) {
            case 'Int':
            case 'Small':
            case 'Tiny':
                columnType = `INT(${maxLength})`;
                break;
            case 'Long':
                columnType = `BIGINT(${maxLength})`;
                break;
            case 'DateTime':
                columnType = "DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL";
                break;
            case 'Character':
                // For Character fields, use VARCHAR(maxLength) or TEXT if maxLength > 255
                columnType = maxLength <= 255 ? `VARCHAR(${maxLength})` : "TEXT";
                break;
            case 'Decimal':
                const prePoint = maxLength - (precision || 0);
                const postPoint = precision || 0;
                
                // Ensure M >= D for DECIMAL
                if (prePoint < postPoint) {
                    columnType = `DECIMAL(${postPoint},${postPoint})`; // Ensure M >= D
                } else {
                    columnType = `DECIMAL(${prePoint},${postPoint})`;
                }
                break;
            case 'Boolean':
                columnType = "CHAR(1)";
                break;
            case 'Date':
                columnType = "DATE DEFAULT '0000-00-00' NOT NULL";
                break;
            case 'Time':
                columnType = "TIME DEFAULT '00:00:00' NOT NULL";
                break;
            default:
                columnType = "VARCHAR(255)";  // Default for other types
                break;
        }

        return `${columnName} ${columnType} COMMENT '${longName}'`;
    }).join(',\n  ');

    // Add the primary key, using the first field as the keyField
    const primaryKey = keyField ? `, PRIMARY KEY(${keyField})` : '';

    return `CREATE TABLE ${tableName} (\n  ${columns}${primaryKey}\n) ENGINE=Aria;`;
};

RETSMetadataTable.d.ts

// Define the type for individual fields
export interface RETSField {
    SystemName?: string | string[];
    Interpretation?: string[];
    DataType?: string[];
    MaximumLength?: number[];
    Precision?: number[];
    LongName?: string[];
}

// Define the type for the metadata table
export interface RETSMetadataTable {
    $: {
        Resource: string;
        Class: string;
    };
    Field?: RETSField[];
}

// Define the type for the metadata section
export interface RETSMetadata {
    'METADATA-TABLE'?: RETSMetadataTable[];
}

// Define the main RETS response type
export interface RETSResponse {
    RETS: {
        $: {
            ReplyCode: string;
            ReplyText: string;
        };
        // Optional fields
        "RETS-RESPONSE"?: {
            Info?: {
                USERID?: string;
                USERLEVEL?: number;
                TIMEOUT?: number;
                USERCLASS?: string;
            };
            GetMetadata?: string;
            GetObject?: string;
            Logout?: string;
            Search?: string;
            Update?: string;
        };
        COUNT?: { Records: string };
        DELIMITER?: { value: string };
        COLUMNS?: string;
        DATA?: any[];
        MAXROWS?: {};
        METADATA?: RETSMetadata[];
        ObjectData?: {
            URL: string;
            MimeType: string;
            ObjectID: string;
            Description?: string;
        }[];
        Message?: string;
        "RETS-STATUS"?: {
            ReplyCode: string;
            ReplyText: string;
        };
    };
}

// Define the type for MLS query parameters
export interface MLSQueryParams {
    SearchType: string;
    Class: string;
    Offset: number;
    Limit: number;
    Query: string;
    QueryType: string;
    Count?: number;
    Format: string;
    StandardNames?: string;
    Select?: string;
    OrderBy?: string;
    
}