Tuesday, February 3, 2015

Relatively Easy Data Entry for a MySQL Database

I'm making a web app for exploring literary award winners (and honors). It's running with some hand-coded sample data, but now it's time to set up the database and start entering info for many books and awards.

The first time I made a database-driven website, I did data entry at the MySQL command prompt. What a hassle! This time, I won't do it by hand. And, since visitors won't be making changes, I don't want to code input logic into the web app itself. I went looking for generic software that I could run in a password secured sub-directory without having to pretty it up for others.


If my main URL is http://www.garrensneatapp.org, there will be an http://www.garrensneatapp.org/xataface sub-directory that's running Xataface.

Sample Database Schema
Diagram from MySQL Workbench

CREATE  TABLE IF NOT EXISTS `awards`.`contributor` (
  `contributor_id` INT NOT NULL AUTO_INCREMENT ,
  `surname` TEXT NULL ,
  `first_name` TEXT NULL ,
  PRIMARY KEY (`contributor_id`) )

CREATE  TABLE IF NOT EXISTS `awards`.`role` (
  `name` TEXT NOT NULL ,
  PRIMARY KEY (`role_id`) )

CREATE  TABLE IF NOT EXISTS `awards`.`work` (
  `title` TEXT NOT NULL ,
  `cover_filename` TEXT NOT NULL ,
  `description` TEXT NULL ,
  PRIMARY KEY (`work_id`) )

