> That's right. I hope there are better ways like Delphi's MIDAS or DataSnap, which are not.
Yes and no. There is no drop-in replacement, but there are different means to achive multi-tier architecture.
> It's simple if I connect to database (Firebird in my case) directly, like HTTP connection. But it does not seem good idea to open database on the http. So I have to write the transmission codes myself.
The classic way to connect to IB/FB over global net was ZeeBeeDee which later wast incorporated into OpenVPN.
Given good configuration, this should fence off crackers and relax temporary connection drops (but not totalyl hide them).
Firebird low level protocol, though, is chatty, having a lot of roundtrips. Which is perfectly okay for LAN envionment, provides easy early detection of errors, etc. But for global, internet-like connection the accumulated "ping penality" becomes big.
So, direct far-away conneciton to database is usually considered workable but not best of solutions.
That said, while mORMot has FB3 interface unit, i don't know if its support is anywhere but nominal. mORMot chose FPC as their neext (after Delphi 7) base target, which is good for you, but they also chose SQLite as their primary DB.
However...
With Firebird you definitely always have a choice to populate ID columns or to let server auto-populate them. When i saw
DeltaDS.FieldByName('ID').NewValue:=newID ;
I was buffled.
For Firebird usually you do
INSERT INTO TableName(data1,Data2, ...) VALUES ( :param1, :param2, ...) returning ID
And then you read into your program the real ID created by Firebird. This, BTW, saves you one netwrok roundtrip, comparing to an extra query to GEN_ID
Try reading more, on the stack overflow for example, about GTT and Merge.
https://stackoverflow.com/questions/43997516https://stackoverflow.com/questions/36887591Those exampels are not immediately your problem - but hopefully would be a starter for you to get idea of those tools. There were other questions, more close to your case, you would probably find them eventually.
> 1) first delete records of to-be-deleted ids,
Really bad idea.
On any server: you would have all the Foreign-key linked tables to loose their data. In ISAM term when you delete a row from Master table - the rows in Details tables have to be deleted too.
Specifically on Firebird/Interbase (and also Postress, Oracle, and any other MGA/MVCC server) - you make unneededly big changes in the database storage, creati a lot of garbage, invaluidating huge parts of indexes. IOW you create a lot of garbage in the storage.
Then, Posgress does store record version in the indexes, so it makes updates slower. Interbase/Firebird does not an do updates are faster - but it means lot of leafs in the indexs are dangling pointers having no rows they point to, which slows down reading operations. Eventually Firebird gives up and starts Garbage Collector and this slows down the database for quite a while [is SuperServer arch]. In [Super]Classic Server architectures it also means your query is stopped and waits until GC completes, before it is executed.
FB SuperServer means forking threads, and before FB3 it means "one CPU core per connection" (maybe still is, not sure).
FB Classic and SuperClassic means forking processes not threads for connecitons.Frankly, massive delete is on the list of "don't do"s for IB/FB - it is in common wisdom domain, or you can look for articles explaining how GC works in FB/IB family
Also see
http://www.ibase.ru/dontdoit/Some articles, but probably not that one, were translated on
https://ib-aid.com/en/articles/> 2) UpdateOrInsert based on the key.
Bad idea. This operation can only work one one single row. Meaning if you have 100 rows you would have to issue 100 separate queries. Each would have several network roundtrips, paying ping penalty again and again.
Either go multi-tier, making a FPC/Delphi-written intermediate server, and your clients worinf with him never having direct conneciton to the DB.
Or make it GTT+Merge.
> These actions can be done within one transaction.
per-transaciton GTT + merge
> In my case values of primary key itself may change (without autoincrement field),
Why??? Make them non-changing. What purpose ios for them to change? How can you solve the problem different ways?
When you have several clients working with the same DB - how would Client A inform CLient B that he just changes the PK for the row Client B is processing and so the Client B should drop hios work ad start processing anew with different PK value ???
Or, even make them changing but make Firebird UPDATE PK values and tack the changes into all the detail tables (Foreign Keys in the "on update cascade" mode), though it is still creating unnedded proiblem just to then be looking for ways to escape it. Better be not creeating the problem.
BTW, you can have a column with Unique Constraint with Unique Index different than PK