Code integration / ORM

ab · 2010-06-21 16:17:52

The programmer has to define its database table properties as Delphi classes, descendant from the TSQLRecord class. All published properties of these classes are then stored and retrieved from the SQLite3 database engine, by the framework. For most common usage, the coder doesn't have to write SQL queries: they are all created on the fly by the Object-relational mapping (ORM) framework.
For example, a People Table is defined in Delphi code as followed:

/// table used for the Babies queries
TSQLPeople = class(TSQLRecord)
  private
    fName: RawUTF8;
    fAddress: RawUTF8;
    fBirthDate: TDateTime;
  published
    property Name: RawUTF8 read fName write fName;
    property Address: RawUTF8 read fAddress write fAddress;
    property BirthDate: TDateTime read fBirthDate write fBirthDate;
end;

By adding this TSQLPeople class to a TSQLModel instance, common for both Client and Server, the corresponding People table is created by the Framework in the SQLite3 database engine. All SQL work ('CREATE...') is done by the framework. Just code in Pascal, and all is done for you. You won't miss any ' or ; in your SQL query any more.

To access a particular record, the following code can be used:

var People: TSQLPeople;
  ID: integer;
begin
  // create a new record, since Smith, Jr was just born
  People := TSQLPeople.Create;
  try
    People.Name := 'Smith';
    People.Address := 'New York City';
    People.BirthDate := Now;
    ID := Client.Add(People);
  finally
    People.Free;
  end;
  // retrieve record data
  People := TSQLPeople.Create(Client,ID);
  try
    assert(People.Name='Smith');
  finally
    People.Free;
  end;
end;

This framework also handles directly the creation of Ribbon-like interfaces, with full data view and navigation as visual Grids. The whole User Interface is designed in code, by some constant definitions. See below the RTTI usage paragraph.

Since the framework is truly object oriented, another database engine could be used instead of the SQLite3 framework. You could easily write your own TSQLRestServer descendent (as an example, we included a fast in-memory database engine) and link to a another engine (like FireBird, or a private one). You can use our framework without any link to the SQLite3 engine itself, by using our provided very fast in memory dataset (which can be made persistent by writing and reading JSON files on disk). The SQLite3 engine is implemented in a separate unit, SQLite3.pas, and the main unit of the framework is SQLite3Commons.pas.

ab · 2010-06-21 16:22:26

Albert Drent, on the Embarcadero forum wrote:

What's the advantage above the usage of SQL?

with MyQuery do begin
   sql.text := 'insert into people values ("Smith","New York City","2008-01-01")';
   execSQL;
end;

You don't have to worry about field orders, and can use field completion in the IDE. It's much more convenient to type People. then select the Name property, and access to its value.

The ORM code is much more readable than the SQL. You don't have to switch your mind from one syntax to another, in your code. You can even forget about the SQL itself for most projects; only some performance-related or complex queries should be written in SQL, but you will avoid it most of the time. Think object pascal. And happy coding. Your software architecture will thank you for it.

Another good impact is the naming consistency. For example, what about if you want to rename your table? Just change the class definition, and your IDE will do all refactoring for you, without any risk of missing an hidden SQL statement anywhere. Do you want to rename or delete a field? Change the class definition, and the Delphi compiler will let you know all places where this property were used.

Another risk-related improvement is about the strong type checking, included into the Delphi language during compile time, and only during execution time for the SQL. You will avoid most runtime exceptions for your database access: your clients will thank you for that. In one word, forget about field typing mismatch or wrong type assignment in your database tables. Strong typing is great in such cases for code SQA, and if you worked with some scripting languages (like python or ruby), you should have wished to have this feature in your project!

It's worth noting that our framework allows writing triggers and stored procedures (or like-stored procedures) in Delphi code, and can create key indexing and perform foreign key checking in class definition.

Another interesting feature is the enhanced Grid component supplied with this framework, and the AJAX-ready orientation, by using natively JSON flows for client/server data streaming. The REST protocol can be used in most application, since the framework provide you with an easy to use "Refresh" and caching mechanism. You can even work offline, with a local database replication of the remote data.

For Client/Server aspect, you don't have to open a connection to the database, just create an instance of a TSQLRestClient object (with the communication layer you want to use: direct access, named pipe or http), and use it as any normal Delphi object. All the SQL coding or communication and error handling will be done by the framework. The same code can be used in the Client or Server side: the parent TSQLRest object is available in both side, and its properties and methods are strong enough to access the data.

