Re: [sqlite] A Question About Creating and Accessing a SQLite Database in a RAM Drive

[Rich Shepard]

On Mon, 20 Aug 2007, Lee Crain wrote:


Is there any reason why this would not be possible?


Lee,

  Not at all. You can create the databse in memory as long as you have
sufficient storage for it. See the web pages for syntax. If you do not
specify a filename when you invoke sqlite3, it's an in-memory database on
which you can perform all SQL operations.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] A Question About Creating and Accessing a SQLite Database in a RAM Drive

[Lee Crain]

Is there any reason why this would not be possible? 

Data persistence is not required.

Thanks,

Lee Crain


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] FTS2 suggestion

[Cesar D. Rodas]

Hello SQLite community


This is suggestion for the core team suggestion.

As I know ( I can be wrong ) SQLite Full Text Search is only match with hole
words right? It could not be

And also no FT extension to db ( as far I know) is miss spell tolerant, And
I've found this Paper that talks about *Using Superimposed Coding Of N-Gram
Lists For Efficient Inexact Matching*
http://citeseer.ist.psu.edu/cache/papers/cs/22812/http:zSzzSzwww.novodynamics.comzSztrenklezSzpaperszSzatc92v.pdf/william92using.pdf

I was reading and it is not so hard to implement, but it cost a extra
storage space, but I think the benefits are more.

Also following this paper could be done a way to match with fragments of
words... what do you think of it?



-- 
Cesar D. Rodas
http://www.cesarodas.com/
Mobile Phone: 595 961 974165
Phone: 595 21 645590
[EMAIL PROTECTED]
[EMAIL PROTECTED]

Re: [sqlite] Looking for a cryptographic library

[Ulrich Telle]

Hi,

> I'm writing an application that uses SQLite to store user's data,
> and need a library to crypt some stuff, including passwords and data.
> The goal is to crypt before insert and decript after extract tha data,
> so this last can't be seen by others who gain access to the SQLite
> dataBase.

I have written a crypt extension for SQLite which uses the same API as the 
commercial solution of D.R. Hipp. This extension crypts the whole database file 
so it's not even possible to analyze the database schema for unauthorized users.

This extension is distributed together with my wxWidgets component wxSQLite3, 
but it can be used without wxSQLite3 and wxWidgets.

It's downloadable from
 
http://wxcode.sourceforge.net/components/wxsqlite3/

You have to get the SQLite sources, too, to be able to build a SQLite 
DLL/library with encryption support.

One SQLite source file needs modification. The latest wxSQLite3 release 
includes the required modified files up to SQLite 3.3.17. If you decide to use 
my extension and need to support SQLite 3.4.x you may contact me by private 
mail.

> The application don't need military security level :-) 

The extension uses 128-Bit-AES encryption.

> The ideal is some freeware library although commercial products can also
> be considered. Of course the final product must be commercially
> distributable without patent issues. 

My extension may be used in commercial projects.

Regards,

Ulrich

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] FTS2 Question again Python + .NET

[Samuel R. Neff]

"
I'm having the same problem with .net, cant find a function which does
this... 
"

Which .NET wrapper are you using?

System.Data.SQLite has FTS2 precompiled.  http://sqlite.phxsoftware.com

Sam


---
We're Hiring! Seeking a passionate developer to join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Andre du Plessis [mailto:[EMAIL PROTECTED] 
Sent: Monday, August 20, 2007 11:50 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] FTS2 Question again Python + .NET



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] FTS2 Question again Python + .NET

[Andre du Plessis]

Schema:

 

create table files (FILE_NAME TEXT, DATA TEXT);

create index idx_file_name on files (FILE_NAME);

create virtual table fts_files using fts2('file_name', 'data');

 

Ok, I just can't seem to figure out how to load fts2.dll in Python:

 

Standard sqlite3 module that comes with python does not even run a
query:

 

