///<inheritdoc/>
public IDbConnection CreateConnection()
{
var connection = new SnowflakeDbConnection();
connection.ConnectionString = _connectionString;
//NOTE: replace original database name with quoted name for case-sensitivity
//by default snowflake converts all object identifies into upper case unless it is enclosed in double quote
//do not rebuild the connection string because it will add single quote to the value
//https://docs.snowflake.com/en/sql-reference/identifiers-syntax.html
var connectionStringBuilder = new SnowflakeDbConnectionStringBuilder();
connectionStringBuilder.ConnectionString = _connectionString;
connectionStringBuilder.TryGetValue("db", out object result);
//db name is empty when checking if the database exists
if (null != result)
{
var databaseName = result.ToString();
if (!databaseName.ToString().IsDoubleQuoted())
{
var modifiedConnectionString = _connectionString.Replace(databaseName, databaseName.DoubleQuote());
connection.ConnectionString = modifiedConnectionString;
}
}
return(connection);
}
private string BuildConnectionString(Connection parmList)
{
var csItems = ClassToDictionary(parmList);
SnowflakeDbConnectionStringBuilder csb = new SnowflakeDbConnectionStringBuilder();
foreach (KeyValuePair <string, string> item in csItems)
{
csb.Add(item.Key, item.Value);
}
return(csb.ConnectionString);
}
///<inheritdoc/>
public ConnectionInfo GetConnectionInfo()
{
var connectionStringBuilder = new SnowflakeDbConnectionStringBuilder();
connectionStringBuilder.ConnectionString = _connectionString;
//extract the server information
connectionStringBuilder.TryGetValue("host", out object dataSource);
//extract the database name
connectionStringBuilder.TryGetValue("db", out object database);
return(new ConnectionInfo {
DataSource = dataSource?.ToString(), Database = database?.ToString()
});
}
///<inheritdoc/>
public IDbConnection CreateMasterConnection()
{
var connectionStringBuilder = new SnowflakeDbConnectionStringBuilder();
connectionStringBuilder.ConnectionString = _connectionString;
//NOTE: remove existing db & schema from connection string parameters
//this is necessary to avoid connection errors as it will attempt to connect to non-existing database
connectionStringBuilder.Remove("db");
connectionStringBuilder.Remove("schema");
var connection = new SnowflakeDbConnection();
connection.ConnectionString = connectionStringBuilder.ConnectionString;
return(connection);
}
public override string GetConnectionString(string databaseName)
{
var connectionString = EnvironmentHelper.GetEnvironmentVariable("YUNIQL_TEST_CONNECTION_STRING");
if (string.IsNullOrEmpty(connectionString))
{
throw new ApplicationException("Missing environment variable YUNIQL_TEST_CONNECTION_STRING. See WIKI for developer guides.");
}
//extract the default database name from connection string and replaced with test database name
var connectionStringBuilder = new SnowflakeDbConnectionStringBuilder();
connectionStringBuilder.ConnectionString = connectionString;
connectionStringBuilder.Remove("db");
connectionStringBuilder.Add("db", databaseName.DoubleQuote());
return(connectionStringBuilder.ConnectionString);
}
public override void DropDatabase(string connectionString)
{
//extract the test database name from connection string
var connectionStringBuilder = new SnowflakeDbConnectionStringBuilder();
connectionStringBuilder.ConnectionString = connectionString;
connectionStringBuilder.TryGetValue("db", out object databaseName);
var sqlStatement = $"DROP DATABASE {databaseName.ToString().DoubleQuote()};";
//prepare a connection to snowflake without any targetdb or schema, we need to remove these keys else it throws an error that db doesn't exists
var masterConnectionStringBuilder = new SnowflakeDbConnectionStringBuilder();
masterConnectionStringBuilder.ConnectionString = connectionString;
masterConnectionStringBuilder.Remove("db");
masterConnectionStringBuilder.Remove("schema");
base.ExecuteNonQuery(masterConnectionStringBuilder.ConnectionString, sqlStatement);
}
public override bool CheckIfDbExist(string connectionString)
{
//extract the test database name from connection string
var connectionStringBuilder = new SnowflakeDbConnectionStringBuilder();
connectionStringBuilder.ConnectionString = connectionString;
connectionStringBuilder.TryGetValue("db", out object databaseName);
//prepare the sql statement
var tokens = new List <KeyValuePair <string, string> > {
new KeyValuePair <string, string>(RESERVED_TOKENS.YUNIQL_DB_NAME, databaseName.ToString()),
new KeyValuePair <string, string>(RESERVED_TOKENS.YUNIQL_SCHEMA_NAME, base.SchemaName),
};
var sqlStatement = _tokenReplacementService.Replace(tokens, _dataService.GetSqlForCheckIfDatabaseExists());
//prepare a connection to snowflake without any targetdb or schema, we need to remove these keys else it throws an error that db doesn't exists
var masterConnectionStringBuilder = new SnowflakeDbConnectionStringBuilder();
masterConnectionStringBuilder.ConnectionString = connectionString;
masterConnectionStringBuilder.Remove("db");
masterConnectionStringBuilder.Remove("schema");
return(QuerySingleRow(masterConnectionStringBuilder.ConnectionString, sqlStatement));
}
public override void CleanupDbObjects(string connectionString)
{
var connectionStringBuilder = new SnowflakeDbConnectionStringBuilder();
connectionStringBuilder.ConnectionString = connectionString;
var sqlStatements = base.BreakStatements(File.ReadAllText(Path.Combine(Environment.CurrentDirectory, "Platforms", "Snowflake", "Cleanup.sql")));
sqlStatements.ForEach(sqlStatement => base.ExecuteNonQuery(connectionStringBuilder.ConnectionString, sqlStatement));
////extract the test database name from connection string
//var connectionStringBuilder = new SnowflakeDbConnectionStringBuilder();
//connectionStringBuilder.ConnectionString = connectionString;
//connectionStringBuilder.TryGetValue("db", out object databaseName);
//var sqlStatement = $"DROP DATABASE {databaseName.ToString().DoubleQuote()};";
////prepare a connection to snowflake without any targetdb or schema, we need to remove these keys else it throws an error that db doesn't exists
//var masterConnectionStringBuilder = new SnowflakeDbConnectionStringBuilder();
//masterConnectionStringBuilder.ConnectionString = connectionString;
//masterConnectionStringBuilder.Remove("db");
//masterConnectionStringBuilder.Remove("schema");
//base.ExecuteNonQuery(masterConnectionStringBuilder.ConnectionString, sqlStatement);
}
