/// <summary>
/// Gets the columns schema information for the given database table.
/// </summary>
/// <param name="dataTable">The data table to get the schema info for.</param>
/// <returns>Table with schema information regarding its columns.</returns>
public static MySqlColumnsInformationTable GetColumnsInformationTable(this DataTable dataTable)
{
if (dataTable == null)
{
return(null);
}
var schemaInfoTable = new MySqlColumnsInformationTable();
foreach (DataColumn column in dataTable.Columns)
{
var newRow = schemaInfoTable.NewRow();
newRow["Name"] = column.ColumnName;
newRow["Type"] = MySqlDataType.GetMySqlDataType(column.DataType);
newRow["Null"] = column.AllowDBNull ? "YES" : "NO";
newRow["Key"] = dataTable.PrimaryKey.Any(indexCol => indexCol.ColumnName == column.ColumnName) ? "PRI" : string.Empty;
newRow["Default"] = column.DefaultValue != null?column.DefaultValue.ToString() : string.Empty;
newRow["CharSet"] = null;
newRow["Collation"] = null;
newRow["Extra"] = column.AutoIncrement ? MySqlDataColumn.ATTRIBUTE_AUTO_INCREMENT : string.Empty;
schemaInfoTable.Rows.Add(newRow);
}
return(schemaInfoTable);
}
public MySqlColumn(string name, Type type, string mySqlDataType,
string collation, bool allowNull, string key, string defaultValue,
string extra, string privileges, string comment)
{
Name = name;
DataType = type;
MySqlDataType = mySqlDataType.ToLower();
Collation = collation;
AllowNull = allowNull;
Key = key;
DefaultValue = defaultValue;
Extra = extra;
Privileges = privileges;
Comment = comment;
if (key.ToLower() == "pri")
{
IsPrimaryKey = true;
}
if (DataType != typeof(DateTime) || MySqlDataType.Length <= 8)
{
return;
}
var fractionLength = MySqlDataType.Where(char.IsNumber)
.Aggregate("", (current, _dL) => current + Convert.ToString(_dL));
if (fractionLength.Length <= 0)
{
return;
}
_timeFractionLength = 0;
int.TryParse(fractionLength, out _timeFractionLength);
}
/// <summary>
/// Checks if the string value representing a date is a MySQL zero date.
/// </summary>
/// <param name="dateValueAsString">The string value representing a date.</param>
/// <param name="checkIfIntZero">Flag indicating whether a value of 0 should also be treated as a zero date.</param>
/// <returns><c>true</c> if the passed string value is a MySQL zero date, <c>false</c> otherwise.</returns>
public static bool IsMySqlZeroDateTimeValue(this string dateValueAsString, bool checkIfIntZero = false)
{
var isDateValueZero = checkIfIntZero && int.TryParse(dateValueAsString, out var zeroValue) && zeroValue == 0;
MySqlDataType.IsMySqlDateTimeValue(dateValueAsString, out var isDateValueAZeroDate);
return(isDateValueZero || isDateValueAZeroDate);
}
/// <summary>
/// Instantiates a new instance of the <see cref="CustomProperty"/> class.
/// </summary>
/// <param name="name">The property name.</param>
/// <param name="mySqltype">The <see cref="MySqlDataType"/> of the value the property will hold.</param>
/// <param name="value">The property value.</param>
/// <param name="readOnly">Flag indicating whether the property is read only.</param>
/// <param name="visible">Flag indicating whether the property is visible in a property editor.</param>
public CustomProperty(string name, MySqlDataType mySqltype, object value, bool readOnly, bool visible)
{
Name = name;
ReadOnly = readOnly;
MySqlType = mySqltype;
Value = value;
Visible = visible;
}
public static bool TryGetImplicitConversion(MySqlDataType mysqlDataType, Type targetType, out MySqlDataConversionTechnique foundTechnique)
{
if (s_implicitConvPlan.TryGetValue(mysqlDataType, out MySqlTypeConversionPlan convPlan))
{
//found conv for some mysql data type
if (convPlan.TryGetConvTechnique(targetType, out foundTechnique))
{
return(true);
}
}
//not found
foundTechnique = MySqlDataConversionTechnique.Unknown;
return(false);
}
/// <summary>
/// Assembles a SET statement that declares a user variable that contains the given <see cref="MySqlParameter"/> value.
/// </summary>
/// <param name="parameter">A <see cref="MySqlParameter"/> object.</param>
/// <param name="firstParameter">When <c>true</c> the SET token is prepended, otherwise a comma is.</param>
/// <returns>A SET statement that declares a user variable that contains the given <see cref="MySqlParameter"/> value.</returns>
public static string GetSetStatement(this MySqlParameter parameter, bool firstParameter = true)
{
if (parameter == null)
{
return(string.Empty);
}
var mySqlDataType = MySqlDataType.FromMySqlDbType(parameter.MySqlDbType);
bool requireQuotes = mySqlDataType.RequiresQuotesForValue;
return(string.Format("{0} @{1} = {2}{3}{2}",
firstParameter ? "SET" : ",",
parameter.ParameterName,
requireQuotes ? "'" : string.Empty,
requireQuotes ? parameter.Value.ToString().EscapeDataValueString() : parameter.Value));
}
/// <summary>
/// Returns a table containing schema information for columns contained in a MySQL table with the given name.
/// </summary>
/// <param name="connection">MySQL Workbench connection to a MySQL server instance selected by users.</param>
/// <param name="schemaName">The schema the MySQL table belongs to.</param>
/// <param name="tableName">The name of a MySQL table.</param>
/// <param name="beautifyDataTypes">Flag indicating whether the data types are camel cased as shown in the Export Data data type combo box.</param>
/// <returns>A table containing schema information for columns contained in a MySQL table with the given name.</returns>
public static MySqlColumnsInformationTable GetColumnsInformationTable(this MySqlWorkbenchConnection connection, string schemaName, string tableName, bool beautifyDataTypes = false)
{
if (connection == null)
{
return(null);
}
schemaName = string.IsNullOrEmpty(schemaName) ? connection.Schema : schemaName;
var schemaTable = connection.GetSchemaCollection("Columns", null, schemaName, tableName);
if (schemaTable == null)
{
return(null);
}
var columnsInfoTable = new MySqlColumnsInformationTable(schemaTable.TableName);
foreach (DataRow row in schemaTable.Rows)
{
string dataType = row["COLUMN_TYPE"].ToString();
if (beautifyDataTypes)
{
var mySqlDataType = new MySqlDataType(dataType, false);
dataType = mySqlDataType.FullType;
}
var infoRow = columnsInfoTable.NewRow();
infoRow["Name"] = row["COLUMN_NAME"];
infoRow["Type"] = dataType;
infoRow["Null"] = row["IS_NULLABLE"];
infoRow["Key"] = row["COLUMN_KEY"];
infoRow["Default"] = row["COLUMN_DEFAULT"];
infoRow["CharSet"] = row["CHARACTER_SET_NAME"];
infoRow["Collation"] = row["COLLATION_NAME"];
infoRow["Extra"] = row["EXTRA"];
columnsInfoTable.Rows.Add(infoRow);
}
return(columnsInfoTable);
}
public MySqlTypeConversionPlan(MySqlDataType sourceMySqlType)
{
this.SourceMySqlType = sourceMySqlType;
}
/// <summary>
/// Gets a corresponding <see cref="MySqlDisplayDataType"/> from the given <see cref="MySqlDataType"/>.
/// </summary>
/// <param name="fromMySqlDataType">A <see cref="MySqlDataType"/> instance.</param>
/// <returns>A corresponding <see cref="MySqlDisplayDataType"/>.</returns>
public static MySqlDisplayDataType GetDisplayType(MySqlDataType fromMySqlDataType)
{
return(fromMySqlDataType == null ? null : DataTypesList.FirstOrDefault(dispType => dispType.Name.Equals(fromMySqlDataType.TypeName, StringComparison.InvariantCultureIgnoreCase)));
}
public MySqlTypeConversionPlan(MySqlDataType sourceMySqlType)
{
this.SourceMySqlType = sourceMySqlType;
}
public static bool TryGetImplicitConversion(MySqlDataType mysqlDataType, Type targetType, out MySqlDataConversionTechnique foundTechnique)
{
MySqlTypeConversionPlan convPlan;
if (implicitConvPlan.TryGetValue(mysqlDataType, out convPlan))
{
//found conv for some mysql data type
if (convPlan.TryGetConvTechnique(targetType, out foundTechnique))
{
return true;
}
}
//not found
foundTechnique = MySqlDataConversionTechnique.Unknown;
return false;
}
MyStructData ReadCurrentRowTextProtocol(MySqlFieldDefinition f)
{
BufferReader r = this.bufferReader;
MyStructData data = new MyStructData();
MySqlDataType type = (MySqlDataType)f.FieldType;
string numberString = null;
switch (type)
{
case MySqlDataType.TIMESTAMP:
case MySqlDataType.DATE:
case MySqlDataType.DATETIME:
case MySqlDataType.NEWDATE:
{
QueryParsingConfig qparsingConfig = _queryParsingConf;
tmpStringBuilder.Length = 0; //clear
data.myString = r.ReadLengthCodedString(this.StringConverter);
data.type = type;
if (data.myString == null)
{
data.type = MySqlDataType.NULL;
return(data);
}
if (qparsingConfig.DateStrings)
{
return(data);
}
if (data.myString == "0000-00-00")
{
data.myDateTime = DateTime.MinValue; //?
data.type = type;
return(data);
}
//-------------------------------------------------------------
// var originalString = dateString;
// if (field.type === Types.DATE) {
// dateString += ' 00:00:00';
// }
tmpStringBuilder.Append(data.myString);
//string originalString = dateString;
if (type == MySqlDataType.DATE)
{
tmpStringBuilder.Append(" 00:00:00");
}
// if (timeZone !== 'local') {
// dateString += ' ' + timeZone;
// }
if (!qparsingConfig.UseLocalTimeZone)
{
tmpStringBuilder.Append(' ' + qparsingConfig.TimeZone);
}
//var dt;
// dt = new Date(dateString);
// if (isNaN(dt.getTime())) {
// return originalString;
// }
data.myDateTime = DateTime.Parse(tmpStringBuilder.ToString(),
System.Globalization.CultureInfo.InvariantCulture);
data.type = type;
tmpStringBuilder.Length = 0; //clear
}
return(data);
case MySqlDataType.TINY:
case MySqlDataType.SHORT:
case MySqlDataType.LONG:
case MySqlDataType.INT24:
case MySqlDataType.YEAR:
//TODO: review here,
data.myString = numberString = r.ReadLengthCodedString(this.StringConverter);
if (numberString == null ||
(f.IsZeroFill && numberString[0] == '0') ||
numberString.Length == 0)
{
data.type = MySqlDataType.NULL;
}
else
{
data.myInt32 = Convert.ToInt32(numberString);
data.type = type;
}
return(data);
case MySqlDataType.FLOAT:
case MySqlDataType.DOUBLE:
data.myString = numberString = r.ReadLengthCodedString(this.StringConverter);
if (numberString == null || (f.IsZeroFill && numberString[0] == '0'))
{
data.type = MySqlDataType.NULL;
}
else
{
data.myDouble = Convert.ToDouble(numberString);
data.type = type;
}
return(data);
// return (numberString === null || (field.zeroFill && numberString[0] == "0"))
// ? numberString : Number(numberString);
case MySqlDataType.NEWDECIMAL:
case MySqlDataType.LONGLONG:
// numberString = parser.parseLengthCodedString();
// return (numberString === null || (field.zeroFill && numberString[0] == "0"))
// ? numberString
// : ((supportBigNumbers && (bigNumberStrings || (Number(numberString) > IEEE_754_BINARY_64_PRECISION)))
// ? numberString
// : Number(numberString));
QueryParsingConfig config = _queryParsingConf;
data.myString = numberString = r.ReadLengthCodedString(this.StringConverter);
if (numberString == null || (f.IsZeroFill && numberString[0] == '0'))
{
data.type = MySqlDataType.NULL;
}
else if (config.SupportBigNumbers &&
(config.BigNumberStrings || (Convert.ToInt64(numberString) > Packet.IEEE_754_BINARY_64_PRECISION)))
{
//store as string ?
//TODO: review here again
data.myString = numberString;
data.type = type;
throw new NotSupportedException();
}
else if (type == MySqlDataType.LONGLONG)
{
data.myInt64 = Convert.ToInt64(numberString);
data.type = type;
}
else //decimal
{
data.myDecimal = Convert.ToDecimal(numberString);
data.type = type;
}
return(data);
case MySqlDataType.BIT:
data.myBuffer = r.ReadLengthCodedBuffer();
data.type = type;
return(data);
// return parser.parseLengthCodedBuffer();
case MySqlDataType.STRING:
case MySqlDataType.VAR_STRING:
case MySqlDataType.TINY_BLOB:
case MySqlDataType.MEDIUM_BLOB:
case MySqlDataType.LONG_BLOB:
case MySqlDataType.BLOB:
if (f.MarkedAsBinary)
{
data.myBuffer = r.ReadLengthCodedBuffer();
data.type = (data.myBuffer != null) ? type : MySqlDataType.NULL;
}
else
{
data.myString = r.ReadLengthCodedString(this.StringConverter);
data.type = (data.myString != null) ? type : MySqlDataType.NULL;
}
return(data);
// return (field.charsetNr === Charsets.BINARY)
// ? parser.parseLengthCodedBuffer()
// : parser.parseLengthCodedString();
case MySqlDataType.GEOMETRY:
//TODO: unfinished
data.type = MySqlDataType.GEOMETRY;
return(data);
default:
data.myString = r.ReadLengthCodedString(this.StringConverter);
data.type = type;
return(data);
}
}
MyStructData ReadCurrentRowBinaryProtocol(MySqlFieldDefinition f)
{
MySqlDataType fieldType = (MySqlDataType)f.FieldType;
MyStructData myData = new MyStructData();
BufferReader r = this.bufferReader;
switch (fieldType)
{
case MySqlDataType.TIMESTAMP: //
case MySqlDataType.DATE: //
case MySqlDataType.DATETIME: //
case MySqlDataType.NEWDATE: //
r.ReadLengthCodedDateTime(out myData.myDateTime);
myData.type = fieldType;
return(myData);
case MySqlDataType.TINY: //length = 1;
myData.myInt32 = r.U1();
myData.type = fieldType;
return(myData);
case MySqlDataType.SHORT: //length = 2;
case MySqlDataType.YEAR: //length = 2;
myData.myInt32 = (int)r.U2();
myData.type = fieldType;
return(myData);
case MySqlDataType.INT24:
case MySqlDataType.LONG: //length = 4;
myData.myInt32 = (int)r.U4();
myData.type = fieldType;
return(myData);
case MySqlDataType.FLOAT:
myData.myDouble = r.ReadFloat();
myData.type = fieldType;
return(myData);
case MySqlDataType.DOUBLE:
myData.myDouble = r.ReadDouble();
myData.type = fieldType;
return(myData);
case MySqlDataType.NEWDECIMAL:
myData.myDecimal = r.ReadDecimal();
myData.type = fieldType;
return(myData);
case MySqlDataType.LONGLONG:
myData.myInt64 = r.ReadInt64();
myData.type = fieldType;
return(myData);
case MySqlDataType.VARCHAR:
myData.myString = r.ReadLengthCodedString(this.StringConverter);
myData.type = fieldType;
return(myData);
case MySqlDataType.BIT:
myData.myBuffer = r.ReadLengthCodedBuffer();
myData.type = fieldType;
return(myData);
case MySqlDataType.STRING:
case MySqlDataType.VAR_STRING:
case MySqlDataType.TINY_BLOB:
case MySqlDataType.MEDIUM_BLOB:
case MySqlDataType.LONG_BLOB:
case MySqlDataType.BLOB:
if (f.MarkedAsBinary)
{
myData.myBuffer = r.ReadLengthCodedBuffer();
}
else
{
myData.myString = r.ReadLengthCodedString(this.StringConverter);
}
myData.type = fieldType;
return(myData);
case MySqlDataType.GEOMETRY:
throw new NotSupportedException();
default:
myData.myBuffer = r.ReadLengthCodedBuffer();
myData.type = MySqlDataType.NULL;
return(myData);
}
}
/// <summary>
/// Prepares the procedure parameters needed to call the MySQL procedure.
/// </summary>
public void InitializeParameters()
{
var parametersTable = Connection.GetSchemaInformation(SchemaInformationType.ProcedureParameters, true, null, Connection.Schema, Name);
if (parametersTable == null)
{
return;
}
try
{
var parametersCount = parametersTable.Rows.Count;
Parameters = new List <Tuple <string, MySqlParameter> >(parametersCount);
for (var paramIdx = 0; paramIdx < parametersCount; paramIdx++)
{
var dr = parametersTable.Rows[paramIdx];
var dataType = dr["DATA_TYPE"].ToString().ToLowerInvariant();
var paramName = dr["PARAMETER_NAME"].ToString();
var paramDirection = ParameterDirection.Input;
var paramSize = dr["CHARACTER_MAXIMUM_LENGTH"] != null && dr["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value
? Convert.ToInt32(dr["CHARACTER_MAXIMUM_LENGTH"])
: 0;
var paramPrecision = dr["NUMERIC_PRECISION"] != null && dr["NUMERIC_PRECISION"] != DBNull.Value
? Convert.ToByte(dr["NUMERIC_PRECISION"])
: (byte)0;
var paramScale = dr["NUMERIC_SCALE"] != null && dr["NUMERIC_SCALE"] != DBNull.Value
? Convert.ToByte(dr["NUMERIC_SCALE"])
: (byte)0;
var paramUnsigned = dr["DTD_IDENTIFIER"].ToString().Contains("unsigned", StringComparison.InvariantCultureIgnoreCase);
var paramDirectionStr = paramName != "RETURN_VALUE"
? dr["PARAMETER_MODE"].ToString().ToLowerInvariant()
: "return";
switch (paramDirectionStr)
{
case "in":
paramDirection = ParameterDirection.Input;
break;
case "out":
paramDirection = ParameterDirection.Output;
break;
case "inout":
paramDirection = ParameterDirection.InputOutput;
break;
case "return":
paramDirection = ParameterDirection.ReturnValue;
break;
}
var mySqlType = new MySqlDataType(dataType, true);
if (paramUnsigned)
{
mySqlType.Unsigned = true;
}
var mySqlDbType = mySqlType.MySqlDbType == MySqlDbType.Geometry ? MySqlDbType.Blob : mySqlType.MySqlDbType;
Parameters.Add(new Tuple <string, MySqlParameter>(dataType,
new MySqlParameter(paramName, mySqlDbType, paramSize, paramDirection, false, paramPrecision, paramScale, null, DataRowVersion.Current, mySqlType.TypeDefaultValue)));
}
}
catch (Exception ex)
{
Logger.LogException(ex, true, Resources.ProcedureParametersInitializationError);
}
}
/// <summary>
/// Prepares the procedure parameters needed to call the MySQL procedure.
/// </summary>
public void InitializeParameters()
{
var parametersTable = Connection.GetSchemaCollection("Procedure Parameters", null, Connection.Schema, Name);
if (parametersTable == null)
{
return;
}
try
{
var parametersCount = parametersTable.Rows.Count;
Parameters = new List <Tuple <string, MySqlParameter> >(parametersCount);
for (int paramIdx = 0; paramIdx < parametersCount; paramIdx++)
{
DataRow dr = parametersTable.Rows[paramIdx];
string dataType = dr["DATA_TYPE"].ToString().ToLowerInvariant();
string paramName = dr["PARAMETER_NAME"].ToString();
ParameterDirection paramDirection = ParameterDirection.Input;
int paramSize = dr["CHARACTER_MAXIMUM_LENGTH"] != null && dr["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value
? Convert.ToInt32(dr["CHARACTER_MAXIMUM_LENGTH"])
: 0;
byte paramPrecision = dr["NUMERIC_PRECISION"] != null && dr["NUMERIC_PRECISION"] != DBNull.Value
? Convert.ToByte(dr["NUMERIC_PRECISION"])
: (byte)0;
byte paramScale = dr["NUMERIC_SCALE"] != null && dr["NUMERIC_SCALE"] != DBNull.Value
? Convert.ToByte(dr["NUMERIC_SCALE"])
: (byte)0;
bool paramUnsigned = dr["DTD_IDENTIFIER"].ToString().Contains("unsigned", StringComparison.InvariantCultureIgnoreCase);
string paramDirectionStr = paramName != "RETURN_VALUE"
? dr["PARAMETER_MODE"].ToString().ToLowerInvariant()
: "return";
switch (paramDirectionStr)
{
case "in":
paramDirection = ParameterDirection.Input;
break;
case "out":
paramDirection = ParameterDirection.Output;
break;
case "inout":
paramDirection = ParameterDirection.InputOutput;
break;
case "return":
paramDirection = ParameterDirection.ReturnValue;
break;
}
var mySqlType = new MySqlDataType(dataType, true);
if (paramUnsigned)
{
mySqlType.Unsigned = true;
}
Parameters.Add(new Tuple <string, MySqlParameter>(dataType,
new MySqlParameter(paramName, mySqlType.MySqlDbType, paramSize, paramDirection, false, paramPrecision, paramScale, null, DataRowVersion.Current, mySqlType.TypeDefaultValue)));
}
}
catch (Exception ex)
{
MiscUtilities.ShowCustomizedErrorDialog(Resources.ProcedureParametersInitializationError, ex.Message, true);
MySqlSourceTrace.WriteAppErrorToLog(ex);
}
}
