Re: [sqlite] Concurrency, MVCC

[Mark D. Anderson]

Wednesday, April 14, 2004, 1:16:54 AM, Andrew Piskorski wrote:
> as far as I can tell, it seems to be describing a system with
> the usual Oracle/PostgreSQL MVCC semantics, EXCEPT of course that
> Currie proposes that each Write transaction must take a lock on the
> database as a whole.

Well, i suppose from a sufficient distance they look alike,
but in practice MVCC and shadow paging are rather different.

In MVCC, each row typically has two hidden fields identifying the first
and last transaction ids for which the row is relevant.
The last transaction id is to skip rows that are deleted.
There are many variants of MVCC, but you get the idea.

Any reader (or writer) knows its own transaction id, and just
ignores rows that are no applicable.

A "vacuum" process is necessary to periodically reclaim space
taken by rows whose last transaction id is lower than any live
transaction.

In shadow paging, the basic idea is that any reader or writer
gets a view onto the data based on reachability from "pointers"
in a particular root block. Pages that are reachable from any
live root block are never modified. A vacuum process is required 
to collect the space from blocks that are no longer reachable.
Updates to indexes must be treated in roughly the same way as
data pages, because they contain pointers to different data.

Shadow paging can be used for a table-based database, or
a persistent object store.
It certainly is much older than the HUT work; see for example Lorie 77,
"Physical Integrity in a Large Segmented Database."
It falls into the general class of logless transaction systems,
as opposed to the log-based approach that predominates in
current day non-academic database implementations.

-mda

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Concurrency Proposal

[Mark D. Anderson]

On Wed, 31 Mar 2004 12:15:36 +1000, [EMAIL PROTECTED] said:
> G'day,

> [snip of Ben's pseudo-code]

Just to check my understanding: the suggestion here is to reduce
reader-writer conflict windows by buffering of writes.
The writer acquires a read lock at the start of the transaction,
and upgrades to a write lock only when it comes time to commit all
pending IO.

If i understand the proposal, this improves read concurrency
at the cost of write throughput, because write IO is held back
and started later than it could be if it were commenced
as soon as the intent was known.

-mda

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] differences between 2.8.11 and 2.8.12

[Lloyd thomas]

Ok,
 I just tried the query using the sqlite command tool 2.8.12 and I
get no results, whereas if I use PHP5 with sqlite 2.8.11 I get the expected
results.
Can some one tell me why this is or what I am doing wrong?

- Original Message - 

I have a query which successfully runs on PHP5, which I beleive has sqlite
2.8.11 embedded, but run it in sqliteplus (windows GUI using 2.8.12) it does
not return any results. I have check with eZtools the provider of Sqliteplus
and they can not find fault with their program.
Is there any differences in these to versions which would stop the following
query returning results.

sql---
SELECT C.extn_no AS extn_no, dest, dest_name, call_time, duration, cost,
firstname, surname
FROM call_data as C INNER JOIN users as U on C.extn_no = U.extn_no WHERE '1'
= '1'
AND call_time >= '2004-12-12 00:00:00' AND call_time <= '2004-12-12
23:59:59'
AND direction = 'Out' AND U.user_id IN (SELECT user_id FROM grp_user WHERE
group_id = '1') LIMIT 0,1
-




-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] FreeBSD and SQLite

[Greg Miller]

Al Rider wrote:

Is there anyone successfully running SQLite on a FreeBSD machine?  If so,
would you email me and give me some help with it.
I've never used it on anything else. The instructions assume that make 
is actually GNU make, which is almost certainly not the case on a 
non-Linux machine. If you substitute "gmake" for "make" in the 
instructions, it should build fine as long as GNU make is installed.

Alternatively, you can build it from the databases/sqlite port by 
changing to the appropriate directory (/usr/ports/databases/sqlite) and 
typing "make install"

If you continue to have problems, I'd be glad to help. Need more 
details, though.
--
http://www.classic-games.com/ http://www.indie-games.com/

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] FreeBSD and SQLite

[Markus Hoenicka]

Al Rider writes:
 > 
 > I tried to compile and install SQLite without any success.
 > 

You might want to be a tad more specific at this point. What kind of
error messages did you get?

 > Is there anyone successfully running SQLite on a FreeBSD machine?  If so,
 > would you email me and give me some help with it.
 > 

