Re: [sqlite] Please test prior to version 3.1.5. Was: Delete crashes with Version 3.2.4 in Windows XP

[Glen Nakamura]

On Wed, Aug 24, 2005 at 04:19:09PM -0400, D. Richard Hipp wrote:
> On Wed, 2005-08-24 at 10:08 -1000, Glen Nakamura wrote:
> > The problem could be the following lines in Makefile.in:
> > 
> > # In LIBOBJ but not TESTSRC
> > COMMONOBJ = $(foreach obj,$(LIBOBJ),\
> > $(if $(findstring $(patsubst %.lo,%.c,$(obj)),$(TESTSRC)),,$(obj)))
> > 
> 
> OK.  The line above was in a patch someone sent in for ticket
> #1292:  http://www.sqlite.org/cvstrac/tktview?tn=1292.
> 
> Can someone who understands make and configure perhaps suggest a
> fix for this so that it will work on OpenBSD and Mac OS-X at the
> same time?

I've attached a patch in Ticket #1390:
http://www.sqlite.org/cvstrac/tktview?tn=1390

- glen

Re: [sqlite] C++ API: Retrieve multiple rows into struct, using callback

[Keith Herold]

It's not too hard.

Instantiante a static class method that matches the sqlite callback signature.  

When you register the callback with sqlite, you'll want to pass in the
this pointer to the instantiated class.

Within the static class method, cast the void pointer back to the
instantiated class, and reenter the class through an internal
(non-static) method.

Push all the results you find into the class member variables, and
voila, you have your container holding all results.

Be careful not to muck with the instatiated class object from other
threads much, though.

--Keith

On 8/26/05, Jay Sprenkle <[EMAIL PROTECTED]> wrote:
> On 8/26/05, Cam Crews <[EMAIL PROTECTED]> wrote:
> > Hi.  I'm new to sqlite and would like to use the sqlite C++ API to
> > return multiple rows from a SELECT statement.  I'm able to load a
> > single row's result into a struct & pass it back by reference through
> > the callback, but haven't been able to find any resources recommending
> > a method for returning *multiple* results (rows).  Anyone know if
> > there are sample code/suggestions for getting this to work?  I've thus
> > far seen only rudementry examples of printing results to stdout from
> > the callback.
> >
> > If these resources aren't already out there, I'll consider writing a
> > tutorial on performing this using a vector of structs...  thanks,
> 
> If you're using c++ you probably don't want to use the callback
> for retrieving results. It's difficult to reference members of an
> instantiated class from a callback. use sqlite_step().
> I used stl vectors to store my results.
> 
> 
> ---
> The Castles of Dereth Calendar: a tour of the art and architecture of
> Asheron's Call
> http://www.lulu.com/content/77264
>

Re: [sqlite] Linking to SQLite with Cygwin / CMake [SOLVED]

[Kiel W.]

Reid,

Thanks for the help! 


1) I didn't realize the catch that the .lib directory was a hidden folder
2) I wasn't using the --enable-debug and --disable-tcl flags
3) CMake is not generating a valid Makefile that links correctly =(

Re: [sqlite] checking the database status

[Robert Simpson]

- Original Message - 
From: "Dennis Jenkins" <[EMAIL PROTECTED]>

To: 
Sent: Friday, August 26, 2005 1:48 PM
Subject: Re: [sqlite] checking the database status



Robert Simpson wrote:

What I say below is in no means trying to be rude or show you (or anyone) 
up.  I have no idea what your experiences are with Windows or Unix or 
Unix-Like operating systems.  I'm probably stating things that almost all 
of us already know. :)


I've been coding for about 24 years, the last 15 on a PC ... so as vast as 
my experience is with DOS/Windows, there is only one truism that I hold to: 
There is always someone out there who knows more than I do.


In Unix you can delete an open file.  The file is simply removed from the 
directory.  Properly, this is called "unlinking".  The actual syscall is 
called "unlink".  When a file has 0 hard-links, and no process has it 
open, it is removed from the file system and the space reclaimed.  Normal 
files have only one "hard-link".  The 'hard-link' count is stored in the 
file's inode in a traditional unix file system.  (I know about UFS and 
EXT2/EXT3.  I have no idea what JFS, XFS, ReiserFS, et al do.)


As I said, I am not familiar with *nix environments, so my comments are (as 
always) subject to platform corrections.


