Experimental/Research-FoxPro.SoftDeleteSupport.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
<#
.SYNOPSIS
Show how FoxPro (and dBase) 'soft deletes' work
 
 
.NOTES
When you delete records from a FoxPro table, they are only marked as deleted.
They remain in the table, unseen, until you "PACK" that table.
 
You can see these records using the "SET DELETED OFF" command, which means
"show the deleted rows". It seems backwards but has been proven experimentally.
 
Once you issue the "SET DELETED OFF" command, you can tell the difference
between deleted rows and non-deleted rows by using the IsDeleted() function.
 
FoxPro and dBase work the same way, in this respect.
 
#>


$ConstantsFile = Join-Path -Path (Split-Path $PSScriptRoot) -childpath "Tests\constants.ps1"
. $ConstantsFile 

Write-Verbose -Verbose -Message "FoxPro Db Path: $script:FoxProDbPath"

# clean up from last time
Get-ChildItem -path $FoxProDbPath -Filter presdent.* | Remove-Item


try {

    $cn = Get-FoxProConnection -DataSource $FoxProDbPath

    # This creates a test table, with indexing
    # the basic idea is here: https://stackoverflow.com/questions/11653407/oledb-and-visual-foxpro
    Invoke-FoxProQuery -as NonQuery -Connection $cn -Query "CREATE TABLE presdent(fname v(20), lname v(20), presdentid i)"
    Invoke-FoxProQuery -as NonQuery -Connection $cn -Query "EXECSCRIPT([USE presdent IN SELECT(0) EXCLUSIVE])"
    Invoke-FoxProQuery -as NonQuery -Connection $cn -Query "EXECSCRIPT([INDEX ON lname TAG idx_lname])"
    Invoke-FoxProQuery -as NonQuery -Connection $cn -Query "EXECSCRIPT([INDEX ON fname + lname TAG idx_xname])"
    Invoke-FoxProQuery -as NonQuery -Connection $cn -Query "EXECSCRIPT([INDEX ON presdentid TAG pkpresdent CANDIDATE])"

    # Note: indexing on DELETED doesn't work here.
    # Why? See 'binary indexes', https://www.codemag.com/Article/0404022/What's-New-with-Data-in-Visual-FoxPro-9
    # "EXECSCRIPT([INDEX ON DELETED() TAG DELETED BINARY])"

    # Now that we have the table built, we can put some data into it.
    $QueryInsert = "insert into presdent (fname,lname,presdentid) values ('george','washington',1)"
    Invoke-FoxProQuery -as NonQuery -Connection $cn -Query $QueryInsert

    $QueryInsert = "insert into presdent (fname,lname,presdentid) values ('john','adams',2)"
    Invoke-FoxProQuery -as NonQuery -Connection $cn -Query $QueryInsert

    $QueryInsert = "insert into presdent (fname,lname,presdentid) values ('thomas','jefferson',3)"
    Invoke-FoxProQuery -as NonQuery -Connection $cn -Query $QueryInsert

    # show the data we just inserted
    $Query = "SELECT 'initial' event, *, deleted() isdeleted, recno() RowNo FROM presdent"
    Invoke-FoxProQuery -Connection $cn -Query $Query |
        Format-Table -AutoSize

    $QueryDelete = "DELETE FROM presdent WHERE presdentid = 2"
    Invoke-FoxProQuery -Connection $cn -as NonQuery -Query $QueryDelete

    $Query = "SELECT 'deleted1' event, *, deleted() isdeleted, recno() RowNo FROM presdent"
    Invoke-FoxProQuery -Connection $cn -Query $Query |
        Format-Table -AutoSize

    # "SET DELETED OFF" means "show the deleted rows". It seems backwards but has been proven experimentally.
    $QuerySet = "SET DELETED OFF"
    Invoke-FoxProQuery -Connection $cn -as NonQuery -Query $QuerySet

    $Query = "SELECT 'deletedoff' event, *, deleted() isdeleted, recno() RowNo FROM presdent"
    Invoke-FoxProQuery -Connection $cn -Query $Query |
        Format-Table -AutoSize

    # If you don't go through this to reset the connection, PACK can't get exclusive access to the table.
    # I presume there is an easier way to do this, but I don't know it and this works.
    # dstrait, 2019/08/10
    $cn.Close()
    $cn.Dispose()
    $cn = $null
    $cn = Get-FoxProConnection -DataSource $FoxProDbPath

    # Now, pack, set DELETED again and show that Adams is really gone.
    # You can verify this by looking at the RowNo values.
    $QueryInsert = "PACK presdent"
    Invoke-FoxProQuery -as NonQuery -Connection $cn -Query $QueryInsert

    $QuerySet = "SET DELETED OFF"
    Invoke-FoxProQuery -Connection $cn -as NonQuery -Query $QuerySet

    $Query = "SELECT 'postpack' event, *, deleted() isdeleted, recno() RowNo FROM presdent"
    Invoke-FoxProQuery -Connection $cn -Query $Query |
        Format-Table -AutoSize
}

catch {
    throw
}


Finally {
    $cn.Close()
    $cn.Dispose()
    $cn = $null
}

# clean up
Get-ChildItem -path $FoxProDbPath -Filter presdent.* | Remove-Item