public void ClientGetFieldTypeWithNoGivenType()
{
var server = CreateConnection();
server.Open();
var client = CreateConnection();
client.Open();
const string sqlMaster = "CREATE TABLE t1(x SOMETYPE, y INTEGER, z)";
using (var command = new SQLiteServerCommand(sqlMaster, client))
{
command.ExecuteNonQuery();
}
const string sqlSelect = "SELECT * FROM t1";
using (var command = new SQLiteServerCommand(sqlSelect, client))
{
using (var reader = command.ExecuteReader())
{
Assert.AreEqual(typeof(object), reader.GetFieldType(0)); // SOMETYPE = object
Assert.AreEqual(typeof(long), reader.GetFieldType(1)); // integer
Assert.AreEqual(typeof(object), reader.GetFieldType(2)); // "" = object
}
}
client.Close();
server.Close();
}
public void ClientGetDataTypeNameWithNoGivenType()
{
var server = CreateConnection();
server.Open();
var client = CreateConnection();
client.Open();
const string sqlMaster = "CREATE TABLE t1(u VARCHAR(128), w varchar(255), x SOMETYPE, y INTEGER, z)";
using (var command = new SQLiteServerCommand(sqlMaster, client))
{
command.ExecuteNonQuery();
}
const string sqlSelect = "SELECT * FROM t1";
using (var command = new SQLiteServerCommand(sqlSelect, client))
{
using (var reader = command.ExecuteReader())
{
Assert.AreEqual("VARCHAR(128)", reader.GetDataTypeName(0)); // uppercase 128...
Assert.AreEqual("varchar(255)", reader.GetDataTypeName(1)); // lowercase 255
Assert.AreEqual("SOMETYPE", reader.GetDataTypeName(2));
Assert.AreEqual("INTEGER", reader.GetDataTypeName(3)); // integer
Assert.AreEqual("", reader.GetDataTypeName(4)); // no type was given
}
}
client.Close();
server.Close();
}
public void ClientGetBlobTypeType()
{
var server = CreateConnection();
server.Open();
var client = CreateConnection();
client.Open();
const string sqlMaster = "CREATE TABLE t1(x BLOB, y INTEGER, z)";
using (var command = new SQLiteServerCommand(sqlMaster, client))
{
command.ExecuteNonQuery();
}
const string sqlSelect = "SELECT * FROM t1";
using (var command = new SQLiteServerCommand(sqlSelect, client))
{
using (var reader = command.ExecuteReader())
{
Assert.AreEqual(typeof(byte[]), reader.GetFieldType(0)); // BLOB = byte[]
Assert.AreEqual(typeof(long), reader.GetFieldType(1)); // integer
}
}
client.Close();
server.Close();
}
public void ClientCheckBusyTimeout()
{
const int timeout = 1;
var shortTimeout1 = new SocketConnectionBuilder(Address, Port, Backlog, HeartBeatTimeOut);
var shortTimeout2 = new SocketConnectionBuilder(Address, Port, Backlog, HeartBeatTimeOut);
var con1 = CreateConnection(shortTimeout1, timeout);
var con2 = CreateConnection(shortTimeout2, timeout);
const string sql = @"WITH RECURSIVE r(i) AS (
VALUES(0)
UNION ALL
SELECT i FROM r
LIMIT 25000000
)
SELECT i FROM r WHERE i = 1;";
con1.Open();
con2.Open();
using (var command = new SQLiteServerCommand(sql, con2))
{
Assert.Throws <TimeoutException>(() => command.ExecuteNonQuery());
}
con2.Close();
con1.Close();
}
public void RunInsertTest(int rows)
{
Console.Write($"Insert {rows} rows : ");
var watch = System.Diagnostics.Stopwatch.StartNew();
for (var i = 0; i < rows; ++i)
{
var sql = $"insert into {Table}(column_1, column_2, column_3) VALUES ('{Guid.NewGuid().ToString()}', 0, 1 )";
using (var command = new SQLiteServerCommand(sql, (_useClient ? _connectionClient : _connectionServer)))
{
command.ExecuteNonQuery();
}
}
watch.Stop();
var elapsedMs = watch.ElapsedMilliseconds;
var c = Console.ForegroundColor;
Console.ForegroundColor = ConsoleColor.Green;
Console.Write($"{((double)elapsedMs / 1000):N4}");
Console.ForegroundColor = c;
if (_source == ":memory:")
{
Console.WriteLine($"s. [SQLite {(_useClient ? "Client via Server memory" : "Server Memory")}]");
}
else
{
Console.WriteLine($"s. [SQLite {(_useClient ? "Client via Server" : "Server")}]");
}
}
public void ClientZeroTimeoutNeverErrors()
{
const int timeout = 0;
var shortTimeout1 = new SocketConnectionBuilder(Address, Port, Backlog, HeartBeatTimeOut);
var shortTimeout2 = new SocketConnectionBuilder(Address, Port, Backlog, HeartBeatTimeOut);
var con1 = CreateConnection(shortTimeout1, timeout);
var con2 = CreateConnection(shortTimeout2, timeout);
const string sql = @"WITH RECURSIVE r(i) AS (
VALUES(0)
UNION ALL
SELECT i FROM r
LIMIT 25000000
)
SELECT i FROM r WHERE i = 1;";
con1.Open();
con2.Open();
using (var command = new SQLiteServerCommand(sql, con2))
{
// even with no timeout, we should never get an error
Assert.AreEqual(-1, command.ExecuteNonQuery());
}
con2.Close();
con1.Close();
}
public void ClientGetFieldTypes()
{
var server = CreateConnection();
server.Open();
var client = CreateConnection();
client.Open();
const string sqlMaster = "create table tb_config (A varchar(255), B INTEGER, C TEXT, D REAL, E BLOB )";
using (var command = new SQLiteServerCommand(sqlMaster, client))
{
command.ExecuteNonQuery();
}
const string sqlSelect = "SELECT * FROM tb_config";
using (var command = new SQLiteServerCommand(sqlSelect, client))
{
using (var reader = command.ExecuteReader())
{
Assert.AreEqual(typeof(string), reader.GetFieldType(0)); // varchar
Assert.AreEqual(typeof(long), reader.GetFieldType(1)); // integer
Assert.AreEqual(typeof(string), reader.GetFieldType(2)); // text
Assert.AreEqual(typeof(double), reader.GetFieldType(3)); // real
Assert.AreEqual(typeof(byte[]), reader.GetFieldType(4)); // blob
}
}
client.Close();
server.Close();
}
private static void TryExecute(string s, SQLiteServerConnection connection)
{
try
{
using (var command = new SQLiteServerCommand(s, connection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
for (var i = 0; i < reader.FieldCount; ++i)
{
Console.Write($"\t{reader[i]}");
}
Console.WriteLine("");
}
var r = Console.ForegroundColor;
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("The command(s) completed successfully");
Console.ForegroundColor = r;
}
}
}
catch (SQLiteServerException e)
{
var r = Console.ForegroundColor;
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine(e.Message);
Console.ForegroundColor = r;
}
}
public void CallbackClientToClient()
{
var sourceMaster = CreateConnectionNewSource(new SocketConnectionBuilder(Address, 1202, Backlog, HeartBeatTimeOut), null);
var sourceClient = CreateConnection(new SocketConnectionBuilder(Address, 1202, Backlog, HeartBeatTimeOut), sourceMaster);
var destinationMaster = CreateConnectionNewSource(new SocketConnectionBuilder(Address, 1203, Backlog, HeartBeatTimeOut), null);
var destinationClient = CreateConnection(new SocketConnectionBuilder(Address, 1203, Backlog, HeartBeatTimeOut), destinationMaster);
sourceMaster.Open();
sourceClient.Open();
destinationMaster.Open();
destinationClient.Open();
// add data to source
const string sqlMaster = "create table tb_config (name varchar(20), value INTEGER)";
using (var command = new SQLiteServerCommand(sqlMaster, sourceClient)) { command.ExecuteNonQuery(); }
for (var i = 0; i < 100; ++i)
{
var long1 = RandomNumber <long>();
var sql = $"insert into tb_config(name, value) VALUES ('a', {long1})";
using (var command = new SQLiteServerCommand(sql, sourceClient))
{
command.ExecuteNonQuery();
}
}
var called = 0;
var callback =
new SQLiteServerBackupCallback(
(sc, sn, dc, dn, page, remainingPages, totalPages, retry) =>
{
Assert.AreEqual(page, 1);
Assert.AreEqual(remainingPages, 1);
Assert.AreEqual(totalPages, 2);
Assert.AreEqual(retry, false);
++called;
return(true);
});
// backup
// page size is 1 - (or 1024)
// we added 100 long = 4 bytes
// and we added '100' 'a' = 2 bytes
// 400 + 200 = 600 = one page
sourceClient.BackupDatabase(destinationClient, "main", "main", 1, callback, 0);
// check that this was called exactly once
// that way we know the other asserts are checked.
Assert.AreEqual(1, called);
sourceMaster.Close();
destinationMaster.Close();
sourceClient.Close();
destinationClient.Close();
}
public void DbConnectionIsNull()
{
var command = new SQLiteServerCommand();
Assert.Throws <InvalidOperationException>(() =>
{
command.ExecuteNonQuery();
});
}
public void TryingToRunCodeWhenDatabaseIsNotOpen()
{
var con = CreateConnection();
const string sql = "select 1;";
using (var command = new SQLiteServerCommand(sql, con))
{
Assert.Throws <InvalidOperationException>(() =>
{
command.ExecuteNonQuery();
});
}
}
private void CreateTable()
{
var sql = $@"
CREATE TABLE {Table}(
column_1 varchar(255),
column_2 INTEGER,
column_3 INTEGER
);";
using (var command = new SQLiteServerCommand(sql, _useClient ? _connectionClient : _connectionServer))
{
command.ExecuteNonQuery();
}
}
public void SimpleClientTransaction()
{
var server = CreateConnection();
server.Open();
var client = CreateConnection();
client.Open();
const string sqlMaster = "create table tb_config (name varchar(20), value INTEGER)";
using (var command = new SQLiteServerCommand(sqlMaster, client))
{
command.ExecuteNonQuery();
}
const int numRows = 5;
var trans = client.BeginTransaction();
for (var i = 0; i < numRows; i++)
{
var sql = $"insert into tb_config(name, value) VALUES ('a', {i} )";
using (var command = new SQLiteServerCommand(sql, client))
{
command.ExecuteNonQuery();
}
}
trans.Commit();
client.Close();
client = CreateConnection();
client.Open();
const string select = "select count(*) FROM tb_config";
using (var command = new SQLiteServerCommand(select, client))
{
using (var reader = command.ExecuteReader())
{
Assert.IsTrue(reader.HasRows);
while (reader.Read())
{
Assert.AreEqual(numRows, reader.GetInt16(0));
}
}
}
client.Close();
server.Close();
}
public void CommandTextIsSpaces()
{
// make sure that the connection is valid.
var server = CreateConnection();
server.Open();
var command = new SQLiteServerCommand(server);
Assert.Throws <InvalidOperationException>(() =>
{
command.ExecuteNonQuery();
});
server.Close();
}
public void ClientGetDataTypeNameWithNoGivenTyeAfterInsert()
{
var server = CreateConnection();
server.Open();
var client = CreateConnection();
client.Open();
const string sqlMaster = "CREATE TABLE t1(w REAL, x SOMETYPE, y INTEGER, z)";
using (var command = new SQLiteServerCommand(sqlMaster, client))
{
command.ExecuteNonQuery();
}
var sql = "INSERT INTO t1 (w, x, y, z) VALUES(3.12, RANDOMBLOB(1), 1, 'z');";
using (var command = new SQLiteServerCommand(sql, client)) { command.ExecuteNonQuery(); }
sql = "INSERT INTO t1 (w, x, y, z) VALUES(12, RANDOMBLOB(1), 1, 12);";
using (var command = new SQLiteServerCommand(sql, client)) { command.ExecuteNonQuery(); }
const string sqlSelect = "SELECT * FROM t1";
using (var command = new SQLiteServerCommand(sqlSelect, client))
{
using (var reader = command.ExecuteReader())
{
Assert.IsTrue(reader.Read());
// we now now the types.
Assert.AreEqual("REAL", reader.GetDataTypeName(0)); // SOMETYPE = object
Assert.AreEqual("SOMETYPE", reader.GetDataTypeName(1)); // SOMETYPE = object
Assert.AreEqual("INTEGER", reader.GetDataTypeName(2)); // integer
Assert.AreEqual("", reader.GetDataTypeName(3)); // string
Assert.IsTrue(reader.Read());
Assert.AreEqual("REAL", reader.GetDataTypeName(0)); // SOMETYPE = object
Assert.AreEqual("SOMETYPE", reader.GetDataTypeName(1)); // SOMETYPE = object
Assert.AreEqual("INTEGER", reader.GetDataTypeName(2)); // integer
Assert.AreEqual("", reader.GetDataTypeName(3)); // int
}
}
client.Close();
server.Close();
}
public void BackupClientToClient()
{
var sourceMaster = CreateConnectionNewSource(new SocketConnectionBuilder(Address, 1202, Backlog, HeartBeatTimeOut), null);
var sourceClient = CreateConnection(new SocketConnectionBuilder(Address, 1202, Backlog, HeartBeatTimeOut), sourceMaster);
var destinationMaster = CreateConnectionNewSource(new SocketConnectionBuilder(Address, 1203, Backlog, HeartBeatTimeOut), null);
var destinationClient = CreateConnection(new SocketConnectionBuilder(Address, 1203, Backlog, HeartBeatTimeOut), destinationMaster);
sourceMaster.Open();
sourceClient.Open();
destinationMaster.Open();
destinationClient.Open();
// add data to source
const string sqlMaster = "create table tb_config (name varchar(20), value INTEGER)";
using (var command = new SQLiteServerCommand(sqlMaster, sourceClient)) { command.ExecuteNonQuery(); }
var long1 = RandomNumber <long>();
var sql = $"insert into tb_config(name, value) VALUES ('a', {long1})";
using (var command = new SQLiteServerCommand(sql, sourceClient)) { command.ExecuteNonQuery(); }
// backup
sourceClient.BackupDatabase(destinationClient, "main", "main", -1, null, 0);
// check that the backup now has the data
sql = "select * FROM tb_config";
using (var command = new SQLiteServerCommand(sql, destinationClient))
{
using (var reader = command.ExecuteReader())
{
Assert.IsTrue(reader.Read());
Assert.AreEqual("a", reader.GetString(0));
Assert.AreEqual(long1, reader.GetInt64(1));
Assert.IsFalse(reader.Read());
}
}
sourceClient.Close();
sourceMaster.Close();
destinationClient.Close();
destinationMaster.Close();
}
public void ClientGetFieldTypeWithNoGivenTyeAfterInsert()
{
var server = CreateConnection();
server.Open();
var client = CreateConnection();
client.Open();
const string sqlMaster = "CREATE TABLE t1(x SOMETYPE, y INTEGER, z)";
using (var command = new SQLiteServerCommand(sqlMaster, client))
{
command.ExecuteNonQuery();
}
var sql = "INSERT INTO t1 (x, y, z) VALUES(RANDOMBLOB(1), 1, 'z');";
using (var command = new SQLiteServerCommand(sql, client)) { command.ExecuteNonQuery(); }
sql = "INSERT INTO t1 (x, y, z) VALUES(RANDOMBLOB(1), 1, 12);";
using (var command = new SQLiteServerCommand(sql, client)) { command.ExecuteNonQuery(); }
const string sqlSelect = "SELECT * FROM t1";
using (var command = new SQLiteServerCommand(sqlSelect, client))
{
using (var reader = command.ExecuteReader())
{
Assert.IsTrue(reader.Read());
// we now now the types.
Assert.AreEqual(typeof(byte[]), reader.GetFieldType(0)); // SOMETYPE = object
Assert.AreEqual(typeof(long), reader.GetFieldType(1)); // integer
Assert.AreEqual(typeof(string), reader.GetFieldType(2)); // string
Assert.IsTrue(reader.Read());
Assert.AreEqual(typeof(byte[]), reader.GetFieldType(0)); // SOMETYPE = object
Assert.AreEqual(typeof(long), reader.GetFieldType(1)); // integer
Assert.AreEqual(typeof(long), reader.GetFieldType(2)); // int
Assert.IsFalse(reader.Read());
}
}
server.Close();
}
public void ServerCheckLongDefaultTimeout()
{
const int timeout = 60;
var shortTimeout1 = new SocketConnectionBuilder(Address, Port, Backlog, HeartBeatTimeOut);
var con1 = CreateConnection(shortTimeout1, timeout);
const string sql = @"WITH RECURSIVE r(i) AS (
VALUES(0)
UNION ALL
SELECT i FROM r
LIMIT 25000000
)
SELECT i FROM r WHERE i = 1;";
con1.Open();
using (var command = new SQLiteServerCommand(sql, con1))
{
Assert.AreEqual(-1, command.ExecuteNonQuery());
}
con1.Close();
}
public void ClosingConnectionWithOpenTransactions()
{
var server = CreateConnection();
server.Open();
var client = CreateConnection();
client.Open();
const string sqlMaster = "create table tb_config (name varchar(20), value INTEGER)";
using (var command = new SQLiteServerCommand(sqlMaster, client))
{
command.ExecuteNonQuery();
}
client.BeginTransaction();
for (var i = 0; i < 5; i++)
{
var sql = $"insert into tb_config(name, value) VALUES ('a', {i} )";
using (var command = new SQLiteServerCommand(sql, client))
{
command.ExecuteNonQuery();
}
}
// we are now going to close without commiting
client.Close();
const string select = "select * FROM tb_config";
using (var command = new SQLiteServerCommand(select, server))
{
using (var reader = command.ExecuteReader())
{
Assert.IsFalse(reader.HasRows);
}
}
server.Close();
}