Fun FoxPro Quirk using NOUPDATE and AGAIN together

[Joe Crescenzi]

Here's something fun that I stumbled into that I decided was worthy of sharing for those who may be curious.

When you open a table using USE... NOUPDATE, any subsequent USE commands with AGAIN and ALIAS may, or may not end up inheriting the Read Only / NOUPDATE status, depending on how the first USE command was called.

Here's an example.

CLOSE DATABASES
USE testtable IN 0 AGAIN SHARED ALIAS "Regular" 
USE testtable IN 0 AGAIN SHARED ALIAS "ReadOnlyVersion" NOUPDATE
USE testtable IN 0 AGAIN SHARED ALIAS "Regular2" 
USE testtable IN 0 AGAIN SHARED ALIAS "Regular3"

The result here will be that you will have four open tables, with the one called "ReadOnlyVersion" being the only one that is open in Read Only mode.

Select area:  4, Table in Use: C:\SOURCECODE\CARTPRO.NEW\TESTTABLE.DBF    Alias: REGULAR3
Select area:  3, Table in Use: C:\SOURCECODE\CARTPRO.NEW\TESTTABLE.DBF    Alias: REGULAR2
Select area:  2, Table in Use: C:\SOURCECODE\CARTPRO.NEW\TESTTABLE.DBF     (Readonly) Alias: READONLYVERSION
Currently Selected Table:
Select area:  1, Table in Use: C:\SOURCECODE\CARTPRO.NEW\TESTTABLE.DBF    Alias: REGULAR

However, if you open the tables with the NOUPDATE line first, ALL of the tables will end up Read Only.

CLOSE DATABASES
USE testtable IN 0 AGAIN SHARED ALIAS "ReadOnlyVersion" NOUPDATE
USE testtable IN 0 AGAIN SHARED ALIAS "Regular" 
USE testtable IN 0 AGAIN SHARED ALIAS "Regular2"
USE testtable IN 0 AGAIN SHARED ALIAS "Regular3"

Here's the result.

Select area:  4, Table in Use: C:\SOURCECODE\CARTPRO.NEW\TESTTABLE.DBF     (Readonly) Alias: REGULAR3
Select area:  3, Table in Use: C:\SOURCECODE\CARTPRO.NEW\TESTTABLE.DBF     (Readonly) Alias: REGULAR2
Select area:  2, Table in Use: C:\SOURCECODE\CARTPRO.NEW\TESTTABLE.DBF     (Readonly) Alias: REGULAR
Currently Selected Table:
Select area:  1, Table in Use: C:\SOURCECODE\CARTPRO.NEW\TESTTABLE.DBF     (Readonly) Alias: READONLYVERSION

I discovered this accidentally when I decided to open some tables in NOUPDATE on a screen that didn't allow editing, then without realizing that later in the session I open another form that DOES allow edits, and since the previous table was already open in ReadOnly, the second form didn't allow edits.

 

[EinTerraner]

Look at the Fox-Helpfile
When you open a table again in another work area, the table in the new work area takes on the attributes of the table in the original work area. For example, if a table is opened for read-only or exclusive access and is opened again in another work area, the table is opened for read-only or exclusive access in the new work area.

 

[Joe Crescenzi]

I definitely expected the exclusive option to play a major role, after all you can't have one version that's exclusive and allow work area to be able to open it, but the NOUPDATE was a bit more of a surprise because it does allow other work areas to be completely independent when the initial version was not opened with NOUPDATE.

So, if you open the table normally, you can re-use it as often as you want, sometimes with NOUPDATE, sometimes without it, and each work area will have whatever you specify to decide whether it's read only or not. However, if the first version of the USE has the NOUPDATE, you no longer have a choice. All will be read only.

There's no workaround other than not opening a table using NOUPDATE if you think you may need to re-open that table elsewhere. You'd think they would have a reciprocal UPDATE option so you can implicitly declare whether to allow or not allow edits, just like there's a SHARED and EXCLUSIVE option.

Anyhow, I stumbled on this accidentally and thought it could be helpful in case somebody searches for it some day.

 

[Chriss Miller]

I understand you, Joe, theoretically there is the option to allow each workarea to differ regarding the readonly/updatable nature of the workarea. But it is as EinTerraner says.

It's of course unavoidable with the exclusive option, the only way you can have a secondary workarea with the same table is, that it's also exclusive - and it obviously only works within the same process. I think it proves that workareas don't map 1:1 with file handles used to act on the files in the background, i.e. when using AGAIN the same file handle is used for the new workarea, which therefore inherits the features that belong to that (C++) file handle the runtime uses in the background, while you use a workarea number or alias name.

