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 <gaddamsairam at gmail.com> 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
