[sqlite] How to read data from WAL?

[Donald Griggs]

Sairam,

Just in case it's useful and you've not see it, Simon Slavin pointed out
this "rqlite" project for replicated sqlite in an email from 25 Feb.



Donald

Simon Slavin slavins at bigfraud.org via

mailinglists.sqlite.org
Feb 25 (11 days ago)
to SQLite
This looks interesting.





It allows you to set up multiple computers on the internet to keep copies
of a database in synch.  Changes in one are promulgated to them all.  As a
side effect it proves a TCP server for SQL commands.

Haven't had time to evaluate it, but there are people here who can do that
far better than I can.

Simon.

On Mon, Mar 7, 2016 at 7:36 AM, Simon Slavin  wrote:

>
> On 7 Mar 2016, at 12:24pm, Sairam Gaddam  wrote:
>
> > I want to make note of those changes and replicate in another DB.
>
> Okay.
>
> Reading changes from the WAL file is perhaps a poor way to do this.
> First, not all SQLite databases have a WAL file.  Second, SQLite can put
> changes in the WAL file and then immediately process them and overwrite the
> contents of the file.  Third, because you are modifying database files
> outside of sqlite you stand the chance of corrupting those files.  Fourth,
> you would need to have a constantly-running process to see what is going on
> and this wastes a lot of CPU and power.
>
> The standards way to ensure database replication is to do the following.
> Either
> A) Always make changes via your own library routine which logs them OR
> B) Rewrite sqlite3_exec() or sqlite3_prepare() to log changes as well as
> do their normal job.
>
> To log a change, create a new SQL table called 'change_log' and add to it
> all commands which start with INSERT, UPDATE or DROP.
>
> Then your routine simply reads that table, executes those commands on
> another database file (you can use ATTACH or send the changes as a text
> file to another computer) then does "DELETE FROM change_log".
>
> If your database files are small and you do not make changes frequently
> then you could instead use the SQLite Backup API:
>
> 
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] How to read data from WAL?

[Sairam Gaddam]

> I have done some manual WAL decoding for my forensic software that can
> identifiy a previous DB state - its fun :)...
>
> (From memory)
>
> To determine which pages belong to the last transaction, you need to :
>
> Read the WAL header to obtain the current salt, then read each wal
> frame to determine which frames belong to the current transaction and
> then you can get the page number from the frame.
>
> To determine which table/index the page belongs to you will need to
> check each b-tree (making sure that you take account of pages that are
> in the current WAL checkpoint - i.e. exist in the WAL prior to the
> page you are looking for).
>
> You will then need to manually decode each page you have identified in
> the WAL to get the rows within the page and then you need to determine
> what rows have been added/deleted or altered.
>
> The issues here are that a small change to a table (one row
> added/edited) can result in many changes to the pages for that table,
> e.g. if a string is appended to then the row may no longer fit in the
> page that it currently occupies so the row will be moved. Depending on
> the key structure for the table this may also result in other rows
> being moved to maintain the structure of the B-Tree. So if you read a
> page from the WAL and then find the* previous version of that page
> (which may be earlier in the WAL or in the DB) and a row is not
> present you will need to parse all the changed pages in that tree to
> determine whether it has been deleted or just moved.
>
> (* I say THE previous page because you can and very often do have
> multiple copies of a page in the WAL.)
>
> All of the information you need is in the DB file format at the link
> provided earlier. It is however not as simple as just reading the
> format spec though, it took me a lot of experimentation (including
> writing some DB/WAL page level visualisation tools) to work out what
> is actually going on.


Thanks for the info. I think this is what I need. Can you comment on time
complexity in this method?

[sqlite] How to read data from WAL?

[Sairam Gaddam]

>
> Sounds to me you want to implement logical decoding, that is to
> extract an SQL form from the binary WAL format.
> I don't believe SQLite provides such a feature, that is something
> other databases use for bidirectional replication.


I just want to know which tables and rows are changed from WAL, not
necessarily forming a SQL query. If that feature is not present in SQLite.
Can you suggest me a good way to achieve that goal?

Wal shipping is a good way if you don't need to get bidirectional.


Do you mean copying WAL file and placing someplace else when you say WAL
shipping? or something else?

[sqlite] How to read data from WAL?

[Sairam Gaddam]

>
> To achieve what goal?
>
> In any case, perhaps https://www.sqlite.org/rbu.html is of interest to
> you.
>

I want to make note of those changes and replicate in another DB. And I
found that WAL is the correct place where changes to the database are
present. So my main aim is to get those changes and replicate after further
processing.