In Windows you can not delete an open file, even if your process has the 
file open for writing.  The text at the bottom was lifted directly from 
the February 2003 Win32 platform SDK on the "DeleteFile" API call [1] 
about 15 seconds ago.  Note that the rules I'm discussing are for NT 
kernel based versions of Windows.


Because of this behavior, at least in Windows, it is permissable to 
create/open a file, keep it open, call sqlite3_open() and then close your 
handle, and know that the operation you performed on the file was for all 
intents and purposes, atomic.


I know :)  I'm simply saying that I wish that sqlite3_open() took some 
flags and "did the right thing" based on the underling OS.  Those flags 
would be specific to sqlite and translated into the native OS flags deep 
inside to "os_***.c" wrappers.


#define SQLITE3_OPEN_DEFAULT0
#define SQLITE3_OPEN_EXISTING1
#define SQLITE3_OPEN_TRUNCATE   2
...etc...

So my question is simply, "Why was sqlite3 designed to behave the way it 
does?" (That is, the caller has no control over the underlying "open" 
operation.


Another option would be to create a version of "sqlite3_open" that takes 
an existing OS handle and uses the handle as-is (I suppose that you'd 
still have to pass in a filename so that the journal, vacuum, (and 
other?), files could be created).


Now, I've not had a need for any of this.  I did notice this a long time 
ago though.  If I really needed this, I'd code it myself.  I've already 
hacked away on the sqlite code base to make it do some things that I want 
it to do.


I agree, it would've been nice to have more control over the potential 
creation of a new file during the call to open.


[snip]
The *DeleteFile* function fails if an application attempts to delete a 
file that is open for normal I/O or as a memory-mapped file.


*Windows Me/98/95:  *The *DeleteFile* function deletes a file even if it 
is open for normal I/O or as a memory-mapped file. To prevent loss of 
data, close files before attempting to delete them.


To recursively delete the files in a directory, use the *SHFileOperation* 
function.


To close an open file, use the *CloseHandle* function.

The *DeleteFile* function marks a file for deletion on close. Therefore, 
the file deletion does not occur until the last handle to the file is 
closed. Subsequent calls to *CreateFile*  to open the file 
fail with ERROR_ACCESS_DENIED.


Here the MSDN documentation may be faulty or misleading.  I verified in 
Windows 98 that if you attempt to call DeleteFile() on a file that is open, 
the file will not be deleted as the documentation seems to state.  Not only 
that, but the file is not marked as delete-on-close either as the 
documentation seems to state.  I tested on 98 and 2003 with FAT and NTFS 
volumes respectively.  The test consisted of two processes opening the same 
file (one gets it, the other fails to create) then having both processes 
attempt to delete the file before the file creator's file handle is closed. 
This behavior is consistent with all my previous observations of Windows and 
how it deletes files.


Robert

Re: [sqlite] checking the database status

[Dennis Jenkins]

Robert Simpson wrote:

What I say below is in no means trying to be rude or show you (or 
anyone) up.  I have no idea what your experiences are with Windows or 
Unix or Unix-Like operating systems.  I'm probably stating things that 
almost all of us already know. :)




So?  If you open the file, that's 1 handle open.  Someone unlinks it, 
but a handle is still open.  sqlite3_open() then opens the file, 
that's 2 handles. You then close your handle and there's still 1 
handle open until sqlite is done with it.  I'm not a *nix programmer, 
so maybe I am missing something obvious.


In Unix you can delete an open file.  The file is simply removed from 
the directory.  Properly, this is called "unlinking".  The actual 
syscall is called "unlink".  When a file has 0 hard-links, and no 
process has it open, it is removed from the file system and the space 
reclaimed.  Normal files have only one "hard-link".  The 'hard-link' 
count is stored in the file's inode in a traditional unix file system.  
(I know about UFS and EXT2/EXT3.  I have no idea what JFS, XFS, 
ReiserFS, et al do.)


In Windows you can not delete an open file, even if your process has the 
file open for writing.  The text at the bottom was lifted directly from 
the February 2003 Win32 platform SDK on the "DeleteFile" API call [1] 
about 15 seconds ago.  Note that the rules I'm discussing are for NT 
kernel based versions of Windows.



#2..  Would that work if you opened the file exclusively?  If you 
don't open it exclusively, theoretically someone else could open it too.



On Windows, sqlite3_open() calls the CreateFile() API with the 
OPEN_ALWAYS flag, which means if the file doesn't exist, then create 
it -- in either case, always open the file.  To atomically create a 
file and make sqlite3 open it (at least in Windows) you would call 
CreateFile() with the CREATE_NEW flag, which will atomically fail if 
the file already exists.  If it creates a new file however, you can 
then pass the filename to sqlite3_open() and then subsequently close 
your handle.




