Re: [sqlite] Calculating a checksum within SQLite, without using an extension...

[Thomas Olson]

Hello again,

I found a problem with the example I sent earlier.  I'm fairly new to 
dealing with blob data and didn't realize that substr([some_blob], 1, 1) 
didn't return a byte value, at least not one that can be cast as its' 
decimal integer value.  I have fixed the problem by using a lookup 
[hex2dec] table, which seems to have solved the problem.  If anyone is 
interested let me know and I'll send it.

Thanks,

Tom

On 1/8/2011 4:37 PM, Thomas Olson wrote:
> Hello,
>
> I was bored so I wrote a quick recursion test that calculates a
> Fletcher32 checksum and it works!  At least I think it does, I can't
> find any test vectors for Fletcher32.  I realize it would be better to
> implement this as an extension library, but I was using it as an
> exercise to get a better feel for recursive triggers.  Below is the
> source code.
...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Corrupted database file.

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/05/2011 11:05 AM, Greg Morehead wrote:
> Any insights on how this could occur would be greatly appreciated.

http://www.sqlite.org/lockingv3.html#how_to_corrupt

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk0pNzoACgkQmOOfHg372QTaoACfWBBY2CJXMZMaJ1LjdirLROF8
RzQAoNZdPoxJjEo9J2nlcjqTrqbM1Hqs
=6Pd0
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] R: Crypto lib for Sqlite - suggest required

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/08/2011 04:36 AM, Marco Turco wrote:
> Essentially I would avoid that my competitors can look inside the Db
> structure and import data.

On any machine where the database is used the key will have to exist in
plain form no matter how convoluted the encryption going on.  Your
competitors will always be able to run it under a debugger.

You don't say if the database content is the same for everyone or if it is
different for each user.  If it is the same for everyone then all it takes
is one bad user and the DB contents will be publishable for all.  If it is
different per user then I don't see the problem.

The only way to be "secure" is to provide the data one value at a time via a
web service where you can audit each and every request and not provide
everything at once.  (And anyone receiving that data can still republish it.)

In these situations it is usually enough just to obfuscate the database so
it isn't immediately apparent that SQLite is in use.  A simple way of doing
that is to write your own VFS that calls the normal VFS but xors all data
that is being read and written.  This is exactly that scheme using Python
and demonstrates how much code it is:

 http://apidoc.apsw.googlecode.com/hg/example.html#example-vfs

If you still want to go down the encryption route then be aware that getting
encryption right is very hard.  More accurately it is trivial to use
encryption, and even easier to use it wrongly.  People usually get it wrong.
 Some random subjects: IV, salt, key strengthening

The SQLite paid for encryption module is a bargain.  It is a one time fee
and you get it forever.  It will always work with SQLite as the versions
change.  It does security right and is always thoroughly tested alongside
SQLite development.  Work out the value of that and the value of your time.

Since you won't really be able to prevent copying the data, what you should
look for is ways of proving that someone has done so.  Obfuscating the
database is a good first start since no one could "accidentally" look at the
contents - they had to put in deliberate effort.  Then throw in some
mountweazels:

  http://en.wikipedia.org/wiki/Fictitious_entry#Motivations_for_creation
  http://en.wikipedia.org/wiki/Trap_street

This would give you enough evidence to sue a competitor.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk0pNnIACgkQmOOfHg372QT8jwCg0DRP/QhGxrOWo2fWDsYNPZj4
tgUAoM0ReVOOJ9exG8rb9iz4cFqZJOWq
=w+gv
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Calculating a checksum within SQLite, without using an extension...

[Thomas Olson]

Hello,

I was bored so I wrote a quick recursion test that calculates a 
Fletcher32 checksum and it works!  At least I think it does, I can't 
find any test vectors for Fletcher32.  I realize it would be better to 
implement this as an extension library, but I was using it as an 
exercise to get a better feel for recursive triggers.  Below is the 
source code.

Enjoy,

Tom

pragma recursive_triggers=1;
DROP TABLE IF EXISTS [fletcher32];
CREATE TABLE IF NOT EXISTS [fletcher32] (
   [data] blob,
   [loop] integer,
   [len] integer,
   [pos] integer,
   [sum1] integer,
   [sum2] integer,
   [result] integer
);

DROP TRIGGER IF EXISTS [tr_ai_fletcher32];

CREATE TRIGGER IF NOT EXISTS [tr_ai_fletcher32] AFTER INSERT ON [fletcher32]
FOR EACH ROW
BEGIN
   UPDATE [fletcher32]
   SET [loop] = 0,
   [len] = length(new.[data]),
   [pos] = 1,
   [sum1] = 0,
   [sum2] = 0,
   [result] = 0;
END;

DROP TRIGGER IF EXISTS [tr_au_fletcher32];

