<TBL>.DELETE() - not what you might think

[michaelklein]

Hi all.

I've just spent the better part of a day trying to delete parent and child records related in a 1:M set. I've pored through the online Help and this forum and have learned that there is a sublety that isn't emphasized enough. I'd like to share what I've learned so that others can benefit. Here's the problem:

According to a note attached to the Help for "TBL".DELETE(), "For Alpha Five V3.02 and beyond, the record pointer will move to the next record after the "TBL".CHANGE_END() method is executed. For prior versions, the record pointer will remain on the deleted record."

That means that if you use a standard while/fetch_next() loop to delete multiple child records as mentioned in thread

http://msgboard.alphasoftware.com/alphaphorum/read.php3?sortby=lastreply&amp;direction=desc&amp;num=11&amp;id=53033&amp;thread=53016

and the online Help, you will actually delete *every other* child record. This happens because as soon as you delete a record, the record pointer automatically moves to the next record, so the fetch_next() will skip the one the pointer has just moved to. (I verified this by using Visual FoxPro 5.0 to view a table with records which had been deleted by a while/fetch_next() loop in A5. Since VFP 5.0 clearly shows deleted records in its browses, one of the few things I can't seem to do with A5, it was easy to see that "every other record" had been deleted.)

So how do you solve this problem? Again referring to the thread above, Tom Cone, Jr. suggested using "TBL".DELETE_RANGE(). That turns out to be an excellent suggestion as long as you construct the filter properly.

So, perhaps A5v6 should have an additional logical parameter attached to "TBL".DELETE() where .T. (default) moves the record pointer (current behavior) and .F. doesn't. That would provide the best of both worlds. For me, I'll just use "TBL".DELETE_RANGE() for now :). What do you think?

--Michael

[Stan Mathews]

RE: &lt;TBL&gt;.DELETE() - not what you might thin

If you like

"tbl".delete()

you can just issue a

"tbl".fetch_prev() after the

"tbl".change_end(.t.)

[michaelklein]

RE: &lt;TBL&gt;.DELETE() - not what you might thin

Stan,

Actually, we could just remove the tbl.fetch_next() entirely and it should work. However, I don't think a simple delete function should require that much effort to work the way most developers are probably used to.

I think part of the issue relates to the fact that unlike other .dbf database products (dBase, FoxPro, Clipper), A5 *protects* the user from having to deal with *deleted* records. (Do you know how to include/view deleted records inside a browse?) In a number of cases, the other products require that you either test if a record is deleted or that you filter deleted records from an open table. I would have no problem if A5 offered a way for me to manage deleted records.

Just my 2 cents.
--Michael

[Steve Dentler]

RE: &lt;TBL&gt;.DELETE() - not what you might thin

Careful with using Delete_Range() across a mult-user network. If any of the records in the range is locked, Delete_Range will fail without triggering an Error Message. Not cool.

[michaelklein]

RE: &lt;TBL&gt;.DELETE() - not what you might thin

Steve,

Thanks for the "heads up". Another important piece of information to add to this *delete profile*.

--Michael

[Blake]

RE: &lt;TBL&gt;.DELETE() - not what you might thin

When I ran into "tbl".delete last August:

http://msgboard.alphasoftware.com/alphaphorum/read.php3?sortby=lastreply&amp;direction=desc&amp;num=11&amp;id=39578&amp;thread=39489

It didn't advance the pointer at all. I've just been marking and deleting records since then.

[michaelklein]

RE: &lt;TBL&gt;.DELETE() - not what you might thin

Blake,

Do you *mark* and then use delete_range() for the marked records as Tom suggested in the thread you referenced? If so, how do you prevent running into the same problem Steve Dentler mentions above where one of your marked records is locked by another user? Or do you use a loop and just keep trying to delete the marked records until they're all deleted?

From what I can see so far, there doesn't seem to be a *foolproof* method of deleting all records one wishes to delete. Perhaps if A5 offered us the ability to manually *lock* multiple records or an entire table, this issue could be resolved.

--Michael

[Lenny Forziati]

RE: &lt;TBL&gt;.DELETE() - not what you might thin

Perhaps if A5 offered us the ability to manually *lock* multiple records or an entire table, this issue could be resolved.

t=table.open("mytable",FILE_RW_EXCLUSIVE)
will open your table in exclusive (locked) mode.

-Lenny

[Blake]

RE: &lt;TBL&gt;.DELETE() - not what you might thin

Michael--

I don't worry too much about multiple-users. A5v5 isn't really a multi-user DBMS, and I accept that a certain degree of, em, sloppiness is inherent. I spend a lot of time (probably too much) pondering whether to use references or copies, to avoid serous problems, and I accept that I may be leaving orphaned records lying around from time-to-time.

