Re: [sqlite] PRAGMA temp_store confusion

Roger Binns wrote: Where is the appropriate place to discuss 3.6 issues? For example in my case I have very strong feelings about error codes vs return values in the VFS api. The sqlite-dev mailing list would be a good place. ___ sqlite-users

Re: [sqlite] Index and ORDER BY

Really, there is problem with multi-column indexes. You must use only primary key index for ">=" where clause and "ASC" sorting and "<=" where clause and DESC sorting. 1. I try with primary key: #!/usr/bin/tclsh package require sqlite3 sqlite3 db index_order.db db eval {DROP TABLE IF EXISTS

Re: [sqlite] select with union fails on an attached database for 500 records

Anyone has any clues on this? Best Regards, N.Rajesh Courage is the knowledge of how to fear what ought to be feared and how not to fear what ought not to be feared.> From: [EMAIL PROTECTED]> To: sqlite-users@sqlite.org> Date: Fri, 27 Jun 2008 11:33:53 +> Subject: [sqlite] select with

Re: [sqlite] Multiple constraints per table?

"flakpit" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > is it legal sql syntax to allow more than one constraint field in > table creation? I need at least these four below to guarantee that > duplicate items do make it into the database but not on the same day. > > item TEXT

Re: [sqlite] SQLite assert failure in sqlite3FaultEndBenign()

thanks very much! On Mon, Jun 30, 2008 at 4:52 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > > > > > > It is not a bug because sqlite3FaultEndBenign() is used during testing > > only. And the tests in which this function are used only run a single > > thread. > > > > > Recompile with

Re: [sqlite] Newbie questions regarding in-memory database.

"Karthik" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Newbie question regarding in-memory databases. I read in the wiki > pages that it is not safe to use same db connection across multiple > threads. It's actually safe with recent SQLite versions. A lot of issues have been

[sqlite] Three LEFT JOIN issues

1) In the following LEFT JOIN, is it possible to alter the query so that there is no w. prefix at the beginning of each returned column name: SELECT w.* FROM Words AS w LEFT JOIN Words as w2 ON w.Id=w2.Id AND w.Lang=w2.Lang AND

Re: [sqlite] Three LEFT JOIN issues

"Csaba" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 1) In the following LEFT JOIN, is it possible to alter > the query so that there is no w. prefix at the > beginning of each returned column name: > > SELECT w.* FROM Words AS w LEFT JOIN Words as w2 >ON w.Id=w2.Id AND

Re: [sqlite] [newbie] linking two tables with index...

Igor Tandetnik wrote: You can't make this happen automatically with SQLite. Hmm? Of course you can. Just use a trigger: http://sqlite.org/lang_createtrigger.html ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Newbie questions regarding in-memory database.

Igor Tandetnik wrote: > "Karthik" <[EMAIL PROTECTED]> wrote in > message news:[EMAIL PROTECTED] > >> Newbie question regarding in-memory databases. I read in the wiki >> pages that it is not safe to use same db connection across multiple >> threads. >> > > It's actually safe with recent

Re: [sqlite] Multiple constraints per table?

your main issue seems to be that you really need to normalize your db.  using your example of pepper 220 gr, when you examine that string it consists of 3 parts, pepper = product description or product name, 220 = weight or volume or measurement of product, followed by the measurement type. All

Re: [sqlite] [newbie] linking two tables with index...

Mihai Limbasan <[EMAIL PROTECTED]> wrote: >> Igor Tandetnik wrote: >>> You can't make this happen automatically with SQLite. > >> Hmm? Of course you can. Just use a trigger: >> http://sqlite.org/lang_createtrigger.html Can you show an example of a trigger that would make these statements work:

Re: [sqlite] Newbie questions regarding in-memory database.

Karthik <[EMAIL PROTECTED]> wrote: > is it possible to set a limit on the database size? PRAGMA max_page_count http://sqlite.org/pragma.html > how does sqlite > behave when database size reaches the threshold(the > default/configured one)? You get an error executing the statement that takes it

[sqlite] How does the database file grow?

