How to properly create a database model ?

TLama · 2012-03-31 00:11:36

Hi again,

I've upgraded from the latest stable version to the latest trunk without leaf and my previosly created application stops to work. Especially it's not possible to CreateMissingTables nor do the Client.Update (if I tried it with the old, already created DB). I've used the TSQLRest base class because in the future I'd like to use different kind of clients.

Here's how am I creating the database model:

unit SynSQLiteTestUnit;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, SynCommons, SQLite3Commons, SQLite3;

type
  TBaseSQLRecord = class(TSQLRecord)
  private
    FCreatedAt: TTimeLog;
    FCreatedBy: RawUTF8;
    FUpdatedAt: TTimeLog;
    FUpdatedBy: RawUTF8;
  published
    property CreatedAt: TTimeLog read FCreatedAt write FCreatedAt;
    property CreatedBy: RawUTF8 read FCreatedBy write FCreatedBy;
    property UpdatedAt: TTimeLog read FUpdatedAt write FUpdatedAt;
    property UpdatedBy: RawUTF8 read FUpdatedBy write FUpdatedBy;
  end;

  TSQLEmployee = class(TBaseSQLRecord)
  private
    FNumber: Integer;
    FFirstName: RawUTF8;
    FLastName: RawUTF8;
  published
    property Number: Integer read FNumber write FNumber;
    property FirstName: RawUTF8 read FFirstName write FFirstName;
    property LastName: RawUTF8 read FLastName write FLastName;
  end;

type
  TForm1 = class(TForm)
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
  private
    FClient: TSQLRest;
    FSQLModel: TSQLModel;
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
begin
  FSQLModel := TSQLModel.Create([
    TSQLEmployee
  ]);
  FClient := TSQLRestClientDB.Create(FSQLModel, nil,
    ChangeFileExt(Application.ExeName,'.sqlite'), TSQLRestServerDB);
  TSQLRestServerDB(FClient).CreateMissingTables;
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
  FClient.Free;
  FSQLModel.Free;
end;

end.

What has changed from the latest stable version till now ? I've checked the docs and on the page 68 seems to be the same way to create a database model and even MainDemo fails in the FileServer.pas on line with CreateMissingTables. The problem is laying somewhere in the TSQLRestServerDB.CreateMissingTables, the first line check DB.TransactionActive throws the access violation because the DB is nil.

Can you suggest me what am I doing wrong or tell if it's a bug, please ?

Thanks a lot!

Last edited by TLama (2012-03-31 00:45:59)

ab · 2012-03-31 08:03:20

I just checked with the latest sources from http://synopse.info/fossil

I tried the regression tests, and the MainDemo.
They work as expected.

I think you did not retrieve all latest sources, or the corresponding .obj files.
See http://synopse.info/fossil/wiki?name=Get+the+source

TLama · 2012-03-31 10:21:47

I think I did, I've downloaded this zip archive and these SQLite3 object files.

c:\Users\Public\Documents\Synopse\ - path where I've copied the framework (this is also where I have the SQLite *.obj files), and it's 100% the only place where it is
c:\Users\Public\Documents\Synopse\SQLite3\Samples\MainDemo\SynFile.dpr - trying to run this project gives me an access violation at start caused by what I've described

The AV is thrown from SynCommons.pas in TSynCache.Reset when the Count is being read; could you please check you are reading the right value ?

Forgot to mention I'm using Delphi 2009 on Windows 7, if it's important.
Or does it mean I should download trunk or SQLite3 object files from somewhere else ?
About the zip archives, do they contain the trunk or should I download each file per one ?

Thank you!

Last edited by TLama (2012-04-01 19:59:28)

TLama · 2012-04-03 08:22:59

I've checked the same versions I've mentioned in Delphi 2007 and it works there, but in my D2009 doesn't. It would be hard to tell you here what everything I have installed there, because it's lot of stuff, but it causes the AV in MainDemo.
Never mind, I'll stick back to standalone SQLite.

Thanks anyway!

Last edited by TLama (2012-04-03 08:28:36)

Leander007 · 2012-04-03 11:01:34

Yes, MainDemo is not ok as is for Delphi 2009, see the workaround.

Summarized info about my personal experience about mORMot "usability" in general and from commercial point of view.

ab · 2012-04-03 12:02:32

Sounds like an initialization issue of a record, due to a Delphi 2009 compiler bug..
See http://synopse.info/forum/viewtopic.php?id=210
This is a random issue, that is it will occur one time or another, depending of the last build status...

But TSynCache is a class, so won't normally suffer of this issue.
But it may fails because of TSQLStatementCached definition as object.

I've committed some changes, trying to fix those Delphi 2009 compiler issues with object.
See http://synopse.info/fossil/info/c1228fe691

TLama · 2012-04-03 13:10:17

@Arnaud, thanks a lot!
I'll try it as soon as I get home to my D2009. I'll let you know later on.

TLama · 2012-04-03 22:36:39

The same problem remains. When I call TSQLRestServerDB(FClient).CreateMissingTables; from my example above and step into it with debugger I fail with AV on the first line on condition if DB.TransactionActive then because DB is at that time nil.

procedure TSQLRestServerDB.CreateMissingTables(user_version: cardinal);
var t,f: integer;
    TableNamesAtCreation, aFields: TRawUTF8DynArray;
    TableJustCreated: TSQLFieldTables;
    aSQL: RawUTF8;
begin
  if DB.TransactionActive then // here DB = nil, thus fails with AV
    raise EBusinessLayerException.Create('CreateMissingTables: Transaction');

ab · 2012-04-04 05:19:12

The mistake comes from the fact that you force the typecast of FClient to TSQLRestServerDB, which is not this kind of class.

You should not write

TSQLRestServerDB(FClient).CreateMissingTables;

but

FClient.Server.CreateMissingTables;

As stated by all corresponding sample code.

When you typecast one object to the other, you should better use not a forced type cast, but use the "as" operator as such:

(FClient as TSQLRestServerDB).CreateMissingTables;

Using the forced type cast does make sense only for performance purpose, in code explicit like this:

if aServer.InheritsFrom(TSQLRestServerDB) then 
begin
  TSQLRestServerDB(aServer).CreateMissingTables;
  ....

TLama · 2012-04-04 08:16:20

Thanks, I will try it. I know about as "safe" typecast, that hard typecast came from the old docs. I have to update my brain to the new version :-)

mORMot Open Source

#1 2012-03-31 00:11:36

How to properly create a database model ?

#2 2012-03-31 08:03:20

Re: How to properly create a database model ?

#3 2012-03-31 10:21:47

Re: How to properly create a database model ?

#4 2012-04-03 08:22:59

Re: How to properly create a database model ?

#5 2012-04-03 11:01:34

Re: How to properly create a database model ?

#6 2012-04-03 12:02:32

Re: How to properly create a database model ?

#7 2012-04-03 13:10:17

Re: How to properly create a database model ?

#8 2012-04-03 22:36:39

Re: How to properly create a database model ?

#9 2012-04-04 05:19:12

Re: How to properly create a database model ?

#10 2012-04-04 08:16:20

Re: How to properly create a database model ?

Board footer