The No-Code Database

This database will keep track of RSS files I have subscribed to. I will track two pieces of information for each file: the URL where the latest copy of the file can be retrieved and the name of the RSS feed.

I'll dive right into creating the database without any further discussion. Create a new REALbasic desktop application in the Project Editor.

To create a new database, Control+Click (or right-click) to get the ContextualMenu. Select Add, Database, New REAL SQL database to create a new database, as shown in Figure 8.1. If the database already existed, you would just select Open REAL SQL Database.

Figure 8.1. Create a new REALSQLdatabase.

When you do this, you will be prompted to name the database file and to select a folder into which it should be saved. In this example, I will name the database file RSSSub. The file will be saved with an extension of .rsd. After the file is saved, you will need to create the structure for the database. Databases are organized into tables and each table consists of a collection of fields that are used to contain specific data. A table is a collection of related fields, and in this example, only one table is used. The fields themselves are typed (which means you have to assign a data type to them, like integer or Boolean), but SQLite uses something called affinity typing, which really means that the type of the data used doesn't matter. Setting a type for each field is important, however, if you plan to migrate to a different database plug-in that uses more traditional typing schemes. Setting the type for each field can also be useful for future developers to understand what your intentions were for a particular field.

Field Types: Integer VarChar Boolean Date Time TimeStamp Double Binary

When you create a table like this, either programmatically or through the user interface, you are creating a table schema that documents the structure of the table. After the database is established, actual data will be input and the data that is added will be called a record. A record has values for the fields defined in the table schema. A record has a similar relationship to a table that a class instance has to the class. Records are sometimes referred to as rows, and the individual fields in the record are called columns. In different places, REALbasic uses the terms field and column to describe the same thing.

The RSSSub database should be available in the Project Editor. To edit the database schema, you will need to double-click the database to get to the schema editor. To add a table to the database, click the Add Table button in the Database editor, as shown in Figure 8.2.

Figure 8.2. Add tables to a new database.

When you add a new table, an untitled database will appear in the Table Editor. You can select it and modify its name in the Properties pane in the right column. I will call this table Subscriptions.

After the table is created, the next step is to add fields. In the Database editor, this is done by clicking the Add Column button. Each field has four properties to set: Name, Type, Primary Key, and Mandatory. The Name is the name of the field, which is used when interacting with the database. Type is one of the eight field types I listed earlier. When selected, the Mandatory CheckBox indicates that this field must have data in it. If the field is empty, you cannot create a new record.

The Primary Key property designates one field as a primary key. This is used when relating two or more tables to each other (hence the name relational database). The primary key is a unique identifier that is used to link a record in one table to a record in another table. SQLite automatically generates a primary key for your table, so you do not need to create one yourself. Later on in the chapter I will have more samples of how tables get related and the role that primary keys play.

In the sample database, I have created a table called Subscriptions with two fields, Name and URL, both of which are VarChar and Mandatory. VarChar means a field that can hold a string of varying length. Both fields are mandatory. I have designated URL as the Primary Key. One characteristic of primary keys is that they have to be unique. Because each subscription in the database will have a unique URL, it is an appropriate field for a primary key. After the fields have been created, you must save the database, refer to Figure 8.3.

Figure 8.3. Add a Name field.

Databases can have thousands and even millions of records, and as the database gets larger, it gets harder and harder to find what you are looking for. You can imagine a library with only 10 books. If you go to this library and look for a book, it's easy to look through everything to see if you can find what you want. If the library has 1,000 books, then the task gets quite a bit harder. Libraries solve this problem by placing the books in special categories and sorting them by title or the author's name; then they provide a card catalog, which indexes the book, so that you can look it up by author, subject, title, and so on.

Databases use the same principle to speed up the search process when finding records. One of the things a database is good for is sorting data and retrieving it very quickly. To do that, it needs to create indexes of individual fields or groups of fields that work like card catalogs doafter the database finds the item in the index, the index points to the exact location in the library where the book can be found.

When you are finished adding your fields, you can click the Indexes button to designate which fields should be indexed, as shown in Figure 8.4.

