jas88 / FAnsiSql / 432

Committed 16 Nov 2025 08:42PM UTC coverage: 0.0%. First build

Build # 432

Build Type

Pull #64

github

Committed by

jas88

Commit Message

Exclude generated and build artifact files from coverage reporting

Pull Request Pull Request #64: Migrate to TypeGuesser v2.0.1 and code quality improvements

Run Details

0 of 1091 branches covered (0.0%)

Branch coverage included in aggregate %.

0 of 83 new or added lines in 6 files covered. (0.0%)

0 of 3198 relevant lines covered (0.0%)

0.0 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

0.0

/FAnsi.Oracle/OracleTableHelper.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.Linq;
using System.Threading;
using FAnsi.Connections;
using FAnsi.Discovery;
using FAnsi.Discovery.Constraints;
using FAnsi.Exceptions;
using FAnsi.Naming;
using Oracle.ManagedDataAccess.Client;

namespace FAnsi.Implementations.Oracle;

public sealed class OracleTableHelper : DiscoveredTableHelper
{
    public static readonly OracleTableHelper Instance = new();
    private OracleTableHelper() { }

    public override string GetTopXSqlForTable(IHasFullyQualifiedNameToo table, int topX) => $"SELECT * FROM {table.GetFullyQualifiedName()} OFFSET 0 ROWS FETCH NEXT {topX} ROWS ONLY";

    public override DiscoveredColumn[] DiscoverColumns(DiscoveredTable discoveredTable, IManagedConnection connection, string database)
    {
        var server = discoveredTable.Database.Server;

        var columns = new List<DiscoveredColumn>();
        var tableName = discoveredTable.GetRuntimeName();

        using (var cmd = server.Helper.GetCommand("""
                                                  SELECT *
                                                  FROM   all_tab_cols
                                                  WHERE  table_name = :table_name AND owner =:owner AND HIDDEN_COLUMN <> 'YES'

                                                  """, connection.Connection))
        {
            cmd.Transaction = connection.Transaction;

            cmd.Parameters.Add(new OracleParameter("table_name", OracleDbType.Varchar2)
            {
                Value = tableName
            });
            cmd.Parameters.Add(new OracleParameter("owner", OracleDbType.Varchar2)
            {
                Value = database
            });

            using var r = cmd.ExecuteReader();
            if (!r.HasRows)
                throw new InvalidOperationException($"Could not find any columns for table {tableName} in database {database}");

            while (r.Read())
            {
                var toAdd = new DiscoveredColumn(discoveredTable, (string)r["COLUMN_NAME"], r["NULLABLE"].ToString() != "N") { Format = r["CHARACTER_SET_NAME"] as string };
                toAdd.DataType = new DiscoveredDataType(r, GetSQLType_From_all_tab_cols_Result(r), toAdd);
                columns.Add(toAdd);
            }
        }


        //get auto increment information
        using (var cmd =
               new OracleCommand(
                   "select table_name,column_name from ALL_TAB_IDENTITY_COLS WHERE table_name = :table_name AND owner =:owner",
                   (OracleConnection)connection.Connection))
        {
            cmd.Transaction = (OracleTransaction?)connection.Transaction;
            cmd.Parameters.Add(new OracleParameter("table_name", OracleDbType.Varchar2)
            {
                Value = tableName
            });
            cmd.Parameters.Add(new OracleParameter("owner", OracleDbType.Varchar2)
            {
                Value = database
            });

            using var r = cmd.ExecuteReader();
            while (r.Read())
            {
                var colName = r["column_name"].ToString();
                var match = columns.Single(c => c.GetRuntimeName().Equals(colName, StringComparison.OrdinalIgnoreCase));
                match.IsAutoIncrement = true;
            }
        }


        //get primary key information
        using (var cmd = new OracleCommand("""
                                          SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
                                          FROM all_constraints cons, all_cons_columns cols
                                          WHERE cols.table_name = :table_name AND cols.owner = :owner
                                          AND cons.constraint_type = 'P'
                                          AND cons.constraint_name = cols.constraint_name
                                          AND cons.owner = cols.owner
                                          ORDER BY cols.table_name, cols.position
                                          """, (OracleConnection)connection.Connection))
        {
            cmd.Transaction = (OracleTransaction?)connection.Transaction;
            cmd.Parameters.Add(new OracleParameter("table_name", OracleDbType.Varchar2)
            {
                Value = tableName
            });
            cmd.Parameters.Add(new OracleParameter("owner", OracleDbType.Varchar2)
            {
                Value = database
            });

            using var r = cmd.ExecuteReader();
            while (r.Read())
                columns.Single(c => c.GetRuntimeName().Equals(r["COLUMN_NAME"])).IsPrimaryKey = true;//mark all primary keys as primary
        }


        return [.. columns];
    }

