Morfik 07 - Working with Databases

From Morfikwiki.com

In this chapter we will focus on working with Morfik’s built-in database engine and see how to create tables and queries and how to work with data in the tables directly from within the Morfik IDE.  Basic usage of the IDE’s Wizards to create and customize forms will also be quickly reviewed, leaving a more detailed view of database application interface design to Chapter 5 - Working With Forms.

Contents 1 Getting Started 2 Defining a Table’s Structure 2.1 Selecting Data Types 2.2 Configuring a Column’s Properties 2.3 Selecting the Primary Key 3 Entering Data into a Table 4 Creating and Using Queries 5 Creating a Data Entry Form 6 Wrapping it up

[edit] Getting Started

Every project in Morfik WebOS Application Builder is associated with a default database that bears the same file name, with a slightly different extension (.mxd).  This means that you are creating a new database every time you create a new project.

In order to start exploring the possibilities of working with databases, the first step is to fire up the Morfik IDE and create a new project. In this chapter I’ll be using the Contacts project that I created in Chapter 2.  If you have not created it yet, please take a moment to go over the description of the New Project Wizard in Chapter 2 and create it, before proceeding.

So we proceed by selecting the "Create table in design view" option which should be the second item in the tables list, which at this time should be empty. This should bring up the table design view window where we will start to add the necessary columns to our table.  For this project I will call our first table tblContacts.  As implied by its name, this table will hold information about business contacts.

[edit] Defining a Table’s Structure

Fields, or columns, in a table define exactly what information the table will store.  In our example we will have data such as "First Name", "Last Name" and "Street Address", since we will be working with contacts.  When naming your columns you should strive to maintain consistency in style by, for example, having descriptive names and using capital letters for the beginning of each word.  The names I mentioned have spaces in them and this is a standard that Morfik fully supports.  Out of personal habit I do not use spaces in column names, so you won’t see many in this book. This is not to say that I have anything against the use of spaces in field/column names it is just that I have worked with databases that did not support this feature for many years and therefore have developed the habit of composing my names differently.

To start defining our table’s structure let’s create some columns.  Follow the steps below to create a column called ContactId as the first column of our table:

1. In the table Designer View click on the first row of the Field Name column and enter ContactId.

2. Click in the Data Type column and select AutoNumber from the dropdown list of data types.

3. Click in the Description column and type in "Contact unique identifier code."

After you take these steps, your Data Designer View should look like Figure 2.

You will find below a complete list of the fields/columns for table tblContacts.  Table 1 shows the property values of each of the fields/columns that you need to create in order to have the complete table as we will use in our sample project in the next few chapters.

