public void VerifyColumnType(CorrelationProperty correlationProperty)
{
var columnType = MySqlCorrelationPropertyTypeConverter.GetColumnType(correlationProperty.Type);
var name = correlationProperty.Name;
writer.Write($@"
set @column_type_{name} = (
select concat(column_type,' character set ', character_set_name)
from information_schema.columns
where
table_schema = database() and
table_name = @tableNameNonQuoted and
column_name = 'Correlation_{name}'
);
set @query = IF(
@column_type_{name} <> '{columnType}',
'call sqlpersistence_raiseerror(concat(\'Incorrect data type for Correlation_{name}. Expected {columnType} got \', @column_type_{name}, \'.\'));',
'select \'Column Type OK\' status');
prepare script from @query;
execute script;
deallocate prepare script;
");
}
public void Write()
{
var testDirectory = TestContext.CurrentContext.TestDirectory;
var path = Path.Combine(testDirectory, "../../../../../nservicebus/sql-persistence/correlationpropertytypes.include.md");
path = Path.GetFullPath(path);
File.Delete(path);
using (var writer = File.CreateText(path))
{
writer.WriteLine(@"
#### Microsoft SQL Server
| CorrelationPropertyType | Sql Type |
|--|--|");
foreach (var type in GetValues())
{
var columnType = MsSqlServerCorrelationPropertyTypeConverter.GetColumnType(type);
writer.WriteLine($"| `{type}` | `{columnType}` |");
}
writer.WriteLine(@"
#### MySQL
| CorrelationPropertyType | Sql Type |
|--|--|");
foreach (var type in GetValues().Where(x => x != CorrelationPropertyType.DateTimeOffset))
{
var columnType = MySqlCorrelationPropertyTypeConverter.GetColumnType(type);
writer.WriteLine($"| `{type}` | `{columnType}` |");
}
}
}
public void AddProperty(CorrelationProperty correlationProperty)
{
var columnType = MySqlCorrelationPropertyTypeConverter.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 = @tableNameNonQuoted;
set @query = IF(
@exist <= 0,
concat('alter table ', @tableNameQuoted, ' add column Correlation_{name} {columnType}'), 'select \'Column Exists\' status');
prepare script from @query;
execute script;
deallocate prepare script;
");
}
