Tag - Database

Entries feed - Comments feed

2020-06-05

SQlite3 Encryption Not Possible Any More Since 3.32.x

About latest SQlite3 3.32.xxx there is a big problem with codecs.

Critical changes to the public SQLite code were introduced on Feb 7, 2020: “Simplify the code by removing the unsupported and undocumented SQLITE_HAS_CODEC compile-time option”. With the release of SQLite version 3.32.0 on May 22, 2020 these changes finally took officially effect, although they weren't officially announced.

As a sad and unexpected consequence, we are NOT ANY MORE able to compile the new SQlite3 amalgamation with our encryption patch.

Continue reading

2015-12-11

Audit Trail for Services

We have seen previously how the ORM part of the framework is able to provide an Audit Trail for change tracking.
It is a very convenient way of storing the change of state of the data.

On the other side, in any modern SOA solution, data is not at the center any more, but services.
Sometimes, the data is not stored within your server, but in a third-party Service-Oriented Architecture (SOA).
Being able to monitor the service execution of the whole system becomes sooner or later mandatory.

Our framework allows to create an Audit Trail of any incoming or outgoing service operation, in a secure, efficient and automated way.

Continue reading

2015-09-25

ORM TNullable* fields for NULL storage

In Delphi code, NULLable types do not exist as such. There is no native int? type, as in C#.
But at SQL and JSON levels, the NULL value does exist and should be converted as expected by the ORM.

In SQLite3 itself, NULL is handled as stated in http://www.sqlite.org/lang_expr.html (see e.g. IS and IS NOT operators).
It is worth noting that NULL handling is not consistent among all existing database engines, e.g. when you are comparing NULL with non NULL values... so we recommend using it with care in any database statements, or only with proper (unit) testing, when you switch from one database engine to another.

By default, in the mORMot ORM/SQL code, NULL will appear only in case of a BLOB storage with a size of 0 bytes.
Otherwise, you should not see it as a value, in most kinds of ORM properties.

Null-oriented value types have been implemented in our framework, since the object pascal language does not allow defining a nullable type (yet)

We choose to store those values as variant, with a set of TNullable dedicated types, as defined in mORMot.pas:

type
  TNullableInteger = type variant;
  TNullableBoolean = type variant;
  TNullableFloat = type variant;
  TNullableCurrency = type variant;
  TNullableDateTime = type variant;
  TNullableTimeLog = type variant;
  TNullableUTF8Text = type variant;

Continue reading

2015-08-31

Delphi 10 = DX Seattle is out, mORMot supports it

We expected Delphi XE9, and now we have Rad Studio 10 Seattle, with Delphi renamed as Delphi 10 Seattle, or simply DX. No big news for the Delphi compiler itself (we are still waiting for Linux server support), but a lot of FireMonkey updates, Windows 10 compatibility enhancements, enhancements to  […]

Continue reading

2015-08-23

"SQL and NoSQL", not "SQL vs NoSQL"

You know certainly that our mORMot Open Source framework is an ORM, i.e. mapping objects to a relational / SQL database (Object Relational Mapping).
You may have followed also that it is able to connect to a NoSQL database, like MongoDB, and that the objects are then mapped via an ODM (Object Document Mapping) - the original SQL SELECT are even translated on the fly to MongoDB queries.

But thanks to mORMot, it is not "SQL vs NoSQL" - but "SQL and NoSQL".
You are not required to make an exclusive choice.
You can share best of both worlds, depending on your application needs.

In fact, the framework is able to add NoSQL features to a regular relational / SQL database, by storing JSON documents in TEXT columns.

In your end-user code, you just define a variant field in the ORM, and store a TDocVariant document within.
We also added some dedicated functions at SQL level, so that SQLite3 could be used as embedded fast engine, and provide advanced WHERE clauses on this JSON content.

Continue reading

2015-06-06

GetIt "Spirit" Concerns

