[sqlite] [ANN] 1st pre-alpha release of the SQLite OLE/DB provider
Hi all.
I just finished a first version of the SQLite OLE/DB provider.
It only implements the basic ADO functionality (execute SQL command and
Recordset view).
I make it available for all (free of charge, with no limitations), so I
can get feedback on the OLE/DB functionality people want most.
You can get it at: http://xpto.ath.cx/sqlite
It includes a sqlite3.dll, so you can change to a more recent sqlite
version without problems.
You need to register it using "regsvr32.exe sqliteoledb.dll"
A basic ADO usage case (in javascript, as I don't know/remember VB), is
attached (and can be found at the directory).
Let me know what it doesn't work in your case, so I know what I need to
implement next (and any bugs, off course ;)
You can contact me at: [EMAIL PROTECTED] or [EMAIL PROTECTED]
Best regards,
~Nuno Lucas
//
// Run this in a command prompt using a line like this:
// c:\> cscript demo.js
//
/
* Utility functions
***/
function JustLeft( str, n )
{
if ( str.length > n )
return str;
for ( left = n - str.length; left >= 0; --left )
str += ' ';
return str;
}
function PrintProps( out, props )
{
for ( n = 0; n < props.Count; ++n )
{
tmp = JustLeft( props(n).Name, 40 );
out.Write( " " + tmp );
out.WriteLine( props(n) );
}
}
function DumpRecordset( out, rs )
{
// Print column names
out.WriteLine( "===" );
flds = rs.Fields;
for ( i = 0; i < flds.Count; ++i )
{
f = flds.Item(i);
out.Write( JustLeft(f.Name,8) );
}
if ( flds.Count > 0 )
{
out.WriteLine( );
out.WriteLine( "---" );
}
else
out.WriteLine( "[No records]" );
while ( !rs.EOF )
{
flds = rs.Fields;
for ( i = 0; i < flds.Count; ++i )
{
f = flds.Item(i);
out.Write( JustLeft(f.Value,8) );
}
out.WriteLine( );
rs.MoveNext( );
}
out.WriteLine( "===" );
}
/
* Main Program
***/
connString = "Provider=OleDb.SqliteProv; Location='demo.db'";
out = WScript.StdOut;
// Create the ADO conection object and open the database
conn = new ActiveXObject( "ADODB.Connection" );
conn.Open( connString );
// Show Connection properties
out.WriteLine( "ADO.Connection.Properties:" );
PrintProps( out, conn.Properties );
out.writeLine( );
// Do a simple integrey check
out.WriteLine( "PRAGMA integrity_check" );
rs = conn.Execute( "PRAGMA integrity_check" );
DumpRecordset( out, rs );
// Create a table (will fail on second time, off course)
// and insert some rows
conn.Execute( "CREATE TABLE t1 ( x INTEGER PRIMARY KEY, y, z )" );
conn.Execute( "INSERT INTO t1(x,y,z) VALUES(NULL,'a field','1 more field')")
conn.Execute( "INSERT INTO t1(x,y,z) VALUES(NULL,'hello',76342)")
conn.Execute( "INSERT INTO t1(x,y,z) VALUES(NULL,'hello','world!')")
// Show existing tables
out.WriteLine( "SELECT * FROM sqlite_master:" );
rs = conn.Execute( "SELECT * FROM sqlite_master" );
DumpRecordset( out, rs );
// Show rows of the table 't1'
out.WriteLine( "SELECT * FROM t1:" );
rs = conn.Execute( "SELECT * FROM t1" );
DumpRecordset( out, rs );
Re: [sqlite] New DBD::SQLite*s
At 4:51 PM +0100 9/10/04, Matt Sergeant wrote: Uploaded to CPAN are DBD::SQLite 1.05 and DBD::SQLite2 0.33 Changes for DBD::SQLite2: 0.33 - Set HAVE_USLEEP appropriately. This massively improves concurrent access to your SQLite DB. Changes for DBD::SQLite: 1.05 - Enabled HAVE_USLEEP where available which should massively improve concurrent usage of DBD::SQLite - Update to sqlite 3.0.6 Hey, just in time! As it stands, I had myself last night just made another round of CPAN uploads. As preparation for the next round, that I am starting to work on today, I would be updating all my dependencies to the newest versions, so I can include yours. With this round, I will start using the new stuff like named host parameters. -- Darren Duncan
Re: [sqlite] Encrypting data stored in database
No argument here. We're targeting x86 and xScale architectures so we're
able to reference DWORD objects on non-DWORD boundaries. I never really
meant to distribute this code so we only tried to get it working for our
purposes. I just hope someone can get some use from it.
Joey.
At 04:11 PM 9/10/2004, you wrote:
Joey Blankenship <[EMAIL PROTECTED]> writes:
> if( !ReadFile(id->h, pBuf, amt, , 0) ){
> got = 0;
> }
>
> // PPD - XOR the buffer with a pattern so the disk file contents are not
> in plain text
> for (i = 0; i < got/4; i++)
> {
> *((DWORD *)((DWORD *)pBuf + i)) = (*((DWORD *)((DWORD *)pBuf +
> i)))^0xA5A5A5A5;
> }
DANGER, DANGER, Will Robinson! If pBuf is not pointing to a boundary that is
legal on the hardware architecture for a DWORD pointer, this will crash the
program. It may work, either because the hardware architecture allows for
dereferencing DWORD pointers at any address, or because pBuf is always on such
a boundary, but may fail later if other changes are later made.
> // XOR the buffer with a pattern - any leftover bytes
> for (i = 0; i < got%4; i++)
> {
> *((BYTE *)((BYTE *)pBuf + i)) = (*((BYTE *)((BYTE *)pBuf + i)))^0xA5;
> }
I don't believe that this is doing what you expect. The above DWORD section
did not update pBuf, so this is manipulating the *first* 0-3 bytes, not the
0-3 bytes at the end, i.e. the leftover bytes, as you desire.
Although potentially minimally slower, I'd just do a byte-by-by XOR. The fact
that you're doing the pointer addition (twice) each iteration is probably
slower anyway (although optimizers are mighty good these days).
Here's an alternative solution that replaces both sets of loops:
{
char * p;
char * pend;
for (p = pBuf, pEnd = pBuf + got; p < pEnd; )
{
*p++ ^= 0xA5;
}
}
Cheers,
Derrell
RE: [sqlite] Encrypting data stored in database
At the beginning of main.c after the includes:
int ppdbuffersize = 10 * 1024;
void *ppdbuffer;
Also in main.c at the end of sqlite3_close before the return statement:
free(ppdbuffer);
Also in main.c at the beginning of openDatabase, after the declarations:
ppdbuffer = malloc(ppdbuffersize);
Now I know that this has some problems if you open more that one database
at a time or maybe if you are multithreading. But remember, we had a very
specific purpose in mind. For a more general purpose approach, I'd
probably either keep a reference count for the buffer and free it when I
hit 0, or keep a separate buffer for each database.
Joey.
At 04:06 PM 9/10/2004, you wrote:
Joey,
You are going to have to excuse my ignorance of c++, but I am a bit
confused as far as the ppdbuffersize and ppdbuffer. Is there additional
code I would need to add elsewhere for these variables, and if so, would
you mind including those as well??
I'm very sorry if this is really stupid, but I'm stuck in a C#.Net world
and don't have to deal with this stuff.
Thanks!
Bob
-Original Message-
From: Joey Blankenship [mailto:[EMAIL PROTECTED]
Sent: Friday, September 10, 2004 2:48 PM
To: [EMAIL PROTECTED]
Subject: RE: [sqlite] Encrypting data stored in database
I'm including the routines that we modified. I hate to send the whole
file
around the entire list. The ppdbuffer and ppdbuffersize are set
initially
when the database is opened and closed. The current implementation may
not
be threadsafe, but we are single threaded.
extern int ppdbuffersize;
extern void *ppdbuffer;
int sqlite3OsRead(OsFile *id, void *pBuf, int amt){
DWORD got;
int i;
assert( id->isOpen );
SimulateIOError(SQLITE_IOERR);
TRACE3("READ %d lock=%d\n", id->h, id->locktype);
if( !ReadFile(id->h, pBuf, amt, , 0) ){
got = 0;
}
// PPD - XOR the buffer with a pattern so the disk file contents are
not
in plain text
for (i = 0; i < got/4; i++)
{
*((DWORD *)((DWORD *)pBuf + i)) = (*((DWORD *)((DWORD *)pBuf +
i)))^0xA5A5A5A5;
}
// XOR the buffer with a pattern - any leftover bytes
for (i = 0; i < got%4; i++)
{
*((BYTE *)((BYTE *)pBuf + i)) = (*((BYTE *)((BYTE *)pBuf +
i)))^0xA5;
}
if( got==(DWORD)amt ){
return SQLITE_OK;
}else{
return SQLITE_IOERR;
}
}
int sqlite3OsWrite(OsFile *id, const void *pBuf, int amt){
int rc;
DWORD wrote;
int i;
if (ppdbuffersize < amt)
{
ppdbuffersize = amt + 1024;
ppdbuffer = realloc(ppdbuffer, ppdbuffersize);
}
// PPD - XOR the buffer with a pattern so the disk file contents are
not
in plain text
for (i = 0; i < amt/4; i++)
{
*((DWORD *)((DWORD *)ppdbuffer + i)) = (*((DWORD *)((DWORD *)pBuf +
i)))^0xA5A5A5A5;
}
// XOR the buffer with a pattern - any leftover bytes
for (i = 0; i < amt%4; i++)
{
*((BYTE *)((BYTE *)ppdbuffer + i)) = (*((BYTE *)((BYTE *)pBuf +
i)))^0xA5;
}
pBuf = ppdbuffer;
assert( id->isOpen );
SimulateIOError(SQLITE_IOERR);
TRACE3("WRITE %d lock=%d\n", id->h, id->locktype);
while( amt>0 && (rc = WriteFile(id->h, pBuf, amt, , 0))!=0 &&
wrote>0 ){
amt -= wrote;
pBuf = &((char*)pBuf)[wrote];
}
if( !rc || amt>(int)wrote ){
return SQLITE_FULL;
}
return SQLITE_OK;
}
At 03:03 PM 9/10/2004, you wrote:
>Joey,
>
>Would you mind sharing the modifications you made to the os_win.c file?
>I would be interested in using such a modification, unfortunately, c++
>isn't my strongest language.
>
>Thanks!
>
>Bob
Re: [sqlite] Storing text in sqlite vs. external flat file
If you decide to store the text in an external file, you might check out this library to make this process a bit more fault tolerant (like sqlite): http://users.auriga.wearlab.de/~alb/libjio/ Cheers! -j On Fri, 10 Sep 2004 13:21:08 -0700, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > I am looking into using sqlite for storing some data that will be ~100,000 > records in size, where each record will contain text that has an average > size of 40k, but could be > 200k. I will likely need to encrypt (and > potentially compress) the database. > > My question is whether to store the text in the database, or to keep a > separate file for the text with seek pointers and lengths in the database. > My preference is for the former, since I wouldn't have to manage a separate > robust encryption/compression/deletion process, but I am concerned about the > perf and size of the insertion stress experiments I've been running, and > I've seen comments on this list suggesting the latter for BLOBs. Are there > specific tuning tweaks I can make to improve my results? > > Thanks, > > Ken Cooper > >
[sqlite] Storing text in sqlite vs. external flat file
I am looking into using sqlite for storing some data that will be ~100,000 records in size, where each record will contain text that has an average size of 40k, but could be > 200k. I will likely need to encrypt (and potentially compress) the database. My question is whether to store the text in the database, or to keep a separate file for the text with seek pointers and lengths in the database. My preference is for the former, since I wouldn't have to manage a separate robust encryption/compression/deletion process, but I am concerned about the perf and size of the insertion stress experiments I've been running, and I've seen comments on this list suggesting the latter for BLOBs. Are there specific tuning tweaks I can make to improve my results? Thanks, Ken Cooper
Re: [sqlite] Encrypting data stored in database
Joey Blankenship <[EMAIL PROTECTED]> writes:
> if( !ReadFile(id->h, pBuf, amt, , 0) ){
> got = 0;
> }
>
> // PPD - XOR the buffer with a pattern so the disk file contents are not
> in plain text
> for (i = 0; i < got/4; i++)
> {
> *((DWORD *)((DWORD *)pBuf + i)) = (*((DWORD *)((DWORD *)pBuf +
> i)))^0xA5A5A5A5;
> }
DANGER, DANGER, Will Robinson! If pBuf is not pointing to a boundary that is
legal on the hardware architecture for a DWORD pointer, this will crash the
program. It may work, either because the hardware architecture allows for
dereferencing DWORD pointers at any address, or because pBuf is always on such
a boundary, but may fail later if other changes are later made.
> // XOR the buffer with a pattern - any leftover bytes
> for (i = 0; i < got%4; i++)
> {
> *((BYTE *)((BYTE *)pBuf + i)) = (*((BYTE *)((BYTE *)pBuf + i)))^0xA5;
> }
I don't believe that this is doing what you expect. The above DWORD section
did not update pBuf, so this is manipulating the *first* 0-3 bytes, not the
0-3 bytes at the end, i.e. the leftover bytes, as you desire.
Although potentially minimally slower, I'd just do a byte-by-by XOR. The fact
that you're doing the pointer addition (twice) each iteration is probably
slower anyway (although optimizers are mighty good these days).
Here's an alternative solution that replaces both sets of loops:
{
char * p;
char * pend;
for (p = pBuf, pEnd = pBuf + got; p < pEnd; )
{
*p++ ^= 0xA5;
}
}
Cheers,
Derrell
RE: [sqlite] Encrypting data stored in database
Joey,
You are going to have to excuse my ignorance of c++, but I am a bit
confused as far as the ppdbuffersize and ppdbuffer. Is there additional
code I would need to add elsewhere for these variables, and if so, would
you mind including those as well??
I'm very sorry if this is really stupid, but I'm stuck in a C#.Net world
and don't have to deal with this stuff.
Thanks!
Bob
-Original Message-
From: Joey Blankenship [mailto:[EMAIL PROTECTED]
Sent: Friday, September 10, 2004 2:48 PM
To: [EMAIL PROTECTED]
Subject: RE: [sqlite] Encrypting data stored in database
I'm including the routines that we modified. I hate to send the whole
file
around the entire list. The ppdbuffer and ppdbuffersize are set
initially
when the database is opened and closed. The current implementation may
not
be threadsafe, but we are single threaded.
extern int ppdbuffersize;
extern void *ppdbuffer;
int sqlite3OsRead(OsFile *id, void *pBuf, int amt){
DWORD got;
int i;
assert( id->isOpen );
SimulateIOError(SQLITE_IOERR);
TRACE3("READ %d lock=%d\n", id->h, id->locktype);
if( !ReadFile(id->h, pBuf, amt, , 0) ){
got = 0;
}
// PPD - XOR the buffer with a pattern so the disk file contents are
not
in plain text
for (i = 0; i < got/4; i++)
{
*((DWORD *)((DWORD *)pBuf + i)) = (*((DWORD *)((DWORD *)pBuf +
i)))^0xA5A5A5A5;
}
// XOR the buffer with a pattern - any leftover bytes
for (i = 0; i < got%4; i++)
{
*((BYTE *)((BYTE *)pBuf + i)) = (*((BYTE *)((BYTE *)pBuf +
i)))^0xA5;
}
if( got==(DWORD)amt ){
return SQLITE_OK;
}else{
return SQLITE_IOERR;
}
}
int sqlite3OsWrite(OsFile *id, const void *pBuf, int amt){
int rc;
DWORD wrote;
int i;
if (ppdbuffersize < amt)
{
ppdbuffersize = amt + 1024;
ppdbuffer = realloc(ppdbuffer, ppdbuffersize);
}
// PPD - XOR the buffer with a pattern so the disk file contents are
not
in plain text
for (i = 0; i < amt/4; i++)
{
*((DWORD *)((DWORD *)ppdbuffer + i)) = (*((DWORD *)((DWORD *)pBuf +
i)))^0xA5A5A5A5;
}
// XOR the buffer with a pattern - any leftover bytes
for (i = 0; i < amt%4; i++)
{
*((BYTE *)((BYTE *)ppdbuffer + i)) = (*((BYTE *)((BYTE *)pBuf +
i)))^0xA5;
}
pBuf = ppdbuffer;
assert( id->isOpen );
SimulateIOError(SQLITE_IOERR);
TRACE3("WRITE %d lock=%d\n", id->h, id->locktype);
while( amt>0 && (rc = WriteFile(id->h, pBuf, amt, , 0))!=0 &&
wrote>0 ){
amt -= wrote;
pBuf = &((char*)pBuf)[wrote];
}
if( !rc || amt>(int)wrote ){
return SQLITE_FULL;
}
return SQLITE_OK;
}
At 03:03 PM 9/10/2004, you wrote:
>Joey,
>
>Would you mind sharing the modifications you made to the os_win.c file?
>I would be interested in using such a modification, unfortunately, c++
>isn't my strongest language.
>
>Thanks!
>
>Bob
Re: [sqlite] temp files
Dmytro Bogovych wrote: On Fri, 10 Sep 2004 15:31:00 -0400, D. Richard Hipp <[EMAIL PROTECTED]> wrote: SQLite is using the temporary file to hold a statement-level rollback journal so that the partial results of the UPDATE can be rolled back if it encounters an error half way through. You can circumvent this by doing UPDATE OR ROLLBACK log: SQLite version 3.0.6 Enter ".help" for instructions sqlite> begin; sqlite> update or rollback ITEMS set number=number+1 where number>2; -- here is breakpoint triggering -- OK. My workaround trick didn't work afterall. Looks like you are stuck with a temporary file when doing an UPDATE or a mass INSERT inside a transaction. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
RE: [sqlite] Encrypting data stored in database
I'm including the routines that we modified. I hate to send the whole file
around the entire list. The ppdbuffer and ppdbuffersize are set initially
when the database is opened and closed. The current implementation may not
be threadsafe, but we are single threaded.
extern int ppdbuffersize;
extern void *ppdbuffer;
int sqlite3OsRead(OsFile *id, void *pBuf, int amt){
DWORD got;
int i;
assert( id->isOpen );
SimulateIOError(SQLITE_IOERR);
TRACE3("READ %d lock=%d\n", id->h, id->locktype);
if( !ReadFile(id->h, pBuf, amt, , 0) ){
got = 0;
}
// PPD - XOR the buffer with a pattern so the disk file contents are not
in plain text
for (i = 0; i < got/4; i++)
{
*((DWORD *)((DWORD *)pBuf + i)) = (*((DWORD *)((DWORD *)pBuf +
i)))^0xA5A5A5A5;
}
// XOR the buffer with a pattern - any leftover bytes
for (i = 0; i < got%4; i++)
{
*((BYTE *)((BYTE *)pBuf + i)) = (*((BYTE *)((BYTE *)pBuf + i)))^0xA5;
}
if( got==(DWORD)amt ){
return SQLITE_OK;
}else{
return SQLITE_IOERR;
}
}
int sqlite3OsWrite(OsFile *id, const void *pBuf, int amt){
int rc;
DWORD wrote;
int i;
if (ppdbuffersize < amt)
{
ppdbuffersize = amt + 1024;
ppdbuffer = realloc(ppdbuffer, ppdbuffersize);
}
// PPD - XOR the buffer with a pattern so the disk file contents are not
in plain text
for (i = 0; i < amt/4; i++)
{
*((DWORD *)((DWORD *)ppdbuffer + i)) = (*((DWORD *)((DWORD *)pBuf +
i)))^0xA5A5A5A5;
}
// XOR the buffer with a pattern - any leftover bytes
for (i = 0; i < amt%4; i++)
{
*((BYTE *)((BYTE *)ppdbuffer + i)) = (*((BYTE *)((BYTE *)pBuf + i)))^0xA5;
}
pBuf = ppdbuffer;
assert( id->isOpen );
SimulateIOError(SQLITE_IOERR);
TRACE3("WRITE %d lock=%d\n", id->h, id->locktype);
while( amt>0 && (rc = WriteFile(id->h, pBuf, amt, , 0))!=0 &&
wrote>0 ){
amt -= wrote;
pBuf = &((char*)pBuf)[wrote];
}
if( !rc || amt>(int)wrote ){
return SQLITE_FULL;
}
return SQLITE_OK;
}
At 03:03 PM 9/10/2004, you wrote:
Joey,
Would you mind sharing the modifications you made to the os_win.c file?
I would be interested in using such a modification, unfortunately, c++
isn't my strongest language.
Thanks!
Bob
Re: [sqlite] temp files
On Fri, 10 Sep 2004 15:31:00 -0400, D. Richard Hipp <[EMAIL PROTECTED]> wrote: SQLite is using the temporary file to hold a statement-level rollback journal so that the partial results of the UPDATE can be rolled back if it encounters an error half way through. You can circumvent this by doing UPDATE OR ROLLBACK log: SQLite version 3.0.6 Enter ".help" for instructions sqlite> begin; sqlite> update or rollback ITEMS set number=number+1 where number>2; -- here is breakpoint triggering -- sqlite> commit; sqlite> select * from ITEMS; 1 2 5 6 sqlite> or UPDATE OR IGNORE The same story as with UPDATE OR ROLLBACK :( -- With best regards, Dmytro Bogovych
Re: [sqlite] temp files
Dmytro Bogovych wrote: Greetings. I'm trying to run simple update update TREE set number = number + 1 where child = ? and parent = ? and number > ? During execution of this query the following func is called: static int sqlite3pager_opentemp(char *zFile, OsFile *fd) from pager.c and temporary file is created in my temp directory. Is it expected behaviour? Dmytro Bogovych wrote: sqlite> create table ITEMS(number INTEGER); sqlite> insert into ITEMS(number) values(1); sqlite> insert into ITEMS(number) values(2); sqlite> insert into ITEMS(number) values(3); sqlite> insert into ITEMS(number) values(4); sqlite> begin; sqlite> update ITEMS set number=number+1 where number>2; -- here I've got breakpoint triggering in sqlite3pager_opentemp. SQLite is using the temporary file to hold a statement-level rollback journal so that the partial results of the UPDATE can be rolled back if it encounters an error half way through. You can circumvent this by doing UPDATE OR ROLLBACK or UPDATE OR IGNORE instead of just plain UPDATE -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Locking in 3.0.6
Steve O'Hara wrote: The audit logic is as follows; begin transaction select total from useractivity where event= . If lRows = 0 Then insert into useractivity .. Else update useractivity . End If commit transaction Is useractivity.event UNIQUE? Does it use the UNIQUE keyword in the CREATE TABLE statement or do you have a UNIQUE index on that column. If so, then you might consider just doing a REPLACE instead of an INSERT. If a REPLACE will work for you then you can avoid the complication entirely. If REPLACE does not work for you, then when the INSERT or UPDATE fails because of a lock, you need to do a ROLLBACK and start all over again with the BEGIN TRANSACTION. That will take care of your locking problems. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] temp files
On Fri, 03 Sep 2004 17:17:24 +0300, Dmytro Bogovych <[EMAIL PROTECTED]> wrote: On Fri, 03 Sep 2004 17:07:36 +0300, Dmytro Bogovych <[EMAIL PROTECTED]> wrote: On Fri, 03 Sep 2004 09:12:31 -0400, D. Richard Hipp <[EMAIL PROTECTED]> wrote: Unable to reproduce. I put a breakpoint on sqlite3pager_opentemp() and did lots of UPDATEs in the style shown, but no temporary file was ever opened. I've built simple sqlite3.exe replacement using shell.c as main file with debugging info turned on. The compiler was VC++ 6.0 SP6 There is a log: SQLite version 3.0.6 Enter ".help" for instructions sqlite> create table ITEMS(number INTEGER); sqlite> insert into ITEMS(number) values(1); sqlite> insert into ITEMS(number) values(2); sqlite> insert into ITEMS(number) values(3); sqlite> insert into ITEMS(number) values(4); sqlite> begin; sqlite> update ITEMS set number=number+1 where number>2; -- here I've got breakpoint triggering in sqlite3pager_opentemp. sqlite> commit; sqlite> select * from ITEMS; 1 2 4 5 sqlite> And here is another log without transactions: SQLite version 3.0.6 Enter ".help" for instructions sqlite> create table ITEMS(number INTEGER); sqlite> insert into ITEMS(number) values(1); sqlite> insert into ITEMS(number) values(2); sqlite> insert into ITEMS(number) values(3); sqlite> insert into ITEMS(number) values(4); sqlite> update ITEMS set number=number+1 where number>2; sqlite> select * from ITEMS; 1 2 4 5 sqlite> No temp files. As I can see this behaviour is caused by my attempts to use transactions. Is it expected behaviour? I feel it would be nice to have callbacks for operating with temporary files to be able to provide something like in-memory temporary files. -- Using Opera's revolutionary e-mail client: http://www.opera.com/m2/
RE: [sqlite] Encrypting data stored in database
Joey,
Would you mind sharing the modifications you made to the os_win.c file?
I would be interested in using such a modification, unfortunately, c++
isn't my strongest language.
Thanks!
Bob
-Original Message-
From: Joey Blankenship [mailto:[EMAIL PROTECTED]
Sent: Friday, September 10, 2004 8:00 AM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Encrypting data stored in database
We made the mods in os_win.c, in the read and write routines, after the
read and before the write. A more robust solution would have been to
modify the code in os_mac.c and os_unix.c as well, but we didn't need
that. It was very easy and the only caveat is that once you modify it,
it
will no longer read existing databases. That was not a problem for us,
but
it might be if you want to convert a database.
Joey.
At 06:11 PM 9/9/2004, you wrote:
>Joey,
>
>Where in the code did you do that? Was it easy to put in one or two
places?
>
>-brett
>
>try IeToolbox Passwords & Notes Keeper, Form Filler and much more
>www.ietoolbox.com
>
>
>
>Joey Blankenship wrote:
>
>>Not to respond out of turn here, but we had a need to obfuscate the
plain
>>text (due to some users that are a little too curious for their own
good)
>>that was stored but did not want the performance burden that
>>encryption/decryption would place on the PocketPC. Prior to writing
and
>>subsequent to reading, we just perform a simple mangle of the
>>read/written data. A byte NOT or XOR works pretty well and does not
>>cause an extreme performance hit. Again, no real encryption, but it
>>hides the data from casual viewing.
>>
>>Joey.
>>
>>At 05:34 AM 9/9/2004, you wrote:
>>
>>>Hello sqlite users,
>>>Hello Dr. Hipp,
>>>
>>>I would like to know if there is a way to encrpyt the data stored in
the
>>>database but to still be able to use SQL queries with plain text,
>>>something like
>>>
>>>CREATE TABLE TEST (fld)
>>>INSERT INTO Test VALUES ('some string')
>>>SELECT * FROM Test WHERE fld = 'some string'
>>>
>>>but, when the sqlite database is opened in a file viewer, 'some
string'
>>>would not be visible but only it's encrypted form.
>>>
>>>Regards,
>>>George Ionescu
>>
>>
Re: [sqlite] blob length SUM command
a a wrote: > > Is there a way to sum the blob lengths from a blob column without adding a > "blob length" integer column? > SELECT sum(length(x)) FROM table; -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Newbie's first question about using SQLite in C++
jb, download the sqlite-source-3_0_6.zip file from http://www.sqlite.org/download.html. unzip it. build with the attached VC project file, you will end up with a sqlite3.lib static library. You can build your project with this library and sqlite3.h file. These two files are all you need. good luck. wei
[sqlite] Newbie's first question about using SQLite in C++
I should like to use SQLite from within a VC 2003 NET program. Is this documented somewhere? I do not know which parts to dowload, to start with. I downloaded the .dll but it comes with no .h file and now I am confused. Is there somewhere on the net a sort of tutorial or at least a C++ example? TIA, jb
Re: [sqlite] Encrypting data stored in database
Man this sounds great! I would really like to know
where exactly I can make the changes to encrypt the
database. I would even add a passkey feature (or xor
string) that would be used to offer minor database
obfuscation.
Why don't the development group add something like
this to the source? Is it because Dr Hipp already have
an encryption module? I think we should at least have
a very simple obfuscation feature added to the open
source version of SQLite.
__
Raymond Irving
--- Joey Blankenship <[EMAIL PROTECTED]> wrote:
> We made the mods in os_win.c, in the read and write
> routines, after the
> read and before the write. A more robust solution
> would have been to
> modify the code in os_mac.c and os_unix.c as well,
> but we didn't need
> that. It was very easy and the only caveat is that
> once you modify it, it
> will no longer read existing databases. That was
> not a problem for us, but
> it might be if you want to convert a database.
>
> Joey.
>
> At 06:11 PM 9/9/2004, you wrote:
> >Joey,
> >
> >Where in the code did you do that? Was it easy to
> put in one or two places?
> >
> >-brett
> >
> >try IeToolbox Passwords & Notes Keeper, Form Filler
> and much more
> >www.ietoolbox.com
> >
> >
> >
> >Joey Blankenship wrote:
> >
> >>Not to respond out of turn here, but we had a need
> to obfuscate the plain
> >>text (due to some users that are a little too
> curious for their own good)
> >>that was stored but did not want the performance
> burden that
> >>encryption/decryption would place on the PocketPC.
> Prior to writing and
> >>subsequent to reading, we just perform a simple
> mangle of the
> >>read/written data. A byte NOT or XOR works pretty
> well and does not
> >>cause an extreme performance hit. Again, no real
> encryption, but it
> >>hides the data from casual viewing.
> >>
> >>Joey.
> >>
> >>At 05:34 AM 9/9/2004, you wrote:
> >>
> >>>Hello sqlite users,
> >>>Hello Dr. Hipp,
> >>>
> >>>I would like to know if there is a way to encrpyt
> the data stored in the
> >>>database but to still be able to use SQL queries
> with plain text,
> >>>something like
> >>>
> >>>CREATE TABLE TEST (fld)
> >>>INSERT INTO Test VALUES ('some string')
> >>>SELECT * FROM Test WHERE fld = 'some string'
> >>>
> >>>but, when the sqlite database is opened in a file
> viewer, 'some string'
> >>>would not be visible but only it's encrypted
> form.
> >>>
> >>>Regards,
> >>>George Ionescu
> >>
> >>
>
>
Re: [sqlite] blob length SUM command
select sum(length(col)) from t this should work, if i understand what you want. a a wrote: All, Is there a way to sum the blob lengths from a blob column without adding a "blob length" integer column? Would my architecture be more portable if I just added an integer "blob length" column? Mike - Do you Yahoo!? Yahoo! Mail is new and improved - Check it out!
[sqlite] New DBD::SQLite*s
Uploaded to CPAN are DBD::SQLite 1.05 and DBD::SQLite2 0.33 Changes for DBD::SQLite2: 0.33 - Set HAVE_USLEEP appropriately. This massively improves concurrent access to your SQLite DB. Changes for DBD::SQLite: 1.05 - Enabled HAVE_USLEEP where available which should massively improve concurrent usage of DBD::SQLite - Update to sqlite 3.0.6 __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
[sqlite] blob length SUM command
All, Is there a way to sum the blob lengths from a blob column without adding a "blob length" integer column? Would my architecture be more portable if I just added an integer "blob length" column? Mike - Do you Yahoo!? Yahoo! Mail is new and improved - Check it out!
RE: [sqlite] Encrypting data stored in database
That's true. However we wanted to obfuscate everything about the database,
including the sqlite header. Since our target is a PocketPC, field level
encryption/decryption may have been too much of a performance burden. This
was quick and easy, and although we're not using "real" encryption, it's
enough to discourage our more curious users from sniffing through the data.
As a testimony to the value of SQLite, previous versions of our software
used ADO on the desktop and ADOCE on the PocketPC. The ADO database was
stored in an Access format and we had users that would use Access to
directly browse or modify the database. That wasn't good. In addition,
copying to the device required a DesktopToDevice call which then converted
the desktop Access database to a Pocket Access format. It took several
minutes depending on the number of records. With SQLite the database is
smaller (saving over 2MB on the device), faster (nearly twice as fast), and
we can directly copy the file to the device in 15 or 20 seconds as opposed
to 5 minutes. SQLite is simply an awesome product.
Joey.
At 08:46 AM 9/10/2004, you wrote:
In theory, ( have not tried this with SQLite) you caould make 2 add-in
functions, encrypt(str) and decript(str) then use it as such:
Select decrypt(sensitive_column) from table where id=3;
Insert into table (sensitive_column) values (encrypt('3434 3434 3434
3434'))
To provide field-level encryption.
> -Original Message-
> From: EzTools Support [mailto:[EMAIL PROTECTED]
> Sent: Thursday, September 09, 2004 5:12 PM
> To: [EMAIL PROTECTED]
> Subject: Re: [sqlite] Encrypting data stored in database
>
> Joey,
>
> Where in the code did you do that? Was it easy to put in one or two
> places?
>
> -brett
>
> try IeToolbox Passwords & Notes Keeper, Form Filler and much more
> www.ietoolbox.com
>
>
>
> Joey Blankenship wrote:
>
> > Not to respond out of turn here, but we had a need to obfuscate the
> > plain text (due to some users that are a little too curious for
their
> > own good) that was stored but did not want the performance burden
that
> > encryption/decryption would place on the PocketPC. Prior to writing
> > and subsequent to reading, we just perform a simple mangle of the
> > read/written data. A byte NOT or XOR works pretty well and does not
> > cause an extreme performance hit. Again, no real encryption, but it
> > hides the data from casual viewing.
> >
> > Joey.
> >
> > At 05:34 AM 9/9/2004, you wrote:
> >
> >> Hello sqlite users,
> >> Hello Dr. Hipp,
> >>
> >> I would like to know if there is a way to encrpyt the data stored
in
> >> the database but to still be able to use SQL queries with plain
text,
> >> something like
> >>
> >> CREATE TABLE TEST (fld)
> >> INSERT INTO Test VALUES ('some string')
> >> SELECT * FROM Test WHERE fld = 'some string'
> >>
> >> but, when the sqlite database is opened in a file viewer, 'some
> >> string' would not be visible but only it's encrypted form.
> >>
> >> Regards,
> >> George Ionescu
> >
> >
> >
>
__
This electronic message may contain proprietary and confidential
information of Verint Systems Inc., its affiliates and/or subsidiaries.
The information is intended to be for the use of the individual(s) or
entity(ies) named above. If you are not the intended recipient (or
authorized to receive this e-mail for the intended recipient), you may not
use, copy, disclose or distribute to anyone this message or any
information contained in this message. If you have received this
electronic message in error, please notify us by replying to this e-mail. (1)
Re: [sqlite] Encrypting data stored in database
We made the mods in os_win.c, in the read and write routines, after the
read and before the write. A more robust solution would have been to
modify the code in os_mac.c and os_unix.c as well, but we didn't need
that. It was very easy and the only caveat is that once you modify it, it
will no longer read existing databases. That was not a problem for us, but
it might be if you want to convert a database.
Joey.
At 06:11 PM 9/9/2004, you wrote:
Joey,
Where in the code did you do that? Was it easy to put in one or two places?
-brett
try IeToolbox Passwords & Notes Keeper, Form Filler and much more
www.ietoolbox.com
Joey Blankenship wrote:
Not to respond out of turn here, but we had a need to obfuscate the plain
text (due to some users that are a little too curious for their own good)
that was stored but did not want the performance burden that
encryption/decryption would place on the PocketPC. Prior to writing and
subsequent to reading, we just perform a simple mangle of the
read/written data. A byte NOT or XOR works pretty well and does not
cause an extreme performance hit. Again, no real encryption, but it
hides the data from casual viewing.
Joey.
At 05:34 AM 9/9/2004, you wrote:
Hello sqlite users,
Hello Dr. Hipp,
I would like to know if there is a way to encrpyt the data stored in the
database but to still be able to use SQL queries with plain text,
something like
CREATE TABLE TEST (fld)
INSERT INTO Test VALUES ('some string')
SELECT * FROM Test WHERE fld = 'some string'
but, when the sqlite database is opened in a file viewer, 'some string'
would not be visible but only it's encrypted form.
Regards,
George Ionescu
RE: [sqlite] Encrypting data stored in database
In theory, ( have not tried this with SQLite) you caould make 2 add-in
functions, encrypt(str) and decript(str) then use it as such:
Select decrypt(sensitive_column) from table where id=3;
Insert into table (sensitive_column) values (encrypt('3434 3434 3434
3434'))
To provide field-level encryption.
> -Original Message-
> From: EzTools Support [mailto:[EMAIL PROTECTED]
> Sent: Thursday, September 09, 2004 5:12 PM
> To: [EMAIL PROTECTED]
> Subject: Re: [sqlite] Encrypting data stored in database
>
> Joey,
>
> Where in the code did you do that? Was it easy to put in one or two
> places?
>
> -brett
>
> try IeToolbox Passwords & Notes Keeper, Form Filler and much more
> www.ietoolbox.com
>
>
>
> Joey Blankenship wrote:
>
> > Not to respond out of turn here, but we had a need to obfuscate the
> > plain text (due to some users that are a little too curious for
their
> > own good) that was stored but did not want the performance burden
that
> > encryption/decryption would place on the PocketPC. Prior to writing
> > and subsequent to reading, we just perform a simple mangle of the
> > read/written data. A byte NOT or XOR works pretty well and does not
> > cause an extreme performance hit. Again, no real encryption, but it
> > hides the data from casual viewing.
> >
> > Joey.
> >
> > At 05:34 AM 9/9/2004, you wrote:
> >
> >> Hello sqlite users,
> >> Hello Dr. Hipp,
> >>
> >> I would like to know if there is a way to encrpyt the data stored
in
> >> the database but to still be able to use SQL queries with plain
text,
> >> something like
> >>
> >> CREATE TABLE TEST (fld)
> >> INSERT INTO Test VALUES ('some string')
> >> SELECT * FROM Test WHERE fld = 'some string'
> >>
> >> but, when the sqlite database is opened in a file viewer, 'some
> >> string' would not be visible but only it's encrypted form.
> >>
> >> Regards,
> >> George Ionescu
> >
> >
> >
>
__
This electronic message may contain proprietary and confidential information of Verint
Systems Inc., its affiliates and/or subsidiaries.
The information is intended to be for the use of the individual(s) or
entity(ies) named above. If you are not the intended recipient (or authorized to
receive this e-mail for the intended recipient), you may not use, copy, disclose or
distribute to anyone this message or any information contained in this message. If
you have received this electronic message in error, please notify us by replying to
this e-mail. (1)
[sqlite] No data with PRAGMA table_info
When I issue PRAGMA table_info for an existing table, I get the column names back, but no values. Does it work for anyone else? TIA -brett -- try IeToolbox Passwords & Notes Keeper, Form Filler and much more www.ietoolbox.com
Re: [sqlite] Encrypting data stored in database
Joey,
Where in the code did you do that? Was it easy to put in one or two places?
-brett
try IeToolbox Passwords & Notes Keeper, Form Filler and much more
www.ietoolbox.com
Joey Blankenship wrote:
Not to respond out of turn here, but we had a need to obfuscate the
plain text (due to some users that are a little too curious for their
own good) that was stored but did not want the performance burden that
encryption/decryption would place on the PocketPC. Prior to writing
and subsequent to reading, we just perform a simple mangle of the
read/written data. A byte NOT or XOR works pretty well and does not
cause an extreme performance hit. Again, no real encryption, but it
hides the data from casual viewing.
Joey.
At 05:34 AM 9/9/2004, you wrote:
Hello sqlite users,
Hello Dr. Hipp,
I would like to know if there is a way to encrpyt the data stored in
the database but to still be able to use SQL queries with plain text,
something like
CREATE TABLE TEST (fld)
INSERT INTO Test VALUES ('some string')
SELECT * FROM Test WHERE fld = 'some string'
but, when the sqlite database is opened in a file viewer, 'some
string' would not be visible but only it's encrypted form.
Regards,
George Ionescu
[sqlite] Locking in 3.0.6
I'm wondering what logic I should employ to prevent mutual exclusion occurring. I'm using SQLite to record an audit trail from a web application - I count the number of edits, searches and the maximum number of concurrent users. Each user opens a connection to the database for the duration of their session. The audit logic is as follows; begin transaction select total from useractivity where event= . If lRows = 0 Then insert into useractivity .. Else update useractivity . End If commit transaction What is happening is that the insertion/update in thread A locks the database. Before thread A gets the chance to commit the transaction, thread B also attempts an update/insertion but fails because the database is locked. However, this causes thread A to also be locked out from committing the change. I don't have any recovery code in the logic so we're then in a situation whereby the database is permanently locked for everyone, or until all the sessions die and each thread closes it's handle. This can't be right can it ? Where should I do my busy checking and recovery logic to prevent this mutual exclusion? Steve
Re: [sqlite] Error: Can't find package sqlite
- Original Message - From: "Cena, Resty" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, September 07, 2004 9:31 PM Subject: [sqlite] Error: Can't find package sqlite > Hi, > I'm trying to use TclSqlite, but when I try to load sqlite with the command: > > % package require sqlite > I get the following error: > can't find package sqlite > Is this the way to load the package into Tcl? You need to either put your directory with the package with all the other tcl libs, or lappend the package directory to auto_path. Look under pkg_mkIndex in the TCL man pages for more info Lawrence --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.754 / Virus Database: 504 - Release Date: 06/09/04
Re: [sqlite] sqlite.exe binary (windows)
SQLite generating the "create index" SQL DDL statement at the end of the .dump output is the correct behaviour. In my experience using a number of different DBMSs, the sequence of data loading and then indexing is usually quicker than performing those operations the other way round. In a commercial environment, it's not unusual to have tables that contain many millions of rows and have a number of indexes (perhaps 10 or more) associated with them. Inserting a row into such a table becomes almost a trivial exercise for the DBMS compared to work it has to perform to maintain that many indexes for so much data. As is usually the case, those that design and maintain SQLite have probably got it right. Regards. rayB |-+> | | Steven Van | | | Ingelgem | | | <[EMAIL PROTECTED]> | | || | | 09/09/2004 15:12 | | | Please respond to| | | sqlite-users | | || |-+> >--| | | | To: [EMAIL PROTECTED] | | cc: | | Subject: [sqlite] sqlite.exe binary (windows) | >--| I just noticed something rather stupid... when you .dump a table via the sqlite.exe binary (2.8.15)... It dumps first the "create table", then the "insert"s, and afterwards the indexes Now if you have a very big table it will take a LOT of time to place those indexes... Maybe it is more performant to place the "create index" just after the "create table" statement? Greetings, KaReL (aka Steven) Main Webpage : http://www.karels0ft.be/ ICQ # : 35217584 *** Confidentiality and Privilege Notice *** This e-mail is intended only to be read or used by the addressee. It is confidential and may contain legally privileged information. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone, and you should destroy this message and kindly notify the sender by reply e-mail. Confidentiality and legal privilege are not waived or lost by reason of mistaken delivery to you. Qantas Airways Limited ABN 16 009 661 901 Visit Qantas online at http://qantas.com
