Re: [sqlite] Re: Re: How do I know what DBs I have attached?

[Gussimulator]

I don't know if this helps, but I'll comment anyway!

I had a similar issue in the past, and what I did was to develop a small 
layer with a queue system, which took care of all the database requests and 
executed them without risking the database integrity nor the data to be 
processed.


Depending on how you implement this, it might leave a user waiting for a few 
ms to secs after the request could be processed if there were 2 "accesses" 
at the same time. Its well worth it and chances are you wont have a 
"collision" so response times will be similar if not equal -to the human 
eye- Neither you'll have a huge queue list (unless X factors do take place 
into the equation) for instance.. huge amount of users / simultaneus 
accesses.





- Original Message - 
From: "jose isaias cabrera" <[EMAIL PROTECTED]>

To: 
Sent: Sunday, March 11, 2007 2:14 AM
Subject: Re: [sqlite] Re: Re: How do I know what DBs I have attached?





jose isaias cabrera <[EMAIL PROTECTED]>
wrote:

Perhaps, this should be the case.  It would not be so hard to keep a
table of connections that are attached or open with a table.


You don't want an otherwise read-only connection have to write to the 
database. This will harm concurrency.


Oh, I agree.  The reason why I want to do it, is to not attach, if there
one attached, already.  For the exact reason that you point out.  Since
I have a few users that will update this DB at any moment, meaning that
could do it at the same time, I want to have a catch for that problem.




Well, only one connection can modify a particular database file at
the same time. It doesn't matter if you open the file directly, or
attach it to an existing connection. If two processes attempt the 
modifying

operation at the same time, one of them will proceed and the other
will be locked out.


Is there a way of telling the DB to UPDATE records after the actual
connection has completed its processing?  Something like UPDATE queue?


I'm not sure I understand the question. Update records after which of 
possibly multiple connections has completed its processing?


Yeah, that was bad.  It's getting late here and I am tired. :-(  Sorry.
Let us say that I have a few users that would connect to this DB to
update it at any moment. Say user one connects to do an update to his
data.  At millisecond later, user2 connects to do an update to his data,
and here is the question, can user say say something like,

UPDATE TAble  after database is not busy.

Or something like that.  More or less, can one commands updates in a 
queue?


thanks Igor for all your help.

josé


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




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

[sqlite] Re: Re: Re: How do I know what DBs I have attached?

[Igor Tandetnik]

jose isaias cabrera <[EMAIL PROTECTED]>
wrote:

jose isaias cabrera
<[EMAIL PROTECTED]>
wrote:

Perhaps, this should be the case.  It would not be so hard to keep a
table of connections that are attached or open with a table.


You don't want an otherwise read-only connection have to write to the
database. This will harm concurrency.


Oh, I agree.


I'm confused. Didn't you just say that it would be a good idea for every 
connection to be recorded in some table in the database? Wouldn't that 
require every connection to write to the database as part of opening or 
attaching?



The reason why I want to do it, is to not attach, if
there one attached, already.  For the exact reason that you point out. 
Since I have a few users that will update this DB at any moment, 
meaning
that could do it at the same time, I want to have a catch for that 
problem.


As soon as you try to update, if another update is already in progress, 
you'll get an error from SQLite. Wouldn't that be sufficient to "catch 
the problem"?



Let us say that I have a few users that would connect to this DB to
update it at any moment. Say user one connects to do an update to his
data.  At millisecond later, user2 connects to do an update to his
data, and here is the question, can user say say something like,

UPDATE TAble  after database is not busy.


Not quite, but you can retry again some time later. See also 
sqlite3_busy_handler, sqlite3_busy_timeout.


Igor Tandetnik 



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

Re: [sqlite] Re: Re: How do I know what DBs I have attached?

[jose isaias cabrera]

jose isaias cabrera <[EMAIL PROTECTED]>
wrote:

Perhaps, this should be the case.  It would not be so hard to keep a
table of connections that are attached or open with a table.


You don't want an otherwise read-only connection have to write to the 
database. This will harm concurrency.


Oh, I agree.  The reason why I want to do it, is to not attach, if there
one attached, already.  For the exact reason that you point out.  Since
I have a few users that will update this DB at any moment, meaning that
could do it at the same time, I want to have a catch for that problem.




Well, only one connection can modify a particular database file at
the same time. It doesn't matter if you open the file directly, or
attach it to an existing connection. If two processes attempt the 
modifying

operation at the same time, one of them will proceed and the other
will be locked out.


Is there a way of telling the DB to UPDATE records after the actual
connection has completed its processing?  Something like UPDATE queue?


I'm not sure I understand the question. Update records after which of 
possibly multiple connections has completed its processing?


Yeah, that was bad.  It's getting late here and I am tired. :-(  Sorry.
Let us say that I have a few users that would connect to this DB to
update it at any moment. Say user one connects to do an update to his
data.  At millisecond later, user2 connects to do an update to his data,
and here is the question, can user say say something like,

UPDATE TAble  after database is not busy.

Or something like that.  More or less, can one commands updates in a queue?

thanks Igor for all your help.

josé


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

[sqlite] Re: Re: How do I know what DBs I have attached?

[Igor Tandetnik]

jose isaias cabrera <[EMAIL PROTECTED]>
wrote:

Perhaps, this should be the case.  It would not be so hard to keep a
table of connections that are attached or open with a table.


You don't want an otherwise read-only connection have to write to the 
database. This will harm concurrency.



Well, only one connection can modify a particular database file at
the same time. It doesn't matter if you open the file directly, or
attach it to an existing connection. If two processes attempt the 
modifying

operation at the same time, one of them will proceed and the other
will be locked out.


Is there a way of telling the DB to UPDATE records after the actual
connection has completed its processing?  Something like UPDATE queue?


I'm not sure I understand the question. Update records after which of 
possibly multiple connections has completed its processing?


Igor Tandetnik 



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

[sqlite] UPDATE colomns based on their values

[jose isaias cabrera]

Greetings!

So, I would like to update some columns based on whether or not they are 
null.  Some of you folks have helped me with ifnull and IS NULL, and I 
happened to see coalesce, but none of these are working.  If I set the 
value, they will work, but with the checks, they do not get UPDATEd.


Here is the call:

BEGIN;
UPDATE LSOpenJobs SET bdate = '2007-03-02' WHERE ProjID = '215' AND bdate IS 
NULL;
UPDATE LSOpenJobs SET ddate = coalesce(ddate, '2007-03-05') WHERE ProjID = 
'215';
UPDATE LSOpenJobs SET edate = ifnull(edate,'2007-03-05') WHERE ProjID = 
'215';

COMMIT;

As you can see, I am using 3 different checks and none of these are working. 
I know it's something simple, but what it is?


Any ideas?

Maybe the other question is, what defines "IS NULL" or "ifnull" or 
"coalesce"?


thanks,

josé 



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

Re: [sqlite] Re: How do I know what DBs I have attached?

[jose isaias cabrera]

"Igor Tandetnik" wrote,



jose isaias cabrera <[EMAIL PROTECTED]>
wrote:

What
I would like to know is, how do I know if I have a db attached 
already?