[sqlite] How to read data from WAL?

[Sairam Gaddam]

>
> If you're really sure that the database files are identical, the easiest
> way to do the replication would be to copy the WAL file.
>
> What processing do you want to do?
>

I want to take note of changes and depending on data, I might replicate in
some other DB. The database files are not necessarily identical. So mere
copying wont help me. I should get the data from WAL and based on which
table(can I get these info from WAL?) the data was changed, I should act
accordingly. So the only way is to read page and extract information from
it or any other way??

> Is there any other better way other than WAL?
>
> Record the SQL statements that are executed.


This is one another case, where some optimization could happen, like for
example if I run a update query and nothing gets updated in a particular
table which is similar in 2 different databases, then WAL doesn't have any
changes on that particular table and no need to execute the same query
again in different DB!
And I have some other use cases like those.
So I would like to see the changes from WAL and based on changes and the
query, I will proceed.
So i need a better way to read the contents from the WAL.
One of them is analyzing the pages from WAL, any others??. In PostgreSQL,
for WAL, they have a module which will give summary of which row in a
particular table was changed by reading the contents from WAL. Any module
like that?? because my work will be easy if I get the info of changes that
way!!

[sqlite] How to read data from WAL?

[Simon Slavin]

On 7 Mar 2016, at 12:24pm, Sairam Gaddam  wrote:

> I want to make note of those changes and replicate in another DB.

Okay.

Reading changes from the WAL file is perhaps a poor way to do this.  First, not 
all SQLite databases have a WAL file.  Second, SQLite can put changes in the 
WAL file and then immediately process them and overwrite the contents of the 
file.  Third, because you are modifying database files outside of sqlite you 
stand the chance of corrupting those files.  Fourth, you would need to have a 
constantly-running process to see what is going on and this wastes a lot of CPU 
and power.

The standards way to ensure database replication is to do the following.  Either
A) Always make changes via your own library routine which logs them OR
B) Rewrite sqlite3_exec() or sqlite3_prepare() to log changes as well as do 
their normal job.

To log a change, create a new SQL table called 'change_log' and add to it all 
commands which start with INSERT, UPDATE or DROP.

Then your routine simply reads that table, executes those commands on another 
database file (you can use ATTACH or send the changes as a text file to another 
computer) then does "DELETE FROM change_log".

If your database files are small and you do not make changes frequently then 
you could instead use the SQLite Backup API:



Simon.

[sqlite] How to read data from WAL?

[Sairam Gaddam]

>
> This requires detailed knowledge of how SQLite works.  If the file format
> page I referred you to does not help you may have to study the SQLite
> source code to find out what you need.
>

Yeah I read the source file wal.c and based on what I read, SQLite syncs
the frames which have page data in WAL with the main database to update it.
But I couldn't find a way to get those changes because they deal with
frames and pages. So if I get those WAL frames where page data is stored
and read them will I be able to get the changes which are made?

And the decryption here I mean is to get information from those pages(like
on which table, which column a change is made etc.,) which involves reading
the page to get the changes which are presently made.



> What are you trying to do ?  Are you trying to monitor a program which is
> running  or recover a corrupt database with some data in the WAL file ?
> There are good ways to do these things but they do not involve reading raw
> data from the WAL file.
>

I want to make note of those changes and replicate someplace else. And I
found that WAL is the correct place where changes to the database are
present. So my main aim is to get those changes and replicate after further
processing.
Is there any other better way other than WAL?

[sqlite] How to read data from WAL?

[Sairam Gaddam]

My main aim is to find the fresh changes which are to be made to database
through WAL module.


How is data stored in WAL?
If the data is stored in pages inside WAL then how to see the changes which
are presently made to the database?
Should I decrypt the information from WAL to know the changes which are to
be made to the database or any other way?
I found some decryption tools which are available for WAL. Will they give
changes to be made?
Or is there any module readily present inside SQLite which will decrpt the
information from WAL?

Any information on WAL decryption would be appreciated.
Thank you.

[sqlite] How to read data from WAL?

[Dominique Devienne]

On Mon, Mar 7, 2016 at 6:52 AM, Sairam Gaddam 
wrote:

> My main aim is to find the fresh changes which are to be made to database
> through WAL module.
>

To achieve what goal?

In any case, perhaps https://www.sqlite.org/rbu.html is of interest to you.
--DD

[sqlite] How to read data from WAL?

[Luca Ferrari]

On Mon, Mar 7, 2016 at 7:55 AM, Sairam Gaddam  wrote:
> And the decryption here I mean is to get information from those pages(like
> on which table, which column a change is made etc.,) which involves reading
> the page to get the changes which are presently made.

