/// <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); } }