Realize that a set of attached DBs is a property of a connection (a 
sqlite3* handle), not some kind of persistent state of the database. You 
seem to be under impression that when two processes open the same DB 
file, and one process attaches another DB file, the other process can 
somehow query SQLite and find out that this happened. This is not the 
case.


Perhaps, this should be the case.  It would not be so hard to keep a table
of connections that are attached or open with a table.


So, if you want to know if _you_ have attached a DB, just keep track of 
ATTACH DATABASE commands you have issued on your connection. If you want 
to know if someone else attached a DB to one you also happen to have 
open, SQLite can't help you there.


Perhaps it should.


Also, can different clients ATTACH to the same DB and REPLACE unique
RECORDS without any problem?


Well, only one connection can modify a particular database file at the 
same time. It doesn't matter if you open the file directly, or attach it 
to an existing connection. If two processes attempt the modifying 
operation at the same time, one of them will proceed and the other will 
be locked out.


Is there a way of telling the DB to UPDATE records after the actual
connection has completed its processing?  Something like UPDATE queue?


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

Re: [sqlite] Compiling the linux package on windows using LCC ?

[Gussimulator]

I generated this make with LCC ide, thats why I dont understand the reason 
of my problem!


I tried using GCC but when I run the make with the included makefile I get 
"nothing to be done for..." . Im getting quite frustrated by now.





- Original Message - 
From: "John Stanton" <[EMAIL PROTECTED]>

To: 
Sent: Saturday, March 10, 2007 10:52 PM
Subject: Re: [sqlite] Compiling the linux package on windows using LCC ?


From my recollection the lcc make is not syntax compatible with standard 
make.  I have always used the lcc make within its own IDE so that the make 
file is generated by the lcc IDE.


You could use a regular make and just subsitute the CC definition for the 
lcc compiler.  You might find the need to substitute link library 
definitions as well.


GCC holds is the solution to your problem.

Gussimulator wrote:

Thanks for the info.
Im trying with the LCC ide but I get odd errors while trying to compile:

"c:\lcc\bin\make.exe: Commands defined twice for target where.obj near 
line 87"
I was getting a similar error with another file, I remade the project and 
now Im getting the same error on a different file.


What could be causing it?

Thanks.




- Original Message - From: "John Stanton" <[EMAIL PROTECTED]>
To: 
Sent: Saturday, March 10, 2007 4:02 PM
Subject: Re: [sqlite] Compiling the linux package on windows using LCC ?



GCC will compile you a library usable with a broad range of C compilers.

If you find the gcc library unusable use the makefile as the basis for 
compiling it using lcc.  I use lcc and gcc (mingw) interchangably.  GCC 
has optimization but if you write elegant and efficient C lcc will 
generate excellent code and compiles very fast.


If you get the options correct you can compile each module using lcc or 
use the lcc IDE to compile them all at once then use ar to build a link 
library.  The makefile generated by configure and gcc or the one in the 
Sqlite Windows zip file will give you a template to follow.


I recollect that compiling Sqlite under lcc was not a big deal, but it 
was a long time back for me and I cannot recall the precise details. Now 
I use gcc to build the static library and have a compiler which works 
identically on all flavors of Windows, Linux, AIX and Solaris.


If you want dynamic linking to a DLL you need to look at the lcc 
documentation to see precisely how it is done.


Gussimulator wrote:

GCC?, I need to use the resulting DLL on normal C under windows later 
on, without having strange dependencies, etc.


I'd like to know what am I doing wrong when I try to compile the 
sources... Do I have to output obj for each source and then somehow 
link this into the final library?





- Original Message - From: "John Stanton" <[EMAIL PROTECTED]>
To: 
Sent: Friday, March 09, 2007 2:10 PM
Subject: Re: [sqlite] Compiling the linux package on windows using LCC 
?




Gussimulator wrote:

Hi, I would like to compile the tar version of the sources using LCC 
under Windows.


Since I'm not a *nix guy, I don't know where to start, because my 
little linux knowledge played against me. Heres the problem, I tried 
using the makefile with make.exe from LCC but that didnt work out (it 
said it couldn't open the makefile...) so I tried the config but I 
dont know which tool handles this, so I was back to square one...


So I began questioning myself... Would it even be possible to compile 
this package under windows with LCC?


I want to perform a static compilation, so I can statically link the 
library on my application. However I'd still be happy if I could 
compile as a dynamic lib.


Now that I'm at it, I would like to suggest for the windows build, 
that the required .exp or .lib be added on the package as well, since 
this allows for easier linking on applications - at least under my 
environment (I'm sure others would like this as well - However, being 
able to compile the source by myself would take care of this issue).


Thanks.



A simple way is to compile it under gcc then use LCC.

After you have compiled with gcc you may discover that to use LCC does 
not make a lot of sense since my experience with both compilers is 
that lcc is very well implemented but lacks the level of optimization 
available with gcc.  A static library created with gcc will work with 
your lcc programs if you are using lcc for your applications to take 
advantage of some of its clever extensions to regular C.


-


To unsubscribe, send email to [EMAIL PROTECTED]
-






-


To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] journal - "Unable to open the database file"

[Nuno Lucas]

On 3/10/07, Nuno Lucas <[EMAIL PROTECTED]> wrote:

On 3/10/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> Joe Wilson <[EMAIL PROTECTED]> wrote:
> > --- [EMAIL PROTECTED] wrote:
> > > Is there something that the SQLite core can do better?
> >
> > Perhaps exclusive locks on journal files would help avoid this problem.
> > Or are the -journal and etilqs_* files supposed to be sharable by other
> > sqlite processes?
> >
>
> They are, at least on unix.  On unix, both files are opened with
> the O_EXCL flag.  How do I do the same thing for windows?

The code does that already. You just pass 0 on the share parameter and
no one can open (or delete) the file until closed. The only difference
to unix is the fact you can't delete a file while open (unless if you
pass the FILE_SHARE_DELETE, which isn't supported by Win9x).

I don't see a problem there. The problem seems to be the journal file
is opened by other programs BEFORE sqlite tries to open it, meaning
sqlite fails to open it in exclusive mode.


Answering to myself again, I'm guessing the programs that don't let
sqlite open the journal files in exclusive mode probably will let it
open in shared mode, so maybe if the journal file is open using the
FILE_SHARE_READ attribute, it will allow both the antivirus and
indexers to also have them open (which I'm guessing open them in read
mode and with sharing enabled), although denying any other program to
open it in write mode.

The only problem may be that sqlite will probably fail to delete them
on the end, but maybe this can be solved with some flag on the journal
file saying it can be ignored if sqlite opens it again, or maybe by
just truncating the file to a zero size.


Regards,
~Nuno Lucas

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

[sqlite] Re: What is wrong with this UPDATE?

[Igor Tandetnik]

RB Smissaert <[EMAIL PROTECTED]>
wrote:

UPDATE
A3SQLADC_J
SET
ADDED_DATE = '' WHERE ADDED_DATE = 0 OR ADDED_DATE IS NULL,
START_DATE = '' WHERE START_DATE = 0 OR START_DATE IS NULL

near ",": syntax error


Make it

