Is it possible to recover deleted records?

In theory it is as they are not removed until you reorganize the table, but at the moment there is now tool to show deleted records so you would need to do it manually with HexEdit etc.

ON DELETING RECORDS

One of the most important aspects of aDatabase or System, (leaving aside security and structure concepts) is the ability to record what is required.

The second most important aspect is the ability to report on that data.

The third most important aspect, are the tools that allow you to maintain, manipulate, repair and recover that data.

In fact I think that the third aspect should be the primary consideration, as if you design those tools from the outset, then it makes for easier maintenance later.

Deleting records by accident is a frequent situation, and it is difficult to give users freedom to use the system and at the same time protecting the system from the user!

In this case the deletion affects the parent record and two child records.

There are fifty tables in this database. Of course they use old fashioned table names of consisting of 8 characters and an extension, so only a bit useful in identifying the database name.

The first four letter of the database use the first four names of the table, and as some of the tables share the same first four letters, this is not helpful in identifying which table does which.

In DE for DOS this could be identified under option

6 Database Maintenance, then 1 Database Status then 1 Forms (for database names)

And

6 Database Maintenance, then 1 Database Status then 2 Procedures

Pic 01

Pic 02

In DE8 the options are under Application

Pic 03

Then Status > Records would show us the relevant Dos File and is found under

Application – Status - Records,

However forms and procedures are found under

Application – Status – Documents

Pic04

To show how deleted records and deleted records are shown, I created a very simple two field text form with Field1 and Field2

Viewing this in HxD editor shows

Empty file

Pic05

After one entry with contents as below

Pic06

Pic07

Pic08

After second entry with contents as below

Pic09

Pic10

Pic11

After 3^rd entry

Pic12

Pic13

So in tabular form in DE8 it shows

Pic14

We now mark entry 3 as deleted. In tabular form it changes the line to pale blue.

Pic15

However if you delete it in form mode it shows (there is only one form in this example)

Pic16

(If you then return to tabular view it show the entry at line 4, and you cannot scroll backwards!! – bug ??? )

Pic17

And the result in HxD editor is

Pic18

If we then enter a fourth record into the database it shows the following.

In table mode

Pic19

Pic20

In HxD

Pic21

If you run compare in HxD editor it shows that first table (with the deleted record) differs by the entry OD against OC in the original.

Pic22

Changing the highlighted entry OD to OC in HxD

Pic23

Results in the record being restored in DE8

Pic24

This worked on a simple single database.

Of course the whole issue of deleting a record in a database could and should have been avoided, by not permitting the user direct access onto the tables, and by using a data entry form to enter data – but that is another story!

On a database with a parent and child tables, then there are further problems………

As a postcript to this....

plus ça change, plus c'est la même chose  Except in this case it is 

plus ça change, mais n'est pas la même chose

https://groups.google.com/forum/#!topic/comp.datab...

So why does Ctrl-F3/record# not work or been implemented in DE8 ?

There is many increments between DFD 4.2 (4.53, 5.53) and DE8 so it is important to think when things changed ...

One of the biggest problems with improving migration from 6.x to 8.x was that 7.x was in between. It is no secret that 7.x was a major "blunder" on so many levels from incentive and motivation for making 7.x in the first place, through quality of work, project management, QA etc.

I won't spend much time on it, but for us it has been a major headache as we have to think of backwards compatibility for functionality that shouldn't really be there in the first place.

To be honest, this obtuse disregard for "history" is very much what has blighted DFW from the start. Good intentions came in the way of practical options and reality.

However the lack of Searching up a Record by number is not one of these. In DataEase for DOS people had a "personal" relationship to records. YOu would know that customer X (which you could never remember the name of) was 3 records before customer Y which you always remembered the name of. So you would search up Customer Y and then simply SHift-F3 back to X.

I won't lecture but this is a big NO NO in relational Databases. The best thing with DFW is that it took the step to start working with cursors and data-sets.

Where a search in DFD would find the first record in the search and then the next and the next, DFW will search up the complete dataset that is defined by the filter and return that Multi-view to you.

In DFW you NEVER work on the table. You always work on a Cursor so there is no Record Number for you to go to as it will be dynamic dependent on the search/filter you put in.

However!

This doesn't mean that you shouldn't be able to view or search up a deleted record.

It would be a small thing to simply include a Deleted=Yes in some way in a search to display all the deleted records or search only in deleted records.

OK I agree, however recovering records has been there since at least dBase !!! which I used on the Commodore Business Pet !!!!

dBase II user Guide 1983. ( CPM on NEC ) ( download it below - makes interesting reading if nothing else )

Page 7-8

CLEANING UP A DATA BASE

Deleting Records

Removing records from a data base is a two-step process. First, identify the records to be deleted using either of the methods outlined below. This logically deletes the records but does not actually remove them from the data base, which is helpful in preventing catastrophic losses of data. You have a chance to recover your data before physically removing it from the data base. Records marked for deletion app-ear on LISTs and DISPLAYs of the data base. However, dBASE II bypasses these records in most other operations. DELETING FROM THE COMMAND MODE DELETE alone deletes the current record. It places an asterisk, called th] [FOR ] • The default for is NEXT 1 (the current record). To delete a record other than the current record, use the phrase: ALL, RECORD , or NEXT . • To make the deletions conditional, expand the command with the FOR phrase. • When both the scope and a conditional phrase are used, the system deletes aU records within the scope for which the expression is true.