The AGAIN section of the USE command help topic states it exactly:

USE Command

AGAIN
To open a table concurrently in multiple work areas, you can do one of the following:

• Select another work area and issue USE with the table name and the AGAIN clause.
• Issue USE with the table name and the AGAIN clause, and specify a different work area with the IN clause.

When you open a table again in another work area, the table in the new work area takes on the attributes of the table in the original work area. For example, if a table is opened for read-only or exclusive access and is opened again in another work area, the table is opened for read-only or exclusive access in the new work area.


Index files opened for the original table are available for the table you open again if you don't open indexes when you open the table again. The index order is set to 0 in the work areas where the table is opened again.


You can open indexes that were not opened with the original table. This sets the index order to 0 for the original table.


A table opened again is assigned the default alias of the work area. You can include an alias every time you open a table in multiple work areas as long as the aliases are unique.


In Visual FoxPro for Windows, opening a table again in another work area doesn't consume an additional file handle.

There's another side effect mentioned about index order of table used initiall vs again. You have the ability to set no order in the workarea using the table AGAIN, but when you set an index the help says you thereby set the index order to 0 for the originally opened table. Let's see. I opened the tastrade sample database and then did:

USE tastrade!customer ORDER tag customer_i
USE tastrade!customer again IN 0 ALIAS customeragain
SELECT customeragain
SET ORDER TO company_na

Afterwards the original customer workarea still is sorted by cusomter_i tag. The sort orders are almost identical, as one is by the infamous abbreviations like ALFKI and the other is by the long company name (Alfreds Futterksite for ALFKI, for example) and they sort quite in the same order, first difference is for BSBEV sorting in before BERGS, if you look precisely. Anyway, the properties of the workarea also still show both indexes used.

If I don't have a complete misunderstanding of what the help tells about index sort order - I assume they talk of index tag numbers where 0 means no sorting - then this is a documentation error and the section about AGAIN could also be wrong, anything in the help could potentially be wrong, the authors of the help are not the programmers of the VFP language, but in general it's correct and so regarding the NOUPDATE state of workareas you just found out, what the help on the AGAIN clause states. So that section is correct.

Now the challenge is to USE again writable. I think there was a hack you needed to use in VFP6 for query results as it didn't have the NOFILTER vs READWRITE options or only NOFILTER and every sql result was readonly aka noupdate. But I think that was simply using DBF() again in the same workarea to make it readwrite. And that means you'll need to use the table in the original workarea where it gets rid of the readonly state you may want to have there. And trying it this trick only works, if the workarea to change from readonly to readwrite is the only having the dbf open.

It comes down to a rule of thumb of either not using NOUPDATE ever or always first use a dbf without the NOUPDATE clause and then with NOUPDATE in a secondary workarea. It's feasable to do, even if you want a form to finally only use the NOUPDATE workarea. I haven't checked what happens in spearate datasessions, I think a new datasession allows you to use a table without AGAIN clause, again, as it is the main feature of a separate datasession to allow reusing alias names, of course. So it's just +1 for using private datasessions in forms.

 

[Joe Crescenzi]

It's an interesting study in the nuances of how FoxPro handles the NOUPDATE option.

According to the help file:

"In Visual FoxPro for Windows, opening a table again in another work area doesn't consume an additional file handle."

Based on that it's super clear what's under the hood. FoxPro conserves file handles by sharing the original handle rather than creating new ones.

The odd thing is why it even needed to open even the first file for read only at the operating system level in the first place because it already has the ability to internally allow any subsequent opens to conditionally use NOUPDATE and still enforce the read only integrity on a work area by work area basis.

If FoxPro always opened files with read/write at the O/S level, whatever flags they use for NOUPDATE would still work to protect the data in every subsequent call.

In my case, I used the NOUPDATE on a form that had a dropdown that I knew wouldn't need to be edited, so I assumed it would have some potential for saving resources, but didn't anticipate that it would block me from adding new drop-down values from an entirely different form that opened it with a new alias. Removing the NOUPDATE was a quick fix, and a quick lesson that I thought was interesting to share in case anyone else stumbles into it.

 

[Chriss Miller]

