Blog
All Blog Posts | Next Post | Previous Post
Crash Course TMS Aurelius – AnyDAC or dbExpress?
Monday, February 11, 2013
In the example provided in the previous post, we saved a TCustomer instance in a local SQLite database which was accessed natively by TMS Aurelius. Let’s refactor that code a little bit:procedure SaveCustomer(Connection: IDBconnection; CustomerName: string); var Manager: TObjectManager; Customer: TCustomer; begin Manager := TObjectManager.Create(Connection); Customer := TCustomer.Create; Customer.Name := CustomerName; Manager.Save(Customer); Manager.Free; end;
uses
{…}, Aurelius.Drivers.SQLite, Aurelius.SQL.SQLite;
Connection := TSQLiteNativeConnectionAdapter.Create('test.db');
SaveCustomer(Connection, 'Jack');
uses
{…}, Aurelius.Drivers.dbExpress, Aurelius.SQL.MySQL;
Connection := TDBExpressConnectionAdapter.Create(SQLConnection1, 'MySQL', False);
SaveCustomer(Connection, 'Joe');
To retrieve that interface, we used a component adapter (TDBExpressConnectionAdapter, declared in unit Aurelius.Drivers.dbExpress) that takes our current dbExpress connection component (a TSQLConnection named SQLConnection1) and retrieves the interface. The second parameter indicates which database we are connecting to (more specifically, which SQL dialect Aurelius needs to use to execute SQL statements). That dialect, 'MySQL', is available after you use the unit Aurelius.SQL.MySQL. Finally, the third parameter (False) indicates that when the IDBConnection interface is destroyed, the adapted component (SQLConnection1) should not be destroyed. Optionally you can set it to true, which can be useful if you are creating the component only to be used by IDBConnection, so that the component is destroyed when interface is destroyed.
Now that Embarcadero has purchased AnyDac library and it will probably be provided natively in Delphi, using it instead of dbExpress will be a matter of changing a couple of lines:
uses
{…}, Aurelius.Drivers.AnyDac, Aurelius.SQL.MySQL;
Connection := TDBExpressConnectionAdapter.Create(ADConnection1, False);
SaveCustomer(Connection, 'Phil');
Another thing is worth noting is that with this approach, code is very abstract and flexible. Aurelius doesn’t have any connection parameters that you need to configure like server name, password, etc. Everything is configured in the same components you already use. Any database connection configuration, including advanced ones, provided by each database access components, is still available.
So, if you don’t know if you should use AnyDac or dbExpress, you can use both and change them as you want to. Not only those, but at the current version (1.8) Aurelius also supports ADO components, Direct Oracle Access, ElevateDB, NexusDB, Absolute Database, FIBPlus, IBObjects, IBX components, SQL-Direct, UniDac and of course the native SQLite adapter. Aurelius documentation also provides the unit names and the name of adapter classes in its topic about component adapters.
As for the supported databases, you can use not only SQLite and MySQL, but also Firebird, MS SQL Server, Interbase, Oracle, PostgreSQL, Absolute Database, DB2, ElevateDB, NexusDB and SQLite. The names of units and SQL dialects are available in the topic “SQL Dialects” in documentation.
To conclude, I would like to mention that not only those databases and components are supported, but they are also extensively tested in each Aurelius release, with almost all possible combinations (dbExpress connecting to SQL Server, AnyDac connecting to PostgreSQL, and so on). You can check in the documentation which minimum versions of were used for tests for each combination. So most of little problems here are there with field types, SQL syntax, among other common problems that we usually find when switching components and databases are already solved, making Aurelius code effectively database/component agnostic, not only in theory, but also in practice.
Wagner Landgraf
This blog post has received 11 comments.
2. Monday, February 11, 2013 at 12:04:02 PM
Currently database update is done using TMS Data Modeler tool. Update database through TMS Aurelius is planned and will be included in version 2.0 to be released in a couple of months. Aurelius can fetch objects on demand, either using TAureliusDataset or directly using cursors so you don''t need to load the whole table before processing it. Finally, Aurelius gives you all advantage of object management, which has an overhead. We are also planning to provide a stateless manager which will give better performance for inserting a huge amount of records, but for now we consider that the great advantage of an ORM is to manage the states. It will be added eventually, but database update is certainly our main priority right now.
Wagner Landgraf
3. Monday, February 11, 2013 at 12:05:31 PM
I would also like to add that besides fetch-on-demand, Aurelius also provide paged results which gives you advantage of fetch-on-demand without having an active database connection. This will be a subject for a future post of this crash course.
Wagner Landgraf
4. Monday, February 11, 2013 at 12:47:58 PM
Hi, thank you.I am a fan of fetch-on-demand because our applications have a lot of records and, in traditiona way, performance are good also without paging or filter data in grids. I use Anydac Live Data Window feature for fetch data in "chunck"; so your on-demand feature is on my wish top list.
Disconnetted fetch is another plus, especially on remote server where are active a lot of connections.
I will try all features, sure.
Thanks,
Regards,
Stefano Monterisi
Stefano Monterisi
5. Tuesday, February 12, 2013 at 11:54:20 AM
I''m new to ORM, and I''m struggling to adapt this concept to DATASNAP. There is some client server example where I can start ?
John
6. Tuesday, February 12, 2013 at 3:08:33 PM
ORM like Aurelius is a good tool to be used together with servers, including DataSnap. Aurelius is server-framework agnostic, which means you can use DataSnap or any other server framework. Have you seen this previous blog post? http://tmssoftware.com/site/blog.asp?post=240
Wagner Landgraf
7. Monday, March 4, 2013 at 5:34:48 AM
I have "played" with Aurelius trial for test its features.On Fetch-on-demand, I have tested that it perform fetch in one direction, traditionally, from start to end of table...
But, If I want use "extensively" fetch-on-demand in our applications, I need extra power. I cannot go to end of table without read ALL records, true?
Infact, I use Anydac Live data window approach for the best result. It perform automatic quering so I can load only records necessary to fill grids or other, even I start from the end. If I need 100 records for display a grid, it load (100*2) records and I have ALWAYS only 200 records in memory, even if I start from the end...so it fetch data starting from need starting point, and not only from the first record.
There is a way for obtain this result on Aurelius?
Thanks in advance, and sorry for my english...
Stefano
Stefano Monterisi
8. Monday, March 4, 2013 at 10:14:29 AM
TMS Aurelius has also the concept of paging, which allows you to retrieve data from a query from any position, using Take and Skip (you Skip X records and then Take Y records from the query). This allows you full flexibility to retrieve part of the data at the position you want.However, when we talk about the TDataset specifically, it always retrieve the first records then the last ones. I''m not aware of such feature in Dataset that allows you to retrieve previous records of the dataset.
Wagner Landgraf
9. Tuesday, March 5, 2013 at 4:23:57 AM
Hi, thank you.But there is a big difference. Retrieve data from a query starting at any position is different that perform a query for only need records. You must consider a table with a million record, and must go to end and to top in a grid. Using Take and skip on a query that require one hour to extract all data, is very different to performa a query that require only 100 records. Please try Live data window (Anydac) so you can test what i need. I can scroll one million records table in a grid, without initial delay. I need this feature in Aurelius, for use it.
Thanks in advance,
Stefano
Stefano Monterisi
10. Tuesday, March 5, 2013 at 8:28:12 AM
Take and Skip do a server-side select that only retrieves the number of records requested. if you do Skip(100000).Take(100) it will skip the first 100 thousand records, it will not retrieve all of them - it''s done at server side, Aurelius does build a specific SQL for that.Wagner Landgraf
11. Thursday, March 7, 2013 at 3:59:10 AM
Perfect!Please insert an automatic management in Aurelius dataset (not manual code) as a feature... like ADTable does!
Thanks!
Stefano Monterisi
All Blog Posts | Next Post | Previous Post
what about DB structure evolution? There is an automatic way for update tables structure, relations, etc... when we add/modify/delete fields in Aureilus objects? I have seen only CreateDatabase...
I already use Anydac (Firebird backend), any hint for obtain best performance, especially on big tables? There are benchmark on fetching/inserting time "native" vs. Aurelius layer?
Thanks in advance,
Stefano
Stefano Monterisi