[sqlite] Timeout not honored on ATTACH in 2.8. Same in 3.0?

[Derrell . Lipman]

In the 2.8 series, an ATTACH command does not honor a specified busy-timeout
value; rather, if the database which is being attached is locked, the ATTACH
fails immediately.  It would seem that ATTACH should honor busy-timeout as do
other commands, and retry until it succeeds or the timeout is exceeded.

If this same issue occurs in 3.0, it would be nice to correct it.  I mention
this now only on the very very small chance that adding header fields or other
file format changes would be involved in the fix.  If, as I expect, that's not
the case, then after the 3.0 official release, I'll verify that the same
problem exists in 3.0 and submit a new report (i.e. after all of the file
format issues have been addressed).

Cheers,

Derrell

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

Re: [sqlite] SQLite crashes during rapid inserts!

[Darren Duncan]

At 2:07 PM +0100 6/24/04, Chris Ulliott wrote:
Hi all,
 I am using the SQLite DB with MS VC++ v 6. In the code below you can see
I am looping around a ODBC connection to an access database and I am
inserting those records into my new SQLite database. My problem is
SQLite.dll causes an access violation after inserting about 190 records.
Does anyone have any ideas what I am missing or if this is a bug?
 Code...
You can isolate whether this is a speed issue or a quantity / data 
type issue by slowing down the inserts.  For example, sleep a short 
time or a second between each row, and see if it still dies at 190 
records. -- Darren Duncan

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

Re: [sqlite] 3.0 file format change for non-manifest typing or strict affinity

[Darren Duncan]

At 3:35 AM -0700 6/24/04, Daniel K wrote:
Fortunately the only file format issue here is whether
or not the 'affinity mode' is a persistent property
of the database.
True.  And a persistent property is definately what I want. 
Otherwise, someone may simply open the file the wrong way some day 
and put "bad" data into a slot that is supposed to be strict.  Oops. 
I think that this would be trivially easy to put in the file header 
page of the file spec. -- Darren Duncan

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

RE: [sqlite] Performance Issue with SQLite Inserts

[Alex Wang]

But I am really like in my testing result.
I did 15 M insert in maybe 10 transaction and I am really happy with  the
performance.
I did not use any long filed as index.
-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Sent: Thursday, June 24, 2004 3:13 PM
Cc: [EMAIL PROTECTED]
Subject: Re: [sqlite] Performance Issue with SQLite Inserts

Soham Mehta wrote:
> Thanks you for all of the ideas.  Here are answers to the various
questions:
> 
> 1) Currently, I am committing after every 10,000 inserts.  Initially, I
was
> running all of the inserts of a set (25,000+ records) in one transaction.
I
> found that I was able to improve performance slightly by committing after
> every 10,000 records.  Either way, the performance is still too poor.
> 
> 2) I am going directly to the dll (not using ODBC).
> 
> 3) I am already using PRAGMA default_synchronous=OFF
> 

*  Are you writing to a local disk or to a network filesystem?

*  Please show use your schema and an example INSERT, complete
with data.


-- 
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] Performance Issue with SQLite Inserts

[Soham Mehta]

Thanks you for all of the ideas.  Here are answers to the various questions:

1) Currently, I am committing after every 10,000 inserts.  Initially, I was
running all of the inserts of a set (25,000+ records) in one transaction.  I
found that I was able to improve performance slightly by committing after
every 10,000 records.  Either way, the performance is still too poor.

2) I am going directly to the dll (not using ODBC).

3) I am already using PRAGMA default_synchronous=OFF


Thank you all for the help.

Regards,
Soham


Here are the directions to my test application again. It should be really
easy to run.  I would greatly appreciate it, if someone would take a look at
it.

1) download the test zip file from the following FTP location (14MB):
ftp://ftp.fileburst.com/
USER: sqlite
PSWD: sqlhelp
2) unzip it
3) run register.bat (this just registers a required COM object)
4) open DataLoaderTest\DataLoaderTest.sln
5) build
6) run (you can do this directly from the debugger)
7) click on "Load Data"


