Xataface lets non-technical users edit MySQL data

Xataface is a framework for the LAMP stack designed to allow non-technical users to edit the contents of MySQL databases through a Web interface. While phpMyAdmin is a great tool for database administrators and those who are familiar with SQL and database design, Xataface aims at allowing less technical people to modify the database.

A single install of Xataface can service many Web sites on the same server you installed Xataface on. Each site using Xataface is set up to edit a single MySQL database. Without any configuration, Xataface gives you a Web interface that lets users add, edit, delete, and find tuples in all the tables of a nominated database. A tuple can be though of as the same thing as a row in a database, for example, the details of a particular customer or item that a shop is selling. If you want to change the labels, form entries used to edit tuples, allow relationships between tables to be edited, or perform data validation in the Web browser, you can use simple key=value options in configuration files.

Xataface is not in the Ubuntu, openSUSE, or Fedora repositories. In this article I’ll use Xataface version 1.0 beta3 on a 64-bit Fedora 8 machine. To get started with it, download the latest version and follow the steps shown below. I’m assuming here that your Apache DocumentRoot is /var/www/html. After these steps, Xataface should be able to be used by all LAMP applications on the server. A test page should be available at http://localhost/xataface/dataface_info.php telling you that the installation status is OK.

# cd /var/www/html
# tar xzvf /FromWeb/xataface-1.0-beta-3.tar.gz 
# ln -s xataface-1.0-beta-3 xataface
# chown -R  root.apache xataface*
# chown apache.apache xataface-1.0-beta-3/*ataface/templates_c

Shown below is an example database schema and some example data we can use to test Xataface with. The items table keeps track of items that we are selling, while the order table represents customers’ orders. Of course we would have a customers table in a real application instead of just dumping customer information directly into the order table. The orderedItems join table exists to capture the many-to-many relation between orders and items.

create database xatafacetest;
connect xatafacetest;

create table items 
  ( ProductBlurb text, 
    Price decimal(12,2) NOT NULL, 
    ItemID int(11) NOT NULL auto_increment, 
    Primary Key( ItemID ) );
create table orders
  ( OrderedOn timestamp NOT NULL default CURRENT_TIMESTAMP,
    Customer varchar(200),
    OrderID int(11) not null auto_increment,
    Primary Key( OrderID ) );
create table orderedItems
  ( OrderID int(11) not null,
    ItemID int(11) not null,
    PriceAtOrderTime decimal(12,2) not null,
    Primary Key( OrderID, ItemID, PriceAtOrderTime ) ,
    Foreign Key( OrderID ) references orders( OrderID ),
    Foreign Key( ItemID )  references items( ItemID )
);

insert into items values ( "Super candy",           3.50, 1 );
insert into items values ( "Five dollar shake",     5.01, 2 );
insert into items values ( "24 inch LCD",         299.99, 3 );
insert into items values ( "750Gb SATA HDD",       99.99, 4 );
insert into items values ( "32Gb flash usb drive",149.99, 5 );

insert into orders values ( default, "Freddy", 1 );
insert into orders values ( default, "Bart",   2 );

insert into orderedItems values ( 1, 2,   5.01 );
insert into orderedItems values ( 1, 3, 299.99 );
insert into orderedItems values ( 2, 3, 299.99 );
insert into orderedItems values ( 2, 4,  99.99 );
insert into orderedItems values ( 2, 5, 149.99 );

Now that we have Xataface itself installed and we can use the above database for demonstration, the simplest way to create a Web site to allow a user to access a database is to use the makesite script. You can also set up the Web site manually, as described in the documentation but there is no real advantage to doing so.

As shown below, makesite expects to know where you want the new Web site, how to connect to the database, and where Xataface itself is installed. I called my MySQL database xatafacetest, so I decided to give the Web interface the same name so that the site would be available at http://localhost/xatafacetest once the makesite command was been executed. One advantage of using the makesite script over manual setup is that it automatically creates a .htaccess file to protect the file containing the database connection parameters from being downloaded — a step that you have to perform yourself if you are setting up a site manually, and one with severe consequences if forgotten. Note that the location of the Xataface install is a URL that can be used by the site rather than the path on the local host. If you make a mistake invoking the makesite script with incorrect options you will have to drop the dataface__version table from your database before you can run makesite again.