I know :)  I'm simply saying that I wish that sqlite3_open() took some 
flags and "did the right thing" based on the underling OS.  Those flags 
would be specific to sqlite and translated into the native OS flags deep 
inside to "os_***.c" wrappers.


#define SQLITE3_OPEN_DEFAULT0
#define SQLITE3_OPEN_EXISTING1
#define SQLITE3_OPEN_TRUNCATE   2
...etc...

So my question is simply, "Why was sqlite3 designed to behave the way it 
does?" (That is, the caller has no control over the underlying "open" 
operation.


Another option would be to create a version of "sqlite3_open" that takes 
an existing OS handle and uses the handle as-is (I suppose that you'd 
still have to pass in a filename so that the journal, vacuum, (and 
other?), files could be created).


Now, I've not had a need for any of this.  I did notice this a long time 
ago though.  If I really needed this, I'd code it myself.  I've already 
hacked away on the sqlite code base to make it do some things that I 
want it to do.


[1]

If an application attempts to delete a file that does not exist, the 
*DeleteFile* function fails. If the file is a read-only file, the 
function fails with ERROR_ACCESS_DENIED. To delete a read-only file, you 
must first remove the read-only attribute.


To delete or rename a file, you must have either delete permission on 
the file or delete child permission in the parent directory. If you set 
up a directory with all access except delete and delete child and the 
ACLs of new files are inherited, then you should be able to create a 
file without being able to delete it. However, you can then create a 
file get all the access you request on the handle returned to you at the 
time you create the file. If you requested delete permission at the time 
you created the file, you could delete or rename the file with that 
handle but not with any other. For more information, see File Security 
and Access Rights .


The *DeleteFile* function fails if an application attempts to delete a 
file that is open for normal I/O or as a memory-mapped file.


*Windows Me/98/95:  *The *DeleteFile* function deletes a file even if it 
is open for normal I/O or as a memory-mapped file. To prevent loss of 
data, close files before attempting to delete them.


To recursively delete the files in a directory, use the 
*SHFileOperation* function.


To close an open file, use the *CloseHandle* function.

The *DeleteFile* function marks a file for deletion on close. Therefore, 
the file deletion does not occur until the last handle to the file is 
closed. Subsequent calls to *CreateFile*  to open the 
file fail with ERROR_ACCESS_DENIED.


*Windows Me/98/95:  **DeleteFileW* is supported by the Microsoft Layer 
for Unicode. To use this, you must add certain files to your 
application, as outlined in Microsoft Layer for Unicode on 
Windows 95/98/Me Systems.

Re: [sqlite] checking the database status

[Robert Simpson]

- Original Message - 
From: "Dennis Jenkins" <[EMAIL PROTECTED]>

To: 
Sent: Friday, August 26, 2005 1:12 PM
Subject: Re: [sqlite] checking the database status



Robert Simpson wrote:

sqlite3_open() doesn't create/open a file for exclusive access off the 
bat.


If you want to atomically open a file that already exists,
open it yourself and while its open, call sqlite3_open(), then close your 
own handle to it.


If you want to atomically create a file and only open it if you created 
it,
create it yourself and while its open, call sqlite3_open() then close 
your handle.


#1 should not work in all cases on Unix.  Someone could unlink the file 
after you open it the first time and before you open it the second time. 
The first file will be removed when all open file handles to that file are 
closed.


So?  If you open the file, that's 1 handle open.  Someone unlinks it, but a 
handle is still open.  sqlite3_open() then opens the file, that's 2 handles. 
You then close your handle and there's still 1 handle open until sqlite is 
done with it.  I'm not a *nix programmer, so maybe I am missing something 
obvious.


#2..  Would that work if you opened the file exclusively?  If you don't 
open it exclusively, theoretically someone else could open it too.


On Windows, sqlite3_open() calls the CreateFile() API with the OPEN_ALWAYS 
flag, which means if the file doesn't exist, then create it -- in either 
case, always open the file.  To atomically create a file and make sqlite3 
open it (at least in Windows) you would call CreateFile() with the 
CREATE_NEW flag, which will atomically fail if the file already exists.  If 
it creates a new file however, you can then pass the filename to 
sqlite3_open() and then subsequently close your handle.


Unless I'm missing something, the only way to atomically create or open a 
file is to ask the OS to do that operation only once.


