/// <summary>
/// Gather a table definition from an existing table in the database.
/// </summary>
/// <param name="connectionManager">The connection manager of the database you want to connect</param>
/// <param name="tableName">A name of an existing table in the database</param>
/// <returns></returns>
public static TableDefinition FromTableName(IConnectionManager connection, string tableName)
{
IfTableOrViewExistsTask.ThrowExceptionIfNotExists(connection, tableName);
ConnectionManagerType connectionType = connection.ConnectionManagerType;
ObjectNameDescriptor TN = new ObjectNameDescriptor(tableName, connection.QB, connection.QE);
if (connectionType == ConnectionManagerType.SqlServer)
{
return(ReadTableDefinitionFromSqlServer(connection, TN));
}
else if (connectionType == ConnectionManagerType.SQLite)
{
return(ReadTableDefinitionFromSQLite(connection, TN));
}
else if (connectionType == ConnectionManagerType.MySql)
{
return(ReadTableDefinitionFromMySqlServer(connection, TN));
}
else if (connectionType == ConnectionManagerType.Postgres)
{
return(ReadTableDefinitionFromPostgres(connection, TN));
}
else if (connectionType == ConnectionManagerType.Access)
{
return(ReadTableDefinitionFromAccess(connection, TN));
}
else if (connectionType == ConnectionManagerType.Oracle)
{
return(ReadTableDefinitionFromOracle(connection, TN));
}
else
{
throw new ETLBoxException("Unknown connection type - please pass a valid TableDefinition!");
}
}
private static void CreateSourceTable(IConnectionManager connection, string tableName)
{
DropTableTask.DropIfExists(connection, tableName);
var TableDefinition = new TableDefinition(tableName
, new List <TableColumn>()
{
new TableColumn("Col1", "VARCHAR(100)", allowNulls: true),
new TableColumn("Col2", "VARCHAR(100)", allowNulls: true),
new TableColumn("Col3", "VARCHAR(100)", allowNulls: true)
});
TableDefinition.CreateTable(connection);
ObjectNameDescriptor TN = new ObjectNameDescriptor(tableName, connection.QB, connection.QE);
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES('1','Test1','1')");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES('1.35','TestX','X')");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES('2','Test2', NULL)");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES('X',NULL, NULL)");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES('3','Test3', '3')");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES('4','Test4', 'X')");
}
public override void BulkInsert(ITableData data, string tableName)
{
var TN = new ObjectNameDescriptor(tableName, QB, QE);
var sourceColumnNames = data.ColumnMapping.Cast <IColumnMapping>().Select(cm => cm.SourceColumn).ToList();
var destColumnNames = data.ColumnMapping.Cast <IColumnMapping>().Select(cm => cm.DataSetColumn).ToList();
var quotedDestColumns = destColumnNames.Select(col => TN.QB + col + TN.QE);
using (var writer = DbConnection.BeginBinaryImport($@"
COPY {TN.QuotatedFullName} ({string.Join(", ", quotedDestColumns)})
FROM STDIN (FORMAT BINARY)"))
{
while (data.Read())
{
writer.StartRow();
foreach (var destCol in destColumnNames)
{
TableColumn colDef = DestinationColumns[destCol];
int ordinal = data.GetOrdinal(destCol);
object val = data.GetValue(ordinal);
if (val != null)
{
object convertedVal = System.Convert.ChangeType(data.GetValue(ordinal), colDef.NETDataType);
writer.Write(convertedVal, colDef.InternalDataType.ToLower());
}
else
{
writer.WriteNull();
}
}
}
writer.Complete();
}
}
protected override void DoBulkInsert(ITableData data)
{
data.Definition.EnsureColumns(this, true);
var TN = new ObjectNameDescriptor(data.Definition.Name, ConnectionManagerType.Postgres);
var quotedDestColumns = data.Definition.ColumnsSynchronized.Names().Select(col => TN.QB + col + TN.QE);
using (var writer = DbConnection.BeginBinaryImport($@"
COPY {TN.QuotatedFullName} ({string.Join(", ", quotedDestColumns)})
FROM STDIN (FORMAT BINARY)"))
{
foreach (var row in data.EnumerateRowsAndConvertValuesToNETDataType())
{
writer.StartRow();
foreach (var column in row)
{
if (column.value != null)
{
writer.Write(column.value, column.column.InternalDataType.ToLower());
}
else
{
writer.WriteNull();
}
}
}
writer.Complete();
}
}
void InsertSourceData(IConnectionManager connection, ObjectNameDescriptor TN)
{
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(1,'I','Insert', 'Test1')");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(1,'U','Update', 'Test2')");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(1,'E','NoChange', 'Test3')");
}
void InsertDestinationData(IConnectionManager connection, ObjectNameDescriptor TN)
{
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(1,'U','Update', 'XXX')");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(1,'E','NoChange', 'Test3')");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(1,'D','Delete', 'Test4')");
}
public void MySqlQuotatedTableName()
{
ObjectNameDescriptor desc = new ObjectNameDescriptor("`Test`", ConnectionManagerType.MySql);
Assert.Equal(@"", desc.QuotatedSchemaName);
Assert.Equal(@"`Test`", desc.QuotatedObjectName);
Assert.Equal(@"`Test`", desc.QuotatedFullName);
Assert.Equal(@"Test", desc.UnquotatedObjectName);
Assert.Equal(@"Test", desc.UnquotatedFullName);
}
public void PostgresWithSchemaAndQuotation()
{
var desc = new ObjectNameDescriptor(@"""public"".""Test""", @"""", @"""");
Assert.Equal(@"""public""", desc.QuotatedSchemaName);
Assert.Equal(@"""Test""", desc.QuotatedObjectName);
Assert.Equal(@"""public"".""Test""", desc.QuotatedFullName);
Assert.Equal(@"Test", desc.UnquotatedObjectName);
Assert.Equal(@"public.Test", desc.UnquotatedFullName);
}
public void MySqlQuotatedTableNameAndDots()
{
var desc = new ObjectNameDescriptor("`Test.Test`", "`", "`");
Assert.Equal(@"", desc.QuotatedSchemaName);
Assert.Equal(@"`Test.Test`", desc.QuotatedObjectName);
Assert.Equal(@"`Test.Test`", desc.QuotatedFullName);
Assert.Equal(@"Test.Test", desc.UnquotatedObjectName);
Assert.Equal(@"Test.Test", desc.UnquotatedFullName);
}
public void SqlServerNoSchemaWithQuotation()
{
ObjectNameDescriptor desc = new ObjectNameDescriptor("[Test]", ConnectionManagerType.SqlServer);
Assert.Equal("", desc.QuotatedSchemaName);
Assert.Equal("[Test]", desc.QuotatedObjectName);
Assert.Equal("[Test]", desc.QuotatedFullName);
Assert.Equal("Test", desc.UnquotatedObjectName);
Assert.Equal("Test", desc.UnquotatedFullName);
}
public void PostgresWithSchemaAndQuotation()
{
ObjectNameDescriptor desc = new ObjectNameDescriptor(@"""public"".""Test""", ConnectionManagerType.Postgres);
Assert.Equal(@"""public""", desc.QuotatedSchemaName);
Assert.Equal(@"""Test""", desc.QuotatedObjectName);
Assert.Equal(@"""public"".""Test""", desc.QuotatedFullName);
Assert.Equal(@"Test", desc.UnquotatedObjectName);
Assert.Equal(@"public.Test", desc.UnquotatedFullName);
}
public void SqlServerNoSchemaWithQuotation()
{
var desc = new ObjectNameDescriptor("[Test]", "[", "]");
Assert.Equal("", desc.QuotatedSchemaName);
Assert.Equal("[Test]", desc.QuotatedObjectName);
Assert.Equal("[Test]", desc.QuotatedFullName);
Assert.Equal("Test", desc.UnquotatedObjectName);
Assert.Equal("Test", desc.UnquotatedFullName);
}
public void SqlServerWithSchema()
{
var desc = new ObjectNameDescriptor("dbo.Test", "[", "]");
Assert.Equal("[dbo]", desc.QuotatedSchemaName);
Assert.Equal("[Test]", desc.QuotatedObjectName);
Assert.Equal("[dbo].[Test]", desc.QuotatedFullName);
Assert.Equal("dbo", desc.UnquotatedSchemaName);
Assert.Equal("Test", desc.UnquotatedObjectName);
Assert.Equal("dbo.Test", desc.UnquotatedFullName);
}
public void SqlServerSchemaWithDot()
{
var desc = new ObjectNameDescriptor("[Foo.Bar].[Test]", "[", "]");
Assert.Equal("[Foo.Bar]", desc.QuotatedSchemaName);
Assert.Equal("[Test]", desc.QuotatedObjectName);
Assert.Equal("[Foo.Bar].[Test]", desc.QuotatedFullName);
Assert.Equal("Foo.Bar", desc.UnquotatedSchemaName);
Assert.Equal("Test", desc.UnquotatedObjectName);
Assert.Equal("Foo.Bar.Test", desc.UnquotatedFullName);
}
void InsertTestData(IConnectionManager connection, string tableName)
{
ObjectNameDescriptor TN = new ObjectNameDescriptor(tableName, connection);
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(1, 10 ,'Test1')");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(2, 10 ,'Test2')");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(3, 10 ,'Test3')");
}
public void SqlServerSchemaWithDot()
{
ObjectNameDescriptor desc = new ObjectNameDescriptor("[Foo.Bar].[Test]", ConnectionManagerType.SqlServer);
Assert.Equal("[Foo.Bar]", desc.QuotatedSchemaName);
Assert.Equal("[Test]", desc.QuotatedObjectName);
Assert.Equal("[Foo.Bar].[Test]", desc.QuotatedFullName);
Assert.Equal("Foo.Bar", desc.UnquotatedSchemaName);
Assert.Equal("Test", desc.UnquotatedObjectName);
Assert.Equal("Foo.Bar.Test", desc.UnquotatedFullName);
}
void AddFKConstraint(IConnectionManager connection, string sourceTableName, string referenceTableName)
{
ObjectNameDescriptor TN = new ObjectNameDescriptor(sourceTableName, connection);
ObjectNameDescriptor TNR = new ObjectNameDescriptor(referenceTableName, connection);
SqlTask.ExecuteNonQuery(connection, "Add FK constraint",
$@"ALTER TABLE {TN.QuotatedFullName}
ADD CONSTRAINT constraint_fk
FOREIGN KEY ({TN.QB}Key2{TN.QE})
REFERENCES {TNR.QuotatedFullName}({TNR.QB}Id{TNR.QE})
ON DELETE CASCADE;");
}
private void InsertTestData(IConnectionManager connection, string tableName)
{
var TN = new ObjectNameDescriptor(tableName, connection.QB, connection.QE);
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(1,'\0 \"" \b \n \r \t \Z \\ \% \_ ')");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(2,' '' """" ')");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(3,' !""�$%&/())='' ')");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(4,NULL)");
}
void ReCreateTable(IConnectionManager connection, ObjectNameDescriptor TN)
{
DropTableTask.DropIfExists(connection, TN.ObjectName);
CreateTableTask.Create(connection, TN.ObjectName,
new List <TableColumn>()
{
new TableColumn("ColKey1", "INT", allowNulls: false, isPrimaryKey: true),
new TableColumn("ColKey2", "CHAR(1)", allowNulls: false, isPrimaryKey: true),
new TableColumn("ColValue1", "NVARCHAR(100)", allowNulls: true, isPrimaryKey: false),
new TableColumn("ColValue2", "NVARCHAR(100)", allowNulls: true, isPrimaryKey: false),
});
}
void ReCreateOtherTable(IConnectionManager connection, string tablename)
{
DropTableTask.DropIfExists(connection, tablename);
CreateTableTask.Create(connection, tablename,
new List <TableColumn>()
{
new TableColumn("Id", "INT", allowNulls: false, isPrimaryKey: true),
new TableColumn("Other", "VARCHAR(100)", allowNulls: true, isPrimaryKey: false),
});
ObjectNameDescriptor TN = new ObjectNameDescriptor(tablename, connection);
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(10,'TestX')");
}
internal void ReadTableDefinition(ObjectNameDescriptor TN, List <TableColumn> columns)
{
DataTable schemaTable = GetSchemaDataTable(TN.UnquotatedFullName, "Columns");
foreach (var row in schemaTable.Rows)
{
DataRow dr = row as DataRow;
TableColumn col = new TableColumn()
{
Name = dr[schemaTable.Columns["COLUMN_NAME"]].ToString(),
DataType = dr[schemaTable.Columns["TYPE_NAME"]].ToString(),
AllowNulls = dr[schemaTable.Columns["IS_NULLABLE"]].ToString() == "YES" ? true : false
};
columns.Add(col);
}
}
private static void AssertFirstRow(IConnectionManager conn, string tableName)
{
// IntCol LongCol DecimalCol DoubleCol DateTimeCol DateCol StringCol CharCol DecimalStringCol NullCol
//1 - 1 2.3 5.4 2010 - 01 - 01 10:10:10.100 2020 - 01 - 01 Test T 13.4566000000 NULL
var TN = new ObjectNameDescriptor(tableName, conn.QB, conn.QE);
SqlTask.ExecuteReaderSingleLine(conn, "Check data",
$"SELECT * FROM {TN.QuotatedFullName} WHERE {conn.QB}intcol{conn.QE} = 1",
col => Assert.True(Convert.ToInt32(col) == 1),
col => Assert.True(Convert.ToInt64(col) == -1),
col => Assert.True(Convert.ToDecimal(col) == 2.3M),
col => Assert.True(Convert.ToDecimal(col) == 5.4M),
col => Assert.True(Convert.ToDateTime(col) == new DateTime(2010, 1, 1, 10, 10, 10)),
col => Assert.True(Convert.ToDateTime(col) == new DateTime(2020, 1, 1)),
col => Assert.True(Convert.ToString(col) == "Test"),
col => Assert.True(Convert.ToString(col) == "T" || Convert.ToString(col) == "84"),
col => Assert.True(Convert.ToString(col).Replace("0", "") == "13.4566"),
col => Assert.True(col == null),
col => Assert.True(Convert.ToInt32(col) == 2)
);
}
public void MergeWithCompositeKey(IConnectionManager connection)
{
//Arrange
ObjectNameDescriptor TNS = new ObjectNameDescriptor("DBMergeSource", connection.QB, connection.QE);
ObjectNameDescriptor TND = new ObjectNameDescriptor("DBMergeDestination", connection.QB, connection.QE);
ReCreateTable(connection, TNS);
ReCreateTable(connection, TND);
InsertSourceData(connection, TNS);
InsertDestinationData(connection, TND);
//Act
DbSource <MyMergeRow> source = new DbSource <MyMergeRow>(connection, "DBMergeSource");
DbMerge <MyMergeRow> dest = new DbMerge <MyMergeRow>(connection, "DBMergeDestination");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(3, RowCountTask.Count(connection, "DBMergeDestination"));
Assert.Equal(1, RowCountTask.Count(connection, "DBMergeDestination", $"{TND.QB}ColKey2{TND.QE} = 'E' and {TND.QB}ColValue2{TND.QE} = 'Test3'"));
Assert.Equal(1, RowCountTask.Count(connection, "DBMergeDestination", $"{TND.QB}ColKey2{TND.QE} = 'U' and {TND.QB}ColValue2{TND.QE} = 'Test2'"));
Assert.Equal(1, RowCountTask.Count(connection, "DBMergeDestination", $"{TND.QB}ColKey2{TND.QE} = 'I' and {TND.QB}ColValue2{TND.QE} = 'Test1'"));
}
private static TableDefinition ReadTableDefinitionFromSQLite(IConnectionManager connection, ObjectNameDescriptor TN)
{
TableDefinition result = new TableDefinition(TN.ObjectName);
TableColumn curCol = null;
var readMetaSql = new SqlTask(
$@"PRAGMA table_info(""{TN.UnquotatedFullName}"")"
, () => { curCol = new TableColumn(); }
, () => { result.Columns.Add(curCol); }
, cid => {; }
, name => curCol.Name = name.ToString()
, type => curCol.DataType = type.ToString()
, notnull => curCol.AllowNulls = (long)notnull == 1 ? true : false
, dftl_value => curCol.DefaultValue = dftl_value?.ToString()
, pk => curCol.IsPrimaryKey = (long)pk >= 1 ? true : false
)
{
DisableLogging = true,
ConnectionManager = connection,
TaskName = $"Read column meta data for table {TN.ObjectName}"
};
readMetaSql.ExecuteReader();
return(result);
}
private static TableDefinition ReadTableDefinitionFromMySqlServer(IConnectionManager connection, ObjectNameDescriptor TN)
{
TableDefinition result = new TableDefinition(TN.ObjectName);
TableColumn curCol = null;
var readMetaSql = new SqlTask(
$@"
SELECT DISTINCT cols.column_name
, CASE WHEN cols.data_type IN ('varchar','char') THEN CONCAT (cols.data_type,'(',cols.character_maximum_length, ')')
WHEN cols.data_type IN ('decimal') THEN CONCAT (cols.data_type,'(',cols.numeric_precision,',', cols.numeric_scale, ')')
ELSE cols.data_type
END AS 'data_type'
, CASE WHEN cols.is_nullable = 'NO' THEN 0 ELSE 1 END AS 'is_nullable'
, CASE WHEN cols.extra IS NOT NULL AND cols.extra = 'auto_increment' THEN 1 ELSE 0 END AS 'auto_increment'
, CASE WHEN isnull(k.constraint_name) THEN 0 ELSE 1 END AS 'primary_key'
, cols.column_default
, cols.collation_name
, cols.generation_expression
, cols.column_comment
, CASE WHEN tc_uq.CONSTRAINT_TYPE = 'UNIQUE' THEN 1 ELSE 0 END AS 'is_unique'
, tc.CONSTRAINT_NAME AS 'pk_name'
, tc_uq.CONSTRAINT_NAME AS 'uq_constr_name'
, cols.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS cols
INNER JOIN INFORMATION_SCHEMA.TABLES tbl
ON cols.table_name = tbl.table_name
AND cols.table_schema = tbl.table_schema
AND cols.table_catalog = tbl.table_catalog
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
ON cols.table_name = k.table_name
AND cols.table_schema = k.table_schema
AND cols.table_catalog = k.table_catalog
AND cols.column_name = k.column_name
AND k.constraint_name = 'PRIMARY'
LEFT JOIN information_schema.TABLE_CONSTRAINTS tc
ON k.TABLE_SCHEMA = tc.TABLE_SCHEMA
AND k.TABLE_NAME = tc.TABLE_NAME
AND k.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE uq
ON cols.table_name = uq.table_name
AND cols.table_schema = uq.table_schema
AND cols.table_catalog = uq.table_catalog
AND cols.column_name = uq.column_name
LEFT JOIN information_schema.TABLE_CONSTRAINTS tc_uq
ON uq.TABLE_SCHEMA = tc_uq.TABLE_SCHEMA
AND uq.TABLE_NAME = tc_uq.TABLE_NAME
AND uq.CONSTRAINT_NAME = tc_uq.CONSTRAINT_NAME
AND tc_uq.CONSTRAINT_TYPE = 'UNIQUE'
WHERE ( cols.table_name = '{TN.UnquotatedFullName}' OR CONCAT(cols.table_catalog,'.',cols.table_name) = '{TN.UnquotatedFullName}')
AND cols.table_schema = DATABASE()
ORDER BY cols.ordinal_position
"
, () => { curCol = new TableColumn(); }
, () => { result.Columns.Add(curCol); }
, column_name => curCol.Name = column_name.ToString()
, data_type => curCol.DataType = data_type.ToString()
, is_nullable => curCol.AllowNulls = (int)is_nullable == 1 ? true : false
, auto_increment => curCol.IsIdentity = (int)auto_increment == 1 ? true : false
, primary_key => curCol.IsPrimaryKey = (int)primary_key == 1 ? true : false
, column_default => curCol.DefaultValue = column_default?.ToString()
, collation_name => curCol.Collation = collation_name?.ToString()
, generation_expression => curCol.ComputedColumn = generation_expression?.ToString()
, comment => curCol.Comment = comment?.ToString()
, uq_key => curCol.IsUnique = (int)uq_key == 1 ? true : false
, pk_name => result.PrimaryKeyConstraintName = String.IsNullOrWhiteSpace(pk_name?.ToString()) ? result.PrimaryKeyConstraintName : pk_name.ToString()
, uq_name => result.UniqueKeyConstraintName = String.IsNullOrWhiteSpace(uq_name?.ToString()) ? result.UniqueKeyConstraintName : uq_name.ToString()
, ignore => { }
)
{
DisableLogging = true,
ConnectionManager = connection,
TaskName = $"Read column meta data for table {TN.ObjectName}"
};
readMetaSql.ExecuteReader();
return(result);
}
private static TableDefinition ReadTableDefinitionFromPostgres(IConnectionManager connection, ObjectNameDescriptor TN)
{
TableDefinition result = new TableDefinition(TN.ObjectName);
TableColumn curCol = null;
var readMetaSql = new SqlTask(
$@"
SELECT cols.column_name
,CASE
WHEN LEFT(cols.data_type,4) = 'time' THEN REPLACE(REPLACE(REPLACE(cols.data_type,'without time zone',''), 'with time zone', 'tz'),' ','')
ELSE cols.data_type
END AS ""internaldatatype""
,CASE
WHEN cols.domain_name IS NOT NULL THEN domain_name
WHEN cols.data_type='character varying' THEN
CASE WHEN character_maximum_length IS NULL
THEN 'varchar'
ELSE 'varchar('||character_maximum_length||')'
END
WHEN cols.data_type='character' THEN 'char('||character_maximum_length||')'
WHEN cols.data_type='numeric' THEN
CASE WHEN numeric_precision IS NULL
THEN 'numeric'
ELSE 'numeric('||numeric_precision||','||numeric_scale||')'
END
WHEN LEFT(cols.data_type,4) = 'time' THEN REPLACE(REPLACE(REPLACE(cols.data_type,'without time zone',''), 'with time zone', 'tz'),' ','')
ELSE cols.data_type
END AS ""datatype""
, CASE WHEN cols.is_nullable = 'NO' THEN 0 ELSE 1 END AS ""is_nullable""
, CASE WHEN cols.column_default IS NOT NULL AND substring(cols.column_default,0,8) = 'nextval' THEN 1 ELSE 0 END AS ""serial""
, CASE WHEN tccu.column_name IS NULL THEN 0 ELSE 1 END AS ""primary_key""
, cols.column_default
, cols.collation_name
, cols.generation_expression
, CASE WHEN tccu_uq.column_name IS NULL THEN 0 ELSE 1 END AS ""unique_key""
, tccu.constraint_name
, tccu_uq.constraint_name
FROM INFORMATION_SCHEMA.COLUMNS cols
INNER JOIN INFORMATION_SCHEMA.TABLES tbl
ON cols.table_name = tbl.table_name
AND cols.table_schema = tbl.table_schema
AND cols.table_catalog = tbl.table_catalog
LEFT JOIN INFORMATION_SCHEMA.table_constraints tc
ON cols.table_name = tc.table_name
AND cols.table_schema = tc.table_schema
AND cols.table_catalog = tc.table_catalog
AND tc.constraint_type = 'PRIMARY KEY'
LEFT JOIN information_schema.constraint_column_usage tccu
ON cols.table_name = tccu.table_name
AND cols.table_schema = tccu.table_schema
AND cols.table_catalog = tccu.table_catalog
AND tccu.constraint_name = tc.constraint_name
AND tccu.constraint_schema = tc.constraint_schema
AND tccu.constraint_catalog = tc.constraint_catalog
AND cols.column_name = tccu.column_name
LEFT JOIN INFORMATION_SCHEMA.table_constraints tc_uq
ON cols.table_name = tc_uq.table_name
AND cols.table_schema = tc_uq.table_schema
AND cols.table_catalog = tc_uq.table_catalog
AND tc_uq.constraint_type = 'UNIQUE'
LEFT JOIN information_schema.constraint_column_usage tccu_uq
ON cols.table_name = tccu_uq.table_name
AND cols.table_schema = tccu_uq.table_schema
AND cols.table_catalog = tccu_uq.table_catalog
AND tccu_uq.constraint_name = tc_uq.constraint_name
AND tccu_uq.constraint_schema = tc_uq.constraint_schema
AND tccu_uq.constraint_catalog = tc_uq.constraint_catalog
AND cols.column_name = tccu_uq.column_name
WHERE(cols.table_name = '{TN.UnquotatedFullName}' OR CONCAT(cols.table_schema, '.', cols.table_name) = '{TN.UnquotatedFullName}')
AND cols.table_catalog = CURRENT_DATABASE()
ORDER BY cols.ordinal_position
"
, () => { curCol = new TableColumn(); }
, () => { result.Columns.Add(curCol); }
, column_name => curCol.Name = column_name.ToString()
, internal_type_name => curCol.InternalDataType = internal_type_name.ToString()
, data_type => curCol.DataType = data_type.ToString()
, is_nullable => curCol.AllowNulls = (int)is_nullable == 1 ? true : false
, serial => curCol.IsIdentity = (int)serial == 1 ? true : false
, primary_key => curCol.IsPrimaryKey = (int)primary_key == 1 ? true : false
, column_default => curCol.DefaultValue = column_default?.ToString().ReplaceIgnoreCase("::character varying", "")
, collation_name => curCol.Collation = collation_name?.ToString()
, generation_expression => curCol.ComputedColumn = generation_expression?.ToString()
, uq_key => curCol.IsUnique = (int)uq_key == 1 ? true : false
, pk_name => result.PrimaryKeyConstraintName = String.IsNullOrWhiteSpace(pk_name?.ToString()) ? result.PrimaryKeyConstraintName : pk_name.ToString()
, uq_name => result.UniqueKeyConstraintName = String.IsNullOrWhiteSpace(uq_name?.ToString()) ? result.UniqueKeyConstraintName : uq_name.ToString()
)
{
DisableLogging = true,
ConnectionManager = connection,
TaskName = $"Read column meta data for table {TN.ObjectName}"
};
readMetaSql.ExecuteReader();
return(result);
}
private static TableDefinition ReadTableDefinitionFromSqlServer(IConnectionManager connection, ObjectNameDescriptor TN)
{
TableDefinition result = new TableDefinition(TN.ObjectName);
TableColumn curCol = null;
var readMetaSql = new SqlTask(
$@"
SELECT cols.name
, CASE WHEN tpes.name IN ('varchar','char','binary','varbinary')
THEN CONCAT ( UPPER(tpes.name)
, '('
, IIF (cols.max_length = -1, 'MAX', CAST(cols.max_length as varchar(20)))
, ')'
)
WHEN tpes.name IN ('nvarchar','nchar')
THEN CONCAT ( UPPER(tpes.name)
, '('
, IIF (cols.max_length = -1, 'MAX', CAST( (cols.max_length/2) as varchar(20)))
, ')'
)
WHEN tpes.name IN ('decimal','numeric')
THEN CONCAT ( UPPER(tpes.name)
, '('
, cols.precision
,','
,cols.scale, ')'
)
ELSE UPPER(tpes.name)
END AS type_name
, cols.is_nullable
, cols.is_identity
, ident.seed_value
, ident.increment_value
, CONVERT (BIT, CASE WHEN pkidxcols.index_column_id IS NOT NULL THEN 1 ELSE 0 END ) AS primary_key
, defconstr.definition AS default_value
, cols.collation_name
, compCol.definition AS computed_column_definition
, CONVERT (BIT, CASE WHEN uqidxcols.index_column_id IS NOT NULL THEN 1 ELSE 0 END ) AS is_unique
, CASE WHEN pkidxcols.index_column_id IS NOT NULL THEN pkidx.name ELSE NULL END AS pkkey_name
, CASE WHEN uqidxcols.index_column_id IS NOT NULL THEN uqidx.name ELSE NULL END AS uqkey_name
FROM sys.columns cols
INNER JOIN (
SELECT name, type, object_id, schema_id FROM sys.tables
UNION
SELECT name, type, object_id, schema_id FROM sys.views
) tbl
ON cols.object_id = tbl.object_id
INNER JOIN sys.schemas sc
ON tbl.schema_id = sc.schema_id
INNER JOIN sys.systypes tpes
ON tpes.xtype = cols.system_type_id
LEFT JOIN sys.identity_columns ident
ON ident.object_id = cols.object_id
LEFT JOIN sys.indexes pkidx
ON pkidx.object_id = cols.object_id
AND pkidx.is_primary_key = 1
LEFT JOIN sys.index_columns pkidxcols
on pkidxcols.object_id = cols.object_id
AND pkidxcols.column_id = cols.column_id
AND pkidxcols.index_id = pkidx.index_id
LEFT JOIN sys.indexes uqidx
ON uqidx.object_id = cols.object_id
AND uqidx.is_unique_constraint = 1
LEFT JOIN sys.index_columns uqidxcols
on uqidxcols.object_id = cols.object_id
AND uqidxcols.column_id = cols.column_id
AND uqidxcols.index_id = uqidx.index_id
LEFT JOIN sys.default_constraints defconstr
ON defconstr.parent_object_id = cols.object_id
AND defconstr.parent_column_id = cols.column_id
LEFT JOIN sys.computed_columns compCol
ON compCol.object_id = cols.object_id
WHERE ( CONCAT (sc.name,'.',tbl.name) ='{TN.UnquotatedFullName}' OR tbl.name = '{TN.UnquotatedFullName}' )
AND tbl.type IN ('U','V')
AND tpes.name <> 'sysname'
ORDER BY cols.column_id
"
, () => { curCol = new TableColumn(); }
, () => { result.Columns.Add(curCol); }
, name => curCol.Name = name.ToString()
, type_name => curCol.DataType = type_name.ToString()
, is_nullable => curCol.AllowNulls = (bool)is_nullable
, is_identity => curCol.IsIdentity = (bool)is_identity
, seed_value => curCol.IdentitySeed = (int?)(Convert.ToInt32(seed_value))
, increment_value => curCol.IdentityIncrement = (int?)(Convert.ToInt32(increment_value))
, primary_key => curCol.IsPrimaryKey = (bool)primary_key
, default_value =>
curCol.DefaultValue = default_value?.ToString().Substring(2, (default_value.ToString().Length) - 4)
, collation_name => curCol.Collation = collation_name?.ToString()
, computed_column_definition => curCol.ComputedColumn = computed_column_definition?.ToString().Substring(1, (computed_column_definition.ToString().Length) - 2)
, uq_key => curCol.IsUnique = (bool)uq_key
, pk_name => result.PrimaryKeyConstraintName = String.IsNullOrWhiteSpace(pk_name?.ToString()) ? result.PrimaryKeyConstraintName : pk_name.ToString()
, uq_name => result.UniqueKeyConstraintName = String.IsNullOrWhiteSpace(uq_name?.ToString()) ? result.UniqueKeyConstraintName : uq_name.ToString()
)
{
DisableLogging = true,
ConnectionManager = connection,
TaskName = $"Read column meta data for table {TN.ObjectName}"
};
readMetaSql.ExecuteReader();
return(result);
}
private static TableDefinition ReadTableDefinitionFromOracle(IConnectionManager connection, ObjectNameDescriptor TN)
{
TableDefinition result = new TableDefinition(TN.ObjectName);
TableColumn curCol = null;
//Regarding default values: The issue is described partly here
//https://stackoverflow.com/questions/46991132/how-to-cast-long-to-varchar2-inline/47041776
string sql = $@"
SELECT cols.COLUMN_NAME
, CASE WHEN cols.DATA_TYPE
IN ('VARCHAR','CHAR', 'NCHAR', 'NVARCHAR', 'NVARCHAR2', 'NCHAR2', 'VARCHAR2', 'CHAR2' )
THEN cols.DATA_TYPE || '(' || cols.CHAR_LENGTH || ')'
WHEN cols.DATA_TYPE
IN ('NUMBER')
THEN cols.DATA_TYPE || '(' ||cols.DATA_LENGTH ||',' ||
CASE WHEN cols.DATA_SCALE IS NULL THEN 127 ELSE cols.DATA_SCALE END
|| ')'
ELSE cols.DATA_TYPE
END AS data_type
, cols.NULLABLE
, cols.IDENTITY_COLUMN
, CASE WHEN cons.CONSTRAINT_TYPE = 'P' THEN 'ENABLED' ELSE NULL END as primary_key
, cols.DATA_DEFAULT --not working, see restriction above
, cols.COLLATION
, cols.DATA_DEFAULT AS generation_expression
, CASE WHEN cons.CONSTRAINT_TYPE = 'U' THEN 'ENABLED' ELSE NULL END as unique_key
FROM ALL_TAB_COLUMNS cols
LEFT JOIN (
SELECT acols.table_name, acols.column_name, acols.position, acons.status, acons.owner, acons.constraint_type
FROM ALL_CONSTRAINTS acons, ALL_CONS_COLUMNS acols
WHERE acons.CONSTRAINT_TYPE IN ('U','P')
AND acons.CONSTRAINT_NAME = acols.CONSTRAINT_NAME
AND acons.OWNER = acols.OWNER
) cons
ON cons.TABLE_NAME = cols.TABLE_NAME
AND cons.OWNER = cols.OWNER
--AND cons.position = cols.COLUMN_ID
AND cons.column_name = cols.COLUMN_NAME
WHERE
cols.TABLE_NAME NOT LIKE 'BIN$%'
AND cols.OWNER NOT IN ('SYS', 'SYSMAN', 'CTXSYS', 'MDSYS', 'OLAPSYS', 'ORDSYS', 'OUTLN', 'WKSYS', 'WMSYS', 'XDB', 'ORDPLUGINS', 'SYSTEM')
AND ( cols.TABLE_NAME = '{TN.UnquotatedFullName}'
OR (cols.OWNER || '.' || cols.TABLE_NAME ) = '{TN.UnquotatedFullName}'
)
ORDER BY cols.COLUMN_ID
";
var readMetaSql = new SqlTask(
sql
, () => { curCol = new TableColumn(); }
, () => { result.Columns.Add(curCol); }
, column_name => curCol.Name = column_name.ToString()
, data_type => curCol.DataType = data_type.ToString()
, nullable => curCol.AllowNulls = nullable.ToString() == "Y" ? true : false
, identity_column => curCol.IsIdentity = identity_column?.ToString() == "YES" ? true : false
, primary_key => curCol.IsPrimaryKey = primary_key?.ToString() == "ENABLED" ? true : false
, data_default => curCol.DefaultValue = data_default?.ToString()
, collation => curCol.Collation = collation?.ToString()
, generation_expression => curCol.ComputedColumn = generation_expression?.ToString()
, uq_key => curCol.IsUnique = uq_key?.ToString() == "ENABLED" ? true : false
)
{
DisableLogging = true,
ConnectionManager = connection,
TaskName = $"Read column meta data for table {TN.ObjectName}"
};
readMetaSql.ExecuteReader();
return(result);
}
private static TableDefinition ReadTableDefinitionFromAccess(IConnectionManager connection, ObjectNameDescriptor TN)
{
return(connection?.ReadTableDefinition(TN));
}
private static TableDefinition ReadTableDefinitionFromMySqlServer(IConnectionManager connection, ObjectNameDescriptor TN)
{
TableDefinition result = new TableDefinition(TN.ObjectName);
TableColumn curCol = null;
var readMetaSql = new SqlTask($"Read column meta data for table {TN.ObjectName}",
$@"
SELECT cols.column_name
, CASE WHEN cols.data_type IN ('varchar','char') THEN CONCAT (cols.data_type,'(',cols.character_maximum_length, ')')
WHEN cols.data_type IN ('decimal') THEN CONCAT (cols.data_type,'(',cols.numeric_precision,',', cols.numeric_scale, ')')
ELSE cols.data_type
END AS 'data_type'
, CASE WHEN cols.is_nullable = 'NO' THEN 0 ELSE 1 END AS 'is_nullable'
, CASE WHEN cols.extra IS NOT NULL AND cols.extra = 'auto_increment' THEN 1 ELSE 0 END AS 'auto_increment'
, CASE WHEN isnull(k.constraint_name) THEN 0 ELSE 1 END AS 'primary_key'
, cols.column_default
, cols.collation_name
, cols.generation_expression
, cols.column_comment
FROM INFORMATION_SCHEMA.COLUMNS cols
INNER JOIN INFORMATION_SCHEMA.TABLES tbl
ON cols.table_name = tbl.table_name
AND cols.table_schema = tbl.table_schema
AND cols.table_catalog = tbl.table_catalog
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
ON cols.table_name = k.table_name
AND cols.table_schema = k.table_schema
AND cols.table_catalog = k.table_catalog
AND cols.column_name = k.column_name
AND k.constraint_name = 'PRIMARY'
WHERE ( cols.table_name = '{TN.UnquotatedFullName}' OR CONCAT(cols.table_catalog,'.',cols.table_name) = '{TN.UnquotatedFullName}')
AND cols.table_schema = DATABASE()
ORDER BY cols.ordinal_position
"
, () => { curCol = new TableColumn(); }
, () => { result.Columns.Add(curCol); }
, column_name => curCol.Name = column_name.ToString()
, data_type => curCol.DataType = data_type.ToString()
, is_nullable => curCol.AllowNulls = (int)is_nullable == 1 ? true : false
, auto_increment => curCol.IsIdentity = (int)auto_increment == 1 ? true : false
, primary_key => curCol.IsPrimaryKey = (int)primary_key == 1 ? true : false
, column_default => curCol.DefaultValue = column_default?.ToString()
, collation_name => curCol.Collation = collation_name?.ToString()
, generation_expression => curCol.ComputedColumn = generation_expression?.ToString()
, comment => curCol.Comment = comment?.ToString()
)
{
DisableLogging = true,
ConnectionManager = connection
};
readMetaSql.ExecuteReader();
return(result);
}
