Harold Wood & Meyuni Gani wrote:
>
> U, hmm. The tips I gave you were from my pda based shopping program
> that will be selling for 9.99 soon.
>
Good on you Woody, hope you sell a lot.! If my eyes were up to the challenge
of reading my PDA's small screen, i'd buy a copy and save myself wor
> > I'm using SQLite 3.5.9 and there are no differents in my tests
> > between DESC
> > and default indeces. I try create index with keywork DESC for
> > optimize DESC
> > sorting but it don't work for me. My tests you can see above.
>
> Have you seen the notes on file-format and "pragma legacy_fil
U, hmm. The tips I gave you were from my pda based shopping program that
will be selling for 9.99 soon. Its 6 for one, half dozen for the other. You
can design the db so it does the work for you or you code the program to do the
work for you.
Either way, you will get things to work, it jus
I understand your solution Woody, but it isn't practical for me, not for my
home shopping list program. A full on relational database like that is an
awful lot of work and it's only for home use (and any other family I can con
into using it and testing it).
I'd go for the relational route if I we
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 learned
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
On Jul 2, 2008, at 2:53 AM, 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 events(type,eid)"
>>> and "CREATE INDEX ev_idx ON events(type,e
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
> > si
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,
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', x'
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:
>
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
> > singl
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,
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 numb
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
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
___
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 comm
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 comm
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 eve
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>
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.
>>
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
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 exp
В сообщении от 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 DESC
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 ord
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, I
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 = sqlite3_open("test.sqlite",&
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 blob
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
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 work
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
http://sqlite.org:8080/cgi-bin/mailman/listin
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 man
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 the
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
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
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 time
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 primar
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
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
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 take up to a day
Joanne Ph
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 can take up to a day
Joann
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 insertTa
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 co
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.)
___
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
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 o
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, SQLite
Mihai Limbasan <[EMAIL PROTECTED]> wrote:
> 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);
Terrific!
sqlite> SELECT events.* FROM events WHERE eid<=3261976 AND +type=22
ORDER BY eid DESC LIMIT 1;
3261891|4910298|1206924|1|22|9|4
CPU Time: user 0.002000 sys 0.017997
Now I have a workaround - thanks to everyone for the help. I assume
that what sqlite is doing under the hood here is
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
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 Of
I agree. If I drop indices that use "type", I get my performance back
for this query:
sqlite> SELECT events.* FROM events WHERE eid<=32619760 AND type=22
ORDER BY eid DESC LIMIT 1;
16643833|27906245|5972704|0|22|9|4
CPU Time: user 0.001000 sys 0.001000
sqlite> EXPLAIN QUERY PLAN SELECT event
В сообщении от 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
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 multi-colu
>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
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. Can
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
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 dis
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 cases
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 li
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 o
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:
i
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
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 SQL
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
http://sqlite.org:8080/cgi-bin/mailm
"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 w.Lang
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 w.Revhttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
"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 elim
Hi,
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. I
am trying to use an in-memory database. My understanding is that opening
a connection with ":memory:" creates the database and closing it erases
"martin linklater"
<[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hi. I'm real new to SQLite via the command line, but I have used
> CoreData quite a bit on OSX. My problem is that I have two tables
> that I want to link via a unique ID, but I don' t know how to
> automatically link
Hi. I'm real new to SQLite via the command line, but I have used CoreData
quite a bit on OSX. My problem is that I have two tables that I want to link
via a unique ID, but I don' t know how to automatically link the ID
parameter properly. My tables look like this:
create table snapshot( id INTEGER
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 -DSQLI
"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 CONSTR
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 un
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 e
75 matches
Mail list logo