Re: [sqlite] equality searches and range searches with encrypteddata

2008-08-26 Thread Derek Developer
Thanks for the link. Unfortunatly its a little expensive and probably 50% 
slower than my implementation. 

No offense, but C is a language that a lot of us tolerate and is not the 
panacea that some C developers like to believe it is. Reading C is like reading 
Chinese. ASM may not be a high level language, but it certainly is fast.

My knowledge of encryption is above adequate, while I will be the first to 
admit my understanding of the inner workings of a database is not. As I grapple 
to understand how Encryption may be applied to a database project I am 
developing, I have displayed my ignorance in the hope of learning. It is fairly 
obvious that some very talented developers on this forum would prefer to use 
this as an exercise in pedantic sniping rather than offer substantive help.

To the others, I thank you.









Cory Nelson <[EMAIL PROTECTED]> wrote: On Mon, Aug 25, 2008 at 6:33 PM, Derek 
Developer
 wrote:
> Dennis thank you for taking the time to explain that. I have read the 
> Architecture page and I think I have a better idea.
>
> Since this does seem to be a viable way to protect the data I would like to 
> implement the schema, but using AES instead of MD5 which is unsecure.
>
> Has anyone done this and posted the code?
>

drh sells a version with encryption builtin here:
http://www.hwaci.com/sw/sqlite/prosupport.html

I don't mean any offense here, but in case you aren't doing this to
learn and will really be storing people's credit cards and socials:
you are not knowledgeable enough in this area to be writing any
production encryption code.  Doing so would be a disservice to any
customers.  Definitely use available tested code in this case, like
drh's version.

-- 
Cory Nelson
___
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] FTS, snippet & Unicode?

2008-08-26 Thread Alexandre Courbot
I know there is a patch at
http://www.sqlite.org/cvstrac/tktview?tn=3140,38 that is supposed to
improve Unicode support in FTS3. I suspect it to turn any Unicode
character into a token - however maybe you can use it as a basis to
implement what you need.

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


[sqlite] SQLite in memory database concurrency model

2008-08-26 Thread Alex Katebi
There is a lot of information about disk file concurrency model.
I have not found much information regarding the in memory database
concurrency model.

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


Re: [sqlite] GCC -ffast-math safe version of sqlite3IsNaN

2008-08-26 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Brown, Daniel wrote:
> In the process of upgrading to 3.6.1 I've run into the error on line 46
> of util.c about int sqlite3IsNaN(double x) not behaving consistently
> with the GCC  -ffast-math compiler option (which we have enabled), is
> there any alternative function I could use that would be compatible with
> GCC -ffast-math?  Removing the -ffast-math option from our projects
> would be highly undesirable for us, as performance is paramount.

If you don't use any floating point with SQLite then just remove the
#error.  However if you do use floating point with SQLite then you can't
use fast math.  See the following tickets which show a variety of
problematic behaviour with -ffast-math:

http://www.sqlite.org/cvstrac/tktview?tn=3101
http://www.sqlite.org/cvstrac/tktview?tn=3186
http://www.sqlite.org/cvstrac/tktview?tn=3194
http://www.sqlite.org/cvstrac/tktview?tn=3202

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFItJj7mOOfHg372QQRAlXDAJ9n+/Xe1E/1DszYXxCcVPjb+pxHOwCfcB5J
XkV7LD9lbEv59oK9WS+r174=
=8d1g
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] GCC -ffast-math safe version of sqlite3IsNaN

2008-08-26 Thread Brown, Daniel
Unfortunately our build system is automated and not particularly agile, it can 
be done but it would be preferred not to have to do that and to replace the 
offending function instead but I've never had to write a IsNaN test.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael Ruck
Sent: Tuesday, August 26, 2008 3:59 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] GCC -ffast-math safe version of sqlite3IsNaN

Have you tried to compile the util.c/amalgamation file without -ffast-math
and
use it with your other sources (compiled with -ffast-math)?

Mike

