/// <summary>
/// Gets the System TypeCode of a db column type
/// </summary>
/// <param name="type">db column type</param>
/// <returns></returns>
public static System.TypeCode TypeCode(this DbColumnType type)
{
switch (type)
{
case DbColumnType.Byte: return(System.TypeCode.Byte);
case DbColumnType.Char: return(System.TypeCode.Char);
case DbColumnType.String: return(System.TypeCode.String);
case DbColumnType.Int16: return(System.TypeCode.Int16);
case DbColumnType.Int32: return(System.TypeCode.Int32);
case DbColumnType.Int64: return(System.TypeCode.Int64);
case DbColumnType.Single: return(System.TypeCode.Single);
case DbColumnType.Double: return(System.TypeCode.Double);
case DbColumnType.Decimal: return(System.TypeCode.Decimal);
case DbColumnType.Bool: return(System.TypeCode.Boolean);
default:
return(System.TypeCode.DBNull);
}
}
internal T ExecuteFunction <T>(DbTableDataReader reader,
DbColumnType valueType,
IEnumerable <int> collection)
{
dynamic oneColumnCollection = collection;
//apply quantifiers here
if (Query.Select.Function != TokenType.COUNT)
{
//get real values
//not transformed, need to
var recordValues = new List <object[]>();
foreach (var offset in collection)
{
recordValues.Add(reader.ReadRecord(offset));
}
//read records return only one column
oneColumnCollection = recordValues.Select(cols => cols[0]).Cast <T>();
}
MethodInfo method =
this.GetType().GetMethod(nameof(ApplyFunction), BindingFlags.Instance | BindingFlags.NonPublic);
MethodInfo genMethod = method.MakeGenericMethod(Type.GetType($"System.{valueType}"));
var result = genMethod.Invoke(this, new object[] { oneColumnCollection, Query.Select.Function });
return((T)result);
}
public SelectColumn(DbQuery.Column column, int index)
{
Column = column;
ColumnIndex = column.Meta.Index;
Type = Enum.Parse <DbColumnType>(column.Meta.Type);
Index = index;
}
public KeySet Add(string keyName, DbColumnType keyType, object keyValue)
{
if (string.IsNullOrEmpty(keyName))
{
throw new ArgumentNullException(string.Format("The key name (key type: {0}) cannot be null or empty", keyType));
}
if (keyType == null)
{
throw new ArgumentNullException(string.Format("The key type (key name: {0}) cannot be null", keyName));
}
if (keyValue == null)
{
throw new ArgumentNullException(string.Format("The key value (key name: {0}) cannot be null", keyName));
}
if (keyNameTypeValueSet.ContainsKey(keyName))
{
throw new Exception(string.Format("The key ({0}) already exists", keyName));
}
keyNameTypeValueSet.Add(keyName, Tuple.Create(keyType, keyValue));
return(this);
}
internal PageIndexNode(int flags, int number, io.BinaryReader reader, DbColumnType keyType)
: base(flags, number)
{
//read info
PageSize = reader.ReadInt32();
UniqueValue = (flags & Consts.BTreeUniqueKeyValueFlag) != 0;
//for Unique = true, this's the unique value, otehrwise it's the Value count
Int32 keyValueCount = reader.ReadInt32();
Values = new List <int>();
//read value(s)
if (UniqueValue)
{
//store unique value
Values.Add(keyValueCount);
}
else
{
for (var i = 0; i < keyValueCount; i++)
{
Values.Add(reader.ReadInt32());
}
}
//read key, is an object
Key = (T)keyType.LoadKey(reader);
}
/// <summary>
/// Gets the size in bytes of a database column type, String (-1)
/// </summary>
/// <param name="type">column type</param>
/// <returns></returns>
public static int GetSize(this DbColumnType type)
{
switch (type)
{
case DbColumnType.Bool:
case DbColumnType.None:
default:
return(0);
case DbColumnType.Byte:
case DbColumnType.Char:
return(1);
case DbColumnType.Int16:
return(2);
case DbColumnType.Int32:
case DbColumnType.Single:
return(4);
case DbColumnType.Int64:
case DbColumnType.Double:
return(8);
case DbColumnType.Decimal:
return(16);
case DbColumnType.String:
return(-1);
}
}
private Type GetSystemType(DbColumnType columnType)
{
Type t = null;
switch (columnType)
{
case DbColumnType.INT:
t = typeof(int);
break;
case DbColumnType.DATETIME:
t = typeof(DateTime);
break;
case DbColumnType.NVARCHAR:
t = typeof(string);
break;
case DbColumnType.VARBINARY:
t = typeof(byte[]);
break;
case DbColumnType.BIGINT:
t = typeof(long);
break;
default:
break;
}
return(t);
}
/// <summary>
/// normalize or returns the conversion type for a type check comparison
/// </summary>
/// <param name="type">type</param>
/// <param name="other">other to compare</param>
/// <returns>needed type for successful comparison, otherwise None</returns>
public static DbColumnType Normalize(this DbColumnType type, DbColumnType other)
{
//if equals, return same
if (type == other)
{
return(type);
}
//otherwise atomize
switch (type)
{
case DbColumnType.Byte:
case DbColumnType.Int16:
case DbColumnType.Int32:
case DbColumnType.Int64:
//cast to Int64 if differents
return((other == DbColumnType.Byte ||
other == DbColumnType.Int16 ||
other == DbColumnType.Int32 ||
other == DbColumnType.Int64) ? DbColumnType.Int64 : DbColumnType.None);
case DbColumnType.Single:
case DbColumnType.Double:
case DbColumnType.Decimal:
//cast to decimal if different
return((other == DbColumnType.Single ||
other == DbColumnType.Double ||
other == DbColumnType.Decimal) ? DbColumnType.Decimal : DbColumnType.None);
}
// if DbColumnType.String, DbColumnType.Char drives here then none for these two
return(DbColumnType.None);
}
private static DbColumn ParseColumn(string input)
{
var name = input.Substring(0, input.IndexOf(Space, StringComparison.Ordinal));
var type = DbColumnType.Parse(ExtractBetween(input, Space, Space));
var allowNull = input.IndexOf(@"NOT NULL", StringComparison.OrdinalIgnoreCase) < 0;
var isPrimaryKey = input.IndexOf(@"PRIMARY KEY", StringComparison.OrdinalIgnoreCase) >= 0;
return(new DbColumn(type, name, allowNull, isPrimaryKey));
}
public static SimpleTypes TypeDbToSimple(DbColumnType tp, int scale)
{
if (tp == DbColumnType.Numeric)
{
if (scale > 0)
{
return(SimpleTypes.Float);
}
else
{
return(SimpleTypes.Integer);
}
}
switch (tp)
{
case DbColumnType.Byte:
case DbColumnType.BigInt:
case DbColumnType.Integer:
case DbColumnType.SmallInt:
return(SimpleTypes.Integer);
case DbColumnType.Blob:
return(SimpleTypes.Blob);
case DbColumnType.Boolean:
return(SimpleTypes.Boolean);
case DbColumnType.Char:
case DbColumnType.VarChar:
case DbColumnType.Text:
return(SimpleTypes.String);
case DbColumnType.Date:
return(SimpleTypes.Date);
case DbColumnType.DateTime:
case DbColumnType.DateTimeWithTimeZone:
return(SimpleTypes.DateTime);
case DbColumnType.Time:
case DbColumnType.TimeWithTimeZome:
return(SimpleTypes.Time);
case DbColumnType.Money:
case DbColumnType.Double:
case DbColumnType.Real:
return(SimpleTypes.Float);
case DbColumnType.Geography:
case DbColumnType.Geometry:
return(SimpleTypes.Geometry);
default:
throw new Exception("Unknow data type");
}
}
public DataColumnCollection AddColumn(string columnName, DbColumnType dataType)
{
var parmeter = default(DataColumn);
switch (dataType)
{
case DbColumnType.Int:
parmeter = new DataColumn(columnName, typeof(int));
break;
case DbColumnType.Short:
parmeter = new DataColumn(columnName, typeof(short));
break;
case DbColumnType.Byte:
parmeter = new DataColumn(columnName, typeof(byte));
break;
case DbColumnType.Decimal:
parmeter = new DataColumn(columnName, typeof(decimal));
break;
case DbColumnType.String:
parmeter = new DataColumn(columnName, typeof(string));
break;
case DbColumnType.Bool:
parmeter = new DataColumn(columnName, typeof(bool));
break;
case DbColumnType.DateTime:
parmeter = new DataColumn(columnName, typeof(DateTime));
break;
case DbColumnType.Char:
parmeter = new DataColumn(columnName, typeof(Char));
break;
case DbColumnType.Long:
parmeter = new DataColumn(columnName, typeof(long));
break;
case DbColumnType.UniqueIdentifier:
parmeter = new DataColumn(columnName, typeof(Guid));
break;
case DbColumnType.Time:
parmeter = new DataColumn(columnName, typeof(TimeSpan));
break;
default:
break;
}
this.Add(parmeter);
return(this);
}
public static string GetStr(this DbColumnType type, object value)
{
if (value == null)
{
return(String.Empty);
}
return(type == DbColumnType.String ?
$"'{value}'" :
value.ToString());
}
public static ExactType Create(DbColumnType tp, int precision = 0, int scale = 0, int maxCharLength = 0)
{
ExactType r = new ExactType();
r.MaxTextLength = maxCharLength;
r.Precision = precision;
r.Scale = scale;
r.Type = tp;
return(r);
}
private string BuildSqlStringForValue(object value, DbColumnType type)
{
if (value == null || value.GetType() == typeof(System.DBNull))
{
return("null");
}
// HACK: a special case where the Asset_ID of GovCorp..Asset view is binary type in EJV DSOS,
// but the assetId of Asset table in Luna DB is varchar,
// and we cannot convert the byte[] to string directly using toString method
if (value.GetType() == typeof(byte[]) && (type == DbColumnType.CHAR || type == DbColumnType.VARCHAR || type == DbColumnType.VARCHAR2))
{
return(string.Format("'{0}'", ((byte[])value).ToStringBySybaseStandard()));
}
if (type == DbColumnType.CHAR ||
type == DbColumnType.VARCHAR || type == DbColumnType.TEXT || type == DbColumnType.VARCHAR2)
{
return(string.Format("'{0}'", value.ToString().Replace("'", "''"))); // Escape the single quote in the string text.
}
if (type == DbColumnType.NVARCHAR || type == DbColumnType.NTEXT || type == DbColumnType.NCHAR)
{
return(string.Format("N'{0}'", value.ToString().Replace("'", "''"))); // Escape the single quote in the string text.
}
if (type == DbColumnType.DATETIME || type == DbColumnType.DATETIME2)
{
return(value.ToString() == string.Empty ? "null" : string.Format("'{0}'", ((DateTime)value).ToString(GetDateTimeFormatString(), CultureInfo.InvariantCulture)));
}
if (type == DbColumnType.DATE)
{
return(value.ToString() == string.Empty ? "null" : string.Format("'{0}'", ((DateTime)value).ToString(GetDateFormatString(), CultureInfo.InvariantCulture)));
}
if (type == DbColumnType.TIMESTAMP)
{
return(value.ToString() == string.Empty ? "null" : string.Format("'{0}'", ((DateTime)value).ToString(GetTimestampFormatString(), CultureInfo.InvariantCulture)));
}
if (type == DbColumnType.FLOAT ||
type == DbColumnType.BIGINT ||
type == DbColumnType.INT ||
type == DbColumnType.NUMERIC ||
type == DbColumnType.SMALLINT ||
type == DbColumnType.BINARY ||
type == DbColumnType.REAL ||
type == DbColumnType.DECIMAL)
{
return(value.ToString() == string.Empty ? "null" : value.ToString());
}
if (type == DbColumnType.BIT)
{
return(value.ToString() == string.Empty ? "0" : (Convert.ToInt32(value).ToString()));
}
throw new Exception("The type is not supported in SQL: " + type.ToString());
}
protected override IDataParameter GetDataParameter(string paraName, DbColumnType columnType, object value)
{
var type = OracleType.Number;
switch (columnType)
{
case DbColumnType.INT:
case DbColumnType.DECIMAL:
type = OracleType.Number;
break;
case DbColumnType.DATETIME:
case DbColumnType.DATE:
type = OracleType.DateTime;
break;
case DbColumnType.CHAR:
type = OracleType.Char;
break;
case DbColumnType.VARCHAR:
case DbColumnType.VARCHAR2:
type = OracleType.VarChar;
break;
case DbColumnType.NCHAR:
type = OracleType.NChar;
break;
case DbColumnType.NVARCHAR:
type = OracleType.NVarChar;
break;
case DbColumnType.NTEXT:
type = OracleType.NClob;
break;
case DbColumnType.CLOB:
type = OracleType.Clob;
break;
case DbColumnType.TIMESTAMP:
type = OracleType.Timestamp;
break;
default:
break;
}
return(new OracleParameter(paraName, type)
{
Value = BuildValueForParameters(value, columnType)
});
}
/// <summary>
/// creates an expression numeric operand
/// </summary>
/// <param name="text">text</param>
/// <param name="cast">cast, None if doesnt apply</param>
public NumberOperand(string text, DbColumnType cast)
: base(text, cast)
{
if ((valueType = text.ToNumberType()) == null)
{
throw new ArgumentException($"Invalid number operand: {text}");
}
if (cast != DbColumnType.None && !cast.IsNumeric())
{
throw new ArgumentException($"Invalid numeric cast: {cast}");
}
}
public static int MinParamsByDbType(DbColumnType dbType)
{
if (dbType == DbColumnType.Numeric)
{
return(0);
}
if (dbType == DbColumnType.Char || dbType == DbColumnType.VarChar)
{
return(1);
}
return(0);
}
protected object BuildValueForParameters(object value, DbColumnType type)
{
if (value == null || value.GetType() == typeof(System.DBNull))
{
return(DBNull.Value);
}
if (value.GetType() == typeof(byte[]) && (type == DbColumnType.CHAR || type == DbColumnType.VARCHAR || type == DbColumnType.VARCHAR2))
{
return(string.Format("{0}", ((byte[])value).ToStringBySybaseStandard()));
}
return(value);
}
private SqlDbType GetColumnType(DbColumnType columnType)
{
var typeList = Enum.GetValues(typeof(SqlDbType)).Cast <Enum>().ToList();
foreach (var t in typeList)
{
if (t.ToString().ToUpper() == columnType.ToString())
{
return((SqlDbType)t);
}
}
return(0);
}
private string GetStringValue(DbColumnType type, object value)
{
if (value.GetType() == typeof(byte[]) && (type == DbColumnType.CHAR || type == DbColumnType.VARCHAR))
{
return(((byte[])value).ToStringBySybaseStandard());
}
if (type == DbColumnType.DATETIME && value is DateTime)
{
return(((DateTime)value).ToString("yyyy-MM-dd hh:mm:ss tt"));
}
return(value.ToString());
}
protected override IDataParameter GetDataParameter(string paraName, DbColumnType columnType, object value)
{
var type = SqlDbType.Int;
switch (columnType)
{
case DbColumnType.INT:
type = SqlDbType.Int;
break;
case DbColumnType.DECIMAL:
type = SqlDbType.Decimal;
break;
case DbColumnType.DATETIME:
type = SqlDbType.DateTime;
break;
case DbColumnType.CHAR:
type = SqlDbType.Char;
break;
case DbColumnType.VARCHAR:
type = SqlDbType.VarChar;
break;
case DbColumnType.NCHAR:
type = SqlDbType.NChar;
break;
case DbColumnType.NVARCHAR:
type = SqlDbType.NVarChar;
break;
case DbColumnType.TEXT:
type = SqlDbType.Text;
break;
case DbColumnType.NTEXT:
type = SqlDbType.NText;
break;
default:
break;
}
return(new SqlParameter(paraName, type)
{
Value = value
});
}
public void FindDifferenceColums(Type type, IEnumerable <TableSchemaInformation> dbColumns, IEnumerable <PropertyInfo> entityProps)
{
StringBuilder addBuilder = new StringBuilder();
StringBuilder removeBuilder = new StringBuilder();
var dbColumsList = dbColumns as TableSchemaInformation[] ?? dbColumns.ToArray();
var dbColumnNames = dbColumsList.Select(v => v.column_name).ToList();
var dbColumnTypes = dbColumsList.Select(v => v.data_type).ToList();
var propertyInfos = entityProps as PropertyInfo[] ?? entityProps.ToArray();
var removeProps = dbColumnNames.Except(propertyInfos.Select(v => v.Name)).ToList();
var addProps = propertyInfos.Select(v => v.Name).Except(dbColumnNames).ToList();
foreach (var dbColumn in dbColumsList)
{
var entityProp = propertyInfos.FirstOrDefault(v => v.Name == dbColumn.column_name);
if (entityProp == null)
{
continue;
}
DbColumnType propType = PostgreScriptGenerator.DataTypeMapper()[entityProp.PropertyType];
if (propType.Type == dbColumn.data_type && propType.IsNullable == IsNullableConverter(dbColumn.is_nullable))
{
continue;
}
removeProps.Add(dbColumn.column_name);
addProps.Add(entityProp.Name);
}
foreach (var propString in addProps)
{
var prop = entityProps.FirstOrDefault(v => v.Name == propString);
string script = "ALTER TABLE \"" + type.Name + "\" ADD COLUMN IF NOT EXISTS \"" + propString + "\" " + PostgreScriptGenerator.StaticDataMapper()[prop.PropertyType];
if (!DataHelper.IsNullableType(prop.PropertyType))
{
script += GenerateDefaultValue(type, prop);
}
script += ";";
addBuilder.AppendLine(script);
}
foreach (var propString in removeProps)
{
string script = "ALTER TABLE \"" + type.Name + "\" DROP COLUMN \"" + propString + "\";";
removeBuilder.AppendLine(script);
}
_connection.Execute(removeBuilder.ToString() + addBuilder);
}
private int GetColumnSize(DbColumnType columnType)
{
switch (columnType)
{
case DbColumnType.VARBINARY:
return(int.MaxValue);
case DbColumnType.DATETIME:
return(8);
case DbColumnType.INT:
return(4);
}
return(0);
}
public SqlParameter CreateParameter(string name, DbColumnType data_type, object value)
{
if (_parameters == null)
{
_parameters = new List <SqlParameter>();
}
SqlParameter prm = _parameters.FirstOrDefault(x => x.Name == name);
if (prm == null)
{
prm = new SqlParameter(name, data_type, value);
_parameters.Add(prm);
}
return(prm);
}
/// <summary>
/// creates an expression column operand
/// </summary>
/// <param name="column">column</param>
/// <param name="cast">cast, None if doesnt apply</param>
public ColumnOperand(Column column, DbColumnType cast = DbColumnType.None)
: base(column == null ? String.Empty : column.Identifier(), cast)
{
if ((Column = column) == null)
{
throw new ArgumentException($"Column operand null or empty");
}
Type = Enum.Parse <DbColumnType>(Column.Meta.Type);
if (cast != DbColumnType.None)
{
if ((Type.IsNumeric() && !cast.IsNumeric()))
{
throw new ArgumentException($"cast types: {Type} and {cast} doesnot match");
}
}
}
public static object LoadKey(this DbColumnType keyType, io.BinaryReader reader)
{
switch (keyType)
{
case DbColumnType.Char:
//
return(reader.ReadChar());
case DbColumnType.Byte:
//
return(reader.ReadByte());
case DbColumnType.Int16:
//
return(reader.ReadInt16());
case DbColumnType.Int32:
//
return(reader.ReadInt32());
case DbColumnType.Int64:
//
return(reader.ReadInt64());
case DbColumnType.Single:
//
return(reader.ReadSingle());
case DbColumnType.Double:
//
return(reader.ReadDouble());
case DbColumnType.Decimal:
//
return(reader.ReadDecimal());
case DbColumnType.String:
//
var length = reader.ReadByte();
var chars = reader.ReadChars(length);
return(new string(chars));
default:
return(null);
}
}
public static object CallGeneric(
object classObj,
string methodName,
DbColumnType valueType,
object[] parameters = null,
BindingFlags flags = BindingFlags.Default)
{
var type = classObj.GetType();
MethodInfo method = (flags == BindingFlags.Default) ?
type.GetMethod(methodName) :
type.GetMethod(methodName, flags);
//123816
MethodInfo genMethod = method.MakeGenericMethod(Type.GetType($"System.{valueType}"));
var result = genMethod.Invoke(classObj, parameters);
return(result);
}
public static DbColumnType TypeParse(string datatype)
{
#region
/*
* /// <remarks/>
* @int,
*
* /// <remarks/>
* @string,
*
* /// <remarks/>
* @decimal,
*
* /// <remarks/>
* datetime,
*
* /// <remarks/>
* guid,
*
* /// <remarks/>
* @long,
*/
#endregion
if (string.IsNullOrEmpty(datatype))
{
return(DbColumnType.@string);
}
// if (string.Compare(datatype, [email protected](), true) == 0) return DbColumnType.@int;
DbColumnType type = DbColumnType.@string;
try
{
type = (DbColumnType)Enum.Parse(typeof(DbColumnType), datatype.ToLower());
}
catch (Exception ex)
{
throw ex;
}
return(type);
}
public CastingExpression(DbColumnType type)
: base(ExpressionItemType.Casting)
{
Type = type;
}
/// <summary>
/// creates an expression constant operand
/// </summary>
/// <param name="groupType">main group type</param>
/// <param name="text">text</param>
/// <param name="cast">cast, None if doesnt apply</param>
internal ConstantOperand(string text, DbColumnType cast)
: base(text, cast)
{
}