But in my current app, it's unlikely that there will be more than one person in any particular area of the database at the same time, and even more unlikely that two people will be looking at the same record at the same time while one is changing it.

Not elegant, but practical.

===Blake===

[whanigsberg]

RE: &lt;TBL&gt;.DELETE() - not what you might thin

Blake,

You write "I don't worry too much about multiple-users. A5v5 isn't really a multi-user DBMS...."

Given that (to choose just a couple of examples) I have 35 simultaneous users and Tom Henkel has many more I cannot imagine why you would make such a statement?

Bill

[Blake]

RE: &lt;TBL&gt;.DELETE() - not what you might thin

""Given that (to choose just a couple of examples) I have 35 simultaneous users and Tom Henkel has many more I cannot imagine why you would make such a statement?""

I know there are people who run it that way; my hat's off to you. However, I don't know the contexts under which these users are supported =and= I knew people who had similar user counts under dBASE III--also not a multi-user database.

What Alpha does is use OS file and record locks to avoid some multi-user conflicts, subject to the vagaries of the OS (opporutnistic locking can cause file corruption), and it adds you a few features to assist.

Rather than get into whether it's a "true" multi-user database, let's just say there's a gradient. A5's concurrency support isn't much higher level than the OS-calls--compared to Oracle or, let's say, Interbase, which allows versioning (keeping multiple versions of a record around to ensure read consistency).

Also, as is pointed out periodically, there are certain things you just can't do easily (if at all) in A5 that, say, a C/S system does by default (like log a user out).

No slight is meant by this. Clearly, supporting multiple users is =possible=. And if you can do all you need with it, with no concurrency problems, it's not without advantages for multiple users.

However, A5 is still, down at the metal, a series of standalone "enlightened" dBase III engines.

[whanigsberg]

RE: &lt;TBL&gt;.DELETE() - not what you might thin

Blake,

I gave myself a full day to digest your latest post.

You make a series of sophisticated points about network implementation which are easy to agree with but the essense of your argument is quite simple: a database is not multi-user unless it is client server.

Can you really mean this? I don't imagine most would agree. I know I don't

Of course there are advantages to client server but that is a separate issue.

Bill

[Blake]

RE: &lt;TBL&gt;.DELETE() - not what you might thin

""I gave myself a full day to digest your latest post.

No heartburn, I trust."s"

I wasn't =thinking= multi-user DBMS has to be client/server, but I have to agree with your analysis, and ask myself, "is that what I mean"?

Maybe. I'm trying to imagine some circumstance where a non-C/S database can fully support multiple-users, and I'm not coming up with anything.

To clarify, the term "client/server" is often used these days to refer to a specific setup of client program which connects directly to server program, differentiating it from multi-tier, web-based, XML, whatever.

But in the ultimate sense of: one program (server) to manipulate the database, yes, I don't think any other system is very robust for handling multiple users.

Again, this is in a general sense. The ingenuity of the A5 community never ceases to amaze me, and it's certainly right to point out that multi-user apps are written in A5 (heck, I'm doing that). But they rely heavily on programmer ingenuity, not on features inherent in the system.

On the philosophical side, however, we have a system designed to be a standalone single-user client AND server, and it shows: We actually strip powers OUT of the A5 client to build our apps (because we can't have people arbitrarily deciding to compact the database, modify tables, lock records and not unlock them, etc. etc. etc.). We all invent (and re-invent) all the security any of our apps have. We all have to handle simultaneity individually for each app. Etc. Etc.

If it's just semantics, here, I'll concede any point: Yes, you can have many people use an A5 app. And with some effort you can make it fairly safe for them to do so. If that's a "multi-user DBMS" in your mind, I won't argue.

If you can imagine some arrangement of fully-independent programs that can manipulate data AND fully address the simultenaity issues that arise with many users, I'd like to hear it. (I did think of =one= possibility: You could have a peer-to-peer DBMS. All users of a database would contact each other to handle simultaneity issues, rather than a master server. But in the long run, that's just making ALL the peers take turns being servers, instead of just having one. Structurally, different, though: Might be interesting.)

[whanigsberg]

RE: &lt;TBL&gt;.DELETE() - not what you might thin

Blake,

Thanks for the thoughtful response. I appreciate it.

In a way we do have (as you say) a question of semantics in the sense that given the diversity of this board's membership the assertion that A5 is not multi-user is likely to lead to misunderstanding.

As for your general analysis of the gradient of multi-user support, I find it useful and well taken.

Bill