} //
/// <summary>
///Get list of columns for SELECT statement.
/// Handles virtual columns
/// </summary>
/// <returns>select columns</returns>
public String GetSelectColumns()
{
GetColumns(false);
StringBuilder sb = new StringBuilder();
for (int i = 0; i < m_columns.Length; i++)
{
MColumn col = m_columns[i];
if (i > 0)
{
sb.Append(",");
}
if (col.IsVirtualColumn())
{
sb.Append(col.GetColumnSQL()).Append(" AS ");
}
sb.Append(col.GetColumnName());
}
return(sb.ToString());
} // getSelectColum
} // generate
/// <summary>
/// Generate Delta and History Table
/// </summary>
/// <returns></returns>
private bool GenerateTable()
{
// Table
m_derivedTable = MTable.Get(m_ctx, m_dTableName);
if (m_derivedTable == null)
{
m_derivedTable = new MTable(m_ctx, 0, null);
}
PO.CopyValues(m_baseTable, m_derivedTable);
m_derivedTable.SetTableName(m_dTableName);
m_derivedTable.SetName(m_derivedTable.GetName() + " SubTable");
m_derivedTable.SetSubTableType(m_derivedTableType);
m_derivedTable.SetBase_Table_ID(m_baseTable.GetAD_Table_ID());
if (!m_derivedTable.Save())
{
throw new Exception("Cannot save " + m_dTableName);
}
MColumn[] dCols = SyncMColumns(true);
// Sync Columns in Database
List <MColumn> list = new List <MColumn>(dCols.Length);
foreach (MColumn element in dCols)
{
list.Add(element);
}
Trx trx = Trx.Get("getDatabaseMetaData");
//
String catalog = "";
String schema = DataBase.DB.GetSchema();
String tableName = m_dTableName;
tableName = tableName.ToUpper();
int noColumns = 0;
//
DataSet rs = null;
using (DatabaseMetaData md = new DatabaseMetaData())
{
rs = md.GetColumns(catalog, schema, tableName);
}
for (int rscount = 0; rscount <= rs.Tables[0].Rows.Count - 1; rscount++)
{
noColumns++;
String columnName = rs.Tables[0].Rows[rscount]["COLUMN_NAME"].ToString();
bool found = false;
for (int i = 0; i < list.Count; i++)
{
MColumn dCol = list[i];
if (columnName.Equals(dCol.GetColumnName(), StringComparison.OrdinalIgnoreCase))
{
String sql = dCol.GetSQLModify(m_derivedTable, false);
DataBase.DB.ExecuteUpdateMultiple(sql, false, null);
found = true;
list.Remove(list[i]);
break;
}
}
if (!found)
{
String sql = "ALTER TABLE " + m_dTableName + " DROP COLUMN " + columnName;
DataBase.DB.ExecuteQuery(sql, null);
}
}
//rs.close();
trx.Close();
// No Columns
if (noColumns == 0)
{
String sql = m_derivedTable.GetSQLCreate();
return(DataBase.DB.ExecuteUpdateMultiple(sql, false, null) >= 0);
}
// New Columns
for (int i = 0; i < list.Count(); i++)
{
MColumn dCol = list[i];
if (dCol.IsVirtualColumn())
{
continue;
}
String sql = dCol.GetSQLAdd(m_derivedTable);
DataBase.DB.ExecuteUpdateMultiple(sql, false, null);
}
return(true);
} // generateTable
} // getSQLCrete
// getSQLCrete
/**
* Get SQL Create
* @param requery refresh columns
* @return create table DDL
*/
public String GetSQLCreate(bool requery)
{
StringBuilder sb = new StringBuilder("CREATE TABLE ")
.Append(GetTableName()).Append(" (");
//
bool hasPK = false;
bool hasParents = false;
bool firstColumn = true;
StringBuilder constraints = new StringBuilder();
StringBuilder unqConstraints = new StringBuilder();
GetColumns(requery);
for (int i = 0; i < m_columns.Length; i++)
{
MColumn column = m_columns[i];
if (column.IsVirtualColumn())
{
continue;
}
if (firstColumn)
{
firstColumn = false;
}
else
{
sb.Append(", ");
}
sb.Append(column.GetSQLDDL());
//
if (column.IsKey())
{
constraints.Append(", CONSTRAINT PK").Append(GetAD_Table_ID())
.Append(" PRIMARY KEY (").Append(column.GetColumnName()).Append(")");
hasPK = true;
}
if (column.IsParent())
{
hasParents = true;
}
if (column.IsUnique())
{
if (unqConstraints.Length == 0)
{
unqConstraints.Append(", CONSTRAINT UK").Append(GetAD_Table_ID())
.Append(" UNIQUE (").Append(column.GetColumnName());
}
else
{
unqConstraints.Append(",").Append(column.GetColumnName());
}
}
}
// Multi Column PK
if (!hasPK && hasParents)
{
StringBuilder cols = new StringBuilder();
for (int i = 0; i < m_columns.Length; i++)
{
MColumn column = m_columns[i];
if (!column.IsParent())
{
continue;
}
if (cols.Length > 0)
{
cols.Append(", ");
}
cols.Append(column.GetColumnName());
}
sb.Append(", CONSTRAINT PK").Append(GetAD_Table_ID())
.Append(" PRIMARY KEY (").Append(cols).Append(")");
}
if (unqConstraints.Length > 0)
{
unqConstraints.Append(")");
}
sb.Append(constraints);
sb.Append(unqConstraints);
sb.Append(")");
return(sb.ToString());
} // getSQLCreate