Introduction
After looking at the minimalist meta-model for CRUD applications and a few examples of on the fly code generation, let's now see how we can make it easier to build and manage CRUD (Create, Read, Update, Delete) applications.
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 1 - Application definition: Gathers the application name, the entity name (as the user calls it) in singular and plural.
Step 2 - Data definition: Gathers the list of fields (labels and types).
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:
CREATE TRIGGER [EvoDico_Field_TR_Add] ON [EvoDico_Field]
FOR INSERT
AS
DECLARE @dbtable nVARCHAR(100), @dbcolumn nVARCHAR(100),
@maxlength INT,@typeID INT , @TypeSQL nvarchar(20)
SELECT @dbcolumn=dbcolumn, @maxlength=INSERTED.maxlength, @dbtable=dbtable,
@TypeSQL=EvoDico_FieldType.sqlname
FROM INSERTED, EvoDico_Form, EvoDico_FieldType (nolock)
WHERE INSERTED.formID=EvoDico_Form.id AND EvoDico_FieldType.ID=INSERTED.TypeID
select @typeID=id from sysobjects where name=@dbtable and xtype='u'
if @typeID>0
begin
if not exists (select id from syscolumns where name = @dbcolumn and id=@typeID)
if @TypeSQL='nvarchar'
exec('ALTER TABLE ['+@dbtable+'] ADD ['+@dbcolumn+'] nvarchar(
'+@maxlength+') NULL')
else
exec('ALTER TABLE ['+@dbtable+'] ADD ['+@dbcolumn+'] '+@TypeSQL+' NULL')
end
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.
| <nobr>Download the latest version of Evolutility at | |
