Re: [sqlite] BUG

[Igor Tandetnik]

Alexey Pechnikov  wrote:
> I did get this frustrate behaviour in my prototype of the versioning
> datastore where all versions of records are stored permanently. In my
> example the foreign identifiers are stored in the user table and all rows
> versions are stored in the user_record table. For visualization we need to
> get only last versions of records

But view_user statement makes no attempt to select the last version. It picks 
some arbitrary random version. You might want to consider something like this:

CREATE VIEW view_user AS
SELECT * FROM user_record
WHERE record_id in
(select max(record_id) from user_record group by user_id)
ORDER BY name ASC;

-- or

CREATE VIEW view_user AS
SELECT * FROM user_record r1
WHERE r1.record_id =
(select max(record_id) from user_record r2 where r1.user_id = r2.user_id)
ORDER BY name ASC;

-- 
Igor Tandetnik


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

Re: [sqlite] BUG

[Alexey Pechnikov]

I did get this frustrate behaviour in my prototype of the versioning
datastore where all versions of records are stored permanently. In my
example the foreign identifiers are stored in the user table and all rows
versions are stored in the user_record table. For visualization we need to
get only last versions of records and so"group by timestamp" is needed in
view (in the example above I did replace "timestamp" to "name" field). But
some search operations can use all versions of records. My tests on the
datastore are successfully tested with previous SQLite builds but returns
strange results now and I simplificate "wrong" tests to this example. I
think it may be not so trivial to find this "not bug" in production code...
Of cource in development stage is not very difficult to rewrite such
queries.

2010/10/26 Black, Michael (IS) 

> And...if you drop the "group by" from the view it's correct again
>
> sqlite> drop view view_user;
> sqlite> CREATE VIEW view_user AS
>...> SELECT user.id,user_record.*
>   ...> FROM user, user_record
>   ...> WHERE user.id=user_record.user_id
>...> ;
> sqlite> select * from test;
> 11|76|8|11|A
> 4|86|11|4|B
> 4|87|3|4|B
> sqlite> select count(*) from test;
> 3
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Alexey Pechnikov
> Sent: Mon 10/25/2010 3:06 PM
> To: General Discussion of SQLite Database
> Subject: EXTERNAL:Re: [sqlite] BUG
>
>
>
> > The result of the view above is undefined.  It will choose one of the
> > user_record rows for each distinct user.id, but you don't know which
> row.
>
> Yes! But it choose only single user_record row for two distinct user_id in
> count(*) expression.
>
> Are you really think that count(*)=1 for _two_ rows is not the bug?
>
> sqlite> select * from test;
> 4|87|3|4|B
> 11|76|8|11|A
> sqlite> select count(*) from test;
> 1
> 
> CREATE TABLE user
> (
>  id INTEGER PRIMARY KEY
> );
> INSERT INTO "user" VALUES(4);
> INSERT INTO "user" VALUES(11);
>
> CREATE TABLE user_record
> (
>  record_id INTEGER PRIMARY KEY,
>  record_version INTEGER,
>  user_id INTEGER NOT NULL,
>  name TEXT
> );
> INSERT INTO "user_record" VALUES(76,8,11,'A');
> INSERT INTO "user_record" VALUES(86,11,4,'B');
> INSERT INTO "user_record" VALUES(87,3,4,'B');
>
> CREATE VIEW view_user AS
> SELECT user.id,user_record.*
> FROM user, user_record
> WHERE user.id=user_record.user_id
> GROUP BY user.id;
>
> create temp view test as select * from main.view_user where record_id in
> (select record_id from main.view_user where name like '%');
>
> select * from test;
> select count(*) from test;
> 
>
> --
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> 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
>
>


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

Re: [sqlite] BUG

[Black, Michael (IS)]

And...if you drop the "group by" from the view it's correct again
 
sqlite> drop view view_user;
sqlite> CREATE VIEW view_user AS
   ...> SELECT user.id,user_record.*
   ...> FROM user, user_record
   ...> WHERE user.id=user_record.user_id
   ...> ;
