public void TestInsertArrayColumnParam()
{
using (var cnn = ConnectionHandler.GetConnection()) {
var cmd = cnn.CreateCommand("INSERT INTO array_test (date,x, arr)values ('2017-01-01',1,@p)");
cmd.AddParameter("p", new[] { "*****@*****.**", "awdasdas" });
cmd.ExecuteNonQuery();
}
}
public void SelectDecimal()
{
using (var cnn = ConnectionHandler.GetConnection())
using (var cmd = cnn.CreateCommand("SELECT date,dec1,dec2,dec3 FROM decimal_test")) {
using (var reader = cmd.ExecuteReader()) {
PrintData(reader);
}
}
}
public void CreateStructures()
{
using (var cnn = ConnectionHandler.GetConnection()) {
cnn.CreateCommand("DROP TABLE IF EXISTS test_99_neg_num").ExecuteNonQuery();
cnn.CreateCommand("CREATE TABLE test_99_neg_num (FixedDate Date, Amount1 Decimal(16,4), Amount2 Decimal(20,8), Amount3 Decimal(22,10) ) ENGINE = MergeTree PARTITION BY toYYYYMM(FixedDate) ORDER BY FixedDate SETTINGS index_granularity = 8192").ExecuteNonQuery();
}
Thread.Sleep(1000);
}
public void TestBadNullableType()
{
using (var cnn = ConnectionHandler.GetConnection()) {
var cmd = cnn.CreateCommand("select 1,1,toNullable(max(datetime)), dumpColumnStructure(toNullable(max(datetime))) from default.nullable_date_time");
using (var reader = cmd.ExecuteReader()) {
reader.ReadAll(r => { Assert.True(r.IsDBNull(2)); });
}
}
}
public void CreateStructures()
{
using (var cnn = ConnectionHandler.GetConnection()) {
cnn.CreateCommand("DROP TABLE IF EXISTS test_87_lowcardinality").ExecuteNonQuery();
cnn.CreateCommand("CREATE TABLE test_87_lowcardinality (a LowCardinality(String)) ENGINE = Memory").ExecuteNonQuery();
}
Thread.Sleep(1000);
}
public void CreateStructures()
{
using (var cnn = ConnectionHandler.GetConnection()) {
cnn.CreateCommand("DROP TABLE IF EXISTS test_100_arr_first").ExecuteNonQuery();
cnn.CreateCommand("CREATE TABLE test_100_arr_first (d Date, a Array(String) ) ENGINE = MergeTree PARTITION BY toYYYYMM(d) ORDER BY d SETTINGS index_granularity = 8192").ExecuteNonQuery();
}
Thread.Sleep(1000);
}
public void CreateStructures()
{
using (var cnn = ConnectionHandler.GetConnection()) {
cnn.CreateCommand("DROP TABLE IF EXISTS test_119_stringescaping").ExecuteNonQuery();
cnn.CreateCommand("CREATE TABLE test_119_stringescaping (a String) ENGINE = Memory").ExecuteNonQuery();
}
Thread.Sleep(1000);
}
public void CreateStructures()
{
using (var cnn = ConnectionHandler.GetConnection()) {
cnn.CreateCommand("DROP TABLE IF EXISTS test_decimal").ExecuteNonQuery();
cnn.CreateCommand("CREATE TABLE test_decimal (k Date, d Decimal(20,4)) ENGINE = MergeTree(k, (d), 8192)").ExecuteNonQuery();
}
Thread.Sleep(1000);
}
public void CreateStructures()
{
using (var cnn = ConnectionHandler.GetConnection()) {
cnn.CreateCommand("DROP TABLE IF EXISTS test_dt64").ExecuteNonQuery();
cnn.CreateCommand("CREATE TABLE test_dt64 (k Date, dt64 DateTime64(5), dt64tz DateTime64(5,'Europe/Moscow')) ENGINE = MergeTree(k, (dt64,dt64tz), 8192)").ExecuteNonQuery();
}
Thread.Sleep(1000);
}
public void TestConnectionAutoClose()
{
using (var cnn = ConnectionHandler.GetConnection()) {
using (var cmd = cnn.CreateCommand("SELECT * FROM system.databases"))
using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
reader.ReadAll(r => { });
}
}
}
public void CreateStructures()
{
using (var cnn = ConnectionHandler.GetConnection()) {
cnn.CreateCommand("DROP TABLE IF EXISTS test_nfs").ExecuteNonQuery();
cnn.CreateCommand("CREATE TABLE test_nfs (k Date, a Int32, nfs Nullable(FixedString(2))) ENGINE = MergeTree(k, (a), 8192)").ExecuteNonQuery();
}
Thread.Sleep(1000);
}
public void TestInsertBulk()
{
using (var cnn = ConnectionHandler.GetConnection()) {
cnn.CreateCommand("INSERT INTO test_nfs (k, nfs) VALUES @bulk").AddParameter("bulk", DbType.Object, new object[] {
new object[] { DateTime.Now, "aa" }, new object[] { DateTime.Now, null }
})
.ExecuteNonQuery();
}
}
public void SelectIn()
{
using (var cnn = ConnectionHandler.GetConnection())
using (var cmd = cnn.CreateCommand("SELECT * FROM `test_data` WHERE user_id IN (@values)")) {
cmd.Parameters.Add("values", DbType.UInt64, new[] { 1L, 2L, 3L });
using (var reader = cmd.ExecuteReader()) {
PrintData(reader);
}
}
}
public void DecimalParam()
{
using (var cnn = ConnectionHandler.GetConnection()) {
var cmd = cnn.CreateCommand("insert into decimal_test values('1970-01-01',@d,@d,@d,@d)");
cmd.AddParameter("d", DbType.Decimal, 666m);
cmd.ExecuteNonQuery();
cmd = cnn.CreateCommand("insert into decimal_test values('1970-01-01',@d,@d,@d,@d)");
cmd.AddParameter("d", DbType.Decimal, -666m);
cmd.ExecuteNonQuery();
}
}
public void TestSsl()
{
using (var cnn = ConnectionHandler.GetConnection("Compress=False;BufferSize=32768;SocketTimeout=10000;CheckCompressedHash=False;Encrypt=True;Compressor=lz4;Host=ch-test.flippingbook.com;Port=9440;Database=default;User=andreya;Password=123")) {
var cmd = cnn.CreateCommand("insert into decimal_test values('1970-01-01',@d,@d,@d,@d)");
cmd.AddParameter("d", DbType.Decimal, 666m);
cmd.ExecuteNonQuery();
cmd = cnn.CreateCommand("insert into decimal_test values('1970-01-01',@d,@d,@d,@d)");
cmd.AddParameter("d", DbType.Decimal, -666m);
cmd.ExecuteNonQuery();
}
}
public void TestSelect()
{
using (var cnn = ConnectionHandler.GetConnection()) {
cnn.CreateCommand("INSERT INTO test_dt64 (k, dt64, dt64tz) VALUES (@k,@p,@p)").AddParameter("k", DbType.Date, new DateTime(2000, 01, 02))
.AddParameter("p", DbType.DateTime, new DateTime(2000, 01, 02, 01, 01, 01, 10)).ExecuteNonQuery();
var values = new List <Tuple <DateTime, DateTime, DateTime> >();
using (var cmd = cnn.CreateCommand("SELECT k, dt64, dt64tz FROM test_dt64 WHERE k=@k").AddParameter("k", DbType.Date, new DateTime(2000, 01, 02)))
using (var reader = cmd.ExecuteReader()) {
reader.ReadAll(r => { values.Add(Tuple.Create(r.GetDateTime(0), r.GetDateTime(1), r.GetDateTime(2))); });
}
}
}
public void TestRoundtripLiteral()
{
using (var cnn = ConnectionHandler.GetConnection()) {
cnn.CreateCommand("INSERT INTO test_98_dt64 (k, dt64, dt64tz) VALUES ('2020-01-02','2020-01-01 00:00:00','2020-01-01 00:00:00')").ExecuteNonQuery();
DateTime noOffset = DateTime.Now, offset = DateTime.Now;
cnn.CreateCommand("SELECT dt64, dt64tz FROM test_98_dt64 WHERE k='2020-01-02'").ExecuteReader().ReadAll(r =>
{
noOffset = r.GetDateTime(0);
offset = r.GetDateTime(1);
});
Assert.AreEqual(noOffset.ToString("O"), "2020-01-01T00:00:00.0000000Z");
Assert.AreEqual(offset.ToString("O"), "2020-01-01T00:00:00.0000000Z");
}
}
public void TestInsertArrayColumnBulk()
{
using (var cnn = ConnectionHandler.GetConnection()) {
var cmd = cnn.CreateCommand("INSERT INTO default.`super+` (date,email)values @bulk;");
cmd.Parameters.Add(
new ClickHouseParameter {
DbType = DbType.Object,
ParameterName = "bulk",
Value = new[] { new object[] { DateTime.Now, "*****@*****.**" }, new object[] { DateTime.Now.AddHours(-1), "" } }
}
);
cmd.ExecuteNonQuery();
}
}
public void TestRoudtrip()
{
using (var cnn = ConnectionHandler.GetConnection()) {
cnn.CreateCommand("INSERT INTO test_decimal (k, d) VALUES @bulk").AddParameter("bulk", DbType.Object, new object[] { new object[] { DateTime.Now, 387m }, new object[] { DateTime.Now, 0m } })
.ExecuteNonQuery();
var values = new List <Tuple <DateTime, decimal> >();
using (var cmd = cnn.CreateCommand("SELECT k, d FROM test_decimal ORDER BY d"))
using (var reader = cmd.ExecuteReader()) {
reader.ReadAll(r => { values.Add(Tuple.Create(r.GetDateTime(0), r.GetDecimal(1))); });
}
Assert.AreEqual(0, (double)values[0].Item2, .33);
Assert.AreEqual(387, (double)values[1].Item2, .33);
}
}
public void TestDecimal128()
{
using (var cnn = ConnectionHandler.GetConnection())
{
decimal d = 12345678901234567890123456789.1234567890m;
var values = new List <decimal>();
using (var cmd = cnn.CreateCommand("select toDecimal128('" + d + "',9) as f, toTypeName(f);"))
using (var reader = cmd.ExecuteReader())
{
reader.ReadAll(r => { values.Add(r.GetDecimal(0)); });
}
Assert.AreEqual(d, values[0]);
}
}
public void CreateDatabase()
{
using (var cnn = ConnectionHandler.GetConnection()) {
cnn.CreateCommand("DROP TABLE IF EXISTS guid_test").ExecuteNonQuery();
cnn.CreateCommand(
@" CREATE TABLE guid_test (
guid UUID,
dt DateTime)
ENGINE=MergeTree()
PARTITION BY toYYYYMM(dt)
ORDER BY (dt, guid)"
).ExecuteNonQuery();
}
Thread.Sleep(1000);
}
public void TestDateTimeInViewsV20()
{
using (var cnn = ConnectionHandler.GetConnection()) {
var cmd = cnn.CreateCommand("select * from test_dt20_view");
using (var reader = cmd.ExecuteReader()) {
reader.ReadAll(
r => {
var date = r.GetDateTime(0);
var dateTime = r.GetDateTime(1);
var val = r.GetString(2);
Console.WriteLine($"{date} {dateTime} {val}");
}
);
}
}
}
public void TestInsertAfterInsert()
{
using (var cnn = ConnectionHandler.GetConnection()) {
var cmd = cnn.CreateCommand("INSERT INTO test (date,val)values @bulk;");
cmd.Parameters.Add(
new ClickHouseParameter {
DbType = DbType.Object,
ParameterName = "bulk",
Value = new[] { new object[] { DateTime.Now, 1 }, new object[] { DateTime.Now.AddHours(-1), 2 } }
}
);
cmd.ExecuteNonQuery();
cmd.Parameters[0].Value = new[] { new object[] { DateTime.Now.AddHours(-2), 3 }, new object[] { DateTime.Now.AddHours(-3), 4 } };
cmd.ExecuteNonQuery();
}
}
public void TestInsertNestedColumnBulk()
{
using (var cnn = ConnectionHandler.GetConnection()) {
var cmd = cnn.CreateCommand("INSERT INTO nest_test (date,x, values.name,values.value)values @bulk;");
cmd.Parameters.Add(
new ClickHouseParameter {
DbType = DbType.Object,
ParameterName = "bulk",
Value = new[] {
new object[] { DateTime.Now, 1, new[] { "*****@*****.**", "awdasdas" }, new[] { "dsdsds", "dsfdsds" } }, new object[] { DateTime.Now.AddHours(-1), 2, new string[0], new string[0] }
}
}
);
cmd.ExecuteNonQuery();
}
}
public void TestRoundtripParameter()
{
using (var cnn = ConnectionHandler.GetConnection()) {
cnn.CreateCommand("INSERT INTO test_98_dt64 (k, dt64, dt64tz) VALUES ('2020-01-03',@d1,@d2)")
.AddParameter("d1", DbType.DateTime, new DateTime(2020, 01, 01, 0, 0, 0, DateTimeKind.Utc))
.AddParameter("d2", DbType.DateTime, new DateTime(2020, 01, 01, 0, 0, 0, DateTimeKind.Utc))
.ExecuteNonQuery();
DateTime noOffset = DateTime.Now, offset = DateTime.Now;
cnn.CreateCommand("SELECT dt64, dt64tz FROM test_98_dt64 WHERE k='2020-01-03'").ExecuteReader().ReadAll(r =>
{
noOffset = r.GetDateTime(0);
offset = r.GetDateTime(1);
});
Assert.AreEqual(noOffset.ToString("O"), "2020-01-01T00:00:00.0000000Z");
Assert.AreEqual(offset.ToString("O"), "2020-01-01T00:00:00.0000000Z");
}
}
public void CreateStructures()
{
using (var cnn = ConnectionHandler.GetConnection()) {
cnn.CreateCommand("DROP TABLE IF EXISTS test_decimal").ExecuteNonQuery();
cnn.CreateCommand("CREATE TABLE test_decimal (k Date, d Decimal(20,4)) ENGINE = MergeTree(k, (d), 8192)").ExecuteNonQuery();
cnn.CreateCommand("DROP TABLE IF EXISTS test_decimal_big").ExecuteNonQuery();
cnn.CreateCommand("CREATE TABLE test_decimal_big (k Date, d Decimal(38,16)) ENGINE = MergeTree(k, (d), 8192)").ExecuteNonQuery();
cnn.CreateCommand("DROP TABLE IF EXISTS test_decimal_big_double").ExecuteNonQuery();
cnn.CreateCommand("CREATE TABLE test_decimal_big_double (k Date, d Decimal(38,4)) ENGINE = MergeTree(k, (d), 8192)").ExecuteNonQuery();
if (cnn.ServerInfo.Build > BuildWithDecimal256Support)
{
cnn.CreateCommand("DROP TABLE IF EXISTS test_decimal_super_big").ExecuteNonQuery();
cnn.CreateCommand("CREATE TABLE test_decimal_super_big (k Date, d Decimal(70,50)) ENGINE = MergeTree(k, (d), 8192)").ExecuteNonQuery();
}
}
Thread.Sleep(1000);
}
public void TestRoundtrip()
{
using (var cnn = ConnectionHandler.GetConnection()) {
cnn.CreateCommand("INSERT INTO test_99_neg_num (FixedDate,Amount1,Amount2,Amount3) VALUES @bulk").AddParameter("bulk", DbType.Object, new object[] {
new object[] { DateTime.Now, -100m, -100m, -100m }
})
.ExecuteNonQuery();
var values = new List <Tuple <DateTime, decimal, decimal, decimal> >();
using (var cmd = cnn.CreateCommand("SELECT FixedDate,Amount1,Amount2,Amount3 FROM test_99_neg_num ORDER BY FixedDate"))
using (var reader = cmd.ExecuteReader()) {
reader.ReadAll(r => { values.Add(Tuple.Create(r.GetDateTime(0), r.GetDecimal(1), r.GetDecimal(2), r.GetDecimal(3))); });
}
Assert.AreEqual(-100, (double)values[0].Item2, .33);
Assert.AreEqual(-100, (double)values[0].Item3, .33);
Assert.AreEqual(-100, (double)values[0].Item4, .33);
}
}
public void Test()
{
using (var cnn = ConnectionHandler.GetConnection()) {
var items = new List <string>();
for (var i = 0; i < 1000; i++)
{
items.Add(((char)('A' + (i % 20))).ToString());
}
var result = cnn.CreateCommand("INSERT INTO test_87_lowcardinality (a) VALUES @bulk").AddParameter("bulk", DbType.Object, items.Select(x => (object)new object[] { x }).ToArray())
.ExecuteNonQuery();
var values = new List <string>();
using (var cmd = cnn.CreateCommand("SELECT a FROM test_87_lowcardinality"))
using (var reader = cmd.ExecuteReader()) {
reader.ReadAll(r => { values.Add(r.GetString(0)); });
}
Assert.IsTrue(items.SequenceEqual(values));
}
}
public void TestRoundtrip([Values(-100, -40.96, 40.96, 100, -1234567890.1234, 1234567890.1234)] decimal value)
{
using (var cnn = ConnectionHandler.GetConnection()) {
cnn.CreateCommand("INSERT INTO test_99_neg_num (FixedDate,Amount1,Amount2,Amount3) VALUES @bulk").AddParameter("bulk", DbType.Object, new object[] {
new object[] { DateTime.Now, value, value, value }
})
.ExecuteNonQuery();
var values = new List <Tuple <DateTime, decimal, decimal, decimal> >();
using (var cmd = cnn.CreateCommand("SELECT FixedDate,Amount1,Amount2,Amount3 FROM test_99_neg_num ORDER BY FixedDate"))
using (var reader = cmd.ExecuteReader()) {
reader.ReadAll(r => { values.Add(Tuple.Create(r.GetDateTime(0), r.GetDecimal(1), r.GetDecimal(2), r.GetDecimal(3))); });
}
var row = values.FirstOrDefault(x => decimal.Equals(x.Item2, value));
Assert.IsNotNull(row);
Assert.AreEqual(value, row.Item2);
Assert.AreEqual(value, row.Item3);
Assert.AreEqual(value, row.Item4);
}
}
public void TestRoundtrip()
{
using (var cnn = ConnectionHandler.GetConnection()) {
cnn.CreateCommand("INSERT INTO test_100_arr_first (a,d) VALUES @bulk").AddParameter("bulk", DbType.Object, new object[] {
new object[] { new string[] { "a", "b" }, DateTime.Now }
})
.ExecuteNonQuery();
var values = new List <Tuple <DateTime, string[]> >();
using (var cmd = cnn.CreateCommand("SELECT d,a FROM test_100_arr_first ORDER BY d"))
using (var reader = cmd.ExecuteReader()) {
reader.ReadAll(r => { values.Add(Tuple.Create(r.GetDateTime(0), ((object[])r.GetValue(1)).OfType <string>().ToArray())); });
}
Assert.Greater(values.Count, 0);
Assert.AreEqual("a", values[0].Item2[0]);
Assert.AreEqual("b", values[0].Item2[1]);
}
}