Hi all, i create and fill database files which reach quite a large size after a while, because i only add data and never remove it. The database files themselves become quite fragmented on the disk. Because I can in many cases calculate in advance the size to which the file will grow, i would

Re: [sqlite] How does the database file grow?

On Jul 1, 2008, at 11:03 AM, Martin.Engelschalk wrote: > Hi all, > > i create and fill database files which reach quite a large size > after a > while, because i only add data and never remove it. > The database files themselves become quite fragmented on the disk. > Because I can in many

Re: [sqlite] How does the database file grow?

D. Richard Hipp wrote: > On Jul 1, 2008, at 11:03 AM, Martin.Engelschalk wrote: > >> Hi all, >> >> i create and fill database files which reach quite a large size >> after a >> while, because i only add data and never remove it. >> The database files themselves become quite fragmented on the

Re: [sqlite] prepackaged sql statement

The problem with the view is that you can not pass a parameter or variable from the outer select to the views select. On Mon, Jun 30, 2008 at 12:49 AM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > "Henrik Bechmann" <[EMAIL PROTECTED]> > wrote in message news:[EMAIL PROTECTED] > > Thanks Igor! And

Re: [sqlite] How does the database file grow?

On Jul 1, 2008, at 12:16 PM, Stephen Woodbridge wrote: > >> On Jul 1, 2008, at 11:03 AM, Martin.Engelschalk wrote: >>> Because I can in many cases calculate in advance the size to which >>> the >>> file will grow, i would like to reserve the disk space before >>> inserting >>> all the data.

Re: [sqlite] Performance on HP

>On Jun 30, 2008, at 2:37 PM, Andrea Connell wrote: > >> Any ideas? It's driving me crazy why SQLite is this much slower on >> UNIX >> boxes, while other applications maintain their speed. > >What filesystem are you using on the unix boxes? Are you *sure* you >are not using NFS? > >D. Richard

Re: [sqlite] Index and ORDER BY

I haven't looked closely at this problem but a cursory glance suggests that Sqlite is not using an ASC indesx if there is a DESC ORDER By clause. Try doing the selection ASC and then sorting the output DESC as a seperate action. Alexey Pechnikov wrote: > Really, there is problem with

Re: [sqlite] Index and ORDER BY

В сообщении от Tuesday 01 July 2008 19:26:47 John Stanton написал(а): > I haven't looked closely at this problem but a cursory glance suggests > that Sqlite is not using an ASC indesx if there is a DESC ORDER By clause. But primary key index work fine. Why? > Try doing the selection ASC and then

Re: [sqlite] Index and ORDER BY

Jeff, try this select instead sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 and +type=22 ORDER BY eid DESC LIMIT 1; 0|0|TABLE events USING PRIMARY KEY ORDER BY Regards-- Noah -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf

Re: [sqlite] [newbie] linking two tables with index...

Igor Tandetnik wrote: > Can you show an example of a trigger that would make these statements > work: > > insert into snapshot (bumf) values ('one'); > insert into sample(type, used) values ('a', 1); > insert into sample(type, used) values ('b', 1); > > so that, at the end, the two new records in

Re: [sqlite] Index and ORDER BY

On Jul 1, 2008, at 1:24 PM, [EMAIL PROTECTED] wrote: > > Is it a problem in sqlite that it will only optimize: "WHERE > primary_key<=X ORDER BY primary_key DESC" if it's not using an index? > Is it supposed to? It would be a problem if it where the case. But in every test I have tried,

Re: [sqlite] prepackaged sql statement

Alex Katebi wrote: > The problem with the view is that you can not pass a parameter or variable > from the outer select to the views select. Right that is not the purpose of a view. Think of a view as a virtual TABLE. You can not pass a parameter to a table either. You can just query the table

Re: [sqlite] [newbie] linking two tables with index...

Igor Tandetnik wrote: >> Sure. I'll use your examples, just making sure to add an id insertion >> in the first statement since the id column isn't specified as >> autoincrement. >> > > But it _is_ specified as INTEGER PRIMARY KEY. In your example it wasn't. It also wasn't in Martin's original

Re: [sqlite] [newbie] linking two tables with index...