sqlite> select * from test;
11|76|8|11|A
4|86|11|4|B
4|87|3|4|B
sqlite> select count(*) from test;
3
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Alexey Pechnikov
Sent: Mon 10/25/2010 3:06 PM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] BUG



> The result of the view above is undefined.  It will choose one of the
> user_record rows for each distinct user.id, but you don't know which row.

Yes! But it choose only single user_record row for two distinct user_id in
count(*) expression.

Are you really think that count(*)=1 for _two_ rows is not the bug?

sqlite> select * from test;
4|87|3|4|B
11|76|8|11|A
sqlite> select count(*) from test;
1

CREATE TABLE user
(
  id INTEGER PRIMARY KEY
);
INSERT INTO "user" VALUES(4);
INSERT INTO "user" VALUES(11);

CREATE TABLE user_record
(
  record_id INTEGER PRIMARY KEY,
  record_version INTEGER,
  user_id INTEGER NOT NULL,
  name TEXT
);
INSERT INTO "user_record" VALUES(76,8,11,'A');
INSERT INTO "user_record" VALUES(86,11,4,'B');
INSERT INTO "user_record" VALUES(87,3,4,'B');

CREATE VIEW view_user AS
SELECT user.id,user_record.*
FROM user, user_record
WHERE user.id=user_record.user_id
GROUP BY user.id;

create temp view test as select * from main.view_user where record_id in
(select record_id from main.view_user where name like '%');

select * from test;
select count(*) from test;


--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
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] BUG

[Black, Michael (IS)]

To add on...it does NOT work under 3.7.3...so perhaps that's what you're using? 
 I agree this doesn't seem intuitive at all...
 
4|87|3|4|B
11|76|8|11|A
sqlite> select count(*) from test;
1
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 

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

Re: [sqlite] BUG

[Richard Hipp]

On Mon, Oct 25, 2010 at 4:06 PM, Alexey Pechnikov wrote:

> > The result of the view above is undefined.  It will choose one of the
> > user_record rows for each distinct user.id, but you don't know which
> row.
>
> Yes! But it choose only single user_record row for two distinct user_id in
> count(*) expression.
>
> Are you really think that count(*)=1 for _two_ rows is not the bug?
>

You miss my point.  "test" in this case doesn't have one row or two rows.
It has an arbitrary number of rows due to indeterminacy in your view.
Sometimes "test" will return one row.  Sometimes it will return two.  You
can never predict which.  Both are "correct" in the sense that both are
allowed interpretations of what SQLite ought to do.

The above will never happen for a simple table named "test".  It only
happens for things like:

 ... WHERE record_id IN (SELECT record_id FROM view_user WHERE name LIKE
'%');

where the record_id value returned from the view_user view is
indeterminate.  The WHERE clause above might be equivalent to

 ... WHERE record_id IN (76,86)

and in that case count(*) will return 2.  But the WHERE clause might also be
equivalent to

 ... WHERE record_id IN (76,87)

in which case count(*) will return 1.  SQLite is free to choose either
interpretation for the subquery in your WHERE clause, and hence might get
either 1 or 2 as the count(*) result.  Version 3.7.2 happened to get 2.
Version 3.7.3 happens to get 1.  Who knows what 3.7.4 will get - both
answers are correct


> sqlite> select * from test;
> 4|87|3|4|B
> 11|76|8|11|A
> sqlite> select count(*) from test;
> 1
> 
> CREATE TABLE user
> (
>  id INTEGER PRIMARY KEY
> );
> INSERT INTO "user" VALUES(4);
> INSERT INTO "user" VALUES(11);
>
> CREATE TABLE user_record
> (
>  record_id INTEGER PRIMARY KEY,
>  record_version INTEGER,
>  user_id INTEGER NOT NULL,
>  name TEXT
> );
> INSERT INTO "user_record" VALUES(76,8,11,'A');
> INSERT INTO "user_record" VALUES(86,11,4,'B');
> INSERT INTO "user_record" VALUES(87,3,4,'B');
>
> CREATE VIEW view_user AS
> SELECT user.id,user_record.*
> FROM user, user_record
> WHERE user.id=user_record.user_id
> GROUP BY user.id;
>
> create temp view test as select * from main.view_user where record_id in
> (select record_id from main.view_user where name like '%');
>
> select * from test;
> select count(*) from test;
> 
>
> --
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> 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] BUG

