Re: [sqlite] question about php_pdo_sqlite

[yuyen]

Ok, finally I found the problem is that the PHP and Apache are installed in 
local drive and I put the sqlite database file on a remote file server which 
is a Linux / Samba. I can use sqlite3.exe to open that remote database with 
a mapped remote drive. And Insert, Update and Delete have no problem with 
it. While with Apache and PHP pdo, if the database is concurrently opened by 
sqlite3.exe, it will report "can't not open database". Close sqlite3.exe, 
then PHP pdo can open it, but it open as read-only. If I put the database 
file in local dirve, then there is no any problem at all. Even I can use 
sqlite3.exe concurrently with PHP pdo. Does Apache has different thinking 
about remote server?


Jack

- Original Message - 
From: "DJ Anubis" <[EMAIL PROTECTED]>

To: 
Sent: Friday, June 02, 2006 12:13 AM
Subject: Re: [sqlite] question about php_pdo_sqlite



yuyen wrote:

And the error message is:
"execute err: SQLSTATE[HY000]: General error: 8 attempt to write a
readonly database"

So the problem is why the database becomes read only.


This time, you get another message :)
Are you sure the Web Server user (Apache or...) has write access to your
sqlite database file ?

[sqlite] Re: Query performance issues - index selection

[Steve Green]

Using Richard's suggestion of changing the where clause of my query
to

where +utime >= 1146441600 and +utime < 114912000

did force sqlite to use the index that gave better performance.

However, I'm seeing some strange behavior that I'm hoping someone can
shed some light on.

