Re: [sqlite] Another SQLite documentation/manual

[Firman Wandayandi]

On 7/5/05, Roger Binns <[EMAIL PROTECTED]> wrote:
> > Any SQLite documentation/manual out there?
> 
> http://www.sqlite.org/docs.html
> http://www.sqlite.org/cvstrac/wiki
> 

That's the official docs one.

> > There's not enough with the
> > official documentation for me, I need more docs for exploring SQLite.
> 
> What specifically is missing?
> 

Anything about sqlite_master table information I guess, or maybe I missed it?

> Roger
> 

Regards,
-- 
:: Never Dreamt Before ::
http://php.hm/~firman/
firman(@)php.net | firmanw(@)gmail.com

Re: [sqlite] Another SQLite documentation/manual

[Roger Binns]

Any SQLite documentation/manual out there? 


http://www.sqlite.org/docs.html
http://www.sqlite.org/cvstrac/wiki


There's not enough with the
official documentation for me, I need more docs for exploring SQLite.


What specifically is missing?

Roger

Re: [sqlite] how to do select by Date and Time range?

[jack wu]

Cory, thanks for the information. the wiki talks about
time functions. what should we do when defining the
table columns? should i just use REAL? can you give a
sample CREATE table statement? and a sample insert
statement? 


jack.

Re: [sqlite] how to do select by Date and Time range?

[Cory Nelson]

sqlite stores dates/times in floating point, the normal comparison
operators should work fine on them. 
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

On 7/4/05, jack wu <[EMAIL PROTECTED]> wrote:
> 
> i have a table which has Date one the columns. i 'd
> like run a query in this form:
> 
> select * from table1 where Date>date1 and Date 
> as we all understand sqlite does not have timestamp
> datatype. i am wondering how everybody is working
> around the limitation. thanks.
> 
> jack.
> 


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

[sqlite] Another SQLite documentation/manual

[Firman Wandayandi]

Hi Guys.

Any SQLite documentation/manual out there? There's not enough with the
official documentation for me, I need more docs for exploring SQLite.

Thanks,
-- 
:: Never Dreamt Before ::

http://php.hm/~firman/
< firman(@)php.net | firmanw(@)gmail.com >

[sqlite] how to do select by Date and Time range?

[jack wu]

i have a table which has Date one the columns. i 'd
like run a query in this form:

select * from table1 where Date>date1 and Date

Re: [sqlite] 3.2.2 MacOS X = 3.0.8.6 ?

[Dominic]

Le 4 juil. 05 à 23:41, Dirk Theisen a écrit :

It seems to be that the build system for OS X always assigns the  
3.0.8.6 version number - never what.

I haven't looked at it any further.



That's what I've figured out. But functions that are supposed to be  
included in 3.2.2 are not included in this
built library. Plus, the generated "sqlite3.h" includes one, but not  
the other. So, I still get linker reported

symbol errors for these two functionsand I'm stuck.

What about Linux version ? Everything is OK ? Maybe the OS X build  
system is broken ?


Regards.

Dom.

Re: [sqlite] 3.2.2 MacOS X = 3.0.8.6 ?

[Dirk Theisen]

Hi!


So it looks like a mistery to me. Which sqlite version I need to
compile in order to be able to use these two functions ?


It seems to be that the build system for OS X always assigns the 
3.0.8.6 version number - never what.

I haven't looked at it any further.

BTW: Anyone used CoreData with a newer version of sqlite *without* 
replacing the installed one.


Greetings,
  Dirk

Re: [sqlite] What happens to unused space?

[Dirk Jagdmann]

So perhaps I could add some code to sqlite to overwrite deleted data 
when it is marked as unused/deleted.


If I do this*, is anyone else interested in it?  Obviously it would be a 
compile time option, as it would probably impact performance (but not to 
a level that matters for my application).


It would be a very good and reliable security feature which other open 
source databases lack too. Having such a feature would be a very good 
argument in favour of sqlite for security aware developers and from my 
point of view greatly increases sqlite's value for embedded programmers 
(who would use it in memory features).


--
---> doj / cubic
> http://cubic.org/~doj
-> http://llg.cubic.org

Re: [sqlite] 3.2.2 MacOS X = 3.0.8.6 ?

[Dominic]

Le 4 juil. 05 à 18:40, Gwendolynn ferch Elydyr a écrit :


On Mon, 4 Jul 2005, Dominic wrote:

I've just compiled 3.2.2 version on MacOS X (both Panther-10.3.X  
and Tiger-10.4.X) and I was surprised that generated libsql3.dylib  
is labelled version 3.0.8.6, with a sqlite3.h that doesn't  
integrate functions like "sqlite3_clear_bindings()" and  
"sqlite3_transfer_bindings()" that I would like to use.




How were you finding libsql3.dylib?  Tiger at least ships with  
libsqlite

and has version 3.0.8.6 in /usr/lib ...



Yes, this is why I would like ton compile and use a more recent  
build ;-)


Tiger is delivered with sqlite "3.1.1" but the .dylib has a version  
number : 3.0.8.6. I was thinking that this is why I couldn't get access
to the functions "sqlite3_clear_bindings()" and  
"sqlite3_transfer_bindings()". So I decided to download last sources  
(3.2.2) from
sqlite.org, then compile it. So it was a bad surprise when I saw that  
the generated .dylib was a 3.0.8.6 too, without these two functions

I saw in the online doc...

So it looks like a mistery to me. Which sqlite version I need to  
compile in order to be able to use these two functions ?


Thanks.

Dom.

Re: [sqlite] What happens to unused space?

[Tim Browse]

Dan Kennedy wrote:

>> So in summary, when I delete a blob, I want it to stay deleted, even 
from prying eyes with a hex editor - what's the best way to do this in 
the SQLite model?

>
>
> Tricky question.


So, judging from this and other replies, there isn't a 'safe' way of 
doing this at the moment with SQLite.


So perhaps I could add some code to sqlite to overwrite deleted data 
when it is marked as unused/deleted.


If I do this*, is anyone else interested in it?  Obviously it would be a 
compile time option, as it would probably impact performance (but not to 
a level that matters for my application).


Tim

* Assuming I don't go insane trying to work out how sqlite works.

[sqlite] Probleme with Java SQLite Wrapper

[majed chatti]

my problem is a litel stange.
Firstly I work with eclipse and Java SQLite Wrapper dounloaded at this site and 
that normally works well http://www.ch-werner.de
 
I open a sqlite DB and I execute the query
I do same think like this
 
query = "select name from sqlite_master where type=table";
DataBase DB;
TableResult TR;
 
DB.open(file.db);
TR = DB.GetTable(query);
System.out.println(TR);
 
 
in my DB I have 15 tables but after the System.out I found same times 15, 12, 
10
 
I really dont know why if there is a problem it will be wrrong evry time no
is same one know same think about this???
 
 thanks for help
 
Majed CHATTI.
 
 


-
 Appel audio GRATUIT partout dans le monde avec le nouveau Yahoo! Messenger
 Téléchargez le ici !  

Re: [sqlite] 3.2.2 MacOS X = 3.0.8.6 ?

[Gwendolynn ferch Elydyr]

On Mon, 4 Jul 2005, Dominic wrote:
I've just compiled 3.2.2 version on MacOS X (both Panther-10.3.X and 
Tiger-10.4.X) and I was surprised that generated libsql3.dylib is labelled 
version 3.0.8.6, with a sqlite3.h that doesn't integrate functions like 
"sqlite3_clear_bindings()" and "sqlite3_transfer_bindings()" that I would 
like to use.


How were you finding libsql3.dylib?  Tiger at least ships with libsqlite
and has version 3.0.8.6 in /usr/lib ...

cheers!
==
"A cat spends her life conflicted between a deep, passionate and profound
desire for fish and an equally deep, passionate and profound desire to
avoid getting wet.  This is the defining metaphor of my life right now."

Re: [sqlite] What happens to unused space?

[Tom Shaw]

At 2:24 PM +0100 7/4/05, Tim Browse wrote:

Hi,

Before I go diving into the SQLite source code, can anyone tell me 
what happens to unused space? (i.e. from deleted data in the db)


It gets reused unless you use VACUUM in which case it could end up 
reused in the DB or reused in the OS file system


Specifically, I'm interested in whether it gets over-written by 
zeroes, or something.  I'm storing information in a database (in a 
blob field), which the user can then decide to encrypt, and then the 
plaintext blob is set to NULL, and the ciphertext blob (i.e. a 
different column) is set to contain the encrypted data.


So what I'm asking is, in this scenario, is it possible that 
sometimes I could load the sqlite db into a hex editor, and see the 
original unencrypted data? (i.e. the old deleted data from the 
plaintext blob)


