Table creation in MongoDB

Shadownildo · 2019-05-29 14:13:37

Hi There, I'm developing a Multi Database Server, and MongoDB is one of the last guys i need to implement, but i'm having some trouble to Define the SQLRecords Tables in the database,
i already Read the Documentation and search around here and nothing have helped, so i'm making this topic
i have this SQLRecord

  TSQLTestTable = class(TSQLRecord)
  private
    FAge: Integer;
    FName: RawUTF8;
    FAdress: RawUTF8;
  published
    property Name: RawUTF8 read FName write FName;
    property Age: Integer index 4 read FAge write FAge stored AS_UNIQUE;
    property Adress: RawUTF8 index 100 read FAdress write FAdress;
  end;

I'm using StaticMongoDBRegisterAll() to Register all the tables im SQLModel, but the fields are not
persist in the database, Only Age because is Stored AS_UNIQUE, but i need to know if there is a way i can have all the fields in the Collection, i'm missing something? MongoDB it's not suppose to have all the fields?

ab · 2019-05-29 15:40:34

Could you try to run and execute sample 24 - aka MongoDBTests.dpr ?
On my place, no problem persisting the TSQLORM class:

Using MongoDB 2.6.1 V8
Generated with: Delphi 7 32 bit compiler

Time elapsed for all tests: 691.07ms
Performed 2019-05-29 17:38:56 by User on ACER

Total assertions failed for all test suits:  0 / 25,998
! All tests passed successfully.

Then put some code to reproduce your problem in a gist - not directly in the forum message.

Shadownildo · 2019-05-29 18:48:56

ab wrote:

Could you try to run and execute sample 24 - aka MongoDBTests.dpr ?
On my place, no problem persisting the TSQLORM class:
Using MongoDB 2.6.1 V8
Generated with: Delphi 7 32 bit compiler

Time elapsed for all tests: 691.07ms
Performed 2019-05-29 17:38:56 by User on ACER

Total assertions failed for all test suits:  0 / 25,998
! All tests passed successfully.
Then put some code to reproduce your problem in a gist - not directly in the forum message.

I got Two errors, on this Sample
https://files.slack.com/files-pri/T7MCR … /image.png

and part of the code is this

   begin
        FServer := TDBInstance.Create(FServerInfos.Config.GetRoot);
        StaticMongoDBRegisterAll(FServer, FFactory.ConnectionNativeDBFactory.GetMongoDB.GetMongoDB.Open(FServerInfos.Config.GetDatabasepath));
        FServices := TService.Create(FServer);
        FHTTPServer := THTTPInstance.Create(FServerInfos.Config.GetPortmORMot, [FServer], '+', useHttpApiRegisteringURI);
      end;

There is were i create all. FFactory.ConnectionNativeDBFactory.GetMongoDB.GetMongoDB is a Factory that returns for me a TMongoClient, and i think the rest is self-explain

and this is the Create of the FServer
https://pastebin.com/K3rF0NS0

the SQLModel is this

function DataModel(Root: string): TSQLModel;
begin
  Result := TSQLModel.Create([TSQLAuthGroup, TSQLAuthUser, TSQLTestTable], Root);
end;

Thanks,
Shadownildo

ab · 2019-05-29 20:03:34

About the errors in sample 24, I can't see the image.png (not public access).

You call CreateMissingTables before StaticMongoDBRegisterAll() which is wrong.
The tables are therefore created before MongoDB is registered.

Shadownildo · 2019-05-29 20:24:27

ab wrote:

About the errors in sample 24, I can't see the image.png (not public access).
You call CreateMissingTables before StaticMongoDBRegisterAll() which is wrong.
The tables are therefore created before MongoDB is registered.

Oh, i'm sorry about that, but the errors are

! ORM with acknowledge - Retrieve from SQL
! Exception EVariantInvalidOpError raised with Message :
! Invalid variant operation

Total Failed : 1 / 11.597 - ORM with acknowledge FAILED 6.87s

2.2. ORM without acknowledge

! ORM with acknowledge - Retrieve from SQL
! Exception EVariantInvalidOpError raised with Message :
! Invalid variant operation


Total Failed : 1 / 11.597 - ORM Without acknowledge FAILED 6.87s

About the Call CreateMissingTables is because the method is for a Generic Creation of a Rest Server DB, so i use for SQLite Native, SQLite Firedac, Firebird, Postgres, MYSQL and some of they use this call, and i know that has no impact on MongoDB, what you suggest ab ?

ab · 2019-05-30 08:48:12

Are you using the latest version of the framework? Currently it is 1.18.5233.
What is your compiler?
What is your MongoDB revision?
My guess is that some access right problem to your MongoDB instance.

CreateMissingTables should be called AFTER registering the external SQL connections, or MongoDB.
Modify your code to do such.

Shadownildo · 2019-05-30 11:58:09

ab wrote:

Are you using the latest version of the framework? Currently it is 1.18.5233.
What is your compiler?
What is your MongoDB revision?
My guess is that some access right problem to your MongoDB instance.
CreateMissingTables should be called AFTER registering the external SQL connections, or MongoDB.
Modify your code to do such.

i'm in version 1.18.5232
I'm using Delphi Berlin 10.1
my mongoDB is 4.0.9 Community, and i'm using mongodb Compass to access the data

You say that i need to Call CreateMissingTables after the registering, but the Server Instance do not have a access to the Mongo Instance , it's not like Other databases like Postgres , Firebird where i inform my Props and start the server with those infos , i start the server, and then i link the Mongo Instance with the Server, or i misunderstood?, but i was debugging inside the StaticMongoDBRegisterAll() , and i notice that the Table is Found out , and he tries to Register all the fields , but for some reason it's not created