-Original Message-
From: andy [mailto:[EMAIL PROTECTED] 
Sent: Thursday, June 24, 2004 2:38 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Performance Issue with SQLite Inserts

Soham Mehta wrote:

> Hello,
> 
> We recently switched to SQLite in our application and have not been able
to
> get the performance that partially motivated the switch.  Our application
> downloads TV guide data from the internet and loads it into a local
> database.
>  

> 
> Obviously, this does not match up with the performance tests that appear
on
> the website.  Is there something that we are doing wrong?  Any help or
> guidance that you can provide would be greatly appreciated.
> 
> 
> Regards,
First a few questions...  Are you running the entire import in one 
transaction?

Are you using ODBC or right to the DLL?

Have you tried using PRAGMA synchronous = OFF?

see:
http://sqlite.org/lang.html#pragma_default_synchronous
(* where it says its 50% faster this way, and warns about corrupting the 
db *)

-Andy

-
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] v3.0.1 sqlite3_mprintf

[Hans-Juergen Taenzer]

Julian Brierley  ([EMAIL PROTECTED]) wrote:
 > Hans-Juergen Taenzer wrote:

 >> doing some tests with the new version, I have encountered a
 >> problem:
 >>
 >> void testva(void)
 >> {
 >>char *s;
 >>unsigned long ul1 = 0x7FFF;
 >>unsigned long ul2 = 0x;
 >>
 >>s = sqlite3_mprintf("%lu", ul1);
 >>printf("ul1: %lx, %s\n", ul1, s);
 >>
 >>s = sqlite3_mprintf("%lu", ul2);
 >>printf("ul2: %lx, %s\n", ul2, s);
 >>
 >>printf("sizeof(unsigned long): %u\n", sizeof(unsigned long));
 >> }
 >>
 >> Output is:
 >>
 >> ul1: 7fff, 2147483647
 >> ul2: , 18446744073709551615
 >> sizeof(unsigned long): 4
 >>
 >> Is this the expected behavior? Compiler is MSVC 6.

 > Perhaps if the two lines of code starting at 349 in printf.c were
 > changed from:
 > else if( flag_long )longvalue = va_arg(ap,long int);
 > else longvalue = va_arg(ap,int);
 > to somthing like:
 > else if( flag_long )
 > {
 > if (infop->flags & FLAG_SIGNED)
 > longvalue = va_arg(ap,long int);
 > else
 > longvalue = va_arg(ap,unsigned long int);
 > }
 > else
 > {
 >   if (infop->flags & FLAG_SIGNED)
 > longvalue = va_arg(ap,int);
 >   else
 > longvalue = va_arg(ap,unsigned int);
 > }

 > You would get what you expect for %lu and %u?

 > ul1: 7fff, 2147483647
 > ul2: , 4294967295

I haven't tried it yet. I think this function (or the workhorse vxprintf)
is very important for sqlite. I think its to dangerous to change without
good knowledge of sqlite.

I've opened a ticket.

The different behavior between 2.8 and 3.0 makes it somewhat difficult to
migrate my databases to sqlite 3.0.

Hans-Jürgen

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

Re: [sqlite] Performance Issue with SQLite Inserts

[Michal Zaborowski]

As mentioned before try with putting all your inserts into
begin end block. In that way there is no way to query the
file from other proces. Any way check pragma directives ie.
make bigger cache size...
--
Regards,
  Michał Zaborowski (TeXXaS)
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Performance Issue with SQLite Inserts

[John LeSueur]

