} // generateTable
/// <summary>
/// Generate Delta View
/// </summary>
/// <returns>true, if created</returns>
private bool GenerateView()
{
// View
m_viewTable = MTable.Get(m_ctx, m_vTableName);
if (m_viewTable == null)
{
m_viewTable = new MTable(m_ctx, 0, null);
}
PO.CopyValues(m_baseTable, m_viewTable);
m_viewTable.SetTableName(m_vTableName);
m_viewTable.SetName(m_baseTable.GetName() + " View");
m_viewTable.SetIsView(true);
if (!m_viewTable.Save())
{
throw new Exception("Cannot save " + m_vTableName);
}
MColumn[] vCols = SyncMColumns(false);
//ColumnSync
// Create View
StringBuilder sql = new StringBuilder("CREATE OR REPLACE VIEW ")
.Append(m_vTableName)
.Append(" AS SELECT ");
for (int i = 0; i < vCols.Length; i++)
{
if (i > 0)
{
sql.Append(",");
}
MColumn column = vCols[i];
String columnName = column.GetColumnName();
if (column.IsStandardColumn() ||
column.IsKey())
{
sql.Append("b.").Append(columnName);
}
else
{
sql.Append(",COALESCE(b.").Append(columnName)
.Append("d.").Append(columnName).Append(") AS ").Append(columnName);
}
}
// From
String keyColumnName = m_baseTable.GetTableName() + "_ID";
sql.Append(" FROM ").Append(m_baseTable.GetTableName())
.Append(" b LEFT OUTER JOIN ").Append(m_dTableName)
.Append(" d ON (b.").Append(keyColumnName)
.Append("=d.").Append(keyColumnName).Append(")");
//
log.Info(sql.ToString());
if (DataBase.DB.ExecuteQuery(sql.ToString(), null) < 0)
{
return(false);
}
return(true);
} // generateView
} // isSingleKey
/// <summary>
///Get Key Columns of Table
/// </summary>
/// <returns>key columns</returns>
public String[] GetKeyColumns()
{
List <String> list = new List <String>();
GetColumns(false);
for (int i = 0; i < m_columns.Length; i++)
{
MColumn column = m_columns[i];
if (column.IsKey())
{
return new String[] { column.GetColumnName() }
}
;
if (column.IsParent())
{
list.Add(column.GetColumnName());
}
}
String[] retValue;
retValue = list.ToArray();
return(retValue);
} // getKeyColumns
} // 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