#1 mORMot 1 » JOIN questions » 2014-11-10 01:56:36

Martin Sedgewick
Replies: 1

Hi Arnaud,

Great work on the HTML documentation, it is a great resource.

I am trying to figure out how to do some things in the framework and I am not sure what is the best approach.

1) Multi table joins

In this example I have 3 tables

TSQLCountry = class( TSQLRecord )
  private
    fname: RawUTF8;
  published
    property Name: RawUTF8 read fname write fname;
  end;

  TSQLContactDetails = class( TSQLRecord )
  private
    fCountry: TSQLCountry;
  published
    property Country: TSQLCountry read fCountry write fCOuntry;
  end;

  TSQLStudent = class(TSQLRecord)
  private
    fFirstName: RawUTF8;
    fContactDetails: TSQLContactDetails;
  published
    property FirstName: RawUTF8 read fFirstName write fFirstName;
    property ContactDetails: TSQLContactDetails read fContactDetails write fContactDetails;
  end;

  What I want to do is get all students where the country is X. I can write SQL as follows: (SQL MAY BE WRONG, but you get the point smile)

  SELECT S.*
  FROM 
    STUDENTS S 
    JOIN CONTACTDETAILS CD ON (CD.ID = S.CONTACTDETAILSID)
    JOIN COUNTRYS C ON (C.ID = CD.COUNTRYID)
  WHERE
    C.Name = 'Scotland'

  I looked at something like CreateAndFillPrepareMany where it allows multi-table joins, but this is only for use in many-to-many relationships.

  Something like

  student.CreateAndFillPrepareJoined( Client, 'ContactDetails.Country.Name = ?', [], ['Scotland'] );

  Also, I would then want to put in where clauses for each table, so S.firstname LIKE ? AND C.Name = ?.



2) Please note that the code does have not a check in CreateAndFillPrepareMany if you dont have a many relationship, it writes incorrect SQL. I think it should write correct SQL or raise an exception "No many-to-many relationships defined on this record". To try this, call the method with a TSQLRecord with no TSQLRecordMany, like my TSQLStudent. The SQL has a where statement which starts " and (...)". This is because of the following line:

aSQL := aSQL+' and ('+FormatUTF8(pointer(aSQLWhere),aParamsSQLJoin,aBoundsSQLJoin)+')';


3) I then want to start getting students from a join across both One-To-One relationships AND Many-To-Many relationships. As far as I can tell, I am restricted to one or the other in either CreateAndFillPrepareMany or CreateAndFillPrepareJoined. Is this correct?


4) What is your opinion on the best way to retrieve data across multiple tables? I could not see anything in the documentation or examples for this scenario.

If, from the example above (Students, ContactDetails, Countries) I wanted to write the following SQL:

  SELECT S.FirstName, C.Name    <---returning fields across tables
  FROM 
    STUDENTS S 
    JOIN CONTACTDETAILS CD ON (CD.ID = S.CONTACTDETAILSID)
    JOIN COUNTRYS C ON (C.ID = CD.COUNTRYID)
  WHERE
    C.Name = 'Scotland'

  I have no idea how I would ask for this in code.

  Would I loop through all students, look at their contactdetails and then look at their countries? This may be ok for a couple of objects, but as soon as more table joins are added and the number of records increases, this would not be efficient.

  Most of the SQL in my application consists of querying data across many tables and while I can see the ORM being useful for some things, reading data like this doesnt seem supported.

  Am I best, in this case, to use a SQL query and bypass the ORM? 

 

  An approach I would think might work: it might be a list which shows all students who live in Scotland. I would possibly write a new TSQLStudentWithCountry, which is not a real table, but one which is purely a read-only mapping. You would not really want to write this back to the database.

TSQLStudentWithCountry = class(TSQLReadOnlyRecord)  <--- Not sure if you have a way of making a record read only. But something like this...
  private
    fFirstName: RawUTF8;
    fContactDetails: TSQLContactDetails;
    fCountryName: RawUTF8;
  published
    property FirstName: RawUTF8 read fFirstName write fFirstName;
    property ContactDetails: TSQLContactDetails read fContactDetails write fContactDetails;
    property CountryName: RawUTF8 read fCountryName write fCountryName;
  end;


  model := TSQLModel.Create( [ TSQLCountry, TSQLContactDetails, TSQLStudent, TSQLStudentWithCountry ] );

  Model.Props[TSQLStudentsWithCountry].ExternalDB.
  MapField('CountryName','ContactDetails.Country.Name');

  I have intentionally made this across multiple tables, as that is what I would use it for, and not just a StudentAndAddress class, which would be similar, and can currently be done with the CreateAndFillPrepareJoined method.
  I appreciate this is maybe not possible. I used Entity Framework many years ago and I remember you could do something like this in the mappings.
 


I hope these make sense. Thanks for any help.

#2 Re: mORMot 1 » Stubs and Mocks in mORMot » 2013-01-25 09:05:37

Thank you Arnaud.

I would expect NIL if not assigned.

#3 Re: mORMot 1 » Stubs and Mocks in mORMot » 2013-01-25 00:19:45

I am trying to use Mocks and Stubs and have run into a problem: interfaces as parameters.

I am using Delphi 6 and source code from 23rd January.

First I define 2 interfaces. Interface I2 has a property which is of type I1.

type
  I1 = interface( IInvokable )
       ['{FDF97D0F-9290-44C2-AD2B-48C87EF9F8EA}']
       end;

  I2 = interface( IInvokable )
       ['{904BC00C-2745-4C12-B09C-D841627BC7AF}']
       procedure Set_I( value: I1 );

       property I: I1 write Set_I;
       end;

I then create a Mock.

procedure DoTest;
var
  iii : I2;
