Pages: 1

#1 2014-10-30 15:22:47

fabvit
Member
Registered: 2014-10-27
Posts: 9

Can one use mormot with legacy Firebird tables?

Having read the SAD manual and several posts about the mormot framework I cannot understand if I can use legacy tables with mormot.

For example if I have a firebird table where its primary key is composed by several fields instead of the surrogate ID key field, how do I tell mormot to cope with this multi-field primary key? May be there is a way, in mormot, to configure a multi-field primary.

I've tried using firebird external tables in mormot. As far as I've investigated I've seen that every class property declared  as string, will be translated in to a BLOB field in the corresponding Firebird extenal table: this surprises me a lot (I'm not a big fan of BLOB fields). Is there in mormot something to configure a string property of a class to be represented as VARCHAR or as CHAR in Firebird?

thank you so much

fabio vitale

Offline

#2 2014-10-30 15:42:26

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,660
Website

Re: Can one use mormot with legacy Firebird tables?

No, there is no way to define such a multi-field primary key.
Primary keys are expected to be an integer.
This is due to several restrictions outside of mORMot, the main one being that SQLite3 virtual tables (the mechanism which is at the kernel of our ORM for external tables), expects primary keys to be defined as integer.

So you cannot use directly such legacy tables.
For them, you should better re-use your existing SQL statement, if possible using the SynDB classes instead of your former library.
In fact, in SynDB you would be able to return directly some JSON from the DB, and have a statement cache.
Then wrap your SQL queries into a persistent service, if possible using interfaces.

Please take care of the documentation: you have to specify a field width if you use an external table, by adding "index .." to the published field.
For instance:

    property FirstName: RawUTF8 index 40 read fFirstName write fFirstName;
    property LastName: RawUTF8 index 40 read fLastName write fLastName;
    property Data: TSQLRawBlob read fData write fData;

This will create VARCHAR(40) columns, as you expect.
Without this 'index ...', the ORM will estimate that the field is a CLOB, without any maximum size.

Offline

#3 2014-10-30 15:54:01

miab3
Member
From: Poland
Registered: 2014-10-01
Posts: 188

Re: Can one use mormot with legacy Firebird tables?

For me "30 - MVC Server" and ZEOS 7.2 such database in Firebird 2.53 has created:

SET SQL DIALECT 3;

SET NAMES UTF8;

CREATE DATABASE 'MVCSERVERFIREBIRD.FDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 4096
DEFAULT CHARACTER SET UTF8 COLLATION UTF8;



/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/



CREATE TABLE ARTICLE (
    ID              INTEGER NOT NULL,
    CREATEDAT       BIGINT,
    MODIFIEDAT      BIGINT,
    TITLE           VARCHAR(80),
    CONTENT         BLOB SUB_TYPE 1 SEGMENT SIZE 2000,
    AUTHOR          BIGINT,
    AUTHORNAME      VARCHAR(50),
    PUBLISHEDMONTH  BIGINT,
    ABSTRACT        VARCHAR(1024),
    TAGS            BLOB SUB_TYPE 0 SEGMENT SIZE 2000
);

CREATE TABLE ARTICLESEARCH (
    ID    INTEGER NOT NULL,
    TEXT  BLOB SUB_TYPE 1 SEGMENT SIZE 2000
);

CREATE TABLE AUTHOR (
    ID              INTEGER NOT NULL,
    CREATEDAT       BIGINT,
    MODIFIEDAT      BIGINT,
    LOGONNAME       VARCHAR(30) NOT NULL,
    FIRSTNAME       VARCHAR(50),
    FAMILYNAME      VARCHAR(50),
    BIRTHDATE       TIMESTAMP,
    EMAIL           VARCHAR(40),
    HASHEDPASSWORD  VARCHAR(64),
    VERIFIED        BIGINT,
    RIGHTS          BIGINT
);

CREATE TABLE BLOGINFO (
    ID           INTEGER NOT NULL,
    TITLE        VARCHAR(80),
    LANGUAGE     VARCHAR(3),
    DESCRIPTION  VARCHAR(120),
    COPYRIGHT    VARCHAR(80),
    ABOUT        BLOB SUB_TYPE 1 SEGMENT SIZE 2000
);

