04 August 2011 ~ 1 Comment

Superbase to SIMPOL App Conversion

I would like to discuss the approach taken to convert TaxiPlus into a new SIMPOL-based product called Taxi Pro Gold. The order of execution was roughly this:

  • Analyze the original
  • Initial Conversion
  • Create project using the Address Book sample as the basis.
  • Add into the project all the pieces.
  • Ensure that data-entry, the menu, the tool bar, etc. all work and allow record selection, creation, modification, saving, and deletion.
  • Clean up all the forms to look consistent. Add in whatever buttons are required and get them working.
  • Examine the reports. Convert the graphic reports using the conversion tool, then clean up the XML to be correct.
  • Add the reports to the system. Add dialogs for preparing the filters. Test the reports.
  • Final testing and clean up.
  • Create handbook and registration system.
  • Produce and test installer.

Analysis

Using the Superbase Project Manager (added into the product with the 2001 release), we analyzed the original. This process consisted of assessing the number of database files, forms, quick reports, graphic reports, and the overall amount of code. As part of this we established how much of the code was being used to manage the Superbase package, and how much was used to actually do the work of the package. These were split apart, so that we could concentrate only on the code that actually did the work of the package. The internal management would be left to the application framework library.

Once the analysis was complete, the next phase was the initial conversion.

Initial Conversion

Converting the Databases

The first step in the conversion was to convert the databases. The utility program “Superbase to SIMPOL Database Converter” is found in the installation program group in the Utilities sub-folder. Running that produces a GUI-based conversion tool that allows the batch conversion of Superbase database files into SIMPOL database files. The recommended approach is to use one database file per container, this is for performance reasons when doing maintenance. In this case, we did not want any of the data, so we chose not to copy the data across, unticking the “Copy data” check box.

Superbase to SIMPOL Database Converter

Superbase to SIMPOL Database Converter

Once the databases were converted, we also needed to deal with the field level formulas for initializing constant values, calculating field values, and validating field values. Since field level calculations, validations, and constants are not supported by SIMPOL, there is a different approach that is used. An event handling function is created and assigned to the onnewrecord event to handle constants and another for theĀ  calculations and validations that is assigned to handle the onsaverecord event.

Converting the Database Formulas

These functions can be created mostly automatically, using another conversion tool, called sbd_formula_rdr.smp. This is a command line program that is located in the Utilities directory beneath the main SIMPOL installation directory. It takes as arguments, a starting path and an output file name. It will then look at every *.sbd file in that directory(it does not recurse into subdirectories), and will create functions in the target file for each formula type (constant, calculation, and validation) with entries for each field that has a formula of said type. The resulting output will be a mixture of SIMPOL and unconvertable SBL, which will have to be converted by hand. The program looks like this:

SBD Formula Reader

SBD Formula Reader

When run against the SBAIR sample from Superbase, the output looks like this (I have truncated it here):

function city_rec_validation(type(db1record) r, appwindow appw)
 // RouteCategory.CITY LIKE "[SML]"
end function
function crew_rec_initialize(type(db1record) r, appwindow appw)
 // r!CrewID = SER (CrewID.CREW)
end function
function flight_rec_initialize(type(db1record) r, appwindow appw)
 // r!FlightNo = "SUP" + STR$ ( SER (FlightNo.FLIGHT,1),"000")
 // r!Frequency = "1234567"
end function

As you can see, the code that is output won’t run, it has to be modified by hand, still, having it all in one place, and in appropriate functions makes it easier. Also, in many cases the conversion tool will generate fairly correct code. The biggest difficulty we face here is the lack of a SER() function. In many cases, even in Superbase systems, more experienced developers have written their own function for this, since network serial number generation is complicated to get right.

We created a function for use with this conversion project, which is based on a serial number table, with a record for each database table that it manages. The first time it will go searching for the final record in the appropriate index depending on which table it is working with. If the table is empty, it will create the number 1 and store it in the newly created record for that table. From then on it will need to lock the record to ensure that only one client receives the number. Our function is called getserno() and it takes a reference to the serial number table, the name of the target table, and the application object, which contains the references to all the database tables.

The hand modified code for our functions above looks like this:

function city_rec_validation(type(db1record) r, appwindow appw)
  boolean ok
  ok = .like1(r!RouteCategory, "[SML]")
end function ok
function crew_rec_initialize(type(db1record) r, appwindow appw)
  r!CrewID = getserno(appw.app.serno, "CREW", appw.app)
end function
function flight_rec_initialize(type(db1record) r, appwindow appw)
  r!FlightNo = "SUP" + STR(getserno(appw.app.serno, "FLIGHT", appw.app), "000")
  r!Frequency = "1234567"
end function

As you can see, once the serial number strategy has been resolved, things speed up quickly. The other common problem is when people are using the Superbase LOOKUP to search for a record in another table. There is a similarly named function called lookup() in SIMPOL and it is found in the appframework.sml library. Also available through there is the getuserinput() function (similar to REQUEST 4 in Superbase) and the listpicker() function which is similar to the REQUEST 20, 24, and 25 from Superbase. The source for listpicker() and getuserinput() is provided in the uisyshelp project.

The next step, was to convert the forms.

Converting the Forms

To convert the forms, a Superbase language utility was created. It makes a number of assumptions about the target, chief among them, that the database tables are each stored in a *.sbm file with the same name as the original table, where the file name is the table name in lower case, and the table itself is stored with the table name in uppercase. The utility will run in Superbase 2001 or later, but it may not run in anything older. The interface is quite sparse, it simply asks you to select a form, then asks for the name of the target file suggesting the original form name with an sxf extension.