>>> row = conn.cursor().execute("select * from sqlite_master where type
= 'table'").fetchone()

Traceback (most recent call last):

  File "", line 1, in 

OperationalError: malformed database schema - near "VIRTUAL": syntax
error

 

I tried apsw (Another python SQLite Wrapper), this one gets further, but
the error remains:

 

>>> conn2.cursor().execute("select file_name from fts_files where data
match 'BEGIN'")

Traceback (most recent call last):

  File "", line 1, in 

  File "apsw.c", line 4168, in Cursor_execute.sqlite3_prepare_v2

SQLError: SQLError: no such module: fts2

 

 

Sqlite3 and apsw has no function calls I can find to set the
enable_load_extention I tried just loading it in SQL:

 

>>> conn2.cursor().execute("SELECT load_extension('fts2')")

Traceback (most recent call last):

  File "", line 1, in 

  File "apsw.c", line 3518, in resetcursor

SQLError: SQLError: not authorized

 

Obviously this gives an error, the load_extention is off and I cant set
it on.

 

Apsw, claims in the documentation there is a function called
enableloadextention, but for the life of me I cant find it.

 

I'm having the same problem with .net, cant find a function which does
this...

 

Just wondering if anyone has done this in python and C# .NET that can be
of assistance

 

Thanks.

 

 

 

Re: [sqlite] Looking for a cryptographic library

[Teg]

Hello ajm,

Monday, August 20, 2007, 8:20:37 AM, you wrote:

azc> Hi all: 

azc> I'm writing an application that uses SQLite to store user's
azc> data, and need a library to crypt some stuff, including passwords
azc> and data. The goal is to crypt before insert and decript after
azc> extract tha data, so this last can't be seen by others who gain
azc> access to the SQLite dataBase. The application don't need military 
security level :-)

azc> I have been reading about Blowfish, but it seem that it encrypts
azc> data in 8-byte blocks, and I suppose that it need pad the data to
azc> 8-byte round, who might cause some headache. 

azc> The ideal is some freeware library although commercial products
azc> can also be considered. Of course the final product must be
azc> commercially distributable without patent issues. 

azc> Any advice in this matter would be grateful 

azc> A.J.Millan 


I use OpenSSL with AES to encrypt specific fields. You do have to pad,
at least with AES. If you're just encrypting strings the padding won't
matter as long as you include the null terminator in the encrypted
data. When it decrypts, you'll still end up with a null terminated
string. You save the encrypted data as blobs though, you could text
encode them and save them as strings if you wanted too. You MAY want
to CRC the strings and include the CRC in the blob so, you know
whether the decrypt worked properly.

I'm planning on buying the crypto-version of SQLite too but, that part
of my project is sidelined at the moment. The benefit of the
crypto-sqlite is that users can't even see the tables or schema so,
you're really protected. I don't want the schema visible so, my
competitors can't see what I'm doing.

Keep in mind, if the password for the DB is hard-coded into the program
then anyone can get at it.

-- 
Best regards,
 Tegmailto:[EMAIL PROTECTED]


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] In-memory Database only

[Capiau, Valentin]

Hi,

I'm currently working on a project for embedded device that needs to
work with in-memory databases only.

I tried to compile sqlite with the -DSQLITE_OMIT_DISKIO option but it
seems that there are some issues while linking.

Is there any easy way to fix this?

Thanks a lot
Valentin
***
This e-mail is confidential, the property of NDS Ltd and intended for the 
addressee only.  Any dissemination, copying or distribution of this message or 
any attachments by anyone other than the intended recipient is strictly 
prohibited.  If you have received this message in error, please immediately 
notify the [EMAIL PROTECTED] and destroy the original message.  Messages sent 
to and from NDS may be monitored.  NDS cannot guarantee any message delivery 
method is secure or error-free.  Information could be intercepted, corrupted, 
lost, destroyed, arrive late or incomplete, or contain viruses.  We do not 
accept responsibility for any errors or omissions in this message and/or 
attachment that arise as a result of transmission.  You should carry out your 
own virus checks before opening any attachment.  Any views or opinions 
presented are solely those of the author and do not necessarily represent those 
of NDS.

To protect the environment please do not print this e-mail unless necessary.

NDS Limited Registered office: One Heathrow Boulevard, 286 Bath Road, West 
Drayton, Middlesex, UB7 0DQ, United Kingdom. A company registered in England 
and Wales  Registered no. 3080780   VAT no. GB 603 8808 40-00
***
 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Unique ids for each record

[Sreedhar.a]

Hi Lokesh,

Thanks for your suggestions.

My proble is assigning a unique record for each record in a table.

The database table name MUSIC.

I have 3 columns Artist Album and Tracks.

Artist1 Album1  Track1
Artist2 Album2  Track2
Artist3 Album2  Track3

I need to assign a unique id value for each item in the row.

For example Artist1 with id = 100, Album1 id = 200 Track1 id = 300
Artist2 with id = 101, Album2 id = 201 Track2 id = 301
Etc.