I'm confused by the GetIt Submitting official blog page. Reminds me the darker ages of Delphi licensing change of XE3. GetIt is the new XE8 package manager for RAD Studio. Information about how to submit your libraries to GetIt has just been made available by Embarcadero. The idea behind GetIt is  […]

Continue reading

2015-04-06

Asynchronous Service - WebSockets, Callbacks and Publish-Subscribe

When publishing SOA services, most of them are defined as stateless, in a typical query/answer pattern - see Service-Oriented Architecture (SOA).
This fits exactly with the RESTful approach of Client-Server services via interfaces, as proposed by the framework.

But it may happen that a client application (or service) needs to know the state of a given service. In a pure stateless implementation, it will have to query the server for any state change, i.e. for any pending notification - this is called polling.

Polling may take place for instance:

  • When a time consuming work is to be processed on the server side. In this case, the client could not wait for it to be finished, without raising a timeout on the HTTP connection: as a workaround, the client may start the work, then ask for its progress status regularly using a timer and a dedicated method call;
  • When an unpredictable event is to be notified from the server side. In this case, the client should ask regularly (using a timer, e.g. every second), for any pending event, then react on purpose.

It may therefore sounds preferred, and in some case necessary, to have the ability to let the server notify one or several clients without any prior query, nor having the requirement of a client-side timer:

  • Polling may be pretty resource consuming on both client and server sides, and add some unwanted latency;
  • If immediate notification is needed, some kind of "long polling" algorithm may take place, i.e. the server will wait for a long time before returning the notification state if no event did happen: in this case, a dedicated connection is required, in addition to the REST one;
  • In an event-driven systems, a lot of messages are sent to the clients: a proper publish/subscribe mechanism is preferred, otherwise the complexity of polling methods may increase and become inefficient and unmaintainable;
  • Explicit push notifications may be necessary, e.g. when a lot of potential events, associated with a complex set of parameters, are likely to be sent by the client.

Our mORMot framework is therefore able to easily implement asynchronous callbacks over WebSockets, defining the callbacks as interface parameters in service method definitions - see Available types for methods parameters.

Continue reading

Real-Time ORM Master/Slave Replication via WebSockets

In a previous article, we presented how Master/Slave replication may be easily implemented in mORMot's RESTful ORM.
Do not forget to visit the corresponding paragraphs of our online documentation, which has been updated, and is more accurate!

Sometimes, the on-demand synchronization is not enough.
So we have just introduced real-time replication via WebSockets.
For instance, you may need to:

  • Synchronize a short list of always evolving items which should be reflected as soon as possible;
  • Involve some kind of ACID-like behavior (e.g. handle money!) in your replicated data;
  • Replicate not from a GUI application, but from a service, so use of a TTimer is not an option;
  • Combine REST requests (for ORM or services) and master/slave ORM replication on the same wire, e.g. in a multi-threaded application.

In this case, the framework is able to use WebSockets and asynchronous callbacks to let the master/slave replication - see Asynchronous callbacks - take place without the need to ask explicitly for pending data.
You would need to use TSQLRestServer.RecordVersionSynchronizeMasterStart, TSQLRestServer.RecordVersionSynchronizeSlaveStart and TSQLRestServer.RecordVersionSynchronizeSlaveStop methods over the proper kind of bidirectional connection.

Continue reading

2015-03-31

ORM Master/Slave Replication

Master Slave ORM Replication

As stated during TSQLRecord fields definition, the ORM is able to maintain a revision number for any TSQLRecord table, so that it the table may be easily synchronized remotely by another TSQLRestServer instance.
If you define a TRecordVersion published property, the ORM core will fill this field just before any write with a monotonically increasing revision number, and will take care of any deletion, so that those modifications may be replayed later on any other database.

This synchronization will work as a strict master/slave replication scheme, as a one-way on demand refresh of a replicated table.
Each write operation on the master database on a given table may be easily reflected on one or several slave databases, with almost no speed nor storage size penalty.

Continue reading

2014-11-28

ODM magic: complex queries over NoSQL / MongoDB