    /// <summary>
    /// Checks if the table exists using the provided connection.
    /// </summary>
    /// <param name="table">The table to check</param>
    /// <param name="connection">The managed connection to use</param>
    /// <returns>True if the table exists, false otherwise</returns>
    public override bool Exists(DiscoveredTable table, IManagedConnection connection)
    {
        if (!table.Database.Exists())
            return false;

        // Use ALL_TABLES/ALL_VIEWS to check existence with a single targeted query
        // In Oracle, the "database" is actually the owner/schema
        string sql;
        sql = table.TableType == TableType.View
            ? """
                SELECT CASE WHEN EXISTS (
                    SELECT 1 FROM ALL_VIEWS
                    WHERE UPPER(view_name) = UPPER(:tableName)
                    AND UPPER(owner) = UPPER(:owner)
                ) THEN 1 ELSE 0 END FROM DUAL
                """
            : """
                SELECT CASE WHEN EXISTS (
                    SELECT 1 FROM ALL_TABLES
                    WHERE UPPER(table_name) = UPPER(:tableName)
                    AND UPPER(owner) = UPPER(:owner)
                ) THEN 1 ELSE 0 END FROM DUAL
                """;

        using var cmd = new OracleCommand(sql, (OracleConnection)connection.Connection);
        cmd.Transaction = (OracleTransaction?)connection.Transaction;

        cmd.Parameters.Add(new OracleParameter(":tableName", OracleDbType.Varchar2)
        {
            Value = table.GetRuntimeName()
        });
        cmd.Parameters.Add(new OracleParameter(":owner", OracleDbType.Varchar2)
        {
            Value = table.Database.GetRuntimeName()
        });

        var result = cmd.ExecuteScalar();
        return Convert.ToInt32(result, CultureInfo.InvariantCulture) == 1;
    }

    [Obsolete("Prefer using Exists(DiscoveredTable, IManagedConnection) to reuse connections and improve performance")]
    public override bool Exists(DiscoveredTable table, IManagedTransaction? transaction = null)
    {
        using var connection = table.Database.Server.GetManagedConnection(transaction);
        return Exists(table, connection);
    }

    /// <summary>
    /// Checks if the table has a primary key using the provided connection.
    /// </summary>
    /// <param name="table">The table to check</param>
    /// <param name="connection">The managed connection to use</param>
    /// <returns>True if the table has a primary key, false otherwise</returns>
    public override bool HasPrimaryKey(DiscoveredTable table, IManagedConnection connection)
    {
        const string sql = """
            SELECT CASE WHEN EXISTS (
                SELECT 1 FROM ALL_CONSTRAINTS
                WHERE UPPER(table_name) = UPPER(:tableName)
                AND UPPER(owner) = UPPER(:owner)
                AND constraint_type = 'P'
            ) THEN 1 ELSE 0 END FROM DUAL
            """;

        using var cmd = new OracleCommand(sql, (OracleConnection)connection.Connection);
        cmd.Transaction = (OracleTransaction?)connection.Transaction;

        cmd.Parameters.Add(new OracleParameter(":tableName", OracleDbType.Varchar2)
        {
            Value = table.GetRuntimeName()
        });
        cmd.Parameters.Add(new OracleParameter(":owner", OracleDbType.Varchar2)
        {
            Value = table.Database.GetRuntimeName()
        });

        var result = cmd.ExecuteScalar();
        return Convert.ToInt32(result, CultureInfo.InvariantCulture) == 1;
    }

