Demo application + some question / remarks.

migajek · 2010-11-30 16:56:55

Hi,
I decided to build a very trivial yet closer to real life demo of SQLite3 Framework. The main reason behind this decision was to learn the Framework a bit
Currently the application is just a list of Customers with Tasks assigned to them (the relation type is "HAS MANY AND BELONGS TO MANY"). In my case, TCustomer publishes TTasks list.

I've also decided to share the code in hope that if anybody knows the better way for solving any of the "tasks", he might edit & share.
The code is available at http://code.google.com/p/synopse-sqlite-demo/

Now, during the development I had first contact with relations implementation. Probably some of the remarks are caused by mine poor knowledge of framework, in that case I hope you'll correct me and point the proper way

1. is that really necessary to specify SourceID each time I access "Dests" list?

 cust.Tasks.ManyAdd(Database, cust.ID, task.ID, true)

if Tasks (the TSQLRecordMany) is created & owned by TCustomer (TSQLRecord), why can't it access the owner's ID by itself? It would be more consistent, since the manner is following: SomeObject->ListOfItsRelatives->AddRelative
The same applies to others Many* methods.
Of course, keeping the overloaded versions which takes SourceID argument is a very good idea.

2. Let's say I have a Task and want to retrieve all it's clients. Currently I'm doing it by:
* creating TCustomer instance
* loading list of "Source" id's by specifying task.id as DestID.
* iterating the id's one-by-one in "for" loop

First of all, why can't I create TTasks directly? Actually, I can -> but since it doesn't have an owner, calling SourceGet fails (IDWhereSQL returns empty string). That way it doesn't seem consistent at first look...
Moreover, I'd love to see FillSource method - equivalent of FillMany

3. why doesn't TSQLRecordMany publish "Source" and "Dest" fields? If they are declared in TSQLRecordMany, creating own "pivot" record becomes as simple as