He Finds the Collection
https://imgur.com/LVYvZ3v

When tries to find the fields in the first BSONProjectionSet he founds
https://imgur.com/qnOTeYt

but in the Second one, only _id is found out and setted as 0, don't know why.
https://imgur.com/o7MQWnW

https://imgur.com/aCc1CMm

Shadownildo · 2019-05-30 12:57:02

ab wrote:

Are you using the latest version of the framework? Currently it is 1.18.5233.
What is your compiler?
What is your MongoDB revision?
My guess is that some access right problem to your MongoDB instance.
CreateMissingTables should be called AFTER registering the external SQL connections, or MongoDB.
Modify your code to do such.

I Tried now, to do what you suggest to Call CreateMissingTables After Register The Mongo, but same results

constructor TDBInstance.Create(Root: string; MongoDB: TMongoDatabase);
begin
    aModel := DataModel(Root);
    inherited Create(aModel, SQLITE_MEMORY_DATABASE_NAME);
    StaticMongoDBRegisterAll(Self, MongoDB);
    Self.AcquireExecutionMode[execORMGet] := amBackgroundORMSharedThread;
    Self.AcquireExecutionMode[execORMWrite] := amBackgroundORMSharedThread;
    Self.DB.Synchronous := smOff;
    Self.DB.LockingMode := lmExclusive;
    Self.CreateMissingTables;
end;

Shadownildo · 2019-05-31 15:04:12

Just to Update

we Change the Compiler, now we are Using FPC with Lazarus, with the Trunk Version of fpcupdeluxe, but with same results. Mongo still Only creating Auth Tables

ab · 2019-06-01 12:46:52

What do you mean that the "table is not created"?
There is no CREATE TABLE -like statement in MongoDB.
To create a collection, you need to add items to it.

Shadownildo · 2019-06-03 13:12:07

ab wrote:

What do you mean that the "table is not created"?
There is no CREATE TABLE -like statement in MongoDB.
To create a collection, you need to add items to it.

thanks for the answer ab,
Sorry about that, i know there is not a Create Table, but even if Empty Mongo Should Create the Collection right ? since AuthGroup and AuthUser don't have any item, only the indexes like , in AuthUser is created with _LogonName and _GroupRights and _id, and if i put some "Primary keys"

 Stored AS_UNIQUE

in the SQLRecord,As I showed ealier, The collection is created, but in the exemple you asked me to run, it's created a Collection called mORMot, only with the _id Index, there is a way to accomplish that without add any item ? just creating a Collection with the default _id without any Primary key from the table, or i really misunderstood ?

Sorry about the English
Best regards,
Shadownildo.

ab · 2019-06-03 16:42:10

Isn't "mORMot" the database name?

Shadownildo · 2019-06-03 17:24:39

ab wrote:

Isn't "mORMot" the database name?

No, it's a Collection
https://imgur.com/J39Vu2p ( Ignore the others Databases with Similar names, i was just trying others things )

Shadownildo · 2019-06-04 12:18:22

A Update
Making some Tests, i find out that if i make a InitializeTable in the SQLRecord the Mongo Create the Table

class procedure TSQLTestTable.InitializeTable(Server: TSQLRestServer;
  const FieldName: RawUTF8; Options: TSQLInitializeTableOptions);
var
  Test : TSQLTESTtable;
begin
  inherited InitializeTable(Server, FieldName, Options);
 if FieldName='' then begin // new table -> create default
    Test := TSQLTestTable.Create;
    try
      Server.Add(Test,true);
    finally
      Auth.Free;
    end;
  end;
end;

But i wonder if there is a way to do that without having to Create a Empty Document.

ab · 2019-06-05 07:48:53

But why on earth are you expecting to have a collection appear when it is void?
As I wrote, there is no "CREATE TABLE" statement in MongoDB.
Isn't it enough to have the collection appear when there is some data within?

Shadownildo · 2019-06-05 13:02:43

ab wrote:

But why on earth are you expecting to have a collection appear when it is void?
As I wrote, there is no "CREATE TABLE" statement in MongoDB.
Isn't it enough to have the collection appear when there is some data within?

Yep, You are right, i make a more deep research yesterday , and find out that i was really wrong with Mongo concept... i have this solid concept with databases that must have tables, but with Mongo it's not in that way, Sorry about that Ab everything is working as expected now

ab · 2019-06-05 17:20:59

mORMot Open Source

#1 2019-05-29 14:13:37

Table creation in MongoDB

#2 2019-05-29 15:40:34

Re: Table creation in MongoDB

#3 2019-05-29 18:48:56

Re: Table creation in MongoDB

#4 2019-05-29 20:03:34

Re: Table creation in MongoDB

#5 2019-05-29 20:24:27

Re: Table creation in MongoDB

#6 2019-05-30 08:48:12

Re: Table creation in MongoDB

#7 2019-05-30 11:58:09

Re: Table creation in MongoDB

#8 2019-05-30 12:57:02

Re: Table creation in MongoDB

#9 2019-05-31 15:04:12

Re: Table creation in MongoDB

#10 2019-06-01 12:46:52

Re: Table creation in MongoDB

#11 2019-06-03 13:12:07

Re: Table creation in MongoDB

#12 2019-06-03 16:42:10

Re: Table creation in MongoDB

#13 2019-06-03 17:24:39

Re: Table creation in MongoDB

#14 2019-06-04 12:18:22

Re: Table creation in MongoDB

#15 2019-06-05 07:48:53

Re: Table creation in MongoDB

#16 2019-06-05 13:02:43

Re: Table creation in MongoDB

#17 2019-06-05 17:20:59

Re: Table creation in MongoDB

Board footer