Saturday, February 27, 2010

more data fun - PaperHater

Links: Common MySQL Commands  WizzyWeb   VistA database

I've been working on going paperless since about 2002. It's a maddeningly dull, time consuming affair so that, for my own amusement, I call this project or effort "PaperHater". I'm not a php programmer or CS guy and it's been a long road but, who knows, maybe someday it will become an open-source project.

In mid-2002, I started scanning my documents. These varied as widely as old letters from my deceased grandmother to recent bank statements. 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?

I noted one common feature in the software products that could actually have (too expensively) solved my problems in 2002: a DBMS. 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,, 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 -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 -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.

No comments: