public override void CreateDatabase(DbConnectionStringBuilder builder, IHasRuntimeName newDatabaseName)
{
using (var con = new OracleConnection(builder.ConnectionString))
{
con.Open();
//create a new user with a random password!!! - go oracle this makes perfect sense database=user!
using (var cmd = new OracleCommand("CREATE USER \"" + newDatabaseName.GetRuntimeName() + "\" IDENTIFIED BY pwd" +
Guid.NewGuid().ToString().Replace("-", "").Substring(0, 27) //oracle only allows 30 character passwords
, con))
{
cmd.CommandTimeout = CreateDatabaseTimeoutInSeconds;
cmd.ExecuteNonQuery();
}
using (var cmd = new OracleCommand("ALTER USER \"" + newDatabaseName.GetRuntimeName() + "\" quota unlimited on system", con))
{
cmd.CommandTimeout = CreateDatabaseTimeoutInSeconds;
cmd.ExecuteNonQuery();
}
using (var cmd = new OracleCommand("ALTER USER \"" + newDatabaseName.GetRuntimeName() + "\" quota unlimited on users", con))
{
cmd.CommandTimeout = CreateDatabaseTimeoutInSeconds;
cmd.ExecuteNonQuery();
}
}
}
public void AddJoinToBuilder(AggregateConfiguration user, IColumn usersExtractionIdentifier, AggregateBuilder builder, QueryBuilderArgs args)
{
var joinableTableAlias = args.JoinIfAny.GetJoinTableAlias();
string joinDirection = args.JoinIfAny.GetJoinDirectionSQL();
IHasRuntimeName joinOn = null;
if (args.JoinedTo.Catalogue.IsApiCall(out IPluginCohortCompiler plugin))
{
if (plugin == null)
{
throw new Exception($"No IPluginCohortCompiler was found that supports API cohort set '{args.JoinedTo}'");
}
joinOn = plugin.GetJoinColumnForPatientIndexTable(args.JoinedTo);
}
else
{
joinOn = args.JoinedTo.AggregateDimensions.SingleOrDefault(d => d.IsExtractionIdentifier);
}
if (joinOn == null)
{
throw new QueryBuildingException(
$"AggregateConfiguration {user} uses a join aggregate (patient index aggregate) of {args.JoinedTo} but that AggregateConfiguration does not have an IsExtractionIdentifier dimension so how are we supposed to join these tables on the patient identifier?");
}
// will end up with something like this where 51 is the ID of the joinTable:
// LEFT Join (***INCEPTION QUERY***)ix51 on ["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].[patientIdentifier] = ix51.patientIdentifier
builder.AddCustomLine(
$" {joinDirection} Join ({Environment.NewLine}{TabIn(args.JoinSql.Sql, 1)}{Environment.NewLine}){joinableTableAlias}{Environment.NewLine}on {usersExtractionIdentifier.SelectSQL} = {joinableTableAlias}.{joinOn.GetRuntimeName()}", QueryComponent.JoinInfoJoin);
}
public string GetTopXSqlForColumn(IHasRuntimeName database, IHasFullyQualifiedNameToo table, IHasRuntimeName column, int topX, bool discardNulls)
{
//[dbx].[table]
string sql = "SELECT TOP " + topX + " " + column.GetRuntimeName() + " FROM " + table.GetFullyQualifiedName();
if (discardNulls)
{
sql += " WHERE " + column.GetRuntimeName() + " IS NOT NULL";
}
return(sql);
}
public string GetTopXSqlForColumn(IHasRuntimeName database, IHasFullyQualifiedNameToo table, IHasRuntimeName column, int topX, bool discardNulls)
{
string sql = "SELECT " + column.GetRuntimeName() + " FROM " + table.GetFullyQualifiedName();
if (discardNulls)
{
sql += " WHERE " + column.GetRuntimeName() + " IS NOT NULL";
}
sql += " OFFSET 0 ROWS FETCH NEXT " + topX + " ROWS ONLY";
return(sql);
}
public override void CreateDatabase(DbConnectionStringBuilder builder, IHasRuntimeName newDatabaseName)
{
var b = (MySqlConnectionStringBuilder)GetConnectionStringBuilder(builder.ConnectionString);
b.Database = null;
using (var con = new MySqlConnection(b.ConnectionString))
{
con.Open();
GetCommand("CREATE DATABASE `" + newDatabaseName.GetRuntimeName() + "`", con).ExecuteNonQuery();
}
}
public override void CreateDatabase(DbConnectionStringBuilder builder, IHasRuntimeName newDatabaseName)
{
var b = new SqlConnectionStringBuilder(builder.ConnectionString);
b.InitialCatalog = "master";
using (var con = new SqlConnection(b.ConnectionString))
{
con.Open();
using (SqlCommand cmd = new SqlCommand("CREATE DATABASE [" + newDatabaseName.GetRuntimeName() + "]", con))
cmd.ExecuteNonQuery();
}
}
public string GetTopXSqlForColumn(IHasRuntimeName database, IHasFullyQualifiedNameToo table, IHasRuntimeName column, int topX, bool discardNulls)
{
var syntax = new MySqlQuerySyntaxHelper();
string sql = "SELECT " + syntax.EnsureWrapped(column.GetRuntimeName()) + " FROM " + table.GetFullyQualifiedName();
if (discardNulls)
{
sql += " WHERE " + syntax.EnsureWrapped(column.GetRuntimeName()) + " IS NOT NULL";
}
sql += " LIMIT " + topX;
return(sql);
}
public string GetTopXSqlForColumn(IHasRuntimeName database, IHasFullyQualifiedNameToo table,
IHasRuntimeName column, int topX,
bool discardNulls)
{
string sql = "SELECT \"" + column.GetRuntimeName() + "\" FROM " + table.GetFullyQualifiedName();
if (discardNulls)
{
sql += " WHERE \"" + column.GetRuntimeName() + "\" IS NOT NULL";
}
sql += " fetch first " + topX + " rows only";
return(sql);
}
public override void CreateDatabase(DbConnectionStringBuilder builder, IHasRuntimeName newDatabaseName)
{
var b = (NpgsqlConnectionStringBuilder)GetConnectionStringBuilder(builder.ConnectionString);
b.Database = null;
using (var con = new NpgsqlConnection(b.ConnectionString))
{
con.Open();
using (var cmd = GetCommand("CREATE DATABASE \"" + newDatabaseName.GetRuntimeName() + '"', con))
{
cmd.CommandTimeout = CreateDatabaseTimeoutInSeconds;
cmd.ExecuteNonQuery();
}
}
}
public override void CreateDatabase(DbConnectionStringBuilder builder, IHasRuntimeName newDatabaseName)
{
var b = new SqlConnectionStringBuilder(builder.ConnectionString);
b.InitialCatalog = "master";
var syntax = new MicrosoftQuerySyntaxHelper();
using (var con = new SqlConnection(b.ConnectionString))
{
con.Open();
using (SqlCommand cmd = new SqlCommand("CREATE DATABASE " + syntax.EnsureWrapped(newDatabaseName.GetRuntimeName()), con))
{
cmd.CommandTimeout = CreateDatabaseTimeoutInSeconds;
cmd.ExecuteNonQuery();
}
}
}
public static bool IsHicPrefixed(IHasRuntimeName col)
{
return(IsHicPrefixed(col.GetRuntimeName()));
}
public abstract void CreateDatabase(DbConnectionStringBuilder builder, IHasRuntimeName newDatabaseName);
