date:20060726


On Wed, 26 Jul 2006 16:02:00 -0500, Dennis Jenkins wrote:

>[EMAIL PROTECTED] wrote:
>> It has been suggested that I add a mutex to every SQLite
>> database connection.  This would cause access to a database
>> connection to automatically serialize even when two or more
>> threads try to use that connection at once, thus preventing
>> problems such as the above.
>>
>> The downside is the (minimal) additional overhead of acquiring 
>> and releasing a mutex on each API call.  There will also be 
>> some changes to the OS-layer backend which will break 
>> private ports to unusual OSes.
>>
>> Thoughts?
>>
>> --
>> D. Richard Hipp   <[EMAIL PROTECTED]>


>IMHO:
>
>Don't do it.  Those of us that want to use threads should create our own 
>threaded sqlite wrappers.

If i understand Richard well, there would be a mutex for
serializing calls to the same database connection.
So nothing would change if each thread opened its own instance.

As it doesn't affect my applications in any way I'm not
particularly in favour or against it, but i can imagine that
life would be easier for many other people if such a feature
were available.

In order to keep sqlite lean and mean where needed I think it
should be a compile time option.
-- 
  (  Kees Nuyt
  )
c[_]

Re: [sqlite] how to use the sqlite command-line specific commands with the dll version


John Newby wrote:

Hi, I was wondering how I could use the commands specific to the
command-line interface with the dll version.

The commands I am wanting to use are as follows :-

.dump
.import
.output
.read

Any help would be greatly appreciated

Many thanks

John

These are part of the Sqlite3.exe program.  Lift code out of that and 
place it in your application.

Re: [sqlite] retrieving integers and long


Wyan wrote:
I'm calling sqlite3_column_type() to get the type, and, for a number 
(20), it returns SQLITE_INTEGER.  I'm guessing this covers int, long,  
and long long.  Is there any way to distinguish which one it is, so I  
can know to call the right sqlite3_column_() function?  I've got some  
higher level code over this, and I'd like to be able to get the type  
that's actually in the database.


Thanks,

-- wyan
You can also retrieve the declared type, which (thanks to manifest 
typing) is not necessarily the same as the actual stored type.  If you 
are interfacing to another system you are very likely to need the 
declared type and sometimes perform conversions to reverse out what 
Sqlite decided to do based on the actual data.

Re: [sqlite] Reading the same table from two threads

Jay Sprenkle wrote:

On 7/26/06, John Stanton <[EMAIL PROTECTED]> wrote:

>
> Almost a "plug-in" serialization organization. I don't think I've ever
> seen anyone do it that way, but that might be really valuable.
> The embedded software guys would just love it.

We had an ISAM product which worked after that fashion.  It replaced a
server-type implementation which in unfamiliar hands was more trouble
than a bag full of monkeys.

The isam product was barrel of monkeys fun, or the server implementation?

The only thing I can see that would be a problem would be chosing the wrong
model and ending up with corruption because you aren't serialized.

--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com
The server was a constant source of problems because it had to be 
installed on the host machine and integrated with the startup, not 
always easy in an earlier era of departmental computers and junkyard dog 
system administrators.  If it stopped for some reason it generated 
service calls.  An ISAM manager integrated with the the applications was 
much less trouble, and I can see that situation recurring with Sqlite a 
fortiori.

Your point about problems due to the wrong model is very pertinent. 
Perhaps there could be a check compile mode, like compiling in range 
checking in a Pascal program, to intercept at run time gross 
misconfigurations.

[sqlite] Re: Reading the same table from two threads

2006-07-26 Thread A. Pagaltzis

* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2006-07-26 16:40]:
> It has been suggested that I add a mutex to every SQLite
> database connection.

Client code can easily use trivial wrappers to supply such
serialisation for itself, though.

> The downside is the (minimal) additional overhead of acquiring
> and releasing a mutex on each API call.

I don’t see this is a factor. A platform with threads is by
definition not starved for CPU cycles.

> There will also be some changes to the OS-layer backend which
> will break private ports to unusual OSes.

This is a big deal IMO.

An idea worth pursuing might be not to implement such thread
serialisation natively in SQLite itself, but to merely add a way
for client code to supply the address of a callback to be called
called at the appropriate time in each relevant function, so that
API clients can implement serialisation on their own through
whichever means they choose.

If that doesn’t seem like a good idea and you’d prefer a deeper
change that implements native thread serialisation, I’d suggest
to merely earmark that for a time when a backend interface change
is unavoidable anyway, and revisit it then.

Regards,
-- 
Aristotle Pagaltzis //

Re: [sqlite] Reading the same table from two threads

2006-07-26 Thread Nathaniel Smith

On Wed, Jul 26, 2006 at 02:32:58PM +, [EMAIL PROTECTED] wrote:
> It has been suggested that I add a mutex to every SQLite
> database connection.  This would cause access to a database
> connection to automatically serialize even when two or more
> threads try to use that connection at once, thus preventing
> problems such as the above.
> 
> The downside is the (minimal) additional overhead of acquiring 
> and releasing a mutex on each API call.  There will also be 
> some changes to the OS-layer backend which will break 
> private ports to unusual OSes.

I'm -0 on this.  For monotone, we ship sqlite in our source package
(to avoid version skew), but do not otherwise use threads.  I think
this would mean we had to change our build/configury to figure out how
the local thread library worked for sqlite alone, and for no gain to
us (perhaps even a speed loss -- mutexes used by libc already show up
surprisingly high on our profiles).

-- Nathaniel

-- 
The Universe may  /  Be as large as they say
But it wouldn't be missed  /  If it didn't exist.
  -- Piet Hein

Re: [sqlite] Re: retrieving integers and long

2006-07-26 Thread Wyan

Well, obviously, I can always call the int64 function.  But, if I  
have a database library, I want to be able to put the int's in the  
database into C/C++ int's, and whole numbers that are long's in the  
database into C/C++ long's.


I've worked around it, but it's a little unsafe/strange.

On Jul 26, 2006, at 1:20 PM, Igor Tandetnik wrote:


Wyan <[EMAIL PROTECTED]> wrote:

On Jul 26, 2006, at 12:39 PM, Jay Sprenkle wrote:

On 7/26/06, Wyan <[EMAIL PROTECTED]>
wrote:
I'm calling sqlite3_column_type() to get the type, and, for a  
number

(20), it returns SQLITE_INTEGER.  I'm guessing this covers int,
long, and long long.  Is there any way to distinguish which one it
is, so I can know to call the right sqlite3_column_() function?  
I've got some higher level code over this, and I'd like to be able

