detect database modification

[kgasman]

I am writing a back up script. How do I detect if the database has been modified or just viewed

kevin

[Raybaudi]

Hi

I think that you can solve your problem with an appropriate use of the Get(RecordModificationCount) function.

[LaRetta]

In an attempt to head off a possible train wreck ...

There are many factors which should be considered such as: Are you networked? Using FM Server? You don't want other copies of the file residing anywhere (they should be zipped). You don't want to back up an OPEN FILE either. This is a big subject and the best suggestion can ONLY happen once we know more. Get(RecordModificationCount) applies to RECORDS. It would need to check each table. In a large solution (with many records xor tables), this wouldn't be a good option.

Can you provide more information about your setup?

[Raybaudi]

BTW if your solution is a simple one, you can try this file as an example :)

LastModification.zip

[LaRetta]

If it were me ...

1. I wouldn't add 3 unstored calculations (one of which is an AGGREGATE) to every table.

2. I wouldn't open Field Definitions and add a field in the DB Modifications calc every time I create a new field.

3. I wouldn't add these three unstored calculatons to EVERY table I create. I probably wouldn't remember.

4. I wouldn't count how many record modifications there were because it is moot - HAS THE FILE CHANGED OR NOT? Whether you determine 32,424 changes were made or 1 makes no difference ... you still need to back up the entire file.

5. It also doesn't take into account design changes which are just as important to capture.

Of course, that's just me; I've been called fussy.

I am concerned about the backup process itself.

LaRetta

[kgasman]

thanks for the great info.

I wrote a filemaker pro 7 database program for my wife's practice. I wanted to create backups of the database for each time she changed something. Ideally I would keep only the last 2 months of backups and delete the rest. Since you can only make a file "Database Copy.fp7" from within Filemaker pro 7, I thought I would write an Applescript that could be called from within filemaker. I would set filemaker pro to call a filemaker script at shut down that would first make a generic backup of the database "Database Copy.fp7" in a backup folder "clientbackups". Then the filemaker pro script would perform an applescript to rename the file with a date and timestamp. I would then have the applescript delete all the files in the backup folder that were older than 2 months. As another wrinkle since the database is used on a desktop and laptop computer that are synchronized I didn't want the Applescript to delete files created on the desktop when run on the Laptop and vis a versa. My solution was to have the Filemaker script determine if the database was being run on the desktop or the Laptop and run the Applescript designed for each computer. The major difference was that the Applescript would add "L-" before the database name if on the laptop or "D-" before the name if on the desktop.

The following is the filemaker script:

Save a Copy as [ "file:clientbackups/database Copy.fp7" ] [compacted copy (smaller)]

Pause/Resume Script [ Duration (seconds) 2]

If [ Get ( HostName ) = "Kevin desktop" ]

Perform AppleScript [ ]

Exit Script

End If

If [ Get ( HostName ) = "Kevin Laptop" ]

Perform AppieScript [ ]

Exit Script

End If

I am a novice at applescript but from searching Macscripter.com site I came up with the following is the Applescript for the laptop:

tell application "Finder"

set somedate to current date

set shortsomedate to short date string of somedate

set hrsomedate to hours of somedate as number

set minsomedate to minutes of somedate as number

set secsomedate to seconds of somedate as number

set the name of file "Macintosh HD:Users:AllynLaptop:Documents:Allyn Clients:clientbackups:ClientDatabase Copy.fp7" to "L-ClientDatabaseBKUP" & shortsomedate & " " & hrsomedate & "-" & minsomedate & "-" & secsomedate & ".fp7"

set thefolder to alias "/Users/AllynLaptop/Documents/Allyn Clients/clientbackups"

delete (items of thefolder whose creation date < ((current date) - (60 * days)) and name contains "L-")

end tell

for the Desktop I just substitute the desktop file path names and prepend "D-" to the backup file name.

The problem with this solution is that I get backups even if nothing has changed in Filemaker database. So I am trying to find a way to tell if the data has been modified or just viewed so I will only backup if necessary

kevin

[comment]

You could set a global field gOpened with Get (CurrentTimeStamp) upon opening the file. Then, on closing, check in each table if Max ( Table::Modified ) > gOpened, and if so, run your backup script.

You will of course need a Modified field (auto-enter modification timestamp) in each table for this.

To deal with records being deleted, you can add another global field, gLastModified. Any time you delete a record or a portal row, add a line to the script that sets gLastModified to Get (CurrentTimeStamp). Do not allow deletion other than by script. Then your closing script can be something like:

#REPEAT THIS FOR EVERY TABLE

Go to Layout [ "A" (TableA) ]

If [ Max ( TableA::Modified ) > Preferences::gLastModified ]

Set Field [ Preferences::gLastModified; Max ( TableA::Modified ) ]

End If

#

If [ Preferences::gLastModified > Preferences::gOpened ]

Perform Script [ your backup script ]

End If

[kgasman]

Thanks

I am going to try that.

I wonder if I can capture the Modified state that the finder keeps on files and use that in the applescript?

Kevin

[comment]

I don't think that would be very useful to you. Filemaker will modify its file even when no "real" data had been modified, e.g. if you have modified some global field used only for viewing, or if a table has been sorted in a different order.

[kgasman]

You are right about the finder modification state.

Ok so would I put the gOpened global field in a separate table unrelated to the rest of the database?

thanks so much for all the help

[comment]

It doesn't really matter where you put it. Global fields can be accessed from any table, even unrelated.

[Oldfogey]

This is a very interesting discussion BUT I'd suggest going back to basics.

How often are changes made?

How hard are they to re-make?

How vital is the data?

How often is the database opened and closed relative to the frequency of change?

If the DB is complex (ie multiple files with many relationships), then see LaRetta's post.

Why not back up the lot each day/hour/minute/week or after every close?

KISS

Edited March 30, 2006 by Guest

[kgasman]

Although I do agree that the backup process, ie using retrospect, is most important I had two concerns.

One I am writing this database for someone else to use who may not be as deligent in backing up their hard disks. In light of the fact that this database is used on both laptop and desktop and some times they forget to sync the two.

Second as the database grows in size I don't want more copies of the database than is necessary taking up laptop disk space.

Thanks for all the input