Figure 8.4. Creating indexes.

One thing you will notice right away is that an index already exists. This index is for the URL field and it exists because I designated the URL field as the primary key, and primary keys are always automatically indexed. If you have not yet saved the database, the index will have not been automatically generated. If you are following along with the example and you do not see an index, go back and make sure that the Primary Key CheckBox has been checked on the URL field and that the database has been saved.

If I want to create another index, I add a column and select the field that I want indexed as shown in Figure 8.5. Then I give the index a name. In this case, I am indexing the Name field and I have named the index NameIndex.

Figure 8.5. Customizing indexes.

After creating the indexes, there are a few other things you can do. If the database has data in it, you can click the Browse button and see a list of all the records in the table, as shown in Figure 8.6.

Figure 8.6. Browsing existing data.

At this point, there is no data in the database yet, but if there were, you would click the Query button. If you're familiar with SQL, the advanced query tool is easier to use because you can just enter a SQL command. The Query dialog can be seen in Figure 8.7.

Figure 8.7. Query.

REALbasic also provides a dialog that allows you to create more advanced queries by directly typing in SQL statements. See an example of the Advanced Query dialog in Figure 8.8.

Figure 8.8. Advanced query.

After the database is created, I need to add functionality to make it useful. First, I need to provide a user interface that lets users enter new records into the database or update existing records. This part will be implemented using a DataControl control, and I will bind that control to two EditFields that will be used to display the feed's name and URL. I will also bind the control to four PushButtons that will be used to insert new records, update existing records, or delete existing records. Second, I need to provide users with a way to see a list of all the RSS feeds that have been subscribed to. To provide this function, I will use a DatabaseQuery control.

The next step is to return to the Window Editor, where you can use the DataControl control to provide a rudimentary front-end to your newly created database.

DataControl

The DataControl control sits at the heart of what makes databases so easy to use in REALbasic. It provides some basic navigation tools that allow the user to move forward and backward through the records, but it also makes data binding possible, which is the automatic linking of fields in tables with various controls such as EditFields, CheckBoxes, RadioButtons and more.

