Re: [sqlite] weird (and dangerous) bug in Microsoft Compiler

[mike cariotoglou]

did you also compile the ado .net wrapper ?

- Original Message - 
From: "Robert Simpson" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Wednesday, November 08, 2006 4:57 PM
Subject: RE: [sqlite] weird (and dangerous) bug in Microsoft Compiler



I tried that same SELECT statement in 3 builds of SQLite on VS2005.  The
code was called from C# using the ADO.NET 2.0 wrapper, and I got 10 all 3
times.  I tried /fp:fast, /fp:strict and /fp:precise

The rest of the build options were:

   AdditionalOptions="/GS-"
   Optimization="2"
   FavorSizeOrSpeed="1"

PreprocessorDefinitions="WIN32;NDEBUG;_WINDOWS;_USRDLL;_CRT_SECURE_NO_DEPREC
ATE;NO_TCL;THREADSAFE;SQLITE_HAS_CODEC;SQLITE_ENABLE_COLUMN_METADATA;SQLITE_
ENABLE_FTS1"
   StringPooling="true"
   ExceptionHandling="0"
   BufferSecurityCheck="false"
   EnableFunctionLevelLinking="true"
   RuntimeTypeInfo="false"



-Original Message-
From: mike cariotoglou [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 08, 2006 5:27 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] weird (and dangerous) bug in Microsoft Compiler

hello to all. I wish to report some quirks I discovered with
floating point
and
ROUND() function, while looking into a problem reported by my
development
team wrt
to sqlite handling of above function.

first of all, let me state that I understand the issues with inexact
floating point
representations, so let us not go into discussions about which is the
"correct"
interpretation of 9.95. however, a given implementation
should at least be
consistent
wrt to this.

my tests have shown that, for the following statement:

 select ROUND(9.95,1)

the command-line sqlite3.exe (v 3.3.8) returns 10.0
OTOH, the compiled DLL that can be downloaded from the sqlite
site returns
9.9 !
(as a reference, both MS SQL and ORACLE return 10.0)

What gives ? is the result dependent on compilation options,
and if so,
which ?

trying to investigate this issue, I compiled the dll locally
(3.3.8), using
Microsoft
Visual Studio 2005, and came across a beauty :

the dll compiled with MSVC, using default options more or
less, gives 0.0
(yes, zero)

I pulled my hair out over this for some hours, and discovered that :

a. the floating point optimizer in MSVC has a bug, which is
triggered when
you use the
optimization setting /fp:precice (which is the default), and
gives the above
erroneous
result.

b. you can get the correct behavior by speifying optimization
as /fp:strict

Clearly, this is a problem with the microsoft compiler.
however, trying to
avoid future
issues, I suggest that somebody which is conversant in C
(which is not me),
try to find
the sqlite3 source construct that triggers this bug, and
re-writes the code
so that it
is not optimization-sensitive. I tracked the problem down
somewhere in the
vxprintf function
in the print.c source file. It is quite difficult to pin the
problem down,
because:

the problem goes away when you build in debug mode, because
optimizations
are turned off.
Even if you force some optimizations by hand, in debug mode,
the compiler
will start
using floating-point stack and registers for variables. the debugger,
however,does not seem
to understand about these optimizations, so, inspecting the
variables gives
you the wrong
results. (how people manage to work in this environment is
beyond me, my
good ole delphi
never does thigs like this!)

Could someone please verify my findings, especially the one about the
correct setting for
MSVC to compile the dll ? and, assuming the above is
verified, I think we
should add
a warning in the WIKI for poor souls who will try to compile
using MS Visual
Studio 2005
in the future !

PS
when testing, pls note that the value 9.95 is a "magic"
value, due to the
way the
code is written in print.c. AFAIK it is the only value
manifesting the
compiler bug,
probably due to the way that the imput to ROUND() is compared
to the value
"10.0" in
various points in the code.



--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---






-
To unsubscribe, send email to [EMAIL PROTECTED]
-







-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] weird (and dangerous) bug in Microsoft Compiler

[mike cariotoglou]

hello to all. I wish to report some quirks I discovered with floating point 
and
ROUND() function, while looking into a problem reported by my development 
team wrt

to sqlite handling of above function.

first of all, let me state that I understand the issues with inexact 
floating point
representations, so let us not go into discussions about which is the 
"correct"
interpretation of 9.95. however, a given implementation should at least be 
consistent

wrt to this.

my tests have shown that, for the following statement:

select ROUND(9.95,1)