I've been compiling SQLite on FreeBSD since 2.7.x without any
problems. I'm currently running 2.8.11 (due to some lazyness), but I
assume 2.8.13 would compile just as well. If the port/package (as
mentioned by Stephane) doesn't help, feel free to drop me a line to
sort this out.

regards,
Markus

-- 
Markus Hoenicka
[EMAIL PROTECTED]
(Spam-protected email: replace the quadrupeds with "mhoenicka")
http://www.mhoenicka.de



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] MySQL / SQLite

[D. Richard Hipp]

Puneet Kishor wrote:
To Richard --

So in my test, SQLite is a little faster.  Perhaps the difference
might be in a bad implementation of the SQLite bindings for Perl,
or perhaps the "mysql" command-line shell is less than optimal.


You perhaps meant "PHP" instead of "Perl" as that is what hannes is 
using. As far as I can see, DBD-SQLite is a most excellent product.


Clearly I do not know enough about PHP and Perl syntax
to tell the difference between the two.  :-)
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] MySQL / SQLite

[Puneet Kishor]

Hannes Roth wrote:

Hi.

I don't want to publish that table I used to make that benchmark. So I 
created some random data:
http://dl.magiccards.info/speedtest.tar.bz2

$db = sqlite_open("speedtest.sqlite");
$result = sqlite_query($db, "SELECT * FROM speedtest WHERE text5 LIKE 
'%a%'");

include("MySQL.php");
$erg = mysql_query("SELECT * FROM speedtest WHERE text5 LIKE '%a%'");
MySQL: 0.13727307319641
SQLite: 0.17734694480896
-hannes



I loaded the above data into MySQL 4.01 on my dual P4, 2.4 GHz, 1 Gb 
Ram, WinXP box appropriately called "Lazy," and ran the following script 
using Activestate Perl 5.8.1 (while iTunes was happily streaming KCRW 
simulcast).

#!perl.exe -w

use strict;
use Benchmark;
use DBI;
my $sql = "SELECT * FROM speedtest WHERE text5 LIKE '%a%'";

my $mh = DBI->connect('dbi:test:mysql', '', '');
my $msth = $mh->prepare(qq{$sql});
sub mysql {
  $msth->execute;
}
my $sh = DBI->connect('dbi:SQLite:speedtest.sqlite', '', '');
my $ssth = $sh->prepare(qq{$sql});
sub sqlite {
  $ssth->execute;
}
timethese 1, {
  Sqlite => \,
  MySQL => \,
};
__END__

I got the following result --

D:\user\pkishor\Desktop\speedtest>sqlite_v_mysql.pl
Benchmark: timing 1 iterations of MySQL, Sqlite...
 MySQL: 1409 wallclock secs (396.52 usr + 649.03 sys = 1045.55 CPU) 
@  9.56/
s (n=1)
Sqlite:  2 wallclock secs ( 1.22 usr +  0.80 sys =  2.02 CPU) @ 
4960.32/s (n
=1)

Then I changed the subroutines to actually fetch the data, so they were now

sub mysql {
  $msth->execute;
  my $mrow = $msth->fetchrow_arrayref;
}
sub sqlite {
  $ssth->execute;
  my $srow = $ssth->fetchrow_arrayref;
}
and ran the test 1000 times. I got --

D:\user\pkishor\Desktop\speedtest>sqlite_v_mysql.pl
Benchmark: timing 1000 iterations of MySQL, SQLite...
 MySQL: 141 wallclock secs (38.80 usr + 66.20 sys = 105.00 CPU) @ 
9.52/s (n
=1000)
SQLite:  0 wallclock secs ( 0.13 usr +  0.11 sys =  0.23 CPU) @ 
4273.50/s (n
=1000)
(warning: too few iterations for a reliable count)

Then I changed the SQL statement to actually fetch the count so it was 
now --

my $sql = "SELECT COUNT(*) AS foo FROM speedtest WHERE text5 LIKE '%a%'";

and surprisingly the situation reversed. SQLite was now slower --

D:\user\pkishor\Desktop\speedtest>sqlite_v_mysql.pl
Benchmark: timing 1000 iterations of MySQL, SQLite...
 MySQL: 25 wallclock secs ( 0.11 usr +  0.05 sys =  0.16 CPU) @ 
6451.61/s (n
=1000)
(warning: too few iterations for a reliable count)
SQLite: 39 wallclock secs (20.70 usr + 17.78 sys = 38.49 CPU) @ 
25.98/s (n=1
000)

SQLite also slowed dramatically when using fetchall_arrayref({}). As far 
as I can see, it is almost impossible to compare because results depend 
so much on what one is trying to do.

In response to hannes -- no, you don't have to change to PHP mailing 
list for SQLite questions, however, the PHP mailing list might be more 
appropriate for questions regarding PHP's SQLite implementation. This 
is, after all, the SQLite list, and most folks here seem to be C 
programmers even while they seem to happily assist with non-SQLite 
questions.

The lesson here is that db performances differ because of numerous 
reasons. If MySQL is faster than SQLite under the conditions in which 
you are working, and if speed is important for you, then you should 
stick with MySQL. If you like SQLite for what it gives (supreme 
simplicity with darn good speeds), then you should choose the best tools 
to make it work most optimally.

To Richard --

So in my test, SQLite is a little faster.  Perhaps the difference
might be in a bad implementation of the SQLite bindings for Perl,
or perhaps the "mysql" command-line shell is less than optimal.
You perhaps meant "PHP" instead of "Perl" as that is what hannes is 
using. As far as I can see, DBD-SQLite is a most excellent product.

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] MySQL / SQLite

