public override int CreateTable(IDbConnection connection, IDbTransaction trans, String DatabaseName, ClassTable table)
{
StringBuilder sql = new StringBuilder();
try
{
sql.Append(" CREATE TABLE " + table.Table + " ( ");
for (int i = 0; i < table.listColumn.Count; i++)
{
sql.Append(" " + table.listColumn[i].ColumnName + " " + table.listColumn[i].ColumnDefine);
if (table.listColumn[i].isPRIMARY)
{
sql.Append(" PRIMARY KEY ");
}
if (i < table.listColumn.Count - 1)
{
sql.Append(" , ");
}
}
sql.Append(" ); ");
}
catch (Exception ex)
{
SanitaLogEx.e(TAG, ex);
}
int ret = -100;
try
{
ret = myBaseDao.DoUpdate(connection, trans, sql.ToString());
return(ret);
}
catch { return(-100); }
}
public int DeleteTable(IDbConnection connection, IDbTransaction trans, String DatabaseName, ClassTable table)
{
StringBuilder sql = new StringBuilder();
try
{
sql.Append(" DROP TABLE " + table.Table + " ; ");
}
catch (Exception ex)
{
SanitaLogEx.e(TAG, ex);
}
int ret = -100;
try
{
ret = myBaseDao.DoUpdate(connection, trans, sql.ToString());
return(ret);
}
catch { return(-100); }
}
public override int AlterTableTable(IDbConnection connection, IDbTransaction trans, String DatabaseName, ClassTable Fixtable, ClassTable NewTable)
{
try
{
//Update column
for (int i = 0; i < Fixtable.listColumn.Count; i++)
{
//Tim column tuong ung trong NewTable
var foundColumn = NewTable.listColumn.FirstOrDefault(p => p.ColumnName.ToLower() == Fixtable.listColumn[i].ColumnName.ToLower());
if (foundColumn == null)
{
StringBuilder sql = new StringBuilder();
sql.Append(" ALTER TABLE " + Fixtable.Table + " ");
sql.Append(" ADD COLUMN " + Fixtable.listColumn[i].ColumnName + " " + Fixtable.listColumn[i].ColumnDefine + Fixtable.listColumn[i].ColumnAfter + " ");
int ret = myBaseDao.DoUpdate(connection, trans, sql.ToString());
if (ret < 0)
{
return(ret);
}
SanitaLogEx.e(TAG, " >>Add column '" + Fixtable.listColumn[i].ColumnName + "'");
}
}
return(0);
}
catch (Exception ex)
{
SanitaLogEx.e(TAG, ex);
return(-100);
}
}
public override int CreateTable(IDbConnection connection, IDbTransaction trans, String DatabaseName, ClassTable table)
{
StringBuilder sql = new StringBuilder();
try
{
sql.Append(" CREATE TABLE " + table.Table + " ( ");
for (int i = 0; i < table.listColumn.Count; i++)
{
sql.Append(" " + table.listColumn[i].ColumnName + " " + table.listColumn[i].ColumnDefine);
if (table.listColumn[i].isPRIMARY)
{
sql.Append(" PRIMARY KEY ");
}
if (i < table.listColumn.Count - 1)
{
sql.Append(" , ");
}
}
sql.Append(" ) WITH OIDS ; ");
//sql.Append(" ); ");
//Update index
for (int i = 0; i < table.listColumn.Count; i++)
{
if (table.listColumn[i].isIndex)
{
sql.Append(String.Format("CREATE INDEX {0}_{1}_idx ON {0} USING btree ({2});", table.Table.ToLower(), table.listColumn[i].ColumnName.ToLower().Replace(table.Table.ToLower().Replace("tb_", ""), ""), table.listColumn[i].ColumnName));
}
}
}
catch (Exception ex)
{
SanitaLogEx.e(TAG, ex);
}
int ret = -100;
try
{
ret = myBaseDao.DoUpdate(connection, trans, sql.ToString());
return(ret);
}
catch { return(-100); }
}
public int AlterTableTable_DeleteColumn(IDbConnection connection, IDbTransaction trans, String DatabaseName, ClassTable NewTable, ClassTable Fixtable)
{
StringBuilder sql = new StringBuilder();
bool isNeedDeleteColumn = false;
try
{
//Update column
for (int i = 0; i < NewTable.listColumn.Count; i++)
{
//Tim column tuong ung trong NewTable
var foundColumn = Fixtable.listColumn.FirstOrDefault(p => p.ColumnName.ToLower() == NewTable.listColumn[i].ColumnName.ToLower());
if (foundColumn == null)
{
sql.Append(" ALTER TABLE " + NewTable.Table + " DROP COLUMN " + NewTable.listColumn[i].ColumnName + "; ");
isNeedDeleteColumn = true;
SanitaLogEx.e(TAG, " >>Drop column '" + NewTable.listColumn[i].ColumnName + "'");
}
}
sql.Replace(", ; ", "; ");
}
catch (Exception ex)
{
SanitaLogEx.e(TAG, ex);
}
int ret = -100;
try
{
if (isNeedDeleteColumn)
{
ret = myBaseDao.DoUpdate(connection, trans, sql.ToString());
return(ret);
}
else
{
return(0);
}
}
catch { return(-100); }
}
public override int AlterTableTable(IDbConnection connection, IDbTransaction trans, String DatabaseName, ClassTable Fixtable, ClassTable NewTable)
{
StringBuilder sql = new StringBuilder();
bool isNeedAddColumn = false;
bool isNeedAddIndex = false;
string strPRIMARY = "";
try
{
sql.Append(" ALTER TABLE " + Fixtable.Table + " ");
//Update column
for (int i = 0; i < Fixtable.listColumn.Count; i++)
{
//Add PRIMARY KEY
if (Fixtable.listColumn[i].isPRIMARY)
{
if (string.IsNullOrEmpty(strPRIMARY))
{
strPRIMARY = "'" + Fixtable.listColumn[i].ColumnName + "'";
}
else
{
strPRIMARY += " , " + "'" + Fixtable.listColumn[i].ColumnName + "'";
}
}
//Tim column tuong ung trong NewTable
bool isupdateSQL = false;
var foundColumn = NewTable.listColumn.FirstOrDefault(p => p.ColumnName.ToLower() == Fixtable.listColumn[i].ColumnName.ToLower());
if (foundColumn == null)
{
//AliboboLog.e("[SynchSchema] Add column '" + Fixtable.listColumn[i].ColumnName + "'");
sql.Append(" ADD COLUMN " + Fixtable.listColumn[i].ColumnName + " " + Fixtable.listColumn[i].ColumnDefine + " ");
isNeedAddColumn = true;
isupdateSQL = true;
SanitaLogEx.e(TAG, " >>Add column '" + Fixtable.listColumn[i].ColumnName + "'");
}
if (i < Fixtable.listColumn.Count - 1)
{
if (isupdateSQL)
{
sql.Append(" , ");
}
}
else //Da check den column cuoi cung
{
//Them dau ket thuc lenh
sql.Append(" ; ");
}
}
//Reset
if (!isNeedAddColumn)
{
sql = new StringBuilder();
}
//Update index
for (int i = 0; i < Fixtable.listColumn.Count; i++)
{
if (Fixtable.listColumn[i].isIndex)
{
String index_name = String.Format("{0}_{1}_idx", Fixtable.Table.ToLower(), Fixtable.listColumn[i].ColumnName.ToLower().Replace(Fixtable.Table.ToLower().Replace("tb_", ""), ""));
var foundIndex = NewTable.listIndex.FirstOrDefault(p => p.ColumnName.Equals(index_name, StringComparison.CurrentCultureIgnoreCase));
if (foundIndex == null)
{
sql.Append(String.Format("CREATE INDEX {2} ON {0} USING btree ({1});", Fixtable.Table, Fixtable.listColumn[i].ColumnName, index_name));
isNeedAddIndex = true;
SanitaLogEx.e(TAG, " >>Add index '" + Fixtable.listColumn[i].ColumnName + "'");
}
}
}
sql.Replace(", ; ", "; ");
}
catch (Exception ex)
{
SanitaLogEx.e(TAG, ex);
}
int ret = -100;
try
{
if (isNeedAddColumn || isNeedAddIndex)
{
ret = myBaseDao.DoUpdate(connection, trans, sql.ToString());
return(ret);
}
else
{
return(0);
}
}
catch { return(-100); }
}
public virtual int CreateTable(IDbConnection connection, IDbTransaction trans, String DatabaseName, ClassTable table)
{
return(-100);
}
public virtual int AlterTableTable(IDbConnection connection, IDbTransaction trans, String DatabaseName, ClassTable Fixtable, ClassTable NewTable)
{
return(-100);
}