Be aware: If you open a file read/write with FOPEN, that is exclusive access. So default FOPEN mode is readonly, actually, not only for the NOUPDATE option, but also for shared access. VFP diverts from read only mode to write mode with automatic locks done for any insert/update/replace, etc. when you actually write to a DBF. The basis for that is not a design consideration of VFP, but simply the fact of how the FAT and NTFS file systems work, only one file handle is capable of writing to a file at the same time. Which means the shared writeable mode VFP offers is emulated on the basis of readonly access to files all the way. The difference VFP internally makes with NOUPDATE vs normal writable SHARED mode is really an internal convention to not allow write operations, otherwise writing to a dbf triggers the automatic locking mechanism also descibed in the help file. It has nothing to do with the usual table head or record locks. It's mainly a temporary switch of file handles to one that allows writing.

When using a dbf exclusive, that just uses a writable handle when FOPENing a file on the runtime C++ level and you can do the same for any file, not only dbfs with VFPs own FOPEN, once you have a writable handle, no other can get any handle, not even a read handle. After FOPEN("some.dbf",12) for read/write you can't even do FOPEN("some.dbf,0) in default read only mode in the same IDE process.

That all roots in the decision made for these file systems to solve the problem of concurrent changes to files. In many other file systems on many other OSes you have file systems are more complex with journaling, which is a transaction log on files, that allow actually shared write access, as long as it's collision free.

The irony of it is that although MS therefore decided for the simplest solution to avoid write collisions by making it a semaphore like access to files, where only one instance can write, the collisions still happen and are the most often reason for DBF file corruptions.

I forgot to explain the most important reason why VFP doesn't use multiple file handles on the same DBF for the different workareas you open it first and again and again: If it would, none of them could be switched to a writable file handle, not even temporarily as VFP does for the actual write operations.

 

[Joe Crescenzi]

... when you actually write to a DBF. The basis for that is not a design consideration of VFP, but simply the fact of how the FAT and NTFS file systems work, only one file handle is capable of writing to a file at the same time. Which means the shared writeable mode VFP offers is emulated on the basis of readonly access to files all the way. The difference VFP internally makes with NOUPDATE vs normal writabel SHARED mode is really an internal convention to not allow write operations, otherwise writing to a dbf triggers the automatic locking mechanism also descibed in the help file. It has nothing to do with the usual table head or record locks. It's mainly a temporary switch of file handles to one that allows writing.


That all roots in the decision made for these file systems to solve the problem of concurrent changes to files. In many other file systems on many other OSes you have file systems are more complex with journaling, which is a transaction log on files, that allow actually shared write access, as long as it's collision free.

Great insights. For those who are curious, you can see this demonstrated in real time by looking at the Open Files section of a file server, then sort by the # of Locks tab. 99% of the files will have zero locks, then if you refresh the page, the locks come in and out of existence as the locks are added and released moments later after the write.

The only odd thing that stands out is that as long as they already have support for managing independent read only status of each table, and all those automatic locks and unlocks, why not always open files the same way?

For what it's worth, other than that experiment, the only time I use NOUPDATE is outside my programs when I run ad-hoc queries and test code from the Command Window, where I'm mainly making sure I don't accidentally touch the records. Inside my applications, the code itself dictates where, when and who can change records at any given time.

 

[Chriss Miller]

why not always open files the same way?

They do, once at the first USE of a dbf, readonly. The only actual difference on the file handle level is with exclusive access. The point is they actually delegate the exclusive handling to the OS/filesystem that way. VFP only internally resolves the shared / noupdate vs readwrite options. USE AGAIN recycles the file handle and indeed whether you first USE with or without NOUPDATE that's not a difference of the file handle mode. That's internal. So they could decide to not inherit the NOUPDATE flagging, but they decided that way.

Once more, in short:
SHARED: No matter whether readwrite or noupdate - VFP uses a file in readonly mode. Difference between readwrite and update is flagging, whether the readonly handle can be swapped to a writable handle temporarily or not, and that flag is inherited by USE AGAIN, additional to recycling the file handle.
EXCLUSIVE: VFP uses a readwrite file handle and delegates managing the exclusiveness to the OS/filesystem.

There's always another option: I have a fundamental misunderstanding of the principles used myself. But your file monitoring experiment shows that most of that is true, actually. I don't know whether the initial USE of a DBF in a private datasession will actually cause a new spearate file handle to the dbf, or it recycles the handle used in the C++ background across the datasessions, because otherwise after my own assumptions you also couldn't switch to a writable handle for the second datasession, if the first still has a separate readonly handle. My guess is that VFP actually recycles file handles all the way, unless you open a new process that requires its own handles. Within a process and no matter how many datasessions are used to separate workareas, make alias names reusable and have separate sorting by index tags and separate record pointer positions, VFP manages all that with one file handle per file. It should be easy to verify with access to a file server. I'm currently not in that position, on a single notebook.

 

