Unique index fails if data is added to table after local SQL delete

[Pieter van Heerden]

I have found that if data is added to a table, with a unique index, after the table has been emptied by local SQL delete, the unique index fails on the newly added data. When the following commands are used:

//Test_U_Index
// Create a table for out origional data
set safety off

// Set the database name to whatever is convenient
d = new database()
d.databasename = "sapwat"
d.active = true

// Delete existing tables
if d.tableExists("ws_crop")
d.dropTable("ws_crop")
endif
if d.tableExists("ws_temp1")
d.dropTable("ws_temp1")
endif

// Create a table for holding the origionla data
create table ":sapwat:ws_crop.dbf"(;
WStation character(10), ;
Crop character(10))

// Fill the table with data
aWs = {"WS1", "WS2"}
aCrop = {"Apples", "Pears", "Prunes"}
q = new query()
q.database = d
q.sql = "select * from ws_crop"
q.active = true
for i = 1 to 2
for j = 1 to 3
q.rowset.beginappend()
q.rowset.fields["WStation"].value = aWs[i]
q.rowset.fields["Crop"].value = aCrop[j]
q.rowset.save()
endfor
endfor
q.active = false

// Now copy the structure of the table to another table
use ws_crop
copy structure to ws_temp1
use ws_temp1 exclusive
index on WStation tag wstationu unique
for i = 1 to 2
use ws_temp1
append from ws_crop
if i = 1
browse
use
delete from ws_temp1
else
set order to wstationu
browse
use
endif
endfor
d.active = false
set safety on


the table is shown as being empty, even though the newly added data shows if the index is not active.

If the table is packed, and the above commands are used, the relevant rows show as expected.

[kathy kolosky]

Hi Pieter. The problem here seems to be the deletion of the files in the
first section.

If you do not delete but, do the following directly after appending the
files to ws_temp1 you'll see two rows.

append from :databasename:ws_crop
use ws_temp1 exclusive
index on WStation tag wstationu unique
browse

Results ...

Wstation Crop
WS1 Apples
WS2 Apples

Thanks, Kathy
QA
dBI

Post by Pieter van Heerden
I have found that if data is added to a table, with a unique index, after
the table has been emptied by local SQL delete, the unique index fails on
//Test_U_Index
// Create a table for out origional data
set safety off
// Set the database name to whatever is convenient
d = new database()
d.databasename = "sapwat"
d.active = true
// Delete existing tables
if d.tableExists("ws_crop")
d.dropTable("ws_crop")
endif
if d.tableExists("ws_temp1")
d.dropTable("ws_temp1")
endif
// Create a table for holding the origionla data
create table ":sapwat:ws_crop.dbf"(;
WStation character(10), ;
Crop character(10))
// Fill the table with data
aWs = {"WS1", "WS2"}
aCrop = {"Apples", "Pears", "Prunes"}
q = new query()
q.database = d
q.sql = "select * from ws_crop"
q.active = true
for i = 1 to 2
for j = 1 to 3
q.rowset.beginappend()
q.rowset.fields["WStation"].value = aWs[i]
q.rowset.fields["Crop"].value = aCrop[j]
q.rowset.save()
endfor
endfor
q.active = false
// Now copy the structure of the table to another table
use ws_crop
copy structure to ws_temp1
use ws_temp1 exclusive
index on WStation tag wstationu unique
for i = 1 to 2
use ws_temp1
append from ws_crop
if i = 1
browse
use
delete from ws_temp1
else
set order to wstationu
browse
use
endif
endfor
d.active = false
set safety on
the table is shown as being empty, even though the newly added data shows
if the index is not active.
If the table is packed, and the above commands are used, the relevant rows
show as expected.

[Bruce Beacham]

Now - I do need to do a second, a third, etc. round using the same
table, with its index activated, to do a similar action as the first,
but with a different set of data. Therefore I clear out the table
with emptyTable() before starting the next round. And this is where
things seem to go wrong, because it is at this stage where only a
blank table is seen in browse and if the index is activated. (The
relevant records are there, I have checked.)

So the data is in the table but the browse window doesn't show them.
But if you call a second browse window _after_ the append, does that
show them?

If so, it sounds like a weakness in the signals between the database
functions and the browse window, suspicion falling on the browse window
as it's an old technology shortcut. The emptytable() is OODML whereas
the browse is XDML.

What happens if you use a grid to view the data through the process?


Bruce Beacham

[Geoff Wass [dBVIPS]]

Bruce and Pieter;
I set up a program (attached) that uses grids to view the data. It seems to
display the (ws_temp1) data correctly. After deletion and re-appending, with
the unique index, the two rows are seen. But I noticed some suprising
details.
A. When Ws_temp1 has been emptied like this: [
_app.databases[1].emptytable('ws_temp1.dbf')], and re-filled, and the unique
index is set, the two unique non-deleted rows display correctly in the grid.
But when WS_temp1 is emptied like this [delete from ws_temp1], those two
rows don't display.
B. Regardless of which way Ws_temp1 is emptied, the two unique non-deleted
[index on WStation tag wstationu unique for not deleted()].
But I couldn't find any way to fix the problem when browsing.
Eric Logan

Eric,

.emptyTable() == ZAP
DELETE FROM... == DELETE FOR... or any other way of deleting rows

ZAP does a PACK. DELETE does not. So even though a row is deleted, dBASE
is still aware of the index information related to it and can affect
UNIQUE indexes.

[Eric Logan]