UPDATE
A3SQLADC_J
SET
ADDED_DATE = (case when ADDED_DATE = 0 OR ADDED_DATE IS NULL then '' 
else ADDED_DATE end),
START_DATE = (case when START_DATE = 0 OR START_DATE IS NULL then '' 
else START_DATE end);


Igor Tandetnik 



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

Re: [sqlite] What is wrong with this UPDATE?

[Gerry Snyder]

RB Smissaert wrote:

UPDATE
A3SQLADC_J
SET
ADDED_DATE = '' WHERE ADDED_DATE = 0 OR ADDED_DATE IS NULL,
START_DATE = '' WHERE START_DATE = 0 OR START_DATE IS NULL

near ",": syntax error

I am sure I have run this before with no trouble.


According to the web page, there should be only one WHERE clause, after 
all the assignments.


UPDATE
sql-statement ::= 	UPDATE [ OR conflict-algorithm ] [database-name.] 
table-name SET assignment [, assignment]*

[WHERE expr]

assignment ::=  column-name = expr

HTH,

Gerry

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

Re: [sqlite] Compiling the linux package on windows using LCC ?

[John Stanton]

From my recollection the lcc make is not syntax compatible with 
standard make.  I have always used the lcc make within its own IDE so 
that the make file is generated by the lcc IDE.


You could use a regular make and just subsitute the CC definition for 
the lcc compiler.  You might find the need to substitute link library 
definitions as well.


GCC holds is the solution to your problem.

Gussimulator wrote:

Thanks for the info.
Im trying with the LCC ide but I get odd errors while trying to compile:

"c:\lcc\bin\make.exe: Commands defined twice for target where.obj near 
line 87"
I was getting a similar error with another file, I remade the project 
and now Im getting the same error on a different file.


What could be causing it?

Thanks.




- Original Message - From: "John Stanton" <[EMAIL PROTECTED]>
To: 
Sent: Saturday, March 10, 2007 4:02 PM
Subject: Re: [sqlite] Compiling the linux package on windows using LCC ?



GCC will compile you a library usable with a broad range of C compilers.

If you find the gcc library unusable use the makefile as the basis for 
compiling it using lcc.  I use lcc and gcc (mingw) interchangably.  
GCC has optimization but if you write elegant and efficient C lcc will 
generate excellent code and compiles very fast.


If you get the options correct you can compile each module using lcc 
or use the lcc IDE to compile them all at once then use ar to build a 
link library.  The makefile generated by configure and gcc or the one 
in the Sqlite Windows zip file will give you a template to follow.


I recollect that compiling Sqlite under lcc was not a big deal, but it 
was a long time back for me and I cannot recall the precise details. 
Now I use gcc to build the static library and have a compiler which 
works identically on all flavors of Windows, Linux, AIX and Solaris.


If you want dynamic linking to a DLL you need to look at the lcc 
documentation to see precisely how it is done.


Gussimulator wrote:

GCC?, I need to use the resulting DLL on normal C under windows later 
on, without having strange dependencies, etc.


I'd like to know what am I doing wrong when I try to compile the 
sources... Do I have to output obj for each source and then somehow 
link this into the final library?





- Original Message - From: "John Stanton" <[EMAIL PROTECTED]>
To: 
Sent: Friday, March 09, 2007 2:10 PM
Subject: Re: [sqlite] Compiling the linux package on windows using LCC ?



Gussimulator wrote:

Hi, I would like to compile the tar version of the sources using 
LCC under Windows.