to get the type that's actually in the database.


You can call any of the column functions. They convert the database
data to the type for each function type. You can even call a text()
function
to retrieve a textual representation of a numeric column.


Right.  But I want to be able to determine the type.


SQLite does not internally distinguish between different sizes of  
integer. You have already determined the type - it's SQLITE_INTEGER.


If you are concerned about losing significant bits, just always use  
sqlite3_column_int64 to retrieve an integer value.


Igor Tandetnik

Re: [sqlite] Reading the same table from two threads

2006-07-26 Thread Dennis Jenkins


[EMAIL PROTECTED] wrote:

It has been suggested that I add a mutex to every SQLite
database connection.  This would cause access to a database
connection to automatically serialize even when two or more
threads try to use that connection at once, thus preventing
problems such as the above.

The downside is the (minimal) additional overhead of acquiring 
and releasing a mutex on each API call.  There will also be 
some changes to the OS-layer backend which will break 
private ports to unusual OSes.


Thoughts?

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

  


IMHO:

Don't do it.  Those of us that want to use threads should create our own 
threaded sqlite wrappers.


I might make several sqlite calls that I don't want interwoven with 
other sqlite calls from other threads.  I rely on my own mutexes.  
Granted, I can continue to use my own mutexes..  I just think that you 
should not add mutex code when:


a) (guessing here) a sizable group of users don't use mutliple threads 
anyway.

b) it would break the private OS ports as you mentioned.
c) the library can alreayd be made thread safe via external logic.

If you try to stop people from shooting themselves in the foot, they'll 
just pull a Dick Chenney on you and shoot someone else.

Re: [sqlite] temp_store=1 performance on Mac OS X vs. Windows

On Wed, 26 Jul 2006 20:56:16 +0200, you wrote:

>Because sqlite has to deal with so many platforms, it shouldn't
>make as few assumptions as possible about the 'correctness' ...

Oops, that should of course read:
Because sqlite has to deal with so many platforms, it should
make as few assumptions as possible about the 'correctness' ...
-- 
  (  Kees Nuyt
  )
c[_]

[sqlite] Re: retrieving integers and long

2006-07-26 Thread Igor Tandetnik


Wyan <[EMAIL PROTECTED]> wrote:

On Jul 26, 2006, at 12:39 PM, Jay Sprenkle wrote:

On 7/26/06, Wyan <[EMAIL PROTECTED]>
wrote:

I'm calling sqlite3_column_type() to get the type, and, for a number
(20), it returns SQLITE_INTEGER.  I'm guessing this covers int,
long, and long long.  Is there any way to distinguish which one it
is, so I can know to call the right sqlite3_column_() function? I've 
got some higher level code over this, and I'd like to be able

to get the type that's actually in the database.


You can call any of the column functions. They convert the database
data to the type for each function type. You can even call a text()
function
to retrieve a textual representation of a numeric column.


Right.  But I want to be able to determine the type.


SQLite does not internally distinguish between different sizes of 
integer. You have already determined the type - it's SQLITE_INTEGER.


If you are concerned about losing significant bits, just always use 
sqlite3_column_int64 to retrieve an integer value.


Igor Tandetnik

Re: [sqlite] retrieving integers and long

2006-07-26 Thread Wyan


Right.  But I want to be able to determine the type.

On Jul 26, 2006, at 12:39 PM, Jay Sprenkle wrote:


On 7/26/06, Wyan <[EMAIL PROTECTED]> wrote:

I'm calling sqlite3_column_type() to get the type, and, for a number
(20), it returns SQLITE_INTEGER.  I'm guessing this covers int, long,
and long long.  Is there any way to distinguish which one it is, so I
can know to call the right sqlite3_column_() function?  I've got some
higher level code over this, and I'd like to be able to get the type
that's actually in the database.


You can call any of the column functions. They convert the database
data to the type for each function type. You can even call a text()  
function

to retrieve a textual representation of a numeric column.

Re: [sqlite] how to use the sqlite command-line specific commands with the dll version

2006-07-26 Thread Nuno Lucas


On 7/26/06, John Newby <[EMAIL PROTECTED]> wrote:

Hi, I was wondering how I could use the commands specific to the
command-line interface with the dll version.


You can't, but see below.


The commands I am wanting to use are as follows :-

.dump
.import
.output
.read


You can look into the shell.c source and just copy the code you want
into your own functions.

The sqlite shell is all implemented in this file, so it's not too hard
to look how it's done there and use in your code.


Regards,
~Nuno Lucas


Any help would be greatly appreciated

Many thanks

John

Re: [sqlite] "SQL Error: near 'Table': Syntax error"


Hi Richard, I noticed 'ADD' and 'COLUMN' aren't allowed but aren't on the
list but I haven't came accross any others at the moment.

I have taken your advice and am now putting all identifiers between double
quotes.

Many thanks for your help

John

On 26/07/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


Dennis Cote <[EMAIL PROTECTED]> wrote:
> John Newby wrote:
> >
> > Is there any other names I need to look out for other than the
> > "sqlite_" and
> > "table" that SQLite doesn't like as being a table name that anyone
> > knows of?
> >
> John,
>
> All keywords need to be quoted to use them as identifiers. There is a
> comprehensive list of keywords at
http://www.sqlite.org/lang_keywords.html
>
> I would also suggest that you stick with the SQL standard method of
> quoting identifiers using double quotes (rather than the other
> extensions that SQLite accepts for compatibility with other non-standard
> database systems).
>

The keyword list has not been scrubbed lately and might contain
omissions.  Also, new keywords are added from time to time.
To be safe, it is best to quote all table and column names, or
else use a prefix on every name that is unlikely to ever be used
as a keyword.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

[sqlite] how to use the sqlite command-line specific commands with the dll version


Hi, I was wondering how I could use the commands specific to the
command-line interface with the dll version.

The commands I am wanting to use are as follows :-

.dump
.import
.output
.read

Any help would be greatly appreciated

Many thanks

John

Re: [sqlite] retrieving integers and long


On 7/26/06, Wyan <[EMAIL PROTECTED]> wrote:

I'm calling sqlite3_column_type() to get the type, and, for a number
(20), it returns SQLITE_INTEGER.  I'm guessing this covers int, long,
and long long.  Is there any way to distinguish which one it is, so I
can know to call the right sqlite3_column_() function?  I've got some
higher level code over this, and I'd like to be able to get the type
that's actually in the database.


You can call any of the column functions. They convert the database
data to the type for each function type. You can even call a text() function
to retrieve a textual representation of a numeric column.

