public override string GetMutilationSql(INameDatabasesAndTablesDuringLoads namer)
{
return
(String.Format(
@"
ALTER TABLE {0} Add {1}_bit bit
GO
UPDATE {0} SET {1}_bit = CASE WHEN {1} is null THEN 0 else 1 end
GO
ALTER TABLE {0} DROP column {1}
GO
EXEC sp_rename '{0}.{1}_bit', '{1}' , 'COLUMN'
GO
", ColumnToDilute.TableInfo.GetRuntimeName(LoadStage.AdjustStaging, namer), ColumnToDilute.GetRuntimeName()));
}
public void Check(ICheckNotifier notifier)
{
if (ColumnToDilute == null)
{
notifier.OnCheckPerformed(new CheckEventArgs("ColumnToDilute is null", CheckResult.Fail, null));
return;
}
if (ColumnToDilute.Destination != DiscardedColumnDestination.Dilute)
{
notifier.OnCheckPerformed(new CheckEventArgs("ColumnToDilute '" + ColumnToDilute.GetRuntimeName() + "' is not marked as DiscardedColumnDestination.Dilute", CheckResult.Fail, null));
}
//Stamp out the type
IDilutionOperation instance = null;
try
{
var factory = new DilutionOperationFactory(ColumnToDilute);
instance = factory.Create(Operation);
}
catch (Exception e)
{
notifier.OnCheckPerformed(
new CheckEventArgs(
"Could not create DilutionOperation of Type " + Operation +
" using DilutionOperationFactory, see inner Exception for details", CheckResult.Fail, e));
}
if (_loadStage != LoadStage.AdjustStaging)
{
notifier.OnCheckPerformed(new CheckEventArgs("Dilution can ONLY occur in load stage AdjustStaging, it is currently configured as load stage: " + _loadStage, CheckResult.Fail));
}
if (instance != null)
{
instance.Check(notifier);
}
}
public override string GetMutilationSql(INameDatabasesAndTablesDuringLoads namer)
{
return
(string.Format(@"
IF OBJECT_ID('dbo.RemoveDodgyCharacters') IS NOT NULL
DROP FUNCTION RemoveDodgyCharacters
GO
Create Function [dbo].[RemoveDodgyCharacters](@Temp VarChar(max))
Returns VarChar(max)
AS
Begin
Declare @KeepValues as varchar(50)
Set @KeepValues = '%[^A-Za-z0-9]%'
While PatIndex(@KeepValues, @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')
Return @Temp
End
GO
IF OBJECT_ID('dbo.Left4OfPostcodes') IS NOT NULL
DROP FUNCTION Left4OfPostcodes
GO
CREATE FUNCTION Left4OfPostcodes
(
-- Add the parameters for the function here
@str varchar(max)
)
RETURNS varchar(4)
AS
BEGIN
--Pass through nulls
if @str IS NULL
RETURN @str
--Start by stripping out all dodgy characters (see method above)
DECLARE @hackedStr varchar(max)
set @hackedStr = dbo.RemoveDodgyCharacters(@str)
--If the result is less than 3 characters, return the original string
if LEN(@hackedStr) < 3
RETURN @str
--Part we are about to discard
DECLARE @discardedBit varchar(3)
SET @discardedBit = RIGHT(@hackedStr,3)
--http://www.mrs.org.uk/pdf/postcodeformat.pdf
--PO1 3AX
--Have we identified the 3AX bit correctly?
if PATINDEX('[0-9][A-Za-z][A-Za-z]' ,UPPER(@discardedBit)) = 1 --Yes
RETURN SUBSTRING(@hackedStr,1,LEN(@hackedStr)-3) --Return the hacked string (no dodgy characters) minus the validated suffix
--Suffix is missing or malformed but there is 5 or more characters so we aren't looking at 'DD3' we are looking at 'DD3 5L5' where the final digit should be a char but is an int by mistype
if LEN(@hackedStr) > 4 AND LEN(@hackedStr) < 8
RETURN SUBSTRING(@hackedStr,1,LEN(@hackedStr)-3)
RETURN @hackedStr --Else just return the hacked String (at least we did them the favour of removing dodgy characters and the varchar(4) means we probably fulfilled a contract reasonably anyway
END
GO
UPDATE {0} SET {1}=dbo.Left4OfPostcodes({1})
GO", ColumnToDilute.TableInfo.GetRuntimeName(LoadStage.AdjustStaging, namer), ColumnToDilute.GetRuntimeName()));
}
public override string GetMutilationSql(INameDatabasesAndTablesDuringLoads namer)
{
return(String.Format(@"IF OBJECT_ID('dbo.RoundDateToMiddleOfQuarter') IS NOT NULL
DROP FUNCTION RoundDateToMiddleOfQuarter
GO
CREATE FUNCTION RoundDateToMiddleOfQuarter
(
-- Add the parameters for the function here
@DOB date
)
RETURNS date
AS
BEGIN
-- Declare the return variable here
DECLARE @anonDOB date
-- Add the T-SQL statements to compute the return value here
IF MONTH(@DOB) IN (1,2,3)
SET @anonDOB = LEFT(@DOB, 4) + '0215'
ELSE IF MONTH(@DOB) IN (4,5,6)
SET @anonDOB = LEFT(@DOB, 4) + '0515'
ELSE IF MONTH(@DOB) IN (7,8,9)
SET @anonDOB = LEFT(@DOB, 4) + '0815'
ELSE IF MONTH(@DOB)IN (10,11,12)
SET @anonDOB = LEFT(@DOB, 4) + '1115'
ELSE SET @anonDOB = NULL
-- Return the result of the function
RETURN @anonDOB
END
GO
UPDATE {0} SET {1}=dbo.RoundDateToMiddleOfQuarter({1})
GO",
ColumnToDilute.TableInfo.GetRuntimeName(LoadStage.AdjustStaging, namer), ColumnToDilute.GetRuntimeName()));
}