Table 1 - All fields/columns for table '''tblContacts

Field	Type	Options
ContactId	AutoNumber	Field Class: Integer
FirstName	Text	Field Class: Variable Size Field Size: 32
LastName	Text	Field Class: Variable Size Field Size: 32
Company	Text	Field Class: Variable Size Field Size: 64
DateofBirth	DateTime	Field Class: Date Format: mm/dd/yyyy Default Value: now
WorkEmail	Text	Field Class: Variable Size Field Size: 64
MobilePhone	Text	Field Class: Variable Size Field Size: 25
HomePhone	Text	Field Class: Variable Size Field Size: 25
WorkPhone	Text	Field Class: Variable Size Field Size: 25
WorkStreetAddress	Text	Field Class: Variable Size Field Size: 250
HomeStreetAddress	Text	Field Class: Variable Size Field Size: 250
HomeEmail	Text	Field Class: Variable Size - Field Size: 64

[edit] Selecting Data Types

For the columns we defined in this project we’ve had examples of the AutoNumber and Text data types. Morfik, however, has a rich set of data types that you can choose for the columns that you create in a table.  In order to choose wisely it is necessary for you to know what the characteristics are of the data types available. You can see the available data types in table 2.

Table 2 - Morfik database engine data types

Data Type	Characteristics
Text	strings of letters, digits, punctuation marks and other basic symbols up to 32765 characters long
Memo	Text with arbitrary length
Number	Numbers used in mathematical calculations. Can be set as being of subtypes: long real, short real, long integer or short integer
DateTime	data and/or time values
Currency	numbers formatted for currency
AutoNumber	sequentially numbers each new record. This data type is typically used for the primary key
Yes/No	Lets you set up fields with a yes/no or true/false or 0/1 data type. Often called Boolean data type.
Picture	any form of picture, compressed or not. i.e. a JPEG, BMP, TIFF or other graphic file.
Document	any of the standard document types such as Microsoft Word, PDF, etc
Multimedia	any multimedia file such as Flash File, Audio File, Video File
Blob	any other type of object that does not fit into any of the above categories. Often called Binary Large OBject.

After creating all the fields/columns described in table 1 you should have a complete list in the Table Design View as is shown in Figure 3.

You will note that Figure 3 shows a picture of a key next to the ContactId column. This shows that this is our primary key. We discuss this more below.

[edit] Configuring a Column’s Properties

Each column in a table has a set of properties that define how the column behaves and what it can store.  The properties you can set in the Table Design View is defined by the column type and class.

For example, for DateTime columns you can enter a string in the Format property to indicate how the date should be formatted or enter the function "now" into Default Value property so that any new rows inserted will start out with the current timestamp as its value.  If you’ve entered a formatting string containing only date characters, then only the date will be visible. The column will however store the entire timestamp information: date + time.

While in Data Design View you should notice that for every column/field there is a description property that can be set directly in the grid that takes up most of the screen.  This property is intended for documentation purposes and should be used in order to make your database as self documenting as possible.

[edit] Selecting the Primary Key

Good relational database design practices dictate that every table should have a primary key.  A primary key is a column that uniquely identifies each row, allowing for no duplicate values. A table might contain more than one column that has unique values for each row, for example it might have a numeric id and a telephone.  In this case both columns are what we call candidate keys and you must choose one of them to be the primary key.

One good way to evaluate candidate keys is to think about the possibility that they might change their value over time.  For example, it is possible for a person to change phone numbers; in fact it is likely to happen over long periods of time, while a numeric id is under your system’s control and won’t change unless the system needs it to.  Comparing these two candidate keys in this light the numeric id is the better choice as a primary key.

Defining a primary key is a very important aspect of table structure design. In many databases, this task is absolutely necessary as it is the only way to uniquely identify the records in a table. Morfik, however, defines a hidden field which serves this internal task. In many cases, however, it is still useful to define a primary key. The most common way to define a primary key is to use the AutoNumber data type.

In order to set a primary key you should take the following steps:

1. In the Table Design View, select the field that you want for the primary key.

2. Click the Primary key icon on the General toolbar, or right-click and select "Primary Key"

3. If this was performed successfully a key symbol should appear to the left of the field name as you can see in Figure 3.

4. After you select the primary key and have finished entering the rest of your table fields you should save the table. Click the Save button on the Table Design toolbar to save your work.

5. After saving the Table, you can either switch to the Datasheet View (by selecting the Data Tab, or clicking on the Table icon and selecting Data view from the pull-down menu) or you can close the table (by clicking the close window icon)

[edit] Entering Data into a Table

After you have created a table, one of the first things you might want to do is to populate it with data of your own.  The Morfik IDE offers you all the necessary support for you to do that, in what is probably going to be a familiar way to most users who have ever worked with a database.

In order to enter and alter data in a table you will be using the Table Data View, which can be seen in the screenshot in Figure 4. This view allows you to see several records at once in grid form.

The Table Data View is very good for quickly entering a large number of simple records.  The tblContacts table is a bit more complex than I would like for entering data in this manner.  To enter a new record, press the Insert key. Add a few records now.

[edit] Creating and Using Queries

The Morfik WebOS AppsBuilder IDE offers a broad range of features and tools specifically created to allow for the easy construction, utilization and re-utilization of queries. Queries can be built visually in a very easy manner and without the need of the developer to be comfortable with the use of SQL language constructs.

The AppsBuilder borrows a concept from Microsoft’s popular access database application and allows you to create and save queries independently for any programming language code that might come to use.  In the project explorer select the Queries option and you will see a list of all queries created in your project.

In Figure 5 you can see a screenshot of the Morfik AppsBuilder IDE with Query Designer View open, editing a pre-existing query.  In this particular case it is one of the queries from the Northwind demo application that accesses information from three different tables.

As you visually create and edit your query, the AppsBuilder IDE will be generating and maintaining SQL code, behind the scenes.  If you are familiar with SQL language, you can see the SQL code that is generated for each query.  The SQL code for the query that appears in Figure 6 can be seen in the screenshot of the Query Designer’s SQL View, in Figure 6.

[edit] Creating a Data Entry Form

Creating a form for data entry can be a tedious task, especially when you have a lot of fields in which to store data. However the Morfik IDE has a wizard to take the pain from such a mechanical task.  Let us create a data entry form for our tblContacts table using this wizard.

While in the Forms document view in the Project Explorer, choose the first option: "Create form by using wizard".  You can see this option highlighted in Figure 7.

On selecting the option to create the form through the wizard you are directed through a series of very simple steps which will give you a ready-to-use form at the end.  In the screenshots displayed in Figure 8 you can see the different steps in this process.

When the Form Creation Wizard offers the choice of linking a form to a data source, it can be selected by clicking on the button to the right of the edit box. This will bring up the Data Sources window as shown in Figure 11.

If the tblContacts table does not appear, you need to go back and save the table before continuing. One way to do this is to close the table by selecting Close on the File menu and then you will be prompted to save the table.

In this particular case we wish to create a Single Form since we want to create a view for editing a single contact’s data within our application.

If the option to customize the controls which will be created by the new form wizard is selected a special dialog will be shown when you select the Customize button that will allow you to select the type of control for each field. This dialog can be seen in Figure 16.

In this example the DateofBirth field will be represented by a DateTimeEdit control instead of the default TextEdit control.

And so, at the end of this process we get a complete and fully functional form for editing Contact information.  This form will have a clear and simple layout, which you can then customize to your needs or to match the specific layout requirements of your application. You can see the preview of this form by right-clicking on the form designer and selecting preview.   What you see should be very much like what you can see in Figure 18.

Since we selected that option, the Create Form Wizard will have added a navigation bar to this form. The navigation bar appears in the footer band and will be visible if you scroll down on the page. In Figure 19 you can see the navigation bar as I chose to configure it, from within the wizard.

As shown previously, in step 5 of the Create Form Wizard you are given the option of having a navigation bar, or not.  At that point you can click on the customize button and actually choose the options that will be available to the user on the navigation bar and customize its look and feel.  What you see in Figure 10 is the default look with small, flat and round buttons.

If you chose to open your form in a browser, this is the same as choosing View in Browser from the View menu. The application will be compiled and your form will come up inside the browser.

If you try to re-run the application using the Run option or pressing F9, you will get a blank page. This is because the default form to load is the Index form, not this one. Since we have done nothing to the Index form yet, you will get a blank screen for your viewing pleasure.

[edit] Wrapping it up

The Morfik IDE offers a lot of great features for the creation of database applications.  From creating a database’s structure to defining how data is accessed and creating the presentation layer of your applications, nothing is left out.  In future chapters, such as Chapter 5 - Working with Forms and Chapter 6 - Working with Reports, we will be addressing other topics related to database applications, in more detail as they are needed.