Assignment of id in Tables and Columns

Explanation of how assigning a unique id to a table ingot and columns works

Assigning IDs in tables and columns is an important aspect of working with databases, especially when managing table structures and their evolution. It helps ensure uniqueness and relationships between different database components, as well as maintaining data integrity.

How It Works

  1. The TableComparator class is responsible for comparing tables in new and old database schemas, calculating the percentage of similarity for various table components such as columns, computed columns, unique options, constraints, foreign keys, and relationships.

  2. TableComparator methods use various helper methods to compare different aspects of tables, including calculating the similarity percentages between columns and other elements.

  3. The ColumnsComparator class specializes in comparing only columns, including computed columns, and determines the percentage of similarity based on column fields and values.

  4. The calculatePercentagesOfModifiedColumns method in the ColumnsComparator class compares new and old columns, calculating the percentage of matching their properties.

  5. The calculatePercentagesOfModifiedComputedColumns method in the ColumnsComparator class performs a similar comparison for computed columns.

  6. The _getUniqueColumns method in the ColumnsComparator class and the calculatePercentagesOfTablesWithModifiedState method in the TableComparator class are used for filtering and detecting unique elements in new and old tables.

  7. The TableComparator class interacts with the ColumnsComparator class to obtain detailed information about columns and their percentage similarity.

  8. Both classes provide precise analysis of schema changes, allowing for the identification of which table elements have been modified, added, or removed.

  9. The classes use different data types and helper functions to assess changes in the database based on specific interfaces and data structures.

  10. The interaction between these classes ensures comprehensive comparison and detailed analysis of the database for managing changes and updates.

TableCreator Class Overview

The TableCreator class is an essential component for creating and processing tables in your database. It interacts with other parts of the system to create tables based on models and check their state against the current state of the database. Here is a detailed description of its functions and methods:

Main Responsibilities

  • Creating Tables (createIngotOfTables)

    The createIngotOfTables method is responsible for creating table structures based on models. It retrieves the current state of the database, compares it with the provided models, and processes this data to generate SQL queries for table creation.

    Process:

    • Checks for the existence of models.

    • Retrieves the current state of database tables.

    • Prepares table models.

    • Constructs the database state based on prepared models and the current table state.

    • Processes tables with modified state, new tables, and tables with original names to generate SQL queries.

  • Table Formation (_formationOfTableIngot)

    The _formationOfTableIngot method processes the retrieved table state data and forms a TableIngot, which is an abstraction for creating SQL queries.

    Process:

    • Processes new tables and tables with modified state.

    • Forms results for tables with original names.

  • Generating SQL Queries for Many-to-Many Relationships (generateCreateTableQueryForManyToManyRelation)

    The generateCreateTableQueryForManyToManyRelation method creates SQL queries for tables implementing many-to-many relationships.

    Process:

    • Collects information about many-to-many relationships.

    • Creates tables for each such relationship with the appropriate primary keys.

  • Generating SQL Queries for Tables (generateCreateTableQuery)

    The generateCreateTableQuery method generates SQL queries for creating tables based on TableIngot.

    Process:

    • Creates SQL queries for tables with different types of relationships (one-to-many, many-to-many, one-to-one).

    • Calls the method for generating SQL queries for many-to-many relationships.

    • Outputs the generated SQL queries to the console for verification.

Parameters and Methodology

  • Constructor:

    • Initializes data source and processors for creating tables and building the database state.

  • Internal Methods:

    • _formationOfTableIngot: Forms tables for further use.

    • generateCreateTableQueryForManyToManyRelation: Generates SQL queries for many-to-many relationships.

    • generateCreateTableQuery: Generates SQL queries for creating tables based on models.

This class provides a comprehensive solution for table creation in the database, handling various aspects and interacting with other system components for effective database structure management.

https://github.com/MyroslavShymon/ORM/blob/main/src/orm/context/table-creator/table-creator.ts

