} // 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