[sqlite] retrieving integers and long

2006-07-26 Thread Wyan

I'm calling sqlite3_column_type() to get the type, and, for a number 
(20), it returns SQLITE_INTEGER.  I'm guessing this covers int, long,  
and long long.  Is there any way to distinguish which one it is, so I  
can know to call the right sqlite3_column_() function?  I've got some  
higher level code over this, and I'd like to be able to get the type  
that's actually in the database.


Thanks,

-- wyan

Re: [sqlite] Re: UTF8


OK! I understand Igor!

Thanks 4 your help!

On 7/26/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote:


Cesar David Rodas Maldonado
<[EMAIL PROTECTED]> wrote:
> I wanted to ask how can i know if a given text is UTF8 or ISO-8859-1?

You can't really. You need to have some metadata - some means outside
the text itself to let you know what encoding the text is in.

E.g. a combination of two bytes C3 A5 can be interpreted as two
characters Ã¥ in ISO-8859-1, but it's also a valid UTF8 sequence
representing Unicode character U+00E5  å . There's no way to tell from
text alone.

Igor Tandetnik

Re: [sqlite] temp_store=1 performance on Mac OS X vs. Windows

On Tue, 25 Jul 2006 23:13:31 +0200, you wrote:

>Hi,
>
>we just found that when using file-based temporary storage (compile  
>time macro definition TEMP_STORE=1) vs. memory-based temporary  
>storage (TEMP_STORE=2), on Mac OS X, the performance almost doesn't  
>degrade at all, whereas on Windows, we're getting a huge performance  
>penalty when using file-based temporary storage.
>
>We are compiling sqlite 3.3.6 ourselves using pretty much the stock  
>compile time options, except for TEMP_STORE and THREAD_SAFE=1.
>We also found that apparently, database performance with TEMP_STORE=1  
>is especially slow when running on Windows XP (SP2).
>
>OS Versions where Mac OS X 10.4.4 and Windows XP (SP2).
>Both machines were equipped with > 1 GB of RAM, but since we're using  
>file temp storage, memory usage is not really a limit here. Hard disk  
>performance should be about equal on both machines.

You don't mention the settings / optimisations of the
filesystems, nor the amount of memory the OS allows the
filesystem for caching. At least in Windows, server and desktop
versions have different optimisations.
In my opinion this might make a significant difference.
-- 
  (  Kees Nuyt
  )
c[_]

[sqlite] Re: UTF8

2006-07-26 Thread Igor Tandetnik


Cesar David Rodas Maldonado
<[EMAIL PROTECTED]> wrote:

I wanted to ask how can i know if a given text is UTF8 or ISO-8859-1?


You can't really. You need to have some metadata - some means outside 
the text itself to let you know what encoding the text is in.


E.g. a combination of two bytes C3 A5 can be interpreted as two 
characters Ã¥ in ISO-8859-1, but it's also a valid UTF8 sequence 
representing Unicode character U+00E5  å . There's no way to tell from 
text alone.


Igor Tandetnik

Re: [sqlite] temp_store=1 performance on Mac OS X vs. Windows

On Wed, 26 Jul 2006 15:23:04 +0100 (BST), you wrote:

>Looking at this reminded me. What is the point of deleting the rollback 
>log? Why not just truncate it when committing a transaction, and deleting 
>it only when closing the database?

My guess: 
Because sqlite has to deal with so many platforms, it shouldn't
make as few assumptions as possible about the 'correctness' of
any filesystem. Deleting the journal file would have a bigger
chance to actually cause a directory update on harddisk than a
mere reset of filesize. Mind powerfailures and system panics.

To be sure, in your construction a simple truncate wouldn't be
enough, additionaly the contents of the journal should be
invalidated in some way, with a guaranteed flush to the
filesystem.

As the journal is our last resort for database consistency (thus
the ACID nature of sqlite), it is better to be safe than sorry.

>The benefits could be:
>- Improved performance:
> - No delete of the rollback log to cause synchronous IO on the
>   directory.

There is the consistency culprit.

> - Only need to create the rollback log when it doesn't exist. Else,
>   just the existing empty one.
>
>- Would play better with Virus scanners on Windows. No deleting of the
>   rollback log would cause no file locked errors until we're closing the
>   database.

IMHO we'd better make the virusscanners behave. 

>So long as the rollback code handled the case of the rollback journal not 
>being present, this should be backward compatible with existing versions.

True.

>Christian
-- 
  (  Kees Nuyt
  )
c[_]

Re: [sqlite] UTF8


Thanks peter! :D

On 7/26/06, Peter Cunderlik <[EMAIL PROTECTED]> wrote:


> I wanted to ask how can i know if a given text is UTF8 or ISO-8859-1?

If you need conversions, the simplest would be to do it manually using
look-up tables. AFAIK none of the Latin-1 characters take more than 2
bytes in UTF-8, so having 2*256 bytes long table won't hurt.

If you want to decode special Unicode things like right-to-left stuff,
I'd recommend some serious library, such as ICU (icu.sourceforge.net).

If you want to detect the encoding/codepage, I don't think it can be
done in general, unless you know what text to expect. I might be
wrong.

Peter

Re: [sqlite] UTF8

2006-07-26 Thread Peter Cunderlik


I wanted to ask how can i know if a given text is UTF8 or ISO-8859-1?


If you need conversions, the simplest would be to do it manually using
look-up tables. AFAIK none of the Latin-1 characters take more than 2
bytes in UTF-8, so having 2*256 bytes long table won't hurt.

If you want to decode special Unicode things like right-to-left stuff,
I'd recommend some serious library, such as ICU (icu.sourceforge.net).

If you want to detect the encoding/codepage, I don't think it can be
done in general, unless you know what text to expect. I might be
wrong.

Peter

Re: [sqlite] Reading the same table from two threads

On 7/26/06, John Stanton <[EMAIL PROTECTED]> wrote:

>
> Almost a "plug-in" serialization organization. I don't think I've ever
> seen anyone do it that way, but that might be really valuable.
> The embedded software guys would just love it.

We had an ISAM product which worked after that fashion.  It replaced a
server-type implementation which in unfamiliar hands was more trouble
than a bag full of monkeys.

The isam product was barrel of monkeys fun, or the server implementation?

The only thing I can see that would be a problem would be chosing the wrong
model and ending up with corruption because you aren't serialized.

--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

Re: [sqlite] Reading the same table from two threads

Jay Sprenkle wrote:

On 7/26/06, John Stanton <[EMAIL PROTECTED]> wrote:

