/// <summary>
/// Checks if table exists in SQL Server database
/// </summary>
/// <param name="tableIdentifier">Table identifier model</param>
/// <returns>True if table exists</returns>
public override bool CheckIfTableExists(ISqlTableIdentifier tableIdentifier)
{
int count = GetScalar <int>("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE UPPER(TABLE_NAME) = UPPER({table})",
new { table = tableIdentifier.Table });
return(count > 0);
}
/// <summary>
/// The BuildCountQuery
/// </summary>
/// <param name="table">The table<see cref="ISqlTableIdentifier"/></param>
/// <param name="filter">The filter<see cref="ISqlConditionExpression"/></param>
/// <returns>The <see cref="IParameterizedQuery"/></returns>
public IParameterizedQuery BuildCountQuery(ISqlTableIdentifier table, ISqlConditionExpression filter)
{
string conditions;
IEnumerable <ISqlParameter> parameters;
if (filter != null)
{
IUncheckedSqlCondition uncheckedCondition = ConditionStringBuilder.Build(filter);
conditions = uncheckedCondition.SqlConditionString;
parameters = uncheckedCondition.Parameters;
foreach (string column in uncheckedCondition.UncheckedIdentifiers)
{
Logger.LogTrace($"Checking existence of {table.Table}.{column}");
if (!Connection.CheckIfColumnExists(table, column))
{
throw new InvalidOperationException($"Column {table.Table}.{column} does not exist or is not accessible.");
}
}
}
else
{
conditions = "1 = 1";
parameters = Enumerable.Empty <ISqlParameter>();
}
string tableStr = DbCommandBuilder.QuoteIdentifier(table.Table);
string sql = $"SELECT COUNT(*) FROM {tableStr} WHERE ({conditions})";
return(new DbQuery(sql, parameters));
}
/// <summary>
/// Checks if column exists in SQL Server database
/// </summary>
/// <param name="tableIdentifier">Table identifier</param>
/// <param name="columnIdentifier">Column identifier</param>
/// <returns>True if column exists</returns>
public override bool CheckIfColumnExists(ISqlTableIdentifier tableIdentifier, string columnIdentifier)
{
object[] parameters = new object[] { new { tableName = tableIdentifier.Table }, new { columnName = columnIdentifier } };
string query = @"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = {tableName} AND COLUMN_NAME = {columnName}";
int count = GetScalar <int>(query, parameters);
return(count > 0);
}
/// <summary>
/// Returns the primary key for given table
/// </summary>
/// <param name="table">Table identifier</param>
/// <returns>Primary key</returns>
private string GetPrimaryKeyForTableAsync(ISqlTableIdentifier table)
=> GetScalar <string>(@"SELECT column_name
FROM all_constraints cons, all_cons_columns cols
WHERE UPPER(cols.table_name) = UPPER({tableName})
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
ORDER BY cols.table_name, cols.position",
new[] { new { tableName = table.Table } });
/// <summary>
/// Checks if column exists in Oracle database
/// </summary>
/// <param name="tableIdentifier">Table identifier</param>
/// <param name="column">Column identifier</param>
/// <returns>True if column exists</returns>
public override bool CheckIfColumnExists(ISqlTableIdentifier tableIdentifier, string column)
{
string query = @"SELECT COUNT(*) FROM all_tab_cols WHERE table_name = {tableName} AND column_name = {columnName}";
object[] parameters = new object[] { new { tableName = tableIdentifier.Table }, new { columnName = column } };
int count = (int)GetScalar <decimal>(query, parameters);
return(count > 0);
}
/// <summary>
/// Returns the name of the first column in given SQL Server table
/// </summary>
/// <param name="tableIdentifier">Table identifier</param>
/// <returns>Name of the first column</returns>
public override string GetFirstColumnName(ISqlTableIdentifier tableIdentifier)
{
object[] parameters = new object[] { new { tableName = tableIdentifier.Table }, new { schema = tableIdentifier.Schema } };
string query = @"SELECT TOP 1 COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE UPPER(TABLE_NAME) = UPPER({tableName}) AND UPPER(TABLE_SCHEMA) = UPPER({schema})
ORDER BY ORDINAL_POSITION";
return(GetScalar <string>(query, parameters));
}
/// <summary>
/// Initializes a new instance of the <see cref="SqlSelectQuery"/> class.
/// </summary>
/// <param name="columns">List of columns in the SELECT clause</param>
/// <param name="tableIdentifier">Table identifier after the FROM clause</param>
/// <param name="sqlConditionExpression">Condition expression binary tree</param>
/// <param name="log">Logger instance</param>
public SqlSelectQuery(
IEnumerable <SqlColumn> columns,
ISqlTableIdentifier tableIdentifier,
ISqlConditionExpression sqlConditionExpression,
ILogger log)
{
this.columns = columns.ToList();
TableIdentifier = tableIdentifier;
Condition = sqlConditionExpression;
this.log = log ?? throw new ArgumentNullException(nameof(log));
}
/// <summary>
/// Checks if table exists in Oracle database
/// </summary>
/// <param name="tableIdentifier">Table identifier model</param>
/// <returns>True if table exists</returns>
public override bool CheckIfTableExists(ISqlTableIdentifier tableIdentifier)
{
int count = (int)GetScalar <decimal>(@"SELECT SUM(tbl.cnt)
FROM (
SELECT COUNT(*) cnt FROM user_tables WHERE UPPER(table_name) = UPPER({tableName})
UNION
SELECT COUNT(*) cnt FROM all_views WHERE UPPER(view_name) = UPPER({tableName})
) tbl", new { tableName = tableIdentifier.Table });
return(count > 0);
}
/// <summary> /// RDBMS specific check for column existence /// </summary> /// <param name="tableIdentifier">Table identifier model</param> /// <param name="column">Column identifier</param> /// <returns>True if column exists</returns> public abstract bool CheckIfColumnExists(ISqlTableIdentifier tableIdentifier, string column);
/// <summary> /// RDBMS specific check for table existence /// </summary> /// <param name="tableIdentifier">Table identifier model</param> /// <returns>True if the table exists</returns> public abstract bool CheckIfTableExists(ISqlTableIdentifier tableIdentifier);
/// <summary> /// Returns a first column in given table. Implementation is RDBMS specific. /// </summary> /// <param name="table">Table in which the first column is to be found</param> /// <returns>Name of the first column</returns> public abstract string GetFirstColumnName(ISqlTableIdentifier table);
/// <summary> /// Attempts to find and return the primary key found for given table. Implementation is RDBMS specific. /// </summary> /// <param name="table">Identification of the table where primary key is to be found</param> /// <param name="primaryKey">Primary key is returned in this out parameter.</param> /// <returns>True if primary key was identified, false if not.</returns> public abstract bool TryGetPrimaryKeyForTable(ISqlTableIdentifier table, out string primaryKey);
/// <summary>
/// Attempts to return a primary key for given SQL Server table.
/// </summary>
/// <param name="tableIdentifier">Table identifier</param>
/// <param name="primaryKey">Primary key identifier</param>
/// <returns>True whether the attempt to find a primary key succeeded</returns>
public override bool TryGetPrimaryKeyForTable(ISqlTableIdentifier tableIdentifier, out string primaryKey)
{
primaryKey = GetPrimaryKeyForTableAsync(tableIdentifier);
return(primaryKey != null);
}
/// <summary>
/// Returns the primary key for given table
/// </summary>
/// <param name="tableIdentifier">Table identifier</param>
/// <returns>Primary key</returns>
private string GetPrimaryKeyForTableAsync(ISqlTableIdentifier tableIdentifier)
=> GetScalar <string>(@"SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
AND UPPER(TABLE_NAME) = UPPER({tableName})",
new { tableName = tableIdentifier.Table });
/// <summary>
/// Returns the name of the first column in given oracle table
/// </summary>
/// <param name="table">Table identifier</param>
/// <returns>Name of the first column</returns>
public override string GetFirstColumnName(ISqlTableIdentifier table)
=> GetScalar <string>(@"SELECT column_name
FROM ALL_TAB_COLUMNS cols
WHERE UPPER(cols.table_name) = UPPER({tableName})
ORDER BY COLUMN_ID",
new { tableName = table.Table });