Maybe Dr. Hipp can elaborate on why we can pass the file open/creation 
flags into the sqlite3_open() function.


Maybe these flags do not exist on all platforms (in which case, why not 
just ignore the flags)? ?


Robert

Re: [sqlite] checking the database status

[Dennis Jenkins]

Robert Simpson wrote:

sqlite3_open() doesn't create/open a file for exclusive access off the 
bat.


If you want to atomically open a file that already exists,
open it yourself and while its open, call sqlite3_open(), then close 
your own handle to it.


If you want to atomically create a file and only open it if you 
created it,
create it yourself and while its open, call sqlite3_open() then close 
your handle.


#1 should not work in all cases on Unix.  Someone could unlink the file 
after you open it the first time and before you open it the second 
time.  The first file will be removed when all open file handles to that 
file are closed.


#2..  Would that work if you opened the file exclusively?  If you don't 
open it exclusively, theoretically someone else could open it too.



Unless I'm missing something, the only way to atomically create or open 
a file is to ask the OS to do that operation only once.


Maybe Dr. Hipp can elaborate on why we can pass the file open/creation 
flags into the sqlite3_open() function.


Maybe these flags do not exist on all platforms (in which case, why not 
just ignore the flags)? ?

Re: [sqlite] C++ API: Retrieve multiple rows into struct, using callback

[Jay Sprenkle]

On 8/26/05, Cam Crews <[EMAIL PROTECTED]> wrote:
> Hi.  I'm new to sqlite and would like to use the sqlite C++ API to
> return multiple rows from a SELECT statement.  I'm able to load a
> single row's result into a struct & pass it back by reference through
> the callback, but haven't been able to find any resources recommending
> a method for returning *multiple* results (rows).  Anyone know if
> there are sample code/suggestions for getting this to work?  I've thus
> far seen only rudementry examples of printing results to stdout from
> the callback.
> 
> If these resources aren't already out there, I'll consider writing a
> tutorial on performing this using a vector of structs...  thanks,

If you're using c++ you probably don't want to use the callback
for retrieving results. It's difficult to reference members of an
instantiated class from a callback. use sqlite_step().
I used stl vectors to store my results.


---
The Castles of Dereth Calendar: a tour of the art and architecture of
Asheron's Call
http://www.lulu.com/content/77264

Re: [sqlite] checking the database status

[Dennis Jenkins]

Robert Simpson wrote:

um ... It's a file.  You just check to see if the file exists.  No 
sqlite calls involved.


Robert

That would work in this situation, but what about the inverse?  You only 
want to open the file IF it already exists.


There is a race condition: where you check for the file, don't find it, 
and then try to open the database, and in the mean time, a different 
process/thread has done the same?


I do not need this yes, but I can imagine situations where an atomic 
"create new database ONLY" would be very nice.

[sqlite] checking the database status

[YOGESH]

hello,
i am working on sqlite 2.8.16 and my requirement is when i use the sqlite_open 
function i want to display error message if the database is not present.I don't 
want a new database with the wrong name to be created each time i use the 
sqlite_open function.
I checked for the error parameter in the sqlite_open function but each time it 
returns null.

so what is the solution for the above requirement.?

Thanks in advance

Regards
Yogesh

Re: [sqlite] checking the database status

[Robert Simpson]

um ... It's a file.  You just check to see if the file exists.  No sqlite 
calls involved.


Robert

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

To: 
Sent: Friday, August 26, 2005 12:50 PM
Subject: [sqlite] checking the database status


hello,
i am working on sqlite 2.8.16 and my requirement is when i use the 
sqlite_open function i want to display error message if the database is not 
present.I don't want a new database with the wrong name to be created each 
time i use the sqlite_open function.
I checked for the error parameter in the sqlite_open function but each time 
it returns null.


so what is the solution for the above requirement.?

Thanks in advance

Regards
Yogesh 

[sqlite] C++ API: Retrieve multiple rows into struct, using callback

[Cam Crews]

Hi.  I'm new to sqlite and would like to use the sqlite C++ API to
return multiple rows from a SELECT statement.  I'm able to load a
single row's result into a struct & pass it back by reference through
the callback, but haven't been able to find any resources recommending
a method for returning *multiple* results (rows).  Anyone know if
there are sample code/suggestions for getting this to work?  I've thus
far seen only rudementry examples of printing results to stdout from
the callback.

If these resources aren't already out there, I'll consider writing a
tutorial on performing this using a vector of structs...  thanks,

