Pavel Ivanov wrote:
At least I think that is what you suggest, and think it just
may work! But I could be wrong!
Yes, that's exactly what I suggest.
Pavel
It worked! Fortunately I had already parameterized SQLITE3 as a
preference variable so I could have the same scripts run easily on
In my multithreaded applications I use sqlite3_busy_timeout(sqlite3*, int
ms) to avoid failures on temporary locked databases.
This works fine so far. The only problem is the very poor computer I have
to use. I made a test to let 3 applications do nothing, but write to the
database. When I use
On Sep 17, 2009, at 10:56 PM, Stephan Wehner wrote:
I downloaded the sqlite3 source ( sqlite-3.6.18.tar.gz ) and managed
to complete make.
Now with make fulltest, there is no progress for over almost two
hours. The present output is
misc7-6.1.1... Ok
misc7-6.1.2... Ok
There is still
On Sep 18, 2009, at 4:52 AM, s.breith...@staubli.com wrote:
In my multithreaded applications I use
sqlite3_busy_timeout(sqlite3*, int
ms) to avoid failures on temporary locked databases.
This works fine so far. The only problem is the very poor computer I
have
to use. I made a test to
Hello all!
I am searching some API function for get list of active savepoint
names. Is this possible? Thank you!
--
Lukas Gebauer.
E-mail: gebau...@mlp.cz
http://synapse.ararat.cz/ - Ararat Synapse - TCP/IP Lib.
___
sqlite-users mailing list
Hello!
I'm using extension for base unicode support
(http://mobigroup.ru/files/sqlite-ext/unicode/), but in last two releases find
the problem with indexes by columns with redefined NOCASE collation
(groups.name autoindex and composite index with
const_telephony_direction.name):
If neither index individually offers a performance boost, it's possible a
single multi-column index might be better.
Sam
On Thu, Sep 17, 2009 at 3:43 PM, Bart Smissaert bart.smissa...@gmail.comwrote:
Have tried INDEXED BY and it does indeed work and force the use of the
specified index.
It
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
D. Richard Hipp wrote:
It is suppose to use usleep() (or the equivalent, depending
on your OS) to sleep for short intervals
Note that on non-Windows platforms the default is to use sleep() which
has a one second granularity. For people who have
I was trying to force the use of a multi-column index. Will have a better
look and see what is going on here. For now I get best performance with a
2-stage approach with the use of a intermediate temp table. Will post the
exact details of this later.
RBS
If neither index individually offers a
MacBook Mac OS X 10.5.8
2 GHz Intel Core Duo
1 GB memory:
17 minutes 46 seconds.
IBM ThinkPad
Windows XP (latest patches)
1.70 GHz, 512 MB memory:
6 hours 25 minutes 57 seconds
Windows is very slow in starting new processes if compared to any Unix
system (especially if compared Windows +
Now the strange thing is that this query returns the correct number of rows,
but all the columns from the employee-table are empty.
There's no employee-table in your query.
If I change UNION to UNION ALL the join works as expected.
Is there an explanation for this behavior ?
As we don't see
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Lukas Gebauer wrote:
I am searching some API function for get list of active savepoint
names. Is this possible? Thank you!
Since your code is creating and releasing savepoints, why not just
record them in your code? Also IIRC you can also have
I am searching some API function for get list of active savepoint
names. Is this possible? Thank you!
No, this is not possible. Why do you need it in the first place?
Pavel
On Fri, Sep 18, 2009 at 5:55 AM, Lukas Gebauer gebyl...@mlp.cz wrote:
Hello all!
I am searching some API function
On Fri, Sep 18, 2009 at 2:35 AM, D. Richard Hipp d...@hwaci.com wrote:
On Sep 17, 2009, at 10:56 PM, Stephan Wehner wrote:
I downloaded the sqlite3 source ( sqlite-3.6.18.tar.gz ) and managed
to complete make.
Now with make fulltest, there is no progress for over almost two
hours. The
Alexey,
I'm using extension for base unicode support
(http://mobigroup.ru/files/sqlite-ext/unicode/), but in last two
releases find the problem with indexes by columns with redefined
NOCASE collation
This code has many problems and the version on your site (the same
version is available
I'm writing this system wherein I want operations performed on the
database to block when a lock cannot be achieved, and I'm looking at my
options. This system that has multiple processes accessing a single
sqlite file with a single database with a single table. I was
disappointed to find out
Since then I've come to realize that
sqlite doesn't have such a blocking feature. Is that correct?
Yes, that's correct.
I was thinking that a good solution would be to have a lock file,
with POSIX locks (I'm doing this in Linux) on it whenever one tries to
access the db in such a way
On Fri, Sep 18, 2009 at 7:51 AM, Stephan Wehner stephanweh...@gmail.com wrote:
On Fri, Sep 18, 2009 at 2:35 AM, D. Richard Hipp d...@hwaci.com wrote:
On Sep 17, 2009, at 10:56 PM, Stephan Wehner wrote:
I downloaded the sqlite3 source ( sqlite-3.6.18.tar.gz ) and managed
to complete make.
Pavel Ivanov wrote:
To be clear, my idea of blocking is as follows: if one tries to
achieve a lock, and it is not possible, the request is put into a queue,
and the caller stops consuming cycles. Locks are then granted (when
feasible) in the queue in the order that they were requested.
The kernel grants them: http://www.manpagez.com/man/2/flock . Or I
might use fcntl().
That's why I've asked what is different here from what SQLite already
does because SQLite uses fcntl() on database file already. You can try
to change it to flock() of course but be aware that SQLite needs
Pavel Ivanov wrote:
The kernel grants them: http://www.manpagez.com/man/2/flock . Or I
might use fcntl().
That's why I've asked what is different here from what SQLite already
does because SQLite uses fcntl() on database file already. You can try
Then it must use fcntl()
Pavel Ivanov wrote:
Hell if I know why they use fcntl() for locks, and don't even give
you the option to block.
I think because they need to detect dead locks. BTW, I believe in case
of dead lock even busy_handler will not be called, just SQLITE_BUSY is
returned...
I guess that
Angus March an...@uducat.com wrote:
Pavel Ivanov wrote:
Hell if I know why they use fcntl() for locks, and don't even give
you the option to block.
I think because they need to detect dead locks. BTW, I believe in
case of dead lock even busy_handler will not be called, just
SQLITE_BUSY is
I think because they need to detect dead locks. BTW, I believe in case
of dead lock even busy_handler will not be called, just SQLITE_BUSY is
returned...
I guess that makes sense, in cases where multiple tables are involved.
No, that makes sense when you're starting deferred transaction,
Pavel Ivanov wrote:
How does this preclude me from coming up w/my own lock file with
POSIX locks? If a bunch of process start making incompatible requests on
a single lock file, then they'll be queued and processed in order. I
don't see how you can have a deadlock when you have multiple
You mean you will lock this extra-file before doing any update and
unlock when update is done? Then ok, it will work. But again be aware
of possible dead locks.
You mean deadlocks are still possible in that scenario? How?
I mean just that I don't know exactly what do you want to do but
Angus March an...@uducat.com wrote:
Yes, I see. So what is key to the problem is that someone tries to
change their read lock to a write lock. I guess I just thought that
the kernel that manages fcntl() would have a way of dealing with
this. Can this situation not be averted if at step 3,
On 18 Sep 2009, at 4:49pm, bartsmissa...@blueyonder.co.uk wrote:
Did something in the documentation make
you think SQLite wouldn't use a
multi-column index unless you forced it ?
No, but I just noticed it didn't use the index I thought would be
best. As
it turned out it looks I was
Igor Tandetnik wrote:
Angus March an...@uducat.com wrote:
Yes, I see. So what is key to the problem is that someone tries to
change their read lock to a write lock. I guess I just thought that
the kernel that manages fcntl() would have a way of dealing with
this. Can this situation not
So if you had a team of programmers to write something like SQLite
which didn't have the drawbacks SQLite has, which drawbacks would you
identify ? I'm asking not about minor faults with specific SQLite
library calls, but about the sort of things which require rewriting
from the ground
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Simon Slavin wrote:
* Unicode support from the ground up
SQLite already has unicode support from the ground up. Try using
non-Unicode strings and you'll see!
The issue some developers have is that they also want collations, case
comparisons etc
On 18 Sep 2009, at 9:07pm, Roger Binns wrote:
Simon Slavin wrote:
* Unicode support from the ground up
SQLite already has unicode support from the ground up. Try using
non-Unicode strings and you'll see!
SQLite's indexing correctly understands how to order Unicode
strings ? It can do
Stored Procedures
Noah
Simon Slavin-2 wrote:
So if you had a team of programmers to write something like SQLite
which didn't have the drawbacks SQLite has, which drawbacks would you
identify ? I'm asking not about minor faults with specific SQLite
library calls, but about the
This is what I am dealing with:
2 tables with exactly the same schema (but could be slightly
different, so can't put in same table):
CREATE TABLE TABLE1(
[PATIENT_ID] INTEGER,
[ENTRY_ID] INTEGER PRIMARY KEY,
[READ_CODE] TEXT,
[ADDED_DATE] TEXT,
[START_DATE] TEXT)
The last 2 date
Hello!
On Saturday 19 September 2009 00:43:18 Noah Hart wrote:
Stored Procedures
There are Tiny C compiler extension and realization of
stored procedures for SQLite 2 and Lua extension and other.
So you can use one or all of these.
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
Simon Slavin
slav...@hearsay.demon.co.uk wrote:
On 18 Sep 2009, at 9:07pm, Roger Binns wrote:
Simon Slavin wrote:
* Unicode support from the ground up
SQLite already has unicode support from the ground up. Try using
non-Unicode strings and you'll see!
SQLite's indexing correctly
On 18 Sep 2009, at 9:07pm, Roger Binns wrote:
Simon Slavin wrote:
* Unicode support from the ground up
SQLite already has unicode support from the ground up. Try using
non-Unicode strings and you'll see!
SQLite's indexing correctly understands how to order Unicode
strings ? It
*I'm leaving aside the rant that your first delete is not identical to
combination of the select and delete in the second approach and select
in second approach contains typos...*
But did you try to combine your insert and delete statements from the
second approach? This approach quicker because
Thanks, will have a look at your suggestion and yes, I had a feeling I
was overlooking some elemental things here. I typed it out all bit
quick (hence the typo's and difference in the deletes), but I thought
it would make clear what was going on. Will test now and see if your
suggestion is indeed
From:
Bart Smissaert bart.smissa...@gmail.com
Then the SQL I was trying to improve:
DELETE
FROM
TABLE2
WHERE
PATIENT_ID NOT IN (
SELECT
TABLE1.PATIENT_ID
FROM
TABLE1
WHERE
JULIANDAY(TABLE2.START_DATE, '-14 month')
JULIANDAY(TABLE1.START_DATE) AND
TABLE1.PATIENT_ID =
Hi,
I'm trying to optimize a query for 2 different scenarios, and I'm
having trouble getting something that works good in general. I want
to be sure I'm not missing something. Here are the tables and indexes
used in my database:
sqlite CREATE TABLE path_table (idx INTEGER PRIMARY KEY, path
We don't really need that SQLite. We already have it. It is commonly
called MySQL. It take well over 150MB of disk space and major management
efforts to maintain any level of performance. Just what the client/server
guys love to play with.
SQLite is way too small and Bring to catch their
StepSqlite brings powerful Stored Procedure support with full power of
PL/SQL syntax to SQLite. It is a 'compiler' as opposed to a mere
'wrapper' so it generates much more efficient code than any wrapper
could ever achieve - for instance, it pre-compiles all SQL in your
code right when the
DELETE
FROM
TABLE2
WHERE
ENTRY_ID NOT IN
(SELECT T2.ENTRY_ID FROM
TABLE2 T2 INNER JOIN TABLE1 T1 ON
(T1.PATIENT_ID = T2.PATIENT_ID)
WHERE julianday(T2.START_DATE, '+15 month')
julianday(T1.START_DATE)
)
That is indeed a lot faster and then slightly faster than my approach
with the
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Noah Hart wrote:
Stored Procedures
Stored procedures don't make sense as a core part of SQLite because there is
no one solution that fits all. For example what language would you write
them in, and how would you deal with security (blindly loading
Had a look at this suggestion now and it works and uses the PATIENT_ID,
ADDED_DATE index, but it is as slow as my delete with Julianday. It
looks Pavel's suggestion is the way to do this. Just will have a look
now and see if doing the construction with DATE( instead of Julianday
is any faster.
thanks Pavel,
and sorry for mixing the wikipedia example with the real situation.
create table RT1 ( PID integer, V1 text );
insert into RT1 values ( '684', 'aap' );
insert into RT1 values ( '685', 'other empty' );
create table RT2 ( PID integer, V2 text );
insert into RT2 values ( '684', 'beer'
On 18 Sep 2009, at 9:43pm, Noah Hart wrote:
Stored Procedures
How do those differ from what can be done with triggers ?
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
On 18 Sep 2009, at 9:57pm, Igor Tandetnik wrote:
Simon Slavin
slav...@hearsay.demon.co.uk wrote:
On 18 Sep 2009, at 9:07pm, Roger Binns wrote:
Simon Slavin wrote:
* Unicode support from the ground up
SQLite already has unicode support from the ground up. Try using
non-Unicode strings
OK, it looks the construction with DATE instead of Julianday is a bit
faster, so best option here seems to be:
DELETE FROM TABLE1 WHERE
ENTRY_ID NOT IN (
SELECT T1.ENTRY_ID FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON
(T1.PATIENT_ID = T2.PATIENT_ID)
WHERE
DATE(T1.ADDED_DATE, '+15 month')
Stef Mientki s.mien...@ru.nl wrote:
create table RT1 ( PID integer, V1 text );
insert into RT1 values ( '684', 'aap' );
insert into RT1 values ( '685', 'other empty' );
create table RT2 ( PID integer, V2 text );
insert into RT2 values ( '684', 'beer' );
insert into RT2 values ( '686', 'other
Simon Slavin
slav...@hearsay.demon.co.uk wrote:
On 18 Sep 2009, at 9:57pm, Igor Tandetnik wrote:
Simon Slavin
slav...@hearsay.demon.co.uk wrote:
On 18 Sep 2009, at 9:07pm, Roger Binns wrote:
Simon Slavin wrote:
* Unicode support from the ground up
SQLite already has unicode support from
On Sat, Sep 19, 2009 at 12:14:37AM +0100, Simon Slavin scratched on the wall:
On 18 Sep 2009, at 9:57pm, Igor Tandetnik wrote:
Simon Slavin
slav...@hearsay.demon.co.uk wrote:
On 18 Sep 2009, at 9:07pm, Roger Binns wrote:
Simon Slavin wrote:
* Unicode support from the ground up
On 19 Sep 2009, at 12:55am, Igor Tandetnik wrote:
Using ICU extension does require certain discipline. You must run
icu_load_collation soon after opening the database, and all users of
the
database must agree to map the same identifiers to the same locales
(the
best way to achieve that
Simon Slavin wrote:
Thanks to you and Jay for explanations. I hadn't encountered ICU at
all before. Your descriptions make perfect sense and are very
interesting since ICU is a good attempt to get around one of the
fundamental problems of Unicode.
Out of curiosity - what do you consider a
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Simon Slavin wrote:
Your descriptions make perfect sense and are very
interesting since ICU is a good attempt to get around one of the
fundamental problems of Unicode.
Errr, this is not the fault of Unicode. It is the fault of people!
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hamish Allan wrote:
Not sure whether this would need a rewrite, but for debug purposes I'd
love to be able to view the SQL for a prepared statement with its
values bound.
I am always confused by requests like this. Your code called prepare and
On 19 Sep 2009, at 3:07am, Igor Tandetnik wrote:
Simon Slavin wrote:
Thanks to you and Jay for explanations. I hadn't encountered ICU at
all before. Your descriptions make perfect sense and are very
interesting since ICU is a good attempt to get around one of the
fundamental problems of
This is not a good example i think.
If a transaction is intent to update after the select, it should start
a write lock before the select.
And as described in previous 'dead lock' example, the update in this
example could fail due to 'dead lock'
I believe the 'read lock' is designed for a 'read
Please sorry for my terrible Engilsh. :)
Thanks for the answer.
Yes, I know that it is bad design in the common case. But I have to use
it because I have data which has following format:
time | value_1 | value_2|value_
|---|-|
It'll work, but SQLite does not use a balanced tree to store the
columns for a particular record. So if you're seeking the 700th
column of a particular row, it has to look through 699 others before
it gets to it. Unless you always handle all the columns of a row
together, it'll be
61 matches
Mail list logo