[Black, Michael (IS)]

You failed to say what version you are using.  Running your SQL on 3.7.2 works 
just fine.
 
SQLite version 3.7.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE user
   ...> (
   ...>   id INTEGER PRIMARY KEY
   ...> );
sqlite> INSERT INTO "user" VALUES(4);
sqlite> INSERT INTO "user" VALUES(11);
sqlite>
sqlite> CREATE TABLE user_record
   ...> (
   ...>   record_id INTEGER PRIMARY KEY,
   ...>   record_version INTEGER,
   ...>   user_id INTEGER NOT NULL,
   ...>   name TEXT
   ...> );
sqlite> INSERT INTO "user_record" VALUES(76,8,11,'A');
sqlite> INSERT INTO "user_record" VALUES(86,11,4,'B');
sqlite> INSERT INTO "user_record" VALUES(87,3,4,'B');
sqlite>
sqlite> CREATE VIEW view_user AS
   ...> SELECT user.id,user_record.*
   ...> FROM user, user_record
   ...> WHERE user.id=user_record.user_id
   ...> GROUP BY user.id;
sqlite>
sqlite> create temp view test as select * from main.view_user where record_id in
   ...> (select record_id from main.view_user where name like '%');
sqlite>
sqlite> select * from test;
4|86|11|4|B
11|76|8|11|A
sqlite> select count(*) from test;
2
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Alexey Pechnikov
Sent: Mon 10/25/2010 3:06 PM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] BUG



> The result of the view above is undefined.  It will choose one of the
> user_record rows for each distinct user.id, but you don't know which row.

Yes! But it choose only single user_record row for two distinct user_id in
count(*) expression.

Are you really think that count(*)=1 for _two_ rows is not the bug?

sqlite> select * from test;
4|87|3|4|B
11|76|8|11|A
sqlite> select count(*) from test;
1


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

Re: [sqlite] BUG

[Alexey Pechnikov]

> The result of the view above is undefined.  It will choose one of the
> user_record rows for each distinct user.id, but you don't know which row.

Yes! But it choose only single user_record row for two distinct user_id in
count(*) expression.

Are you really think that count(*)=1 for _two_ rows is not the bug?

sqlite> select * from test;
4|87|3|4|B
11|76|8|11|A
sqlite> select count(*) from test;
1

CREATE TABLE user
(
  id INTEGER PRIMARY KEY
);
INSERT INTO "user" VALUES(4);
INSERT INTO "user" VALUES(11);

CREATE TABLE user_record
(
  record_id INTEGER PRIMARY KEY,
  record_version INTEGER,
  user_id INTEGER NOT NULL,
  name TEXT
);
INSERT INTO "user_record" VALUES(76,8,11,'A');
INSERT INTO "user_record" VALUES(86,11,4,'B');
INSERT INTO "user_record" VALUES(87,3,4,'B');

CREATE VIEW view_user AS
SELECT user.id,user_record.*
FROM user, user_record
WHERE user.id=user_record.user_id
GROUP BY user.id;

create temp view test as select * from main.view_user where record_id in
(select record_id from main.view_user where name like '%');

select * from test;
select count(*) from test;


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

[sqlite] WAL file growth concern

[Bob Smith]

On Sat Oct 23 20:57:56 GMT 2010, H. Phil Duby wrote:

> Give the described conditions, I do not think you need to worry about the
> WAL file growing without bound.  I think that each increase should be
> smaller than the previous one, and will stop growing all together [for your
> stress test] when the WAL file has increased to the point that all of the
> [WAL] writes [during your forced lock conditions time frame] fit in the
> expanded file

