private List <IIndexDefinition> ReadIndexDefinitionsFromDatabase(string selectStatement, string tableName)
{
var indexDefinitions = new List <IIndexDefinition>();
IDataCursor cursor = null;
try
{
cursor = _dbContext.PowerPlant.CreateDataCursor();
var reader = cursor.ExecuteReader(selectStatement);
while (reader.Read())
{
var indexName = reader.GetString(0);
var columnList = reader.GetString(1);
var location = reader.GetString(2);
var isUnique = Convert.ToBoolean(reader.GetValue(3));
var dbName = reader.GetString(4);
var indexDefinition = _dbContext.PowerPlant.CreateIndexDefinition(indexName, tableName, location, isUnique);
indexDefinition.Columns = SplitColumns(columnList);
indexDefinition.DbSpecific = TableDefinition.ConvertStringToDbType(dbName);
indexDefinitions.Add(indexDefinition);
}
}
finally
{
cursor?.Close();
}
return(indexDefinitions);
}
protected override List <IIndexColumn> GetIndexColumnsForIndex(IIndexDefinition index)
{
string selectStmt = "";
selectStmt += "SELECT tc.name as col_name " + "\n";
selectStmt += "FROM sys.index_columns ic, " + "\n";
selectStmt += " sys.columns tc " + "\n";
selectStmt += "WHERE ic.column_id = tc.column_id " + "\n";
selectStmt += " AND ic.object_id = tc.object_id " + "\n";
selectStmt += " AND is_included_column = 0 " + "\n";
selectStmt += string.Format(" AND index_id = {0} ", index.IndexId) + "\n";
selectStmt += string.Format(" AND ic.object_id = Object_id('{0}') ", index.TableName) + "\n";
selectStmt += " AND is_included_column = 0 ";
List <IIndexColumn> columns = new List <IIndexColumn>();
IDataCursor cursor = DbContext.PowerPlant.CreateDataCursor();
try
{
IDataReader reader = cursor.ExecuteReader(selectStmt);
while (reader.Read())
{
string name = reader.GetString(0);
columns.Add(IndexColumnFactory.CreateInstance(name));
}
}
finally
{
cursor.Close();
}
return(columns);
}
private void CheckAllValues()
{
IDataCursor cursor = DbContext.PowerPlant.CreateDataCursor();
var columnTypeConverter = DbContext.PowerPlant.CreateColumnTypeConverter(ConversionFileForWrite);
ITableDefinition tableDefinition = DbSchema.GetTableDefinition(columnTypeConverter, TestTable);
try
{
IDataReader reader = cursor.ExecuteReader(string.Format("select * from {0}", TestTable));
reader.Read();
tableDefinition.Columns[0].ToString(reader["bool_col"]).Should().Be("1", "because bool was true");
var tmp = reader["bool_col"];
tableDefinition.Columns[1].ToString(reader["char_col"]).Should().Be("'NO'", "because that's the value for char_col");
tableDefinition.Columns[2].ToString(reader["date_col"]).Should().Be("19000223 00:00:00", "because that's the value for date_col");
tableDefinition.Columns[3].ToString(reader["float_col"]).Should().Be("123.12345678", "because that's the value for float_col");
tableDefinition.Columns[4].ToString(reader["guid_col"]).Should().Be(TestGuid, "because that's the value for guid_col");
tableDefinition.Columns[5].ToString(reader["int_col"]).Should().Be("1234567890", "because that's the value for int_col");
tableDefinition.Columns[6].ToString(reader["int8_col"]).Should().Be("150", "because that's the value for int8_col");
tableDefinition.Columns[7].ToString(reader["int16_col"]).Should().Be("12345", "because that's the value for int16_col");
tableDefinition.Columns[8].ToString(reader["int64_col"]).Should().Be("123456789012345", "because that's the value for int64_col");
tableDefinition.Columns[9].ToString(reader["longtext_col"]).Should().Be("'Very long text with æøå'", "because that's the value for longtext_col");
tableDefinition.Columns[10].ToString(reader["money_col"]).Should().Be("123.123", "because that's the value for money_col");
tableDefinition.Columns[12].ToString(reader["nvarchar_col"]).Should().Be("'A unicode ﺽ string'", "because that's the value for string_col");
tableDefinition.Columns[13].ToString(reader["varchar_col"]).Should().Be("'A varchar string'", "because that's the value for varchar_col");
string blob = Encoding.Default.GetString((byte[])reader["blob_col"]);
blob.Should().Be("A long blob");
}
finally
{
cursor.Close();
}
}
private List <IViewDefinition> ReadViewDefinitionsFromDatabase(string selectStatement)
{
List <IViewDefinition> viewDefinitions = new List <IViewDefinition>();
IDataCursor cursor = null;
try
{
cursor = _dbContext.PowerPlant.CreateDataCursor();
IDataReader reader = cursor.ExecuteReader(selectStatement);
while (reader.Read())
{
string database = reader.GetString(0);
string viewName = reader.GetString(1);
string select = reader.GetString(2);
viewDefinitions.Add(ViewDefinitionFactory.CreateInstance(ViewDefinition.ConvertFromStringToDbType(database), viewName, select));
}
}
finally
{
if (cursor != null)
{
cursor.Close();
}
}
return(viewDefinitions);
}
private long WriteTableDataToDataFile(ITableDefinition tableDefinition, string dataFileSuffix)
{
long rowCounter = 0;
using (DataFileTextWriter dataWriter = new DataFileTextWriter(Directory + tableDefinition.Name + "." + dataFileSuffix, UseCompression))
{
string selectStmt = CreateSelectStatement(tableDefinition);
IDataCursor cursor = _dbContext.PowerPlant.CreateDataCursor();
try
{
IDataReader reader = cursor.ExecuteReader(selectStmt, tableDefinition.HasBlobColumn);
while (reader.Read())
{
WriteRow(rowCounter, reader, tableDefinition, dataWriter);
dataWriter.WriteLine();
if (rowCounter % 10000 == 0)
{
_logger.Write($"...... reached row {rowCounter} for {tableDefinition.Name} ......");
}
rowCounter++;
}
}
finally
{
cursor.Close();
}
}
return(rowCounter);
}
public override void GetRawColumnDefinition(string tableName, string colName, out string type, out int length, out int prec, out int scale)
{
type = "";
length = prec = scale = 0;
var selectStmt = "";
selectStmt += "SELECT c.name, " + "\n";
selectStmt += " t.name AS t_name, " + "\n";
selectStmt += " isnull(c.max_length, 0) as length, " + "\n";
selectStmt += " isnull(c.precision, 0) as prec, " + "\n";
selectStmt += " isnull(c.scale, 0) as scale, " + "\n";
selectStmt += " c.is_nullable, " + "\n";
selectStmt += " convert(varchar(256), isnull(c.collation_name, '')) as collation, " + "\n";
selectStmt += " isnull(object_definition(c.default_object_id), '') as def, " + "\n";
selectStmt += " c.is_identity " + "\n";
selectStmt += "FROM sys.columns c " + "\n";
selectStmt += " JOIN sys.types t " + "\n";
selectStmt += " ON c.user_type_id = t.user_type_id " + "\n";
selectStmt += $"WHERE c.object_id = Object_id('{tableName}') " + "\n";
selectStmt += $" AND c.name = '{colName}' " + "\n";
selectStmt += "ORDER BY c.column_id ";
IDataCursor cursor = null;
try
{
cursor = DbContext.PowerPlant.CreateDataCursor();
var reader = cursor.ExecuteReader(selectStmt);
while (reader.Read())
{
var name = reader.GetString(0);
type = reader.GetString(1);
length = reader.GetInt16(2);
if (length != -1 && (type == "nvarchar" || type == "nchar"))
{
length /= 2;
}
prec = reader.GetByte(3);
scale = reader.GetByte(4);
var isNullable = reader.GetBoolean(5);
var collation = reader.GetString(6);
var def = reader.GetString(7);
var isIdentity = reader.GetBoolean(8);
var sourceType = type.AddParameters();
}
}
finally
{
cursor?.Close();
}
}
private void VerifyColumnType(string expectedType, int?expectedLength, int?expectedPrec, int?expectedScale)
{
var selectStmt = "";
selectStmt += "SELECT data_type, " + "\n";
selectStmt += " nvl(Decode(char_length, 0, data_length, " + "\n";
selectStmt += " char_length), 0) AS col3, " + "\n";
selectStmt += " nvl(data_precision, 0) as col4, " + "\n";
selectStmt += " nvl(data_scale, 0) as col5 " + "\n";
selectStmt += "FROM user_tab_columns " + "\n";
selectStmt += "WHERE table_name = '" + TableName.ToUpper() + "' \n";
selectStmt += "AND column_name = 'COL1'" + "\n";
IDataCursor cursor = DbContext.PowerPlant.CreateDataCursor();
string type = "";
int length = -99;
int prec = -99;
int scale = -99;
try
{
IDataReader reader = cursor.ExecuteReader(selectStmt);
reader.Read();
type = reader.GetString(0);
length = reader.GetInt16(1);
prec = reader.GetInt16(2);
scale = reader.GetInt32(3);
}
catch (Exception)
{
false.Should().BeTrue("because we want to fail when we cant read type from database.");
}
finally
{
cursor.Close();
}
type.Should().Be(expectedType);
if (expectedLength != null)
{
length.Should().Be(expectedLength, "because that's the expected length");
}
if (expectedPrec != null)
{
prec.Should().Be(expectedPrec, "because that's the expected precision");
}
if (expectedScale != null)
{
scale.Should().Be(expectedScale, "because that's the expected scale");
}
}
protected override List <IIndexDefinition> GetIndexesForTable(string tableName)
{
string selectStmt = "";
selectStmt += "SELECT DISTINCT i.name, " + "\n";
selectStmt += " s.name loc_name, " + "\n";
selectStmt += " i.index_id indid, " + "\n";
selectStmt += " i.is_unique unique_flag, " + "\n";
selectStmt += " i.type_desc " + "\n";
selectStmt += "FROM sys.indexes i, " + "\n";
selectStmt += " sys.filegroups s " + "\n";
selectStmt += "WHERE i.index_id BETWEEN 1 AND 254 " + "\n";
selectStmt += " AND i.data_space_id = s.data_space_id " + "\n";
selectStmt += " AND i.is_primary_key = 0 " + "\n";
selectStmt += " AND i.is_unique_constraint = 0 " + "\n";
selectStmt += " AND i.is_hypothetical = 0 " + "\n";
selectStmt += string.Format(" AND i.object_id = Object_id('{0}') ", tableName) + "\n";
selectStmt += "ORDER BY i.name ";
List <IIndexDefinition> indexes = new List <IIndexDefinition>();
IDataCursor cursor = DbContext.PowerPlant.CreateDataCursor();
try
{
IDataReader reader = cursor.ExecuteReader(selectStmt);
while (reader.Read())
{
string name = reader.GetString(0);
string location = reader.GetString(1);
int id = reader.GetInt32(2);
bool isUnique = reader.GetBoolean(3);
bool isClustered = reader.GetString(4) == "CLUSTERED";
indexes.Add(DbContext.PowerPlant.CreateIndexDefinition(name, tableName, location, isUnique, id, isClustered));
}
}
finally
{
cursor.Close();
}
return(indexes);
}
private void VerifyColumnType(string expectedType, int?expectedLength, int?expectedPrec, int?expectedScale)
{
var selectStmt = "";
selectStmt += "SELECT c.name, " + "\n";
selectStmt += " t.name AS t_name, " + "\n";
selectStmt += " isnull(c.max_length, 0) as length, " + "\n";
selectStmt += " isnull(c.precision, 0) as prec, " + "\n";
selectStmt += " isnull(c.scale, 0) as scale, " + "\n";
selectStmt += " c.is_nullable, " + "\n";
selectStmt += " convert(varchar(256), isnull(c.collation_name, '')) as collation, " + "\n";
selectStmt += " isnull(object_definition(c.default_object_id), '') as def, " + "\n";
selectStmt += " c.is_identity " + "\n";
selectStmt += "FROM sys.columns c " + "\n";
selectStmt += " JOIN sys.types t " + "\n";
selectStmt += " ON c.user_type_id = t.user_type_id " + "\n";
selectStmt += $"WHERE c.object_id = Object_id('{TableName}') " + "\n";
selectStmt += "ORDER BY c.column_id ";
IDataCursor cursor = DbContext.PowerPlant.CreateDataCursor();
string type = "";
int length = -99;
int prec = -99;
int scale = -99;
try
{
IDataReader reader = cursor.ExecuteReader(selectStmt);
reader.Read();
type = reader.GetString(1).ToLower();
length = reader.GetInt16(2);
if (length != -1 && (type == "nvarchar" || type == "nchar"))
{
length /= 2;
}
prec = reader.GetByte(3);
scale = reader.GetByte(4);
if (reader.GetBoolean(8))
{
type = "identity";
}
}
catch (Exception)
{
false.Should().BeTrue("because we want to fail when we cant read type from database.");
}
finally
{
cursor.Close();
}
type.Should().Be(expectedType);
if (expectedLength != null)
{
length.Should().Be(expectedLength, "because that's the expected length");
}
if (expectedPrec != null)
{
prec.Should().Be(expectedPrec, "because that's the expected precision");
}
if (expectedScale != null)
{
scale.Should().Be(expectedScale, "because that's the expected scale");
}
}
public override ITableDefinition GetTableDefinition(IColumnTypeConverter columnTypeConverter, string tableName)
{
string selectStmt = "";
selectStmt += "SELECT c.name, " + "\n";
selectStmt += " t.name AS t_name, " + "\n";
selectStmt += " isnull(c.max_length, 0) as length, " + "\n";
selectStmt += " isnull(c.precision, 0) as prec, " + "\n";
selectStmt += " isnull(c.scale, 0) as scale, " + "\n";
selectStmt += " c.is_nullable, " + "\n";
selectStmt += " convert(varchar(256), isnull(c.collation_name, '')) as collation, " + "\n";
selectStmt += " isnull(object_definition(c.default_object_id), '') as def, " + "\n";
selectStmt += " c.is_identity " + "\n";
selectStmt += "FROM sys.columns c " + "\n";
selectStmt += " JOIN sys.types t " + "\n";
selectStmt += " ON c.user_type_id = t.user_type_id " + "\n";
selectStmt += string.Format("WHERE c.object_id = Object_id('{0}') ", tableName) + "\n";
selectStmt += "ORDER BY c.column_id ";
IColumnFactory columnFactory = DbContext.PowerPlant.CreateColumnFactory();
List <IColumn> columns = new List <IColumn>();
bool tableHasBlobColumn = false;
IDataCursor cursor = null;
try
{
cursor = DbContext.PowerPlant.CreateDataCursor();
IDataReader reader = cursor.ExecuteReader(selectStmt);
while (reader.Read())
{
string name = reader.GetString(0);
string type = reader.GetString(1);
int length = reader.GetInt16(2);
if (length != -1 && (type == "nvarchar" || type == "nchar"))
{
length /= 2;
}
int prec = reader.GetByte(3);
int scale = reader.GetByte(4);
if (scale != 0 && type == "datetime2")
{
prec = 0;
}
bool isNullable = reader.GetBoolean(5);
string collation = reader.GetString(6);
string def = reader.GetString(7);
bool isIdentity = reader.GetBoolean(8);
string sourceType = type.AddParameters();
ColumnTypeName colType = columnTypeConverter.GetDestinationType(sourceType, ref length, ref prec, ref scale).ColumnTypeName(length);
if (colType == ColumnTypeName.Blob || colType == ColumnTypeName.OldBlob)
{
tableHasBlobColumn = true;
}
columns.Add(
columnFactory.CreateInstance(
colType,
name,
length,
prec,
scale,
isNullable,
isIdentity,
def,
collation)
);
}
}
catch (Exception ex)
{
throw new ADatabaseException("ERROR when Get TableDefinition: " + selectStmt, ex);
}
finally
{
if (cursor != null)
{
cursor.Close();
}
}
ITableDefinition tableDefinition = DbContext.PowerPlant.CreateTableDefinition(tableName, columns, GetSegmentName(tableName));
tableDefinition.HasBlobColumn = tableHasBlobColumn;
tableDefinition.Columns.RemoveAll(c => c.Name == "agrtid");
return(tableDefinition);
}
