An introduction to an application server that allows you to build MySQL user interfaces without programming.

Assume you are tasked to write a browser-based, MySQL user interface for the table called CITY. CITY has two columns. The column names are city_name and state_code—each combined are the primary key.

Your user interface must enable users to execute the four main SQL operations: select, insert, update and delete. The main characteristics for each operation are:

• The select operation needs an HTML prompt form to request a query. It also needs a where clause generator to select from CITY. After forking MySQL and retrieving the raw rows, it needs to translate them into an HTML table form.
• The HTML table form needs to be editable, and user edits need to be translated into update statements.
• Each resulting row following the execution of a query is a candidate for deletion.
• The insert operation needs a blank form. It also needs to translate Apache's common gateway interface (CGI) into insert statements.

So, you might create the source file called city.c and type in all the required code. Of course, relational databases have relations. One city has many persons residing in it. Assume the PERSON table has the column names of full_name, street_address, city_name and state_code. full_name and street_address combined are the primary key (Figure 1).

Figure 1. Database Schema of Many Persons Residing in One City

Are you going to create the source file called person.c too? What about customer.c, inventory.c, order.c, ...?

Alternatively, you might create the source files called select.c, insert.c, update.c and delete.c. Then each of these modules would need as input:

• A single table name.
• The table's additional attributes.
• The table's column names and additional attributes.
• A recursive list of related tables.
• Apache's CGI dictionary output.

The principle behind Appaserver is this multi-module approach. Appaserver stores table names in a table. Each table's column names and relations are also stored in tables. Taking the table-driven concept to the nth degree forms a database of a database. You can glean a detailed understanding of how the Appaserver database is modeled from https://appahost.com/appaserver_database_schema.pdf.

To create Appaserver applications, you first need Appaserver. Because Appaserver communicates with both Apache and MySQL, installation has multiple steps. The installation steps are available at https://github.com/timhriley/appaserver/blob/master/INSTALL. You will install a database called "template" from which all your applications are spawned. Alternatively, you can create an Appaserver application securely at Cloudacus.

After you create your first application from the template database, you are ready to build it. After you log in, you are presented with three preinstalled roles (Figure 2).

Figure 2. Appaserver Supports Multiple Roles

Appaserver's security centers around roles. You grant permissions to roles, and you assign users to roles. The "System" role is used to build your database. Users interact with the database in all the other roles.

The highest user role is "Supervisor". The "Supervisor" role has permission to select, insert, update and delete every column in every row in every application table. Two important considerations are:

1. The "Supervisor" role cannot access any of the system tables, only application tables. (Well, except the APPLICATION_CONSTANTS table.)
2. The "Supervisor" role has permission to delete too much.

The lowest subordinate role is "Public". The "Public" role has select permission only. It is used in publicly funded research applications. Cloudacus hosts a research application called "Benthic". "Public" also may be used in commercial applications to display inventories.

The next lowest subordinate role is "Dataentry". The "Dataentry" role receives insert and lookup permissions but not update nor delete. If someone in the "Dataentry" role comes across a mistake, a supervisor needs to be interrupted to make the fix.

You may create many subordinate roles above these two. Assign yourself to all of them. Then you can easily test the security.

Users will interact with your database using Appaserver. Likewise, you will build your database using Appaserver. You will first use Appaserver's insert operations. If you make a mistake, you will use Appaserver's update and delete operations. After your user interface vision is complete, execute the "Create Application" process. You then can change to a user role and start producing.

Take a look at Figure 3. The cycle begins by first choosing a table to insert into or lookup from. Appaserver generates and sends a "select" SQL statement to MySQL, requesting the column names of your table. MySQL returns data containing the table's column names and other metadata to Appaserver.

Appaserver then generates HTML tags and sends them to your browser. The HTML tags will be blank widgets if you are inserting and query widgets if you are selecting.

Your browser displays a dialog-box (form) that contains a CGI "Submit" button. After you submit your form, the browser sends its contents to Appaserver. Appaserver generates and sends the next appropriate SQL statement to MySQL. The cycle then repeats.

Figure 3. Appaserver Data Flow Diagram

The Hello World Tutorial will step you through the entire database build.

Appaserver is a MySQL user interface. The interface is consistent throughout your application—both at the system level and the user level. Once you discover Appaserver's look and feel, new tables and columns can become new features simply by filling out a few forms.