DEV Community: Ilotus

[HELP] PG Trigger on before update

Ilotus — Fri, 29 Mar 2024 09:33:55 +0000

Hey everyone,

I'm seeking assistance with implementing a trigger in PostgreSQL. I'm currently struggling with crafting a trigger that functions as follows:

When a new record is inserted, if it contains a value for a specific column, I want to update the corresponding column in the existing record with this new value.

However, if the new record doesn't contain a value for this column (i.e., it's null, an empty object, or an empty array), I'd like to retain the old value in the existing record.

I've made an attempt by updating the NEW statement, but unfortunately, it hasn't worked as expected. Any guidance or assistance you can provide would be greatly appreciated. Thank you in advance for your help!

CREATE OR REPLACE FUNCTION coalesce_only_valuable_columns()
    RETURNS TRIGGER AS
$$
DECLARE
    col_name  text;
    col_value text;
BEGIN
    -- Loop through each column in the NEW row
    FOR col_name IN SELECT column_name
                    FROM information_schema.columns
                    WHERE table_name = TG_TABLE_NAME
        LOOP
            -- Get the value of the column
            EXECUTE format('SELECT $1.%I::text', col_name) INTO col_value USING NEW;

            -- If the new value is null or empty, set it to the old value
            IF col_value IS NULL OR col_value = '{}' OR col_value = '[]' THEN
                EXECUTE format('SELECT $1.%I::text', col_name) INTO col_value USING OLD;

                -- It does not work, so need another solution
                NEW[col_name] := col_value;
            END IF;
        END LOOP;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert_update_companies_coalesce_only_valuable_columns_trigger
    BEFORE UPDATE
    ON "companies"
    FOR EACH ROW
EXECUTE FUNCTION coalesce_only_valuable_columns();

[HELP] Best practices for pagination using EF Core 8

Ilotus — Tue, 23 Jan 2024 19:24:51 +0000

Hi everyone. I'm learning EF Core 8 and encountered some issues with pagination in the following code.

using Dashboard.Data;
using Dashboard.Data.Models;
using JetBrains.Annotations;
using MediatR;
using Microsoft.EntityFrameworkCore;
using Shared.Utils;

namespace Dashboard.Core.Advertising.Api;

[UsedImplicitly]
public class ListAdvertisersQuery : PaginationQuery, IRequest<PaginationResult<Advertiser>> {
  public bool WithCampaigns { get; set; }
}

[UsedImplicitly]
public class ListAdvertisersQueryHandler(DataContext dataContext) : IRequestHandler<ListAdvertisersQuery, PaginationResult<Advertiser>> {
  public async Task<PaginationResult<Advertiser>> Handle(ListAdvertisersQuery query, CancellationToken cancellationToken) {
    var queryable = dataContext.Advertisers.AsQueryable();

    if (query.WithCampaigns) {
      queryable = queryable.Include(x => x.Campaigns);
    }

    var totalItemsTask = queryable.CountAsync(cancellationToken);
    var resultTask = queryable.Take(query.Limit).ToListAsync(cancellationToken);

    await Task.WhenAll(resultTask, totalItemsTask);

    return PaginationResult<Advertiser>.From(resultTask.Result, totalItemsTask.Result, query);
  }
}

When I run this code multiple times, I encounter an error.

System.InvalidOperationException: A second operation was started on this context instance before a previous operation completed. This is usually caused by different threads concurrently using the same instance of DbContext. For more information on how to avoid threading issues with DbContext, see https://go.microsoft.com/fwlink/?linkid=2097913.

DataContext is registered in DI like this

builder.Services.AddDbContext<DataContext>(options => {
  var databaseConfiguration = builder.Configuration.GetSection(DatabaseConfiguration.SectionName).Get<DatabaseConfiguration>();

  options
    .UseNpgsql(databaseConfiguration!.ConnectionString)
    .UseSnakeCaseNamingConvention();
});

I don't want to await every query sequentially. Could you help me with it and explain how to run it in parallel?

How to keep list of different payment methods in a web account following DDD principles?

Ilotus — Wed, 25 Aug 2021 15:44:43 +0000

