mORMot Open Source

cypriotcalm

Member

Registered: 2015-02-18

Posts: 122

database connection definitions in ini

Is there in the mORMot framework a possibility to read a connection definition/definitions from an INI/Text-file?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: database connection definitions in ini

You can serialize the TSQLDBConnectionProperties to/from JSON, or to/from INI.
See mORMot.pas' ObjectToJSON/JSONToObject or WriteObject/ReadObject functions.

cypriotcalm

Member

Registered: 2015-02-18

Posts: 122

Re: database connection definitions in ini

Is it also possible to serialize the passwort property?

In general, I would like to have a possibility to define the database connection parameters in an external file which I can change without compiling the software? Is it possible to get it done by the framework?

Last edited by cypriotcalm (2015-03-02 13:57:10)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: database connection definitions in ini

I've added some new TSQLDBConnectionProperties SaveTo/SaveToJSON/SaveToFile methods and TSQLDBConnectionPropertiesStorage.NewInstance*() methods to persist the database connection properties, and the associated class, as JSON file or variable.
See http://synopse.info/fossil/info/ae854a5e71

I've updated the documentation:

Storing connection properties as JSON

You can use TSQLDBConnectionPropertiesStorage to persist the connection properties as a JSON content, in memory or file.

Typical stored content could be:

{
"Kind": "TSQLDBSQLite3ConnectionProperties",
"ServerName": "server",
"DatabaseName": "",
"UserID": "",
"Password": "PtvlPA=="
}

The "Kind" parameter will be used to store the actual class name. Note that the units implementing the class should be compiled with the executable, e.g. SynDBSQLite3.pas for TSQLDBSQLite3ConnectionProperties.

To create a new TSQLDBConnectionProperties instance from a local JSON file, you could simply write:

var Props: TSQLDBConnectionProperties;
...
  Props := TSQLDBConnectionPropertiesStorage. NewInstanceFromFile('localDBsettings.json');

The password will be encrypted and encoded as Base64 in the file, for safety. You could use TSQLDBConnectionPropertiesStorage's Password and PasswordPlain properties to compute the value to be written on disk.

Since TSQLDBConnectionPropertiesStorage is a TSynPersistent class, you can nest it into a TSynAutoCreateFields instance containing all settings of your application.

Then ObjectToJSON/ObjectToJSONFile and JSONToObject/JSONFileToObject functions could be used for persistence as a file, of those global settings.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: database connection definitions in ini

Update:
I've modified the implementation, to add a similar TSQLRest instantiation mechanism from TSynConnectionDefinition storage.

TSQLDBConnectionPropertiesStorage has been renamed as TSynConnectionDefinition, and TSQLDBConnectionProperties or TSQLRest serialization methods are now DefinitionTo*() and CreateFrom*().
See http://synopse.info/fossil/info/78ce0600e1

cypriotcalm

Member

Registered: 2015-02-18

Posts: 122

Re: database connection definitions in ini

Wow! Great work, Arnaud! I will take a look at it! Thank you very much for the fast reaction!

cypriotcalm

Member

Registered: 2015-02-18

Posts: 122

Re: database connection definitions in ini

I have tested the new feature in the following way:

var
  dbConnProps: TSQLDBConnectionProperties;
begin
  dbConnProps := TSQLDBFireDACConnectionProperties.Create('MySQL?localhost;Port=3307', 'general', 'root', 'suhgUKpH5BLQPxFNvCJl');
  dbConnProps.DefinitionToFile('dbConnDef.json');
end;

The JSON-file looks like:

{
    "Kind": "TSQLDBFireDACConnectionProperties",
    "ServerName": "MySQL",
    "DatabaseName": "general",
    "User": "root",
    "Password": "bJy+bxhysB/eWMpKHsLt63Y9BqM="
}

But if I serialize the properties via TSynConnectionDefinition then I get a result as follows:

  ConnDefFromIni := TSynConnectionDefinition.Create();
  ConnDefFromIni.ServerName := 'MySQL?localhost;Port=3307';
  ConnDefFromIni.User := 'root';
  ConnDefFromIni.DatabaseName := 'general';
  ConnDefFromIni.PasswordPlain := 'suhgUKpH5BLQPxFNvCJl';
  
  JSONSynDef := ConnDefFromIni.SaveToJSON()

Resulted JSON-file:

{
    "Kind":"",
    "ServerName":"MySQL?localhost;Port=3307",
    "DatabaseName":"general",
    "User":"root",
    "Password":"bJy+bxhysB/eWMpKHsLt63Y9BqM="
}

Should be the resulted JSON-file not the same?

Generally, is it possible to get a JSON-File like this?

{
    "Kind": "TSQLDBFireDACConnectionProperties",
    "ServerName": "localhost",
    "Port": 3307,
    "DriverID": "MySQL",
    "DatabaseName": "general",
    "User": "root",
    "Password": "bJy+bxhysB/eWMpKHsLt63Y9BqM="
}

And one more question: if there is a need to change the password directly in a JSON-file without saved it before via "TSQLDBFireDACConnectionProperties.DefinitionToFile(...)". Is there a possibility to encrypt a plain password and to encode it as Base64?

Make it sense to have a functionality to ecnrypt/decrypt the plain passwords outside the application?

cypriotcalm

Member

Registered: 2015-02-18

Posts: 122

Re: database connection definitions in ini

I deserialize a JSON-file as follows:

The input JSON-file ist:

{
    "Kind": "TSQLDBFireDACConnectionProperties",
    "ServerName": "MySQL?localhost;Port=3307",
    "DatabaseName": "general",
    "User": "root",
    "Password": "bJy+bxhysB/eWMpKHsLt63Y9BqM="
}

dbConnProps := TSQLDBFireDACConnectionProperties.CreateFromFile('dbProps.json');

I'll get this result:

dbConnProps.ServerName -> "MySQL" instead of  "MySQL?localhost;Port=3307"

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: database connection definitions in ini

You should not create the TSynConnectionDefinition instance directly.
In your case, the Kind member is not filled, so such JSON won't work.
Use TSQLDBFireDACConnectionProperties.DefinitionTo*() methods, e.g. DefinitionToFile().

To encrypt/decrypt the plain passwords, use TSynConnectionDefinition Password/Password plain methods, associated with the Key property value.

The fields of TSynConnectionDefinition are fixed, and match TSQLDBConnectionProperties.Create() constructor parameters.
The fact that dbConnProps.ServerName is "MySQL" is expected: additional parameters are passed to the internal FireDAC options, following the TSQLDBFireDACConnectionProperties.URI() layout.
But I guess that the TSQLDBFireDACConnectionProperties.URI() layout won't be automatically handled by DefinitionTo*() methods.