PgRoutiner Code-Gen

floki

Feb 12, 2024

Data-Access Extensions

pgroutiner -r | --r | -rout | --rout | -routines | --routines

pgroutiner -r | --r | -rout | --rout | -routines | --routines

// pgroutiner auto-generated code
#pragma warning disable CS8632
#pragma warning disable CS8618
using System.Threading.Tasks;
using NpgsqlTypes;
using Npgsql;

namespace dvdrental.Extensions;

public static class PgRoutineFilmInStock
{
    public const string Name = "public.film_in_stock";
    public const string Query = $"select p_film_count from {Name}($1, $2)";

    /// <summary>
    /// Executes sql function public.film_in_stock(integer, integer)
    /// </summary>
    /// <param name="pFilmId">p_film_id integer</param>
    /// <param name="pStoreId">p_store_id integer</param>
    /// <returns>IEnumerable of int? instances</returns>
    public static IEnumerable<int?> FilmInStock(this NpgsqlConnection connection, int? pFilmId, int? pStoreId)
    {
        using var command = new NpgsqlCommand(Query, connection)
        {
            CommandType = System.Data.CommandType.Text,
            Parameters =
            {
                new() { NpgsqlDbType = NpgsqlDbType.Integer, Value = pFilmId == null ? DBNull.Value : pFilmId },
                new() { NpgsqlDbType = NpgsqlDbType.Integer, Value = pStoreId == null ? DBNull.Value : pStoreId }
            },
            UnknownResultTypeList = new bool[] { false }
        };
        if (connection.State != System.Data.ConnectionState.Open)
        {
            connection.Open();
        }
        using var reader = command.ExecuteReader(System.Data.CommandBehavior.Default);
        while (reader.Read())
        {
            var value = reader.GetProviderSpecificValue(0);
            yield return value == DBNull.Value ? null : (int)value;
        }
    }

    /// <summary>
    /// Asynchronously executes sql function public.film_in_stock(integer, integer)
    /// </summary>
    /// <param name="pFilmId">p_film_id integer</param>
    /// <param name="pStoreId">p_store_id integer</param>
    /// <returns>IAsyncEnumerable of int? instances</returns>
    public static async IAsyncEnumerable<int?> FilmInStockAsync(this NpgsqlConnection connection, int? pFilmId, int? pStoreId)
    {
        using var command = new NpgsqlCommand(Query, connection)
        {
            CommandType = System.Data.CommandType.Text,
            Parameters =
            {
                new() { NpgsqlDbType = NpgsqlDbType.Integer, Value = pFilmId == null ? DBNull.Value : pFilmId },
                new() { NpgsqlDbType = NpgsqlDbType.Integer, Value = pStoreId == null ? DBNull.Value : pStoreId }
            },
            UnknownResultTypeList = new bool[] { false }
        };
        if (connection.State != System.Data.ConnectionState.Open)
        {
            await connection.OpenAsync();
        }
        using var reader = await command.ExecuteReaderAsync(System.Data.CommandBehavior.Default);
        while (await reader.ReadAsync())
        {
            var value = reader.GetProviderSpecificValue(0);
            yield return value == DBNull.Value ? null : (int)value;
        }
    }
}

// pgroutiner auto-generated code
#pragma warning disable CS8632
#pragma warning disable CS8618
using System.Threading.Tasks;
using NpgsqlTypes;
using Npgsql;

namespace dvdrental.Extensions;

public static class PgRoutineFilmInStock
{
    public const string Name = "public.film_in_stock";
    public const string Query = $"select p_film_count from {Name}($1, $2)";

    /// <summary>
    /// Executes sql function public.film_in_stock(integer, integer)
    /// </summary>
    /// <param name="pFilmId">p_film_id integer</param>
    /// <param name="pStoreId">p_store_id integer</param>
    /// <returns>IEnumerable of int? instances</returns>
    public static IEnumerable<int?> FilmInStock(this NpgsqlConnection connection, int? pFilmId, int? pStoreId)
    {
        using var command = new NpgsqlCommand(Query, connection)
        {
            CommandType = System.Data.CommandType.Text,
            Parameters =
            {
                new() { NpgsqlDbType = NpgsqlDbType.Integer, Value = pFilmId == null ? DBNull.Value : pFilmId },
                new() { NpgsqlDbType = NpgsqlDbType.Integer, Value = pStoreId == null ? DBNull.Value : pStoreId }
            },
            UnknownResultTypeList = new bool[] { false }
        };
        if (connection.State != System.Data.ConnectionState.Open)
        {
            connection.Open();
        }
        using var reader = command.ExecuteReader(System.Data.CommandBehavior.Default);
        while (reader.Read())
        {
            var value = reader.GetProviderSpecificValue(0);
            yield return value == DBNull.Value ? null : (int)value;
        }
    }

