mORmot Patterns: Database Connection.

jvillasantegomez · 2013-11-13 15:20:21

Hello

It is time to start writing and discussing about good patterns in mORmot. Here's one of them for database connections, all samples are with Oracle but I think that the pattern will stand any database server.

Sqlite is a very neat piece of software, but the reality is that we always use something else as our backend RDBMS, so, at some point the question comes: I'm having too many connections against my database?

Here's how i'm approaching the subject, note that i'm using mORmot authentication and I want it to be on oracle.

When I create my server I have this lines:

aProps := TSQLDBOracleConnectionProperties.Create(SERVER_NAME, DATABASE_NAME, USER_ID, USER_PASSWORD);
aModel := TSQLModel.Create([TSQLAuthGroup, TSQLAuthUser], WA_ROOT_NAME);
VirtualTableExternalRegisterAll(aModel, aProps, false);

aServer := TSQLRestServerDB.Create(aModel, GetRealPath(WA_DB_NAME), true);
aServer.AuthenticationRegister(TSQLRestServerAuthenticationDefault);
aServer.CreateMissingTables(0);
aServer.NoAJAXJSON := False;

aServer.ServiceRegister(TService, [TypeInfo(IService)], sicPerSession).DenyAll.AllowAllByName(['User']);

aHTTPServer := TSQLHttpServer.Create(PORT_NAME, [aServer], '+', useHttpApiRegisteringURI);
aHTTPServer.OnlyJSONRequests := False;
aHTTPServer.AccessControlAllowOrigin := '*'; // for AJAX requests to work

With that I ensure that both AuthGroup and AuthUser tables will be created on Oracle and that the authentication will check that tables, meaning that the ORM will use Oracle from this point on. I don't really use the ORM in my code (i like SQL very much) but the authentication mecanism seems to use it. All this goes on my server creation code (think the main method that starts everything).

After that i organize the rest of my code (interface base service) with three discrete pieces. I have services (of course), controllers and repositories. Of them, the repositories will need an oracle connection (TSQLDBOracleConnectionProperties), and here comes the first question. Do i can reuse the connection created on my server initialization code or do i open new connections everytime.

What i ended up doing was to to create a new connection for every interface i have, so, every interface base service will have it's own
fProps := TSQLDBOracleConnectionProperties.Create(SERVER_NAME,DATABASE_NAME,USER_ID, USER_PASSWORD);, and since i defined my services as sicPerSession i assure that every user will have it's own connection to oracle, but this will be for every service i define, and I mean to define a lot of them.

That approach works great, until i wanted to test my server endpoint with apache jmeter, oracle could'nt get the charge... effectively "too many connections"

So, how is everyone here approaching this pattern?. I think that i could create a singleton (DbConnection) open the connection once and share it for everyone that needs it, but it would be better to create a pool of connections. The problem here is that Delphi runs as a lasting process, I think that one connection per request would be a nice compromise, but i'm not sure how to do it.

Comments on this thread will be much appreciated. I will come up with more patterns of this great framework over time.

Best regards...

Last edited by jvillasantegomez (2013-11-13 15:37:40)

ab · 2013-11-13 16:47:54

Your initialization pattern sounds just fine.

AFAIR it is not mandatory to manage your own connections by hand.

In fact, TSQLDBConnectionProperties.NewThreadSafeStatementPrepared() will handle a per-thread connection.
When used remotely from HTTP, it will only create one connection per thread in the HTTP server thread pool, so it should be just good.

The only occasion when you may need a connection "tracking" is when used with transactions.
In this case, you should better rely on a "Unit of work" pattern, and commit all your changes at once.
See http://msdn.microsoft.com/en-us/magazine/dd882510.aspx

A direct "mORmot-spirit" solution for transactions may be to use server-side BATCH mode.
See "Implement SOA-level Unit Of Work transactional safe process" in the mORMot roadmap: http://synopse.info/fossil/wiki?name=RoadMap
Proposal is in this forum thread: http://synopse.info/forum/viewtopic.php?pid=6421#p6421
Feedback is welcome.

