private static StringBuilder GetJoinSelectStatement(Relation relation) { StringBuilder sql = new StringBuilder(); DbRelation tblRelation = GetTableRelation(relation); IList <DbField> pTblFields = GetTableStructure(tblRelation.PrimaryTableName).Columns; IList <DbField> fTblFields = GetTableStructure(tblRelation.ForeignTableName).Columns; sql.Append("SELECT "); foreach (DbField field in pTblFields) { sql.AppendFormat("{0}.{1}, ", tblRelation.PrimaryTableName.ToString(), field.FieldName); } foreach (DbField field in fTblFields) { sql.AppendFormat("{0}.{1}, ", tblRelation.ForeignTableName.ToString(), field.FieldName); } sql = sql.Remove(sql.Length - 2, 1); sql.AppendFormat(" FROM {0} ", tblRelation.PrimaryTableName.ToString()); return(sql); }
public static String GetInnerJoinSelectStatement(Relation relation, long[] pKey) { DbRelation tblRelation = GetTableRelation(relation); IList <String> pkFields = GetTableStructure(tblRelation.PrimaryTableName).PKColumns; StringBuilder sql = GetJoinSelectStatement(relation); sql.AppendFormat(" INNER JOIN {0} ON {1}.{2} = {3}.{4}", tblRelation.ForeignTableName.ToString(), tblRelation.PrimaryTableName.ToString(), tblRelation.FKey, tblRelation.ForeignTableName.ToString(), tblRelation.FKey); if (pKey != null && pKey.Length > 0) { sql.AppendFormat(" WHERE {0}.{1} = {2} ", tblRelation.ForeignTableName.ToString(), tblRelation.FKey, pKey[0]); for (int j = 1; j < pKey.Length; j++) { sql.AppendFormat("AND {0}.{1} = {2} ", tblRelation.PrimaryTableName.ToString(), pkFields[j], pKey[j]); } } return(sql.ToString()); }
public static String GetCreateTableFKPartitionColocateStatement(Relation relation) { DbRelation rel = GetTableRelation(relation); StringBuilder sql = new StringBuilder(GetCreateTableFKPartitionStatement(relation)); sql.AppendFormat(" COLOCATE WITH ({0}) ", rel.ForeignTableName); return(sql.ToString()); }
public static String GetCreateTableFKPartitionStatement(Relation relation) { DbRelation rel = GetTableRelation(relation); StringBuilder sql = new StringBuilder(GetCreateTableBasicStatement(rel.PrimaryTableName)); if (String.IsNullOrEmpty(rel.FKey)) { return(sql.ToString()); } sql.AppendFormat(" PARTITION BY COLUMN ({0}) ", rel.FKey); return(sql.ToString()); }
public static String GetLeftOuterJoinSelectStatement(Relation relation, String condition) { DbRelation tblRelation = GetTableRelation(relation); IList <String> pkFields = GetTableStructure(tblRelation.PrimaryTableName).PKColumns; StringBuilder sql = GetJoinSelectStatement(relation); sql.AppendFormat(" LEFT OUTER JOIN {0} ON {1}.{2} = {3}.{4}", tblRelation.ForeignTableName.ToString(), tblRelation.PrimaryTableName.ToString(), tblRelation.FKey, tblRelation.ForeignTableName.ToString(), tblRelation.FKey); if (!String.IsNullOrEmpty(condition)) { sql.AppendFormat(" WHERE {0} ", condition); } return(sql.ToString()); }
public static String GetCreateViewStatement(Relation relation) { StringBuilder sql = new StringBuilder(); DbRelation tblRelation = GetTableRelation(relation); IList <DbField> pTblFields = GetTableStructure(tblRelation.PrimaryTableName).Columns; IList <DbField> fTblFields = GetTableStructure(tblRelation.ForeignTableName).Columns; sql.AppendFormat("CREATE VIEW {0} (", relation.ToString()); foreach (DbField field in pTblFields) { sql.AppendFormat("P_{0}, ", field.FieldName); } foreach (DbField field in fTblFields) { sql.AppendFormat("F_{0}, ", field.FieldName); } sql = sql.Remove(sql.Length - 2, 1); sql.AppendFormat(") AS {0}", GetInnerJoinSelectStatement(relation, null)); return(sql.ToString()); }