Handle the data base of the History using SQL commands
Hi, guys.
Does anyone here understand a little about the Firefox database? I mean local bank, SQL-Lite's standard. I just want to merge the History of several different profiles into a single database. The "places.sqlite" file located at root folder of each profile should store this history. How to add all records into a single bank? If there are any duplicates, what is the SQL command to remove them?
I have also installed SQLiteBrowser. What I need is familiarity with handling data bank records.
Putting it better, I don't intend to overwrite files in folders. All files in the profile folders will be intact. I do not want to open these tables in Firefox. It could even be after add everything, but that is not the intention. I just wanted to merge all history and favorites into one bank and remove any duplicates. For that, I need to know the SQL commands to handle these files properly, with "append", "purge", "index", etc. By not mastering SQL, I'm looking for someone who understands more than me to give me the way.
I am not quite sure, but it seems that the "favicons.sqlite" file should also be involved in the manipulations. I saw that there are several tables of each other. I would have to write SQL commands to split the banks by month.
Any idea?
Attached to this question, I submit the table structure of "places.sqlite" and "favicons.sqlite":
Modified by Gab Arito
Chosen solution
Hi, guys.
I came back here to say that I was able to accomplish what I wanted with the history of Firefox. And to leave the way for someone else who has the same needs.
Among other files, Firefox keeps “places.sqlite” in the root folder of the profile. In it we find the tables “moz_historyvisits” and the “moz_places” which is where the records that interest us the most.
There is also the "favicons" file that will store the icons of the visited URL's among other data. It's not that important to us, but it's good that he walks side by side with his respective places.sqlite.
The correlation of tables in the data banks is explained in the appendix (may be outdated).
The explanation of the “places.sqlite” fields can be found here.
Well, to manipulate the history records of various profile folders, I used a few programs namely:
1. Firefox itself;
2. the MZHistoryView tool, which can be found here;
3. firefox-history-merger, an open source CLI (command line) tool hosted on Github;
4. SQLiteBrowser to compact data banks that had some records deleted. It can be found here;
With Firefox, I opened the history of each profile and deleted the months that already existed in other profiles. With SQLiteBrowser, I compressed data banks that had records deleted and could also inspect included records. With firefox-history-merger I have attached records from one "places.sqlite" to other "places.sqlite". With MZHistoryView I was able to track the amount of records attached, confirm the corresponding months and get a more complete view of the final result. This tool is not indispensable to reach the final result.
The firefox-history-merger tool also allows you to repair lost "favicons.sqlite" database icons. This action is also not essential to the end result.
With all this, I was able to take old histories and organize everything, eliminating redundancy of many folders and files stored in profiles. There were only lean "places.sqlite" files, sorted and without duplicates.
I researched about the maximum number of records and saw that maybe I could put it all together in one "places.sqlite" file. According to a SuperUser topic, this is possible after setting the places.history.expiration.transient_current_max_pages variable to 2147483647, the largest integer for 32 bits) in about: config. I didn't get to test. I prefer to keep files smaller and lighter.
That's all. Thank you for your attention and hope to have left useful information to those who need it most.
Read this answer in context 👍 0All Replies (3)
I'm not intimately familiar with all the tables. For history, these are the two I pay attention to:
- moz_places has an entry for each URL
- moz_historyvisits table records the date/time that a URL was visited and has a foreign key column to the id of moz_places
I don't know what would happen if you add duplicate URLs to moz_places, but you definitely cannot have duplicate values in the id column.
Since you are not aiming to use the combined places.sqlite in Firefox, might I suggest extracting the data to flat format like CSV or HTML tables instead? You could use a tool like the following:
jscher2000 said
Since you are not aiming to use the combined places.sqlite in Firefox, might I suggest extracting the data to flat format like CSV or HTML tables instead? You could use a tool like the following: http://www.nirsoft.net/utils/mozilla_history_view.html
Very nice tip! Nirsoft has several useful tools, but I was not aware of this one. I'll have a look on it and come back to some reviews.
Thanks.
Chosen Solution
Hi, guys.
I came back here to say that I was able to accomplish what I wanted with the history of Firefox. And to leave the way for someone else who has the same needs.
Among other files, Firefox keeps “places.sqlite” in the root folder of the profile. In it we find the tables “moz_historyvisits” and the “moz_places” which is where the records that interest us the most.
There is also the "favicons" file that will store the icons of the visited URL's among other data. It's not that important to us, but it's good that he walks side by side with his respective places.sqlite.
The correlation of tables in the data banks is explained in the appendix (may be outdated).
The explanation of the “places.sqlite” fields can be found here.
Well, to manipulate the history records of various profile folders, I used a few programs namely:
1. Firefox itself;
2. the MZHistoryView tool, which can be found here;
3. firefox-history-merger, an open source CLI (command line) tool hosted on Github;
4. SQLiteBrowser to compact data banks that had some records deleted. It can be found here;
With Firefox, I opened the history of each profile and deleted the months that already existed in other profiles. With SQLiteBrowser, I compressed data banks that had records deleted and could also inspect included records. With firefox-history-merger I have attached records from one "places.sqlite" to other "places.sqlite". With MZHistoryView I was able to track the amount of records attached, confirm the corresponding months and get a more complete view of the final result. This tool is not indispensable to reach the final result.
The firefox-history-merger tool also allows you to repair lost "favicons.sqlite" database icons. This action is also not essential to the end result.
With all this, I was able to take old histories and organize everything, eliminating redundancy of many folders and files stored in profiles. There were only lean "places.sqlite" files, sorted and without duplicates.
I researched about the maximum number of records and saw that maybe I could put it all together in one "places.sqlite" file. According to a SuperUser topic, this is possible after setting the places.history.expiration.transient_current_max_pages variable to 2147483647, the largest integer for 32 bits) in about: config. I didn't get to test. I prefer to keep files smaller and lighter.
That's all. Thank you for your attention and hope to have left useful information to those who need it most.
Modified by Gab Arito