Jay Sprenkle wrote:
>> > Will the mutex replace file locking for database access control?
>> >
>>
>> No.  A mutex only works within a single process.
>
>
> I was thinking it might make a good compile time option.
> If you aren't going to access the database from multiple machines then
> the mutex could replace file locking as an access control mechanism.
> My guess was it would be much faster,
> but would only work correctly in that special case.

If the mutex capability were layered then there could be an init call
which would let the user choose a thread-type mutex for multi-threaded
single process, a semaphore-type kernel structure for multiple processes
and some form of lock manager (as I remember using on VMS) for a network.

Almost a "plug-in" serialization organization. I don't think I've ever
seen anyone do it that way, but that might be really valuable.
The embedded software guys would just love it.

We had an ISAM product which worked after that fashion.  It replaced a 
server-type implementation which in unfamiliar hands was more trouble 
than a bag full of monkeys.

Re: [sqlite] UTF8

I'm sorry! English is not my first language!! :D

I wanted to ask how can i know if a given text is UTF8 or ISO-8859-1?

Thanks and please forgive me for my english! :D

On 7/26/06, Cory Nelson <[EMAIL PROTECTED]> wrote:

ASCII is completely valid UTF-8, so no conversion is necessary.

On 7/26/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:
> How can i know if a given text is UTF8 or ascii? and how can i convert
> between ascii to UTF8?
>
>

--
Cory Nelson
http://www.int64.org

Re: [sqlite] UTF8

2006-07-26 Thread Cory Nelson


ASCII is completely valid UTF-8, so no conversion is necessary.

On 7/26/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:

How can i know if a given text is UTF8 or ascii? and how can i convert
between ascii to UTF8?





--
Cory Nelson
http://www.int64.org

[sqlite] Re: UTF8

2006-07-26 Thread Igor Tandetnik


Cesar David Rodas Maldonado
<[EMAIL PROTECTED]> wrote:

How can i know if a given text is UTF8 or ascii? and how can i convert
between ascii to UTF8?


If by ASCII you mean a set of 128 characters representable in 7 bits, 
then UTF8 is a superset of ASCII. Any ASCII text is also a UTF8 text, 
and any UTF8 text consisting entirely of 7-bit characters is also ASCII 
text. No conversion required.


Igor Tandetnik

[sqlite] UTF8


How can i know if a given text is UTF8 or ascii? and how can i convert
between ascii to UTF8?

Re: [sqlite] "SQL Error: near 'Table': Syntax error"

Dennis Cote <[EMAIL PROTECTED]> wrote:
> John Newby wrote:
> >
> > Is there any other names I need to look out for other than the 
> > "sqlite_" and
> > "table" that SQLite doesn't like as being a table name that anyone 
> > knows of?
> >
> John,
> 
> All keywords need to be quoted to use them as identifiers. There is a 
> comprehensive list of keywords at http://www.sqlite.org/lang_keywords.html
> 
> I would also suggest that you stick with the SQL standard method of 
> quoting identifiers using double quotes (rather than the other 
> extensions that SQLite accepts for compatibility with other non-standard 
> database systems).
> 

The keyword list has not been scrubbed lately and might contain
omissions.  Also, new keywords are added from time to time.
To be safe, it is best to quote all table and column names, or
else use a prefix on every name that is unlikely to ever be used
as a keyword.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Reading the same table from two threads

On 7/26/06, John Stanton <[EMAIL PROTECTED]> wrote:

Jay Sprenkle wrote:
>> > Will the mutex replace file locking for database access control?
>> >
>>
>> No.  A mutex only works within a single process.
>
>
> I was thinking it might make a good compile time option.
> If you aren't going to access the database from multiple machines then
> the mutex could replace file locking as an access control mechanism.
> My guess was it would be much faster,
> but would only work correctly in that special case.

If the mutex capability were layered then there could be an init call
which would let the user choose a thread-type mutex for multi-threaded
single process, a semaphore-type kernel structure for multiple processes
and some form of lock manager (as I remember using on VMS) for a network.

Almost a "plug-in" serialization organization. I don't think I've ever
seen anyone do it that way, but that might be really valuable.
The embedded software guys would just love it.

Re: [sqlite] Reading the same table from two threads

Jay Sprenkle wrote:

> Will the mutex replace file locking for database access control?
>

No.  A mutex only works within a single process.

I was thinking it might make a good compile time option.
If you aren't going to access the database from multiple machines then
the mutex could replace file locking as an access control mechanism.
My guess was it would be much faster,
but would only work correctly in that special case.

If the mutex capability were layered then there could be an init call 
which would let the user choose a thread-type mutex for multi-threaded 
single process, a semaphore-type kernel structure for multiple processes 
and some form of lock manager (as I remember using on VMS) for a network.

Re: [sqlite] Reading the same table from two threads


[EMAIL PROTECTED] wrote:

"Rob Richardson" <[EMAIL PROTECTED]> wrote:


I found the spot where I was telling the two threads to use the same
database pointer instead of running on separate ones.  Once I fixed
that, it works.




It has been suggested that I add a mutex to every SQLite
database connection.  This would cause access to a database
connection to automatically serialize even when two or more
threads try to use that connection at once, thus preventing
problems such as the above.

The downside is the (minimal) additional overhead of acquiring 
and releasing a mutex on each API call.  There will also be 
some changes to the OS-layer backend which will break 
private ports to unusual OSes.


Thoughts?

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

I would regard that as detrimental for a library which is LITE and 
better left up to the application designer.  However a set of 
synchronizing API calls using a mutex or similar could be a nice 
addition and remove what is a persistent, nagging issue for many users, 
serialization from their own code.


A set of layered API calls with inbuilt access arbitration which using 
Sqlite look like access to a server would be intuitive to many potential 
users.


If the mutex path were taken then the file locks could be removed 
entirely, with the benefit of decoupling Sqlite from file locking 
implementation woes and possible performance improvements but at the 
cost of implementing some form of lock manager for remote file systems 
and breaking backwards compatibility.

Re: [sqlite] "SQL Error: near 'Table': Syntax error"

Hi Dennis, thanks for this, I've just spent the last 30 mins typing in every
word I could think of that might have come up with a conflict, it was
nowhere near as big as the list in the link.

Thanks for the tips.

John

On 26/07/06, Dennis Cote <[EMAIL PROTECTED]> wrote:

John Newby wrote:
>
> Is there any other names I need to look out for other than the
> "sqlite_" and
> "table" that SQLite doesn't like as being a table name that anyone
> knows of?
>
John,

