• Home
• Technical
• Support
• Downloads
• More...

_EntityVersions

Store information about application entities.

Populated by: [sp_UpdateLastCaptured]

@@@---@@@

This SQL script, t-_EntityVersions, is responsible for creating and maintaining the persistent [dbo].[_EntityVersions] table. This table serves as the central repository for tracking the versions of various entities (like procedures, functions, tables, etc.) within the dbOmnibus application across different deployment sessions and server restarts.

Script Breakdown
1. Update Persistent Entity Version Record
Metadata Declaration:

SQL

DECLARE @Name [varchar](128),@Description [varchar](128),@Version [int],@Type [varchar](5);
SET @Version=240825;
SET @Type='T';
SET @Name= '_EntityVersions';
SET @Description= 'Store information about application entities';
These lines declare variables and set the metadata for the _EntityVersions table itself, marking its version as 240825 (August 25, 2024).

EXEC [Deploy].[sp_UpdateEntityVer] @Type=@Type,@Name=@Name,@Description=@Description,@Version=@Version;: This calls the sp_UpdateEntityVer stored procedure. Crucially, as discussed in its analysis, sp_UpdateEntityVer updates a temporary table (#EntityVers) for the current deployment session. This means this line is tracking the deployment script's knowledge of the _EntityVersions table's version within this specific execution, not directly updating the persistent table with its own version number (though the persistent table's structure is what this script defines and maintains). The persistent table's actual version would be determined by the versioning of this script.

GO: Separates the variable declarations and procedure call from the main table creation block.

2. Table Creation ([dbo].[_EntityVersions])
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_EntityVersions]') AND type in (N'U')): This is a standard idempotent check to ensure the table is only created if it doesn't already exist.

CREATE TABLE [dbo].[_EntityVersions](...): If the table doesn't exist, it is created with the following columns:

[EntityName] [nvarchar](128) NULL

[EntityVersion] [int] NULL

[LastGathered] [datetime] NULL

[Retention] [int] NULL

[UpdatedDate] [datetime] NULL

[Cadence] [int] NULL

[Capture] [bit] NULL

[LastCaptured] [datetime] NULL

[Type] [varchar](50) NULL

[Schema] [varchar](50) NULL

[Description] [varchar](255) NULL
All columns are initially nullable.

CREATE UNIQUE CLUSTERED INDEX [PK__EntityVersions_EntityName] ON [dbo].[_EntityVersions]([EntityName] ASC) WITH (...) ON [PRIMARY]: Immediately after table creation, a unique clustered index named PK__EntityVersions_EntityName is created on the [EntityName] column. This effectively makes EntityName the primary key for the table, ensuring uniqueness and optimizing lookups by entity name. The WITH options are standard for index creation (e.g., PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, ALLOW_ROW_LOCKS = ON).

3. Incremental Updates (Add Columns, Drop/Add Index)
The script then includes several blocks of code, each labeled with an "Update" version (e.g., Update 231127), which use the previously defined [Deploy] utilities to progressively add columns or modify indexes. This demonstrates an incremental schema upgrade pattern.

Update 231127:

EXEC [Deploy].[sp_AddColumn] @Table='_EntityVersions', @Column='Type', @Type='[varchar](50)';

EXEC [Deploy].[sp_AddColumn] @Table='_EntityVersions', @Column='Description', @Type='[varchar](255)';
These lines add the Type and Description columns to the table if they don't already exist.

Update 231201:

EXEC [Deploy].[sp_AddColumn] @Table='_EntityVersions', @Column='Capture', @Type='[bit]';
Adds the Capture column.

Update 231206:

EXEC [Deploy].[sp_AddColumn] @Table='_EntityVersions', @Column='LastCaptured', @Type='[datetime]';
Adds the LastCaptured column.

Update 231211: This block performs an index and column modification, indicating a schema evolution:

EXEC [Deploy].[sp_DropObject] @Type='I', @Name='PK_EntityVersions_ID', @Table='_EntityVersions';

EXEC [Deploy].[sp_DropObject] @Type='I', @Name='NCUI_EntityVersions_EntityName', @Table='_EntityVersions';
These lines drop two old indexes, likely from a previous schema version.

EXEC [Deploy].[sp_AddIndex] @Table='_EntityVersions', @IndexName='PK__EntityVersions_EntityName', @Columns='[EntityName] ASC', @Unique=1, @Clustered=1, @IgnoreDupe=0;
This line then re-adds the PK__EntityVersions_EntityName index (which was also created in the initial CREATE TABLE block). This implies that if the table already existed from a previous deployment, this sp_AddIndex call would ensure the correct primary key index is in place, possibly re-creating it if its definition changed or if old, less efficient indexes were present.

EXEC [Deploy].[sp_DropObject] @Type='C', @Name='ID', @Table='_EntityVersions';
This drops an old ID column, suggesting that the EntityName is now the primary identifier, replacing a previous numeric ID.

Update 240824:

EXEC [Deploy].[sp_AddColumn] @Table='_EntityVersions', @Column='Schema', @Type='[varchar](50)';
Adds the Schema column.

Purpose and Role in the Framework
The [dbo].[_EntityVersions] table is the persistent metadata store for the dbOmnibus application. Its purpose is to:

Track Deployed Component Versions: It provides a definitive record of which version of each application entity (tables, procedures, functions, etc.) is currently installed in the database.

Facilitate Upgrades and Downgrades: By querying this table, a deployment script can determine the current state of the application and execute only the necessary upgrade or downgrade steps.

Inventory: It acts as an inventory of application components.

Audit Trail: UpdatedDate and similar columns (if populated elsewhere) could provide an audit trail of when components were last updated.

The script itself demonstrates a common and effective database migration pattern:

Idempotent Table Creation: Ensures the table exists before any other operations.

Incremental Schema Changes: Uses helper procedures (sp_AddColumn, sp_DropObject, sp_AddIndex) to apply schema changes (column additions, index modifications) in an idempotent manner, ensuring that the script can be run on both fresh installations and existing databases. This is crucial for managing database schema evolution over time.

This script is central to the self-documenting and manageable nature of the dbOmnibus deployment framework.