dev-npgsql/npgsql: Npgsql is the .NET data provider for PostgreSQL.

test/Npgsql.Tests/ReaderOldSchemaTests.cs
244 строки10 KB
Shay Rojansky
Obsolete ServerCompatibilityLevel.Redshift (#5927)
12 ноя 2024, 17:52
12 ноя 2024, 17:52196f405
using System;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using NUnit.Framework;
using static Npgsql.Tests.TestUtil;

namespace Npgsql.Tests;

/// <summary>
/// This tests the .NET Framework DbDataReader schema/metadata API, which returns DataTable.
/// For the new CoreCLR API, see <see cref="ReaderNewSchemaTests"/>.
/// </summary>
public class ReaderOldSchemaTests(SyncOrAsync syncOrAsync) : SyncOrAsyncTestBase(syncOrAsync)
{
    [Test]
    public async Task Primary_key_composite()
    {
        using var conn = await OpenConnectionAsync();
        var table = await GetTempTableName(conn);

        await conn.ExecuteNonQueryAsync($@"
CREATE TABLE {table} (
    field_pk1 INT2 NOT NULL,
    field_pk2 INT2 NOT NULL,
    field_serial SERIAL,
    CONSTRAINT {table}_pk PRIMARY KEY (field_pk1, field_pk2)
)");

        using var command = new NpgsqlCommand($"SELECT * FROM {table}", conn);
        using var dr = command.ExecuteReader(CommandBehavior.KeyInfo);
        dr.Read();
        var dataTable = await GetSchemaTable(dr);
        var keyColumns = dataTable!.Rows.Cast<DataRow>().Where(r => (bool)r["IsKey"]).ToArray()!;
        Assert.That(keyColumns, Has.Length.EqualTo(2));
        Assert.That(keyColumns.Count(c => (string)c["ColumnName"] == "field_pk1"), Is.EqualTo(1));
        Assert.That(keyColumns.Count(c => (string)c["ColumnName"] == "field_pk2"), Is.EqualTo(1));
    }

    [Test]
    public async Task Primary_key()
    {
        using var conn = await OpenConnectionAsync();
        var table = await CreateTempTable(conn, "id SERIAL PRIMARY KEY, serial SERIAL");

        using var command = new NpgsqlCommand($"SELECT * FROM {table}", conn);
        using var dr = command.ExecuteReader(CommandBehavior.KeyInfo);
        dr.Read();
        var metadata = await GetSchemaTable(dr);
        var key = metadata!.Rows.Cast<DataRow>().Single(r => (bool)r["IsKey"])!;
        Assert.That(key["ColumnName"], Is.EqualTo("id"));
    }

    [Test]
    public async Task IsAutoIncrement()
    {
        await using var conn = await OpenConnectionAsync();
        await IgnoreOnRedshift(conn, "Serial columns not supported on Redshift");

        var table = await CreateTempTable(conn, "serial SERIAL, int INT");

        var command = new NpgsqlCommand($"SELECT serial, int, 8 FROM {table}", conn);
        await using var reader = command.ExecuteReader(CommandBehavior.KeyInfo);
        var rows = (await GetSchemaTable(reader))!.Rows;

        Assert.That(rows[0]["IsAutoIncrement"], Is.True);
        Assert.That(rows[1]["IsAutoIncrement"], Is.False);
        Assert.That(rows[2]["IsAutoIncrement"], Is.False);
    }

    [Test]
    public async Task IsAutoIncrement_identity()
    {
        await using var conn = await OpenConnectionAsync();
        await IgnoreOnRedshift(conn, "Serial columns not supported on Redshift");
        MinimumPgVersion(conn, "10.0", "IDENTITY introduced in PostgreSQL 10");

        var table =
            await CreateTempTable(conn, "identity1 INT GENERATED BY DEFAULT AS IDENTITY, identity2 INT GENERATED ALWAYS AS IDENTITY");

        await using var command = new NpgsqlCommand($"SELECT identity1, identity2 FROM {table}", conn);
        await using var reader = command.ExecuteReader(CommandBehavior.KeyInfo);
        var rows = (await GetSchemaTable(reader))!.Rows;

        Assert.That(rows[0]["IsAutoIncrement"], Is.True);
        Assert.That(rows[1]["IsAutoIncrement"], Is.True);
    }

    [Test]
    public async Task IsIdentity()
    {
        await using var conn = await OpenConnectionAsync();
        await IgnoreOnRedshift(conn, "Identity columns not support on Redshift");
        MinimumPgVersion(conn, "10.0", "IDENTITY introduced in PostgreSQL 10");
        var table = await CreateTempTable(
            conn,
            "identity1 INT GENERATED BY DEFAULT AS IDENTITY, identity2 INT GENERATED ALWAYS AS IDENTITY, serial SERIAL, int INT");

        await using var cmd = new NpgsqlCommand($"SELECT identity1, identity2, serial, int, 8 FROM {table}", conn);
        await using var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo);
        var rows = (await GetSchemaTable(reader))!.Rows;

        Assert.That(rows[0]["IsIdentity"], Is.True);
        Assert.That(rows[1]["IsIdentity"], Is.True);
        Assert.That(rows[2]["IsIdentity"], Is.False);
        Assert.That(rows[3]["IsIdentity"], Is.False);
        Assert.That(rows[4]["IsIdentity"], Is.False);
    }

    [Test]
    public async Task IsReadOnly()
    {
        using var conn = await OpenConnectionAsync();
        var table = await GetTempTableName(conn);
        var view = await GetTempViewName(conn);

        await conn.ExecuteNonQueryAsync($@"
CREATE TABLE {table} (id SERIAL PRIMARY KEY, int2 SMALLINT);
CREATE OR REPLACE VIEW {view} (id, int2) AS SELECT id, int2 + int2 AS int2 FROM {table}");

        var command = new NpgsqlCommand($"SELECT id, int2 FROM {view}", conn);

        using var dr = command.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo);
        var metadata = await GetSchemaTable(dr);

        var idRow = metadata!.Rows.OfType<DataRow>().FirstOrDefault(x => (string)x["ColumnName"] == "id");
        Assert.IsNotNull(idRow, "Unable to find metadata for id column");
        var int2Row = metadata.Rows.OfType<DataRow>().FirstOrDefault(x => (string)x["ColumnName"] == "int2");
        Assert.IsNotNull(int2Row, "Unable to find metadata for int2 column");

        Assert.IsFalse((bool)idRow!["IsReadonly"]);
        Assert.IsTrue((bool)int2Row!["IsReadonly"]);
    }

    // ReSharper disable once InconsistentNaming
    [Test]
    public async Task AllowDBNull()
    {
        using var conn = await OpenConnectionAsync();
        var table = await CreateTempTable(conn, "nullable INTEGER, non_nullable INTEGER NOT NULL");

        using var cmd = new NpgsqlCommand($"SELECT * FROM {table}", conn);
        using var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo);
        using var metadata = await GetSchemaTable(reader);

        var nullableRow = metadata!.Rows.OfType<DataRow>().FirstOrDefault(x => (string)x["ColumnName"] == "nullable");
        Assert.IsNotNull(nullableRow, "Unable to find metadata for nullable column");
        var nonNullableRow = metadata.Rows.OfType<DataRow>().FirstOrDefault(x => (string)x["ColumnName"] == "non_nullable");
        Assert.IsNotNull(nonNullableRow, "Unable to find metadata for non_nullable column");

        Assert.IsTrue((bool)nullableRow!["AllowDBNull"]);
        Assert.IsFalse((bool)nonNullableRow!["AllowDBNull"]);
    }

    [Test, IssueLink("https://github.com/npgsql/npgsql/issues/1027")]
    public async Task Without_result()
    {
        using var conn = await OpenConnectionAsync();
        using var cmd = new NpgsqlCommand("SELECT 1", conn);
        using var reader = await cmd.ExecuteReaderAsync();
        reader.NextResult();
        // We're no longer on a result
        var table = await GetSchemaTable(reader);
        Assert.That(table, Is.Null);
    }

    [Test]
    public async Task Precision_and_scale()
    {
        using var conn = await OpenConnectionAsync();
        using var cmd = new NpgsqlCommand("SELECT 1::NUMERIC AS result", conn);
        using var reader = await cmd.ExecuteReaderAsync();
        var schemaTable = await GetSchemaTable(reader);
        foreach (var myField in schemaTable!.Rows.OfType<DataRow>())
        {
            Assert.That(myField["NumericScale"], Is.EqualTo(0));
            Assert.That(myField["NumericPrecision"], Is.EqualTo(0));
        }
    }

    [Test]
    public async Task SchemaOnly([Values(PrepareOrNot.NotPrepared, PrepareOrNot.Prepared)] PrepareOrNot prepare)
    {
        // if (prepare == PrepareOrNot.Prepared && IsMultiplexing)
        //     return;

        using var conn = await OpenConnectionAsync();
        var table = await CreateTempTable(conn, "name TEXT");

        var query = $@"
SELECT 1 AS some_column;
UPDATE {table} SET name='yo' WHERE 1=0;
SELECT 1 AS some_other_column, 2";

        using var cmd = new NpgsqlCommand(query, conn);
        if (prepare == PrepareOrNot.Prepared)
            cmd.Prepare();
        using (var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SchemaOnly))
        {
            Assert.That(reader.Read(), Is.False);
            var t = await GetSchemaTable(reader);
            Assert.That(t!.Rows[0]["ColumnName"], Is.EqualTo("some_column"));
            Assert.That(reader.NextResult(), Is.True);
            Assert.That(reader.Read(), Is.False);
            t = await GetSchemaTable(reader);
            Assert.That(t!.Rows[0]["ColumnName"], Is.EqualTo("some_other_column"));
            Assert.That(t.Rows[1]["ColumnName"], Is.EqualTo("?column?"));
            Assert.That(reader.NextResult(), Is.False);
        }

        // Close reader in the middle
        using (var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SchemaOnly))
            reader.Read();
    }

    [Test]
    public async Task BaseColumnName()
    {
        using var conn = OpenConnection();

        conn.ExecuteNonQuery(@"
                CREATE TEMP TABLE data (
                    Cod varchar(5) NOT NULL,
                    Descr varchar(40),
                    Date date,
                    CONSTRAINT PK_test_Cod PRIMARY KEY (Cod)
                );
            ");

        var cmd = new NpgsqlCommand("SELECT Cod as CodAlias, Descr as DescrAlias, Date FROM data", conn);

        using var dr = cmd.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo);
        var dt = await GetSchemaTable(dr);

        Assert.That(dt!.Rows[0]["BaseColumnName"].ToString(), Is.EqualTo("cod"));
        Assert.That(dt.Rows[0]["ColumnName"].ToString(), Is.EqualTo("codalias"));
        Assert.That(dt.Rows[1]["BaseColumnName"].ToString(), Is.EqualTo("descr"));
        Assert.That(dt.Rows[1]["ColumnName"].ToString(), Is.EqualTo("descralias"));
        Assert.That(dt.Rows[2]["BaseColumnName"].ToString(), Is.EqualTo("date"));
        Assert.That(dt.Rows[2]["ColumnName"].ToString(), Is.EqualTo("date"));
    }

    async Task<DataTable?> GetSchemaTable(NpgsqlDataReader dr) => IsAsync ? await dr.GetSchemaTableAsync() : dr.GetSchemaTable();
}