ab · 2010-11-24 07:42:52

Another good point of our ORM implementation, is the ability to handle high-level Delphi types.

For instance, enumerations are handled natively by the framework.
Any published property of an enumeration type will be converted to an INT field in the database. The conversion will be made by the ORM engine, and you'll only use Delphi explicit enumeration values.

Recently, we added set handling.
That is, any published property with an enumeration set (with up to 64 elements) will be converted to an INT field in the database.

The User Interface itself can be created on the fly, via the SQLite3UIEdit unit:
- the window content is taken from the RTTI of the supplied record; all the User Interface (fields, etc...) is created from the class definition using RTTI: published properties are displayed as editing components;
- visual components creation is fully customizable by some event;
- hints can be displayed before any field edition, in order to guide the user;
- i18n is handled natively;
- text or numerical fields are edited as such;
- TSQLRecord properties are selected via an automatically populated comboboxes (to select the record to point on);
- sets are displayed as a group of checkboxes;
- booleans are displayed as checkboxes;
- UTF-8 encoding/decoding is handled for all versions of Delphi - from Delphi 6 up to XE.

andrewdynamo · 2010-11-29 07:34:38

Do you also have a generator for the data classes?

My current customer has a so called "CRUD generator" which generates all data object from the database (via metadata).
Very convenient if you have a lot of tables and if your DB changes a lot (just "re-generate all" and your model is updated with the latest DB version). Much better then "FieldByName" ;-).

I have just refactored the current implementation (with attributes for metadata and generics), but I will take a look at this implementation too (we use MS SQL server).

ab · 2010-11-29 07:58:08

I didn't implement any reverse-generator from DB metadata into classes yet.
Because I didn't need it yet... and I guess it won't fit perfectly our ORM design.

For instance, our ORM handles high-level Delphi types, like enumerators and sets, using the Delphi-style of conversion to ordinal values for efficiency. This conversion style won't probably fit the way data is stored in the database (you can have enumerators coded as huge numerical values e.g. like 823450000, 823460000 and such).
Another example is that our ORM also handles a very rich reference type, named TRecordReference, which is able to design any record type of the current database model. I.e. only one given table type, but any record of any table of the database model. This is very powerful, and not handled natively by most databases.

So reverse-generation would lead into:
- having database column types mapped into less rich Delphi types (double instead of enumerates e.g.);
- loss of some very handy features of the framework.

Of course, there is no ideal world, and it could make sense to have some basic reverse-generator, which in all cases will save a lot of time.

In a current project I'm maintaining, based on a lot of Oracle tables, I'll certainly make such a generator. But no generator would fit all needs, all existing DB design. Stay tuned!

ab · 2011-02-09 07:28:07

@jailu: From my experiment, it's exactly the contrary, with an ORM.

You define tables only once, in the code, as regular classes, then the ORM creates the SQL from the class definition.

But you can use the SQLite3 unit without any ORM approach, with standard SQL statements, using TSQLDataBase and TSQLRequest.

Antonio · 2013-03-27 13:03:21

Hi,

Where is this client in line?

ID: = Client.Add (People);

In delphi XE2 gives error!

thanks

ab · 2013-03-27 14:09:38

Antonio wrote:

Where is this client in line?
In delphi XE2 gives error!

It is a bit vague as a description.

Ensure you are working with the latest 1.18 unstable version of the framework.
See the ReadMe.txt, and http://synopse.info/fossil/wiki?name=Get+the+source

Antonio · 2013-03-27 14:23:19

Sorry,

my english is very poor, so I summarize.

Follow the error:

  People := TSQLPeople.Create;
  try
    People.Name := 'Smith';
    People.Address := 'New York City';
    People.BirthDate := Now;
    ID := Client.Add(People);
  finally
    People.Free;

Checking project dependencies...
Compiling PrjCad.dproj (Debug, Win32)
[DCC Error] Ucad.pas(157): E2003 Undeclared identifier: 'Client'
[DCC Error] Ucad.pas(68): E2065 Unsatisfied forward or external declaration: 'TMainForm.Execute'
[DCC Fatal Error] PrjCad.dpr(5): F2063 Could not compile used unit 'Ucad.pas'
Failed
Elapsed time: 00:00:00.2

