1. Linas Naginionis Avatar Linas Naginionis
  2. Untitled project
  3. Marshmallow

Wiki

Clone wiki

Marshmallow / Home

View History

Project "Marshmallow". Modern ORM/OPF framework for Delphi

SQL Server SQLite Firebird Oracle MySQL PostgreSql MongoDB

Background

Project "Marshmallow" was inspired by .NET micro ORM's (mostly by PetaPoco) and Java Hibernate. The main goal was to write simple to use but powerful Delphi ORM framework. Framework uses latest Delphi language features, including generics, attributes, enhanced RTTI, records, operator overloading, etc. This allows developers to be more productive, write cleaner code. "Marshmallow" is developed by Linas Naginionis.

Features

  • Works with attribute decorated PODO's (Plain Old Delphi Object)
  • Can create and/or update database tables from PODOs
  • Helper methods for Insert/Delete/Update/Save and IsNew
  • Paged requests automatically work out total record count and fetch a specific page.
  • Easy transactions. They are implemented as interfaces and rollback is performed when transaction goes out of scope (of course commit and rollback can be done manually).
  • Uses native query language for each database or Criteria API (similar to Hibernate)
  • Can automagically bind images to table blob fields. Engine will detect image type from stream and load it to TPicture object.
  • Currently works with SQLite, Sybase ASA, SQL Server, Firebird, Oracle, MySQL, PostgreSQL, MongoDB but other databases can be easily added due to flexible architecture.
  • Cross platform
  • Unit tested
  • Very easy logging. Multiple logging listeners can be used.
  • Uses Spring collections.
  • Updates only changed fields, not all specified columns
  • Nullable types
  • Lazy types support (lazy loading)
  • Can map Delphi enums to DB fields, streams to blobs, etc. automatically
  • Entities can be easily persisted to/from JSON/XML using TSvSerializer from open source delphi-oop library
  • Safety. Queries are automatically parameterized where it is possible (this protects from SQL Injection attacks)
  • Data binding extensions. Bind your entities to DBGrids using TObjectDataset. TObjectDataset itself has some advanced features like local record filtering, sorting, locating, etc.
  • List sessions. Unique "Marshmallow" feature which allows to sync your changes in the list with the database table.
  • Entities can be generated from database tables using our simple code generator.
  • Implements repository pattern
  • Optimistic locking can be achieved easily using [Version] attribute

Marshmallow will become part of Spring4D

Marshmallow code is already in Spring4D repository. Please support open source community.

Code examples

Very simple PODO example:

  [Entity]
  [Table('Customers')]
  TCustomer = class
  private
    [Column('CUSTID', [cpRequired, cpPrimaryKey])][AutoGenerated]
    FId: Integer;
    FName: string;
    FAge: Integer;
    FLastEdited: TDateTime;
    FEmail: string;
    FMiddleName: Nullable<string>;
  public
    property ID: Integer read FId;
    [Column] property Name: string read FName write FName;
    [Column] property Age: Integer read FAge write FAge;
    [Column] property LastEdited: TDateTime read FLastEdited write FLastEdited;
    [Column] property EMail: string read FEmail write FEmail;
    [Column] property MiddleName: Nullable<string> read FMiddleName write FMiddleName;
  end;

Start working with "Marshmallow"

Suppose we want to use SQLite3 database engine. At first we must register SQLite3 adapter by using it's unit somewhere in our project, e.g.:

uses
  Adapters.SQLite;

List of all available adapters:

  • Adapters.ADO - use with any ADO supported database
  • Adapters.ASA - use with Sybase ASA
  • Adapters.DBX - use with any DBX supported database
  • Adapters.MSSQL - use with Microsoft SQL Server
  • Adapters.SQLite - use with SQLite3 embedded database
  • Adapters.UIB - use with Firebird, Interbase
  • Adapters.MongoDB - use with MongoDB
  • Adapters.Zeos - use with any Zeos supported database
  • Adapters.Oracle - use with Oracle
  • Adapters.FireDAC - use with any FireDAC supported database