Wempa, Kristofer (Kris), ALABS wrote:
-Original Message-
From: Soham Mehta [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 24, 2004 3:22 PM
To: [EMAIL PROTECTED]
Subject: [sqlite] Performance Issue with SQLite Inserts
Hello,

Obviously, this does not match up with the performance tests that appear
on
the website.  Is there something that we are doing wrong?  Any help or
guidance that you can provide would be greatly appreciated.

Regards,
Soham

How are you loading all of this data into the sqlite database.  I notice
that bulk loading from a file using "COPY" works pretty good.  A bunch
of single inserts performs poorly.
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
 

Also, have you tried putting all of the inserts/copy commands inside of 
a single transaction?  Sqlite likes transactions,
and does much better with large sets of inserts inside transactions.

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

Re: [sqlite] Performance Issue with SQLite Inserts

[andy]

Soham Mehta wrote:
Hello,
We recently switched to SQLite in our application and have not been able to
get the performance that partially motivated the switch.  Our application
downloads TV guide data from the internet and loads it into a local
database.
 

Obviously, this does not match up with the performance tests that appear on
the website.  Is there something that we are doing wrong?  Any help or
guidance that you can provide would be greatly appreciated.
Regards,
First a few questions...  Are you running the entire import in one 
transaction?

Are you using ODBC or right to the DLL?
Have you tried using PRAGMA synchronous = OFF?
see:
http://sqlite.org/lang.html#pragma_default_synchronous
(* where it says its 50% faster this way, and warns about corrupting the 
db *)

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

RE: [sqlite] Performance Issue with SQLite Inserts

[Wempa, Kristofer \(Kris\), ALABS]

-Original Message-
From: Soham Mehta [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 24, 2004 3:22 PM
To: [EMAIL PROTECTED]
Subject: [sqlite] Performance Issue with SQLite Inserts


Hello,

 

We recently switched to SQLite in our application and have not been able
to
get the performance that partially motivated the switch.  Our
application
downloads TV guide data from the internet and loads it into a local
database.

 

The data is in 6 sets of XML files (each set holds 2 days of data).  We
read
the data and insert it into the database a set of data at a time.

 

Other than one unique index, all of the indexes are generated after the
inserts to help speed up the initial data load.

 

Here's how the inserts performed (this will print in the output pane of
the
debugger):

Inserting 26544 records took 32538.7241 ms.

Inserting 26420 records took 36432.113 ms.

Inserting 26262 records took 37901.9064 ms.

Inserting 25560 records took 37948.8147 ms.

Inserting 26389 records took 54225.9948 ms.

Inserting 17324 records took 31084.5668 ms.

 

Just to prove that the hit is not coming from the XML reading or other
logic, here are the results for the exact same test over the exact same
data
with just the SQLite insert commented out:

Inserting 26544 records took 4597.0134 ms.

Inserting 26420 records took 4393.7441 ms.

Inserting 26262 records took 4409.3802 ms.

Inserting 25560 records took 4956.6437 ms.

Inserting 26389 records took 5378.8184 ms.

Inserting 17324 records took 3486.8503 ms.

 

Creating indexes is also slow.

 

Obviously, this does not match up with the performance tests that appear
on
the website.  Is there something that we are doing wrong?  Any help or
guidance that you can provide would be greatly appreciated.

 

Regards,

Soham



How are you loading all of this data into the sqlite database.  I notice
that bulk loading from a file using "COPY" works pretty good.  A bunch
of single inserts performs poorly.

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

[sqlite] Performance Issue with SQLite Inserts

[Soham Mehta]

Hello,

 

We recently switched to SQLite in our application and have not been able to
get the performance that partially motivated the switch.  Our application
downloads TV guide data from the internet and loads it into a local
database.

 

The data is in 6 sets of XML files (each set holds 2 days of data).  We read
the data and insert it into the database a set of data at a time.

 

Other than one unique index, all of the indexes are generated after the
inserts to help speed up the initial data load.

 

Here's how the inserts performed (this will print in the output pane of the
debugger):

Inserting 26544 records took 32538.7241 ms.

Inserting 26420 records took 36432.113 ms.

Inserting 26262 records took 37901.9064 ms.

Inserting 25560 records took 37948.8147 ms.

Inserting 26389 records took 54225.9948 ms.

Inserting 17324 records took 31084.5668 ms.

 

Just to prove that the hit is not coming from the XML reading or other
logic, here are the results for the exact same test over the exact same data
with just the SQLite insert commented out:

Inserting 26544 records took 4597.0134 ms.

Inserting 26420 records took 4393.7441 ms.

Inserting 26262 records took 4409.3802 ms.

Inserting 25560 records took 4956.6437 ms.

Inserting 26389 records took 5378.8184 ms.

Inserting 17324 records took 3486.8503 ms.

 

Creating indexes is also slow.

 

Obviously, this does not match up with the performance tests that appear on
the website.  Is there something that we are doing wrong?  Any help or
guidance that you can provide would be greatly appreciated.

 

Regards,

Soham

 

 

Instructions to Run Test:

1) download the test zip file from the following FTP location (14MB):

ftp://ftp.fileburst.com/

USER: sqlite

PSWD: sqlhelp

2) unzip it

1) run register.bat (this just registers a required COM object)

2) open DataLoaderTest\DataLoaderTest.sln

3) build

4) run (you can do this directly from the debugger)

5) click on "Load Data"

 

Re: [sqlite] SQLite and Windows 2k

[andy]

To make the dll, I could not get the dllwrap to work.  Based on:
http://www.neuro.gatech.edu/users/cwilson/cygutils/dll-stuff/README
I used this script to create the dll.
#!/bin/sh
gcc -mno-cygwin -Wl,--base-file,base.tmp \
 -mdll -Wl,-e,[EMAIL PROTECTED] \
  -o sqlite3.dll *.o -L/lib/mingw -lmsvcrt
