Apache memory, SQLite backup and restore

Running one's (simple) personal-use HTML pages sometimes requires more than a browser, and so you gotta make a little sandbox. Reasons for posts here: 1) Little sandboxes nevertheless require Apache and PHP giants -- they must be minimized or they appropriate significant RAM and CPU desired for normal ops. 2) SQLite is useful, but should be backed up. How?

SQLite backup and restore

From this stack overflow page... "The safe way to backup is to run:

$ sqlite3 my_database.sq3

and then:

$ .backup backup_file.sq3

Result is copy of the database. It's different from regularly file copying, because it takes care of any users currently working on the database. There are proper locks set on the database, so the backup is done exclusively."

That's the proper way, but with one user (no worries about locks), simpler ways are available.

initialization

Apache: Main file httpd.conf. Problem: as noted at top, Apache combined with PHP can sip RAM incrementally. Serverfault.com has pages of helpful forum question answers for a fine-grained approach. One particularly helpful overview is here.

In general for a sandbox, you only need a couple of connections. I set MaxClients (MaxRequestWorkers since Apache 2.4) to "5".

Another thing is to be sure to enable

Firefox - bookmarks -SQLite

Links: SQLite browser   SQLite backup

---

My parents noted some problems with Firefox bookmarks; crashes when attempting to organize bookmarks and so on. I experienced some weird effects from a large bookmark file on my system as well. I looked into how the bookmarks are stored.

formats
We users typically access and manage our bookmarks via Firefox. However, unseen to users, Firefox relies upon SQLite, a flat-file (2-D) database application, to store and retrieve bookmark information on our hard drives. Many applications, like Firefox, require limited data storage and retrieval. A full 3-D database would be an extra installation process with additional problems. Accordingly, it's not uncommon to find SQLite built-in to many lighter applications; it's a free, open-source application that developers can easily adapt. What this means with Firefox is the bookmark data itself is kept in an SQLite file called places.sqlite. This file lives in the default, hidden Mozilla folder in the user's home directory, eg:

/home/foo/.mozilla/firefox/bQt4mrl3a.default/places.sqlite

(Of course, "foo" is a stand-in for whatever one has actually named their user directory.) Although the data is in SQLite format, the bookmarks remain portable; Firefox allows users to export and import bookmarks as html files, instead of the less familiar SQLite database file format.

bookmark problems
If there are problems with bookmarks, it's not always clear whether the problem is in a single bookmark entry from a selected website, or whether the SQLite index which manages all the bookmarks has become corrupted. It's much easier to restore the database, so this is probably the best starting solution attempt.

database solution
As a first solution, it's easier to restore the database, which can be done in about 3 minutes. The steps, as I do them, are to, 1) export the bookmarks file, 2) delete the old bookmarks file (and related backups) in the mozilla folder, and then, 3) re-open Firefox and import the exported bookmarks. This will reestablish and repopulate the database, which should then be stable.

In greater detail, the steps are:

1) Open Firefox, go into Bookmarks -> Organize Bookmarks -> Import and Backup -> Export HTML . This exports the bookmarks in html format. I gave my exported file the name bookmark.html, but any name is fine.

2) Exit Firefox, open a terminal, and delete places.sqlite and other bookmark related files:
$ cd .mozilla/firefox/bQt4mrl3a.default
$ rm places.sqlite
$ rm bookmarks.html
$ cd bookmarkbackups
$ rm *

3) Re-open Firefox and import the previously exported bookmark.html file (or whatever you named it when exporting).

the database solution didn't work
The steps above seem to address potential database corruption possibilities, since a new database is created when Firefox is re-opened after deleting the bookmark files. If this does not solve the challenge, then the problem might be a corrupt bookmark entry. This would have been imported back into the database and so would persist. I have hundreds of bookmarks, which means that looking through them, bookmark by bookmark, attempting to locate a corrupted one, might take hours and hours of hunting. If the solution above doesn't repair my problem, and if I know it's not a hardware or OS problem, I would find it easier to simply delete all bookmarks and start over. However, if I were retired and had loads of free time, I might read on here...