the command-line sqlite3.exe (v 3.3.8) returns 10.0
OTOH, the compiled DLL that can be downloaded from the sqlite site returns
9.9 !
(as a reference, both MS SQL and ORACLE return 10.0)

What gives ? is the result dependent on compilation options, and if so, 
which ?


trying to investigate this issue, I compiled the dll locally (3.3.8), using 
Microsoft

Visual Studio 2005, and came across a beauty :

the dll compiled with MSVC, using default options more or less, gives 0.0 
(yes, zero)


I pulled my hair out over this for some hours, and discovered that :

a. the floating point optimizer in MSVC has a bug, which is triggered when 
you use the
optimization setting /fp:precice (which is the default), and gives the above 
erroneous

result.

b. you can get the correct behavior by speifying optimization as /fp:strict

Clearly, this is a problem with the microsoft compiler. however, trying to 
avoid future
issues, I suggest that somebody which is conversant in C (which is not me), 
try to find
the sqlite3 source construct that triggers this bug, and re-writes the code 
so that it
is not optimization-sensitive. I tracked the problem down somewhere in the 
vxprintf function
in the print.c source file. It is quite difficult to pin the problem down, 
because:


the problem goes away when you build in debug mode, because optimizations 
are turned off.
Even if you force some optimizations by hand, in debug mode, the compiler 
will start
using floating-point stack and registers for variables. the debugger, 
however,does not seem
to understand about these optimizations, so, inspecting the variables gives 
you the wrong
results. (how people manage to work in this environment is beyond me, my 
good ole delphi

never does thigs like this!)

Could someone please verify my findings, especially the one about the 
correct setting for
MSVC to compile the dll ? and, assuming the above is verified, I think we 
should add
a warning in the WIKI for poor souls who will try to compile using MS Visual 
Studio 2005

in the future !

PS
when testing, pls note that the value 9.95 is a "magic" value, due to the 
way the
code is written in print.c. AFAIK it is the only value manifesting the 
compiler bug,
probably due to the way that the imput to ROUND() is compared to the value 
"10.0" in

various points in the code.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite3 Explorer dates

[mike cariotoglou]

well, yes, it IS a limitation, which I will fix in the next release. the
problem is,
EXP registers its own version of a "date" function, as described in the
readme (I think I did describe it :), which is completely different than the
standard sqlite3 date function. I will make this optional in the next
release.

- Original Message -
From: "Clark Christensen" <[EMAIL PROTECTED]>
To: "SQLite List" 
Sent: Friday, August 19, 2005 8:09 PM
Subject: [sqlite] SQLite3 Explorer dates


> Mike,
>
> If appropriate for a future rev of SQLite3 Explorer (EXP),
> I'm requesting an update to the handling of date strings.
>
> Using SQLite3 Explorer (EXP) v1.6 under WinXP, "select
> date('2005-08-19')" gives a "not a valid FP number" error.
>
> Using the SQLite3 shell, I get '2005-08-19'.
>
> Maybe I'm mistaken, but shouldn't EXP return the result of
> the valid SQLite3 function (a string, in this case)?  It's
> the same if I ask for a date string from a field value.
> "select date(datefield) from t1" (where datefield is a
> julian number), gives a number in EXP, and the expected
> date string in the SQLite3 shell.
>
> For what it's worth, strftime() seems to return the
> expected date string.  Which is fine, except I'm using EXP
> to test my queries during development.  I don't like having
> to change to strftime() where the app can work with date()
> or datetime()  :-))
>
> Is this a limitation in EXP, or am I missing something?
>
> Thanks!
>
>  -Clark
>
>
>

Re: [sqlite] ORDER BY Does not work

[mike cariotoglou]

yes, I am sure. I tested with 3.1.3, with the same results.

> 
> I'll eventually get to this.  If Dan's fix is correct, though,
> the problem was introduced after 3.2.2.  Are you sure you are
> not using code out of CVS?
> 

[sqlite] ORDER BY Does not work

[mike cariotoglou]

I have found a bug in the way sqlite treats ORDER BY clauses.
to reproduce the bug, run this script against an empty database (memory db
would do):

  ===
  CREATE TABLE Eidh (
ekey VARCHAR(12) NOT NULL,
perigrafh VARCHAR(30) NOT NULL,
PRIMARY KEY(ekey));


  INSERT INTO Eidh VALUES('0001','bla');

  CREATE TABLE KinApo (
ekey VARCHAR(12) NOT NULL ,
date DATE,
polhths INTEGER);

  INSERT INTO KinApo VALUES('0001',38353,40);
  INSERT INTO KinApo VALUES('0001',38353,30);
  INSERT INTO KinApo VALUES('0001',38353,20);

  select kinapo.ekey, kinapo.polhths
  from eidh inner join kinapo on eidh.ekey=kinapo.ekey
  order by eidh.ekey, kinapo.polhths
  ==

