TPDA | Tutorial - Creating a simple application

The table structure and SQL command is the same for all three RDBMS's of choice. The differences came from the mechanism used to generate new primary keys.

CREATE TABLE computers (
      id_comp    INTEGER NOT NULL
    , user_name  VARCHAR(25)  -- the user of the computer
    , net_ip     VARCHAR(20)  -- IP
    , acq_date   DATE         -- acquisition date
    , processor  VARCHAR(100) -- processor type
    , ram        VARCHAR(100) -- RAM type and amount
    , hdd        VARCHAR(100) -- HDD vendor and capacity
    , os         VARCHAR(100) -- operating system(s)
    , graphics   VARCHAR(100) -- graphics_card
    , units      VARCHAR(100) -- other units
    , monitor    VARCHAR(100) -- the type of the monitor
    , fdd        SMALLINT     -- has a FDD?
    , comments   VARCHAR(100)
    , CONSTRAINT pk_computers_id_comp PRIMARY KEY (id_comp)
);

Note: Minimum requirements regarding the table structure: the table must have a primary key of type integer so we can generate it automagicaly, and at least one other column, so we can store something useful in it. The column names and types can be changed with no other restrictions as you like.

For Firebird create a generator:

CREATE GENERATOR g_computers_id_comp;

For PostgreSQL, a sequence:

CREATE SEQUENCE g_computers_id_comp;
ALTER SEQUENCE g_computers_id_comp OWNED BY computers.id_comp;

For MySQL, a simulated sequence:

CREATE TABLE g_computers_id_comp (id INTEGER NOT NULL);
INSERT INTO  g_computers_id_comp VALUES (0);

We don't have any code or computed columns in the table, so we don't need a view. I usually use VIEWS for displaying information in the screen for columns that belong to other referenced tables, with the help of SQL joins.

First step is to generate a XML template file with column information from the table. The following command will (hopefully) do that for us with little effort:

% cd tpda
% perl tools/tpda-gen_xml.pl -c ../tpda-apps/computers/conf/computers-xx.xml \
     -u username -p password -t computers

Replace username and password with your login information and xx with the appropriate string (fb|pg|my) depending on the database you use.

Here -t | -table is the option for the table name to use for the new screen and -c | -config is the option for the path and file name of the configuration to be used. The config file, among other things, contains the settings of the connection to the RDBMS to be used.

Next based on the setting from the XML file we just have created, we can make a basic screen and later modify it as we like so we don't need to start from scratch.

% perl tools/tpda-gen_scr.pl -f computers.xml

We do a quick test of the screen to see how it looks like, with another tool in our toolbox:

% perl tools/tpda-test_scr.pl -s Computers.pm

Press the first toolbar button to load the screen.

The generated screen can be used as is but we can make some small customizations, like changing the labels of the entries, reorder the entries or even split the frame in smaller frames, all this just by editing the XML file. Of course, we need to regenerate the screen every time the XML changes.

Let's experiment a little.

<metadata>
  <topwin name="inreg_p" campid="id_comp">
    <frame label="Computers at home" name="frame1" gridpos="0,0">
      <entry pos="0" label="ID" name="id_comp" ...
      <entry pos="2" label="IP" name="ip_net" ...
      ...
    </frame>
    <frame label="Test frame" name="frame2" gridpos="1,0">
      <entry pos="10" label="Monitor" name="monitor" ...
      ...
    </frame>
  </topwin>
</metadata>

If we split the frame we have to set gridpos, first is the row setting and second the column. If we want the second frame bellow the first set gridpos to 1,0, if you want it near on the right set to 0,1.

The pos elements in the XML file refer to the position of the column in the table schema, don't bother to renumber when moving a column to another position, the screen generator ignores them.

Don't change topwin element name setting, it's hard-coded in the application.

For the final look, moved the fdd entry near hdd an made it into a Check button. The Entry objects hash_ref eobj_rec must be updated to know that fdd is a Check button.

fdd => [ 'c','w',\$vfdd, $efdd, 'normal', $bg, undef, 1 ],

Also moved user_name near comp_id and acq_date near the ip_net entry.

Last step is to install the screen in the Computers application. Rename the perl module file from Computer.pm to ComputersH.pm and move it to the computers path. Edit ComputersH.pm and change the package name to ComputersH.

Here is the configuration section that we add to computers-xx.xml:

<screen id="ComputersH" style="default" find_type="default">
  <menu menubar="Computers" label="Computers"/>
  <pos linux="496x402+475+216" MSWin32="439x190+716+581"/>
  <columns>
    <column key="ccol2" width="15" name="net_ip" label="IP" sort="A"/>
    <column key="ccol3" width="25" name="processor" label="Processor" sort="A"/>
    <column key="ccol4" width="15" name="user_name" label="User" sort="A"/>
  </columns>
  <lists></lists>
  <raport script=""></raport>
  <table name="computers">
    <pk_col width="6" name="id_comp" label="ID"/>
    <generator>g_computers_id_comp</generator>
    <view>computers</view>
  </table>
  <toolbar>
    <!-- toolbar states 1=flip state, 0=unchanged -->
    <off>0,0,0,0,0,0,0,0,0,0,0,0</off>
    <sele>0,0,0,0,0,0,0,0,0,0,0,0</sele>
    <add>0,0,0,0,0,0,0,0,0,0,0,0</add>
    <find>0,0,0,0,0,0,0,0,0,0,0,0</find>
    <edit>0,0,0,0,0,0,0,0,0,0,0,0</edit>
    <idle>0,0,0,1,0,0,0,0,0,0,0,0</idle>
  </toolbar>
</screen>

There are for sure, many steps that can go wrong, but consider this project as experimental, and if you like it, than maybe you can help me improve it.

The message: (ComputersH=HASH(…)) Unimplemented screen data check! is not an error, just a warning, to remind me that I have to add data check to the screen.

When using tpda-gen_scr.pl, if you encounter a error like this below, than you, like I did at first, forget (or didn't know) to add a PRIMARY KEY constraint to the table :)

Error: Global symbol $eNONE requires explicit package name at /home/stefan/tpda/Computers.pm line 440.

Add a PRIMARY KEY, or can fix it, alternatively, follow the advice of the screen generation tool and edit XML result file and set a ID column.

Another common mistake is to misspell the name of the id attribute of the screen element in the XML configuration. This ID must match the name of the screen (the Perl module file name without the extension) and the name of the package in the module.

For MySQL, a message saying that the table doesn't exists even if you know is there, the problem might be due to the identifier case sensitivity on some platforms.