How I can achieve in assigning a unique id for each and every item.

One more problem:

I want to restrict the Artist range to 100 to 199 so that with the id 
I can make my search fast and also I knew with unique id that I need to
search for the Artist alone.


Thanks & Best Regards,
A.Sreedhar.
 
-Original Message-
From: Babu, Lokesh [mailto:[EMAIL PROTECTED] 
Sent: Monday, August 20, 2007 6:27 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Unique ids for each record

refer *In-Memory Database: Delete rows on a Table increases the memory usage
*

More help needed or didn't understand, let me know.


On 8/17/07, Sreedhar.a <[EMAIL PROTECTED]> wrote:
>
> Thanks Marcus
>
>
>
> I am working in 3.3.6 version.
>
> I created a table with id as integer primary key and with constraint id<6.
>
>
>
> For eg,
> "create table Test(id INTEGER PRIMARY KEY CHECK (id > 0 and id < 
> 6),name integer);"
>
> "insert into Test (name) values('name1');"
> "insert into Test (name) values('name2');"
> "insert into Test (name) values('name3');"
> "insert into Test (name) values('name4');"
> "insert into Test (name) values('name5');"
>
> The output for :"select rowid,* from Test;"
> Rowid   id  name
> 1  1  name1
> 2  2  name2
> 3  3  name3
> 4  4  name4
> 5  5  name5
>
> Now I deleted 2 records.
>
> "delete from Test where id=3;"
> "delete from Test where id=4;"
>
> "vacuum Test;"
>
> The output for   :"select rowid,* from Test;"
>
> Rowid   id  name
> 1  1  name1
> 2  2  name2
> 5 5  name5
>
>
>
> My doubt is
>
> . if I tried to insert one more file ,I cant able to insert.error
> like "constraint failed" flashes.Since I deleted 2 records that space 
> is freed only.i tried after doing vacuum also.Is there any other way 
> to insert.
>
>
>
> . The rowid is also not updated after doing vacuum.Is there any
> other way to update.If I tried the same by creating a table with 
> INTEGER alone at that time rowed is updating properly after "Vacuum Test".
>
> Can any one please clarify my doubts.
>
>
>
> Best Regards,
> A.Sreedhar.
>
>
> -Original Message-
> From: Markus Hoenicka [mailto:[EMAIL PROTECTED]
> Sent: Thursday, August 16, 2007 7:39 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Unique ids for each record
>
> I assume you'd rather want three separate tables (artist, album,
> track) with an autoincrementing ID field per table. Your approach 
> would not allow users to own more than 100 albums.
>
> regards,
> Markus
>
> Quoting "Sreedhar.a" <[EMAIL PROTECTED]>:
>
> > Hi,
> >
> > I have a table with 3 columns.
> >
> > Artist Album and tracks.
> >
> > Can i fix a range of ids for each column like 1-100 for Artist 
> > 101-200 for Album and 201-300 for tracks
> >
> > So that I can have a unique number(id) for each record.
> >
> > Will there be a problem while deleting and inserting the records?
> >
> > Thanks and best regards,
> > A.Sreedhar.
> >
> >
> >
> >
>
>
>
> --
> Markus Hoenicka
> [EMAIL PROTECTED]
> (Spam-protected email: replace the quadrupeds with "mhoenicka") 
> http://www.mhoenicka.de
>
>
>
> --
> --
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> --
> --
> -
>
>
>
>
>
> --
> --- To unsubscribe, send email to 
> [EMAIL PROTECTED]
>
> --
> ---
>
>



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] To increase search speed

[Samuel R. Neff]

Method 3, normalization, is the right route but I think the implementation
needs a little more work.  First don't store both Artist ID and Artist Name
in the Music table--only store the ID. This goes for AlbumID/Name and
GenreID/Name as well.  Then reorder the columns to put the integers first in
the field order for the Music table, especially the foreign keys.  Finally,
create appropriate indexes.  Keep in mind that SQLite will only use one
index per table in a query so most likely you'll want a compound index that
indexes multiple foreign key fields at once.  

