GGshow reloaded GGshow reloaded

December 31, 2011

Compact, not vacuum…

Issue:

  • Unable to execute a SQLite VACUUM command from actionscript.

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
Filed under: ActionScript,Flash Platform,SQLite — Tags: , , , , — GG @ 4:36 pm

ROWID messed up by VACUUM

Issue:

  • ROWIDs in a SQLite table has been changed after executed a VACUUM command.

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: , , — GG @ 4:29 pm

VACUUMing SQLite

Issue:

  • SQLite database file size keep increasing.
  • SQLite database file size not reduced after deleted tables or rows.

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:

  • Use VACUUM command rebuilds the entire database; or
  • enable auto vacuum in the database, use command pragma auto_vacuum.
Filed under: Database,SQLite — Tags: , — GG @ 4:17 pm

© 2024 GGSHOW | Powered by WordPress