Morfik Database Guide - Visual Database Programming
From Morfikwiki.com
This topic will provide you with a general overview of how to plan your application’s interface based on a database model you have previously constructed. In this particular case we will see how to plan a generic site interface for the publication of articles, grouped under sections. Our primary objective is to create a simple but flexible application which will allow all of the website’s contents to be edited without the requirement of changing the supporting database.
In order to achieve this goal, while maximizing the effective of Morfik AppsBuilder and the Morfik Framework we will see how to use simple but effective queries and small snippets of code to link everything together into the desired behavior for a website. Through out this topic we will be using the database that was created in the Database Modeling as the basis for our small CMS application.
Contents |
[edit] Planning a Site’s Layout
A website’s layout is generally associated with its purpose and with the aesthetic sense of the designer in charge of its creation. In our case, we will be relying mostly on practical sense and in creating a layout which can be applied to almost any kind of topic or subject about which someone might want to publish information. We chose to follow a popular layout for websites, where we have a menu of options on the left side, a header at the top with the title and informational content in a larger portion of the page.
This general layout has been quite popular with websites for many years, being practical while not the most sophisticated of designs. Figure 1 shows our sample application running in the Morfik built-in Debug browser. Each of the three areas that we have just mentioned can be seen marked with rectangles and identifying numbers.
This particular layout matches very well the information we want to present in our generic website. First there is the name of the website, which must be presented. Second there are different areas of interest for topic publication, or in our database model’s terms: sections. It stands to reason that if have topics classified into sections we might want to choose which section’s articles we want to see/read.
The new layout tools which were introduced in Morfik R2, make it quite simple to apply a layout to a page and later, if you wish change the layout to a totally different one. To that effect you can simply choose a layout from the gallery in the Page designer, as we will see further ahead in this topic. Figure 1 shows the basic layout that we wish to implement, with the final application running within the Morfik Debug Browser.
Figure 1 – Morfik CMS project running within the Morfik Debug Browser. Areas which present information from different data sources are highlighted by red rectangles.
Beyond what can be seen in Figure 1, our sample site’s home page should have a footer, which will present the website’s copyright message. The website’s footer is highlighted in Figure 2.
Figure 2 – Footer section of the home page of the
Morfik CMS sample project.
To create this layout data comes from several tables: WebsiteInfo, Section, Article and UserCat.
Information shown in the header and footer come from the WebsiteInfo table. This table always holds only one record which has the basic information about the website: Title, Subtitle, copyright message, etc. The list of topics or sections that appears to the left of the page (in the area marked with the number 2) comes from the Section table and the titles, summaries of articles and the names of authors (in the area marked with the number 3) come from two tables Article and UserCat.
[edit] Creating the Interface
The first step in creating the interface for this application is creating the main Page and applying the desired layout to it. This will be the starting point for users coming into the application/website and the starting point of our design as well.
[edit] The SiteMain Page
That starting point for our application will be the SiteMain Page. This page will effectively bring together all the interface components in the appropriate layout for displaying the MorfikCMS "Home" page.
By default Morfik projects are created with one page and three forms: Header, Footer and Content. We will be using these initial forms as part of our application. In this case, however, we need to have one more area in our layout because we need to display two different types of content in the page. We want to have a list of sections and a list of articles. The list of articles being defined by which section is selected by the user.
In order to have such a layout we need to go to the newly created page and choose to apply a layout which corresponds to what we need. In Figure 3 you can see this option selected in the gallery that is presented in the Design Tab of the Ribbon when you are in the Page designer.
This page will, by default be statically linked to just two forms, the Header and Footer forms. It will, however, dynamically load other forms into its middle sections/Subforms.
[edit] The Header Form
This form will fill the role of the site's header area and will be included in the header section/subform of the layouts of both of the application's pages. In the MorfikCMS sample project this form is the Header form and it is shown in design mode within the AppsBuilder Workspace in Figure 5.
Figure 5 – Header form of the MorfikCMS sample project.
The Header Form will be showing data from the WebsiteInfo table. In the header area, at the top, the site’s title and subtitle will be displayed in two TextLabel controls formatted for that purpose. An additional TextLabel control, with its Visible property set to false will be bound to a field of the WebsiteInfo table called HomeSection (foreign key in the WebsiteInfo table which defines a relationship with the Section table) which holds the Id of the Section whose content should be initially displayed when users access this website.
Through out the construction of this application we will use this invisible TextLabel control bound to a field which holds an identifier, many times. This is a repeating pattern in Morfik applications which is used to make this information readily available when it is needed with minimal coding.
All the content in this Form has been placed in the Header band. This design provides greater flexibility in case more information is to be added to the website's header in the future.
When the Header Form is completely loaded into the browser an event called OnReady is triggered. In this event a couple of lines of code must be inserted to display the appropriate content in the SubForm1 and SubForm2 SubForms/sections of the SiteMain Page. The following code snippet is the source code for this event in this project.
procedure ShowSection(SectionId: string); overload;
begin
CurrentSection := StrToInt(SectionId);
XApp.OpenForm('SectionContent', 'SiteMain:Subform2','"ParamId='+SectionId+'",
"OpenMode=Refresh"');
XApp.OpenForm('SideBar', 'SiteMain:Subform1', '"ParamParent='+SectionId+'",
"OpenMode=Refresh"');
end;
procedure ShowSection(SectionId: integer); overload;
begin
CurrentSection := SectionId;
XApp.OpenForm('SectionContent', 'SiteMain:Subform2','"ParamId='+
IntToStr(SectionId)+'", "OpenMode=Refresh"');
XApp.OpenForm('SideBar', 'SiteMain:Subform1', '"ParamParent='+
IntToStr(SectionId)+'", "OpenMode=Refresh"');
end;
Both implementations of the ShowSection procedure are essentially identical in functionality, differing only in the parameter type and consequently in the internal type conversions.
| Note | |
 | Both instances of the OpenForm command, in both overloaded versions of the ShowSection procedure add the "openmode=refresh" parameter. This parameter is particularly useful in this case because it is instructing the OpenForm function to request this Form from the server, instead of using a version of it previously cached by the Morfik browser side framework. This is important in the MorfikCMS, project since all content editing is done with a live site, so that all data modifications are immediately reflected to the end users. |
Note the two OpenForm commands in this procedure. These commands are responsible for showing the SectionContent Form in the SubForm2 SubForm and the SideBar Form in the SubForm2 SubForm of the SiteMain page. In this code you will notice that in the third parameter of the OpenForm command, in both cases, the value of the HomeSectionIdLabel control’s caption is passed as a Form parameter. This is done so that the content form the correct Section can be shown in these Forms. Both Forms have as their data sources queries which require as parameter the Id of a section.
Just before requesting the Forms which will be shown in the two central areas of the SiteMain Page this code stores in the CurrentSection variable the integer value of the identifier of the initial section to be displayed. We will see further in this chapter how this variable is used to track which section is currently being displayed in the browser.
[edit] The Footer Form
The Footer Form is linked to the WebsiteInfo table, in order to retrieve the copyright message which is displayed in that area. This is a very slim form which displays just a copyright notice and an option for Site Management, once the user is signed-in more on site management will be seen in other topics of this reference.
[edit] The SideBar Form
The SideBar Form is opened into the left SubForm (SubForm1) of the application's main Page: SiteMain. This form will be responsible for navigation through all the end user accessible content in the website.
Content displayed in the SideBar Form is divided through the Header and Details bands. In the Header band a single option will be presented to return to the startup content of the home page. In the Details band an option for each of the Subsections of the current Section will be displayed. Note that the options in both the Header and Details bands have been made to look the same and to the end user will appear to be a part of the same options set.
Figure 7 – The SideBar Form seen in Design mode within the Morfik Workspace.
The need to actually create the separate options in the Header and Details bands comes from the fact that they use information from different sources to choose which content to display in the SubForm2 section/SubForm of the SiteMain Page.
The following is code snippet is the event handler associated with the OnClick event of the TextLabel control used to represent the Home Option.
Procedure SideBar.HomeOptionClick(Event: TDOMEvent);
Begin
ShowSection(Header(Xapp.Forms['Header']).HomeSectionIdLabel.Caption);
End;
You might note that the code in this snippet is very similar to the code in the OnReady event of the Header Form. This is due to the fact that they are essentially doing the same thing: requesting that content from a particular section be displayed in the SubForm1 and SubForm2 Sections/SubForms. In this particular case they are even requesting the very same section.
The event handler for the Section option is just a little different due to the necessity of establishing which of the displayed options (one for each section in the Section table) was clicked on. The following code snippet is the source code for the OnClick event of the TextLabel control which is used to represent the Section Option.
Procedure SideBar.SectionOptionClick(Event: TDOMEvent);
Begin
SectionIdLabel.BandIndex := GetEventSource(Event).BandIndex;
ShowSection(SectionIdLabel.Caption);
End;
To establish which value should be used in the call to the ShowSection procedure this event uses the BandIndex property of the control which triggered the event. Once the BandIndex of the control whose value we want to retrieve has been set to the same value as that of the control which triggered the event, we can comfortably use it in requesting that the section be show.
[edit] The SideBar Data Source
The SideBar Form is connected to the GetSubSections query which takes one parameter. This query, which is defined as shown in Figure 8, recovers information about Sections which have a specific section as their parents. In our current case it will be the Subsections of the Section requested in the call to the ShowSection procedure.
Figure 8 – The GetSubSections query definition in the Query designer of the Morfik Workspace.
Observe in Figure 8 that in the criteria row for the ParentId field the word ParamParent has been typed and that in the IsPublic field the value of 1 as been entered. The ParamParent string is understood by the AppsBuilder Workspace to be a parameter for this query which will be provided at runtime while the literal value 1 is understood as a constant which will always apply. The value of 1 in the IsPublic field means that we only want records which have been marked as Public.
When this query is tied in as the data source for the SideBar Form, its ParamParent parameter is automatically added as a parameter of the SideBar Form. Figure 9 shows the parameter list for the SideBar Form where the ParamParent identifier is present as the last member of the list.
Figure 9 – Parameter list for the SideBar Form, showing the parameter of its data source which has been added as its own.
When the SideBar Form is loaded and the ParamParent is part of the parameter string received, it requests the execution of the Query with the corresponding parameter value.
[edit] The SectionContent Form
This Form is actually very simple since most of the work done to display information within the SubForm2 of the SiteMain Page is actually delegated to another form. Figure 10 shows the SectionContent Form in Design mode within the Morfik AppsBuilder Workspace.
Figure 10 – The SectionContent Form in Design mode.
The SectionContent Form will show in its Header band the section’s title in an appropriately formatted TextLabel control and hold the value of that Section’s identifier in the an invisible TextLabel control, using the same implementation pattern used in the SideBar Form.
When this Form is completely loaded in the Browser its OnReady event will be triggered, as seen when discussing the Header Form. It is in this moment that another Form will be opened within the ArticleArea SubForm which completely fills SectionContent’s Details band.
The following code snippet contains the full source code for the OnReady event handler of the SectionContent Form.
Procedure SectionContent.WebFormReady(Var Ready: Boolean);
Begin
OpenForm('ArticleContent', 'SectionContent:ArticleArea',
'"ParamId='+SectionIdLabel.Caption+'"');
End;
[edit] The SectionContent Data Source
The SectionContent Form has its data source defined as the GetOneSection query. This query recovers all the particular information of a single section specified as its parameter. Figure 11 shows the definition of the the GetOneSection query.
Figure 11 – Definition of the GetOneSection query.
[edit] The ArticleContent Form
The ArticleContent Form is shown inside the Details band of the SectionContent Form and is actually responsible for displaying the titles, authorship and summaries of the articles available in a specific section. Figure 9 shows the ArticleContent Form in Design mode within the Morfik AppsBuilder Workspace.
The ArticleContent Form does not have a Header since it is embedded inside the SectionContent Form which does present a Section header. It does however have a Footer band in which it displays a NavigationBar.
The NavigationBar is necessary in this case because naturally the number of articles in a single section will be more than would be desired in a single page. In this case we result to what is commonly know as "paging" which is a technique which allows us to specify how many records will be shown in each "page". In order to customize how the paging will work the PageSize of the Form should be set to a number which provide the best result for each specific application. In the case of MorfikCMS, as a general content management application, the default value of 10 was considered adequate. This means that ten records will be presented in each "page".
Figure 12 – ArticleContent Form in Design Mode.
[edit] The ArticleContent Data Source
This Form is responsible displaying the Title and Summary of each article, as well as the author’s full name. Since the Article table does not have the author’s name this Form needs to be connected to a data source which is a query which retrieves data from both the Article and UserCat tables. In this case the ArticleContent Form is using the GetSectionArticles query as its data source.
Figure 13 – GetSectionArticles query definition.
This query recovers all the basic information that might be required from the Article table, plus the article’s author full name from the UserCat table. This query, as the one which have been used in the SideBar and SectionContent Forms takes one parameter which is the Section for which we want to recover the articles. In figure 13 the presence of the string ParamId in the criteria row of the SectionId field indicates that is the parameter which defines what to retrieve.
As with the other Forms, once the ArticleContent Form was linked to the GetSectionArticles query, this parameter was automatically created in the Form so that whenever we call OpenForm specifying it, it is automatically passed on to the query which gets executed automatically by the Morfik Framework.
ArticleContent makes each article title into a hyperlink which, if when clicked will result in the substitution of the SectionContent, inside the SubForm2 of SiteMain Page by another Form called ViewOneArticle. The following code snippet contains the entire source code for the OnClick event for the TextLabel control which displays an article’s title.
Procedure ArticleContent.ArticleTitleLabelClick(Event: TDOMEvent);
Begin
ArticleIdLabel.BandIndex := GetEventSource(Event).BandIndex;
OpenForm('ViewOneArticle', 'SiteMain:SubForm2', '"ParamId='
+ArticleIdLabel.Caption+'","openmode=refresh"');
End;
Notice that this event’s code uses the same invisible TextLabel control pattern as in previous code samples we have looked at in this chapter.
[edit] The ViewOneArticle Form
Once the end user or visitor to the application’s website clicks on one specific article he/she wants to read in full, a complete version of the body of the article’s text must be displayed.
In order to create this display the ViewOneArticle Form is used. This Form replaces the SectionContent Form within the SubForm2 section/SubForm of the SiteMain Page presenting the complete text to the user.
Figure 14 shows the ViewOneArticle Form. Notice that this Form also contains a Footer band. In this case a small link is used to return to the previous view with the SectionContent Form exhibiting the articles of a single section.
Figure 14 – The ViewOneArticle Form in Design mode.
Figure 15 shows the website in Apple's Safari browser, with the ViewOneArticle Form being shown in the SubForm2 SubForm of the SiteMain Page.
Figure 15 – View of the MorfikCMS application displaying the full contents of the Body field.
[edit] The ViewOneArticle Data Source
The ViewOneArticle Form uses the GetOneArticle query as its data source. This query recovers all data from the one specific record in the Article table. The following snippet shows the SQL language code for this query.
SELECT ALL
"Article"."Id",
"Article"."Title",
"Article"."Body",
"Article"."DateCreated",
"Article"."DatePublished",
"Article"."CreatedBy",
"Article"."Summary",
"Article"."IsPublished",
"Article"."SectionId"
FROM
"Article"
WHERE
"Article"."Id" = ParamId
Notice that this is a very simple and straight forward query which will always return a single record from the Article table.
A closer examination of the picture in Figure 16 will reveal a small pencil icon and the word "Edit" by the title of the article. This can be used to alter the information in that specific article.
Figure 16 highlights this small detail to make it easier to notice.
Figure 16 – The Edit link by the title of an article.
Clicking on this link will result in an error message, unless the site’s visitor has previously signed in by clicking on the Sign In link at the top of the page. We will see more information about the login process in the next Chapter.
The following snippet contains the full source code of the OnClick event associated with the TextLabel control which is used to represent the "Edit" link.
Procedure ViewOneArticle.EditRecordOptionClick(Event: TDOMEvent);
Begin
If not UserIsAuthenticated Then
ShowMessage('Operation not allowed. Please, sign in first.')
Else
Begin
ArticleIDLabel.BandIndex := GetEventSource(Event).BandIndex;
OpenForm('EditOneArticle', 'POPUP', '"ParamId='
+ArticleIdLabel.Caption+'", "title=Edit Article",
"modal=true"');
End;
End;
Note that the OpenForm command in this case is a bit different from has been used elsewhere in this sample. The first difference is that the target is not any specific SubForm but the word "Popup". This is understood by the Morfik Framework as a command to display a the form as a popup "window". The Title parameter for the form will be used as the title of the "popup window" and the modal=true parameter will shade everything else in the browser limiting user interaction to the new Form.
[edit] Wrapping it up
Creating the interface for an application which is totally database driven is not a difficult task with Morfik AppsBuilder. It is actually quite a simple task, considering that an adequate database model was previously created.
In this the next couple of chapters we will look at how to handle the need to authenticate users and to update all the information in the website. These tasks must be done in way which compatible with the overall design of the application/website.
