How to create a MSSQL Database from TSQLModel?

corchi72 · 2011-11-22 10:35:36

I created a client \ server by using it your own framework.
I have used the SQLite as database server, but the client operations are too slow, now I want to migrate to MSSQL and I read that it's possible.
I ask if there is a procedure that automatically creat all the tables in MSSQ ( with "CreateMissingTables") or I have to map every single class of my model with the MSSQL tables?
I only found this example:

// !  Props := TOleDBMSSQLConnectionProperties.Create('.\SQLEXPRESS','AdventureWorks2008R2','','');
// !  Model := TSQLModel.Create([TSQLCustomer],'root');
// !  VirtualTableExternalRegister(Model,TSQLCustomer,Props,'Sales.Customer');

Thanks corchi 72

ab · 2011-11-22 10:48:18

See the corresponding paragraphs in the latest version of the SAD document.
It will explain how external databases can be used.
In particular, it will create all tables - yes, CreateMissingTables will work as with SQlite3.

But I suspect that using MSSQL instead of SQLite won't make your application noticeably faster.

What is slow?
Where is time spent?
You may better use a software profiler and identify the bottlenecks.

For instance:
- Are you creating indexes to speed up queries?
- Which data are you retrieving to the clients? What is the needed bandwidth? Perhaps you may be able to let the process be made on the server side (by changing the query parameters, or using a service).
- Did you try to enable the logs then try to guess what could be enhanced?
- Are you using BATCH sequences and Transactions to speed up insertion, update or deletion? Transactions make a big difference with SQLite3 writing speed - but they should better be defined on the server side, within a service.

corchi72 · 2011-11-22 11:31:07

ok thanks, before, I try to connect to MSSQL and then I will do all the tests that you suggested

corchi72 · 2011-11-22 11:37:06

I forgot to tell you that I work in a network is not the Internet, change anything?

corchi72 · 2011-11-22 14:12:41

Sorry,

I read the documentation but I have not found a consistent example to make me understand how to implement the connection using MSSQL and my model without adding new classes.
I wrote the following routine that uses the server to connect to SQLite files, what should I add to make sure that the tables are created depending on the model?

constructor TFileServer.Create(const ServerName,SeverPort: AnsiString;const aFilename: AnsiString);

