Re: [sqlite] Legal to use sqlite docs in commercial applications ?

[George Ionescu]

Hello Dr. Hipp,

sorry for the second repost. I've had some problems with mailing lists and I
didn't notice my message actually arrived and that I've even got a reply.

> I have no objection to you using the parts that I wrote.  But bare
> in mind that many others have contributed to the documentation.

Thanks.
How can I distinguish between *your* documentations and everyone else's ?

Regards,
George Ionescu

[sqlite] Legal to use sqlite docs in commercial applications ?

[George Ionescu]

Hello Dr. Hipp,
Hello sqlite users,

I would like to know if it's legal to use sqlite documentation (sql syntax, 
sqlite logo etc.) into commercial applications' documentation (which are 
obviously built on top of sqlite).

Thanks.

Regards,
George Ionescu

Re: [sqlite] Sqlite Binding

[D. Richard Hipp]

Randall Fox wrote:
> [I]s there any chance of allowing the '?' use in a create table
> statement as well?  For binding of default values of a particular
> column.
>
Not much.  Recall that SQLite stores the original text of
the CREATE TABLE statement in the SQLITE_MASTER table and
reparses that text when it needs to reload the schema.  That
plan won't work so well if the CREATE TABLE contains one
or more "?" characters.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

Re: [sqlite] Why does my query take so long

[Dennis Cote]

Ulrik Petersen wrote:
> Hi,
>
>> Hi, I am having a problem with the following query. It seems to
>> force php to timeout after 30secs. The query goes through 150K
>> records. Is there anything I can do to speed it up?
>>
>> code-
>> SELECT call_id, C.extn_no AS extn_no, dest, dest_name, call_time,
>> duration, cost, U.firstname AS firstname, U.surname AS surname
>> FROM call_data as C LEFT JOIN users as U on C.extn_no = U.extn_no
>> WHERE C.stamptime >= $unixtimestart
>> AND C.stamptime <= $unixtimeend
>> AND direction = 'Out'
>> ORDER BY cost desc LIMIT 0,16
>> --
>
> You can try using "C.stamptime BETWEEN $unixtimestart AND
> $unixtimeend" instead, and then put an index on C.stamptime.  I have
> found that BETWEEN ... AND ... is faster than the "<= AND >="
> version, especially if you put an index on the column.  If you look
> at the VM code with EXPLAIN, you will see why.
>
>>
>> Lloydie-t
>
> Ulrik P.

Lloyd and Ulrik,

Don't use the "BETWEEN x AND y" syntax! It always does a table scan even if
there is an index available. Use two seperate comparisons. The explain
output below shows the four cases for some sample tables of mine:

compares no index
between no index
compraes with index
between with index

with no index
select * from device_property_value where device_property_id >= 100 and
device_property_id <= 200
0 Goto 0 18
1 Integer 0 0
2 OpenRead 0 63
3 SetNumColumns 0 3
4 Rewind 0 16
5 Column 0 1
6 Integer 100 0
7 Lt 26881 15 collseq(BINARY)
8 Column 0 1
9 Integer 200 0
10 Gt 26881 15 collseq(BINARY)
11 Column 0 0
12 Column 0 1
13 Column 0 2
14 Callback 3 0
15 Next 0 5
16 Close 0 0
17 Halt 0 0
18 Transaction 0 0
19 VerifyCookie 0 47
20 Goto 0 1
21 Noop 0 0

select * from device_property_value where device_property_id between 100 and
200
0 Goto 0 20
1 Integer 0 0
2 OpenRead 0 63
3 SetNumColumns 0 3
4 Rewind 0 18
5 Column 0 1
6 Dup 0 0
7 Integer 100 0
8 Ge 26880 11 collseq(BINARY)
9 Pop 1 0
10 Goto 0 17
11 Integer 200 0
12 Gt 26881 17 collseq(BINARY)
13 Column 0 0
14 Column 0 1
15 Column 0 2
16 Callback 3 0
17 Next 0 5
18 Close 0 0
19 Halt 0 0
20 Transaction 0 0
21 VerifyCookie 0 49
22 Goto 0 1
23 Noop 0 0

with index on device_property_value(device_property_id)

select * from device_property_value where device_property_id >= 100 and
device_property_id <= 200
0 Goto 0 32
1 Integer 0 0
2 OpenRead 0 63
3 SetNumColumns 0 3
4 Integer 0 0
5 OpenRead 1 178 keyinfo(1,BINARY)
6 Integer 200 0
7 NotNull -1 10
8 Pop 1 0
9 Goto 0 29
10 MakeRecord 1 0 i
11 MemStore 0 1
12 Integer 100 0
13 NotNull -1 16
14 Pop 1 0
15 Goto 0 29
16 MakeRecord 1 0 i
17 MoveGe 1 29
18 MemLoad 0 0
19 IdxGE 1 29 +
20 RowKey 1 0
21 IdxIsNull 1 28
22 IdxRecno 1 0
23 MoveGe 0 0
24 Column 0 0
25 Column 0 1
26 Column 0 2
27 Callback 3 0
28 Next 1 18
29 Close 0 0
30 Close 1 0
31 Halt 0 0
32 Transaction 0 0
33 VerifyCookie 0 48
34 Goto 0 1
35 Noop 0 0

select * from device_property_value where device_property_id between 100 and
200
0 Goto 0 20
1 Integer 0 0
2 OpenRead 0 63
3 SetNumColumns 0 3
4 Rewind 0 18
5 Column 0 1
6 Dup 0 0
7 Integer 100 0
8 Ge 26880 11 collseq(BINARY)
9 Pop 1 0
10 Goto 0 17
11 Integer 200 0
12 Gt 26881 17 collseq(BINARY)
13 Column 0 0
14 Column 0 1
15 Column 0 2
16 Callback 3 0
17 Next 0 5
18 Close 0 0
19 Halt 0 0
20 Transaction 0 0
21 VerifyCookie 0 48
22 Goto 0 1
23 Noop 0 0

As you can see, only the third case uses the index.

If the range of value that meet your conditions is small relative to the
size of your table, using the index can be much faster. If your table has
150K records then a table scan will look at all 150K of them. If your range
selects 1000 records in the middle, the index will be used to locate the
first matching record, and then it will scan the 1000 records of interest
and stop when it finds the first record greater than the maximum value.

I don't know why BETWEEN doesn't use an index, but it doesn't, so stick to
comparisons. The VDBE code produced is larger, but it executes much faster.

Dennis Cote

[sqlite] Sqlite Binding

[Randall Fox]

On Wed, 01 Dec 2004 16:35:48 -0500, you wrote:

>I have lately noticed a need for an "IS" operator in SQLite.
>IS would work just like "=" for most things.  The difference
>is that "IS" would compares NULLs as equals.  There would,
>of course, need to be a corresponding "IS NOT" operator.
>
>You can already us the IS operator with a right-hand side
>of NULL.  For example:  "x IS NULL" or "x IS NOT NULL".  What
>I am proposing is to expand IS so that the right-hand side
>can be an arbitrary expression.  Like this:  "x IS 5" or
>"x IS NOT y".
>
>The motivation for this change is so that one can compile
>statements that use "?" as the right-hand side of IS and
>then insert NULL or a value as appropriate.
>
>Thoughts?

