KendoUi is used to create reactive, nice and responsive design website.
This page explains how to use minuteproject SDD - Statement Driven Development - to create admin, helpdesk, troubleshooting website.
  • SDD is one of the four pillars of productivity that offers minuteproject (alongside reverse-engineering, virtualisation, transient definition).
  • SDD uses statements: here SQL statements to create applications.
  • Statements such as queries can be parameterized, filtered, nested to experience a user centric navigation. In the SDD approach, I/O and functionality only matter, the model is not important contrary to reverse-engineering.

Minuteproject offers SDD recording facilities to store specific business statements, while minuteproject tracks that are SDD enable (such as KendoUi track) powers the generation features to ensure application creation in the specific technology.

Based on a sample model sakila shipped with mysql database, this page guides you on what application you can expect just by issuing couple of queries.
It covers:
  • the architecture used
  • how-to generate and run the application
  • how-to craft you queries with parameters, filters, chunks... to yield reusability of your business queries throughout the application

Online version

Test the generated application with the online version at http://mp-sakila-kendoui.cfapps.io

Architecture

  • KendoUi / Jquery / html / css front end
  • REST jersey to expose backend raw data
  • Service / Dao and Domain Layer

Configuration

Global configuration

Activate SDD only

Minuteproject works usually by reverse engineering database, but it can work also with statements like queries.
To disable the reverse-engineering part and work only with custom defined statements add the global target-convention 'disable-business-model-generation'.
<generator-config>
    <configuration>
        <conventions>
            <target-convention type="disable-business-model-generation" />
        </conventions>

Generation

Sample

Complete configuration example is shipped in <MP_HOME>/demo/sakila-SDD-KendoUi.xml
First ensure that mysql is up with the database sakila available.
Adapt sakila-SDD-KendoUi.xml with the correct username password to connect to your database.

Command line

In <MP_HOME>/demo run:
model-generation.(cmd/sh) sakila-SDD-KendoUi.xml

The result will go to <MP_HOME>/demo/output/REST-KendoUi/sakila

Build

The generated code is structured as a maven project.
To build the application in <MP_HOME>/demo/output/REST-KendoUi/sakila execute:
mvn clean package

Deploy

The generated code of this configuration is conceived to use embedded connection pools. So you can deploy as-is the generated war on a tomcat server.

Start your tomcat
Copy the build result (In <MP_HOME>/demo/output/REST-KendoUi/sakila/REST/target the artifact sakilaKendoUiApp.war) into <TOMCAT>/webapps

SDD in action

Simple query


Query with no parameter such as get the list of countries, cities, categories.
Those queries although trivial at first sight maybe be used later on as filters on more complex queries.

Simple query configuration

<statement-model>
    <queries>
        <!-- generic queries -->
        <query name="distinct city" id="sakilacity" package-name="masterdata">
            <query-body> <!-- dimensions column first -->
                <value>
<![CDATA[select distinct city from city]]>
        </value>
    </query-body>
</query>
<query name="countries" id="distinct-countries" package-name="masterdata" content-type="master-data">
    <query-body> <!-- dimensions column first -->
        <value>
<![CDATA[select distinct country from country order by country asc]]>
        </value>
    </query-body>
</query>
<query name="category" id="distinct-categories" package-name="masterdata" content-type="master-data">
    <query-body> <!-- dimensions column first -->
        <value>
<![CDATA[select distinct name from category order by name asc]]>
        </value>
    </query-body>
</query>
 
    </queries>
</statement-model>

Result

Queries with no parameters are executed directly (since there is no input form).

Screen level output


1_master_data_3_category.png

1_master_data_2_distinct_cities.png
1_master_data_1_countries.png


Query with parameters

In the context of SDD, parameters means mandatory fields associated to the query core body. Should a parameter be not mandatory it is called a filter (parameter).

Input parameters

<!-- actor -->
<query name="actor film" id="actor-film" package-name="technical">
    <query-body> <!-- dimensions column first -->
                            <value>
<![CDATA[select ai.first_name, ai.last_name, ai.film_info from actor_info ai, film_actor fa where ai.actor_id = fa.actor_id and fa.film_id = ?]]>
                       </value>
    </query-body>
    <query-params>
        <query-param name = "film_id" type="int" sample="1"/>
    </query-params>
    <query-display result-row-display="first_name, last_name"/>
    <actions>
        <action name="details" default-implementation="showDetails"></action>
    </actions>
</query>
In this example the input is the primary key of a the film entity. Although it works, at first sight it might not be that interested, since usually search run on more business oriented context than technical. Meanwhile, this query can be nested and used just by reference each time we have some displayed entities that contains a field holding the primary key of a film. This is a powerful mechanism (nearly OTB) to navigate ha-doc your model allowing master detail with ease.

Rendering
kendoui_sdd_actor_film.png