begin
  try

  writeln(format('file server is %s',[aFilename]));
  inherited Create(CreateFileModel(self),aFilename);
  CreateMissingTables(0); // ExeVersion.Version.Version32);
  Server := TSQLite3HttpServer.Create(SeverPort,[self],ServerName);

  {$ifdef MSSQL}
   Props := TOleDBMSSQLConnectionProperties.Create('SQL2008\SQL2008',MyDB','','');
   inherited Create(CreateFileModel(self))
   
  {$endif}

  finally

  end;



end;

thanks

ab · 2011-11-22 14:54:59

As stated by the documentation, you have to inherit your classes not from TSQLRecord but from TSQLRecordExternal.

Then use VirtualTableExternalRegister().

You have some sample code in TTestExternalDatabase.Test method of unit SynSelfTests.

This is explained in pages 93 and following of the SAD document (revision 1.15).
And also this blog article: http://blog.synopse.info/post/2011/08/0 … e3-limited

My advice is first to make it faster with SQlite3, and only use MSSQL if you need to.
I'm quite sure the issue is not in SQLite3 performance, but in how you defined your data.

corchi72 · 2011-11-22 15:39:40

If I derive classes from TSQLRecordExternal means I have to modify the code written for the model, I had understood that it not necessary to modified the original classes

TSQLFile = class(TSQLRecordSigned)   modified to TSQLFile = class(TSQLRecordExternal)
  private 
...
end
 // user of our system
  TSQLUser = class(TSQLFile) 
  private
...
end

//VirtualTableExternalRegister(fModel,TSQLRecordPeopleExt,fConnection,'PeopleExternal');

function CreateFileModel(Owner: TSQLRest): TSQLModel;
var Classes: array[0..high(FileTabs)] of TSQLRecordClass;
    i: integer;
begin

  for i := 0 to high(FileTabs) do
    Classes[i] := FileTabs[i].Table;

  Model := TSQLModel.Create(Classes);
  Model.Owner := Owner;
  Model.SetEvents(TypeInfo(TFileEvent));
  Result := Model;
end;


constructor TFileServer.Create(const ServerName,SeverPort: AnsiString;const aFilename: AnsiString);
var
 i :Integer;
begin
  try

  writeln(format('file server is %s',[aFilename]));
  inherited Create(CreateFileModel(self),aFilename);

 {$ifdef MSSQL}
   Props := TOleDBMSSQLConnectionProperties.Create('SQL2008\SQL2008',MyDB','','');
   inherited Create(CreateFileModel(self))
   for i := 0 to high(Model.Tables) do
      VirtualTableExternalRegister(Model, Model.Tables[i],Props,'');
  {$endif}

  CreateMissingTables(0); // ExeVersion.Version.Version32);
  Server := TSQLite3HttpServer.Create(SeverPort,[self],ServerName);


  finally

  end;

end;

way is it correct? or the following code is not needed?

...
for i := 0 to high(Model.Tables) do
      VirtualTableExternalRegister(Model, Model.Tables[i],Props,'');
...

thanks

ab · 2011-11-22 15:50:29

VirtualTableExternalRegister() calls are needed to specify the DB connection to be used for each TSQLRecordExternal classes.

corchi72 · 2011-11-23 08:27:10

Sorry but I am not yet clear. I had to insert this line "fClient: = TSQLRestClientDB.Create (Model, nil, 'test.db3', TSQLRestServerDB)," because the tables were created in MSSQL. I do not understand why I need to create a db3 "test.db3" and then work with MSSQL. But where the data are saved?

constructor TFileServer.Create(const ServerName,SeverPort: AnsiString;const aFilename: AnsiString);
var
    i:Integer;
    fClient: TSQLRestClientDB;
    fConnection: TSQLDBConnectionProperties;
    Start, Updated: TTimeLog;
begin
  try


   Props := TOleDBMSSQLConnectionProperties.Create('SQL2008\SQL2008',',MyDB','','');
   inherited Create(CreateFileModel(self));

   for i := 0 to high(Model.Tables) do
      VirtualTableExternalRegister(Model, TSQLRecordExternalClass(Model.Tables[i]),Props,'');



  try
    fClient := TSQLRestClientDB.Create(Model,nil,'test.db3',TSQLRestServerDB);
    Start := fClient.ServerTimeStamp;
    fClient.Server.StaticVirtualTableDirect := StaticVirtualTableDirect;
    fClient.Server.CreateMissingTables;
  finally
    fClient.Free;
  end;

...

thanks

ab · 2011-11-23 10:51:37

You need the SQLite3 file to be able to work with virtual tables: the SQLite3 engine is used to create cross-table requests.

But you can supply ':memory:' in order to write nothing on disk, since it is not needed in your case - i.e. if there is no regular SQLite3 table.

corchi72 · 2011-11-23 11:25:26

ok thanks

now I have these tables and when I Server.CreateMissingTables in SQLite function in MSSQL and an exception occurs when I create the user table or role etc.. I think it's because it refers to the group of roles and these tables do not exist yet.

I can create tables "TSQLRecordMany" and if you like I have to write code?thanks

type

  TSQLUser =Class;
  TSQLRole =Class;
  TSQLGruppo =Class;
  TSQLGroupRoles = class;
  TSQLUserGroups = Class;
  TSQLUserRoles = class;

  TSQLFile = class(TSQLRecordExternal)// class(TSQLRecordSigned)
  private
    Check:Boolean;
  public
    fName: RawUTF8;
    fModified: TTimeLog;
    fCreated: TTimeLog;
    fPicture: TSQLRawBlob;
    fKeyWords: RawUTF8;
    fLabel :RawUTF8;
    fImageIndex: Integer;
    function CheckValues(Reference: TSQLRecord): boolean;virtual;
    function CopyFrom(ARec: TSQLRecord): TSQLRecord;
    procedure CopyTo(var ARec:TSQLRecord);
  published
    property Name: RawUTF8 read fName write fName;
    property Created: TTimeLog read fCreated write fCreated;
    property Modified: TTimeLog read fModified write fModified;
    property Picture: TSQLRawBlob read fPicture write fPicture;
    property KeyWords: RawUTF8 read fKeyWords write fKeyWords;
//    property SignatureTime;
//    property Signature;

    property Label_: RawUTF8 read fLabel write fLabel;
    property ImageIndex: Integer read FImageIndex write FImageIndex;
  end;

  
  TSQLUser = class(TSQLFile)
  private
    fRoles: TSQLUserRoles;
    fLogin, fPassword: RawUTF8;
    fSubName: RawUTF8;
    fGroups: TSQLUserGroups;
  public
    property Roles: TSQLUserRoles read fRoles write fRoles;
    property Groups: TSQLUserGroups read fGroups write fGroups;

  published
    property Login: RawUTF8 read fLogin write fLogin;
    property Password: RawUTF8 read fPassword write SetPassword;
    property SubName: RawUTF8 read fSubName write fSubName;
  end;

  TSQLGruppo = class(TSQLFile)
  private
    fUsers: TSQLUserGroups;
    fRoles: TSQLGroupRoles;
  public

  published
    property Users: TSQLUserGroups read fUsers write fUsers;
    property Roles: TSQLGroupRoles read fRoles write fRoles;
  end;


  TSQLRole = class(TSQLFile)
  private
    fUsers: TSQLUserRoles;
    fGroups: TSQLGroupRoles;
  public

  published
    property Users: TSQLUserRoles read fUsers write fUsers;
    property Groups: TSQLGroupRoles read fGroups write fGroups;
  end;
 
  TSQLUserGroups = class(TSQLRecordMany)
  private
    fValidUntil: TTimeLog;
    fSource: TSQLUser;
    fDest: TSQLGruppo;
  published
    property Source: TSQLUser read fSource;
    property Dest: TSQLGruppo read fDest;
  end;

  TSQLUserRoles = class(TSQLRecordMany)
  private
    fValidUntil: TTimeLog;
    fSource: TSQLUser;
    fDest: TSQLRole;
  published
    property ValidUntil: TTimeLog read fValidUntil write fValidUntil;
    property Source: TSQLUser read fSource;
    property Dest: TSQLRole read fDest;
  end;

ab · 2011-11-23 12:15:51

Tables are created in the order defined in the TSQLModel.
So put first the refered tables.

corchi72 · 2011-11-23 14:46:39

I had already tried, but I think that TSQLRecordMany not work,

ab · 2011-11-24 06:19:42

If I understand you right, TSQLRecordMany can not be set as an external table.
This is a limitation of the current implementation.

But you can use dynamic arrays or TCollection instances with external tables.

corchi72 · 2011-11-24 08:28:20

Too bad because I have created a model that has 48 classes (tables) with only 13 derive from TSQLRecordSigne and all the others derive from TSQLRecordMany, therefore I conclude that I not being able to use the tables of relationship and so I can not use an external db, If I understand you right.

ab · 2011-11-24 10:54:00

Yes, you can use your TSQLRecordMany tables directly with an external DB yet.

About your performance issue, try to guess what is wrong with the current implementation.

corchi72 · 2011-11-24 13:40:41

After many failed attempts carryover below the code on the server side and client-side code, please tell me where wrong because I do not see any records in the database MSSQL.

Thanks
Client side

   /// a HTTP/1.1 client to access SynFile
  TFileClient = class(TSQLite3HttpClient)
  public
    /// initialize the Client for a specified network Server name
    constructor Create(const aServer: AnsiString;SeverPort: AnsiString); reintroduce;

    /// used internaly to retrieve a given action
    function OnSetAction(TableIndex, ToolbarIndex: integer; TestEnabled: boolean;
      var Action): string;
    /// client-side access to the remote RESTful service
    procedure AddAuditTrail(aEvent: TFileEvent; aAssociatedRecord: TSQLRecord);
  end;
...
constructor TFileClient.Create(const aServer: AnsiString;SeverPort: AnsiString);
var
   i:Integer;
   fModel: TSQLModel;

begin
  fModel:= CreateFileModel(self);
  inherited Create(aServer,SeverPort,fModel);
  ForceBlobTransfert := true;

end;

Server side

  /// a server to access SynFile data content
  TFileServer =   class(TSQLRestserverDB)
  private
    fTempAuditTrail: TSQLAuditTrail;
    procedure DeleteAssociatedRelations(aEvent: TFileEvent;
      const aMessage: RawUTF8; aAssociatedRecord: TRecordReference);
    function DeleteField(Table: TSQLRecordClass; Where: integer;
      const FieldName: shortstring; FieldValue: integer;
      ByID: boolean): boolean;
    function AfterDeleteForceCoherency(Table: TSQLRecordClass;
      aID: integer): boolean;override;
  public
    /// the runing HTTP/1.1 server
    Server: TSQLite3HttpServer;
      /// create the database and HTTP/1.1 server
    constructor Create(const ServerName,SeverPort: AnsiString;const aFilename: AnsiString); reintroduce;
      /// release used memory and data
    destructor Destroy; override;
      /// add a row to the TSQLAuditTrail table
    procedure AddAuditTrail(aEvent: TFileEvent; const aMessage: RawUTF8='';
      aAssociatedRecord: TRecordReference=0);
      /// database server-side trigger which will add an event to the
      // TSQLAuditTrail table
    function OnDatabaseUpdateEvent(Sender: TSQLRestServer;
      Event: TSQLEvent; aTable: TSQLRecordClass; aID: integer): boolean;
  published
    /// a RESTful service used from the client side to add an event
      // to the TSQLAuditTrail table
      // - an optional database record can be specified in order to be
      // associated with the event
    function Event(aRecord: TSQLRecord;
      aParameters: PUTF8Char; const aSentData: RawUTF8;
      var aResp, aHead: RawUTF8): Integer;
    function DataAsXML(aRecord: TSQLRecord; aParameters: PUTF8Char;
      const aSentData: RawUTF8; out aResp, aHead: RawUTF8): Integer;
    function DataAsHex(aRecord: TSQLRecord; aParameters: PUTF8Char;
      const aSentData: RawUTF8; out aResp, aHead: RawUTF8): Integer;
    function Sum(aRecord: TSQLRecord; aParameters: PUTF8Char;
      const aSentData: RawUTF8; var aResp, aHead: RawUTF8): Integer;
    function XMLAsData(aRecord: TSQLRecord; aParameters: PUTF8Char;
      const aSentData: RawUTF8; out aResp, aHead: RawUTF8): Integer;
    function PropValue(aRecord: TSQLRecord; aParameters: PUTF8Char;
      const aSentData: RawUTF8; out aResp, aHead: RawUTF8): Integer;
  end;
...

constructor TFileServer.Create(const ServerName,SeverPort: AnsiString;const aFilename: AnsiString);
var
    i:Integer;
    fModel :TSQLModel;
begin
  try

  writeln(format('file server is %s',[aFilename]));
  fModel := CreateFileModel(self);
  Props := TOleDBMSSQLConnectionProperties.Create('SQL2008\SQL2008','MyDB','','');

  for i := 0 to high(FileTabsEx) do
  begin
     if FileTabsEx[i] = TSQLUSer then
        VirtualTableExternalRegister(fModel,FileTabsEx[i],Props,'Utenti')
     else
     VirtualTableExternalRegister(fModel,FileTabsEx[i],Props,'');
  end;

  inherited Create(fModel);

  self.ServerTimeStamp;

  Server := TSQLite3HttpServer.Create(SeverPort,[self],ServerName);

  AddAuditTrail(feServerStarted);
  OnUpdateEvent := OnDatabaseUpdateEvent;

  with Self do
  try

    if ExportServer then
      writeln('Background server is running.'#10) else
      writeln('Error launching the server'#10);

  finally

  end;


  except
    on E: Exception do
    begin
       writeln('Error launching the server' +#10+E.Message);
//     handle initialization error here
    end;
  end;
end;

Main form Client side

...
   Client := TFileClient.Create(ServerName,ServerHttpPort);

ab · 2011-11-30 10:13:48

Did you update the callback service methods signature, by adding the new Session parameter?

http://blog.synopse.info/post/2011/11/2 … -prototype

corchi72 · 2011-11-30 13:19:26

I'm putting a new property AuthUser :TSQLAuthUser in my current class TSQLUser class and then let you know.

thanks

mORMot Open Source

#1 2011-11-22 10:35:36

How to create a MSSQL Database from TSQLModel?

#2 2011-11-22 10:48:18

Re: How to create a MSSQL Database from TSQLModel?

#3 2011-11-22 11:31:07

Re: How to create a MSSQL Database from TSQLModel?

#4 2011-11-22 11:37:06

Re: How to create a MSSQL Database from TSQLModel?

#5 2011-11-22 14:12:41

Re: How to create a MSSQL Database from TSQLModel?

#6 2011-11-22 14:54:59

Re: How to create a MSSQL Database from TSQLModel?

#7 2011-11-22 15:39:40

Re: How to create a MSSQL Database from TSQLModel?

#8 2011-11-22 15:50:29

Re: How to create a MSSQL Database from TSQLModel?

#9 2011-11-23 08:27:10

Re: How to create a MSSQL Database from TSQLModel?

#10 2011-11-23 10:51:37

Re: How to create a MSSQL Database from TSQLModel?

#11 2011-11-23 11:25:26

Re: How to create a MSSQL Database from TSQLModel?

#12 2011-11-23 12:15:51

Re: How to create a MSSQL Database from TSQLModel?

#13 2011-11-23 14:46:39

Re: How to create a MSSQL Database from TSQLModel?

#14 2011-11-24 06:19:42

Re: How to create a MSSQL Database from TSQLModel?

#15 2011-11-24 08:28:20

Re: How to create a MSSQL Database from TSQLModel?

#16 2011-11-24 10:54:00

Re: How to create a MSSQL Database from TSQLModel?

#17 2011-11-24 13:40:41

Re: How to create a MSSQL Database from TSQLModel?

#18 2011-11-30 10:13:48

Re: How to create a MSSQL Database from TSQLModel?

#19 2011-11-30 13:19:26

Re: How to create a MSSQL Database from TSQLModel?

Board footer