Sounds good, it would aid in the bindings a lot in those cases you
mentioned.  Without it, you would need to alter the sql statement
instead of having a simple IF statement.  I guess you would bind NULL
with the sqlite3_bind_null. 

Speaking of bindings, is there any chance of allowing the '?' use in a
create table statement as well?  For binding of default values of a
particular column.  

Thanks

Randall Fox

Re: [sqlite] SQL "IS" operator.

[Grzegorz Makarewicz]

Brass Tilde wrote:
You can already us the IS operator with a right-hand side
of NULL.  For example:  "x IS NULL" or "x IS NOT NULL".  What
I am proposing is to expand IS so that the right-hand side
can be an arbitrary expression.  Like this:  "x IS 5" or
"x IS NOT y".
The motivation for this change is so that one can compile
statements that use "?" as the right-hand side of IS and
then insert NULL or a value as appropriate.

In the last 5 years coding against SQL Server, I've *never* needed such an
operator.  I've gotten so used to coding SQL statements to properly check
for null before checking for a value that I don't even notice the effort
anymore.
That said, I like the idea.  There are somethings that would be much easier.
Is there any precedent for it in the SQL-92 standard, or is it something
completely new?

*IS* is pretty cool for MS, Sybase, Oracle, but for other SQL92 
databases joins are required, so x in (sql) is rather useless

sql != sql92
mak

RE: [sqlite] SQL "IS" operator.

[Brass Tilde]

> You can already us the IS operator with a right-hand side
> of NULL.  For example:  "x IS NULL" or "x IS NOT NULL".  What
> I am proposing is to expand IS so that the right-hand side
> can be an arbitrary expression.  Like this:  "x IS 5" or
> "x IS NOT y".
> 
> The motivation for this change is so that one can compile
> statements that use "?" as the right-hand side of IS and
> then insert NULL or a value as appropriate.

In the last 5 years coding against SQL Server, I've *never* needed such an
operator.  I've gotten so used to coding SQL statements to properly check
for null before checking for a value that I don't even notice the effort
anymore.

That said, I like the idea.  There are somethings that would be much easier.
Is there any precedent for it in the SQL-92 standard, or is it something
completely new?

Re: [sqlite] SQL "IS" operator. Was: field=null vs. field isnull

[Ara.T.Howard]

On Wed, 1 Dec 2004, D. Richard Hipp wrote:
I have lately noticed a need for an "IS" operator in SQLite.
IS would work just like "=" for most things.  The difference
is that "IS" would compares NULLs as equals.  There would,
of course, need to be a corresponding "IS NOT" operator.
You can already us the IS operator with a right-hand side
of NULL.  For example:  "x IS NULL" or "x IS NOT NULL".  What
I am proposing is to expand IS so that the right-hand side
can be an arbitrary expression.  Like this:  "x IS 5" or
"x IS NOT y".
The motivation for this change is so that one can compile
statements that use "?" as the right-hand side of IS and
then insert NULL or a value as appropriate.
Thoughts?
sounds great.  i'm fond of '==' or '===' though - but perhaps this is 
reserved
or too confusing.
-a
--
===
| EMAIL   :: Ara [dot] T [dot] Howard [at] noaa [dot] gov
| PHONE   :: 303.497.6469
| When you do something, you should burn yourself completely, like a good
| bonfire, leaving no trace of yourself.  --Shunryu Suzuki
===

Re: [sqlite] sqlite shell

[Mateusz Łoskot]

User D. Richard Hipp wrote::
Mateusz Łoskot wrote:
I wonder if there is any plan of development sqlite shell?
I have some small list of new features I would like to add to the
"sqlshell" (SQLiteSHELL ;-) but I would like to know it
there is some roadmap or sth about that.
I'm sure the command-line shell will be improved from time
to time.  But there is no specific plan about when those
improvements might occur or what they might be.
OK, so if I add something new I will announce about that
on the list.
Best regards
--
Mateusz Łoskot, mateusz (at) loskot (dot) net
Registered Linux User #220771, Debian (Sarge)

Re: [sqlite] sqlite shell

[D. Richard Hipp]

Mateusz Łoskot wrote:
I wonder if there is any plan of development sqlite shell?
I have some small list of new features I would like to add to the
"sqlshell" (SQLiteSHELL ;-) but I would like to know it
there is some roadmap or sth about that.
I'm sure the command-line shell will be improved from time
to time.  But there is no specific plan about when those
improvements might occur or what they might be.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

[sqlite] sqlite shell

[Mateusz Łoskot]

Hello,
I wonder if there is any plan of development sqlite shell?
I have some small list of new features I would like to add to the
"sqlshell" (SQLiteSHELL ;-) but I would like to know it
there is some roadmap or sth about that.
Best regards
--
Mateusz Łoskot, mateusz (at) loskot (dot) net
Registered Linux User #220771, Debian (Sarge)

[sqlite] SQL "IS" operator. Was: field=null vs. field isnull

[D. Richard Hipp]

I have lately noticed a need for an "IS" operator in SQLite.
IS would work just like "=" for most things.  The difference
is that "IS" would compares NULLs as equals.  There would,
of course, need to be a corresponding "IS NOT" operator.
You can already us the IS operator with a right-hand side
of NULL.  For example:  "x IS NULL" or "x IS NOT NULL".  What
I am proposing is to expand IS so that the right-hand side
can be an arbitrary expression.  Like this:  "x IS 5" or
"x IS NOT y".
The motivation for this change is so that one can compile
statements that use "?" as the right-hand side of IS and
then insert NULL or a value as appropriate.
Thoughts?
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

[sqlite] benchmarking SQL queries

[Eli Burke]

I've read through pretty much everything available on sqlite.org, 
including Dr. Hipp's helpful slides from
the 2004 International PHP conference (slides 56-58), but I still feel 
like I'm stumbling around in the dark
when it comes to writing optimizedl SQL queries (specifically, SELECTS + 
JOINS).

I know about using EXPLAIN to see the opcodes generated for a query. 
I've seen people post
benchmarks by piping a single query into the sqlite3 command line tool. 
But for me, looking at opcodes
isn't very helpful and the queries I'm trying to optimize are rather 
complicated with  multiple substitutions
and JOINS between 3 or 4 tables.

So I was thinking that a useful addition would be a function along the 
lines of sqlite3_last_insert_rowid()--
sqlite3_last_query_time(). Call it after sqlite3_exec(), 
sqlite3_get_table() or sqlite3_finalize() and you get
CPU ticks or elapsed milliseconds. Call it after sqlite3_step() and you 
get elapsed time for just that row.
Or maybe a debug mode where the full text of all queries and their 
elapsed time are automatically inserted
as rows into a (temporary?) table. With something like this, one can 
fiddle with their queries and easily
check to see if there is any benefit to rearranging them.

Is this just wishful thinking? Am I better off spamming the list with 
schemas and queries?