    /// <summary>
    /// Asynchronously executes sql function public.film_in_stock(integer, integer)
    /// </summary>
    /// <param name="pFilmId">p_film_id integer</param>
    /// <param name="pStoreId">p_store_id integer</param>
    /// <returns>IAsyncEnumerable of int? instances</returns>
    public static async IAsyncEnumerable<int?> FilmInStockAsync(this NpgsqlConnection connection, int? pFilmId, int? pStoreId)
    {
        using var command = new NpgsqlCommand(Query, connection)
        {
            CommandType = System.Data.CommandType.Text,
            Parameters =
            {
                new() { NpgsqlDbType = NpgsqlDbType.Integer, Value = pFilmId == null ? DBNull.Value : pFilmId },
                new() { NpgsqlDbType = NpgsqlDbType.Integer, Value = pStoreId == null ? DBNull.Value : pStoreId }
            },
            UnknownResultTypeList = new bool[] { false }
        };
        if (connection.State != System.Data.ConnectionState.Open)
        {
            await connection.OpenAsync();
        }
        using var reader = await command.ExecuteReaderAsync(System.Data.CommandBehavior.Default);
        while (await reader.ReadAsync())
        {
            var value = reader.GetProviderSpecificValue(0);
            yield return value == DBNull.Value ? null : (int)value;
        }
    }
}

// pgroutiner auto-generated code
#pragma warning disable CS8632
#pragma warning disable CS8618
using System.Threading.Tasks;
using NpgsqlTypes;
using Npgsql;
using dvdrental.Models;

namespace dvdrental.Extensions;

public static class PgRoutineRewardsReport
{
    public const string Name = "public.rewards_report";
    public const string Query = $"select customer_id, store_id, first_name, last_name, email, address_id, activebool, create_date, last_update, active from {Name}($1, $2)";

    /// <summary>
    /// Executes plpgsql function public.rewards_report(integer, numeric)
    /// </summary>
    /// <param name="minMonthlyPurchases">min_monthly_purchases integer</param>
    /// <param name="minDollarAmountPurchased">min_dollar_amount_purchased numeric</param>
    /// <returns>IEnumerable of Customer instances</returns>
    public static IEnumerable<Customer> RewardsReport(this NpgsqlConnection connection, int? minMonthlyPurchases, decimal? minDollarAmountPurchased)
    {
        using var command = new NpgsqlCommand(Query, connection)
        {
            CommandType = System.Data.CommandType.Text,
            Parameters =
            {
                new() { NpgsqlDbType = NpgsqlDbType.Integer, Value = minMonthlyPurchases == null ? DBNull.Value : minMonthlyPurchases },
                new() { NpgsqlDbType = NpgsqlDbType.Numeric, Value = minDollarAmountPurchased == null ? DBNull.Value : minDollarAmountPurchased }
            },
            UnknownResultTypeList = new bool[] { false, false, true, true, true, false, false, false, false, false }
        };
        if (connection.State != System.Data.ConnectionState.Open)
        {
            connection.Open();
        }
        using var reader = command.ExecuteReader(System.Data.CommandBehavior.Default);
        while (reader.Read())
        {
            object[] values = new object[10];
            reader.GetProviderSpecificValues(values);
            yield return new Customer
            {
                CustomerId = (int)values[0],
                StoreId = (short)values[1],
                FirstName = (string)values[2],
                LastName = (string)values[3],
                Email = values[4] == DBNull.Value ? null : (string)values[4],
                AddressId = (short)values[5],
                Activebool = (bool)values[6],
                CreateDate = (DateTime)values[7],
                LastUpdate = values[8] == DBNull.Value ? null : (DateTime)values[8],
                Active = values[9] == DBNull.Value ? null : (int)values[9]
            };
        }
    }

