VFPTransactions

[Olaf Doschke]

A big hello to anyone still using DBCs,

As may be well known I am not a big proponent of DBFs anymore, since SMB and oplocks make life harder for shared DBCs. But in recent threads, alternatives like NAS system seem very stable, as they are often based on Linux and may either not implement the SMBv3 protocol or are able to be configured in ways you get no file corruption of DBFs.

Well, no matter exactly how and why you'd also still use DBCs, one feature I was missing is transaction logs. I started this a few years ago but it also stalled for a long time and now I took the time to finish it to a usable level. You find a first version including the infrastructure to get started easily and incorporate this into your application DBCs.

VFPTransactions

https://github.com/DoschkeO/VFPTransactions

More details in the repository. When you don't use Git the easiest way to get the project is to use the feature of downloading the repository as a single ZIP and then unzip anywhere you work with your VFP projects. sampleusage.prg will reveal anything you need to know to apply this to your own DBCs, but the project also comes with a little sampledatabase.dbc that will demonstrate how this works. The initial state of the sampledatabase.DBC also is kept as is in the sample usage and a copy is generated, modified, and then used.

Ideas about usage are manifold, for example, you might base a replication process on the transaction log data. And even as is, the log can also be seen as a data history or audit trail of your data. In fact, not only the data that makes it through being committed by END TRANSACTION or TABLEUPDATE flushing buffers, etc. As the log mainly is based on reacting to any insert/update/delete trigger, makes a copy record object and writes them out to log tables in a different session than the original DBF usage, this data can even be persisted when your main application calls ROLLBACK or TABLEUPDATE()s fail in your session for any reasons.

As said, much more is described on the Github repository. I think I propose this to become a VFPX project, as I see no project in VFPX about that topic of extending a DBC with a transaction log.

And last not least, this isn't just interesting when you are making extensive use of VFP transactions. In one way it extends their meaning and motivates transaction usage, on the other side it also will log what happens at transaction level 0 outside of any transaction, as it catches all data change events in the form of the insert/update/delete triggers that can be defined for DBC tables. So it's a very general logger of all DBF changes happening in DBFs belonging to a DBC.

In its current form, it can also be used to maybe debug what happens in your data access and business objects, as the logging is quite verbose about every little trigger caused. So it can also be a debugging tool. A little downside is, that both the codebase and the output data are quite overwhelming, if you expect a single output dbf or even text log, that's not how this works. AS said mainly the triggers are the entry point for the logging, and to keep this just a brief interruption from the application code the main trigger procedures do nothing more than mainly SCATTER NAME oRecord, creating a record object of the triggering table row and adding this enriched with further information to a queue, which then is processed by a timer for as long as items are in there. So after logs are written with a low lag time, the timer even disables itself and relies on triggers and queue to reactivate it, once new items get into the queue. Which makes it less resource hungry.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Koen Piller]

Olaf,
I do think your 'Transaction logging' should be a fruitful extension to a DBC. I do have some troubles with your "sampleusage.prg" in the project. The file "create_or_alter_vfptransactions_stored_procedures" is bold and marked as main. I have troubles making a sample test to run.

If loFSO.FolderExists(JustPath(lcDataDir))
   loFSO.MoveFolder(JustPath(lcDataDir),JustPath(lcDataDir)+Sys(2015))
   Doevents
   loFSO.CreateFolder(JustPath(lcDataDir))
   Doevents
EndIf
*!*The line loFSO.CreateFOlder(JustPath(lcDataDir)) errors "OLE error code 0x800a003a: Unknown COM status code

A small question: would it be possible to add a working DBC plus a form with updatable/editing fields to show the effect of your transaction logging? I am sure that it will make it easier to understand what is going on what I prefer to change a running application with your TL.
Regards,
Koen

 

[Olaf Doschke]

Well, I marked create_or_alter_vfptransactions_stored_procedures as main by intention, as it's the major routine you'd call to add transactions to your DBC. You already found in the readme, that sampleusage is meant to be your getting started point and you need to read the readme.

I had this FSO error, too, in some situations, for example when you have opened up Windows Explorer and look into the data folder it wants to move. But I did the test to download my repository as is, unzip in a new folder and it runs, if you don't step on your own foot by first looking at everything in all detail and open up files and data. Not to say this isn't a natural thing to do, but I don't think this will be easily fixed to cover any cases of what you could have open not just within the current VFP IDE or either VFP sessions but also Windows Explorer.

This whole passage tries to move the DATA folder -if one exists - out of the way for the next step is repopulating DATA with INITIALDATA. So this also doesn't work if you first open up the initaldata sampledatabase in a separate session or have that folder open in Windows Explorer. About such things my code can't ever take full control. The best I can also put there upfront is CLOSE DATABASES ALL and CLOSE TABLES ALL and maybe check to see if _vfp.Transactions already was established.

