Re: [sqlite] FTS3 Question

Thanks for the help Scott, you've confirmed what I had concluded. Thanks again M -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Hess Sent: 20 May 2008 17:00 To: General Discussion of SQLite Database Subject: Re: [sqlite] FTS3 Question I think

Re: [sqlite] deleting 100,000 entries

Thanks Keith. Carlo - Original Message From: Keith Goodman <[EMAIL PROTECTED]> To: General Discussion of SQLite Database Sent: Wednesday, May 21, 2008 12:45:42 PM Subject: Re: [sqlite] deleting 100,000 entries On Tue, May 20, 2008 at 8:59 PM, Carlo S. Marcelo

Re: [sqlite] deleting 100,000 entries

On Tue, May 20, 2008 at 8:59 PM, Carlo S. Marcelo <[EMAIL PROTECTED]> wrote: > That means I save space right? Because the space all the original entries are > still intact when I run the delete command? Whereas dropping table clears > everything (since we are re-creating it)? > > I'm only

Re: [sqlite] deleting 100,000 entries

That means I save space right? Because the space all the original entries are still intact when I run the delete command? Whereas dropping table clears everything (since we are re-creating it)? I'm only assuming this is what dropping table does. It would be cool to know how to drop tables

Re: [sqlite] deleting 100,000 entries

BareFeet wrote: > Hi Carlo, > > >> I want to clear the whole thing out(list)! >> > > Do you mean that you want to delete all rows from the "list" table? If > so, do this: > > delete from list; Or you could drop the table and then create it again. Gerry

Re: [sqlite] deleting 100,000 entries

@Barefoot and Keith, Awesome! It took forever for the web interface to delete, and I had to restart httpd for the database to unlock.. the command you provided took less than a second to clear everything out, thanks! Carlo - Original Message From: BareFeet <[EMAIL PROTECTED]> To:

Re: [sqlite] deleting 100,000 entries

Hi Carlo, > I want to clear the whole thing out(list)! Do you mean that you want to delete all rows from the "list" table? If so, do this: delete from list; See the syntax pages at: http://www.sqlite.org/lang.html http://www.sqlite.org/lang_delete.html Tom BareFeet

Re: [sqlite] deleting 100,000 entries

On Tue, May 20, 2008 at 8:40 PM, Carlo S. Marcelo <[EMAIL PROTECTED]> wrote: > I run select * from list; and get loads and loads of entries: > > > 81203|0|1|www.newwhitelistentry44994.com|0|0|0 > 81204|0|1|www.newwhitelistentry44995.com|0|0|0 > 81205|0|1|www.newwhitelistentry44996.com|0|0|0 >

[sqlite] deleting 100,000 entries

I run select * from list; and get loads and loads of entries: 81203|0|1|www.newwhitelistentry44994.com|0|0|0 81204|0|1|www.newwhitelistentry44995.com|0|0|0 81205|0|1|www.newwhitelistentry44996.com|0|0|0 81206|0|1|www.newwhitelistentry44997.com|0|0|0 81207|0|1|www.newwhitelistentry44998.com|0|0|0

[sqlite] PRAGMA journal_mode = OFF slower?

Done some testing with 3.5.9 with PRAGMA journal_mode = OFF and it seems that strangely it makes DB writing queries slower. I use SQLite from VB/VBA with the wrapper from Olaf Schmidt. Should it not be that PRAGMA journal_mode = OFF should make inserts, create index etc. faster rather than slower?

Re: [sqlite] baffling performance decrease across network (specific case)

Thanks for the response - no it hadn't occurred to me to try a different network filesystem, as I don't really have access to non windows machines. I'm just using windows xp machines set up on the same domain, where the databases reside on shared folders. I actually thought there might be extra

Re: [sqlite] last_insert_rowid reproducible bug with triggers and FTS2 and 3

Bram, I don't think there is a solid workaround of the form you suggest. The last_insert_rowid() you're getting is for the segment in the internal segdir table, and is unrelated to the rowid of the overall virtual table. What you should be able to do is to call last_insert_rowid() IMMEDIATELY

Re: [sqlite] last_insert_rowid reproducible bug with triggers and FTS2 and 3

On Tue, May 20, 2008 at 1:26 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > In Bram's case, he was surprised that the last-insert-rowid changed > because he is not thinking about how FTS works behind the scenes. And > this is reasonable. There is a lot of magic in FTS that programmers > are

Re: [sqlite] last_insert_rowid reproducible bug with triggers and FTS2 and 3