CREATE TABLE COMMENT (
    ID          INTEGER NOT NULL,
    CREATEDAT   BIGINT,
    MODIFIEDAT  BIGINT,
    TITLE       VARCHAR(80),
    CONTENT     BLOB SUB_TYPE 1 SEGMENT SIZE 2000,
    AUTHOR      BIGINT,
    AUTHORNAME  VARCHAR(50),
    ARTICLE     BIGINT
);

CREATE TABLE TAG (
    ID         INTEGER NOT NULL,
    IDENT      BLOB SUB_TYPE 1 SEGMENT SIZE 2000,
    OCCURENCE  BIGINT,
    CREATEDAT  BIGINT
);



/******************************************************************************/
/****                          Unique Constraints                          ****/
/******************************************************************************/

ALTER TABLE AUTHOR ADD UNIQUE (LOGONNAME);


/******************************************************************************/
/****                             Primary Keys                             ****/
/******************************************************************************/

ALTER TABLE ARTICLE ADD PRIMARY KEY (ID);
ALTER TABLE ARTICLESEARCH ADD PRIMARY KEY (ID);
ALTER TABLE AUTHOR ADD PRIMARY KEY (ID);
ALTER TABLE BLOGINFO ADD PRIMARY KEY (ID);
ALTER TABLE COMMENT ADD PRIMARY KEY (ID);
ALTER TABLE TAG ADD PRIMARY KEY (ID);


/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/

CREATE INDEX NDXARTICLEAUTHOR ON ARTICLE (AUTHOR);
CREATE UNIQUE DESCENDING INDEX NDXARTICLEID ON ARTICLE (ID);
CREATE INDEX NDXARTICLEPUBLISHEDMONTH ON ARTICLE (PUBLISHEDMONTH);
CREATE UNIQUE DESCENDING INDEX NDXARTICLESEARCHID ON ARTICLESEARCH (ID);
CREATE UNIQUE DESCENDING INDEX NDXAUTHORID ON AUTHOR (ID);
CREATE UNIQUE DESCENDING INDEX NDXBLOGINFOID ON BLOGINFO (ID);
CREATE INDEX NDXCOMMENTARTICLE ON COMMENT (ARTICLE);
CREATE INDEX NDXCOMMENTAUTHOR ON COMMENT (AUTHOR);
CREATE UNIQUE DESCENDING INDEX NDXCOMMENTID ON COMMENT (ID);
CREATE UNIQUE DESCENDING INDEX NDXTAGID ON TAG (ID);

Michal

Offline

#4 2014-10-30 16:05:46

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,660
Website

Re: Can one use mormot with legacy Firebird tables?

Yes, this is as expected by how the TSQL* classes have been defined.
The Content field is defined as RawUTF8 without "index", so is defined as a TEXT BLOB, as expected.

If you want, you just can define:

  TSQLContent = class(TSQLRecordTimeStamped)
  ...
    property Content: RawUTF8 index 32768 read fContent write fContent;

and you will have a VARCHAR(32768) column definition.
But the user won't be able to post more than 32768 UTF-8 characters... which may be less than expected for a blog post, sadly.

Offline

#5 2014-10-30 17:17:44

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 357

Re: Can one use mormot with legacy Firebird tables?

@fabvit
Yes, I can and you can. But I have to confess my tables don't have multi field-primary indexes.

If you have a special database, you have to know, you don't need the ORM of mORMot. You can use only interface based services.

E.g.

procedure TKontenService.GetKontostand(const datum: RAWUTF8; out res: RawUTF8);
var
  tKonten: ISQLDBRows;
  y, m, d: Integer;
  a: TStringDynArray;
begin
  a:= TStringDynArray(SplitString(datum, '-'));
  if (Length(a) < 3) then exit;
  y:= StrToInt(a[0]);
  m:= StrToInt(a[1]);
  d:= StrToInt(a[2]);

  res:= '';
  tKonten:= Props.Execute('select * from p_kontostand(?, ?)', [DateToSQL(EncodeDate(y,m,d)), 'EUR']);
  res:= tKonten.FetchAllAsJSON(true);
end;

This service calls a stored procedure. You can't call a procedure with mORMot by default. But in your own service you can do all what you want to do with sql.

Offline

#6 2014-10-30 19:14:47

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,660
Website