[D. Richard Hipp]

rich coco wrote:
i am curious as to the discrepancies in 'sys' time between
sQLite and mySQL (9.12s .vs 1.96s as reported below).
SQLite lacks a persistent server, so it has to flush its
cache and reread every page of the database for each of
the 100 queries.  This takes time.  MySQL, on the other hand
gets to keep all those pages in memory.
If I change the query slightly so that it begins like this:

   PRAGMA cache_size=2;
   BEGIN;
That allows SQLite to hold all database pages in cache after
the initial read.  In that case, SQLite is much faster:
   [EMAIL PROTECTED] bld]$ time ./sqlite test.db /dev/null
   real0m13.403s
   user0m13.190s
   sys 0m0.080s
also, the 'usr + sys' time for SQLite more or less equals
the real time. but for mySQL, the 'real' time is over 5s
longer than the respective 'usr + sys' times (25% greater).
With SQLite, all processing happens in a single process so
you see it all with the "time" command.  With MySQL, you are
only seeing the time used by the client-side.  The server-side
processing is omitted from the "user" and "sys" times.  But
it is included in the real time, of course.

I took your data and loaded it into SQLite and MySQL databases.
Then I create a script file that contains 100 instances of your
query.  Here is what I get:
[EMAIL PROTECTED] bld]# time mysql drh /dev/null

real0m25.585s
user0m18.290s
sys 0m1.960s
[EMAIL PROTECTED] bld]# time ./sqlite test.db /dev/null
real0m22.993s
user0m13.870s
sys 0m9.120s


So in my test, SQLite is a little faster.  Perhaps the difference
might be in a bad implementation of the SQLite bindings for Perl,
or perhaps the "mysql" command-line shell is less than optimal.




--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: [sqlite] MySQL / SQLite

[basil . thomas]

I totally agree...
Seems like when users say SQLite is slower than "xyz...", they are using
a high level driver based interface instead of using a "c" based driver
program to
really test what SQLite is doing.
I have written tests at the "c" level for both MySQL and SQLite
and SQLite is generally much faster. The MySQL overhead is probably due to
tcp/ip communication...
Now where SQLite seems to slow down is when the database contains over a
gigabyte of data.
Hopefully SQLite ver 3.xx will fix that...


