[sqlite] How to run sqlite just in memory - pragma cache_size issue

[Kf Lee]

Hi, Richard,

Thanks for the advice, I have look at the link and experiment with it.
The example loadOrSaveDb is
pretty clear and straightforward.  However I did not get it working
with the following example:

 sqlite3 *db;
 char *zErrMsg = 0;

 void main(void) {
 int rc;
 char oph_sql_db[32], oph_sql_bk[32],  s[10];

 strcpy(oph_sql_db, "oph.bk3");
 strcpy(oph_sql_bk, "oph.sav");

 rc = sqlite3_open(":memory:", );
 if( rc!=SQLITE_OK )
  fprintf(stderr, "ER! - Open in Memory DB : %s\n", zErrMsg);
 else printf("\nOpen Memory DB ok\n");

 rc=loadOrSaveDb(, oph_sql_db, 0);
 if( rc!=SQLITE_OK )
  fprintf(stderr, "ER! - Load DB from disk to Memory : %s\n", zErrMsg);
 else printf("\nLoad DB ok\n");

 printf("\nHit any key + return to save back to hard disk\n");
 scanf("%s", s);

 rc=loadOrSaveDb(, oph_sql_bk, 1);
 if( rc!=SQLITE_OK )
  fprintf(stderr, "ER - Save in Memory to disk : %s\n", zErrMsg);
 else printf("\nSave Memory ok\n");

 }


it reports that the "Open Memory DB OK" and the other two statements
return with NOT SQLITE_OK.  Further more, if I open the DB normally
and just call to
save a backup copy, it actually save the db onto hard disk with file
of identical size
but it also return NOT SQLITE_OK.

I am using sqlite 3.7.9   on Linuxmint, as I can not see any obvious
reason for reporting
error I am wondering could it be an issue of sqlite version?

Please advice, thanks.

kfl (Hong Kong).


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

Re: [sqlite] Tool for extracting deleted data from unvacuumed SQLite files

[Paul L Daniels]

> If Undark decodes the metadata, it might be more useful to create a
> new database instead of a CSV file.  The new database would be a
> mirror of the input, except that its tables would hold deleted rows
> (or both deleted and extant, with a column signifying which).  

I may look in to adding such a feature in the future, though it'd
require a few more layers of funtionality within Undark to be able to
facilitate it.

It should be cautioned that due to the statistical probabilities dumped
data should always still be vetted by a human for now to weed out false
rows that arise due to the limited size of the probability space
( the fewer columns in a table the higher the chance of a false hit ).

Regards,
Paul




-- 
Computer Repairs for Charters towers - http://ctpc.biz
A.B.N. 19 500 721 806
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tool for extracting deleted data from unvacuumed SQLite files

[James K. Lowden]

On Sun, 6 Oct 2013 22:58:28 +1000
Paul L Daniels  wrote:

>   "Undark" is only at v0.2 at this time, it's still quite coarse
>   around some corners and it does not differentiate between
>   deleted and undeleted rows.  It also has a limitation of only
>   decoding what it finds within the SQLite page (ie, 4K in most
>   cases), if the row extends beyond the page the data won't be
>   retrieved ( I am working on this for v0.3 ), finally, it
>   doesn't decode or dump BLOB data yet, simply not sure how to
>   represent that sanely on a CSV dump.

If Undark decodes the metadata, it might be more useful to create a new
database instead of a CSV file.  The new database would be a mirror of
the input, except that its tables would hold deleted rows (or both
deleted and extant, with a column signifying which).  

That way no information is lost, re-insertion is simple, and
representation questions are mooted.  

--jkl


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

Re: [sqlite] SQLite4 release date and how to compile on windows platform

[Gabriel Corneanu]

Simon,
I have asked several times similar questions, and got similar replies.
This answer is simply NOT correct.
While I understand nobody wants to commit to a release/stable version, I 
really wanted to evaluate how it fits my needs.

Therefore I took some time to understand how could I compile it.
I found the unpleasant truth: at this time (ok, a few weeks ago) Windows 
is not supported at all.
The file lsm_unix.c is not ported to Windows (contains low level file 
access, shared memory, memory mapped files, etc).


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

Re: [sqlite] Tool for extracting deleted data from unvacuumed SQLite files

[Paul L Daniels]

On Sun, 6 Oct 2013 15:07:06 +0100
Simon Slavin  wrote:

> Quick summary, most of which you probably do right:
> 
> Numeric fields should go unquoted and string fields in a CSV files
> must be quoted:

Thanks for the notes, there's a few things in there I'm not
doing right, so I do sincerely appreciate the input and
that'll be folded in to the next release ( fairly quick cycle
at the moment as the bugs/features are tacked on ).

The original purpose of the whole Undark to retrieve a client's
SMSs was satisfied simply from a visual dump ( as opposed to
interpreted ) and as such I didn't put a lot of fine tuning in
to the CSV format. I greatly appreciate the tips
there, thank you.

The blob details were something I wasn't having any real
success in finding during my short searches around the net, so
again, another very good bit of info.

After my original post, I realised that Undark could also be
used to pull data from corrupted db's ( missing tables or
various B-tree indexes ), so I'll probably need some willing
victims to test that later.

Regards,
Paul.


-- 
Computer Repairs for Charters towers - http://ctpc.biz
A.B.N. 19 500 721 806
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use my own python function in a SQLite WHERE clause?

[Ryan Johnson]

On 05/10/2013 6:01 AM, Clemens Ladisch wrote:

Bao Niu wrote:

SELECT * FROM myTable WHERE MyDate > MyModule.ChineseDate("兔年八月十五")

You cannot use Python function directly in SQL.
... but you can register it with sqlite3 easily enough and use it from 
SQL afterward:

http://docs.python.org/2/library/sqlite3.html#sqlite3.Connection.create_function

Ryan

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

Re: [sqlite] Tool for extracting deleted data from unvacuumed SQLite files

[Simon Slavin]

On 6 Oct 2013, at 1:58pm, Paul L Daniels  wrote:

> finally, it
>   doesn't decode or dump BLOB data yet, simply not sure how to
>   represent that sanely on a CSV dump.

Quick summary, most of which you probably do right:

Numeric fields should go unquoted and string fields in a CSV files must be 
quoted:

384.2, "Frank", 23, "",, "2"

Fields may start and/or end in whitespace which must be ignored.
If the first non-whitespace character is a quote, you have a text field.
The fourth field contains a zero-length string which is not the same as NULL.
The fifth field contains a NULL, or any other convention which means 'value 
missing'.
A NULL must not be interpreted as the numeric value zero or a zero-length 
string.
That last field is a one-character string, not the number 2.

If a double-quote appears in a text field it must be doubled:

"Here is a quote character:"".", 384.2, "Frank", 23, "",, "2"

Tabs and return characters in a text field can be used as is.  Yes, the result 
looks messy to the naked eye, but the parser is looking for a double-quote 
character and doesn't care.

BLOBs
-
The convention for BLOB fields is to dump them in hex surrounded like this:

X'deadbeef'

(you can use upper- or lower-case x).  So inside a CSV one might look like this:

x'462F82A4D7', "Here is a quote character:"".", 384.2, "Frank", 23, "",, "2"

If the first non-whitespace character is 'x' or 'X', you have a BLOB.  Hex 
letters can be upper- or lower-case.



Simon.

PS: "Be conservative in what you generate and liberal in what you accept." -- 
Jon Postel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Tool for extracting deleted data from unvacuumed SQLite files

[Paul L Daniels]

Hello everyone,

Sincerely hoping this is the appropriate place to post this
announcement.

A while ago a client needed some important SMSs retrieved from
their iPhone that had been deleted, anyhow, long story short,
I've since created a tool which I now call "Undark" to pull all
the data it can find in a SQLite3 file and dump it to stdout in
a CSV type format.

I looked at several forensics & sleuthing websites during the
writing process and at this point I couldn't find anything else
to do the job.  Initially I wrote it so that it was
specifically structured to look for iPhone iOS6 SMSs, but by
the time the next revision came around I had made it work with
all data.

I'm offering "Undark" to the community under a BSD Revised
licence.

"Undark" is only at v0.2 at this time, it's still quite coarse
around some corners and it does not differentiate between
deleted and undeleted rows.  It also has a limitation of only
decoding what it finds within the SQLite page (ie, 4K in most
cases), if the row extends beyond the page the data won't be
retrieved ( I am working on this for v0.3 ), finally, it
doesn't decode or dump BLOB data yet, simply not sure how to
represent that sanely on a CSV dump.

Obviously if the DB has been vacuumed then there's simply no
chance to get it back; but fortunately I've found that a lot of
them aren't vacuumed.

Available at - http://pldaniels.com/undark


-- 
Computer Repairs for Charters towers - http://ctpc.biz
A.B.N. 19 500 721 806
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users