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.

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.
  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.

    [_database]
        host=example.db
        name=awards
        user=librarian
        password=librarianpass

    [_tables]
        contributor=contributor
        role=role
        work=work
        contribution=contribution
        award=award
        awarding=awarding
  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
    </FilesMatch>
  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):

    <?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.
  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.)
        /home/public/xataface/tables/contribution
        /home/public/xataface/tables/awarding
  2. Within each folder, create a pair of empty files:
       /home/public/xataface/contribution/valuelists.ini
       /home/public/xataface/contribution/fields.ini
  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.

    [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"
  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.

    [contributor_id]
    widget:type = select
    vocabulary = Contributor

    [role_id]
    widget:type = select
    vocabulary = Role

    [work_id]
    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.

No comments:

Post a Comment