-Original Message-
From: Jimmy Lantz [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 14, 2004 1:39 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: [sqlite] MySQL / SQLite


At 19:32 2004-04-14, you wrote:
>>Yes, but are mysql_query and sqlite_query really doing the same thing? It 
>>is quite possible that mysql_query doesn't actually perform the complete 
>>query, while sqlite_query does. I think a better test would be to do the 
>>query and then step through all of the results, doing something with each 
>>row. What is the time difference for that?
>
>Ok:
>
>$array = array();
>include("../cardsearch/setMySQL.php");
>$erg = mysql_query("SELECT * FROM speedtest WHERE text5 LIKE '%a%'");
>while($row=mysql_fetch_row($erg)) $array[] = $row;
>
>$array = array();
>$db = sqlite_open("speedtest.sqlite");
>$result = sqlite_query($db, "SELECT * FROM speedtest WHERE text5 LIKE
'%a%'");
>while($row=sqlite_fetch_array($result)) $array[] = $row;
>
>MySQL: 0.24748015403748
>SQLite: 0.68342804908752
>
>$array = array();
>$db = sqlite_open("speedtest.sqlite");
>$result = sqlite_unbuffered_query($db, "SELECT * FROM speedtest WHERE 
>text5 LIKE '%a%'");
>while($row=sqlite_fetch_array($result)) $array[] = $row;
>
>MySQL: 0.23681807518005
>SQLite: 0.64980888366699
>
>I ran it several times, of course.
I think there might be some work needed on the PHP implementation of SQLite 
(marked as experimental)
Whereas the MySQL implementation has had years to mature and improve.
Do your tests using the CLI instead and see if you get the same.
/ Jimmy

>-hannes
>
>
>
>
>-
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



This e-mail may be privileged and/or confidential, and the sender does not
waive any related rights and obligations. Any distribution, use or copying of
this e-mail or the information it contains by other than an intended recipient
is unauthorized. If you received this e-mail in error, please advise me (by
return e-mail or otherwise) immediately. 

Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce
pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation
ou copie de ce message ou des renseignements qu'il contient par une personne
autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez
ce courrier électronique par erreur, veuillez m'en aviser immédiatement, par
retour de courrier électronique ou par un autre moyen.

Re: [sqlite] MySQL / SQLite

[Jimmy Lantz]

At 19:32 2004-04-14, you wrote:
Yes, but are mysql_query and sqlite_query really doing the same thing? It 
is quite possible that mysql_query doesn't actually perform the complete 
query, while sqlite_query does. I think a better test would be to do the 
query and then step through all of the results, doing something with each 
row. What is the time difference for that?
Ok:

$array = array();
include("../cardsearch/setMySQL.php");
$erg = mysql_query("SELECT * FROM speedtest WHERE text5 LIKE '%a%'");
while($row=mysql_fetch_row($erg)) $array[] = $row;
$array = array();
$db = sqlite_open("speedtest.sqlite");
$result = sqlite_query($db, "SELECT * FROM speedtest WHERE text5 LIKE '%a%'");
while($row=sqlite_fetch_array($result)) $array[] = $row;
MySQL: 0.24748015403748
SQLite: 0.68342804908752
$array = array();
$db = sqlite_open("speedtest.sqlite");
$result = sqlite_unbuffered_query($db, "SELECT * FROM speedtest WHERE 
text5 LIKE '%a%'");
while($row=sqlite_fetch_array($result)) $array[] = $row;

MySQL: 0.23681807518005
SQLite: 0.64980888366699
I ran it several times, of course.
I think there might be some work needed on the PHP implementation of SQLite 
(marked as experimental)
Whereas the MySQL implementation has had years to mature and improve.
Do your tests using the CLI instead and see if you get the same.
/ Jimmy

-hannes



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] MySQL / SQLite

[D. Richard Hipp]

Hannes Roth wrote:
I don't want to publish that table I used to make that benchmark. So I 
created some random data:
http://dl.magiccards.info/speedtest.tar.bz2

$db = sqlite_open("speedtest.sqlite");
$result = sqlite_query($db, "SELECT * FROM speedtest WHERE text5 LIKE 
'%a%'");

include("MySQL.php");
$erg = mysql_query("SELECT * FROM speedtest WHERE text5 LIKE '%a%'");
MySQL: 0.13727307319641
SQLite: 0.17734694480896
I took your data and loaded it into SQLite and MySQL databases.
Then I create a script file that contains 100 instances of your
query.  Here is what I get:
[EMAIL PROTECTED] bld]# time mysql drh /dev/null

real0m25.585s
user0m18.290s
sys 0m1.960s
[EMAIL PROTECTED] bld]# time ./sqlite test.db /dev/null
real0m22.993s
user0m13.870s
sys 0m9.120s
So in my test, SQLite is a little faster.  Perhaps the difference
might be in a bad implementation of the SQLite bindings for Perl,
or perhaps the "mysql" command-line shell is less than optimal.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] MySQL / SQLite

[Will Leshner]

Hannes Roth wrote:

$db = sqlite_open("speedtest.sqlite");
$result = sqlite_query($db, "SELECT * FROM speedtest WHERE text5 LIKE 
'%a%'");

include("MySQL.php");
$erg = mysql_query("SELECT * FROM speedtest WHERE text5 LIKE '%a%'");
MySQL: 0.13727307319641
SQLite: 0.17734694480896
Yes, but are mysql_query and sqlite_query really doing the same thing? It is quite possible that mysql_query doesn't actually perform the complete query, while sqlite_query does. I think a better test would be to do the query and then step through all of the results, doing something with each row. What is the time difference for that?

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] FreeBSD and SQLite

[Al Rider]

I do website design for clubs, etc., with a lot of custom, CM php scripts.
SQLite is ideally suited for many of my scripts; but, unfortunately one of
the sites is hosted on a FreeBSD based server.  Most of my designs are
Linux; but, I want to keep the designs portable.

I tried to compile and install SQLite without any success.

I posted a ticket about the problem and you relied FreeBSD was not a
supported platform.

Because of the obvious advantages for my applications, I'd really like to
start using SQLite.

Plus, I'm looking forward to php 5.  If SQLite is not supported on FreeBSD
machines does that mean it won't be compiled into php 5 on them?

Is there anyone successfully running SQLite on a FreeBSD machine?  If so,
would you email me and give me some help with it.

Thanks...



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Adding SQL commands

[D. Richard Hipp]

[EMAIL PROTECTED] wrote:
Great...can you tell me if there will be any increased technical specs???
Ver 3.0 will have a 64-bit ROWID, what about:
- page size
512 to 64K selectable at compile-time.  Default: 1K

	- max table rows
2^64

	- max database size
2^32 pages.

The current implementation does not do well with
databases over a few dozen GB.  But a backwards-
compatible fix for that is possible with future
releases.
	- max blob size
Adjustable at compile-time up to 2^64 bytes.
Performance for large BLOBs is not good and it is
recommended that large BLOBs be stored in separate
files.  The default compile-time limit will be 1MB.
	- any other tech limitations info...



--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: [sqlite] Adding SQL commands

[basil . thomas]

Great...can you tell me if there will be any increased technical specs???
Ver 3.0 will have a 64-bit ROWID, what about:
- page size
- max table rows
- max database size
- max blob size
- any other tech limitations info...


-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 14, 2004 11:52 AM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Adding SQL commands


[EMAIL PROTECTED] wrote:
 > [I]f version 3.0 is a foundation move to implement enhanced language
 > functionality later,...

That is a correct assessment.  SQLite version 3.0.0 is an enhancement
to the foundation.  SQL Langauge enhancements can be made later and
in a backwards-compatible way.  The only thing that really has to go
into 3.0.0 is incompatible file format or API changes that will
facilitate future enhancments.

-- 
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



This e-mail may be privileged and/or confidential, and the sender does not
waive any related rights and obligations. Any distribution, use or copying of
this e-mail or the information it contains by other than an intended recipient
is unauthorized. If you received this e-mail in error, please advise me (by
return e-mail or otherwise) immediately. 

Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce
pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation
ou copie de ce message ou des renseignements qu'il contient par une personne
autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez
ce courrier électronique par erreur, veuillez m'en aviser immédiatement, par
retour de courrier électronique ou par un autre moyen.

Re: [sqlite] MySQL / SQLite

[D. Richard Hipp]

Hannes Roth wrote:
Ok:
-18 fields, all strings or numbers.
-No indices.
-The query I used is "SELECT * FROM table WHERE field1 LIKE '%foo%'.
This is similar to Test-5 at http://www.sqlite.org/speed.html
In Test-5, SQLite is 30% faster than MySQL.  I do not know
what the difference might be from what you are seeing.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Adding SQL commands

[D. Richard Hipp]

[EMAIL PROTECTED] wrote:
> [I]f version 3.0 is a foundation move to implement enhanced language
> functionality later,...
That is a correct assessment.  SQLite version 3.0.0 is an enhancement
to the foundation.  SQL Langauge enhancements can be made later and
in a backwards-compatible way.  The only thing that really has to go
into 3.0.0 is incompatible file format or API changes that will
facilitate future enhancments.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Concurrency, MVCC

[Doug Currie]

> D. Richard Hipp wrote:
>> 
>> My thoughts on BlueSky have been added to the wiki page:
>>http://www.sqlite.org/cvstrac/wiki?p=BlueSky

I added some responses; I do not agree with Richard's concerns about
Shadow Paging, and I corrected some mistaken conclusions. I apologize
if my paper was not clear enough in these areas.

Thank you, Richard, for taking the time to review the Shadow Paging
option.

Regards,

e


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: [sqlite] Adding SQL commands

[basil . thomas]

Yes...It would be great if SQLite had control-flow statements and variables
just like
Transact-SQL(MS/Sybase) as it would allow one to put all the data
manipulation into
one script and run it like a stored procedure...
I do like the fine control that SQLite gives my application but I also think
declarative programming is far more productive for most of my data
manipulation tasks.
Are there any pans to add any more SQL commands or at least implement the
current SQL 
commands more fully such as CHECK, auto increment/identy, named parameters,
etc..???
I do use as many of the sqlite_... "c" function in my .net managed code as
needed but
I am increasingly using more SQL to rapidly implement solutions by calling
it as a script.
Any custom function I implement in "c" but over 95% of my data manipulation
problems
are solved easily in SQL.
SQLite Version 3.0 does not seem to implement any enhancements to the SQL
language
and I think that needs to be seriously looked at. But if version 3.0 is a
foundation move
to implement enhanced language functionality later, then this would be a
good step forward...
 

-Original Message-
From: Greg Obleshchuk [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 13, 2004 7:20 PM
To: Thomas, Basil; [EMAIL PROTECTED]
Subject: Re: [sqlite] Adding SQL commands


Hi Basil,
The first use of IF is same syntax as the case statement so I don't think it
is required and as IF isn't SQL92 I doubt it will be included
 
so your command is replaced by
 
case when (select count(*) from foo) = 100 then 'good' else 'bad' end 
 
I like the idea of exist but then again you can do this (in a query) count 
where (Select count(*) from foo where col1 ='a') = 1
 
but what I really think you are talking about is a command language like
TSQL for Microsoft or P/SQL for Oracle (is it P/SQL what ever?)  At the
moment you can't do that type of thing is SQLite.  I think the reason here
is the it is a Database system that is included in your application and
therefore you application can make these decisions far better that a SQL
language
 
Greg O
Don't for get www.SQL-Scripts.Com  
 

- Original Message - 
From: [EMAIL PROTECTED]   
To: [EMAIL PROTECTED]   
Sent: Wednesday, April 14, 2004 8:03 AM
Subject: [sqlite] Adding SQL commands

I would like to add some commands to SQLite to make my SQL(ite) programming
life easier!!!
The commands are:
IF - e.g IF ((select count(*) from foo) = 100)
select "good";
   ELSE
select "bad";
   END

EXISTS - e.g IF EXISTS(select * from sqlite_master where name =
'foo')
DROP TABLE foo;
END

local variables - e.g. DECLARE @var TEXT;
 select @var = name from foo;

I am not familiar with how compilers work but would be willing to write the
"c" code
to make these commands work.

Does anyone have a simple way to add a command using the lemon parser??? I
find the documentation
hard to understand and I would like to add these command s using the c++
compiler from Visual Studio.Net

B.Thomas



This e-mail may
be
privileged and/or confidential, and the sender does not waive any related
rights and obligations. Any distribution, use or copying of this e-mail or
the
information it contains by other than an intended recipient is unauthorized.
If you received this e-mail in error, please advise me (by return e-mail or
otherwise) immediately. 

Ce courrier électronique est confidentiel et protégé. L'expéditeur ne
renonce
pas aux droits et obligations qui s'y rapportent. Toute diffusion,
utilisation
ou copie de ce message ou des renseignements qu'il contient par une personne
autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez
ce courrier électronique par erreur, veuillez m'en aviser immédiatement, par
retour de courrier électronique ou par un autre moyen.




This e-mail may be 
privileged and/or confidential, and the sender does not waive any related rights and 
obligations. Any distribution, use or copying of this e-mail or the information it 
contains by other than an intended recipient is unauthorized. If you received this 
e-mail in error, please advise me (by return e-mail or otherwise) immediately. 

Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce pas aux 
droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce 
message ou des renseignements qu'il contient par une personne autre que le (les) 
destinataire(s) désigné(s) est interdite. Si vous recevez ce courrier électronique par 
erreur, veuillez m'en aviser immédiatement, par retour de courrier électronique ou par 
un autre moyen.

RE: [sqlite] Effectiveness of PRAGMA integrity_check;

[Liz Steel]

Hello!

I am trying to do a similar sort of thing with my database. The only way 
I've found to fairly reliably create a corrupt database file is to pull the 
battery out of my laptop whilst my application is accessing the database. I 
haven't used the "PRAGMA integrity_check;" command, but I will try it now 
and see if that detects my corrupt database. At the moment, I am selecting 
every row from every table, but I can see this getting a bit slow when my 
database gets bigger.

I've just tried it, and I get a code 11 (SQLITE_CORRUPT) returned from the 
PRAGMA integrity_check command. Is this correct behaviour? Will I always get 
this error returned? The website says something about it returning "ok" but 
I didn't check this as I am using sqlite_exec.

I haven't managed to find anything on the website about using the journal 
file that is created. Can anyone point me in the right direction to using 
this file to re-create my database? Would I need to take a backup of the 
database file before every transaction to use this?

Thanks,

Liz.

Original Message Follows
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: [sqlite] Effectiveness of PRAGMA integrity_check;
Date: Wed, 14 Apr 2004 15:48:57 +1000
G'day,

I'm trying to write some defensive code that is able to recover from
database corruption. The idea is that if a disk fails and a database
becomes corrupt it can be detected and synchronised from a backup copy.
To this end, I've just been trying to write a function that returns true
only when it is sure the database is ok. I use PRAGMA integrity check; and
compare the returned string with "ok". When I tried this with a few random
database changes, though, I had a hard time trying to get the corruption
to trigger. I did the following:
CREATE TABLE foo(bar);
INSERT INTO foo VALUES("bar");
I then went in with a text editor and started modifying the bar record. I
changed "bar" to "car", but the change was not detected. I started
modifying characters to the left and right of the "car" string, but still
no corruption. I was able to get corruption to be detected when I
truncated the file.
Can I take it from this behaviour that there isn't any checksum checking
going on apart from headers and the BTrees themselves? Will the
integrity_check at least guarantee me that I won't at some later stage get
an SQLITE_CORRUPT return?
Benjamin.

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
_
Tired of 56k? Get a FREE BT Broadband connection 
http://www.msn.co.uk/specials/btbroadband

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Concurrency, MVCC

[D. Richard Hipp]

D. Richard Hipp wrote:
My thoughts on BlueSky have been added to the wiki page:

  http://www.sqlite.org/wiki?p=BlueSky

That URL should have been:

   http://www.sqlite.org/cvstrac/wiki?p=BlueSky

Left out the "cvstrac".  Sorry for the confusion.

--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Effectiveness of PRAGMA integrity_check;

[D. Richard Hipp]

[EMAIL PROTECTED] wrote:
I'm trying to write some defensive code that is able to recover from 
database corruption. The idea is that if a disk fails and a database 
becomes corrupt it can be detected and synchronised from a backup copy.

To this end, I've just been trying to write a function that returns true 
only when it is sure the database is ok. I use PRAGMA integrity check; and 
compare the returned string with "ok". When I tried this with a few random 
database changes, though, I had a hard time trying to get the corruption 
to trigger. I did the following:

CREATE TABLE foo(bar);
INSERT INTO foo VALUES("bar");
I then went in with a text editor and started modifying the bar record. I 
changed "bar" to "car", but the change was not detected. I started 
modifying characters to the left and right of the "car" string, but still 
no corruption. I was able to get corruption to be detected when I 
truncated the file.

Can I take it from this behaviour that there isn't any checksum checking 
going on apart from headers and the BTrees themselves? Will the 
integrity_check at least guarantee me that I won't at some later stage get 
an SQLITE_CORRUPT return?

PRAGMA integrity_check does a good job of testing the
integrity of the BTree layer in the file.  (See
http://www.sqlite.org/fileformat.html for a definition
of the "btree layer".)  The pragma also checks to make
sure that all indices are correct.  But other than that,
changes to the database can easily go undetected.
Note that if you had had an index on the foo table,
your change of "bar" to "car" would have been detected
by the index checks.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Concurrency, MVCC

[D. Richard Hipp]

Andrew Piskorski wrote:
How feasible would it be to add support for higher concurrency to
SQLite, especially via MVCC?  
My thoughts on BlueSky have been added to the wiki page:

  http://www.sqlite.org/wiki?p=BlueSky

The current plan for version 3.0 is as follows:

  * Support for a modification of the Carlyle method for
allowing writes to begin while reads are still pending.
All reads must finish before the write commits, however.
  * Support for atomic commits of multi-database transactions,
which gives you a limited kind of table-level locking,
assuming you are willing to put each table in a separate
database.
Business constraints require that version 3.0 be working
no later than May 31.  So if you have any alternative
suggestions, you should get them in quickly.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: [sqlite] MySQL / SQLite

[KL Chin]

Dear D. Richard Hipp,

I had some strange result on some PCs, 

a) On my notebook (P3 899, 256Mbytes, WinXP) when do insert a 
records to a blank table, it > 100% slower as compare to other.  
(< 3000 rows).


b) On one of PC (Celeron 1.7G, 256MBytes WinXP), when do search. 
it will 100% slower as compare to others, (> 8000 rows)
Infact it slower then a Celeron 633 with 128M Win98.