You are free to use as many adapters in your project as You want. Independant TSession instances can work with different databases.

Support for other databases can be easily added by using universal adapters (e.g. Adapters.ADO or Adapters.DBX). If database uses different query language which are not supported by "Marshmallow" directly than custom SQL generator can be easily implemented. In most cases it will need to override only a few methods.

Session

Then we need to create our IDBConnection and TSession instances:

var
  TestDB: TSQLiteDatabase;
  Connection: IDBConnection;
  Session: TSession;
begin
  TestDB := TSQLiteDatabase.Create(':memory:');
  Connection := TConnectionFactory.GetInstance(dtSQLite, TestDB);
  Session:= TSession.Create(Connection);
  //TSession is our main work class. 
  //Do something with the session...

Connection

Or we can simply get new connection instance from the json configured file or string.

var
  Connection: IDBConnection;
  Session: TSession;
begin
  Connection := TConnectionFactory.GetInstanceFromFilename(dtUIB, 'conn_Firebird.json');
  Session:= TSession.Create(Connection);
  //Do something with the session...

Filename should be valid json file which specifies connection's qualified class name and it's properties. Our conn_Firebird.json can look like this:

{
    "uib.TUIBDataBase": {
        "UserName": "SYSDBA",
        "PassWord": "masterkey",
        "DatabaseName": "localhost:D:\\DB\\GDB\\TEST.GDB"
    }
}

Repositories

Repositories are a simple way to work with a single type of entities.

FCustomerRepository := TMongoDBRepository<TCustomer, Integer>.Create(FSession);
LCustomers := FCustomerRepository.FindAll;

Marshmallow can even implement your repository methods automatically! All you need is to extend your interface from IPagedRepository<T, TID> and create it with TProxyRepository, e.g.:

type
  ICustomerRepository = interface(IPagedRepository<TCustomer, Integer>)
    ['{955BF130-3E2F-45E2-A9E9-79647CA3F33B}']

    [Query('SELECT * FROM CUSTOMERS WHERE CUSTNAME = :0')] //Query for the SQL database
    [Query('{"CustName": ?0}')] //Query for the MongoDB database 
    function FindByName(const AName: string): TCustomer;

    [Query('SELECT * FROM CUSTOMERS WHERE CUSTNAME = :0')]
    function FindByNamePaged(const AName: string; APage: Integer; APageSize: Integer): IDBPage<TCustomer>;//paged methods
  end;
//---
var
  FCustomerRepository: ICustomerRepository;
//----
FCustomerRepository := TProxyRepository<TCustomer, Integer>.Create(FSession, TypeInfo(ICustomerRepository)) as ICustomerRepository;
//---
LCustomer := FCustomerRepository.FindByName('Foo');
CheckEquals('Foo', LCustomer.Name);

Note that Marshmallow automatically checks your method's return type and returns one entity, list of entities or IDBPage<T> interface.

Session Methods:

Get a single record:

var
  LCustomer: TCustomer;
begin
  LCustomer := Session.SingleOrDefault<TCustomer>('SELECT * FROM CUSTOMERS WHERE CUSTID=:0', [1]);

Or without writing any SQL:

var
  LCustomer: TCustomer;
begin
  LCustomer := Session.FindOne<TCustomer>(1);

Get list of customers:

var
  LCustomers: IList<TCustomer>;
begin
  LCustomers := Session.GetList<TCustomer>('SELECT * FROM CUSTOMERS;', []);

Or without writing any SQL:

var
  LCustomers: IList<TCustomer>;
begin
  LCustomers:= Session.FindAll<TCustomer>();

Paged fetches

var
  LPage: IDBPage<TCustomer>;
  LFirstCustomer: TCustomer;
begin
  LPage := Session.Page<TCustomer>(1, 10, 'SELECT * FROM CUSTOMERS;', []);
  LFirstCustomer := LPage.Items.First;