    /// <summary>
    /// Asynchronously executes plpgsql function public.rewards_report(integer, numeric)
    /// </summary>
    /// <param name="minMonthlyPurchases">min_monthly_purchases integer</param>
    /// <param name="minDollarAmountPurchased">min_dollar_amount_purchased numeric</param>
    /// <returns>IAsyncEnumerable of Customer instances</returns>
    public static async IAsyncEnumerable<Customer> RewardsReportAsync(this NpgsqlConnection connection, int? minMonthlyPurchases, decimal? minDollarAmountPurchased)
    {
        using var command = new NpgsqlCommand(Query, connection)
        {
            CommandType = System.Data.CommandType.Text,
            Parameters =
            {
                new() { NpgsqlDbType = NpgsqlDbType.Integer, Value = minMonthlyPurchases == null ? DBNull.Value : minMonthlyPurchases },
                new() { NpgsqlDbType = NpgsqlDbType.Numeric, Value = minDollarAmountPurchased == null ? DBNull.Value : minDollarAmountPurchased }
            },
            UnknownResultTypeList = new bool[] { false, false, true, true, true, false, false, false, false, false }
        };
        if (connection.State != System.Data.ConnectionState.Open)
        {
            await connection.OpenAsync();
        }
        using var reader = await command.ExecuteReaderAsync(System.Data.CommandBehavior.Default);
        while (await reader.ReadAsync())
        {
            object[] values = new object[10];
            reader.GetProviderSpecificValues(values);
            yield return new Customer
            {
                CustomerId = (int)values[0],
                StoreId = (short)values[1],
                FirstName = (string)values[2],
                LastName = (string)values[3],
                Email = values[4] == DBNull.Value ? null : (string)values[4],
                AddressId = (short)values[5],
                Activebool = (bool)values[6],
                CreateDate = (DateTime)values[7],
                LastUpdate = values[8] == DBNull.Value ? null : (DateTime)values[8],
                Active = values[9] == DBNull.Value ? null : (int)values[9]
            };
        }
    }
}

// pgroutiner auto-generated code
#pragma warning disable CS8632
#pragma warning disable CS8618
using System.Threading.Tasks;
using NpgsqlTypes;
using Npgsql;
using dvdrental.Models;

namespace dvdrental.Extensions;

public static class PgRoutineRewardsReport
{
    public const string Name = "public.rewards_report";
    public const string Query = $"select customer_id, store_id, first_name, last_name, email, address_id, activebool, create_date, last_update, active from {Name}($1, $2)";

    /// <summary>
    /// Executes plpgsql function public.rewards_report(integer, numeric)
    /// </summary>
    /// <param name="minMonthlyPurchases">min_monthly_purchases integer</param>
    /// <param name="minDollarAmountPurchased">min_dollar_amount_purchased numeric</param>
    /// <returns>IEnumerable of Customer instances</returns>
    public static IEnumerable<Customer> RewardsReport(this NpgsqlConnection connection, int? minMonthlyPurchases, decimal? minDollarAmountPurchased)
    {
        using var command = new NpgsqlCommand(Query, connection)
        {
            CommandType = System.Data.CommandType.Text,
            Parameters =
            {
                new() { NpgsqlDbType = NpgsqlDbType.Integer, Value = minMonthlyPurchases == null ? DBNull.Value : minMonthlyPurchases },
                new() { NpgsqlDbType = NpgsqlDbType.Numeric, Value = minDollarAmountPurchased == null ? DBNull.Value : minDollarAmountPurchased }
            },
            UnknownResultTypeList = new bool[] { false, false, true, true, true, false, false, false, false, false }
        };
        if (connection.State != System.Data.ConnectionState.Open)
        {
            connection.Open();
        }
        using var reader = command.ExecuteReader(System.Data.CommandBehavior.Default);
        while (reader.Read())
        {
            object[] values = new object[10];
            reader.GetProviderSpecificValues(values);
            yield return new Customer
            {
                CustomerId = (int)values[0],
                StoreId = (short)values[1],
                FirstName = (string)values[2],
                LastName = (string)values[3],
                Email = values[4] == DBNull.Value ? null : (string)values[4],
                AddressId = (short)values[5],
                Activebool = (bool)values[6],
                CreateDate = (DateTime)values[7],
                LastUpdate = values[8] == DBNull.Value ? null : (DateTime)values[8],
                Active = values[9] == DBNull.Value ? null : (int)values[9]
            };
        }
    }