> -Ursprüngliche Nachricht-
> Von: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] Im Auftrag von Brown, Daniel
> Gesendet: Mittwoch, 27. August 2008 00:38
> An: General Discussion of SQLite Database
> Betreff: [sqlite] GCC -ffast-math safe version of sqlite3IsNaN
> 
> Good afternoon list,
> 
> In the process of upgrading to 3.6.1 I've run into the error 
> on line 46 of util.c about int sqlite3IsNaN(double x) not 
> behaving consistently with the GCC  -ffast-math compiler 
> option (which we have enabled), is there any alternative 
> function I could use that would be compatible with GCC 
> -ffast-math?  Removing the -ffast-math option from our 
> projects would be highly undesirable for us, as performance 
> is paramount.
> 
> Cheers,
> 
> Daniel Brown
> "The best laid schemes o' mice an' men, gang aft agley"
> 
> 
> ___
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] GCC -ffast-math safe version of sqlite3IsNaN

2008-08-26 Thread Michael Ruck
Have you tried to compile the util.c/amalgamation file without -ffast-math
and
use it with your other sources (compiled with -ffast-math)?

Mike

> -Ursprüngliche Nachricht-
> Von: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] Im Auftrag von Brown, Daniel
> Gesendet: Mittwoch, 27. August 2008 00:38
> An: General Discussion of SQLite Database
> Betreff: [sqlite] GCC -ffast-math safe version of sqlite3IsNaN
> 
> Good afternoon list,
> 
> In the process of upgrading to 3.6.1 I've run into the error 
> on line 46 of util.c about int sqlite3IsNaN(double x) not 
> behaving consistently with the GCC  -ffast-math compiler 
> option (which we have enabled), is there any alternative 
> function I could use that would be compatible with GCC 
> -ffast-math?  Removing the -ffast-math option from our 
> projects would be highly undesirable for us, as performance 
> is paramount.
> 
> Cheers,
> 
> Daniel Brown
> "The best laid schemes o' mice an' men, gang aft agley"
> 
> 
> ___
> 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] GCC -ffast-math safe version of sqlite3IsNaN

2008-08-26 Thread Brown, Daniel
Good afternoon list,

In the process of upgrading to 3.6.1 I've run into the error on line 46
of util.c about int sqlite3IsNaN(double x) not behaving consistently
with the GCC  -ffast-math compiler option (which we have enabled), is
there any alternative function I could use that would be compatible with
GCC -ffast-math?  Removing the -ffast-math option from our projects
would be highly undesirable for us, as performance is paramount.

Cheers,

Daniel Brown 
"The best laid schemes o' mice an' men, gang aft agley"


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


Re: [sqlite] create table if not exists & virtual table?

2008-08-26 Thread Dennis Cote
Petite Abeille wrote:
> 
> Is it possible to use 'if not exists' in conjunction with the creation  
> DDL for a virtual table?
> 

No, its not possible.

The syntax of a "create table" statement is shown here 
http://www.sqlite.org/lang_createtable.html and that for a "create 
virtual table" statement is shown here 
http://www.sqlite.org/lang_createvtab.html. The virtual table statement 
does not allow the optional "if not exists" clause.

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


Re: [sqlite] Get transaction number

2008-08-26 Thread Kees Nuyt
On Tue, 26 Aug 2008 20:11:05 +0400, you wrote:

>Hello!
>
>? ? ?? Tuesday 26 August 2008 18:59:55 Igor Tandetnik ???(?):
>> > I don't know method to set triggers on begin and commit transaction
>> > events.
>>
>> You could log begin/end transaction (along with any other statements) by
>> installing sqlite3_trace hook.
>>
>> Another approach would be to use sqlite3_commit_hook,
>> sqlite3_rollback_hook, sqlite3_get_autocommit. Call
>> sqlite3_get_autocommit from inside your custom function to see whether
>> the statement is part of an explicit transaction. sqlite3_commit_hook
>> and sqlite3_rollback_hook tell you when a transaction ends. The first
>> query that's part of an explicit transaction after a previous
>> transaction (if any) has ended, starts a new one.
>>
>> By the way, see sqlite3_update_hook. Perhaps you could use that in lieu
>> of your triggers.
>
>Thanks, it's better way for my task. But is it safe to logging uncommited 
>changes? 