After thinking about that and also after some testing, I concur. I ran
my read/write stress test again inserting 2 million rows over 9 hours
while doing my reads. The WAL file occasionally kept growing even in
the 8th hour, but the growth really slowed. It grew up through 12MB
during the first 2 hours or so and then only grew another 700K during
the next 2.5 hours and only grew another 400K over the next 4.5 hours
to a total of 13.1MB through the 9 hour test. Every once in a while a
read is going to take an extra long time from the norm or the writes
are going to go faster than the norm (this a somewhat controlled test,
but on a a general purpose machine - other processes do exist who get
time slices...) and the file grows a little bit every once in a while
after it initially settles down. I do believe on a longer run it would
pretty much stop growing all together. Very good.

The reason I looked into this in the first place and put together my
more controlled test is my colleague was doing some testing with my
full system and reported that his WAL file had grown to 306MB and his
system had become sluggish. After my initial results, I thought the
culprit causing the huge WAL file was the simultaneous reads and
writes However, it turns out this was not the culprit. We also
periodically delete the oldest X rows from the database in a purge. He
had deleted 750K rows in one shot. That is definitely what grew the
WAL file so large.

I have not yet determined why his system got sluggish afterwards. It
may not have anything to do with sqlite at all. So, I won't dwell on
it here. I will ask one question, however. I know that reads are
expected to slow down as the WAL file gets larger.  Is that just in
terms of real data/pages in the WAL file or does the actual size of
the file have an impact? For example, I have a 300MB WAL file due to a
previous operation. However, everything has been checkpointed, and we
are back to really only having ~1MB of actual data being written into
the WAL file and then checkpointed. The rest (vast majority) of the
file is not being used. Would we expect performance degradation here
as compared to a WAL file with a real physical size of ~1MB at this
point?

Considering things like bulk deletes (and updates) potentially really
growing a WAL file to be quite large and having a system that is
constantly running and inserting data into the database over a long
period of time with reads also coming in,  I wonder about the value of
adding an optional feature to sqlite. What if there was an API to to
specify a max desired limit to the size of the WAL file? Whenever a
checkpoint was 100% successful and it was determined that the entire
WAL has been transferred into the database and synced and  no readers
are making use of the WAL, then in addition to the writer rewinding
the WAL back the beginning, the WAL file was truncated IF this option
was configured and the size of the WAL file was greater than the
specified value? This seems like it would be simple to implement
without costing anything by default to those who don't configure it...
If I was to use such a feature and was to do the default 1000 page
checkpoint (which seems to correspond to a little over a 1MB WAL file
size), I would make the limit something like 50MB. Under normal
conditions, the limit would never be reached anyways. But, in the case
where a large WAL file did get created at some point, this would be
used to get it truncated. Thoughts?

Best Regards,

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

Re: [sqlite] [OS/2] Update OS/2 support in sqlite 3.7.x

[Walter Meinl]

Just in case this has slipped thru cause it took a while for me to
register, here is my reply again.

On 10/09/29 23:41, Walter Meinl wrote:
>>
>> If the underlying VFS does not support shared-memory (which the OS/2 VFS
>> does not) then SQLite simply will not go into WAL mode.  No patching is
>> needed for this.  Everything should work as delivered.
>>
>> What exactly is malfunctioning?  What is the problem that this patch
>> attempts to fix?
> The mozilla bug was originally filed against 3.7.1. In pager.c was a
> function: static int pagerPagecount in an #ifndef SQLITE_OMIT_WAL block.
> The last 3 hunks of the original patch moved the exclusion of omit_wal
> further down, because all sqlite databases in a new profile had zero
> size on OS/2 and a warning was issued from firefox that history and
> bookmarks won't work, because the data bases were in use by another program.
>>> https://bugzilla.mozilla.org/attachment.cgi?id=474575
> However, this issue has been fixed already in 3.7.2 [d1ed743b6e].
> (Hopefully mozilla will update to 3.7.2 soon).
> 
> The rest of the patch contained (in fossil format) in
 https://bugzilla.mozilla.org/attachment.cgi?id=477692
