/// <summary>This method adds to the SQL statement any path that appears in a formula</summary> /// <param name="onSql">This parameter has the current SQL statement</param> /// <param name="joinType">This parameter has the type of join</param> /// <param name="facet">First class, the beginning of the path</param> /// <param name="onPath">Path to add to SQL statement</param> /// <param name="processedOnPath">Path pocessed until the call of this method</param> /// <param name="initialClass">Domain of the object valued argument, object valued filter variables or AGENT when it should be necessary</param> /// <param name="forceLastAlias">Create almost the last alias in the sql</param> public static string AddPath(ONSqlSelect onSql, JoinType joinType, string facet, ONPath onPath, ONPath processedOnPath, string initialClass, bool forceLastAlias) { // initialClass is used for Object-valued arguments, object-valued filter variables, agent instance, ... ONPath lProcessedOnPath = new ONPath(processedOnPath); ONPath lOnPath = new ONPath(onPath); // Calculate processed path string lRole = lOnPath.RemoveHead() as string; lProcessedOnPath += lRole; // Search Path if (lOnPath.Count == 0) { string lAlias = onSql.GetAlias(facet, lProcessedOnPath); if ((lAlias != "") && (!forceLastAlias)) return (lAlias); } // Create path if (initialClass == "") // Simple paths { if (string.Compare(lRole, "NaveNodriza", true) == 0) return NaveNodrizaData.AddPath(onSql, joinType, facet, lOnPath, lProcessedOnPath, "", forceLastAlias); if (string.Compare(lRole, "Aeronave", true) == 0) return AeronaveData.AddPath(onSql, joinType, facet, lOnPath, lProcessedOnPath, "", forceLastAlias); if (string.Compare(lRole, "Pasajero", true) == 0) return PasajeroData.AddPath(onSql, joinType, facet, lOnPath, lProcessedOnPath, "", forceLastAlias); if (string.Compare(lRole, "PasajeroAeronave", true) == 0) return PasajeroAeronaveData.AddPath(onSql, joinType, facet, lOnPath, lProcessedOnPath, "", forceLastAlias); if (string.Compare(lRole, "Revision", true) == 0) return RevisionData.AddPath(onSql, joinType, facet, lOnPath, lProcessedOnPath, "", forceLastAlias); if (string.Compare(lRole, "RevisionPasajero", true) == 0) return RevisionPasajeroData.AddPath(onSql, joinType, facet, lOnPath, lProcessedOnPath, "", forceLastAlias); if (string.Compare(lRole, "Administrador", true) == 0) return AdministradorData.AddPath(onSql, joinType, facet, lOnPath, lProcessedOnPath, "", forceLastAlias); } // Solve path with initialPath object[] lParameters = new object[6]; lParameters[0] = onSql; lParameters[1] = facet; lParameters[2] = lOnPath; lParameters[3] = lProcessedOnPath; lParameters[4] = ""; lParameters[5] = forceLastAlias; return ONContext.InvoqueMethod(ONContext.GetType_Data(initialClass), "AddPath", lParameters) as string; }
private static string PasajeroAeronaveRoleAddSql(ONSqlSelect onSql, JoinType joinType, string facet, ONPath onPath, ONPath processedPath, string role, bool force, bool isLinkedTo) { ONPath lOnPath = new ONPath(processedPath); lOnPath += role; //Source table string lAliasProcessed = onSql.GetAlias("Aeronave", processedPath, isLinkedTo); if (lAliasProcessed == "") { force = false; lAliasProcessed = onSql.CreateAlias(joinType, lAliasProcessed, CtesBD.TBL_AERONAVE, processedPath, "Aeronave", force, isLinkedTo); } //Target table string lAlias = onSql.GetAlias("PasajeroAeronave", lOnPath, isLinkedTo); if (lAlias == "") { force = false; lAlias = onSql.CreateAlias(joinType, lAliasProcessed, CtesBD.TBL_PASAJEROAERONAVE, lOnPath, "PasajeroAeronave", force, isLinkedTo); onSql.AddAliasWhere(lAlias, lAliasProcessed + "." + CtesBD.FLD_AERONAVE_ID_AERONAVE + "=" + lAlias + "." + CtesBD.FLD_PASAJEROAERONAVE_FK_AERONAVE_1); } //Target path if ((((object) onPath == null) || (onPath.Count == 0)) && (string.Compare("PasajeroAeronave", facet, true) == 0) && (!force)) return lAlias; return PasajeroAeronaveData.AddPath(onSql, joinType, facet, onPath, lOnPath, "", force, isLinkedTo); }
private static string AeronaveFacetAddSql(JoinType joinType, ONSqlSelect onSql, ONPath onPath, bool force, bool isLinkedTo) { //Target table string lAliasFacet = ""; string lAlias = onSql.CreateAlias(joinType, "", CtesBD.TBL_AERONAVE, onPath, "Aeronave", false, isLinkedTo); // Load facet from 'Aeronave' to 'Aeronave' lAliasFacet = onSql.GetAlias("Aeronave", onPath, isLinkedTo); if ((lAliasFacet == "") || force) { if (force) lAliasFacet = onSql.CreateAlias(joinType, "", CtesBD.TBL_AERONAVE, onPath, "Aeronave", force, isLinkedTo); else lAliasFacet = onSql.CreateAlias(joinType, lAlias, CtesBD.TBL_AERONAVE, onPath, "Aeronave", force, isLinkedTo); onSql.AddAliasWhere(lAliasFacet, lAlias + "." + CtesBD.FLD_AERONAVE_ID_AERONAVE + " = " + lAliasFacet + "." + CtesBD.FLD_AERONAVE_ID_AERONAVE); } return lAliasFacet; }
///<summary> This method adds the order criteria to the SQL statement </summary> ///<param name = "onSql"> This parameter represents the SQL component </param> ///<param name = "comparer"> This parameter has all the information refering to the order criteria to add to SQL statement</param> /// <param name="startRowOid">This parameter has the OID necessary to start the search</param> /// <param name="blockSize">This parameter represents the number of instances to be returned</param> protected override void AddOrderCriteria(ONSqlSelect onSql, ONOrderCriteria comparer, ONOid startRowOid, int blockSize, ONPath initialPath) { //Initilizate StartRow AeronaveInstance lInstance = null; if (startRowOid != null) { lInstance = new AeronaveInstance(OnContext); lInstance.Oid = startRowOid as AeronaveOid; } //Default OrderCriteria if (comparer == null) { string lAlias = onSql.GetAlias("Aeronave", initialPath); if (lInstance != null) { onSql.AddOrderBy(lAlias, CtesBD.FLD_AERONAVE_ID_AERONAVE, OrderByTypeEnumerator.Asc, lInstance.Oid.Id_AeronaveAttr); } else { onSql.AddOrderBy(lAlias, CtesBD.FLD_AERONAVE_ID_AERONAVE, OrderByTypeEnumerator.Asc, null); } return; } //Add OrderCriteria bool lUseStartRow = (!comparer.InMemory); foreach (ONOrderCriteriaItem lOrderCriteriaItem in comparer.OrderCriteriaSqlItem) { ONPath lPath = new ONPath(lOrderCriteriaItem.OnPath); if((lInstance != null) && (lUseStartRow)) { ONSimpleType lAttrStartRow = null; if (lPath.Path == "") lAttrStartRow = lInstance[lOrderCriteriaItem.Attribute] as ONSimpleType; else { ONCollection lCollection = (lInstance[lPath.Path] as ONCollection); if((lCollection != null) && (lCollection.Count > 0)) lAttrStartRow = lCollection[0][lOrderCriteriaItem.Attribute] as ONSimpleType; } onSql.AddOrderBy(AeronaveData.AddPath(onSql, JoinType.LeftJoin, lOrderCriteriaItem.Facet, lPath, null, lOrderCriteriaItem.DomainArgument, false), lOrderCriteriaItem.Attribute, lOrderCriteriaItem.Type, lAttrStartRow); lUseStartRow = (lAttrStartRow != null); } else onSql.AddOrderBy(AeronaveData.AddPath(onSql, JoinType.LeftJoin, lOrderCriteriaItem.Facet, lPath, null, lOrderCriteriaItem.DomainArgument, false), lOrderCriteriaItem.Attribute, lOrderCriteriaItem.Type, null); } return; }
/// <summary>This method adds to the SQL statement any path that appears in a formula</summary> /// <param name="onSql">This parameter has the current SQL statement</param> /// <param name="joinType">This parameter has the type of join</param> /// <param name="facet">First class, the beginning of the path</param> /// <param name="onPath">Path to add to SQL statement</param> /// <param name="processedOnPath">Path pocessed until the call of this method</param> /// <param name="initialClass">Domain of the object valued argument, object valued filter variables or AGENT when it should be necessary</param> /// <param name="forceLastAlias">Create almost the last alias in the sql</param> /// <param name="isLinkedTo">The alias belongs to a role in a linked To element</param> public static string AddPath(ONSqlSelect onSql, JoinType joinType, string facet, ONPath onPath, ONPath processedOnPath, string initialClass, bool forceLastAlias, bool isLinkedTo) { // initialClass is used for Object-valued arguments, object-valued filter variables, agent instance, ... ONPath lProcessedOnPath = new ONPath(processedOnPath); ONPath lOnPath = new ONPath(onPath); bool lOnPathExist = true; object[] lParameters = new object[8]; if (initialClass != "") { string lRol = lOnPath.RemoveHead(); lProcessedOnPath += lRol; // Solve path with initialPath lParameters[0] = onSql; lParameters[1] = joinType; lParameters[2] = facet; lParameters[3] = lOnPath; lParameters[4] = lProcessedOnPath; lParameters[5] = ""; lParameters[6] = forceLastAlias; lParameters[7] = isLinkedTo; return ONContext.InvoqueMethod(ONContext.GetType_Data(initialClass), "AddPath", lParameters) as string; } // Search max solved path ONPath lMaxSolvedPath = new ONPath(onPath); string lMaxSolvedPathDomain = facet; while ((lMaxSolvedPath.Count > 0) && (onSql.GetAlias(lMaxSolvedPathDomain, lProcessedOnPath + lMaxSolvedPath, isLinkedTo) == "")) { lMaxSolvedPath.RemoveTail(); lMaxSolvedPathDomain = GetTargetClassName(lMaxSolvedPath); } if (lMaxSolvedPath.Count > 0) { lProcessedOnPath += lMaxSolvedPath; for (int i = 0; i < lMaxSolvedPath.Count; i++) lOnPath.RemoveHead(); lParameters[0] = onSql; lParameters[1] = joinType; lParameters[2] = facet; lParameters[3] = lOnPath; lParameters[4] = lProcessedOnPath; lParameters[5] = ""; lParameters[6] = forceLastAlias; lParameters[7] = isLinkedTo; return ONContext.InvoqueMethod(ONContext.GetType_Data(lMaxSolvedPathDomain), "AddPath", lParameters) as string; } // Create inheritance path if ((onPath == null) || (onPath.Count == 0)) { if (forceLastAlias) return AeronaveFacetAddSql(joinType, onSql, processedOnPath, forceLastAlias, isLinkedTo); if ((processedOnPath == null) || (processedOnPath.Count == 0)) return (onSql.CreateAlias(joinType, "", CtesBD.TBL_AERONAVE, null, "Aeronave", false, isLinkedTo)); else return (onSql.CreateAlias(joinType, "", CtesBD.TBL_AERONAVE, processedOnPath, "Aeronave", false, isLinkedTo)); } // Calculate processed path string lRole = lOnPath.RemoveHead() as string; lProcessedOnPath += lRole; // Search Path if (lOnPath.Count == 0) { string lAlias = onSql.GetAlias(facet, lProcessedOnPath, isLinkedTo); if ((lAlias != "") && (!forceLastAlias)) return (lAlias); else lOnPathExist = false; } else { string lTargetClass = GetTargetClassName(new ONPath(lRole)); // Agent & OV Argument Control if ((lTargetClass == "") && (initialClass != "")) lTargetClass = initialClass; string lAlias = onSql.GetAlias(lTargetClass, lProcessedOnPath, isLinkedTo); if (lAlias == "") lOnPathExist = false; } // Create path if (string.Compare(lRole, "PasajeroAeronave", true) == 0) { if (lOnPathExist) return PasajeroAeronaveData.AddPath(onSql, joinType, facet, lOnPath, lProcessedOnPath, "", forceLastAlias, isLinkedTo); else return PasajeroAeronaveRoleAddSql(onSql, joinType, facet, lOnPath, processedOnPath, lRole, forceLastAlias, isLinkedTo); } initialClass = "Aeronave"; // Solve path with initialPath lParameters[0] = onSql; lParameters[1] = joinType; lParameters[2] = facet; lParameters[3] = lOnPath; lParameters[4] = lProcessedOnPath; lParameters[5] = ""; lParameters[6] = forceLastAlias; lParameters[7] = isLinkedTo; return ONContext.InvoqueMethod(ONContext.GetType_Data(initialClass), "AddPath", lParameters) as string; }
/// <summary> /// Generate Select Sql /// </summary> /// <returns>Sql string</returns> public override string GenerateSQL(out ArrayList sqlParameters) { StringBuilder lTextBuilder = new StringBuilder(); sqlParameters = new ArrayList(); #region Select StringBuilder lTextSelect = new StringBuilder(); foreach (string lSelectAttribute in mSelectAttributes) { if (lTextSelect.Length > 0) { lTextSelect.Append(", "); } lTextSelect.Append(lSelectAttribute); } sqlParameters.AddRange(mSelectParametersItem); lTextBuilder.Append("SELECT "); if (mIsDistinct) { lTextBuilder.Append("DISTINCT "); } lTextBuilder.Append(lTextSelect); #endregion StringBuilder lTextWhereJoin = new StringBuilder(); #region From bool lGenerateFrom = false; if (mFrom.Count > 0) { foreach (ONSqlAlias lOnSqlAlias in mFrom.Values) { if ((lOnSqlAlias.FatherAlias == null) || ((SuperQuery != null) && (SuperQuery.GetAlias(lOnSqlAlias.FatherAlias.Table, lOnSqlAlias.FatherAlias.OnPath) != ""))) { lGenerateFrom = true; break; } } } if (lGenerateFrom) { StringBuilder lTextFrom = new StringBuilder(); foreach (ONSqlAlias lOnSqlAlias in mFrom.Values) { if ((lOnSqlAlias.FatherAlias == null) || ((SuperQuery != null) && (SuperQuery.GetOnSqlAlias(lOnSqlAlias.FatherAlias.Alias) != null))) { foreach (string lConjunction in lOnSqlAlias.Wheres) { mWhereConjuntion.Add(lConjunction); } if (lTextFrom.Length > 0) { lTextFrom.Append(", "); } lTextFrom.Append(lOnSqlAlias.Table); lTextFrom.Append(" "); lTextFrom.Append(lOnSqlAlias.Alias); GenerateSQL_Join(lOnSqlAlias, lTextFrom, lTextWhereJoin); } } lTextBuilder.Append(" FROM "); lTextBuilder.Append(lTextFrom); } #endregion #region Where if ((mOrderByDisjuntion.Count > 0) || (mWhereConjuntion.Count > 0) || (mWhereDisjunction.Count > 0) || (lTextWhereJoin.Length > 0)) { StringBuilder lTextWhere = new StringBuilder(); #region Left Join if (lTextWhereJoin.Length > 0) { if (lTextWhere.Length > 0) { lTextWhere.Append(" AND "); } if ((lTextWhereJoin.Length > 1) || (mOrderByDisjuntion.Count > 0) || (mWhereConjuntion.Count > 0)) { lTextWhere.Append("("); } lTextWhere.Append(lTextWhereJoin); if ((lTextWhereJoin.Length > 1) || (mOrderByDisjuntion.Count > 0) || (mWhereConjuntion.Count > 0)) { lTextWhere.Append(")"); } } #endregion #region Order by (where) if (mOrderByDisjuntion.Count > 0) { if (lTextWhere.Length > 0) { lTextWhere.Append(" AND "); } if ((lTextWhereJoin.Length > 0) || (mWhereConjuntion.Count > 0) || (mWhereDisjunction.Count > 0)) { lTextWhere.Append("("); } StringBuilder lTextWhereTemp = new StringBuilder(); for (int i = 0; i < mOrderByDisjuntion.Count; i++) { if (lTextWhereTemp.Length != 0) { lTextWhereTemp.Append(" OR "); } if (mOrderByDisjuntion.Count > 1) { lTextWhereTemp.Append("("); } lTextWhereTemp.Append(mOrderByDisjuntion[i]); if (mOrderByDisjuntion.Count > 1) { lTextWhereTemp.Append(")"); } } lTextWhere.Append(lTextWhereTemp); if ((lTextWhereJoin.Length > 0) || (mWhereConjuntion.Count > 0) || (mWhereDisjunction.Count > 0)) { lTextWhere.Append(")"); } } for (int i = 0; i < OrderByParameters.Count; i++) { for (int j = 0; j <= i; j++) { sqlParameters.Add(OrderByParameters[j]); } } sqlParameters.AddRange(OrderByParameters); #endregion #region Where if (mWhereConjuntion.Count > 0) { foreach (string lItem in mWhereConjuntion) { if (lTextWhere.Length > 0) { lTextWhere.Append(" AND "); } if ((lTextWhereJoin.Length > 0) || (mOrderByDisjuntion.Count > 0) || (mWhereConjuntion.Count > 1)) { lTextWhere.Append("("); } lTextWhere.Append(lItem); if ((lTextWhereJoin.Length > 0) || (mOrderByDisjuntion.Count > 0) || (mWhereConjuntion.Count > 1)) { lTextWhere.Append(")"); } } } sqlParameters.AddRange(mWhereParametersItem); #endregion #region Where (Disjunctions) if (mWhereDisjunction.Count > 0) { StringBuilder lTextWhereDisjunctions = new StringBuilder(); foreach (string lItem in mWhereDisjunction) { if (lTextWhereDisjunctions.Length > 0) { lTextWhereDisjunctions.Append(" OR "); } if (mWhereDisjunction.Count > 1) { lTextWhereDisjunctions.Append("("); } lTextWhereDisjunctions.Append(lItem); if (mWhereDisjunction.Count > 1) { lTextWhereDisjunctions.Append(")"); } } // Add the disjunctions into the sentence as a conjunction if (lTextWhere.Length > 0) { lTextWhere.Append(" AND (").Append(lTextWhereDisjunctions).Append(")"); } else { lTextWhere.Append(lTextWhereDisjunctions); } } sqlParameters.AddRange(mWhereDisjParametersItem); #endregion lTextBuilder.Append(" WHERE "); lTextBuilder.Append(lTextWhere); } #endregion #region Order by if (mOrderBy.Count > 0) { StringBuilder lTextOrderBy = new StringBuilder(); foreach (string lItem in mOrderBy) { if (lTextOrderBy.Length > 0) { lTextOrderBy.Append(", "); } lTextOrderBy.Append(lItem); } lTextBuilder.Append(" ORDER BY "); lTextBuilder.Append(lTextOrderBy); } #endregion // Solve Subqueries lTextBuilder = GenerateSQL_SubQueries(lTextBuilder, ref sqlParameters); return(lTextBuilder.ToString()); }
private static string NaveNodrizaFacetAddSql(JoinType joinType, ONSqlSelect onSql, ONPath onPath, bool force, bool isLinkedTo) { //Target table string lAliasFacet = ""; string lAlias = onSql.CreateAlias(joinType, "", CtesBD.TBL_NAVENODRIZA, onPath, "NaveNodriza", false, isLinkedTo); // Load facet from 'NaveNodriza' to 'NaveNodriza' lAliasFacet = onSql.GetAlias("NaveNodriza", onPath, isLinkedTo); if ((lAliasFacet == "") || force) { if (force) lAliasFacet = onSql.CreateAlias(joinType, "", CtesBD.TBL_NAVENODRIZA, onPath, "NaveNodriza", force, isLinkedTo); else lAliasFacet = onSql.CreateAlias(joinType, lAlias, CtesBD.TBL_NAVENODRIZA, onPath, "NaveNodriza", force, isLinkedTo); onSql.AddAliasWhere(lAliasFacet, lAlias + "." + CtesBD.FLD_NAVENODRIZA_ID_NAVENODRIZA + " = " + lAliasFacet + "." + CtesBD.FLD_NAVENODRIZA_ID_NAVENODRIZA); } return lAliasFacet; }
private static string PasajeroFacetAddSql(JoinType joinType, ONSqlSelect onSql, ONPath onPath, bool force, bool isLinkedTo) { //Target table string lAliasFacet = ""; string lAlias = onSql.CreateAlias(joinType, "", CtesBD.TBL_PASAJERO, onPath, "Pasajero", false, isLinkedTo); // Load facet from 'Pasajero' to 'Pasajero' lAliasFacet = onSql.GetAlias("Pasajero", onPath, isLinkedTo); if ((lAliasFacet == "") || force) { if (force) lAliasFacet = onSql.CreateAlias(joinType, "", CtesBD.TBL_PASAJERO, onPath, "Pasajero", force, isLinkedTo); else lAliasFacet = onSql.CreateAlias(joinType, lAlias, CtesBD.TBL_PASAJERO, onPath, "Pasajero", force, isLinkedTo); onSql.AddAliasWhere(lAliasFacet, lAlias + "." + CtesBD.FLD_PASAJERO_ID_PASAJERO + " = " + lAliasFacet + "." + CtesBD.FLD_PASAJERO_ID_PASAJERO); } return lAliasFacet; }