Or without writing any SQL:

var
  LPage: IDBPage<TCustomer>;
  LFirstCustomer: TCustomer;
begin
  LPage := Session.Page<TCustomer>(1,10);
  LFirstCustomer := LPage.Items.First;

Behind the scenes, "Marshmallow" does the following:

  • Synthesizes and executes a query to retrieve the total number of matching records
  • Modifies your original query to request just a subset of the entire record set You now have everything to display a page of data and a pager control all wrapped up in one handy little interface!

Non-query Commands

To execute non-query commands, use the Execute method

Session.Execute('INSERT INTO CUSTOMERS SELECT * FROM CUSTOMERS;', []);

To get a single value, use ExecuteScalar method:

var
  LResult: Integer;
begin
  LResult := Session.ExecuteScalar<Integer>('SELECT COUNT(*) FROM CUSTOMERS', []);

Inserts, Updates and Deletes

var
  LCustomer: TCustomer;
begin
  LCustomer := TCustomer.Create;
  try
    LCustomer.Name := 'Insert test';
    //most of the time you should use save, which will automatically insert or update your PODO based on it's state
    Session.Save(LCustomer);

    //explicitly inserts customer into the database
    Session.Insert(LCustomer);

    LCustomer.Name := 'Update customer name';
    //explicitly updates customer's name in the database
    Session.Update(LCustomer); 
    
    //deletes customer from the database
    Session.Delete(LCustomer);
  finally
    LCustomer.Free;
  end;

Transactions

Transactions are very simple to use. Remember that if you didn't commit your started transaction, rollback will be executed when interface goes out of scope and is freed.

var
  LTran: IDBTransaction;
begin
  LTran := Session.BeginTransaction;
  //commit
  LTran.Commit;
  //explicit rollback
  LTran.Rollback;

Nullable types

"Marshmallow" supports declaring any type as Nullable<T> type. Core.Types unit contains Nullable<T> implementation. Spring Nullables are supported as well. E.g., if our PODO has property declared as:

[Column]
property MiddleName: Nullable<string> read FMiddleName write FMiddleName;

Then we can check if it's value is null:

var
  LCustomer: TCustomer;
begin
  LCustomer := Session.FindOne<TCustomer>(1);
  try
    if not LCustomer.MiddleName.IsNull then
    begin
      //do something with the value
      WriteLn(LCustomer.MiddleName);
    end;
  finally
    LCustomer.Free;
  end;
end;

Lazy loading

var
  LCustomer: TCustomer;
begin
  //add 2 customers orders to the table
  InsertCustomerOrder(LCustomer.ID, 10, 5, 100.59);
  InsertCustomerOrder(LCustomer.ID, 20, 15, 150.59);
  //get customer
  LCustomer := Session.SingleOrDefault<TCustomer>('SELECT * FROM ' + TBL_PEOPLE, []);
  try  
    CheckEquals(2, LCustomer.Orders.Count); //LCustomer.Orders fetches database data only now
    CheckEquals(LCustomer.ID, LCustomer.Orders.First.Customer_ID);
    CheckEquals(10, LCustomer.Orders.First.Customer_Payment_Method_Id);
    CheckEquals(5, LCustomer.Orders.First.Order_Status_Code);
    CheckEquals(LCustomer.ID, Orders.OrdersIntf.Last.Customer_ID);
    CheckEquals(20, LCustomer.Orders.Last.Customer_Payment_Method_Id);
    CheckEquals(15, LCustomer.Orders.Last.Order_Status_Code);
  finally
    LCustomer.Free;
  end;

Criteria API

"Marshmallow" exposes it's own Criteria API which helps to build queries in more abstract way. For example, to get customers named Foo and aged at 30, also ordered desc by age, you can write your criteria like this:

var
  LCustomers: IList<TCustomer>;
