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