A system to store property values as strings that are coerced and formatted according a data type. Supported data types are defined in the [services].[ValueType] table.
This is an alternative to using multiple typed columns or a single sql_variant type column in the database. All have their benefits. This is just another way handle user-defined typed data.
Partial Schema
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
CREATE SCHEMA [services] GO CREATE TABLE [services].[ValueType]( [Name] [nvarchar](25) NULL, [Default] [bit] NULL CONSTRAINT [DF_ValueType_Default] DEFAULT ((0)), [Entity] [bit] NULL CONSTRAINT [DF_ValueType_Entity] DEFAULT ((0)) ) ON [PRIMARY] GO INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Name', 0, 0) GO INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Text', 1, 0) GO INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Integer', 0, 0) GO INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'DateTime', 0, 0) GO INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Date', 0, 0) GO INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Time', 0, 0) GO INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Money', 0, 0) GO INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Decimal', 0, 0) GO INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'User', 0, 1) GO INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Customer', 0, 1) GO INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'Boolean', 0, 0) GO INSERT [services].[ValueType] ([Name], [Default], [Entity]) VALUES (N'List', 0, 0) GO CREATE TABLE [services].[Service]( [Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Capture_Id] DEFAULT (newsequentialid()), [Namespace] [nvarchar](100) NOT NULL, --... [ServicePropertiesAsText] [nvarchar](max) NULL, CONSTRAINT [PK_Service] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO CREATE TABLE [services].[ServiceProperty]( [Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ServiceProperty_Id] DEFAULT (newsequentialid()), [Namespace] [nvarchar](100) NOT NULL, [ServiceId] [uniqueidentifier] NOT NULL, [Name] [nvarchar](100) NOT NULL, [ValueTypeName] [nvarchar](25) NOT NULL, [ServicePropertyValuesAsText] [nvarchar](max) NULL, [Order] [int] NOT NULL CONSTRAINT [DF_ServiceProperty_Order] DEFAULT ((0)), CONSTRAINT [PK_ServiceProperty_Id] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [UIX_ServiceProperty_ServiceId_Name] UNIQUE NONCLUSTERED ( [ServiceId] ASC, [Name] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO CREATE TABLE [services].[ServicePropertyValue]( [Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ServicePropertyValue_Id] DEFAULT (newsequentialid()), [ServicePropertyId] [uniqueidentifier] NOT NULL, [Namespace] [nvarchar](100) NOT NULL, [Value] [nvarchar](4000) NULL, [EntityId] [uniqueidentifier] NULL, [Count] [int] NOT NULL CONSTRAINT [DF_ServicePropertyValue_Count] DEFAULT ((1)), [CoercionFault] [bit] NOT NULL CONSTRAINT [DF_ServicePropertyValue_CoercionFault] DEFAULT ((0)), CONSTRAINT [PK_ServicePropertyValue] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO |
Base SQL Functions
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 |
CREATE function [dbo].fLTrim(@value varchar(max)) returns varchar(max) as begin if(@value is not null) if (ascii(left(@value, 1)) < 33) set @value = stuff(@value, 1, patindex('%[^' + char(0) + '-' + char(32) + ']%', @value) - 1,''); return @value; end go CREATE function [dbo].fRTrim(@value varchar(max)) returns varchar(max) as begin if(@value is not null) set @value = reverse([dbo].fLTrim(reverse(@value))); return @value; end go CREATE function [dbo].fTrim(@value varchar(max)) returns varchar(max) as begin if(@value is not null) return [dbo].fLTrim([dbo].fRTrim(@value)); return @value; end go CREATE function [services].[fFormatNumericValue](@value nvarchar(max)) returns nvarchar(max) as begin declare @pattern nvarchar(20); set @pattern = '%[^0-9.]%'; declare @incorrectCharLoc smallint set @incorrectCharLoc = patindex(@pattern, @value) while @incorrectCharLoc > 0 begin set @value = stuff(@value, @incorrectCharLoc, 1, '') set @incorrectCharLoc = patindex(@pattern, @value) end return @value end go CREATE function [services].[fFormatDecimalValue](@value nvarchar(max)) returns nvarchar(max) as begin set @value = [services].[fFormatNumericValue](@value) if(isnumeric(@value) = 1) begin set @value = convert(nvarchar(max),convert(decimal(18,2),@value)); end return @value end go CREATE function [services].[fFormatIntegerValue](@value nvarchar(max)) returns nvarchar(max) as begin set @value = [services].[fFormatNumericValue](@value) if(isnumeric(@value) = 1) begin set @value = convert(nvarchar(max),convert(int,round(@value, 0))); end return @value end go CREATE function [services].[fFormatMoneyValue](@value nvarchar(max)) returns nvarchar(max) as begin set @value = [services].[fFormatNumericValue](@value) if(isnumeric(@value) = 1) begin set @value = convert(nvarchar(max),convert(money,@value)); end return @value end go CREATE function [services].[fFormatTemporalValue] ( @value as nvarchar(max) ,@valuetypename varchar(10) ) returns nvarchar(max) as begin declare @out nvarchar(max) if(@value is null) return null; set @out = @value; if(isDate(@value) = 1) begin set @valuetypename = [services].[fGetValueTypeNameFromName](@valuetypename,0) set @out = case when @valuetypename = 'DateTime' then replace(replace(convert(nvarchar(20), convert(datetime,@value), 100),'AM',' AM'),'PM',' PM') --Mon DD YYYY HH:MI:SS:MMMAM (or PM) when @valuetypename = 'Date' then convert(nvarchar(20), convert(date,@value), 107) --Mon DD, YYYY when @valuetypename = 'Time' then REPLACE(REPLACE(RIGHT(LTRIM(RIGHT(CONVERT(varchar,convert(time,@value),100),7)),7),'AM',' AM'),'PM',' PM') --hh:mm:ss end end return @out; end go CREATE function [dbo].[fFilterAsciiControlChars](@value varchar(max)) returns varchar(max) as begin --filter ascii chars 0-31, but not 9 (tab),10 (newline) & 13 (carriage return). if(@value is null) return @value; declare @newValue nvarchar(max) declare @ascii int; declare @pos int; set @newValue = ''; set @pos = 1; while (1 = 1) begin if(@pos <= len(@value)) begin set @ascii = ascii(substring(@value,@pos,1)); if(@ascii >= 0 and @ascii <=31 and @ascii <> 9 and @ascii <> 10 and @ascii <> 13) set @newValue= @newValue + char(@ascii) set @pos = @pos + 1 end else break; end return @newValue; end |
Core Coercion Objects
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 |
CREATE function [services].[tfCoerceValueType] ( @value nvarchar(max) ,@valueTypeName nvarchar(25) ) returns @tab table ( [Coerceable] bit ,[Value] nvarchar(max) ) as begin declare @coercedValue nvarchar(max); declare @coerceable bit; set @coerceable = 0; set @coercedValue = @value; set @valueTypeName = [services].[fGetValueTypeNameFromName](@valueTypeName,0) if([dbo].[fIsNullOrWhitespace](@valueTypeName) = 1) begin --any value for an unknown type is never coerceable insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue) return; end if(@value is null) begin --null for a known type is always coerceable set @coerceable = 1; insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue) return; end --trim and filter ascii chars 0-31, but not 9,10 & 13. set @value = [dbo].[fTrim]([dbo].[fFilterAsciiControlChars](@value)); --check text, customer, user, and list types if(@valueTypeName = 'Text' or @valueTypeName = 'Customer' or @valueTypeName = 'User' or @valueTypeName = 'List') begin set @coerceable = 1; insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue) return; end --check integer if(@valueTypeName = 'Integer') begin if(isnumeric(@value)=1) begin declare @int int; set @value = [services].[fFormatIntegerValue](@value) set @int = convert(int,@value) set @coercedValue = convert(nvarchar(250),@int) set @coerceable = 1; end insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue); return; end --check date if(@valueTypeName = 'Date') begin if(isdate(@value)=1) begin declare @d datetime; set @d = convert(datetime,@value) set @coercedValue = convert(nvarchar(25), @d, 23) --yyyy-mm-dd set @coerceable = 1; end insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue); return; end --check time if(@valueTypeName = 'Time') begin if(isdate(convert(nvarchar(25), getdate(), 23) + ' ' + @value)=1) --add an arbitrary yyyy-mm-dd date part to the time so IsDate() can be used. begin declare @t time; set @t = convert(time,@value) set @coercedValue = convert(nvarchar(25), @t, 108) --hh:mm:ss set @coerceable = 1; end insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue); return; end --check datetime if(@valueTypeName = 'DateTime') begin if(isdate(@value)=1) begin declare @dt datetime; set @dt = convert(datetime,@value) set @coercedValue = [dbo].[fFormatDateTimeAsISOStandard](@dt) --yyyy-mm-dd hh:mm:ss.mmm set @coerceable = 1; end insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue); return; end --check decimal if(@valueTypeName = 'Decimal') begin if(isnumeric(@value)=1) begin set @value = [services].[fFormatDecimalValue](@value) declare @dec decimal(18,4); set @dec = convert(decimal(18,4),@value) set @coercedValue = convert(nvarchar(250),@dec) set @coerceable = 1; end insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue); return; end --check money if(@valueTypeName = 'Money') begin if(isnumeric(@value)=1) begin set @value = [services].[fFormatMoneyValue](@value) declare @mon money; set @mon = convert(money,@value) set @coercedValue = convert(nvarchar(250),@mon) set @coerceable = 1; end insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue); return; end --check boolean if(@valueTypeName = 'Boolean') begin if(@value = 'Yes' or @value = 'True' or @value = '1' or @value = 'Y' or @value = 'T') begin set @coercedValue = 'Yes' set @coerceable = 1; end else if(@value = 'No' or @value = 'False' or @value = '0' or @value = 'N' or @value = 'F') begin set @coercedValue = 'No' set @coerceable = 1; end insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue); return; end --unhandled known type insert @tab ([Coerceable],[Value]) values (@coerceable, @coercedValue); return; end go CREATE function [services].[fCoerceValueType] ( @value nvarchar(max) ,@valueTypeName varchar(10) ) returns nvarchar(max) as begin declare @newValue nvarchar(max); select top 1 @newValue = [Value] from [services].[tfCoerceValueType](@value, @valueTypeName); return @newValue; end go CREATE procedure [services].[spCoerceValueType] ( @value nvarchar(max) ,@valueTypeName nvarchar(25) out ,@coerceable bit out ,@coercedValue nvarchar(max) out ) as begin select top 1 @coercedValue = [Value], @coerceable = [Coerceable] from [services].[tfCoerceValueType](@value, @valueTypeName); return; end go |
Stored procedures for updating concatenated lists of property values
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 |
CREATE procedure [services].[spRefreshServicePropertyValuesAsTextForServiceProperties](@namespace nvarchar(100)) as begin BEGIN TRY SET NOCOUNT ON SET XACT_ABORT ON update [services].[serviceproperty] set [ServicePropertyValuesAsText] = [result].[ServicePropertyValuesAsText] from ( select [ServicePropertyId] ,[ServicePropertyValuesAsText] = substring([ServicePropertyValuesAsText],0,len([ServicePropertyValuesAsText])) from ( select [ServicePropertyId] = sp.[id] ,[ServicePropertyValuesAsText] = substring( ( select isnull(spv.[value],'') + ',' from [services].[servicepropertyvalue] spv where sp.[id] = spv.[servicepropertyid] ORDER BY spv.[value] FOR XML PATH('') ), 1, 100000) from [services].[serviceproperty] sp where sp.[namespace] = @namespace ) as [concat] ) as [result] where [Id] = [result].[ServicePropertyId] END TRY BEGIN CATCH --IF @@trancount > 0 ROLLBACK TRANSACTION EXEC [spHandleError] RETURN -1 END CATCH end go CREATE function [services].[tfGetServicePropertyValuesAsTextForServiceProperties](@namespace nvarchar(100), @optionalServicePropertyId uniqueidentifier = null) returns @tab table ( [ServiceId] uniqueidentifier not null ,[ServicePropertyId] uniqueidentifier not null ,[ServicePropertyName] nvarchar(100) ,[ServicePropertyValuesAsText] nvarchar(max) ) as begin set @namespace = [dbo].[fFormatNamespace](@namespace); insert @tab select [ServiceId] ,[ServicePropertyId] ,[ServicePropertyName] ,[ServicePropertyValuesAsText] = substring([ServicePropertyValuesAsText],0,len([ServicePropertyValuesAsText])) from ( select [ServiceId] = sp.[serviceid] ,[ServicePropertyId] = sp.[id] ,[ServicePropertyName] = sp.[name] ,[ServicePropertyValuesAsText] = substring( ( select isnull(spv.[value],'') + ',' from [services].[servicepropertyvalue] spv where sp.[id] = spv.[servicepropertyid] ORDER BY spv.[value] FOR XML PATH('') ), 1, 100000) from [services].[serviceproperty] sp where sp.[namespace] = @namespace and (@optionalServicePropertyId is null or (@optionalServicePropertyId is not null and sp.id = @optionalServicePropertyId)) ) as [concat] return; end go CREATE procedure [services].[spRefreshServicePropertiesAsTextForServices](@namespace nvarchar(100)) as begin BEGIN TRY SET NOCOUNT ON SET XACT_ABORT ON declare @tab table ([ServiceId] uniqueidentifier, [Text] nvarchar(max)); update [services].[service] set [ServicePropertiesAsText] = result.[Text] from ( select [ServiceId] ,[Text] = [ServicePropertiesAsText] from [services].[tfGetServicePropertiesAsTextForServices](@namespace, null) ) as result where [Id] = result.[ServiceId] END TRY BEGIN CATCH --IF @@trancount > 0 ROLLBACK TRANSACTION EXEC [spHandleError] RETURN -1 END CATCH end go |
Query and Update Property Values Manually
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
--query select spv.[Id] ,sp.[Namespace] ,sp.[Name] ,sp.[ValueTypeName] ,spv.[Value] ,cvt.[Coerceable] ,[CoercedValue] = cvt.[Value] from [services].[ServicePropertyValue] spv join [services].[ServiceProperty] sp on sp.[Id] = spv.[ServicePropertyId] outer apply ( select * from [services].[tfCoerceValueType](spv.[Value], sp.[ValueTypeName]) ) as cvt --update update [services].[ServicePropertyValue] set [services].[ServicePropertyValue].[CoercionFault] = case when spv.[Coerceable] = 1 then 0 else 1 end ,[services].[ServicePropertyValue].[Value] = spv.[Value] from ( select [spvid] = spv.[Id] ,cvt.[Coerceable] ,cvt.[Value] from [services].[ServicePropertyValue] spv join [services].[ServiceProperty] sp on sp.[Id] = spv.[ServicePropertyId] cross apply ( select * from [services].[tfCoerceValueType](spv.[Value], sp.[ValueTypeName]) ) as cvt ) as spv where [Id] = spv.[spvid] |