Provide an API to delete a database (or origin)

[GoogleCodeExporter]

michaeln says:
The proposed API of
db.close(opt_delete_db_file)
sounds pretty good

Original issue reported on code.google.com by gears.te...@gmail.com on 11 Jun 2007 at 3:06

[GoogleCodeExporter]

db.close(opt_delete_db_file) is annoying if your only goal is to delete the 
database
file.  I think we should just have the obvious reflection API, something to 
enumerate
database names, something to delete them, something to rename them, perhaps even
something to copy them (at the filesystem level rather than the SQLite level, 
this
would be useful for backing things up before a schema change).  I could even see
utility in setting a database read-only.

Original comment by Scott.Hess on 11 Jun 2007 at 3:32

[GoogleCodeExporter]

Original comment by gears.te...@gmail.com on 27 Jun 2007 at 7:38

• Changed state: Accepted
• Added labels: Milestone-M3
• Removed labels: Priority-Low

[GoogleCodeExporter]

Original comment by othman@gmail.com on 5 Sep 2007 at 7:46

• Added labels: Version-later
• Removed labels: Milestone-M3

[GoogleCodeExporter]

Arv said:

Currently, there is no way to remove the database using a Gears API.  The 
closest thing is to iterate over the sqlite_master and drop all tables and then 
do a 
VACUUM.  This is very bad for several reasons.

* It is very slow
* It will not work on a locked/broken database (Othman got a broken DB a
while back)
* Skipping vacuuming will leave large files on the users disk that will
never be reclaimed.

Original comment by gears.te...@gmail.com on 23 Jan 2008 at 12:54

• Added labels: Version-0.3
• Removed labels: Version-later

[GoogleCodeExporter]

Dimitri asked:

Should this be an API functionality, like db.remove(name) or something only 
available using dev tools?

Original comment by gears.te...@gmail.com on 23 Jan 2008 at 12:54

[GoogleCodeExporter]

Is VACUUM currently black listed?

Original comment by erik.arv...@gmail.com on 25 Jan 2008 at 4:26

[GoogleCodeExporter]

VACUUM is compiled out.  The goal is to have things work without the need to 
VACUUM.
 If something comes up which doesn't work without VACUUM, then we should address that
something.  Cleaning up disk space definitely qualifies.

While I think apps should practice good hygiene around database existence, 
deleting
databases they no longer use, etc, I think that we shouldn't require apps to 
manage
their overall disk footprint.  That kind of thing should be done via the Gears
Settings panel (for instance, by providing quotas, or letting users look for 
big apps
which haven't been used in a long time).

Note that Gears sets auto_vacuum mode.  In auto_vacuum, SQLite will truncate 
the file
to the last non-free page as appropriate.  So if you drop all tables from the
database, it SHOULD result in a very small database file.  A couple simple 
trials
with a sqlite3 command-line seem to bear this out, by YMMV.

Original comment by Scott.Hess on 25 Jan 2008 at 5:19

[GoogleCodeExporter]

BTW, WRT Dmitri's question, I think this should definitely be part of the 
standard
API.  To a certain extent, database names are simply part of the schema, like 
tables
or indices, and if apps want to change their database layout, they should be 
able to
do so.

Original comment by Scott.Hess on 25 Jan 2008 at 5:20

[GoogleCodeExporter]

Lowering priority.  Issue 376 (handle db corruption) is the key here.  Database 
delete() is a low priority if we handle database corruption transparently.

Original comment by cpri...@google.com on 11 Feb 2008 at 11:22

• Added labels: Version-later
• Removed labels: Version-0.3

[GoogleCodeExporter]

If you can guarantee that there will be no more corruptions again.  Ever.  
...then this seems OK.

I really think this needs to be in 0.4 or a guarantee (which I know no one can 
make) that the database will 
never ever get corrupted.

Original comment by erik.arv...@gmail.com on 13 Feb 2008 at 10:07

[GoogleCodeExporter]

Erik, by "handle db corruption", Chris means "detect corruption and move the 
database file". Not "make it so 
that corruption cannot occur".

So basically we're just discussing deleting the database file for apps instead 
of having them have to do it 
themselves.

Original comment by gears.te...@gmail.com on 13 Feb 2008 at 10:15

[GoogleCodeExporter]

Erik, not sure exactly what your response is.  When Chris said "handle database
corruption transparently", he meant that when database corruption is detected, 
the
system swings into action and takes care of it.  The current thinking on that 
is not
that the system in any way *fixes* the corruption, the current thinking is that 
the
system deletes the database and you get to start over.

In that case, additionally allowing the developer to delete a database is 
redundant,
at least so far as corruption is concerned.

Original comment by Scott.Hess on 13 Feb 2008 at 10:18

[GoogleCodeExporter]

BTW, I think that if we can detect corruption and delete the database, then 
adding a
Database.delete() API would be trivial.

Original comment by Scott.Hess on 13 Feb 2008 at 10:19

[GoogleCodeExporter]

OK.  I did not get that.  Now that I understand what Chris meant the argument 
for having a delete is now 
back to only being able to clear the space used by the database but I assume 
that can be handled 
automatically by Gears/SQLite with auto vacuum?

Original comment by erik.arv...@gmail.com on 13 Feb 2008 at 10:50

[GoogleCodeExporter]

Talking more to Andy it seems that we can still read things out of the database 
when it is corrupted which 
allows us to finish any pending actions.  If the db is automatically removed 
when Gears detects that it is 
corrupted this will lead to dataloss that we can prevent.

Original comment by erik.arv...@gmail.com on 16 Feb 2008 at 12:07

[GoogleCodeExporter]

Do not delete the database automatically when there is corruption. In the cases 
we
have seen most of the database is accessible. When we are told that the 
database is
corrupted we try to extract the local changes, save them into a temporary 
database,
then delete the current database and resynchronize. During resynchronization we 
can
then upload the saved local changes.

Clearly this is not possible to do if you delete the database automatically.

Original comment by ajpa...@gmail.com on 16 Feb 2008 at 12:10

[GoogleCodeExporter]

It seems to me like an application developer should be able to provide "reset"
functionality to the user as a last resort solution for a myriad of problems,
including DB corruption or unreleased locks. Yes we risk losing user data, but 
better
that than be in a state where we can not recover without doing heavy 
diagnostics, or
telling the user to manually delete their Gears directory.

Original comment by ptuc...@gmail.com on 20 Feb 2008 at 10:27

[GoogleCodeExporter]

I like ptucker's suggestion -- I think it makes sense.

I'm updating the bug title to list that as an alternative.

Original comment by cpri...@google.com on 20 Feb 2008 at 10:57

• Changed title: Provide an API to delete a database (or origin)

[GoogleCodeExporter]

http://code.google.com/p/google-gears/issues/detail?id=365

Gist: removing origin permissions should remove origin databases as a catch-all 
way
to handle ambiguously broken things.  Sort of like clearing cookies.

Original comment by Scott.Hess on 20 Feb 2008 at 10:59

[GoogleCodeExporter]

Right; I agree with issue 365.

I see issue 76 as a tracking bug for whether origin deletion should *also* be 
exposed
via JavaScript.  API access lets apps control the entire experience, which is
important to some developers.

Original comment by cpri...@google.com on 20 Feb 2008 at 11:07

[GoogleCodeExporter]

Interesting thought just occurred to me: What if databases which had no tables
magically disappeared?  I'm not suggesting this as an alternative to exposing
Database.Delete(), which I think is a Good Idea.  I like explicit things.  The
no-tables thing just occurred to me and seemed interesting.

Original comment by Scott.Hess on 20 Feb 2008 at 11:12