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

Config

The Lookup.Config table Stores configuration items.

Populated by: [sp_UpdateConfig]

Used by: [sp_ReadConfig]

Related objects:

• Config table
• sp_UpdateConfig proc
• sp_ReadConfig (X)

@@@ --- @@@

This SQL script, t-Config-LOOKUP, is responsible for creating and maintaining the persistent [Lookup].[Config] table. This table is designed to store configuration items for the dbOmnibus application, providing a flexible and centralized way to manage application settings.

Script Breakdown
1. Initial Entity Version Update and Variable Synchronization
This block handles the initial versioning of the Config table within the current deployment session:

Metadata Declaration:

SQL

EXEC [Deploy].[sp_UpdateEntityVer] @Type=@Type,@Name=@Name,@Description=@Description,@Version=@Version;: This calls [Deploy].[sp_UpdateEntityVer] to record the Config table's version in the temporary #EntityVers table for the current deployment session.

EXEC [Deploy].[sp_GetEntityVersion] @EntityName=@Name,@ReturnValue=@EntityVersion OUTPUT;: This calls [Deploy].[sp_GetEntityVersion] to retrieve the persisted version of Config from [dbo].[_EntityVersions].

EXEC [Deploy].[sp_UpdateVarValue] @Key='CurrentVersion',@Value=@EntityVersion;: This updates the CurrentVersion key in the temporary #Vars table. This seems to be setting the CurrentVersion in #Vars to the previously deployed version of Config, rather than the version currently being deployed by this script (@Version=241011). This might be an intended behavior for specific deployment logic or an oversight.

GO: Separates the initial versioning and variable updates from the table creation.

2. Table Creation ([Lookup].[Config])
This block handles the creation of the Config table if it doesn't exist:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Lookup].[Config]') AND type in (N'U')): This idempotent check ensures the [Lookup].[Config] table is only created if it doesn't already exist.

CREATE TABLE [Lookup].[Config](...): If the table does not exist, it is created with the following columns:

[ID] [int] IDENTITY(1,1) NOT NULL: An auto-incrementing integer serving as the primary key.

[Key] [int] NOT NULL: An integer key to identify the configuration item.

[Key_Desc] [nvarchar](255) NOT NULL: A description for the configuration key. nvarchar is appropriate for descriptive text.

[Value] [nvarchar](max) NOT NULL: The actual configuration value. nvarchar(max) allows for very large and flexible configuration values.

[Advanced] [bit] NULL: A boolean flag, likely indicating if a configuration item is for advanced users/settings.

ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]: Specifies that the table and its large value data (nvarchar(max)) should be stored on the PRIMARY filegroup.

Redundant "Update Version" Block within IF NOT EXISTS:

SQL

-- Update Version
DECLARE @Name [varchar](128),@Version [int],@Type [varchar](5)
SELECT @Type = [Value] FROM #Vars WHERE [Key] = 'CurrentType'
SELECT @Name = [Value] FROM #Vars WHERE [Key] = 'CurrentEntity'
DECLARE @Ver [int]
EXEC [Deploy].[sp_GetEntityVer] @EntityName=@Name, @ReturnValue=@Ver OUTPUT
EXEC [Deploy].[sp_UpdateEntityVer] @Type=@Type, @Name=@Name, @Version=@Ver
EXEC [Deploy].[sp_UpdateVarValue] @Key = 'CurrentVersion', @Value = @Ver
This inner "Update Version" block appears to be a copy-paste error or redundant logic. It re-declares variables, re-retrieves CurrentType and CurrentEntity from #Vars (which would still be 'T' and 'Config' from the first block), and then tries to update the entity version again based on @Ver obtained from #EntityVers. However, the version for Config was already set to 241011 by the first sp_UpdateEntityVer call. This block's purpose is unclear and potentially harmless due to idempotency but could be removed for clarity.

GO: Separates the table creation logic from subsequent updates.

3. Incremental Updates
This section applies schema changes using sp_AddColumn, demonstrating an incremental upgrade pattern:

-- Update 240805:

IF COL_LENGTH('Lookup.Config', 'Advanced') IS NULL ALTER TABLE [Lookup].[Config] ADD [Advanced] [bit] NULL;: This check and ALTER TABLE statement ensure that the Advanced column is added to the [Lookup].[Config] table only if it doesn't already exist. This is an idempotent way to handle schema evolution.

4. Manage Indices
This block ensures the correct index is present on the Config table:

EXEC [Deploy].[sp_AddIndex] @Table='Config', @Schema='Lookup', @IndexName='PK_Config_ID', @Columns='[ID] ASC', @Unique=1, @Clustered=1, @IgnoreDupe=1;: This call to [Deploy].[sp_AddIndex] creates a unique clustered index on the ID column. The IgnoreDupe=1 option is noteworthy, indicating that if duplicate ID values somehow exist during index creation (unlikely for an IDENTITY column unless data was manually inserted incorrectly), they would be ignored.

GO: Separates the index creation from the final version update.

5. Final Entity Version Update
This block updates the entity's version in the temporary table one last time:

DECLARE @Name [varchar](128),@Version [int],@Type [varchar](5);: Declares variables.

SELECT @Type = [Value] FROM #Vars WHERE [Key] = 'CurrentType';

SELECT @Name = [Value] FROM #Vars WHERE [Key] = 'CurrentEntity';: Retrieves the current entity type and name from #Vars. These should still be 'T' and 'Config'.

SELECT @Version = [Version] FROM [#EntityVers] WHERE [Name] = @Name;: Retrieves the version of 'Config' from the temporary #EntityVers table. This should be 241011 as set at the beginning of the script.

EXEC [Deploy].[sp_UpdateEntityVer] @Type=@Type, @Name=@Name, @Version=@Version;: Updates the Config entity's version in #EntityVers one more time with the same version it already has. This final call is largely redundant if the version was correctly set at the top of the script and no other version-affecting logic occurs between the first and last sp_UpdateEntityVer calls. It ensures the entry in #EntityVers is current.

GO: Final batch separator.

6. Populated By Comment
-- Populated in cf_X-Cleanup: This comment indicates that the data in this Config table is populated in a separate script named cf_X-Cleanup. This suggests that the initial setup of configuration values happens at a later stage of the deployment process.

Purpose and Role in the Framework
The [Lookup].[Config] table is a core component for storing application-specific configuration settings.

Centralized Configuration: Provides a single place to manage various application parameters (e.g., timeouts, feature flags, external service endpoints).

Dynamic Settings: Allows applications to read settings directly from the database, enabling changes without requiring code deployments.

Idempotent Deployment: The script efficiently creates the table and applies schema updates incrementally, ensuring it can be safely run multiple times in a deployment pipeline.

Version Tracking: Integrated with the [Deploy] framework, its version is tracked in _EntityVersions, allowing the deployment process to manage upgrades.

The script demonstrates a well-structured approach to managing application configuration tables within a robust database deployment framework. The small redundancy in version updates might be a minor inefficiency but doesn't harm idempotency.

 

---