Kexi in Use

 in
Kexi is an integrated data management application and forms part of the KOffice suite. It can be used for creating database schemas, inserting data, performing queries, processing data and producing reports.

A possible bug in the script shown in Listing 1 is that it assumes there are no current records in the table, and it creates primary keys starting at 1. It is okay to run the script once, but if it is run again, it tries to overwrite records that have an ID matching what it is trying to insert. To make it more robust, it first needs to find out the current maximum of the ID field (this would be a good exercise to get used to writing scripts).

When executed from the script toolbar, the script gathered 11 records worth of data, which is visible from the Table Data View (Figure 3).

Figure 3. Table Data

It's worth pointing out that the above script took a lot of trial and error, as it is not initially obvious that it is possible to import extra libraries or use Kexi-specific functions. The documentation needs work to make this easier for new users, and submissions are very welcome at the KDE Userbase Web site.

Sort the Data, Create a Query

At the moment, the data is ordered in the order in which it was extracted from KMail. Because I need it to be in ascending date order, I created a query to sort it. From the Create tab, this time I chose Query. I wanted all fields except the auto-incrementing primary key, so I set it up as shown in Figure 4.

Figure 4. Query Design

Switching to Data View executes the query and displays the results (Figure 5).

Figure 5. Query Data

I saved the query as qryStats for use in a report.

Bringing It Together with a Report

A new feature of Kexi 2 is the report plugin. This allows reports to be designed and executed directly within Kexi using a GUI editor similar to report designers in other database systems, such as Microsoft Access, Crystal Reports or Oracle Reports. In Kexi 1.6, reports were available as a separate add-on from kde-apps.org, but it did not contain as many features as the version in Kexi 2, and it was not fully integrated with the application, as the designer was an external program.

Reports can be printed, saved as a PDF, exported to HTML or OpenDocument Spreadsheet files or simply remain in the database for live viewing. It is possible to save the report in all these formats because of the two-stage generation process. Reports first are rendered into an intermediate description, and this description is used to generate the final version in whatever format is selected. In a future version, it is likely that extra formats will be supported, such as OpenDocument Text and XML, suitable for further processing using XSLT.

From the Create tab, I choose Report to create a blank report with a single “Detail” section. The structure of a report is based around Sections, which can be page headers or footers, report header or footer, or Group sections where data is grouped on a field value.

Initially, all I want is a simple tabular view of the data, so all the fields will go into the detail section, apart from a header, and the field titles, which must go either in a Page Header or Report Header. From the Section Editor on the report toolbar, I added a Report Header, and using the Report Design tab on the menu bar, I added fields and labels to create the report layout. From the Data Source tab on the sidebar, I set the reports data source to the qryStats query I created above. Finally, I set the Control Source property of each field item to the corresponding field in the query and the Caption of the labels appropriately. In the end, it looked like Figure 6, and it generated a report, shown in Figure 7.

Figure 6. Report Design

Figure 7. Plain Report

This gets the job done, but it isn't quite as “jazzed up” as I would like. It's common in desktop applications to alternate the background color of rows to make it more obvious where each set of data begins and ends, so let's try that.

I created another script, but this time set its type to Object, as it is to be associated with the report object. Report scripts are event-driven—that is, whenever a certain event occurs in the generation of the report, the associated code in the script is called. Report scripts use the features of Kross::Object, where each object in a report can be associated with a script object, making it more object-oriented in nature. Each script object can have its own variables and functions. Report objects can be the report itself or any of the report sections. To make it more clear, the final script looks like what's shown in Figure 8.

Figure 8. Statistics Script

This is quite a simple script. There is an object called detail, containing a function OnRender, which will be called whenever a detail section is rendered. The function keeps track of how many times it has been called and alternates the background color. The final line of the script associates the detail function with the detail section of the report.

Then, in the report, I set the Interpreter Type to QTScript and the Object Script property to the name of the script. It is important that the Interpreter type of both the report and script match; otherwise, the script won't be presented as an option in the Object Script list.

The generated report now looks like Figure 9.

Figure 9. Tabular Report 1

It's not so great with the white background on the fields, so I went back to the designer and changed the Opacity property of each of the fields to 0 to make them transparent, resulting in a more reasonable report (Figure 10).

Figure 10. Tabular Report 2

______________________

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState