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.
Solution
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`) )
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `awards`.`role` (
`role_id` INT NOT NULL AUTO_INCREMENT ,
`name` TEXT NOT NULL ,
PRIMARY KEY (`role_id`) )
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `awards`.`work` (
`work_id` INT NOT NULL AUTO_INCREMENT ,
`title` TEXT NOT NULL ,
`cover_filename` TEXT NOT NULL ,
`description` TEXT NULL ,
PRIMARY KEY (`work_id`) )
ENGINE = InnoDB;
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` )
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT `contribution_work_work_id`
FOREIGN KEY (`work_id` )
REFERENCES `awards`.`work` (`work_id` )
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT `contribution_role_role_id`
FOREIGN KEY (`role_id` )
REFERENCES `awards`.`role` (`role_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `awards`.`award` (
`award_id` INT NOT NULL AUTO_INCREMENT ,
`title` TEXT NOT NULL ,
`description` TEXT NOT NULL ,
PRIMARY KEY (`award_id`) )
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `awards`.`awarding` (
`awarding_id` INT NOT NULL AUTO_INCREMENT ,
`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` )
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT `awarding_work_work_id`
FOREIGN KEY (`work_id` )
REFERENCES `awards`.`work` (`work_id` )
ON DELETE CASCADE
ON UPDATE NO ACTION)
ENGINE = InnoDB;
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.
- 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".
- 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.
[_database]
host=example.db
name=awards
user=librarian
password=librarianpass
[_tables]
contributor=contributor
role=role
work=work
contribution=contribution
award=award
awarding=awarding - 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
</FilesMatch> - Create a "/home/public/xataface/templates_c" folder. Nothing else needed with this.
- 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):
<?php
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. - 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 {} \; - 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.
- 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.)
/home/public/xataface/tables/contribution
/home/public/xataface/tables/awarding - Within each folder, create a pair of empty files:
/home/public/xataface/contribution/valuelists.ini
/home/public/xataface/contribution/fields.ini - 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.
[Contributor]
__sql__ = "SELECT contributor_id, surname FROM contributor ORDER BY
surname"
[Role]
__sql__ = "SELECT role_id, name FROM role ORDER BY name"
[Work]
__sql__ = "SELECT work_id, title FROM `work` ORDER BY title" - 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.
[contributor_id]
widget:type = select
vocabulary = Contributor
[role_id]
widget:type = select
vocabulary = Role
[work_id]
widget:type = select
vocabulary = Work - 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 {} \; - Try to create a new record for one of these 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.
