Investigating the ORM – retrieving data

In a previous post we illustrated how to use the ORM to populate a simple data store. Now, we’ll look how to retrieve the data.

If you have the ID of the record / instance you’re after, you can simply call:

  Note := TNote.Create(Client,ID);

for mORMot to fetch and populate Note.

Being an implementation of the Active Record Pattern, basic operations, such as Update, Retrieve and Delete are part of the basic implementation. TSQLRecord also provides list retrieval methods and lazy loading to all it’s descendants.

For example:

procedure TestIt(rest:TSQLRest);
var
  Note : NoteORM.TNote;
begin
  Note := TNote.CreateAndFillPrepare( rest, 'Body LIKE ?',['Sample 2%']);
  try
    while Note.FillOne do
    begin
      writeln( 'Note ID: ', Note.ID );
      writeln( 'Note Title: ', Note.Title );
      writeln( 'Note Body: ', Note.Body );
    end;
  finally
    Note.Free;
  end;
end;

will produce:

Note ID: 3
Note Title: This is the title!
Note Body: Sample 2. This is a sample note.

Investigating the ORM – persisting data

The ORM (Object Relational Mapper) that ships with mORMot implements the Active Record pattern. All objects that you want to persist must inherit from TSQLRecord, which handles a lot of the marshaling and facilitates querying and verification.

Let’s look at a simple sample:
Let’s say you have an application server that stores notes.

The very first thing we’ll do is create a unit to define our ORM classes. Let’s call this one NoteORM.pas.

unit NoteORM.pas;
interface

uses Classes, SynCommons, mORMot;

type
  TNote = class(TSQLRecord)
  private
    FBody: RawUTF8;
  published
    property Body: RawUTF8 read FBody write FBody;
  end;

implementation

end.

That’s pretty simple! Great, how do we use it?

To illustrate the usage, here’s a basic test application framework:

program TestNotes;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  {$I SynDprUses.inc}
  SysUtils,
  SynSQLite3Static,
  mORMot,
  mORMotSQLite3,
  NoteORM in 'NoteORM.pas';


procedure TestIt(rest:TSQLRest);
var
  Note : NoteORM.TNote;
begin
  Note := TNote.Create;
  Note.Body := 'Sample 1. This is a sample note.';
  rest.Add( Note, true ); 
end;

var RestServer: TSQLRestServerDB;
    RestClient: TSQLRestClientURI;
begin
  RestServer := TSQLRestServerDB.CreateWithOwnModel( 
                    [NoteORM.TNote], 'Test.DB' );
  RestClient := TSQLRestClientURIDll.Create(
                    TSQLModel.Create(RestServer.Model),
                    @URIRequest);
  try
    // initialize URIRequest() with the aStatic database
    RestServer.ExportServer;
    USEFASTMM4ALLOC := true;
    RestServer.CreateMissingTables;
    RestClient.Model.Owner := RestClient;
    TestIt(RestClient);
  finally
    RestClient.Free;
    RestServer.Free;
  end;
end.

Running the application, a new SQLite database will be created, called Test.DB. In it there will be a table called Note, with a two fields: ID and Body. A single row will exist:

ID          Body
----------  --------------------------------
1           Sample 1. This is a sample note.

Let’s extend our Note class to add a title. We’d like the title to be restricted to 80 characters.

We simply modify NoteORM.pas to look like this:

  TNote = class(TSQLRecord)
  private
    FBody: RawUTF8;
    FTitle: RawUTF8;
  published
    property Body: RawUTF8 read FBody write FBody;
    property Title: RawUTF8 index 80 read FTitle write FTitle;
  end;

Notice the “index 80” part: This tells the ORM that we would like to restrict the width of the field in the database to 80 characters.

Running the application now, will yield the following in our database:

ID          Body                              Title
----------  --------------------------------  ----------
1           Sample 1. This is a sample note.
2           Sample 1. This is a sample note.

Great! mORMot automatically added the Title column, but we forgot to add test data. Let’s do that now:

procedure TestIt(rest:TSQLRest);
var
  Note : NoteORM.TNote;
begin
  Note := TNote.Create;
  Note.Body := 'Sample 2. This is a sample note.';
  Note.Title := 'This is the title!';
  rest.Add( Note, true );
end;

Run it and it produces the following in our database:

ID          Body                              Title
----------  --------------------------------  ----------
1           Sample 1. This is a sample note.
2           Sample 1. This is a sample note.
3           Sample 2. This is a sample note.  This is the title!

That was quite easy! Next, we see how we can retrieve data using the ORM.

Some mORMot basics

mORMot’s architecture is based on a modular multi-tier system.

Puzzles

Everything you do in mORMot will follow this pattern. The server component can be embedded in the same physical application, or it can be a proxy, passing the requests and queries along to another instance, which will work with the same pattern again.