Since I'm not a *nix guy, I don't know where to start, because my 
little linux knowledge played against me. Heres the problem, I 
tried using the makefile with make.exe from LCC but that didnt work 
out (it said it couldn't open the makefile...) so I tried the 
config but I dont know which tool handles this, so I was back to 
square one...


So I began questioning myself... Would it even be possible to 
compile this package under windows with LCC?


I want to perform a static compilation, so I can statically link 
the library on my application. However I'd still be happy if I 
could compile as a dynamic lib.


Now that I'm at it, I would like to suggest for the windows build, 
that the required .exp or .lib be added on the package as well, 
since this allows for easier linking on applications - at least 
under my environment (I'm sure others would like this as well - 
However, being able to compile the source by myself would take care 
of this issue).


Thanks.



A simple way is to compile it under gcc then use LCC.

After you have compiled with gcc you may discover that to use LCC 
does not make a lot of sense since my experience with both compilers 
is that lcc is very well implemented but lacks the level of 
optimization available with gcc.  A static library created with gcc 
will work with your lcc programs if you are using lcc for your 
applications to take advantage of some of its clever extensions to 
regular C.


- 



To unsubscribe, send email to [EMAIL PROTECTED]
- 







- 



To unsubscribe, send email to [EMAIL PROTECTED]
- 







- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 

Re: [sqlite] are nested joins possible ?

[Stef Mientki]

Stef Mientki wrote:

thanks guys,
for the fast and different responses,
I really appreciate that.
... now I need a couple hours to study all your ideas.


After studying all your suggestions, I must conclude
that my knowledge of SQL is too low to create a real fool proof solution.
For the moment I've it working for simple visual designs, with the 
following algorithm


- find all connected tables
- find all links between tables
- order the table-boxes, the most left table will get the highest number
- search all top tables, i.e.tables that have no left join to another table
- put all top tables in the "FROM" clause
- walk to the tree of tables and put them in a "JOIN" clause
- check if there are joins between top tables, if found, connect them 
through the "WHERE" clause

- add the user "WHERE" to the "WHERE" clause

You can see an example here
 
http://oase.uci.kun.nl/~mientki/data_www/pic/jalcc/help/jalcc_sqlite.html#visual_link_design
(btw this page, although messy, gives an idea of the db-manager I'm 
working on)


thanks again for all your support,

--
cheers,
Stef Mientki
http://pic.flappie.nl


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

Re: [sqlite] Compiling the linux package on windows using LCC ?

[Gussimulator]

Thanks for the info.
Im trying with the LCC ide but I get odd errors while trying to compile:

"c:\lcc\bin\make.exe: Commands defined twice for target where.obj near line 
87"
I was getting a similar error with another file, I remade the project and 
now Im getting the same error on a different file.


What could be causing it?

Thanks.




- Original Message - 
From: "John Stanton" <[EMAIL PROTECTED]>

To: 
Sent: Saturday, March 10, 2007 4:02 PM
Subject: Re: [sqlite] Compiling the linux package on windows using LCC ?



GCC will compile you a library usable with a broad range of C compilers.

If you find the gcc library unusable use the makefile as the basis for 
compiling it using lcc.  I use lcc and gcc (mingw) interchangably.  GCC 
has optimization but if you write elegant and efficient C lcc will 
generate excellent code and compiles very fast.


If you get the options correct you can compile each module using lcc or 
use the lcc IDE to compile them all at once then use ar to build a link 
library.  The makefile generated by configure and gcc or the one in the 
Sqlite Windows zip file will give you a template to follow.


I recollect that compiling Sqlite under lcc was not a big deal, but it was 
a long time back for me and I cannot recall the precise details. Now I use 
gcc to build the static library and have a compiler which works 
identically on all flavors of Windows, Linux, AIX and Solaris.


If you want dynamic linking to a DLL you need to look at the lcc 
documentation to see precisely how it is done.


Gussimulator wrote:
GCC?, I need to use the resulting DLL on normal C under windows later on, 
without having strange dependencies, etc.


I'd like to know what am I doing wrong when I try to compile the 
sources... Do I have to output obj for each source and then somehow link 
this into the final library?





- Original Message - From: "John Stanton" <[EMAIL PROTECTED]>
To: 
Sent: Friday, March 09, 2007 2:10 PM
Subject: Re: [sqlite] Compiling the linux package on windows using LCC ?



Gussimulator wrote:

Hi, I would like to compile the tar version of the sources using LCC 
under Windows.


Since I'm not a *nix guy, I don't know where to start, because my 
little linux knowledge played against me. Heres the problem, I tried 
using the makefile with make.exe from LCC but that didnt work out (it 
said it couldn't open the makefile...) so I tried the config but I dont 
know which tool handles this, so I was back to square one...


So I began questioning myself... Would it even be possible to compile 
this package under windows with LCC?


I want to perform a static compilation, so I can statically link the 
library on my application. However I'd still be happy if I could 
compile as a dynamic lib.


Now that I'm at it, I would like to suggest for the windows build, that 
the required .exp or .lib be added on the package as well, since this 
allows for easier linking on applications - at least under my 
environment (I'm sure others would like this as well - However, being 
able to compile the source by myself would take care of this issue).


Thanks.


A simple way is to compile it under gcc then use LCC.

After you have compiled with gcc you may discover that to use LCC does 
not make a lot of sense since my experience with both compilers is that 
lcc is very well implemented but lacks the level of optimization 
available with gcc.  A static library created with gcc will work with 
your lcc programs if you are using lcc for your applications to take 
advantage of some of its clever extensions to regular C.


-

To unsubscribe, send email to [EMAIL PROTECTED]
-





-

To unsubscribe, send email to [EMAIL PROTECTED]
-





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




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

[sqlite] What is wrong with this UPDATE?

[RB Smissaert]

UPDATE
A3SQLADC_J
SET
ADDED_DATE = '' WHERE ADDED_DATE = 0 OR ADDED_DATE IS NULL,
START_DATE = '' WHERE START_DATE = 0 OR START_DATE IS NULL

near ",": syntax error

I am sure I have run this before with no trouble.

RBS





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

Re: [sqlite] journal - "Unable to open the database file"

[drh]

Dave Dyer <[EMAIL PROTECTED]> wrote:
> >
> >The main purpose of the journal is so that if the program
> >or OS crashes or there is a power failure, once the machine
> >reboots and some other process tries to read the database,
> >the other process can see the journal and roll it back.
> >Private anonymous mapped objects defeat that purpose, it
> >would seem.
> 
> I thought the purpose was to prevent corruption in the main
> database.  If you assume the power fails, or some other
> program disaster occurs, it doesn't really matter if the 
> last transaction was made persistant or not.  It's just
> like the power failed a few seconds sooner.
> 
> So as long as sqlite is still safe if a journal file is deleted
> after a restart, I don't think there's a problem.
> 

The point is that SQLite is NOT safe if the journal file is
deleted after a restart.  In fact, the whole point of the
journal file is to make SQLite safe following program or
OS crashes or power failures.

Sometimes users try to get clever and delete a journal
file after a power failure.  This leads to database
corruption.  See, for example,

   http://www.sqlite.org/cvstrac/tktview?tn=2224

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


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

[sqlite] Re: journal - "Unable to open the database file"

[Dave Dyer]

>
>OS designers could think about providing a capability of tagging a file as 
>private and making it immune to interference from gratuitous activity.

Of course, if such a facility existed it would be exploited by 
viruses to keep the antivirus forces at bay.   Quite a conundrum.

This kind of thing isn't just a database problem.  All kinds of
routine operations can fail unexpectedly valid but very temporary
reasons, and the OS solution is to throw the crap back to every
single application to handle.   It's not good for reliability.


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

Re: [sqlite] Re: journal - "Unable to open the database file"

[John Stanton]

The journal file enables Sqlite to roll back a part-posted transaction 
on restart and ensure transactional integrity.


Dave Dyer wrote:

The main purpose of the journal is so that if the program
or OS crashes or there is a power failure, once the machine
reboots and some other process tries to read the database,
the other process can see the journal and roll it back.
Private anonymous mapped objects defeat that purpose, it
would seem.



I thought the purpose was to prevent corruption in the main
database.  If you assume the power fails, or some other
program disaster occurs, it doesn't really matter if the 
last transaction was made persistant or not.  It's just

like the power failed a few seconds sooner.

So as long as sqlite is still safe if a journal file is deleted
after a restart, I don't think there's a problem.


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




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

[sqlite] Re: journal - "Unable to open the database file"

[Dave Dyer]

>
>The main purpose of the journal is so that if the program
>or OS crashes or there is a power failure, once the machine
>reboots and some other process tries to read the database,
>the other process can see the journal and roll it back.
>Private anonymous mapped objects defeat that purpose, it
>would seem.

I thought the purpose was to prevent corruption in the main
database.  If you assume the power fails, or some other
program disaster occurs, it doesn't really matter if the 
last transaction was made persistant or not.  It's just
like the power failed a few seconds sooner.

So as long as sqlite is still safe if a journal file is deleted
after a restart, I don't think there's a problem.


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

Re: [sqlite] journal - "Unable to open the database file"

[Nuno Lucas]

On 3/10/07, Nuno Lucas <[EMAIL PROTECTED]> wrote:

On 3/10/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> They are, at least on unix.  On unix, both files are opened with
> the O_EXCL flag.  How do I do the same thing for windows?

The code does that already. You just pass 0 on the share parameter and
no one can open (or delete) the file until closed. The only difference
to unix is the fact you can't delete a file while open (unless if you
pass the FILE_SHARE_DELETE, which isn't supported by Win9x).


My limited Unix file semantics knowledge got me in error. O_EXCL is
not (as the name seems to imply) used to open a file in exclusive
mode. It only works at creation time to error out if it was already
created (and, from the man page, seems to be broken on NFS
filesystems).

But I believe I got the windows case right, so please just ignore my
unix comparation part.

Regards,
~Nuno Lucas

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

Re: [sqlite] stupid man's manual to sqlite?

[A.J.Millan]

>Removing the index is one of the silliest suggestions I've ever
>encountered. A book without an index is almost useless, unless of course
>you're blessed with a photographic memory. As far as putting in on a CD
>or on the web, that's fine as long as a _good_ index is also included in
>the book. I often read books on the subway, train, in a car, etc., where
>there is no possibility of access to a CD or the web. Many people I know
>have similar reading habits and consequently have no use for this sort
>of thing.

Certainly I admit that a stupid idea can seem to some superior minds, but
then the editors of almost all the novels that I have in my library should
be stupid.

As almost all in this forum has understood, I am not referring to that index
type that can occupy a couple of pages at the end,  and that is limited to
point out the title of each chapter, but to the exhaustive index of words
and terms that appear in this type of technical books; manuals and
references that by the way, are not those that usually read the people in
the subway or in the bus, but like guide in the desktop.  As an example, the
work "Win32 Programming" from Brent E. Rector and Joseph M. Newcomer,
Addison-Wesley.  A "brick" with 1522 pages, those that the index occupies
187 pages of words to three columns per page. The one that can let You pass
three years of continuous trips without getting bored.

Greetings

A.J.Millan
ZATOR Systems


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

Re: [sqlite] Compiling the linux package on windows using LCC ?

[John Stanton]

GCC will compile you a library usable with a broad range of C compilers.

If you find the gcc library unusable use the makefile as the basis for 
compiling it using lcc.  I use lcc and gcc (mingw) interchangably.  GCC 
has optimization but if you write elegant and efficient C lcc will 
generate excellent code and compiles very fast.


If you get the options correct you can compile each module using lcc or 
use the lcc IDE to compile them all at once then use ar to build a link 
library.  The makefile generated by configure and gcc or the one in the 
Sqlite Windows zip file will give you a template to follow.


I recollect that compiling Sqlite under lcc was not a big deal, but it 
was a long time back for me and I cannot recall the precise details. 
Now I use gcc to build the static library and have a compiler which 
works identically on all flavors of Windows, Linux, AIX and Solaris.


If you want dynamic linking to a DLL you need to look at the lcc 
documentation to see precisely how it is done.


Gussimulator wrote:
GCC?, I need to use the resulting DLL on normal C under windows later 
on, without having strange dependencies, etc.


I'd like to know what am I doing wrong when I try to compile the 
sources... Do I have to output obj for each source and then somehow link 
this into the final library?





- Original Message - From: "John Stanton" <[EMAIL PROTECTED]>
To: 
Sent: Friday, March 09, 2007 2:10 PM
Subject: Re: [sqlite] Compiling the linux package on windows using LCC ?



Gussimulator wrote:

Hi, I would like to compile the tar version of the sources using LCC 
under Windows.


Since I'm not a *nix guy, I don't know where to start, because my 
little linux knowledge played against me. Heres the problem, I tried 
using the makefile with make.exe from LCC but that didnt work out (it 
said it couldn't open the makefile...) so I tried the config but I 
dont know which tool handles this, so I was back to square one...


So I began questioning myself... Would it even be possible to compile 
this package under windows with LCC?


I want to perform a static compilation, so I can statically link the 
library on my application. However I'd still be happy if I could 
compile as a dynamic lib.


Now that I'm at it, I would like to suggest for the windows build, 
that the required .exp or .lib be added on the package as well, since 
this allows for easier linking on applications - at least under my 
environment (I'm sure others would like this as well - However, being 
able to compile the source by myself would take care of this issue).


Thanks.


A simple way is to compile it under gcc then use LCC.

After you have compiled with gcc you may discover that to use LCC does 
not make a lot of sense since my experience with both compilers is 
that lcc is very well implemented but lacks the level of optimization 
available with gcc.  A static library created with gcc will work with 
your lcc programs if you are using lcc for your applications to take 
advantage of some of its clever extensions to regular C.


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

Re: [sqlite] journal - "Unable to open the database file"

[John Stanton]

This is a code snippet for such a function with Windows and Unix.  Even 
here the dreaded NFS raises its ugly head.  The Windows code has worked 
fine, but Windows gurus might find some way to improve it.


  /*Open the file if possible in the appropriate mode.*/
#if IS_WIN32
  mode = GENERIC_READ;
  if (!read_only) mode = mode | GENERIC_WRITE;
  if (exclusive) mode_share = 0;
  else {
mode_share = FILE_SHARE_READ;
if (!read_only) mode_share = mode_share | FILE_SHARE_WRITE;
  }   /*if/else*/

  if (autocreate) mode_create = CREATE_ALWAYS;
  else mode_create = OPEN_EXISTING;

  fd = CreateFile(filename, mode, mode_share, NULL, mode_create,
  FILE_ATTRIBUTE_NORMAL, NULL);
  if (fd == INVALID_HANDLE_VALUE) {
errormet = TRUE; errorflag = IO; errorsub = 'M';
return(TRUE);
  }  /*if*/
#else
  if (read_only) mode = O_RDONLY;
  else mode = O_RDWR;
#if IS_CYGWIN
  mode = mode | O_BINARY;
#endif

  /*Set the mode for exclusive open.*/
#if IS_AIX
  if (exclusive) mode = mode | O_NSHARE;   /*!! Not supported by NFS.*/
#else
  if (exclusive) mode = mode | O_EXCL;
#endif

  /*We may have a PREPARE of an existing file.  Save time by opening
  with truncate.*/
  if (autocreate) mode = mode | O_TRUNC | O_CREAT;

  fd = open(filename, mode, 0666);

  /*A handle value of less then zero indicates that the file was
  neither opened nor created.*/
  if (fd < 0) {
errormet = TRUE; errorflag = IO; errorsub = 'M';
return(TRUE);
  }  /*if*/
#endif

[EMAIL PROTECTED] wrote:

Joe Wilson <[EMAIL PROTECTED]> wrote:


--- [EMAIL PROTECTED] wrote:


Is there something that the SQLite core can do better?


Perhaps exclusive locks on journal files would help avoid this problem.
Or are the -journal and etilqs_* files supposed to be sharable by other 
sqlite processes?





They are, at least on unix.  On unix, both files are opened with
the O_EXCL flag.  How do I do the same thing for windows?
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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




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

Re: [sqlite] UPDATE base on certain values

[jose isaias cabrera]

"Dennis Cote" wrote,



jose isaias cabrera wrote:



So, there is on way of doing it in one call/instruction as I previously 
exampled, correct?  I will have to do them in many calls, correct?




Jose,

You can do it in one API call if you put all the update statements into a 
single string. You will probably also want to add a begin and end around 
the updates so they are all done atomically in the database. You can then 
pass this string containing multiple statement to sqlite3_exec() and it 
will execute all the statements one after the other.


UPDATE table SET
  ID = '88' if not = '88',
  parent = '1171291314642' if null,
  children = '',
  login = 'blah',
  notes = 'blah-blah' if null,
  status = 'o'
  WHERE ProjID = '88';

Would become

string sql  =   "begin;"
   "update table "
   "   set ID = '88', children = '', login = 'blah', status = 
'o' "   "   where ProjId = '88';"

   "update table "
   "   set parent = '1171291314642' "
   "   where ProjID = '88' AND parent IS NULL;"
   "update table "
   "   set notes = 'blah-blah' "
   "   where ProjID = '88' AND notes IS NULL;"
   "commit;";
  int rc = sqlite3_exec(db, sql.c_str(), 0, 0, 0);

Each update must have the same condition, but it can set multiple fields. 
The sqlite3_exec() function will execute all the SQL statements in a 
string in one call.


None of these are working...

BEGIN;
UPDATE LSOpenJobs SET bdate = '2007-03-02' WHERE ProjID = '215' AND bdate IS 
NULL;
UPDATE LSOpenJobs SET ddate = coalesce(ddate, '2007-03-05') WHERE ProjID = 
'215'; UPDATE LSOpenJobs SET edate = ifnull(edate,'2007-03-05') WHERE ProjID 
= '215';

COMMIT;

Any ideas?  If I just set them, they do work.

thanks,

josé 



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

Re: [sqlite] journal - "Unable to open the database file"

[John Stanton]

After I hit  it struck me like a bombshell that the journal has to 
be there for the next startup to perform its complete function so such a 
method would fail.  A virtual memory based object would only provide 
transactional support, not  crash recovery.  Humble pie for breakfast!


OS designers could think about providing a capability of tagging a file 
as private and making it immune to interference from gratuitous activity.


Here is a snippet of Windows and Unix code to create an anonymous 
object.  In this particular concept it is used in an interpreter to 
allocate memory as a more durable alternative to allocating heap area 
with a malloc.


  /*Create a new anonymous mapped virtual memory object.*/
#if IS_WIN32
  hnd = CreateFileMapping((HANDLE)0x, NULL,
  PAGE_READWRITE, 0, newtvsize, NULL);
  newtvar = MapViewOfFile(hnd, FILE_MAP_WRITE, 0, 0, newtvsize);
  if (newtvar == NULL) {   /*Failed*/
/*Fatal error.*/
errormet = TRUE;
spring_trap(PARITY, 'T', ' ', ctxp);
return;
  }  /*if*/
#else
  newtvar = mmap(NULL, newtvsize, (PROT_READ | PROT_WRITE),
 (MAP_PRIVATE | MAP_ANONYMOUS), -1, (off_t)0);
  if ((int)newtvar == -1) {
/*Fatal error.*/
errormet = TRUE;
spring_trap(PARITY, 'T', ' ', ctxp);
return;
  }  /*if*/
#endif

[EMAIL PROTECTED] wrote:

John Stanton <[EMAIL PROTECTED]> wrote:

A suggestion for the journal files would be to make them private 
anonymous mapped objects. 



The main purpose of the journal is so that if the program
or OS crashes or there is a power failure, once the machine
reboots and some other process tries to read the database,
the other process can see the journal and roll it back.
Private anonymous mapped objects defeat that purpose, it
would seem.

On windows, TEMP tables are stored in c:\temp\etilqs_* files.
But on unix, TEMP tables are stored in private, nameless
files that are automatically deleted after a crash.  On unix,
this is accomplished by opening a file then immediately 
unlinking the file while it is still open. Such files disappear

from the filesystem but are still available for I/O.  I don't
think that sort of thing is possible on windows, unless these
"anonymous mapped objects" you speak of might serve the 
purpose.  Where do I find out more about "anonymous mapped

objects" for windows?

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


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




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

Re: [sqlite] Compiling the linux package on windows using LCC ?

[Gussimulator]

GCC?, I need to use the resulting DLL on normal C under windows later on, 
without having strange dependencies, etc.


I'd like to know what am I doing wrong when I try to compile the sources... 
Do I have to output obj for each source and then somehow link this into the 
final library?





- Original Message - 
From: "John Stanton" <[EMAIL PROTECTED]>

To: 
Sent: Friday, March 09, 2007 2:10 PM
Subject: Re: [sqlite] Compiling the linux package on windows using LCC ?



Gussimulator wrote:
Hi, I would like to compile the tar version of the sources using LCC 
under Windows.


Since I'm not a *nix guy, I don't know where to start, because my little 
linux knowledge played against me. Heres the problem, I tried using the 
makefile with make.exe from LCC but that didnt work out (it said it 
couldn't open the makefile...) so I tried the config but I dont know 
which tool handles this, so I was back to square one...


So I began questioning myself... Would it even be possible to compile 
this package under windows with LCC?


I want to perform a static compilation, so I can statically link the 
library on my application. However I'd still be happy if I could compile 
as a dynamic lib.


Now that I'm at it, I would like to suggest for the windows build, that 
the required .exp or .lib be added on the package as well, since this 
allows for easier linking on applications - at least under my environment 
(I'm sure others would like this as well - However, being able to compile 
the source by myself would take care of this issue).


Thanks.

A simple way is to compile it under gcc then use LCC.

After you have compiled with gcc you may discover that to use LCC does not 
make a lot of sense since my experience with both compilers is that lcc is 
very well implemented but lacks the level of optimization available with 
gcc.  A static library created with gcc will work with your lcc programs 
if you are using lcc for your applications to take advantage of some of 
its clever extensions to regular C.


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




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

Re: [sqlite] Question about shared cache and read_uncommitted

[Dan Kennedy]

On Sat, 2007-03-10 at 22:22 +0900, Brownie wrote:
> Thanks Dan,
> 
> > That the first database file was ATTACHed instead of being opened
> > directly makes no difference.
> 
> A cache of :memory: database also be shared on shared cache mode ?
> Or :memory: database of each connections are irrelevant ?

Shared cache mode does not apply to :memory: databases. It is not
currently possible for more than one database handle to access a 
single in-memory database.

Dan.


> Regards,
> 
> 2007/3/10, Dan Kennedy <[EMAIL PROTECTED]>:
> > On Sat, 2007-03-10 at 14:11 +0900, Brownie wrote:
> > > Hi,
> > >
> > > I have two questions about shared cache mode of SQLite.
> > >
> > > 1. When is shared cache of SQLite deallocated?
> > > A last call of sqlite3_close()? Or end of thread procedure?
> >
> > The former. The shared cache (and shared schema) are reference
> > counted. Resources are released as soon as the number of connections
> > to the shared cache drops to zero.
> >
> > > 2. Can I use read_uncommitted isolation in the following case?
> > >
> > > (Connection1 and Connection2 is established by the same thread
> > > and shared cache mode was enabled)
> > >
> > > Connection1: Opened for :memory:
> > > ATTACH DATABASE 'mydb.db' as mydb;
> > > SELECT myname FROM mydb.mytable;
> > >
> > > Connection2: Opened for mydb.db
> > > INSERT INTO mytable(myname) VALUES('myname');
> >
> > I think so. But I'm not 100% sure I understand the question.
> >
> > If Connection1 is in read_uncommitted mode, then the SELECT will
> > neither block nor be blocked by the INSERT statement executed
> > by Connection2.
> >
> > That the first database file was ATTACHed instead of being opened
> > directly makes no difference.
> >
> > Dan.
> >
> >
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> >
> >


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

RE: [sqlite] stupid man's manual to sqlite?

[Anderson, James H \(IT\)]

Removing the index is one of the silliest suggestions I've ever
encountered. A book without an index is almost useless, unless of course
you're blessed with a photographic memory. As far as putting in on a CD
or on the web, that's fine as long as a _good_ index is also included in
the book. I often read books on the subway, train, in a car, etc., where
there is no possibility of access to a CD or the web. Many people I know
have similar reading habits and consequently have no use for this sort
of thing.

-Original Message-
From: A.J.Millan [mailto:[EMAIL PROTECTED] 
Sent: Saturday, March 10, 2007 6:54 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] stupid man's manual to sqlite?


>I will get back to you about the index. I also would like to invite
>anyone else who has any specific things they would like to see changed
>in the index to tell me. Assuming I can get a revised index out
>electronically, I will make any such changes immediately to the file I
>have on-hand.

>-- Mike

I have continued thinking of the index matter, and I consider that the
possibility to take out it completely in the printed version, or leaving
it
summarized to a minimum, would have the additional advantage to be able
to
dedicate more pages to the truly useful things (in the past, I also
published some thing, so I have some idea about the editorial world.)

On the other hand, besides the mentioned possibility, to put on-line, or
in
a CD, the index, I believe that equally, an artifice could be included
that
allow "Google" any word or combination of them (in the case of a CD, the
original content could be ciphered to avoid the piracy).  As in Google,
besides the page and line-number, the result it could include certain
quantity of characters around all the matchs-points. For example, say 30
before and 30 later, to give an idea of the context.  Beside the utility
for
the book owners, the potential buyers could check what can wait.  It
could
be a good "hook " to attract possible clients.

As I stated before, the same thing for the idea.  Please, don't patent
it
(yes I have some paranoia about patents).  Following the Hipp's blessing
,
and inside my much smaller possibilities, I would like that these ideas
remains in the public domain.

Greetings

A. J. Millan
ZATOR Systems.



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

[sqlite] Re: Fwd: database is locked error on Mac OS X

[A. Pagaltzis]

* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2007-03-10 17:30]:
> Do "configure". Then edit the Makefile that is generated.

It would be nice if these options were mapped to `--enable-foo`
switches on the configure script… would that take a lot of work?

(Alternatively, if you don’t want to do it yourself: would it
take a lot of research to learn how to do it? If not, and you’d
be willing to accept a patch, I might contribute one.)

Regards,
-- 
Aristotle Pagaltzis // 

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

Re: Fwd: [sqlite] database is locked error on Mac OS X

[P Kishor]

On 3/10/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

T <[EMAIL PROTECTED]> wrote:
> Can someone please tell me (politely ;-) where to put the -
> DSQLITE_ENABLE_LOCKING_STYLE=1
>
> I tried inserting it arbitrarily in the Makefile.in file but it
> didn't seem to make a difference.
>

Do "configure".  Then edit the Makefile that is generated.


I think Tom is trying to figure out where exactly in the Makefile to
insert the D flag. Tom, I am walking on thin ice here, but after you
have run ./configure, you could try inserting a line like so pretty
much anywhere in your Makefile.in AFTER TCC has been declared. So, you
could add a line like so

# flag to deal with Mac OS X file locking on shared drives
TCC += -DSQLITE_ENABLE_LOCKING_STYLE=1

Obviously, I could be way wrong about this, but if I am, hopefully
someone will chime up and tell us so.,


--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

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

Re: Fwd: [sqlite] database is locked error on Mac OS X

[drh]

T <[EMAIL PROTECTED]> wrote:
> Can someone please tell me (politely ;-) where to put the - 
> DSQLITE_ENABLE_LOCKING_STYLE=1
> 
> I tried inserting it arbitrarily in the Makefile.in file but it  
> didn't seem to make a difference.
> 

Do "configure".  Then edit the Makefile that is generated.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] journal - "Unable to open the database file"

[Joe Wilson]

--- Joe Wilson <[EMAIL PROTECTED]> wrote:
> There's also this - CreateFileTransacted():
> 
>  http://msdn2.microsoft.com/en-us/library/aa363859.aspx
> 
> which has the argument:
> 
>  dwShareMode
> ...
> If this parameter is 0 (zero) and CreateFileTransacted succeeds, 
> the object cannot be shared and cannot be opened again until the 
> handle is closed. 
> ...
> The sharing options remain in effect until you close the handle to 
> an object.

Regrettably, CreateFileTransacted() is not a good option as it is new to 
Windows Vista.


 

Looking for earth-friendly autos? 
Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center.
http://autos.yahoo.com/green_center/

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

Re: [sqlite] journal - "Unable to open the database file"

[Scott Hess]

On 3/10/07, Joe Wilson <[EMAIL PROTECTED]> wrote:

There's also this - CreateFileTransacted():

 http://msdn2.microsoft.com/en-us/library/aa363859.aspx


Doesn't this require Vista?

-scott

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

Re: [sqlite] journal - "Unable to open the database file"

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> Joe Wilson <[EMAIL PROTECTED]> wrote:
> > --- [EMAIL PROTECTED] wrote:
> > > Is there something that the SQLite core can do better?
> > 
> > Perhaps exclusive locks on journal files would help avoid this problem.
> > Or are the -journal and etilqs_* files supposed to be sharable by other 
> > sqlite processes?
> 
> They are, at least on unix.  On unix, both files are opened with
> the O_EXCL flag.  How do I do the same thing for windows?

I'm just guessing, but LockFileEx() and LOCKFILE_EXCLUSIVE_LOCK might do
the trick:

 http://msdn2.microsoft.com/en-us/library/aa365203.aspx

Unfortunately, the open and exclusive lock combination is not atomic.

There's also this - CreateFileTransacted():

 http://msdn2.microsoft.com/en-us/library/aa363859.aspx

which has the argument:

 dwShareMode
...
If this parameter is 0 (zero) and CreateFileTransacted succeeds, 
the object cannot be shared and cannot be opened again until the 
handle is closed. 
...
The sharing options remain in effect until you close the handle to 
an object.



 

Expecting? Get great news right away with email Auto-Check. 
Try the Yahoo! Mail Beta.
http://advision.webevents.yahoo.com/mailbeta/newmail_tools.html 

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

[sqlite] collate function / international sort on linux

[jp]

Thanks to several posts in this forum, I now have a
custom collation defined with
sqlite3_create_collation, which uses Window's
CompareStringA.  I managed to add it to main.c and
recompiled sqlite (I finally got my compilation
running on Windows with MingW/Msys).  For my needs
properly does case-insensitive and characters with
diacritics sorts.  For example:

AAA
amigo
ándale
Andalucía
ángel
Azul

Now I need to have this function, CompareStringA or
similar/better, on linux.  Does anybody have it or can
point me where to look for it?

jp


 

It's here! Your new message!  
Get new email alerts with the free Yahoo! Toolbar.
http://tools.search.yahoo.com/toolbar/features/mail/

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

Fwd: [sqlite] database is locked error on Mac OS X

[T]

Can someone please tell me (politely ;-) where to put the - 
DSQLITE_ENABLE_LOCKING_STYLE=1


I tried inserting it arbitrarily in the Makefile.in file but it  
didn't seem to make a difference.


Don't laugh :-[ ;-)

Thanks,
Tom

 
From: T <[EMAIL PROTECTED]>
Date: 9 March 2007 1:46:21 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] database is locked error on Mac OS X
Reply-To: sqlite-users@sqlite.org


I recently installed SQLite 3.3.13, after having used previous
versions. I now get an error:

Error: database is locked


This is not a bug in SQLite - it is a bug in Apple's implementation  
(or more precisely their lack of implementation) of POSIX advisory  
locks for AppleShare mounted volumes.  The SQLite sources include  
an (Apple-supplied) patch to work around the problem.


Thank you :-)


