public static void DbFunctions_SYSDATE()
{
DbFunctionTools tools = new DbFunctionTools();
//String,Int32.
{
Console.WriteLine("Testing DbFunctions.SYSDATE...");
DbValue valOutput = DbFunctions.SYSDATE(tools, new DbFunctionArguments());
ByteSlice bs = valOutput.Eval();
DateTime output = tools.GetDateTime(bs);
DateTime expected = DateTime.Now;
TimeSpan sp = output - expected;
if (sp.TotalMinutes > 5)
{
throw new Exception("DbFunctions.SYSDATE has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
}
public static void DbFunctions_PATINDEX()
{
DbFunctionTools tools = new DbFunctionTools();
//String,Int32.
{
Console.WriteLine("Testing DbFunctions.PATINDEX(String, string)...");
List <DbValue> args = new List <DbValue>();
args.Add(tools.AllocValue(mstring.Prepare("%ap_pl[e]%")));
args.Add(tools.AllocValue(mstring.Prepare("red is ap5ple, my favourite.")));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.PATINDEX(tools, fargs);
ByteSlice bs = valOutput.Eval();
int output = tools.GetInt(bs);
int expected = 7;
if (expected != output)
{
throw new Exception("DbFunctions.PATINDEX(String) has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
}
protected override DbValue[] ReadRow()
{
var row = new DbValue[_fields.Count];
try
{
var nullBytes = _database.XdrStream.ReadOpaque((int)Math.Ceiling(_fields.Count / 8d));
var nullBits = new BitArray(nullBytes);
for (var i = 0; i < _fields.Count; i++)
{
if (nullBits.Get(i))
{
row[i] = new DbValue(this, _fields[i], null);
}
else
{
var value = ReadRawValue(_fields[i]);
row[i] = new DbValue(this, _fields[i], value);
}
}
return(row);
}
catch (IOException ex)
{
throw IscException.ForErrorCode(IscCodes.isc_net_read_err, ex);
}
}
public static void DbFunctions_RIGHT()
{
DbFunctionTools tools = new DbFunctionTools();
//String,Int32.
{
Console.WriteLine("Testing DbFunctions.RIGHT(String,Int32)...");
mstring input = Utils.GenString(100);
List <DbValue> args = new List <DbValue>();
args.Add(tools.AllocValue(input));
args.Add(tools.AllocValue(5));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.RIGHT(tools, fargs);
ByteSlice bs = valOutput.Eval();
mstring output = tools.GetString(bs);
string str = input.ToString();
string expected = str.Substring(str.Length - 5, 5);
if (expected != output.ToString())
{
throw new Exception("DbFunctions.RIGHT(String,Int32) has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
}
public void DbMap5()
{
var ips = new List <IPAddress> {
IPAddress.Parse("192.168.1.1"),
IPAddress.Parse("192.168.1.2")
};
var value = new DbValue(AttributeCollection.FromObject(new Machine {
Id = 1,
Ips = ips
}));
Assert.Equal(@"{
""M"": {
""id"": {
""N"": ""1""
},
""ips"": {
""L"": [
{
""B"": ""wKgBAQ==""
},
{
""B"": ""wKgBAg==""
}
]
}
}
}", value.ToJson().ToString());
}
public void WriteDbValue(DbValue value)
{
switch (value.Kind)
{
case S: WriteString(value.ToString()); break;
case B: WriteValue("B", value.ToString()); break;
case BOOL: WriteBool(value.ToBoolean()); break;
case BS: WriteSet("BS", value.ToSet <byte[]>()); break;
case SS: WriteSet("SS", value.ToSet <string>()); break;
case NS: WriteSet("NS", value.ToSet <string>()); break;
case L: WriteList((IEnumerable <DbValue>)value.Value); break;
case N: WriteValue("N", value.ToString()); break;
case M: WriteMap((AttributeCollection)value.Value); break;
default: throw new Exception("Unexpected type:" + value.Kind);
}
}
public static void DbFunctions_RAND()
{
DbFunctionTools tools = new DbFunctionTools();
Random rnd = new Random();
{
Console.WriteLine("Testing DbFunctions.RAND(Int32)...");
int input = rnd.Next(Int32.MinValue, Int32.MaxValue);
List <DbValue> args = new List <DbValue>();
args.Add(tools.AllocValue(input));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.RAND(tools, fargs);
ByteSlice bs = valOutput.Eval();
double output = tools.GetDouble(bs);
Console.WriteLine("Expected results received: {0}", output);
}
{
Console.WriteLine("Testing DbFunctions.RAND()...");
List <DbValue> args = new List <DbValue>();
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.RAND(tools, fargs);
ByteSlice bs = valOutput.Eval();
double output = tools.GetDouble(bs);
Console.WriteLine("Expected results received: {0}", output);
}
}
static void EnsureNotNull(DbValue dbValue)
{
if (dbValue._value == null || dbValue._value == DBNull.Value)
{
throw new InvalidCastException();
}
}
public Column Add(string name, ColumnType type, ColumnQuantity quantity = ColumnQuantity.Single,
bool isNullable = true, bool isPrimaryKey = false, DbValue defaultValue = null)
{
var column = new Column(name, type, quantity, isNullable, isPrimaryKey, defaultValue);
return(Add(column));
}
private void Reload(SQLiteConnection connection)
{
var columns = Table.Columns.ToArray(); // to preserve order
var columnNames = String.Join(",", columns.Select(x => x.Name.EscapeIdentifier()));
using (var command = new SQLiteCommand(LoadSql.FormatExt(columnNames, Table.Name), connection))
{
command.Parameters.Add(new SQLiteParameter("@RowId", RowId));
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
for (var i = 0; i < columns.Length; i++)
{
var value = DbValue.FromDb(reader.GetValue(i), columns[i].Type, columns[i].Quantity);
if (Cells[columns[i].Name] == null)
{
Cells.AddExisting(new Cell(this, columns[i], value));
}
else
{
Cells[columns[i].Name].SetExistingValue(value);
}
}
}
}
}
}
public static void DbFunctions_SUBSTRING()
{
DbFunctionTools tools = new DbFunctionTools();
//String,Int32.
{
Console.WriteLine("Testing DbFunctions.SUBSTRING(String, int, int)...");
List <DbValue> args = new List <DbValue>();
args.Add(tools.AllocValue(mstring.Prepare("HELLO WORLD")));
int si = 6;
int len = 5;
args.Add(tools.AllocValue(si));
args.Add(tools.AllocValue(len));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.SUBSTRING(tools, fargs);
ByteSlice bs = valOutput.Eval();
mstring output = tools.GetString(bs);
mstring expected = mstring.Prepare("WORLD");
if (expected != output)
{
throw new Exception("DbFunctions.SUBSTRING(String, int, int) has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
}
/// <summary>
/// Get a single key
/// </summary>
/// <param name="key"></param>
/// <returns>null if key is not found</returns>
public byte[] Get(byte[] key)
{
IntPtr keyPtr = Marshal.AllocHGlobal(key.Length);
try
{
Marshal.Copy(key, 0, keyPtr, key.Length);
DbValue dbKey = new DbValue(keyPtr, key.Length);
DbValue dbValue = Dbi.Get(_tran._txnPtr, _dbi, dbKey);
byte[] buffer = null;
if (dbValue.Address != IntPtr.Zero && dbValue.Length >= 0)
{
buffer = new byte[dbValue.Length];
if (dbValue.Length > 0)
{
Marshal.Copy(dbValue.Address, buffer, 0, buffer.Length);
}
}
return(buffer);
}
catch (MdbxException ex)
{
if (ex.ErrorNumber == MdbxCode.MDBX_NOTFOUND)
{
return(null); // key not found
}
throw;
}
finally
{
Marshal.FreeHGlobal(keyPtr);
}
}
/// <summary>
/// Delete a specific key
/// </summary>
/// <param name="key"></param>
/// <returns>true if deleted successfully; false means not-found</returns>
public bool Del(byte[] key)
{
IntPtr keyPtr = Marshal.AllocHGlobal(key.Length);
try
{
Marshal.Copy(key, 0, keyPtr, key.Length);
DbValue dbKey = new DbValue(keyPtr, key.Length);
Dbi.Del(_tran._txnPtr, _dbi, dbKey, IntPtr.Zero);
return(true);
}
catch (MdbxException ex)
{
if (ex.ErrorNumber == MdbxCode.MDBX_NOTFOUND)
{
return(false); // key not found
}
throw;
}
finally
{
Marshal.FreeHGlobal(keyPtr);
}
}
public static void DbFunctions_LAST_DAY()
{
DbFunctionTools tools = new DbFunctionTools();
//String,Int32.
{
Console.WriteLine("Testing DbFunctions.LAST_DAY...");
List <DbValue> args = new List <DbValue>();
DateTime dt = DateTime.Parse("12/1/2000 10:00:00 AM");
args.Add(tools.AllocValue(dt));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.LAST_DAY(tools, fargs);
ByteSlice bs = valOutput.Eval();
DateTime output = tools.GetDateTime(bs);
DateTime expected = DateTime.Parse("12/31/2000 10:00:00 AM");
if (expected != output)
{
throw new Exception("DbFunctions.LAST_DAY has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
}
protected virtual DbValue[] ReadRow()
{
var row = new DbValue[_fields.Count];
try
{
for (var i = 0; i < _fields.Count; i++)
{
var value = ReadRawValue(_fields[i]);
var sqlInd = _database.Xdr.ReadInt32();
if (sqlInd == -1)
{
row[i] = new DbValue(this, _fields[i], null);
}
else if (sqlInd == 0)
{
row[i] = new DbValue(this, _fields[i], value);
}
else
{
throw IscException.ForStrParam($"Invalid {nameof(sqlInd)} value: {sqlInd}.");
}
}
}
catch (IOException ex)
{
throw IscException.ForErrorCode(IscCodes.isc_network_error, ex);
}
return(row);
}
public static void DbFunctions_ADD_MONTHS()
{
DbFunctionTools tools = new DbFunctionTools();
//String,Int32.
{
Console.WriteLine("Testing DbFunctions.ADD_MONTHS...");
List <DbValue> args = new List <DbValue>();
DateTime dt = DateTime.Now;
int months = 9;
args.Add(tools.AllocValue(dt));
args.Add(tools.AllocValue(months));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.ADD_MONTHS(tools, fargs);
ByteSlice bs = valOutput.Eval();
DateTime output = tools.GetDateTime(bs);
DateTime expected = dt.AddMonths(months);
if (expected != output)
{
throw new Exception("DbFunctions.ADD_MONTHS has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
}
protected override async Task <DbValue[]> ReadRow(AsyncWrappingCommonArgs async)
{
var row = new DbValue[_fields.Count];
try
{
if (_fields.Count > 0)
{
var nullBytes = await _database.Xdr.ReadOpaque((int)Math.Ceiling(_fields.Count / 8d), async).ConfigureAwait(false);
var nullBits = new BitArray(nullBytes);
for (var i = 0; i < _fields.Count; i++)
{
if (nullBits.Get(i))
{
row[i] = new DbValue(this, _fields[i], null);
}
else
{
var value = await ReadRawValue(_database.Xdr, _fields[i], async).ConfigureAwait(false);
row[i] = new DbValue(this, _fields[i], value);
}
}
}
}
catch (IOException ex)
{
throw IscException.ForIOException(ex);
}
return(row);
}
public static void DbFunctions_NVL2()
{
DbFunctionTools tools = new DbFunctionTools();
{
Console.WriteLine("Testing DbFunctions.NVL2(DateTime)...");
List <DbValue> args = new List <DbValue>();
DateTime dt = DateTime.Parse("12/1/2000 10:00:00 AM");
DateTime ifnull = DateTime.Parse("12/11/2000 10:00:00 AM");
DateTime notnull = DateTime.Parse("12/14/2000 10:00:00 AM");
args.Add(tools.AllocValue(dt));
args.Add(tools.AllocValue(notnull));
args.Add(tools.AllocValue(ifnull));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.NVL2(tools, fargs);
ByteSlice bs = valOutput.Eval();
DateTime output = tools.GetDateTime(bs);
DateTime expected = notnull;
if (expected != output)
{
throw new Exception("DbFunctions.NVL2(DateTime) has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
{
Console.WriteLine("Testing DbFunctions.NVL2(DateTime)...");
List <DbValue> args = new List <DbValue>();
byte[] buf = new byte[9];
buf[0] = 1; //is null
DateTime ifnull = DateTime.Parse("12/11/2000 10:00:00 AM");
DateTime notnull = DateTime.Parse("12/14/2000 10:00:00 AM");
args.Add(tools.AllocValue(ByteSlice.Prepare(buf), DbType.Prepare("DateTime", 9)));
args.Add(tools.AllocValue(notnull));
args.Add(tools.AllocValue(ifnull));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.NVL2(tools, fargs);
ByteSlice bs = valOutput.Eval();
DateTime output = tools.GetDateTime(bs);
DateTime expected = ifnull;
if (expected != output)
{
throw new Exception("DbFunctions.NVL2(DateTime) has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
}
public static void DbFunctions_CONCAT()
{
DbFunctionTools tools = new DbFunctionTools();
//String,Int32.
{
Console.WriteLine("Testing DbFunctions.CONCAT(String,String)...");
List <DbValue> args = new List <DbValue>();
args.Add(tools.AllocValue(mstring.Prepare("hello ")));
args.Add(tools.AllocValue(mstring.Prepare("world")));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.CONCAT(tools, fargs);
ByteSlice bs = valOutput.Eval();
mstring output = tools.GetString(bs);
mstring expected = mstring.Prepare("hello world");
if (expected != output)
{
throw new Exception("DbFunctions.CONCAT(String,String) has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
}
protected virtual async Task <DbValue[]> ReadRow(AsyncWrappingCommonArgs async)
{
var row = new DbValue[_fields.Count];
try
{
for (var i = 0; i < _fields.Count; i++)
{
var value = await ReadRawValue(_database.Xdr, _fields[i], async).ConfigureAwait(false);
var sqlInd = await _database.Xdr.ReadInt32(async).ConfigureAwait(false);
if (sqlInd == -1)
{
row[i] = new DbValue(this, _fields[i], null);
}
else if (sqlInd == 0)
{
row[i] = new DbValue(this, _fields[i], value);
}
else
{
throw IscException.ForStrParam($"Invalid {nameof(sqlInd)} value: {sqlInd}.");
}
}
}
catch (IOException ex)
{
throw IscException.ForIOException(ex);
}
return(row);
}
public static void DbFunctions_CHARINDEX()
{
DbFunctionTools tools = new DbFunctionTools();
//String,Int32.
{
Console.WriteLine("Testing DbFunctions.CHARINDEX(char(n), char(n)...");
mstring word = mstring.Prepare("apple");
mstring sentence = mstring.Prepare("Red is apple");
List <DbValue> args = new List <DbValue>();
args.Add(tools.AllocValue(word));
args.Add(tools.AllocValue(sentence));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.CHARINDEX(tools, fargs);
ByteSlice bs = valOutput.Eval();
int output = tools.GetInt(bs);
int expected = 7;
if (expected != output)
{
throw new Exception("DbFunctions.CHARINDEX(char(n), char(n) has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
{
Console.WriteLine("Testing DbFunctions.CHARINDEX(char(n), char(n), Int32...");
mstring word = mstring.Prepare("apple");
mstring sentence = mstring.Prepare("Red is apple, or more apples.");
int startIndex = 8;
List <DbValue> args = new List <DbValue>();
args.Add(tools.AllocValue(word));
args.Add(tools.AllocValue(sentence));
args.Add(tools.AllocValue(startIndex));
DbFunctionArguments fargs = new DbFunctionArguments(args);
DbValue valOutput = DbFunctions.CHARINDEX(tools, fargs);
ByteSlice bs = valOutput.Eval();
int output = tools.GetInt(bs);
int expected = 22;
if (expected != output)
{
throw new Exception("DbFunctions.CHARINDEX(char(n), char(n), Int32 has failed. Expected result: " + expected.ToString() + ", but received: " + output.ToString());
}
else
{
Console.WriteLine("Expected results received.");
}
}
}
public void DbList2()
{
var value = DbValue.FromJson(JsonObject.Parse(@"{ ""L"": [ { ""N"": ""1.1"" }, { ""N"":""7.543"" } ] }"));
Assert.Equal(DbValueType.L, value.Kind);
Assert.Equal(new[] { 1.1f, 7.543f }, value.ToArray <float>());
}
public void BinaryTests()
{
var value = DbValue.FromJson(JsonObject.Parse(@"{""B"":""dmFsdWU=""}"));
Assert.Equal(DbValueType.B, value.Kind);
Assert.Equal("dmFsdWU=", Convert.ToBase64String(value.ToBinary()));
Assert.Equal(@"{""B"":""dmFsdWU=""}", value.ToJson().ToString(pretty: false));
}
/// <summary>
/// 实例化 <see cref="SqlServerMethodCallExressionVisitor"/> 类的新实例
/// </summary>
/// <param name="provider">查询语义提供者</param>
/// <param name="visitor">表达式访问器</param>
public MySqlMethodCallExressionVisitor(IDbQueryProvider provider, ExpressionVisitorBase visitor)
: base(provider, visitor)
{
_provider = provider;
_visitor = visitor;
_builder = visitor.SqlBuilder;
_visitedMark = _visitor.VisitedStack;
_dbValue = _provider.DbValue;
}
protected void UpdateColumnIfDifferent <TDbEntity, TId>(ref DbValue <TId?> column, TId?value, ref IDbEntityRef <TDbEntity> dbEntityRef)
where TDbEntity : DbEntity
where TId : struct
{
if (ShouldUpdateColumn(ref column, value, ref dbEntityRef, () => (value as long?) ?? 0))
{
column.Entity = value;
}
}
public DbField()
{
_charCount = -1;
_name = string.Empty;
_relation = string.Empty;
_owner = string.Empty;
_alias = string.Empty;
_dbValue = new DbValue(this, DBNull.Value);
}
public object ToObject(DbValue item, IMember member)
{
if (member?.Precision == 4)
{
return(DateTimeOffset.FromUnixTimeMilliseconds(item.ToInt64()).UtcDateTime);
}
return(DateTimeOffset.FromUnixTimeSeconds(item.ToInt64()).UtcDateTime);
}
public void WriteProperty(string name, DbValue value)
{
writer.Write("\"");
JavaScriptEncoder.Default.Encode(writer, name);
writer.Write("\"");
writer.Write(":");
WriteDbValue(value);
}
public override void Execute()
{
if (_state == StatementState.Deallocated)
{
throw new InvalidOperationException("Statment is not correctly created.");
}
ClearStatusVector();
var inSqlda = IntPtr.Zero;
var outSqlda = IntPtr.Zero;
if (_parameters != null)
{
inSqlda = XsqldaMarshaler.MarshalManagedToNative(_db.Charset, _parameters);
}
if (_statementType == DbStatementType.StoredProcedure)
{
Fields.ResetValues();
outSqlda = XsqldaMarshaler.MarshalManagedToNative(_db.Charset, _fields);
}
var trHandle = _transaction.HandlePtr;
_db.FbClient.isc_dsql_execute2(
_statusVector,
ref trHandle,
ref _handle,
IscCodes.SQLDA_VERSION1,
inSqlda,
outSqlda);
if (outSqlda != IntPtr.Zero)
{
var descriptor = XsqldaMarshaler.MarshalNativeToManaged(_db.Charset, outSqlda, true);
var values = new DbValue[descriptor.Count];
for (var i = 0; i < values.Length; i++)
{
values[i] = new DbValue(this, descriptor[i]);
}
_outputParams.Enqueue(values);
}
XsqldaMarshaler.CleanUpNativeData(ref inSqlda);
XsqldaMarshaler.CleanUpNativeData(ref outSqlda);
_db.ProcessStatusVector(_statusVector);
UpdateRecordsAffected();
_state = StatementState.Executed;
}
public void DbList()
{
var value = DbValue.FromJson(JsonObject.Parse(@"{ ""L"": [ { ""N"": ""1"" }, { ""N"":""2"" } ] }"));
Assert.Equal(DbValueType.L, value.Kind);
Assert.Equal(new[] { "1", "2" }, value.ToArray <string>());
Assert.Equal(new[] { 1, 2 }, value.ToArray <int>());
Assert.Equal(new[] { 1L, 2L }, value.ToArray <long>());
}