dlltool --base-file base.tmp --output-exp exp.tmp --def sqlite3.def
gcc -mno-cygwin -Wl,--base-file,base.tmp  exp.tmp \
  -mdll -Wl,-e,[EMAIL PROTECTED] -o sqlite3.dll \
  *.o  -L/lib/mingw  -lmsvcrt
dlltool --base-file base.tmp --output-exp exp.tmp --def sqlite3.def
gcc exp.tmp -mno-cygwin -mdll \
  -Wl,-e,[EMAIL PROTECTED] -o sqlite3.dll \
  *.o -L/lib/mingw  -lmsvcrt
Boy did I make that harder than it needed to be.  Here is the easy way:
gcc -shared -mno-cygwin -o sqlite3.dll \
  -L/lib/mingw -lmsvcrt  *.o sqlite3.def
-Andy
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] encoding

[Michael Roth]

nathanvi wrote:
Thanks. Do you know how can i teach to my application (phpsqliteadmin
and sqlitebrowser-GUI) the result set is utf8 and it is not iso8859-1?
I'm sorry. I don't know that. Maybe the authors of these tools know how 
to solve your problem?

At least for the PHP stuff I guess it's related with the content 
encoding used by the web page or server?


Thank all.
No problem. ;-)
cu
Michael


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

Re: [sqlite] SQLite and Windows 2k

[andy]

Chris Ulliott wrote:
Andy!
 
FANTASTIC JOB! Thank you! Could you also make the source available
please and tell me what changes you made? In the future if a new version
comes out, it may not include your change and I need it!
 
I would hate to upgrade and loose your change...
 
Thanks again, Great work!
 
Chris
 

Wahoo! Got it! It was a -mno-cygwin that was the magic. I have uploaded
it to my website. You can get it from

http://squeakycode.net/files/sqlite3.dll
I noticed the sqlite3.def was also missing sqlite3_changes, so I added
it too.
Let me know if you have problems.
-Andy

I made no changes to the .c files.  the .def file has been patched and 
checked in so you wont need to edit it either.

So here is what I did.
dl the source and uncompress it.  then
mkdir build
cd build
../configure
Then I edited the Makefile and changed this line:
TCC = gcc -g -O2 -mno-cygwin -DOS_WIN=1 -DHAVE_USLEEP=1 -I. -I${TOP}/src
to include the -mno-cygwin
then do a make.  This will make the sqlite.exe for windows without 
requiring the cygwin1.dll

To make the dll, I could not get the dllwrap to work.  Based on:
http://www.neuro.gatech.edu/users/cwilson/cygutils/dll-stuff/README
I used this script to create the dll.
#!/bin/sh
gcc -mno-cygwin -Wl,--base-file,base.tmp \
 -mdll -Wl,-e,[EMAIL PROTECTED] \
  -o sqlite3.dll *.o -L/lib/mingw -lmsvcrt
dlltool --base-file base.tmp --output-exp exp.tmp --def sqlite3.def
gcc -mno-cygwin -Wl,--base-file,base.tmp  exp.tmp \
  -mdll -Wl,-e,[EMAIL PROTECTED] -o sqlite3.dll \
  *.o  -L/lib/mingw  -lmsvcrt
dlltool --base-file base.tmp --output-exp exp.tmp --def sqlite3.def
gcc exp.tmp -mno-cygwin -mdll \
  -Wl,-e,[EMAIL PROTECTED] -o sqlite3.dll \
  *.o -L/lib/mingw  -lmsvcrt
strip sqlite3.dll
cp sqlite3.dll /cygdrive/d/sqlite/
Watch the wrapping, I added a blank line between each command.
However, I dont really think you'll need all that.  Sense  3 is still in 
beta its probably just a build problem that'll get cleared up.  You'll 
be able to download the dll just fine and it'll work once all the kinks 
get worked out.  I'm just posting this so people can play with the dll 
while its in beta.

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

Re: [sqlite] SQLite and Windows 2k

[Chris Ulliott]

Andy!
 
FANTASTIC JOB! Thank you! Could you also make the source available
please and tell me what changes you made? In the future if a new version
comes out, it may not include your change and I need it!
 
I would hate to upgrade and loose your change...
 
Thanks again, Great work!
 
Chris
 