You know that our mORMot is able to access directly any MongoDB database engine, allowing its ORM to become an ODM, and using NoSQL instead of SQL for the query languages.

But at mORMot level, you could share the same code between your RDBMS and NoSQL databases.
The ORM/ODM is able to do all the conversions by itself!
Since we have just improved this feature, it is time to enlighten its current status.

Continue reading

2014-11-18

HTTP remote access for SynDB SQL execution

For mORMot, we developed a fully feature direct access layer to any RDBMS, implemented in the SynDB.pas unit.

You can use those SynDB classes to execute any SQL statement, without any link to the framework ORM.
At reading, the resulting performance is much higher than using the standard TDataSet component, which is in fact a true performance bottleneck.
It has genuine features, like column access via late-binding, an innovative ISQLDBRows interface, and ability to directly access the low-level binary buffers of the database clients.

We just added a nice feature to those classes: the ability to access remotely, via plain HTTP, to any SynDB supported database!

Continue reading

2014-11-14

BREAKING CHANGE - TSQLRecord.ID primary key changed to TID: Int64

Up to now, the TSQLRecord.ID property was defined in mORMot.pas as a plain PtrInt/NativeInt (i.e. Integer under Win32), since it was type-cast as pointer for TSQLRecord published properties.
We introduced a new TID type, so that the ORM primary key would now be defined as Int64.

All the framework ORM process relies on the TSQLRecord class.
This abstract TSQLRecord class features a lot of built-in methods, convenient to do most of the ORM process in a generic way, at record level.

It first defines a primary key field, defined as ID: TID, i.e. as Int64 in mORMot.pas:

type
  TID = type Int64;
  ...
  TSQLRecord = class(TObject)
  ...
    property ID: TID read GetID write fID;
  ...

In fact, our ORM relies now on a Int64 primary key, matching the SQLite3 ID/RowID primary key.
This primary key will be used as RESTful resource identifier, for all CRUD operations.

Continue reading

2014-10-25

Are "Micro Services" the proper way of writing SOA?

I just wanted to share a great article by Martin Fowler, about Micro Services. IMHO such "Micro Services" are the proper way of defining a SOA project, following SOLID principles. If we follow the "Single Responsibility" principle, we will define small uncoupled services, which  […]

Continue reading

2014-10-24

MVC/MVVM Web Applications

We almost finished implementing a long-standing feature request, introducing MVC / MVVM for Web Applications (like RubyOnRails) in mORMot.
This is a huge step forward, opening new perspectives not only to our framework, but for the Delphi community.
In respect to the existing MVC frameworks for Delphi, our  solution is closer to Delphi On Rails (by the convention-over-configuration pattern) than the Delphi MVC Framework or XMM.
The mORMot point of view is unique, and quite powerful, since it is integrated with other parts of our framework, as its ORM/ODM or interface-based services.
Of course, this is a work in progress, so you are welcome to put your feedback, patches or new features!

We will now explain how to build a MVC/MVVM web application using mORMot, starting from the "30 - MVC Server" sample.
First of all, check the source in our GitHub repository: two .pas files, and a set of minimalist Mustache views.

This little web application publishes a simple BLOG, not fully finished yet (this is a Sample, remember!).
But you can still execute it in your desktop browser, or any mobile device (thanks to a simple Bootstrap-based responsive design), and see the articles list, view one article and its comments, view the author information, log in and out.

This sample is implemented as such:

MVVM Source mORMot
Model MVCModel.pas TSQLRestServerDB ORM over a SQlite3 database
View *.html Mustache template engine in the Views sub-folder
ViewModel MVCViewModel.pas Defined as one IBlogApplication interface

For the sake of the simplicity, the sample will create some fake data in its own local SQlite3 database, the first time it is executed.

Continue reading

2014-09-12

Legacy code, mORMot, and database sharing

It is pretty much possible that you would have to maintain and evolve a legacy project, based on an existing database, with a lot of already written SQL statements - see Legacy code and existing projects.