Yes and, in fact, you might also see it in a disk drive hex editor on 
some free sector or in the VM swap area.


If so, a workaround is obviously to set the original blob data to 
contain a block of zeroes (of the same length), and *then* set it to 
NULL.  But then it's also within the realms of possibility (not 
wishing to criticise anyone's code; only guessing) that SQLite might 
reallocate a block for the blob even if it's the same length as the 
old data, in which case my cunning plan wouldn't work either.


Actually, as long as you don't release the space you have control of 
it during your rewriting.


So in summary, when I delete a blob, I want it to stay deleted, even 
from prying eyes with a hex editor - what's the best way to do this 
in the SQLite model?


Overwrite exactly and commit the update before you release the 
storage by committing NULL Depending on how paranoid you are, you 
will have to overwrite all disk based data with all zeros, all ones 
and then alternating ones and zeros or else one can reconstruct the 
data if one physically has possession of the media.  Indeed, you will 
also need to zero all buffers in your normal course of business 
because they may be staged to your VM disk.


NB. To pre-empt various invigorating arguments, I know about the 
various 'encrypt the whole db' solutions that are available, but I 
don't need them; I have my own encryption framework. I just want to 
know how to *really* delete data from the DB (without calling VACUUM 
after every update).


Calling VACUUM just moves the problem to the OS' file system.

Tom

Re: [sqlite] What happens to unused space?

[Claudio Bezerra Leopoldino]

About the blob delection issue, i have a sugestion:
- At the same blob deletion transaction, before the
delection sql code, update the blob with an standard
blob without relevant meaning, overwriting the
original. Then an hacker will read the standard blob
in the deleted blob space.  

Well... it have performance issues, but i didn't
tests.

Cláudio

> > So in summary, when I delete a blob, I want it to
> stay deleted, even 
> > from prying eyes with a hex editor - what's the
> best way to do this in 
> > the SQLite model?
> 
> Tricky question.
> 
> 






___ 
Yahoo! Acesso Grátis - Internet rápida e grátis. 
Instale o discador agora! http://br.acesso.yahoo.com/

Re: [sqlite] What happens to unused space?

[Dan Kennedy]

> Hi,
> 
> Before I go diving into the SQLite source code, can anyone tell me what 
> happens to unused space? (i.e. from deleted data in the db)
> 
> Specifically, I'm interested in whether it gets over-written by zeroes, 
> or something.  I'm storing information in a database (in a blob field), 
> which the user can then decide to encrypt, and then the plaintext blob 
> is set to NULL, and the ciphertext blob (i.e. a different column) is set 
> to contain the encrypted data.
> 
> So what I'm asking is, in this scenario, is it possible that sometimes I 
> could load the sqlite db into a hex editor, and see the original 
> unencrypted data? (i.e. the old deleted data from the plaintext blob)

It could happen.
 
> If so, a workaround is obviously to set the original blob data to 
> contain a block of zeroes (of the same length), and *then* set it to 
> NULL.  But then it's also within the realms of possibility (not wishing 
> to criticise anyone's code; only guessing) that SQLite might reallocate 
> a block for the blob even if it's the same length as the old data, in 
> which case my cunning plan wouldn't work either.

I can contrive a situation where that would happen. I think.

> So in summary, when I delete a blob, I want it to stay deleted, even 
> from prying eyes with a hex editor - what's the best way to do this in 
> the SQLite model?

Tricky question.




 
Yahoo! Sports 
Rekindle the Rivalries. Sign up for Fantasy Football 
http://football.fantasysports.yahoo.com

[sqlite] 3.2.2 MacOS X = 3.0.8.6 ?

[Dominic]

Hello,

I've just compiled 3.2.2 version on MacOS X (both Panther-10.3.X and  
Tiger-10.4.X) and I was surprised that generated libsql3.dylib is  
labelled version 3.0.8.6, with a sqlite3.h that doesn't integrate  
functions like "sqlite3_clear_bindings()" and  
"sqlite3_transfer_bindings()" that I would like to use.


is there any explanation about this mystery ?

Thanks

Dom.

Re: [sqlite] Database LOCK on SELECT only - BUG ?

[Massimo Gaspari]

I am now able to replicate the error using NT as well. So the problem is
not related to XP only.

Massimo

[sqlite] Database LOCK on SELECT only - BUG ?

[Massimo Gaspari]

As reported some days ago I got a message "database is locked" in an
application using a SELECT statement only.

Some facts.

