Re: [sqlite] sqlite - Delete large table in sqlite

2016-09-09 Thread Keith Medcalf
Do you have secure_delete turned on? Do you have triggers on the table you are trying to delete? Is the table you are deleting from the parent table of any referential constraints? > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On

Re: [sqlite] sqlite - Delete large table in sqlite

2016-09-09 Thread Quan Yong Zhai
DELETE FROM tab WHERE rowid in (SELECT rowid from tab ORDER BY … LIMIT 1000); If SQLite compiled with SQLITE_ENABLE_UPDATE_DELETE_LIMIT http://www.sqlite.org/compile.html#enable_update_delete_limit DELETE FROM tab WHERE … ORDER BY … LIMIT 1000; From: Simon Slavin

Re: [sqlite] sqlite - Delete large table in sqlite

2016-09-09 Thread Scott Hess
Do you have auto_vacuum turned on? It may be that the cost isn't actually in deleting the table, it may be that the cost is rearranging the rest of the file to fill the gaps left by deleting the table. In that case you could turn off auto_vacuum, or you could use incremental vacuum to smooth out

Re: [sqlite] schema_version and Vacuum or Backup API

2016-09-09 Thread Stephen Chrzanowski
Not what I expected, but this can of work for my dual purpose. I'll have to tune it just a little bit, but this will work both as a check for differences between prior and current, and give me a "one-liner transactionable" SQL command to give a FILE>NEW command. Appreciated. On Fri, Sep 9, 2016

Re: [sqlite] sqlite - Delete large table in sqlite

2016-09-09 Thread Carlos
maybe rename and recreate the database with "insert from" the remaining tables would be faster, and give the benefit of a vacuum also? On 09/09/2016 13:14, Венцислав Русев wrote: On 09/09/2016 12:49 PM, Bhavesh Patel wrote: I have a 4GB SQLite database (currently using SQLCipher for

Re: [sqlite] schema_version and Vacuum or Backup API

2016-09-09 Thread R Smith
On 2016/09/08 4:59 PM, Stephen Chrzanowski wrote: Is there a way that I can get your sub-query (Reordered to have tables, then indexes, then views) to come out as one row? I can then have the application just do an easy string comparison, AND have a method to include the resource string to

Re: [sqlite] sqlite - Delete large table in sqlite

2016-09-09 Thread Simon Slavin
On 9 Sep 2016, at 6:14pm, Венцислав Русев wrote: > I don't know if this will work for you, but can't you delete 1000 rows then > another 1000 ... until the table is empty. If you do do that, each 1000 rows should be deleted inside a transaction. So BEGIN DELETE FROM

Re: [sqlite] sqlite - Delete large table in sqlite

2016-09-09 Thread Венцислав Русев
On 09/09/2016 12:49 PM, Bhavesh Patel wrote: I have a 4GB SQLite database (currently using SQLCipher for encryption). I have created a physical table in the SQLite DB which has approx 20 rows x 183 columns. When I am trying to delete the table. Sqlite is taking up a lot of time to delete

Re: [sqlite] sqlite - Delete large table in sqlite

2016-09-09 Thread Simon Slavin
On 9 Sep 2016, at 1:49pm, Bhavesh Patel wrote: > during the process of delete/drop is running, if I try to connect I randomly > get database lock error. Set a timeout of five minutes when you open your SQLite connections using one of the following:

[sqlite] sqlite - Delete large table in sqlite

2016-09-09 Thread Bhavesh Patel
I have a 4GB SQLite database (currently using SQLCipher for encryption). I have created a physical table in the SQLite DB which has approx 20 rows x 183 columns. When I am trying to delete the table. Sqlite is taking up a lot of time to delete the huge table. I tried drop table and also

[sqlite] SQLite - how to get number of active connection?

2016-09-09 Thread Bhavesh Patel
I want to run certain clean-up tasks on the SQLite database only and only when there is only 1 connection(self) to SQLite. I do not want to lock the database during the cleanup process. The main application(s) using the SQLite DB is given 1st preference. the cleanup application is executed at

Re: [sqlite] Problem with rename table

2016-09-09 Thread Radovan Antloga
Yes my first idea was to rename back to old name but you cant because every sql fails with error malformed database schema. Interest thing is that sql Richard suggested works and no error message returned. It looks like triggers are parsed when other sql executed and produce error. I found

Re: [sqlite] schema_version and Vacuum or Backup API

2016-09-09 Thread Dan Kennedy
On 09/09/2016 12:06 PM, Keith Medcalf wrote: Richard, Can a single sqlite3_create_function call define a function which is both a scalar function and an aggregate function, or are two calls to create_function required, one defining the scalar and the other the aggregate version? (using the