Hello everyone! I really would like to know your opinion about how better handle and keep different payment methods in a single web account. I consider the account as aggregate root in DDD terms and I want keep list of payment methods there. Also account has to have methods such as makePaymentMethodDefault, addPaymentMethod, removePaymentMethod etc. There is business rule saying that there is only one default payment method so when I remove or add new one I have to make other payment methods as not default. Bellow I showed how it looks now for me but I really don't like it. Actually there are other ways to do it, make a service to handle payment methods for account for instance but I want to make everything in account to be sure that all business rules always go correct and keep related items in one place.

Also in system can be a few payment methods:

Stripe tokenized card
PayPal
Something else in a future

Making payment happens in a different module so my question is not about how to charge money or make a payment but about how to keep payment methods.

This is my common payment method interface (TypeScript):

export interface IPaymentMethod {
  isDefault(): boolean;

  makeDefault(): void;

  getClass(): new(...args: any[]) => any;

  getPaymentProvider(): PaymentProvider;

  getPaymentMethodType(): PaymentMethodType;

  remove(): void;

  init(owner: IOwningPaymentMethod): void;
}

This is my first concrete payment method (TypeScript):

export class StripeTokenizedCardPaymentMethod implements IPaymentMethod {
  readonly token: string;
  readonly last4: string;
  readonly expYear: number;
  readonly expMonth: number;
  readonly country: string;
  readonly brand: string;
  private _owner: IOwningPaymentMethod;

  constructor(props: IStripeTokenizedCardPaymentMethodProps) {
    this.token = props.token;
    this.last4 = props.last4;
    this.expYear = props.expYear;
    this.expMonth = props.expMonth;
    this.country = props.country;
    this.brand = props.brand;
  }

  init(owner: IOwningPaymentMethod): void {
    this._owner = owner;
  }

  getClass() {
    return StripeTokenizedCardPaymentMethod;
  }

  getPaymentMethodType(): PaymentMethodType {
    return PaymentMethodType.StripeTokenizedCard;
  }

  getPaymentProvider(): PaymentProvider {
    return PaymentProvider.Stripe;
  }

  isDefault(): boolean {
    return this._owner.getDefaultPaymentMethod().getPaymentProvider() === this.getPaymentProvider()
      && this._owner.getDefaultPaymentMethod().getPaymentMethodType() === this.getPaymentMethodType();
  }

  makeDefault(): void {
    this._owner.makePaymentMethodDefault(this);
  }

  remove(): void {
    this._owner.removePaymentMethod(this);
  }
}

And finally aggregate root account (TypeScript):

export class Account extends BaseEntity<AccountId> implements IOwningPaymentMethod {
  get updatedAt() {
    return this._updatedAt;
  }

  readonly id: AccountId;
  readonly user: User;
  readonly createdAt: Moment;
  private _updatedAt: Moment;
  private _paymentMethods: IPaymentMethod[] = [];
  private _defaultPaymentMethod: IPaymentMethod;

  constructor(props: IAccountProps) {
    super();
    this.user = props.user;
    this.id = props.id;
    this._updatedAt = props.updatedAt;
    this.createdAt = props.createdAt;
  }

  getDefaultPaymentMethod(): IPaymentMethod {
    return this._defaultPaymentMethod;
  }

  makePaymentMethodDefault(paymentMethod: IPaymentMethod): void {
    this._defaultPaymentMethod = paymentMethod;
  }

  removePaymentMethod(paymentMethod: IPaymentMethod): void {
    throw new Error("Method not implemented.");
  }

  addPaymentMethod(paymentMethod: IPaymentMethod): void {
    paymentMethod.init(this);
    this._paymentMethods.push(paymentMethod);
  }

  static create(props: IAccountProps): Result<Account> {
    const guardResult = Guard.againstNullOrUndefinedBulk([
      { argument: props.user, argumentName: "user" }
    ]);

    if (!guardResult.succeeded) {
      return Result.fail(new Error(guardResult.message));
    }

    const defaultProps: IAccountProps = {
      ...props,
      id: props.id ?? GUID.generateUUID4(),
      createdAt: props.createdAt ?? moment(),
      updatedAt: props.updatedAt ?? moment()
    };

    const account = new Account(defaultProps);

    return Result.ok(account);
  }
}