This is a read-only archive. Find the latest Linux articles, documentation, and answers at the new!

Feature: Tools & Utilities

Xataface lets non-technical users edit MySQL data

By Ben Martin on July 28, 2008 (4:00:00 PM)

Share    Print    Comments   

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.

Ben Martin has been working on filesystems for more than 10 years. He completed his Ph.D. and now offers consulting services focused on libferris, filesystems, and search solutions.

Share    Print    Comments   


on Xataface lets non-technical users edit MySQL data

Note: Comments are owned by the poster. We are not responsible for their content.

Xataface lets non-technical users edit MySQL data

Posted by: Anonymous [ip:] on July 28, 2008 06:50 PM
damned what for a great tip! thats exactly what i´m looking for. i´m not so a freaky techy and on the first look it seems to be pretty easy to handle. at time i´m working on a new site of me and i will just give it a try, anyway good article, great tips and a fantastic peace of software... Sonya from (the new "thing i´m working on")


Xataface lets non-technical users edit MySQL data

Posted by: Anonymous [ip:] on July 29, 2008 03:24 PM
Hey! What about this Plone-(exactly-)like interface [1]? ... I'm surprised to see it's php! ... Are those images real? ...



Re: Xataface lets non-technical users edit MySQL data

Posted by: Anonymous [ip:] on August 07, 2008 10:23 PM
The default Xataface stylesheet was adapted from Plone 2.0. The stylesheet is the only similarity between the two projects, code-wise. I.e. Xataface doesn't borrow any code from Plone (aside from CSS and some javascript).


Posted by: Anonymous [ip:] on August 02, 2008 10:23 PM
paylaşım için sağol klavyene sağlık.


Xataface lets non-technical users edit MySQL data

Posted by: Anonymous [ip:] on August 07, 2008 10:25 PM
Thanks for this great article. Some of the setup instructions you provide I shall have to incorporate into my own.
One correction. The name change was from Dataface to Xataface and not vice versa. So Xataface is the correct name moving forward.

Best regards
Steve Hannah


Xataface lets non-technical users edit MySQL data

Posted by: Anonymous [ip:] on August 08, 2008 08:43 AM
I've been using the framework for over two years, and it makes it very easy to get a user interface to a database up and running quickly. It is very flexible and can be configured in lots of ways. If you know any php it is very powerful, but it's really useful to people like me who don't know much php.

I strongly recommend Xataface if you need to give users access to databases over the web, but don't have the knowledge or time to handcraft.


This story has been archived. Comments can no longer be posted.

Tableless layout Validate XHTML 1.0 Strict Validate CSS Powered by Xaraya