entry by entry solution
First, it's worthwhile to make a copy of places.sqlite, and then examine the copy instead of the original. If one is able to locate the problem in the copy, one can repair it there without damaging the original. Copy it back when complete and tested.

Secondly, going into the places.sqlite file to examine it, bookmark by bookmark, can be done by Command Line commands or via a GUI. We're talking about masochism either way -- the ultimate masochism would be using the command line. Using command line sqlite commands is beyond the scope here, but there are many tutorials out there, such as this one at the SQLite site.

Thirdly, because command line appears less efficient in this case, I'd suggest using a GUI SQLite manager of some sort. This should make an inspection of the places.sqlite file easier than with the command line, at least for most people. The free, open-source SQLite Browser may be a good option.

SQLite Browser
I found this application easy to download, compile, and install. It took about 10 minutes all together. I downloaded the latest release from SQLite sourceforge project and then followed the standard steps. Two variations;

1) it uses qmake since it's Qt-based:
$ qmake -unix -recursive
$ make

2) it doesn't install, just compiles a binary. But we can copy the binary into any directory we wish and start if from there.

All of this, from download to finish, took perhaps 10 minutes. Again, once the binary has been created, I put it in a folder with some other apps, and started it from the command line:
$ ./sqlitebrowser

Fig. 1 SQLite Browser

With the program running, open the copy of the bookmarks file places.sqlite (Figure 1). The bookmarks file/database appears to have a fairly elaborate structure. Looking for corrupted characters would take too much time for my taste in this structure; it's easier for me to wipe-out my bookmarks and start over again. That said, SQLite Browser is a helpful application and would be very useful in a situation where one had a simple SQLite database, say, a personal database for birthdays or anniversaries.