Thanks,
Eli

[sqlite] pragma allbytes; pragma locks_block;

[Ara.T.Howard]

every month or so someone seems to write in with 'database locked' 
questions.
is there any good reason a pragma could not be added to
  lock the file in a blocking fashion (pragma locks_block)
  lock the whole file vs. byte range locking (pragma allbytes)
??
i realize this yields lower performance - but surely this is not always the
case: polling to db to catch it in an unlocked state cannot be a nice for
heavy concurrent use as letting the process go to sleep untill notified by the
os that a lock has been granted.  locking the whole file would be an option
for the truely paranoid (like me) who are also doing crazy things like
concurrent access of nfs mounted sqlite files.  the thought of byte range
locks/writes on an nfs mounted file makes me cringe.
regards.
-a
--
===
| EMAIL   :: Ara [dot] T [dot] Howard [at] noaa [dot] gov
| PHONE   :: 303.497.6469
| When you do something, you should burn yourself completely, like a good
| bonfire, leaving no trace of yourself.  --Shunryu Suzuki
===

Re: [sqlite] field=null vs. field isnull

[Ara.T.Howard]

On Wed, 1 Dec 2004, Brass Tilde wrote:
i've read the 'null handling' section and still not found the answer to
this
question:
   why should
 'select * from tbl where field=null'
   be any different from
 'select * from tbl where field isnull'
The short answer is "because it's different".
A longer answer would reference the SQL-92 standard, talking about how any
comparison involving null will resolve to false, including "null = null".
Null is defined to be not equal to anything, including null.
You *must* use "is null" to check for null, that's the way the SQL language
is implemented, and it's the way that all SQL-92 compliant implementations
are supposed to behave.
hmmm.  that's a good answer, but from the docs:
"SQL As Understood By SQLite
The SQLite library understands most of the standard SQL language. But it does
omit some features while at the same time adding a few features of its own.
This document attempts to describe precisely what parts of the SQL language
SQLite does and does not support. A list of keywords is given at the end."
i guess what i'm driving at is that, given that sqlite is not sql-92
compliant, there seems to be good reason for this behaviour.
oh well.  guess i'll live with it.
thanks for the response.
-a
--
===
| EMAIL   :: Ara [dot] T [dot] Howard [at] noaa [dot] gov
| PHONE   :: 303.497.6469
| When you do something, you should burn yourself completely, like a good
| bonfire, leaving no trace of yourself.  --Shunryu Suzuki
===

Re: [sqlite] Legal to use sqlite docs in commercial applications?

[D. Richard Hipp]

George Ionescu wrote:
Hello Dr. Hipp,
Hello sqlite users,
I would like to know if it's legal to use sqlite documentation (sql 
syntax, sqlite logo etc.) into commercial applications' documentation 
(which are obviously built on top of sqlite).

I have no objection to you using the parts that I wrote.  But bare
in mind that many others have contributed to the documentation.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

Re: [sqlite] %Q in sqlite3_mprintf

[D. Richard Hipp]

Randall Randall wrote:
I notice that the "%Q" option for sqlite3_mprintf
no longer appears in the documentation, but still
works as of 3.0.8.  Is this now deprecated, or is
there some other reason for omitting it?
Must be an oversight.  SQLite uses %Q internally so
it isn't likely to go away anytime soon.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

Re: [sqlite] This is a test...

[D. Richard Hipp]

Ralph Wetzel wrote:
... to check if the SQLite Mailinglist is down??
Disregard if not down ;-)
I got back from a business trip this afternoon (Wed)
and noticed that I hadn't seen any SQLite mail for a
couple of days.  So I logged onto www.sqlite.org and
reset the qmail daemon.  That seemed to clear whatever
the problem was.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

RE: [sqlite] Tools for sqlite ver 3

[Griggs, Donald]

Amir,

Regarding: Are there any tools like sqliteplus that work with version 3?

Not sure what platform you need, but SqliteExplorer
http://www.sqlite.org/contrib

And SqliteCC (Control Center)
http://home.student.uu.se/frax0795/
Look for sqlitecc-api3-exe.zip and you must also have it's DLL along
with
  MSVCP71.DLL, msvcr71.dll

Are nice, open utilities.


Donald Griggs
Desk: 803-735-7834

Opinions are not necessarily those of Misys Healthcare Systems nor its board
of directors.

Re: [sqlite] field=null vs. field isnull

[Eric Bohlman]

Ara.T.Howard wrote:
i've read the 'null handling' section and still not found the answer to 
this
question:

  why should
'select * from tbl where field=null'
  be any different from
'select * from tbl where field isnull'
Because the SQL standard says so.  By definition, a null never compares 
as equal to anything, *even another null* ("null=null" is defined to 
evaluate as false).  Some people refer to this as "SQL's three-valued 
logic."

[sqlite] SQLite ( old and new ) and QNX

[Adlai Shawareb]

Hi,

 

I am using the latest version of QNX (6.3.0). I got SQLite 2.7.3 from
the QNX web site. I would prefer to use SQLite3, and I'm wondering if
there is anything I unique to QNX that was done for SQLite 2.7.3 that I
would need to do to make SQLite3 work. I am thinking of malloc calls, or
anything else that might be a problem.

 

Regards,

Adlai

 

Re: [sqlite] field=null vs. field isnull

[Brass Tilde]

> i've read the 'null handling' section and still not found the answer to
this
> question:
>
>why should
>
>  'select * from tbl where field=null'
>
>be any different from
>
>  'select * from tbl where field isnull'

The short answer is "because it's different".

A longer answer would reference the SQL-92 standard, talking about how any
comparison involving null will resolve to false, including "null = null".
Null is defined to be not equal to anything, including null.

You *must* use "is null" to check for null, that's the way the SQL language
is implemented, and it's the way that all SQL-92 compliant implementations
are supposed to behave.

Re: [sqlite] Help compiling sqlite2

[Ulrik Petersen]

Hi,

> Hi,
>
> I need to compile sqlite 2.8.15 under Linux and an other UNIX platform
> with THREAD_SAFE, NDEBUG and -DTEMP_STORE=3 enabled.
> If I write something like:
>
> ./configure --enable-tempdb-in-ram=always
>
> I can see in the makefile that -DTEMP_STORE is defined as 3.
> But what about THREAD_SAFE and NDEBUG?

You need to do this before you run configure (assuming bash is the shell):

$ export BUILD_CFLAGS="-DTHREAD_SAFE=1 -DNDEBUG=1"
$ export TARGET_CFLAGS="-DTHREAD_SAFE=1 -DNDEBUG=1"

Read the top of the configure.ac script for why.

> And in Linux, have I to manually add -lpthread to the Makefile?

I don't know about this, but it would probably be in TARGET_CFLAGS if you
need to add it.  That would add it to the TCC Makefile variable, which in
turn would add it to LTLINK, which is used when linking the libraries and
executables.

HTH

Ulrik Petersen
-- 
Ulrik Petersen, Denmark

Re: [sqlite] Why does my query take so long

[Ulrik Petersen]

Hi,