Re: Can one use mormot with legacy Firebird tables?

You can use RawJSON output parameter instead of RawUtf8 to return a true json array.

Offline

#7 2014-10-30 19:49:35

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 357

Re: Can one use mormot with legacy Firebird tables?

@ab
Thanks, I will try it.

Offline

#8 2014-10-31 08:51:35

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,660
Website

Re: Can one use mormot with legacy Firebird tables?

@miab3

For your interest, I've just changed how foreign keys columns are defined: they will now be defined as 32 bit integer instead of 64 bit integer.
The same for enumerate/boolean or RecordRef fields.
It won't make a huge performance nor storage size difference, but sounded somewhat cleaner.
See http://synopse.info/fossil/info/dcbca02962e11

Offline

#9 2014-10-31 11:01:14

miab3
Member
From: Poland
Registered: 2014-10-01
Posts: 188

Re: Can one use mormot with legacy Firebird tables?

@ab

r 1.18.447, Zeos 7.2-beta r3438, Firebird 2.53

Indeed, there has been a change in the Firebird database:

SET SQL DIALECT 3;

SET NAMES UTF8;

CREATE DATABASE 'MVCSERVERFIREBIRD.FDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 4096
DEFAULT CHARACTER SET UTF8 COLLATION UTF8;



/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/



CREATE TABLE ARTICLE (
    ID              INTEGER NOT NULL,
    CREATEDAT       BIGINT,
    MODIFIEDAT      BIGINT,
    TITLE           VARCHAR(80),
    CONTENT         BLOB SUB_TYPE 1 SEGMENT SIZE 2000,
    AUTHOR          INTEGER,
    AUTHORNAME      VARCHAR(50),
    PUBLISHEDMONTH  BIGINT,
    ABSTRACT        VARCHAR(1024),
    TAGS            BLOB SUB_TYPE 0 SEGMENT SIZE 2000
);

CREATE TABLE ARTICLESEARCH (
    ID    INTEGER NOT NULL,
    TEXT  BLOB SUB_TYPE 1 SEGMENT SIZE 2000
);

CREATE TABLE AUTHOR (
    ID              INTEGER NOT NULL,
    CREATEDAT       BIGINT,
    MODIFIEDAT      BIGINT,
    LOGONNAME       VARCHAR(30) NOT NULL,
    FIRSTNAME       VARCHAR(50),
    FAMILYNAME      VARCHAR(50),
    BIRTHDATE       TIMESTAMP,
    EMAIL           VARCHAR(40),
    HASHEDPASSWORD  VARCHAR(64),
    VERIFIED        INTEGER,
    RIGHTS          BIGINT
);

CREATE TABLE BLOGINFO (
    ID           INTEGER NOT NULL,
    TITLE        VARCHAR(80),
    LANGUAGE     VARCHAR(3),
    DESCRIPTION  VARCHAR(120),
    COPYRIGHT    VARCHAR(80),
    ABOUT        BLOB SUB_TYPE 1 SEGMENT SIZE 2000
);

CREATE TABLE COMMENT (
    ID          INTEGER NOT NULL,
    CREATEDAT   BIGINT,
    MODIFIEDAT  BIGINT,
    TITLE       VARCHAR(80),
    CONTENT     BLOB SUB_TYPE 1 SEGMENT SIZE 2000,
    AUTHOR      INTEGER,
    AUTHORNAME  VARCHAR(50),
    ARTICLE     INTEGER
);

CREATE TABLE TAG (
    ID         INTEGER NOT NULL,
    IDENT      VARCHAR(80),
    OCCURENCE  BIGINT,
    CREATEDAT  BIGINT
);



/******************************************************************************/
/****                          Unique Constraints                          ****/
/******************************************************************************/

ALTER TABLE AUTHOR ADD UNIQUE (LOGONNAME);


/******************************************************************************/
/****                             Primary Keys                             ****/
/******************************************************************************/

ALTER TABLE ARTICLE ADD PRIMARY KEY (ID);
ALTER TABLE ARTICLESEARCH ADD PRIMARY KEY (ID);
ALTER TABLE AUTHOR ADD PRIMARY KEY (ID);
ALTER TABLE BLOGINFO ADD PRIMARY KEY (ID);
ALTER TABLE COMMENT ADD PRIMARY KEY (ID);
ALTER TABLE TAG ADD PRIMARY KEY (ID);


