public ATable cacheTable(string tablename)
{
// if (this._tableArray.ContainsKey(tablename)) this._tableArray.Remove(tablename);
// Query the database to get information about this table.
ATable newCacheObject = null;
System.Diagnostics.Debug.WriteLine("Caching Table: " + tablename);
try
{
ATable cacheObject = new ATable(tablename);
FieldDescriptor[] fieldinfo = this._db.GetDatabaseProvider().getTableDescription(tablename);
if (fieldinfo == null)
{
throw (new Exception("Error getting table."));
}
foreach (FieldDescriptor fInfo in fieldinfo)
{
cacheObject.addField(fInfo.name, fInfo.type, fInfo.maxlen, fInfo.defaultval, fInfo.modifiers);
}
this._tableArray.Add(tablename, cacheObject);
newCacheObject = cacheObject;
}
catch(Exception e)
{
System.Diagnostics.Debug.Print(e.Message);
}
return newCacheObject;
}
public override string buildCreateTableStatement(ATable tbl)
{
// TODO Write code to compile this table object into sql statements.
string sqlstr = "create table " + tbl.name + " (";
// build the defs here
string typeString = ""; string collectedModifiers = "";
foreach (AField field in tbl.getFieldList())
{
typeString = this._dbObj.GetDatabaseProvider().fieldToTypeStr(field);
//typeString = SQLConsole.Data.ProviderConverters.TypeConverters.FieldToTypeString(field, this._dbObj.GetDatabaseConnector().getDBType());
if (this._dbObj.GetDatabaseConnector().getDBType() == DATABASETYPES.MYSQL)
{
// Since mysql puts keys at the end of a statement we must collect them here.
if (field.modifiers != null)
{
foreach (ABSTRACTFIELDMODIFIERS mod in field.modifiers)
{
if (mod == ABSTRACTFIELDMODIFIERS.PrimaryKey)
{
collectedModifiers += " PRIMARY KEY(" + field.name + "), ";
}
else if (mod == ABSTRACTFIELDMODIFIERS.IndexKey)
{
collectedModifiers += " KEY(" + field.name + "), ";
}
else if (mod == ABSTRACTFIELDMODIFIERS.ForeignKey)
{
string foreignPrimaryKey = this._dbObj.GetTableCache().getCachedTable((string)field.value).getPrimaryKey().name;
collectedModifiers += " FOREIGN KEY(" + field.name + ") REFERENCES " + field.value + "(" + foreignPrimaryKey + "), ";
}
}
}
}
sqlstr += field.name + " " + typeString + ", ";
}
sqlstr += collectedModifiers;
sqlstr = sqlstr.Trim();
if (sqlstr.Substring(sqlstr.Length - 1) == ",")
{
sqlstr = sqlstr.Substring(0, sqlstr.Length - 1);
}
sqlstr += ");";
return sqlstr;
}
public override string buildCreateTableStatement(ATable tbl)
{
// TODO Write code to compile this table object into sql statements.
string sqlstr = "create table " + tbl.name + " (";
// build the defs here
string typeString = ""; string collectedModifiers = "";
foreach (AField field in tbl.getFieldList())
{
//typeString = SQLConsole.Data.ProviderConverters.TypeConverters.FieldToTypeString(field, this._dbObj.GetDatabaseConnector().getDBType());
typeString = this._dbObj.GetDatabaseProvider().fieldToTypeStr(field);
sqlstr += field.name + " " + typeString + ", ";
}
sqlstr += collectedModifiers;
sqlstr = sqlstr.Trim();
if (sqlstr.Substring(sqlstr.Length - 1) == ",")
{
sqlstr = sqlstr.Substring(0, sqlstr.Length - 1);
}
sqlstr += ");";
return sqlstr;
}
public AField(string name, object value, ATable ownertable)
: this(name, value, ownertable.name)
{
}
public AField(string name, ATable ownertable, ABSTRACTAGGREGATE AggregateFunction)
: this(name, null, ownertable)
{
this._agg = AggregateFunction;
}
public System.Collections.ArrayList getResultAsFieldArrayWithCaching()
{
System.Collections.ArrayList retArray = new System.Collections.ArrayList();
this.Execute();
TableDataReader tdr = this.getDatabaseObject().CreateTableDataReader();
tdr.Bind(this);
while (tdr.Read())
{
if (tdr.getFieldList().Count > 1)
{
//System.Collections.ArrayList retArray2 = new System.Collections.ArrayList();
ATable coll = new ATable();
foreach (AField a in tdr.getFieldList())
{
if (this.getField(a.name) != null)
{
AField newField = coll.addField(a.name, ABSTRACTDATATYPES.AString);
newField.value = a.value;
}
}
retArray.Add(coll);
}
else
retArray.Add(Convert.ToString(((AField)tdr.getFieldList()[0]).value));
}
return retArray;
}
public bool isCached(ATable tbl)
{
foreach (ATable ctbl in this._tableArray.Values)
{
if (ctbl.name.Equals(tbl.name)) return true;
}
return false;
}
public void InsertIfAbsent(ATable row)
{
string where = "";
foreach (AField f in row.getFieldList())
{
where = (where==""?f.name+"='"+f.value+"'":where + " and " + f.name+"='"+f.value+"'");
}
object check = this.getSingleResult("select * from " + row.name + " where " + where);
if (check == null)
{
QueryBuilder qbl = this.CreateQueryBuilder();
qbl.setType(ABSTRACTQUERYTYPES.InsertQuery);
qbl.addSource(row.name);
qbl.setFieldList(row.getFieldList());
string sql = qbl.Compile();
}
}
// These 2 addField implementations deal with foreign key support
public AField addField(string name, ABSTRACTFIELDMODIFIERS modifier, ATable foreignTable)
{
return addField(name, modifier, foreignTable.name);
}
public abstract int generateNextID(ATable srcTable);
public abstract string buildTableUpdateStatements(ATable tbl);
public abstract string buildCreateTableStatement(ATable qbl);
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 void InsertIfAbsent(ATable row, string[] except)
{
string where = "";
foreach (AField f in row.getFieldList())
{
bool wordBanned = Array.Exists(except, new Predicate<string>(
delegate(String str)
{
if (str == f.name) return true;
return false;
}
));
if(!wordBanned)
where = (where == "" ? f.name + "='" + f.value + "'" : where + " and " + f.name + "='" + f.value + "'");
}
object check = this.getSingleResult("select * from " + row.name + " where " + where);
if (check == null)
{
QueryBuilder qbl = this.CreateQueryBuilder();
qbl.setType(ABSTRACTQUERYTYPES.InsertQuery);
qbl.addSource(row.name);
qbl.setFieldList(row.getFieldList());
string sql = qbl.Compile();
}
}
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;
}
// Generate the next logical id in the sequence for the source table
public override int generateNextID(ATable srcTable)
{
return generateNextID(srcTable.name);
}
public ATable getTableObject(string name)
{
// check the cache...
if (this.GetTableCache().getCachedTable(name) != null) return this.GetTableCache().getCachedTable(name);
// Query the database to get information about this table.
ATable tblObject = new ATable(name);
FieldDescriptor[] fieldinfo = this.GetDatabaseProvider().getTableDescription(name);
if (fieldinfo == null) return null;
foreach (FieldDescriptor fInfo in fieldinfo)
{
tblObject.addField(fInfo.name, fInfo.type, fInfo.maxlen, fInfo.modifiers);
}
return tblObject;
}
