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());
}