> is intended to explicitly disable WAL on OS/2
> and bring the OS/2 related files up to date.
> That involves:
> - adding a new function, os2CurrentTimeInt64(), and refining the
>   existing os2CurrentTime();
> - adding new members to the "os2Vfs" structure, and explicitly
>   defining them as NULL pointers since they support WAL;
> - updating the OS/2 semaphore documentation to match other platforms.
> It would be nice if these changes could be considered to get checked-in.
> Thanks, Walter
> 
> ___
> 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] BUG

[Richard Hipp]

On Mon, Oct 25, 2010 at 1:18 PM, Alexey Pechnikov wrote:

>
> CREATE VIEW view_user AS
> SELECT user.id,user_record.*
> FROM user, user_record
> WHERE user.id=user_record.user_id
> GROUP BY user.id
> ORDER BY name ASC;
>

The result of the view above is undefined.  It will choose one of the
user_record rows for each distinct user.id, but you don't know which row.
Your queries below return different results depending on which of the
user_record rows is choosen.  3.7.2 just happened to choose a different
result row from 3.7.3.  But that is not a bug.



>
> -- returns two rows
> select * from main.view_user where record_id in (select record_id from
> main.view_user where name like '%');
> -- but count(*) returns 1
> select count(*) from main.view_user where record_id in (select record_id
> from main.view_user where name like '%');
> -- equal query returns only single row!
> select * from main.view_user where record_id in (select record_id from
> main.view_user where name like '%') order by name;
>
> ---
>
> --
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> 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] WAL file growth concern

[Nicolas Williams]

On Fri, Oct 22, 2010 at 09:56:22PM -0400, Richard Hipp wrote:
> On many (most?) filesystems, it is faster to overwrite an existing area of a
> file than it is to extend the file by writing past the end.  That's why
> SQLite doesn't truncate the WAL file on each checkpoint - so that subsequent
> writes will be overwriting an existing file region and thus go faster.

I think that assumption is getting harder to make.  For one thing,
filesystems nowadays aggregate transactions into large writes, which
means that old blocks aren't overwritten, but replaced -- COW goes
hand-in-hand with such aggregation.

For ZFS your assumption is wrong because of ZFS' variable block size
support[*].  I don't know if there are filesystems other than ZFS where
file data block size varies.  But for filesystems that aggregate writes
I think you'd find that overwriting performs about as well as appending
(assuming there's no O_APPEND synchronization going on).

Does the WAL store modified pages?

Nico

[*] In ZFS files have a single data block until the file size exceeds
the host filesystem's "recordsize", from which point the file will
consist of two or more data blocks, all of that size.  Block sizes
are all the powers of two between nine and seventeen (512 bytes to
128KB).  Thus overwriting a 1KB SQLite3 page in the middle of the
file with 128KB recordsize will result in a read-modify-write of the
modified block.  Though likely the application will have already
caused that block to be in memory, in which case there's no RMW, but
the disparity between application "page size" and ZFS recordsize
obviously has a significant cost.  SQLite3 users should set the
SQLite3 page size and host ZFS dataset recordsize so they match.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] BUG

[Alexey Pechnikov]

By sql script below I get count(*)=1 for two rows in result set! I use
SQLite 3.7.3. In 3.7.0.1 this work correct.

---
CREATE TABLE user
(
  id INTEGER PRIMARY KEY
);
INSERT INTO "user" VALUES(4);
INSERT INTO "user" VALUES(11);

CREATE TABLE user_record
(
  record_id INTEGER PRIMARY KEY,
  record_version INTEGER,
  user_id INTEGER NOT NULL,
  name TEXT
);
INSERT INTO "user_record" VALUES(76,8,11,'A');
INSERT INTO "user_record" VALUES(86,11,4,'B');
INSERT INTO "user_record" VALUES(87,3,4,'B');

CREATE VIEW view_user AS
SELECT user.id,user_record.*
FROM user, user_record
WHERE user.id=user_record.user_id
GROUP BY user.id
ORDER BY name ASC;

