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:
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:
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:
APPLICATION_CONSTANTS table.)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.
Tim Riley started programming on a TRS-80 model I in 1982. He holds a computer science degree from Florida International University and an accounting degree from California State University Sacramento. For the past 20 years he has been programming research databases for the Everglades National Park. He can be reached via appahost.com/contact.