    /// <summary>
    /// Asynchronously executes plpgsql function public.rewards_report(integer, numeric)
    /// </summary>
    /// <param name="minMonthlyPurchases">min_monthly_purchases integer</param>
    /// <param name="minDollarAmountPurchased">min_dollar_amount_purchased numeric</param>
    /// <returns>IAsyncEnumerable of Customer instances</returns>
    public static async IAsyncEnumerable<Customer> RewardsReportAsync(this NpgsqlConnection connection, int? minMonthlyPurchases, decimal? minDollarAmountPurchased)
    {
        using var command = new NpgsqlCommand(Query, connection)
        {
            CommandType = System.Data.CommandType.Text,
            Parameters =
            {
                new() { NpgsqlDbType = NpgsqlDbType.Integer, Value = minMonthlyPurchases == null ? DBNull.Value : minMonthlyPurchases },
                new() { NpgsqlDbType = NpgsqlDbType.Numeric, Value = minDollarAmountPurchased == null ? DBNull.Value : minDollarAmountPurchased }
            },
            UnknownResultTypeList = new bool[] { false, false, true, true, true, false, false, false, false, false }
        };
        if (connection.State != System.Data.ConnectionState.Open)
        {
            await connection.OpenAsync();
        }
        using var reader = await command.ExecuteReaderAsync(System.Data.CommandBehavior.Default);
        while (await reader.ReadAsync())
        {
            object[] values = new object[10];
            reader.GetProviderSpecificValues(values);
            yield return new Customer
            {
                CustomerId = (int)values[0],
                StoreId = (short)values[1],
                FirstName = (string)values[2],
                LastName = (string)values[3],
                Email = values[4] == DBNull.Value ? null : (string)values[4],
                AddressId = (short)values[5],
                Activebool = (bool)values[6],
                CreateDate = (DateTime)values[7],
                LastUpdate = values[8] == DBNull.Value ? null : (DateTime)values[8],
                Active = values[9] == DBNull.Value ? null : (int)values[9]
            };
        }
    }
}

Unit Tests

pgroutiner  -ut | --unit-tests

pgroutiner  -ut | --unit-tests

// pgroutiner auto-generated code

namespace dvdrentalTests;

///<summary>
/// Test method for sql function public.film_in_stock
///</summary>
public class FilmInStockUnitTests : PostgreSqlUnitTest
{
    public FilmInStockUnitTests(PostgreSqlUnitTestFixture tests) : base(tests) { }

    [Fact]
    public void FilmInStock_Test1()
    {
        // Arrange
        int? pFilmId = default;
        int? pStoreId = default;

        // Act
        var result = Connection.FilmInStock(pFilmId, pStoreId).ToList();

        // Assert
        // todo: adjust assert logic template to match actual logic
        pFilmId.Should().BeEquivalentTo(result);
    }

    [Fact]
    public async Task FilmInStockAsync_Test1()
    {
        // Arrange
        int? pFilmId = default;
        int? pStoreId = default;

        // Act
        var result = await Connection.FilmInStockAsync(pFilmId, pStoreId).ToListAsync();

        // Assert
        // todo: adjust assert logic template to match actual logic
        pFilmId.Should().BeEquivalentTo(result);
    }
}

// pgroutiner auto-generated code

namespace dvdrentalTests;

///<summary>
/// Test method for sql function public.film_in_stock
///</summary>
public class FilmInStockUnitTests : PostgreSqlUnitTest
{
    public FilmInStockUnitTests(PostgreSqlUnitTestFixture tests) : base(tests) { }

    [Fact]
    public void FilmInStock_Test1()
    {
        // Arrange
        int? pFilmId = default;
        int? pStoreId = default;

        // Act
        var result = Connection.FilmInStock(pFilmId, pStoreId).ToList();

        // Assert
        // todo: adjust assert logic template to match actual logic
        pFilmId.Should().BeEquivalentTo(result);
    }

    [Fact]
    public async Task FilmInStockAsync_Test1()
    {
        // Arrange
        int? pFilmId = default;
        int? pStoreId = default;

        // Act
        var result = await Connection.FilmInStockAsync(pFilmId, pStoreId).ToListAsync();

        // Assert
        // todo: adjust assert logic template to match actual logic
        pFilmId.Should().BeEquivalentTo(result);
    }
}

// pgroutiner auto-generated code

namespace dvdrentalTests;

///<summary>
/// Test method for plpgsql function public.rewards_report
///</summary>
public class RewardsReportUnitTests : PostgreSqlUnitTest
{
    public RewardsReportUnitTests(PostgreSqlUnitTestFixture tests) : base(tests) { }

    [Fact]
    public void RewardsReport_Test1()
    {
        // Arrange
        int? minMonthlyPurchases = default;
        decimal? minDollarAmountPurchased = default;

        // Act
        var result = Connection.RewardsReport(minMonthlyPurchases, minDollarAmountPurchased).ToList();

        // Assert
        // todo: adjust assert logic template to match actual logic
        Assert.Equal(default(List<Customer>), result);
    }

    [Fact]
    public async Task RewardsReportAsync_Test1()
    {
        // Arrange
        int? minMonthlyPurchases = default;
        decimal? minDollarAmountPurchased = default;

        // Act
        var result = await Connection.RewardsReportAsync(minMonthlyPurchases, minDollarAmountPurchased).ToListAsync();

        // Assert
        // todo: adjust assert logic template to match actual logic
        Assert.Equal(default(List<Customer>), result);
    }
}

