SQLite « GGshow reloaded

December 31, 2011

Issue:

Reason:

flash.events.SQLErrorEvent

SQLError
detailID : 2037
details : VACUUM is not allowed from SQL.

Solution:

use method compact() to reclaims all unused space in the database.
```
compact(responder:Responder = null):void
```

Issue:

Reason:

VACUUM command rebuilds the entire database, and recreates rowid for all tables.

Solution:

Although query by ROWID could be fast, but it should not be used as an external key.
Use an explicits integer primary key in the tables.
```
CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);
```

Filed under: Database,SQLite — Tags: rowid, sqlite, vacuum — GG @ 4:29 pm

Issue:

Reason:

In SQLite, when an object (table, index, or trigger) is dropped from the database, it leaves behind empty space. This makes the database file larger than it needs to be, but can speed up INSERT operations. Over time, INSERT and DELETE operations can leave the database file structure fragmented, which slows down disk access to the database contents.

Solution:

Filed under: Database,SQLite — Tags: sqlite, vacuum — GG @ 4:17 pm