>Wahoo! Got it! It was a -mno-cygwin that was the magic. I have uploaded
it to my website. You can get it from
> 
http://squeakycode.net/files/sqlite3.dll
>I noticed the sqlite3.def was also missing sqlite3_changes, so I added
it too.
>Let me know if you have problems.
>-Andy

Re: [sqlite] v3.0.1 sqlite3_mprintf

[Julian Brierley]

Hans-Juergen Taenzer wrote:
hello,
doing some tests with the new version, I have encountered a problem:
void testva(void)
{
   char *s;
   unsigned long ul1 = 0x7FFF;
   unsigned long ul2 = 0x;
   s = sqlite3_mprintf("%lu", ul1);
   printf("ul1: %lx, %s\n", ul1, s);
   s = sqlite3_mprintf("%lu", ul2);
   printf("ul2: %lx, %s\n", ul2, s);
   printf("sizeof(unsigned long): %u\n", sizeof(unsigned long));
}
Output is:
ul1: 7fff, 2147483647
ul2: , 18446744073709551615
sizeof(unsigned long): 4
Is this the expected behavior? Compiler is MSVC 6.
Hans-Jürgen
 

Perhaps if the two lines of code starting at 349 in printf.c were 
changed from:
   else if( flag_long )longvalue = va_arg(ap,long int);
   else longvalue = va_arg(ap,int);
to somthing like:
   else if( flag_long )
   {
   if (infop->flags & FLAG_SIGNED)
   longvalue = va_arg(ap,long int);
   else
   longvalue = va_arg(ap,unsigned long int);
   }
   else
   {
 if (infop->flags & FLAG_SIGNED)
   longvalue = va_arg(ap,int);
 else
   longvalue = va_arg(ap,unsigned int);
   }

You would get what you expect for %lu and %u?
ul1: 7fff, 2147483647
ul2: , 4294967295
Regards,
Julian.
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] SQLite and Windows 2k

[andy]

Chris Ulliott wrote:
Hi All,
I have downloaded from the downloads page the DLL (Sqlite.dll) for
Windows. I have written some code using the API and everything was going
great until I needed to do an execute and it came to my attention that
the DLL does not export sqlite3_exec
Does anybody have a Win32 DLL of SQLite 3 that exports all the
documented functions? Am I missing something else?
I also tried using the SQLite3.dll instead but I soon found that there
are NO EXPORTED FUNCTIONS from there whatsoever.
Any help appreciated,
Thanks,
Chris
Wahoo!  Got it!  It was a -mno-cygwin that was the magic.  I have 
uploaded it to my website.  You can get it from

http://squeakycode.net/files/sqlite3.dll
I noticed the sqlite3.def was also missing sqlite3_changes, so I added 
it too.

Let me know if you have problems.
-Andy
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] SQLite and Windows 2k

[Chris Ulliott]

Hi Richard
 
Unfortionatly there is no link for a Compiled Binary (win 32) for
version 2.x on the downloads page otherwise I would be using it.
 
Thanks for replying.
 
Chris
 
 
 
Richard Boehme wrote:
 
The regular sqlite.dll should be the 2.X release, not the 3 release, and
therefore won't export sqlite3_exec, but will export sqlite_exec.

As for the other, I haven't played around with sqlite3 much as I'm in
the middle of a project, but I intend to in a couple of days.


Thank you.

Richard Boehme




Chris Ulliott wrote: 

Hi All,

I have downloaded from the downloads page the DLL (Sqlite.dll) for

Windows. I have written some code using the API and everything was going

great until I needed to do an execute and it came to my attention that

the DLL does not export sqlite3_exec

Does anybody have a Win32 DLL of SQLite 3 that exports all the

documented functions? Am I missing something else?

I also tried using the SQLite3.dll instead but I soon found that there

are NO EXPORTED FUNCTIONS from there whatsoever.

Any help appreciated,

Thanks,

Chris




-

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] v3.0.1 sqlite3_mprintf

[Hans-Juergen Taenzer]

hello,

doing some tests with the new version, I have encountered a problem:

void testva(void)
{
char *s;
unsigned long ul1 = 0x7FFF;
unsigned long ul2 = 0x;

s = sqlite3_mprintf("%lu", ul1);
printf("ul1: %lx, %s\n", ul1, s);

s = sqlite3_mprintf("%lu", ul2);
printf("ul2: %lx, %s\n", ul2, s);

printf("sizeof(unsigned long): %u\n", sizeof(unsigned long));
}

Output is:

ul1: 7fff, 2147483647
ul2: , 18446744073709551615
sizeof(unsigned long): 4

Is this the expected behavior? Compiler is MSVC 6.

Hans-Jürgen

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

Re: [sqlite] SQLite and Windows 2k

[Richard Boehme]

The regular sqlite.dll should be the 2.X release, not the 3 release, and 
therefore won't export sqlite3_exec, but will export sqlite_exec.

As for the other, I haven't played around with sqlite3 much as I'm in 
the middle of a project, but I intend to in a couple of days.

Thank you.
Richard Boehme

Chris Ulliott wrote:
Hi All,
I have downloaded from the downloads page the DLL (Sqlite.dll) for
Windows. I have written some code using the API and everything was going
great until I needed to do an execute and it came to my attention that
the DLL does not export sqlite3_exec
Does anybody have a Win32 DLL of SQLite 3 that exports all the
documented functions? Am I missing something else?
I also tried using the SQLite3.dll instead but I soon found that there
are NO EXPORTED FUNCTIONS from there whatsoever.
Any help appreciated,
Thanks,
Chris

-
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]

[sqlite] SQLite crashes during rapid inserts!

[Chris Ulliott]

Hi all,
 
I am using the SQLite DB with MS VC++ v 6. In the code below you can see
I am looping around a ODBC connection to an access database and I am
inserting those records into my new SQLite database. My problem is
SQLite.dll causes an access violation after inserting about 190 records.
Does anyone have any ideas what I am missing or if this is a bug?
 
Code...
 
 
  nRecord=0;
  while(pRs->EndOfFile == FALSE)
  {
   nRecord++;
 
   // Create Insert Command

   strCmd = _T("INSERT INTO SCHEMATABLES VALUES(");
   for(int nField = 0; nField < pRs->Fields->Count; nField++)
   {
var.lVal = nField;
var.ChangeType(VT_I2, NULL);
strCmd += _T("\"");
strCmd += pRs->Fields->GetItem(var)->Value.bstrVal;
strCmd += _T("\", ");
   }
   strCmd = strCmd.Left(strCmd.GetLength()-2);
   strCmd += _T(");\n");
 
 
   // SQLite .. Insert record into SQLLite Database
   nReturn = procPrepare(pResult, (LPCTSTR)strCmd, -1, ,
);
   nReturn = procStep(stmt);
 
   // MDB
   pRs->MoveNext();
  }
 
  pRs->Close();
 
  nReturn = procClose(pResult);
  str = procErrMsg(pResult);
 
Thank you in advance,
 
Chris
 

Re: [sqlite] encoding

[Michael Roth]

nathanvi wrote:
Strings are in utf8 encoding.
My system has utf8 locales too.
I've a lot of problem with accents as èéàòù.
I inserted them in a field.
When i make a select of that field, i obtain right accents via shell:
sqlite db.sqlite 'select frase from logchan limit 16660' => èéàòù
On the contrary, if i look for that field via sqlitebrowser or
phpsqliteadmin it result bad: ©Ã¹Ã 
Looks like that's sqlitebrowser and phpsqliteadmin fault.
Sqlite itself and the console tool 'sqlite' doesn't know anything about 
utf-8 or encodings, because it doesn't matters to sqlite.

Maybee your tools are interpreting the bytes stored in the database as 
iso8859-xx encodings. IIRC your results show that the two tools you use 
are asuming iso8859-1 in the database, which if of course wrong.

cu
Michael

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

[sqlite] A quick code review, analysis of locking model, "fine-tuning" suggestions

[ben . carlyle]

G'day,

I thought I'd so somewhat of a code review on the lower-level pieces of 
sqlite 3.0.1, so here goes:

v- code review v



You use a loop here to try and write all data 
synchronously to disk. I had to read it a couple of times before I 
realised it was actually performing the check on write's return correctly, 
but I am happy with it
Is there a reason why similar logic doesn't appear in 
sqlite3OsRead? 
Background: Under unix, multiple reads/writes may be 
required for "slow" devices. One read/write should alway suffice for a 
"fast" device. A disk is considered a fast device for this purpose. An nfs 
mount would be considered a slow device.