// pgroutiner auto-generated code

namespace dvdrentalTests;

///<summary>
/// Test method for plpgsql function public.rewards_report
///</summary>
public class RewardsReportUnitTests : PostgreSqlUnitTest
{
    public RewardsReportUnitTests(PostgreSqlUnitTestFixture tests) : base(tests) { }

    [Fact]
    public void RewardsReport_Test1()
    {
        // Arrange
        int? minMonthlyPurchases = default;
        decimal? minDollarAmountPurchased = default;

        // Act
        var result = Connection.RewardsReport(minMonthlyPurchases, minDollarAmountPurchased).ToList();

        // Assert
        // todo: adjust assert logic template to match actual logic
        Assert.Equal(default(List<Customer>), result);
    }

    [Fact]
    public async Task RewardsReportAsync_Test1()
    {
        // Arrange
        int? minMonthlyPurchases = default;
        decimal? minDollarAmountPurchased = default;

        // Act
        var result = await Connection.RewardsReportAsync(minMonthlyPurchases, minDollarAmountPurchased).ToListAsync();

        // Assert
        // todo: adjust assert logic template to match actual logic
        Assert.Equal(default(List<Customer>), result);
    }
}

Model Generation

pgroutiner -mo | --mo | -model | --model | -model-output [ model ]
pgroutiner -mo | --mo | -model | --model | -model-output [ model ] -mof | --mof | -model-file | --model-file [ model file ]
pgroutiner -mo | --mo | -model | --model | -model-output [ model ] -mos | --mos | -model-save | --model-save | --model-save-to-model-dir

pgroutiner -mo | --mo | -model | --model | -model-output [ model ]
pgroutiner -mo | --mo | -model | --model | -model-output [ model ] -mof | --mof | -model-file | --model-file [ model file ]
pgroutiner -mo | --mo | -model | --model | -model-output [ model ] -mos | --mos | -model-save | --model-save | --model-save-to-model-dir

CRUD Functions

pgroutiner -create --crud-create [ similar exp ]
pgroutiner -create_returning -create-returning --crud-create-returning [ similar exp ]
pgroutiner -create_on_conflict_do_nothing -create-on-conflict-do-nothing --crud-create-on-conflict-do-nothing [ similar exp ]
pgroutiner -create_on_conflict_do_nothing_returning -create-on-conflict-do-nothing-returning --crud-create-on-conflict-do-nothing-returning [ similar exp ]
pgroutiner -create_on_conflict_do_update -create-on-conflict-do-update --crud-create-on-conflict-do-update [ similar exp ]
pgroutiner -create_on_conflict_do_update_returning -create-on-conflict-do-update-returning --crud-create-on-conflict-do-update-returning [ similar exp ]
pgroutiner -read_by -read-by --crud-read-by [ similar exp ]
pgroutiner -read_all -read-all --crud-read-all [ similar exp ]
pgroutiner -read_page -read-page --crud-read-page [ similar exp ]
pgroutiner -update --crud-update [ similar exp ]
pgroutiner -update_returning -update-returning --crud-update-returning [ similar exp ]
pgroutiner -delete_by -delete-by --crud-delete-by [ similar exp ]
pgroutiner -delete_by_returning -delete-by-returning --crud-delete-by-returning [ similar exp ]

pgroutiner -create --crud-create [ similar exp ]
pgroutiner -create_returning -create-returning --crud-create-returning [ similar exp ]
pgroutiner -create_on_conflict_do_nothing -create-on-conflict-do-nothing --crud-create-on-conflict-do-nothing [ similar exp ]
pgroutiner -create_on_conflict_do_nothing_returning -create-on-conflict-do-nothing-returning --crud-create-on-conflict-do-nothing-returning [ similar exp ]
pgroutiner -create_on_conflict_do_update -create-on-conflict-do-update --crud-create-on-conflict-do-update [ similar exp ]
pgroutiner -create_on_conflict_do_update_returning -create-on-conflict-do-update-returning --crud-create-on-conflict-do-update-returning [ similar exp ]
pgroutiner -read_by -read-by --crud-read-by [ similar exp ]
pgroutiner -read_all -read-all --crud-read-all [ similar exp ]
pgroutiner -read_page -read-page --crud-read-page [ similar exp ]
pgroutiner -update --crud-update [ similar exp ]
pgroutiner -update_returning -update-returning --crud-update-returning [ similar exp ]
pgroutiner -delete_by -delete-by --crud-delete-by [ similar exp ]
pgroutiner -delete_by_returning -delete-by-returning --crud-delete-by-returning [ similar exp ]

PgRoutiner Database Management

PgRoutiner Documentation