Logging uncommitted changes is fine as long as 
- you also log commits
- only replay / evaluate complete transactions, 
  complete being defined as "having a commit on the logfile"

Logging rollbacks strictly isn't necessary, because rolled
back transactions have to be identified by the missing
commit logrecord, not by the presence of a rollback
logrecord. Reason: In case of crashes you won't be able to
log rollbacks, anyway, whereas SQLite will rollback using
the journal the first time a new connection is made.

>Best regards, Alexey.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] FTS, snippet & Unicode?

2008-08-26 Thread Petite Abeille
Hello,

% sqlite3 -version 3.5.9

FTS's snippet seems to truncate Unicode sequences at time.

For example, given the following text:

Motto: ძალა ერთობაშია  (Georgian)
"Strength is in Unity"

FTS's snippet would return the extract bellow for 'Unity, Freedom,  
Work':

“… ��ია  (Georgian) "Strength is in Unity" Anthem:  
Tavisupleba  ("Freedom") Capital (and largest city) … America.  
Relations with NATO Georgia is working in becoming a full member of  
NATO. In …”

Note how ერთობაშია has been truncated to ��ია.

Thoughts?

Thanks in advance.

Cheers,

--
PA.
http://alt.textdrive.com/nanoki/



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


[sqlite] create table if not exists & virtual table?

2008-08-26 Thread Petite Abeille
Hello,

Is it possible to use 'if not exists' in conjunction with the creation  
DDL for a virtual table?

For example:

create table if not exists document( content text )

vs.

create virtual table if not exists document using fts3( content text )

The first statement works as advertise, but the second one fails with  
a syntax error:

SQL error: near "not": syntax error

What gives?

Thanks in advance.

Cheers,

--
PA.
http://alt.textdrive.com/nanoki/

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


Re: [sqlite] Get transaction number

2008-08-26 Thread Alexey Pechnikov
Hello!

В сообщении от Tuesday 26 August 2008 21:24:24 Igor Tandetnik написал(а):
> > Thanks, it's better way for my task. But is it safe to logging
> > uncommited changes?
>
> You tell me. Aren't your triggers doing that already?

Well, if I'm writing log to other database in single transaction it's correct. 
But for disk-based log It's may be unsafe, I think.

Thanks for answer, I'll use hooks.

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


Re: [sqlite] Get transaction number

2008-08-26 Thread Igor Tandetnik
Alexey Pechnikov <[EMAIL PROTECTED]>
wrote:
>> By the way, see sqlite3_update_hook. Perhaps you could use that in
>> lieu of your triggers.
>
> Thanks, it's better way for my task. But is it safe to logging
> uncommited changes?

You tell me. Aren't your triggers doing that already?

Igor Tandetnik 



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


Re: [sqlite] Get transaction number

2008-08-26 Thread Alexey Pechnikov
Hello!

В сообщении от Tuesday 26 August 2008 18:59:55 Igor Tandetnik написал(а):
> > I don't know method to set triggers on begin and commit transaction
> > events.
>
> You could log begin/end transaction (along with any other statements) by
> installing sqlite3_trace hook.
>
> Another approach would be to use sqlite3_commit_hook,
> sqlite3_rollback_hook, sqlite3_get_autocommit. Call
> sqlite3_get_autocommit from inside your custom function to see whether
> the statement is part of an explicit transaction. sqlite3_commit_hook
> and sqlite3_rollback_hook tell you when a transaction ends. The first
> query that's part of an explicit transaction after a previous
> transaction (if any) has ended, starts a new one.
>
> By the way, see sqlite3_update_hook. Perhaps you could use that in lieu
> of your triggers.

Thanks, it's better way for my task. But is it safe to logging uncommited 
changes? 

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


Re: [sqlite] Get transaction number

2008-08-26 Thread Igor Tandetnik
Alexey Pechnikov <[EMAIL PROTECTED]>
wrote:
> ÷ ÓÏÏÂÝÅÎÉÉ ÏÔ Tuesday 26 August 2008 16:44:56 Igor Tandetnik
> ÎÁÐÉÓÁÌ(Á):
>>> Can I group logged queries by any transaction id?
>>
>> Well, BEGIN TRANSACTION and END TRANSACTION are themselves queries,
>> and thus will presumably appear in your log. Any queries in between
>> would then belong to that transaction.
>
> I don't know method to set triggers on begin and commit transaction
> events.