> Hi, I am having a problem with the following query. It seems to force php
> to timeout after 30secs. The query goes through 150K records. Is there
> anything I can do to speed it up?
>
> code-
> SELECT call_id, C.extn_no AS extn_no, dest, dest_name, call_time,
> duration, cost, U.firstname AS firstname, U.surname AS surname
> FROM call_data as C LEFT JOIN users as U on C.extn_no = U.extn_no
> WHERE C.stamptime >= $unixtimestart
> AND C.stamptime <= $unixtimeend
> AND direction = 'Out'
> ORDER BY cost desc LIMIT 0,16
> --

You can try using "C.stamptime BETWEEN $unixtimestart AND $unixtimeend"
instead, and then put an index on C.stamptime.  I have found that BETWEEN
... AND ... is faster than the "<= AND >=" version, especially if you put
an index on the column.  If you look at the VM code with EXPLAIN, you will
see why.

>
> Lloydie-t

Ulrik P.

-- 
Ulrik Petersen, Denmark

[sqlite] %Q in sqlite3_mprintf

[Randall Randall]

I notice that the "%Q" option for sqlite3_mprintf
no longer appears in the documentation, but still
works as of 3.0.8.  Is this now deprecated, or is
there some other reason for omitting it?
--
Randall Randall <[EMAIL PROTECTED]>
Property law should use #'EQ , not #'EQUAL .

[sqlite] Help compiling sqlite2

[Marco Bambini]

Hi,
I need to compile sqlite 2.8.15 under Linux and an other UNIX platform 
with THREAD_SAFE, NDEBUG and -DTEMP_STORE=3 enabled.
If I write something like:

./configure --enable-tempdb-in-ram=always
I can see in the makefile that -DTEMP_STORE is defined as 3.
But what about THREAD_SAFE and NDEBUG?
Have I to manually add them to the TCC line in the Makefile?
And in Linux, have I to manually add -lpthread to the Makefile?
Thanks a lot for your help.
Regards,
Marco Bambini

Re: [sqlite] sqlite-GUI - under linux

[Maurício M. Maia]

A web based tool, like phpmyadmin:

http://sourceforge.net/projects/sqlitemanager/


 --- ajit mote <[EMAIL PROTECTED]> escreveu: 
>   hello freind's ,
> i am using sqlite on terminal ..
> any download version under linux which
> is availabale in GUI ..
> 
>  THNKING U.
>  

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] Locking database

[Ara.T.Howard]

On Wed, 1 Dec 2004, Alexander Jordanov wrote:
this is getting a bit of off thread so if anyone is annoyed please speak up.
I can't really make that because in PHP i can't set a global variable that
will be available for anyone that uses the script. If i set a variable and
change it in the script the next one that runs the script will have the
unchanged variable... I can't set a variable that will be valid across
users...
i meant global per-process.
I did the closest i could to what you wrote and need i say? the database got
locked again :( I checked all the scripts to be sure that every script uses
the class and there are no scripts that don't use the class... so 1) is
excluded can you explain more 2) i could't understand it right what do you
mean by :" fcntl based locks are process based so all thread 'get the lock'
" I don't know how exactly are working apache and PHP under Linux but i
suppose that the whole PHP is one process and every script is run as a
thread... so as a man opens the site a new thread is run for the script he
opened so yes the access is multythreaded as any web site.
what i mean is that if php threads are green threads and one of them gets 
the
lock they all will have it.  i've no idea how PHP, apache, and sqlite fit
together.
I also tried that:
database - seriously, if you coordinated ALL access through something like
 ret = flock 'db.lock', LOCK_EX
 mv '.db', 'db'
 result = execute sql
 mv 'db', '.db'
 ret = flock 'db.lock', LOCK_UN
(or at least i tried to try it) but it seems that i can't rename the
database until the database is opened and it opens at the beggining of every
script and closes at the end so i can't rename the database in the middle of
the script because the database is opened and it doesn't let me.
o.k. - i'd still consider trying to do this somehow.
I added writing to file if the .lock file is locked but there is nothing in
the file so the .lock file is not locked and that means (at least i think
so) that the database shouldn't be locked by my script but by some other
process or am i wrong?
sounds right.
I tried not to commit a transaction but the database didn't lock... any
ideas?
I think it didn't lock because the PHP closes the database when the
script finishes and automatically commits.
again - i don't know about the specifics of PHP.  it sounds like it's doing
too much though...
Today i asked from the hosting the access log lines that include my database
file but there was only the ftp process acesssing the file i thought that
the PHP also must be listed there as process but it wasn't there?? How is
that possible? Why isn't it there?
what ftp process?  yours i assume.  if you are refering to 'lsof' and 
'fuser'
then these are would not appear - they use the /proc file system to show what
files are being used by a user but only while it's running.
Thank you I will wait more explanations on 2) but for now it seems to me
that it's 0) that is the most possible choice.
perhaps.  i think you should post your problem to the PHP lists - it sounds
like this could be the problem.
one last thing you might consider:  fcntl can tell you which pid is blocking
your lock (man fcntl).  you could check which process is blocking your lock if
you ever get a 'database locked' error.  having done that you could grab the
process information using
  lines = `ps -elf | grep $pid`
and log this info.  allow there is a small chance you'll miss the offending
process between the time the database is locked and when you try to find out
who has the lock using fcntl - this approach would tell you which (if any)
other process is grabbing your lock.
cheers.
-a
--
===
| EMAIL   :: Ara [dot] T [dot] Howard [at] noaa [dot] gov
| PHONE   :: 303.497.6469
| When you do something, you should burn yourself completely, like a good
| bonfire, leaving no trace of yourself.  --Shunryu Suzuki
===

Re: [sqlite] Locking database

[Alexander Jordanov]

> make every single sql execution follow this logic
> 
>die 'nested lock' if global_recursion_flag
> 
>global_recursion_flag = true
> 
>  ret = flock 'db.lock', LOCK_EX
>  die 'lock failed' unless ret
> 
>  result = execute sql
> 
>  ret = flock 'db.lock', LOCK_UN
>  die 'unlock failed' unless ret
> 
>global_recursion_flag = false
I can't really make that because in PHP i can't set a global variable
that  will be available for anyone that uses the script. If i set a
variable and change it in the script the next one that runs the script
will have the unchanged variable... I can't set a variable that will
be valid across users...
I did the closest i could to what you wrote and need i say? the
database got locked again :(
I checked all the scripts to be sure that every script uses the class
and there are no scripts that don't use the class... so 1) is excluded
can you explain more 2) i could't understand it right what do you mean
by :" fcntl based locks are process based so all thread 'get the lock'
" I don't know how exactly are working apache and PHP under Linux but
i suppose that the whole PHP is one process and every script is run as
a thread... so as a man opens the site a new thread is run for the
script he opened so yes the access is multythreaded as any web site.
I also tried that:
 database - seriously, if you coordinated ALL access through something like
> 
>  ret = flock 'db.lock', LOCK_EX
> 
>  mv '.db', 'db'
> 
>  result = execute sql
> 
>  mv 'db', '.db'
> 
>  ret = flock 'db.lock', LOCK_UN
> 
 (or at least i tried to try it) but it seems that i can't rename the