Sounds to me you want to implement logical decoding, that is to
extract an SQL form from the binary WAL format.
I don't believe SQLite provides such a feature, that is something
other databases use for bidirectional replication.

> I want to make note of those changes and replicate someplace else. And I
> found that WAL is the correct place where changes to the database are
> present. So my main aim is to get those changes and replicate after further
> processing.
> Is there any other better way other than WAL?

Wal shipping is a good way if you don't need to get bidirectional.

Luca

[sqlite] How to read data from WAL?

[Paul Sanderson]

I have done some manual WAL decoding for my forensic software that can
identifiy a previous DB state - its fun :)...

(From memory)

To determine which pages belong to the last transaction, you need to :

Read the WAL header to obtain the current salt, then read each wal
frame to determine which frames belong to the current transaction and
then you can get the page number from the frame.

To determine which table/index the page belongs to you will need to
check each b-tree (making sure that you take account of pages that are
in the current WAL checkpoint - i.e. exist in the WAL prior to the
page you are looking for).

You will then need to manually decode each page you have identified in
the WAL to get the rows within the page and then you need to determine
what rows have been added/deleted or altered.

The issues here are that a small change to a table (one row
added/edited) can result in many changes to the pages for that table,
e.g. if a string is appended to then the row may no longer fit in the
page that it currently occupies so the row will be moved. Depending on
the key structure for the table this may also result in other rows
being moved to maintain the structure of the B-Tree. So if you read a
page from the WAL and then find the* previous version of that page
(which may be earlier in the WAL or in the DB) and a row is not
present you will need to parse all the changed pages in that tree to
determine whether it has been deleted or just moved.

(* I say THE previous page because you can and very often do have
multiple copies of a page in the WAL.)

All of the information you need is in the DB file format at the link
provided earlier. It is however not as simple as just reading the
format spec though, it took me a lot of experimentation (including
writing some DB/WAL page level visualisation tools) to work out what
is actually going on.





Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 7 March 2016 at 09:49, Sairam Gaddam  wrote:
>>
>> If you're really sure that the database files are identical, the easiest
>> way to do the replication would be to copy the WAL file.
>>
>> What processing do you want to do?
>>
>
> I want to take note of changes and depending on data, I might replicate in
> some other DB. The database files are not necessarily identical. So mere
> copying wont help me. I should get the data from WAL and based on which
> table(can I get these info from WAL?) the data was changed, I should act
> accordingly. So the only way is to read page and extract information from
> it or any other way??
>
>> Is there any other better way other than WAL?
>>
>> Record the SQL statements that are executed.
>
>
> This is one another case, where some optimization could happen, like for
> example if I run a update query and nothing gets updated in a particular
> table which is similar in 2 different databases, then WAL doesn't have any
> changes on that particular table and no need to execute the same query
> again in different DB!
> And I have some other use cases like those.
> So I would like to see the changes from WAL and based on changes and the
> query, I will proceed.
> So i need a better way to read the contents from the WAL.
> One of them is analyzing the pages from WAL, any others??. In PostgreSQL,
> for WAL, they have a module which will give summary of which row in a
> particular table was changed by reading the contents from WAL. Any module
> like that?? because my work will be easy if I get the info of changes that
> way!!
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to read data from WAL?

[Clemens Ladisch]

Sairam Gaddam wrote:
> I want to make note of those changes and replicate someplace else. And I
> found that WAL is the correct place where changes to the database are
> present. So my main aim is to get those changes and replicate after further
> processing.

If you're really sure that the database files are identical, the easiest
way to do the replication would be to copy the WAL file.

What processing do you want to do?

> Is there any other better way other than WAL?

Record the SQL statements that are executed.


Regards,
Clemens

[sqlite] How to read data from WAL?

[Simon Slavin]

On 7 Mar 2016, at 5:52am, Sairam Gaddam  wrote:

> How is data stored in WAL?

You can learn about the data format for the WAL file in section 4.0 of this 
page:



> If the data is stored in pages inside WAL then how to see the changes which
> are presently made to the database?

This requires detailed knowledge of how SQLite works.  If the file format page 
I referred you to does not help you may have to study the SQLite source code to 
find out what you need.

What are you trying to do ?  Are you trying to monitor a program which is 
running  or recover a corrupt database with some data in the WAL file ?  There 
are good ways to do these things but they do not involve reading raw data from 
the WAL file.

> Any information on WAL decryption would be appreciated.

The WAL file is not encrypted unless the database file is encrypted, which is 
unusual.

Simon.