private void InitStatementString(IOuterJoinLoadable elementPersister, string alias, int batchSize, SqlString subquery) { int joins = CountEntityPersisters(associations); Suffixes = BasicLoader.GenerateSuffixes(joins + 1); int collectionJoins = CountCollectionPersisters(associations) + 1; CollectionSuffixes = BasicLoader.GenerateSuffixes(joins + 1, collectionJoins); SqlStringBuilder whereString = WhereString(alias, oneToManyPersister.KeyColumnNames, subquery, batchSize); string filter = oneToManyPersister.FilterFragment(alias, EnabledFilters); whereString.Insert(0, StringHelper.MoveAndToBeginning(filter)); JoinFragment ojf = MergeOuterJoins(associations); SqlSelectBuilder select = new SqlSelectBuilder(Factory).SetSelectClause( oneToManyPersister.SelectFragment(null, null, alias, Suffixes[joins], CollectionSuffixes[0], true) + SelectString(associations)).SetFromClause(elementPersister.FromTableFragment(alias) + elementPersister.FromJoinFragment(alias, true, true)).SetWhereClause( whereString.ToSqlString()).SetOuterJoins(ojf.ToFromFragmentString, ojf.ToWhereFragmentString + elementPersister.WhereJoinFragment(alias, true, true)); select.SetOrderByClause(OrderBy(associations, oneToManyPersister.GetSQLOrderByString(alias))); if (Factory.Settings.IsCommentsEnabled) { select.SetComment("load one-to-many " + oneToManyPersister.Role); } SqlString = select.ToSqlString(); }
private void InitStatementString(SqlString projection,SqlString condition, string orderBy,string groupBy,LockMode lockMode) { int joins = CountEntityPersisters(associations); Suffixes = BasicLoader.GenerateSuffixes(joins + 1); JoinFragment ojf = MergeOuterJoins(associations); SqlString selectClause = projection ?? new SqlString(persister.SelectFragment(alias, Suffixes[joins]) + SelectString(associations)); SqlSelectBuilder select = new SqlSelectBuilder(Factory) .SetLockMode(lockMode) .SetSelectClause(selectClause) .SetFromClause(Dialect.AppendLockHint(lockMode, persister.FromTableFragment(alias)) +persister.FromJoinFragment(alias, true, true)) .SetWhereClause(condition) .SetOuterJoins(ojf.ToFromFragmentString,ojf.ToWhereFragmentString + WhereFragment) .SetOrderByClause(OrderBy(associations, orderBy)) .SetGroupByClause(groupBy); if (Factory.Settings.IsCommentsEnabled) select.SetComment(Comment); SqlString = select.ToSqlString(); }
public void SelectStringSqlTest() { Configuration cfg = new Configuration(); ISessionFactory factory = cfg.BuildSessionFactory(); ISessionFactoryImplementor factoryImpl = (ISessionFactoryImplementor) factory; SqlSelectBuilder select = new SqlSelectBuilder(factoryImpl); select.SetSelectClause("column1, column2"); select.SetFromClause("select_test", "select_test_alias"); select.SetOuterJoins( new SqlString(" LEFT OUTER JOIN before ON select_test_alias.column1 = before.column1"), new SqlString(" after.some_field = after.another_field ")); select.SetOrderByClause(new SqlString("column1 DESC")); select.SetWhereClause("select_test_alias", new string[] {"identity_column"}, NHibernateUtil.Int64); SqlString sqlString = select.ToSqlString(); string expectedSql = new StringBuilder().Append("SELECT ") .Append("column1, column2 ") .Append("FROM select_test select_test_alias ") .Append("LEFT OUTER JOIN before ON select_test_alias.column1 = before.column1 ") .Append("WHERE ") .Append("after.some_field = after.another_field") .Append(" AND ") .Append("select_test_alias.identity_column = ? ") .Append("ORDER BY column1 DESC") .ToString(); Assert.AreEqual(expectedSql, sqlString.ToString(), "SQL String"); Assert.AreEqual(1, sqlString.GetParameterCount(), "One parameter"); }
protected SqlString GenerateIdInsertSelect(IQueryable persister, string tableAlias, IASTNode whereClause) { var select = new SqlSelectBuilder(Factory); SelectFragment selectFragment = new SelectFragment(Factory.Dialect) .AddColumns(tableAlias, persister.IdentifierColumnNames, persister.IdentifierColumnNames); select.SetSelectClause(selectFragment.ToFragmentString().Substring(2)); string rootTableName = persister.TableName; SqlString fromJoinFragment = persister.FromJoinFragment(tableAlias, true, false); SqlString whereJoinFragment = persister.WhereJoinFragment(tableAlias, true, false); select.SetFromClause(rootTableName + ' ' + tableAlias + fromJoinFragment); if (whereJoinFragment == null) { whereJoinFragment = SqlString.Empty; } else { whereJoinFragment = whereJoinFragment.Trim(); if (whereJoinFragment.StartsWithCaseInsensitive("and ")) { whereJoinFragment = whereJoinFragment.Substring(4); } } SqlString userWhereClause = SqlString.Empty; if (whereClause.ChildCount != 0) { // If a where clause was specified in the update/delete query, use it to limit the // returned ids here... try { var nodes = new CommonTreeNodeStream(whereClause); var gen = new SqlGenerator(Factory, nodes); gen.whereClause(); userWhereClause = gen.GetSQL().Substring(7); } catch (RecognitionException e) { throw new HibernateException("Unable to generate id select for DML operation", e); } if (whereJoinFragment.Length > 0) { whereJoinFragment.Append(" and "); } } select.SetWhereClause(whereJoinFragment + userWhereClause); var insert = new InsertSelect(); if (Factory.Settings.IsCommentsEnabled) { insert.SetComment("insert-select for " + persister.EntityName + " ids"); } insert.SetTableName(persister.TemporaryIdTableName); insert.SetSelect(select); return insert.ToSqlString(); }
private void InitStatementString(string alias, int batchSize, SqlString subquery) { int joins = CountEntityPersisters(associations); int collectionJoins = CountCollectionPersisters(associations) + 1; Suffixes = BasicLoader.GenerateSuffixes(joins); CollectionSuffixes = BasicLoader.GenerateSuffixes(joins, collectionJoins); SqlStringBuilder whereString = WhereString(alias, collectionPersister.KeyColumnNames, subquery, batchSize); string manyToManyOrderBy = string.Empty; string filter = collectionPersister.FilterFragment(alias, EnabledFilters); if (collectionPersister.IsManyToMany) { // from the collection of associations, locate OJA for the // ManyToOne corresponding to this persister to fully // define the many-to-many; we need that OJA so that we can // use its alias here // TODO : is there a better way here? IAssociationType associationType = (IAssociationType)collectionPersister.ElementType; foreach (OuterJoinableAssociation oja in associations) { if (oja.JoinableType == associationType) { // we found it filter += collectionPersister.GetManyToManyFilterFragment(oja.RHSAlias, EnabledFilters); manyToManyOrderBy += collectionPersister.GetManyToManyOrderByString(oja.RHSAlias); } } } whereString.Insert(0, StringHelper.MoveAndToBeginning(filter)); JoinFragment ojf = MergeOuterJoins(associations); SqlSelectBuilder select = new SqlSelectBuilder(Factory) .SetSelectClause(collectionPersister.SelectFragment(alias, CollectionSuffixes[0]) + SelectString(associations)) .SetFromClause(collectionPersister.TableName, alias) .SetWhereClause(whereString.ToSqlString()) .SetOuterJoins(ojf.ToFromFragmentString, ojf.ToWhereFragmentString); select.SetOrderByClause(OrderBy(associations, MergeOrderings(collectionPersister.GetSQLOrderByString(alias), manyToManyOrderBy))); if (Factory.Settings.IsCommentsEnabled) select.SetComment("load collection " + collectionPersister.Role); SqlString = select.ToSqlString(); }
public void SelectStringSqlTest() { Configuration cfg = new Configuration(); ISessionFactory factory = cfg.BuildSessionFactory( ); ISessionFactoryImplementor factoryImpl = (ISessionFactoryImplementor)factory; SqlSelectBuilder select = new SqlSelectBuilder(factoryImpl); select.SetSelectClause("column1, column2"); select.SetFromClause("select_test", "select_test_alias"); select.SetOuterJoins( new SqlString(" LEFT OUTER JOIN before ON select_test_alias.column1 = before.column1"), new SqlString(" LEFT OUTER JOIN after ON select_test_alias.column1 = after.column1") ); select.SetOrderByClause("column1 DESC"); select.SetWhereClause("select_test_alias", new string[] {"identity_column"}, NHibernateUtil.Int64); SqlString sqlString = select.ToSqlString(); string expectedSql = new StringBuilder().Append("SELECT ") .Append("column1, column2 ") .Append("FROM select_test select_test_alias ") .Append("LEFT OUTER JOIN before ON select_test_alias.column1 = before.column1 ") .Append("WHERE select_test_alias.identity_column = :select_test_alias.identity_column ") .Append("LEFT OUTER JOIN after ON select_test_alias.column1 = after.column1 ") .Append("ORDER BY column1 DESC") .ToString(); int numOfParams = 0; Parameter expectedParam = null; foreach(object part in sqlString.SqlParts) { if(part is Parameter) { numOfParams++; expectedParam = (Parameter)part; } } Assert.AreEqual(expectedSql , sqlString.ToString(), "SQL String"); Assert.AreEqual(1, numOfParams, "One parameter"); Parameter firstParam = new Parameter( "identity_column", "select_test_alias", new SqlTypes.Int64SqlType() ); Assert.AreEqual(firstParam.SqlType.DbType, expectedParam.SqlType.DbType, "First Parameter Type"); Assert.AreEqual(firstParam.Name, expectedParam.Name, "First Parameter Name"); }
public virtual object[] GetNaturalIdentifierSnapshot(object id, ISessionImplementor session) { if (!HasNaturalIdentifier) { throw new MappingException("persistent class did not define a natural-id : " + MessageHelper.InfoString(this)); } if (log.IsDebugEnabled) { log.Debug("Getting current natural-id snapshot state for: " + MessageHelper.InfoString(this, id, Factory)); } int[] naturalIdPropertyIndexes = NaturalIdentifierProperties; int naturalIdPropertyCount = naturalIdPropertyIndexes.Length; bool[] naturalIdMarkers = new bool[PropertySpan]; IType[] extractionTypes = new IType[naturalIdPropertyCount]; for (int i = 0; i < naturalIdPropertyCount; i++) { extractionTypes[i] = PropertyTypes[naturalIdPropertyIndexes[i]]; naturalIdMarkers[naturalIdPropertyIndexes[i]] = true; } /////////////////////////////////////////////////////////////////////// // TODO : look at perhaps caching this... SqlSelectBuilder select = new SqlSelectBuilder(Factory); if (Factory.Settings.IsCommentsEnabled) { select.SetComment("get current natural-id state " + EntityName); } select.SetSelectClause(ConcretePropertySelectFragmentSansLeadingComma(RootAlias, naturalIdMarkers)); select.SetFromClause(FromTableFragment(RootAlias) + FromJoinFragment(RootAlias, true, false)); string[] aliasedIdColumns = StringHelper.Qualify(RootAlias, IdentifierColumnNames); SqlString whereClause = new SqlStringBuilder() .Add(StringHelper.Join(new SqlString("=", Parameter.Placeholder, " and "), aliasedIdColumns)) .Add("=").AddParameter() .Add(WhereJoinFragment(RootAlias, true, false)) .ToSqlString(); SqlString sql = select.SetOuterJoins(SqlString.Empty, SqlString.Empty).SetWhereClause(whereClause).ToStatementString(); /////////////////////////////////////////////////////////////////////// object[] snapshot = new object[naturalIdPropertyCount]; using (new SessionIdLoggingContext(session.SessionId)) try { IDbCommand ps = session.Batcher.PrepareCommand(CommandType.Text, sql, IdentifierType.SqlTypes(factory)); IDataReader rs = null; try { IdentifierType.NullSafeSet(ps, id, 0, session); rs = session.Batcher.ExecuteReader(ps); //if there is no resulting row, return null if (!rs.Read()) { return null; } for (int i = 0; i < naturalIdPropertyCount; i++) { snapshot[i] = extractionTypes[i].Hydrate(rs, GetPropertyAliases(string.Empty, naturalIdPropertyIndexes[i]), session, null); if (extractionTypes[i].IsEntityType) { snapshot[i] = extractionTypes[i].ResolveIdentifier(snapshot[i], session, null); } } return snapshot; } finally { session.Batcher.CloseCommand(ps, rs); } } catch (DbException sqle) { var exceptionContext = new AdoExceptionContextInfo { SqlException = sqle, Message = "could not retrieve snapshot: " + MessageHelper.InfoString(this, id, Factory), Sql = sql.ToString(), EntityName = EntityName, EntityId = id }; throw ADOExceptionHelper.Convert(Factory.SQLExceptionConverter, exceptionContext); } }
protected SqlString RenderSelect(int[] tableNumbers, int[] columnNumbers, int[] formulaNumbers) { Array.Sort(tableNumbers); //get 'em in the right order (not that it really matters) //render the where and from parts int drivingTable = tableNumbers[0]; string drivingAlias = GenerateTableAlias(RootAlias, drivingTable); //we *could* regenerate this inside each called method! SqlString where = CreateWhereByKey(drivingTable, drivingAlias); string from = CreateFrom(drivingTable, drivingAlias); //now render the joins JoinFragment jf = CreateJoin(tableNumbers, drivingAlias); //now render the select clause SelectFragment selectFragment = CreateSelect(columnNumbers, formulaNumbers); //now tie it all together SqlSelectBuilder select = new SqlSelectBuilder(Factory); select.SetSelectClause(selectFragment.ToFragmentString().Substring(2)); select.SetFromClause(from); select.SetWhereClause(where); select.SetOuterJoins(jf.ToFromFragmentString, jf.ToWhereFragmentString); if (Factory.Settings.IsCommentsEnabled) { select.SetComment("sequential select " + EntityName); } return select.ToSqlString(); }
protected virtual SqlString GenerateSnapshotSelectString() { //TODO: should we use SELECT .. FOR UPDATE? SqlSelectBuilder select = new SqlSelectBuilder(Factory); if (Factory.Settings.IsCommentsEnabled) { select.SetComment("get current state " + EntityName); } string[] aliasedIdColumns = StringHelper.Qualify(RootAlias, IdentifierColumnNames); string selectClause = StringHelper.Join(StringHelper.CommaSpace, aliasedIdColumns) + ConcretePropertySelectFragment(RootAlias, PropertyUpdateability); SqlString fromClause = new SqlString(FromTableFragment(RootAlias)) + FromJoinFragment(RootAlias, true, false); SqlString joiner = new SqlString("=", Parameter.Placeholder, " and "); SqlStringBuilder whereClauseBuilder = new SqlStringBuilder() .Add(StringHelper.Join(joiner, aliasedIdColumns)) .Add("=") .AddParameter() .Add(WhereJoinFragment(RootAlias, true, false)); // H3.2 the Snapshot is what we found in DB without take care on version //if (IsVersioned) //{ // whereClauseBuilder.Add(" and ") // .Add(VersionColumnName) // .Add("=") // .AddParameter(); //} return select.SetSelectClause(selectClause) .SetFromClause(fromClause) .SetOuterJoins(SqlString.Empty, SqlString.Empty) .SetWhereClause(whereClauseBuilder.ToSqlString()) .ToSqlString(); }
private SqlString GenerateGeneratedValuesSelectString(ValueInclusion[] inclusions) { SqlSelectBuilder select = new SqlSelectBuilder(Factory); if (Factory.Settings.IsCommentsEnabled) { select.SetComment("get generated state " + EntityName); } string[] aliasedIdColumns = StringHelper.Qualify(RootAlias, IdentifierColumnNames); // Here we render the select column list based on the properties defined as being generated. // For partial component generation, we currently just re-select the whole component // rather than trying to handle the individual generated portions. string selectClause = ConcretePropertySelectFragment(RootAlias, inclusions); selectClause = selectClause.Substring(2); string fromClause = FromTableFragment(RootAlias) + FromJoinFragment(RootAlias, true, false); SqlString whereClause = new SqlStringBuilder() .Add(StringHelper.Join(new SqlString("=", Parameter.Placeholder, " and "), aliasedIdColumns)) .Add("=").AddParameter() .Add(WhereJoinFragment(RootAlias, true, false)) .ToSqlString(); return select.SetSelectClause(selectClause) .SetFromClause(fromClause) .SetOuterJoins(SqlString.Empty, SqlString.Empty) .SetWhereClause(whereClause) .ToSqlString(); }
public virtual InsertSelect SetSelect(SqlSelectBuilder select) { this.select = select; return this; }
public virtual InsertSelect SetSelect(SqlSelectBuilder select) { this.select = select; return(this); }
private void InitStatementString( IQueryableCollection persister, string alias, IList associations, int batchSize, ISessionFactoryImplementor factory ) { Suffixes = GenerateSuffixes( associations.Count ); SqlStringBuilder whereString = WhereString( factory, alias, persister.KeyColumnNames, persister.KeyType, batchSize ); if( persister.HasWhere ) { whereString .Add( " and " ) .Add( persister.GetSQLWhereString( alias ) ); } JoinFragment ojf = MergeOuterJoins( associations ); SqlSelectBuilder select = new SqlSelectBuilder( factory ) .SetSelectClause( persister.SelectFragment( alias ).Append( SelectString( associations, factory ) ).ToString() ) .SetFromClause( persister.TableName, alias ) .SetWhereClause( whereString.ToSqlString() ) .SetOuterJoins( ojf.ToFromFragmentString, ojf.ToWhereFragmentString ); if( persister.HasOrdering ) { select.SetOrderByClause( persister.GetSQLOrderByString( alias ) ); } SqlString = select.ToSqlString(); }
protected virtual SqlString GenerateSnapshotSelectString() { //TODO: should we use SELECT .. FOR UPDATE? SqlSelectBuilder select = new SqlSelectBuilder(Factory); //if (Factory.Settings.IsCommentsEnabled) //{ // select.SetComment("get current state " + ClassName); //} string[] aliasedIdColumns = StringHelper.Qualify(RootAlias, IdentifierColumnNames); string selectClause = StringHelper.Join(", ", aliasedIdColumns) + ConcretePropertySelectFragment(RootAlias, PropertyUpdateability); SqlString fromClause = new SqlString(FromTableFragment(RootAlias)) + FromJoinFragment(RootAlias, true, false); SqlString joiner = new SqlString("=", Parameter.Placeholder, " and "); SqlString whereClause = new SqlStringBuilder() .Add(StringHelper.Join(joiner, aliasedIdColumns)) .Add("=") .AddParameter() .Add(WhereJoinFragment(RootAlias, true, false)) .ToSqlString(); // TODO H3: this is commented out in H3.2 if (IsVersioned) { whereClause.Append(" and ") .Append(VersionColumnName) .Append("=?"); } return select.SetSelectClause(selectClause) .SetFromClause(fromClause) .SetOuterJoins(SqlString.Empty, SqlString.Empty) .SetWhereClause(whereClause) .ToSqlString(); }