Recompile with

   -DSQLITE_ENABLE_LOCKING_STYLE=1


Pardon my ignorance, but where/how exactly do I enter this before  
recompiling?


Thanks,
Tom

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

Re: [sqlite] Question about shared cache and read_uncommitted

[Brownie]

Thanks Dan,


That the first database file was ATTACHed instead of being opened
directly makes no difference.


A cache of :memory: database also be shared on shared cache mode ?
Or :memory: database of each connections are irrelevant ?

Regards,

2007/3/10, Dan Kennedy <[EMAIL PROTECTED]>:

On Sat, 2007-03-10 at 14:11 +0900, Brownie wrote:
> Hi,
>
> I have two questions about shared cache mode of SQLite.
>
> 1. When is shared cache of SQLite deallocated?
> A last call of sqlite3_close()? Or end of thread procedure?

The former. The shared cache (and shared schema) are reference
counted. Resources are released as soon as the number of connections
to the shared cache drops to zero.

> 2. Can I use read_uncommitted isolation in the following case?
>
> (Connection1 and Connection2 is established by the same thread
> and shared cache mode was enabled)
>
> Connection1: Opened for :memory:
> ATTACH DATABASE 'mydb.db' as mydb;
> SELECT myname FROM mydb.mytable;
>
> Connection2: Opened for mydb.db
> INSERT INTO mytable(myname) VALUES('myname');