direct backup of an SQLite database
We noted above that we could simply copy the database records (in Firefox's case places.sqlite), and use that for a backup. However, if a person wants to make a compressed snapshot which includes the entire directory structure, it's relatively easy from a terminal using command-line:

$ tar cvfz `date +%Y%m%d`_mtdb_bkup.sql.tar.gz DATABASE PATH

And then to restore:
$ tar xvfz ARCHIVE.tar.gz

Much more can obviously be said about these bookmark problems and about SQLite. It's good to understand that there is an additional layer beyond Firefox, under the hood. A couple of options were provided above and it's my hope we can all hang on to our bookmarks.

more data fun - PaperHater

Links: Common MySQL Commands  WizzyWeb   VistA database

---

I've worked on going paperless since about 2002. It's a maddeningly dull, time consuming affair if one is not a computer programmer capable of designing a database. I am not. Just a person, a computer user. I call this project "PaperHater".

schema

critical, tax, code, projects, auto,

categories

Can it be done without a database? We have the Dewey Decimal (since 1876) and Library of Congress system for books and mags (ISBN, ISSN), so we can use their system to an extent, possibly for our library and citations grouping. But at home I have receipts from vehicles, medical lab reports, HS diploma, and on. This is also not exactly business style records management, since the categories include personal areas. Probably benefits from electronic records management (ISO 15489). File naming conventions, folder conventions and metadata conventions. Yet still might end up requiring a database.

file naming conventions (10:00) Simpletivity, 2018. Ad first 1:36. Uses 3 part naming, succinctly described. Probably best at .75 speed. Comments excellent.
Ffmpeg advanced techniques pt 2 (1:23:57) Nicholas Andre 2013. Windows-based lecture, but clear thinking fellow gives good context for what we're after. Backgrounder. Corning (NY) Community College course.

clutter issues

By mid-2002, I was regularly scanning. Everything from bank statements to letters from my deceased grandmother. I learned that the number and variety of the generated files grew rapidly and could not easily be organized; I was spending significant time categorizing and renaming the files using customary file-naming and folder-naming conventions. In spite of this effort, and with only a couple thousand files (albeit growing in number), I had to admit to myself that I was wasting more time arranging and not finding files than I previously had spent hassling with the inconvenience of paper documents. What good was the computer accomplishing for me?

A proper database sitting between thousands of stored electronic files, and the user attempting to retrieve one of those files, appeared to make all the difference. However, since an immediate solution looked neither likely nor affordable at that time, I reverted to storing documents in bankers boxes. I did this for several years, but with began to slowly acquaint myself with database programs.

By 2009, I had become routinely frustrated with paper accumulation again. Stuck between a computer and a pile of papers, there appeared to be no easy way out. Since early 2009, alternating Saturdays have been spent reading and learning installation processes, webserver configurations, and the like -- LAMP stuff. It seems to have paid off. I'm roughly at the 2/3 mark on this project. Over the next several months, I intend to add an entry here and there about PaperHater's progress to summarize for myself and to possibly help other home users who might be attempting to design and implement something similar on their own system(s). Good luck to all of us. It would be helpful if expert organizations with CMS-type experience could release reasonably priced PC/Mac database solutions instead of gouging us.

step 1

Design the database. I mostly use localhost for this, since it's fast and secure, but sometimes run things on the cloud server. In other words, I spent the many weekends required to learn how to configure reliable LAMPs on both my localhost and server site, and to have them running with the permissions and tweaks (think, eg. php.ini, config.inc.php, etc) I wanted. Following that portion came additional reading, head-scratching, and back-of-envelope sketches; then downloading and installing working applications. Finally, using phpMyAdmin and MySQL commands, I reviewed and modified table structures until I could determine a desirable schema. I also worked on php scripts. Eventually, I had a set of working databases, some copied to .sql scripts, and some partially completed php scripts. That's mostly where it stands now, but I'll move on to describe what's going to come next.

step 2

Copy the structure of a localhost or online database I've designed and tested and want to use for interaction. We put this into an .sql script.
(localhost)$ mysqldump --no-data database >/home/foo/database_template.sql

(online)$ mysqldump -h whateversite.com -uuser -ppassword --no-data -D database >database_template.sql

Then go to whatever web address I keep databases on, create a new database there (thanks to this site), and give it the structure we want from the .sql script.

$ mysql -h whateversite.com -uuser -ppassword
mysql> CREATE DATABASE newdatabase;
mysql> USE newdatabase;
mysql> SOURCE /home/foo/database_template.sql;
mysql> quit;

The new database is now ready to accept data-entry and to run queries.

step 3

Upload some php scripts. I've written a large percentage of the interfacing php scripts but, today, I learned about WizzyWeb, a $99 product which could help tune my scripts or create similar ones quickly. This seems like a very reasonable price if it does what's advertised. That is, if it saves me two hours, I've made-back my money.

step 4

Enjoy. But remember: the PHP, Postgresql/Mysql,Apache, etc. are all on the server side. Users still must create some Javascript additions for their served pages to influence the browser client. However, it is possible to write such skillful PHP code on the server side, that a client needs very little browser-side code to have a nice experience on the site. And remember that server-side is usually more secure since it can't be hacked without hacking the site, not just a webpage. Here's a list of some pre-built server-side PHP "UI Frameworks" written in PHP (as opposed to Java or .Net). Edit: Also, Sitepoint's poll results from 2015 for PHP frameworks.

PHP vs Python vs Java vs C#

Java no way, since the 2011 Oracle purchase. C# no way, since it's more or less locked to Microsoft's .NET framework. But Python now has modules which allow it to work on a server (of course it can still be used to code stand-alone programs too). PHP is designed to work on a webserver inherently, and so is more naturally integrated into HTML, but if one wants to be consistent, they could just do everything with (open-source) Python, using its server side add-on modules (scroll down to "Compared as Web Development Frameworks"). Even low-end ISP's like APlus, which has a terrible lag implementing anything, has PHP4/5 and Python capacity.