public override int GetRowCount(DbConnection connection, IHasFullyQualifiedNameToo table, DbTransaction dbTransaction = null)
{
var cmd = new MySqlCommand("select count(*) from " + table.GetFullyQualifiedName(), (MySqlConnection)connection);
cmd.Transaction = dbTransaction as MySqlTransaction;
return(Convert.ToInt32(cmd.ExecuteScalar()));
}
/// <summary>
/// Replaces all references to the given table with the new table name in a columns SelectSQL. This will also save the column. Ensure
/// that newFullySpecifiedColumnName is in fact fully qualified too e.g. [mydb]..[mytable].[mycol]
/// </summary>
/// <param name="column"></param>
/// <param name="columnName"></param>
/// <param name="newFullySpecifiedColumnName"></param>
/// <param name="strict">Determines behaviour when column SelectSQL does not contain a reference to columnName. True will throw a RefactoringException, false will return without making any changes</param>
public void RefactorColumnName(IColumn column, IHasFullyQualifiedNameToo columnName, string newFullySpecifiedColumnName, bool strict = true)
{
string fullyQualifiedName = columnName.GetFullyQualifiedName();
if (!column.SelectSQL.Contains(fullyQualifiedName))
{
if (strict)
{
throw new RefactoringException("IColumn '" + column + "' did not contain the fully specified column name during refactoring ('" + fullyQualifiedName + "'");
}
else
{
return;
}
}
if (newFullySpecifiedColumnName.Count(c => c == '.') < 2)
{
throw new RefactoringException("Replacement column name was not fully specified, value passed was '" + newFullySpecifiedColumnName + "' which should have had at least 2 dots");
}
column.SelectSQL = column.SelectSQL.Replace(fullyQualifiedName, newFullySpecifiedColumnName);
Save(column);
}
public void BasicCase()
{
var ex = Assert.Throws <ArgumentException>(() => _manager.CommitResults(new CacheCommitExtractableAggregate(_config, "I've got a lovely bunch of coconuts", new DataTable(), 30)));
Assert.IsTrue(ex.Message.StartsWith("The DataTable that you claimed was an ExtractableAggregateResults had zero columns and therefore cannot be cached"));
DataTable dt = new DataTable();
dt.Columns.Add("Col1");
dt.Rows.Add("fishy!");
var ex2 = Assert.Throws <NotSupportedException>(() => _manager.CommitResults(new CacheCommitExtractableAggregate(_config, "I've got a lovely bunch of coconuts", dt, 30)));
Assert.IsTrue(
ex2.Message.StartsWith(
"Aggregate ExtractableAggregateCachingTests is not marked as IsExtractable therefore cannot be cached"));
_config.IsExtractable = true;
_config.SaveToDatabase();
//make the underlying column an is extraction identifier
_extractionInformation.IsExtractionIdentifier = true;
_extractionInformation.SaveToDatabase();
AggregateDimension dim = new AggregateDimension(CatalogueRepository, _extractionInformation, _config);
_config.ClearAllInjections();
var ex3 = Assert.Throws <NotSupportedException>(() => _manager.CommitResults(new CacheCommitExtractableAggregate(_config, "I've got a lovely bunch of coconuts", dt, 30)));
Assert.IsTrue(
ex3.Message.StartsWith(
"Aggregate ExtractableAggregateCachingTests contains dimensions marked as IsExtractionIdentifier or HashOnDataRelease (Col1)"));
_extractionInformation.IsExtractionIdentifier = false;
_extractionInformation.SaveToDatabase();
_config.ClearAllInjections();
Assert.DoesNotThrow(() => _manager.CommitResults(new CacheCommitExtractableAggregate(_config, "I've got a lovely bunch of coconuts", dt, 30)));
dim.DeleteInDatabase();
using (var con = DataAccessPortal.GetInstance().ExpectServer(QueryCachingDatabaseServer, DataAccessContext.InternalDataProcessing).GetConnection())
{
IHasFullyQualifiedNameToo table = _manager.GetLatestResultsTableUnsafe(_config, AggregateOperation.ExtractableAggregateResults);
con.Open();
using (var cmd = DatabaseCommandHelper.GetCommand("Select * from " + table.GetFullyQualifiedName(), con))
using (var r = cmd.ExecuteReader())
{
Assert.IsTrue(r.Read());
Assert.AreEqual("fishy!", r["Col1"]);
}
}
}
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 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);
}
/// <summary>
/// Replaces all references to the given table with the new table name in a ColumnInfo. This will also save the column. Ensure
/// that new tableName is in fact fully qualified e.g. '[db]..[tbl]'
/// </summary>
/// <param name="column"></param>
/// <param name="tableName"></param>
/// <param name="newFullySpecifiedTableName"></param>
public void RefactorTableName(ColumnInfo column, IHasFullyQualifiedNameToo tableName, string newFullySpecifiedTableName)
{
string fullyQualifiedName = tableName.GetFullyQualifiedName();
if (!column.Name.StartsWith(fullyQualifiedName))
{
throw new RefactoringException("ColumnInfo '" + column + "' did not start with the fully specified table name during refactoring ('" + fullyQualifiedName + "'");
}
if (!newFullySpecifiedTableName.Contains("."))
{
throw new RefactoringException("Replacement table name was not fully specified, value passed was '" + newFullySpecifiedTableName + "' which did not contain any dots");
}
column.Name = column.Name.Replace(fullyQualifiedName, newFullySpecifiedTableName);
column.SaveToDatabase();
}
/// <summary>
/// Replaces all references to the given table with the new table name in a columns SelectSQL. This will also save the column. Ensure
/// that new tableName is in fact fully qualified e.g. '[db]..[tbl]'
/// </summary>
/// <param name="column"></param>
/// <param name="tableName"></param>
/// <param name="newFullySpecifiedTableName"></param>
public void RefactorTableName(IColumn column, IHasFullyQualifiedNameToo tableName, string newFullySpecifiedTableName)
{
var ci = column.ColumnInfo;
if (ci == null)
{
throw new RefactoringException("Cannot refactor '" + column + "' because it's ColumnInfo was null");
}
string fullyQualifiedName = tableName.GetFullyQualifiedName();
if (!column.SelectSQL.Contains(fullyQualifiedName))
{
throw new RefactoringException("IColumn '" + column + "' did not contain the fully specified table name during refactoring ('" + fullyQualifiedName + "'");
}
if (!newFullySpecifiedTableName.Contains("."))
{
throw new RefactoringException("Replacement table name was not fully specified, value passed was '" + newFullySpecifiedTableName + "' which did not contain any dots");
}
column.SelectSQL = column.SelectSQL.Replace(fullyQualifiedName, newFullySpecifiedTableName);
Save(column);
}
public override int GetRowCount(DbConnection connection, IHasFullyQualifiedNameToo table, DbTransaction dbTransaction = null)
{
SqlCommand cmdCount = new SqlCommand(@"/*Do not lock anything, and do not get held up by any locks.*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Quickly get row counts.
declare @rowcount int = (SELECT distinct max(p.rows) AS [Row Count]
FROM sys.partitions p
INNER JOIN sys.indexes i ON p.object_id = i.object_id
AND p.index_id = i.index_id
WHERE OBJECT_NAME(p.object_id) = @tableName)
-- if we could not get it quickly then it is probably a view or something so have to return the slow count
if @rowcount is null
set @rowcount = (select count(*) from " + table.GetFullyQualifiedName() + @")
select @rowcount", (SqlConnection)connection);
cmdCount.Transaction = dbTransaction as SqlTransaction;
cmdCount.Parameters.Add(new SqlParameter("@tableName", SqlDbType.VarChar));
cmdCount.Parameters["@tableName"].Value = table.GetRuntimeName();
return(Convert.ToInt32(cmdCount.ExecuteScalar()));
}
public abstract int GetRowCount(DbConnection connection, IHasFullyQualifiedNameToo table, DbTransaction dbTransaction = null);
public abstract string GetTopXSqlForTable(IHasFullyQualifiedNameToo table, int topX);
public override string GetTopXSqlForTable(IHasFullyQualifiedNameToo table, int topX)
{
return("SELECT * FROM " + table.GetFullyQualifiedName() + " FETCH FIRST " + topX + " ROWS ONLY");
}
public override string GetTopXSqlForTable(IHasFullyQualifiedNameToo table, int topX)
{
return("SELECT TOP " + topX + " * FROM " + table.GetFullyQualifiedName());
}
