public void VerifyColumnType(CorrelationProperty correlationProperty)
{
var columnType = GetColumnType(correlationProperty.Type);
var name = correlationProperty.Name;
writer.Write($@"
declare @dataType_{name} nvarchar(max);
set @dataType_{name} = (
select data_type
from information_schema.columns
where
table_name = ' + @tableName + ' and
column_name = 'Correlation_{name}'
);
if (@dataType_{name} <> '{columnType}')
throw 50000, 'Incorrect data type for Correlation_{name}', 0
");
}
public void WriteCreateIndex(CorrelationProperty correlationProperty)
{
var name = correlationProperty.Name;
writer.Write($@"
select count(*)
into @exist
from information_schema.statistics
where
table_schema = database() and
index_name = 'Index_Correlation_{name}' and
table_name = @tableName;
set @query = IF(
@exist <= 0,
concat('create unique index Index_Correlation_{name} on ', @tableName, '(Correlation_{name})'), 'select \'Index Exists\' status');
prepare statment from @query;
execute statment;
deallocate prepare statment;
");
}
public void AddProperty(CorrelationProperty correlationProperty)
{
var columnType = GetColumnType(correlationProperty.Type);
var name = correlationProperty.Name;
writer.Write($@"
select count(*)
into @exist
from information_schema.columns
where table_schema = database() and
column_name = 'Correlation_{name}' and
table_name = @tableName;
set @query = IF(
@exist <= 0,
concat('alter table ', @tableName, ' add column Correlation_{name} {columnType}'), 'select \'Column Exists\' status');
prepare statment from @query;
execute statment;
deallocate prepare statment;
");
}
public void AddProperty(CorrelationProperty correlationProperty)
{
var columnType = GetColumnType(correlationProperty.Type);
var name = correlationProperty.Name;
writer.Write($@"
if not exists
(
select * from sys.columns
where
name = 'Correlation_{name}' and
object_id = object_id(@tableName)
)
begin
declare @createColumn_{name} nvarchar(max);
set @createColumn_{name} = '
alter table ' + @tableName + '
add Correlation_{name} {columnType};';
exec(@createColumn_{name});
end
");
}
public void WriteCreateIndex(CorrelationProperty correlationProperty)
{
var name = correlationProperty.Name;
writer.Write($@"
if not exists
(
select *
from sys.indexes
where
name = 'Index_Correlation_{name}' and
object_id = object_id(@tableName)
)
begin
declare @createIndex_{name} nvarchar(max);
set @createIndex_{name} = '
create unique index Index_Correlation_{name}
on ' + @tableName + '(Correlation_{name})
where Correlation_{name} is not null;';
exec(@createIndex_{name});
end
");
}
public void VerifyColumnType(CorrelationProperty correlationProperty)
{
var columnType = GetColumnType(correlationProperty.Type);
var name = correlationProperty.Name;
writer.Write($@"
set @column_type_{name} = (
select column_type
from information_schema.columns
where
table_schema = database() and
table_name = @tableName and
column_name = 'Correlation_{name}'
);
set @query = IF(
@column_type_{name} <> '{columnType}',
'SIGNAL SQLSTATE \'45000\' SET MESSAGE_TEXT = \'Incorrect data type for Correlation_{name}\'',
'select \'Column Type OK\' status');
prepare statment from @query;
execute statment;
deallocate prepare statment;
");
}