Yes, i'am using latest 1.18 unstable version of the framework

/// Virtual Tables for external DB access for mORMot
// - this unit is a part of the freeware Synopse mORMot framework,
// licensed under a MPL/GPL/LGPL tri-license; version 1.18

Thanks

Antonio · 2013-03-27 14:40:37

I have a system written in Delphi with Oracle and need to add new functionality, web access, access via smartphone, so I decided to re-write a new approach, using ORM and SOA.

There are no examples of CRUD on an entity table with oracle.

I managed to connect to the server, passing objects, but to make a CRUD a table yet.

Does anyone have a simple example to add, change and delete a registry basics, using mORMot?

thank you

ab · 2013-03-27 15:13:42

Ensure you have read the Client/Server part of the SAD pdf, in revision 1.18.

In short, you need to instantiate one of the TSQLRestClientURI inherited class, depending on the communication protocol used.

See also the "Sample" sub-folder.
For instance, "04 - HTTP Client-Server" or "14 - Interface based services" directories.

Antonio · 2013-03-27 15:58:30

I read all the "Framework Synopse mORMot SAD 1.18", compiled and tested all examples, however, do not have exactly what I want.

Actually, you're right, after reading all that I read, I am able to build what I want, but I wanted something done, like copy and paste ... hehehe

The examples are not in Oracle, so it's a little different.

I'm out of time ...

I'll use a server Oracle database with http protocol and a thin client written in delphi.

I appreciate your prompt service, I am now beginning to develop with this framework, soon I hope to contribute to the community.

hugs

ab · 2013-03-27 16:02:22

Take a look at the Project14ServerExternal.dpr code, and also the unit PerfMain, in "15 - External DB performance".

In "15 - External DB performance", you can benchmark Oracle access, which is pretty good.
See http://blog.synopse.info/post/2013/01/2 … provements - in short, you can insert more than 70,000 records per second, and retrieve 90,000 records per second in a remote Oracle server.

CoMPi · 2013-03-27 16:10:00

Hi,
Let say I have a table

/// table used for the Babies queries
TSQLPeople = class(TSQLRecord)
  private
    fName: RawUTF8;
    fCreatedOn: TCreateTime;
  published
    property Name: RawUTF8 read fName write fName;
    property CreatedOn: TCreateTime read fCreatedOn write fCreatedOn;
end;

and I want to build a tree where root nodes will represents dates where some people were added into the DB. Typicaly I could get this information using "SELECT DISTINCT CreatedOn FROM People". So how to get this in mORMot framework?

Thanks

ab · 2013-03-27 16:30:29

You can execute any SQL statement by hand, on the server side.

But to handle a DISTINCT command, you can not use directly ORM.
In fact, only aggregation statement handled by now is COUNT(*), via the TableRowCount() method.

You can e.g. execute directly SQL statements on the server side, then return the data with an interface-based service.
It will be the faster approach.

Antonio · 2013-03-27 16:42:06

When I run "15 - External DB performance" I got the following msg:

"First chance exception at $7645C41F. Exception class ESQLDBOracle with message 'ORA-01722: nÃºmero invÃ¡lido'. Process PerfTest.exe (5028)"

When I run "16 - Execute SQL via services", I used a "SELECT * FROM TABLE", and TABLE contained a numeric field with a comma, the grid shifted one column.

thanks

ab · 2013-03-27 17:50:22

Numeric fields should be retrieved with '.'.

Perhaps the Oracle connection shall be forced to use '.'.
Could you try to change NLS_LANG?

Antonio · 2013-03-27 19:14:22

Oracle 11g XE has changed the way it treats the NLS_LANG, I had several problems with Delphi applications (Note that I only use Delphi applications).

My language is BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252

Then I tried with BRAZILIAN PORTUGUESE_BRAZIL.AL32UTF8 (This is the language of my oracle server) and repeated the error invalid number

And finally with american_america.AL32UTF8 and the result was

First chance exception at $ 7645C41F. Exception class EODBCException with message
'[IM002] [Microsoft] [ODBC Driver Manager] Data source name not found and no default driver specified (0)'.
PerfTest.exe Process (5824)

You thought of some other language?

ab · 2013-03-27 19:18:24

Are you using ODBC?