For queries that used to be "select distinct AlbumName from music" now just
query "select AlbumName from Albums" and you'll see a huge performance
increase (notice they don't need the music table at all).

Also, I would suggest using consistent names across tables.  So instead of
naming all your id fields "id" use something more specific like "ArtistID"
and "AlbumID" that way the same field name is used in both the Albums table
as well as the Music table.  This will make it easier to program against (no
effect on performance, just your sanity).
HTH,

Sam


---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Sreedhar.a [mailto:[EMAIL PROTECTED] 
Sent: Saturday, August 18, 2007 1:23 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] To increase search speed


Method 3:

Joining Multiple tables.
I have created 4 tables as follows;

"create table  ALBUMS (id integer primary key,Album
text,unique(Album));"
"create table  ARTISTS (id integer primary key,Artist
text,unique(Artist));"
"create table  GENRES (id integer primary key,Genre
text,unique(Genre));"

"create table MUSIC (Id integer primary key, AlbumName text not
null collate nocase,Track text not null collate nocase,ArtistName text not
null collate nocase,URL text not null collate nocase , Duration integer,
TrackFormat text not null collate nocase, BitRate integer, sampleRate
integer, Channels integer, Filesize integer,album_id integer,artist_id
integer, GenreName text not null collate nocase ,Genre_Id integer);"

Here album_id , artist_id and Genre_Id are the id values of
ALBUMS,ARTISTS,GENRES Tables.
This shows better performance than indexing for the following searches.

 
Select distinct AlbumName from MUSIC;
 
Select  distinct ArtistName from MUSIC;
 
Select  distinct  GenreName from MUSIC;


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Looking for a cryptographic library

[Günter Greschenz]

hi,

i've written some sqlite-functions to crypt (blowfish) or compress 
(bzip) data in sqlite:

e.g.
   insert into blubs values (crypt('data','pwd'))
or
   select from xyz where decompress(data) = 'blablabla'
or
   select from xyz where data = compress('blablabla')
...

but you have to wait until next weekend, because i'v traveling for my 
company at the moment and return on friday (i hope :-)


cu, gg



[EMAIL PROTECTED] wrote:
Hi all: 

I'm writing an application that uses SQLite to store user's data, and need a library to crypt some stuff, including passwords and data. The goal is to crypt before insert and decript after extract tha data, so this last can't be seen by others who gain access to the SQLite dataBase. The application don't need military security level :-) 

I have been reading about Blowfish, but it seem that it encrypts data in 8-byte blocks, and I suppose that it need pad the data to 8-byte round, who might cause some headache. 

The ideal is some freeware library although commercial products can also be considered. Of course the final product must be commercially distributable without patent issues. 

Any advice in this matter would be grateful 

A.J.Millan 





  



No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.484 / Virus Database: 269.12.0/961 - Release Date: 19.08.2007 07:27
  

Re: [sqlite] Unique ids for each record

[Babu, Lokesh]

refer *In-Memory Database: Delete rows on a Table increases the memory usage
*

More help needed or didn't understand, let me know.


On 8/17/07, Sreedhar.a <[EMAIL PROTECTED]> wrote:
>
> Thanks Marcus
>
>
>
> I am working in 3.3.6 version.
>
> I created a table with id as integer primary key and with constraint id<6.
>
>
>
> For eg,
> "create table Test(id INTEGER PRIMARY KEY CHECK (id > 0 and id < 6),name
> integer);"
>
> "insert into Test (name) values('name1');"
> "insert into Test (name) values('name2');"
> "insert into Test (name) values('name3');"
> "insert into Test (name) values('name4');"
> "insert into Test (name) values('name5');"
>
> The output for :"select rowid,* from Test;"
> Rowid   id  name
> 1  1  name1
> 2  2  name2
> 3  3  name3
> 4  4  name4
> 5  5  name5
>
> Now I deleted 2 records.
>
> "delete from Test where id=3;"
> "delete from Test where id=4;"
>
> "vacuum Test;"
>
> The output for   :"select rowid,* from Test;"
>
> Rowid   id  name
> 1  1  name1
> 2  2  name2
> 5 5  name5
>
>
>
> My doubt is
>
> . if I tried to insert one more file ,I cant able to insert.error
> like "constraint failed" flashes.Since I deleted 2 records that space is
> freed only.i tried after doing vacuum also.Is there any other way to
> insert.
>
>
>
> . The rowid is also not updated after doing vacuum.Is there any
> other way to update.If I tried the same by creating a table with INTEGER
> alone at that time rowed is updating properly after "Vacuum Test".
>
> Can any one please clarify my doubts.
>
>
>
> Best Regards,
> A.Sreedhar.
>
>
> -Original Message-
> From: Markus Hoenicka [mailto:[EMAIL PROTECTED]
> Sent: Thursday, August 16, 2007 7:39 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Unique ids for each record
>
> I assume you'd rather want three separate tables (artist, album,
> track) with an autoincrementing ID field per table. Your approach would
> not
> allow users to own more than 100 albums.
>
> regards,
> Markus
>
> Quoting "Sreedhar.a" <[EMAIL PROTECTED]>:
>
> > Hi,
> >
> > I have a table with 3 columns.
> >
> > Artist Album and tracks.
> >
> > Can i fix a range of ids for each column like 1-100 for Artist 101-200
> > for Album and 201-300 for tracks
> >
> > So that I can have a unique number(id) for each record.
> >
> > Will there be a problem while deleting and inserting the records?
> >
> > Thanks and best regards,
> > A.Sreedhar.
> >
> >
> >
> >
>
>
>
> --
> Markus Hoenicka
> [EMAIL PROTECTED]
> (Spam-protected email: replace the quadrupeds with "mhoenicka")
> http://www.mhoenicka.de
>
>
>
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> 
> -
>
>
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>

