How to synchronize auto increment keys between different databases in Firedac?

Question

I am researching how the auto increment fields work in Firedac, using Delphi xE7 and managed to assemble an example of the documentation that synchronizes two master-details tables with the correct key. The doubt arose when I wanted to make a mirror of my Firebird server on Sqlite, as I will distribute an application that needs not only work offline, as well as persist the data when closing the application.

Then in my remote application I will add records in two tables (Pedidos,Itens) in master-detail schema, which need to have a foreign key in common. When connecting to the server at the end of the day I want to send orders and new items, knowing that there may be other users who have also generated Sqlite keys the same as mine. Using Firedac, how to update them with new keys without losing the master-detail link? Remembering that the server is Firebird and locally is Sqlite...

Answer 1

Let’s go in pieces:

---

First: Firedac is data access so auto-increment is something from the database and not firedac. The auto increment option is available in Firebird 3 without using the combination Quence/Trigger.

fieldid bigint generated by default as identity primary key

To enter you have the ID value Firebird offers the returning feature

INSERT INTO tab1 (...) values (...) RETURNING idpk;

---

Second: In the system that I keep from selling automation the order table (Sqllite) of each seller has its own ID, when sending the order to the main database is used the Idvendedor/Idpedido combination to identify unequivocally the order. In other words, these fields are attributes of the request on the Firebird side of the force.

---

One solution to synchronize Primary key in different applications that operate asynchronously, regardless of why it works like this, is the use of UUID, Universally Unique Identifier (UUID) UUID is a 128bit hexadecimal type, Firebird uses a 16 byte char type for this purpose. Firebird brings the functions GEN_UUID() since version 2.1, UID_TO_CHAR() and CHAR_TO_UID() since version 2.5, required for the use of UUID in its application on the Firebird side.

When to Sqlite: See the references below
Generation of the UUID
Example of DDL