protected override List <ColumnDefinition> ExtractColumnsFromTable(string tableName) { var defs = new List <ColumnDefinition>(); tableName = tableName.ToLower(); IDataReader rdr = null; try { rdr = Query(string.Format("desc {0}", tableName), new Dictionary <string, object>()); if (rdr != null) { while (rdr.Read()) { var name = rdr ["Field"]; //var pk = rdr["Key"]; var type = rdr ["Type"]; //var extra = rdr["Extra"]; object defaultValue = rdr ["Default"]; ColumnTypeDef typeDef = ConvertTypeToColumnType(type.ToString()); typeDef.isNull = rdr ["Null"].ToString() == "YES"; typeDef.auto_increment = rdr ["Extra"].ToString().IndexOf("auto_increment", StringComparison.Ordinal) >= 0; typeDef.defaultValue = defaultValue is DBNull ? null : defaultValue.ToString(); defs.Add(new ColumnDefinition { Name = name.ToString(), Type = typeDef, }); } } } catch (Exception e) { MainConsole.Instance.ErrorFormat("[MySQL]: ExtractColumnsFromTable: {0}", e); } finally { try { if (rdr != null) { rdr.Close(); //rdr.Dispose (); } } catch (Exception e) { MainConsole.Instance.DebugFormat("[MySQL]: ExtractColumnsFromTable: {0}", e); } } return(defs); }
protected override List <ColumnDefinition> ExtractColumnsFromTable(string tableName) { List <ColumnDefinition> defs = new List <ColumnDefinition>(); IndexDefinition primary = null; bool isFaux = false; foreach (KeyValuePair <string, IndexDefinition> index in ExtractIndicesFromTable(tableName)) { if (index.Value.Type == IndexType.Primary) { isFaux = index.Key == "#fauxprimary#"; primary = index.Value; break; } } var cmd = PrepReader(string.Format("PRAGMA table_info({0})", tableName)); using (IDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { var name = rdr["name"]; var type = rdr["type"]; object defaultValue = rdr["dflt_value"]; ColumnTypeDef typeDef = ConvertTypeToColumnType(type.ToString()); typeDef.isNull = uint.Parse(rdr["notnull"].ToString()) == 0; typeDef.defaultValue = defaultValue.GetType() == typeof(System.DBNull) ? null : defaultValue.ToString(); if ( uint.Parse(rdr["pk"].ToString()) == 1 && primary != null && isFaux == true && primary.Fields.Length == 1 && primary.Fields[0].ToLower() == name.ToString().ToLower() && (typeDef.Type == ColumnType.Integer || typeDef.Type == ColumnType.TinyInt) ) { typeDef.auto_increment = true; } defs.Add(new ColumnDefinition { Name = name.ToString(), Type = typeDef, }); } rdr.Close(); } CloseReaderCommand(cmd); return(defs); }
public static Func<double, string> GetFormat(ColumnTypeDef columnType) { if (columnType == ColumnTypeDef.Number) return value => value.ToString(new NumberFormatInfo()); if (columnType == ColumnTypeDef.Datetime) { return value => new DateTime((long)value).ToString("u"); } throw new ArgumentException("Unsupported type"); }
public (bool IsParsed, object ParsedObject) TryParse(string value, ColumnTypeDef valueType) { if (valueType == ColumnTypeDef.Number) { var parsed = double.TryParse(value, NumberStyles.Any, _numberFormat, out var result); return(parsed, result); } else if (valueType == ColumnTypeDef.Datetime) { var style = DateTimeStyles.AllowInnerWhite | DateTimeStyles.AllowLeadingWhite | DateTimeStyles.AllowTrailingWhite | DateTimeStyles.AllowWhiteSpaces; var parsed = DateTime.TryParse(value, CultureInfo.InvariantCulture, style, out var result); return(parsed, result); } else if (valueType == ColumnTypeDef.Unknown) { return(true, value); } else { throw new ArgumentException("Unknown type"); } }
public Func <double, string> GetFormat(ColumnTypeDef type, IEnumerable <long> values) { if (type == ColumnTypeDef.Number) { return(val => val.ToString(CultureInfo.CurrentCulture)); } if (type == ColumnTypeDef.Datetime) { var interval = TimeSpan.FromTicks(values.Max() - values.Min()); if (interval < TimeSpan.FromDays(1)) { return(val => new DateTime((long)val).ToString("hh:mm:ss")); } if (interval >= TimeSpan.FromDays(1) && interval < TimeSpan.FromDays(365)) { return(val => new DateTime((long)val).ToShortDateString()); } return(val => new DateTime((long)val).ToString("yyyy")); } throw new ArgumentException("Unsupported type"); }
public override string GetColumnTypeStringSymbol(ColumnTypeDef coldef) { string symbol; switch (coldef.Type) { case ColumnType.Blob: symbol = "BLOB"; break; case ColumnType.LongBlob: symbol = "LONGBLOB"; break; case ColumnType.Boolean: symbol = "TINYINT(1)"; break; case ColumnType.Char: symbol = "CHAR(" + coldef.Size + ")"; break; case ColumnType.Date: symbol = "DATE"; break; case ColumnType.DateTime: symbol = "DATETIME"; break; case ColumnType.Double: symbol = "DOUBLE"; break; case ColumnType.Float: symbol = "FLOAT"; break; case ColumnType.Integer: symbol = "INT(" + coldef.Size + ")" + (coldef.unsigned ? " unsigned" : ""); break; case ColumnType.TinyInt: symbol = "TINYINT(" + coldef.Size + ")" + (coldef.unsigned ? " unsigned" : ""); break; case ColumnType.String: symbol = "VARCHAR(" + coldef.Size + ")"; break; case ColumnType.Text: symbol = "TEXT"; break; case ColumnType.MediumText: symbol = "MEDIUMTEXT"; break; case ColumnType.LongText: symbol = "LONGTEXT"; break; case ColumnType.UUID: symbol = "VARCHAR(36)"; break; case ColumnType.Binary: symbol = "BINARY(" + coldef.Size + ")"; break; default: throw new DataManagerException("Unknown column type."); } return(symbol + (coldef.isNull ? " NULL" : " NOT NULL") + ((coldef.isNull && coldef.defaultValue == null) ? " DEFAULT NULL" : (coldef.defaultValue != null ? " DEFAULT '" + coldef.defaultValue.MySqlEscape() + "'" : "")) + ((coldef.Type == ColumnType.Integer || coldef.Type == ColumnType.TinyInt) && coldef.auto_increment ? " AUTO_INCREMENT" : "")); }
protected override Dictionary <string, IndexDefinition> ExtractIndicesFromTable(string tableName) { Dictionary <string, IndexDefinition> defs = new Dictionary <string, IndexDefinition>(); IndexDefinition primary = new IndexDefinition { Fields = new string[] {}, Type = IndexType.Primary }; string autoIncrementField = null; List <string> fields = new List <string>(); SQLiteCommand cmd = PrepReader(string.Format("PRAGMA table_info({0})", tableName)); using (IDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { if (uint.Parse(rdr["pk"].ToString()) > 0) { fields.Add(rdr["name"].ToString()); if (autoIncrementField == null) { ColumnTypeDef typeDef = ConvertTypeToColumnType(rdr["type"].ToString()); if (typeDef.Type == ColumnType.Integer || typeDef.Type == ColumnType.TinyInt) { autoIncrementField = rdr["name"].ToString(); } } } } rdr.Close(); } CloseReaderCommand(cmd); primary.Fields = fields.ToArray(); cmd = PrepReader(string.Format("PRAGMA index_list({0})", tableName)); Dictionary <string, bool> indices = new Dictionary <string, bool>(); using (IDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { indices[rdr["name"].ToString()] = (uint.Parse(rdr["unique"].ToString()) > 0); } rdr.Close(); } CloseReaderCommand(cmd); bool checkForPrimary = primary.Fields.Length > 0; foreach (KeyValuePair <string, bool> index in indices) { defs[index.Key] = new IndexDefinition { Type = index.Value ? IndexType.Unique : IndexType.Index }; fields = new List <string>(); cmd = PrepReader(string.Format("PRAGMA index_info({0})", index.Key)); using (IDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { fields.Add(rdr["name"].ToString()); } rdr.Close(); } defs[index.Key].Fields = fields.ToArray(); CloseReaderCommand(cmd); if (checkForPrimary && defs[index.Key].Fields.Length == primary.Fields.Length) { uint i = 0; bool isPrimary = true; foreach (string pkField in primary.Fields) { if (defs[index.Key].Fields[i++] != pkField) { isPrimary = false; break; } } if (isPrimary) { // MainConsole.Instance.Warn("[" + Identifier + "]: Primary Key found (" + string.Join(", ", defs[index.Key].Fields) + ")"); defs[index.Key].Type = IndexType.Primary; checkForPrimary = false; } } } if (checkForPrimary == true && autoIncrementField != null) { primary = new IndexDefinition { Fields = new string[1] { autoIncrementField }, Type = IndexType.Primary }; defs["#fauxprimary#"] = primary; } return(defs); }
protected ColumnDefinition ColDef(string name, ColumnTypes columnType) { ColumnTypeDef type = new ColumnTypeDef(); switch (columnType) { case ColumnTypes.Blob: type.Type = ColumnType.Blob; break; case ColumnTypes.Char32: type.Type = ColumnType.Char; type.Size = 32; break; case ColumnTypes.Char36: type.Type = ColumnType.Char; type.Size = 36; break; case ColumnTypes.Char5: type.Type = ColumnType.Char; type.Size = 5; break; case ColumnTypes.Date: type.Type = ColumnType.Date; break; case ColumnTypes.DateTime: type.Type = ColumnType.DateTime; break; case ColumnTypes.Double: type.Type = ColumnType.Double; break; case ColumnTypes.Float: type.Type = ColumnType.Float; break; case ColumnTypes.Integer11: type.Type = ColumnType.Integer; type.Size = 11; break; case ColumnTypes.Integer30: type.Type = ColumnType.Integer; type.Size = 30; break; case ColumnTypes.LongBlob: type.Type = ColumnType.LongBlob; break; case ColumnTypes.LongText: type.Type = ColumnType.LongText; break; case ColumnTypes.MediumText: type.Type = ColumnType.MediumText; break; case ColumnTypes.String: type.Type = ColumnType.Text; break; case ColumnTypes.String1: type.Type = ColumnType.String; type.Size = 1; break; case ColumnTypes.String10: type.Type = ColumnType.String; type.Size = 10; break; case ColumnTypes.String100: type.Type = ColumnType.String; type.Size = 100; break; case ColumnTypes.String1024: type.Type = ColumnType.String; type.Size = 1024; break; case ColumnTypes.String128: type.Type = ColumnType.String; type.Size = 128; break; case ColumnTypes.String16: type.Type = ColumnType.String; type.Size = 16; break; case ColumnTypes.String2: type.Type = ColumnType.String; type.Size = 2; break; case ColumnTypes.String255: type.Type = ColumnType.String; type.Size = 255; break; case ColumnTypes.String30: type.Type = ColumnType.String; type.Size = 30; break; case ColumnTypes.String32: type.Type = ColumnType.String; type.Size = 32; break; case ColumnTypes.String36: type.Type = ColumnType.String; type.Size = 36; break; case ColumnTypes.String45: type.Type = ColumnType.String; type.Size = 45; break; case ColumnTypes.String50: type.Type = ColumnType.String; type.Size = 50; break; case ColumnTypes.String512: type.Type = ColumnType.String; type.Size = 512; break; case ColumnTypes.String64: type.Type = ColumnType.String; type.Size = 64; break; case ColumnTypes.String8196: type.Type = ColumnType.String; type.Size = 8196; break; case ColumnTypes.Text: type.Type = ColumnType.Text; break; case ColumnTypes.TinyInt1: type.Type = ColumnType.TinyInt; type.Size = 1; break; case ColumnTypes.TinyInt4: type.Type = ColumnType.TinyInt; type.Size = 4; break; default: type.Type = ColumnType.Unknown; break; } return(new ColumnDefinition { Name = name, Type = type }); }
public ColumnTypeDef ConvertTypeToColumnType(string typeString) { string tStr = typeString.ToLower(); ColumnTypeDef typeDef = new ColumnTypeDef(); switch (tStr) { case "blob": typeDef.Type = ColumnType.Blob; break; case "longblob": typeDef.Type = ColumnType.LongBlob; break; case "date": typeDef.Type = ColumnType.Date; break; case "datetime": typeDef.Type = ColumnType.DateTime; break; case "double": typeDef.Type = ColumnType.Double; break; case "float": typeDef.Type = ColumnType.Float; break; case "text": typeDef.Type = ColumnType.Text; break; case "mediumtext": typeDef.Type = ColumnType.MediumText; break; case "longtext": typeDef.Type = ColumnType.LongText; break; case "uuid": typeDef.Type = ColumnType.UUID; break; case "integer": typeDef.Type = ColumnType.Integer; typeDef.Size = 11; break; case "binary": typeDef.Type = ColumnType.Binary; break; default: string regexInt = "^int\\((\\d+)\\)( unsigned)?$"; string regexTinyint = "^tinyint\\((\\d+)\\)( unsigned)?$"; string regexChar = "^char\\((\\d+)\\)$"; string regexString = "^varchar\\((\\d+)\\)$"; string regexBinary = "^binary\\((\\d+)\\)$"; Dictionary <string, ColumnType> regexChecks = new Dictionary <string, ColumnType>(5); regexChecks[regexInt] = ColumnType.Integer; regexChecks[regexTinyint] = ColumnType.TinyInt; regexChecks[regexChar] = ColumnType.Char; regexChecks[regexString] = ColumnType.String; regexChecks[regexBinary] = ColumnType.Binary; Match type = Regex.Match("foo", "^bar$"); foreach (KeyValuePair <string, ColumnType> regexCheck in regexChecks) { type = Regex.Match(tStr, regexCheck.Key); if (type.Success) { typeDef.Type = regexCheck.Value; break; } } if (type.Success) { typeDef.Size = uint.Parse(type.Groups[1].Value); typeDef.unsigned = (typeDef.Type == ColumnType.Integer || typeDef.Type == ColumnType.TinyInt) ? (type.Groups.Count == 3 && type.Groups[2].Value == " unsigned") : false; break; } else { throw new Exception( "You've discovered some type that's not reconized by WhiteCore, please place the correct conversion in ConvertTypeToColumnType. Type: " + tStr); } } return(typeDef); }
public abstract string GetColumnTypeStringSymbol(ColumnTypeDef coldef);
public (bool Parsed, List <object> ParsedValues) TryParseAll(IEnumerable <string> values, ColumnTypeDef type) { var parsedValues = new List <object>(); foreach (var value in values) { var(isParsed, parsedObject) = TryParse(value, type); if (!isParsed) { return(false, new List <object>()); } parsedValues.Add(parsedObject); } return(true, parsedValues); }