A) I am using Sqlite 3.2.2 under Windows using MinGW (gcc 3.4.2).
B) It seems that the problem is present if the application is running under
Windows XP Pro Service Pack 1 . Using Windows NT the application seems
running OK.
C) When the application starts it opens the database and then check the
table "DatabaseVersion" to get the current version of my data. Is a
number used to verify the compatibility between the client and the current
structure of the database

The application code is


 SqlError = sqlite3_open(DatabasePath , );

if (SqlError != SQLITE_OK)
{
  MessageBox(NULL,sqlite3_errmsg(mysql),"Error!",MB_OK | MB_TASKMODAL);
  return 0;
}

#ifdef HAVE_LOG
sprintf(buffer,"Database %s opened.",DatabasePath);
WriteSQLLogFile(buffer);
#endif

sprintf(buffer,"SELECT Version FROM DatabaseVersion");

SqlError = sqlite3_get_table(mysql,bufferNULL);  /*
SQLITE_BUSY Here */

if (SqlError != SQLITE_OK)
{
  MessageBox(NULL,sqlite3_errmsg(mysql),"Error!",MB_OK | MB_TASKMODAL);
  return 0;
}

if (strcmp(resultp[1],SQL_DATABASE_VERSION) )
 {
  MessageBox(NULL,"Program not Compatible with Current Database. Please
Install A New Version!","Database Version Error !",MB_OK | MB_TASKMODAL);
  return 0;
 }



During this operation I got a SQLITE_BUSY form the call in the line marked
as  /* SQLITE_BUSY Here */. The database file is stored in a file server
(not in the PC running the application)
and it was already opened from another client by another person.


I compiled the SQLite with the debugging flag turned on to trace the flow
of the program. DRH suggested to verify the return value of isNT(). But
this function is not called before the
error message. I put a break on the file os_win.c line 482 ( if(
id->locktype>=locktype ){ ). Running the debugger I got



GNU gdb 5.2.1
Copyright 2002 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you
are
welcome to change it and/or distribute copies of it under certain
conditions.
Type "show copying" to see the conditions.
There is absolutely no warranty for GDB.  Type "show warranty" for details.
This GDB was configured as "i686-pc-mingw32"...
(gdb) b os_win.c:482
Breakpoint 1 at 0x46c8e4: file os_win.c, line 482.
(gdb) run
Starting program:
C:\C\Projects\ComponentDatabase\objects\ComponentDatabase.exe


Breakpoint 1, sqlite3OsLock (id=0x3e4808, locktype=1) at os_win.c:482
482   if( id->locktype>=locktype ){
(gdb) display locktype
1: locktype = 1
(gdb) display res
2: res = 1
(gdb) display rc
3: rc = 0
(gdb) display id->locktype
4: id->locktype = 0 '\0'
(gdb) n
496   newLocktype = id->locktype;
4: id->locktype = 0 '\0'
3: rc = 0
2: res = 1
1: locktype = 1
(gdb) n
497   if( id->locktype==NO_LOCK
4: id->locktype = 0 '\0'
3: rc = 0
2: res = 1
1: locktype = 1
(gdb) n
500 int cnt = 3;
4: id->locktype = 0 '\0'
3: rc = 0
2: res = 1
1: locktype = 1
(gdb) n
501 while( cnt-->0 && (res = LockFile(id->h, PENDING_BYTE, 0, 1,
0))==0
){
4: id->locktype = 0 '\0'
3: rc = 0
2: res = 1
1: locktype = 1
(gdb) n
506   Sleep(1);
4: id->locktype = 0 '\0'
3: rc = 0
2: res = 0
1: locktype = 1
(gdb) n
501 while( cnt-->0 && (res = LockFile(id->h, PENDING_BYTE, 0, 1,
0))==0
){
4: id->locktype = 0 '\0'
3: rc = 0
2: res = 0
1: locktype = 1
(gdb) n
506   Sleep(1);
4: id->locktype = 0 '\0'
3: rc = 0
2: res = 0
1: locktype = 1
(gdb) n
501 while( cnt-->0 && (res = LockFile(id->h, PENDING_BYTE, 0, 1,
0))==0
){
4: id->locktype = 0 '\0'
3: rc = 0
2: res = 0
1: locktype = 1
(gdb) n
506   Sleep(1);
4: id->locktype = 0 '\0'
3: rc = 0
2: res = 0
1: locktype = 1
(gdb) n
501 while( cnt-->0 && (res = LockFile(id->h, PENDING_BYTE, 0, 1,
0))==0
){
4: id->locktype = 0 '\0'
3: rc = 0
2: res = 0
1: locktype = 1
(gdb) n
508 gotPendingLock = res;
4: id->locktype = 0 '\0'
3: rc = 0
2: res = 0
1: locktype = 1
(gdb) n
513   if( locktype==SHARED_LOCK && res ){
4: id->locktype = 0 '\0'
3: rc = 0
2: res = 0
1: locktype = 1
(gdb) n
523   if( locktype==RESERVED_LOCK && res ){
4: id->locktype = 0 '\0'
3: rc = 0
2: res = 0
1: locktype = 1
(gdb) n
533   if( locktype==EXCLUSIVE_LOCK && res ){
4: id->locktype = 0 '\0'
3: rc = 0
2: res = 0
1: locktype = 1
(gdb) n
540   if( locktype==EXCLUSIVE_LOCK && res ){
4: id->locktype = 0 '\0'
3: rc = 0
2: res = 0
1: locktype = 1
(gdb) n
555   if( gotPendingLock && locktype==SHARED_LOCK ){
4: id->locktype = 0 '\0'
3: rc = 0
2: res = 0
1: locktype = 1
(gdb) n
562   if( res ){
4: id->locktype = 0 '\0'
3: rc = 0
2: res = 0
1: locktype = 1
(gdb) n
567 rc = SQLITE_BUSY;
4: id->locktype = 0 '\0'
3: rc = 0
2: res = 0
1: locktype = 1
(gdb) n
569   id->locktype = newLocktype;
4: id->locktype = 0 '\0'
3: rc = 5
2: res = 0
1: locktype = 1
(gdb) n
570   return rc;
4: id->locktype = 

[sqlite] What happens to unused space?

[Tim Browse]

Hi,

Before I go diving into the SQLite source code, can anyone tell me what 
happens to unused space? (i.e. from deleted data in the db)


Specifically, I'm interested in whether it gets over-written by zeroes, 
or something.  I'm storing information in a database (in a blob field), 
which the user can then decide to encrypt, and then the plaintext blob 
is set to NULL, and the ciphertext blob (i.e. a different column) is set 
to contain the encrypted data.


So what I'm asking is, in this scenario, is it possible that sometimes I 
could load the sqlite db into a hex editor, and see the original 
unencrypted data? (i.e. the old deleted data from the plaintext blob)


If so, a workaround is obviously to set the original blob data to 
contain a block of zeroes (of the same length), and *then* set it to 
NULL.  But then it's also within the realms of possibility (not wishing 
to criticise anyone's code; only guessing) that SQLite might reallocate 
a block for the blob even if it's the same length as the old data, in 
which case my cunning plan wouldn't work either.


So in summary, when I delete a blob, I want it to stay deleted, even 
from prying eyes with a hex editor - what's the best way to do this in 
the SQLite model?


NB. To pre-empt various invigorating arguments, I know about the various 
'encrypt the whole db' solutions that are available, but I don't need 
them; I have my own encryption framework. I just want to know how to 
*really* delete data from the DB (without calling VACUUM after every 
update).


Tim

PS. Couldn't see anything obvious about this in the docs from a quick 
search, but if anyone has a link that explains all this, then apologies, 
and let me at it! :)

[sqlite] INTEGER data type

[Ajay]

Hello All,

What is the maximum number that can be stored using INTEGER data type? My
table uses INTEGER to store primary key, I store lot of records in table. I
suspect that primary key will run out of limit. How can I use long instead
of INTEGER?

 

 

Regards,

Ajay Sonawane

RE: [sqlite] SQLite and VB 2005 Express Edition

[Chris Moody]

-Original Message-
From: Robert Simpson [mailto:[EMAIL PROTECTED] 
Sent: Sunday, July 03, 2005 8:30 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] SQLite and VB 2005 Express Edition

> -Original Message-
> From: Chris Moody [mailto:[EMAIL PROTECTED] 
> Sent: Sunday, July 03, 2005 3:32 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] SQLite and VB 2005 Express Edition
> 
> Hi all,
> 
> I'm currently using the public beta 2 of M$'s VB 2005 Express 
> Edition. Is it possible to get SQLite to work with it?

Yep.

http://sourceforge.net/projects/sqlite-dotnet2


Thanks, now just need to figure out how to get into the program.

Chris