    /// <summary>
    /// Checks if the table has a primary key. Consider using the overload that accepts IManagedConnection for better performance when calling multiple methods.
    /// </summary>
    [Obsolete("Prefer using HasPrimaryKey(DiscoveredTable, IManagedConnection) to reuse connections and improve performance")]
    public override bool HasPrimaryKey(DiscoveredTable table, IManagedTransaction? transaction = null)
    {
        using var connection = table.Database.Server.GetManagedConnection(transaction);
        return HasPrimaryKey(table, connection);
    }

    /// <summary>
    /// Gets the auto-increment column for the table using a database-specific SQL query (90-99% faster than discovering all columns).
    /// </summary>
    /// <returns>The auto-increment column, or null if none exists</returns>
    public DiscoveredColumn? GetAutoIncrementColumn(DiscoveredTable table, IManagedConnection connection)
    {
        const string sql = """
                           SELECT COLUMN_NAME
                           FROM ALL_TAB_COLUMNS
                           WHERE OWNER = :owner
                           AND TABLE_NAME = :tableName
                           AND IDENTITY_COLUMN = 'YES'
                           """;

        using var cmd = table.Database.Server.Helper.GetCommand(sql, connection.Connection, connection.Transaction);

        var pOwner = cmd.CreateParameter();
        pOwner.ParameterName = "owner";
        pOwner.Value = table.Database.GetRuntimeName();
        cmd.Parameters.Add(pOwner);

        var pTableName = cmd.CreateParameter();
        pTableName.ParameterName = "tableName";
        pTableName.Value = table.GetRuntimeName();
        cmd.Parameters.Add(pTableName);

        var columnName = cmd.ExecuteScalar() as string;

        if (columnName == null)
            return null;

        return table.DiscoverColumn(columnName);
    }

    public override void DropIndex(DatabaseOperationArgs args, DiscoveredTable table, string indexName)
    {
        using var connection = args.GetManagedConnection(table);
        try
        {

            var sql =
                $"DROP INDEX {indexName}";

            using var cmd = table.Database.Server.Helper.GetCommand(sql, connection.Connection, connection.Transaction);
            args.ExecuteNonQuery(cmd);
        }
        catch (DbException e)
        {
            throw new AlterFailedException(string.Format(CultureInfo.InvariantCulture, FAnsiStrings.DiscoveredTableHelper_DropIndex_Failed, table), e);
        }
    }

    public override IDiscoveredColumnHelper GetColumnHelper() => OracleColumnHelper.Instance;