Mihai Limbasan wrote: > In your example it wasn't. Oops, misread what you wrote. What I mean is that it doesn't matter whether the ID is autogenerated or not (of course, if it isn't then the whole issue becomes moot - it in fact *should* be PRIMARY KEY AUTOINCREMENT.)

[sqlite] Insert mil.rows to table can take up to a day

Hi All, I have the file which has million rows of insert statement to insert the data to the database like below:  insert into tables values(...) (there are million insert statement to insert to the table) (file name is insertTables) Then I ran the following

Re: [sqlite] Insert mil.rows to table can take up to a day

Joanne Pham wrote: > Hi All, > I have the file which has million rows of insert statement to insert the data > to the database like below: > insert into tables values(...) > > (there are million insert statement to insert to the table) > (file name is

Re: [sqlite] Insert mil.rows to table can take up to a day

Thanks a lot Mihai, I will try it. Again thank for quick response. JL - Original Message From: Mihai Limbasan <[EMAIL PROTECTED]> To: General Discussion of SQLite Database Sent: Tuesday, July 1, 2008 10:57:31 AM Subject: Re: [sqlite] Insert mil.rows to table

Re: [sqlite] Insert mil.rows to table can take up to a day

Thank you so much Mihai. It worked!!! Once again thanks a ton. JL - Original Message From: Mihai Limbasan <[EMAIL PROTECTED]> To: General Discussion of SQLite Database Sent: Tuesday, July 1, 2008 10:57:31 AM Subject: Re: [sqlite] Insert mil.rows to table can

Re: [sqlite] Index and ORDER BY

I'm including a copy of Alexey's relevant message below. Unless I misunderstand, he has a test case that demonstrates that for the table: CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER) the query: SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY eid DESC LIMIT

Re: [sqlite] Insert mil.rows to table can take up to a day

Joanne Pham wrote: > Thank you so much Mihai. > It worked!!! Once again thanks a ton. > JL > Glad to hear that - have fun! Mihai ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Rigging up SQLite over LINQ (Entity Framework)

In case anyone stopped reading after the convoluted LINQ query, here's a simplified version of what it's trying to do. It's kindof confusing, but that's what you get when a computer constructs a query I guess: create table a (id integer primary key, bid integer); create table b (id integer

Re: [sqlite] Performance on HP

Are there any other processes or threads trying to open your db file while you run your tests? On Mon, Jun 23, 2008 at 9:48 AM, Andrea Connell <[EMAIL PROTECTED]> wrote: > > > >> The program took 47 seconds to run, but the results only account for > >> .39 seconds > > > > Most likely all the

Re: [sqlite] Index and ORDER BY

On Jul 1, 2008, at 2:17 PM, Jeff Gibson wrote: > I'm including a copy of Alexey's relevant message below. Unless I > misunderstand, he has a test case that demonstrates that for the > table: > > CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER) > > the query: > > SELECT events.* FROM

[sqlite] SQLite and updating VIEWs

Hey all, I'm working with a partitioned table setup with a permanent table and a temp table with the same columns and indexes. Every X time we dump all the records in the temp table over to the permanent one. In order to make selection queries easier to manage, I've gone and created a view like

Re: [sqlite] Index and ORDER BY

I see. It turns out that the selectivity of "type" is highly variable - some types are very common and some are quite rare. What made me curious is that when I have an index on type and I look for the first few entries in ascending order, the query is very fast - it seems that it does

Re: [sqlite] SQLite and updating VIEWs

Shawn Wilsher <[EMAIL PROTECTED]> wrote: > I'm working with a partitioned table setup with a permanent table and > a temp table with the same columns and indexes. Every X time we dump > all the records in the temp table over to the permanent one. In order > to make selection queries easier to

Re: [sqlite] Index and ORDER BY

Is any difference between "CREATE INDEX ev_idx ON events(type,eid)" and "CREATE INDEX ev_idx ON events(type,eid desc)"? What is "desc" keyword for index? ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] SQLite and updating VIEWs

On Tue, Jul 1, 2008 at 3:24 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > CREATE TRIGGER > BEGIN >insert into temp_table >select * from perm_table >where and > ; > >update temp_table set ...; > END; I had thought of this, but I'm pretty sure this will only