For instance, you would like to use mORMot for new features, and/or add mobile or HTML clients - see Cross-Platform clients.
In this case, the ORM advanced features - like ORM Cache or BATCH process, see BATCH sequences for adding/updating/deleting records - may conflict with the legacy code, for the tables which may have to be shared.
Here are some guidelines when working on such a project.

To be exhaustive about your question, we need to consider each ORM CRUD operation.
We may have to divide them in three kinds: read queries, insertions, and modifications of existing data.

Continue reading

2014-06-22

Audit-trail for ORM change tracking

Since most CRUD operations are centered within the scope of our mORMot server, we implemented in the ORM an integrated mean of tracking changes (aka Audit Trail) of any TSQLRecord.
In short, our ORM is transformed into a time-machine, just like the good old DeLorean!

Keeping a track of the history of business objects is one very common need for software modeling, and a must-have for any accurate data modeling, like Domain-Driven Design.
By default, as expected by the OOP model, any change to an object will forget any previous state of this object. But thanks to mORMot's exclusive change-tracking feature, you can persist the history of your objects.

Enabling audit-trail

By default, change-tracking feature will be disabled, saving performance and disk use.
But you can enable change tracking for any class, by calling the following method, on server side:

 aServer.TrackChanges([TSQLInvoice]);

This single line will let aServer: TSQLRestServer monitor all CRUD operations, and store all changes of the TSQLInvoice table within a TSQLRecordHistory table.

Continue reading

2014-05-07

MongoDB + mORMot benchmark

Here are some benchmark charts about MongoDB integration in mORMot's ORM.

MongoDB appears as a serious competitor to SQL databases, with the potential benefit of horizontal scaling and installation/administration ease - performance is very high, and its document-based storage fits perfectly with mORMot's advanced ORM features like Shared nothing architecture (or sharding).

Continue reading

MongoDB + mORMot ORM = ODM

MongoDB (from "humongous") is a cross-platform document-oriented database system, and certainly the best known NoSQL database.
According to http://db-engines.com in April 2014, MongoDB is in 5th place of the most popular types of database management systems, and first place for NoSQL database management systems.
Our mORMot gives premium access to this database, featuring full NoSQL and Object-Document Mapping (ODM) abilities to the framework.

Integration is made at two levels:

  • Direct low-level access to the MongoDB server, in the SynMongoDB.pas unit;
  • Close integration with our ORM (which becomes defacto an ODM), in the mORMotMongoDB.pas unit.

MongoDB eschews the traditional table-based relational database structure in favor of JSON-like documents with dynamic schemas (MongoDB calls the format BSON), which matches perfectly mORMot's RESTful approach.

This second article will focus on integration of MongoDB with our ORM.

Continue reading

Direct MongoDB database access

MongoDB (from "humongous") is a cross-platform document-oriented database system, and certainly the best known NoSQL database.
According to http://db-engines.com in April 2014, MongoDB is in 5th place of the most popular types of database management systems, and first place for NoSQL database management systems.
Our mORMot framework gives premium access to this database, featuring full NoSQL and Object-Document Mapping (ODM) abilities to the framework.

Integration is made at two levels:

  • Direct low-level access to the MongoDB server, in the SynMongoDB.pas unit;
  • Close integration with our ORM (which becomes defacto an ODM), in the mORMotMongoDB.pas unit.

MongoDB eschews the traditional table-based relational database structure in favor of JSON-like documents with dynamic schemas (MongoDB calls the format BSON), which matches perfectly mORMot's RESTful approach.

In this first article, we will detail direct low-level access to the MongoDB server, via the SynMongoDB.pas unit.

Continue reading

2014-04-18

Introducing mORMot's architecture and design principles

We have just released a set of slides introducing  ORM, SOA, REST, JSON, MVC, MVVM, SOLID, Mocks/Stubs, Domain-Driven Design concepts with Delphi,  and showing some sample code using our Open Source mORMot framework. You can follow the public link on Google Drive! This is a great opportunity to  […]

Continue reading

- page 1 of 6