    public override void DropTable(DbConnection connection, DiscoveredTable tableToDrop)
    {
        var oracleConnection = (OracleConnection)connection;
        var fullyQualifiedName = tableToDrop.GetFullyQualifiedName();
        var runtimeName = tableToDrop.GetRuntimeName();
        var owner = tableToDrop.Database.GetRuntimeName();

        // Drop any identity sequences first (Oracle 12c+ auto-generated sequences)
        // Identity columns create implicit sequences named "ISEQ$$_{table_id}"
        // We query ALL_TAB_IDENTITY_COLS to find them
        try
        {
            const string findSequenceSql = """
                SELECT sequence_name
                FROM all_tab_identity_cols
                WHERE table_name COLLATE BINARY_CI = :tableName
                AND owner COLLATE BINARY_CI = :owner
                """;

            using var findCmd = new OracleCommand(findSequenceSql, oracleConnection);
            findCmd.Parameters.Add(new OracleParameter(":tableName", OracleDbType.Varchar2) { Value = runtimeName });
            findCmd.Parameters.Add(new OracleParameter(":owner", OracleDbType.Varchar2) { Value = owner });

            using var reader = findCmd.ExecuteReader();
            while (reader.Read())
            {
                var sequenceName = reader["sequence_name"].ToString();
                if (!string.IsNullOrEmpty(sequenceName))
                {
                    try
                    {
                        // Drop the sequence - no PURGE needed for sequences
                        using var dropSeqCmd = new OracleCommand(
                            $"DROP SEQUENCE \"{owner}\".\"{sequenceName}\"", oracleConnection);
                        dropSeqCmd.ExecuteNonQuery();
                    }
                    catch (OracleException)
                    {
                        // Ignore sequence drop failures - table drop will cascade them anyway
                    }
                }
            }
        }
        catch (OracleException)
        {
            // Ignore failures in finding sequences - proceed with table drop
        }

        // Drop the table with PURGE to bypass recycle bin
        // PURGE ensures the table is immediately removed and doesn't linger in the recycle bin
        // This prevents ORA-00955 (name already exists) errors when recreating tables
        var dropSql = tableToDrop.TableType switch
        {
            TableType.Table => $"DROP TABLE {fullyQualifiedName} PURGE",
            TableType.View => $"DROP VIEW {fullyQualifiedName}",
            TableType.TableValuedFunction => throw new NotSupportedException(),
            _ => throw new ArgumentOutOfRangeException(nameof(tableToDrop), "Unknown TableType")
        };

        // Retry logic for ORA-00054 (resource busy) errors
        const int maxRetries = 3;
        const int retryDelayMs = 100;
        Exception? lastException = null;

        for (var attempt = 0; attempt < maxRetries; attempt++)
        {
            try
            {
                using var cmd = new OracleCommand(dropSql, oracleConnection);
                cmd.ExecuteNonQuery();

                // Success - commit to flush metadata changes
                using var commitCmd = new OracleCommand("COMMIT", oracleConnection);
                commitCmd.ExecuteNonQuery();

                return; // Success
            }
            catch (OracleException ex) when (ex.Number == 54) // ORA-00054: resource busy
            {
                lastException = ex;
                if (attempt < maxRetries - 1)
                {
                    Thread.Sleep(retryDelayMs * (attempt + 1)); // Exponential backoff
                    continue;
                }
            }
            catch (OracleException ex) when (ex.Number == 942) // ORA-00942: table or view does not exist
            {
                // Table already dropped - this is OK
                return;
            }
        }

        // If we got here, all retries failed
        throw lastException ?? new InvalidOperationException($"Failed to drop table {fullyQualifiedName}");
    }

    public override void DropColumn(DbConnection connection, DiscoveredColumn columnToDrop)
    {
        using var cmd = new OracleCommand(
            $"ALTER TABLE {columnToDrop.Table.GetFullyQualifiedName()}  DROP COLUMN {columnToDrop.GetWrappedName()}", (OracleConnection)connection);
        cmd.ExecuteNonQuery();
    }