Re: [sqlite] SQLite and updating VIEWs

Shawn Wilsher <[EMAIL PROTECTED]> wrote: > On Tue, Jul 1, 2008 at 3:24 PM, Igor Tandetnik > <[EMAIL PROTECTED]> wrote: >> CREATE TRIGGER >> BEGIN >>insert into temp_table >>select * from perm_table >>where and >> ; >> >>update temp_table set ...; >> END; > I had

[sqlite] blob /transaction wierdness

Hi, I'm having some trouble with blobs. I have 4 blobs tht I want to insert into a db, and it works fine when I execute each insert as a single commit. However, when i try to use transactions to input blocks of 255 inserts, blob 4 gets inputted in the position of blob3, blob 3 in position of

[sqlite] unresolved external symbol

Hi list. I'm currently making modifications to a project, and want to add SQLite3 to it. I'm using VS6, have the sqlite3.h and sqlite3.c in the same directory, and this is the code: #include "sqlite3.h" bool TestSQLite() { sqlite3 *db; int rc; rc =

Re: [sqlite] SQLite and updating VIEWs

Shawn Wilsher wrote: > Hey all, > > I'm working with a partitioned table setup with a permanent table and > a temp table with the same columns and indexes. Every X time we dump > all the records in the temp table over to the permanent one. In order > to make selection queries easier to manage,

Re: [sqlite] Index and ORDER BY

On Tue, 1 Jul 2008, Alexey Pechnikov wrote: > Is any difference between "CREATE INDEX ev_idx ON events(type,eid)" > and "CREATE INDEX ev_idx ON events(type,eid desc)"? What is "desc" keyword > for index? The DESC keyword creates the index in descending collation order, rather than ascending

Re: [sqlite] Index and ORDER BY

В сообщении от Tuesday 01 July 2008 23:47:50 [EMAIL PROTECTED] написал(а): > On Tue, 1 Jul 2008, Alexey Pechnikov wrote: > > Is any difference between "CREATE INDEX ev_idx ON events(type,eid)" > > and "CREATE INDEX ev_idx ON events(type,eid desc)"? What is "desc" > > keyword for index? > > The

Re: [sqlite] SQLite and updating VIEWs

On Tue, Jul 1, 2008 at 3:37 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > They won't fail - they will successfully insert zero records. It's > perfectly valid to run INSERT ... SELECT and have the SELECT part > produce an empty resultset. It simply does nothing. Sorry, I should have been more

Re: [sqlite] SQLite and updating VIEWs

Shawn Wilsher <[EMAIL PROTECTED]> wrote: > On Tue, Jul 1, 2008 at 3:37 PM, Igor Tandetnik > <[EMAIL PROTECTED]> wrote: >> They won't fail - they will successfully insert zero records. It's >> perfectly valid to run INSERT ... SELECT and have the SELECT part >> produce an empty resultset. It simply

Re: [sqlite] SQLite and updating VIEWs

On Tue, Jul 1, 2008 at 4:05 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > Will it? part is supposed to > be false when the data is in fact currently in temp table. A WHERE > clause that is always false will, naturally, produce no records. Fair. I hadn't thought of doing something like that.

Re: [sqlite] unresolved external symbol

Hello Mauricio, Tuesday, July 1, 2008, 3:43:16 PM, you wrote: MC> Hi list. MC> I'm currently making modifications to a project, and want to add SQLite3 to MC> it. MC> I'm using VS6, have the sqlite3.h and sqlite3.c in the same directory, and MC> this is the code: MC> #include "sqlite3.h" MC>

Re: [sqlite] Index and ORDER BY

Yes, this has been my experience as well. I've tried 3.5.6 and 3.5.9. Jeff Alexey Pechnikov wrote: > В сообщении от Tuesday 01 July 2008 23:47:50 [EMAIL PROTECTED] написал(а): > >> On Tue, 1 Jul 2008, Alexey Pechnikov wrote: >> >>> Is any difference between "CREATE INDEX ev_idx ON

Re: [sqlite] blob /transaction wierdness

