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