Cam

RE: [sqlite] Linking to SQLite with Cygwin / CMake

[Reid Thompson]

Kiel W. wrote:
> Ok, I'm definately thinking this is just a problem with my
> understanding of the build process in *nix's and the quirks of Cygwin.
> 
> I am getting the following warning with the default Makefile that is
> generated. "libtool: link: warning: undefined symbols not allowed in
> i686-pc-cygwin shared libraries"
> 
> I found that this is because Windows(r) dlls can't have undefined
> symbols in them and the warning can be removed with the
> following flag:
> '-no-undefined'.  This also generates some other information
> that is not
> helpful to me (probably someone else).  However, this didn't seem to
> produce a static library (either .a or .lib).
> 
> I ran across the 'make sqlite3.dll' target in the make file and tried
> that. It succeeded without any problems and generated a .dll.  I tried
> the companion 'make sqlite3.lib' and this fails because it can't find
> impdef/implib/lib. 
> 
> If anyone as other suggestions, or steps to build a static lib under
> Cygwin I'd appreciate it, otherwise I may just bite the
> bullet and see
> about going the shared libary route.
> 
> -- Kiel

download http://www.sqlite.org/sqlite-3.2.4.tar.gz

$ wget http://www.sqlite.org/sqlite-3.2.4.tar.gz
$ tar xvfz sqlite-3.2.4.tar.gz 
$ cd sqlite-3.2.4
$ ./configure
$ make
$ ---you can do make test here
$ ls -lrt .libs
total 22310
( snip non-pertinent .o files, etc )
-rw-rw-rw-  1 Reid.Thompson Domain Users 7504336 Aug 26 13:07
libsqlite3.a
-rw-rw-rw-  1 Reid.Thompson Domain Users 761 Aug 26 13:07
libsqlite3.lai
lrwxrwxrwx  1 Reid.Thompson Domain Users  16 Aug 26 13:07
libsqlite3.la -> ../libsqlite3.la
-rw-rw-rw-  1 Reid.Thompson Domain Users  125589 Aug 26 13:07
tclsqlite.o
-rw-rw-rw-  1 Reid.Thompson Domain Users 7630154 Aug 26 13:07
libtclsqlite3.a
-rw-rw-rw-  1 Reid.Thompson Domain Users 800 Aug 26 13:07
libtclsqlite3.lai
lrwxrwxrwx  1 Reid.Thompson Domain Users  19 Aug 26 13:07
libtclsqlite3.la -> ../libtclsqlite3.la

$ make install
$ ls -lrt /usr/local/lib/*sql*
-rwx--+ 1 Reid.Thompson Domain Users 758 Aug 26  2004
/usr/local/lib/libsqlite.la
-rwx--+ 1 Reid.Thompson Domain Users 4952172 Aug 26  2004
/usr/local/lib/libsqlite.a
-rwx--+ 1 Reid.Thompson Domain Users  282624 Mar  8 13:47
/usr/local/lib/sqlite3.dll
-rwx--+ 1 Reid.Thompson Domain Users  340680 Mar  8 14:40
/usr/local/lib/libsqlite3.dll.a
-rwx--+ 1 Reid.Thompson Domain Users 1148960 Mar  8 14:46
/usr/local/lib/cygsqlite3.dll
-rwx--+ 1 Reid.Thompson Domain Users 761 Apr 12 11:52
/usr/local/lib/libsqlite3.la
-rwx--+ 1 Reid.Thompson Domain Users 6981520 Apr 12 11:52
/usr/local/lib/libsqlite3.a



reid

RE: [sqlite] Encryption and fixed header values (was Re: [sqlite] Why can i open a textfile?)

[Griggs, Donald]

Regarding Mrs. Brisby's ending comment:
   "A better defense: use a different key each time. Encrypt the session key
separately."

I may way off in asking this, but:
  Since we're talking about the encyption of data at rest, and aren't in a
client-server situation, how could sqlite make session keys work?   What
would constitute a session?


Donald Griggs

Opinions are not necessarily those of Misys Healthcare Systems nor its board
of directors.

Re: [sqlite] Encryption and fixed header values (was Re: [sqlite]Why can i open a textfile?)

[F.W.A. van Leeuwen]

> 
> The usual defense against this attack is to mix some random information
> into the beginning of the plaintext.
> 
> A better defense: use a different key each time. Encrypt the session key
> separately.
> 

And /or start encrypting after the fixed header.

Best regards,
Frank.