Something I find I'm building in more and more is a facility to archive data for a given table so that is isn't deleted, can be restored but doesn't get in the way of day-to-day operations. Up to now I've been producing code to do it myself but I think it would make a nice addition to ElevateDB.

It would be something like

ARCHIVE FROM fromtable TO totable [WHERE <where clause>] [ROLLBACK ON LOCKED ITEMS] [IGNORE LOCKED ITEMS] [TWO PASSES]

and

RECOVER FROM fromtable TO totable WHERE

it would operate roughly like:

totable and fromtable would have to be different.

If the totable didn't exist it would be created as a clone of the fromtable without any triggers, defaults or constraints apart from the primary key and with indices. Any autoinc  (INTEGER GENERATED ALWAYS?) fields would be replaced by simple integers and any computed field would vanish.

The flags would go along the lines of:
IGNORE LOCKED ITEMS
If when doing the transfer a row had an edit lock in place that row would be skipped on this pass

TWO PASSES
would allow the programmer to define a number of passes so that if some rows are locked the first time a second pass will be tried

ROLLBACK ON LOCKED ITEMS
If there are items that haven't transfered after two passes (or one) then rollback

RECOVER would have to have a mechanism for putting the values into the autoincs so that new numbers are not generated.

Roy Lambert

Roy,

<< Something I find I'm building in more and more is a facility to archive
data for a given table so that is isn't deleted, can be restored but doesn't
get in the way of day-to-day operations. Up to now I've been producing code
to do it myself but I think it would make a nice addition to ElevateDB. >>

I won't go into why the locking aspects are a little off with your proposal,
but what you're proposing is essentially the backup/restore with an
additional modification to IDENTITY columns.  If the time required to do a
backup is prohibitive, then a better alternative is to use the replication
to do a hot backup of the data in real/near real time.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy Lambert wrote:
> Something I find I'm building in more and more is a facility to archive data for a given table so that is isn't deleted, can be restored but doesn't get in the way of day-to-day operations. Up to now I've been producing code to do it myself but I think it would make a nice addition to ElevateDB.
> ...

Couldn't you use triggers? (In another type of database) I use
AfterDelete and AfterUpdate triggers to keep a "history of changes" in
separate tables.
Restoring these records is usually done "very carefully" (when fixing up
some records) - it happens no more than once in several years.

--
Aage J.

Tim


><< Something I find I'm building in more and more is a facility to archive
>data for a given table so that is isn't deleted, can be restored but doesn't
>get in the way of day-to-day operations. Up to now I've been producing code
>to do it myself but I think it would make a nice addition to ElevateDB. >>
>
>I won't go into why the locking aspects are a little off with your proposal,

Shame - I might learn something

>but what you're proposing is essentially the backup/restore with an
>additional modification to IDENTITY columns.

Obviously not well enough explained if you can come to that conclusion - yet another case of "I know what I mean" I suspect.

Take the simplest case - the email log file. The app has the capabilities for creating logs at various points in the process as well as for errors. Generally their usefulness is short term (ie did I send that email which seems to have vanished) but occasionally is useful long term (about 7 years ago I would have been unable to prise a lot of money out of a company who were trying to duck out of paying an invoice if I hadn't had them). The idea is "live" logs will be kept for c1month then the data will be added to an archive table. Both live and archive will be subject to the backup regime.

A more complex case is the Contacts table. When people vanish (eg change jobs, move house, swap mobiles) so that I can no longer contact them I want to keep the data just in case they resurface but I want to exclude them from normal operations.

I can achieve what I want by writing code but as always, being lazy, I thought it would make a nice addition.

If the time required to do a
>backup is prohibitive, then a better alternative is to use the replication
>to do a hot backup of the data in real/near real time.

Its not the time for it to run but the time for me to write (see just me being lazy). I can't see replication working for what I want but I'll give it some thought after beating the current stuff into submission.

Roy Lambert

Aage


Thanks for the suggestion, but its not what I'm trying to achieve.

Roy Lambert

Roy,

<< Shame - I might learn something >>

Well, the basics are that you need a covering read lock on the table(s)
being archived and that row locks have no bearing on the situation.

<< Obviously not well enough explained if you can come to that conclusion -
yet another case of "I know what I mean" I suspect. >>

I was referring to the use of the backup/restore facilities as the method by
which you store the archived data.  The moving of the individual rows would
still be subject to how you plan on moving them out of the current database
tables into the archive tables.  The backup/restore will provide the
appropriate covering locks on the archive tables being backed up or
restored, and then you can move the data to/from the current database as you
wish.

<< I can achieve what I want by writing code but as always, being lazy, I
thought it would make a nice addition. >>

Well, like you say, the issue is to whether it is really saving all that
much effort.  At most I would think that we're talking about an INSERT
INTO..SELECT combined with a DELETE FROM.. followed by a BACKUP DATABASE...

<< Its not the time for it to run but the time for me to write (see just me
being lazy). I can't see replication working for what I want but I'll give
it some thought after beating the current stuff into submission. >>

Replication will allow you to log everything into the archive tables, but at
batched intervals of your choosing (SAVE UPDATES on source database and LOAD
UPDATES on backup/archive database).  Think of the replication as "delayed
triggers", and you start to get an idea of what a hot backup would do for
you.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>Well, like you say, the issue is to whether it is really saving all that
>much effort. At most I would think that we're talking about an INSERT
>INTO..SELECT combined with a DELETE FROM.. followed by a BACKUP DATABASE...

You've forgotten the CREATE TABLE But to compensate the BACKUP DATABASE isn't needed. Backup is a separate issue.

Roy Lambert