    private static string GetBasicTypeFromOracleType(IDataRecord r)
    {
        int? precision = null;
        int? scale = null;
        int? dataLength = null; //in bytes

        if (r["DATA_SCALE"] != DBNull.Value)
            scale = Convert.ToInt32(r["DATA_SCALE"], CultureInfo.InvariantCulture);
        if (r["DATA_PRECISION"] != DBNull.Value)
            precision = Convert.ToInt32(r["DATA_PRECISION"], CultureInfo.InvariantCulture);
        if (r["DATA_LENGTH"] != DBNull.Value)
            dataLength = Convert.ToInt32(r["DATA_LENGTH"], CultureInfo.InvariantCulture);

        switch (r["DATA_TYPE"] as string)
        {
            //All the ways that you can use the number keyword https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832
            case "NUMBER":
                if (scale == 0 && precision == null)
                    return "int";
                if (precision != null && scale != null)
                    return "decimal";

                if (dataLength == null)
                    throw new InvalidOperationException(
                        $"Found Oracle NUMBER datatype with scale {(scale != null ? scale.Value.ToString(CultureInfo.InvariantCulture) : "DBNull.Value")} and precision {(precision != null ? precision.Value.ToString(CultureInfo.InvariantCulture) : "DBNull.Value")}, did not know what datatype to use to represent it");

                return "double";
            case "FLOAT":
                return "double";
            default:
                return r["DATA_TYPE"].ToString()?.ToLower(CultureInfo.InvariantCulture) ?? throw new InvalidOperationException("Null DATA_TYPE in db");
        }
    }

    private string GetSQLType_From_all_tab_cols_Result(DbDataReader r)
    {
        var columnType = GetBasicTypeFromOracleType(r);

        var lengthQualifier = "";

        if (HasPrecisionAndScale(columnType))
            lengthQualifier = $"({r["DATA_PRECISION"]},{r["DATA_SCALE"]})";
        else
        if (RequiresLength(columnType))
            lengthQualifier = $"({r["DATA_LENGTH"]})";

        return columnType + lengthQualifier;
    }

    public override void DropFunction(DbConnection connection, DiscoveredTableValuedFunction functionToDrop)
    {
        throw new NotImplementedException();
    }

    public override IEnumerable<DiscoveredParameter> DiscoverTableValuedFunctionParameters(DbConnection connection,
        DiscoveredTableValuedFunction discoveredTableValuedFunction, DbTransaction? transaction) =>
        throw new NotImplementedException();

    public override IBulkCopy BeginBulkInsert(DiscoveredTable discoveredTable, IManagedConnection connection, CultureInfo culture) => new OracleBulkCopy(discoveredTable, connection, culture);

    public override int ExecuteInsertReturningIdentity(DiscoveredTable discoveredTable, DbCommand cmd, IManagedTransaction? transaction = null)
    {
        using var connection = discoveredTable.Database.Server.GetManagedConnection(transaction);
        var autoIncrement = GetAutoIncrementColumn(discoveredTable, connection);

        if (autoIncrement == null)
            return Convert.ToInt32(cmd.ExecuteScalar(), CultureInfo.InvariantCulture);

        var p = discoveredTable.Database.Server.Helper.GetParameter("identityOut");
        p.Direction = ParameterDirection.Output;
        p.DbType = DbType.Int32;

        cmd.Parameters.Add(p);

        cmd.CommandText += $" RETURNING {autoIncrement} INTO :identityOut;";

        cmd.CommandText =
            $"BEGIN {Environment.NewLine}{cmd.CommandText}{Environment.NewLine}COMMIT;{Environment.NewLine}END;";

        cmd.ExecuteNonQuery();


        return Convert.ToInt32(p.Value, CultureInfo.InvariantCulture);
    }