Enumeration parameters

Enumeration are static set of predefined parameters. Those can be passed either as parameters or filters in format of a drop down list.

<query name="film_by_rating" id="film_by_rating" package-name="film">
    <query-body>
        <value><![CDATA[select f.title, f.description, l.name, f.rating, f.rental_rate, f.last_update from film f, language l $whererating]]>
        </value>
    </query-body>
    <query-filters>
        <query-filter name="whererating" and-where-connection="where">
                        <value><![CDATA[ rating = ? ]]></value>
            <query-params>
                <query-param name="rating" type="string" sample="'xxx'">
                    <property tag="checkconstraint">
                        <property name="G" value="G"/>
                        <property name="PG" value="PG"/>
                        <property name="PG-13" value="PG-13"/>
                        <property name="R" value="R"/>
                        <property name="NC-17" value="NC-17"/>
                    </property>
                </query-param>
            </query-params>
        </query-filter>
    </query-filters>
</query>
Rendering

kendoui_sdd_film_list_filtered_by_rating.png

Query with filters


Input filters

                    <query name="customer and store" id="customer-and-store-details" package-name="customerdata">
                        <query-body> <!-- dimensions column first -->
                            <value>
<![CDATA[
select customer_id, first_name, last_name, email, a.address, ci.city, a.postal_code, co.country, a2.address as store_address
 from customer cu, address a, city ci, country co, store st, address a2
 where cu.address_id = a.address_id and a.city_id = ci.city_id and ci.country_id = co.country_id and cu.store_id = st.store_id and st.address_id = a2.address_id
$wherefirstname $wherelastname
]]>
                            </value>
                        </query-body>
                        <query-display result-row-display="first_name, last_name, email, address, postal_code, city, store_address, country">
                        </query-display>
                        <query-filters>
                            <query-filter name="wherefirstname" and-where-connection="and">
                                <value>
        <![CDATA[first_name like ? ]]>
                                </value>
                                <query-params>
                                    <query-param name="first_name" type="string" size="20" sample="'xxx'">
                                        <property name="like-mode" value="startsWith"></property>
                                    </query-param>
                                </query-params>
                            </query-filter>
                            <query-filter name="wherelastname" and-where-connection="and">
                                <value>
        <![CDATA[last_name like ? ]]>
                                </value>
                                <query-params>
                                    <query-param name="last_name" type="string" size="20" sample="'xxx'">
                                        <property name="like-mode" value="startsWith"></property>
                                    </query-param>
                                </query-params>
                            </query-filter>
                        </query-filters>
                    </query>
 



kendoui_sdd_customer_filters.png

Query chunk

Query chunk is a pattern that allows to plug portion of sql together, it is up to the chunk provider to guarantee that the build sql works.
It is useful when building generic statements.

Sub select query chunk example


Configuration
<query name="film_by_customer_area" id="film_by_customer_area" package-name="customer">
    <query-body>
        <value><![CDATA[
select customer_id, concat_ws (' ',first_name, last_name) full_name, email, a.address, ci.city, a.postal_code, co.country, a2.address as store_address
 from customer cu, address a, city ci, country co, store st, address a2
 where cu.address_id = a.address_id and a.city_id = ci.city_id and ci.country_id = co.country_id and cu.store_id = st.store_id and st.address_id = a2.address_id
and $country_zone
]]>
        </value>
    </query-body>
    <query-chunks>
        <query-chunk name="country_zone" sample-value="co.country_id in (select country_id from country where country in ('France', 'United Kingdom', 'Italy', 'Belgium', 'Germany'))">
            <query-chunk-value name="europe" >
                <value>
                <![CDATA[ co.country_id in (select country_id from country where country in ('France', 'United Kingdom', 'Italy', 'Belgium', 'Germany')) ]]>
                </value>
            </query-chunk-value>
            <query-chunk-value name="north_america" >
                <value>
                <![CDATA[ co.country_id in (select country_id from country where country in ('Canada', 'Mexico', 'United States')) ]]>
                </value>
            </query-chunk-value>
            <query-chunk-value name="south_america" >
                <value>
                <![CDATA[ co.country_id in (select country_id from country where country in ('Argentina', 'Brazil', 'Columbia')) ]]>
                </value>
            </query-chunk-value>
            <query-chunk-value name="africa" >
                <value>
                <![CDATA[ co.country_id in (select country_id from country where country in ('Algeria', 'Morocco', 'Tunisia', 'Cameroun', 'South Africa')) ]]>
                </value>
            </query-chunk-value>
        </query-chunk>
    </query-chunks>
</query>

Rendering
kendoui_sdd_query_chunk_country_area.png