Column numbering for binding starts at 0 Not 1. sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT); smlacc1 leador <[EMAIL PROTECTED]> wrote: Hi, I'm having some trouble with blobs. I have 4 blobs tht I want to insert into a db, and it works fine when I execute each insert as a single

Re: [sqlite] blob /transaction wierdness

Column numbering for binding starts at 0 Not 1. sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT); smlacc1 leador <[EMAIL PROTECTED]> wrote: Hi, I'm having some trouble with blobs. I have 4 blobs tht I want to insert into a db, and it works fine when I execute each insert as a single

Re: [sqlite] blob /transaction wierdness

Ken <[EMAIL PROTECTED]> wrote: > Column numbering for binding starts at 0 Not 1. > sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT); Not true. Parameters in sqlite3_bind_* are numbered from 1. Columns in sqlite3_column_* are numbered from 0. Igor Tandetnik

Re: [sqlite] blob /transaction wierdness

smlacc1 leador <[EMAIL PROTECTED]> wrote: > I'm having some trouble with blobs. I have 4 blobs tht I want to > insert into a db, and it works fine when I execute each insert as a > single commit. However, when i try to use transactions to input > blocks of 255 inserts, blob 4 gets inputted in the

Re: [sqlite] blob /transaction wierdness

Yup thats what i was thinking, sqlite3_column. My mistake and thanks for catching that! Igor Tandetnik <[EMAIL PROTECTED]> wrote: Ken wrote: > Column numbering for binding starts at 0 Not 1. > sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT); Not true. Parameters in sqlite3_bind_* are

[sqlite] Table Level Locking

Hi All, I read the online document regarding "Table Level Locking" as below:         At any one time, a single table may have any number of active read-locks or a single active write lock. To read data a table, a connection                 must first obtain a read-lock. To write to a table,

Re: [sqlite] blob /transaction wierdness

ok, I'll try that. Thanks. On Tue, Jul 1, 2008 at 6:10 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > smlacc1 leador <[EMAIL PROTECTED]> wrote: > > I'm having some trouble with blobs. I have 4 blobs tht I want to > > insert into a db, and it works fine when I execute each insert as a > >

Re: [sqlite] Table Level Locking

Table level locking is used among statements for the same connection. File level locking is used among connections. Your case is file level. On Tue, Jul 1, 2008 at 8:00 PM, Joanne Pham <[EMAIL PROTECTED]> wrote: > Hi All, > I read the online document regarding "Table Level Locking" as below: >

Re: [sqlite] prepackaged sql statement

Hi Steve, This is my problem: create table inet0 (dest, mask, nexthop); create index inet0_idx on inet0(mask, destination); insert into inet0 values(x'0100', x'FF00', x'08080808'); insert into inet0 values(x'0101', x'', x'16161616'); insert into inet0 values(x'01010100',

Re: [sqlite] blob /transaction wierdness

Can you update your SQLite to the latest revision? On Tue, Jul 1, 2008 at 3:42 PM, smlacc1 leador <[EMAIL PROTECTED]> wrote: > Hi, > > I'm having some trouble with blobs. I have 4 blobs tht I want to insert > into a db, and it works fine when I execute each insert as a single commit. > However,

Re: [sqlite] blob /transaction wierdness

this worked great. thank you. On Tue, Jul 1, 2008 at 6:10 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > smlacc1 leador <[EMAIL PROTECTED]> wrote: > > I'm having some trouble with blobs. I have 4 blobs tht I want to > > insert into a db, and it works fine when I execute each insert as a > >

[sqlite] rollback/replay journals and durability of the most recent transaction

I've done a little looking into journals, fsyncs, and filesystems recently. One thing I'm trying to understand is whether there was a reason for SQLite choosing to use a rollback journal (of the steps to undo a transaction) rather than a replay journal (of the steps to perform a transaction). In

Re: [sqlite] Multiple constraints per table?

Thank you Igor, the solution below (that you also posted) is what I found in the forums after hours of searching when I should have been asleep. And rather that produce shoddy code, I downloaded a proper sqlite tool to verify that it was all working fine. Thank you for the response, I have