public IEnumerable <T> Read <T>(ClickHouseConnection conn, string where = null) where T : new()
{
var table = Schema.GetTable <T>();
string commandText = SelectCommandText.Select(ConnectionSettings.Database, table.CommandText, where : where);
return(conn.Read <T>(commandText));
}
static void Main(string[] args)
{
var dbfilename = Path.GetFullPath("Northwind.db3");
ClickHouseConnectionSettings set = new ClickHouseConnectionSettings();
// set.Database = "Northwind";
set.User = "******";
set.Password = "";
set.Host = "10.200.101.163";
set.Port = 9000;
ClickHouseConnection clickHouseConnection = new ClickHouseConnection(set);
clickHouseConnection.Open();
using (var connection = new SQLiteConnection($"Data Source={dbfilename};Pooling=True")) {
connection.Open();
var northdb = new Northwind(connection);
northdb.DoReload(clickHouseConnection);
foreach (var emp in northdb.EmployeesList)
{
Console.WriteLine($"{emp.EmployeeID} {emp.Country} {emp.Title}");
}
connection.Close();
}
}
public ClickHouseContext(string connectionSettings, IExtendedActions extendedActions)
{
_extendedActions = extendedActions;
CheckDatabaseSettings(connectionSettings);
_connection = new ClickHouseConnection(connectionSettings);
_connection.Open();
var cmdDDL = _connection.CreateCommand();
var onModelCreatingParameters = new OnDatabaseModelConfiguringParameters()
{
Query_CreateTable_LogFiles = Resources.Query_CreateTable_LogFiles,
Query_CreateTable_RowsData = Resources.Query_CreateTable_RowsData
};
_extendedActions?.OnDatabaseModelConfiguring(this, onModelCreatingParameters);
cmdDDL.CommandText = onModelCreatingParameters.Query_CreateTable_RowsData
.Replace("{TemplateFields}", string.Empty);
cmdDDL.ExecuteNonQuery();
cmdDDL.CommandText = onModelCreatingParameters.Query_CreateTable_LogFiles
.Replace("{TemplateFields}", string.Empty);
cmdDDL.ExecuteNonQuery();
}
public static int Update(this ClickHouseConnection conn, string tableName, string where, params ColumnValue[] values)
{
string commandText = $"ALTER TABLE {tableName} "
+ $"UPDATE {string.Join(",", values.Select(t => $"{t.Name}={t.Value.AsClickValue()}").ToArray())} WHERE {where}";
return(conn.Execute(commandText));
}
public static void CHConnection(Action myMethodName)
{
WaitScreen.Splash();
try
{
con = new ClickHouseConnection(str);
con.Open();
Console.WriteLine("DBConnected");
myMethodName();
}
catch (ClickHouseException err)
{
Console.WriteLine(err);
}
finally
{
if (con != null)
{
con.Close();
}
}
}
protected AbstractConnectionTestFixture()
{
connection = TestUtilities.GetTestClickHouseConnection();
using var command = connection.CreateCommand();
command.CommandText = "CREATE DATABASE IF NOT EXISTS test;";
command.ExecuteScalar();
}
public async Task HandleDoubleCallbackException()
{
var settings = GetDefaultConnectionSettings();
await using var connection = new ClickHouseConnection(settings);
connection.StateChange += OnStateChanged;
var ex = await Assert.ThrowsAnyAsync <AggregateException>(() => connection.OpenAsync());
Assert.Equal(ConnectionState.Closed, connection.State);
Assert.Equal(2, ex.InnerExceptions.Count);
Assert.Equal("You shall not pass!", ex.InnerExceptions[0].Message);
Assert.Equal("How dare you!", ex.InnerExceptions[1].Message);
void OnStateChanged(object sender, StateChangeEventArgs e)
{
Assert.Same(sender, connection);
if (e.CurrentState == ConnectionState.Closed)
{
throw new Exception("How dare you!");
}
if (e.CurrentState == ConnectionState.Connecting)
{
throw new Exception("You shall not pass!");
}
}
}
public static ClickHouseDatabase CreateDatabase(this ClickHouseConnection conn) =>
new ClickHouseDatabase(
new ClickHouseConnectionSettings(conn.ConnectionString),
new ClickHouseCommandFormatter(),
new ClickHouseConnectionFactory(),
null,
new DefaultPropertyBinder());
public SqlInlineParametersSelectTest(bool useCompression)
{
var connectionStringBuilder = TestUtilities.GetConnectionStringBuilder();
connectionStringBuilder.Compression = useCompression;
connection = new OldClickHouseVersionConnection(connectionStringBuilder.ToString());
}
public ClickHouseConnection CreateConnection()
{
var connection = new ClickHouseConnection(CreateConnectionSettings());
// connection.Open();
return(connection);
}
public void CreateTableTest()
{
int res1;
int res2;
int res3;
using (ClickHouseConnection con = new ClickHouseConnection(settings))
{
con.Open();
ClickHouseCommand cmd = con.CreateCommand();
cmd.CommandText = "CREATE DATABASE IF NOT EXISTS `test`;";
res1 = cmd.ExecuteNonQuery();
cmd.CommandText = "DROP TABLE IF EXISTS test.temp_table;";
res2 = cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE IF NOT EXISTS test.temp_table ( OrderID Int32, Subject String, Date Date, Summ Decimal(15,2)) ENGINE = SummingMergeTree(Summ) PARTITION BY toYYYYMM(Date) ORDER BY (OrderID,Subject,Date);";
res3 = cmd.ExecuteNonQuery();
con.Close();
}
Assert.Equal(res1 + res2 + res3, 0);
}
static void Main(string[] args)
{
//Use TCP Port.
//If you are using Web application. Feel free to add ClickHouseConnection as Singleton. And add IClickHouseRepository as Scoped.
//Take example here:
//
//[CODE]
//services.AddScoped<IClickHouseRepository, ClickHouseRepository>();
//services.AddSingleton(x => new ClickHouseConnection(_configuration.GetValue<int>("Connection:Count"), _configuration.GetValue<string>("ConnectionStrings:SRV"))
// .InitSemaphore(_configuration.GetValue<int>("Connection:InitialConcurrentCount"), _configuration.GetValue<int>("Connection:MaximumConcurrentCount")));
//[CODE]
//If you don't need DI, just new object for yourself.
//Parameter explain: 30 is ConnectionPool size.
ClickHouseConnection _conn = new ClickHouseConnection(30, "Host=0.0.0.0;Port=9003;Database=Test;User=Test;Password=Test");
//Parameter explain: They are SemaphoreSlim initCount and maxCount
_conn.InitSemaphore(20, 20);
IClickHouseRepository repository = new ClickHouseRepository(_conn);
//Take Repository instance. Invoke GetResultAsync, passing your logic code here
var sql = @"SELECT ID, Name, Job FROM one_table";
var data = repository.GetResultAsync((conn) => GetSomeDataAsync(conn, sql));
//now you have the data.
Console.WriteLine("Hello World!");
}
private static DataTable DescribeColumns(ClickHouseConnection connection, string[] restrictions)
{
var query = new StringBuilder("SELECT database as Database, table as Table, name as Name, type as ProviderType, type as DataType FROM system.columns");
var database = restrictions != null && restrictions.Length > 0 ? restrictions[0] : null;
var table = restrictions != null && restrictions.Length > 1 ? restrictions[1] : null;
if (database != null)
{
query.Append($" WHERE database='{database}'");
}
if (table != null)
{
query.Append($" AND table='{table}'");
}
var result = connection.ExecuteDataTable(query.ToString());
foreach (var row in result.Rows.Cast <DataRow>())
{
var clickHouseType = TypeConverter.ParseClickHouseType((string)row["ProviderType"]);
row["ProviderType"] = clickHouseType.ToString();
// TODO: this should return actual framework type like other implementations do
row["DataType"] = clickHouseType.FrameworkType.ToString().Replace("System.", string.Empty);
}
return(result);
}
public static IEnumerable <T> Read <T>(this ClickHouseConnection conn, string commandText) where T : new()
{
using (ClickHouseDatabase db = conn.CreateDatabase())
{
return(db.Read <T>(commandText));
}
}
protected virtual void InitializeTable()
{
var conn = new ClickHouseConnection(_connectionString);
conn.Open();
var command = conn.CreateCommand();
command.CommandText = $"CREATE DATABASE IF NOT EXISTS {_database};";
command.ExecuteNonQuery();
if (Options.DropTable)
{
var sql = $"DROP TABLE IF EXISTS {_database}.{_table};";
Logger.Warning(sql);
command = conn.CreateCommand();
command.CommandText = sql;
command.ExecuteNonQuery();
}
command = conn.CreateCommand();
command.CommandText = GenerateCreateTableSql();
command.ExecuteNonQuery();
}
public void Setup()
{
_connectionSettings = ConnectionSettingsHelper.GetConnectionSettings();
using (var connection = new ClickHouseConnection(_connectionSettings))
{
connection.Open();
var cmd = connection.CreateCommand("DROP TABLE IF EXISTS ColumnWriterInsertBenchmarks");
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE ColumnWriterInsertBenchmarks(id UUID, str Nullable(String), dt DateTime, val Decimal64(4)) ENGINE = Memory";
cmd.ExecuteNonQuery();
}
_id = new List <Guid>(Rows);
_str = new List <string?>(Rows);
_dt = new List <DateTime>(Rows);
_val = new List <decimal>(Rows);
const string strSource =
"Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.";
var now = DateTime.Now;
var rnd = new Random(2306);
for (int i = 0; i < Rows; i++)
{
_id.Add(Guid.NewGuid());
_str.Add(i % strSource.Length == 0 ? null : strSource.Substring(0, i % strSource.Length));
_dt.Add(now.AddSeconds(i));
_val.Add(Math.Round((decimal)(rnd.NextDouble() - 0.5) * 100_000, 4));
}
}
static void Main(string[] args)
{
var conn = new ClickHouseConnection("Compress=True;CheckCompressedHash=False;Compressor=lz4;Host=192.168.10.110;Port=9000;Database=geekbuying;User=default;Password=;");
conn.Open();
CreateTable(conn);
try
{
var user = new TestUser()
{
ResisterDate = DateTime.Now, ResisterTime = DateTime.Now, Age = 18, Name = "张三2"
};
var user2 = new TestUser()
{
ResisterDate = DateTime.Now, ResisterTime = DateTime.Now, Age = 18, Name = "张三"
};
// conn.Insert(user);
Console.WriteLine("Insert single success.");
conn.InsertBulk(new List <TestUser>()
{
user, user2
});
Console.WriteLine("Insert multi success.");
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
conn.Close();
}
public override void FillTableSchema(DataTable table, string selectCommand, CommandParameterCollection parameters)
{
ClickHouseConnection clickHouseConnection = GetConnection() as ClickHouseConnection;
try
{
OpenConnection(clickHouseConnection);
selectCommand = PrepareSelectCommand(selectCommand, table.TableName, clickHouseConnection);
/*To reduce size of traffic and size of answer from ClickHouse server.
* Because FillSchema doesn't work in this ADO.NET library.
* LIMIT 0 gets an empy set, but we still have list of desired columns
* Prorably can be a better way.
*/
selectCommand += " LIMIT 0";
ClickHouseCommand clickHouseCommand = clickHouseConnection.CreateCommand();
foreach (CommandParameter p in parameters)
{
selectCommand = selectCommand.Replace($"@{p.Name}", $"{{{p.Name}:{(ClickHouseTypeCode)p.DataType}}}");
clickHouseCommand.AddParameter(p.Name, ((ClickHouseTypeCode)p.DataType).ToString(), p.Value);
}
clickHouseCommand.CommandText = selectCommand;
using (ClickHouseDataReader reader = clickHouseCommand.ExecuteReader() as ClickHouseDataReader)
{
var clms = GetColumns(reader);
table.Columns.AddRange(clms.ToArray());
}
}
finally
{
DisposeConnection(clickHouseConnection);
}
}
public async Task <ClickHouseConnection> OpenConnectionAsync(ClickHouseConnectionSettings settings, CancellationToken cancellationToken)
{
ClickHouseConnection connection = new ClickHouseConnection(settings);
await connection.OpenAsync(cancellationToken);
return(connection);
}
public async Task HandleCallbackException(ConnectionState throwOnState, ConnectionState expectedState)
{
var settings = GetDefaultConnectionSettings();
await using var connection = new ClickHouseConnection(settings);
connection.StateChange += OnStateChanged;
var ex = await Assert.ThrowsAnyAsync <ClickHouseException>(async() =>
{
await connection.OpenAsync();
await connection.CloseAsync();
});
Assert.Equal(expectedState, connection.State);
Assert.Equal(ClickHouseErrorCodes.CallbackError, ex.ErrorCode);
Assert.NotNull(ex.InnerException);
Assert.Equal("You shall not pass!", ex.InnerException !.Message);
void OnStateChanged(object sender, StateChangeEventArgs e)
{
Assert.Same(sender, connection);
if (e.CurrentState == throwOnState)
{
throw new Exception("You shall not pass!");
}
}
}
public async Task OpenConnectionInParallel()
{
// Only one thread should open the connection and other threads should fail
int counter = 0;
var settings = GetDefaultConnectionSettings();
await using var connection = new ClickHouseConnection(settings);
connection.StateChange += OnStateChanged;
var tcs = new TaskCompletionSource <bool>();
var tasks = Enumerable.Range(0, 32).Select(_ => Open()).ToList();
tcs.SetResult(true);
await Assert.ThrowsAnyAsync <Exception>(() => Task.WhenAll(tasks));
int notFailedTaskCount = 0;
foreach (var task in tasks)
{
if (task.Exception == null)
{
++notFailedTaskCount;
continue;
}
var aggrEx = Assert.IsAssignableFrom <AggregateException>(task.Exception);
Assert.Single(aggrEx.InnerExceptions);
var ex = Assert.IsAssignableFrom <ClickHouseException>(aggrEx.InnerExceptions[0]);
Assert.Equal(ClickHouseErrorCodes.InvalidConnectionState, ex.ErrorCode);
}
// There should be at least one completed task and several failed tasks
Assert.True(notFailedTaskCount > 0 && notFailedTaskCount < tasks.Count);
Assert.Equal(ConnectionState.Open, connection.State);
Assert.Equal(0x10001, counter);
void OnStateChanged(object sender, StateChangeEventArgs e)
{
Assert.Same(sender, connection);
if (e.OriginalState == ConnectionState.Closed && e.CurrentState == ConnectionState.Connecting)
{
Interlocked.Increment(ref counter);
}
if (e.OriginalState == ConnectionState.Connecting && e.CurrentState == ConnectionState.Open)
{
Interlocked.Add(ref counter, 0x10000);
}
}
async Task Open()
{
await tcs.Task;
await connection.OpenAsync();
}
}
public ClickHouseConnection OpenConnection(Action <ClickHouseConnectionStringBuilder>?updateSettings = null)
{
ClickHouseConnection connection = new ClickHouseConnection(GetDefaultConnectionSettings(updateSettings));
connection.Open();
return(connection);
}
private ClickHouseConnection GetConnection(string cstr = "Compress=True;CheckCompressedHash=False;Compressor=lz4;Host=ch-test.flippingbook.com;Port=9000;Database=default;User=andreya;Password=123")
{
var settings = new ClickHouseConnectionSettings(cstr);
var cnn = new ClickHouseConnection(settings);
cnn.Open();
return(cnn);
}
private ClickHouseConnection GetConnection(string cstr = "Compress=False;BufferSize=32768;SocketTimeout=10000;CheckCompressedHash=False;Compressor=lz4;Host=file-server;Port=9000;Database=default;User=test;Password=123")
{
var settings = new ClickHouseConnectionSettings(cstr);
var cnn = new ClickHouseConnection(settings);
cnn.Open();
return(cnn);
}
public ClickHouseConnection OpenConnection()
{
ClickHouseConnection connection = new ClickHouseConnection(GetDefaultConnectionSettings());
connection.Open();
return(connection);
}
private static ClickHouseConnection GetConnection()
{
var settings = new ClickHouseConnectionSettings(clickhouseConnectionString);
var cnn = new ClickHouseConnection(settings);
cnn.Open();
return(cnn);
}
private ClickHouseConnection GetConnection(string cstr = "Compress=True;CheckCompressedHash=False;Compressor=lz4;Host=localhost;Port=9001;Database=default;User=default;Password=;ConnectionTimeout=10000;SocketTimeout=10000")
{
var settings = new ClickHouseConnectionSettings(cstr);
var cnn = new ClickHouseConnection(settings);
cnn.Open();
return(cnn);
}
private static ClickHouseConnection GetConnection(string cstr = "Compress=True;CheckCompressedHash=False;Compressor=lz4;Host=192.168.32.239;Port=9334;Database=jcyj10;User=default;Password=")
{
var settings = new ClickHouseConnectionSettings(cstr);
var cnn = new ClickHouseConnection(settings);
cnn.Open();
return(cnn);
}
public ClickHouseConnection GetConnection()
{
var settings = new ClickHouseConnectionSettings(this._conn);
var conn = new ClickHouseConnection(settings);
conn.Open();
return(conn);
}
private ClickHouseConnection GetClickHouseConnection(string cstr = "Host=localhost;Port=9000;Database=default;User=default")
{
var settings = new ClickHouseConnectionSettings(cstr);
var connection = new ClickHouseConnection(settings);
connection.Open();
return(connection);
}