CREATE TRIGGER IF NOT EXISTS [tr_au_fletcher32] AFTER UPDATE ON [fletcher32]
FOR EACH ROW
BEGIN
   SELECT CASE
 WHEN (new.[pos] > new.[len]) THEN RAISE(IGNORE)
   END;

   UPDATE [fletcher32]
   SET [loop] = ifnull(new.[loop], 0) + 1,
   [sum1] = CASE
  WHEN (ifnull(new.[sum1], 0) + ((hex(substr(new.[data], 
new.[pos], 1)) << 8) | (hex(substr(new.[data], new.[pos] + 1, 1) > 
65535 THEN
(ifnull(new.[sum1], 0) + ((hex(substr(new.[data], 
new.[pos], 1)) << 8) | (hex(substr(new.[data], new.[pos] + 1, 1) - 65535
  ELSE ifnull(new.[sum1], 0) + ((hex(substr(new.[data], 
new.[pos], 1)) << 8) | (hex(substr(new.[data], new.[pos] + 1, 1
END,
   [sum2] = CASE
  WHEN (ifnull(new.[sum2], 0) + (ifnull(new.[sum1], 0) + 
((hex(substr(new.[data], new.[pos], 1)) << 8) | (hex(substr(new.[data], 
new.[pos] + 1, 1)) > 65535 THEN
(ifnull(new.[sum2], 0) + (ifnull(new.[sum1], 0) + 
((hex(substr(new.[data], new.[pos], 1)) << 8) | (hex(substr(new.[data], 
new.[pos] + 1, 1)) - 65535
  ELSE ifnull(new.[sum2], 0) + (ifnull(new.[sum1], 0) + 
((hex(substr(new.[data], new.[pos], 1)) << 8) | (hex(substr(new.[data], 
new.[pos] + 1, 1)
END,
   [result] = CASE
WHEN (new.[len] - new.[pos] <= 2) THEN
  (CASE
 WHEN (ifnull(new.[sum2], 0) + 
(ifnull(new.[sum1], 0) + ((hex(substr(new.[data], new.[pos], 1)) << 8) | 
(hex(substr(new.[data], new.[pos] + 1, 1)) > 65535 THEN
   (ifnull(new.[sum2], 0) + (ifnull(new.[sum1], 
0) + ((hex(substr(new.[data], new.[pos], 1)) << 8) | 
(hex(substr(new.[data], new.[pos] + 1, 1)) - 65535
 ELSE ifnull(new.[sum2], 0) + 
(ifnull(new.[sum1], 0) + ((hex(substr(new.[data], new.[pos], 1)) << 8) | 
(hex(substr(new.[data], new.[pos] + 1, 1)
   END << 16) |
   CASE
 WHEN (ifnull(new.[sum1], 0) + 
((hex(substr(new.[data], new.[pos], 1)) << 8) | (hex(substr(new.[data], 
new.[pos] + 1, 1) > 65535 THEN
   (ifnull(new.[sum1], 0) + 
((hex(substr(new.[data], new.[pos], 1)) << 8) | (hex(substr(new.[data], 
new.[pos] + 1, 1) - 65535
 ELSE ifnull(new.[sum1], 0) + 
((hex(substr(new.[data], new.[pos], 1)) << 8) | (hex(substr(new.[data], 
new.[pos] + 1, 1
   END
 ELSE 0
   END,
   [pos] = new.[pos] + 2
;
END;

DROP TRIGGER IF EXISTS [tr_bi_fletcher32];

CREATE TRIGGER IF NOT EXISTS [tr_bi_fletcher32] BEFORE INSERT ON 
[fletcher32]
FOR EACH ROW
BEGIN
   SELECT CASE
 WHEN (lower(typeof(new.[data])) <> 'blob') THEN RAISE(ABORT, 'The 
data must be passed as a blob.')
   END;
   DELETE FROM [fletcher32];
END;

INSERT INTO [fletcher32] ([data]) VALUES 
(cast('abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz'
 
||

'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz'
 
||

'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz'
 
||

'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz'
 
||

'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz'
 
||

'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz'
 
||

'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz'
 
||

'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz'
 
||

'abcd

Re: [sqlite] SQLite version with a query

[Nicolas Buduroi]

OK, I'm blind! Just searched in the pragma when I think about it!

Thanks

On Sat, Jan 8, 2011 at 5:15 PM, Richard Hipp  wrote:

> On Sat, Jan 8, 2011 at 5:08 PM, Nicolas Buduroi 
> wrote:
>
> > Hi, first post here. I'm wondering if there a way to get the SQLite
> version
> > with a query. I didn't found anything related to that in the
> documentation
> > (or I'm blind) but maybe there's an undocumented way.
> >
>
> http://www.sqlite.org/lang_corefunc.html#sqlite_version
>
>
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version with a query

[Richard Hipp]

On Sat, Jan 8, 2011 at 5:08 PM, Nicolas Buduroi  wrote:

> Hi, first post here. I'm wondering if there a way to get the SQLite version
> with a query. I didn't found anything related to that in the documentation
> (or I'm blind) but maybe there's an undocumented way.
>

http://www.sqlite.org/lang_corefunc.html#sqlite_version


> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite version with a query

[Nicolas Buduroi]

Hi, first post here. I'm wondering if there a way to get the SQLite version
with a query. I didn't found anything related to that in the documentation
(or I'm blind) but maybe there's an undocumented way.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Advice on breaking trigger recursion?

[Nicolas Williams]

On Sat, Jan 08, 2011 at 12:07:08PM +0700, Dan Kennedy wrote:
> On 01/08/2011 08:12 AM, Nicolas Williams wrote:
> > I need to use recursive triggers.  In some cases I want to "normalize"
> > values of some columns of NEW being INSERTed or UPDATEd, but there's no
> > UPDATE syntax for changing NEW, thus I can't write something like:
> >
> > [...]
> >
> > I must write:
> >
> > CREATE TRIGGER ...
> > BEGIN
> >  UPDATE  SET somecol = () 
> > WHERE rowid = NEW.rowid;
> > END;
> 
> Something like this perhaps:
> 
> CREATE TRIGGER sometrig AFTER UPDATE WHEN somecol !=  BEGIN
>UPDATE sometab SET somecol =  WHERE rowid = NEW.rowid;
> END;

Yes, adding a WHEN clause to the triggers also works.

I did have some triggers with WHEN clauses, actually, but it didn't
occur to me to use that as a technique for breaking recursion in part, I
think, because the WHEN expression would, in this case, be several lines
long, and aesthetically that rubbed me the wrong way.  I've got several
instances of that sort of expression in my code and it occurs to me that
I can use additional views to reduce the length of that expression.

Thanks to Dan as well.  CHeers,

Nico
-- 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result set column names

[Peter]

Jay A. Kreibich wrote, On 08/01/11 14:07:
> On Sat, Jan 08, 2011 at 09:12:36AM +, Simon Slavin scratched on the wall:
>>
>> On 8 Jan 2011, at 9:09am, Peter wrote:
>>
>>> So the 'column name' may be some internal representation/magic number
>>> and bear no direct relationship to the name in the projector as supplied
>>> in the SELECT?
>>
>> For columns which are exact copies of columns in a table, the name
>> is always a copy of the table's column name.  You can rely on that.
>
>Unless the name is prefixed with a table name.
>
>Or unless the name is prefixed with a table alias (AS in a FROM clause).
>
>I wouldn't even be surprised to find version that prefix database names,
>when appropriate.
>
>
>
>You can rely on "AS" names, and that's pretty much it-- just as the
>docs warn.
>
> -j
>
Well so long as it _is_ a column name, just that it's not specified 
whether it will include tablename or alias then that's all that matters.

I interpreted Igor's reply to mean that unless the column name was 
specified by an AS in the projectors then that function could return 
anything, including garbage.


-- 
Peter Hardman

'For every complex problem there is a solution that is simple, 
straightforward - and wrong'
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Embarassed Newbie

[Teg]

Hello Michael,

Yeah, sqlite3.exe runs everywhere. This problem is more basic.
Sqlite3.exe is in some other path and not in the command line search
path.

Where did you unzip the files? Either do the test from the folder you
unzipped the files to or use full path

C:> C:\Unzipped\Sqlite3.exe test.db

C


Saturday, January 8, 2011, 9:12:10 AM, you wrote:

BMI> I just confirmed that the shell download is static.  The 566,272
BMI> size also indicates static compilation.
BMI>  
BMI> There are no dependencies other than OS DLLs.
BMI>  
BMI> c:\windows\system32\KERNEL32.DLL
BMI> c:\windows\system32\MSVCRT.DLL
BMI> c:\windows\system32\NTDLL.DLL
BMI> d:\SQLITE3.EXE

BMI>  
BMI> Michael D. Black
BMI> Senior Scientist
BMI> Advanced Analytics Directorate
BMI> Northrop Grumman Information Systems
BMI>  

BMI> 

BMI> From: sqlite-users-boun...@sqlite.org on behalf of Richard Hipp
BMI> Sent: Sat 1/8/2011 7:10 AM
BMI> To: General Discussion of SQLite Database
BMI> Subject: EXTERNAL:Re: [sqlite] Embarassed Newbie



BMI> On Sat, Jan 8, 2011 at 8:06 AM, Peter
BMI>  wrote:
>> On 08/01/11 11:43, james630...@aol.com wrote:
>>> sqlite3  test.db
>>
>> I assume by 'binaries' you mean sqlite-shell-win32... and
>> sqlite-dll-win32...? You need both to run the command you give above.

BMI> Is this true?  I thought I was compiling sqlite3.exe as a standalone 
binary?
BMI>  Does it really require that the DLL be downloaded too?  If so, that is
BMI> something that needs to be fixed.  Can you double-check this for me please.


BMI> --
BMI> D. Richard Hipp
BMI> d...@sqlite.org
BMI> ___
BMI> sqlite-users mailing list
BMI> sqlite-users@sqlite.org
BMI> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users





-- 
Best regards,
 Tegmailto:t...@djii.com

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Crypto lib for Sqlite - suggest required

[Ulrich Telle]

Hi Marco,

> I am looking for a Crypto lib to encrypt the Sqlite Db in full but I would
> like something less expensive that provided from the Sqlite author.
> I have found some libs on www.sqlite-cript.com and www.sqlite-encrypt.com.
> I would like your opinion about these libs and any other you repute stable
> and fast.

There are several encryption options for free:

1) System.Data.SQLite (http://sqlite.phxsoftware.com)
   For Windows only.

2) SQLCipher (http://sqlcipher.net)
   Portable, depends on OpenSSL's libcrypto

3) wxSQLite3 (http://wxcode.sourceforge.net/components/wxsqlite3)
   Portable, self-contained.
   Although wxSQLite3 is a SQLite wrapper for wxWidgets applications
   the distribution contains the source of an encryption extension
   supporting AES-128 or AES-256 encryption, which can be build
   using the SQLite amalgamation source code. It doesn't depend on
   any external library. Pre-compiled binaries for Windows are included.

All 3 use the native encryption API provided by SQLite.

Regards,

Ulrich

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Embarassed Newbie

[Black, Michael (IS)]

I just confirmed that the shell download is static.  The 566,272 size also 
indicates static compilation.
 
There are no dependencies other than OS DLLs.
 
c:\windows\system32\KERNEL32.DLL
c:\windows\system32\MSVCRT.DLL
c:\windows\system32\NTDLL.DLL
d:\SQLITE3.EXE

 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Richard Hipp
Sent: Sat 1/8/2011 7:10 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] Embarassed Newbie



On Sat, Jan 8, 2011 at 8:06 AM, Peter  wrote:
> On 08/01/11 11:43, james630...@aol.com wrote:
>> sqlite3  test.db
>
> I assume by 'binaries' you mean sqlite-shell-win32... and
> sqlite-dll-win32...? You need both to run the command you give above.

Is this true?  I thought I was compiling sqlite3.exe as a standalone binary?
 Does it really require that the DLL be downloaded too?  If so, that is
something that needs to be fixed.  Can you double-check this for me please.


--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result set column names

[Jay A. Kreibich]

On Sat, Jan 08, 2011 at 09:12:36AM +, Simon Slavin scratched on the wall:
> 
> On 8 Jan 2011, at 9:09am, Peter wrote:
> 
> > So the 'column name' may be some internal representation/magic number 
> > and bear no direct relationship to the name in the projector as supplied 
> > in the SELECT?
> 
> For columns which are exact copies of columns in a table, the name
> is always a copy of the table's column name.  You can rely on that.

  Unless the name is prefixed with a table name.

  Or unless the name is prefixed with a table alias (AS in a FROM clause).

  I wouldn't even be surprised to find version that prefix database names,
  when appropriate.



  You can rely on "AS" names, and that's pretty much it-- just as the
  docs warn.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Embarassed Newbie

[Peter]

Richard Hipp wrote, On 08/01/11 13:10:
> On Sat, Jan 8, 2011 at 8:06 AM, Peter  wrote:
>> On 08/01/11 11:43, james630...@aol.com wrote:
>>> sqlite3  test.db
>>
>> I assume by 'binaries' you mean sqlite-shell-win32... and
>> sqlite-dll-win32...? You need both to run the command you give above.
>
> Is this true?  I thought I was compiling sqlite3.exe as a standalone binary?
>   Does it really require that the DLL be downloaded too?  If so, that is
> something that needs to be fixed.  Can you double-check this for me please.
>
>

My bad - I just assumed you would need both engine and shell.

-- 
Peter Hardman

'For every complex problem there is a solution that is simple, 
straightforward - and wrong'
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Embarassed Newbie

[Richard Hipp]

On Sat, Jan 8, 2011 at 8:06 AM, Peter  wrote:
> On 08/01/11 11:43, james630...@aol.com wrote:
>> sqlite3  test.db
>
> I assume by 'binaries' you mean sqlite-shell-win32... and
> sqlite-dll-win32...? You need both to run the command you give above.

Is this true?  I thought I was compiling sqlite3.exe as a standalone binary?
 Does it really require that the DLL be downloaded too?  If so, that is
something that needs to be fixed.  Can you double-check this for me please.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] a bug report

[Richard Hipp]

The relevant code in 3.7.4 (with assert() statements removed) is as
follows (line numbers o the left):

434 static int isLookaside(sqlite3 *db, void *p){
435   return p && p>=db->lookaside.pStart && plookaside.pEnd;
436 }
450 int sqlite3DbMallocSize(sqlite3 *db, void *p){
452   if( db && isLookaside(db, p) ){
453 return db->lookaside.sz;
454   }else{
458 return sqlite3GlobalConfig.m.xSize(p);
459   }
460 }

The isLookaside() function should return false if p==0.   So I do not
see how you might have segfaulted on the db->lookaside.sz expression
of line 453.  And even then, I don't see how such a segfault is
possible if db is still a valid pointer.

Can you please send a stack trace from the point where the original
3.7.4 segfaults?

Also:  What do you mean when you say "I added callback function
pointers to my sqlite database"?  Have you modified the code
someplace?  Or are you using one of the many SQLite APIs that sets
callback functions?  If the latter, can you please tell us which
routines you are using?


On Fri, Jan 7, 2011 at 6:00 PM,  wrote:
>
> Hi SQLite Team
>
>
>
> I added callback function pointers for my sqlite database and the
> program crashed in the function sqlite3DbMallocSize. Same code works
> fine with older release such as 3.6.15.
>
>
>
> I debugged and got the place where the exception was thrown.
>
>
>
> in 3.7.4
>
> SQLITE_PRIVATE int sqlite3DbMallocSize(sqlite3 *db, void *p){
>
>            assert( db==0 || sqlite3_mutex_held(db->mutex) );
>
>            else if( db && isLookaside(db, p) ){
>
>                        return db->lookaside.sz;
>
>            }else{
>
>                        assert( sqlite3MemdebugHasType(p, MEMTYPE_DB) );
>
>                        assert( sqlite3MemdebugHasType(p,
> MEMTYPE_LOOKASIDE|MEMTYPE_HEAP) );
>
>                        assert( db!=0 || sqlite3MemdebugNoType(p,
> MEMTYPE_LOOKASIDE) );
>
>                        return sqlite3GlobalConfig.m.xSize(p);
>
>            }
>
> }
>
>
>
> while in 3.6.15 it was implemented as
>
>
>
> SQLITE_PRIVATE int sqlite3DbMallocSize(sqlite3 *db, void *p){
>
>  assert( db==0 || sqlite3_mutex_held(db->mutex) );
>
>  if( p==0 ){
>
>    return 0;
>
>  }else if( isLookaside(db, p) ){
>
>    return db->lookaside.sz;
>
>  }else{
>
>    return sqlite3GlobalConfig.m.xSize(p);
>
>  }
>
> }
>
>
>
> So, in order to get my app fly, I modified 3.7.4 sqlite code by adding a
> few more lines (in red):
>
>
>
> SQLITE_PRIVATE int sqlite3DbMallocSize(sqlite3 *db, void *p){
>
>            assert( db==0 || sqlite3_mutex_held(db->mutex) );
>
>            if( p == 0 ) // youfei fixed to prevent  illegal memory
> access when callbacks get called
>
>            {
>
>                        return 0;
>
>            }
>
>            else if( db && isLookaside(db, p) ){
>
>                        return db->lookaside.sz;
>
>            }else{
>
>                        assert( sqlite3MemdebugHasType(p, MEMTYPE_DB) );
>
>                        assert( sqlite3MemdebugHasType(p,
> MEMTYPE_LOOKASIDE|MEMTYPE_HEAP) );
>
>                        assert( db!=0 || sqlite3MemdebugNoType(p,
> MEMTYPE_LOOKASIDE) );
>
>                        return sqlite3GlobalConfig.m.xSize(p);
>
>            }
>
> }
>
>
>
> In my code, *p is 0 when sqlite3DbMallocSize gets called, so a memory
> access violation exception is thrown by "return
> sqlite3GlobalConfig.m.xSize(p);". Previous release covers it well while
> the latest does not. After the modification my program gets back to
> work. Please verify my change and let me know if it's a bug missed by
> you experts?
>
>
>
> Thanks,
>
> Youfei
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Youfei Chen | EMC Corporation   | 176 South Street
> Hopkinton, MA 01748
>  nton,+MA+01748>  | Direct #: (508)293-6402 | Extension: 76402 | Email:
> youfei.c...@emc.com 
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Embarassed Newbie

[Peter]

On 08/01/11 11:43, james630...@aol.com wrote:
> Hi All,
>
> I'm using Windows Home Vista(!) and yesterday download the  binaries for
> windows. I think I then unzipped same. then tried typing sqlite3  test.db only
> to be informed that "not recognised as an internal or external  command,
> operable program or batch file."

I assume by 'binaries' you mean sqlite-shell-win32... and 
sqlite-dll-win32...? You need both to run the command you give above.

You have three choices:
- Run your command in the same folder that you unzipped the files into.
- Give the full or relative path to that folder, eg 
c:\path\to\folder\sqlite3 test.db
- Add that folder to your path variable - the GUI to do that is 
somewhere in My Computer->Advanced or some such IIRC

-- 
Peter Hardman

'For every complex problem there is a solution that is simple, 
straightforward - and wrong'
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] R: Crypto lib for Sqlite - suggest required

[Simon Slavin]

On 8 Jan 2011, at 12:36pm, Marco Turco wrote:

> Essentially I would avoid that my competitors can look inside the Db
> structure and import data.

Good answer.  Okay, now the people who actually use crypto can answer your 
question.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] R: Crypto lib for Sqlite - suggest required

[Marco Turco]

Hi,
Essentially I would avoid that my competitors can look inside the Db
structure and import data.

Marco



-Messaggio originale-
Da: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
Per conto di Simon Slavin
Inviato: sabato 8 gennaio 2011 12:37
A: General Discussion of SQLite Database
Oggetto: Re: [sqlite] Crypto lib for Sqlite - suggest required


On 8 Jan 2011, at 10:59am, Marco Turco wrote:

> I am looking for a Crypto lib to encrypt the Sqlite Db in full but I 
> would like something less expensive that provided from the Sqlite author.
> I have found some libs on www.sqlite-cript.com and www.sqlite-encrypt.com.
> I would like your opinion about these libs and any other you repute 
> stable and fast.

What are you using encryption to guard against ?  Are you encryption
financial data ?  Are you protecting against users understanding what your
application does ?  Are you guarding your user data against industrial
espionage ?

The two systems you cite work the same way: they encrypt each stored value.
This makes them far more easy to crack than the one provided by DRH since
the contents of the SQLITE_MASTER table are predictable.  But as you say,
they are far cheaper, and they may be good enough for your purposes.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Embarassed Newbie

[James630165]

Hi All,
 
I'm using Windows Home Vista(!) and yesterday download the  binaries for 
windows. I think I then unzipped same. then tried typing sqlite3  test.db only 
to be informed that "not recognised as an internal or external  command, 
operable program or batch file."
 
I'm lost as to what to do.
 
I assume I am missing something that is fundamental and which I don't  
understand and so apologise to all for bothering you with this basic cry for  
help!
 
Jim.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] NOT NULL

[tinauser]

Thanks for the answer.
I'm using the firefox add-on(sqlite manager).I wrongly guessed that
when creating a table with a field defined as follows:
"start_script" TEXT NOT NULL
so without default, the default would be NULL, while apparently is an
empty string. Indeed, if I try to insert NULL i got an exception.
So, if I want to force the user to insert something, should I add as
default NULL?Or is this a problem with SQLite manager?
Thanks for helping!

On Jan 6, 7:09 pm, Stephen Chrzanowski  wrote:
> insert into YourTable (Field1, NotNullField) values ('Empty String','');
> insert into YourTable (Field1, NotNullField) values ('Null Value',NULL);
>
> The first insert one will work, the second will not.
>
> An empty string is NOT considered a NULL value.  An empty string is actually
> considered very valid data.  A value of NULL means that there is no data
> associated to that particular field.
>
> On Wed, Jan 5, 2011 at 12:27 PM, tinauser  wrote:
> > Hi there,
> > I'm an inexpert user of SQLite.
> > I create a table with a field NOT NULL
> > However i succesfully insert a record with the NOT NULL field empty.
> > Shouldn't this be "forbidden"?
> > Thanks
> > ___
> > sqlite-users mailing list
> > sqlite-us...@sqlite.org
> >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-us...@sqlite.orghttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] a bug report

[youfei.chen]

Hi SQLite Team

 

I added callback function pointers for my sqlite database and the
program crashed in the function sqlite3DbMallocSize. Same code works
fine with older release such as 3.6.15.

 

I debugged and got the place where the exception was thrown.

 

in 3.7.4

SQLITE_PRIVATE int sqlite3DbMallocSize(sqlite3 *db, void *p){

assert( db==0 || sqlite3_mutex_held(db->mutex) );

else if( db && isLookaside(db, p) ){

return db->lookaside.sz;

}else{

assert( sqlite3MemdebugHasType(p, MEMTYPE_DB) );

assert( sqlite3MemdebugHasType(p,
MEMTYPE_LOOKASIDE|MEMTYPE_HEAP) );

assert( db!=0 || sqlite3MemdebugNoType(p,
MEMTYPE_LOOKASIDE) );

return sqlite3GlobalConfig.m.xSize(p);

}

}

 

while in 3.6.15 it was implemented as 

 

SQLITE_PRIVATE int sqlite3DbMallocSize(sqlite3 *db, void *p){

  assert( db==0 || sqlite3_mutex_held(db->mutex) );

  if( p==0 ){

return 0;

  }else if( isLookaside(db, p) ){

return db->lookaside.sz;

  }else{

return sqlite3GlobalConfig.m.xSize(p);

  }

}

 

So, in order to get my app fly, I modified 3.7.4 sqlite code by adding a
few more lines (in red):

 

SQLITE_PRIVATE int sqlite3DbMallocSize(sqlite3 *db, void *p){

assert( db==0 || sqlite3_mutex_held(db->mutex) );

if( p == 0 ) // youfei fixed to prevent  illegal memory
access when callbacks get called

{

return 0;

}

else if( db && isLookaside(db, p) ){

return db->lookaside.sz;

}else{

assert( sqlite3MemdebugHasType(p, MEMTYPE_DB) );

assert( sqlite3MemdebugHasType(p,
MEMTYPE_LOOKASIDE|MEMTYPE_HEAP) );

assert( db!=0 || sqlite3MemdebugNoType(p,
MEMTYPE_LOOKASIDE) );

return sqlite3GlobalConfig.m.xSize(p);

}

}

 

In my code, *p is 0 when sqlite3DbMallocSize gets called, so a memory
access violation exception is thrown by "return
sqlite3GlobalConfig.m.xSize(p);". Previous release covers it well while
the latest does not. After the modification my program gets back to
work. Please verify my change and let me know if it's a bug missed by
you experts?

 

Thanks,

Youfei

 

 

 

 

 

 

 

Youfei Chen | EMC Corporation   | 176 South Street
Hopkinton, MA 01748
  | Direct #: (508)293-6402 | Extension: 76402 | Email:
youfei.c...@emc.com   

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pin index to memory

[Black, Michael (IS)]

I had similar thoughts to Simon
 
How are you keeping track of "frequently queried"??  Seems to me that has to 
either be in-memory in your program or a database update.   Either of which 
seems like a waste of time.  So you have to update some structure to keep track 
of frequency...do a query...do an insert where appropriate...query the other 
table when not found...insert into other table when appropriate...
 
Have you proven to yourself that the indexing the table itself is too slow?  
What did you do to test that?  How many records are you talking about?
 
I've got a real-time system I'm working on...I have one index on my tables.  I 
can tell you it makes absolutely no difference if the database is on-disk or 
in-memory (I'm doing 5-second transactions).  Now I'm not really data-base 
bound but others have noted similar disappointment when expecting too much out 
of in-memory performance.
 
http://stackoverflow.com/questions/764710/sqlite-performance-benchmark-why-is-memory-so-slow-only-1-5x-as-fast-as-di
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of srl309
Sent: Sat 1/8/2011 3:45 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:Re: [sqlite] Pin index to memory




Thanks for replying but i don't think that will do what i want.

I am trying to create a system that will save frequently queried values that
are not indexed to index's. The total amount of indices allocated this way
would have a set size and would be dropped and created depending on how
often they and other values are used. I was thinking since these indices are
used frequently it would be a good idea to save them to memory dropping them
when the connection closes.

The values saved to index's should act like index's. As in if i query their
table they should be used.
--
View this message in context: 
http://old.nabble.com/Pin-index-to-memory-tp30620303p30620918.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Crypto lib for Sqlite - suggest required

[Simon Slavin]

On 8 Jan 2011, at 10:59am, Marco Turco wrote:

> I am looking for a Crypto lib to encrypt the Sqlite Db in full but I would
> like something less expensive that provided from the Sqlite author.
> I have found some libs on www.sqlite-cript.com and www.sqlite-encrypt.com.
> I would like your opinion about these libs and any other you repute stable
> and fast.

What are you using encryption to guard against ?  Are you encryption financial 
data ?  Are you protecting against users understanding what your application 
does ?  Are you guarding your user data against industrial espionage ?

The two systems you cite work the same way: they encrypt each stored value.  
This makes them far more easy to crack than the one provided by DRH since the 
contents of the SQLITE_MASTER table are predictable.  But as you say, they are 
far cheaper, and they may be good enough for your purposes.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Crypto lib for Sqlite - suggest required

[Günter Greschenz]

hi,

you could try
 http://greschenz.dyndns.org/downloads/sqlite_crypt.zip

bye, gg

Am 08.01.2011 11:59, schrieb Marco Turco:
> Hi all,
> I am looking for a Crypto lib to encrypt the Sqlite Db in full but I would
> like something less expensive that provided from the Sqlite author.
> I have found some libs on www.sqlite-cript.com and www.sqlite-encrypt.com.
> I would like your opinion about these libs and any other you repute stable
> and fast.
>
> Thanks in advance
>
> Best Regards,
>
> Marco Turco
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Crypto lib for Sqlite - suggest required

[Marco Turco]

Hi all,
I am looking for a Crypto lib to encrypt the Sqlite Db in full but I would
like something less expensive that provided from the Sqlite author.
I have found some libs on www.sqlite-cript.com and www.sqlite-encrypt.com.
I would like your opinion about these libs and any other you repute stable
and fast.

Thanks in advance

Best Regards,

Marco Turco




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pin index to memory

[Simon Slavin]

On 8 Jan 2011, at 9:45am, srl309 wrote:

> I am trying to create a system that will save frequently queried values that
> are not indexed to index's. The total amount of indices allocated this way
> would have a set size and would be dropped and created depending on how
> often they and other values are used. I was thinking since these indices are
> used frequently it would be a good idea to save them to memory dropping them
> when the connection closes.

You can definitely make up ad-hoc indexes as your data is used in unexpected 
ways.  Then, as your application is quit, just go through and delete all the 
indexes you've noted as temporary.

But the technique of keeping indexes in memory as you describe is what a good 
adisk caching algorithm does.  If your OS and hardware are working properly, 
then areas of disk frequently used should be cached.  Whatever system you work 
out can't be more efficient than disk caching, since the caching system itself 
knows which areas of disk are most deserving of keeping in memory.  So just 
make up whatever indexes you think will be used, and rely on your operating 
system to work out what's worth caching.

Your question does make me think of premature optimization.  So I suggest you 
write your application first.  Only once you find it's too slow is it time to 
worry about ways to speed it up.  If it turns out that the best way to improve 
it is to keep more of your indexes to be kept in memory, buy more RAM !

The exception is probably the case where the programmer can't possibly work out 
what indexes to create.  For instance, if you're trying to produce a general 
tool for handling data, and don't know what your users are going to use the 
variables for.  If it turns out that you really do need cached temporary 
indices (which is the technical name for what you describe) then you might be 
better-off using MySQL or one of the other implementations of SQL which has 
server/client architecture.  Most of those have cached temporary indexing built 
in.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pin index to memory

[srl309]

Thanks for replying but i don't think that will do what i want.

I am trying to create a system that will save frequently queried values that
are not indexed to index's. The total amount of indices allocated this way
would have a set size and would be dropped and created depending on how
often they and other values are used. I was thinking since these indices are
used frequently it would be a good idea to save them to memory dropping them
when the connection closes.

The values saved to index's should act like index's. As in if i query their
table they should be used.
-- 
View this message in context: 
http://old.nabble.com/Pin-index-to-memory-tp30620303p30620918.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result set column names

[Peter]

On 08/01/11 09:12, Simon Slavin wrote:
>
> On 8 Jan 2011, at 9:09am, Peter wrote:
>
>> So the 'column name' may be some internal representation/magic number
>> and bear no direct relationship to the name in the projector as supplied
>> in the SELECT?
>
> For columns which are exact copies of columns in a table, the name is always 
> a copy of the table's column name.  You can rely on that.  For columns which 
> are the results of a calculation, there is no documentation for the name 
> produced by SQLite.  Different versions of SQLite may produce different names.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>

That sounds better. I just have to always use an AS clause on calculated 
fields.

Maybe the documentation should be clarified?

Pete

-- 
Peter Hardman

'For every complex problem there is a solution that is simple, 
straightforward - and wrong'
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result set column names

[Simon Slavin]

On 8 Jan 2011, at 9:09am, Peter wrote:

> So the 'column name' may be some internal representation/magic number 
> and bear no direct relationship to the name in the projector as supplied 
> in the SELECT?

For columns which are exact copies of columns in a table, the name is always a 
copy of the table's column name.  You can rely on that.  For columns which are 
the results of a calculation, there is no documentation for the name produced 
by SQLite.  Different versions of SQLite may produce different names.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result set column names

[Peter]

On 07/01/11 18:41, Igor Tandetnik wrote:
> On 1/7/2011 1:31 PM, Peter wrote:
>> In the C interface documentation we are told:
>>
>> "The name of a result column is the value of the "AS" clause for that
>> column, if there is an AS clause. If there is no AS clause then the name
>> of the column is unspecified and may change from one release of SQLite
>> to the next."
>>
>> What exactly is meant by 'unspecified'?
>
> Generated by internal logic that is not precisely documented, and is
> subject to change without notice; should be treated as unpredictable,
> and not relied on for anything of substance.
>
>> Does it mean that in the absence
>> of an "AS" clause I will get either TABLE.COLUMN or COLUMN, or are
>> there/may there be other alternatives?
>
> There may potentially be other alternatives, now or in the future.

So the 'column name' may be some internal representation/magic number 
and bear no direct relationship to the name in the projector as supplied 
in the SELECT?

Not a very useful function then.

-- 
Peter Hardman

'For every complex problem there is a solution that is simple, 
straightforward - and wrong'
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Advice on breaking trigger recursion?

[Simon Slavin]

On 8 Jan 2011, at 6:38am, Nicolas Williams wrote:

> Thanks to Drake Wilson and Simon Slavin.  The trick was to create a VIEW
> that the application uses and which has INSTEAD OF triggers to do the
> right thing.  This breaks the recursive triggering because there are no
> recursive triggers left on the underlying table.  Worked like a charm.

No problem.  I had forgotten the problem with recursion.  I'm glad Drake 
mentioned it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pin index to memory

[Simon Slavin]

On 8 Jan 2011, at 5:36am, srl309 wrote:

> In oracle you can do this 
> Alter index  storage (buffer_pool keep);
> This would keep the index in memory thus allowing it to be read quicker. 
> Is there any way to do this in sqlite without the table or database being in
> memory.
> 
> If there is not does anyone have any ideas on functions i can manipulate in
> sqlite3.c to get the ability to store indices in memor

Create a new table in memory with just the fields that you care about for that 
index.




Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users