You could log begin/end transaction (along with any other statements) by 
installing sqlite3_trace hook.

Another approach would be to use sqlite3_commit_hook, 
sqlite3_rollback_hook, sqlite3_get_autocommit. Call 
sqlite3_get_autocommit from inside your custom function to see whether 
the statement is part of an explicit transaction. sqlite3_commit_hook 
and sqlite3_rollback_hook tell you when a transaction ends. The first 
query that's part of an explicit transaction after a previous 
transaction (if any) has ended, starts a new one.

By the way, see sqlite3_update_hook. Perhaps you could use that in lieu 
of your triggers.

Igor Tandetnik



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


Re: [sqlite] Get transaction number

2008-08-26 Thread Alexey Pechnikov
Hello!

В сообщении от Tuesday 26 August 2008 16:44:56 Igor Tandetnik написал(а):
> > Can I group logged queries by any transaction id?
>
> Well, BEGIN TRANSACTION and END TRANSACTION are themselves queries, and
> thus will presumably appear in your log. Any queries in between would
> then belong to that transaction.

I don't know method to set triggers on begin and commit transaction events.

For example:

CREATE TABLE groups
(
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  save_date REAL,
  update_date REAL,
  delete_date REAL NOT NULL DEFAULT '',
  name TEXT COLLATE russian UNIQUE NOT NULL DEFAULT ''
);

create trigger groups_insert after insert on groups begin
  SELECT notify_insert('groups', NEW.rowid, NEW.id, NEW.save_date, 
NEW.update_date, NEW.delete_date, NEW.name);
  update groups set save_date = coalesce(save_date, 
julianday('now')),update_date = julianday('now') where rowid=NEW.rowid;
end;

create trigger groups_update_after after update on groups begin
  update groups set update_date = julianday('now') where rowid=OLD.rowid;
  SELECT notify_update('groups', NEW.rowid, NEW.id, NEW.save_date, 
NEW.update_date, NEW.delete_date, NEW.name);
end;

In my log I get lines as:

DATABASE NOTIFY UPDATE  table=groups   rowid=1003646
1003646 2454528.5 2454704.97969 {group_name}
...

(and I'm saving log records to another SQLite database).

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


Re: [sqlite] Get transaction number

2008-08-26 Thread Igor Tandetnik
"Alexey Pechnikov" <[EMAIL PROTECTED]>
wrote in message news:[EMAIL PROTECTED]
> Â ñîîáùåíèè îò Tuesday 26 August 2008 15:58:56 Jeffrey Becker
> íàïèñàë(à):
>> I dont believe sqlite supports named transactions. As such there
>> wouldnt be any identifier to get.
>
> Can I group logged queries by any transaction id?

Well, BEGIN TRANSACTION and END TRANSACTION are themselves queries, and 
thus will presumably appear in your log. Any queries in between would 
then belong to that transaction.

Igor Tandetnik



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


Re: [sqlite] Get transaction number

2008-08-26 Thread Alexey Pechnikov
Hello!

В сообщении от Tuesday 26 August 2008 15:58:56 Jeffrey Becker написал(а):
> I dont believe sqlite supports named transactions.  As such there
> wouldnt be any identifier to get.

Can I group logged queries by any transaction id?

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


Re: [sqlite] Get transaction number

2008-08-26 Thread Jeffrey Becker
I dont believe sqlite supports named transactions.  As such there
wouldnt be any identifier to get.

On Tue, Aug 26, 2008 at 4:47 AM, Alexey Pechnikov
<[EMAIL PROTECTED]> wrote:
> Hello!
>
> How can I get transaction identifier or number (my programm lang is tcl)?
>
> Best regards, Alexey.
> ___
> 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] Get transaction number

2008-08-26 Thread Alexey Pechnikov
Hello!

How can I get transaction identifier or number (my programm lang is tcl)?

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