jvillasantegomez · 2013-11-13 19:16:36

Well, I ended up creating a singleton db class. So whenever I need a TSQLDBOracleConnectionProperties i just call DB.getConnection() to get it. The connection is initialized just once i i let TSQLDBConnectionProperties.NewThreadSafeStatementPrepared() to handle threads issues.

Another approach is to create the same singleton, and on every service request do a DB.getConnection.NewConnection but it could be an overhead.

What do you think about it?

ab · 2013-11-13 21:43:55

You do not need to connect manually.
The framework will do it for you.

jvillasantegomez · 2013-11-14 13:40:03

I'm posting the code for my DBSingleton class, I'm new to delphi and don't really know if this is the right way to code a singleton, but forget about implementation, the important thing is that this class creates a TSQLDBOracleConnectionProperties once, then, whenever I need a connection i just reference that one connection like: Global_DBSingleton.GetConnection;

feedback is welcome!

unit uDBSingleton;

interface

uses
  SysUtils, SynDbOracle;

type
  TDBSingleton = class(TObject)
  public
    constructor Create;
    destructor Destroy; override;
    function GetConnection: TSQLDBOracleConnectionProperties;
  protected
    fProps: TSQLDBOracleConnectionProperties;
  end;

var
  Global_DBSingleton: TDBSingleton;

implementation

uses
  uCommons;

constructor TDBSingleton.Create;
begin
  if Global_DBSingleton <> nil then
    raise Exception.Create('Global DB Singleton already created!')
  else
    Global_DBSingleton := Self;
end;

destructor TDBSingleton.Destroy;
begin
  if Assigned(fProps) then
    fProps.Free;
  Global_DBSingleton := nil;

  inherited Destroy;
end;

function TDBSingleton.GetConnection: TSQLDBOracleConnectionProperties;
begin
  if not Assigned(Global_DBSingleton) then
    raise Exception.Create('Global DB Singleton not created!. You must call Create once.');

  if not Assigned(fProps) then
    fProps := TSQLDBOracleConnectionProperties.CreateWithCodePage(SERVER_NAME, USER_ID, USER_PASSWORD, 0);

  Result := fProps;
end;

end.

Last edited by jvillasantegomez (2013-11-14 13:40:51)

ab · 2013-11-14 17:17:00

IMHO Singletons are evil.
They are just the new global, killing most re-entrance of the OOP programming, including SOLID principles.

For Oracle, it may work as expect, but with other DB drivers (e.g. OleDB), this won't work.

Why not simply use TSQLDBConnectionProperties.NewThreadSafeStatementPrepared() ?

jvillasantegomez · 2013-11-14 17:48:34

Yeah, you are right, buth the question here is where/when to call TSQLDBOracleConnectionProperties.CreateWithCodePage(...). After that I always use TSQLDBConnectionProperties.NewThreadSafeStatementPrepared() all around my code.

It seems that you can go away with calling TSQLDBOracleConnectionProperties.CreateWithCodePage(...) once on server startup, but i'm not really sure. I come from a dynamic language background (php, node.js, ruby) and don't know how to really do things with delphi lasting running proccesses.

So, here's the question again.
If you just need to call TSQLDBOracleConnectionProperties.CreateWithCodePage(...) once on server startup, then a singleton does a good job, but, is that a persistent connection against oracle database?.

But if that is not a good solution then how to do it right?

Best regards...

Last edited by jvillasantegomez (2013-11-14 17:54:15)

mORMot Open Source

#1 2013-11-13 15:20:21

mORmot Patterns: Database Connection.

#2 2013-11-13 16:47:54

Re: mORmot Patterns: Database Connection.

#3 2013-11-13 19:16:36

Re: mORmot Patterns: Database Connection.

#4 2013-11-13 21:43:55

Re: mORmot Patterns: Database Connection.

#5 2013-11-14 13:40:03

Re: mORmot Patterns: Database Connection.

#6 2013-11-14 17:17:00

Re: mORmot Patterns: Database Connection.

#7 2013-11-14 17:48:34

Re: mORmot Patterns: Database Connection.

Board footer