database until the database is opened and it opens at the beggining of
every script and closes at the end so i can't rename the database in
the middle of the script because the database is opened and it doesn't
let me.
I added writing to file if the .lock file is locked but there is
nothing in the file so the .lock file is not locked and that means (at
least i think so) that the database shouldn't be locked by my script
but by some other process or am i wrong?
> > I tried not to commit a transaction but the database didn't lock... any
> > ideas?
I think it didn't lock because the PHP closes the database when the
script finishes and automatically commits.

> this smells fishy.  obviously SOMETHING changed right? 
Yes i agree that something changed what i meant is that i didn't
change the script with the recursion that was working and is still
working all the time because it's one of the most visited

Today i asked from the hosting the access log lines that include my
database file but there was only the ftp process acesssing the file i
thought that the PHP also must be listed there as process but it
wasn't there?? How is that possible? Why isn't it there?
Thank you I will wait more explanations on 2) but for now it seems to
me that it's 0) that is the most possible choice.



On Mon, 29 Nov 2004 16:08:39 -0700 (MST), Ara.T.Howard
<[EMAIL PROTECTED]> wrote:
> On Tue, 30 Nov 2004, Alexander Jordanov wrote:
> 
> > I am 100% sure that my all my queries go trought that class.  Unfortunatelly
> > it is too late to make such changes ('forces the user to specify whether the
> > transaction is write or read') - i have too many scripts to change.
> 
> make every single sql execution follow this logic
> 
>die 'nested lock' if global_recursion_flag
> 
>global_recursion_flag = true
> 
>  ret = flock 'db.lock', LOCK_EX
>  die 'lock failed' unless ret
> 
>  result = execute sql
> 
>  ret = flock 'db.lock', LOCK_UN
>  die 'unlock failed' unless ret
> 
>global_recursion_flag = false
> 
> if all access is through this one class you CANNOT experience a locked
> database using this logic.  if you are then:
> 
>0) some other process is locking the database
> 
>1) there is some database access NOT though this class
> 
>2) your app is doing some weird thread things fcntl based locks are process
>based so all thread 'get the lock'
> 
> 
> > About NFS i asked the hosting and they said they dont use NFS.
> 
> good.
> 
> > And can the lack of threadsafe lead to such behaviour? (i suppose it can...)
> 
> i don't know - but seriously doubt it unless you're access is multithreaded.
> 
> is it?
> 
> > I must say also that the database seems to get locked during a select query
> > because i can read normally from the database but can't write in it.
> 
> then some other process holds the lock.
> 
> > i can't get the database to lock (exept when the script times out and i only
> > noticed that on a linux machine and i can't confirm that it also applies to
> > the server - probably will test that tommorow but even if it applies i have
> > put one hour timeout and my scripts are not that heavy i have 2 places if i
> > recall correctly where i use recursion and for the first i am 100% sure it
> > is ok and the second has been used for -4-5 months without problems and i
> > hav

[sqlite] This is a test...

[Ralph Wetzel]

... to check if the SQLite Mailinglist is down??
Disregard if not down ;-)
Greetings, Ralph

[sqlite] field=null vs. field isnull

[Ara.T.Howard]

i've read the 'null handling' section and still not found the answer to this
question:
  why should
'select * from tbl where field=null'
  be any different from
'select * from tbl where field isnull'
using 2.8.15 it seems to be:
  jib:~ > sqlite db 'create table tbl(field); insert into tbl values(null)'
  jib:~ > sqlite db 'select count(*) from tbl where field=null'
  0
  jib:~ > sqlite db 'select count(*) from tbl where field isnull'
  1
is this doccumented somewhere and i simply have missed (sorry if so).  if not,
why the difference?
regards.
-a
--
===
| EMAIL   :: Ara [dot] T [dot] Howard [at] noaa [dot] gov
| PHONE   :: 303.497.6469
| When you do something, you should burn yourself completely, like a good
| bonfire, leaving no trace of yourself.  --Shunryu Suzuki
===

[sqlite] Tools for sqlite ver 3

[Amir Hadar]

Hi
Are there any tools like sqliteplus that work with version 3?
 
Amir Hadar
Software Engineer
 
tel: +972.9.7476668
fax: +972.9.7476600
mobile: +972.52.8550201
email: [EMAIL PROTECTED]  
IXI Mobile Inc.
 

**
This email and any attachments thereto contain private, confidential,
and privileged material. Any use, review, copying, or disclosure of this
email (or any attachments thereto) by any party other than the intended
recipient is strictly prohibited. If you are not the intended recipient,
please contact the sender immediately and permanently delete the
original and any copies of this email and any attachments thereto.
 
 

[sqlite] Help compiling sqlite2

[Marco Bambini]

Hi,
I need to compile sqlite 2.8.15 under Linux and an other UNIX platform 
with THREAD_SAFE, NDEBUG and -DTEMP_STORE=3 enabled.
If I write something like:

./configure --enable-tempdb-in-ram=always
I can see in the makefile that -DTEMP_STORE is defined as 3.
But what about THREAD_SAFE and NDEBUG?
Have I to manually add them to the TCC line in the Makefile?
And in Linux, have I to manually add -lpthread to the Makefile?
Thanks a lot for your help.
Regards,
Marco Bambini

[sqlite] new sqlite user - need help

[ajit mote]

  
hello friend's ,
i want to know that hpw to convert tables from msexcel to sqlite...
i am new for sqlite and wanna shift from window's ,
hoping Ustand my problem..

thanking u.
 

[sqlite] Why does my query take so long

[Lloyd Thomas]

Hi, I am having a problem with the following query. It seems to force php to 
timeout after 30secs. The query goes through 150K records. Is there anything I 
can do to speed it up?

code-
SELECT call_id, C.extn_no AS extn_no, dest, dest_name, call_time, duration, 
cost, U.firstname AS firstname, U.surname AS surname
FROM call_data as C LEFT JOIN users as U on C.extn_no = U.extn_no 
WHERE C.stamptime >= $unixtimestart 
AND C.stamptime <= $unixtimeend 
AND direction = 'Out'
ORDER BY cost desc LIMIT 0,16
--

Lloydie-t

Re: [sqlite] Locking database

[Ara.T.Howard]

On Tue, 30 Nov 2004, Alexander Jordanov wrote:
I am 100% sure that my all my queries go trought that class.  Unfortunatelly
it is too late to make such changes ('forces the user to specify whether the
transaction is write or read') - i have too many scripts to change.
make every single sql execution follow this logic
  die 'nested lock' if global_recursion_flag
  global_recursion_flag = true
ret = flock 'db.lock', LOCK_EX
die 'lock failed' unless ret
result = execute sql
ret = flock 'db.lock', LOCK_UN
die 'unlock failed' unless ret
  global_recursion_flag = false
if all access is through this one class you CANNOT experience a locked
database using this logic.  if you are then:
  0) some other process is locking the database
  1) there is some database access NOT though this class
  2) your app is doing some weird thread things fcntl based locks are process
  based so all thread 'get the lock'