DatabaseStateBuilder Class Overview

The DatabaseStateBuilder class is responsible for processing and comparing the database state to determine which tables exist, which have changed, and which need to be created or deleted. Here is a detailed analysis of its functions and methods:

Main Responsibilities

  • Preparing Models (getPreparedModels)

    The getPreparedModels method processes models to create TableIngot, which is an abstraction for database tables. It extracts metadata from decorators applied to model classes.

    Process:

    • For each model, retrieves metadata about the table, columns, primary keys, foreign keys, and relationships (one-to-one, one-to-many, many-to-many).

    • Creates TableIngot based on the retrieved metadata.

    • Returns an array of prepared models in the TableIngotInterface format.

  • Forming Database State (formationOfDatabaseState)

    The formationOfDatabaseState method forms the database state by comparing the prepared models with the current tables in the database.

    Process:

    • Identifies tables with original names (i.e., tables already existing in the database).

    • Identifies new tables that need to be created.

    • Identifies tables that have been removed from the database.

    • Returns a DatabaseStateInterface object containing this data.

Parameters and Methodology

  • Constructor:

    • Initializes the class without parameters. The constructor does not have specific logic in this class.

  • Methods:

    • getPreparedModels: Uses reflection to obtain metadata from decorators applied to model classes. Processes this data to create TableIngot for each model. Returns an array of TableIngot for further processing.

    • formationOfDatabaseState: Compares prepared models with current tables in the database. Forms a list of tables with original names, new tables, and deleted tables. Returns a DatabaseStateInterface object containing information about these tables.

Interaction with Other Components

  • Decorator Metadata:

    • The class uses reflection to access metadata written with decorators. This allows for dynamically forming information about tables and their structure based on model classes.

  • Database State Formation:

    • The class interacts with other components, such as TableCreator, to compare the database state and prepare SQL queries for database modifications based on changes.

This class provides essential functions for preparing and comparing the database state, allowing the ORM system to effectively manage database tables, identify necessary changes, and ensure synchronization between models and the actual database structure.

https://github.com/MyroslavShymon/ORM/blob/main/src/orm/context/table-creator/database-state-builder.ts

TableCreationProcessor Class Overview

The TableCreationProcessor class is responsible for processing tables in the database that may be new, modified, or deleted. The main goal of the class is to ensure correct creation and modification of tables in the database based on the comparison of new and current tables.

Main Responsibilities

  • Processing Tables with Modified State (processingTablesWithModifiedState)

    The processingTablesWithModifiedState method analyzes tables that may be new or deleted and determines which of them need to be renamed or processed otherwise.

    Process:

    • Calculates the percentages of differences between new and deleted tables.

    • Identifies tables that need to be renamed based on the percentage of changes.

    • Filters new tables that are not renamed.

    • Returns a combined array of tables for processing, including renamed and new tables.

  • Processing New Tables (processingNewTables)

    The processingNewTables method processes new tables by assigning each table a unique identifier.

    Process:

    • Adds unique identifiers to columns and computed columns of new tables.

    • Returns updated tables with new identifiers.

  • Processing Original Tables (processingOriginalTables)

    The processingOriginalTables method processes tables with original names and updates their columns, computed columns, and other metadata.

    Process:

    • Processes columns and computed columns, determining the percentage of changes.

    • Filters and updates columns using special functions to calculate the percentage of changes.

    • Returns an array of tables with updated data.

  • Processing Columns (_handleColumns)

    The _handleColumns method processes table columns, determining their percentage changes and creating new columns as needed.

    Process:

    • Adds columns with matching names and calculates the percentage of changes.

    • Filters columns with significant percentages of changes.

    • Adds new columns to the result.

  • Filtering Unique Percentages (_filterUniquePercentages)

    The _filterUniquePercentages method filters percentage changes to avoid duplicates based on column names.

    Process:

    • Identifies duplicate column names and old column names.

    • Filters elements with duplicate names to ensure uniqueness.

