protected IEnumerable <ITableSourceColumnInfo> GetColumns(IEnumerable <TableSourceInfo> tableSources, DatabaseInfo dbInfo,
CreateAutoNumberInfo createAutoNumber, CreateDataTypeInfo createDataType, CreateColumnInfo createColumnInfo)
{
var result = new List <ITableSourceColumnInfo>();
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
var execService = DatabaseServices.ExecutionService as ExecutionService.ExecutionService;
// #417717 - In these queries we don't want to use the CIAI settings
if (execService != null)
{
execService.ForceCSASSetting(conn);
}
IDictionary <string, AutoNumberColumnInfo> autoGeneratedColumns = new Dictionary <string, AutoNumberColumnInfo>();
IList <TableSourceInfo> tableSourceList = tableSources.ToList();
using (IDataReader reader = GetTriggersBulk(conn, tableSourceList, /*onlyAutoNumbers*/ true, String.Empty, dbInfo)) {
while (reader.Read())
{
string tableName = Convert.ToString(reader["TABLE_NAME"]);
if (autoGeneratedColumns.ContainsKey(tableName))
{
break;
}
string triggerName = Convert.ToString(reader["trigger_name"]).ToUpperInvariant();
string triggerBody = Convert.ToString(reader["trigger_body"]).ToUpperInvariant();
string triggerType = Convert.ToString(reader["trigger_type"]).ToUpperInvariant();
string triggeringEvent = Convert.ToString(reader["triggering_event"]).ToUpperInvariant();
var autoNumberInfo = createAutoNumber(triggerType, triggeringEvent, triggerName, triggerBody);
if (autoNumberInfo != null)
{
autoGeneratedColumns[GetAutoNumberColumnKey(tableName, autoNumberInfo.ColumnName)] = autoNumberInfo;
}
}
}
using (IDataReader reader = GetColumnsBulk(conn, tableSourceList, dbInfo)) {
while (reader.Read())
{
// WARNING:
// Do not change the order of these variables. In Java, columns of type Long and Long Raw must be read before the others (DATA_DEFAULT must be first).
// Read more here: https://blog.jooq.org/2015/12/30/oracle-long-and-long-raw-causing-stream-has-already-been-closed-exception/
string dataDefault = Convert.ToString(reader["DATA_DEFAULT"]);
string tableName = Convert.ToString(reader["TABLE_NAME"]);
string columnName = Convert.ToString(reader["COLUMN_NAME"]);
object constraintType = reader["CONSTRAINT_TYPE"];
object dataPrecision = reader["DATA_PRECISION"];
object dataScale = reader["DATA_SCALE"];
bool isMandatory = Convert.ToString(reader["NULLABLE"]).ToUpper() != "Y";
bool isPrimaryKey = !constraintType.Equals(DBNull.Value) && Convert.ToString(constraintType) == "P";
int precision = dataPrecision.Equals(DBNull.Value) ? 0 : Convert.ToInt32(dataPrecision);
int scale = dataScale.Equals(DBNull.Value) ? 0 : Convert.ToInt32(dataScale);
DataTypeInfo dataType = createDataType(Convert.ToString(reader["DATA_TYPE"]), Convert.ToInt32(reader["CHAR_LENGTH"]),
precision, scale);
AutoNumberColumnInfo autoNumberInfo;
autoGeneratedColumns.TryGetValue(GetAutoNumberColumnKey(tableName, columnName), out autoNumberInfo);
if (autoNumberInfo == null) // For versions above 12c (12.1) autonumbers can be generated via identity or sequences
{
if (IsSequenceValue(dataDefault))
{
autoNumberInfo = new AutoNumberColumnInfo()
{
ColumnName = columnName
};
}
}
TableSourceInfo tableSource = tableSourceList.First(ts => ts.Name.EqualsIgnoreCase(tableName));
result.Add(createColumnInfo(tableSource, columnName, dataType, isMandatory, isPrimaryKey, autoNumberInfo));
}
}
}
return(result);
}
protected IEnumerable <ITableSourceColumnInfo> GetColumns(ITableSourceInfo tableSource, CreateDataTypeInfo createDataTypeInfo, CreateColumnInfo createColumnInfo)
{
string paramPrefix = DatabaseServices.ExecutionService.ParameterPrefix;
var columnsInfo = new List <ITableSourceColumnInfo>();
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection())
{
string database;
string schema;
string table;
SnowflakeDatabaseObjectFactory.ParseQualifiedTableName(tableSource.QualifiedName, out database, out schema, out table);
string queryInfoSchema = string.Format("SELECT DISTINCT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, COLUMN_DEFAULT, ORDINAL_POSITION " +
"FROM {0}.INFORMATION_SCHEMA.COLUMNS " +
"WHERE TABLE_NAME = '{1}' " +
"AND TABLE_SCHEMA = '{2}' " +
"ORDER BY ORDINAL_POSITION;", database, table, schema);
string queryInfoTable = string.Format("DESC TABLE {0}", tableSource.QualifiedName);
IDbCommand cmdInfoSchema = DatabaseServices.ExecutionService.CreateCommand(conn, queryInfoSchema);
IDbCommand cmdInfoTable = DatabaseServices.ExecutionService.CreateCommand(conn, queryInfoTable);
cmdInfoSchema.CommandTimeout = QueryTimeout;
cmdInfoTable.CommandTimeout = QueryTimeout;
string pkField = null;
using (IDataReader readerInfoTable = cmdInfoTable.ExecuteReader())
{
while (pkField == null && readerInfoTable.Read())
{
string fieldName = (string)readerInfoTable["name"];
string pk = (string)readerInfoTable["primary key"];
if (pk == "Y")
{
pkField = fieldName;
break;
}
}
}
using (IDataReader readerInfoSchema = cmdInfoSchema.ExecuteReader())
{
while (readerInfoSchema.Read())
{
string tableName = (string)readerInfoSchema["TABLE_NAME"];
string columnName = (string)readerInfoSchema["COLUMN_NAME"];
string type = (string)readerInfoSchema["DATA_TYPE"];
int length;
//longblobs and longtext might cause overflow so we need to protect the decoding
Int32.TryParse(Convert.ToString(readerInfoSchema["CHARACTER_MAXIMUM_LENGTH"]), out length);
int precision = readerInfoSchema["NUMERIC_PRECISION"] == DBNull.Value ? 0 : Convert.ToInt32(readerInfoSchema["NUMERIC_PRECISION"]);
int scale = readerInfoSchema["NUMERIC_SCALE"] == DBNull.Value ? 0 : Convert.ToInt32(readerInfoSchema["NUMERIC_SCALE"]);
IDataTypeInfo datatype = createDataTypeInfo(type, length, precision, scale);
bool isAutoGenerated = false;
bool isPrimaryKey = pkField != null && columnName.Equals(pkField);
bool isMandatory = isAutoGenerated || "NO".EqualsIgnoreCase((string)readerInfoSchema["IS_NULLABLE"]);
ITableSourceColumnInfo columnInfo = createColumnInfo(tableSource, columnName, datatype, isMandatory, isPrimaryKey, isAutoGenerated);
columnsInfo.Add(columnInfo);
}
}
}
return(columnsInfo);
}
protected IEnumerable <ITableSourceColumnInfo> GetColumns(
IEnumerable <CacheTableSourceInfo> tableSources,
CreateDataTypeInfo createDataTypeInfo,
CreateColumnInfo createColumnInfo)
{
var columnsInfo = new List <ITableSourceColumnInfo>();
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
string paramPrefix = DatabaseServices.ExecutionService.ParameterPrefix;
string tableNames = "'" + tableSources.Select(t => t.Name).StrCat("','") + "'";
string query = string.Format(@"SELECT
TABLE_NAME, COLUMN_NAME, odbctype, DATA_TYPE,
IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION,
NUMERIC_SCALE, PRIMARY_KEY, AUTO_INCREMENT , COLUMN_DEFAULT, DATETIME_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA= {0}
AND TABLE_NAME in (" + tableNames + @")
ORDER BY ORDINAL_POSITION", paramPrefix + "schema");
IDbCommand cmd = DatabaseServices.ExecutionService.CreateCommand(conn, query);
DatabaseServices.ExecutionService.CreateParameter(cmd, paramPrefix + "schema", DbType.String, tableSources.First().Database.Name);
cmd.CommandTimeout = QueryTimeout;
using (IDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
int length;
string tableName = (string)reader["TABLE_NAME"];
string columnName = (string)reader["COLUMN_NAME"];
int precision = reader["NUMERIC_PRECISION"] == DBNull.Value ? 0 : Convert.ToInt32(reader["NUMERIC_PRECISION"]);
int scale = reader["NUMERIC_SCALE"] == DBNull.Value ? 0 : Convert.ToInt32(reader["NUMERIC_SCALE"]);
int datetimePrecision = reader["DATETIME_PRECISION"] == DBNull.Value ? 0 : Convert.ToInt32(reader["DATETIME_PRECISION"]);
// Could not find what types are given in the DATA_TYPE column,
// but could find a table which gives the integer value odbctype so using this.
int odbcType = reader["odbctype"] == DBNull.Value ? 0 : Convert.ToInt32(reader["odbctype"]);
string cacheDataType = (string)reader["DATA_TYPE"];
//longblobs and longtext might cause overflow so we need to protect the decoding
Int32.TryParse(Convert.ToString(reader["CHARACTER_MAXIMUM_LENGTH"]), out length);
if (length == 0)
{
length = int.MaxValue;
}
IDataTypeInfo datatype = createDataTypeInfo(odbcType, cacheDataType, length, precision, scale, datetimePrecision);
bool isAutoGenerated = "YES".EqualsIgnoreCase((string)reader["AUTO_INCREMENT"]);
bool isPrimaryKey = false;
bool isMandatory = isAutoGenerated || "NO".EqualsIgnoreCase((string)reader["IS_NULLABLE"]);
// Force all "ID" columns to be the primary key. Caché can use composite primary keys and this is not supported
// by OutSystems, Caché always creates the "ID" column, so we use this as the Primary Key.
if (columnName.CompareTo("ID") == 0)
{
isPrimaryKey = true;
isMandatory = true;
}
ITableSourceInfo tableSource = tableSources.First(t => t.Name.EqualsIgnoreCase(tableName));
ITableSourceColumnInfo columnInfo = createColumnInfo(tableSource, columnName, datatype, isMandatory, isPrimaryKey, isAutoGenerated);
columnsInfo.Add(columnInfo);
}
}
}
return(columnsInfo);
}
protected IEnumerable <ITableSourceColumnInfo> GetColumns(IEnumerable <MySQLTableSourceInfo> tableSources, CreateDataTypeInfo createDataTypeInfo,
CreateColumnInfo createColumnInfo)
{
string paramPrefix = DatabaseServices.ExecutionService.ParameterPrefix;
var columnsInfo = new List <ITableSourceColumnInfo>();
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
string tableNames = "'" + tableSources.Select(t => t.Name).StrCat("','") + "'";
string query = string.Format(@"SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, DATA_TYPE, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, COLUMN_KEY, EXTRA , COLUMN_DEFAULT, DATETIME_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA= {0} AND TABLE_NAME in (" + tableNames + @")
ORDER BY ORDINAL_POSITION;", paramPrefix + "schema");
IDbCommand cmd = DatabaseServices.ExecutionService.CreateCommand(conn, query);
DatabaseServices.ExecutionService.CreateParameter(cmd, paramPrefix + "schema", DbType.String, tableSources.First().Database.Name);
cmd.CommandTimeout = QueryTimeout;
using (IDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
string tableName = (string)reader["TABLE_NAME"];
string columnName = (string)reader["COLUMN_NAME"];
string type = (string)reader["DATA_TYPE"];
int length;
//longblobs and longtext might cause overflow so we need to protect the decoding
Int32.TryParse(Convert.ToString(reader["CHARACTER_MAXIMUM_LENGTH"]), out length);
if (length == 0)
{
length = int.MaxValue;
}
//due to mysql bugs on metadata http://bugs.mysql.com/bug.php?id=69042
//will try to parse first from raw data type and fall back to numeric precision metadata
//numeric precision is reliable for floating point data types
string column_type = ((string)reader["COLUMN_TYPE"]);
int precision;
string[] pieces = column_type.Split(new[] { '(', ')' });
if (pieces.Length < 2 || int.TryParse(pieces[1], out precision) == false)
{
precision = reader["NUMERIC_PRECISION"] == DBNull.Value ? 0 : Convert.ToInt32(reader["NUMERIC_PRECISION"]);
}
bool isUnsigned = column_type.Contains("unsigned");
int scale = reader["NUMERIC_SCALE"] == DBNull.Value ? 0 : Convert.ToInt32(reader["NUMERIC_SCALE"]);
int datetimePrecision = reader["DATETIME_PRECISION"] == DBNull.Value ? 0 : Convert.ToInt32(reader["DATETIME_PRECISION"]);
IDataTypeInfo datatype = createDataTypeInfo(type, column_type, length, precision, scale, datetimePrecision, isUnsigned);
bool isAutoGenerated = ((string)reader["EXTRA"]).ContainsIgnoreCase("auto_increment");
bool isPrimaryKey = "PRI".EqualsIgnoreCase((string)reader["COLUMN_KEY"]);
bool isMandatory = isAutoGenerated || "NO".EqualsIgnoreCase((string)reader["IS_NULLABLE"]);
ITableSourceInfo tableInfo = tableSources.First(t => t.Name.EqualsIgnoreCase(tableName));
ITableSourceColumnInfo columnInfo = createColumnInfo(tableInfo, columnName, datatype, isMandatory, isPrimaryKey, isAutoGenerated, isUnsigned);
columnsInfo.Add(columnInfo);
}
}
}
return(columnsInfo);
}
