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 void DropDatabase(bool ifExists = false)
{
string cluster = CreateParameters.NodeMode == ClickHouseNodeMode.Replicated ? CreateParameters.Cluster : null;
using (ClickHouseConnection conn = new ClickHouseConnection(ConnectionSettings.GetConnectionWithoutDb()))
{
conn.Open();
conn.Execute(DatabaseCommandText.DropDatabase(db: ConnectionSettings.Database, ifExists: ifExists, cluster: cluster));
}
}
protected virtual ClickHouseConnection CreateClickHouseConnection(string database = null)
{
string connectStr = $"Compress=True;CheckCompressedHash=False;Compressor=lz4;Host={_arguments.Host};Port={_arguments.Port};Database=system;User={_arguments.User};Password={_arguments.Password}";
var settings = new ClickHouseConnectionSettings(connectStr);
var cnn = new ClickHouseConnection(settings);
cnn.Open();
if (!string.IsNullOrWhiteSpace(database))
{
cnn.Execute($"USE {database};");
}
return(cnn);
}
public void CreateDatabase(bool ifNotExists = false, CreateDatabaseMode mode = CreateDatabaseMode.All)
{
string cluster = CreateParameters.NodeMode == ClickHouseNodeMode.Replicated ? CreateParameters.Cluster : null;
using (ClickHouseConnection conn = new ClickHouseConnection(ConnectionSettings.GetConnectionWithoutDb()))
{
conn.Open();
conn.Execute(DatabaseCommandText.CreateDatabase(db: ConnectionSettings.Database, ifNotExists: ifNotExists, cluster: cluster));
if ((mode & CreateDatabaseMode.Tables) != 0)
{
Schema.Tables.ToList().ForEach(t => t.Create(conn, TableEngines[t.EntityType], db: ConnectionSettings.Database, ifNotExists: ifNotExists, cluster: cluster));
}
if ((mode & CreateDatabaseMode.Views) != 0)
{
Schema.Views.ForEach(t => t.Create(conn, ConnectionSettings.Database, ifNotExists: ifNotExists));
}
if ((mode & CreateDatabaseMode.Dictionaries) != 0)
{
Schema.Dictionaries.ForEach(t => t.Create(conn, db: ConnectionSettings.Database, ifNotExists: ifNotExists));
}
}
}
public void Drop(ClickHouseConnection conn, string db, bool ifExists = false) => conn.Execute(CommandText.DropView(db, ifExists: ifExists));
public void Create(ClickHouseConnection conn, string db, bool ifNotExists = false) => conn.Execute(CommandText.CreateView(db, ifNotExists: ifNotExists));
static void CreateTable(ClickHouseConnection conn)
{
conn.Execute(
"CREATE TABLE IF NOT EXISTS TestUser (ResisterDate Date, ResisterTime DateTime, Name String, Age UInt16) ENGINE=MergeTree(ResisterDate,(ResisterTime,Name,Age), 8192)");
Console.WriteLine("Create table 'TestUser' success.");
}
private void CreateTable(ClickHouseConnection conn)
{
conn.Execute(
"CREATE TABLE IF NOT EXISTS TestUser (ResisterDate Date, ResisterTime DateTime64(3) DEFAULT now64(3), Name String, Age UInt16) ENGINE=MergeTree(ResisterDate,(ResisterTime,Name,Age), 8192)");
}
public static int Delete(this ClickHouseConnection conn, string tableName, string where)
{
string commandText = $"ALTER TABLE {tableName} DELETE WHERE {where}";
return(conn.Execute(commandText));
}
public static void Insert(this ClickHouseConnection conn, string tableName, params ColumnValue[] values)
{
string commandText = $"INSERT INTO {tableName}({string.Join(",", values.Select(t => t.Name).ToArray())}) VALUES ({string.Join(",", values.Select(t => t.Value.AsClickValue()).ToArray())})";
conn.Execute(commandText);
}
public void Truncate(ClickHouseConnection conn, string db, bool ifExists = false, string cluster = null) => conn.Execute(CommandText.TruncateTable(db: db, ifExists: ifExists, cluster: cluster));
public void Drop(ClickHouseConnection conn, string db, bool ifExists = false, string cluster = null) => conn.Execute(CommandText.DropTable(db: db, ifExists: ifExists));
public void Create(ClickHouseConnection conn, ITableEngine engine, string db, bool ifNotExists = false, string cluster = null) => conn.Execute(CommandText.CreateTable(GetColumnsWithTypes(), engine: engine, db: db, ifNotExists: ifNotExists, cluster: cluster));