versin 3.2.2 of sqlite gives the following result set, which is unordered:

  0001 40
  0001 30
  0001 20

also, EXPLAIN shows that no sorting is taking place.

This is a SERIOUS malfunction!
can you pls verify this in other environments (I am using dll 3.2.2, locally
compiled),
and do something about it.

RE: AW: [sqlite] sqlite3_column_tablename

[mike cariotoglou]

What do you want if the user types "SELECT (t0.x || t1.x) FROM table0,
table1 ."?

I would like to second that request. Ok, sure, there are cases where the
info is meaningless (like above), and should be blank.
But in most cases, it IS meaningful, and extremely useful for tools (not so
much perhaps for hard-coded situations, but do consider
A report generator, or a visual query builder, for example)

RE: [sqlite] multiple thread concurrency problem with exclusive transaction locks

[mike cariotoglou]

I am not running on *ix, so I cannot test.however, your mentioning multi-cpu
machines, brings something to mind.
In windows, most synchronization functions rely on some form of INTERLOCKED
operation. Now, these operations are implemented differently on multi-cpu
(and hyperthreading counts as multi-cpu in this context), than on single-cpu
machines.
Namely, in the multi-cpu kernel, each interlocked operation is prefixed by a
bus LOCK instruction, whereas in the single-cpu kernel, it is prefixed by a
NOP. As a result, if multi-threaded code runs on a machine that has a
single-cpu kernel, but multiple-cpus, all hell breaks loose (it can happen,
due to a bad installation). Your problem is suspiciously similar.

here are some things to check for:

1. is your *ix kernel compiled for multi-cpu ? I think that linux requires a
special build of the kernel for multi-cpu machines.
2. is your sqlite code compiled with SQLITE_UNIX_THREADS enabled (and
THREADSAFE enabled) ? If not, the default
mechanism used for mutexes in os_unix.c will certainly FAIL on multi-cpu
machines, as it does not have the atomic LOCK prefixes.
If yes, then the functionality of the mutexes (sqlite3OsEnterMutex) depends
on how well the POSIX (or whatever) lock mechanism works. From the comments
on the beginning of the file, I suspect that locking in Linux is horribly
unstable...

So, I would first check that the mutex mechanism works as expected in your
environment before looking for a bug in the sqlite code (unless the default,
simplistic mutex mechanism in sqlite3OsEnterMutex counts as a bug)

-Original Message-
From: Eli Burke [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 12, 2005 6:34 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] multiple thread concurrency problem with exclusive
transaction locks

I hate to beat on a tired horse (threads and db locking issues), but I am
running into what I believe is a bug as we scale up the number of threads in
our application. A little background: there is a main scheduler thread that
does most of the processing, and client threads to handle remote
connections. These clients don't touch the database very often, but on
occasion (in particular when they exit) they request an exclusive lock to
remove themselves from the database.

The problem that I see is that with multiple threads all attempting to
"BEGIN EXCLUSIVE", they will occasionally *all* fail, calling the busy
handler repeatedly until it finally returns SQL_BUSY. Let me re-state for
clarity's sake: 10 threads all try "BEGIN EXCLUSIVE" at the same time. One
succeeds, processes, and COMMITs. The other 9 will sometimes repeatedly call
the busy handler over and over until they fail with SQLITE_BUSY, even though
the database *should be* available to start a new exclusive transaction.

RE: [sqlite] Advice needed for a new group member

[mike cariotoglou]

Definitely 3.x. why go with an old version ? 

-Original Message-
From: Ahmet Aksoy [mailto:[EMAIL PROTECTED] 
Sent: Saturday, January 01, 2005 3:10 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Advice needed for a new group member

Hi Mike,
I'll check your wrapper as son as possible.
Do you think that  version 3.x to be preferred to version 2.8.x?
My sql clauses are generally very simple. I'm using no triggers, etc.
Do you think that version 2.8.x will be enough for me? (Considering I've
been using MsAccess.) Thanks a lot.
Ahmet Aksoy

mike wrote:

>When used correctly, sqlite is the fastest SQL engine I have seen  (I 
>am also using D7). So, yes, it would be a good choice.
>You need some API wrapper for sqlite, there are a few around including 
>my own :) (see contrib)
>
>  
>