Post by Geoff Wass [dBVIPS]
Eric,
.emptyTable() == ZAP
DELETE FROM... == DELETE FOR... or any other way of deleting rows
ZAP does a PACK. DELETE does not. So even though a row is deleted, dBASE
is still aware of the index information related to it and can affect
UNIQUE indexes.
Geoff Wass [dBVIPS]

Thank you for pointing that out.
So the problem that Pieter reported seems to have two parts. The part
related to indexes still happens when the table is viewed with grids.
Emptytable() could be a workaround. But there are additional problems
associated with the browse.
E.L.

[Geoff Wass [dBVIPS]]

Post by Eric Logan

Post by Geoff Wass [dBVIPS]
Eric,
.emptyTable() == ZAP
DELETE FROM... == DELETE FOR... or any other way of deleting rows
ZAP does a PACK. DELETE does not. So even though a row is deleted, dBASE
is still aware of the index information related to it and can affect
UNIQUE indexes.
Geoff Wass [dBVIPS]

Thank you for pointing that out.
So the problem that Pieter reported seems to have two parts. The part
related to indexes still happens when the table is viewed with grids.
Emptytable() could be a workaround. But there are additional problems
associated with the browse.
E.L.

Eric,

I have not checked, but that sounds correct, especially the part about
the indexes and emptyTable().

[Eric Logan]

Geoff;
I am certain that one of the problems originally observed by Pieter occurs
because an open browse window responds immediately to the 'use' command, and
then appears to display an empty table.
For Pieter's program to perform 'correctly', two things are necessary. 1)
Eliminate deleted rows by doing '..emptytable(..)' instead of 'delete from'.
2) After the second browse is opened, insert a command like 'sleep 2' to
allow the data rows to be seen before they're wiped out by the 'use'
command.
This is an example of why 'browse' should only be used for interactive work,
not user systems.
E.L.

Post by Geoff Wass [dBVIPS]

Post by Eric Logan
Thank you for pointing that out.
So the problem that Pieter reported seems to have two parts. The part
related to indexes still happens when the table is viewed with grids.
Emptytable() could be a workaround. But there are additional problems
associated with the browse.
E.L.

Eric,
I have not checked, but that sounds correct, especially the part about
the indexes and emptyTable().
Geoff Wass [dBVIPS]

[Geoff Wass [dBVIPS]]

Post by Eric Logan
Geoff;
I am certain that one of the problems originally observed by Pieter occurs
because an open browse window responds immediately to the 'use' command, and
then appears to display an empty table.
For Pieter's program to perform 'correctly', two things are necessary. 1)
Eliminate deleted rows by doing '..emptytable(..)' instead of 'delete from'.
2) After the second browse is opened, insert a command like 'sleep 2' to
allow the data rows to be seen before they're wiped out by the 'use'
command.
This is an example of why 'browse' should only be used for interactive work,
not user systems.
E.L.

Eric,

You make some good points. I agree with your conclusion. It is best to
stick to GRIDs.

[Pieter van Heerden]

Kathy

The sequence of events where I experience the problem:

I load data into a temporary file in which the index exist. I work with the data and every rocord that should show, does show. This is more-or-less where your response takes me, and I fully agree up to this point. Thanks.

Now - I do need to do a second, a third, etc. round using the same table, with its index activated, to do a similar action as the first, but with a different set of data. Therefore I clear out the table with emptyTable() before starting the next round. And this is where things seem to go wrong, because it is at this stage where only a blank table is seen in browse and if the index is activated. (The relevant records are there, I have checked.)

Post by kathy kolosky
Hi Pieter. The problem here seems to be the deletion of the files in the
first section.
If you do not delete but, do the following directly after appending the
files to ws_temp1 you'll see two rows.
append from :databasename:ws_crop
use ws_temp1 exclusive
index on WStation tag wstationu unique
browse
Results ...
Wstation Crop
WS1 Apples
WS2 Apples
Thanks, Kathy
QA
dBI

Post by Pieter van Heerden
I have found that if data is added to a table, with a unique index, after
the table has been emptied by local SQL delete, the unique index fails on
//Test_U_Index
// Create a table for out origional data
set safety off
// Set the database name to whatever is convenient
d = new database()
d.databasename = "sapwat"
d.active = true
// Delete existing tables
if d.tableExists("ws_crop")
d.dropTable("ws_crop")
endif
if d.tableExists("ws_temp1")
d.dropTable("ws_temp1")
endif
// Create a table for holding the origionla data
create table ":sapwat:ws_crop.dbf"(;
WStation character(10), ;
Crop character(10))
// Fill the table with data
aWs = {"WS1", "WS2"}
aCrop = {"Apples", "Pears", "Prunes"}
q = new query()
q.database = d
q.sql = "select * from ws_crop"
q.active = true
for i = 1 to 2
for j = 1 to 3
q.rowset.beginappend()
q.rowset.fields["WStation"].value = aWs[i]
q.rowset.fields["Crop"].value = aCrop[j]
q.rowset.save()
endfor
endfor
q.active = false
// Now copy the structure of the table to another table
use ws_crop
copy structure to ws_temp1
use ws_temp1 exclusive
index on WStation tag wstationu unique
for i = 1 to 2
use ws_temp1
append from ws_crop
if i = 1
browse
use
delete from ws_temp1
else
set order to wstationu
browse
use
endif
endfor
d.active = false
set safety on
the table is shown as being empty, even though the newly added data shows
if the index is not active.
If the table is packed, and the above commands are used, the relevant rows
show as expected.