I've always been uneasy about the interaction in sqlite 
between the pager an os layer with regards to locking. It seems like 
excessive coupling to me. The os layer is essentially written to know the 
lock state of the pager, and then participate in a dance whereby the pager 
and os always approximately agree on the lock status. The sqlite3OsLock 
function is an example of this, where an attempt to grab a lock of a less 
than or equal status to the current lock is a no-op. To me this means that 
the pager is making uncessary calls and doesn't seem particularly aware of 
its own state. The os layer knows too much about what's happening ("I know 
that you didn't really mean to call this function"), and the pager doesn't 
know enough ("I'll make this call, just in case"). I think that all such 
no-op forms of os layer functions should be replaced with assertions and 
the code above made self-contained.
The concept of upgrading and downgrading locks 
transparently has also consistently bugged me, especially when I've wanted 
to use blocking locks. It makes things much harder when the aren't 
clearly-defined individual lifecycles for reader and writer locks, and 
when writers are forced to share the early lifecycle of readers.


Extraneous assert. The if condition tests this assert 
already.


Use the #define value rather than numeric value of 
NO_LOCK.


F_RDLCK should be replaced with F_WRLCK. Locking only with 
F_RDLCK has no effect.


Use the #define value rather than numeric value of 
NO_LOCK.


lock.l_len should be set to 1 when it needs to be that 
value. Doing it at the top of function reduces code clarity and introduces 
uncecessary asymmetry in conditional branches.


I'm nervy about the different locks that might be held by 
an process in EXCLUSIVE_LOCK state depending on how it reached that state. 
If it went through SHARED_LOCK it has SHARED_FIRST through SHARED_FIRST + 
SHARED_SIZE write locked + PENDING_BYTE through PENDING_BYTE + 1 read 
locked. If it came through the reserved state it also has a write lock on 
RESERVED_BYTE through RESERVED_BYTE + 1. This appears to be dealt with in 
the unlock code, but it grates a little. I actually don't like the SHARED 
-> PENDING path at all, and think it should be removed for simplicity. It 
effectively creates two versions of the pending and exclusive states, 
respectively.


I don't like the setting of PENDING_LOCK here. Surely the 
code would be clearer if it were set back when the pending lock was 
obtained. I understand that you still want access to the "old value" 
during the rest of the function, but couldn't you copy it?

Ok, I haven't reviewed much past this point. I was hoping to get 
in some comments on the pager itself which I haven't read, yet... but I've 
been at this for a little too long now.


v analysis of locking model v

Just to get this straight in my head, this is the current unix locking 
model (does this appear somewhere in comments?):

States

NO_LOCK = Nothing locked
SHARED_LOCK = SHARED_FIRST through SHARED_FIRST + SHARED_SIZE read-locked
RESERVED_LOCK = RESERVED_BYTE through RESERVED_BYTE + 1 write-locked + 
locks of SHARED_LOCK state
PENDING_LOCK = PENDING_BYTE through PENDING_BYTE + 1 read-locked + either 
locks of SHARED_LOCK state, or locks of RESERVED_LOCK state
EXCLUSIVE_LOCK = SHARED_FIRST through SHARED_FIRST + SHARED_SIZE 
write-locked + locks of PENDING_LOCK state

Transitions

NO_LOCK -> SHARED_LOCK:
1. Pick up locks for pending state
2. Pick up locks for shared state
3. Drop locks for pending state

SHARED_LOCK -> RESERVED_LOCK:
1. Pick up locks for reserved state. Reserved lock is exclusive, so only 
one process can be in reserved state at any one time but concurrency with 
readers is ok.

SHARED_LOCK -> EXCLUSIVE_LOCK
1. Pick up locks for pending state
2. Pick up locks for exclusive state

RESERVED_LOCK -> EXCLUSIVE_LOCK
1. Pick up locks for pending state
2. Pick up locks for exclusive state

PENDING_LOCK -> 

Re: [sqlite] 3.0 file format change for non-manifest typing or strict affinity

[Daniel K]

This is pretty much what strict affinity is meant to 
do. But alas, time has slipped away faster than anyone
thought possible and it may not make it into the
release at the end of the month.

Fortunately the only file format issue here is whether
or not the 'affinity mode' is a persistent property
of the database. 

--- [EMAIL PROTECTED] wrote:
> > In brief, I want an option that is like "normal"
> except that an error
> > is returned if the data can not be converted to
> the column's declared
> > type; I want some input flexibility, but I don't
> want the engine to
> > store values that are not of the correct type.
> 
> I second this wish.
> 
> Frank Baumgart
> 
> 
> 
>
-
> To unsubscribe, e-mail:
> [EMAIL PROTECTED]
> For additional commands, e-mail:
> [EMAIL PROTECTED]
> 
> 