/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/

CREATE INDEX NDXARTICLEAUTHOR ON ARTICLE (AUTHOR);
CREATE UNIQUE DESCENDING INDEX NDXARTICLEID ON ARTICLE (ID);
CREATE INDEX NDXARTICLEPUBLISHEDMONTH ON ARTICLE (PUBLISHEDMONTH);
CREATE UNIQUE DESCENDING INDEX NDXARTICLESEARCHID ON ARTICLESEARCH (ID);
CREATE UNIQUE DESCENDING INDEX NDXAUTHORID ON AUTHOR (ID);
CREATE UNIQUE DESCENDING INDEX NDXBLOGINFOID ON BLOGINFO (ID);
CREATE INDEX NDXCOMMENTARTICLE ON COMMENT (ARTICLE);
CREATE INDEX NDXCOMMENTAUTHOR ON COMMENT (AUTHOR);
CREATE UNIQUE DESCENDING INDEX NDXCOMMENTID ON COMMENT (ID);
CREATE UNIQUE DESCENDING INDEX NDXTAGID ON TAG (ID);

I noticed that TITLE and CONTENT in COMMENT can not be a number, because throwing an error.

Michal

Last edited by miab3 (2014-10-31 11:01:58)

Offline

#10 2014-10-31 11:13:02

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,660
Website

Re: Can one use mormot with legacy Firebird tables?

miab3 wrote:

I noticed that TITLE and CONTENT in COMMENT can not be a number, because throwing an error.

Those are text fields, so does make sense, right?

Offline

#11 2014-10-31 11:21:19

miab3
Member
From: Poland
Registered: 2014-10-01
Posts: 188

Re: Can one use mormot with legacy Firebird tables?

@ab

Yes, but it would be safer if they did not cause an error.
Why it happens?

Michal

Offline

#12 2014-10-31 11:52:08

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,660
Website

Re: Can one use mormot with legacy Firebird tables?

Which error are you talking about?

Offline

#13 2014-10-31 12:10:31

miab3
Member
From: Poland
Registered: 2014-10-01
Posts: 188

Re: Can one use mormot with legacy Firebird tables?

@ab

If you type the text look much like a number, eg. 123 in TITLE or CONTENT field,
after  Add Comment  error is generated.
(It's about 30 - MVC Server)


Error Page

Low-level #500 Error occurred with the following message:

HTTP Error 500 - Internal Server Error

The following Exception did occur:

EMVCException raised in TMVCRendererFromViews.ExecuteCommand: TMVCRendererFromViews.CommandRunMethod: IBlogApplication.ArticleComment() execution error

Error context:

{
  "main": {
    "pageName": "ArticleComment",
    "blog": {
      "Title": "mORMot BLOG",
      "Language": "en",
      "Description": "Sample Blog Web Application using Synopse mORMot MVC",
      "Copyright": "&copy;2014 <a href=http://synopse.info>Synopse Informatique</a>",
      "About": "Bla? Ble, bla ble, bli bla ble blu ble. Blo blu. Bla ble, blu bla blu blu bli blu blu bla, bli blo blo blo bli bli blu blo.\r\nBlo bla!"
    },
    "session": {
      "AuthorName": "synopse",
      "AuthorID": 1,
      "AuthorRights": {
        "canComment": true,
        "canPost": true,
        "canDelete": true,
        "canAdministrate": true
      },
      "id": 1
    },
...

Michal

Last edited by miab3 (2014-10-31 12:12:02)

Offline

#14 2014-10-31 12:31:55

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,660
Website

Re: Can one use mormot with legacy Firebird tables?

Yes, the interface execution routine was very strict about incoming values.
I've allowed this case.

See http://synopse.info/fossil/info/4cf6a8959e

Thanks for the feedback - I never observed that!

Offline

#15 2014-10-31 13:02:55

miab3
Member
From: Poland
Registered: 2014-10-01
Posts: 188

Re: Can one use mormot with legacy Firebird tables?

@ab

Thanks.
Well, now it works.
Now you can type numerical poppycock. wink

Michal

Last edited by miab3 (2014-10-31 13:11:10)

Offline

Pages: 1

Board footer

Powered by FluxBB