[Joe Crescenzi]

It makes 100% for exclusive use for lots of reasons, because you need to ensure other users can't access a file until you're done, but within the same session it would've been a better decision to open the files the same way and let the presence or lack of NOUPDATE determine what that work area will need. After all, they already handle it perfectly well if the first open instance was read/write, and dozens of other instances have independent options.

I can only imagine they were trying to save a small amount of resources when a file is opened as ReadOnly, but that was in an era where file handles were scarce. I remember having to put code into my system in the 80s to detect the number of handle available in DOS so I could put a message telling people to add FILES = 60 into their config.sys to ensure we didn't run out of handles. How things change.

 

[Chriss Miller]

After all, they already handle it perfectly well if the first open instance was read/write, and dozens of other instances have independent options.

I'm not sure. But don't you realize even in the first opening VFP uses readonly mode, always, except for exclusive opening of DBFs? There is no shared write mode in Windows, FAT, NTFS. That's "emulated" by VFP.

I mean, you described it yourself, this way:

99% of the files will have zero locks, then if you refresh the page, the locks come in and out of existence as the locks are added and released moments later after the write.

I don't think you can see the mode a file handle represents, only see the lock count. Anyway, there is no difference on that level for readwrite vs noupdate. So indeed what you consider as a better design would be possible, but isn't done, I even agree it could have been designed differently, but that's the way MS designed it, as documented in the help about the AGAIN clause.

I bet there is a detail that differs from what I assume about swapping file handles temporarily. Maybe C++ or Windows API to the file system has functions that change the mode for an existing file handle. The VFP6 or earlier hack of USE DBF() to get it to rewrite only works, if it's the only workarea using that readonly DBF, because USE DBF() just closes and opens the DBF currently open and so causes the readonly mode to vanish, as it's not specified in the USE. USE DBF() AGAIN does not work, I just tried that. It only can change the original workarea from NOUPDATE to normal.

 

[Joe Crescenzi]

I'm not sure. But don't you realize even in the first opening VFP uses readonly mode, always, except for exclusive opening of DBFs? There is no shared write mode in Windows, FAT, NTFS. That's "emulated" by VFP.

I mean, you described it yourself, this way:

I don't think you can see the mode a file handle represents, only see the lock count. Anyway, there is no difference on that level for readwrite vs noupdate. So indeed what you consider as a better design would be possible, but isn't done, I even agree it could have been designed differently, but that's the way MS designed it, as documented in the help about the AGAIN clause.

We are 100% in agreement. They definitely could've changed the behavior because it does in fact let each work area have independent readonly vs readwrite as long as the first file was not using NOUPDATE, so the underlying code is flexible enough under the hood to manage each work area's status.

Since Microsoft doesn't have any newer releases, we just need to be aware of the limitation, which is why I posted this thread. If people get anything out of this it should simply be that they shouldn't use NOUPDATE unless they know for sure they will not need to open a file later to update it from another form.

Also, although I didn't test it, I'm guessing a Private Data Session could eliminate this restriction by allowing VFP to ignore the original file handles and open new ones.

 

[Chriss Miller]

Also, although I didn't test it, I'm guessing a Private Data Session could eliminate this restriction by allowing VFP to ignore the original file handles and open new ones.

I also didn't. We could change that, obviously. Anyway, I'm not even sure about that possibility.

I don't remember anything that happend in my whole development in VFP that would finally be explained by that, but I also did rarely use NOUPDATE. I did use nonupdatable views, I wonder whether that only applies to the actual view workarea, for local views, or also the opened DBF file. I think in that case the DBF is not opened with NOUPDATE, but the view alias workarea has that aspect.

That could all be tested. I wonder whether there is a real good real time monitoring tool for files, file changes and handles on files. I know some powershell commandlets and WMI queries for that, which only take snapshots ever few seconds, but won't actually be real time effected by changes at the exact time they happen. Such a tooling could help find out what definitely happens in the VFP runtime. I guess you can confirm some aspects with just the file handle monitoring on a file server.

I'm just happy the help is wrong about the side effect of setting order to an AGAIN workarea to reset the original workarea sort oder to index tag 0 aka unsorted. I guess that would have suffered from that.