With the time period mentioned below, the data set is about 2.5 million
rows and and 86K distinct u_id values.  Using the index on (u_id, utime),
the query time was reduced from 13.5 minutes to 26 seconds (not great,
but at least the browser won't timeout waiting for a response).

However, with a different time period, I have a much smaller data set of
about 150K rows and 20K distinct u_id values.  Using the index on (u_id,
utime), the query still takes about 20 seconds.  However, if the primary
index on (utime, r_id, u_id) is used, the query only takes 0.5 seconds.
Unfortunately at query time I have no idea of knowing how much data is
going to have to be traversed, so the idea of modifying the query to force
the use of different indexes is not possible.  Can anyone explain why the
performance is so poor with the smaller data set and the "optimal" index.
Any suggestions on a workaround so that optimal performance can be achieved
with all data set sizes?

Thanks for your help,

Steve

Steve Green wrote:


Sorry, I forgot to mention that I'm using sqlite v3.3.4 on redhat linux
v7.3

Steve

Steve Green wrote:


Hi,

I have the following schema

CREATE TABLE data(
utime int4,
r_id int2,
u_id int4,
data1 int8,
data2 int8
);

Each row is uniquely defined by utime, r_id, and u_id, so I have the
following index

CREATE UNIQUE INDEX pk_data on data( utime, r_id, u_id );

This index is also needed because at update time, I use "insert or
replace" to update the data table.

The type of query that I'm attempting to perform is similar to

select u_id, sum( data1 ), sum( data2 )
from data where utime >= 1146441600 and utime < 114912
group by u_id
order by sum( data1 ) desc
limit 10

My current table has about 2.5 million rows and about 86,000 distinct
u_id values in the time period selected, and the query takes about 13.5
minutes.

Performing an explain query plan reveals

0|0|TABLE data WITH INDEX pk_data

so the primary index is being used...

Based on some past experiences, I added the following index to the table

CREATE INDEX ix_data_ut on data( u_id, utime );

Note that the utime is the last parameter in the index.  With the primary
index in place, I was not able to convince sqlite to use this index.  
To test

the index, I was forced to drop the primary index (which I can't do in my
production environment).   After dropping the primary index, an 
explain query

plan revealed

0|0|TABLE data WITH INDEX ix_data_ut

and the query ran in 26 seconds...

Subsequent tests using the following indexes provided no performance 
improvement
over the unique index, although sqlite's query planner chose these 
indexes over

the unique index

CREATE INDEX ix_data_tu on data( utime, u_id );
CREATE INDEX ix_data_t on data( utime );

So, is there a way that I can get sqlite to use the optimal index 
without having
to remove my unique index?  Perhaps this would involve rewritting the 
query, but

I'm at a loss as to how that could be done.

Thanks for your time,

Steve





--
Steve Green
SAVVIS
Transforming Information Technology SM

This message contains information which may be confidential and/or
privileged.  Unless you are the intended recipient (or authorized
to receive for the intended recipient), you may not read, use,
copy or disclose to anyone the message or any information contained
in the message. If you have received the message in error, please
advise the sender by reply e-mail at [EMAIL PROTECTED] and
delete the message and any attachment(s) thereto without retaining
any copies.

Re: [sqlite] Query performance issues - index selection

[Steve Green]

Kurt,

Thanks, the single primary index you suggested does give us the
desired results.

Steve

Kurt Welgehausen wrote:

Steve Green <[EMAIL PROTECTED]> wrote:



CREATE UNIQUE INDEX pk_data on data( utime, r_id, u_id );
...
CREATE INDEX ix_data_ut on data( u_id, utime );



Wouldn't a unique index on (u_id, utime, r_id) get you
the pk constraint and better performance on your query
with no other index to confuse things?

Regards


--
Steve Green
SAVVIS
Transforming Information Technology SM

This message contains information which may be confidential and/or
privileged.  Unless you are the intended recipient (or authorized
to receive for the intended recipient), you may not read, use,
copy or disclose to anyone the message or any information contained
in the message. If you have received the message in error, please
advise the sender by reply e-mail at [EMAIL PROTECTED] and
delete the message and any attachment(s) thereto without retaining
any copies.

Re: [sqlite] Multithreading. Again.

[drh]

Florian Weimer <[EMAIL PROTECTED]> wrote:
> > As long as no prepared statements are outstanding, you should
> > be safe moving sqlite database connections across threads, as
> > of version 3.3.1.  The rules are not really quite that strict,
> > but the exact rules are more complex and this strict rule
> > gives you an extra margin of safety.
> 
> Is it possible to finalize statements in a separate thread?  This
> would be a rather important feature because on most multi-threaded
> VMs, user-defined finalizers (which would be used to clean up SQLite
> objects which are no longer used) run in a separate thread.
> 

It is possible to construct a case where finalizing a statement
from a different thread that the one where it was last stepped
would cause a problem.

Remember, that the operating system bug that is causing all the
multithreading grief is that file locks created by one thread
cannot be reliably removed or modified by a different thread.
So if a statement acquires a lock on the database file in one
thread and you try to finalize the statement in a different
thread, the finalization would involve releasing the lock in
a different thread from which it was acquired - an operation
that silently fails on certain Linux kernels.

On the other hand, if you sqlite3_reset() all statements in 
the thread where they were last run, then all locks are released
by the reset.  Then you are free to finialize the statements
from any thread you want.

--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Strange behavior with sum

[A. Pagaltzis]

* Klint Gore <[EMAIL PROTECTED]> [2006-06-02 07:30]:
> sqlite> select cast(sum(f1) as double)/cast(sum(f2) as double) from bob;
> 0.869779988128673

Just casting one of them is sufficient, btw.

Regards,
-- 
Aristotle Pagaltzis // 

RE: [sqlite] Multithreading. Again.

[Pat Wibbeler]

I don't think it's that uncommon to ask the user of the statement to
finalize database resources explicitly, even in a managed environment.
For example, Java collects memory, but the programmer must explicitly
close network, file, database, and other resources.  In java, I do this
in a finally block.  In c++, I do it using a stack based destructor
guard.

Pat

-Original Message-
From: Florian Weimer [mailto:[EMAIL PROTECTED] 
Sent: Friday, June 02, 2006 9:19 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Multithreading. Again.

> As long as no prepared statements are outstanding, you should
> be safe moving sqlite database connections across threads, as
> of version 3.3.1.  The rules are not really quite that strict,
> but the exact rules are more complex and this strict rule
> gives you an extra margin of safety.

Is it possible to finalize statements in a separate thread?  This
would be a rather important feature because on most multi-threaded
VMs, user-defined finalizers (which would be used to clean up SQLite
objects which are no longer used) run in a separate thread.

Re: [sqlite] Multithreading. Again.

[Florian Weimer]

> As long as no prepared statements are outstanding, you should
> be safe moving sqlite database connections across threads, as
> of version 3.3.1.  The rules are not really quite that strict,
> but the exact rules are more complex and this strict rule
> gives you an extra margin of safety.

Is it possible to finalize statements in a separate thread?  This
would be a rather important feature because on most multi-threaded
VMs, user-defined finalizers (which would be used to clean up SQLite
objects which are no longer used) run in a separate thread.

Re: [sqlite] .import difficulty

[A. Pagaltzis]

* cstrader232 <[EMAIL PROTECTED]> [2006-06-02 15:40]:
> strange error though because ";" can't be part of a tablename
> can it?

sqlite> create table [b;] (a,b,c);
sqlite> select * from sqlite_master;
type   name  tbl_name  rootpage  sql  
-        -
table  b;b;2 CREATE TABLE [b;] (a,b,c)

:-)

Regards,
-- 
Aristotle Pagaltzis // 

Re: [sqlite] Purging the mailing list roles. Was: Please Restore Your Account Access

[Gerry Snyder]

A. Pagaltzis wrote:

This suggests a different strategy: alternate between sending
opt-out and opt-in mails. Indiscriminate autoresponders will
unsubscribe themselves when they get an opt-out mail; people who
throw the mail away will silently drop out after failing to
respond to the opt-in mail.
  


There is a certain evil in this suggestion.

I like it a lot.

Gerry

Re: [sqlite] .import difficulty

[cstrader232]

thanks all.  strange error though because ";" can't be part of a tablename can 
it?

Re: [sqlite] full text search

[drh]

Martin Pfeifle <[EMAIL PROTECTED]> wrote:
> Dear all,
> I have just seen  that there are some thoughts going on to incorporate full 
> text search into SQLite.
> http://www.sqlite.org/cvstrac/wiki/wiki?p=FullTextIndex
> What is the current status on that project?

Work on this is progressing.  It appears that full-text search
will likely be available sometime later this year.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Multithreading. Again.

[drh]

"Peter Cunderlik" <[EMAIL PROTECTED]> wrote:
> > > 1. As of SQLite 3.3.5, there is no multithreading (MT) problem with
> > > the SQLite itself. All problems come from the underlying OS libraries.
> >
> > I would argue that this has always been the case.  But beginning
> > in version 3.3.1, SQLite has taken additional steps to partially
> > work around problems in the OS.  So the OS problems are less
> > troublesome.
> 
> 
> The words "partially" and "less troublesome" make me worry a bit. :-)
> Can I assume that if tests threadtest1.c and threadtest2.c pass on my
> platform, then there would be no serious multithreading problems? (One
> crash per ice age is perfectly acceptable.)
> 
> 

