/// <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!");
}
}
/// <summary>
/// Uses the rules specified in the ConnectionManagerTypeRules variable to determine the connection manager type.
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
private ConnectionManagerType GetConnectionManagerType(ConnectionManager connection)
{
ConnectionManagerType result = ConnectionManagerType.Unsupported;
String creationName = connection.CreationName;
// Use the rules in the ConnectionManagerTypeRules variable ...
foreach (var rule in ConnectionManagerTypeRules)
{
// ... to determine whether the connection manager is supported.
if (creationName.StartsWith(rule.Key))
{
// If the connection string contains user credentials, resolve the connection manager as unsupported; ...
if (!String.IsNullOrEmpty(connection.Properties[USER_NAME_KEY].GetValue(connection)?.ToString()))
{
break;
}
else
{
// ... otherwise, return tha actual connection manager type.
result = rule.Value;
break;
}
}
}
return(result);
}
private static void GetColumns(IConnectionManager connectionManager, string name, List <TableColumn> columns)
{
ValidateName(name);
IfTableOrViewExistsTask.ThrowExceptionIfNotExists(connectionManager, name);
ConnectionManagerType connectionType = ConnectionManagerSpecifics.GetType(connectionManager);
ObjectNameDescriptor TN = new ObjectNameDescriptor(name, connectionType);
if (connectionType == ConnectionManagerType.SqlServer)
{
GetColumnsFromSqlServer(connectionManager, TN, columns);
}
else if (connectionType == ConnectionManagerType.SQLite)
{
GetColumnsFromSQLite(connectionManager, TN, columns);
}
else if (connectionType == ConnectionManagerType.MySql)
{
GetColumnsFromMySqlServer(connectionManager, TN, columns);
}
else if (connectionType == ConnectionManagerType.Postgres)
{
GetColumnsFromPostgres(connectionManager, TN, columns);
}
else if (connectionType == ConnectionManagerType.Access)
{
GetColumnsFromAccess(connectionManager, TN, columns);
}
else
{
throw new ETLBoxException("Unknown connection type - please pass a valid TableDefinition!");
}
}
public static TableDefinition GetDefinitionFromTableName(string tableName, IConnectionManager connection)
{
IfTableOrViewExistsTask.ThrowExceptionIfNotExists(connection, tableName);
ConnectionManagerType connectionType = ConnectionManagerSpecifics.GetType(connection);
if (connectionType == ConnectionManagerType.SqlServer)
{
return(ReadTableDefinitionFromSqlServer(tableName, connection));
}
else if (connectionType == ConnectionManagerType.SQLite)
{
return(ReadTableDefinitionFromSQLite(tableName, connection));
}
else if (connectionType == ConnectionManagerType.MySql)
{
return(ReadTableDefinitionFromMySqlServer(tableName, connection));
}
else if (connectionType == ConnectionManagerType.Postgres)
{
return(ReadTableDefinitionFromPostgres(tableName, connection));
}
else
{
throw new ETLBoxException("Unknown connection type - please pass a valid TableDefinition!");
}
}
/// <summary>
/// Adds the application name argument to a connection string of a supported connection manager type.
/// </summary>
/// <param name="connectionString"></param>
/// <param name="type"></param>
/// <returns></returns>
private String GetEnhancedConnectionString(String connectionString, ConnectionManagerType type)
{
// Create a new database connection string builder, and initialize it with the supplied connection string.
DbConnectionStringBuilder connectionStringBuilder = new DbConnectionStringBuilder(type == ConnectionManagerType.ODBC)
{
ConnectionString = connectionString
};
// If the connection manager is supported and the application name is available, ...
if (type != ConnectionManagerType.Unsupported &&
!String.IsNullOrEmpty(_applicationName))
{
// ... determine the application name connection string argument name, ...
String applicationNameKey;
switch (type)
{
case ConnectionManagerType.OleDb:
case ConnectionManagerType.ODBC:
applicationNameKey = "APP";
break;
case ConnectionManagerType.AdoNet:
default:
applicationNameKey = "Application Name";
break;
}
// ... and add the application name argument to the connection string builder.
connectionStringBuilder.Add(applicationNameKey, _applicationName);
}
// Return the enhanced connection string.
return(connectionStringBuilder.ConnectionString);
}
public static string TryGetDBSpecificType(string dbSpecificTypeName, ConnectionManagerType connectionType)
{
var typeName = dbSpecificTypeName.Trim().ToUpper();
if (connectionType == ConnectionManagerType.SqlServer)
{
if (typeName.Replace(" ", "") == "TEXT")
{
return("VARCHAR(MAX)");
}
}
if (connectionType == ConnectionManagerType.Access)
{
if (typeName == "INT")
{
return("INTEGER");
}
else if (IsCharTypeDefinition(typeName))
{
if (typeName.StartsWith("N"))
{
typeName = typeName.Substring(1);
}
if (GetStringLengthFromCharString(typeName) > 255)
{
return("LONGTEXT");
}
return(typeName);
}
return(dbSpecificTypeName);
}
else if (connectionType == ConnectionManagerType.SQLite)
{
if (typeName == "INT" || typeName == "BIGINT")
{
return("INTEGER");
}
return(dbSpecificTypeName);
}
else if (connectionType == ConnectionManagerType.Postgres)
{
if (IsCharTypeDefinition(typeName))
{
if (typeName.StartsWith("N"))
{
return(typeName.Substring(1));
}
}
else if (typeName == "DATETIME")
{
return("TIMESTAMP");
}
return(dbSpecificTypeName);
}
else
{
return(dbSpecificTypeName);
}
}
public static IEnumerable <string> AddQuotations(this ConnectionManagerType type, IEnumerable <string> names)
{
if (names is null)
{
throw new ArgumentNullException(nameof(names));
}
return(names.Select(i => type.AddQuotations(i)));
}
public ConnectionComponent(NetworkAddress networkAddress, string connectionManagerIpAddress,
int connectionManagerListeningPort, ConnectionManagerType connectionManagerType)
{
_ipAddress = IPAddress.Parse(connectionManagerIpAddress);
_connectionManagerListeningPort = connectionManagerListeningPort;
_connectionManagerType = connectionManagerType;
_handshakeMessage = new ConnectionRequestMessage(networkAddress, PortRandomizer.RandomFreePort());
_connectionManagerType = connectionManagerType;
}
public static string JoinColumnNames(
this ConnectionManagerType type,
IEnumerable <string> columnNames,
bool addQuotationsToColumnName = true,
string tableName = null,
bool addQuotationsToTableName = true)
{
columnNames = type.ColumnNames(columnNames, addQuotationsToColumnName, tableName, addQuotationsToTableName);
return(Sql.ToString(columnNames));
}
public static string GetEndQuotation(ConnectionManagerType type)
{
if (type == ConnectionManagerType.SqlServer || type == ConnectionManagerType.Access)
{
return(@"]");
}
else
{
return(GetBeginQuotation(type));
}
}
public string TryConvertDbDataType(string dbSpecificTypeName, ConnectionManagerType connectionType)
{
if (dbSpecificTypeName == "ABC")
{
return("DATETIME");
}
else
{
return(DataTypeConverter.TryGetDbSpecificType(dbSpecificTypeName, connectionType));
}
}
/// <summary>
/// Returns a database specific type for the provided .NET datat type, depending on the connection
/// manager. E.g. passing the .NET data type long for SqlServer will return the string BIGINT
/// </summary>
/// <param name="clrType">The .NET data type</param>
/// <param name="connectionType">Database connection type, e.g. SqlServer</param>
/// <returns>A database specific type string</returns>
public static string GetDatabaseType(Type clrType, ConnectionManagerType connectionType)
{
if (connectionType == ConnectionManagerType.SqlServer)
{
if (Type2SqlServerType.ContainsKey(clrType))
{
return(Type2SqlServerType[clrType]);
}
else
{
throw new ArgumentOutOfRangeException("clrType", clrType, "Cannot map the ClrType to database specific Type");
}
}
else
{
throw new ArgumentException("This connection type is not supported yet!", nameof(connectionType));
}
}
public static string AddQuotations(this ConnectionManagerType type, string name)
{
if (string.IsNullOrWhiteSpace(name))
{
throw new ArgumentException("Value cannot be null or white space", nameof(name));
}
string qb = type.GetBeginQuotation();
string qe = type.GetEndQuotation();
if (!name.StartsWith(qb))
{
name = qb + name;
}
if (!name.EndsWith(qe))
{
name += qe;
}
return(name);
}
public static string GetBeginQuotation(ConnectionManagerType type)
{
if (type == ConnectionManagerType.SqlServer || type == ConnectionManagerType.Access)
{
return(@"[");
}
else if (type == ConnectionManagerType.MySql)
{
return(@"`");
}
else if (type == ConnectionManagerType.Postgres || type == ConnectionManagerType.SQLite)
{
return(@"""");
}
else
{
return(string.Empty);
}
}
public static IEnumerable <string> ColumnNames(
this ConnectionManagerType type,
IEnumerable <string> columnNames,
bool addQuotationsToColumnName = true,
string tableName = null,
bool addQuotationsToTableName = true)
{
if (columnNames is null)
{
throw new ArgumentNullException(nameof(columnNames));
}
if (addQuotationsToTableName &&
!string.IsNullOrWhiteSpace(tableName))
{
tableName = type.AddQuotations(tableName);
}
columnNames = columnNames.Select(i => type.ColumnName(i, true, tableName, false));
return(columnNames);
}
public static TableDefinition GetDefinitionFromTableName(string tableName, IConnectionManager connection)
{
IfExistsTask.ThrowExceptionIfNotExists(connection, tableName);
ConnectionManagerType connectionType = ConnectionManagerTypeFinder.GetType(connection);
//return ReadTableDefinitionFromDataTable(tableName, connection);
if (connectionType == ConnectionManagerType.SqlServer)
{
return(ReadTableDefinitionFromSqlServer(tableName, connection));
}
else if (connectionType == ConnectionManagerType.SQLLite)
{
return(ReadTableDefinitionFromSQLite(tableName, connection));
}
else
{
throw new ETLBoxException("Unknown connection type - please pass a valid TableDefinition!");
}
}
public static string ColumnName(
this ConnectionManagerType type,
string columnName,
bool addQuotationsToColumnName = true,
string tableName = null,
bool addQuotationsToTableName = true)
{
if (addQuotationsToColumnName)
{
columnName = type.AddQuotations(columnName);
}
if (!string.IsNullOrWhiteSpace(tableName))
{
if (addQuotationsToTableName)
{
tableName = type.AddQuotations(tableName);
}
columnName = Sql.ToString(new[] { tableName, columnName }, Sql.DotDelimiter);
}
return(columnName);
}
public static string TryGetDBSpecificType(string dbSpecificTypeName, ConnectionManagerType connectionType)
{
var typeName = dbSpecificTypeName.Trim().ToUpper();
if (connectionType == ConnectionManagerType.Access)
{
if (IsCharTypeDefinition(typeName))
{
if (typeName.StartsWith("N"))
{
typeName = typeName.Substring(1);
}
if (GetStringLengthFromCharString(typeName) > 255)
{
return("LONGTEXT");
}
//if (typeName.StartsWith("int")
// || typeName.StartsWith("smallint")
// || typeName.StartsWith("bigint")
// || typeName.StartsWith("tinyint")
// || typeName.StartsWith("decimal")
// )
// return "NUMBER";
return(typeName);
}
return(dbSpecificTypeName);
}
if (connectionType == ConnectionManagerType.SQLLite)
{
if (typeName == "INT")
{
return("INTEGER");
}
return(dbSpecificTypeName);
}
else
{
return(dbSpecificTypeName);
}
}
public static string SqlIdIn(this ConnectionManagerType type, IEnumerable <string> idColumnNames, IEnumerable <IEnumerable <object> > ids, string tableName = null)
{
if (ids is null)
{
throw new ArgumentNullException(nameof(ids));
}
string result;
int columnCount = idColumnNames.Count();
// simple in
if (columnCount == 1)
{
string id = type.ColumnName(idColumnNames.Single(), tableName: tableName);
string values = Sql.ToString(ids.SelectMany(i => i));
result = $"{id} in ({values})";
}
// tuples
else if (type.SupportsTuples())
{
string columns = type.JoinColumnNames(idColumnNames, tableName: tableName);
ValidateId(columns, idColumnNames);
string values = Sql.ToString(ids.Select(id => Sql.AddBraces(Sql.ToString(id))));
ValidateIds(values, ids);
result = $"({columns}) in ({values})";
}
// and/or
else
{
ValidateId(type.JoinColumnNames(idColumnNames), idColumnNames);
idColumnNames = type.ColumnNames(idColumnNames, tableName: tableName).ToArray();
result = Sql.ToString(
ids.Select(id =>
Sql.ToString(
idColumnNames.Zip(id, (name, value) => (name, value)),
itemDelimiter: Sql.AndDelimiter)),
Sql.OrDelimiter);
ValidateIds(result, ids);
}
return(result);
}
public ObjectNameDescriptor(string objectName, IConnectionManager connection)
{
this.ObjectName = objectName;
this.ConnectionType = ConnectionManagerSpecifics.GetType(connection);
ParseSchemaAndTable();
}
/// <summary>
/// Converts a data type alias name (e.g. an alias name
/// like "varchar(10)" ) to the original database type name ("character varying").
/// </summary>
/// <param name="dataTypeName">The database alias type name</param>
/// <param name="connectionType">Which database (e.g. Postgres, MySql, ...)</param>
/// <returns>The type name converted to an original database type name</returns>
public static string TryConvertAliasName(string dataTypeName, ConnectionManagerType connectionType)
{
if (connectionType == ConnectionManagerType.Postgres)
{
//See https://www.postgresql.org/docs/9.5/datatype.html for aliases
dataTypeName = dataTypeName.ToLower().Trim();
if (dataTypeName == "int8")
{
return("bigint");
}
else if (dataTypeName == "serial8")
{
return("bigserial");
}
else if (dataTypeName.StartsWith("varbit") || dataTypeName.StartsWith("bit varying"))
{
return("bit varying");
}
else if (dataTypeName == "bool")
{
return("boolean");
}
else if (dataTypeName.StartsWith("char") || dataTypeName.StartsWith("nchar"))
{
return("character");
}
else if (dataTypeName.StartsWith("varchar") || dataTypeName.StartsWith("nvarchar"))
{
return("character varying");
}
else if (dataTypeName == "float8")
{
return("double precision");
}
else if (dataTypeName == "int" || dataTypeName == "int4")
{
return("integer");
}
else if (dataTypeName.StartsWith("decimal") || dataTypeName.StartsWith("numeric"))
{
return("numeric");
}
else if (dataTypeName == "float")
{
return("real");
}
else if (dataTypeName == "int2")
{
return("smallint");
}
else if (dataTypeName == "serial2")
{
return("smallserial");
}
else if (dataTypeName == "serial4")
{
return("serial");
}
else if (dataTypeName == "timestamptz")
{
return("timestamp with time zone");
}
else if (dataTypeName.StartsWith("timestamp") && dataTypeName.EndsWith("with time zone"))
{
return("timestamp with time zone");
}
else if (dataTypeName.StartsWith("timestamp"))
{
return("timestamp without time zone");
}
else if (dataTypeName == "timetz")
{
return("time with time zone");
}
else if (dataTypeName.StartsWith("time") && dataTypeName.EndsWith("with time zone"))
{
return("time with time zone");
}
else if (dataTypeName.StartsWith("time"))
{
return("time without time zone");
}
else if (dataTypeName.StartsWith("bit"))
{
return("bit");
}
else
{
return(dataTypeName);
}
}
return(dataTypeName);
}
/// <summary>
/// Tries to convert the data type into a database specific type.
/// E.g. the data type 'INT' would be converted to 'INTEGER' for SQLite connections.
/// </summary>
/// <param name="dataTypeName">A data type name</param>
/// <param name="connectionType">The database connection type</param>
/// <returns>The converted database specific type name</returns>
public static string TryGetDbSpecificType(string dataTypeName, ConnectionManagerType connectionType)
{
var typeName = dataTypeName.Trim().ToUpper();
//Always normalize to some "standard" for Oracle!
//https://docs.microsoft.com/en-us/sql/relational-databases/replication/non-sql/data-type-mapping-for-oracle-publishers?view=sql-server-ver15
if (connectionType != ConnectionManagerType.Oracle)
{
if (typeName.StartsWith("NUMBER"))
{
return(typeName.Replace("NUMBER", "NUMERIC"));
}
if (typeName.StartsWith("VARCHAR2"))
{
return(typeName.Replace("VARCHAR2", "VARCHAR"));
}
else if (typeName.StartsWith("NVARCHAR2"))
{
return(typeName.Replace("NVARCHAR2", "NVARCHAR"));
}
}
//Now start with "normal" translation, other Database have many commons
if (connectionType == ConnectionManagerType.SqlServer)
{
if (typeName.Replace(" ", "") == "TEXT")
{
return("VARCHAR(MAX)");
}
return(dataTypeName);
}
else if (connectionType == ConnectionManagerType.Access)
{
if (typeName == "INT")
{
return("INTEGER");
}
else if (IsCharTypeDefinition(typeName))
{
if (typeName.StartsWith("N"))
{
typeName = typeName.Substring(1);
}
if (GetStringLengthFromCharString(typeName) > 255)
{
return("LONGTEXT");
}
return(typeName);
}
return(dataTypeName);
}
else if (connectionType == ConnectionManagerType.SQLite)
{
if (typeName == "INT" || typeName == "BIGINT")
{
return("INTEGER");
}
return(dataTypeName);
}
else if (connectionType == ConnectionManagerType.Postgres)
{
if (IsCharTypeDefinition(typeName))
{
if (typeName.StartsWith("N"))
{
return(typeName.Substring(1));
}
}
else if (typeName == "DATETIME")
{
return("TIMESTAMP");
}
else if (typeName.StartsWith("VARBINARY") || typeName.StartsWith("BINARY"))
{
return("BYTEA");
}
return(dataTypeName);
}
else if (connectionType == ConnectionManagerType.Db2)
{
if (typeName == "TEXT")
{
return("CLOB");
}
return(dataTypeName);
}
else if (connectionType == ConnectionManagerType.Oracle)
{
if (IsCharTypeDefinition(typeName))
{
if (typeName.Replace(" ", "").StartsWith("NVARCHAR("))
{
return(typeName.Replace("NVARCHAR", "NVARCHAR2"));
}
else if (typeName.Replace(" ", "").StartsWith("VARCHAR("))
{
return(typeName.Replace("VARCHAR", "VARCHAR2"));
}
}
else if (typeName.StartsWith("BINARY") && !typeName.StartsWith("BINARY_"))
{
return(typeName.Replace("BINARY", "RAW"));
}
else if (typeName == "BIGINT")
{
return("INT");
}
else if (typeName == "DATETIME")
{
return("DATE");
}
else if (typeName == "FLOAT")
{
return("FLOAT(126)");
}
else if (typeName == "TEXT")
{
return("NCLOB");
}
return(dataTypeName);
}
else
{
return(dataTypeName);
}
}
/// <inheritdoc/> public string TryConvertDbDataType(string dataTypeName, ConnectionManagerType connectionType) => DataTypeConverter.TryGetDbSpecificType(dataTypeName, connectionType);
public TableNameDescriptor(string tableName, IConnectionManager connection)
{
this.FullName = tableName;
this.ConnectionType = ConnectionManagerSpecifics.GetType(connection);
}
public TableNameDescriptor(string tableName, ConnectionManagerType connectionType)
{
this.FullName = tableName;
this.ConnectionType = connectionType;
}
protected ConnectionManager(int listeningPort, ConnectionManagerType connectionManagerType)
{
_nodesTcpClients = new Dictionary <NetworkAddress, TcpClient>();
ListeningPort = listeningPort;
ConnectionManagerType = connectionManagerType;
}
/// <summary>
/// Starts the socket server.It registers some types with compact Framework,
/// enables simple logs as well as DetailedLogs, then it checks Ncache licence information.
/// starts connection manager and perfmon counters.
/// </summary>
/// <param name="bindIP" ></param>
///
public void Start(IPAddress bindIP, LoggerNames loggerName, string perfStatColInstanceName, CommandManagerType cmdMgrType, ConnectionManagerType conMgrType)
{
if (loggerName == null)
{
_loggerName = LoggerNames.SocketServerLogs;
}
else
{
_loggerName = loggerName;
}
InitializeLogging();
#if JAVA
_perfStatsColl = new PerfStatsCollector("TayzGrid Server", _serverPort);
#else
_perfStatsColl = new PerfStatsCollector(cacheName, _serverPort);
#endif
_conManager = new ConnectionManager(_perfStatsColl);
_conManager.Start(bindIP, _serverPort, _sendBuffer, _recieveBuffer, _logger, cmdMgrType, conMgrType);
if (ConnectionManagerType.HostClient == conMgrType)
{
_hostClientConnectionManager = _conManager;
}
// We initialize PerfstatsCollector only for SocketServer's instance for client.
// Management socket server has just DUMMY stats collector.
if (conMgrType == ConnectionManagerType.HostClient)
{
_perfStatsColl.InitializePerfCounters();
}
}
public ObjectNameDescriptor(string objectName, ConnectionManagerType connectionType)
{
this.ObjectName = objectName;
this.ConnectionType = connectionType;
ParseSchemaAndTable();
}
public ObjectNameDescriptor(string objectName, ConnectionManagerType connectionType)
{
this.ObjectName = objectName;
this.ConnectionType = connectionType;
}
public static bool SupportsTuples(this ConnectionManagerType type) => type == ConnectionManagerType.Postgres;