begin
  TInterfaceMock.Create( TypeInfo( I2 ), iii, NIL );
end;

This breaks in the interface factory because TypeInfoToMethodValueType returns smvNone as the typeValue. Looking at this method, it does not recognise interfaces as a type.

          ValueType := TypeInfoToMethodValueType(TypeInfo);
          case ValueType of
          smvNone: begin
            case TypeInfo^.Kind of
            ...
            end;
            raise exception.....
         end;

I have read some of the blog and documentation and cannot see any reference to this. I am not sure what the code does and if this is something you dont need, so have not implemented or if there is a problem with implementing it.

Also, it is the same with a tkMethod Kind. As I also pass Event Handlers in interfaces.


Edit:
I have done some further looking into it and I see it is because you store information as JSON through the textwriter class. But you store instance pointer along with the classname. Is it possible to add a new method to this class to store interface pointer and interface type? Then send back the interface when method is called?

Sorry if this should be a new topic, if it should, please let me know

#4 Re: mORMot 1 » Delphi 6 mORMot.pas compile issue (v1.18) » 2013-01-23 17:00:26

in Delphi 6, Delphi cannot generate RTTI for records and TGUID is a packed record.

so in our COM interfaces we have the following:

function Object_(ID: TGUID): IObjectInfo; safecall;

This is allowed in COM, but as TGUID cannot have RTTI generated, I cannot compile my code with {$m+} around the interface.

This has been solved in Delphi 2010.


To solve this I would need to replace the TGUID with a string or other type to pass.

function Object_(ID: WideString): IObjectInfo; safecall;

then cast this to GUID on each side. This is uglier code but allows me to use TInterfaceMock.

So I think we will leave COM interfaces as current manually made mocks (using pascalmocks) and change to use your TInterfaceMock for normal interfaces.

Thanks for the help.

#5 Re: mORMot 1 » Delphi 6 mORMot.pas compile issue (v1.18) » 2013-01-23 16:32:05

Yes, I can add {$M+} into COM definitions which will work,

but we use TGUID a lot in our COM interfaces and TGUID does not have RTTI....so yet another problem.

Change all interfaces to not use TGUID and make the code cast string <-> guid everywhere or have no interface mocking?

I do not know, I will think about it.

#6 Re: mORMot 1 » Delphi 6 mORMot.pas compile issue (v1.18) » 2013-01-23 15:47:43

I realise my mistake. my interface needs to inherit from IInvokable which has {$m+} and my interface will get RTTI.

I am, however, using COM interfaces which do not inherit from IInvokable, but IUknown....:(

#7 Re: mORMot 1 » Delphi 6 mORMot.pas compile issue (v1.18) » 2013-01-23 15:42:44

No problem on finding the issue, just took a little time.

I am trying to use TInterfaceStub/Mock in some code and the documentation says:

TInterfaceStub.Create(TypeInfo(ISmsSender),SmsSender).
    Returns('Send',[true]);

when I put my own interface in place of ISMSSender, I get "IMYINTERFACE interface has no RTTI"

This comes from constructor TInterfaceFactory.Create(aInterface: PTypeInfo);

Is this possible in Delphi 6. RTTI is not something I know a lot about and I am not sure if I can get type info on an interface through another way.

#8 Re: mORMot 1 » Delphi 6 mORMot.pas compile issue (v1.18) » 2013-01-23 12:44:25

Thank you very much!

That was fast and efficient service smile

#9 Re: mORMot 1 » Delphi 6 mORMot.pas compile issue (v1.18) » 2013-01-23 11:27:10

I have tracked the compile issue to the following line:

function TSQLPropInfoRTTIVariant.GetHash(Instance: TObject; CaseInsensitive: boolean): cardinal;
...
   varInt64, varDouble, varDate, varCurrency:
     result := Int64Rec(VInt64).Lo xor Int64Rec(VInt64).Hi;  <-- this one

#10 Re: mORMot 1 » Delphi 6 mORMot.pas compile issue (v1.18) » 2013-01-23 11:00:44

This is the commit which introduces the compile issue in Delphi 6.

2012-11-14

16:20   
[2543c7d420]
added process of Variant and WideString types in TSQLRecord properties (as sftVariant and sftRaWUYTF8)
TSQLPropInfo*.SetValue() now expect an additional wasString: boolean parameter
TSQLPropInfo*.SameValue() is replaced by more versatile TSQLPropInfo*.CompareValue()
(user: abouchez, tags: trunk)

#11 Re: mORMot 1 » Delphi 6 mORMot.pas compile issue (v1.18) » 2013-01-23 10:49:38

I am trying to find the point where the compilation breaks.

I am starting here, where I can compile in SQLite3Commons.

2012-10-16
12:49   
[f8dac6147e] speed up of TDynArrayHashed hashing process (user: abouchez, tags: trunk)

#12 mORMot 1 » Delphi 6 mORMot.pas compile issue (v1.18) » 2013-01-23 01:13:18

Martin Sedgewick
Replies: 12

Hi,

I want to start integrating mORMot into our existing codebase and I thought the first thing I would do is use the TInterfaceMock/TInterfaceStub as it will be really helpful in the short term.

I am using Delphi 6, and I have an existing unit test project. I added mORMot.pas and When compiling I get:

[Fatal Error] mORMot.pas(31557): Internal error: URW699

I think as you test with Delphi 6, it may be how I have the synopse.inc file set up. I have tried to read through the forum and the documentation but could not find any solution to setting up for Delphi 6.

I have uninstalled most 3rd party IDE extensions like CNWizards and even tried just loading and running the sample/demo projects but I get the same issues.

Any help with this would be great, I am really eager to get into the framework.

Martin

Board footer

Powered by FluxBB