public void AddColumn(IDataContext db, IFieldDescriptor field) { if (db == null) { throw new ArgumentNullException("session"); } if (field == null) { throw new ArgumentNullException("field"); } var sqlType = PgSqlTypeConverter.GetSqlType(field); var sql = String.Format(CultureInfo.InvariantCulture, @"alter table ""{0}"" add column ""{1}"" {2}", this.Name, field.Name, sqlType); db.Execute(SqlString.Parse(sql)); this.SetColumnComment(db, field); if (field.IsUnique) { this.AddUniqueConstraint(db, field.Name); } }
public override SqlQuery PageQuery(SqlQuery sqlQuery, PagingOptions pagingOptions) { if (sqlQuery == null) { throw new ArgumentNullException("sqlQuery"); } var arguments = new SqlArgument[sqlQuery.Arguments.Count + 2]; Array.Copy(sqlQuery.ArgumentsArray, 0, arguments, 0, sqlQuery.Arguments.Count); arguments[arguments.Length - 2] = new SqlArgument(pagingOptions.Offset, DbType.Int32); arguments[arguments.Length - 1] = new SqlArgument(pagingOptions.Count, DbType.Int32); var sqlString = SqlString.Parse(sqlQuery.CommandText, Clauses.OrderBy); var commandText = string.IsNullOrEmpty(sqlString.OrderBy) ? sqlQuery.CommandText + " ORDER BY CURRENT_TIMESTAMP" : sqlQuery.CommandText; var stringBuilder = new StringBuilder(commandText) .Replace(Environment.NewLine, string.Empty) .Append(" OFFSET ") .Append(this.SqlCharacters.GetParameterName(arguments.Length - 2)) .Append(" ROWS FETCH NEXT ") .Append(this.SqlCharacters.GetParameterName(arguments.Length - 1)) .Append(" ROWS ONLY"); return(new SqlQuery(stringBuilder.ToString(), arguments)); }
protected string GetTransformedSql(string sqlQuery) { var sql = SqlString.Parse(sqlQuery); var command = jetDriver.GenerateCommand(CommandType.Text, sql, dummyParameterTypes); return(command.CommandText); }
public void AddFK(IDataContext db, string columnName, string refTable, OnDeleteAction act) { if (db == null) { throw new ArgumentNullException("session"); } if (string.IsNullOrEmpty(columnName)) { throw new ArgumentNullException("columnName"); } if (!NamingRule.IsValidSqlName(columnName)) { throw new ArgumentOutOfRangeException("columnName"); } if (string.IsNullOrEmpty(refTable)) { throw new ArgumentNullException("refTable"); } var onDelete = OnDeleteMapping[act]; var fkName = this.GenerateFkName(columnName); var sql = string.Format(CultureInfo.InvariantCulture, "alter table \"{0}\" add constraint \"{1}\" foreign key (\"{2}\") references \"{3}\" on delete {4}", this.Name, fkName, columnName, refTable, onDelete); db.Execute(SqlString.Parse(sql)); }
private void NodeMoveTo(long nodeLeft, long nodeRight, long nodeWidth, long newParentID) { var session = this.DbDomain.CurrentSession; //TODO 检查父节点不存在的异常 long insertPos = 0; if (newParentID > 0) { var ids = new long[] { newParentID }; var newParent = this.ReadInternal(ids, SearchParentNodeFields).First(); insertPos = (long)newParent[LeftFieldName] + 1; } else { insertPos = 1; } //检查递归 if (nodeLeft < insertPos && insertPos <= nodeRight) { throw new Exceptions.DataException("Cannot create a recursion node"); } string sql; //nested-set 的算法就是把一个二维树转换成一维的线段 sql = String.Format( CultureInfo.InvariantCulture, "update {0} set _left=_left+{1} where _left>={2}", this.quotedTableName, nodeWidth, insertPos); session.DataContext.Execute(SqlString.Parse(sql)); sql = String.Format( CultureInfo.InvariantCulture, "update {0} set _right=_right+{1} where _right>={2}", this.quotedTableName, nodeWidth, insertPos); session.DataContext.Execute(SqlString.Parse(sql)); if (nodeLeft < insertPos) //往左移动 { long moveDistance = insertPos - nodeLeft; sql = String.Format( CultureInfo.InvariantCulture, "update {0} set _left=_left+{1}, _right=_right+{1} where _left>={2} and _left<{3}", this.quotedTableName, moveDistance, nodeLeft, nodeRight); session.DataContext.Execute(SqlString.Parse(sql)); } else //往右移动 { long moveDistance = nodeLeft - insertPos; sql = String.Format( CultureInfo.InvariantCulture, "update {0} set _left=_left-{1}, _right=_right-{1} where _left>={2} and _left<{3}", this.quotedTableName, moveDistance + nodeWidth, nodeLeft + nodeWidth, nodeRight + nodeWidth); session.DataContext.Execute(SqlString.Parse(sql)); } //最后一步不需要执行了,之前已经更新过 parent }
public void GetSubselectStringWithFormulaProperty() { SqlString sql = SqlString.Parse("select (select foo from bar where foo=col order by foo) from table where col = ? order by col"); Assert.AreEqual(" from table where col = ? ", sql.GetSubselectString().ToString()); }
private static void VerifyTokenizer(string sql, params ExpectedToken[] expectedTokens) { var sqlString = SqlString.Parse(sql); int tokenIndex = 0; int sqlIndex = 0; foreach (var token in new SqlTokenizer(sqlString) { IgnoreComments = false, IgnoreWhitespace = false }) { if (tokenIndex >= expectedTokens.Length) { Assert.Fail("Tokenizer returns more than expected '{0}' tokens. \nSQL: {1}\nLast Token: {2}({3})", expectedTokens.Length, sql, token.TokenType, token.Value); } var expectedToken = expectedTokens[tokenIndex]; Assert.That(token.TokenType, Is.EqualTo(expectedToken.TokenType), "[Token #{0} in '{1}']TokenType", tokenIndex, sql); Assert.That(token.Value, Is.EqualTo(expectedToken.Value), "[Token #{0} in {1}]Value", tokenIndex, sql); Assert.That(token.SqlIndex, Is.EqualTo(sqlIndex), "[Token #{0} in {1}]SqlIndex", tokenIndex, sql); Assert.That(token.Length, Is.EqualTo(expectedToken.Length), "[Token #{0} in {1}]Length", tokenIndex, sql); tokenIndex++; sqlIndex += expectedToken.Length; } if (tokenIndex < expectedTokens.Length) { Assert.Fail("Tokenizer returns less than expected '{0}' tokens.\nSQL: {1}", expectedTokens.Length, sql); } }
public override SqlString Render(IList args, ISessionFactoryImplementor factory) { return(SqlString.Parse( args.Cast <object>() .Select(x => x.ToString()) .ToString(x => "ISNULL(" + x + ", '')", " + ' ' + ") )); }
public void GetSubselectStringWithSubselectInWhere() { SqlString sql = SqlString.Parse( "select (select foo from bar where foo=col order by foo) from table where col = (select yadda from blah where yadda=x order by yadda) order by col"); Assert.AreEqual(" from table where col = (select yadda from blah where yadda=x order by yadda) ", sql.GetSubselectString().ToString()); }
public WhenParsingMultiLineCommandText_ContainingAllClauses_SpecifyingAllClauses() { this.sqlString = SqlString.Parse( @"SELECT Column1, Column2, Column3 FROM Table WHERE Column1 = @p0 AND Column2 > @p1 GROUP BY Column3 ORDER BY Column2 DESC", Clauses.Select | Clauses.From | Clauses.Where | Clauses.GroupBy | Clauses.OrderBy); }
private static void SetTableComment(IDataContext db, string tableName, string label) { if (!String.IsNullOrEmpty(label)) { label = label.SqlEscape(); var sql = String.Format(CultureInfo.InvariantCulture, @"comment on table ""{0}"" is '{1}'", tableName, label.SqlEscape()); db.Execute(SqlString.Parse(sql)); } }
public override bool IsInitialized() { var sql = SqlString.Parse(@" select distinct count(table_name) from information_schema.tables where table_name in ('core_module', 'core_meta_entity', 'core_meta_field', 'core_session') "); var rowCount = (long)this.QueryValue(sql); return(rowCount == 3); }
private void SetColumnComment(IDataContext db, IFieldDescriptor field) { if (!string.IsNullOrEmpty(field.Label)) { //添加注释 var commentSql = String.Format(CultureInfo.InvariantCulture, @"comment on column ""{0}"".""{1}"" is '{2}'", this.Name, field.Name, field.Label.SqlEscape()); db.Execute(SqlString.Parse(commentSql)); } }
private long[] GetDescendantIDs(IDataContext dbctx, long parentID) { var sqlFmt = @" select hc.* from {0} hp join {0} hc ON hc._left between hp._left and hp._right where hp._id=? and hc._id<>? "; var sql = string.Format(CultureInfo.InvariantCulture, sqlFmt, this.quotedTableName); var ids = dbctx.QueryAsArray <long>(SqlString.Parse(sql), parentID, parentID); return(ids.ToArray()); }
public async Task SaveAsync() { await(DeleteDataAsync()); ISession s = OpenSession(); ITransaction t = s.BeginTransaction(); Widget obj = new Widget(); obj.ValueThree = 5; int id = (int)await(s.SaveAsync(obj)); await(t.CommitAsync()); s.Close(); s = OpenSession(); t = s.BeginTransaction(); IDriver driver = Sfi.ConnectionProvider.Driver; var connection = s.Connection; var statement = driver.GenerateCommand( CommandType.Text, SqlString.Parse("SELECT * FROM STRANGE_TYPED_OBJECT WHERE ID=?"), new SqlType[] { SqlTypeFactory.Int32 }); statement.Connection = connection; t.Enlist(statement); statement.Parameters[0].Value = id; var reader = await(statement.ExecuteReaderAsync()); Assert.IsTrue(await(reader.ReadAsync()), "A row should have been returned"); Assert.IsTrue(reader.GetValue(reader.GetOrdinal("VALUE_ONE")) == DBNull.Value, "Default value should have been mapped to null"); Assert.IsTrue(reader.GetValue(reader.GetOrdinal("VALUE_TWO")) == DBNull.Value, "Default value should have been mapped to null"); Assert.AreEqual(Convert.ToInt32(reader.GetValue(reader.GetOrdinal("VALUE_THREE"))), 5, "Non-Default value should not be changed"); Assert.IsTrue(reader.GetValue(reader.GetOrdinal("VALUE_FOUR")) == DBNull.Value, "Default value should have been mapped to null"); reader.Close(); await(t.CommitAsync()); s.Close(); await(DeleteDataAsync()); }
public void Clause(string clause) { Clause(SqlString.Parse(clause)); /* * if (argInd == args.Count) * { * args.Add(clause); * } * else * { * args[argInd] = args[argInd] + clause; * } */ }
private void UpdateTreeForDeletion(long[] ids, AbstractSqlModel tableModel) { var records = from r in tableModel.ReadInternal(ids, HierarchyFields) select new { ID = (long)r[IdFieldName], Left = (long)r[LeftFieldName], Right = (long)r[RightFieldName] }; //这里是做化简,如果 ids 里面有的节点 id 已经是被其它节点包含了,那么就去掉 //TODO 这里是个 O(n^2) 的复杂度,应该可以用二分搜索优化的 //先删最右侧的很重要,否则 parentRecords 是保存在内存里的,没法反应出后面的 update 语句带来的更改 var parentRecords = from r in records where !records.Any(i => i.Left <r.Left && i.Right> r.Right) orderby r.Right descending select r; var ctx = this.DbDomain.CurrentSession; ctx.DataContext.LockTable(tableModel.TableName); foreach (var record in parentRecords) { var width = record.Right - record.Left; //删除指定ID节点及其下面包含的子孙节点 var sql = new SqlString( "delete from ", tableModel.quotedTableName, " where ", LeftFieldName, " between ", Parameter.Placeholder, " and ", Parameter.Placeholder); ctx.DataContext.Execute(sql, record.Left, record.Right); //更新所有右边节点的 _left 与 _right var sqlUpdate1 = String.Format(CultureInfo.InvariantCulture, "update {0} set _right = _right - {1} where _right > {2}", tableModel.quotedTableName, width + 1, record.Right); var sqlUpdate2 = String.Format(CultureInfo.InvariantCulture, "update {0} set _left = _left - {1} where _left > {2}", tableModel.quotedTableName, width + 1, record.Left); ctx.DataContext.Execute(SqlString.Parse(sqlUpdate1)); ctx.DataContext.Execute(SqlString.Parse(sqlUpdate2)); } }
public override void Create(string dbName) { if (string.IsNullOrEmpty(dbName)) { throw new ArgumentNullException("_dbName"); } LoggerProvider.EnvironmentLogger.Info(String.Format("Creating Database [{0}]...", dbName)); var sql = string.Format(CultureInfo.InvariantCulture, @"create database ""{0}"" template ""template0"" encoding 'unicode' ", dbName); this.Execute(SqlString.Parse(sql)); LoggerProvider.EnvironmentLogger.Info( String.Format("Database [{0}] has been created.", dbName)); }
public void DeleteConstraint(IDataContext dbctx, string constraintName) { if (dbctx == null) { throw new ArgumentNullException("session"); } if (string.IsNullOrEmpty(constraintName)) { throw new ArgumentNullException("constraintName"); } var sql = string.Format(CultureInfo.InvariantCulture, "alter table \"{0}\" drop constraint \"{1}\"", this.Name, constraintName); dbctx.Execute(SqlString.Parse(sql)); }
public override SqlString Render(IList args, ISessionFactoryImplementor factory) { var selectorName = args[0].ToString(); var orderedDaysOfWeek = EnumHelper.GetValues <DayOfWeek>().OrderBy(new DayOfWeekComparer()); return(SqlString.Parse( "CASE " + orderedDaysOfWeek .Select( (dayOfWeek, position) => "WHEN {0} = '{1}' THEN {2} ".FillWith(selectorName, dayOfWeek, position) ) .ToString( x => x, " " ) + "END" )); }
public override SqlString RenderText(Engine.ISessionFactoryImplementor sessionFactory) { var result = SqlString.Parse(Text); // query-parameter = the parameter specified in the NHibernate query // sql-parameter = real parameter/s inside the final SQL // here is where we suppose the SqlString has all sql-parameters in sequence for a given query-parameter. // This happen when the query-parameter spans multiple columns (components,custom-types and so on). if (HasEmbeddedParameters) { var parameters = result.GetParameters().ToArray(); var sqlParameterPos = 0; var paramTrackers = _embeddedParameters.SelectMany(specification => specification.GetIdsForBackTrack(sessionFactory)); foreach (var paramTracker in paramTrackers) { parameters[sqlParameterPos++].BackTrack = paramTracker; } } return result; }
public override SqlString OnPrepareStatement(SqlString sql) { if (sql.StartsWithCaseInsensitive("SELECT")) { var lists = sql.ToString().Split().ToList(); var from = lists.FirstOrDefault(p => p.Trim().Equals("FROM", StringComparison.OrdinalIgnoreCase)); var index = from != null?lists.IndexOf(from) : -1; if (index == -1) { return(sql); } // Add hint with nolock to sql string lists.Insert(lists.IndexOf(from) + 3, "WITH (NOLOCK)"); sql = SqlString.Parse(string.Join(" ", lists)); } return(sql); }
/// <summary> /// Creates an SqlQuery to count the number of records which would be returned by the specified SqlQuery. /// </summary> /// <param name="sqlQuery">The SQL query.</param> /// <returns> /// An <see cref="SqlQuery" /> to count the number of records which would be returned by the specified SqlQuery. /// </returns> public SqlQuery CountQuery(SqlQuery sqlQuery) { if (sqlQuery == null) { throw new ArgumentNullException("sqlQuery"); } if (log.IsDebug) { log.Debug(LogMessages.SqlDialect_CreatingSqlQuery, "COUNT"); } var sqlString = SqlString.Parse(sqlQuery.CommandText, Clauses.From | Clauses.Where); var qualifiedTableName = sqlString.From; var whereValue = sqlString.Where; var whereClause = !string.IsNullOrEmpty(whereValue) ? " WHERE " + whereValue : string.Empty; return(new SqlQuery("SELECT COUNT(*) FROM " + qualifiedTableName + whereClause, sqlQuery.ArgumentsArray)); }
public bool ConstraintExists(IDataContext db, string constraintName) { if (db == null) { throw new ArgumentNullException("session"); } if (string.IsNullOrEmpty(constraintName)) { throw new ArgumentNullException("constraintName"); } var sql = SqlString.Parse(@" select coalesce(count(constraint_name), 0) from information_schema.table_constraints where table_catalog=? and constraint_schema = 'public' and constraint_name=?"); var n = (long)db.QueryValue(sql, db.DatabaseName, constraintName); return(n > 0); }
public override SqlString OnPrepareStatement(SqlString sql) { //var log = new StringBuilder(); //log.Append(sql.ToString()); //log.AppendLine(); // Modify the sql to add hints if (sql.StartsWithCaseInsensitive("select")) { var parts = sql.ToString().Split().ToList(); var fromItem = parts.FirstOrDefault(p => p.Trim().Equals("from", StringComparison.OrdinalIgnoreCase)); int fromIndex = fromItem != null?parts.IndexOf(fromItem) : -1; var whereItem = parts.FirstOrDefault(p => p.Trim().Equals("where", StringComparison.OrdinalIgnoreCase)); int whereIndex = whereItem != null?parts.IndexOf(whereItem) : parts.Count; if (fromIndex == -1) { return(sql); } parts.Insert(parts.IndexOf(fromItem) + 3, "WITH (NOLOCK)"); for (int i = fromIndex; i < whereIndex; i++) { if (parts[i - 1].Equals(",")) { parts.Insert(i + 3, "WITH (NOLOCK)"); i += 3; } if (parts[i].Trim().Equals("on", StringComparison.OrdinalIgnoreCase)) { parts[i] = "WITH (NOLOCK) on"; } } // MUST use SqlString.Parse() method instead of new SqlString() sql = SqlString.Parse(string.Join(" ", parts)); } //log.Append(sql); return(sql); }
/// <summary> /// 层次表获取某指定记录的直系子记录的 IDs /// </summary> /// <param name="dbctx"></param> /// <param name="parentID"></param> /// <returns></returns> private long[] GetChildrenIDs(IDataContext dbctx, long parentID) { var sqlFmt = @" select hc.* from ""{0}"" hp join ""{0}"" hc on hc._left between hp._left and hp._right where hp._id = ? and hc._id <> ? and ( select count(hn._id) from ""{0}"" hn where hc._left between hn._left and hn._right and hn._left between hp._left and hp._right ) <= 2 "; var sql = string.Format(CultureInfo.InvariantCulture, sqlFmt, this.TableName); var ids = dbctx.QueryAsArray <long>(SqlString.Parse(sql), parentID, parentID); return(ids.ToArray()); }
public override SqlString RenderText(ISessionFactoryImplementor sessionFactory) { if (!HasEmbeddedParameters) { // this expression was not changed by MutateRowValueConstructorSyntax return(base.RenderText(sessionFactory)); } var result = SqlString.Parse(Text); // query-parameter = the parameter specified in the NHibernate query // sql-parameter = real parameter/s inside the final SQL // here is where we suppose the SqlString has all sql-parameters in sequence for a given query-parameter. // This happen when the query-parameter spans multiple columns (components,custom-types and so on). var parameters = result.GetParameters().ToArray(); var sqlParameterPos = 0; var paramTrackers = embeddedParameters.SelectMany(specification => specification.GetIdsForBackTrack(sessionFactory)); foreach (var paramTracker in paramTrackers) { parameters[sqlParameterPos++].BackTrack = paramTracker; } return(result); }
public void Parse() { SqlString sql = SqlString.Parse("select col from table where col = ? or col1 = 'a?b' and col2 = ? and col3 = 'x' and col4 = ?"); SqlString expectedSql = new SqlString( new object[] { "select col from table where col = ", Parameter.Placeholder, " or col1 = 'a?b' and col2 = ", Parameter.Placeholder, " and col3 = 'x' and col4 = ", Parameter.Placeholder } ); Assert.AreEqual(expectedSql, sql); Assert.AreEqual(new SqlString("simple"), SqlString.Parse("simple")); Assert.AreEqual(SqlString.Empty, SqlString.Parse("")); }
public void AddConstraint(IDataContext dbctx, string constraintName, string constraint) { if (dbctx == null) { throw new ArgumentNullException("_datactx"); } if (string.IsNullOrEmpty(constraintName)) { throw new ArgumentNullException("constraintName"); } if (string.IsNullOrEmpty(constraint)) { throw new ArgumentNullException("constraint"); } var sql = "alter table \"{0}\" add constraint \"{1}\" {2}"; sql = string.Format(CultureInfo.InvariantCulture, sql, this.Name, constraintName, constraint); dbctx.Execute(SqlString.Parse(sql)); }
public void DeleteColumn(IDataContext db, string columnName) { if (db == null) { throw new ArgumentNullException("session"); } if (string.IsNullOrEmpty(columnName)) { throw new ArgumentNullException("columnName"); } if (!NamingRule.IsValidSqlName(columnName)) { throw new ArgumentOutOfRangeException("columnName"); } var sql = string.Format(CultureInfo.InvariantCulture, "alter table \"{0}\" drop column \"{1}\"", this.Name, columnName); db.Execute(SqlString.Parse(sql)); }