public override AField typeStrToField(string typestr)
{
AField a = new AField();
switch (typestr.ToLower())
{
case "int":
a.type = ABSTRACTDATATYPES.ASmallInteger;
break;
/*
case "int":
a.type = ABSTRACTDATATYPES.ASmallInteger;
break;
case "int":
a.type = ABSTRACTDATATYPES.ASmallInteger;
break;**/
case "tinyint":
a.type = ABSTRACTDATATYPES.ASmallInteger;
break;
case "bigint":
a.type = ABSTRACTDATATYPES.ALargeInteger;
break;
case "string":
case "varchar":
case "nvarchar":
case "text":
a.type = ABSTRACTDATATYPES.AString;
break;
case "bit":
a.type = ABSTRACTDATATYPES.ABool;
break;
case "image":
a.type = ABSTRACTDATATYPES.AData;
break;
case "char":
a.type = ABSTRACTDATATYPES.AChar;
break;
case "datetime":case "smalldatetime":
a.type = ABSTRACTDATATYPES.ADateTime;
break;
}
return a;
}
public override string buildTableUpdateStatements(ATable tbl)
{
string returnCode = "";
// Create a carbon copy of the original table
// then compare it to this table, analyse the differences.
ATable origTable = this._dbObj.getTableObject(tbl.name);
ATable newTable = tbl;
// Get all fields that have been removed.
System.Collections.ArrayList tempList = SQLConsole.Data.utils.intDiff(origTable.getFieldList(), newTable.getFieldList());
// Get all fields that have been added.
tempList = SQLConsole.Data.utils.intDiff(newTable.getFieldList(), origTable.getFieldList());
AField[] additionsList = new AField[tempList.Count];
tempList.ToArray().CopyTo(additionsList, 0);
foreach (AField addField in additionsList)
{
returnCode += "alter table " + tbl.name + " add " + addField.name + " " + this._dbObj.GetDatabaseProvider().fieldToTypeStr(addField) + ";";
}
// Fields that are the same should be scanned for differences...
foreach (AField fld in newTable.getFieldList())
{
if (fld.altermode == ABSTRACTMODIFYACTION.DropColumn)
returnCode += "alter table " + tbl.name + " drop column " + fld.name + ";";
else if (fld.altermode == ABSTRACTMODIFYACTION.FieldModify)
returnCode += "alter table " + tbl.name + " alter column " + fld.name + " " + this._dbObj.GetDatabaseProvider().fieldToTypeStr(fld) + ";";
}
return returnCode;
}
public override string fieldToTypeStr(AField fieldstruct)
{
string dTypeStr = "";
switch (fieldstruct.type)
{
case ABSTRACTDATATYPES.AString:
dTypeStr = "varchar";
break;
case ABSTRACTDATATYPES.ASmallInteger:
// mysql has different datatypes for lengths...
/*if (fieldstruct.maxsize < 4)
dTypeStr = "SMALLINT"; //This would be a tinyint
//however mysql's odbc driver
//links tinyint as boolean...
else if (fieldstruct.maxsize <= 6)
dTypeStr = "SMALLINT";
else if (fieldstruct.maxsize <= 11)
dTypeStr = "INT";
else if (fieldstruct.maxsize <= 20)
dTypeStr = "BIGINT";
else**/
dTypeStr = "INT";
break;
case ABSTRACTDATATYPES.ABool:
dTypeStr = "BIT";
break;
case ABSTRACTDATATYPES.AData:
dTypeStr = "IMAGE";
break;
case ABSTRACTDATATYPES.ADateTime:
dTypeStr = "DATETIME";
break;
case ABSTRACTDATATYPES.AChar:
dTypeStr = "CHAR";
break;
}
if ((fieldstruct.maxsize != -1) && (fieldstruct.type != ABSTRACTDATATYPES.ASmallInteger) && (fieldstruct.type != ABSTRACTDATATYPES.ABool) && (fieldstruct.type != ABSTRACTDATATYPES.AData) && (fieldstruct.type != ABSTRACTDATATYPES.ADateTime))
{
dTypeStr += "(" + fieldstruct.maxsize + ")";
}
if (fieldstruct.modifiers != null)
{
foreach (SQLConsole.Data.ABSTRACTFIELDMODIFIERS fieldMod in fieldstruct.modifiers)
{
switch (fieldMod)
{
case ABSTRACTFIELDMODIFIERS.AutoIncrement:
dTypeStr += " IDENTITY(1,1)";
break;
case ABSTRACTFIELDMODIFIERS.PrimaryKey:
dTypeStr += " PRIMARY KEY";
break;
case ABSTRACTFIELDMODIFIERS.NotNull:
dTypeStr += " NOT NULL";
break;
case ABSTRACTFIELDMODIFIERS.Clustered:
dTypeStr += " CLUSTERED";
break;
case ABSTRACTFIELDMODIFIERS.ForeignKey:
string fpk = this._dbObj.GetTableCache().getCachedTable((string)fieldstruct.value).getPrimaryKey().name;
dTypeStr += " references " + fieldstruct.value + "(" + fpk + ")";
break;
}
}
}
return dTypeStr;
}
public override AField typeStrToField(string typestr)
{
AField a = new AField();
switch (typestr.ToLower())
{
case "counter":
a.type = ABSTRACTDATATYPES.ASmallInteger;
break;
case "double":
a.type = ABSTRACTDATATYPES.ASmallInteger;
break;
case "string":
case "varchar":
case "nvarchar":
a.type = ABSTRACTDATATYPES.AString;
break;
case "bit":
a.type = ABSTRACTDATATYPES.ABool;
break;
}
return a;
}
public void addField(AField field)
{
this._fieldObjects.Add(field);
}
public AField addField(string name, ABSTRACTFIELDMODIFIERS modifier, string foreignTable)
{
if (modifier != ABSTRACTFIELDMODIFIERS.ForeignKey)
{
throw (new Exception("Method tried to create a foreign key with the wrong modifier type."));
}
else
{
AField nField = new AField(name, foreignTable);
nField.modifiers = new ABSTRACTFIELDMODIFIERS[] { modifier };
nField.type = this._dbProvider.GetTableCache().getCachedTable(foreignTable).getPrimaryKey().type;
nField.maxsize = this._dbProvider.GetTableCache().getCachedTable(foreignTable).getPrimaryKey().maxsize;
this._fieldlist.Add(nField);
return nField;
}
}
public override string fieldToTypeStr(AField fieldstruct)
{
string dTypeStr = "";
switch (fieldstruct.type)
{
case ABSTRACTDATATYPES.AString:
dTypeStr = "TEXT";
break;
case ABSTRACTDATATYPES.ASmallInteger:
dTypeStr = "number";
break;
case ABSTRACTDATATYPES.ABool:
dTypeStr = "bit";
break;
}
if ((fieldstruct.maxsize != -1) && (fieldstruct.type != ABSTRACTDATATYPES.ASmallInteger) && (fieldstruct.type != ABSTRACTDATATYPES.ABool))
{
dTypeStr += "(" + fieldstruct.maxsize + ")";
}
if(fieldstruct.modifiers != null) {
foreach (SQLConsole.Data.ABSTRACTFIELDMODIFIERS fieldMod in fieldstruct.modifiers)
{
switch (fieldMod)
{
case ABSTRACTFIELDMODIFIERS.AutoIncrement:
// Access does things a bit different, so set to autonumber
dTypeStr = "COUNTER";
break;
case ABSTRACTFIELDMODIFIERS.PrimaryKey:
dTypeStr += " CONSTRAINT PrimaryKey PRIMARY KEY";
break;
case ABSTRACTFIELDMODIFIERS.NotNull:
dTypeStr += " NOT NULL";
break;
case ABSTRACTFIELDMODIFIERS.Clustered:
// No cluster support in msaccess.
break;
}
}
}
return dTypeStr;
}
public AField addField(string name, ABSTRACTDATATYPES type)
{
AField nField = new AField();
nField.name = name;
nField.type = type;
this._fieldlist.Add(nField);
return nField;
}
public void setFieldByName(string name, AField fld)
{
for(int i=0;i<=this._fieldlist.Count;i++)
{
AField field = (AField)this._fieldlist[i];
if (field.name.ToLower() == name.ToLower())
{
this._fieldlist[i] = fld;
return;
}
}
}
public abstract string fieldToTypeStr(AField fieldstruct);
public override AField typeStrToField(string typestr)
{
string sType = typestr;
string sSize = "";
if (typestr.IndexOf('(') >= 0)
{
sType = typestr.Substring(0, typestr.IndexOf('('));
sSize = typestr.Substring(typestr.IndexOf('(')+1).Substring(0, typestr.Substring(typestr.IndexOf('(')+1).IndexOf(')'));
}
AField a = new AField();
switch (sType.ToLower())
{
case "int":
a.type = ABSTRACTDATATYPES.ASmallInteger;
break;
case "decimal":
a.type = ABSTRACTDATATYPES.AFloat;
break;
case "boolean":
a.type = ABSTRACTDATATYPES.ABool;
break;
case "string":case "varchar":case "nvarchar":case "text":
a.type = ABSTRACTDATATYPES.AString;
break;
case "blob":
a.type = ABSTRACTDATATYPES.AData;
break;
case "datetime":
a.type = ABSTRACTDATATYPES.ADateTime;
break;
}
if (sSize != "")
{
// size contains precision information...
if (sSize != "" && sSize.Contains(",") == false)
{
a.maxsize = Convert.ToInt32(sSize);
}
else
{
a.maxsize = Convert.ToInt32(sSize.Substring(0, sSize.IndexOf(",")));
a.precision = Convert.ToInt32(sSize.Substring(sSize.IndexOf(",") + 1));
}
}
return a;
}
public override string fieldToTypeStr(AField fieldstruct)
{
string dTypeStr = "";
switch (fieldstruct.type)
{
case ABSTRACTDATATYPES.AString:
dTypeStr = "varchar";
break;
case ABSTRACTDATATYPES.ASmallInteger:
dTypeStr = "INT";
break;
case ABSTRACTDATATYPES.ABool:
dTypeStr = "boolean";
break;
case ABSTRACTDATATYPES.AData:
dTypeStr = "blob";
break;
case ABSTRACTDATATYPES.ADateTime:
dTypeStr = "BIGINT";
break;
case ABSTRACTDATATYPES.AFloat:
dTypeStr = "decimal";
break;
}
if ((fieldstruct.maxsize != -1) && (fieldstruct.type != ABSTRACTDATATYPES.ASmallInteger) && (fieldstruct.type != ABSTRACTDATATYPES.ABool) && (fieldstruct.type != ABSTRACTDATATYPES.AData) && (fieldstruct.type != ABSTRACTDATATYPES.ADateTime))
{
if (fieldstruct.precision > 0)
{
dTypeStr += "(" + fieldstruct.maxsize + ", "+fieldstruct.precision+")";
}
else
{
dTypeStr += "(" + fieldstruct.maxsize + ")";
}
}
if (fieldstruct.modifiers != null)
{
foreach (SQLConsole.Data.ABSTRACTFIELDMODIFIERS fieldMod in fieldstruct.modifiers)
{
switch (fieldMod)
{
case ABSTRACTFIELDMODIFIERS.AutoIncrement:
dTypeStr += " auto_increment";
break;
case ABSTRACTFIELDMODIFIERS.PrimaryKey:
// This is done differently in mysql. it is set at the end of the statement
break;
case ABSTRACTFIELDMODIFIERS.NotNull:
dTypeStr += " NOT NULL";
break;
case ABSTRACTFIELDMODIFIERS.Clustered: //Not in mysql
break;
}
}
}
if ((string)fieldstruct.defaultval != "" && fieldstruct != null)
dTypeStr += " default '"+fieldstruct.defaultval+"'";
return dTypeStr;
}
public override string buildTableUpdateStatements(ATable tbl)
{
string returnCode = "";
string indexstring = "";
string pkstring = "";
// Create a carbon copy of the original table
// then compare it to this table, analyse the differences.
ATable origTable = this._dbObj.GetTableCache().getCachedTable(tbl.name);
ATable newTable = tbl;
// Get all fields that have been removed.
System.Collections.ArrayList tempList = SQLConsole.Data.utils.intDiff(origTable.getFieldList(), newTable.getFieldList());
// Get all fields that have been added.
tempList = SQLConsole.Data.utils.intDiff(newTable.getFieldList(), origTable.getFieldList());
AField[] additionsList = new AField[tempList.Count];
tempList.ToArray().CopyTo(additionsList, 0);
foreach (AField addField in additionsList)
{
returnCode += "alter table " + tbl.name + " add " + addField.name + " " + this._dbObj.GetDatabaseProvider().fieldToTypeStr(addField) + ";";
}
// Fields that are the same should be scanned for differences...
foreach (AField fld in newTable.getFieldList())
{
if (fld.hasModifier(ABSTRACTFIELDMODIFIERS.IndexKey) && !origTable.getFieldByName(fld.name).hasModifier(ABSTRACTFIELDMODIFIERS.IndexKey))
indexstring = "alter table " + newTable.name + " add index (" + fld.name + ");";
if (fld.hasModifier(ABSTRACTFIELDMODIFIERS.PrimaryKey) && !origTable.getFieldByName(fld.name).hasModifier(ABSTRACTFIELDMODIFIERS.PrimaryKey))
indexstring = "alter table " + newTable.name + " add primary key (" + fld.name + ");";
if (fld.altermode == ABSTRACTMODIFYACTION.DropColumn)
returnCode += "alter table " + tbl.name + " drop column " + fld.name + ";";
else if (fld.altermode == ABSTRACTMODIFYACTION.FieldModify)
returnCode += "alter table " + tbl.name + " modify " + fld.name + " " + this._dbObj.GetDatabaseProvider().fieldToTypeStr(fld) + ";";
}
// mysql wants seperate index and primary key alterations...
// do them here.
returnCode += indexstring;
returnCode += pkstring;
return returnCode;
}
public override AField typeStrToField(string typestr)
{
string sType = typestr.Trim();
string sSize = "";
AField a = new AField();
if (typestr.IndexOf('(') >= 0)
{
sType = typestr.Substring(0, typestr.IndexOf('(')).Trim();
sSize = typestr.Substring(typestr.IndexOf('(') + 1).Substring(0, typestr.Substring(typestr.IndexOf('(') + 1).IndexOf(')'));
}
if (typestr.Contains(" "))
{
string[] tmp = typestr.Split(' ');
if (tmp.GetLength(0) > 2)
{ //contains modifiers
sType = tmp[1];
if (typestr.ToLower().Contains("primary key"))
{
a.addModifier(ABSTRACTFIELDMODIFIERS.PrimaryKey);
}
if (typestr.ToLower().Contains("auto_increment"))
{
a.addModifier(ABSTRACTFIELDMODIFIERS.AutoIncrement);
}
}
}
switch (sType.ToLower())
{
case "int":case "bit":
a.type = ABSTRACTDATATYPES.ASmallInteger;
break;
case "decimal":
a.type = ABSTRACTDATATYPES.AFloat;
break;
case "boolean":
a.type = ABSTRACTDATATYPES.ABool;
break;
case "string":case "varchar":case "nvarchar":case "text":
a.type = ABSTRACTDATATYPES.AString;
break;
case "blob":
a.type = ABSTRACTDATATYPES.AData;
break;
case "datetime":
a.type = ABSTRACTDATATYPES.ADateTime;
break;
}
if (sSize != "")
{
// size contains precision information...
if (sSize != "" && sSize.Contains(",") == false)
{
a.maxsize = Convert.ToInt32(sSize);
}
else
{
a.maxsize = Convert.ToInt32(sSize.Substring(0, sSize.IndexOf(",")));
a.precision = Convert.ToInt32(sSize.Substring(sSize.IndexOf(",") + 1));
}
}
return a;
}