All keywords need to be quoted to use them as identifiers. There is a
comprehensive list of keywords at http://www.sqlite.org/lang_keywords.html

I would also suggest that you stick with the SQL standard method of
quoting identifiers using double quotes (rather than the other
extensions that SQLite accepts for compatibility with other non-standard
database systems).

HTH
Dennis Cote

Re: [sqlite] "SQL Error: near 'Table': Syntax error"

2006-07-26 Thread Dennis Cote


John Newby wrote:


Is there any other names I need to look out for other than the 
"sqlite_" and
"table" that SQLite doesn't like as being a table name that anyone 
knows of?



John,

All keywords need to be quoted to use them as identifiers. There is a 
comprehensive list of keywords at http://www.sqlite.org/lang_keywords.html


I would also suggest that you stick with the SQL standard method of 
quoting identifiers using double quotes (rather than the other 
extensions that SQLite accepts for compatibility with other non-standard 
database systems).


HTH
Dennis Cote

Re: [sqlite] SQLiteSpy vs. SQLite.dll speed comparison

Eduardo wrote:

At 01:38 26/07/2006, you wrote:

Nuno Lucas wrote:

On 7/22/06, Eduardo <[EMAIL PROTECTED]> wrote:

Sorry, but i must disagree. He uses VC6, a compiler from

you use a compiler from pre-altivec era or non altivec optimized
libraries (including libc), your code will be a lot slower.

While I agree with you on the general, I must note that would be for a
CPU-bound application. Most sqlite applications tend to be IO-bound
(unless you have enough memory and is all cached), so the differences
will not manifest so much.

I usually have enough memory ;) and must agree with your note, I/O to
disk or network is a real bottleneck. But using a better compiler which
make faster code ( or better, do the same work in less CPU cycles )
allows the principal application or other threads work faster also. So
this affect not only sqlite, but the whole app.

As an aside, there are applications that have better performance with
-Os (optimized for size) than with any -O, because can incur in dramatically less cache misses (which is
several times slower than a cache hit and can also imply scheduling
decisions against the process/thread).

Regards,
~Nuno Lucas

Nuno has a very valid point that we certainly observe. Programs which
run all the time, expecially interpreters like the Sqlite engine, run
very much better when they are small so that the regularly used
components fit in the processor cache. Having compact data structures
can be a big help in keeping the working set cached.

Well, i never tried to compile optimizing size, it's very curious that
doing so we can get faster apps. Must try it.

Alien.org contacted...waiting for Ripley
Your processor runs much faster than your memory, so when it has to
access from memory it has to wait. That is why it has cache, to cut
back on waits for frequently accessed items. Minimizing checkerboarding
in your programs makes for faster execution.

Re: [sqlite] Reading the same table from two threads

2006-07-26 Thread Peter Cunderlik


On 7/26/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


It has been suggested that I add a mutex to every SQLite
database connection.  This would cause access to a database
connection to automatically serialize even when two or more
threads try to use that connection at once, thus preventing
problems such as the above.


How about having mutex_(un)lock callbacks that the library would call?
It'd be the app's choice how (or if at all) to implement the locking.
It'd be great if this feature could be switched on/off at compile
time, though.

And let's say it'd be allowed to set the callbacks only before opening
the first connection in the process.

Peter

Re: [sqlite] "SQL Error: near 'Table': Syntax error"


John Newby wrote:

Hi, I've took the following quote from the SQLite website stating that
"Tables names that begin with "*sqlite_*" are reserved for use by the
engine"  and if you try to create a table beginning with this it lets you
know about it, but what about tables called "TABLE", this throws back an
error stating "SQL Error: near 'Table': Syntax error"

Does anyone know any reason why SQLite doesnt like tables called "Table" or
is this a standard SQL thing?

Many thanks

John


TABLE is a reserved word.

Re: [sqlite] Reading the same table from two threads

2006-07-26 Thread Martin Jenkins


Jay Sprenkle wrote:

Why are two threads reading the same data?


Last != previous?

I read it as penultimate vs pre-penultimate data.

Martin

Re: [sqlite] Reading the same table from two threads


> Will the mutex replace file locking for database access control?
>

No.  A mutex only works within a single process.


I was thinking it might make a good compile time option.
If you aren't going to access the database from multiple machines then
the mutex could replace file locking as an access control mechanism.
My guess was it would be much faster,
but would only work correctly in that special case.

Re: [sqlite] "SQL Error: near 'Table': Syntax error"


Hi Jonathan, yes you are right, thanks for this, I'll just put a check for
them all as you never know what a user is going to do its better to try and
fix it beforehand.

thanks again

John

On 26/07/06, Jonathan Ballet <[EMAIL PROTECTED]> wrote:


John Newby wrote:
> Hi Jonathan, Derrel, thanks for the info.
>
> I just wanted to know as I am creating an application that interacts
with
> the SQLite.dll and if a user was to try to create a table beginning with
> "sqlite_" the user would get the very detailed erro message, but if for
> some
> reason the user wanted to call the table "Table" they would just get a
> simple SQL syntax error and may become stuck in my application.
>
> I'll just put some check on the input from the user and if on the off
> chance
> they decide to call it table, it will change it to "Table".
>
> Is there any other names I need to look out for other than the "sqlite_"
> and
> "table" that SQLite doesn't like as being a table name that anyone knows
> of?
>
> Thanks again
>
> John
>

I think that all SQL commands or operators (create, alter, update, where,
from,
select, in, as, etc...) cannot be used.

Just check it by yourself ;)

Jonathan Ballet

[sqlite] Please un-subscribe

2006-07-26 Thread prabhu kumaravelu



Please un-subscribe from sqlite please me know how to do it.
Dont send me email

_
Who will win Bollywoods most coveted IIFA awards? You decide! Cast your 
vote! http://server1.msn.co.in/sp06/IIFA2006/static/weekend.asp

Re: [sqlite] "SQL Error: near 'Table': Syntax error"

2006-07-26 Thread Jonathan Ballet

John Newby wrote:
> Hi Jonathan, Derrel, thanks for the info.
> 
> I just wanted to know as I am creating an application that interacts with
> the SQLite.dll and if a user was to try to create a table beginning with
> "sqlite_" the user would get the very detailed erro message, but if for
> some
> reason the user wanted to call the table "Table" they would just get a
> simple SQL syntax error and may become stuck in my application.
> 
> I'll just put some check on the input from the user and if on the off
> chance
> they decide to call it table, it will change it to "Table".
> 
> Is there any other names I need to look out for other than the "sqlite_"
> and
> "table" that SQLite doesn't like as being a table name that anyone knows
> of?
> 
> Thanks again
> 
> John
> 