Re: [sqlite] In-Memory Database: Delete rows on a Table increases the memory usage.

[Babu, Lokesh]

Hi Folks,

This is the little hack, where you can free up the memory, inside
*btree.h*enable the SQLITE_DEFAULT_AUTOVACUUM to 1, and inside
*btree.c*, make sure to pBt->autoVacuum = SQLITE_DEFAULT_AUTOVACUUM; enable
this statement. The statement is in *if* block, so you need to put outside.

This solution gives perfect memory results even after delete, update,
replace, drop etc has been done.

If *delete *is done it should free up the memory, but it was keeping the
contents, particularly if I'm using In-Memory database I need that memory to
be freed and give room for other records.

BTW thanks for everyone who replied. The requirement is for a small realtime
embedded device. I don't want rollback or anything to happen, as database
will reside on memory and it will not be dumped on disk or anywhere, if the
device is rebooted the database will be created afresh.

If anybody has better solution, please let me know.


On 8/9/07, Joe Wilson <[EMAIL PROTECTED]> wrote:
>
> --- "Babu, Lokesh" <[EMAIL PROTECTED]> wrote:
> > Does anybody knows the exact code, a little hack where can I free up the
> > memory, I don't want it to use it for future requests.
> >
> > Even drop table consumes memory. :-(.
> >
> > If we are doing in-memory database operation, why do we want to maintain
> the
> > free'd memory pages?
>
> On Linux using sqlite 3.4.1:
>
> -- start sqlite3 shell
>
> -- 2m VIRT, 1m RES
>
> pragma temp_store=memory;
> CREATE TABLE abc(a,b,c);
> INSERT INTO "abc" VALUES(5400,'some dumb phrase to fill stuff',-345.7);
> INSERT INTO "abc" VALUES(-1234,'the quick brown fox', 20394203492340.5);
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
> insert into abc select * from abc;
>
> -- 167m VIRT, 166m RES
>
> delete from abc where a != b;
>
> -- 200m VIRT, 199m RES peak usage during delete
>
> -- 167m VIRT, 166m RES after delete
>
> vacuum;
>
> -- 18m VIRT, 1m RES
>
> Are you seeing different results?
>
>
>
>
> Ready
> for the edge of your seat?
> Check out tonight's top picks on Yahoo! TV.
> http://tv.yahoo.com/
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>

[sqlite] About "make install" problems on Windows/MINGW

[Paolo Bormida]

Hi all!

I would like to share a few thoughts about building SQLite on Windows using 
MingW and MSYS.

I have downloaded the full source package and after unpacked I used MSYS to 
run configure with --prefix option to change the path where "install" would 
place libraries and binaries.

The build in MSYS using the Makefile that configure generated worked fine, but 
when I attempted to run "make install" I got an error since no tclsh was 
available.

The error was tricky, since I have run configure with --disable-tcl and so no 
tcl libraries should have been built.

Reading the error better I discovered the problem was that "make install" 
tried to build the amalgamation.

Why building the amalgamation when not asked to using "make sqlite3.c"???

The problem lies in "install" target dependencies as generated by configure 
running in MSYS.

The "install" target depends on "sqlite3" and the line in the Makefile looks 
like this:

install: sqlite3 libsqlite3.la sqlite3.h ${HAVE_TCL:1=tcl_install}

For reasons I not had time to investigate, make gets "sqlite3" dependency as 
sqlite3.c (the amalgamation!) and tries to build it using tclsh.

Just renaming "sqlite3" to "sqlite3.exe" in both lines:

install: sqlite3.($TEXE) libsqlite3.la sqlite3.h ${HAVE_TCL:1=tcl_install}

and 

$(LTINSTALL) sqlite3.($TEXE) $(DESTDIR)$(exec_prefix)/bin

solved the problem and I was able to "make install" work properly.

So the question is why does configure does not generate sqlite3.exe instead in 
MSYS?

Best

Paolo



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Looking for a cryptographic library

[Clay Dowling]

There are two options:

1. D. Richard Hipp sells a version which encrypts the file on disk.  This
might be the fastest and easiest route.

2. OpenSSL offers as much encryption as you're likely to need if you're
willing to deal with the paucity of documentation.  Linux Journal had a
couple of articles on using OpenSSL for encryption, although I don't know
if they covered using OpenSSL from code.

Clay


[EMAIL PROTECTED] wrote:
> Hi all:
>
> I'm writing an application that uses SQLite to store user's data, and need
> a library to crypt some stuff, including passwords and data. The goal is
> to crypt before insert and decript after extract tha data, so this last
> can't be seen by others who gain access to the SQLite dataBase. The
> application don't need military security level :-)
>
> I have been reading about Blowfish, but it seem that it encrypts data in
> 8-byte blocks, and I suppose that it need pad the data to 8-byte round,
> who might cause some headache.
>
> The ideal is some freeware library although commercial products can also
> be considered. Of course the final product must be commercially
> distributable without patent issues.
>
> Any advice in this matter would be grateful
>
> A.J.Millan
>
>
>
>


-- 
Lazarus Registration
http://www.lazarusid.com/registration.shtml


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Looking for a cryptographic library

[ajm]

Hi all:

I'm writing an application that uses SQLite to store user's data, and need a 
library to crypt some stuff, including passwords and data. The goal is to crypt 
before insert and decript after extract tha data, so this last can't be seen by 
others who gain access to the SQLite dataBase. The application don't need 
military security level :-)

