Peter Ypenburg wrote:

>
> When thinking about it what we need is to be able to take a database and
> delete all the rows on all the tables and keep the tables, indexes,
> constraints all in place.

There's a very quick procedure that has exactly the same result. First,
make a metadata backup of your database using gbak (-m switch). Restore
that, and you've got an empty database with exactly the same structure
as the one you were working on.

Yes, it is possible to analyze the system tables and determine the
dependencies and thus the order in which data can be deleted. Helen's
book should give you enough information - and most of what you need is
in the InterBase V6 beta docs that you can download through ibphoenix.

However, the analysis is complex and results in an much slower solution
than a backup/restore of the database's metadata. When you delete
records, Firebird doesn't actually go and remove them. It creates a
deleted stub. Then, a later pass over the data notices that the delete
is "mature" and removes the record. In the process it actually removes
both the deleted stub and the previous record version - possibly other
record versions depending on how the database has been (ab)used. For
each distinct value of each key field in each table, it must traverse
the index and remove the relevant entry. Indexes with long duplicate
chains become a significant performance issue because records are
generally stored (and thus removed) FIFO and indexes are stored LIFO.

Why bother?

Good luck,


Ann