CREATE  TABLE IF NOT EXISTS `awards`.`contribution` (
  `contribution_id` INT NOT NULL AUTO_INCREMENT ,
  `role_id` INT NULL ,
  `work_id` INT NULL ,
  `contributor_id` INT NULL ,
  PRIMARY KEY (`contribution_id`) ,
  INDEX `contribution_role_role_id_idx` (`role_id` ASC) ,
  INDEX `contribution_work_work_id_idx` (`work_id` ASC) ,
  INDEX `contribution_contributor_contributor_id_idx` (`contributor_id` ASC) ,
  CONSTRAINT `contribution_contributor_contributor_id`
    FOREIGN KEY (`contributor_id` )
    REFERENCES `awards`.`contributor` (`contributor_id` )
  CONSTRAINT `contribution_work_work_id`
    FOREIGN KEY (`work_id` )
    REFERENCES `awards`.`work` (`work_id` )
  CONSTRAINT `contribution_role_role_id`
    FOREIGN KEY (`role_id` )
    REFERENCES `awards`.`role` (`role_id` )

CREATE  TABLE IF NOT EXISTS `awards`.`award` (
  `title` TEXT NOT NULL ,
  `description` TEXT NOT NULL ,
  PRIMARY KEY (`award_id`) )

CREATE  TABLE IF NOT EXISTS `awards`.`awarding` (
  `award_id` INT NULL ,
  `work_id` INT NULL ,
  `year` YEAR NULL ,
  PRIMARY KEY (`awarding_id`) ,
  INDEX `awarding_award_award_id_idx` (`award_id` ASC) ,
  INDEX `awarding_work_work_id_idx` (`work_id` ASC) ,
  CONSTRAINT `awarding_award_award_id`
    FOREIGN KEY (`award_id` )
    REFERENCES `awards`.`award` (`award_id` )
  CONSTRAINT `awarding_work_work_id`
    FOREIGN KEY (`work_id` )
    REFERENCES `awards`.`work` (`work_id` )

The following walkthrough assumes the database schema is already loaded in a MySQL instance, and that a MySQL user has been given full permissions to that schema.

MySQL instance: example.db
MySQL schema: awards
MySQL user: librarian
MySQL pass: librarianpass

Website root: /home/public/

Installing Xataface

Xataface is one of those open source projects that has a bunch of documentation in various degrees of being out of date. There are also multiple ways to install it. The least error-prone method seems to be a manual installation.
  1. Download the latest tar.gz file for Xataface. Save this to (or transfer it to) your website's root folder. Extract it and rename the folder from something like "xataface-2.1.2" to simply "xataface". In this example, its absolute path will be "/home/public/xataface".
  2. Create a "conf.ini" file in the xataface folder (e.g: /home/public/xataface/conf.ini) with information about connecting to your database and which tables you want Xataface to make available for editing. See http://xataface.com/wiki/conf.ini_file for details on this file.


  3. Delete the pre-existing file "/home/public/xataface/.htaccess". Create a new one that only blocks access to .ini files. For Apache 2.4, this would be:

    <FilesMatch "\.ini$">
        Require all denied
  4. Create a "/home/public/xataface/templates_c" folder. Nothing else needed with this.
  5. Delete "/home/public/xataface/index.php" and create a new file of the same name with the content (yes, this is the only content and yes the closing stuff for the php tag is missing):

            require_once 'dataface-public-api.php';
            df_init(__FILE__, 'xataface')->display();

    Note: Change the 'xataface' to something else if you aren't using "xataface" as the subdirectory.
  6. Fix permissions, so that the web service's group can access everything. On my host, the group is called "web", so...

    > chgrp -R web /home/public/xataface
    > cd /home/public/xataface
    > find . -type f -exec chmod 664 {} \;
    > find . -type d -exec chmod 775 {} \;
  7. Browse to your equivalent of "http://www.garrensneatapp.org/xataface/index.php". Hopefully, you see pretty graphics and not an error message, but the errors usually contain helpful hints.
Configure Table Relationships

Check one of your tables that doesn't define foreign key relationships. It should be possible to create new records at this point:

Foreign key fields require extra preparation, but they are the big time saver that makes Xataface worthwhile. See http://xataface.com/documentation/tutorial/getting_started for additional information on the procedure below.
  1. Create a "tables" subdirectory in the "xataface" directory, then another subdirectory for each table containing foreign key definitions below that. (Take care: directory names are case-sensitive to the MySQL table names.)
  2. Within each folder, create a pair of empty files:
  3. In "valuelists.ini", put an arbitrary label of your choice in square brackets, then a SQL query that will be used to populate the drop-down box next to that label.

    __sql__ = "SELECT contributor_id, surname FROM contributor ORDER BY

    __sql__ = "SELECT role_id, name FROM role ORDER BY name"

    __sql__ = "SELECT work_id, title FROM `work` ORDER BY title"
  4. In "fields.ini", put the foreign key field names in brackets, then set the name of the corresponding label in "valuelists.ini" as the drop-down box vocabulary list.

    widget:type = select
    vocabulary = Contributor

    widget:type = select
    vocabulary = Role

    widget:type = select
    vocabulary = Work
  5. Make sure you've added a "valueslist.ini" and "fields.ini" for all tables needing the extra customization, then fix permissions again:

    > chgrp -R web /home/public/xataface/tables
    > cd /home/public/xataface/tables
    > find . -type f -exec chmod 664 {} \;
    > find . -type d -exec chmod 775 {} \;
  6. Try to create a new record for one of these tables:

You should be ready to start entering all of the data. With this basic setup, you will need to fill out parent tables with new data (like contributor names) before you can use them in child tables (like using contributor names in the contribution table). If that's too inconvenient, see http://www.xataface.com/documentation/tutorial/getting_started/relationships for a way to create parent records during the creation of records for child tables.

Last tip: to change a single field's contents, it works better to click the data in the "all" view than it does to mess around with the "update" buttons.

Securing Xataface

By installing Xataface in a subdirectory rather than in the main directory, it becomes easy to use web server level measures. For Apache 2.4, I followed the instructions here: http://httpd.apache.org/docs/2.4/howto/auth.html.

Alternatively, use the security options built into Xataface, which are especially useful if you want to allow end users to access Xataface directly: http://xataface.com/wiki/authentication.

Thursday, December 11, 2014

Installing Joomla on NearlyFreeSpeech.net

NearlyFreeSpeech.net is a web hosting service popular with more technically minded folks because of its pricing structure and its anti-censorship attitude. It lacks training-wheels features, so setting up a content management system like Joomla is trickier than usual. This post details what I did to get going.


I already had an account, DNS hosting, and a SQL process set up. I will assume you are to the point where you can create a /home/public/phpinfo.php file, bring up http://somedomain.org/phpinfo.php, and confirm you have a working environment. Then I'll assume you emptied out your /home/public/ directory, including hidden files. (Of course, this also means I assume you're familiar with using an SSH and SCP client to manage things. You can leave FTP turned off, as NearlyFreeSpeech recommends.)

Create Database

In the NFS member console (I'll call this "NFS console" from now on), go to the "mysql" tab then click the DSN you created (let's assume "somedomain.db"). Command line fans can use the info here to connect over the SSH connection and make an empty database. Alternatively, click "Create a database" on this page. I'll assume the new database is called "joombase".

Acquire Joomla

In whichever way you prefer, put the contents of the Joomla download into /home/public/.

Note: You may want to check the requirements of the version of Joomla you download against the Server Type and CGI/SSH Realm as listed on the "sites" tab in NFS console. I used the most recent stable version of Joomla and the current general purpose recommendations on NFS:

Joomla version: 3.3.6
Server Type: [Production] Apache 2.4, PHP 5.5, CGI (Stochastic)
CGI/SSH Realm: Blue

Command Line Stuff

From /home/public/:

> mv htaccess.txt .htaccess
> touch configuration.php

> chgrp -R web *
> find . -type f -exec chmod 664 {} \;
> find . -type d -exec chmod 775 {} \;

At this point, your /home/public/ directory should look similar to this:

Install Time!

Open a web browser to http://somedomain.org/installation

(1) Configuration page: make up your own values here.

(2) Database page:

Database Type: MySQLi
Host Name: somedomain.db (or whatever you named your DSN; don't use 'localhost'!)
Username/Password: (see the same page in NFS console that lists the DSN for MySQL credentials)
Database Name: joombase (or whatever you created earlier)
Table Prefix: (leave it as it is)
Old Database Process: Remove

(3) Overview page: pick your preferred sample data and take a gander at the information below, then proceed.

Hopefully, "Congratulations! Joomla! is now installed." will be showing. Click "Remove installation folder." If you need to remove it manually, go to /home/public/ and run:

> rm -R installation/

Administration Console

Go to http://somedomain.org/administrator/ and use the credentials you created on the Configuration page of the installer.

Click System -> System Information -> Directory Permissions, then confirm that all of the directories have a "Writable" status.

Click System -> Global Configuration -> Server, then confirm that "Enable FTP" is set to "off." I had trouble with installing extensions when this was enabled, even if I wasn't trying to use FTP to install extensions.

Click System -> Control Panel -> Install Extensions, then click "Add 'Install from Web' tab".

Do things with your new site!

Thursday, October 9, 2014

Quote of the Day: Conan Doyle on the American West

"In the central portion of the great North American Continent there lies an arid and repulsive desert, which for many a long year served as a barrier against the advance of civilization. From the Sierra Nevada to Nebraska, and from the Yellowstone River in the north to the Colorado upon the south, is a region of desolation and silence. Nor is Nature always in one mood throughout this grim district. It comprises snow-capped and lofty mountains, and dark and gloomy valleys. There are swift-flowing rivers which dash through jagged cañons; and there are enormous plains, which in winter are white with snow, and in summer are grey with the saline alkali dust. They all present however, the common characteristics of barenness, inhospitality, and misery.

There are no inhabitants of this land of despair. A band of Pawnees or of Blackfeet may occasionally traverse it in order to reach other hunting-grounds, but the hardiest of the braves are glad to lose sight of those awsome plains, and to find themselves once more upon the prairies. The coyote skulks among the scrub, the buzzard flaps heavily through the air, and the clumsy grizzly bear lumbers through the dark ravines, and picks up such sustenance as it can amongst the rocks. These are the sole dwellers in the wilderness.

In the whole world there can be no more dreary view than that from the northern slope of the Sierra Blanco. As far as the eye can reach stretches the great flat plainland, all dusted over with patches of alkali, and intersected by clumps of the dwarfish chapparal bushes. On the extreme verge of the horizon lie a long chain of mountain peaks, with their rugged summits flecked with snow. In this great stretch of country there is no sign of life, nor of anything appertaining to life. There is no bird in the steel-blue heaven, no movement upon the dull, grey earth—above all, there is absolute silence. Listen as one may, there is no shadow of a sound in all that mighty wilderness; nothing but silence—complete and heart-subduing silence.

It has been said there is nothing appertaining to life upon the broad plain. That is hardly true. Looking down from the Sierra Blanco, one sees a pathway traced out across the desert, which winds away and is lost in the extreme distance. It is rutted with wheels and trodden down by the feet of many adventurers. Here and there there are scattered white objects which glisten in the sun, and stand out against the dull deposit of alkali. Approach and examine them! They are bones: some large and coarse, others smaller and more delicate. The former have belonged to oxen, and the latter to men. For fifteen hundred miles one may trace this ghastly caravan route by these scattered remains of those who had fallen by the wayside.

Looking down on this very scene, there stood upon the fourth of May, eighteen hundred and forty-seven, a solitary traveller[....]"

— from A Study in Scarlet (Part 2, Chapter 1), by Arthur Conan Doyle

Wednesday, June 25, 2014

On Living Life and Accepting Death

  photo by Lari Huttunen (cc by-nc-nd 2.0)

I don’t believe in an afterlife, which means I do believe in death. It shouldn’t be such a strange thing to believe life ends in death, but most people believe or at least hope for more. Death denial is an understandable impulse; sometimes it even extends to family pets, but less often to other animals. We want ourselves and those we care about to carry on. We won’t. They won’t.

Does the reality of death mean life doesn’t matter? No, it means life is the only thing that matters. You get once chance to exist and it’s happening now. Now is the time to love, the time to learn, the time to create, the time to enjoy yourself and choose to either bring comfort or suffering to others.

What about jerks who prosper in life and kind people who live hard lives? Doesn’t the reality of death mean the world is unjust? Yes. That may sound harsh, but how kind is it to tell people that the suffering and deaths of their loved ones is for the best? It can be disheartening to know we can’t make everything better, but what we can do matters all the more because there’s no other help on the way.

Besides, popular alternatives tend to be worse. At least suffering and injustice end along with life. Mainstream Christian and Muslim beliefs promise unending joy for a select few and unending suffering for most people. That’s solving a house fire with an atom bomb.

Why not just have as much pleasure in life as possible and forget about other people? Well, there’s nothing wrong with pleasure. Pleasure is great and it comes in many satisfying forms! As a loved one says: “No time enjoyed is entirely wasted.” As for ignoring the suffering of other people, moral philosophers have tried in vain to find a reason for completely selfish people to care about others. You have to start with caring a little. Thankfully, most of us do. We don’t have to solve whole categories of suffering on our own; we can cooperate with others, working within the limits of our imperfect empathy and our incomplete understanding to make our lives a little better.

“If I can stop one heart from breaking,
I shall not live in vain;
If I can ease one life the aching,
Or cool one pain,
Or help one fainting robin
Unto his nest again,
I shall not live in vain.”

— Emily Dickinson

Wednesday, May 21, 2014

Quote of the Day: Chomsky on Technology and Power

"As to the idea [...] that there is some technological imperative, some property of advanced technological society that requires centralized power and decision making [...] as far as I can see it's perfect nonsense; I've never seen any argument in favor of it.

It seems to me that modern technology, like the technology of data-processing, or communication, and so on, has precisely the opposite implications. It implies that relevant information and relevant understanding can be brought to everyone quickly. It doesn't have to be concentrated in the hands of a small group of managers who control all knowledge, all information, and all decision-making. So technology, I think, can be liberating, it has the property of being possibly liberating; it's converted, like everything else, like the system of justice, into an instrument of oppression because of the fact that power is badly distributed. I don't think there is anything in modern technology or modern technological society that leads away from decentralization of power, quite the opposite."

— Noam Chomsky, The Chomsky-Foucault Debate: On Human Nature, p. 64

Tuesday, May 20, 2014

Quote of the Day: Dewey on the Ideal Librarian

"In our state library school I give each year a course of five lectures on the qualifications of a librarian, and point out under a half-hundred different heads the things we should demand in an ideal librarian; but when we have covered the whole field of scholarship and technical knowledge and training, we must confess that overshadowing all are the qualities of the man. To my thinking, a great librarian must have a clear head, a strong hand, and, above all, a great heart. He must have a head as clear as the master in diplomacy; a hand as strong as he who quells the raging mob or leads great armies on to victory; and a heart as great as he who, to save others, will, if need be, lay down his life. Such will be greatest among librarians; and, when I look into the future, I am inclined to think that most of the men who will achieve this greatness will be women."

- Melvil Dewey's brief article "The Ideal Librarian" quoted in full, The Library Journal 24(1), January 1899, p. 14. [Google Books scan]

Friday, May 16, 2014

Quote of the Day: Jensen on Library Neutrality

"Take a simple example involving the common assumption in the United States that the capitalist economic system is the only rational and morally defensible way to organize an economy. There can be, and often is, much debate about how to structure and administer a capitalist economy, but the system itself is rarely contested, despite centuries of resistance to capitalism around the world and considerable intellectual work underlying that resistance. Now, imagine that a librarian wants to produce a display of the libraryʼs resources on economics to encourage patrons to think about the subject. In many libraries such a display would include no critiques of capitalism, but simply literature that takes capitalism as a given. Such a display that ignores critical material likely would produce no controversy (except perhaps a few complaints from anti-capitalists about the absence of critique, who could easily be dismissed as cranks). It is unlikely that school boards or city councils would take up the issue of the obvious bias against socialism and other non-capitalist economic systems. Consider what might happen if a librarian charged with this task actually produced a display that carefully balanced the amount of material from as many different perspectives as s/he could identify. In many places, that display would be denounced for its 'obvious' socialist politics. Now, imagine that a librarian, observing the way in which Americans are systematically kept from being exposed to anti-capitalist ideas in the schools and mass media, decides to organize materials that compensate for that societal failure by emphasizing critiques of capitalism. That librarian could be guaranteed not only criticism and charges of political bias, but likely disciplinary action.

My point is simply that all of those decisions have a political dimension, which is unavoidable. My concern here is not which one is the right decision, but that the librarian whose display is in line with the conventional wisdom likely will escape criticism while any other choices will raise questions about 'politicizing' what should be a professional decision. Unfortunately, this neutrality game will derail rather than foster serious discussion of the issues."

- from "The Myth of the Neutral Professional" by Robert Jensen in Progressive Librarian Issue #24.