There are a few differences between Superbase forms and SIMPOL forms. Superbase form controls are all drawn by Superbase, which is one reason why they tend to look dated. That is also a reason why Superbase is so fast, but on modern hardware the price, not having a consistent looking UI when compared to other applications on the same operating system, is too high. The speed difference is rarely apparent. SIMPOL also does not currently support nested detail blocks, although the preparation for them is there. Nor does it support writing to the detail blocks. There are features in place for retrieving the records associated with a row in a detail block, as well as for updating that row, or even removing it from the detail block. Also, the only unlinked detail blocks available use a query to fill them.

SIMPOL also has a few useful tricks available to it. Each edit control can have an associated drop down list that is activated by the user typing a specific number of characters, similar to the drop down lists that appear in modern browsers. Every control can have a tool tip. Images can be automatically retrieved from various sources, such as URLs. There are also some additional controls that Superbase did not have, such as arcs, triangles, scroll bars, a grid and a data-aware grid.

Once we had converted all the forms, we still needed to clean them up, but that can come later. First we wanted to just see them come up in the application framework, so in the next step, we made a copy of the Address Book sample to use as a starting point.

Creating the Main Project

Now things were starting to get interesting. We copied the sample into a new directory with an appropriate project name, and began making the few minor modifications necessary to get the first form and database table up. This only took an hour, and from there we began making some design decisions and preparing the data types (objects) that we were going to use.

As an early next step, we converted the menu program from the Taxi Plus program. This was created using the Superbase Menu Designer and was still be loaded from the same source code. Fortunately there is also a converter for Superbase menu programs, so we immediately ran the converter. This is also found in the SIMPOL Utilities group, and is called “SB Menu to SIMPOL Source”. It is also a Superbase program and when run it will convert a Superbase menu program into SIMPOL source code. It also will generate placeholder (empty) functions for each of the named functions in the menu. Taxi Plus did not have an icon bar, but if it had, we would have converted it at this point too. There is no converter for icon bars, but there is a sample that is fairly easy to extend.

At this point, all there was left to do to in the conversion was defined by the functions called from the menu, the tool bar, and any buttons on the forms.

Ongoing Development

The biggest chunk was simply to get all the functions filled out from the menu and tool bar, especially those that opened up new forms, and to define the functions for the buttons on those forms. Each function was given a single function call in its body until it was done. That function call was to the notyet() function, which displays a message that the functionality is not yet implemented. The goal was to eventually have none of these left.

Along the way, we stumbled over some dialogs as well. SIMPOL only has forms, and those forms can be selected into a window, a dialog, or even a tool bar. So in some cases, where the dialog code was still fairly consistent with the original, we used the converter for dialogs to SIMPOL forms. This is also a Superbase program and can be found in the Utilities sub-folder.

During this period all of the forms were also revisited and cleaned up, given a new look and checked for consistency across the interface.

Once all of this was done, the last big chunk was there: printed forms, quick reports and graphic reports. Fortunately, the printed forms could be done using the print form library, with some minor adjustments to the converted forms.

Quick Reports and Graphic Reports

There were only a few quick reports in the package, but there were over 30 graphic reports. By clever redesign, we managed to consolidate a number of the graphic reports so that at the end, we only had 23. Still, they all had to be converted. One of the big stumbling blocks is that currently there is no front-end in SIMPOL for designing and modifying graphic reports or quick reports. These front-ends are on the current work list. The back end code, for creating them programmatically, and for saving them and loading them does exist, as does a converter for the graphic reports, which does around 95% of the work. The rest required direct manipulation of the XML file in which the reports were stored. This sounds much worse than it is. Part of the problem with writing a converter for Superbase Graphic Reports is that there is no programmatic way to access the group, aggregate calculation, and report-specific content. Superbase stores this in a separate file which most people then manipulate anyway.

One of the things we discovered, is that in the absence of a graphical report designer, it was actually faster to do all of the manipulation we wanted of the report in the Superbase Graphic Report Designer, and then when we were happy with it, to convert it.

One difference worth mentioning between Superbase and SIMPOL, is that SIMPOL always uses absolute positioning for creating reports and printed forms, so this may require adjustment in Superbase prior to conversion.

Some Screenshots of the Results

Here are some images from the finished program.

System Settings Wizard Page

System Settings Wizard Page

Taxi Pro Bookings Form

Taxi Pro Bookings Form

Taxi Pro Driver Allocation Form

Taxi Pro Driver Allocation Form

Taxi Pro Invoice Creation

Taxi Pro Invoice Creation

Taxi Pro Invoice Print Preview

Taxi Pro Invoice Print Preview

Taxi Pro Driver's Sheet

Taxi Pro Driver's Sheet

Conclusion

Overall, it was a fairly meaty job. Some of the issues that arose and had to be solved were imposed by newer versions of Windows. As of Vista it is no longer a good idea to install your data in the same directory subtree as your application. This only results in problems, because of virtualization. As such, we had to investigate the best way to do it on multiple versions of Windows. Overall, it took around what we thought it would take in terms of time, plus about 15-20%, but some of that time was because of items added during production that were not part of the original product. If we had to do it over again, we might have made a few decisions differently, but that is often the case on a project like this. Overall, the project slipped into the application framework quite well, and works the way we had hoped. There are bound to eventually be some niggles, but overall we are satisfied with this first big conversion. Now it is time to get the remaining front-end bits done to make it even easier. Still, considering that the reports had to be hand-adjusted, it only took about 2-3 weeks to convert and adjust all of the 30+ reports, including building dialogs for requesting filter parameters from the user.

One Response to “Superbase to SIMPOL App Conversion”

  1. Dan Jinks 5 August 2011 at 3:22 pm Permalink

    Thanks for the detailed conversion blog. This parallels the course suggested in the SIMPOL Getting Started doc, but fleshes it out with a real example. So far, my own conversion is going well, using the same strategy. I love the new Development IDE.


Leave a Reply