Compact a SQLITE database

[Santosh Tiwari]

Hello,

I am using sqlite with the xerial jdbc driver to store/retrieve model and simulation data in my application.

I want to reduce the size of the database file (after most of the simulation data has been deleted by the user).

I found that sqlite supports "VACUUM" command to compact the database.

Does sqlite jdbc driver support this command?

If yes, how do I use it?

If no, can you suggest any workaround?

Thanks,

--
Santosh Tiwari
tiw...@gmail.com

[Peter Borissow]

I just tried the following and it seemed to work:

        PreparedStatement preparedStmt = conn.prepareStatement("VACUUM CONTACTS");
        preparedStmt.execute();
        preparedStmt.close();

---

From: Santosh Tiwari <tiw...@gmail.com>
To: xer...@googlegroups.com
Sent: Wednesday, October 12, 2011 11:03 AM
Subject: [xerial 455] Compact a SQLITE database

--
You received this message because you are subscribed to the Google Groups "Xerial" group.
To post to this group, send email to xer...@googlegroups.com.
To unsubscribe from this group, send email to xerial+un...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/xerial?hl=en.

[Santosh Tiwari]

Thanks for the quick and correct response.

what is "CONTACTS" ?

conn is already a connection to the database file. I want to compact this database file.



I assume that "CONTACTS" is the name of the database file. In that case

--
Santosh Tiwari
tiw...@gmail.com

[Peter Borissow]

Hello Santosh-

    "CONTACTS" is the name of a table in my database. After running the vacuum command, I can see the size of the database decrease. I don't know if there's a way to run vacuum on the entire database at once. I suppose you can loop through all the tables in the database using ResultSetMetaData and run the vacuum command on individual tables.

Hope that helps!

Peter

---

From: Santosh Tiwari <tiw...@gmail.com>
To: xer...@googlegroups.com

Sent: Wednesday, October 12, 2011 12:27 PM
Subject: Re: [xerial 457] Compact a SQLITE database

[Santosh Tiwari]

Hello,

The following commands seems to compact the entire database.

//conn is the connection to the database

java.sql.Statement stat = = conn.createStatement();
stat.execute("PRAGMA auto_vacuum = 1");
stat.execute("VACUUM");

Thanks for the help.