Wizard and CRUD Applications to Build other CRUD Applications

Looking at the possibility of using CRUD applications to manage other CRUD applications inside a web browser and without any hand-coding. Also, presenting a step wizard deriving the database structure from the UI rather than the opposite.

In this article we will explore the new possibilities which open up when we decide to drop the XML and save the metadata directly in the database. That way, we can use Evolutility (the "on the fly code generator") as a CRUD application to manage the metadata of other CRUD applications (the same way we already used it to manage the data in our previous examples of "to do list" or "address book"). Basically, the application becomes the application designer.

We will also look at a step wizard to build applications by deriving the DB structure from the UI instead of the more commonly used opposite way.

Moving metadata from XML to database

Sure, XML is "human readable", but even with XSDs, it may not always be "human writable", especially when it comes to metadata. To get rid of the XML, we could either build a designer reading and persisting XML, or we could move the metadata into the database and manage it with a new CRUD applications. The later option is much more fun and requires less code.

With our simple meta-model, going from XML to database is not too difficult:

We can start by using one DB table per XML element and one DB column per XML attribute. We have 4 elements in the meta-model: form, data, panel, and field. These can easily become 4 database tables.

As the relationship between form and data is a 1-to-1, we can normalize the schema by gathering them together in the same table.

Let's also add a table for the necessary list of values (here field types).

As a convention, let's prefix our tables with "EvoDico_" to indicate what the table set is about.

Finally (not shown in the schema), let's add a trigger to automatically delete fields and panels of a form when it gets deleted.

We are now left with the following database schema to store our applications metadata:

For simplicity, this set of tables can live in the same database as the applications data. In the real world, because the metadata doesn't change as often as the data, it may be advantageous to keep them in a separate database to make it easier to backup and restore them independently.

The SQL script to build it is provided in the demo project included with this article.

CRUD applications to manage other CRUD applications

With the former schema, we can now build CRUD applications to manage the metadata of our other applications.

We can move away from the implementation model (of the XML) and get closer to the user mental model by decoupling the UI structure from the database structure. By carefully organizing fields into panels and tabs we can, for example, separate the UI metadata from the database metadata. Also, some user-friendly field labels will be more meaningful than our previous XML attributes.

We can display an application summary like the following:

We will have a different CRUD application for drilling down on fields:

These CRUD pages are now the "designer" for other CRUD applications.

Keeping the designer handy

We can also blur the distinction between the application and the designer by letting users edit the metadata of specific UI elements at run-time. This is done by adding a little icon near each UI element of the application. These icons (color coded by element type) pop up the corresponding designer page shown in the previous paragraph (or some equivalent custom pages where the metadata is presented in a slightly different manner).

A step wizard to create CRUD applications

Let's now look at an even simpler approach: a step wizard. To get closer to the user mental model, we can totally omit from the wizard UI all references to the database. Internally, we can derive the database structure from the UI metadata (rather than the opposite).

To gather the minimum UI metadata necessary to build a CRUD application (here the same "to do list" example as used in the previous 2 articles) we will have the following steps:

Step 3 - Fields definition details: Specifies additional information necessary for each field. Field properties depend on the field type chosen in the previous step.

Step 4 - Search options: Decides which fields are included in the search, advanced search, and list result.

Step 5 - Panels layout: Gathers the list of panels used to visually group fields together (in View and Edit modes). Also specifies the relative width of each panel according to a "flow positioning" scheme.

Step 6 - Fields layout: Decides which fields belong to which panel (specified in the previous step), and the relative width of each field inside its panel.

Step 7 - The CRUD application is ready : This step shows the XML and SQL necessary for the application. Really, these could be hidden as most users may not know what to do with it, but developers should find it useful. This page also contains links to customize the application right away.

Although there was not any single reference to the database, the wizard can build all necessary tables and populate them with seed data automatically (you may have to tune it afterward for optimizations). The trick is that because the scope of CRUD is limited enough, there is only one driving table and we can use field labels for column names after trimming the comas and special characters, and replacing spaces by underscores. We are using our "UI field type" instead of real data types as discussed in the meta-model article. For "lists of values" we already know the pattern: an integer value in the driving table to store the primary key of the secondary table...

Because the wizard runs on the Web and applications do not need compilation, after the last step the new application is ready to use immediately.

Using the code

To run the demo project, follow these steps:

Copy the directory EvoDico which contains the web site to your web server.

Create a new SQL Server database.

Change the database connection string in the appSettings section of the Web.config file (or in every ASPX page).

Run the SQL scripts which are in the SQL directory on your database in the following order:

evodico.2.2.sql

evodico-seed.2.2.sql

evodico-sample.2.2.sql

Run the SQL scripts to create tables for the sample applications (in the SQL/Samples/ directory).

To build CRUD applications from scratch with minimal effort:

Start by running the wizard

Try your new application

Customize your new application

Maybe move it to XML for more flexibility (as the database repository doesn't support all features of the XML yet)

Tweak your database manually if necessary

Playing with fire

Finally, we can imagine using triggers to modify the database structure automatically when the metadata gets modified. It is quite dangerous (especially as we may have several applications running against the same set of tables) but it may be the way to go further into automatizing the process.

A possible trigger for such purpose can be something like the following:

Call for contributions to a budding new open source project

This is a work in progress. The code generator is stable but the wizard and the designer (the application to manage other applications) still need some work.

The code included with the article is also available and updated on SourceForge under Affero GPL v3 with dual licensing, and the web site for the project is www.evolutility.org. In your spare time, feel free to improve this code, and contribute. Thanks.

Share

About the Author

I'm a UI engineer for a startup in California. What I really enjoy is to build tools to describe UI in metadata, store that metadata (outside of the code) in a database, XML or JSON, and then dynamically generate the UI at run-time based on that metadata... which I do with my open source project Evolutility.

I belong to a group of collectors (star wars stuff). We are currently using our own php/mysql (phpbb additions) so that each one can mark the stuff he has in his collection, stuff he has for trade or sale etc.

I was wondering which kind of licence it would be if i wanted to try to develop same kind of application with Evolutivity ?

Anyway, you get my vote of 5 - too many developers still continue to focus on IDE productivity only, not focusing on automatic code/UI generation where the major productivity is ! Thanks

I have a new design in mind that I started prototyping. Basically, I want to generate the HTML for the UI on the client instead of the server.
I need to re-write the HTML generation in JavaScript (maybe a jQuery widget), that would allow Evolutility UI to work w/ different implementations of the server piece in .net or PHP or else.

I will post a link to my JavaScript proto soon for you to check out and give me your comments.

I didn't think of licensing. Would you be OK making your implementation open source under MIT license?

This is a great application but I wonder it support the primary key type of uniqueidentifier with default value is newsequentialid()of sql server 2005. if not, do you intend to support in the next version of Evol?
Thank you

Thanks for your feedback. Currently Evolutility can only use for primary key an auto-incremented number column called "ID". Adding support for other types of primary key would be quite a development effort. I do not think I will find the time to implemented it.

Other people have asked for more advanced primary keys. If you want to add the feature please let me know. Thanks.

Hi, for those that do this daily can really apreciate that this is some awesome ground breaking stuff.

I had already started designing something like this for .Net WinForms before I found this. Its using the DevExpress layout control so the user/developer can jump right in & customize the layout at runtime at the click of a button (still in early stages)

Are there any plans for a WinForms version of Evolutility? (if so I'm willing to help ).
Cheers,
Nez

The most important change is that the code base is now in C# instead of VB.net. This will not change anything for end-users but will change many things for developers interested in modifying the code and participating in our open source project.