2011-06-02

Custom SQL functions

The SQLite3 engine defines some standard SQL functions, like abs() min() max() or upper().
A complete list is available at http://www.sqlite.org/lang_corefunc.html

One of the greatest SQLite3 feature is the ability to define custom SQL functions in high-level language. In fact, its C API allows to implement new functions which may be called within a SQL query. In other database engine, such functions are usually named UDF (for User Defined Functions).

Our framework allows you to add easily such custom functions, directly from Delphi classes.

Continue reading

Fast JSON parsing

When it deals with parsing some (textual) content, two directions are usually envisaged. In the XML world, you have usually to make a choice between:
- A DOM parser, which creates an in-memory tree structure of objects mapping the XML nodes;
- A SAX parser, which reads the XML content, then call pre-defined events for each XML content element.

In fact, DOM parsers use internally a SAX parser to read the XML content. Therefore, with the overhead of object creation and their property initialization, DOM parsers are typically three to five times slower than SAX. But, DOM parsers are much more powerful for handling the data: as soon as it's mapped in native objects, code can access with no time to any given node, whereas a SAX-based access will have to read again the whole XML content.

Most JSON parser available in Delphi use a DOM-like approach. For instance, the DBXJSON unit included since Delphi 2010 or the SuperObject or DWS libraries create a class instance mapping each JSON node.

In a JSON-based Client-Server ORM like ours, profiling shows that a lot of time is spent in JSON parsing, on both Client and Server side. Therefore, we tried to optimize this part of the library.

Continue reading

BATCH sequences for adding/updating/deleting records

When use the so-called BATCH sequences?

In a standard Client-Server architecture, especially with the common understanding (and most implementations) of a RESTful service, any Add / Update / Delete method call requires a back and forth flow to then from the remote server.

In case of a remote connection via the Internet (or a slow network), you could have some 100 ms of latency: it's just the "ping" timing, i.e. the time spent for your IP packet to go to the server, then back to you.

If you are making a number of such calls (e.g. add 1000 records), you'll have 100*1000 ms = 100 s = 1:40 min just because of this network latency!

The BATCH sequence allows you to regroup those statements into just ONE remote call. Internally, it builds a JSON stream, then post this stream at once to the server. Then the server answers at once, after having performed all the modifications.

Continue reading

TSQLRecordRTree to implement R-Tree virtual tables

An R-Tree is a special index that is designed for doing range queries.

R-Trees are most commonly used in geospatial systems where each entry is a rectangle with minimum and maximum X and Y coordinates. Given a query rectangle, an R-Tree is able to quickly find all entries that are contained within the query rectangle or which overlap the query rectangle.

 This idea is easily extended to three dimensions for use in CAD systems. R-Trees also find use in time-domain range look-ups. For example, suppose a database records the starting and ending times for a large number of events. A R-Tree is able to quickly find all events, for example, that were active at any time during a given time interval, or all events that started during a particular time interval, or all events that both started and ended within a given time interval. And so forth. See http://www.sqlite.org/rtree.html

Since the 2010-06-25 source code repository update, the RTREE extension is compiled by default within all supplied .obj files of the framework.

A dedicated ORM class, named TSQLRecordRTree, is available to create such tables. It inherits from TSQLRecordVirtual, like the other virtual tables types (e.g. TSQLRecordFTS3 or our custom virtual tables).

Continue reading

2011-06-01

Business rules: validate and filter data

According to the n-Tier architecture, data filtering and validation should be implemented in the business logic, not in the User Interface.

If you were used to develop RAD database application using Delphi, you may have to change a bit your habits here. Data filtering and validation should be implemented not in the User Interface, but in pure Delphi code.

Data filtering is a process run on the User entry: for instance, it will trim left or right spaces, make the text uppercase...
Data validating is performed before saving the data to the database: for instance, an email address is checked for consistency, a field value to be unique...

Some try to implement this using an external scripting engine, in a procedure/event mode. Back to the 80th...
In our ORM framework, filtering and validation can be performed by creating some Delphi classes, which may be used on both Server and Client side.

Continue reading

2011-05-26

Calling a 64 bit library from a Delphi 32 bit process

Since we are still waiting for a Delphi 64 bit compiler, the only available solution to access a 64 bit library from an application written in Object pascal, is to use the 64 bit version of the FreePascal Compiler.

But you just can not recompile your VCL/GUI based Delphi application with FPC:

  • Some low-level part of your code may not be directly compatible with a 64 bit process (e.g. since the pointer size changed);
  • The GUI part of the application can not be ported directly with FPC - the Lazarus project try to be as close as possible to VCL, but it can be a very difficult, either impossible if you use some third-party components.
I just found out a solution from CodeCentral, allowing to call any 64 bit dll from a Delphi 32 bit process.

Continue reading

2011-05-24

How to implement RESTful authentication

How to handle authentication in a RESTful Client-Server architecture is a matter of debate.

Commonly, it can be achieved, in the SOA over HTTP world via:
- HTTP basic auth over HTTPS;
- Cookies and session management;
- Query Authentication with additional signature parameters.

We'll have to adapt, or even better mix those techniques, to match our framework architecture at best.

Each authentication scheme has its own PROs and CONs, depending on the purpose of your security policy and software architecture.

Continue reading

2011-05-20

How to write fast multi-thread Delphi applications

How to make your software run fast, especially in a multi-threaded architecture?

We tried to remove the Memory Manager scaling problems in our SynScaleMM. It worked as expected in a multi-threaded server environment. Scaling is much better than FastMM4, for some critical tests. But it's not ready for production yet...

To be honest, the Memory Manager is perhaps not the bigger bottleneck in Multi-Threaded applications.

Here are some (not dogmatic, just from experiment and knowledge of low-level Delphi RTL) advice if you want to write FAST multi-threaded application in Delphi.

Continue reading

- page 41 of 52 -