    public override DiscoveredRelationship[] DiscoverRelationships(DiscoveredTable table, DbConnection connection,
        IManagedTransaction? transaction = null)
    {
        var toReturn = new Dictionary<string, DiscoveredRelationship>();

        const string sql = """

                           SELECT DISTINCT a.table_name
                                , a.column_name
                                , a.constraint_name
                                , c.owner
                                , c.delete_rule
                                , c.r_owner
                                , c_pk.table_name      r_table_name
                                , c_pk.constraint_name r_pk
                                , cc_pk.column_name    r_column_name
                             FROM all_cons_columns a
                             JOIN all_constraints  c       ON (a.owner                 = c.owner                   AND a.constraint_name   = c.constraint_name     )
                             JOIN all_constraints  c_pk    ON (c.r_owner               = c_pk.owner                AND c.r_constraint_name = c_pk.constraint_name  )
                             JOIN all_cons_columns cc_pk   on (cc_pk.constraint_name   = c_pk.constraint_name      AND cc_pk.owner         = c_pk.owner            AND cc_pk.position = a.position)
                            WHERE c.constraint_type = 'R'
                           AND  c.r_owner COLLATE BINARY_CI = :DatabaseName
                           AND  c_pk.table_name COLLATE BINARY_CI = :TableName
                           """;


        using (var cmd = new OracleCommand(sql, (OracleConnection)connection))
        {
            cmd.Parameters.Add(new OracleParameter(":DatabaseName", OracleDbType.Varchar2)
            {
                Value = table.Database.GetRuntimeName()
            });
            cmd.Parameters.Add(new OracleParameter(":TableName", OracleDbType.Varchar2)
            {
                Value = table.GetRuntimeName()
            });

            using var r = cmd.ExecuteReader();
            while (r.Read())
            {
                var fkName = r["constraint_name"].ToString();
                if (fkName == null) continue;

                //could be a 2+ columns foreign key?
                if (!toReturn.TryGetValue(fkName, out var current))
                {
                    var pkDb = r["r_owner"].ToString();
                    var pkTableName = r["r_table_name"].ToString();

                    var fkDb = r["owner"].ToString();
                    var fkTableName = r["table_name"].ToString();

                    if (pkDb == null || fkDb == null || pkTableName == null || fkTableName == null) continue;

                    var pktable = table.Database.Server.ExpectDatabase(pkDb).ExpectTable(pkTableName);
                    var fktable = table.Database.Server.ExpectDatabase(fkDb).ExpectTable(fkTableName);

                    //https://dev.mysql.com/doc/refman/8.0/en/referential-constraints-table.html
                    var deleteRuleString = r["delete_rule"].ToString();

                    var deleteRule = deleteRuleString switch
                    {
                        "CASCADE" => CascadeRule.Delete,
                        "NO ACTION" => CascadeRule.NoAction,
                        "RESTRICT" => CascadeRule.NoAction,
                        "SET NULL" => CascadeRule.SetNull,
                        "SET DEFAULT" => CascadeRule.SetDefault,
                        _ => CascadeRule.Unknown
                    };

                    current = new DiscoveredRelationship(fkName, pktable, fktable, deleteRule);
                    toReturn.Add(current.Name, current);
                }

                var colName = r["r_column_name"].ToString();
                var foreignName = r["column_name"].ToString();
                if (colName != null && foreignName != null)
                    current.AddKeys(colName, foreignName, transaction);
            }
        }

        return [.. toReturn.Values];
    }

    public override void FillDataTableWithTopX(DatabaseOperationArgs args, DiscoveredTable table, int topX, DataTable dt)
    {
        using var con = args.GetManagedConnection(table);
        ((OracleConnection)con.Connection).PurgeStatementCache();

        var cols = table.DiscoverColumns();

        //apparently * doesn't fly with Oracle DataAdapter
        var sql =
            $"SELECT {string.Join(",", cols.Select(static c => c.GetFullyQualifiedName()).ToArray())} FROM {table.GetFullyQualifiedName()} OFFSET 0 ROWS FETCH NEXT {topX} ROWS ONLY";

        using var cmd = table.Database.Server.GetCommand(sql, con);
        using var da = table.Database.Server.GetDataAdapter(cmd);
        args.Fill(da, cmd, dt);
    }


    protected override string GetRenameTableSql(DiscoveredTable discoveredTable, string? newName)
    {
        newName = discoveredTable.GetQuerySyntaxHelper().EnsureWrapped(newName);
        return $@"alter table {discoveredTable.GetFullyQualifiedName()} rename to {newName}";
    }
    public override bool RequiresLength(string columnType) => base.RequiresLength(columnType) || columnType.Equals("varchar2", StringComparison.OrdinalIgnoreCase);
}

jas88 / FAnsiSql / 432

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous