Using Database View's instead of Database Table's

razielucas · 2017-01-17 16:59:00

Good afternoon.

I have a question, in the mORMot framework can I use database view's instead of database table's for select my data?

If yes how I can do it?

Thank's a lot.

ab · 2017-01-18 12:20:42

You can define a TSQLRecord class matching the database view.
But of course, it won't create the view, but generate the SQL as expected (SELECT is the same as for a regular table).
Of course, Add/Delete/Update won't work, unless the database supports INSERT/UPDATE/DELETE on views (which is very unlikely, but working on some engines, to some extent).

razielucas · 2017-01-18 12:48:21

Hi.

Return for some help again.
My view name (on MSSQL SERVER) is Clientes.
My class name on APP is defined as TClientes.

When I query from client to server the error above is logged.
The app ignores the view. Any ideas?

20170118 12452356 B EXC ESQLite3Exception {"ErrorCode":1,"SQLite3ErrorCode":"secERROR","Message":"Error SQLITE_ERROR (1) [SELECT codigo, nome, loja FROM Clientes] using 3.15.0 - no such table: Clientes, extended_errcode=1"} at 0063D4FE stack trace API 005634BC 005634E4 0040ACF4

ab · 2017-01-18 14:51:12

Did you define your table as external?

edwinsn · 2017-01-18 14:58:36

ab wrote:

You can define a TSQLRecord class matching the database view.
But of course, it won't create the view, but generate the SQL as expected (SELECT is the same as for a regular table).
Of course, Add/Delete/Update won't work, unless the database supports INSERT/UPDATE/DELETE on views (which is very unlikely, but working on some engines, to some extent).

Oh, it didn't occurred to me that this would work, is this officially supported by the framework? I mean, since it's not officially documented, AFAIK.

Currently, I derive from TSQLTableJSON to define my "view objects".

razielucas · 2017-01-18 15:29:14

How do I define table as External?

ab · 2017-01-18 16:13:01

https://synopse.info/files/html/Synopse … ml#TITL_27
and
https://synopse.info/files/html/Synopse … l#TITL_146
and
https://synopse.info/files/html/api-1.1 … ALREGISTER

jbroussia · 2017-02-05 15:47:49

Hi ab and all,

I'm trying to define a model for a SQLite3 database (using Sample 04 as a starting point).
My database contains views which I want to access from ORM.

I'm following the instructions from https://synopse.info/files/html/Synopse … #TITLE_162
but
1- I'm not sure if I should inherit my class from TSQLRecord or TSQLRecordVirtual ?
2- On Model.CreateMissingTables I get the following error:
Exception class ESQLite3Exception with message 'Error SQLITE_ERROR (1) [CREATE VIRTUAL TABLE match_detail USING Binary(match_id INTEGER, competition_name TEXT COLLATE SYSTEMNOCASE, area1_name TEXT COLLATE SYSTEMNOCASE, team1_name TEXT COLLATE SYSTEMNOCASE, area2_name TEXT COLLATE SYSTEMNOCASE, team2_name TEXT COLLATE SYSTEMNOCASE, date TEXT COLLATE ISO8601);] using 3.16.2 - table match_detail already exists, extended_errcode=1'.

Simplified code is like this:

Model := TSQLModel.Create([TSQLRecordClass1, TSQLRecordClass2], ROOT_NAME);
Model.VirtualTableRegister(TSQLRecordClass2, TSQLVirtualTableBinary); // TSQLRecordClass2 models the view
Server := TSQLRestServerDB.Create(Model, 'mydb.sqlite', TRUE);
Server.CreateMissingTables;
...

What am I dong wrong ?

Thanks.

ab · 2017-02-05 16:26:23

You are mixing things up here.
Virtual tables are only used internally for external databases (e.g. MSSQL or Oracle), or when working with in-memory tables.
No need to define them. The framework will define them from you internally, when needed.

In your case, you are asking to create an in-memory table stored in binary format, and your actual SQLite3 local database has already a real SQLite3 table with the same name.
So it fails creating the virtual table associated with the in-memory table.
Drop the existing SQlite3 database before (or just drop the SQlite3 file).

If your tables are stored in the SQLite3 database, just use TSQLRecord.
If the database has already a view with the TSQLRecord table name, it should work.

There may be a problem in SynSQLite3, so that the views are not seens as existing...
Try to change the code to the following:

  SQL_GET_TABLE_NAMES =
    'SELECT name FROM sqlite_master WHERE type in (''table'',''view'') AND name NOT LIKE ''sqlite_%'';';

ab · 2017-02-05 17:14:26

After code review, I guess the easiest and safest if you want to work with SQLite3 views is to define the new NoCreateMissingTable property for the table in the TSQLModel.
Use

  Model.Props[TSQLRecordName].NoCreateMissingTable  := true;

See https://synopse.info/fossil/info/45427d2406293d16

jbroussia · 2017-02-05 19:01:14

Thank you ab, that solves the "table already exists" problem.

Now I can't seem to be able to retrieve data from my view:
- I don't think TSQLRecordName.Create(Client, someID) is a possibility as, from my limited knowledge, views don't have a ID column ? Well, I tried anyway, but it will return an empty record (with id=0).
- queries like TSQLRecordName.CreateAndFillPrepare don't return anything.

jbroussia · 2017-02-07 09:29:44

Can someone please confirm that accessing SQLite3 Views works from the ORM, and that it is just me doing something wrong ? I'm stuck :'-(

Thanks.

ab · 2017-02-07 13:04:36

If the view has an ID (or RowID) field, it should work as expected.

jbroussia · 2017-02-07 14:55:06

I'm working on a legacy DB of about 500MB so it's not easy to produce a sample that I could post here.
So I'll try to explain what I'm doing with a couple screenshots:

- the DB relational structure (partial):

- and the view I want to query (as you can see , the view has an ID column):

This is the class mapping to that view:

TSQLmatch_detail = class(TSQLRecord)
  private
    // fMatchId: TID;
    fCompetitionName: RawUTF8;
    fArea1Name: RawUTF8;
    fTeam1Name: RawUTF8;
    fArea2Name: RawUTF8;
    fTeam2Name: RawUTF8;
    fDate: TDateTime;
  published
    // property match_id: TID read fMatchId write fMatchId;
    property competition_name: RawUTF8 read fCompetitionName write fCompetitionName;
    property area1_name: RawUTF8 read fArea1Name write fArea1Name;
    property team1_name: RawUTF8 read fTeam1Name write fTeam1Name;
    property area2_name: RawUTF8 read fArea2Name write fArea2Name;
    property team2_name: RawUTF8 read fTeam2Name write fTeam2Name;
    property date: TDateTime read fDate write fDate;
  end;

But queries to the view don't return anything (obviously it's working fine on regular tables).

For ex...

  aMatchDetail := TSQLmatch_detail.Create(Database, 1010055);

...will return an empty object:
{"ID":0,"competition_name":"","area1_name":"","team1_name":"","area2_name":"","team2_name":"","date":""}

1- So, how do I access SQLite3 Views from mORMot ?

...and...

2- How do you map SQLRecord properties to statically linked SQLite3 database column names ?

Thanks

Last edited by jbroussia (2017-02-09 10:27:59)

jbroussia · 2017-02-10 09:31:48

OK, to better illustrate my question, I created a really really simple project (still based on sample 04). Here is the archive containing the code and db file:
https://drive.google.com/file/d/0B1Ycio … sp=sharing

In my sqlite3 db I created a very simple view with an id column. I can query tables, but not the view.
Is it a limitation of the mORMot framework?

jbroussia · 2017-02-12 19:41:05

For anyone interested in the topic, I finally solved my problem by aliasing the ID column name to"RowID".
It wouldn't work with "ID", "id" or anything else.

So a view may be defined like this for example:

SELECT
albums.AlbumId AS RowID, /* <--- IMPORTANT !!! Doesn't work if named or aliased to ID */
albums.Title,
artists.Name
FROM
artists
INNER JOIN albums ON albums.ArtistId = artists.Artistid

sonadorje · 2017-02-13 01:07:18

@jbroussia Good work!

ab · 2017-02-13 07:38:32

Or define the record as external and do proper mapping.

jbroussia · 2017-02-13 10:15:33

I'm really confused ab, how do you define a record as external ? The only reference I could find in the documentation is this: https://synopse.info/files/html/Synopse … l#TITL_145
And the only difference I see with internal records is the use of VirtualTableExternalRegister ? I used VirtualTableExternalRegisterAll in my code and it still wouldn't work without aliasing the ID column to RowId (SQLite3).

Also, proper mapping ? This View:

CREATE VIEW "album_artist" AS 
SELECT
albums.AlbumId AS RowID,
albums.Title,
artists.Name
FROM
artists
INNER JOIN albums ON albums.ArtistId = artists.Artistid

...is mapped to:

TSQLalbum_artist = class(TSQLRecord)
private
  fTitle: RawUTF8;
  fName: RawUTF8;
published
  property Title: RawUTF8 read fTitle write fTitle;
  property Name: RawUTF8 read fName write fName;
end;

Is that wrong ?

jbroussia · 2017-02-15 19:00:46

Update on queries with mORMot on SQLite3 Views...

The above workaround only works with a simple DB.Retrieve(ID, aSQLRecord).
It doesn't work with more advanced queries (CreateAndFillPrepare...)

:-\

I don't know what to do, is it better to use SQL queries after all ?

ab · 2017-02-15 19:40:33

To define the record as external, use a TSQLDBSQLite3Connection instance, as defined in SynDBSQLite3.

Then follow the documentation, like any other SynDB external table in the ORM.
See https://synopse.info/files/html/Synopse … l#TITL_146

jbroussia · 2017-02-16 09:38:01

That's what I'm doing (I think ?)

Here is my code (I hope it's not too long and won't break the forum)...

Server:

program Server;

{$APPTYPE CONSOLE}

uses
  {$I SynDprUses.inc} // use FastMM4 on older Delphi, or set FPC threads
  mORMot, mORMotHttpServer, mORMotSQLite3,
  SynCommons,
  SynDB,
  SynDBSQLite3, SynSQLite3, SynSQLite3Static, mORMotDB,
  chinook;

var
  DBConnProps: TSQLDBSQLite3ConnectionProperties;
  Model: TSQLModel;
  DBServer: TSQLRestServerDB;
  HttpServer: TSQLHttpServer;
begin
  DBConnProps := TSQLDBSQLite3ConnectionProperties.Create('chinook.db', '', '', '');
  try
    Model := CreateModel;
    VirtualTableExternalRegisterAll(Model, DBConnProps);
    Model.Props[TSQLalbum_artist].NoCreateMissingTable := TRUE;
    Model.Props[TSQLartists].ExternalDB.MapField('ID', 'ArtistId');
    Model.Props[TSQLalbums].ExternalDB.MapField('ID', 'AlbumId');
    try
      DBServer := TSQLRestServerDB.Create(Model, 'chinook.db', TRUE);
      try
        DBServer.CreateMissingTables;
        HttpServer := TSQLHttpServer.Create('8080', [DBServer], '+', HTTP_DEFAULT_MODE);
        try
          HttpServer.AccessControlAllowOrigin := '*';
          Writeln(#10'Background HTTP server is running.'#10);
          Writeln('Press [Enter] to close the server.'#10);
          readln;
        finally
          HttpServer.Free;
        end;
      finally
        DBServer.Free;
      end;
    finally
      Model.Free;
    end;
  finally
    DBConnProps.Free;
  end;
end.

Model:

unit chinook;

interface

uses
  SynCommons, mORMot;

type
  TSQLartists = class(TSQLRecord)
  private
    fName: RawUTF8;
  published
    property Name: RawUTF8 read fName write fName;
  end;

  TSQLalbums = class(TSQLRecord)
  private
    fTitle: RawUTF8;
    fArtistId: TSQLartists;
  published
    property Title: RawUTF8 read fTitle write fTitle;
    property ArtistId: TSQLartists read fArtistId write fArtistId;
  end;

  // VIEW
  TSQLalbum_artist = class(TSQLRecord)
  private
    fTitle: RawUTF8;
    fName: RawUTF8;
  published
    property Title: RawUTF8 read fTitle write fTitle;
    property Name: RawUTF8 read fName write fName;
  end;

const
  ROOT_NAME = 'root';

function CreateModel: TSQLModel;
  
implementation

function CreateModel: TSQLModel;
begin
  Result := TSQLModel.Create([TSQLartists,
                              TSQLalbums,
                              TSQLalbum_artist],
                              ROOT_NAME);
end;

end.

And on the client side:

procedure TfrmClient.btnTestClick(Sender: TObject);
var
  aAlbumArtist: TSQLalbum_artist;
begin
  // Test 1: OK
  aAlbumArtist := TSQLalbum_artist.Create(Database, 1);
  // aAlbumArtist := TSQLalbum_artist.Create;
  // Database.Retrieve(1, aAlbumArtist);
  try
    mmoResult.Lines.Add(UTF8ToString(ObjectToJSON(aAlbumArtist)));
  finally
    aAlbumArtist.Free;
  end;

  // Test 2: NOT OK
  aAlbumArtist := TSQLalbum_artist.CreateAndFillPrepare(Database, 'RowID < ?', [10]);
  // aAlbumArtist := TSQLalbum_artist.CreateAndFillPrepare(Database, 'ID < ?', [10]);
  try
    with mmoResult.Lines do begin
      BeginUpdate;
      while aAlbumArtist.FillOne do
        Add(UTF8ToString(ObjectToJSON(aAlbumArtist)));
      EndUpdate;
    end;
  finally
    aAlbumArtist.Free;
  end;
end;

jbroussia · 2017-02-17 09:22:19

Abstract:

I want to query some SQLite3 View from ORM:
- I defined a TSQLRecord class matching the View, the first column of the View is an ID column
- I defined my DB as external by using TSQLDBSQLite3ConnectionProperties
- I registered my tables with VirtualTableExternalRegisterAll
- and I used NoCreateMissingTable for my View

Now I still can't query my View (only "SELECT" statements obviously):
- If the ID column is aliased to RowID:
* I can use TSQLRest.Retrieve(aClass, aID)
* But "complex" queries don't work (CreateAndFillPrepare...) -> no error, but empty result
- Else (no alias or different than RowID):
* Queries don't work at all -> no error, empty result

Does anybody have any experience with SQLite3 Views and accessing them with mORMot ? I'd like to know if I'm doing something wrong, or if it's a limitation of SQLite3, a limitation of the framework, or even a bug ? :-\
(@razielucas: did you have any success with your Views on MSSQL ?)

Thanks !

jbroussia · 2017-02-19 08:57:57

Sorry to insist but I've been stuck on this for 2 weeks :'-(

ab: is it a limitation of the framework ?

Thanks.

ab · 2017-02-19 09:09:29

It is an unsupported/untested feature, as I wrote.

Did you map your external field ID?

Sounds like if the SQL uses the virtual table mechanism - which is not intended here.
See https://synopse.info/files/html/Synopse … ml#TITL_20
and https://synopse.info/files/html/Synopse … ml#TITL_30

Try to find out why your CreateAndFillPrepare request SQL is not recognized in TSQLRestStorageExternal.AdaptSQLForEngineList, or what is the actual SQL executed by the SynDB layer.

You also may try to map the primary key to two fields, one named ID and the other RowID...

jbroussia · 2017-02-19 10:58:34

Hi ab,

Yes I tried mapping the ID of the view with...

Model.Props[TSQLalbum_artist].ExternalDB.MapField('ID', 'AlbumId');

Could it be that the way I define my DB as external, or map the field names, is incorrect (see code a couple messages above) ?
When accessing an external DB with mapped field names, even querying "normal" tables (not Views) doesn't work for me:

aAlbum := TSQLalbums.Create(Database, 1);
// dosomethingwith(aAlbum) // OK

aAlbum := TSQLalbums.CreateAndFillPrepare(Database, 'ID<?', [10]); // AlbumID field is mapped to ID
// while aAlbum.FillOne do
  // dosomethingwith(aAlbum)  // EMPTY

Very probable that I'm doing something wrong but can't spot what !

Last edited by jbroussia (2017-02-19 13:51:31)

ab · 2017-02-19 15:48:39

As I proposed above:

Try to find out why your CreateAndFillPrepare request SQL is not recognized in TSQLRestStorageExternal.AdaptSQLForEngineList, or what is the actual SQL executed by the SynDB layer.

jbroussia · 2017-02-21 10:32:29

Well, that's the reason why I think I'm not doing things as intended: the debugger never reaches the breakpoint I set in TSQLRestStorageExternal.AdaptSQLForEngineList

(I get an exception in SynSQLite3.sqlite3_check where the resulting SQL is: 'SELECT ID,Title,Name FROM album_artist WHERE ID < ?')

ab · 2017-02-21 10:35:33

So your Database instance may not have the table TSQLalbum_artist declared as external in the SQLite3 DB, and the fields mapped as expected.

jbroussia · 2017-02-21 12:05:24

Is that pseudo code correct to declare my sqlite3 db as external, and map the "AlbumId" field of the album_artist view to "ID" ?

Props := TSQLDBSQLite3ConnectionProperties.Create('mydb.db', '', '', '');
Model := TSQLModel.Create([TSQLalbum_artist], ROOT_NAME);
VirtualTableExternalRegister(Model, TSQLalbum_artist, Props);
Model.Props[TSQLalbum_artist].NoCreateMissingTable := TRUE;         // album_artist is a View
Model.Props[TSQLalbum_artist].ExternalDB.MapField('ID', 'AlbumId'); // Map field AlbumId to ID
DB := TSQLRestServerDB.Create(Model, 'mydb.db', TRUE);

ab · 2017-02-21 15:40:03

Perhaps you may reuse the SQlite3 instance of Props for the DB (i.e. use Props.MainSQLite3DB instead of 'mydb.db' for TSQLRestServerDB.Create).
There should be a single exclusive low-level TSQLDataBase instance to access the shared mydb.db file.

And call DB.CreateMissingTables.

afarias · 2017-02-21 17:23:20

I have the same issue. The generated SQL is fine (and works as expected returnig 1 row if run against the DB) but a 404 error "not found" is written to log.

20170221 17103709 B SQL SynDBFireDAC.TSQLDBFireDACStatement(03744180) select ID,EmployeeName,EmployeeRole,AdmissionDate from vw_Employee where ID=1
20170221 17103711 A debug mORMotSQLite3.TSQLRestServerDB(0372D470) TSQLRestRoutingREST.Error: { "errorCode":404, "errorText":"Not Found" }
20170221 17103711 A srvr mORMotSQLite3.TSQLRestServerDB(0372D470) GET root/Employee ORM-Get -> 404 with outlen=47 in 173806 us
20170221 17103711 ! clnt mORMotHttpClient.TSQLHttpClientWinHTTP(036D30D0) GET root/Employee/1 status=404 len=47 state=5
20170221 17103711 ! ERROR mORMotHttpClient.TSQLHttpClientWinHTTP(036D30D0) GET root/Employee/1 returned 404 (Not Found) with message { "errorCode":404, "errorText":"Not Found" }

Here's the code I'm using:

  fModel := CreateModel;

  fDriver := TFDPhysIBDriverLink.Create(nil);
  fDriver.VendorLib := 'fbclient.dll';

  URI := FIREDAC_PROVIDER[dFirebird];
  fProperties := TSQLDBFireDACConnectionProperties.Create(
      URI, DB_PATH, DB_USER, DB_PASS);

  VirtualTableExternalRegisterAll(fModel, fProperties);
  VirtualTableExternalMap(fModel, TEmployee, fProperties, 'VW_EMPLOYEE');

  fRestServer := TSQLRestServerDB.Create(fModel, SQLITE_MEMORY_DATABASE_NAME, False);
  fRestServer.AcquireExecutionMode[execORMGet] := amBackgroundThread;
  fRestServer.AcquireExecutionMode[execORMWrite] := amBackgroundThread;
  fRestServer.CreateMissingTables;

  (..)

If I just change the view name 'VW_EMPLOYEE' with the table name 'EMPLOYEE' than it works.

Regards,

Last edited by afarias (2017-02-21 19:21:17)

ab · 2017-02-21 18:12:11

There is no mapping for your external view...

afarias · 2017-02-21 19:19:46

ab wrote:

There is no mapping for your external view...

Yes, just for the view name:

VirtualTableExternalMap(fModel, TEmployee, fProperties, 'VW_EMPLOYEE');

Field names match the TEmployee class property names.

Last edited by afarias (2017-02-21 19:21:33)

jbroussia · 2017-02-22 10:57:19

ab wrote:

Perhaps you may reuse the SQlite3 instance of Props for the DB (i.e. use Props.MainSQLite3DB instead of 'mydb.db' for TSQLRestServerDB.Create).
There should be a single exclusive low-level TSQLDataBase instance to access the shared mydb.db file.

Yes, thanks, I was wondering why I had two references to the DB file...

And call DB.CreateMissingTables.

Forgot to put it in the pseudo-code, but indeed I'm calling it in my actual code.

After trying many many things, I think the main problem I have is with the way I declare/use external tables. The field name mapping doesn't work, most probably as a result of me doing something wrong with external table declaration Truth be told, for now I don't need this feature (mapping), and I can use an internal DB, but as I'm learning how to use the mORMot framework, I like to understand what I'm doing incorrectly.
Then there is another little problem in accessing SQLite Views: DB.Retrieve and DB.Create(TSQLRecord, ID) don't work, but this is easily circumvented by using CreateAndFillPrepare instead. I think this is because DB.Retrieve/Create internally use the "RowID" column but my View only has an ID field, whicjh I can query with CreateAndFillPrepare.

Last edited by jbroussia (2017-02-23 07:57:41)

luscen · 2017-03-22 16:03:22

me too, i have the same problem，i can not find the solution now.

Last edited by luscen (2017-03-23 02:30:54)

mORMot Open Source

#1 2017-01-17 16:59:00

Using Database View's instead of Database Table's

#2 2017-01-18 12:20:42

Re: Using Database View's instead of Database Table's

#3 2017-01-18 12:48:21

Re: Using Database View's instead of Database Table's

#4 2017-01-18 14:51:12

Re: Using Database View's instead of Database Table's

#5 2017-01-18 14:58:36

Re: Using Database View's instead of Database Table's

#6 2017-01-18 15:29:14

Re: Using Database View's instead of Database Table's

#7 2017-01-18 16:13:01

Re: Using Database View's instead of Database Table's

#8 2017-02-05 15:47:49

Re: Using Database View's instead of Database Table's

#9 2017-02-05 16:26:23

Re: Using Database View's instead of Database Table's

#10 2017-02-05 17:14:26

Re: Using Database View's instead of Database Table's

#11 2017-02-05 19:01:14

Re: Using Database View's instead of Database Table's

#12 2017-02-07 09:29:44

Re: Using Database View's instead of Database Table's

#13 2017-02-07 13:04:36

Re: Using Database View's instead of Database Table's

#14 2017-02-07 14:55:06

Re: Using Database View's instead of Database Table's

#15 2017-02-10 09:31:48

Re: Using Database View's instead of Database Table's

#16 2017-02-12 19:41:05

Re: Using Database View's instead of Database Table's

#17 2017-02-13 01:07:18

Re: Using Database View's instead of Database Table's

#18 2017-02-13 07:38:32

Re: Using Database View's instead of Database Table's

#19 2017-02-13 10:15:33

Re: Using Database View's instead of Database Table's

#20 2017-02-15 19:00:46

Re: Using Database View's instead of Database Table's

#21 2017-02-15 19:40:33

Re: Using Database View's instead of Database Table's

#22 2017-02-16 09:38:01

Re: Using Database View's instead of Database Table's

#23 2017-02-17 09:22:19

Re: Using Database View's instead of Database Table's

#24 2017-02-19 08:57:57

Re: Using Database View's instead of Database Table's

#25 2017-02-19 09:09:29

Re: Using Database View's instead of Database Table's

#26 2017-02-19 10:58:34

Re: Using Database View's instead of Database Table's

#27 2017-02-19 15:48:39

Re: Using Database View's instead of Database Table's

#28 2017-02-21 10:32:29

Re: Using Database View's instead of Database Table's

#29 2017-02-21 10:35:33

Re: Using Database View's instead of Database Table's

#30 2017-02-21 12:05:24

Re: Using Database View's instead of Database Table's

#31 2017-02-21 15:40:03

Re: Using Database View's instead of Database Table's

#32 2017-02-21 17:23:20

Re: Using Database View's instead of Database Table's

#33 2017-02-21 18:12:11

Re: Using Database View's instead of Database Table's

#34 2017-02-21 19:19:46

Re: Using Database View's instead of Database Table's

#35 2017-02-22 10:57:19

Re: Using Database View's instead of Database Table's

#36 2017-03-22 16:03:22

Re: Using Database View's instead of Database Table's

Board footer