About NFS i asked the hosting and they said they dont use NFS.
good.
And can the lack of threadsafe lead to such behaviour? (i suppose it can...)
i don't know - but seriously doubt it unless you're access is multithreaded.
is it?
I must say also that the database seems to get locked during a select query
because i can read normally from the database but can't write in it.
then some other process holds the lock.
i can't get the database to lock (exept when the script times out and i only
noticed that on a linux machine and i can't confirm that it also applies to
the server - probably will test that tommorow but even if it applies i have
put one hour timeout and my scripts are not that heavy i have 2 places if i
recall correctly where i use recursion and for the first i am 100% sure it
is ok and the second has been used for -4-5 months without problems and i
haven't changed anything on it)
this smells fishy.  obviously SOMETHING changed right?  recursion and 
locking
sounds like a great thing NOT to do!  another thing you might try removing your
database - seriously, if you coordinated ALL access through something like
ret = flock 'db.lock', LOCK_EX
mv '.db', 'db'
result = execute sql
mv 'db', '.db'
ret = flock 'db.lock', LOCK_UN
i would expect that the offending code (the secret locker you cannot determine
with lsof or fuser) would begin to blow up.  does that make sense?  what i am
saying is that if database never exists unless the lock is held then code that
is not respecting the lockfile will fail because the database will not be there
(tiny race condition i realize but this would certainly make offending code
fail).
Can a query get the database locked? because i can't imagine a query that
can do that...
i don't think so.
And what can lock a database?
probably nothing but your own code ;-(
I tried not to commit a transaction but the database didn't lock... any
ideas?
something is very, very wrong then:
  harp:~ > rm db; sqlite db 'create table foo(bar)'
  harp:~ > strace sqlite db 'begin;end;' 2>&1 | grep -i lck
  fcntl64(3, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=0, len=0}, 
0xbfff7b30) = 0
  fcntl64(3, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, start=0, len=0}, 
0xbfff7b40) = 0
  fcntl64(4, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=0, len=0}, 
0xbfff7b30) = 0
  fcntl64(4, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, start=0, len=0}, 
0xbfff7b40) = 0
  fcntl64(3, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=0, len=0}, 
0xbfff76b0) = 0
  fcntl64(3, F_SETLK64, {type=F_WRLCK, whence=SEEK_SET, start=0, len=0}, 
0xbfff7710) = 0
  fcntl64(4, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=0, len=0}, 
0xbfff76b0) = 0
  fcntl64(4, F_SETLK64, {type=F_WRLCK, whence=SEEK_SET, start=0, len=0}, 
0xbfff7710) = 0
  fcntl64(3, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=0, len=0}, 
0xbfff76f0) = 0
  fcntl64(3, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, start=0, len=0}, 
0xbfff76c0) = 0
  fcntl64(4, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=0, len=0}, 
0xbfff76f0) = 0
  fcntl64(4, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, start=0, len=0}, 
0xbfff76c0) = 0
  harp:~ > strace sqlite db 'begin; insert into foo values(42); end;' 2>&1 | 
grep -i lck
  fcntl64(3, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=0, len=0}, 
0xbfff9ed0) = 0
  fcntl64(3, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, start=0, len=0}, 
0xbfff9ee0) = 0
  fcntl64(4, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=0, len=0}, 
0xbfff9ed0) = 0
  fcntl64(4, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, start=0, len=0}, 
0xbfff9ee0) = 0
  fcntl64(3, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=0, len=0}, 
0xbfff9a50) = 0
  fcntl64(3, F_SETLK64, {type=F_WRLCK, whence=SEEK_SET, start=0, len=0}, 
0xbfff9ab0) = 0
  fcntl64(4, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=0, len=0}, 
0xbfff9a50) = 0
  fcntl64(4, F_SETLK64, {type=F_WRLCK, whence=SEEK_SET, start=0, len=0}, 
0xbfff9ab0) = 0
  fcntl64(3, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=0, len=0}, 