Hello, On Tue, May 20, 2008 at 10:26 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > So it is not at all clear to me whether this behavior is a bug or a > feature. SQLite is doing what the documentation says it ought to do. > The question is, should the specification of what SQLite ought to do

Re: [sqlite] baffling performance decrease across network (specific case)

On May 20, 2008, at 4:15 PM, Serena Lien wrote: > Thanks for your response. I do realize the performance of sqlite > over the > network will be slower than accessing local databases - I will > restate if I > wasn't clear - the question I asked is why sqlite accessing a > networked >

Re: [sqlite] last_insert_rowid reproducible bug with triggers and FTS2 and 3

On May 20, 2008, at 1:21 PM, Bram de Jong wrote: > Hello all, > > > I have found a bug which happens in both FTS2 and FTS3. > > The bug happens when a trigger updates an FTS table: the insert ID > gets trashed: > > <<< > create table one > ( >

Re: [sqlite] baffling performance decrease across network (specific case)

Thanks for your response. I do realize the performance of sqlite over the network will be slower than accessing local databases - I will restate if I wasn't clear - the question I asked is why sqlite accessing a networked database is slower WHEN that particular database is attached in a different

Re: [sqlite] Bind arguments for insert and not null columns with default values

On May 20, 2008, at 3:40 PM, Jeff Hamilton wrote: > This works in the simple case that I described, but in practice I'd > like to use the pattern on more complex tables that have existing > uniqueness constraints. Using REPLACE INTO will get in the way of > those constraints. > OK. How about:

Re: [sqlite] Bind arguments for insert and not null columns with default values

This works in the simple case that I described, but in practice I'd like to use the pattern on more complex tables that have existing uniqueness constraints. Using REPLACE INTO will get in the way of those constraints. Regarding multiple statements, the tables I'd like to use this pattern on have

Re: [sqlite] Bind arguments for insert and not null columns with default values

> I have a table like this > > CREATE TABLE foo (bar TEXT NOT NULL DEFAULT 'default_value'); > > and I'd like to create a reusable statement to do inserts into foo, like this: > > INSERT INTO foo (bar) VALUES (?); > > Sometimes I have values for bar and sometimes I don't and want the >

Re: [sqlite] Bind arguments for insert and not null columns with default values

On May 20, 2008, at 2:33 PM, Jeff Hamilton wrote: > Hi all, > > I have a table like this > > CREATE TABLE foo (bar TEXT NOT NULL DEFAULT 'default_value'); > > and I'd like to create a reusable statement to do inserts into foo, > like this: > > INSERT INTO foo (bar) VALUES (?); > > Sometimes I

[sqlite] Bind arguments for insert and not null columns with default values

Hi all, I have a table like this CREATE TABLE foo (bar TEXT NOT NULL DEFAULT 'default_value'); and I'd like to create a reusable statement to do inserts into foo, like this: INSERT INTO foo (bar) VALUES (?); Sometimes I have values for bar and sometimes I don't and want the default. Is there

Re: [sqlite] ticket 3127 & MJ file creation

Dan, Yes thats what I mean. No point in even creating an MJ file at all. Thanks, Ken Dan <[EMAIL PROTECTED]> wrote: > It Looks like a Master Journal is still being created even when > journal_mode=off. > > Is the master journal file supposed to be created? Well, there's no point in it

[sqlite] SQLite allows "RowID" to be the name of a column

Ralf Junker wrote: > My oversight, sorry for that. I never questioned that SQLite would reject > reserved word column names, but I now see that this is not so. > > This alerts me to a potential danger for SQLite managers which must rely on > some means to retrieve THE RowID which uniquely

[sqlite] last_insert_rowid reproducible bug with triggers and FTS2 and 3

Hello all, I have found a bug which happens in both FTS2 and FTS3. The bug happens when a trigger updates an FTS table: the insert ID gets trashed: <<< create table one ( id integer not null primary key autoincrement, value text not null

Re: [sqlite] ticket 3127 & MJ file creation

> It Looks like a Master Journal is still being created even when > journal_mode=off. > > Is the master journal file supposed to be created? Well, there's no point in it being created if that's what you mean :) It's a bit more complex than it seems though, as journal_mode=off is set on a

Re: [sqlite] design question / discussion

Actually my reason for writing into a seperate database is more... well crude. I tar several databases together then encrypt using openSSL. Then an FTP like program transmits the data a central server. I must suspend writing into the database for the duration of the tar operation since tar does

Re: [sqlite] baffling performance decrease across network (specific case)

On Tue, May 20, 2008 at 9:43 AM, Serena Lien <[EMAIL PROTECTED]> wrote: > Hello, > > I wonder if anyone could shed some light on this. I am using sqlite in my > client programs to access databases over the network. I know sqlite isn't > recommended for this usage model, but I am happy that I have

Re: [sqlite] FTS3 Question

I think you're going to have to run some code to generate the string to match against. The problem is that you need to take all of the 'query' fields from 'category' and combine them into a string like 'query1 OR query2 OR query3 OR ...'. I'm not aware of a way to do this with straight SQL. You

[sqlite] do someone know? DotGnu

do some one know how i can conect from dotGnu to sqlite? i have mandriva i hope some one can helpme from David Alejandro Garcia Garcia thanks _ Juega y gana, tenemos 3 Xbox a la semana. http://club.prodigymsn.com/

[sqlite] baffling performance decrease across network (specific case)

Hello, I wonder if anyone could shed some light on this. I am using sqlite in my client programs to access databases over the network. I know sqlite isn't recommended for this usage model, but I am happy that I have implemented it such that only 1 client ever has write access, and am also happy

[sqlite] ticket 3127 & MJ file creation

Dan, Thanks for the fixes on ticket 3127. It Looks like a Master Journal is still being created even when journal_mode=off. Is the master journal file supposed to be created? Thanks, Ken Ken <[EMAIL PROTECTED]> wrote: Ticket 3127 created. ___

Re: [sqlite] Ticket 3128: DISTINCT -> GROUP BY can lead to performance degradation

>> This should NOT happen as SQLite usually rejects duplicate rowids with a >constraint error. > >For this table, the badly-named column "RowID" is actually a text field >that's allowed to be null, not a primary key. > >-- Describe NDXPARAMVALUES_LOCALIZEDSTRING >CREATE TABLE

Re: [sqlite] Ticket 3128: DISTINCT -> GROUP BY can lead to performance degradation

On Tue, May 20, 2008 at 6:57 AM, Ralf Junker <[EMAIL PROTECTED]> wrote: > This should NOT happen as SQLite usually rejects duplicate rowids with a constraint error. For this table, the badly-named column "RowID" is actually a text field that's allowed to be null, not a primary key. -- Describe

Re: [sqlite] blob error

On May 20, 2008, at 8:16 AM, [EMAIL PROTECTED] wrote: > Hello i'm using sqlite with turbogears, and any time i try to store > a file in a blobcol of more that 1mb i get this error: > DataError: String or BLOB exceeded size limit > > reading the paper the default value of sqlite for blobclo is

[sqlite] blob error

Hello i'm using sqlite with turbogears, and any time i try to store a file in a blobcol of more that 1mb i get this error: DataError: String or BLOB exceeded size limit reading the paper the default value of sqlite for blobclo is 10 but why i get this error Thanks and Regards Luca

Re: [sqlite] Ticket 3128: DISTINCT -> GROUP BY can lead to performance degradation

Correcting myself: This should NOT happen as SQLite usually rejects duplicate rowids with a constraint error. >This should happen as SQLite usually rejects duplicate rowids with a >constraint error. ___ sqlite-users mailing list

Re: [sqlite] Ticket 3128: DISTINCT -> GROUP BY can lead to performance degradation

I looked at the database attached to the ticked and noticed that the table contains NULL rowids as well duplicate rowids. This should happen as SQLite usually rejects duplicate rowids with a constraint error. When I run this query: select rowid, count() from ndxparamvalues_localizedstring

Re: [sqlite] text datatype and referential integrity

- Original Message - From: "palmer ristevski" <[EMAIL PROTECTED]> To: "General Discussion of SQLite Database" > I always saw SQLite as trying to be the database to beat out MS Access > for the desktop user. > And due to this belief I thought that referential

[sqlite] Ticket 3128: DISTINCT -> GROUP BY can lead to performance degradation

I've only found one reference to slower queries with the DISTINCT/GROUP BY optimization that went in back in November for 3.5.3 and later. I would have expected more given the number of our queries causing problems. The problem as I wrote in ticket 3128

Re: [sqlite] FTS3 Question

What I'm trying to do is get the query strings that are stored in category executed against the text stored in data. Category is essentially a fixed set of content, whilst data changes. I could just step through category and execute each query individually, but I was looking for a way to do it