Sunday, July 30, 2017

SQL, how do you write your queries?

OK - I know there are many ways to do a simple SQL Query... Like:


Query.SQL.Text := 'SELECT * FROM PersonDB where LastName LIKE "'+ALastName+'%" order by LastName,FirstName ASC LIMIT 50';


of course using Parameter is a better way, like:


Query.SQL.Text := 'SELECT * FROM PersonDB where LastName LIKE :0 order by LastName,FirstName ASC LIMIT 50';
Query.SQL.Params[0].AsString := ALastName+'%';



Perhaps you set you statements in the ObjectInspector or you copy the statement from an other tool.

But what if you are new to SQL?

What kind of errors happens in your SQLQuery?
Misspelled fieldname? Missing brackets, missing space or ",".

The problem is,- normally -  you can only find  this errors by executing the statement...


In my FDK I have all database definition in my sourcecode. All fields can easily checked against this definition.

If I do a search for a Person in my PersonDB, I like to shorten the search result by typing not only the Lastname... I like a search of both Firstname and Lastname.

If I search for my name in this database I can put "Lau,Fr" into the Searchedit and I get the result of all Person starting with the Lastname LIKE "Lau%" and FirstName LIKE "Fr%".

Perhaps you do other combinations.
In this case you always have an If or Case statement to select the right SQL, - or - to Ignore NULL DB Fields.

In my latest development of my ORM/CRUD DB Interface, I tried to include this expectations in my FDK.

For the given search problem I have this solution in every application:
 
procedure TPersonViewModel.Search(Const ASearchFor : String);
var
  FirstName,
  LastName  : String;
  P         : Integer;
begin
  LastName  :=  ASearchFor.Trim+'%';  
  P := Pos(',',ASearchFor);
  if P > 0
    then begin
          
FirstName := Copy(LastName,succ(P),
                             Length(LastName));
           Delete(LastName,P,Length(FirstName));
         end
    else FirstName   := '';

  // Normally
  if FirstName.Trim = ''
    then
Query.SQL.Text := 'SELECT ...'// Without FirstName
    else Query.SQL.Text := 'SELECT ...';// With FirstName
end
;


But I don't like this untestable SQL in this procedure. So I came up with this:


procedure TPersonViewModel.Search(Const ASearchFor : String);
var
 
SearchResult : ICanCRUDSearch; // from my CRUD-Framework
 
// .. Same as above
begin 
  // .. Same as above and then
 
  TCRUDSearch.&For(FPerson).

   {} Where('LASTNAME').LIKE(LastName.Trim).
   {}   begin_Optional(FirstName.Trim <> '').
   {}     _AND.Where('FIRSTNAME').LIKE(FirstName.Trim).
   {}   end_Optional.
   {} OrderBy('LASTNAME').OrderBy('FIRSTNAME ASC').
   {} Limit(100).
   {} Start(SearchResult);

   if SearchResult.SyntaxOnly then
     exit;
   // Perform UserIO on SearchResult
end;


And for Unit-Testing you can just call this same procedure and "Start" performs a systaxcheck only.

Perhaps you call it "Over Engineering" - I call it helpful. And more:


I love this kind of fluid-source-code. It's so easy to read and understandable.

The {} in front of each line are only to prevent the sourcecode formatter to kill my structure.


The Fluid-Interface helps you to build your statement - of course you should know a little bit of SQL, but...e.g.


After "Where" you can only use a comparer "LIKE, EQUAL, GREATER...". That's why "Where" is defined as



     ICanCRUDWhere = Interface
    Function  Where(Const AName : String) : ICanCRUDCompare;
  end;

  ICanCRUDCompare = Interface
    Function  LIKE(Const AValue : String) : ICanCRUDSearch;
    Function  EQUALS(Const AValue : String) : ICanCRUDSearch;
    // more...
  end;


The SearchResult stores the Data and you can iterate through it.

My FDK.FMXGridHelper can take this SearchResult and could direct perform the UserIO.

The CleanUp is done by RefCounting automatically.

Stay tuned for the next FDK update... More is coming...

Friday, July 14, 2017

Which Delphi Language Features are necessary?

Long time ago I was very satisfied with (Turbo)-Pascal.


Everything was fine! My world full of Records, (Fixed)Arrays and Shortstring has made me happy.


There was no need for a Database - I could read and write my Records - as easy as it could be - with blockread and blockwrite...

At this time I was able to write any program I wanted. (for the Kids: Program was the name in these days, today you would call it App)