Parameters and Methodology

  • Constructor:

    • Initializes TableComparator and ColumnsComparator classes.

  • Methods:

    • processingTablesWithModifiedState: Uses TableComparator to calculate the percentages of changes between tables. Identifies tables for renaming and new tables.

    • processingNewTables: Adds unique identifiers to new tables and their columns.

    • processingOriginalTables: Updates columns and computed columns of tables with original names.

    • _handleColumns: Processes columns to calculate changes and add new columns.

    • _filterUniquePercentages: Filters percentage changes to avoid duplicates.

Interaction with Other Components

  • Table and Column Comparison:

    • The class relies on comparison functions and utilities provided by TableComparator and ColumnsComparator to assess differences and similarities between tables.

  • Unique Identifiers:

    • The class generates unique identifiers for new tables to ensure they can be processed correctly.

This class provides a structured approach to managing table creation and modification in the database, ensuring that changes are accurately reflected and processed based on comparison results.

https://github.com/MyroslavShymon/ORM/blob/main/src/orm/context/table-creator/table-creation-processor.ts

TableComparator Class

Description: The TableComparator class implements the TableComparatorInterface and provides methods for comparing the state of tables between new and deleted tables. It determines the percentage of similarity between various aspects of tables, such as columns, computed columns, unique options, constraints, foreign keys, and one-to-many relationships.

Types:

  • DT: A generic type representing the database type (DatabasesTypes).

Methods:

  • calculatePercentagesOfTablesWithModifiedState

    • Description: Calculates the percentage of similarity between new and deleted tables for various aspects of the table structure.

    • Parameters:

      • newTables: Array of new tables (TableIngotInterface<DT>[]).

      • deletedTables: Array of deleted tables (TableIngotInterface<DT>[]).

    • Returns: Array of TablePercentageInterface<DT> objects containing similarity percentages.

  • _calculateOptionsPercentage

    • Description: Calculates the percentage of similarity between unique options of two tables.

    • Parameters:

      • newTableOptions: Options of the new table (TableOptionsInterface<DT>).

      • deletedTableOptions: Options of the deleted table (TableOptionsInterface<DT>).

    • Returns: Similarity percentage (number).

  • _calculateColumnPercentage

    • Description: Calculates the percentage of similarity between columns of two tables, considering primary columns.

    • Parameters:

      • newTableColumns: Columns of the new table (ColumnInterface<DT>[] | ComputedColumnInterface<DT>[]).

      • deletedTableColumns: Columns of the deleted table (ColumnInterface<DT>[] | ComputedColumnInterface<DT>[]).

      • newTablePrimaryColumnName: Name of the primary column in the new table (optional parameter, string).

      • deletedTablePrimaryColumnName: Name of the primary column in the deleted table (optional parameter, string).

    • Returns: Similarity percentage (number).

  • _calculateConstraintsPercentage

    • Description: Calculates the percentage of similarity between constraints of two tables.

    • Parameters:

      • newTableConstraints: Constraints of the new table (ConstraintInterface | ConstraintInterface[]).

      • deletedTableConstraints: Constraints of the deleted table (ConstraintInterface | ConstraintInterface[]).

    • Returns: Similarity percentage (number).

  • _calculatePrimaryKeyPercentages

    • Description: Calculates the percentage of similarity between primary keys of two tables.

    • Parameters:

      • newTablePrimaryKeys: Primary keys of the new table (string[]).

      • deletedTablePrimaryKeys: Primary keys of the deleted table (string[]).

    • Returns: Similarity percentage (number).

  • _calculateForeignKeysPercentage

    • Description: Calculates the percentage of similarity between foreign keys of two tables.

    • Parameters:

      • newTableForeignKeys: Foreign keys of the new table (ForeignKeyInterface[]).

      • deletedTableForeignKeys: Foreign keys of the deleted table (ForeignKeyInterface[]).

    • Returns: Similarity percentage (number).

  • _calculateManyToManyPercentage

    • Description: Calculates the percentage of similarity between many-to-many relationships of two tables.

    • Parameters:

      • newTableManyToMany: Many-to-many relationships of the new table (ManyToManyInterface[]).

      • deleteTableManyToMany: Many-to-many relationships of the deleted table (ManyToManyInterface[]).

    • Returns: Similarity percentage (number).

  • _calculateOneToManyPercentage

    • Description: Calculates the percentage of similarity between one-to-many relationships of two tables.

    • Parameters:

      • newTableOneToMany: One-to-many relationships of the new table (OneToManyInterface[]).

      • deleteTableOneToMany: One-to-many relationships of the deleted table (OneToManyInterface[]).

    • Returns: Similarity percentage (number).

  • _calculateOneToOnePercentage

    • Description: Calculates the percentage of similarity between one-to-one relationships of two tables.

    • Parameters:

      • newTableOneToOne: One-to-one relationships of the new table (OneToOneInterface[]).

      • deletedTableOneToOne: One-to-one relationships of the deleted table (OneToOneInterface[]).

    • Returns: Similarity percentage (number).