Restoring Records

Restoring Records to the Data Base Records marked for deletion can be recovered. In edit mode, CTRL U toggles the deletion mark on and off. In command mode, RECALL restores records. RECALL [] [FOR ] RECALL operates exactly the opposite of DELETE. It removes the deletion mark from records. Scope and condition are optional. Scope defaults to the current record. If a conditional expression is used, it does not have to be the same one that was used to mark the records for deletion.

Foxpro Function Commands

Set deleted on – shows deleted record

Set delete off – hides deleted records

VISUAL FOXPRO

DELETE - SQL Command

Visual Studio 2005

Marks records for deletion.

DELETE [Target] FROM [FORCE] Table_List [[, Table_List ...] | [JOIN [ Table_List]]] [WHERE FilterCondition1 [AND | OR FilterCondition2 ...]]

Parameters

DELETE [Target]

Specifies a target table, cursor, table or cursor alias, or file for the delete operation. If the FROM specifies more than one table, you must include the Target parameter. Target can have the following syntaxes:

[DatabaseName!]TableName

DatabaseName! specifies the name of a database containing the table if the table is in a noncurrent database. If the table is in a noncurrent database, you must include the name of the database. Use an exclamation point (!) as a delimiter immediately following the database name and preceding the table name.

TableName specifies the name of a table for the delete operation.

Alias

Alias specifies an alias that matches a table in the FROM clause or a cursor in the current data session for the delete operation.

FileName

FileName specifies the name of a file for the delete operation.

FROM [FORCE] Table_List[[, Table_List...] | [JOIN [Table_List]]]

Specifies one or more tables containing the data for the delete operation.

The FROM clause has the same syntax as in the SQL SELECT command except for the following restrictions:

The target table or cursor cannot be included in an OUTER join as a secondary table or cursor.

It should be possible to evaluate all other JOIN operations before performing a JOIN operation on the target table.

The target cursor cannot be the result from a subquery.

For more information, see SELECT - SQL Command.

FORCE specifies that the tables in Table_List are joined in the order they appear in the FROM clause.

Table_List can have the following syntaxes:

[DatabaseName!]Table [[AS] Local_Alias]

DatabaseName! specifies the name of a database containing the table if the table is in a noncurrent database. If the table is in a noncurrent database, you must include the name of database. Use an exclamation point (!) as a delimiter immediately following the database name and preceding the table name.

Table specifies the name of the table or cursor you want to retrieve data from. If no table is open, Visual FoxPro displays the Open dialog box so you can specify the file location. After the table opens, it remains open when the query is complete.

Local_Alias specifies a temporary name for the table specified in Table. If you specify a local alias, you must use the local alias instead of the table name throughout the DELETE statement. The alias can represent a table or a cursor.

JOIN provides the capability for specifying one or more secondary tables. There is no hard-coded limit on the number of tables, aliases, or JOIN clauses per DELETE statement.

(Subquery) AS Subquery_Alias

Subquery specifies a SELECT statement within another SELECT statement. For more information about subqueries in SELECT statements, see SELECT - SQL Command - FROM Clause.

WHERE FilterCondition1 [AND | OR FilterCondition2...]

Specifies one or more filter conditions that records must meet to be deleted. There is no limit to the number of filter conditions in the WHERE clause.

To reverse the value of a logical expression, use the NOT operator. To check for an empty field, use the EMPTY( ) function. For more information, see EMPTY( ) Function.

Remarks

If the SET DELETED command is set to ON, records marked for deletion are ignored by all commands that include a scope. For more information, see SET DELETED Command.

RECALL Command

Visual Studio 2005

Unmarks records marked for deletion in the selected table.

RECALL [Scope] [FOR lExpression1] [WHILE lExpression2] [NOOPTIMIZE]

[IN nWorkArea | cTableAlias]

Parameters

Scope

Specifies a range of records to recall. The default scope for RECALL is the current record (NEXT 1).

Only the records that fall within the range specified are recalled. The scope clauses are: ALL, NEXT nRecords, RECORD nRecordNumber, and REST.

For more information on scope clauses, see Scope Clauses.

FOR lExpression1

Specifies that only the records for which lExpression1 evaluates to true (.T.) are recalled. This option allows you to filter out undesired records.

Rushmore Query Optimization optimizes a RECALL FOR if lExpression1 is an optimizable expression. For best performance, use an optimizable expression in the FOR clause.

For more information, see SET OPTIMIZE Command and Using Rushmore Query Optimization to Speed Data Access.

WHILE lExpression2

Specifies a condition whereby records are recalled for as long as lExpression2 evaluates to true (.T.).

NOOPTIMIZE

Prevents Rushmore optimization of RECALL

For more information, see SET OPTIMIZE Command and Using Rushmore Query Optimization to Speed Data Access.

IN nWorkArea | cTableAlias

Specifies the workarea or table alias affected by the RECALL command. Use this clause to specify a workarea or a table outside the current work area.

Remarks

You can use RECALL to recover records, provided you have not issued PACK or ZAP.

........

So if there was a marker that could be identified using DQl, then that solves part of the problem !.