I have been reading about Blowfish, but it seem that it encrypts data in 8-byte 
blocks, and I suppose that it need pad the data to 8-byte round, who might 
cause some headache.

The ideal is some freeware library although commercial products can also be 
considered. Of course the final product must be commercially distributable 
without patent issues.

Any advice in this matter would be grateful

A.J.Millan

Re: [sqlite] Increasing performance of joins with a group by clause?

[Jef Driesen]

John Machin wrote:

On 19/08/2007 4:01 AM, Jef Driesen wrote:

Suppose I have two related tables:

CREATE TABLE events (
 id INTEGER PRIMARY KEY NOT NULL,
 place_id INTEGER
);

CREATE TABLE places (
 id INTEGER PRIMARY KEY NOT NULL,
 name TEXT
);

INSERT INTO places (id, name) VALUES (1, 'Place A');
INSERT INTO places (id, name) VALUES (2, 'Place B');
INSERT INTO places (id, name) VALUES (3, 'Place C');

INSERT INTO events (id, place_id) VALUES (1, 1);
INSERT INTO events (id, place_id) VALUES (2, 2);
INSERT INTO events (id, place_id) VALUES (3, 1);

Now, I want to count the number of 'events' at every 'place'. I started
with a simple join and a group by clause to perform the count:

SELECT name, count (*) AS count
FROM events LEFT JOIN places ON places.id = events.place_id
GROUP BY events.place_id;

name|count
Place A|2
Place B|1

It executes very fast (because the join can take advantage of the
primary key) but does not produce the desired result. As you can see,
not all places are included in the result:

So I changed swapped the tables in the join:

SELECT name, count (events.place_id) AS count
FROM places LEFT JOIN events ON events.place_id = places.id
GROUP BY places.id;

name|count
Place A|2
Place B|1
Place C|1


Folks are dumb where I come from; can someone please explain how this 
could be correct? The INSERT statements create 2 events at place_id 1 
(A), 1 event at place_id  2 (B) and *ZERO* events at place_id 3 (C).


Please pardon me if this is strange behaviour (I'm new to this mailing 
list) but I actually *ran* the OP's code, with this result:


C:\junk>sqlite3 junk.sq3
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> .read outer_join_query.sql
--- query 1 ---
Place A|2
Place B|1
--- query 2 ---
Place A|2
Place B|1
Place C|0
--- query 3 ---
Place A|2
Place B|1
Place C|0