__
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail

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

Re: [sqlite] 3.0 file format change for non-manifest typing or strict affinity

[godot]

> In brief, I want an option that is like "normal" except that an error
> is returned if the data can not be converted to the column's declared
> type; I want some input flexibility, but I don't want the engine to
> store values that are not of the correct type.

I second this wish.

Frank Baumgart



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

Re: [sqlite] database corruption

[ben . carlyle]

Hello,





"D. Richard Hipp" <[EMAIL PROTECTED]>
24/06/2004 06:04 AM

 
To: 
cc: [EMAIL PROTECTED]
Subject:Re: [sqlite] database corruption


> Michael Robinette wrote:
> > ...

> You present a new and novel approach to corrupting the database, which
> is to combine a database file with a journal from a different database
> into the same directory.  We'll be thinking about what to prevent this
> attack in the 6 days that remain before we freeze the 3.0.0 database
> format.

This is actually a variant of the method of corrupting the database that 
fsync()ing the directory containing your journal each commit is designed 
to solve. An unsynched directory entry may lead to the existence after a 
power failure of an old journal file, instead of the one that relates to 
the current database state. Obviously, this variant is a solved problem 
while others are not.

The variant I'm most concerned about is actually a copy operation. User A 
says to himself "they're just files, I'll copy them onto my backup media". 
This will often appear to work, so he won't be concerned. One day he 
restores the files and "weird things" start happening.

I'm not sure there's a solution to that, other than user education or an 
operating-system-level implementation of the journalling itself that 
treats a copy operation the same as other kinds of database reads. 
Ultimately the ideal world would have sqlite journalling built into the 
kernel vfs layer. Hrrmm... I've heard that windows longhorn might 
incorporate this kind of function. Perhaps we should be pushing for its 
introduction into other operating systems. It's really very compatible 
with other file operations where you might want to do operations that 
ensure readers always see a consistent state of the data. It might also 
make sqlite just a little touch lighter and more focused.

Hrrm.

http://www.namesys.com/faq.html;>
However, although file data may appear to be consistent from the kernel 
point of view, since there is no API exported to the userspace to control 
transactions, we may end-up in a situation where the application makes 2 
write requests (as part of one logical transaction) but only one of these 
gets journaled before the system crashes. From the application point of 
view, we may then end up with inconsistent data in the file. 
Such issues should be addressed with the upcoming ReiserFS v.4 release. 
Such an API will be exported to userspace and all programs that need 
transactions will be able to use it. 

http://olstrans.sourceforge.net/release/OLS2000-ext3/OLS2000-ext3.html;>
[...] one other thing that I want to do is to actually export the nested 
transaction API into userspace. You have to be very, very careful about 
that because it's not possible to guarantee proper database semantics. You 
can't have unbounded, large transactions. You have to have some way in 
which the user application can get in advance some idea of how many disk 
blocks it's going to need to modify for the operation, because it's going 
to call various things like that which are not entirely straight forward; 
it's not quite as simple as people would hope. But it's sufficiently 
useful that that will be exported to userspace at some point.

http://lists.linux-ha.org/pipermail/linux-ha/1999-May/007901.html;>
A user-visible transaction API is something entirely different.  No way
does it belong in the kernel.

http://seclists.org/lists/linux-kernel/2003/Sep/1364.html;>

There will be a new API to support userspace-controlled 
multifile transactions. 
At first stab, multifile transactions will be used internally to 
implement extended attributes. 
Now, another question is.. will the transaction API support commit() and 
rollback()? *grin* 


http://www.linuxjournal.com/article.php?sid=4466;>




>From time to time, people ask for a version of the transaction API 
exported to user space. The ReiserFS journal layer was designed to support 
finite operations that usually complete very quickly, and it would not be 
a good fit for a general transaction subsystem. It might be a good idea to 
provide atomic writes to user space, however, and give them more control 
over grouping operations together. That way an application could request 
for a 64K file to be created in a certain directory and treat it like an 
atomic operation. Very little planning has happened in this area thus far. 






 A full transaction API will probably never be exported by 
the kernel itself, however some basic hooks may eventually be provided if 
enough people can agree on what those hooks should be. Most of the work 
would be performed in user-space.



Thoughts:
* The breif period sqlite now has an inconsistent state in the main 
database made the copy scenareo less likely to be a problem, but the 
problem may occur occasionally.
* In an embedded scenareo you