This class provides detailed functionality for comparing tables based on their structure and relationships, helping to understand the differences between different versions of tables.

https://github.com/MyroslavShymon/ORM/blob/main/src/orm/context/table-creator/table-comparator.ts

ColumnsComparator Class

Description: The ColumnsComparator class implements the ColumnsComparatorInterface for calculating percentage ratios between new and old columns (including computed columns). It uses two main methods to compare and calculate similarity percentages between columns in new and old tables.

Constructor:

  • The constructor has no parameters and does not initialize any variables.

Methods:

  • calculatePercentagesOfModifiedComputedColumns

    • Description: Calculates the percentage of match between new and old computed columns.

    • Parameters:

      • newComputedColumns: Array of new computed columns (ComputedColumnInterface<DT>[]).

      • oldComputedColumns: Array of old computed columns (ComputedColumnInterface<DT>[]).

    • Returns: Array of ComputedColumnPercentageInterface<DT> objects containing new column, old column, and percentage of match between them.

  • calculatePercentagesOfModifiedColumns

    • Description: Calculates the percentage of match between new and old columns.

    • Parameters:

      • newColumns: Array of new columns (ColumnInterface<DT>[]).

      • oldColumns: Array of old columns (ColumnInterface<DT>[]).

    • Returns: Array of ColumnPercentageInterface<DT> objects containing new column, old column, and percentage of match between them.

  • _getUniqueColumns

    • Description: Internal method for finding unique columns.

    • Parameters:

      • newColumns: Array of new columns (T[]).

      • oldColumns: Array of old columns (T[]).

    • Returns: Array of unique columns present in new columns but missing in old columns.

    • Explanation: The _getUniqueColumns method helps determine which columns are new compared to the old ones, allowing for the exclusion of columns that already exist in both datasets. The methods calculatePercentagesOfModifiedComputedColumns and calculatePercentagesOfModifiedColumns compare specific properties of computed and regular columns to determine their similarity based on respective fields and values.

This class is part of the system for comparing database structures and allows accurate assessment of the percentage of match between different versions of tables.

https://github.com/MyroslavShymon/ORM/blob/main/src/orm/context/table-creator/columns-comparator.ts

The TableComparator and ColumnsComparator classes work together to provide an in-depth analysis of changes in the database schema. They compare different components of tables and columns, providing detailed comparisons and similarity percentage calculations between new and old schemas. This system allows for effective tracking and management of changes in the database. The interfaces and helper methods in both classes contribute to precise and comprehensive analysis. As a result, these classes provide essential functionality for managing database schemas and maintaining their integrity.

PreviousMigrationsNextAssigning IDs to indexes and triggers

Last updated