In the previous section, I opened a new project and created a database called RSSSub. This sample continues using the same project and assumes that the database is listed in the Project pane of your application. If it does not already exist, you will need to go back and create it as described in the previous section. The first thing to do is to drag a DataControl onto Window1. The DataControl itself is a bar at whose center is a caption that should read "Untitled" (unless you've changed it). Buttons on either side of the caption are used to navigate through the database, moving from one record to the next, or jumping to the first or last record.

When the DataControl is on Window1, select it. In the Properties pane, down at the bottom, you will see a section labeled Behavior, as shown in Figure 8.9, which allows you to designate several DataControl properties. You associate the DataControl with a particular table in a database. I have associated DataControl1 with the Subscriptions table in the RSSSub database and set SQLQuery to SELECT * FROM Subscriptions. The meaning of these values will be explained in the next section.

Figure 8.9. The DataControl Properties pane.

Data Control Properties

When the DataControl is displayed on a Window, there is a text area between the forward and back arrows. The content of that text area is controlled by the Caption property, which can be set in the Properties pane in the Window Layout Editor, or by the following code:

DataControl.Caption as String

DataControls are bound to a specific table from a specific database. Again, this can be set in the IDE, or programmatically. If your application has a database already created, the Properties pane will display it as an option to select the database in the Behavior section.

The Database property of a DataControl is an instance of a Database class, not a REALSQLdatabase. This means that the DataControl can work with any database and it will be unaffected in the future if you change the back-end database that you use. I set the values for the following two properties in the Properties pane of the IDE:

DataControl.Database as Database DataControl.TableName as String

The Database is RSSSub and the TableName is Subscriptions.

Although the control can be bound to a database and a table, unless you have a SQL query set, no data will be available. I said this was a code-free example, and at this point you might say I fudged a little, because you do need to add a little bit of SQL code to the SQLQuery property, which is displayed on the DataControl's Properties pane:

DataControl.SQLQuery as String

I want this DataControl to show all the names and URLs in the Subscriptions table, so I used one of the simplest of all the SQL statements and typed it in as the value for the SQLQuery property in the Properties pane for the DataControl:

Select * From Subscriptions

Even if you have never seen a line of SQL code in your life, you can probably deduce what is being said here. Select is the statement that is used to generate a query that returns a RecordSet. The "*" is a wildcard, which in this context means to select all the fields from the Subscriptions table. If I wanted to select only the Names from the Subscriptions table, I could write the SQL like this:

Select Name From Subscriptions

When the query is executed (which is automatically done when the Window is opened), the RecordSet property is populated with the results of the query set in the SQLQuery property:

DataControl.RecordSet as RecordSet

I'll go into more detail on the RecordSet object momentarily, but first I want to finish out the description of the remaining properties. The REALSQLdatabase supports transactions, and this means that you can make changes to a database, such as updating and deleting records and adding new records, but unless you commit the changes, they will not be saved. The following property tells REALbasic to automatically commit all changes:

DataControl.Commit as Boolean

The ReadOnly property is one that you can set to allow or disallow users the capability to change or add new records:

DataControl.ReadOnly as Boolean

The RecordLocked property indicates whether you have the capability to perform any actions on the database. This is different from ReadOnly because it isn't just a property that is set one way or the other. Depending on the database in use, the RecordLocked property may indicate that someone else is in the process of updating that particular record.

DataControl.RecordLocked as Boolean

REALSQLdatabases lock the entire database when one person has it open. Multiuser databases such as MySQL can handle concurrent users more easily and can usually lock individual rows rather than the entire database file. That is one of the biggest advantages of using MySQL or Postgresthey can efficiently handle multiple concurrent users without undo interference into their work flow. Obviously, locking only one record versus the entire table would be less disruptive.

DataControl Methods

The DataControl methods all assume that you have bound controls to the DataControl. You can do four basic actions with database records, and they are embodied in the following methods:

DataControl.NewRecord DataControl.Insert DataControl.Update DataControl.Delete

NewRecord creates a new record, but with no values. In practice, when the DataControl is bound to EditFields, the fields are cleared of text and the user can enter the data into them.

The following methods change the position of the DataControl. This provides a programmatic way of doing the same thing that the buttons on the DataControl do. Bear in mind that the DataControl control does not have to be visible, so you can use these methods in buttons to move the current record forward or backward. I like using "real" buttons instead of the DataControl because the DataControl looks like a DataControl and doesn't seem as polished to me.

DataControl.MoveFirst DataControl.MoveLast DataControl.MoveNext DataControl.MovePrevious DataControl.MoveTo(Index as Integer)

This group of methods provides access to the RecordSet associated with the DataControl.

DataControl.RecordSet as RecordSet DataControl.RecordCount as Integer DataControl.Row as Integer DataControl.FieldCount

Although the query you established for the DataControl is executed when the containing Window is opened, there are times when you need to reexecute the querywhen you insert, delete, or update records. The RecordSet associated with the DataControl is not refreshed unless you reexecute the query:

DataControl.RunQuery

DataControl Events

There are a handful of events that get triggered after the method with the same (or similar) name is called. The reason for this is twofold: because the DataControl is a control and because of how it uses data binding. The impact of data binding is that you do not need to call the Insert or Delete methods in code. This is done by binding PushButtons to particular actions. At the same time, there are times when you need to modify the action that takes place after a record gets inserted or deleted, and you do this by writing code for the particular events, rather than overriding methods.

When it comes to handling records using the DataControl, there are basically three things you can do: insert a new record, update an existing record, or delete an existing record. The following events are triggered when any of these three actions are taken; they correspond to the similarly named methods of the DataControl:

DataControl.Insert Handles Event DataControl.Update Handles Event DataControl.Delete Handles Event

A database usually consists of many records, and these records can be accessed sequentially from the DataControl by clicking the forward or back arrows on the control itself or by calling methods to move to the first record, the next record, or the previous record (these were discussed in the preceding section). Whenever one of these moves happens, either through the user interface or programmatically, the following events are triggered:

DataControl.MoveFirst Handles Event DataControl.MoveNext Handles Event DataControl.MovePrevious Handles Event

The DataControl.MoveTo method causes the following event to be triggered:

DataControl.Reposition(Index as Integer) Handles Event

The Validate property is unique among the events because it is triggered by three methods: AddNew, Update, and Insert. When the event gets triggered, an integer is passed that lets you know what action has just taken place. Based on what that says, you can write code that validates the data before you attempt to commit those changes to the database.

DataControl.Validate(Action as Integer) Handles Event 0 = AddNew 1 = Update 2 = Delete

If everything is okay with the data, return False. If you do not want the data to be committed, return true.

Binding EditFields

So far, all that I have done in this application is create a database and drag a DataControl onto Window1. Next, I need to drag two EditFields onto the Window. These EditFields will be used to display values for the name and URL of the RSS feed as saved in the database.

When a DataControl is associated with a Window, it is an extremely simple task to bind EditFields to particular fields (or columns) in the underlying table. In the Properties pane for each EditField on the Window, there is a Database Binding area at the very bottom. You can use the PopupArrow to select the DataControl you want the EditField bound to, and after that is selected, you select the individual field you want to be viewed in your EditField, as shown in Figure 8.10. Because the Subscriptions table has two fields, Name and URL, I will place two EditFields on the Windowone for each field.

Figure 8.10. EditField binding.

Binding PushButtons

In the previous section, I used REALbasic's binding to bind EditFields to a DataControl to display individual fields in a record. In this section, I will also use binding, but with PushButtons instead of EditFields. This uses an entirely different process. With EditFields, the binding properties are integrated into the Properties pane. That's because what you need to do with an EditField is fairly straightforwardyou need to display data. PushButtons are a little more complicated because you want each PushButton to perform a different action when pushed.

To use REALbasic's control binding for PushButtons, you will first need to add the binding buttons to the Layout Editor toolbar because they are not displayed on the toolbar by default. Under the View menu, select Editor Toolbar and Customize. The Customize Window Editor Toolbar dialog will be displayed. It contains two lists. On the left are listed the available toolbar buttons that are not currently displayed on the toolbar. On the right is the list of current toolbar items. If the Add Binding and List Bindings buttons are in the left column, select them and click the Add button to make them visible. After you have done this, select the OK button to get back to the Layout Editor, where you should now see the two binding buttons.

When binding EditFields, you were binding the control to a particular piece of datathe content of a field. You are binding the button to a particular action, such as inserting, deleting, or updating the database. At this point, I need to drag four PushButtons onto Window1one for each of the following DataControl methods: NewRecord, Insert, Update and Delete. The PushButtons are named PushButtonNew, PushButtonInsert, PushButtonUpdate, and PushButtonDelete, respectively.

You connect a PushButton with the DataControl by selecting both of them (hold down the Shift key and click one and then the other). After both are selected, click the Binding button, and the Window shown in Figure 8.11 is displayed. In this example, PushButtonNew is being bound to the NewRecord method of the DataControl.

Figure 8.11. Button binding.

To have a complete set of functionality, you need to create PushButton bindings for the NewRecord, Insert, Update, and Delete methods of the DataControl.

After you have created the bindings for all four PushButtons, you can click the List Bindings button on the toolbar to view the list of all bindings for that Window, as shown in Figure 8.12.

Figure 8.12. Database binding list.

Thus far, you have created a database using the IDE; then you dragged a DataControl control onto Window1 and used it to bind two EditFields and four PushButtons to the control. At this point, you have a functioning database and you can begin to enter data. When you first launch the database, there will be no data in it, so nothing will be displayed in the EditFields. To add a record to the database, enter a name and a URL for your RSS feed subscription and click the Insert button.

If you are following along and have just done that, you may be alarmed to see that the name and URL you just typed into the EditFields have disappeared. If you click the DataControl forward or backward, there doesn't appear to be any data in the database. There's a reason for this: the DataControl uses the results of the query stored in the SQLQuery property to populate the EditFields. The results of such a query are an instance of a RecordSet. When you insert a new record into the database, the RecordSet needs to be updated so that it includes the new values (RecordSets are examined in detail later on in this chapter). The query is run automatically when the Window is opened, but it is not refreshed until it is run again.

If you close your application and reopen it, you will see that the record you inserted previously is now visible. However, you'd like for it to be visible after you've inserted it, without having to stop and restart the application. This means that you need to reexecute the query after you have inserted data into the database, and the best place to do that is in the Action event of PushButtonInsert. You will also want the display to stay with the record you just added and because I do not do any special sorting, all I need to do is move to the last position of the RecordSet after rerunning the query. This means writing code, although only a little bit. The code in the Action event looks like this:

DataControl1.RunQuery DataControl1.MoveLast

You will need to do something similar when you delete or update a record as well. In these instances, you do not need to move to the last position like you did with the insert, but you do need to rerun the query and insert DataControl1.RunQuery in the Action events of both PushButtons.

DatabaseQuery Control

The second part of this project is to provide a way for users to see a list of feeds that have been subscribed to. The DatabaseQuery control is another control that, when used with REALbasic's binding features, makes for a remarkably easy way to view data. In the previous section, I used a DataControl to make it possible to browse through existing records in a database, in addition to inserting new ones, updating existing ones, or deleting them. The problem is that when browsing through them, I can see only one at a time, and sometimes it is convenient to be able to see a list of items. This is especially true in this case because I may want to view a list of RSS feeds I am subscribed to in a ListBox so that I can select one that I want to view.

The easiest way to accomplish this is to drag a DatabaseQuery control to Window1. Then set the database to RSSSub using the Properties pane, which is the database created in the previous section. Set the TableName property to Subscriptions and set the query to:

SELECT * FROM Subscriptions

Next, drag a ListBox to Window1. Because there are two columns in the database (Name and URL), use the IDE to establish a two-column ListBox; then select both the ListBox and the DatabaseQuery control and click the Bind button in the Layout Editor toolbar. After you do this, a dialog box will appear that lists the available bindings for this combination of controls. The dialog calls them connections instead of bindings, but the documentation uses both terms, so they can be used synonymously. In Figure 8.13 you will see the list of binding options. The ListBox instance is called ListBox1, and I want this ListBox to display the contents of a record as returned by the DatabaseQuery control. To do this, I need to select the first option, which reads, "Bind ListBox1 with list data from DatabaseQuery1".

Figure 8.13. Select the type of connection available for ListBox1 and DatabaseQuery1.

If there is any data in the database, the ListBox will automatically be updated to display it. That's all there is to it. There is a problem, however. Right now, our database application has a DataControl that is bound to two EditFields and can be used for browsing the database. At the same time, it has a ListBox that provides the user with a list of feeds that are currently being subscribed to. I would like the list to be more helpful than this. In fact, I don't want to have to click DataControl buttons to browse through my database. I would rather be able to select a feed in the ListBox and have the name and URL be displayed in the EditFields automatically.

The other thing I would like to do is to minimize the number of buttons I have on the Window. Two buttons in particular are troublesomethe ones labeled Insert and Update. The problem with having both of them is that both buttons should be combined into one. The user shouldn't have to decide whether he or she is inserting a new record or updating an existing one. Our application should be able to keep track of that and insert or update records appropriately without any user intervention.

Another problem is that we created the database in the IDE. Because we did it this way, our application assumes that the database file exists at the location where we created it. What happens if the database gets accidentally deleted by the user? Unless the user has the source code and access to the REALbasic IDE, the user is completely out of luck and has no way of re-creating the database file. A better approach would be to create the database programmatically, so that when the application starts, it looks to see if the database file exists. If it doesn't exist, it can create it.

To make all of these improvements, we need to take a different approach to creating and managing the database. This time around, we will make less use of REALbasic's binding and do more of the job programmatically.