I think so. But I'm not 100% sure I understand the question.

If Connection1 is in read_uncommitted mode, then the SELECT will
neither block nor be blocked by the INSERT statement executed
by Connection2.

That the first database file was ATTACHed instead of being opened
directly makes no difference.

Dan.



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




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

Re: [sqlite] journal - "Unable to open the database file"

[Uwe Sander]

Hi,

Am Samstag, 10. März 2007 13:26 schrieb [EMAIL PROTECTED]:
> purpose.  Where do I find out more about "anonymous mapped
> objects" for windows?

ot sure if this is what John wanted to suggest:
http://msdn2.microsoft.com/en-us/ms810613.aspx

Note that you can create a mapping using the systems page file.

Regards
Uwe

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

Re: [sqlite] journal - "Unable to open the database file"

[drh]

Joe Wilson <[EMAIL PROTECTED]> wrote:
> --- [EMAIL PROTECTED] wrote:
> > Is there something that the SQLite core can do better?
> 
> Perhaps exclusive locks on journal files would help avoid this problem.
> Or are the -journal and etilqs_* files supposed to be sharable by other 
> sqlite processes?
> 

They are, at least on unix.  On unix, both files are opened with
the O_EXCL flag.  How do I do the same thing for windows?
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] journal - "Unable to open the database file"