Direct SynDBOracle connection is much preferred.

But I suspect the same problem will occur.

Antonio · 2013-03-27 19:30:26

Well, I think I'm not using ODBC, now I'm confused, I have the client installed and when I run the program, i use the parameters of the client.

You're right, but do not know how to configure the program to not use ODBC.

Last edited by Antonio (2013-03-27 20:06:45)

ab · 2013-03-28 06:03:12

Includes the SynDBOracle unit in your uses clauses, and create a TSQLDBOracleConnectionProperties instance to specify the external connection.

Antonio · 2013-03-28 14:25:58

Yes, I know, but I refer to the program perMain, he has the two uses and makes two calls, however, I don't know when his use one or the other, would have to analyze the logic of it.

I'm trying to create a server that connects to oracle and publish classes by http

ab · 2013-03-28 15:24:31

Antonio wrote:

I'm trying to create a server that connects to oracle and publish classes by http

... that is, remote ORM access on TSQLRecord classes, with Oracle as storage?

By the way, it is always better to create a new thread in our forum, when you have questions not directly involving the first topic of a thread, like this one.
See http://synopse.info/forum/misc.php?action=rules

mpv · 2013-07-20 14:08:59

I'm little confused with ID field in ORM. Actually on database level mORMot create INT64 field type, but in source used integer. I remember this problem is already discussed in this forum, but not found topic. I once again try to move my soft from Syn* level to mORMot* level and this is big blocker for me (I use one database-wide ID generator for all table with first 4 digit reserved for clientID (so I got something like GUID) for easy replication between different database. So I easy overflow int32 ID in real application. Is it possible (may be in future) to change ID from int32 to Int64 ?

ab · 2013-07-20 17:31:03

The problem is that for cross-reference fields, we use the TSQLRecord class field to identify the target table.
Then the ORM does store the ID within the instance address... I mean the 32 pointer...
So passing the ID to Int64 would be possible only on Win64 with this scheme.

We use a similar mapping for the RecordReference kind of field: some bits are reserved to point to the database model.
That is, a single 32 bit integer is used to identify any table and ID of the whole model.

So changing the paradigm will impact changing both the ORM core and the way external fields are declared at ORM level.
Not a simple choice.

ab · 2014-08-20 11:13:53

Note:

I "unstick" this topic, since it evolved from a general high-level introduction to ORM ("code first" pattern) into some specific support.
Official SAD pdf is much more exhaustive and accurate, now.

mORMot Open Source

#1 2010-06-21 16:17:52

Code integration / ORM

#2 2010-06-21 16:22:26

Re: Code integration / ORM

#3 2010-11-24 07:42:52

Re: Code integration / ORM

#4 2010-11-29 07:34:38

Re: Code integration / ORM

#5 2010-11-29 07:58:08

Re: Code integration / ORM

#6 2011-02-09 07:28:07

Re: Code integration / ORM

#7 2013-03-27 13:03:21

Re: Code integration / ORM

#8 2013-03-27 14:09:38

Re: Code integration / ORM

#9 2013-03-27 14:23:19

Re: Code integration / ORM

#10 2013-03-27 14:40:37

Re: Code integration / ORM

#11 2013-03-27 15:13:42

Re: Code integration / ORM

#12 2013-03-27 15:58:30

Re: Code integration / ORM

#13 2013-03-27 16:02:22

Re: Code integration / ORM

#14 2013-03-27 16:10:00

Re: Code integration / ORM

#15 2013-03-27 16:30:29

Re: Code integration / ORM

#16 2013-03-27 16:42:06

Re: Code integration / ORM

#17 2013-03-27 17:50:22

Re: Code integration / ORM

#18 2013-03-27 19:14:22

Re: Code integration / ORM

#19 2013-03-27 19:18:24

Re: Code integration / ORM

#20 2013-03-27 19:30:26

Re: Code integration / ORM

#21 2013-03-28 06:03:12

Re: Code integration / ORM

#22 2013-03-28 14:25:58

Re: Code integration / ORM

#23 2013-03-28 15:24:31

Re: Code integration / ORM

#24 2013-07-20 14:08:59

Re: Code integration / ORM

#25 2013-07-20 17:31:03

Re: Code integration / ORM

#26 2014-08-20 11:13:53

Re: Code integration / ORM

Board footer