As said, download as is, just run sampleusage.prg and it runs through. It actually has problems with a second run, because all kinds of tables and sessions are kept open, so you have to restart VFP. I'll see what I can do to make this all more stable.

I note your request of a sample user interface. I don't see a big point in UI, as this all happens on the data level no matter what type of UI you put in front of it. Sure, forms will add more datasessions to it, as private sessions. But that's what is anticipated. You'll see the main TransactionLogManager will always stay with its exclusive datasession, too. So application dbf and log dbf handling are mostly kept separate. The system does actually add some tables into the current datasession by intention to participate in any transaction and experience the same commit or rollback effect as the real data, but that's just for that matter and, well, surely this could have some name collisions, but it wouldn't be hard to mend that with all source code.

Okay, but that's leading off topic now. The directory problem would actually have a simple solution when I'd right away generate a varying data folder name for each run instead of insisting to populate DATA. not being able to get INITIALDATA access would be the other case to cover. I don't think I'll spend much time on it, bare in mind this is sampelusage and while once running it to see it works, the major bonus to you is the code, to see what I'm doing in there to transform a DBC to get its transaction log.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Hi Koen,

OK, I committed a change.

Sampleusage.prg now does not use FSO anymore, collisions with directory names are avoided by creating a new data_with_log+sys(215) directory with every run. Now if you play in the section of step 3 just doing something with the sampledatabase tables. Even adding new ones and working on them.

The transaction logging stays in _vfp.Transactions even after sampleusage finishes, so you could also browse and modify data and see what that adds to the log.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

would it be possible to add a working DBC

To get to this detail: The sampledatabase.dbc is one, albeit not a DBC coming from an application. I actually can't hand you out any dbc that's really used by end-users, as my customers have the copyrights on them, plus, only including the application would then showcase how this actually works in production. Last not least the current projects I maintain are having a MySQL or SQL Server backend. Sorry, I won't easily come up with something more. There's some VFP sample data, but obviously no application about Testrade or Northwind.

I guess some sample apps are around. That only bears the last question, how/why don't you get create_or_alter_vfptransactions_stored_procedures to process one of your DBCs? It needs exclusive access. Other than that obvious prerequisites are having tables, not just views, only dbf insert/update/delete triggers enable reacting to data changes. But I do not require anything else. I keep existing stored procs, only add or update my procs when I detect them by a header and footer comment and the create_or_alter_vfptransactions_stored_procedures also takes the previous trigger calls and embeds them in the new ones. It's not covering all cases, ie when vfplog() calls are discovered it keeps them as they are to avoid recursion, but it doesn't check whether the previously embedded call is still relevant now.

If you repeatedly create d stored procs with VFPs referential integrity I guess you also will frequently need to redo the process to add the VFPTransactions stored procs merely to get the trigger calls right. Though VFPs stored proc update also detects its own section by comment header and footer lines and doesn't touch the rest. I bet they just override the calls again. Or if they embed their own calls with what they find, they might not detect they are already kept in my calls. So you also better work with the same idea I used for the sampleusage of keeping an initialdata state of your dbc, that maybe processed at build time by an application framework you use, and then apply and reapply the VFPTransactins as last postprocessing step.

Bye, Olaf

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Koen Piller]

Olaf,
Now with the revised pjx, it is clearer and more understandable to me. Thanks. Will see how I can apply this to an existing .pjx.
One more question, I was ofcourse curios to see what where the data that was collected and noticed I can not open the files:
[ul][li]sampledatabasetriggerevents[/li]
[li]sessions[/li]
[li]transactions[/li]
[/ul]
only the files
[ul][li]sampledatabasetriggerevents_s1_t1[/li]
[li]sessions_s1_t0[/li]
[li]transactions_s1_t0[/li]
[/ul]
why is that?
Regards,
Koen

 

[Olaf Doschke]

Because it's all still running?

I keep a lot of the files open when I generate them, this might need to change for multiple users, I'll see to test this.

But when you stay in the IDE after running this, take a closer look into the datasession window and open up the current session combobox, you find a lot of sessions having open several log files. So they are still in use.

Once you close VFP you can open all dbfs. You will need to dig into the deepest directory levels to get to the core data, though. From the perspective of seeing all events the alltransactionevents.dbf is the most important, but like all the root meta tables it has no concrete data, the data of the orders and orderitems is deep in the directories for each transactions.

One thing to see about the participation and non-participating in the transactions is nice to demonstrate and see. Do something simpler as this:

Use sampledatabase!orders in 0     && <---look for these lines in sampleusage.prg
Use sampledatabase!orderitems In 0 && <---look for these lines in sampleusage.prg