As long as no prepared statements are outstanding, you should
be safe moving sqlite database connections across threads, as
of version 3.3.1.  The rules are not really quite that strict,
but the exact rules are more complex and this strict rule
gives you an extra margin of safety.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] .import difficulty

[A. Pagaltzis]

* cstrader232 <[EMAIL PROTECTED]> [2006-06-02 15:00]:
> I'm having trouble importing.  I'm using sqlite3 from dos.
> 
> sqlite3 test.db;
> create table b (a, b, c);
> .import "test.txt" b;
> 
> returns "no such table b"

No, it reports `Error: no such table: b;` – note the semicolon. A
table called `b;` does indeed not exist. If you omit the
semicolon or separate it with a space, the command will work.
SQLite shell commands (which start with a dot) are single-line
and need not be terminated.

Regards,
-- 
Aristotle Pagaltzis // 

Re: [sqlite] .import difficulty

[Vishal Kashyap]

try

.import "test.txt" b ;

On 6/2/06, cstrader232 <[EMAIL PROTECTED]> wrote:


I'm having trouble importing.  I'm using sqlite3 from dos.

sqlite3 test.db;
create table b (a, b, c);
.import "test.txt" b;

returns "no such table b"

tia







--
With Best Regards,
Vishal Kashyap.
http://www.vishal.net.in

[sqlite] .import difficulty

[cstrader232]

I'm having trouble importing.  I'm using sqlite3 from dos.

sqlite3 test.db;
create table b (a, b, c);
.import "test.txt" b;

returns "no such table b"

tia

Re: [sqlite] Multithreading. Again.

[Peter Cunderlik]

> 1. As of SQLite 3.3.5, there is no multithreading (MT) problem with
> the SQLite itself. All problems come from the underlying OS libraries.

I would argue that this has always been the case.  But beginning
in version 3.3.1, SQLite has taken additional steps to partially
work around problems in the OS.  So the OS problems are less
troublesome.



The words "partially" and "less troublesome" make me worry a bit. :-)
Can I assume that if tests threadtest1.c and threadtest2.c pass on my
platform, then there would be no serious multithreading problems? (One
crash per ice age is perfectly acceptable.)


[snip]


Of course, it has always been the case and probably always will be
that you cannot use the same sqlite3 connection in two or more
threads at the same time.  You can use different sqlite3 connections
at the same time in different threads, or you can move the same
sqlite3 connection across threads (subject to the constraints above)
but never, never try to use the same connection simultaneously in
two or more threads.


It's never been my intention to use one connection in multiple threads
at the same time. Open the sqlite3 connection in one thread, use some
other thread to do something with it within a transaction and close
the connection in a third one. That's all.

Thank you for your help.

Peter

[sqlite] full text search

[Martin Pfeifle]

Dear all,
I have just seen  that there are some thoughts going on to incorporate full 
text search into SQLite.
http://www.sqlite.org/cvstrac/wiki/wiki?p=FullTextIndex
What is the current status on that project?