begin
  FCriteria := Session.CreateCriteria<TCustomer>;
  LCustomers := FCriteria
    .Add(TRestrictions.Eq('Name', 'Foo'))
    .Add(TRestrictions.Eq('Age', 30))
    .AddOrder(TOrder.Desc('Age')).List;
...

Or use IProperties interface which makes it even more readable:

var
  LCustomers: IList<TCustomer>;
  Age, Name: IProperty;
begin
  Age := TProperty.ForName('Age');
  Name := TProperty.ForName('Name');
  FCriteria := Session.CreateCriteria<TCustomer>;
  LCustomers := FCriteria
    .Add(Name.Eq('Foo'))
    .Add(Age.Eq(30))
    .AddOrder(Age.Desc).List;

More complex example:

Age := TProperty.ForName('Age');
  //WHERE ((A.AGE =:AGE1 OR A.AGE = :AGE2) OR A.AGE >=:AGE3)
  //ORDER BY A.AGE DESC
  LCustomers := FCriteria.Add(TRestrictions.Or(TRestrictions.Or(Age.Eq(42), Age.Eq(50)), Age.GEq(10)))
    .AddOrder(Age.Desc)
    .List;

Conjunctions and disjunctions are also supported:

  Age := TProperty.ForName('Age');
  Name := TProperty.ForName('Name');
  InsertCustomer(42, 'Foo');
  InsertCustomer(50, 'Bar');

  LCustomers := FCriteria.Add(
    TRestrictions
      .Disjunction()
        .Add(Age.Eq(42))
        .Add(Name.Eq('Foo'))
        .Add(Age.Eq(50)))
    .AddOrder(Age.Desc)
    .List;

If your main entity contains other sub-entities and they are connected with ManyToOne relation, then it is even possible to add a criterion from the properties of these subentities. E.g.:

procedure TestTCriteria.Add_SubEntity_Criterion;
var
  LOrders: IList<TCustomer_Orders>;
  Age: IProperty;
  LCriteria: ICriteria<TCustomer_Orders>;
begin
  LCriteria := FSession.CreateCriteria<TCustomer_Orders>; //we want to fetch TCustomer_Orders
  //TCustomer_Orders has property Customer: TCustomer
  Age := TProperty<TCustomer>.ForName('Age'); //property from sub-entity

  LOrders := LCriteria.Add( Age.Eq(1) ) //but we want to filter by a Customer's age, because order doesn't have such property
    .List();
end;

Optimistic locking

Optimistic locking can be easily implemented using [Version] attribute.

property [Version] Version: Integer

If someone tries to update entity which has already changed, EORMOptimisticLockException will be raised.

SQL Command Tracking

To log executed statement you'll need to add execution listeners:

FConnection.AddExecutionListener(
    procedure(const ACommand: string; const AParams: IList<TDBParam>)
    begin
      //write statement
    end);

List Sessions

While working with ORM framework You would mostly deal with lists of your entities. "Marshmallow" TSession allows You to insert or update your list models based on their state using SaveList method. But what about the items which were fetched from the database and later were removed from the list? One option is to attach change listener to your list and delete your entity models from the database when the change occurs. This method is valid but it is not very convenient. To overcome this issue List Sessions were introduced to "Marshmallow". Some basic example how to work with List Sessions:

var
  LCustomers: IList<TCustomer>;
  LListSession: IListSession<TCustomer>;
begin
  LCustomers := Session.FindAll<TCustomer>; //fetch customers from the database
  LListSession := Session.BeginListSession<TCustomer>(LCustomers); //start list session for LCustomers
  //do something with LCustomers
  LCustomers.Delete(0); //delete
  LCustomers.Add(TCustomer.Create); //add new customer
  LCustomers[4].Name := 'Edited Name'; //edit customer

  LListSession.CommitListSession; //sends all changes made in LCustomers to the database table
  //List Session will add one new customer, update customers name and delete first customer from the database table
end;

Support

Google+ community page

Contact using this email.

Documentation contents:

Updated