private void IIfStatement(CaseExpression expression, int expNumber) { _Query.Append(" IIF( "); expression.ExpressionToEval.Accept(this); _Query.Append(" = "); expression.TestExpressions[expNumber].TestExpression.Accept(this); _Query.Append(", "); expression.TestExpressions[expNumber].TestResult.Accept(this); _Query.Append(", "); if (expNumber < expression.TestExpressions.Count - 1) IIfStatement(expression, expNumber + 1); else if(expression.DefaultResult != null) expression.DefaultResult.Accept(this); _Query.Append(") "); }
public override void Visit(CaseExpression expression) { IIfStatement(expression, 0); if (expression.Alias != null && expression.Alias != string.Empty) _Query.Append(FormatTableAlias(expression.Alias)).Append(" "); }
public override void Visit(CaseExpression expression) { _Query.Append(SPACE).Append(DECODE).Append(OPENBRACE); expression.ExpressionToEval.Accept(this); _Query.Append(COMMA); foreach (CaseTest cs in expression.TestExpressions) { cs.TestExpression.Accept(this); _Query.Append(COMMA); cs.TestResult.Accept(this); _Query.Append(COMMA); } if (expression.DefaultResult != null) expression.DefaultResult.Accept(this); else _Query.Append(NULL); _Query.Append(CLOSEBRACE); if (expression.Alias != null && expression.Alias != string.Empty) _Query.Append(FormatTableAlias(expression.Alias)).Append(SPACE); }
/// <summary> /// Get an ISqlExpression which correspond to one entity mapping /// This method process the opath constraint /// This method doesn't not process the children of an entity /// </summary> /// <param name="query"></param> /// <param name="attributes"></param> /// <param name="baseType">In the case of a table per hierarchy mapping, the "in" predicates computes all the children from the base</param> /// <param name="orderById"></param> /// <returns></returns> public ISQLExpression TransformToSql(Path path, StringCollection attributes, bool orderById, bool lastIsAttribute) { // Exception management : path if (path == null) throw new ArgumentNullException("path"); // Exception management : identifier if (path.Identifiers.Count == 0) throw new ArgumentNullException("path", "Must have at least one Identifier"); ConvertToSQL(path, true, false, lastIsAttribute); // Restore context SelectStatement currentQuery = (SelectStatement)queryContext.Peek(); Table currentTable = (Table)tableContext.Peek(); EntityMapping entityMapping = (EntityMapping)entityMappingContext.Peek(); // Always add the Type and Id in the SQL query SelectStatement s = (SelectStatement)queryContext.Peek(); string childType = string.Empty; string parenttype = string.Empty; if (path.Identifiers.Count == 1) childType = path.Identifiers[0].Value; else { string refName = path.Identifiers[path.Identifiers.Count - 1].Value; parenttype = string.Empty; //= query.Path.Identifiers[0].Value; // Get the parent type: the type which corresponds to the refName string nextRef = string.Empty;// query.Path.Identifiers[1].Value; string nextType = path.Identifiers[0].Value; for (int i = 1; i < path.Identifiers.Count - 1; i++) { nextRef = path.Identifiers[i].Value; Evaluant.Uss.Models.Reference nextRefModel = _Model.GetReference(nextType, nextRef); // Try to get the reference model in the parent type if (nextRefModel == null) { Evaluant.Uss.Models.Entity currentE = _Model.GetEntity(nextType); while (_Model.GetParent(currentE) != null) { currentE = _Model.GetParent(currentE); nextRefModel = _Model.GetReference(currentE.Type, nextRef); if (nextRefModel != null) break; } } if (nextRefModel == null) throw new SqlMapperException(string.Concat("Cannot find the reference ", refName, " in the model of the entity ", nextType)); nextType = nextRefModel.ChildType; } parenttype = nextType; //if (_Model.GetReference(parenttype, refName) == null) //{ // Evaluant.Uss.Models.Entity current = Model.GetEntity(parenttype); // while (Model.GetParent(current) != null) // { // current = Model.GetParent(current); // parenttype = current.Type; // if (_Model.GetReference(parenttype, refName) != null) // break; // } //} Evaluant.Uss.Models.Reference r = _Model.GetReference(parenttype, refName); if (r == null) { Evaluant.Uss.Models.Attribute a = _Model.GetAttribute(parenttype, refName); if (a == null) { throw new SqlMapperException("Cannot find " + refName + " in the '" + parenttype + "' model"); } childType = parenttype; } else { childType = r.ChildType; } if (_Model.GetReference(parenttype, refName) == null && _Model.GetAttribute(parenttype, refName) == null) throw new SqlMapperException("Cannot find " + refName + " in the '" + parenttype + "' model"); if (_Model.GetReference(parenttype, refName) != null) childType = _Model.GetReference(parenttype, refName).ChildType; if (_Model.GetAttribute(parenttype, refName) != null) childType = parenttype; } if (path.Identifiers.Count == 1 || lastIsAttribute) { // Generates a specific query if a discriminator field is set if (entityMapping.DiscriminatorField != null && entityMapping.DiscriminatorField != string.Empty) { CaseExpression caseExp = new CaseExpression(null, new Column(entityMapping, currentTable.TableAlias, entityMapping.DiscriminatorField), TYPE_ALIAS); caseExp.DefaultResult = new Column(entityMapping, currentTable.TableAlias, entityMapping.DiscriminatorField); // Generates a case for each discriminator value which is different to the real type foreach (Models.Entity e in _Model.GetTree(entityMapping.Type)) { string discriminatorValue = _Mapping.Entities[e.Type].DiscriminatorValue; if (discriminatorValue != e.Type) { caseExp.TestExpressions.Add(new CaseTest(new Constant(discriminatorValue, DbType.String), new Constant(e.Type, DbType.String))); } } // Determines wether the select should transform the discriminator values to real types (needed by the entity isntanciator) if (caseExp.TestExpressions.Count > 0) { s.SelectList.Insert(0, caseExp); } else // If no type conversion has to be done, just write the discriminator-field alias { s.SelectList.Insert(0, new Column(entityMapping, currentTable.TableAlias, entityMapping.DiscriminatorField, TYPE_ALIAS)); } } else { s.SelectList.Insert(0, new Constant(childType, DbType.AnsiStringFixedLength, TYPE_ALIAS)); } bool idFieldInserted = false; foreach (ISQLExpression exp in s.SelectList) { Column id = exp as Column; if (id != null && id.ColumnName == entityMapping.GetIdField(entityMapping.Ids[0])) { idFieldInserted = true; if (id.Alias == null || id.Alias == String.Empty) id.Alias = entityMapping.GetIdFieldAs(entityMapping.Ids[0]); break; } } if (!idFieldInserted) { for (int i = 0; i < entityMapping.Ids.Count; i++) s.SelectList.Insert(Math.Min(1 + i, s.SelectList.Count), new Column(entityMapping.Ids[i], currentTable.TableAlias, entityMapping.GetIdField(entityMapping.Ids[i]), entityMapping.GetIdFieldAs(entityMapping.Ids[i]))); } } else { s.SelectList.Insert(0, new Column(null, currentTable.TableAlias, TYPE_ALIAS, TYPE_ALIAS)); EntityMapping parentMapping = _Mapping.Entities[parenttype]; int pos = 2; if (parentMapping != null) { pos += parentMapping.Ids.Count - 1; for (int i = 0; i < parentMapping.Ids.Count; i++) { PrimaryKeyMapping pmk = parentMapping.Ids[i]; string parentIdAlias = GetParentIdAlias(parentMapping, pmk.Field); s.SelectList.Insert(1 + i, new Column(pmk, currentTable.TableAlias, parentIdAlias, parentIdAlias)); } } for (int i = 0; i < entityMapping.Ids.Count; i++) { PrimaryKeyMapping pmk = entityMapping.Ids[i]; if (currentTable is SelectStatement) s.SelectList.Insert(pos + i, new Column(pmk, currentTable.TableAlias, entityMapping.GetIdFieldAs(pmk), entityMapping.GetIdFieldAs(pmk))); else s.SelectList.Insert(pos + i, new Column(pmk, currentTable.TableAlias, entityMapping.GetIdField(pmk), entityMapping.GetIdFieldAs(pmk))); } } // FROM entity_table e{n} if (!currentQuery.FromClause.Contains(currentTable)) currentQuery.FromClause.Add(currentTable); // If attributes == null => don't load if (attributes != null) { _IsFirstAttribute = true; StringCollection processedAttributes = new StringCollection(); // rule1: alias in selectList are specified only if field name is different than attribute name // if it is the case we use the attribute name as alias // rule2: if more than one item have the same attribute name, add an index number at the end of 2nd, ..., n // rule3: use a hashtable to do the correspondance between the alias and the attributeName // rule4: above rules has to be done only for the first select list in case of union query foreach (string attName in attributes) { string attNameAlias = attName.Substring(attName.LastIndexOf(DOT) + 1); string shortAttName = attNameAlias; AttributeMapping am = entityMapping.Attributes[shortAttName]; if (am == null) continue; PrimaryKeyMapping pkm = entityMapping.Ids[am.Field, false]; if (pkm == null || pkm.Generator.Name != GeneratorMapping.GeneratorType.business) continue; attNameAlias = entityMapping.GetIdFieldAs(pkm); bool exists = false; foreach (ISQLExpression expr in currentQuery.SelectList) { if (expr as Column == null) continue; if ((expr as Column).Alias == attNameAlias) { exists = true; break; } } if (exists) continue; ISQLExpression expression = ProcessAttributes(entityMapping, shortAttName, true); if (expression is Column) ((Column)expression).Alias = attNameAlias; currentQuery = (SelectStatement)queryContext.Peek(); int index = -1; foreach (ISQLExpression expr in currentQuery.SelectList) { Column col = expr as Column; if (col != null && col.Alias == attNameAlias) { index = currentQuery.SelectList.IndexOf(expr); break; } Constant cst = expr as Constant; if (cst != null && cst.Alias == attNameAlias) { index = currentQuery.SelectList.IndexOf(expr); break; } } if (index != -1) currentQuery.SelectList.RemoveAt(index); currentQuery.SelectList.Add(expression); } foreach (string attName in attributes) { string attNameAlias = attName.Substring(attName.LastIndexOf(DOT) + 1); string shortAttName = attNameAlias; AttributeMapping am = entityMapping.Attributes[shortAttName]; EntityMapping em = entityMapping; // if am is null, ask child mapping if (am == null) { string attType = attName.Substring(0, attName.IndexOf(DOT)); // if the attribute was found in another hierarchy, use a NULL value (case of TPCC when two hierarchies inherit from an absctract class) if (_Model.GetTree(entityMapping.Type).Contains(attType)) { foreach (Models.Entity entity in _Model.GetTree(attType)) { EntityMapping childMapping = _Mapping.Entities[entity.Type]; if (childMapping != null) { AttributeMapping tmpAm = childMapping.Attributes[shortAttName]; if (tmpAm != null) { am = tmpAm; em = childMapping; break; } } } } //if (childMapping.Type != entityMapping.Type && childMapping.Table == entityMapping.Table) //{ // am = childMapping.Attributes[shortAttName]; // if (am != null) // em = childMapping; //} } // if duplicated attribute name, add an incremented index at the end (if not already done) if (!_IsAliasColumnComputed) { foreach (string colName in _AliasColumnMapping.Values) { if (colName == shortAttName) { if (!_AliasColumnMapping.Contains(attName)) { attNameAlias += ++_AliasColumnIndexNumber; _AliasColumnMapping.Add(attName, attNameAlias); } else attNameAlias = _AliasColumnMapping[attName].ToString(); break; } } } else { // get the previous computed alias if (_AliasColumnMapping[attName] != null) attNameAlias = _AliasColumnMapping[attName].ToString(); } if (am != null) { // If attribute exists in mapping: add a column to the query ISQLExpression expression = null; // check if is imbricate table if (currentTable is SelectStatement) expression = ProcessAttributes(em, shortAttName, true, true); else expression = ProcessAttributes(em, shortAttName, true); // check if alias is needed or not if (attNameAlias != am.Field) { if (expression is Column) { ((Column)expression).Alias = attNameAlias; } else if (expression is Constant) { ((Constant)expression).Alias = attNameAlias; } } currentQuery = (SelectStatement)queryContext.Peek(); int index = -1; foreach (ISQLExpression expr in currentQuery.SelectList) { Column col = expr as Column; if (col != null && col.Alias == attNameAlias) { index = currentQuery.SelectList.IndexOf(expr); break; } Constant cst = expr as Constant; if (cst != null && cst.Alias == attNameAlias) { index = currentQuery.SelectList.IndexOf(expr); break; } } if (index != -1) currentQuery.SelectList.RemoveAt(index); currentQuery.SelectList.Add(expression); } else { // If attribute doesn't exists in mapping: add a constant instead of // a column so that we can build a UNION currentQuery = (SelectStatement)queryContext.Peek(); Models.Attribute found = null; Models.Entity foundEntity = null; foreach (Models.Entity e in _Model.GetTree(entityMapping.Type)) { foreach (Models.Attribute a in e.Attributes) { if (a.Name == attNameAlias) { found = a; foundEntity = e; break; } } if (found != null) { break; } } // If the attribute is in a sub class, select NULL for the current UNION, the column instead if (found != null && foundEntity.Type == entityMapping.Type) { AttributeMapping foundMapping = _Mapping.Entities[foundEntity.Type].Attributes[found.Name]; if (foundMapping == null) continue; currentQuery.SelectList.Add(new Column(null, currentTable.TableAlias, foundMapping.Field, foundMapping.Name)); } else { currentQuery.SelectList.Add(new Constant(DBNull.Value, DbType.AnsiStringFixedLength, attNameAlias)); } } // Store the correspondance between the alias/column name and the attribute name if (!_IsAliasColumnComputed && !_AliasColumnMapping.Contains(attName)) _AliasColumnMapping.Add(attName, attNameAlias); } } return (SelectStatement)queryContext.Peek(); }
public virtual void Visit(CaseExpression expression) { _Query.Append(CASE); expression.ExpressionToEval.Accept(this); foreach (CaseTest cs in expression.TestExpressions) { _Query.Append(SPACE).Append(WHEN); cs.TestExpression.Accept(this); _Query.Append(SPACE).Append(THEN); cs.TestResult.Accept(this); } if (expression.DefaultResult != null) { _Query.Append(SPACE).Append(ELSE); expression.DefaultResult.Accept(this); } _Query.Append(SPACE).Append(END); if (expression.Alias != null && expression.Alias != string.Empty) _Query.Append(FormatTableAlias(expression.Alias)).Append(SPACE); }