marcelolx/nowa

Simple query builder for Delphi, working with enumerators.

Nowa

Simple SQL query builder for Delphi, working with enumerated types.

Info:

• In development.

How to build queries

1. Do you need one enumerated type.

2. One class (that inherits TEnumAbstract<T> and implements IEnum<T>) to map the database fields based on the enumerated type.

3. One model that inherits TModel<T> and implements Set/GetValue functions declared on IModel<T> that are virtual on base model.

4. Use classes of NowaImpl.pas unit to build the queries.

An Simple select example:

procedure NowaExample.TestExample;
var
  oIPerson: IModel<TEPerson>;
begin
  oIPerson := TPerson.Create;
  oIPerson.PrepareModel('', [tepSequential, tepName, tepEmail]);

  TSQLSelect.Create.Ref
    .Fields([oIPerson.Fields])
    .From(oIPerson.Table)
    .Build;
end;

Example select with InnerJoin:

procedure NowaExample.TestExampleInnerJoin;
var
  oIPerson: IPerson<TEPerson>;
  oIMatriculation: IModel<TEMatriculation>;
  SelectCommand: String;
begin
  oIPerson := TPerson.Create;
  oIMatriculation := TMatriculation.Create;

  oIPerson.PrepareModel('', []);
  oIMatriculation.PrepareModel('', []);

  SelectCommand := TSQLSelect.Create.Ref
    .Fields([oIPerson.Fields, oIMatriculation.Fields])
    .From(oIPerson.Table)
    .InnerJoin(
      TSQLJoin.Create.Ref
        .Table(oIMatriculation.Table)
        .&On(
          TSQLCondition.Create.Ref
            .LeftTerm(oIMatriculation.Field(temPersonSequential))
            .Op(opEqual)
            .RightTerm(oIPerson.Field(tepSequential))
        )
    )
    .Build;
end;

The output assigned to SelectCommand is:

SELECT PERSON.NR_SEQUENTIAL AS PERSON_SEQUENTIAL,
      PERSON.FL_NAME AS PERSON_NAME,
      PERSON.DT_BIRTHDATE AS PERSON_BIRTHDATE,
      PERSON.TX_EMAIL AS PERSON_EMAIL,
      PERSON.TX_PASSWORD AS PERSON_PASSWORD,
      MATRICULATION.NR_SEQUENTIAL AS MATRICULATION_SEQUENTIAL,
      MATRICULATION.NR_PERSONSEQUENTIAL AS MATRICULATION_PERSONSEQUENTIAL,
      MATRICULATION.DT_DATE AS MATRICULATION_DATE,
      MATRICULATION.CD_USER AS PERSON_USER
 FROM TB_PERSON AS PERSON
INNER JOIN TB_MATRICULATION AS MATRICULATION ON (MATRICULATION.NR_PERSONSEQUENTIAL = PERSON.NR_SEQUENTIAL)

There is a lot to implement, but it is this idea that I want to follow.

Getting Started

A. Create a unit named "Enumerator.Person".

B. Create an enumerated TEPerson, see:

unit Enumerator.Person

interface

uses
  Enumerator;

type
  TEPerson = (tepSequential, tepName, tepBirthDate, tepEmail, tepPassword);

implementation

end.

C. Create a class who inherit TEnumAbstract and implements IEnum, see the class and function declarations:

unit Enumerator.Person

interface

uses
  Enumerator;

type
  TEPerson = (tepSequential, tepName, tepBirthDate, tepEmail, tepPassword);

  TEnumPessoa = class(TEnumAbstract<TEPerson>, IEnum<TEPerson>)
  public
    function Column(const AEnumeratedField: TEPerson): String; override;
    function Table: String; override;
    function TableAlias(const AAlias: String = ''): String; override;
    function Sequence: String; override;
    function AllColumns: TArray<TEPerson>; override;
    function Ref: IEnum<TEPerson>; override;
  end;

implementation

end.

nowa's Issues

2 issues found

unit (Nowa.ModelImpl.pas) Line23
Inkompatible Typen: 'System.TArray<Nowa.ModelImpl.TModel.T>' und 'Set'

unit (PersonImpl.pas)
Unit 'Person' not found.

Problems with inheritance of models

we can't inherit enumerators, and this prevent that we inherit models, some way to contour?

Refactor IModel<T> to return IField on functions FieldName/FieldNameAlias

Refactor FieldName/FieldNameAlias: String to return IField.

IField = interface
  ['{B4D2955E-B979-4EE0-96AD-5B12311635C7}']
    function Name: String;
    function Alias: String;
    function Table: String;
    function TableAlias: String;
  end;

this permits that we can write the sql query this way:

TSQLSelect.Create.Ref
    .Fields([oIPerson.PreparedFields, oIMatriculation.PreparedFields])
    .From(oIPerson.Table, oIPerson.TableAlias)
    .InnerJoin(
      TSQLJoin.Create.Ref
        .Table(oIMatriculation.TableAlias, oIMatriculation.Table)
        .&On(
          TSQLCondition.Create.Ref
            .LeftTerm(oIMatriculation.Field(temPersonSequential))
            .Op(opEqual)
            .RightTerm(oIPerson.Field(tepSequential))
        )
    )
    .Build;

and not (described on README.md):

TSQLSelect.Create.Ref
    .Fields([oIPerson.PreparedFields, oIMatriculation.PreparedFields])
    .From(oIPerson.Table, oIPerson.TableAlias)
    .InnerJoin(
      TSQLJoin.Create.Ref
        .Table(oIMatriculation.TableAlias, oIMatriculation.Table)
        .&On(
          TSQLCondition.Create.Ref
            .LeftTerm(oIMatriculation.TableAlias + '.' + oIMatriculation.FieldName(temPersonSequential))
            .Op(opEqual)
            .RightTerm(oIPerson.TableAlias + '.' + oIPerson.FieldName(tepSequential))
        )
    )
    .Build;

Change shape how to TNowaDAO<T> verifies if do insert or update model on .Save method

Includ new function 'IsNew: Boolean' in IModel that verifies if the model primary key is empty, if true, insert, else update.2

Utilities ->
On compound primary key, i doesn't know how to verifies if its a new register or an existing, and in this cases i can implement specific verification on 'IsNew'.

--> Change

• Nowa.Model.pas

IModel<T> = interface
  ['{C466292A-8F89-4B1F-B3BF-EE5588621D00}']
    procedure PrepareModel(const ATableAlias: String; const AFields: TArray<T>);
    procedure SetValue(const AField: T; const AValue: Variant);

    function Table: ITable;
    function GetValue(const AField: T): Variant;
    function Field(const AField: T): IField; overload;
    function Field(const AField: IField): T; overload;
    function Fields: TArray<IField>;
  end;

to

IModel<T> = interface
  ['{C466292A-8F89-4B1F-B3BF-EE5588621D00}']
    procedure PrepareModel(const ATableAlias: String; const AFields: TArray<T>);
    procedure SetValue(const AField: T; const AValue: Variant);

    function Table: ITable;
    function GetValue(const AField: T): Variant;
    function Field(const AField: T): IField; overload;
    function Field(const AField: IField): T; overload;
    function Fields: TArray<IField>;
    function IsNew: Boolean;
  end;

Implement test cases and remove DoInsert of TSQLCommand.

Optimize Save process

When save the same model in loop case for example, we doesn't need rebuild insert/update query, implement one way to reuse latest query builded for insert or update the model.

current implementation of save process is in NowaDAOImpl;