Query chunk ordering and sorting example
<query name="sales_by_film_category_filter" id="sales_by_film_category" package-name="dashboard"
    category="column-chart">
    <query-body>
              <value><![CDATA[select * from sales_by_film_category order by $orderby $sort]]></value>
    </query-body>
    <query-chunks>
        <query-chunk name="orderby" sample-value="category">
            <query-chunk-value name="category" value="category"/>
            <query-chunk-value name="total_sales" value="total_sales"/>
        </query-chunk>
        <query-chunk name="sort" sample-value="asc">
            <query-chunk-value name="ascending" value="asc"/>
            <query-chunk-value name="descending" value="desc"/>
        </query-chunk>
    </query-chunks>
</query>
Rendering
kendoui_sdd_query_chunk_category_filter.png

Referencing queries

Dynamic drop down list


If a drop down list is positioned as a parameter, it means it is mandatory, so by default the first value is picked. If a drop down list is positioned as a filter, it is optional, so no default value is proposed.

Filter drop down list

In this sample the filter $wherecountry is replaced by a selection on the country id. The country id comes from the value of a drop down list of countries. The list of countries has been defined earlier and referenced by 'countries'.
                    <query name="customer and store by country" id="customer-and-store-by-country-details" package-name="customerdata">
                        <query-body> <!-- dimensions column first -->
                            <value>
<![CDATA[
select customer_id, concat_ws (' ',first_name, last_name) full_name, email, a.address, ci.city, a.postal_code, co.country, a2.address as store_address
 from customer cu, address a, city ci, country co, store st, address a2
 where cu.address_id = a.address_id and a.city_id = ci.city_id and ci.country_id = co.country_id and cu.store_id = st.store_id and st.address_id = a2.address_id
$wherefirstname $wherelastname $wherecountry
]]>
                            </value>
                        </query-body>
                        <query-display result-row-display="full_name, email, address, postal_code, city, store_address, country">
                        </query-display>
                        <query-filters>
                            <query-filter name="wherefirstname" and-where-connection="and">
                                <value>
        <![CDATA[first_name like ? ]]>
                                </value>
                                <query-params>
                                    <query-param name="first_name" type="string" size="20" sample="'xxx'">
                                        <property name="like-mode" value="startsWith"></property>
                                    </query-param>
                                </query-params>
                            </query-filter>
                            <query-filter name="wherelastname" and-where-connection="and">
                                <value>
        <![CDATA[last_name like ? ]]>
                                </value>
                                <query-params>
                                    <query-param name="last_name" type="string" size="20" sample="'xxx'">
                                        <property name="like-mode" value="startsWith"></property>
                                    </query-param>
                                </query-params>
                            </query-filter>
                            <query-filter name="wherecountry" and-where-connection="and">
                                <value>
        <![CDATA[co.country = ? ]]>
                                </value>
                                <query-params>
                                    <query-param name="country" type="string" size="20" sample="'xxx'">
                                        <query-param-link sdd-query-name="countries" field-name="country" field-key="country"/>
                                    </query-param>
                                </query-params>
                            </query-filter>
                        </query-filters>
                    </query>
Rendering

kendoui_sdd_filter_drop_down_list.png



Subquery affectation


<query name="rental" id="rental-by-customers" package-name="rental">
    <query-body>
        <value>
<![CDATA[
select count(*) number from rental
$wherecustomer
]]>
        </value>
    </query-body>
    <query-filters>
        <query-filter name="wherecustomer" and-where-connection="where">
            <value>
        <![CDATA[customer_id = ? ]]>
            </value>
            <query-params>
                <query-param name="customer" type="int" size="20" sample="1">
                    <query-param-link sdd-query-name="customer-and-store-by-country-details" field-name="full_name" field-key="customer_id"/>
                </query-param>
            </query-params>
        </query-filter>
    </query-filters>
</query>
Some parameters can refer to other query and pass parameters:
The field customer is populated by the affectation of the result pick-up in the query 'customer-and-store-by-country-details'.
What is displayed is what the query 'customer-and-store-by-country-details' output in the field 'field-name'.

If 'field-name' and 'field-key' are not the same then the affected field is read-only otherwise it is writable.

kendoui-sdd-subselect.png

Multi-select criteria case

Multi select are useful when multiple values of an enumeration (dynamic or static) is used as parameters or filters
The SQL-jdbc syntax slightly differs since instead of using '?' as parameter, '?...' in used to specify a multiple parameters

The parameters comes from a dynamic subselect based on the formerly defined 'distinct-categories' query.

<query name="sales_by_film_among_category_list" id="sales_by_film_among_category_list" package-name="dashboard"
    category="pie-chart">
    <query-body> <!-- dimensions column first -->
                            <value>
<![CDATA[select category, total_sales from sales_by_film_category where category in (?...) order by category]]>
                       </value>
    </query-body>
    <query-params>
        <query-param name="categories" type="string" is-in-clause="true" sample="'hx','tx'">
            <query-param-link sdd-query-name="distinct-categories" field-name="name" field-key="name"/>
        </query-param>
    </query-params>