-- returns two rows
select * from main.view_user where record_id in (select record_id from
main.view_user where name like '%');
-- but count(*) returns 1
select count(*) from main.view_user where record_id in (select record_id
from main.view_user where name like '%');
-- equal query returns only single row!
select * from main.view_user where record_id in (select record_id from
main.view_user where name like '%') order by name;

---

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

Re: [sqlite] Bundling sqlite database together with exe file.

[Black, Michael (IS)]

Depends what you mean by "noticeable"...but you can test it yourself.
 
Just .dump the database, restart with a new name, and .load it back again.  
Similar to what you should see if you do it yourself.
 
If you choose option 3 remember that users WILL see the database and be able to 
copy it quite easiliy.  So you need to elucidate the reasons why your client 
wants to keep it private.  If it's an absolute requirement that nobody copy it 
then option 3 doesn't work.  If it's just "we would like to make it a bit more 
difficult for the blithering idiot to copy it" then option 3 is OK.  Anybody 
who knows what they're doing could intercept it.  
 
The in-memory is a lot better.  Takes a much smarter person to glean any info 
from that.  But 200M could be a bit of a problem for some.
 
What you may really want is to encrypt the database.  That way it looks like 
gibberish to anybody who does find it and you can deploy it like you would 
normally.  Just don't directly store the password.  Do something simple like 
XOR it with another value or phrase in your code.  If you use just a single 
char for XOR almost nobody would recognize what's going on (though disassembly 
of code would work..but that's a much smarter crowd).
 
http://sqlcipher.net/
http://sqlite-crypt.com/documentation.htm
http://www.hwaci.com/sw/sqlite/see.html
 
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Mohd Radzi Ibrahim
Sent: Mon 10/25/2010 3:52 AM
To: sql...@surfulater.com; General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] Bundling sqlite database together with exe file.




On 25-Oct-2010, at 1:28 PM, Neville Franks wrote:

> I'd also suggest option 3. And make sure you extract the file to a
> folder that the user has permission to access.
>

Yes, that's possible. Will it be noticeably slow to write say 200MB db to 
another file? And also another reason, my client don't want anybody to copy the 
database, except together with the exe.




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

Re: [sqlite] Bundling sqlite database together with exe file.

[Mohd Radzi Ibrahim]

On 25-Oct-2010, at 1:28 PM, Neville Franks wrote:

> I'd also suggest option 3. And make sure you extract the file to a
> folder that the user has permission to access.
> 

Yes, that's possible. Will it be noticeably slow to write say 200MB db to 
another file? And also another reason, my client don't want anybody to copy the 
database, except together with the exe. 



> Monday, October 25, 2010, 1:17:13 AM, you wrote:
> 
> KN> On Sun, 24 Oct 2010 20:57:15 +0800, Mohd Radzi Ibrahim
> KN>  wrote:
> 
>>> Hi,
>>> I am planning to deploy my sqlite database together
>>> with the exe file. Is there a way to open the sqlite
>>> database with a file handle and starting offset of
>>> the file, as read-only?
>>> 
>>> Thank you for any suggestion.
> 
> KN> Not out of the box.
> KN> I can think of three solutions:
> 
> KN> 1- Difficult: change the sqlite3 library in such a way so it
> KN> uses the whole executable as the "first page" of the
> KN> database
> 
> KN> 2- Easier: Store the output of the .dump command of the
> KN> sqlite3 shell in a 'resource' in the executable and on
> KN> execution of your program read the resource and load it in
> KN> an in-memory database (filename ":memory:" ) by calling
> KN> sqlite3_exec() on each of its statements.
> 
> KN> 3- Easier: Store the database file as-is in a resource in
> KN> the executable and on execution of your program write it to
> KN> disk and open it in the normal way. 
> 
> KN> HTH
> 
> 
> -- 
> Best regards,
>  Neville Franks, http://www.surfulater.com http://blog.surfulater.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] SQLite type question

[Matthew Jones]

> Also even with a signed rowid and if you keep all rowids positive, you can
> still insert 1,000,000,000 rows per second continuously for 292 years before
> you run out.

Excellent. I think you should adopt that as a strapline Richard.

-- 
Matthew Jones
Hewlett-Packard Ltd
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users