0xbfff9a90) = 0
  fcntl64(3, F_SETLK64

Re: [sqlite] Locking database

[Alexander Jordanov]

I am 100% sure that my all my queries go trought that class.
Unfortunatelly it is too late to make such changes ('forces the user
to specify whether the transaction is write or read') - i have too
many scripts to change. About NFS i asked the hosting and they said
they dont use NFS. Do you or someone else know if the PHP extension
for sqlite in PHP 5.0.2 under linux is compiled as threadsafe? because
i read on the sqlite site that the sqlite binaries for linux (not the
extension) are not compiled as threadsafe and to be such they need to
be recompiled with threadsafe swicth and i downloaded the extension
code and saw in the config.m4 (i suppose it's the make file - i don't
know much about linux...) that "define threadsafe 1" is commented...
And can the lack of threadsafe lead to such behaviour? (i suppose it can...)
I have written to one of the authors of the extension and i am
currently waiting for an answer but it wouldn't hurt to know faster...
After all this i suppose you guessed that my problem is still there :(
and unfortunatelly it got worse now it locks not only during the night
but during the day too.
I must say also that the database seems to get locked during a select
query because i can read normally from the database but can't write in
it. Unfortunatelly i need to write because the site is also an eshop
and i need to write down the orders...
I suppose you read that i don't have access to lsof or fuser on the
hosting machine and i can't reproduce the problem locally because i
can't get the database to lock (exept when the script times out and i
only noticed that on a linux machine and i can't confirm that it also
applies to the server - probably will test that tommorow but even if
it applies i have put one hour timeout and my scripts are not that
heavy i have 2 places if i recall correctly where i use recursion and
for the first i am 100% sure it is ok and the second has been used for
-4-5 months without problems and i haven't changed anything on it)
Can a query get the database locked? because i can't imagine a query
that can do that...
And what can lock a database? I tried not to commit a transaction but
the database didn't lock... any ideas? it would be easier if i know
for what to look.
I think i said all.
Thank you I really appreciate your help please give me some more proposals.



On Mon, 29 Nov 2004 14:38:48 -0700 (MST), Ara.T.Howard
<[EMAIL PROTECTED]> wrote:
> On Wed, 24 Nov 2004, Alexander Jordanov wrote:
> 
> 
> 
> > @Ara.T.Howard:
> > Thanks for the suggestion i will try it and hope the database will not
> > lock again...
> > Here is what i did:
> >clearstatcache ();
> >   if ($sql[0]=='S'){
> >   $fp=fopen('koral.db.lock','a+');
> >   while (!flock($fp,LOCK_SH)){};
> >   } else {
> >   $fp=fopen('koral.db.lock','a+');
> >   while (!flock($fp,LOCK_EX)){};
> >   }
> >   $result = sqlite_query($this->handle, $sql);
> >   flock($fp,LOCK_UN);
> >   fclose($fp);
> > Please tell me if that is what you meant. My database file is
> > koral.db. This is a part of a class file i use to run the queries
> > $this->handle is the handle returned by
> > sqlite_open('koral.db',0666,$sqliteerror). koral.db.lock is empty i
> > don't write or read from it just check if it's locked or not and
> > according to that determine what i can do. $sql is the query that will
> > run and i check if it's first letter is S from SELECT and if it is i
> > do a shared lock if not Exclusive lock.
> > Thank you for the help.
> 
> yes - sounds resonable if 100% of your access is through this class.  my code
> is very similar.  my class forces the user to specify whether the transaction
> is write or read and issued the appropriate sql and aquires the corresponding
> lock (LOCK_SH, LOCK_EX).  in this way i don't need to check for 'S' etc. which
> is a bit fragile.  in any case you've got the idea.  btw. flock is not safe on
> nfs filesystems.
> 
> sorry i was out when you replied and hope your problem is fixed..
> 
> regards.
> 
> 
> 
> -a
> --
> ===
> | EMAIL   :: Ara [dot] T [dot] Howard [at] noaa [dot] gov
> | PHONE   :: 303.497.6469
> | When you do something, you should burn yourself completely, like a good
> | bonfire, leaving no trace of yourself.  --Shunryu Suzuki
> ===
>

Re: [sqlite] Locking database

[Ara.T.Howard]

On Wed, 24 Nov 2004, Alexander Jordanov wrote:
@Ara.T.Howard:
Thanks for the suggestion i will try it and hope the database will not
lock again...
Here is what i did:
   clearstatcache ();
if ($sql[0]=='S'){
$fp=fopen('koral.db.lock','a+');
while (!flock($fp,LOCK_SH)){};
} else {
$fp=fopen('koral.db.lock','a+');
while (!flock($fp,LOCK_EX)){};
}
$result = sqlite_query($this->handle, $sql);
flock($fp,LOCK_UN);
fclose($fp);
Please tell me if that is what you meant. My database file is
koral.db. This is a part of a class file i use to run the queries
$this->handle is the handle returned by
sqlite_open('koral.db',0666,$sqliteerror). koral.db.lock is empty i
don't write or read from it just check if it's locked or not and
according to that determine what i can do. $sql is the query that will
run and i check if it's first letter is S from SELECT and if it is i
do a shared lock if not Exclusive lock.
Thank you for the help.
yes - sounds resonable if 100% of your access is through this class.  my 
code
is very similar.  my class forces the user to specify whether the transaction
is write or read and issued the appropriate sql and aquires the corresponding
lock (LOCK_SH, LOCK_EX).  in this way i don't need to check for 'S' etc. which
is a bit fragile.  in any case you've got the idea.  btw. flock is not safe on
nfs filesystems.
sorry i was out when you replied and hope your problem is fixed..
regards.
-a
--
===
| EMAIL   :: Ara [dot] T [dot] Howard [at] noaa [dot] gov
| PHONE   :: 303.497.6469
| When you do something, you should burn yourself completely, like a good
| bonfire, leaving no trace of yourself.  --Shunryu Suzuki
===

[sqlite] Why does my query take so long

[Lloyd Thomas]

Hi, I am having a problem with the following query. It seems to force php to 
timeout after 30secs. The query goes through 150K records. Is there anything I 
can do to speed it up?

code-
SELECT call_id, C.extn_no AS extn_no, dest, dest_name, call_time, duration, 
cost, U.firstname AS firstname, U.surname AS surname
FROM call_data as C LEFT JOIN users as U on C.extn_no = U.extn_no 
WHERE C.stamptime >= $unixtimestart 
AND C.stamptime <= $unixtimeend 
AND direction = 'Out'
ORDER BY cost desc LIMIT 0,16
--

Lloydie-t

[sqlite] how to convert mysql database to sqlite?

[ajit mote]

  
hello friend's,
i am new user for linux , shifting from windows ...
using sqlite for my project...
i developed database in mysql and wanna it on sqlite..
please give me solution for this ..

Thanking u all. 
 
 ajit mote.

[sqlite] Legal to use sqlite docs in commercial applications?

[George Ionescu]

Hello Dr. Hipp,
Hello sqlite users,
I would like to know if it's legal to use sqlite documentation (sql syntax, 
sqlite logo etc.) into commercial applications' documentation (which are 
obviously built on top of sqlite).

Thanks.
Regards,
George Ionescu
_
Don't just search. Find. Check out the new MSN Search! 
http://search.msn.click-url.com/go/onm00200636ave/direct/01/

[sqlite] hi- how to convert text file to sqlite tables?

[ajit mote]

  
hello freind's,
   i am really thankful  to sqlite users ..
   now i got the information for crating and all basic things...  
   


   i want to know that HOW TO CONVERT TEXT FILE INTO SQLITE TABLES?

  I HAVE SMALL DATABASE BUT  THE ENTRIES ARE LARGE AND HECTIC TO ENTER MANUALLY 
SO I AM TRYING SCAN THE PAGES AND CONVERT THEM TO TEXT FILE.THOSE TEXT FILE 
AGAIN TO SQLITE TABLES. IS IT POSSIBLE AND IF POSSIBLE THEN PLEASE RELPY.
 IF ANY ALTERNATIVE IS THERE OTHER THAN THIS THEN PLEASE REPLY.

  THANKING U.
   

Re: [sqlite] Locking database

[Alexander Jordanov]

Unfortunately i don't have access to lsof or fuser on the hosting
machine i tried to make a cron job from cpanel but it returned that
they don't exist (or at least i dont't have access to them...) so i
can't use that...  and i don't have any other access to the server no
ssh or telnet or whatever just ftp...

> If you put in extra locking at the PHP level, then only your PHP will use
> it. If something else is locking the database, then it will not help
> unless it follows the same lock file protocol.
now about that i was more asking if i have done it right more than
will it work because i have never done locking... Unfortunately that
didn't work the database is still locking
And unfortunatelly the last two days it is loking more than ever it
locks 4-5 times a day aside from the night lockings... So i modified
the log in which i put the queries that are run to that form (i am
100% sure that every script uses that class for running queries so
every query must be inside the log):
before sqlite_query:
IP:xtime: xxxfilename: .php   
username:xxxsql:
and after sqlite_query:
if sqlite_query returned false: not done:  sql query.
else: done
So the result log is tipically :
sql.
done
naturally in a multithreaded environment that is a web site they may
mix like that:
sql...
sql...
done
done
but i noticed that this case is very rare so naturally the suspicion
laied on them
so i got them in a file and started testing them on a small script on
the site that basically lets you enter a query and run it in the
database and there were no problems every query from the "suspects"
was doing just fine doing what it is supposed to do and most
importanly not locking the database...

I got an idea please tell me is it possible this scenario:
When the site was first uploaded to the hosting there was only PHP5
beta version and the hosting didn't had a server with installed PHP5
so they installed it for us and i suppose we were using a separate
machine. At some point these problems began and i noticed that they
have installed PHP 5.0.2 (to be honest i don't remember exactly if i
have first noticed PHP 5.0.2 or first the problems began) so i suppose
they have installed PHP 5.0.2 on a major machine and now the scripts
are using this PHP throught NFS and i think i remember seeing
somewhere that sqlite had some locking problems with NFS (and if i
remember correctly it was not only sqlite...). So if the PHP is
running the scripts throught NFS will be there locking problems ?