</query>
Rendering

kendoui-sdd-film-sales-among-category.png

Actions

The output of some queries can be used as the input of other queries/statements.
Below the records of a list films can be linked to some actions that propagate the id of the film into the next query 'actor-film' defined in the configuration as a input parameters.
This way of using actions enable fast and had oc model navigation.
<query name="Film list simple" id="film_list" package-name="film">
    <query-body> <!-- dimensions column first -->
                            <value>
<![CDATA[select * from film_list $wheretitle $wherecategory order by title asc]]>
                       </value>
    </query-body>
    <query-display result-row-display="title, category, description, length, rating, price"/>
    <actions>
       <action name="details" default-implementation="showDetails"></action>
       <action name="actors" query-id="actor-film">
               <action-field-map>
                 <!-- java convention like field name not sql -->
                <action-field-map-entry output-field="fid" to-action-field="filmId" />
              </action-field-map>
           </action>
    </actions>
        <query-filters>
            <query-filter name="wheretitle" and-where-connection="where">
                <value>
<![CDATA[title like ? ]]>
                 </value>
                <query-params>
                    <query-param name="title" type="string" size="20" sample="'xxx'">
                        <property name="like-mode" value="startsWith"></property>
                    </query-param>
                </query-params>
            </query-filter>
            <query-filter name="wherecategory" and-where-connection="where">
                    <value>
<![CDATA[category = ? ]]>
                     </value>
            <query-params>
                <query-param name="category" type="string" size="20" sample="'xxx'">
                    <query-param-link sdd-query-name="distinct-categories" field-name="name" field-key="name"/>
                </query-param>
            </query-params>
        </query-filter>
    </query-filters>
</query>


Detail action

kendoui_sdd_film_list_filtered_by_category_action_actors.png

Dashboard

KendoUi ships quite usefully dashboard widget can be smoothly used in minuteproject SDD configurations


All-in-one Dashboard-master-details-actions

Dashboard-master-details-actions paradigm the easy way.
To specify a query will be rendered as a chart add the node category with 'column-chart', 'pie-chart', 'bar-chart'.
The action is applied on the graphical area that pass the query 'sales_by_film_and_film_category' the parameter category to further navigate.
<query name="sales_by_film_category_filter" id="sales_by_film_category" package-name="dashboard"
    category="column-chart">
    <query-body> <!-- dimensions column first -->
    <value>
<![CDATA[select * from sales_by_film_category order by $orderby $sort]]>
         </value>
    </query-body>
    <query-chunks>
        <query-chunk name="orderby" sample-value="category">
            <query-chunk-value name="category" value="category"/>
            <query-chunk-value name="total_sales" value="total_sales"/>
        </query-chunk>
        <query-chunk name="sort" sample-value="asc">
            <query-chunk-value name="ascending" value="asc"/>
            <query-chunk-value name="descending" value="desc"/>
        </query-chunk>
    </query-chunks>
    <actions>
        <action name="sales info" query-id="sales_by_film_and_film_category">
            <action-field-map>
                <action-field-map-entry output-field="category" to-action-field="category"></action-field-map-entry>
            </action-field-map>
        </action>
    </actions>
</query>
 
Rendering
sales-by-film-category-filter-from-dashboard-to-details-and-actions.png

Misc

Enable xls export

KendoUI xls export feature depends on JSZip to be installed (manually - no bower integration -) in /REST/src/main/webapp/js

Menu

All your queries can be accessed directly by URL or via a menu

kendoui-sdd-menu.png

Cloud integration

Cloudfoundry

Minuteproject generates cloudfoundry artifacts
  • manifest.yml
  • cloudfoundry scripts
  • embedded tomcat connection pool

Cloudfoundry manifest is filled with reference to
  • cloudfoundry name
  • database name
  • path to built-artifact

<property name="cloud-platform" value="cloudfoundry"></property>
<property name="cloud-application-name" value="your-application-root"></property>
<property name="cloud-databases-name" value="your-application-db"></property>
 
The path to built-artifact is given based on the track (here : REST-KendoUi)

If you connect to pivotal you can host your generated application.
The online version of the generation is available at http://mp-sakila-kendoui.cfapps.io/

Multiple Databases

Minuteproject has the ability to handle persistence against multiple databases

TODO

Swagger API

The REST generated backend use swagger API and model annotation.
The API documentation is available at /${context}/data/api-docs
On the generated online application it is http://mp-sakila-kendoui.cfapps.io/data/api-docs


KendoUI professional

Minuteproject does not bundled KendoUI, but refers to the online distribution.
Some KendoUI widgets are bound to pro license, please check when bundling KendoUI into your application which license terms apply.