for indx := 0 to QstLst.Count - 1 do
  begin
qryQst.SQL = 'Select * from QstTbl As Q, LstTbl As L where L.QstID=''' +
QstLst.Strings[indx] + ''' AND Q.QstID=L.QstID';
qryQst.Open( );
...
...
  end;


Do u have any complete compiled application to check the perfomance.

Regards
KL Chin

-Original Message-
From:   D. Richard Hipp [SMTP:[EMAIL PROTECTED]
Sent:   Wednesday, April 14, 2004 7:15 PM
Cc: [EMAIL PROTECTED]
Subject:Re: [sqlite] MySQL / SQLite

Hannes Roth wrote:

> 2. Why is SQLite twice as fast when using a small database (<3000 rows) 
> and twice as slow when using a large database (>8000 rows)? 
> 

The speed comparison at http://www.sqlite.org/speed.html uses tables
with 25000 rows and is twice as fast as MySQL.  I don't know why yours
is slower - you did not give much data to go on.  Perhaps if you
supplied some information about your database (the schema, what
indices are defined, what data is being stored, what your queries
look like) someone could better answer your question.

-- 
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] MySQL / SQLite

[D. Richard Hipp]

Hannes Roth wrote:

2. Why is SQLite twice as fast when using a small database (<3000 rows) 
and twice as slow when using a large database (>8000 rows)? 

The speed comparison at http://www.sqlite.org/speed.html uses tables
with 25000 rows and is twice as fast as MySQL.  I don't know why yours
is slower - you did not give much data to go on.  Perhaps if you
supplied some information about your database (the schema, what
indices are defined, what data is being stored, what your queries
look like) someone could better answer your question.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] MySQL / SQLite

[Elmar Haneke]

Hannes Roth schrieb:

1. Why is the SQLite database much much bigger than the MySQL table and 
the raw csv file? I didn't use any index and vacuumed all the time ;)
SQLite does store everything as string, therefore the DB cannot be 
significantly small than the CVS-file. MySQL does store integers as 
binary values, this does require less space.

2. Why is SQLite twice as fast when using a small database (<3000 rows) 
and twice as slow when using a large database (>8000 rows)? Is this a 
harddrive issue?
I would assume that SQLite does have less overhead on each query. 
SQLite runs directly embedded into application process, MySQL is an 
Server - there is additional cost for TCP/IP communication.

Such things might easyly result in beeing faster on small jobs and 
slower on large ones - the communication overhead is mostly 
independent of the number of records in the table (as long as this 
does not enlagre the result-set)

Elmar

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] Ticket 575

[Paul Smith]

Is there any progress on this ticket (temporary file storage method 
problem)? If not, can anyone suggest any workarounds?

I've just discovered here that it looks like we're getting really bad 
performance hits on a Windows machine with temporary files when people use 
certain virus scanners. Also, in that case, there seems to be a big 
difference when using temporary files depending on whether writes are done 
in a transaction or not (which is not supposed to be the case)

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]