Re: [sqlite] sqlite3 delete does not delete everything?

2010-03-11 Thread D. Richard Hipp
On Mar 11, 2010, at 12:18 PM, Skand wrote: >> >> Run REINDEX on your database. >> >> Get SQLite 3.6.23 and use that in place of 3.3.6 moving forward. >> > > REINDEX throws following error: > > SQL error: indexed columns are not unique > > Is there a graceful way to fix this? Run sqlite3

Re: [sqlite] sqlite3 delete does not delete everything?

2010-03-11 Thread Skand
D. Richard Hipp wrote: > > > On Mar 11, 2010, at 11:42 AM, Skand wrote: > >> >> >> D. Richard Hipp wrote: >>> >>> >>> On Mar 11, 2010, at 11:13 AM, Skand wrote: >>> >>> sqlite> select count(*) from ip_domain_table where ttl < 99 ; 1605343 >>> >>> What does

Re: [sqlite] sqlite3 delete does not delete everything?

2010-03-11 Thread D. Richard Hipp
On Mar 11, 2010, at 11:42 AM, Skand wrote: > > > D. Richard Hipp wrote: >> >> >> On Mar 11, 2010, at 11:13 AM, Skand wrote: >> >> >>> >>> sqlite> select count(*) from ip_domain_table where ttl < >>> 99 ; >>> >>> 1605343 >> >> What does "PRAGMA integrity_check" show you at this point?

Re: [sqlite] sqlite3 delete does not delete everything?

2010-03-11 Thread Simon Slavin
On 11 Mar 2010, at 4:44pm, Skand wrote: > sqlite> SELECT ttl,typeof(ttl) FROM ip_domain_table WHERE ttl < 99 > LIMIT 10 > ...> ; > 1266895620|integer Okay, that's not the problem, but your response to Richard's post does betray the problem and should attract a useful response.

Re: [sqlite] sqlite3 delete does not delete everything?

2010-03-11 Thread Skand
Simon Slavin-3 wrote: > > > >> sqlite> select * from ip_domain_table where ttl < 99 limit 1; >> 107.35.138.41|127.2.0.2|1266895619 > > Although you have defined your column type as INTEGER, it's possible that > you have some values in that column which are of other types. Even the >

Re: [sqlite] sqlite3 delete does not delete everything?

2010-03-11 Thread Skand
D. Richard Hipp wrote: > > > On Mar 11, 2010, at 11:13 AM, Skand wrote: > > >> >> sqlite> select count(*) from ip_domain_table where ttl < 99 ; >> >> 1605343 > > What does "PRAGMA integrity_check" show you at this point? > > The integrity check shows 395 lines similar to: "rowid

Re: [sqlite] sqlite3 delete does not delete everything?

2010-03-11 Thread Simon Slavin
On 11 Mar 2010, at 4:13pm, Skand wrote: > sqlite> select count(*) from ip_domain_table where ttl < 99 ; > > 258 > > sqlite> select * from ip_domain_table where ttl < 99 limit 1; > 107.35.138.41|127.2.0.2|1266895619 Although you have defined your column type as INTEGER, it's

Re: [sqlite] sqlite3 delete does not delete everything?

2010-03-11 Thread D. Richard Hipp
On Mar 11, 2010, at 11:13 AM, Skand wrote: > > Hi Folks, > > Whats going on here? I would expect the following delete to delete > everything under 99. > > sqlite> .schema > > CREATE TABLE ip_domain_table (ip_domain TEXT, answer TEXT, ttl > INTEGER, > PRIMARY KEY(ip_domain, answer,

Re: [sqlite] sqlite3 delete does not delete everything?

2010-03-11 Thread Dan Kennedy
On Mar 11, 2010, at 11:13 PM, Skand wrote: > > Hi Folks, > > Whats going on here? I would expect the following delete to delete > everything under 99. > > sqlite> .schema > > CREATE TABLE ip_domain_table (ip_domain TEXT, answer TEXT, ttl > INTEGER, > PRIMARY KEY(ip_domain, answer,

[sqlite] sqlite3 delete does not delete everything?

2010-03-11 Thread Skand
Hi Folks, Whats going on here? I would expect the following delete to delete everything under 99. sqlite> .schema CREATE TABLE ip_domain_table (ip_domain TEXT, answer TEXT, ttl INTEGER, PRIMARY KEY(ip_domain, answer, ttl)); sqlite> select count(*) from ip_domain_table where ttl <