[drh]

John Stanton <[EMAIL PROTECTED]> wrote:
> A suggestion for the journal files would be to make them private 
> anonymous mapped objects. 

The main purpose of the journal is so that if the program
or OS crashes or there is a power failure, once the machine
reboots and some other process tries to read the database,
the other process can see the journal and roll it back.
Private anonymous mapped objects defeat that purpose, it
would seem.

On windows, TEMP tables are stored in c:\temp\etilqs_* files.
But on unix, TEMP tables are stored in private, nameless
files that are automatically deleted after a crash.  On unix,
this is accomplished by opening a file then immediately 
unlinking the file while it is still open. Such files disappear
from the filesystem but are still available for I/O.  I don't
think that sort of thing is possible on windows, unless these
"anonymous mapped objects" you speak of might serve the 
purpose.  Where do I find out more about "anonymous mapped
objects" for windows?

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


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

Re: [sqlite] stupid man's manual to sqlite?

[A.J.Millan]

>I will get back to you about the index. I also would like to invite
>anyone else who has any specific things they would like to see changed
>in the index to tell me. Assuming I can get a revised index out
>electronically, I will make any such changes immediately to the file I
>have on-hand.

>-- Mike

I have continued thinking of the index matter, and I consider that the
possibility to take out it completely in the printed version, or leaving it
summarized to a minimum, would have the additional advantage to be able to
dedicate more pages to the truly useful things (in the past, I also
published some thing, so I have some idea about the editorial world.)