The results in my original post are indeed wrong! Initially I started 
with some random insert statements for the events table, but they 
happened to be a bad choice to illustrate my problem. So I changed them, 
but forgot to update the results for the second query...




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] To increase search speed

[Sreedhar.a]

Thanks Raghavendra, 

We use sqlite statements for search. 


Best Regards,
A.Sreedhar.
 

-Original Message-
From: RaghavendraK 70574 [mailto:[EMAIL PROTECTED] 
Sent: Saturday, August 18, 2007 10:13 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] To increase search speed


use sqlite statements.

regards
ragha

**
 This email and its attachments contain confidential information from
HUAWEI, which is intended only for the person or entity whose address is
listed above. Any use of the information contained herein in any way
(including, but not limited to, total or partial disclosure, reproduction,
or dissemination) by persons other than the intended recipient(s) is
prohibited. If you receive this e-mail in error, please notify the sender by
phone or email immediately and delete it!
 

*

- Original Message -
From: "Sreedhar.a" <[EMAIL PROTECTED]>
Date: Saturday, August 18, 2007 10:52 am
Subject: [sqlite] To increase search speed

> Hi
> I am working in 3.3.6.
> I have created a table with 4 records with 12 fields as follows.
> 
> "create table MUSIC (Id integer primary key, AlbumName text not null 
> collate nocase,Track text not null collate nocase,ArtistName text not 
> null collatenocase,URL text not null collate nocase , Duration 
> integer, TrackFormat text not null collate nocase, BitRate integer, 
> sampleRate integer, Channels integer, Filesize integer GenreName text 
> not null collate nocase);"
> 
> I will often search for the following fields only.
> 
>Select distinct 
> AlbumName from MUSIC;
>Select  distinct 
> ArtistName from MUSIC;
>Select  distinct 
> GenreName from MUSIC;
>Select  distinct 
> AlbumName for particular ArtistName
>Select  Track for 
> particular AlbumName
>Select  distinct 
> ArtistName for particular GenreName
>  
> To obtain nice search speed which method will work fine.
> I have tried wilth the following methods.
> 
> Method 1:
> 
> It's the one described above
> 
> Method 2:
> 
> By doing indexing.I tried with the following.
> 
>"create  index Musicidx1 on MUSIC(ArtistName  collate 
> nocase,AlbumNamecollate nocase);"   
>"create  index Musicidx2 on MUSIC(AlbumName collate 
> nocase,ArtistNamecollate nocase,URL collate nocase);"
> 
> This gives better performance than method 1 for the following 
> searches;
>
> Selectdistinct  AlbumName for particular ArtistName
>
> SelectTrack for particular AlbumName
>
> Selectdistinct ArtistName for particular GenreName
> 
> Method 3:
> 
> Joining Multiple tables.
> I have created 4 tables as follows;
> 
>"create table  ALBUMS (id integer primary key,Album 
> text,unique(Album));"
>"create table  ARTISTS (id integer primary key,Artist 
> text,unique(Artist));"
>"create table  GENRES (id integer primary key,Genre 
> text,unique(Genre));"
> 
>"create table MUSIC (Id integer primary key, AlbumName text 
> not null collate nocase,Track text not null collate nocase,ArtistName 
> text not null collate nocase,URL text not null collate nocase , 
> Duration integer,TrackFormat text not null collate nocase, BitRate 
> integer, sampleRateinteger, Channels integer, Filesize 
> integer,album_id integer,artist_idinteger, GenreName text not null 
> collate nocase ,Genre_Id integer);"
> 
> Here album_id , artist_id and Genre_Id are the id values of 
> ALBUMS,ARTISTS,GENRES Tables.
> This shows better performance than indexing for the following 
> searches.
> 
> Select distinct AlbumName from MUSIC;
> 
> Select  distinct ArtistName from MUSIC;
> 
> Select  distinct  GenreName from MUSIC; Method 4:
> 
> Inmemory method.I will copy all the content from the temporary 
> database to inmemory and then performing search.
> If I am using this method means then while inserting records , that 
> recordwill be inserted into the temporary memory only.
> But I want to be inserted in to the original database also. Is there 
> any other way to do this.
> 
> Can any one help to increase my search speed.
> Thanks in Advance.
> 
> Regards,
> Sreedhar
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
>  
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-