Thank you. Hope someone will come up with some idea.

On Fri, 26 Nov 2004 11:52:16 + (GMT), Christian Smith
<[EMAIL PROTECTED]> wrote:
> On Wed, 24 Nov 2004, Alexander Jordanov wrote:
> 
> >@Christian Smith
> >> Please, reply to the list as well, as any information may be useful to
> >> others.
> >Sorry about that i'm new to the mailing lists...
> >I'll try lsof i just wonder what it will return? Is it going to tell
> >me that the file is locked by PHP or it will tel me also the script??
> >I suppose that it will be the first which is not very useful.
> 
> 
> Well, at least you'll have a suspect.
> 
> 
> >> You really need a test server to debug on!
> >As i said i have a test server but nevar managed to lock the database
> >on it (and that's the reason i've asked what could cause a lock if
> >only i knew where to look...) if i could i would've already found what
> >is causing the lock and probably fix it... or at least find a way to
> >avoid it.
> 
> 
> Does the test machine have the same configuration as the production
> machine? Ideally, it should be a mirror, especially if you're trying to
> reproduce a problem.
> 
> If not the same, then it could be some extra process on the production
> machine, which fuser or lsof will point the finger at.
> 
> 
> >Please anybody else i am currently trying the suggestion of
> >Ara.T.Howard i've answered to the mail list also but it seems he(she?)
> >is not available until Monday "i'm out skiing!  back the monday
> >following turkey day.". So if anybody knows if what i've done is
> >correct or not please tell me. Or if anyone has any other suggestions.
> >It's urgent. Thank you.
> 
> 
> If you put in extra locking at the PHP level, then only your PHP will use
> it. If something else is locking the database, then it will not help
> unless it follows the same lock file protocol.
> 
> 
> 
> 
> >
> >
> >
> >On Tue, 23 Nov 2004 16:28:12 + (GMT), Christian Smith
> ><[EMAIL PROTECTED]> wrote:
> >> Please, reply to the list as well, as any information may be useful to
> >> others.
> >>
> >> Inline...
> >>
> >>
> >>
> >> On Tue, 23 Nov 2004, Alexander Jordanov wrote:
> >>
> >> >First thank you for the fast reply
> >> >Well i already have such a log that logs all the queries that are run
> >> >by my php scripts and there was never a BEGIN TRANSACTION or COMMIT
> >> >but you might be right for the activity because Saturday and Sunday i
> >> >was constantly monitoring if the database 

[sqlite] SQLite Tutorial: Request For Comments

[sporkey]

The html has been cleaned up on the tutorial:
http://prdownloads.sourceforge.net/souptonuts/README_sqlite_tutorial.html?download

Regards,

Mike Chirico

Re: [sqlite] Problem with pkg_mkIndex and Tclsqlite3

[Denis Smirnov]

On Tue, Nov 23, 2004 at 07:16:06PM -0500, D. Richard Hipp wrote:
 DRH> >I am getting problem trying to create a packing index for tclsqlite3 
 DRH> We're working on getting the Makefile to do this automatically.
 DRH> Once that is done, we'll publish a tclkit for SQLite.

I create patch, that works for me.
When I test loading sqlite AND sqlite3 it works also.

-- 
wbr, Denis

http://www.sqlite.ru/

--- ./mkdll.sh.orig 2004-09-17 23:48:56 +0400
+++ ./mkdll.sh  2004-11-24 01:53:17 +0300
@@ -20,7 +20,7 @@
   $CMD
 done
 echo 'EXPORTS' >tclsqlite3.def
-echo 'Tclsqlite3_Init' >>tclsqlite3.def
+echo 'Tclsqlite_Init' >>tclsqlite3.def
 echo 'Sqlite3_Init' >>tclsqlite3.def
 i386-mingw32msvc-dllwrap \
  --def tclsqlite3.def -v --export-all \
--- ./src/tclsqlite.c.orig  2004-10-08 17:03:07 +0400
+++ ./src/tclsqlite.c   2004-11-24 01:51:39 +0300
@@ -1201,7 +1201,7 @@
   Tcl_PkgProvide(interp, "sqlite3", "3.0");
   return TCL_OK;
 }
-int Tclsqlite3_Init(Tcl_Interp *interp){
+int Tclsqlite_Init(Tcl_Interp *interp){
   Tcl_InitStubs(interp, "8.4", 0);
   Tcl_CreateObjCommand(interp, "sqlite3", (Tcl_ObjCmdProc*)DbMain, 0, 0);
   Tcl_PkgProvide(interp, "sqlite3", "3.0");
@@ -1210,7 +1210,7 @@
 int Sqlite3_SafeInit(Tcl_Interp *interp){
   return TCL_OK;
 }
-int Tclsqlite3_SafeInit(Tcl_Interp *interp){
+int Tclsqlite_SafeInit(Tcl_Interp *interp){
   return TCL_OK;
 }
 

Re: [sqlite] [ANN] SQLcrypt 1.0

[info]

Hi,
Thank you for your reply. Do you have a list of customers (at least a 
couple?) that use your encryption mechanism in a commercial software? Since 
it's quite new, I'd like to know about other users before I commit myself.

For any further discussion, I think it's best to go off this list, you can 
contact me at dennis (at) psunrise (dot) com

Regards,
  Dennis
// MCP, MCSD
// ASP Developer Member
// Software for animal shelters!
// www.smartpethealth.com
// www.amazingfiles.com
- Original Message - 
From: "Ng Pheng Siong" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, November 26, 2004 4:43 PM
Subject: Re: [sqlite] [ANN] SQLcrypt 1.0


On Mon, Nov 22, 2004 at 09:24:36AM +1100, Dennis Volodomanov wrote:
Is anyone using the mentioned library? Is it stable and fast?
(First one didn't get thru. Trying again...)
Hi,
I suggest you download and test the trial version, which works just like
the retail version, apart from (a) being limited to a passphrase of 
exactly
8 bytes, and (b) have the passphrase stored in the clear in the first 8
bytes of the database file.

Check out this script that does timing tests for SQLcrypt, SQLite v3, 
MySQL
and PostgreSQL. The numbers posted on the SQLcrypt website are based on my
puny test machine. I'd be interested to see your numbers.

 http://www.sqlcrypt.com/speedtest-sqlcrypt.tcl
Just comment out the MySQL or PostgreSQL tests if you don't have them. 
Also
see this page for discussions about the timing tests and the use of 
crypto.

 http://www.sqlcrypt.com/versus.html
Are there any other similar products for SQLite v3?
drh has a source-code add-in. His uses RC4; SQLcrypt uses AES.  His is
available for both SQLite v2 and v3; SQLcrypt is SQLite v3 only.
HTH. Cheers.
--
Ng Pheng Siong <[EMAIL PROTECTED]>
http://sandbox.rulemaker.net/ngps -+- M2Crypto, ZServerSSL for Zope, Blog
http://www.sqlcrypt.com -+- Database Engine with Transparent AES 
Encryption