Then "suddenly" records where bad and I "had to" use classes...


Too Bad... No block-read/write any more.. At this time you had to use Streams... (It finally leads to the same call, but "nobody" knows this...) 


Next Step: You could do fancy things with class operators and implicit. So back to Records?


Then, "suddenly" classes are bad and you have to code against Interfaces to decouple things for Unittests. Have you ever done Unittest in these "old" days?

As time goes by, new compilers were ignored... Delphi 2007 could be used for everything... Windows, Windows-Server(ISAPI.DLL) and ASP.NET...

Sometimes I took a look at new compilers, but without ASP.NET and this new very bad thing called Unicode,  I ignored  them.

So let's jump a few years into mobile development... Many new stuff...
Zero based Unicode Strings - no more Ansi- or Shortstrings, ARC, ARM, 64 Bit, Attributes, Generics, TPL and more...

After 6 years of development - using "new" language features - this features become so handy that I hate every hour of programming old apps with Delphi 2007.

Unbelievable, have you tried "cool stuff" without Generics, lately? Not using generics is like doing the same stuff over and over again. In the old day's we called it copy&paste (OK, Generics is doing copy&paste internally, but it feels better).
And what's about Unit-Test? Are you still using a Hello-World-One-Button-Apps with a Button labeled "Button1" on it to test new Source-Code?

Testdriven-Development - That is the right workflow to speed up development. Of course with Testinsight as you can read in my blogpost from April.


Do you want to see the power of Testdriven-Development - visit my Session at Forentage 2017.

Using new language features? All I can get...





Forentage 2017 - Meine Session.

Hallo zusammen! (English text below)


Ich habe gerade die Bestätigung für meine Session auf den Forentage 2017 erhalten.

Ich nenne sie:


MVVM-Lite oder wie man eine Trennung von Form (View) und Logik (ViewModel) mit ein paar Zeilen Code erreichen kann!


Eine Voraussetzung für die Testbarkeit von Programmen ist die Trennung von UI und Logik, diese Session zeigt, wie dies - ohne die Verwendung eines Framework - mit den einfachsten Möglichkeiten unter VCL & FMX erreichen kann.

Um eine schnellere Entwicklung zu erreichen, werde ich diese Session als Testdriven-Development zeigen.




Hello Everybody!

I just got the confirmation of my session on the Forentage 2017.



I call it:

MVVM-Lite or how to achieve a separation of form (View) and logic (ViewModel) with a few lines of code!

A prerequisite for the testability of programs is the separation of UI and logic, this session shows how to do this without using a framework - with the simplest possibilities under VCL & FMX.

To achieve a faster development, I will show this session as Testdriven-development.


Lesson is in German language. If I find the time I will do an english video later...



Tuesday, July 11, 2017

Are you using Attributes?

Why not?  Too complicate? Not needed?


Let's take a look at this little stupid class:


  [ Bar(42) ]
  [ FooBar('Test') ]
  TFoo = class
    private
      [ FieldName('StringField') ]
      [ FieldLength(30) ]
      FField1 : String;
      [ FieldName('IntegerField') ]
      FField2 : Integer;
  end;



How can we get this Attribute-Values?

Over the RTTI of course...


What do you think about this approach:


  TRTTIHelper.OnClassHasAttributes( TFoo )
   {} .OnAttribute< BarAttribute >(
        procedure( Bar : BarAttribute )
          begin
            _Result1 := Bar.IntValue;
          end )
   {} .OnAttribute< FooBarAttribute >(
        procedure( FooBar : FooBarAttribute )
          begin
            _Result2 := FooBar.StrValue;
          end );



And the fields? Perhaps you prefer this:
  TRTTIHelper.OnFieldsHaveAttribute<FieldNameAttribute,FieldLengthAttribute>(TFoo,
 Procedure (Field : TRttiField; Attr : FieldNameAttribute)
   begin
     if Field.Name = 'FField2' then
       _Result2 := Attr.Value;
   end,

 Procedure (Field : TRttiField; Attr : FieldLengthAttribute)
   begin
     if Field.Name = 'FField1' then
       _Result1 := Attr.Value;
   end);



This Helper has many overloads like TProc<T1,T2,T3> from System.Sysutils,


Stay tuned for the next update of my FDK and just write:


Uses
  Delphiprofi.FDK.RTTI;




Remember:
Rule of Thumb: „Uses is faster than self-typing“!