Re: [sqlite] equality searches and range searches with encrypteddata
On Mon, Aug 25, 2008 at 6:33 PM, Derek Developer <[EMAIL PROTECTED]> 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
Re: [sqlite] sqlite3_stmt declaration
Write a C test.c program that uses the desired type. Then do "gcc -E test.c > test.txt". This will expand/flatten all the macros. Open the test.txt file and look for the type. On Mon, Aug 25, 2008 at 5:30 PM, Brown, Daniel <[EMAIL PROTECTED]> wrote: > Good afternoon list, > > I'm attempting to wrap SQLite with Managed C++ and I'm getting some > compiler warnings as the compiler/linker is have trouble finding the > declaration of the structure 'sqlite3_stmt', I've tried looking for it > manually but I can't find it either all I can find is a typedef on line > 2569 of slite3.h. This isn't enough to stop the CLR compiler form > complaining, I had a similar warning with the structure 'sqlite3' but > including sqlite3Int.h resolved that warning as the structure is defined > there, however tracking down the header file that defines 'sqlite3_stmt' > seems to be leading no where... > > Cheers, > > Daniel Brown | Software Engineer @ EA Canada > "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
Re: [sqlite] equality searches and range searches with encrypteddata
>No, the index is stored in a separate Btree. The master table simply >stores the page number of that btree's root page. With that information >SQLite can read and decrypt the index's root page and begin a O(log N) >search for the first matching record, reading in and decrypting more >pages as required. >... an index will work securely and efficiently for such a search in an >encrypted database. 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? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] equality searches and range searches with encrypteddata
Igor Tandetnik wrote: > > Since I'm not entirely clear of the set of premises you refer to as > "that", I'm not sure whether they happen to be the case or not. But > since I know the conclusion you arrived at is false, I can only assume > that one or more of those premises are incorrect, and/or the logical > deduction from the premises to the conclusion is flawed. > Classic. :-) Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] equality searches and range searches with encrypteddata
Derek Developer <[EMAIL PROTECTED]> wrote: > appologies, "master database" should read "MASTER TABLE" > This is where the index is stored I assume? An index is an index. It's not stored in any table, "MASTER" or otherwise. > You suggested that each record would NOT be decrypted for a SELECT > because the INDEX would handle that work. Index doesn't "handle" any work. It's just a data structure. The database engine does the work. It might use an index to speed up execution of certain statements. Yes, in many cases, the engine can avoid reading every record in the table, by using an index to narrow the search down to the relevant records. > Since the INDEX is > proabably not a MAC hash of the SS# then it would be accessible from > the MASTER TABLE yes? I'm not sure what this "MASTER TABLE" you speak of is, or how an index would be accessible from it. > If that is the case then clearly an Index is not a viable solution > and each page will have to be decrypted to perform and equlity/range > search. Since I'm not entirely clear of the set of premises you refer to as "that", I'm not sure whether they happen to be the case or not. But since I know the conclusion you arrived at is false, I can only assume that one or more of those premises are incorrect, and/or the logical deduction from the premises to the conclusion is flawed. >> Unlike the situation here, you can't encrypt individual columns > > I have several ASm implementations of AES and secure HASH Algos that > are very very fast. I can apply these to individual columns for each > row. Sure, you can build a homebrewn solution. It is then up to you to keep it secure, of course. I can't help but notice that you have conveniently omitted the second half of my statement above. > Before I implement this, I wanted to make sure I understand the > implementation at the page level. Obviously a single Row can take up > more than one page, but I am still not clear if a single page can > ever contain more than one Row? Yes it can. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] equality searches and range searches with encrypteddata
Derek Developer wrote: > appologies, "master database" should read "MASTER TABLE" This is > where the index is stored I assume? > No, the index is stored in a separate Btree. The master table simply stores the page number of that btree's root page. With that information SQLite can read and decrypt the index's root page and begin a O(log N) search for the first matching record, reading in and decrypting more pages as required. > > If that is the case then clearly an Index is not a viable solution > and each page will have to be decrypted to perform and equlity/range > search. > No, an index will work securely and efficiently for such a search in an encrypted database. > > Before I implement this, I wanted to make sure I understand the > implementation at the page level. Obviously a single Row can take up > more than one page, but I am still not clear if a single page can > ever contain more than one Row? > Yes, a table page can contain multiple rows, and an index page can contain multiple index entries. You may want to review http://www.sqlite.org/arch.html. The encryption and decryption is done between the pager and the OS interface layers. Nothing else changes, and all the data is stored securely encrypted in the pages of the file. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] equality searches and range searches with encrypteddata
appologies, "master database" should read "MASTER TABLE" This is where the index is stored I assume? You suggested that each record would NOT be decrypted for a SELECT because the INDEX would handle that work. Since the INDEX is proabably not a MAC hash of the SS# then it would be accessible from the MASTER TABLE yes? If that is the case then clearly an Index is not a viable solution and each page will have to be decrypted to perform and equlity/range search. That doesn't sound like it will be fast... >Unlike the situation here, you can't encrypt individual columns I have several ASm implementations of AES and secure HASH Algos that are very very fast. I can apply these to individual columns for each row. I suspect that, as has been pointed out, the disk access would be more time consuming than the encryption. I would then do a MAC index in a secondary column. Before I implement this, I wanted to make sure I understand the implementation at the page level. Obviously a single Row can take up more than one page, but I am still not clear if a single page can ever contain more than one Row? Igor Tandetnik <[EMAIL PROTECTED]> wrote: Derek Developer wrote: > So if the data is indexed prior to the encryption step, does SQLite > manage that index internally as a Btree that s stored somewhere? Is > it part of the MASTER database? I'm not sure what you mean by "MASTER database" (as opposed to which other database?) SQLite stores everything in a single file - data, indexes and all. > Obviously there would be a security issue if the index is accessible > as per this: > http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx Unlike the situation here, you can't encrypt individual columns or tables with SQLite (at least not using any products I'm familiar with). You encrypt the whole file. > If I am storing CC# or SS#, the index would contain them yes? Well, if you created an index on those columns, then of course the index would contain values from them. -- With best wishes, Igor Tandetnik With sufficient thrust, pigs fly just fine. However, this is not necessarily a good idea. It is hard to be sure where they are going to land, and it could be dangerous sitting under them as they fly overhead. -- RFC 1925 ___ 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] sqlite3_stmt declaration
Thanks for all the advice, I'll use void* pointers for SQLite internal structures which was my plan B if I couldn't find a declaration to feed the compiler to calm it. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Brown, Daniel Sent: Monday, August 25, 2008 2:31 PM To: General Discussion of SQLite Database Subject: [sqlite] sqlite3_stmt declaration Good afternoon list, I'm attempting to wrap SQLite with Managed C++ and I'm getting some compiler warnings as the compiler/linker is have trouble finding the declaration of the structure 'sqlite3_stmt', I've tried looking for it manually but I can't find it either all I can find is a typedef on line 2569 of slite3.h. This isn't enough to stop the CLR compiler form complaining, I had a similar warning with the structure 'sqlite3' but including sqlite3Int.h resolved that warning as the structure is defined there, however tracking down the header file that defines 'sqlite3_stmt' seems to be leading no where... 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
Re: [sqlite] Reducing SQLite Memory footprint(!)
On Mon, 25 Aug 2008 12:26:18 -0700, you wrote: >Interesting, I just tried that in my test application and Dennis's and I >get access violations during the vacuum command execution when trying to >resize the pages from 1k to 4k with my database or Dennis's test >database. I just used the command line tool, exactly as shown. Platform: MS Windows Vista Ultimate SP1. My previous test was on a small database and SQLite 3.6.0. The run below is with SQLite 3.6.1 and a bigger database: \research>copy \data\opt\fos\repo\fossil tmp \research>dir tmp\fossil 2008-08-25 15:37 4.583.424 fossil \research>%sqlite% "tmp/fossil" SQLite version 3.6.1 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> PRAGMA page_size; 1024 sqlite> select count(*) from blob; 3374 sqlite> PRAGMA page_size=8192; sqlite> VACUUM; sqlite> PRAGMA page_size; 8192 sqlite> select count(*) from blob; 3374 sqlite> .q \research>%sqlite% "tmp/fossil" SQLite version 3.6.1 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> PRAGMA page_size; 8192 sqlite> select count(*) from blob; 3374 sqlite> .q \research>dir tmp\fossil 2008-08-25 23:43 4.775.936 fossil So, it works. Perhaps there is an esential difference between your test program and the command line tool? -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_stmt declaration
Brown, Daniel <[EMAIL PROTECTED]> wrote: > I'm attempting to wrap SQLite with Managed C++ and I'm getting some > compiler warnings as the compiler/linker is have trouble finding the > declaration of the structure 'sqlite3_stmt', I've tried looking for it > manually but I can't find it either It's not declared anywhere intended to be used in client programs. sqlite3_stmt* is an opaque handle, you are not supposed to care what's inside. You get it from one API function, and pass it along to others. In C#, I'd represent it as IntPtr. Not sure what the equivalent in C++/CLI is. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_stmt declaration
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Brown, Daniel wrote: > I'm attempting to wrap SQLite with Managed C++ and I'm getting some > compiler warnings as the compiler/linker is have trouble finding the > declaration of the structure 'sqlite3_stmt', I've tried looking for it > manually but I can't find it either all I can find is a typedef on line > 2569 of slite3.h. You code will only ever use a pointer to the structure and never any of the fields. Consequently there is no need to know what the structure is. If the compiler still gives you grief then change sqlite3_stmt pointers to be void pointers or make a dummy structure with no/one element. Actually including the real description will cause problems if the compiler takes note of the contents since the structure is allocated and managed by SQLite itself (completely opaque to you). SQLite can change the contents at any point between releases so binding deeply to it will cause problems as the DLL changes. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFIsyc+mOOfHg372QQRAv1wAKDBN2HcoU0s448CUe0/d3eGQnb4dwCg4w4K aA+sY8a40QrKaVNkXz3Ecbw= =eSBm -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_stmt declaration
On Mon, Aug 25, 2008 at 02:30:44PM -0700, Brown, Daniel wrote:
> I'm attempting to wrap SQLite with Managed C++ and I'm getting some
> compiler warnings as the compiler/linker is have trouble finding the
> declaration of the structure 'sqlite3_stmt', I've tried looking for it
> manually but I can't find it either all I can find is a typedef on line
> 2569 of slite3.h. This isn't enough to stop the CLR compiler form
> complaining, I had a similar warning with the structure 'sqlite3' but
> including sqlite3Int.h resolved that warning as the structure is defined
> there, however tracking down the header file that defines 'sqlite3_stmt'
> seems to be leading no where...
This:
typedef struct foo * foo_t;
without a corresponding definition of struct foo is perfectly valid in C.
So is:
struct foo;
typedef struct foo * foo_t;
It effectively declares a pointer type which is distinct from any other
pointer type while at the same time not exposing any details of struct
foo to applications. Heck, there need not even be a struct foo -- the
library could just cast foo_t values to something else.
This is a very common technique in C API design.
A wrapper tool that insists that you provide a definition for struct foo
is broken, but you can always get around it by adding a bogus
definition, like:
struct foo {
void*bar;
};
That should work because the wrapper shouldn't have to know any of the
private details of struct foo in this case.
Substitute 'sqlite3_stmt' for 'foo'.
Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_stmt declaration
Good afternoon list, I'm attempting to wrap SQLite with Managed C++ and I'm getting some compiler warnings as the compiler/linker is have trouble finding the declaration of the structure 'sqlite3_stmt', I've tried looking for it manually but I can't find it either all I can find is a typedef on line 2569 of slite3.h. This isn't enough to stop the CLR compiler form complaining, I had a similar warning with the structure 'sqlite3' but including sqlite3Int.h resolved that warning as the structure is defined there, however tracking down the header file that defines 'sqlite3_stmt' seems to be leading no where... Cheers, Daniel Brown | Software Engineer @ EA Canada "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] Reducing SQLite Memory footprint(!)
Brown, Daniel wrote: > Interesting, I just tried that in my test application and Dennis's and I > get access violations during the vacuum command execution when trying to > resize the pages from 1k to 4k with my database or Dennis's test > database. > Daniel, I have found that sqlite works correctly if your main database is a file, but crashes when you try to vacuum with a :memory: database as your main database. The vacuum command does not work on attached databases, so you must open the file to be vacuumed as your main database. This crash is a bug that should probably be reported at http://www.sqlite.org/cvstrac/captcha?nxp=/cvstrac/tktnew HTH Denis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] equality searches and range searches with encrypteddata
Derek Developer <[EMAIL PROTECTED]> wrote: > So if the data is indexed prior to the encryption step, does SQLite > manage that index internally as a Btree that s stored somewhere? Is > it part of the MASTER database? I'm not sure what you mean by "MASTER database" (as opposed to which other database?) SQLite stores everything in a single file - data, indexes and all. > Obviously there would be a security issue if the index is accessible > as per this: > http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx Unlike the situation here, you can't encrypt individual columns or tables with SQLite (at least not using any products I'm familiar with). You encrypt the whole file. > If I am storing CC# or SS#, the index would contain them yes? Well, if you created an index on those columns, then of course the index would contain values from them. -- With best wishes, Igor Tandetnik With sufficient thrust, pigs fly just fine. However, this is not necessarily a good idea. It is hard to be sure where they are going to land, and it could be dangerous sitting under them as they fly overhead. -- RFC 1925 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] equality searches and range searches with encrypteddata
Thanks Igor, So if the data is indexed prior to the encryption step, does SQLite manage that index internally as a Btree that s stored somewhere? Is it part of the MASTER database? Obviously there would be a security issue if the index is accessible as per this: http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx If I am storing CC# or SS#, the index would contain them yes? Igor Tandetnik <[EMAIL PROTECTED]> wrote: Derek Developer wrote: > Thank you for your replys, but I am still not sure I understand how a > Query is executed on (page) encrypted data wihout either indexing the > data prior to encrption, creating a secondary hash column of the data > or simply decrypting every page to get at the underlying data? The data _is_ indexed prior to encryption (assuming you did create an index on the appropriate column, of course), then the pages containing the index are themselves encrypted when written to the file (and decrypted when read back, of course). Encryption is built into the I/O. Whenever a page-worth of data is read from disk, it is decrypted right afterwards. When a page is written, it's encrypted right before. The I/O engine doesn't care what kind of data is on that page - it's only concerned about shuffling bytes in and out of storage. The database engine doesn't care whether the file is encrypted or not - it always gets plaintext pages from I/O subsystem. Igor Tandetnik ___ 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] equality searches and range searches with encrypteddata
Derek, I'm more "onlooker" than "expert" here, but I think the answers are: 1) Yes, when a full table scan is performed, the entire table data is decrypted. (If you're using a LIMIT clause without an ORDER BY, then perhaps only part is decrypted -- the point being that the same pages are read and decrypted as would be simply *read* with a non-encrypted database.) For SELECTS where only index pages must be read, then only index pages must be decrypted. 2) For most modern computers, the time to read a sector from disk is much longer than the time to decrypt it, so that even though more CPU time is required, it may not significantly slow data delivery in many cases. This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] equality searches and range searches with encrypteddata
Derek Developer <[EMAIL PROTECTED]> wrote: > Thank you for your replys, but I am still not sure I understand how a > Query is executed on (page) encrypted data wihout either indexing the > data prior to encrption, creating a secondary hash column of the data > or simply decrypting every page to get at the underlying data? The data _is_ indexed prior to encryption (assuming you did create an index on the appropriate column, of course), then the pages containing the index are themselves encrypted when written to the file (and decrypted when read back, of course). Encryption is built into the I/O. Whenever a page-worth of data is read from disk, it is decrypted right afterwards. When a page is written, it's encrypted right before. The I/O engine doesn't care what kind of data is on that page - it's only concerned about shuffling bytes in and out of storage. The database engine doesn't care whether the file is encrypted or not - it always gets plaintext pages from I/O subsystem. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] equality searches and range searches with encrypted data
Thank you for your replys, but I am still not sure I understand how a Query is executed on (page) encrypted data wihout either indexing the data prior to encrption, creating a secondary hash column of the data or simply decrypting every page to get at the underlying data? Would greatly appreciate a detailed explanation. Cory Nelson <[EMAIL PROTECTED]> wrote: On Sun, Aug 24, 2008 at 12:36 AM, Derek Developer wrote: > Well that implies that the "parsing" decrypts each page in the database for > each query. How else would it traverse a key that is encypted? > > Isn't that going to be very slow? > Are you thinking it needs to decrypt the entire database for each query? If so - that's not the case. XTS (or some method like it) is used, where each page can be decrypted by itself so you end up with the exact same amount of I/O as a non-encrypted DB. > > Cory Nelson wrote: On Sun, Aug 24, 2008 at 12:07 AM, Derek Developer > wrote: >> I am still not clear if page level encryption permits equality searches and >> range searches? >> Intuitively it would seem that these searches would require every page to be >> decrypted to access the column data for each record... >> > > no functionality is lost. pages already need to be parsed - > encryption can just be thought of as another phase of this parsing. > -- 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] Reducing SQLite Memory footprint(!)
Interesting, I just tried that in my test application and Dennis's and I
get access violations during the vacuum command execution when trying to
resize the pages from 1k to 4k with my database or Dennis's test
database.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Kees Nuyt
Sent: Saturday, August 23, 2008 8:51 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Reducing SQLite Memory footprint(!)
On Thu, 21 Aug 2008 16:29:10 -0700, you wrote:
>How do I rebuild a database file for another page size
>or did the pragma do that already?
Use PRAGMA page_size={your_new_pagesize} immediately before
a vacuum. It will change the page size of the vacuumed
database. See:
http://www.sqlite.org/pragma.html#pragma_page_size
"As of version 3.5.8, if the page_size pragma is used
to specify a new page size just prior to running the
VACUUM command then VACUUM will change the page size
to the new value."
Demo:
sqlite_version():3.6.0
--
-- new database
PRAGMA page_size=8192;
BEGIN;
CREATE TABLE test (
x integer primary key,
y text
);
INSERT INTO test (y) VALUES ('row1');
INSERT INTO test (y) VALUES ('row2');
COMMIT;
PRAGMA page_size;
8192
PRAGMA schema_version;
1
PRAGMA page_size=1024;
VACUUM;
PRAGMA schema_version;
2
PRAGMA page_size;
1024
--
( Kees Nuyt
)
c[_]
___
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] unsubscribe
unsubscribe ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inserting using random rowids
On Sun, Aug 24, 2008 at 6:18 PM, Susan Ottwell <[EMAIL PROTECTED]>wrote: > How would one insert rows randomly within a range of 1000 rows? I can > easily enough do this in the script that calls the insert function, > but it would be more efficient if I could use an sqlite function or > feature to do this. > > sottwell There are a few ways to accomplish this, based on your particular definition of "random": 1. Unpredictable. This is where things get cryptographic. 2. Evenly distributed. 3. "Not obviously sequential". i.e. you don't really need unpredictable or even distribution, but you don't want your row IDs to go (1,2,3,4...) Anything that satisfies #2 will satisfy #3; anything that satisfies #1 will satisfy #2 (and by induction, #3). #1 is technically impossible, although it's possible to get fairly close. If you're on a *nix box, read 2-4 bytes out of /dev/random and treat them as an integer. #2 can be provided by a Mersenne Twister; most standard libraries' rand() functions are implemented using one. These two, while providing some semblance of randomness, have the disadvantage that you need to pick a number, then check to see if that number has already been used by another row. Thus I present a third option: #3 can be achieved through an LFSR (Linear Feedback Shift Register). A maximal n-bit LFSR will go through every numbers from 1 to (2**n)-1 without repeating, but do it in a seemingly random order. It's great if you want to e.g. generate what *looks* like a list of account numbers for a mock-up report/screenshot. Now, with that said, random rowids means poor locality of reference for newly inserted rows. This means cache misses and reduced performance. Why, exactly, do you want to randomize the rowid? -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inserting using random rowids
Susan Ottwell wrote: > How would one insert rows randomly within a range of 1000 rows? I can > easily enough do this in the script that calls the insert function, > but it would be more efficient if I could use an sqlite function or > feature to do this. > I'm not sure if this is what you are asking or not, but you can use the random function to generate the rowid for a table. The modulo operator can be used to restrict the range of values produced. create table t(id integer primary key, data text); insert into t values(random() % 1000, 'some data'); This will insert a row with a rowid somewhere between 0 and 999. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] PRAGMAs to modify library operations
Hi, Can some one confirm the following for me? I checked the documentation, but still want to confirm once again. The cache size and page size can be configured per database connection. Setting the cache/page size via PRAGMA in one connection will not affect the sqlite library affecting other database connections (existing or new). Thanks in advance. - Gautam. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Correct SQL name resolution on AS clauses in a SELECT?
>>Add FROM T1 for last tests. Personal Oracle Database 10g Release 10.2.0.3.0 CREATE temp TABLE t1(a INTEGER, b INTEGER); INSERT INTO t1 VALUES(1,2); INSERT INTO t1 VALUES(9,8); SELECT a AS b, b AS a FROM t1 ORDER BY a; B A -- -- 1 2 9 8 2 rows selected SELECT b AS a, a AS b FROM t1 ORDER BY a; A B -- -- 2 1 8 9 2 rows selected SELECT a, b AS a FROM t1 ORDER BY a; SQL Error: ORA-00960: ambiguous column naming in select list 00960. 0 - "ambiguous column naming in select list" *Cause:A column name in the order-by list matches more than one select list columns. SELECT a AS b, b AS a FROM t1 WHERE a=1; B A -- -- 1 2 1 rows selected SELECT a AS b, b AS a from t1 WHERE a=2; B A -- -- 0 rows selected SELECT a AS x, b AS x from t1 WHERE x=1 SQL Error: ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_X' >>IRELEVANT SELECT a AS Par, b AS Par from t1 WHERE Par=1; SQL Error: ORA-00904: "PAR": invalid identifier ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