On the other hand, besides the mentioned possibility, to put on-line, or in
a CD, the index, I believe that equally, an artifice could be included that
allow "Google" any word or combination of them (in the case of a CD, the
original content could be ciphered to avoid the piracy).  As in Google,
besides the page and line-number, the result it could include certain
quantity of characters around all the matchs-points. For example, say 30
before and 30 later, to give an idea of the context.  Beside the utility for
the book owners, the potential buyers could check what can wait.  It could
be a good "hook " to attract possible clients.

As I stated before, the same thing for the idea.  Please, don't patent it
(yes I have some paranoia about patents).  Following the Hipp's blessing ,
and inside my much smaller possibilities, I would like that these ideas
remains in the public domain.

Greetings

A. J. Millan
ZATOR Systems.


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

Re: [sqlite] sin and similar functions

[Jakub Ladman]

Thank you very much.
I am new to this mailing list, so i missed it.
Jakub Ladman


Dne sobota 10 březen 2007 00:48 Clark Christensen napsal(a):
> A poster here, "Mikey C", wrote some math functions and posted them a month
> or so ago.  You might have a look at
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg21791.html
>
>  -Clark
>
> - Original Message 
> From: Jakub Ladman <[EMAIL PROTECTED]>
> To: sqlite-users@sqlite.org
> Sent: Friday, March 9, 2007 3:00:22 PM
> Subject: Re: [sqlite] sin and similar functions
>
> Thank you very much.
> Not only my english is poor, but my coordinated geometry too :-(
> But my software will make much more geometrical computations, so probably
> some in queries too.
> Please let You (or someone) direct me to the relevant part of documentation
> for defining new functions.
>
> Thank You
> Jakub
>
> Dne pátek 09 březen 2007 23:35 Dennis Cote napsal(a):
> > Jakub Ladman wrote:
> > > I will have table with sequence of coordinates (two dimensional space)
> > > and corresponding radiuses, so sequence of circles. And i need to use a
> > > sqlite query to detect if a actual coordinates (after their
> > > measurement) match some of the circle's square or not. And which
> > > circle, if match. And this must be for low CPU consumption optimised,
> > > so i am not sure, if separate sin table queries will be enough as fast
> > > as i need at needed precission.
> > >
> > > The whole algorithm is proven on mssql by my colegue, but he is using
> > > the native math functions.
> >
> > Jakub,
> >
> > I may not understand your problem completely, but it seems to me you can
> > solve your problem without using any trigonometric functions.
> >
> > If you have a table of circles like this
> >
> > create table circle (
> > id  integer primary key,
> > cx  real,
> > cy  real,
> > r   real
> > );
> >
> > You can find all the circles that contain a given point (px,py) using a
> > simple query based in the distance between the point and the center of
> > the circle.
> >
> > select id from circle
> > where (px-cx)*(px-cx)+(py-cy)*(py-cy) < r*r;
> >
> > If you want to create a user defined distance function you could
> > possibly speed up the calculation somewhat. You could then use a query
> > like:
> >
> > select id from circle
> > where distance(cx, cy, px, py) < r;
> >
> > where
> >
> > distance(cx, cy, px, py) = sqrt((px-cx)^2 + (py-cy)^2)
> >
> > HTH
> > Dennis Cote
> >
> >
> > -
> >-- -- To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> >-- --
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--
>
>
>
>
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--

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