Guidance needed

leus · 2017-07-06 17:02:37

Hi,

I have to write a program to create a report based on data from three different data sources. I could go ahead and do the easy thing but I want to seize the opportunity to learn more about mORMot.

Some facts about the program:

The three databases are quite old, and there is little to no normalization
They all are SQL server databases, but I really don't want to limit my solution to that.
I have to get data from different tables (JOINs) but I wonder if there is a mORMotish way to do that.

Any tips? How should I create my data objects? One for each table, or there is a way to create compound objects from different tables in the database?

What other catches should I be aware of?

ab · 2017-07-06 19:11:39

The way to do it may be to use SynDB to access to the SQL server, using SQL to retrieve information, then create the report from code, using the mORMotReport.pas unit.

You may define some data objects in-between, if needed.
But it is not mandatory in a first step.
Then, if you expect the information to be available in a clear manner, yes, define some objects in-between.

Perhaps just use plain records and dynamic arrays. Do not make something complicated, but define the simple value objects possible, just holding the data values.
Make the naming match the reality, with explicit names: no abbreviation, and don't follow the SQL table field names.
Encapsulate all record/array type definitions in a single unit, with no link to SQL or whatever: they will be used for value transmission.
Then write another unit to retrieve the information from the databases, from SQL. You may identify some arrays (e.g. lookups) which may be reused by other structures: they may be used instead of a JOIN, to make the join at record/code level.
Then write the last unit taking the records as input, and generating a report (on screen or pdf) as output.

You may end up with creating some DDD-like data structure, which may be exposed as a service later on...

edwinsn · 2017-07-07 08:58:53

Since there are old databases not created by the mORMot framework, it'll make it hard to recreate mORMot's ActiveRecord objects. So if were you I'll do the following:

- Retrieve records from the source databases using TSQLTableJson;
- Use a TClientDataSet to consolidate all the 3 TSQLTableJson tables.
- Generate report from the data stored in the in-memory TClientDataSet.

ab · 2017-07-07 09:37:01

@edwinsn
I perhaps wouldn't include a TClientDataSet in the equation.
If it may work with putting every information locally, using an in-memory Sqlite3 database may be a better approach.

leus · 2017-07-12 23:10:52

ab wrote:

Perhaps just use plain records and dynamic arrays. Do not make something complicated, but define the simple value objects possible, just holding the data values.
Make the naming match the reality, with explicit names: no abbreviation, and don't follow the SQL table field names.
Encapsulate all record/array type definitions in a single unit, with no link to SQL or whatever: they will be used for value transmission.
Then write another unit to retrieve the information from the databases, from SQL. You may identify some arrays (e.g. lookups) which may be reused by other structures: they may be used instead of a JOIN, to make the join at record/code level.
Then write the last unit taking the records as input, and generating a report (on screen or pdf) as output.
You may end up with creating some DDD-like data structure, which may be exposed as a service later on...

This is what I want to do. I want to avoid SQL because I'm looking for this project as a learning opportunity (and I've been using SQL for over twenty years, so no much point to keep learning that.)

mORMot Open Source

#1 2017-07-06 17:02:37

Guidance needed

#2 2017-07-06 19:11:39

Re: Guidance needed

#3 2017-07-07 08:58:53

Re: Guidance needed

#4 2017-07-07 09:37:01

Re: Guidance needed

#5 2017-07-12 23:10:52

Re: Guidance needed

Board footer