* now add one order (just the head record) and roll it back
_VFP.Transactions.Begin(Set("Datasession"))
    Insert Into Orders (customerid) values (1)
_VFP.Transactions.Rollback(Set("Datasession"))

* and add another one and commit it (End = Commit)
_VFP.Transactions.Begin(Set("Datasession"))
    Insert Into Orders (customerid) values (2)
_VFP.Transactions.End(Set("Datasession"))

Do While _VFP.TransactionLogQueue.Count>0
   Doevents
EndDo 
_VFP.Transactions.Release()

? 'testrun finished'
Return

Close and reopen VFP now.

When you now look into alltransactionevents you find two records with cTrigger 'i', the inserts. cLogType = 'T' as in Triggers, so caused by the insert triggers.
One of them was rolled back. This is still there as alltransactionevents is kept away from the datasession 1, it's in the TransactionLogManager session 2.

Look into sampledatabasetriggerevents_s1_t1, That's just a subset of events that are 1) triggers, (cLogType='T') and the ones that happen in datasession 1 transactionlevel 1. Now keep in mind both the transactions were on transaction level 1, they both should be in there, not only the one that was committed. It's a bit mind-boggling, but unfortunately, the same transactionlevel does not mean the same transaction, I mark this with the logid that gets assigned to the cLogType ='t' with small t instead of big T, These are marking the start or discovery of a transaction and the inserts clearly happen in two different transactions.

So sampledatabasetriggerevents_s1_t1 tells you what actually really happened with table triggers and also got committed, it's not a concentrated form of everything that really got committed, though, but what happened in session 1 on transaction-level 1 at least. Overall, you have to collect the data back into aggregated forms. I am still figuring out how to do that best, but I am certainly happy the log works the way it does, even though it's not most comfortable.

Now to see the net data, the ORDERS records, dig into the transaction level directories:

1. transaction about customer1:
sampledatabaseLog\sessions\logid_2_s1computername_username_pid\logid_4_s1_t1
ORDERScrc933918722.dbf has the record for customer 1, although that was rolled back

2. transaction about customer2:
sampledatabaseLog\sessions\logid_2_s1computername_username_pid\\logid_6_s1_t1
ORDERScrc933918722.dbf has the record for customer 2, that was commited

And the way to see that is to relate all records by their iLogid. The details that have no parent/root/head record in sampledatabasetriggerevents_s1_t1 were actually rolled back. If you'd restore from the transaction log, what happens, you can scan through alltransactionlogevents.dbf, look for cLogType='T', the core data logging events and then see what of that actually also has a row in sampledatabasetriggerevents_s1_t1, onl if so, dig deeper into the transaction directories and find the data committed.

But when you're interested in perhaps finding what didn't get stored, that's also logged, unless the logger has some serious problem. It shouldn't, as it actually works exclusive on the transaction detail data, other users may have the same sessionid number, same transaction level, but other transactionlogid and folder. So there is no concurrency problem and the way I save is with TABLEUPDATE(2,.T.), meaning force all buffered changes to the log DBF.

It's not so easy to add the info about the rollback into the data, even though the logger managers are there and doing it especially when you go through the End() or Rollback() method. At the actual rollback moment the existing rows simply disappear, there is no delete trigger caused by that (and it would be bad, if there was), so you need to do something to find all related data. I found it easier to have a root-level table I can then later "ask" whether it knows the same logId entries as the universal alltransactionevents.dbf, and identify rollbacks that way - or only follow up on sampledatabasetriggerevents_s1_t1 (and similar detail tables for other sessions/transaction levels) to see in them what was committed. Only data for the same logIds as in there made it to the application DBC and should be replicated, for example, other data is still logged for documentation purpose and maybe also could serve to override a user's decision.

It's strikingly safe because even in buffered mode when nothing you change in a dbf gets to the dbf file directly, the buffering client sees its changes and inserts or append blank into the buffered workarea still cause the insert trigger, the trigger procedures see any buffered change you make, too, as they also run on the client. This all would be hard if VFP would be a true client/server database. Well, on the other side then only what you finally send over to a server would count into the log and a server has a much easier logging job, as it can serialize all requests coming in.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

I tested how VFPTransactions coped with parallel clients or processes and then decided to change the procedures for not taking any advantage of keeping exclusive access to anything.

I also changed the routine to add the stored procs to a DBC already having stored procs (the sampledatabase by intention and for testing purposes is once with RI triggers), because that way the line numbers in the VFPTransactions stored process match with the PRG, which makes debugging far simpler...

I also encountered shared access problems on the centrally use alltransactionevents and so decided to change the the processing/queueing strategy such that when the log central datalogger Log method does not manage to get alltransactionevents.dbf access it simply informs the queue to keep the item for a further try next time.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name