# php /var/www/html/xataface/makesite 

makesite: invalid options entered.
	
	Usage: makesite <site_path> <db_user>:<db_pass>@<db_host>/<db_name> <dataface_url>
	or
	php makesite <site_path> <db_user>:<db_pass>@<db_host>/<db_name> <dataface_url>
		where 
		<site_path> = The path (absolute or relative) to your application directory.
		<db_user>	= The MySQL username to connect to the database
		<db_pass>	= The User's password to connect to the database
		<db_host>	= The MySQL host name.
		<db_name>	= The name of the mysql database for the application.
		<dataface_url> = The URL to the dataface installation

# php xataface/makesite 
  xatafacetest 
  root:rootme@localhost/xatafacetest 
  http://v8tsrv/xataface
Copying conf.ini file to 'xatafacetest/conf.ini'...
Found table: items .  Adding to application menu...
Found table: orderedItems .  Adding to application menu...
Found table: orders .  Adding to application menu...
Copying .htaccess file to 'xatafacetest/.htaccess'...
Copying index.php file to 'xatafacetest/index.php'...
Creating tables directory at 'xatafacetest/tables'...
Creating config directory for table 'items' at 'xatafacetest/tables/items'...
Creating config directory for table 'orderedItems' at 'xatafacetest/tables/orderedItems'...
Creating config directory for table 'orders' at 'xatafacetest/tables/orders'...
Site successfully created at 'xatafacetest'.

When you load http://localhost/xatafacetest in your Web browser you will see an interface listing the tuples in the items table, as shown in the screenshot.

The find tab lets you start a search by entering data for any column in the database. The details tab lets you view or edit a specific tuple. In the default configuration you will get an HTML textarea to edit the product blurb. You can use a configuration file to tell Xataface to make the tuple editing page easier to use. The makesite script will have created a directory for each table in the database. Inside each directory a handful of INI-format configuration files are used to tweak the way Xataface presents things to the user. The file below adds a few labels to let users of the Web interface know that the price is always in US dollars and to allow them to edit the product blurb using a WYSIWYG HTML editor. This produces the tuple editing page shown in the screenshot.

# cat xatafacetest/tables/items/fields.ini 
[Price]
widget:label = "Price (US$)"
widget:description = "Price is always in USA Dollars"

[ProductBlurb]
widget:type = "htmlarea"

In the default setup shown above, anyone who can connect to the Web site can perform any database operations that the MySQL user Xataface is using can perform. Xataface includes a permission and role system so you can lock down what each MySQL user is able to do to the database.

In the database schema shown above, there are two foreign keys in the orderedItems table. When you edit a tuple in Xataface, the OrderID and ItemID are shown as text input boxes. By setting some configuration entries in an INI file, you can make these foreign keys explicitly reference their target table. In this case, it is not very intuitive for the user to select ItemID numeric values, so you can go one step further and let the ItemID be input through a drop-down box that shows a short description field from the items table while operating on the ItemID behind the scenes.

You have to tell Xataface about foreign key relationships using INI file settings. Once you have told Xataface about the foreign keys you want to allow the user to edit, it will create a new tab in the tuple editing page allowing the relationship to be edited. In a way, it can be a positive thing that you have to tell Xataface about which foreign keys you want to expose, so that the user does not get overloaded with many complex key relationships.

The documentation for Xataface includes a walkthrough using phpMyAdmin to allow those who are not familiar with MySQL or databases to generate the tutorial database for use with Xataface. In general, the getting started guide is wonderful, though because it is an ongoing effort it also currently has an abrupt end.

Wrap up

The Xataface project is in the process of being renamed from Dataface. This minor name change being ongoing means that there are still some references using the old name throughout the documentation.

You can augment the add, delete, find, and edit operations using configuration options, PHP code, and Xataface’s templating system. For instance, you can include a customized find operation that hides some fields and allows users to find information in the database without needing to care about what columns there are.

Xataface is a great tool for allowing non-technical people to edit the contents of a MySQL database. You get a user permission system and the ability to customize how the tuple editing form appears. Users can edit many-to-many relationships without needing to know about join tables or other details, and Xataface uses templates with predefined slots so you can easily hook your own customizations into the site.