I think that all SQL commands or operators (create, alter, update, where, from,
select, in, as, etc...) cannot be used.

Just check it by yourself ;)

Jonathan Ballet

Re: [sqlite] Reading the same table from two threads

"Jay Sprenkle" <[EMAIL PROTECTED]> wrote:
> On 7/26/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > "Rob Richardson" <[EMAIL PROTECTED]> wrote:
> > > I found the spot where I was telling the two threads to use the same
> > > database pointer instead of running on separate ones.  Once I fixed
> > > that, it works.
> > >
> >
> > It has been suggested that I add a mutex to every SQLite
> > database connection.  This would cause access to a database
> > connection to automatically serialize even when two or more
> > threads try to use that connection at once, thus preventing
> > problems such as the above.
> 
> Will the mutex replace file locking for database access control?
> 

No.  A mutex only works within a single process.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] "SQL Error: near 'Table': Syntax error"

Hi Jonathan, Derrel, thanks for the info.

I just wanted to know as I am creating an application that interacts with
the SQLite.dll and if a user was to try to create a table beginning with
"sqlite_" the user would get the very detailed erro message, but if for some
reason the user wanted to call the table "Table" they would just get a
simple SQL syntax error and may become stuck in my application.

I'll just put some check on the input from the user and if on the off chance
they decide to call it table, it will change it to "Table".

Is there any other names I need to look out for other than the "sqlite_" and
"table" that SQLite doesn't like as being a table name that anyone knows of?

Thanks again

John

On 26/07/06, [EMAIL PROTECTED] <
[EMAIL PROTECTED]> wrote:

"John Newby" <[EMAIL PROTECTED]> writes:

> Does anyone know any reason why SQLite doesnt like tables called "Table"
or
> is this a standard SQL thing?

It's a reserved word, so if you really, Really, REALLY want to create a
table
of that name (you're making it confusing to read, so you really
shouldn't),
you can do it using either quotes or square brackets around the table
name, as
shown here:

SQLite version 3.2.1
Enter ".help" for instructions
sqlite> create table "TABLE" (i integer);
sqlite> .schema
CREATE TABLE "TABLE" (i integer);
sqlite> .mode line
sqlite> select * from sqlite_master;
type = table
name = TABLE
tbl_name = TABLE
rootpage = 2
 sql = CREATE TABLE "TABLE" (i integer)
sqlite> drop table "table";
sqlite> create table [TABLE] (i integer);
sqlite> .schema
CREATE TABLE [TABLE] (i integer);
sqlite> select * from sqlite_master;
type = table
name = TABLE
tbl_name = TABLE
rootpage = 2
 sql = CREATE TABLE [TABLE] (i integer)
sqlite>

Derrell

Re: [sqlite] Reading the same table from two threads

On 7/26/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

"Rob Richardson" <[EMAIL PROTECTED]> wrote:
> I found the spot where I was telling the two threads to use the same
> database pointer instead of running on separate ones.  Once I fixed
> that, it works.
>

It has been suggested that I add a mutex to every SQLite
database connection.  This would cause access to a database
connection to automatically serialize even when two or more
threads try to use that connection at once, thus preventing
problems such as the above.

Will the mutex replace file locking for database access control?

Re: [sqlite] Reading the same table from two threads

"Rob Richardson" <[EMAIL PROTECTED]> wrote:
> I found the spot where I was telling the two threads to use the same
> database pointer instead of running on separate ones.  Once I fixed
> that, it works.
> 

It has been suggested that I add a mutex to every SQLite
database connection.  This would cause access to a database
connection to automatically serialize even when two or more
threads try to use that connection at once, thus preventing
problems such as the above.

The downside is the (minimal) additional overhead of acquiring 
and releasing a mutex on each API call.  There will also be 
some changes to the OS-layer backend which will break 
private ports to unusual OSes.

Thoughts?

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

RE: [sqlite] Reading the same table from two threads

2006-07-26 Thread Rob Richardson

I found the spot where I was telling the two threads to use the same
database pointer instead of running on separate ones.  Once I fixed
that, it works.

Thanks for your help.

Rob Richardson
Rad-Con, Inc.

-Original Message-
From: Jay Sprenkle [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, July 26, 2006 9:49 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Reading the same table from two threads

On 7/26/06, Rob Richardson <[EMAIL PROTECTED]> wrote:
> Jay,
>
> After the first thread, a graph will be displayed and the user can
start
> doing things on it while the next batch of data is being loaded.

Ah.

"Many readers" should work fine.
An access violation sounds like a bad pointer or a compile problem.
Check for the standard stuff:
* more than one DLL/loadable lib and you're not loading the one you
thought you were
* correct compile settings for sqlite
* bugs ( "it compiles, therefore it's perfect!" )
* compiler not "service packed"

Re: [sqlite] temp_store=1 performance on Mac OS X vs. Windows

2006-07-26 Thread Christian Smith


[EMAIL PROTECTED] uttered:


Jens Miltner <[EMAIL PROTECTED]> wrote:



OTOH, our customers might also have antivirus software installed, so
this still would not be a solution :(
Does anybody have advice on how to make sqlite work smoothly with
antivirus software [on Windows]? (Probably depends on the antivirus
software?)



Virus scanners will slow things down and cause problems.
This is the fault of the virus scanners and SQLite cannot
really do much about it.

But there have been *some* changes to try to work around
the virus scanner problem.  See, for example,
http://www.sqlite.org/cvstrac/chngview?cn=3200



Looking at this reminded me. What is the point of deleting the rollback 
log? Why not just truncate it when committing a transaction, and deleting 
it only when closing the database?


The benefits could be:
- Improved performance:
- No delete of the rollback log to cause synchronous IO on the
  directory.
- Only need to create the rollback log when it doesn't exist. Else,
  just the existing empty one.
- Would play better with Virus scanners on Windows. No deleting of the
  rollback log would cause no file locked errors until we're closing the
  database.

So long as the rollback code handled the case of the rollback journal not 
being present, this should be backward compatible with existing versions.



Christian

--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

Re: [sqlite] temp_store=1 performance on Mac OS X vs. Windows



Am 26.07.2006 um 15:41 schrieb [EMAIL PROTECTED]:


Jens Miltner <[EMAIL PROTECTED]> wrote:



OTOH, our customers might also have antivirus software installed, so
this still would not be a solution :(
Does anybody have advice on how to make sqlite work smoothly with
antivirus software [on Windows]? (Probably depends on the antivirus
software?)



Virus scanners will slow things down and cause problems.
This is the fault of the virus scanners and SQLite cannot
really do much about it.


Yes, I understand this and I don't blame sqlite at all if virus  
scanners slow down the machine...
Just thought there might be some hints how to optimize  
interoperatibility...





But there have been *some* changes to try to work around
the virus scanner problem.  See, for example,
http://www.sqlite.org/cvstrac/chngview?cn=3200


But this change is in 3.3.6, right? We're running 3.3.6...

Thanks,

Re: [sqlite] Reading the same table from two threads


On 7/26/06, Rob Richardson <[EMAIL PROTECTED]> wrote:

Jay,

After the first thread, a graph will be displayed and the user can start
doing things on it while the next batch of data is being loaded.


Ah.

"Many readers" should work fine.
An access violation sounds like a bad pointer or a compile problem.
Check for the standard stuff:
* more than one DLL/loadable lib and you're not loading the one you
thought you were
* correct compile settings for sqlite
* bugs ( "it compiles, therefore it's perfect!" )
* compiler not "service packed"

RE: [sqlite] unsubscribe me please

2006-07-26 Thread prabhu kumaravelu



unsubscribe me please



From: "Shi, Chunde CTR DISA PEO-C2C" <[EMAIL PROTECTED]>
Reply-To: sqlite-users@sqlite.org
To: 
Subject: [sqlite] unsubscribe me please RE: [sqlite] sqlite3_interrupt() 
and threads

Date: Tue, 25 Jul 2006 19:17:01 -0400



Chunde Shi

[EMAIL PROTECTED]
703 882 1466
703 882 2325



From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tue 7/25/2006 7:06 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] sqlite3_interrupt() and threads



Michael Scharf <[EMAIL PROTECTED]> wrote:
> Rob,
> > I notice in the documentation that the sqlite3_progress_handler() 
method

> > is marked "experimental".  Is that significant?
>
> No idea, that's a question Richard Hipp may answer..
>

I need to remove the experimental marking.  Perhaps
somebody could create a ticket to remind me ;-)

While you are at it, consider creating an enhancement
ticket to allow sqlite3_interrupt to be called from
a different thread.
--
D. Richard Hipp   <[EMAIL PROTECTED]>



_
Best IT jobs on naukri.com 
http://www.naukri.com/tieups/tieups.php?othersrcp=3246

Re: [sqlite] temp_store=1 performance on Mac OS X vs. Windows

Jens Miltner <[EMAIL PROTECTED]> wrote:
>> 
> OTOH, our customers might also have antivirus software installed, so  
> this still would not be a solution :(
> Does anybody have advice on how to make sqlite work smoothly with  
> antivirus software [on Windows]? (Probably depends on the antivirus  
> software?)
> 

Virus scanners will slow things down and cause problems.
This is the fault of the virus scanners and SQLite cannot
really do much about it.

But there have been *some* changes to try to work around
the virus scanner problem.  See, for example,
http://www.sqlite.org/cvstrac/chngview?cn=3200

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

RE: [sqlite] Reading the same table from two threads

2006-07-26 Thread Rob Richardson

Jay,

After the first thread, a graph will be displayed and the user can start
doing things on it while the next batch of data is being loaded.

Rob

-Original Message-
From: Jay Sprenkle [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, July 26, 2006 9:14 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Reading the same table from two threads

On 7/26/06, Rob Richardson <[EMAIL PROTECTED]> wrote:
> few milliseconds after the first one.  I am getting an access
violation
> inside sqlite3.dll when the second thread calls sqlite3_prepare().  Am
I
> trying to do something I shouldn't?

Bugs aside, that should work. It doesn't sound like a very efficient
design
though. Why are two threads reading the same data?

--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

Re: [sqlite] Reading the same table from two threads



Am 26.07.2006 um 14:43 schrieb Rob Richardson:


Greetings!



I am starting two threads in quick succession that read the same  
table.

Each thread calls sqlite3_open(), so they are using separate database
pointers.  The first thread asks for records recorded in the last 24
hours.  The second thread asks for records from the same table  
recorded
in the previous 24 hours.   The second thread is probably started  
only a
few milliseconds after the first one.  I am getting an access  
violation
inside sqlite3.dll when the second thread calls sqlite3_prepare().   
Am I

trying to do something I shouldn't?


Are you sure you built sqlite with THREAD_SAFE macro defined?

Re: [sqlite] sqlite3_interrupt() and threads



Am 26.07.2006 um 01:06 schrieb [EMAIL PROTECTED]:


Michael Scharf <[EMAIL PROTECTED]> wrote:

Rob,
I notice in the documentation that the sqlite3_progress_handler()  
method

is marked "experimental".  Is that significant?


No idea, that's a question Richard Hipp may answer..



I need to remove the experimental marking.  Perhaps
somebody could create a ticket to remind me ;-)

While you are at it, consider creating an enhancement
ticket to allow sqlite3_interrupt to be called from
a different thread.


Tickets #1897 and #1898.
I saw that you already committed code to make sqlite3_interrupt  
thread-safe, but created a ticket anyway to make sure it's tracked.

Re: [sqlite] SQLiteSpy vs. SQLite.dll speed comparison


FYI

ACOVEA (Analysis of Compiler Options via Evolutionary Algorithm)
implements a genetic algorithm to find the "best" options for
compiling programs with the GNU Compiler Collection (GCC) C and C++
compilers. "Best", in this context, is defined as those options that
produce the fastest executable program from a given source code.
Acovea is a C++ framework that can be extended to test other
programming languages and non-GCC compilers.

http://www.coyotegulch.com/products/acovea/index.html

Re: [sqlite] SQLiteSpy vs. SQLite.dll speed comparison

2006-07-26 Thread Eduardo

At 01:38 26/07/2006, you wrote:

Nuno Lucas wrote:

On 7/22/06, Eduardo <[EMAIL PROTECTED]> wrote:

Sorry, but i must disagree. He uses VC6, a compiler from

you use a compiler from pre-altivec era or non altivec optimized
libraries (including libc), your code will be a lot slower.

I usually have enough memory ;) and must agree with your note, I/O to
disk or network is a real bottleneck. But using a better compiler
which make faster code ( or better, do the same work in less CPU
cycles ) allows the principal application or other threads work
faster also. So this affect not only sqlite, but the whole app.

Regards,
~Nuno Lucas

Nuno has a very valid point that we certainly observe. Programs
which run all the time, expecially interpreters like the Sqlite
engine, run very much better when they are small so that the
regularly used components fit in the processor cache. Having
compact data structures can be a big help in keeping the working set cached.

Well, i never tried to compile optimizing size, it's very curious
that doing so we can get faster apps. Must try it.

Alien.org contacted...waiting for Ripley

Re: [sqlite] Reading the same table from two threads


On 7/26/06, Rob Richardson <[EMAIL PROTECTED]> wrote:

few milliseconds after the first one.  I am getting an access violation
inside sqlite3.dll when the second thread calls sqlite3_prepare().  Am I
trying to do something I shouldn't?


Bugs aside, that should work. It doesn't sound like a very efficient design
though. Why are two threads reading the same data?


--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

Re: [sqlite] temp_store=1 performance on Mac OS X vs. Windows



Am 26.07.2006 um 04:16 schrieb Joe Wilson:


According to the documentation in http://sqlite.org/pragma.html
for PRAGMA temp_store, it seems that only the TEMP_STORE macro
values of 0 and 3 unconditionally guarantee temporary storage
to be file and memory respectively. Otherwise the runtime
"PRAGMA temp_store=FILE|MEMORY" plays a role. (Am I the only
person to find the use of these two variables to determine
temp store to be confusing?)


We don't use PRAGMA temp_store in our code and the performance  
changes when we change the default temp_store via the TEMP_STORE  
macro when compiling sqlite.




In order to isolate some variables, what results do you get
with the default ./configure && make on both platforms?
(default is temp store = file, non-threadsafe).


We explicitely define the TEMP_STORE macro in the build settings  
(Xcode on Mac, Visual Studio .NET / VC 7 on Windows), so this  
shouldn't be an issue.




What compiler are you using on Windows?
VC6, 7, Cygwin, MinGW, Intel, other?

And your temp_store_directory isn't set to something crazy like
a floppy drive, is it?  ;-)


Doh! - that's it ;-)   no, just kidding - apparently sqlite picks a  
temp directory on the boot drive by default




Are you running Windows anti-virus software?


That might actually be the culprit - I was under the impression that  
we tested without, but when I just reconfirmed with my Windows  
fellows, they admitted they do have antivirus software installed   |-:
Guess I'll have to make sure we run the test again without antivirus  
software installed.


OTOH, our customers might also have antivirus software installed, so  
this still would not be a solution :(
Does anybody have advice on how to make sqlite work smoothly with  
antivirus software [on Windows]? (Probably depends on the antivirus  
software?)


Thanks




--- Jens Miltner <[EMAIL PROTECTED]> wrote:

Hi,

we just found that when using file-based temporary storage (compile
time macro definition TEMP_STORE=1) vs. memory-based temporary
storage (TEMP_STORE=2), on Mac OS X, the performance almost doesn't
degrade at all, whereas on Windows, we're getting a huge performance
penalty when using file-based temporary storage.

We are compiling sqlite 3.3.6 ourselves using pretty much the stock
compile time options, except for TEMP_STORE and THREAD_SAFE=1.
We also found that apparently, database performance with TEMP_STORE=1
is especially slow when running on Windows XP (SP2).

OS Versions where Mac OS X 10.4.4 and Windows XP (SP2).
Both machines were equipped with > 1 GB of RAM, but since we're using
file temp storage, memory usage is not really a limit here. Hard disk
performance should be about equal on both machines.
Queries that suffer most from the performance hit are, of course,
those that obviously seem to access temporary tables/views, e.g.
CREATE TEMP VIEW xyz   or  DROP VIEW xyz.
(needless to say that the very same queries were performed on both
platforms...)
The performance difference between a 2.8GHz Pentium Windows XP
machine and a 2x1GHz Mac OS X PowerPC machine was easily a factor of
10... When using memory temp storage on Windows, the performance is
about par with the Mac OS X performance when using file temp storage.


Anybody got an explanation / solutions / workarounds for this
performance problem?

Thanks,

[sqlite] Reading the same table from two threads

2006-07-26 Thread Rob Richardson

Greetings!

 

I am starting two threads in quick succession that read the same table.
Each thread calls sqlite3_open(), so they are using separate database
pointers.  The first thread asks for records recorded in the last 24
hours.  The second thread asks for records from the same table recorded
in the previous 24 hours.   The second thread is probably started only a
few milliseconds after the first one.  I am getting an access violation
inside sqlite3.dll when the second thread calls sqlite3_prepare().  Am I
trying to do something I shouldn't?

 

Thanks very much!

 

Rob Richardson

Rad-Con, Inc.

Re: [sqlite] "SQL Error: near 'Table': Syntax error"

2006-07-26 Thread Derrell . Lipman

"John Newby" <[EMAIL PROTECTED]> writes:

> Does anyone know any reason why SQLite doesnt like tables called "Table" or
> is this a standard SQL thing?

It's a reserved word, so if you really, Really, REALLY want to create a table
of that name (you're making it confusing to read, so you really shouldn't),
you can do it using either quotes or square brackets around the table name, as
shown here:

SQLite version 3.2.1
Enter ".help" for instructions
sqlite> create table "TABLE" (i integer);
sqlite> .schema
CREATE TABLE "TABLE" (i integer);
sqlite> .mode line
sqlite> select * from sqlite_master;
type = table
name = TABLE
tbl_name = TABLE
rootpage = 2
 sql = CREATE TABLE "TABLE" (i integer)
sqlite> drop table "table";
sqlite> create table [TABLE] (i integer);
sqlite> .schema
CREATE TABLE [TABLE] (i integer);
sqlite> select * from sqlite_master;
type = table
name = TABLE
tbl_name = TABLE
rootpage = 2
 sql = CREATE TABLE [TABLE] (i integer)
sqlite>



Derrell

Re: [sqlite] "SQL Error: near 'Table': Syntax error"

2006-07-26 Thread Jonathan Ballet

John Newby wrote:

> Does anyone know any reason why SQLite doesnt like tables called "Table" or
> is this a standard SQL thing?
> 
> Many thanks
> 
> John
> 

I guess that 'Table' is a reserved keyword, part of  the SQL language.
If you _really_ want to have a table, named 'table', you should put the name
between quotes in the SQL query :

sqlite> create table table (value TEXT);
SQL error: near "table": syntax error
sqlite> create table 'table' (value TEXT);
sqlite> .schema
CREATE TABLE 'table' (value TEXT);
sqlite> select * from 'table';
sqlite> select * from table;
SQL error: near "table": syntax error
sqlite>

Cheers,
Jonathan

[sqlite] "SQL Error: near 'Table': Syntax error"