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