ValidationResult

Understanding the Validation Result in RestBI

The validation process in RestBI ensures that your data model aligns correctly with the actual structure of the database. This is crucial for identifying any discrepancies between the model you define and the underlying database schema, allowing you to make necessary adjustments before executing queries.

How Validation Works

The validateModel function takes a Model as input and compares it against the actual database schema. The function performs the following steps:

1. Database Connection: The function first establishes a connection to the database using the connection details provided in the Model.

2. Retrieve Actual Tables and Columns: It retrieves the list of tables and their columns from the database.

3. Table Validation: For each table in the Model, it checks if the table exists in the actual database schema:

   • If the table exists, it is marked as validated: true.

   • If the table does not exist in the database, it is marked as validated: false.

4. Column Validation: For each table that exists, the function then validates the columns:

   • If a column exists in the actual database table, it is marked as validated: true.

   • If a column does not exist, it is marked as validated: false.

5. Return Result: After the validation process, a ValidationResult object is returned, containing the updated model with validation flags and the actual tables retrieved from the database.

The ValidationResult Type

The ValidationResult type consists of two key parts:

• model: The original Model provided by the user, with added validated flags indicating the validation status of each table and column.

• dbTables: The actual Table[] retrieved from the database, reflecting the true structure of the database. Note this is also included in the metadata call.

Example Validation Process

Given a model, the validateModel function will compare the model's structure with the database and return a ValidationResult like this:

const validationResult: ValidationResult = { 
    model: {
        id: "1",
        name: "ChinookModel",
        connection: {
            id: "2",
            name: "Chinook",
            host: "localhost",
            port: 5432,
            user: "postgres",
            password: "test",
            database: "chinook",
            type: "POSTGRES"
        },
        tables: [
            {
                id: "1",
                dbName: "album",
                name: "Album",
                validated: true, // This table exists in the database
                columns: [
                    {
                        id: "title",
                        dbName: "title",
                        name: "Album Title",
                        validated: true, // This column exists in the "album" table
                        dataType: "STRING"
                    }
                ]
            },
            {
                id: "2",
                dbName: "artist",
                name: "Artist",
                validated: false, // This table does not exist in the database
                columns: []
            }
        ],
        joins: [],
        formulas: [],
        filters: []
    },
    dbTables: [
        {
            id: "1",
            dbName: "album",
            name: "Album",
            columns: [
                {
                    id: "title",
                    dbName: "title",
                    name: "Title",
                    dataType: "STRING"
                }
            ]
        }
    ]
};

Handling Validation Errors

If an error occurs during the validation process (e.g., if the database connection fails), the function returns an SQLError object with details about the error:

{
  "message": "Database connection failed",
  "query": ""
}

Practical Use

When building or validating your data models with RestBI, always run the validation process to ensure that your model correctly reflects the database schema. This helps catch errors early and guarantees that your queries will run as expected.

The validation result provides a clear view of any discrepancies, allowing you to make informed decisions about how to adjust your model before proceeding to query execution.