public void Serialize()
{
object obj = new java.lang.Long(long.MaxValue);
byte[] bytes = SqlObject.Serialize(obj);
Assert.That(!SqlObject.StartsWithSerializationHeader(bytes));
SqlObject wrapper = new SqlObject(bytes, true);
object wrappedObject = wrapper.getObject();
Assert.AreEqual(new java.lang.Long(long.MaxValue), wrappedObject);
bytes = SqlObject.Serialize(long.MaxValue);
Assert.That(SqlObject.StartsWithSerializationHeader(bytes));
bool isJavaObject;
obj = SqlObject.Deserialize(bytes, out isJavaObject);
Assert.IsFalse(isJavaObject);
Assert.AreEqual(long.MaxValue, obj);
wrapper = new SqlObject(bytes);
obj = wrapper.getObject();
Assert.AreEqual(long.MaxValue, obj);
}
private QueryParameter(int id, string name, ParameterStyle style, SqlObject value, ParameterDirection direction)
{
this.id = id;
this.name = name;
this.style = style;
this.value = value;
this.direction = direction;
}
public virtual void getObject()
{
SqlObject sqlObject = new SqlObject(decimal.MaxValue);
object obj = sqlObject.getObject();
Assert.AreEqual(decimal.MaxValue, obj);
}
public string SaveMessage(string name, string pwd, string content, int code)
{
if (code == AuthCodePage.CurrentValue)
{
SqlObject sqlObj = new SqlObject();
const string sqlGetUser = "******";
const string sqlUpdUser = "******";
const string sqlGetCategoryByName = "select categoryid from tcategories where caption like '祝福寄语'";
const string sqlInsArticle = "insert into tarticles(categoryid, userid, caption,visible, content, articletype) values({0}, {1}, '{2}', {4}, '{3}', 3)";
if (string.IsNullOrEmpty(name) || string.IsNullOrEmpty(pwd))
{
throw new CustomException("用户名或密码不正确");
}
name = SqlHelper.MakeSafeFieldValue(name, "str");
pwd = SqlHelper.MakeSafeFieldValue(pwd, "str");
content = SqlHelper.MakeSafeFieldValue(content, "str");
List<User> rlt = D.DB.ExecuteList<User>(string.Format(sqlGetUser, name));
int cid = D.DB.ExecScalar<int>(sqlGetCategoryByName);
if (cid <= 0)
{
throw new CustomException("类别不存在: 祝福寄语");
}
if (rlt == null || rlt.Count < 1)
{
int id = D.DB.ExecuteInsert("tusers", "uname", "userid");
if (id > 0)
{
D.DB.Execute(string.Format(sqlUpdUser, name, pwd, id.ToString()));
D.DB.ExecuteNonQuery(sqlInsArticle, cid.ToString(), id.ToString(), "祝福寄语", content, "False");
}
}
else
{
User u = rlt[0];
if (!string.Equals(u.UPwd, pwd, StringComparison.Ordinal))
{
throw new CustomException("用户名或密码不正确");
}
D.DB.Execute(string.Format(sqlInsArticle, cid, u.UserId, "祝福寄语", content, u.ULevel < 3 ? "True" : "False"));
}
return "祝福提交已成功，需等待管理员审核。";
}
throw new CustomException("验证码不对，请重新输入");
}
public string ListFiles(int page, int size, string orderField = "utime", bool asc = true)
{
//select top 2 * from tfiles
//where id not in (select top 4 id from tfiles order by utime desc)
//order by utime desc
if (page < 1)
{
page = 1;
}
if (size < 1)
{
size = 10;
}
SqlObject sub = new SqlObject();
SqlObject so = new SqlObject();
if (page > 1)
{
so.SelectTop(size, "id", "fname", "dname", "utime").From("tfiles")
.Where("id", "not in", string.Concat("(",
sub.SelectTop(page * size - size, "id").From("tfiles").OrderBy(orderField, asc ? string.Empty : "desc").ToString(), ")")
, false).OrderBy(orderField, asc ? string.Empty : "desc");
}
else
{
so.SelectTop(size, "id", "fname", "dname", "utime").From("tfiles").OrderBy(orderField, asc ? string.Empty : "desc");
}
string sql = so.ToString();
//DataTable table = D.DB.GetDataTable(sql);
ExecuteResult er = D.DB.ExecuteReader(sql, new Func<IDataReader, object>(delegate(IDataReader reader)
{
ViewerSlot vs = new ViewerSlot();
vs.Retrieve(reader);
return vs;
}));
if (er.IsNoException)
{
ViewerSlot rlt = er.ObjRlt as ViewerSlot;
return rlt.ToJson();
}
else
{
return string.Concat("{ error:\"", er.Exception.Message.Replace("\"", "'"), "\"}");
}
}
public override ITemplate TemplateToUse(SqlObject sqlObject)
{
return(new DropCreateTemplate(sqlObject));
}
private DbUtility(string connectionString)
{
_sqlConnection = SqlObject.GetInstance(connectionString).Connection;
SqlParameters = new List <SqlParamWrapper>();
}
protected TemplateBase(SqlObject sqlObject)
{
_sqlObject = sqlObject;
}
public void Return(SqlObject value) => Return(new StatementScalarResult(value));
public VoidTemplate(SqlObject sqlObject)
: base(sqlObject)
{
}
public QueryParameter(int id, SqlObject value, ParameterDirection direction)
: this(id, null, ParameterStyle.Marker, value, direction)
{
if (id < 0)
throw new ArgumentOutOfRangeException("id");
}
public override SqlExpression VisitConstantString(PlSqlParser.ConstantStringContext context)
{
var value = SqlParseInputString.AsNotQuoted(context.quoted_string());
return(SqlExpression.Constant(SqlObject.String(value)));
}
public override SqlExpression VisitExtractFunction(PlSqlParser.ExtractFunctionContext context)
{
var part = SqlParseName.Simple(context.regular_id());
var exp = Visit(context.concatenationWrapper());
return(SqlExpression.Function("SQL_EXTRACT", new[] { exp, SqlExpression.Constant(SqlObject.String(part)) }));
}
public override ITemplate TemplateToUse(SqlObject sqlObject)
{
return(new VoidTemplate(sqlObject));
}
public InvokeArgument(string parameterName, SqlObject value)
: this(parameterName, SqlExpression.Constant(value))
{
}
public void StartsWithSerializationHeader()
{
Assert.IsTrue(SqlObject.StartsWithSerializationHeader(
SqlObject.SerializationHeader.ToByteArray()));
}
public InvokeArgument(SqlObject value)
: this(null, value)
{
}
public void AddTable(string name, SqlObject type)
{
RunNonQuery(SqlStatementBuilder.BuildCreateTable(name, type));
DatabaseModel.Tables.Add(type);
}
public void AddTable(SqlObject type)
{
string name = SqlHelper.Pluralize(type.Name);
AddTable(name, type);
}
public SqlCollection Select(SqlObject tableModel, params string[] fields)
{
string select = new Statement().Select(string.Join(", ", fields)).From(tableModel.Name) + ";";
return(RunQuery(select));
}
public override void AddCode(SqlConnection connection, SqlObject obj)
{
SysIndex index = null;
using (var cmd = new SqlCommand(@"SELECT ind.index_id AS IndexId
,ind.name AS IndexName
,col.name AS ColumnName
,t.name AS TableName
,s.name AS SchemaName
,ic.index_column_id AS ColumnId
,ind.is_unique AS IsUnique
,ind.type_desc as Type
,ind.has_filter AS HasFilter
,ind.filter_definition AS FilterDefinition
,ic.is_descending_key AS IsDescending
,ic.is_included_column AS IsIncluded
FROM sys.indexes ind
JOIN sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
JOIN sys.tables t ON ind.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE ind.name = @NAME
AND ind.object_id = @ID
ORDER BY TableName
,IndexId
,ColumnId", connection))
{
cmd.Parameters.Add("@NAME", SqlDbType.NVarChar, 128);
cmd.Parameters.Add("@ID", SqlDbType.Int);
cmd.Parameters["@NAME"].Value = obj.Name;
cmd.Parameters["@ID"].Value = obj.ObjectId;
using (var reader = cmd.ExecuteReader())
{
if (!reader.HasRows)
{
return;
}
while (reader.Read())
{
if (index == null)
{
var tbl = new SysTable
{
Name = reader[3].ToString(),
Schema = reader[4].ToString()
};
index = new SysIndex
{
Id = Convert.ToInt32(reader[0]),
Name = reader[1].ToString(),
Table = tbl,
Type = reader[7].ToString(),
IsUnique = Convert.ToBoolean(reader[6]),
HasFilter = Convert.ToBoolean(reader[8]),
FilterDefinition = Convert.ToString(reader[9])
};
}
index.Columns.Add(new SysIndexColumn
{
Id = Convert.ToInt32(reader[5]),
Name = Convert.ToString(reader[2]),
IsDescending = Convert.ToBoolean(reader[10]),
IsIncluded = Convert.ToBoolean(reader[11]),
});
}
var buffer = new StringBuilder();
buffer.AppendLine(@"DECLARE @Name nvarchar(128), @TableName nvarchar(128), @TableSchema nvarchar(128)");
buffer.AppendFormat(
@"SELECT @Name = N'{0}', @TableName=N'{1}', @TableSchema = N'{2}'",
index.Name,
index.Table.Name,
index.Table.Schema);
buffer.AppendLine();
buffer.AppendLine(
"IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('[' + @TableSchema + '].[' + @TableName + ']') AND UPPER(name) = UPPER(@Name))");
buffer.AppendLine(
" EXECUTE('DROP INDEX [' + @Name + '] ON [' + @TableSchema + '].[' + @TableName + ']')");
buffer.AppendLine();
buffer.AppendLine(@"PRINT 'Creating index [' + @Name + '] on table [' + @TableSchema + '].[' + @TableName + ']'");
buffer.AppendLine("GO");
buffer.AppendLine();
buffer.Append(@"CREATE");
if (index.IsUnique)
{
buffer.Append(" UNIQUE");
}
buffer.AppendFormat(" {0} INDEX [{1}]", index.Type, index.Name);
buffer.AppendLine();
buffer.AppendFormat("ON [{0}].[{1}]", index.Table.Schema, index.Table.Name);
buffer.AppendFormat("({0})", string.Join(", ", index.Columns.Where(c => !c.IsIncluded).Select(
c =>
{
if (c.IsDescending)
{
return(c.Name + " DESC");
}
return(c.Name);
})));
if (index.Columns.Any(c => c.IsIncluded))
{
buffer.AppendLine();
buffer.AppendFormat("INCLUDE ({0})", string.Join(", ", index.Columns.Where(c => c.IsIncluded).Select(c => c.Name)));
}
if (index.HasFilter)
{
buffer.AppendLine();
buffer.AppendFormat("WHERE {0}", index.FilterDefinition);
}
obj.Code += buffer.ToString();
obj.AddCodeTemplate();
}
}
}
public QueryParameter(string name, SqlObject value)
: this(name, value, ParameterDirection.Input)
{
}
public void CreateNew()
{
var exp = SqlExpression.Function(ObjectName.Parse("sys.func2"),
new[] { new InvokeArgument(SqlExpression.Constant(SqlObject.Bit(false))) });
Assert.Equal(SqlExpressionType.Function, exp.ExpressionType);
Assert.NotNull(exp.Arguments);
Assert.NotEmpty(exp.Arguments);
Assert.Equal(1, exp.Arguments.Length);
}
public QueryParameter(int id, SqlObject value)
: this(id, value, ParameterDirection.Input)
{
}
public void Serialize()
{
var exp = SqlExpression.Function(ObjectName.Parse("sys.func2"),
new[] { new InvokeArgument(SqlExpression.Constant(SqlObject.Bit(false))) });
var result = BinarySerializeUtil.Serialize(exp);
Assert.Equal(exp.FunctionName, result.FunctionName);
Assert.Equal(exp.Arguments.Length, result.Arguments.Length);
}
public void SetResult(SqlObject value) => SetResult(new StatementScalarResult(value));
public static void GetNamedString()
{
var function = SqlExpression.Function(ObjectName.Parse("sys.Func1"), new InvokeArgument("a", SqlObject.BigInt(33)));
const string sql = "sys.Func1(a => 33)";
Assert.Equal(sql, function.ToString());
}
/// <summary>
/// For the current row, gets an instance of the provider-specific
/// representation of the value of the field with the given column
/// ordinal.
/// </summary>
/// <remarks>
/// Because this method does not check if this reader
/// is open or if row data is available, it should be invoked
/// *only* when it is already known that this reader is open and
/// row data is available.
/// </remarks>
/// <param name="ordinal">The column ordinal.</param>
/// <returns>The provider-specific value representation.</returns>
internal object GetProviderSpecificValueInternal(int ordinal)
{
object value = m_currentRecord.data[ordinal];
if (value == null)
{
return(null);
}
int type = m_metaData.colTypes[ordinal];
switch (type)
{
case (int)HsqlProviderType.Array:
{
return(value);
}
case (int)HsqlProviderType.BigInt:
{
return(value);
}
case (int)HsqlProviderType.Binary:
{
return(((org.hsqldb.types.Binary)value).getClonedBytes());
}
case (int)HsqlProviderType.Blob:
{
return(value);
}
case (int)HsqlProviderType.Boolean:
{
return(value);
}
case (int)HsqlProviderType.Char:
{
return(value);
}
case (int)HsqlProviderType.Clob:
{
return(value);
}
case (int)HsqlProviderType.DataLink:
{
return(value);
}
case (int)HsqlProviderType.Date:
{
return((java.sql.Date)((java.sql.Date)value).clone());
}
case (int)HsqlProviderType.Decimal:
{
return(value);
}
case (int)HsqlProviderType.Distinct:
{
return(value);
}
case (int)HsqlProviderType.Double:
{
return(value);
}
case (int)HsqlProviderType.Float:
{
return(value);
}
case (int)HsqlProviderType.Integer:
{
return(value);
}
case (int)HsqlProviderType.JavaObject:
{
return(value);
}
case (int)HsqlProviderType.LongVarBinary:
{
return(((org.hsqldb.types.Binary)value).getClonedBytes());
}
case (int)HsqlProviderType.LongVarChar:
{
return(value);
}
case (int)HsqlProviderType.Null:
{
return(null);
}
case (int)HsqlProviderType.Numeric:
{
return(value);
}
case (int)HsqlProviderType.Object:
{
byte[] bytes = ((org.hsqldb.types.JavaObject)value).getBytes();
bool isJavaObject;
return(SqlObject.Deserialize(bytes, out isJavaObject));
}
case (int)HsqlProviderType.Real:
{
return(value);
}
case (int)HsqlProviderType.Ref:
{
return(value);
}
case (int)HsqlProviderType.SmallInt:
{
return(value);
}
case (int)HsqlProviderType.Struct:
{
return(value);
}
case (int)HsqlProviderType.Time:
{
return((java.sql.Time)((java.sql.Time)value).clone());
}
case (int)HsqlProviderType.TimeStamp:
{
return((java.sql.Timestamp)((java.sql.Timestamp)value).clone());
}
case (int)HsqlProviderType.TinyInt:
{
return(value);
}
case (int)HsqlProviderType.VarBinary:
{
byte[] bytes = ((org.hsqldb.types.Binary)value).getClonedBytes();
return(new org.hsqldb.types.Binary(bytes, false));
}
case (int)HsqlProviderType.VarChar:
{
return(value);
}
case (int)HsqlProviderType.Xml:
{
return(value);
}
default:
{
throw HsqlConvert.UnknownConversion(value, type);
}
}
}
public void UnWrap()
{
SqlObject sqlObject = new SqlObject(1234.5678D);
bool isJavaObject = true;
object unwrapped = sqlObject.UnWrap(out isJavaObject);
Assert.AreEqual(false, isJavaObject);
Assert.AreEqual(1234.5678D, unwrapped);
sqlObject = new SqlObject(new java.lang.Double(1234.5678D));
isJavaObject = false;
unwrapped = sqlObject.UnWrap(out isJavaObject);
Assert.AreEqual(true, isJavaObject);
Assert.AreEqual(new java.lang.Double(1234.5678D), unwrapped);
}
public IndexKey(SqlObject value)
: this(new[] { value })
{
}
public override SqlExpression VisitInArray(PlSqlParser.InArrayContext context)
{
var exps = context.concatenationWrapper().Select(Visit).ToArray();
return(SqlExpression.Constant(SqlObject.Array(exps)));
}
public UnknownSqlObjectException(SqlObject sqlObject, Exception innerException = null)
: base(
message: $"Unknown {nameof(SqlObject)}: {sqlObject?.GetType()?.ToString() ?? "<NULL>"}",
innerException: innerException)
{
}
public override SqlExpression VisitConstantTrue(PlSqlParser.ConstantTrueContext context)
{
return(SqlExpression.Constant(SqlObject.Boolean(true)));
}
public DropCreateTemplate(SqlObject sqlObject) : base(sqlObject)
{
}
public QueryParameter(string name, SqlObject value, ParameterDirection direction)
: this(-1, name, ParameterStyle.Named, value, direction)
{
if (String.IsNullOrEmpty(name))
throw new ArgumentNullException("name");
}
public CreateAlterTemplate(SqlObject sqlObject)
: base(sqlObject)
{
}
public override SqlExpression VisitNextValueFunction(PlSqlParser.NextValueFunctionContext context)
{
var seqName = SqlParseName.Object(context.objectName());
return(SqlExpression.Function("NEXTVAL",
new SqlExpression[] { SqlExpression.Constant(SqlObject.String(seqName.ToString())) }));
}
public ObjectName(SqlObject sqlObject, string schemaName, string objectName)
{
_sqlObject = sqlObject;
_schemaName = schemaName;
_objectName = objectName;
}
public override async Task <SqlExpression> ReduceAsync(IContext context)
{
var left = await Left.ReduceAsync(context);
var pattern = await Pattern.ReduceAsync(context);
if (left.ExpressionType != SqlExpressionType.Constant ||
!(((SqlConstantExpression)left).Type is SqlCharacterType))
{
throw new SqlExpressionException("The left expression was not reduced to a constant string");
}
if (pattern.ExpressionType != SqlExpressionType.Constant ||
!(((SqlConstantExpression)pattern).Type is SqlCharacterType))
{
throw new SqlExpressionException("The pattern expression was not reduced to a constant string");
}
var escapeChar = PatternSearch.EscapeCharacter;
var leftString = (ISqlString)((SqlConstantExpression)left).Value.Value;
var patternString = ((SqlConstantExpression)pattern).Value.Value.ToString();
if (Escape != null)
{
var escape = await Escape.ReduceAsync(context);
if (escape.ExpressionType != SqlExpressionType.Constant ||
!(((SqlConstantExpression)escape).Type is SqlCharacterType))
{
throw new SqlExpressionException("The escape expression was not reduced to a constant string");
}
var s = (ISqlString)((SqlConstantExpression)escape).Value.Value;
if (s.Length > 1)
{
throw new SqlExpressionException($"Escape string {s} too long: must be one character");
}
escapeChar = s[0];
}
ISqlStringSearch search = null;
if (context != null)
{
search = context.Scope.GetStringSearch();
}
if (search == null)
{
search = new SqlDefaultStringSearch();
}
var result = search.Matches(leftString, patternString, escapeChar);
if (ExpressionType == SqlExpressionType.NotLike)
{
result = !result;
}
return(Constant(SqlObject.Boolean(result)));
}
private static List<string> ProcessFiles(HttpContext context)
{
List<string> urls = new List<string>();
var cidsel = new SqlObject();
cidsel.Select("categoryid").From("tCategories").Where("iname", "like", "upload");
string sql = cidsel.ToString();
//var tb = D.DB.GetDataTable(sql);
var list = D.DB.ExecuteValue(sql);
if (list == null)
{
throw new CustomException("无法找到文件上传对应的类别。");
}
int cid = (int)list;
for (int i = 0; i < context.Request.Files.Count; i++)
{
FileItem file = ProcessSingleFile(context, urls, i);
if (file != null)
{
file.CategoryId = cid;
var fins = new SqlObject();
fins.InsertInto(file);
sql = fins.ToString();
D.DB.ExecuteNonQuery(sql);
}
}
return urls;
}
public override SqlExpression VisitSearchedCaseExpression(PlSqlParser.SearchedCaseExpressionContext context)
{
var switches = new List <CaseSwitch>();
foreach (var partContext in context.simpleCaseWhenExpressionPart())
{
switches.Add(new CaseSwitch {
Condition = Visit(partContext.conditionWrapper()),
ReturnExpression = Visit(partContext.expressionWrapper())
});
}
if (context.caseElseExpressionPart() != null)
{
var returnExp = Visit(context.caseElseExpressionPart().expressionWrapper());
switches.Add(new CaseSwitch {
Condition = SqlExpression.Condition(SqlExpression.Constant(SqlObject.Boolean(true)), returnExp),
ReturnExpression = returnExp
});
}
SqlConditionExpression conditional = null;
for (int i = switches.Count - 1; i >= 0; i--)
{
var current = switches[i];
var condition = SqlExpression.Condition(current.Condition, current.ReturnExpression);
if (conditional != null)
{
conditional = SqlExpression.Condition(current.Condition, current.ReturnExpression, conditional);
}
else
{
conditional = condition;
}
}
return(conditional);
}