type TTasks = class(TSQLRecordMany; 
published
property Dest;
property Source;

I believe in 99% of cases, user-declared code will call fDest and fSource respectively anyway.

ab · 2010-12-01 07:45:28

1. Good remark about the SourceID parameter!

I kept the current method version (how knows if it could not be handy to make an association for another Source ID? - that's exactly what is done in the test regression test TTestSQLite3Engine. _TSQLRestClientDB / TestMany).
But I've added an overloaded function, which will use the current "cust" ID, stored in a protected fSourceID field in TSQLRecord.Create, as Source ID.

You can now write:

cust.Tasks.ManyAdd(Database, task.ID, true);

And I've also made some overloaded function, without any Source ID parameter, for ManyDelete/ManySelect

2. Another good idea.

I've added this constructor:

constructor TSQLRecordMany.Create(aSourceClass: TSQLRecordClass; aSourceID: integer);

So that you could be able to create TTasks directly.

I've also added this method, to have a Task to retrieve all its client:

function TSQLRecordMany.FillManyFromDest(aClient: TSQLRest; aDestID: integer): integer;

3. I've created the properties, as stated by this code:

    /// the reference to a Source record
    // - will be filled via ManySelect/FillMany/FillManyFromDest methods
    // - will be filled automaticaly when a ManyAdd method is called
    property Source: TRecordReference read fSource;
    /// the reference to a Destination record
    // - will be filled via ManySelect/FillMany/FillManyFromDest methods
    // - will be filled automaticaly when a ManyAdd method is called
    property Dest: TRecordReference read fDest;

Thanks for your great remarks and proposal!!!!

migajek · 2010-12-01 10:13:16

Hi,
I'm glad you like the demo and the ideas I had Thank you for implementing them!

one more remark / question / warning for ManyAdd.

Let's say I want to add some related to the newly created record, and I do so before I save the source record.
Pseudo coude:
n = NewRecord;
n.ManyAdd(task1);
n.ManyAdd(task2);
Database.Add(n);

I'm pretty sure this will cause "pivot" records to contain invalid source IDs, since they were assigned before the NewRecord had an ID assigned?

If so, how about solving it by keeping list of "dest" ids to be added when saved? Like that:
pseudocode:

ManyAdd();
begin

if fSourceId <= 0 then
fDestIDs.Add(fDestID);
else
// ... do the standard operations
end;

now when TSQLRecordMany is notified that the "owner" record has been just saved, assign the fSourceID and iterate fDestsIDs list adding each ID by standard ManyAdd?

Last edited by migajek (2010-12-01 10:13:41)

ab · 2010-12-01 12:34:39

migajek wrote:

Hi,
n = NewRecord;
n.ManyAdd(task1);
n.ManyAdd(task2);
Database.Add(n);
I'm pretty sure this will cause "pivot" records to contain invalid source IDs, since they were assigned before the NewRecord had an ID assigned?

IMHO MayAdd will fail to work, because both n.ID=0 and n.fSourceID=0.

That's why it's very important to check for the success of methods:

n = NewRecord;
if not n.ManyAdd(task1) then
  // handle error

migajek wrote:

now when TSQLRecordMany is notified that the "owner" record has been just saved, assign the fSourceID and iterate fDestsIDs list adding each ID by standard ManyAdd?

In such case, you'll have to use the overloaded methods with a Source ID parameter.

Does it make sense to you?
IMHO such an implementation is enough.
I've added some comments to warn about such problems.

migajek · 2010-12-01 13:37:40

ab wrote:

IMHO MayAdd will fail to work, because both n.ID=0 and n.fSourceID=0.

that's what I meant

In such case, you'll have to use the overloaded methods with a Source ID parameter.

What I thought of was a list of "not-yet-saved" related DestID's -> filled with data in case ManyAdd is called when fSourceID = 0.

"if user adds some elements to related list, but I'm not saved (so I dont have ID), keep the list of related IDs and add them to pivot table as soon as I'm assigned with ID (I'm saved for the first time)"

I've added some comments to warn about such problems.

that is definitely very important in current situation, since there's no need to pass SourceID, one may not be aware that he shouldn't add related until the source object is saved.

migajek · 2010-12-01 21:46:44

Hi,
while working on the user access control functionality for the demo app (see latest commit on google code) I have one more idea.
to iterate record list, I need to do following
* obtain & store result of MultiFieldValues
* call FillPrepare
* while FillOne do ...
* free result of MultiFieldValues

now, how about one or two more overloaded constructors, first would take only one argument, TSQLRest. It would do all the above "preparing" operations, but take care of freeing result as well. That way user will end up with the code:

var 
 rec: TRecord;
begin
 rec:= TRecord.Create(Database);
 while rec.FillOne do
 /.. /
 rec.free;
end;

I find it much more convenient, since it is quite commonly used feature (actually the idea is copied from Yii [PHP framework] ORM)

The second idea (of second constructor) would be similar, except that instead of selecting all the records, one would specify the "WHERE" clause (which records to select).

And, one more question ... could you be please so kind to do quick review of the demo, mostly uCustomer, to see what design mistakes I did? So far I have had no experience with serious "native" application databasing (in contrary to PHP based development). The approach of Synopse SQLite FW is significantly different in some parts comparing to those PHP-based ORMs I know, thus I'd like to follow your design the very best way.

ab · 2010-12-02 08:46:03

Another very good proposal!

So I've just added two new methods:

constructor TSQLRecord.CreateAndFillPrepare(aClient: TSQLRest;
  const aSQLWhere: RawUTF8);
var aTable: TSQLTable;
begin
  Create;
  aTable := aClient.InternalListRecordsJSON(RecordClass,aSQLWhere);
  if aTable=nil then
    exit;
  aTable.OwnerMustFree := true;
  FillPrepare(aTable);
end;

constructor TSQLRecord.CreateAndFillPrepare(aClient: TSQLRest;
  FormatSQLWhere: PUTF8Char; const ParamsSQLWhere: array of const);
begin
  CreateAndFillPrepare(aClient,FormatUTF8(FormatSQLWhere,ParamsSQLWhere));
end;

I've changed the constructor name from plain "Create", because it will do more than create one TSQLRecord instance.

Since the TSQLTable must be freed by TSQLRecord.Create, I changed the way such a table can be owned by a record. Such a table must be own in case of TSQLRecordMany.FillMany() method call, so I've done some code refactoring, and added a new TSQLTable.OwnerMustFree property.
A new TSQLRecord.FillClose method has been added.

I've also corrected some memory dual free, if FTS3 is used. Now our SynScaleMM works well with the test code.

I'll take a look at your sample application source.

migajek · 2010-12-02 12:03:55

Thank you

I'll take a look at your sample application source.

I can't wait to know what I'm doing wrong

What bothers me now ... why doesn't TSQLRecord keep it's TSQLRest object once passed in constructor? Have a look at TUser.HasRole implementation ... I need to pass the connection reference all the time. I know I could use global variable, but ... why?

ab · 2010-12-02 15:53:56

Some details points:

Project1.dpr
- you can use {$I SynDprUses.inc} as first line of your uses clause: it will load FastMM4 if it's needed by the Delphi compiler version;

UCustomer.pas
- TUserRole.CreateStandardRoles: names: array [0..1] of RawUTF8
- the FillMany() replaces a SQL statement in pure ORM approach. Well done.
But since this kind of requests could be made very often, I've added a new TSQLRecordMany.DestGetJoined() method, which will use a tuned SQL statement, therefore will highly reduce the bandwidth between Client and Server.
If you want to loop through the content of the returned records, I've added a CreateAndFillPrepare() overloaded method and a FillPrepare() method, which accept aIDs: TIntegerDynArray as parameters.

In uQueryHistory, you could get rid of the Destroy method and the fData field, and use a stack-based aData: TSQLTable, with aData.OwnerMustFree := true; FillPrepare(aData);

In Unit1.pas:
- you could have defined Database: TSQLRestClient instead of Database: TSQLRestClientDB, in order to change later into another TSQLRestClient class type, if your application needs to be Client/Server
- TForm1.FillCustomersList could be simplified by using the new cust := TCustomer.CreateAndFillPrepare(Database,'');
- same for FillTasksList or FillRolesList or FillUsersList
- you directly pass RawUTF8 strings into VCL string values (like Caption or InputBox): it could be better to use UTF8ToString() and StringToUTF8() for non English characters (see also SQlite3i18n unit);

It could be a good practice to have your UI not linked to the data.
I.e. using a separate Unit, with no VCL UI units in the uses clauses, which will hold the database, the TSQLRecord classes, and such.
These data-related units could be shared between a future Client and Server. For instance, the TSQLModel must be shared, together as the TSQLRecord definitions, and some custom methods.
Even if your demo is not Client/Server, it could gain to be ready to switch to this architecture with minimal code change.

migajek · 2010-12-02 16:41:47

What is the purpose of DestGetJoined if that methods selects IDs only anyway?

So, in case of FillMany we do the following ->
SELECT FROM PivotTable WHERE source = .. AND MOD(dest, 64) = ...

than, once we have both RecordReferences we do SELECTs to find more data ...

While DestGetJoined selects list of ID's and then does id-based SELECTing, am I right?

if that eliminates usage of MOD(dest, 64) in WHERE clause than I like it I guess that might be a little bottleneck since I don't think indexes will be used when WHERE contains a function call?

By the way, there should be overloaded DestGetJoined

similar to that:

function TSQLRecordMany.DestGetJoined(aClient: TSQLRestClient;
  out DestIDs: TIntegerDynArray): boolean; 
begin
result:= DestGetJoined(aClient, '', fSourceID, DestIDs);
end;

// -- edit
Oh, as I can see, DestGetJoined doesn't accept empty WHERE clause... but WHY? Do I have to add ' 1 = 1 ' if I want to list each associated record? Wouldn't it be smarter to do:

if aSQL <> '' then
 aSQL := ' AND ' + aSQL;

and later insert it as the last statement of the query?

also, it's a bit strange but ...

that works fine:

ACustomer.Tasks.DestGetJoined(Database, '1 = 1', ACustomer.ID, fIds); //debugger says: ACustomer.ID = 3

but if I pass 0 as sourceID, the auto retrieving fails. debugger says that fSourceID = 0, so it fails on the second "if aSourceID = 0" check in DestGetJoined ...

Last edited by migajek (2010-12-02 17:05:25)

ab · 2010-12-02 17:03:03

The purpose of DestGetJoined is to add some conditions about the Dest record.

For instance, in your code (if the TUserRole class is the TUserRole table in the Database):

function TUser.HasRole(const AClient: TSQLRest; const ARoleName: RawUTF8; const ARoleID: integer; var ARowID: integer): boolean;
var IDs: TIntegerDynArray;
begin
  result := false;
  if aRoleID<=0 then begin
    if not Roles.DestGetJoined(AClient,'TUserRole.RoleName="'+ARoleName+'"',fID,IDs) then
      exit;
  end else
    if not Roles.DestGetJoined(AClient,'TUserRole.ID='+Int32ToUTF8(ARoleID),fID,IDs) then
      exit;
  if length(IDs)=0 then
    exit;
  ARowID := Ids[0];
  result := true;
end;

The aDestWhereSQL is mandatory, your overloaded method will always fail.

Your classes should better be renamed as TSQLTask, TSQLPerson and such.
Because the TSQLRecord.SQLTableName method will truncate either TSQL either TSQLRecord left side of the class name, in order to create table names.
Task/Person are better table names than TTask and TPerson.
And the fact that the classes have a name starting with TSQL... will indicates they are part of the ORM framework.

ab · 2010-12-02 21:00:02

About MOD() and performance, it won't be an issue.

The SQL request will get first, using the Index, getting all matches for the source ID. Very fast.
There will be as many response as there was a ManyAdd() call for this source ID. A very small list.
In this small list, the MOD() will be performed quite instantaneously (with SQLite3, it's a very fast integer operation).

The reason why TRecordReference is used instead of plain ID is that the SQLTableName is retrieved from the ClassName, and this ClassName must match the Dest class type (without a 's' char). Having TRecordReference can hold it for several Source tables, and foreign key checking will be natively handled by the framework.
I should have made it work with exact TSQLRecord published properties, then getting the class names from there, for properties named "Source" and "Dest". It could be a better idea... I'll investigate into this direction.

The TRecordReference sounded to me like a powerful solution. But TSQLRecord could be clearer... worth looking at it tonight!

migajek · 2010-12-02 22:55:51

ab wrote:

About MOD() and performance, it won't be an issue.
The SQL request will get first, using the Index, getting all matches for the source ID. Very fast.
There will be as many response as there was a ManyAdd() call for this source ID. A very small list.
In this small list, the MOD() will be performed quite instantaneously (with SQLite3, it's a very fast integer operation).

good point Indeed, I was just looking at the second condition and all I heard about query optimization & functions in conditions came to my mind

The TRecordReference sounded to me like a powerful solution. But TSQLRecord could be clearer... worth looking at it tonight!

pointing the record directly would be much more consistent as for me
Currently, if I need to access additional data stored in pivot table, it's a bit tricky ...
see TForm1.lbUsersClick from my demo (fresh r7 available at svn )

Using DestGetJoined is very convenient but doesn't allow access to Pivot Table data (am I correct here?).

I could have used FillMany & iterate each Pivot Record, retrieve the data I needed there, than call db.Retrieve to access the actual Dest / Source record, free the accessed dest / source record, free Pivot Record after iteration finished.
That would be so much more convenient to call pivotRecord.Dest.Something ... directly

Also, that way we'll avoid mess in database. Referencing by record ID is most clear, allows to share the database to other systems, simplifies writing sql queries (especially JOINs on pivot table ) etc

ab · 2010-12-03 07:03:31

migajek wrote:

pointing the record directly would be much more consistent as for me

I'll go into this direction...

migajek wrote:

Currently, if I need to access additional data stored in pivot table, it's a bit tricky ..

.
You could have used User.Roles.ManySelect method, as far as I understood.

migajek wrote:

Using DestGetJoined is very convenient but doesn't allow access to Pivot Table data (am I correct here?).

No, wrong guess: you can make an additional condition to the Pivot Table, since both the Pivot and the Dest tables are available. Just use 'PivotTableName.Field=value' syntax.

migajek wrote:

I could have used FillMany & iterate each Pivot Record, retrieve the data I needed there, than call db.Retrieve to access the actual Dest / Source record, free the accessed dest / source record, free Pivot Record after iteration finished.
That would be so much more convenient to call pivotRecord.Dest.Something ... directly

This could need some additional methods in TSQLRecord?
I don't get exactly your point here.
You can use TSQLRecordMany.DestGet() to get an array of DestID, then use TSQLDest.CreateAndFillPrepare(DestIDs) to go through each records.

ab · 2010-12-03 09:19:48

That's it.

Thanks to your feedback. I've changed the TSQLRecordMany implementation.
TSQLRecordMany now uses TSQLRecord properties, and not TRecordReference any more.

Your code will need some rewrite...

Here is the declaration of this class:

  {{ handle "has many" and "has many through" relationships
   - many-to-many relationship is tracked using a table specifically for that
     relationship, turning the relationship into two one-to-many relationships
     pointing in opposite directions
   - by default, only two TSQLRecord (i.e. INTEGER) fields must be created,
     named "Source" and "Dest", the first pointing to the source record (the one
     with a TSQLRecordMany published property) and the second to the destination record
   - you should first create a type inheriting from TSQLRecordMany, which
     will define the pivot table, providing optional "through" parameters if needed
     ! TSQLDest = class(TSQLRecord);
     ! TSQLSource = class;
     ! TSQLDestPivot = class(TSQLRecordMany)
     ! published
     !   property Source: TSQLSource read fSource; // map Source column
     !   property Dest: TSQLDest read fDest; // map Dest column
     !   property AssociationTime: TDateTime read fTime write fTime;
     ! end;
     ! TSQLSource = class(TSQLRecord)
     ! published
     !   DestList: TSQLDestPivot read fDestList;
     ! end;
   - in all cases, at leat two 'Source' and 'Dest' published properties must
     be declared as TSQLRecord children in any TSQLRecordMany descendant
     because they will always be needed for the 'many to many' relationship
   - when a TSQLRecordMany published property exists in a TSQLRecord, it is
     initialized automaticaly by TSQLRecord.Create
   - to add some associations to the pivot table, use the ManyAdd() method
   - to retrieve an association, use the ManySelect() method
   - to delete an association, use the ManyDelete() method
   - to read all Dest records IDs, use the DestGet() method
   - to read the Dest records and the associated "through" fields content, use
     FillMany then FillRow, FillOne and FillRewind methods to loop through records
   - to read all Source records and the associaed "through" fields content,
     FillManyFromDest then FillRow, FillOne and FillRewind methods
   - to read all Dest IDs after a join to the pivot table, use DestGetJoined }
  TSQLRecordMany = class(TSQLRecord)
  protected
    // internal fields initialized during TSQLRecord.Create
    // - map to the Source and Dest properties field values in TSQLRecord values
    fSourceID: PPtrInt;
    fDestID: PPtrInt;
    // - the Source and Dest classes, as defined in so named properties
    fSourceClass: TSQLRecordClass;
    fDestClass: TSQLRecordClass;
    /// retrieve the TSQLRecordMany ID from a given source+dest IDs pair
    function InternalIDFromSourceDest(aClient: TSQLRest; aSourceID, aDestID: integer): integer;
    function InternalFillMany(aClient: TSQLRest; aID: integer;
      const aAndWhereSQL: RawUTF8; isDest: boolean): integer;
  public
    /// initialize this instance, and needed internal fields
    // - will set protected fSourceID/fSourceClass and fDestID/fDestClass fields
    constructor Create; overload;
    /// retrieve all records associated to a particular source record, which
    // has a TSQLRecordMany property
    // - returns the Count of records corresponding to this aSource record
    // - the records are stored in an internal TSQLTable, refered in the private
    // fTable field, and initialized via a FillPrepare call: all Dest items
    // are therefore accessible with standard FillRow, FillOne and FillRewind methods
    // - use a "for .." loop or a "while FillOne do ..." loop to iterate
    // through all Dest items, getting also any additional 'through' columns
    // - if source ID parameter is 0, the ID is taken from the fSourceID field
    // (set by TSQLRecord.Create)
    // - the optional aAndWhereSQL parameter can be used to add any additional
    // condition to the WHERE statement (e.g. 'Salary>1000 AND Salary<2000')
    // according to TSQLRecordMany properties
    // - note that if the Source record has just been added, fSourceID is not
    // set, so this method will fail: please specify aSourceID parameter with
    // the one just added/created
    function FillMany(aClient: TSQLRest; aSourceID: integer=0;
      const aAndWhereSQL: RawUTF8=''): integer;
    /// retrieve all records associated to a particular Dest record, which
    // has a TSQLRecordMany property
    // - returns the Count of records corresponding to this aSource record
    // - use a "for .." loop or a "while FillOne do ..." loop to iterate
    // through all Dest items, getting also any additional 'through' columns
    // - the optional aAndWhereSQL parameter can be used to add any additional
    // condition to the WHERE statement (e.g. 'Salary>1000 AND Salary<2000')
    // according to TSQLRecordMany properties
    function FillManyFromDest(aClient: TSQLRest; aDestID: integer;
      const aAndWhereSQL: RawUTF8=''): integer;
    /// retrieve all Dest items IDs associated to the specified Source
    function DestGet(aClient: TSQLRest; aSourceID: integer; out DestIDs: TIntegerDynArray): boolean; overload;
    /// retrieve all Dest items IDs associated to the current Source ID
    // - source ID is taken from the fSourceID field (set by TSQLRecord.Create)
    // - note that if the Source record has just been added, fSourceID is not
    // set, so this method will fail: please call the other overloaded method
    function DestGet(aClient: TSQLRest; out DestIDs: TIntegerDynArray): boolean; overload;
    /// retrieve all Source items IDs associated to the specified Dest ID
    function SourceGet(aClient: TSQLRest; aDestID: integer; out SourceIDs: TIntegerDynArray): boolean;
    /// retrieve all Dest items IDs associated to the current or
    // specified Source ID, adding a WHERE condition against the Dest rows
    // - if aSourceID is 0, the value is taken from current fSourceID field
    // (set by TSQLRecord.Create)
    // - aDestWhereSQL must specify the Dest table name in the statement, e.g.
    // 'Employee.Salary>1000'
    // - this is faster than a manual FillMany() then loading each Dest,
    // because the condition is executed in the SQL statement by the server
    function DestGetJoined(aClient: TSQLRestClient; const aDestWhereSQL: RawUTF8;
      aSourceID: Integer; out DestIDs: TIntegerDynArray): boolean;
    /// add a Dest record to the Source record list
    // - returns TRUE on success, FALSE on error
    // - if NoDupplicates is TRUE, the existence of this Source/Dest ID pair
    // is first checked
    // - current Source and Dest properties are filled with the corresponding
    // TRecordReference values corresponding to the supplied IDs
    // - any current value of the additional fields are used to populate the
    // newly created content (i.e. all published properties of this record)
    function ManyAdd(aClient: TSQLRest; aSourceID, aDestID: Integer;
      NoDupplicates: boolean=false): boolean; overload;
    /// add a Dest record to the current Source record list
    // - source ID is taken from the fSourceID field (set by TSQLRecord.Create)
    // - note that if the Source record has just been added, fSourceID is not
    // set, so this method will fail: please call the other overloaded method
    function ManyAdd(aClient: TSQLRest; aDestID: Integer;
      NoDupplicates: boolean=false): boolean; overload;
    /// will delete the record associated with a particular Source/Dest pair
    // - will return TRUE if the pair was found and successfully deleted
    function ManyDelete(aClient: TSQLRest; aSourceID, aDestID: Integer): boolean; overload;
    /// will delete the record associated with the current source and a specified Dest
    // - source ID is taken from the fSourceID field (set by TSQLRecord.Create)
    // - note that if the Source record has just been added, fSourceID is not
    // set, so this method will fail: please call the other overloaded method
    function ManyDelete(aClient: TSQLRest; aDestID: Integer): boolean; overload;
    /// will retrieve the record associated with a particular Source/Dest pair
    // - will return TRUE if the pair was found
    // - in this case, all "through" columns are available in the TSQLRecordMany
    // field instance
    function ManySelect(aClient: TSQLRest; aSourceID, aDestID: Integer): boolean; overload;
    /// will retrieve the record associated with the current source and a specified Dest
    // - source ID is taken from the fSourceID field (set by TSQLRecord.Create)
    // - note that if the Source record has just been added, fSourceID is not
    // set, so this method will fail: please call the other overloaded method
    function ManySelect(aClient: TSQLRest; aDestID: Integer): boolean; overload;

    // get the SQL WHERE statement to be used to retrieve the associated
    // records according to a specified ID
    // - search for aID as Source ID if isDest is FALSE
    // - search for aID as Dest ID if isDest is TRUE
    // - the optional aAndWhereSQL parameter can be used to add any additional
    // condition to the WHERE statement (e.g. 'Salary>1000 AND Salary<2000')
    // according to TSQLRecordMany properties
    function IDWhereSQL(aClient: TSQLRest; aID: integer; isDest: boolean;
      const aAndWhereSQL: RawUTF8=''): RawUTF8;
  end;

migajek · 2010-12-03 10:48:17

ab wrote:

No, wrong guess: you can make an additional condition to the Pivot Table, since both the Pivot and the Dest tables are available. Just use 'PivotTableName.Field=value' syntax.

I know all the fields are available inside of query condition, but the only selected data is ID of Dest [which is obvious]. I don't however know the ID of pivot record, thus making it impossible to access ValidUntil field (in my example). Compare the code you have posted in post #11 ( http://synopse.info/forum/viewtopic.php?id=164#p866) and the current (r7) HasRole implementation.
http://code.google.com/p/synopse-sqlite … n7&r=7#189
I know it could be achieved by adding "UserRoles.ValidUntil >= date('now')" to select valid roles, but ... I want the ID of UserRoles record anyway, even when it has expired.

I don't get exactly your point here.

I was referring to the method described above.
So far I could either iterate pivot table one-by-one (User.Roles.FillMany, User.Roles.FillOne], thus having access both to the pivot's additional data stored [directly, via User.Roles.ValidUntil] and the actual record [Database.Retrieve(User.Roles.Dest)] - which is a bit inconvenient because I need to store & free later retrieved Dest record.
The second method was to call DestGet / DestGetJoined, but I had no access to the pivot data (ValidUntil).

Thus, if I need to DISPLAY [access via Delphi code] both UserRole.name AND UserRoles.ValidUntil, I had to use first method ...

migajek · 2010-12-03 11:21:48

ab wrote:

     ! TSQLDestPivot = class(TSQLRecordMany)
     ! published
     !   property Source: TSQLSource read fSource; // map Source column
     !   property Dest: TSQLDest read fDest; // map Dest column

I'm sorry but I don't really get it ... the code you shown won't work, since there's no "fSource" field.
Declaring own fSource: TSQLSource (and fDest: TSQLDest, respectively) will compile, but I don't know how to retrieve the actual Dest / Source records ... The fSourceID / fDestID are nil after each FillOne ... ?

ab · 2010-12-03 13:41:27

You have to add the private fSource/fDest fields, of course.

I've added a new TSQLRecordMany.DestGetJoined() method returning a Dest TSQLRecord, ready to retrieve all joined records (i.e. FillPrepare having been called).
It won't fit your need of having both pivot and Dest tables fields access, because it's not possible with the "FillPrepare" loop (which parse only one kind of record at a time).

See a full sample of use of TSQLRecordMany in SQLite3.pas, TTestSQLite3Engine._TSQLRestClientDB, procedure TestMany. This test function covers most of the features/methods of this class.

ab · 2010-12-03 14:19:05

I've added a new method:

    /// create a TSQLTable, containing all specified Fields, after a JOIN
    // associated to the current or specified Source ID
    // - the Table will have the fields specified by the JoinKind parameter
    // - if aSourceID is 0, the value is taken from current fSourceID field
    // (set by TSQLRecord.Create)
    // - aDestWhereSQL can specify the Dest table name in the statement, e.g.
    // 'Employee.Salary>1000'
    function DestGetJoinedTable(aClient: TSQLRestClient; aDestWhereSQL: RawUTF8;
      aSourceID: Integer; JoinKind: TSQLRecordManyJoinKind): TSQLTable;

The kind of fields which can be obtained are set by this new enumeration type:

  /// the kind of fields to be available in a Table resulting of
  // a TSQLRecordMany.DestGetJoinedTable() method call
  // - Source fields are not available, because they will be always the same for
  // a same SourceID, and they should be available from the TSQLRecord which
  // hold the TSQLRecordMany instance
  TSQLRecordManyJoinKind = (
    jkDestID, jkPivotID, jkDestFields, jkPivotFields, jkPivotAndDestFields);

So you can have access to Dest fields, Pivot fields, or both (or only Dest or Pivot ID).

migajek · 2010-12-03 14:26:52

How about some method executing

SELECT r.*,  p.* FROM UserRoles p JOIN UserRole r ON p.Dest = r.ID WHERE p.Source = %

on UserRoles (TSQLRecordMany)?

That way it'd be possible to fill both the TSQLRecordMany and it's Dest with proper data, using one query only.

Oh, btw - what has happened to
"constructor TSQLRecordMany.Create(aSourceClass: TSQLRecordClass; aSourceID: integer);" ?

ab · 2010-12-03 14:34:29

The DestGetJoinedTable() method executes such a request.

And the constructor with parameters is not needed any more.
All internal parameters will be taken from the Source and Dest properties of the TSQLRecordMany instance.

migajek · 2010-12-03 14:52:41

ab wrote:

All internal parameters will be taken from the Source and Dest properties of the TSQLRecordMany instance.

Indeed.
Unfortunately I must admit current way of accessing Dest / Source records is very misleading. I know it's necessary to tell the TSQLRecordMany Dest/Source ClassType, but it's confusing that the published field of type TSQLDest is in fact it's id, and - prior to accessing - needs to be casted as integer for actual TSQLDest creation...
see http://code.google.com/p/synopse-sqlite … n8&r=8#204

Last edited by migajek (2010-12-03 14:56:01)

ab · 2010-12-03 16:46:28

The framework needs to know which kind of table this fields will be referring to.
The only way of knowing it, via the RTTI, is to use the class name (TSQLDest for instance).

It's not obvious that a TSQLRecord can either be a real instance of a class (as usual in Delphi), or must be transtyped to an Integer, to get an ID.

The other possibility should be to have a class instance created, with just the ID internally.
But at which recursion level will we end, if this instance has itself some TSQLRecord properties?

If you have a better idea, I'm OK with proposals.
But I tried to manage to get the most from the available RTTI, without having any information to get from external files.
In the current implementation, you can create a whole database just by defining regular Delphi classes.

ab · 2010-12-03 16:47:52

You have the TSQLRest.TableRowCount method, which can be used instead of SQL in TSQLUser.GetCount

migajek · 2010-12-03 23:07:03

I think loading per-request could be a good idea to avoid recursion and other problems.
How about implementing a method "GetDest" or something similar:

pseudocode:

function TSQLRecordMany.GetDest: TSQLRecord;
if fRealDest = nil then
 begin
  fRealDest:= TDestClassType.NewInstance();
  fRealDest.Create(fDestId);
 end;
result:= fRealDest;
end;

of course, TSQLRecordMany will have to take care of freeing the "fRealDest" object on each FillOne and on destroy ...

ab · 2010-12-04 09:30:02

Can you post some use code?

I don't get the exact idea here.
Is it just to avoid a aDest.Free call, and a try..finally block?

migajek · 2010-12-04 10:25:08

ab wrote:

Can you post some use code?
I don't get the exact idea here.
Is it just to avoid a aDest.Free call, and a try..finally block?

in case of FillMany - it doesn't make much sense, indeed. But would it be possible to fill both TSQLRecordMany and it's fRealDest (proposed above) with the result of DestGetJoinedTable? It'd allow using both data sources (pivot & dest table) in pure ORM way, with just one query only ...

ab · 2010-12-04 10:54:09

Now I understand your proposal.

But in all cases, you'll need to have a type explicit conversion like TSQLADest(RealDest) before using it.

Or I could use the same method as in TSQLRecordMany.Create... that is, if a "RealDest" property exists, it will instantiate an instance of it.
In this case, the fRealDest will have to be defined during the class definition, not at the TSQLRecord level, to avoid writing such cryptic TSQLADest(RealDest)...

Perhaps it could make sense to name it "TempDest" or "FillManyDest" or "LocalDest". I don't know...

migajek · 2010-12-04 11:52:56

ab wrote:

Now I understand your proposal.
But in all cases, you'll need to have a type explicit conversion like TSQLADest(RealDest) before using it.

Typecasting is not a problem, since you define Dest type anyway, you're also certain about RealDest type.

Or I could use the same method as in TSQLRecordMany.Create... that is, if a "RealDest" property exists, it will instantiate an instance of it.
In this case, the fRealDest will have to be defined during the class definition, not at the TSQLRecord level, to avoid writing such cryptic TSQLADest(RealDest)...

it is up to you, whether you want us to define more fields in TSQLRecordMany, thus avoiding typecasts when using, or to use typecast each time

Perhaps it could make sense to name it "TempDest" or "FillManyDest" or "LocalDest". I don't know...

perhaps ... to be honest, RealDest came to my mind as opposite to Dest which (for me) was not Dest at all (just it's id) ]:->

ab · 2010-12-04 12:06:05

migajek wrote:

to be honest, RealDest came to my mind as opposite to Dest which (for me) was not Dest at all (just it's id) ]:->

What about "DestTempInstance" or "DestLocalInstance", since "Real" is some kind of floating-point type in Delphi?
Another direction could be "OwnedDest", since the TSQLRecordMany would be the owner of this intance.

migajek · 2010-12-04 23:36:20

ab wrote:

migajek wrote:
to be honest, RealDest came to my mind as opposite to Dest which (for me) was not Dest at all (just it's id) ]:->
What about "DestTempInstance" or "DestLocalInstance", since "Real" is some kind of floating-point type in Delphi?
Another direction could be "OwnedDest", since the TSQLRecordMany would be the owner of this intance.

It's completely up to you None of them is perfect, none of them is most intuitive, but we don't have better option, since "Dest" is taken already ... or maybe rename Dest to DestID, and call the new one just Dest?

ab · 2010-12-05 09:45:52

migajek wrote:

or maybe rename Dest to DestID, and call the new one just Dest?

It's a possibility, but we have to be consistent with all records, i.e. all TSQLRecord properties must be renamed as SomeFieldID, then trim right this 'ID' in order to get the field name for database access...
I suspect it will be more difficult for the coder than taking in account one main rule: the TSQLRecord properties contains the ID.

Perhaps a new class method, like ToID, could make it easier: if you want to get the ID, you can either use Integer(aRecord.aDest) or PtrInt(aRecord.aDest), or aRecord.Dest.ToID.
We can't use the ID name here, because it's used to store the real ID in case of a TSQLRecord instance creation....

So I've made another modification. A bit tricky, but could make life easier, with the problem of having both ID and ToID methods.

TSQLRecord.ID reader has now a GetID() getter which can handle the fact that a published property declared as TSQLRecord (sftID type) contains not a true TSQLRecord instance, but the ID of this record: you can use aProperty.ID method in order to get the idea - but prefered method is to typecast it via PtrInt(aProperty), because GetID() relies on some low-level windows memory mapping trick.

So you can use aDest.ID to retrieve the ID - even if PtrInt(aDest) will be a bit faster, and will always work.

Thanks for your feedback. I think it really helps the ORM to be easier to use.

migajek · 2010-12-05 17:02:46

ab wrote:

TSQLRecord.ID reader has now a GetID() getter which can handle the fact that a published property declared as TSQLRecord (sftID type) contains not a true TSQLRecord instance, but the ID of this record: you can use aProperty.ID method in order to get the idea - but prefered method is to typecast it via PtrInt(aProperty), because GetID() relies on some low-level windows memory mapping trick.
So you can use aDest.ID to retrieve the ID - even if PtrInt(aDest) will be a bit faster, and will always work.

ok, now I'm confused I have no idea how is that supposed to work, except that in my case (HasRole) Dest is always integer ID of Dest record ... could you give some code examples?

Thanks for your feedback. I think it really helps the ORM to be easier to use.

I'm glad you like it, and thanks for introducing them

ab · 2010-12-06 07:52:58

If you define a TSQLRecord inherited published property, it will define a one to one link to another record.

When the record is retrieved from the database, the linked ID will be stored in this property value.

Therefore, as you noticed for (HasRole) Dest, you'll have to map it from a pointer to an integer, to get the ID:

role:= TSQLUserRole.Create(globalClient, integer(fRoles.Dest));

or even better (for 64 bits support)

role:= TSQLUserRole.Create(globalClient, PtrInt(fRoles.Dest));

or now you can write

role:= TSQLUserRole.Create(globalClient, fRoles.Dest.ID);

but in the last case, the ID value must be up to 1,048,576 (i.e. $100000).

migajek · 2011-02-15 18:23:48

Hi,
I've been very busy for quite a long time but now I'm back.
I believe the demo app is complete enough to give an overall view on how to start with SQLite Framework.
Would you like to review the current code and possibly include it to the Synopse SQLite FW demos?

ab · 2011-02-16 06:46:38

Is it still available in the same source code repository?

Of course I could add it to the "official" demos. The more there is, the better!

Thanks!

migajek · 2011-02-16 07:35:43

Yup, still on google code, I don't plan to switch the hosting, I find google code perfect for such small code pieces. Btw if you don't cleanup .svn subdirectory, everyone will be able to update the code

but please, have a look at the code before including it, last thing I'd like to do is to provide people with example showing incorrect approach!

ab · 2011-02-16 08:02:58

Of course I'll take a look and make perhaps some code refactoring, to show 'best practice' if possible.

And I'll probably add some comments if needed.

migajek · 2011-02-16 10:49:19

if you have google account I'll let you commit the code ... just give me your uname

ab · 2011-02-16 20:27:41

I don't have a google account...

Could you make an updated download link with the latest release?
It's the rev 5 which is still published in the main download page.

migajek · 2011-02-16 21:04:30

I did so. I'm sorry, I was pretty sure you use svn So I didn't updated the source archives, just commited the lates version to svn

ab · 2011-02-17 06:15:50

No I don't have SVN installed.

I use fossil for my OpenSource projects, because it's much lighter, has an embedded web server and is distributed (whereas SVN stay centralized).
See http://www.fossil-scm.org

esmondb · 2011-02-17 18:39:46

Going back to the first post in the thread, I've got a problem with the new ManyAdd feature. If I do something like (pseudocode):

ManyAdd(client, sourceID, destID, noDupes=True);
for i := 1 to 'rest of items' do
  ManyAdd(client, destID[i], noDupes=True);  //no sourceID

if the first ManyAdd fails because of the noDuplicates condition it exits before this line is run:

fSourceID^ := aSourceID;

Then subsequent calls to ManyAdd all fail because because fSourceID hasn't been set.
Could the line be moved to near the beginning of the function to solve this?
Also found this line doesn't appear at all in ManyDelete so the code construct above wont work - can it be added?

Many Thanks

ab · 2011-02-18 11:05:30

Why is fSourceID=nil?

It could have been set in the TSQLRecordMany.Create constructor automatically.

Your code should use a wrong constructor... how to you create the instance?

Could you try to modify in TSQLRecord definition:

    {{ this constructor initializes
    - the TableMap[] length with published properties count
    - HasField property if some published property is a valid simple SQL field
      (but not a BLOB, since BLOB are not by default part of the JSON content)
    - HasBlob property if some published property is a BLOB (TSQLRawBlob) }
    constructor Create; overload; virtual;

and in TSQLRecordMany definition:

    /// initialize this instance, and needed internal fields
    // - will set protected fSourceID/fSourceClass and fDestID/fDestClass fields
    constructor Create; overload; override;

It's perhaps just a problem of missing virtual/override declaration...

esmondb · 2011-02-18 12:14:39

I don't seem to be calling TSQLRecordMany.Create constructor directly, only through a standard TSQLRecord which contains one. I'm using the framework embedded and below is the code thats giving me trouble. Can you see whats going wrong?

          selectionRec := TSQLSelRec.Create(fdbServers[i],SQLWhere);
          sl := TStringList.Create;
          try
            sl.CommaText := Params;
            if (sl.Count > 0) and (LightboxRec.ID > 0) then begin
              if TransactionBegin(TSQLSelRec) then try
                fi := StrToIntDef(sl.strings[0],-1);
                if fi > 0 then begin
                  SelectionRec.DestList.ManyAdd(fdbServers[i],LightboxRec.ID,fi,true);
                  for si := 1 to sl.Count - 1 do begin
                    fi := StrToIntDef(sl.strings[si],-1);
                    if fi > 0 then
                      SelectionRec.DestList.ManyAdd(fdbServers[i],fi,true);
                  end;
                end;
              finally
                Commit;
              end;
            end;
          finally
            sl.Free;
            LightboxRec.Free;
          end;

ab · 2011-02-18 12:22:34

Did you try with the virtual+override modified declaration above?

migajek · 2011-02-19 00:01:41

ab wrote:

role:= TSQLUserRole.Create(globalClient, fRoles.Dest.ID);
but in the last case, the ID value must be up to 1,048,576 (i.e. $100000).

from what I investigated the trick, it just changes the way it's written from PtrInt(fRoles.Dest) to fRoles.Dest.ID.

But still I have no direct access to the "real" Dest object, I need to create & later free it manually.

Let's say I have a TSQLLesson which has property Teacher: TSQLPerson.

to speed up development Id like to write code like this:

var
l: TSQLLesson;
begin
l:= TSQLLesson.Create(db, fLessonID);

ShowMessage(l.Teacher.Name);

correct me if I'm wrong but currently framework doesn't support it, I need to declare TSQLLesson.GetTeacher
which returns

function TSQLLesson.GetTeacher: TSQLPerson;
begin
result:= TSQLPerson.Create(db, Teacher.ID); // or PtrInt(Teacher);

and of course handling memory free manually.

Do you think it'd be possible to automate the object retrieval & freeing that way?

migajek · 2011-02-19 16:04:47

some thoughts ...

* FillOne -> at first, free all "related" objects (if they were created with FillOne)
* there should be a list of objects to free, so we won't confuse them with manually assigned objects.
* FillOne loads initializes all the 'related' fields with their instances
* each initialized instance is added to 'to-remove' list.
* there should be some 'guard' in order to protect from loading circual references.

ab · 2011-02-20 09:19:37

Use TSQLRecordMany.FillMany() then retrieve the records one by one.

This is more or less the same principle as you "thoughts".

migajek · 2011-02-22 21:24:55

FillMany works on pivot tables, while I'm talking about "direct" relations, so I don't see how is it supposed to work?

mORMot Open Source

#1 2010-11-30 16:56:55

Demo application + some question / remarks.

#2 2010-12-01 07:45:28

Re: Demo application + some question / remarks.

#3 2010-12-01 10:13:16

Re: Demo application + some question / remarks.

#4 2010-12-01 12:34:39

Re: Demo application + some question / remarks.

#5 2010-12-01 13:37:40

Re: Demo application + some question / remarks.

#6 2010-12-01 21:46:44

Re: Demo application + some question / remarks.

#7 2010-12-02 08:46:03

Re: Demo application + some question / remarks.

#8 2010-12-02 12:03:55

Re: Demo application + some question / remarks.

#9 2010-12-02 15:53:56

Re: Demo application + some question / remarks.

#10 2010-12-02 16:41:47

Re: Demo application + some question / remarks.

#11 2010-12-02 17:03:03

Re: Demo application + some question / remarks.

#12 2010-12-02 21:00:02

Re: Demo application + some question / remarks.

#13 2010-12-02 22:55:51

Re: Demo application + some question / remarks.

#14 2010-12-03 07:03:31

Re: Demo application + some question / remarks.

#15 2010-12-03 09:19:48

Re: Demo application + some question / remarks.

#16 2010-12-03 10:48:17

Re: Demo application + some question / remarks.

#17 2010-12-03 11:21:48

Re: Demo application + some question / remarks.

#18 2010-12-03 13:41:27

Re: Demo application + some question / remarks.

#19 2010-12-03 14:19:05

Re: Demo application + some question / remarks.

#20 2010-12-03 14:26:52

Re: Demo application + some question / remarks.

#21 2010-12-03 14:34:29

Re: Demo application + some question / remarks.

#22 2010-12-03 14:52:41

Re: Demo application + some question / remarks.

#23 2010-12-03 16:46:28

Re: Demo application + some question / remarks.

#24 2010-12-03 16:47:52

Re: Demo application + some question / remarks.

#25 2010-12-03 23:07:03

Re: Demo application + some question / remarks.

#26 2010-12-04 09:30:02

Re: Demo application + some question / remarks.

#27 2010-12-04 10:25:08

Re: Demo application + some question / remarks.

#28 2010-12-04 10:54:09

Re: Demo application + some question / remarks.

#29 2010-12-04 11:52:56

Re: Demo application + some question / remarks.

#30 2010-12-04 12:06:05

Re: Demo application + some question / remarks.

#31 2010-12-04 23:36:20

Re: Demo application + some question / remarks.

#32 2010-12-05 09:45:52

Re: Demo application + some question / remarks.

#33 2010-12-05 17:02:46

Re: Demo application + some question / remarks.

#34 2010-12-06 07:52:58

Re: Demo application + some question / remarks.

#35 2011-02-15 18:23:48

Re: Demo application + some question / remarks.

#36 2011-02-16 06:46:38

Re: Demo application + some question / remarks.

#37 2011-02-16 07:35:43

Re: Demo application + some question / remarks.

#38 2011-02-16 08:02:58

Re: Demo application + some question / remarks.

#39 2011-02-16 10:49:19

Re: Demo application + some question / remarks.

#40 2011-02-16 20:27:41