RE: [sqlite] Advice needed for a new group member

[mike cariotoglou]

In the home page of www.sqlite.org there is a "contrib" link in the right
upper area.
It points to http://www.sqlite.org/contrib 

-Original Message-
From: Bert Verhees [mailto:[EMAIL PROTECTED] 
Sent: Thursday, December 30, 2004 11:02 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Advice needed for a new group member

Op donderdag 30 december 2004 13:37, schreef mike:
> When used correctly, sqlite is the fastest SQL engine I have seen  (I 
> am also using D7). So, yes, it would be a good choice.
> You need some API wrapper for sqlite, there are a few around including 
> my own :) (see contrib)

Excuse, me, can you please point me to the location where I can find your
wrapper.

Thanks
Bert

>
> -Original Message-
> From: Ahmet Aksoy [mailto:[EMAIL PROTECTED]
> Sent: Thursday, December 30, 2004 12:00 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Advice needed for a new group member
>
> Hi,
> I'm both new to the group, and sqlite.
> I want to redesign one of my old projects.
> It is a kind of mapping program, which shows ports on a world  map. 
> Map is drawn using geographical coordinates of seashores. Also the 
> coordinates of the ports are used in the program.
> You can make searches on port lists, and draw the selected part of the 
> map which contains the port. Also, you can see the local details  if 
> you point a location on the map. You can change the coordinates and 
> scale of the map easily.
> In the project I'm using MSAccess. Now there are about 3000 ports on 
> the database. That number will increase in near future.
> There are very few editing on the database. Records are mostly readonly.
> In order to gain some extra speed, and remove licencing  problems 
> while distributing my program, I made some search on the internet, and 
> found sqlite as it fits best to my needs.
> I'm using delphi7 pro for programming.
> Do you think that sqlite will be  a correct choice for such a project?
> Which delphi components should I use?
> Thanks in advance.
> Ahmet Aksoy

[sqlite] Schema has changed Error

[mike cariotoglou]

In a previous thread about sqlite_schema, I commented that there *is* a case
where
SQLITE_SCHEMA does get raised as an error, although DRH said that this is
not possible in
version 3.x. It turns out that I was wrong. The scenario I described does
raise an error,
but it is not SQLITE_SCHEMA, it is SQLITE_ERROR, and thus it is more
difficult to handle.

in short:

if two processes are accessing the same database, and one modifies the
schema, the other gets
this error on first access.

stranegely, this behavior can not be verified using the command line
program, as it does not
happen there. however, this probably has to do with how this particular
program is using the
API. 

I am using the library via the windows dll, and in this case the
sqlite3_step function will return a value of SQLITE_ERROR when another
process has modified the schema. when I try to retrieve an error description
via sqlite3_errmsg(fdb), I get a message similar to "database schema has
changed", however the error code returned was NOT SQLITE_SCHEMA,but
SQLITE_ERROR. 

Clearly, this creates havoc in a multi-user environment, and my code does
not have a neat way of handling it. Since the error is sqlite_error, the
only indication that a retry would succeed is in the text description, and I
would hate to have to issue retries based on the text content..

DRH, is this fixable ? shall I open a ticket ?

RE: [sqlite] DATABASE SCHEMA HAS CHANGED

[mike cariotoglou]

> In SQLite version 3.0, when a schema change occurs, SQLite 
> automatically goes back to step 1, rereads the schema, and 
> tries again.  So you should never get an SQLITE_SCHEMA error 
> in version 3.0.  Back in version 2.8, you could get an 
> SQLITE_SCHEMA error in some circumstances.  When you do, all 
> you have to do is retry the command and it should work.
> 
> So to answer your questions:
> 
>No, this is not a serious bug.  You just need to be prepared to
>reissue any SQL statement that returns SQLITE_SCHEMA.
> 
>Yes, this issue is fixed in version 3.0.
> 
> --
> D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
> 

This is not entirely correct IMHO. I can easily re-create an SQLITE_SCHEMA
error in version 3.8. you just need two processes to do it:

*. Open a database from one processs
*. Do a select * from a table

*. Open the same database from another process
*. Create a new table from the second process
*. Go back to the first process, and do a select * on the same table you
used before.
You will get an SQLITE_SCHEMA error, once.

DRH, can you confirm this ? According to your description above , it should
not happen.

Re: [sqlite] SQLite manager for Windows

[mike cariotoglou]

look into www.sqlite.org/contrib

- Original Message - 
From: "Edovia Technologies" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, November 04, 2004 3:45 AM
Subject: [sqlite] SQLite manager for Windows


> Hi,
> 
>  
> 
> Anyone knows about a SQLite 3 manager for Windows? The only ones I've
> found
> so far seems to only be compatible with SQLite 2.
> 
>  
> 
> Thanks!
> 
>  
> 
> Luc Vandal
> Edovia Technologies Inc.
> [EMAIL PROTECTED]
> www.edovia.com
> 
>  
> 
>  
> 
>  
> 
> 

Re: [sqlite] new uploads

[mike cariotoglou]

- Original Message -
From: "Dennis Cote" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, November 02, 2004 5:25 PM
Subject: Re: [sqlite] new uploads

ok, Denis I have fixed the bug and uploaded the new version of
sqlite3Explorer. thanks for pointing it out.

BTW, I have some comments on your database sample:

1. the bug was triggered by the definition of foreign keys. you use the
syntax:

 create table x(
   device_id integer references Device

it would be more appropriate to do this:

 create table x(
   device_id integer references Device(Device_ID)

this gives more complete information for the schema.

also, I noticed that you use mixed semantics in your column typing:

Integer
Integer(5)
Varchar
varchar(32)

If you plan to use the strong typing of SqliteExplorer, you should be more
consistent, and use :

integer
varchar(32)

what is the meaning of varchar without a size ? did you mean a text blob ?
if so, use the definition CLOB, which will give your
column text affinity, which is something you want in order to avoid
unnecessary attempts to convert to numeric values. true, the "varchar"
definition also gives you TEXT affinity, but, since there IS a "preferred"
type to declare text blobs, (CLOB), why not use it ? Sqlite3Explorer will
recognize it, and give you a multi-line editor to edit/display this field.

Re: [sqlite] temp_store assumptions

[mike cariotoglou]

>
> - set the pragma "temp_store" to MEMORY
> - CREATE TEMPORARY VIEW temp_table AS ()
>
> I am assuming that temp_table is completely in memory, and any queries
> against it will not go back to the disk. Since the table itself is
> small, I am hoping that the overhead of reading the entire table into
> RAM will be shadowed by the speed improvement of the largish number of
> queries that don't have to go to the disk. Does this sound reasonable?
>
> --
I suspect not. creating a view does not actually generate any data it is
only when you SELECT from that view that any
data access happens. so your scheme is not caching into RAM at all.

the way to do this is to use a second db instance, open it to :MEMORY:, and
copy the data (once) to this new db. then, you will have a RAM-based db,
which should be fast.

I am not sure I understand your GUI topology, though. dirlling down is a
process of elimination, as I understand it, which means executing queries
against progressively smaller result sets (temporary tables would do fine
here), and should be very fast with proper indexes. once I select
"vacation", I have a list of vacation photos. What is the user's next action
? do they select "sports" ? If so, is this meant to be "select photos with
vacation AND sports keywords" ? if so, this is trivial and very fast. just
do a join with the previous result set (saved in a temporary table).

and, are you sure this is a hierarchical data base ? it sounds like the
typical many-to-many with split relation, which cannot be represented
hirerarchically, since the data is *not* hierarchical in nature.

it may well be that you have a pseudo-problem, created by a GUI that does
not match the nature of the data.

Re: [sqlite] Is this an in-memory database too

[mike cariotoglou]

> It would usually make more sense to do the following:
>
> BEGIN TRANSACTION;
> ... inserts,updates,deletes
> COMMIT;
>
> You get pretty much the same affect, but it is safe. Setting

yes, I am aware of this, of course. what I am saying is : since there *is* a
cache,
and since we *can* modify its paging behavior, we should also have an
explicit
way to flush it.

actually, I have been doing some tests, and "flushing" the cache is not
accurate. it seems that sqlite *does* write to "disk", even if the OS does
not do so immediatelly. it is the actual OS call to flush the *OS* cache
that does not happen in sync=off mode. so, what I am talking about is for an
explicit command to do this. it is a single OS call, but it requires a file
handle, which should *not* be exposed from the sqlite DLL. I can cheat and
get it from the opaque database state pointer, but this would be
version-dependent, hence I am asking for an "official" method to do it.

Re: [sqlite] Is this an in-memory database too

[mike cariotoglou]

> Yes, I was a trifle optimistic with my estimates
>
> On an operating system with a sensible I/O scheduler (I cannot say
> whether or not windows qualifies) it should normally take about
> two complete rotations of the disk platter to complete a write.
> Obviously a large change would take more than that, but the
> common case will usually work in two rotations.
>
ok. fact is, we have to live with windows. so, let me raise a matter that I
mentioned some messages back.
I have a feature request, which should be trivial for you to implement, but
will take hours for me, since I can barely read C,
and not write it, except for trivial cases. case is as follows:

could the "pragma synchronous" statement do a full disk flush when it
executes, in addition to changing the operating mode?

rationale:

assume that I can live with the database being in a high-risk state, for a
small window of time.  I would like to be able to do this:

pragma synchronous=off

... inserts,updates,deletes
pragma synchronous=full

this I *can* do, now, but I dont think that this sequence will flush the
in-memory caches when it ends. so, if the pragma did this automatically, the
above sequence would make sense. Is this something you could/would add ?
alternatively, a PRAGMA FLUSH statement would be fine,too.

[sqlite] Sqlite explorer

[mike cariotoglou]

I have a new version of sqliteExplorer, which handles v3 databases, and has
some other enhancements as well, for some time now. The problem is, I dont
have a web server to put it on. DRH, is there some place on the site where I
can upload it  ?

question to all:

how many people use sqlite with Delphi ? I have a couple of delphi sqlite
components, which are quite sophisticated, and make use of the engine very
easy. I have not bothered to upload them anywhere, since I assumed that most
people use sqlite with C anyway. if there is an interest, however, and if
DRH can provide the upload space, I could make them public, free of charge
of course.

- Original Message -
From: "Steve Frierdich" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, October 07, 2004 11:21 PM
Subject: [sqlite] Does the Sqlite explorer program not view version 3 sqlite
databases?


>
>
> Does the sqlite explorer program not view version 3 sqlite databases? I
> tried to open a sqlite version 3 database with the sqlite explorer
> program and the program said that the database is malformed. Is there a
> new sqlite explorer program to download to view sqlite version 3
databases?
>
> Thanks for all your help
>
> Steve Frierdich
>
> >
>

Re: [sqlite] Is this an in-memory database too

[mike cariotoglou]

- Original Message -
From: "D. Richard Hipp" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, October 07, 2004 7:10 PM
Subject: Re: [sqlite] Is this an in-memory database too


> Yes.  As many different processes as you want can read the database
> at the same time.  Only one process at a time can write, but since
> writes usually take a millisecond or less, that is not normally a
> problem.  The writer process does its writing, then when it is done
> the other reader processes go back to doing their reading.
>
well, this is not actually as true as it should be. true, writes are fast
per se.
however, if you operate at a synchronous level of anything more than 0,
the whole operation of inserting a record,say, is quite slow, at least in
windows.

These are my findings:

with sync=none :
 everything is extremely fast, but no practical way to flush the disk file
buffer

with sync<>none
a single transaction has a largish overhead, which relates to the flushing
of the disk file. for example, the following may take hundreds of
milliseconds:

insert into foo values(somevalues)

and also,

begin
insert into foo values(somevalues)
commit

however, the following will take almost the same time :

begin
insert into foo values(somevalues)
insert into foo values(somevalues)
insert into foo values(somevalues)
insert into foo values(somevalues)
insert into foo values(somevalues)
insert into foo values(somevalues)
insert into foo values(somevalues)
commit

in other words, the time is not consumed  by the INSERTS, but by the END
(implicit or explicit). since the locks are held for the duration of the
transaction,
a reader will be blocked by a minimum time of one single flush overhead,
which is NOT a millisecond or so.

so, at least in windows, there is no such thing as a fast WRITE, unless one
chooses to sacrifice data integrity. bulk operations are fast, however,
because they can be bracketed by a transaction, which disables the
per-statement flushing.

I wish there was a way around this.

DRH, care to comment ?

PS what does the "D." stand for ?

[sqlite] Bug in Sqlite3 and parameters

[mike cariotoglou]

I am using a parametric sql statement, with parameters of the form :Name

The following routines give an access violation, when called via the windows
dll:

sqlite3_bind_parameter_name
sqlite3_bind_parameter_index

The access violation indicates that a NULL pointer is being accessed.
A brief look at the code suggests that some problem with the azVar
initialization
must be the reason, but I do not know enough C to fix it.

environment: Windows 2000, Delphi 7 calling sqlite3.dll.

can you pls do something about it, quickly, as I cannot continue my project.
thanks.