If you're new here, you may want to subscribe to my RSS feed. So that you can read the latest updates about Web2.0 tools, Making Money Online, Tips in SEO, Ajax and many more. Thanks for visiting ProgramimiCOM!

A key trick to the manipulation of HTML elements is understanding that there’s nothing at all special about how most of them work. Most pages could be made up from just a few tags that can be styled any which way you choose. The browser’s default visual representation of most HTML elements consist of varying font styles, margins, padding and, essentially, display types.

The most fundamental types of display are inline, block-line and none and they can be manipulated with the display property and the values inline, block and none.

 

inline does just what it says - elements that are displayed inline follow the flow of a line. Strong, anchor and emphasis elements are traditionally displayed inline.

block puts a line break before and after the element. Header and paragraph elements are examples of elements that are traditionally displayed block-line.

none, well, doesn’t display the element, which may sound pretty useless but can be used to good effect with accessibility considerations (see Accessible Links), alternate stylesheets or advanced hover effects.

The original default style-sheet for this site for example, manipulates a few traditionally in-line and block-line elements to fit the design.

h1 {
	display: inline;
	font-size: 2em;
}

#header p {
	display: inline;
	font-size: 0.9em;
	padding-left: 2em;
}

This enabled the title ‘htmldog.com’ and the tag-line to be displayed next to each other rather than above and below each other while maintaining optimum accessibility.

#navigation, #seeAlso, #comments, #standards {
	display: none;
}

The above code is used in the print-only styles to basically ‘turn-off’ those elements, such as navigation, which are insignificant to the single page.

display: none and visibility: hidden vary in that display: none takes the element completely out of play, where as visibility: hidden keeps the element and its flow in place without visually representing its contents. For example, if the second paragraph of 3 were set to display: none, the first paragraph would run straight in to the third whereas if it were set to visibility: hidden, there would be a gap where the paragraph should be.

Tables

Perhaps the best way to understand the table-related display property values is to think of HTML tables. table is the initial display and you can mimic the tr and td elements with the table-row and table-cell values respectively.

The display property goes further by offering table-column, table-row-group, table-column-group, table-header-group, table-footer-group and table-caption as values, which are all quite self-descriptive. The immediately obvious benefit of these values is that you can construct a table by columns, rather than the row-biased method used in HTML.

Finally, the value inline-table basically sets the table without line breaks before and after it.

Getting carried away with CSS tables can seriously damage your accessibility. HTML should be used to convey meaning, so if you have tabular data it should be arranged in HTML tables. Using CSS tables exclusively could result in a mash of data that is completely unreadable without the CSS. Bad. And not in a Michael Jackson way.

Other display types

list-item is self descriptive and displays as in the way that you would usually expect an li HTML element to. To work properly then, elements displayed this way should be nested in a ul or ol element.

run-in makes an element either in-line or block-line depending on the display of its parent. It doesn’t work on IE or Mozilla based browsers. Very helpful.

compact also makes the element inline or block-line depending on the context. It doesn’t work that well either…

marker is used exclusively with the :before and :after pseudo elements to define the display of the value of the content property. The automatic display of the content property is already marker, so this is only useful if you are overriding a previous display property for the pseudo element.

• 0 Comments

Layout with CSS is easy. If you are used to laying out a page with tables, it may at first appear difficult, but it isn’t, it’s just different and actually makes much more sense.

You need to look at each part of the page as an individual chunk that you can shove wherever you choose. You can place these chunks absolutely or relative to another chunk.

 

 

Positioning

The position property is used to define whether an element is absolute, relative, static or fixed.

The value static is the default value for elements and renders the position in the normal order of things, as they appear in the HTML.

relative is much like static, but the element can be offset from its original position with the properties top, right, bottom and left.

absolute pulls an element out of the normal flow of the HTML and delivers it to a world all of its own. In this crazy little world, the absolute element can be placed anywhere on the page using top, right, bottom and left.

fixed behaves like absolute, but it will absolutely position an element in reference to the browser window as opposed to the web page, so, theoretically, fixed elements should stay exactly where they are on the screen even when the page is scrolled. Why theoretically? Why else - this works great in browsers such as Mozilla and Opera, but in IE it doesn’t work at all.

Layout using absolute positioning

You can create a traditional two-column layout with absolute positioning if you have something like the following HTML:

<div id=“navigation”>
	<ul>
		<li><a href=“this.html”>This</a></li>
		<li><a href=“that.html”>That</a></li>
		<li><a href=“theOther.html”>The Other</a></li>
	</ul>
</div>

<div id=“content”>
	<h1>Ra ra banjo banjo</h1>
	<p>Welcome to the Ra ra banjo banjo page. Ra ra banjo banjo. Ra ra banjo banjo. Ra ra banjo banjo.</p>
	<p>(Ra ra banjo banjo)</p>
</div>

And if you apply the following CSS:

#navigation {
	position: absolute;
	top: 0;
	left: 0;
	width: 10em;
}

#content {
	margin-left: 10em;
}

You will see that this will set the navigation bar to the left and set the width to 10 em’s. Because the navigation is absolutely positioned, it has nothing to do with the flow of the rest of the page, so all that is needed is to set the left margin of the content area to be equal to the width of the navigation bar.

How bloody easy. And you aren’t limited to this two-column approach. With clever positioning, you can arrange as many blocks as you like. If you wanted to add a third column, for example, you could add a ‘navigation2′ chunk to the HTML and change the CSS to:

#navigation {
	position: absolute;
	top: 0;
	left: 0;
	width: 10em;
}

#navigation2 {
	position: absolute;
	top: 0;
	right: 0;
	width: 10em;
}

#content {
	margin: 0 10em; /* setting top and bottom margin to 0 and right and left margin to 10em */
}

The only downside to absolutely positioned elements is that because they live in a world of their own, there is no way of accurately determining where they end. If you were to use the examples above and all of your pages had small navigation bars and large content areas, you would be okay, but, especially when using relative values for widths and sizes, you often have to abandon any hope of placing anything, such as a footer, below these elements. If you wanted to do such a thing, it would be necessary to float your chunks, rather than absolutely positioning them.

Floating

Floating an element will shift it to the right or left of a line, with surrounding content flowing around it.

Floating is normally used to position smaller elements within a page (in the original default CSS for this site, the ‘Next Page’ links in the HTML Beginner Tutorial and CSS Beginner Tutorial are floated right. See also the :first-letter example in Pseudo Elements), but it can also be used with bigger chunks, such as navigation columns.

Taking the HTML example below, you could apply the following CSS:

#navigation {
	float: left;
	width: 10em;
}

#navigation2 {
	float: right;
	width: 10em;
}

#content {
	margin: 0 10em;
}

If you do not want the next element to wrap around the floating objects, you can apply the clear property. clear: left will clear left floated elements, clear: right will clear right floated elements and clear: both will clear both left and right floated elements. So if, for example, you wanted a footer to your page, you could add a chunk of HTML with the id ‘footer’ and then add the following CSS:

#footer {
	clear: both;
}

And there you have it. A footer that will appear underneath all columns, regardless of the length of any of them.

This has been a general introduction to positioning and floating, with emphasis to the larger ‘chunks’ of a page, but remember, these methods can be applied to any element within those chunks too. With a combination of positioning, floating, margins, padding and borders, you should be able to represent ANY web design and there is nothing that can be done in tables that can not be done with CSS.

The ONLY reason for using tables for layout at all is if you are trying to accommodate ancient browsers. This is where CSS really shows its advantages - it results in a highly accessible page a fraction of the weight of an equivalent table-based page.

• 0 Comments

At-rules encapsulate a bunch of CSS rules and apply them to something specific. Woo.

 

 

Importing

The import at-rule will bolt on another style sheet. For example, if you want to add the styles of another style sheet to your existing one, you would add something like:

@import url(addonstyles.css); 

This is often used in place of the link element to link a CSS file to an HTML page, by essentially having an internal style sheet that looks something like this:

<style type=“text/css” media=“all”>@import url(monkey.css);</style>

The benefit of this is that older browsers, such as Netscape 4.x don’t have a clue about at-rules and so won’t link to the style-sheet, which, if you have well-structured markup, will leave functional (although un-styled) plain HTML.

Media types

The media at-rule will apply its contents to a specified media, such as print. For example:

@media print {
	body {
		font-size: 10pt;
		font-family: times new roman, times, serif;
	}

	#navigation {
		display: none;
	}
} 

The media-type can be:

• all - for every media under, over, around and in the sun.
• aural - for speech synthesizers.
• handheld - for handheld devices.
• print - for printers.
• projection - for projectors.
• screen - for computer screens.

You can also use braille, embossed, tty or tv.

Note: having said all of that, the only media-types currently supported by IE are all, screen and print.

Character sets

The charset at-rule simply sets the character set encoding of an external stylesheet. It would appear at the top of the stylesheet and look something like @charset "ISO-8859-1";

Font faces

The font-face at-rule is used for a detailed description of a font and can embed an external font in your CSS.

It requires a font-family descriptor, which the font can be referenced to, the value of which can be an existing font name (so overwriting that font when conditions are met) or it can be a completely new name. To embed a font, the src descriptor is used. Other descriptors added to the font-face at-rule become conditions for that embedded font to be used, for example, if you were to add a font-weight: bold style to the at-rule, the src of the font-family will only be applied to a selector with the font-family property if the font-weight property is also set to bold.

You might use a font-face at-rule like this:

@font-face {
	font-family: somerandomfontname;
	src: url(somefont.eot);
	font-weight: bold;
}

p {
	font-family: somerandomfontname;
	font-weight: bold;
}

This will apply the somefont.eot font to paragraphs (it would not if the p selector was not set to font-weight: bold).

Support for embedded fonts is patchy at best. Mozilla based browsers don’t support them and have no immediate plans to do so. Only Internet Explorer seems to have any kind of decent support and this is by no means straightforward. To embed fonts with IE, you need to use Microsoft’s WEFT software, which will convert the characters of a TrueType font into a condensed OpenType font (and this can then only be used on the URI that you specify). Because of this limited (and quite complicated) compatibility, it is best not to use fonts that do not have an adequate alternative system font.

Pages

The page at-rule is for paged media and is an advanced way to apply styles to printed media. It defines a page block that extends on the box model (see Margins and Padding) so that you can define the size and presentation of a single page.

There are a number of conventions that apply to page at-rules, such as there is no padding or border and this isn’t a computer screen we’re talking about - pixels and ems as units aren’t allowed.

There are a number of specific properties that can be used, such as size, which can be set to portrait, landscape, auto or a length. The marks property can also be used to define crop marks.

@page {
	size: 15cm 20cm;
	margin: 3cm;
	marks: cross;
}

Pseudo classes for paged mediaThere are three pseudo classes that are used specifically in conjunction with the page at-rule, which would take the form of @page :pseudo-class { stuff }.

:first applies to the first page of the paged media.

:left and :right apply to left-facing and right-facing pages respectively. This might be used to specify a greater left margin on left-facing pages and a greater right margin on right-facing pages.

There are a number of other facets specific to the page at-rule such as page-breaks and named pages, but seeing as this at-rule works on hardly any browser, you’ve probably wasted enough time reading this part anyway. It’s a nice enough idea though…

• 0 Comments

Pseudo elements suck on to selectors much like pseudo classes, taking the form of selector:pseudoelement { property: value; }. There are four of the suckers.

 

 

First letters and First lines

The first-letter pseudo element applies to the first letter of an element and first-line to the top line of an element. You could, for examples create drop caps and a bold first-line for paragraphs like this:

p:first-letter {
	font-size: 3em;
	float: left;
}

p:first-line {
	font-weight: bold;
}

Before and after

The before and after pseudo elements are used in conjunction with the content property to place content either side of an element without touching the HTML.

The value of the content property can be open-quote, close-quote, no-open-quote, no-close-quote, any string enclosed in quotation marks or any image using url(imagename).

blockquote:before {
	content: open-quote;
}

blockquote:after {
	content: close-quote;
}

li:before {
	content: “POW: “
}

p:before {
	content: url(images/jam.jpg)
}

Sounds great, dunnit? Well, as with so many things (-sigh-), most users won’t be able to see the before or after effects because IE just can’t be bothered with them. Lazy lazy lazy.

• 0 Comments

If you have two (or more) conflicting CSS rules that point to the same element, there are some basic rules that a browser follows to determine which one is most specific and therefore wins out.

 

It may not seem like something that important, and in most cases you won’t come across any conflicts at all, but the larger and more complex your CSS files become, or the more CSS files you start to juggle with, the greater likelihood there is of conflicts turning up.

If the selectors are the same then the latest one will always take precedence. For example, if you had:

p { color: red; }
p { color: blue; }

p elements would be coloured blue because that rule came last.

However, you won’t usually have identical selectors with conflicting declarations on purpose (because there’s not much point). Conflicts quite legitimately come up, however, when you have nested selectors. In the following example:

div p { color: red; }
p { color: blue; }

It might seem that p elements within a div element would be coloured blue, seeing as a rule to colour p elements blue comes last, but they would actually be coloured red due to the specificity of the first selector. Basically, the more specific a selector, the more preference it will be given when it comes to conflicting styles.

The actual specificity of a group of nested selectors takes some calculating. Basically, you give every id selector (”#whatever”) a value of 100, every class selector (”.whatever”) a value of 10 and every HTML selector (”whatever”) a value of 1. Then you add them all up and hey presto, you have the specificity value.

• p has a specificity of 1 (1 HTML selector)
• div p has a specificity of 2 (2 HTML selectors; 1+1)
• .tree has a specificity of 10 (1 class selector)
• div p.tree has a specificity of 12 (2 HTML selectors and a class selector; 1+1+10)
• #baobab has a specificity of 100 (1 id selector)
• body #content .alternative p has a specificity of 112 (HTML selector, id selector, class selector, HTML selector; 1+100+10+1)

So if all of these examples were used, div p.tree (with a specificity of 12) would win out over div p (with a specificity of 2) and body #content .alternative p would win out over all of them, regardless of the order.

• 0 Comments

---

The original of this article appears at The Esperanto Group. Some slides from “introducing Informix as a general database platform. In HTML format.” are there as well.

Informix Universal Server

Kerry Sainsbury (kerry@kcbbs.gen.nz), 8 March 1999

Ever since Informix “released” Universal Server at the end of 1997, I’ve been keen to get my hands on it. Now that a trial edition has been made available at www.intraware.com, I finally got the opportunity to try it out. This document describes some of the very cool features of “Universal Server” that distinguish it from the regular “Dynamic Server”, yet doesn’t shirk the important task of highlighting some of the things that really *suck* about the product.

This document also works as a tutorial, so if you want to download the product and fire-up dbaccess, feel free!

Row Types

Informix Universal Server (IUS) gives us the concept of ‘row types’. These are approximately the equivalent of ‘classes’ in an object-oriented environment, just without any methods:

create row type person_t ( name varchar(30), birthdate date );

‘row types’ support inheritance. Here’s the definition of a cartoon-character class, which inherits all the properties of the ‘person_t’ type, and adds a ‘popularity’ indicator in the form of an integer:

create row type character_t ( popularity int ) under person_t; 

These ‘row types’ are, like classes, just definitions however. In order to actually store some instances of the classes, we need to create a database table in which to put them. The following table, called ‘character’, stores rows of the ‘character_t’ class:

create table character of type character_t;

Inserting data into this table is done the same way as a regular SQL’92 table:

insert into character ( name, birthdate, popularity )
 values ("Mickey", "02/02/1946", 10 );
insert into character ( name, birthdate, popularity )
 values ("Donald", "03/03/1953", 9 );

Classes (ie: ‘row types’) can include other classes within them. That is, they support both inheritance (’is-a’) and composition (’has-a’) models. The follow example first creates an ‘address’ class, then an ‘employee’ class which includes an address, and finally an employee table to store the employee objects.

create row type address_t ( description varchar(30),
                            city varchar(30) );
create row type employee_t ( startdate date, salary int,
                             address address_t ) under person_t;

create table employee of type employee_t; 

Now when we want to insert a row into the ‘employee’ table, things start getting a little more interesting. The insertion of the ‘address’ portion of the ‘employee’ needs to be explicitly labelled with the ‘row(….)::address_t’ notation:

insert into employee ( name, birthdate, startdate, salary, address )
values ("Joe Bloggs", "7/9/1963", "7/10/1984", 90000,
row("101 Toontown Sq, Disneyland", "Anaheim CA")::address_t );

At this point we encounter the first limitation of IUS — I’m damned if I can figure out how one can insert a row with partial row type information for the address. I seem unable to ignore the ‘city’ portion of the address, for example. You have to specify the full ‘address’ information.

Let’s move on to some cool stuff now, and worry about the implications of the above limitation later.

Next up we create a new sub-class of ‘employee’, especially for managers. Then we create a ‘management’ table, in which to place our ‘management’ objects. The key thing to notice is that we will create the new table ’under’ the employee table - previously we had only created new ‘row types’ under other ‘row types’.

create row type management_t ( stresslevel integer )
  under employee_t;
create table management of type management_t
  under employee; 

Now let’s put a row into the ‘management’ table - that ‘row(…)::address_t’ notation’s still there, remember.

insert into management (stresslevel, name, birthdate,
startdate, salary, address )

values (99, "Walt Disney", "7/9/32", "7/10/45", 12500000,

  row("Rich Dude Hills", "Beverly Hills CA")::address_t ); 

And let’s do a simple SELECT, to see what we’ve got in management.

select * from management; 

Just the ‘Walt Disney’ row is returned, but what if we SELECT from ‘employee’:

select * from employee; 

Cool - Table inheritance! Not only do we see our ‘Joe Bloggs’ row, we also see the ‘Walt Disney’ row we inserted into the ‘management’ table. Very excellent Informix! And just to prove that we can extract just the ‘employee’ data if we need to, we can use the following syntax:

select * from only(employee); { Now just get employees }

One issue with these ‘row types’ is that you need to have a development tool that understands how to interpret these special data types. If you don’t, you’ll need to select the nested row type information columns individually, eg:

select name, birthdate, startdate, salary,
       address.description,
       address.city from employee; 

Updating tables is fairly straight-forward. The only ‘tricky’ part is updating the ‘nested row type’ columns, like address. The following example fixes the address’s city information in the employee table:

update employee
   set address.city = "Auckland"
 where address.city = "Oakland" 

In order to update the entire ‘address_t’ information, you’ll need to use the same ‘row(…::address_t) notation we used in the ‘insert’:

update employee
   set address = row(address.description,
                    'Auckland')::address_t
 where address.city = "Oakland" 

Tables don’t have to be created ‘of type x_t’. You can create ‘regular’ database tables which just use ‘row types’ as column definitions:

create table ad_agencies( name varchar(30),
address address_t, contact person_t ); 

insert into ad_agencies values
   ("Saachi and Saachi",
row("321 Expensive Location", "City")::address_t,
row("Mrs Shipley", "11/04/1942")::person_t ); 

Stuff that sucks

There is no ‘alter row type’ command, in fact, if you have a ‘typed table’ (like the ‘employee’ table which was created ‘…of type employee_t’), the only column adjustment ‘alter table’ can do is ‘drop type’.

‘drop type’ retains all the data in the table, but replaces the ‘row type’ structures with regular database columns. Our ‘employee’ table would change from (startdate date, salary int, address address_t) to (startdate date, salary int, description varchar(30), city varchar(30));

The reason you might want an ‘alter row type’ is if, for example, you decide it would be a good idea to alter ‘person_t’ to include the person’s shoe size. Not unreasonable, huh?

The bad news is that you can’t change the definition of ‘person_t’ without quite a bit of work. You’ll have to alter every table which features ‘person_t’ so that it doesn’t include ‘person_t’, insert a column to hold the new data, and recreate the row-type. For simple tables like ‘character’, this isn’t too difficult:

alter table character drop type;
alter table character add( shoesize integer before popularity ); 

…but then we get to employee and management (and their associated row types)

“alter table employee drop type” doesn’t work, presumably because the ‘management’ table is ‘under’ it. “alter table management drop type” doesn’t work, presumably because it’s ‘under’ employee.

In order to make this work, you would have to:

• Select the data from every table which uses the ‘person_t’ row type, and put it into a temporary tables.
• Alter the temporary tables, placing a new ’shoe_size’ column after the ‘birth_date’ column in each of the tables.
• Drop all the original tables.
• Drop all the person_t row type descendants
• Drop the ‘person_t’ row type.
• Recreate the original tables, using the structure of the new temporary tables.
• Create the new ‘person_t’ row type.
• Recreate all the person_t row type descendants.
• Alter the tables, addings ‘person_t’ row types (and descendants) as appropriate.
  

Just absurd! Even if somehow you managed to keep all the above items straight, I’m sure the referential integrity contraints would drive you nuts anyway!

I’m not sure it would work anyway. What happens if you have a non-typed table, like the ‘ad_agencies’ table, and you attempt to remove the ‘employee_t’ type - you’re going to end up with two ‘name’ columns (one for the name of the agency, and another for the name of the employee). Yuck!

Of course none of this matters if you create databases which never need to be changed :-)

DISTINCT DATA TYPES

IUS adds what it refers to as ‘distinct’ data types. They are an approximation of SQL92 ‘domains’, which act as aliases to built-in datatypes:

create distinct type phone_number_t as char(7);
create table informant(name char(10), phone_number phone_number_t);

This is fine, but doesn’t really go far enough. You can’t specify ‘not null’. You can’t specify a default value. Most importantly, there is no ‘alter distinct type’, so if you decide that phone numbers need to be longer, you’re simply out of luck.

Somewhat assumingly, there is a ‘drop type’ command, which is quite happy to execute, even if there are tables which use the type! Recreating the dropped type won’t let you recover your table either :-(

drop type phone_number_t restrict; 

select * from informant; // bang!!

OPAQUE DATA TYPES

…are custom data types, which the engine knows nothing about. You define:

• A data structure for how the data is stored on disk
• Support functions to determine how to convert between the disk format and the user format
• Secondary access methods that determine how the index on this data type is built, used, and manipulated
• User functions that use the data type
• A row in a system catalog table to register the opaque type in the database
• Methods to control use of operators, aggregates, and casting.

This is all defined via a C-based API. I did not investigate this facility - I couldn’t see myself ever needing it!

SETS

IUS has some simple support for ’sets’, eg:

create table item( item_number integer,
                   description varchar(40),
                   colours set (char(10) not null )); 

insert into item values ( 1234, 'gardening clogs',
                   "set{'red','blue','green'}" ); 

insert into item values ( 5678, 'pruning hosery',
                   "set{'yellow'}" );

What we’ve defined here the fact that the ‘item’ table contains a set called ‘colours’. We then inserted some values into the table. The big thing that’s missing, is that we don’t have the ability to specify what the valid values for the set are — we can insert ‘Tuesday’ into our set of colours, and nothing will complain! There should be (but isn’t) a syntax something like the following:

create set colours_t char(10) of
{'red', 'blue', 'green', 'yellow', 'magenta'}

which would then support:

create table item( item_number integer,
                   description varchar(40),
                   colours colours_t not null);

Enough of my fantasys however. The ’set’ support that does exist lets us do things like list the items which are available in red:

select * from item where 'red' in colours;

… and list the items which have more than one colour available:

select * from item where cardinality ( colours ) > 1;

There are some variations on sets - ‘multisets’ allow a value to be repeated in the set, and ‘lists’ are ordered sets, eg:

create table city_temps( city_name char(30),
                         avg_temps LIST(integer not null));

What’s naff about sets?

When you update a collection type, you must update the entire collection! You can’t update individual items in the collection. Doh!

I can’t see how I can access the elements of the various ’set’ types. There was no syntax I could find that would tell me what colours a given ‘item’ was available in.

I was expecting, at least, some syntax which would return a pseudo-join result set, like this:

item_number description      colours
1234        gardening clogs  red
1234        gardening clogs  blue
1234        gardening clogs  green
5678        pruning hosery   yellow

I was unable to find any syntax to access the ‘ordered’ information in a ‘list’ set. How can I iterate through the list? Find the first, or last element in the list? I figure I must be missing something!

STORED PROCEDURES

IUS stored procedures support method overloading. We’re finally back at something cool! Let’s create a procedure which calculates a 1% bonus for employees.

create procedure bonus( emp employee_t ) returning money;
   return emp.salary * 0.01;
end procedure;

If we run the procedure, we see that both regular employees (Joe Bloggs) and management (Walt Disney) get the same bonus:

select name, salary, bonus(employee) from employee 

Now let’s create another ‘bonus’ procedure, but this once takes a ‘management_t’ row type as an argument, instead of the ‘employee_t’ the previous one did. Notice that we do not have to drop the existing ‘bonus’ procedure:

create procedure bonus( emp management_t ) returning money;
   return emp.salary * 0.5;
end procedure;

Running the same SELECT statement now gives the regular employees a 1% bonus, while management get a 50% bonus:

select name, salary, bonus(employee) from employee

While this is very nice stuff, I have to wonder why the procedures have been created seperately from the row-type information. One of the big things about the OO paradigm is the way data and procedures are drawn together, so I would’ve expected the syntax to be more like:

select name, salary, employee.bonus() from employee

Perhaps it’s a SQL3-ism.

OBJECT IDs - ‘ref’ and ‘deref’

I read Michael Stonebraker’s “Object-Relational DBMSs - Tracking the next great wave’ book when creating this document. In his book he talks about the ability to include a reference to another object in a table, eg:

create row type department_t( name string,
manager ref(management_t) references management,
workers set(ref(employee_t) references employees ) ); 

The above example defines a department, which directly references a row in the management table, and a set of rows in the employee table.

This techique would move much of the need for joins, and be generally rather sexy. In fact Stonebraker says that in order for row types to be useful, an object-relational db must include these ‘ref’ and ‘deref’ functions.

So guess what’s not in IUS?

Summary

The release I downloaded (9.14.TC1 for Windows NT, 17 Apr 1998) has some very interesting technology underneath it, but I was disappointed at its lack of suitability for any ‘real-world’ database - meaning, one in which the schema may need to be altered!

Finally, I need to credit Mary Mudie’s “Informix-Universal Server” chapter in “Informix Unleashed”, which helped provide some of the examples I’ve used in this document.

Kerry Sainsbury

kerry@kcbbs.gen.nz

• 0 Comments

Understanding and Using Informix Stored Procedures

Rafal Czerniawski

Introduction

Since the introduction of stored procedures in version five of the Informix engine there has been a lot of discussion about their use, benefits, limitations and their impact on the applications’ performance. They have gained acceptance in the client/server environment but their usefulness in the single host type environment has been seriously underestimated. As with any tools, before using them it is important to consider whether it is the right tool for the job at hand. To be in the position to make this decision you need to understand its workings, how well it integrates with your environment and development tools, the type of application you are developing and whether it is a small scale application or large mission critical OLTP system. When used appropriately stored procedures can be a very powerful tool both in the client/server environment and single host type environment.

The aim of this article is to provide a high level overview of stored procedures and their features, particularly the advantages and disadvantages of their use from the two perspectives: client/server and single host type environment. I will also discuss issues to consider before using stored procedures on a large scale. Hopefully by the end of this article you will be in the position to decide whether stored procedures are the right for you.

Overview of Stored Procedures

Stored procedures can be thought of as user defined program modules stored in the database and executed by the database engine. They are the simplest form of remote procedure calls where the remote client sends a message to the server, which then executes the procedure. Stored procedures are written in Stored Procedure Language which is a superset of SQL with additional looping and conditional statements. When a stored procedure is created it’s source and the compiled executable is stored in the system catalog tables. From the administration point of view they are like any other database objects which can be created and dropped using ‘create’ and ‘drop’ statements. Their permissions can be administered using grant statements. The source of stored procedures can be extracted from the system catalogs using ‘dbschema -f’ utility. To fully appreciate the benefits and shortcomings of stored procedures it is important to understand what happens at the time stored procedure is created and executed.

Stored procedures are created using a ‘create procedure’ statement. The following steps take place at the time of creation:

1. The source is parsed and stored in the SYSPROCBODY system table.

2. The SQL is extracted from the source of stored procedure and a dependency list built. The dependency list is an index to all database objects accessed by the stored procedure. It is used by Informix at the time of execution to verify that all objects referenced still exist and have not changed.

3. The extracted SQL is optimised to produce a query plan, just like any other SQL.

4. The dependency list and query plan are stored in the system table: SYSPROCPLAN. Note that while the stored procedure is created Informix places exclusive locks on some of the system tables.

Stored procedures are executed using ‘execute procedure’ statement. Following steps take place during execution:

1. Retrieve the dependency list, pcode and query plan for the stored procedure from the system catalog tables if they are not already in the stored procedure cache.

2. Verify that the objects in the dependency list have not changed since the time the stored procedure was created.

3. If no changes were detected execute procedure.

4. If there were changes to the database object, the SQL is re-optimised and a new query access plan is stored in the catalog tables. The procedure is then executed.

The SQL in the stored procedures is optimised at the time of its creation except in the following cases:

1. When the structure of the tables referenced by the procedure or their indices have changed it will be re-optimised at the first execution after the change.

2. If stored procedure uses temporary tables it will be re-optimised every time it is executed. This has been fixed in later versions of 5.x

3. If the stored procedure does not contain any DML statements (e.g. select, insert, update, delete) it will be re-optimised every time it is executed. This has been fixed in later versions of 5.x. The re-optimisation of SQL during stored procedure execution should be avoided as it results in exclusive locks on the SYSPROCBODY table and can lead to serious concurrency problems.

Stored Procedures System Tables

The following system tables are used to store information about stored procedures:

SYSPROCEDURES - General information
SYSPROCBODY - Pcode executable
SYSPROCPLAN - Dependency list and query plans
SYSPROCAUTH - Security authorisation

Security and Stored Procedures

Stored procedures can operate in two security modes at the time of execution:

1. Owner Privileged Procedures

These are stored procedures created by the user with ‘resource’ authority on the database. The stored procedure inherits all the permissions on the objects owned by the creator plus permissions on other objects that been granted to the creator with the grant option. In case of owner privileged stored procedures Informix will check all permissions on referenced objects at the time of execution.

2. DBA Privileged Procedures

These are stored procedures created by the user with ‘dba’ authority on the database or with a dba keyword in the create statement. In this case the stored procedure has ‘dba’ privileges for the duration of it’s execution. When dba privileged procedure is executed there is no need to check the permissions on referenced objects.

Benefits of Using Stored Procedures

For the purpose of this article it is useful to look at the benefits of using procedures from the perspective of the environment you will use to deploy your application. That is whether it is client/server or a single host type environment. Note that the benefits gained from using stored procedures in a single host type environment are also applicable to client/server.

Advantages in Client/Server Environment

1. Provide means for partitioning the application logic between the server and client. For example CPU intensive work such as calculations could be implemented in the stored procedures and the results returned back to the client for presentation.

2. Improve application performance by reducing network traffic. One of the biggest performance bottlenecks in the client /server environment is the network. With use of stored procedures you can substantially reduce network traffic. There are two reasons for this:

a. Instead of sending whole SQL statements only the stored procedure name and arguments have to be sent to the server.

b. In a situation where you have a transaction which consists of multiple SQL statements the intermediate results from the SQL have to be sent between the server and the client. If stored procedures are used then all transaction work can be done on the server and only the final result returned to the client.

3. Allow encapsulation and enforcement of business rules on the server. For example if business rules are controlled through stored procedures then they can be enforced regardless of what tool is used to update the database.

4. Improved application maintenance. If business rules and application logic are implemented in the stored procedures then any changes to business rules are localised. This means that application changes can be done on the server without a need to distribute them to all the clients.

5. Provide more robust transaction control. When using stored procedures transaction control can be implemented on the server instead of the client. If transactions are controlled on the client there exists a possibility of transactions being left open if the client crashes. Open transactions might prevent archiving of logical logs and lead to long transactions. This problem has been addressed in later versions of release 5 where Informix will terminate any open sessions without client connection.

6. Allow to extend the functionality of some 4GL development tools. Some of the Client/Server development tools are not very good in handling complex database interactions such as denormalised data.

7. Improve application security. In ODBC environments any application users can access the database using desktop tools. Using stored procedures you can eliminate the need to grant direct access to all tables and control it through the procedures.

8. Can be used to invoke Unix programs from the PC client on the server using SPL ‘SYSTEM’ command. For example a stored procedure can be used to send Unix mail or update files on the Unix host.

Advantages in Single Host Environment

Note that all the benefits of stored procedures in the single host environment are also applicable to client/server environment

1. Allow implementation of business rules and database integrity checking in the database by disallowing programs and users from accessing the tables directly.

2. Encourage code reuse. Commonly used functionality can be implemented in the stored procedures which can be reused by any programs.

3. Improve performance. When SQL is executed through 4GL or E/SQL, the SQL statement is sent through a pipe to the Informix engine and then parsed, optimised and security checked before it’s execution. With stored procedures the above work needs to done only at the time of it’s creation. Generally stored procedures with four or more SQL statements provide best improvements.

4. Improve application security. If database access is encapsulated within the procedures then it is no longer necessary to grant privileges to users on the tables.

5. Allow to extend the functionality provided by standard SQL functions. Stored procedures can be used to create user defined functions that are transparently utilised within the SQL statements, for example: if a measurement is stored on the database in inches. A function could be written which will convert inches to centimetres. This function can be then used as if it was built-in SQL function.

6. Extend functionality provided by the database triggers. The language used to write triggers is limited to simple type operation.

7. Hide the complexity of the underlying database structures from the users and programmers, for example stored procedures can be written to perform standard reports which are then invoked by GUI end-user reporting tools.

8. Aid in application’s performance tuning and monitoring. Procedures provide ability to perform SET EXPLAIN on SQL with out first executing it. Since the stored procedure’s SQL is optimised when the procedure is created, it is possible to obtain access paths chosen by the optimiser at this time. In the case of SQL executed from E/SQL or 4GL it is not possible to see the explain output unless the SQL has been run against the database which might not be always possible or practical in a production environment.

9. Can be used increase application’s independence from the underlying database structures, therefore, isolating the programs from the database structure changes, for example: data denormalisation.

Disadvantages of Stored Procedures

Most of the disadvantages of stored procedures are due to an extra layer of complexity and lack of support by development and administration tools

1. Some of the 4GL development tools are designed to work best by accessing the database tables directly. If you are using stored procedures then some of the default functionality provided by the development tool might be lost. For example most of 4GL tools will automatically implement optimistic locking (checking for lost updates). If you choose to perform updates through stored procedures then in most cases you will lose the functionality provided by the tool which will result in decreased productivity.

2. For the above reason, when using stored procedures you might lose the database independence provided by your 4GL tools. Also the stored procedure’s code is not portable to other DBMS platforms.

3. More complicated program debugging.

4. Before version seven of the engine, stored procedures are buffered in the sqlturbo processes hence each user holds their own copy of the stored procedures cache. As a result of this your application might require more memory for each sqlturbo process and you will lose the benefits of sharing buffered procedures across different database sessions. This is no longer a problem since version seven has a common stored procedure cache for all the users.

5. Extra dependency is introduced into the applications. It necessary to ensure that the programs execute correct versions of the procedures. Configuration management is a major issue. 6.In some situations the use of stored procedures might degrade the performance. As stated earlier, when stored procedure is executed the Informix engine has to check the system tables to ensure that the tables used by the procedure have not changed.

7. The stored procedure language does not support dynamic SQL and versions prior to seven did not support access to SQLCA.

Using Stored Procedures

As illustrated, many benefits can be gained from using stored procedures. When embarking on their use, consider the impact on the developers due to the extra layer of complexity in the environment. It is best to carefully plan how they will be utilised and administered. Following is an overview of issues that should be considered before using stored procedures:

1. Devise naming standards. Good naming standards are important with a large number of stored procedures. They will improve the maintainability of the application by allowing easy identification of stored procedure function and the tables it accesses.

2. Document SPL coding standards. The standards should address issues such as:

a. Guidelines on when to use stored procedures. You might decide that only certain type of work should be done in stored procedures. Good candidates are: complex reports and queries, access to sensitive data, processing of large cursors, any SQL that is used repetitively, transactions which perform more then four SQL statements.

b. Document conventions for parameter passing between the application programs and stored procedures.

c. Define how the stored procedures will communicate back with the application.

d. Provide guidelines for transaction control. Transaction control can be either done from the application program or within the stored procedure. The choice will largely depend on the development tools at use. Try to stick to one consistent method.

3. Error handling. How will the errors handled and communicated back to the application programs.

4. Workout how the dependencies between the procedures and programs will be tracked.

5. Update configuration management procedures.

6. Management of SYSPROCBODY and SYSPROCPLAN tables. When a large number of stored procedures is used these tables can grow to many extents which can effect the performance of loading the procedures into memory. This can be eliminated by altering the ‘next extent’ to a larger size.

7. Security administration.

8. Remember that by default stored procedures are created with execute to public permission. When migrating stored procedures to production execute to public should be revoked.

Summary and Conclusion

As you can see stored procedures are very powerful tools when used appropriately. The decision to use stored procedures should depend on your organisation, development environment and the type of application being developed. All of the benefits of store procedures can be fully realised when building large mission critical applications, where database integrity, security and performance with a large number of concurrent users are critical. Stored procedures should be also given serious consideration when building client/server applications which will be deployed over the wide area networks where the amount of network traffic will have a significant impact on your application’s performance. In most cases you will find yourself using stored procedures just for a specific tasks which cannot be easily done using your current development tools or in situations where central enforcement of business rules and security is critical to the application.

Rafal Czerniawski is director of Dataspace Consulting Pty Ltd. He specialises in logical and physical database design, performance tuning, database administration, enterprise client/server design and CASE tools, his experience includes Informix, Sybase and DB2. You can contact him through email at: rafal@dataspace.com.au.

• 0 Comments

Tips and Tricks on OO GUI Development Using Informix NEW ERA

By Don Udawattage (email: taraka@alphalink.com.au, udadon@hotmail.com)

Having worked as a major developer on a large commercial application developed in Melbourne using the full power of New Era and having many years prior experience of OO GUI Client Server Database application development, I am in a position to identify the strength of this powerful truly OO product, despite its not being well known in Australia. New Era has some inherent problems which stand on the way of serious development and we have found sound solutions to overcome them and made robust applications. For some of you, these findings may look like reinventing the wheel because you may have come across the same problems and found better solutions long time ago. If what I am talking about in this note seems like gibberish and does not make sense to you(not an unreal possibility), please do not hesitate to Email or Phone me. The most up to date copy of this note may be seen in my homepage in the internet(URL: www.alphalink.com.au/~taraka). My home number is +61 3 9395 2637 and my mobile no is +61 0402 044 652 – I am an Australian. If you are viewing this document on a browser, it is convenient get a print and read. At the end of this paper, there is a hypertext link to download software mentioned in this paper.

Part A: To Make your Application Maintainable and User Friendly Focus on the Following

A1. Create Your own Base Classes of Visual Objects

Create your own base classes of visual objects(windows, list boxes, text boxes, superfields etc.) derived from New era base classes by simply cloning the 4GLs & 4GHs so that you can add your own common member functions and variables. In this way generic changes, found necessary later, could be implemented easily. When you paste a visual object such as a text box on a window, change its class name property from ixTextbox(for example) to your site’s textbox class. If you develop subsystems, create a separate window base class for each subsystem so that, for example, if your manager wants on the eve of implementation, the windows of different subsystems to have different background colours, you can do this easily, making him happy.

A2. Implement a Naming Standard

Have a standard for naming visual objects and classes and variables, so that they can be recognisable in 4gl code. For example, you may decide all your list boxes should end with lowercase abbreviation, preceded by underscore(e.g _lb). In addition to being readable, you can write generic functions which, by looking at the name can create, references to them using New Era’s powerful function ixWindow. GetContainedObjectByName() which we have found usefull to build a vector of all the controls in a window at runtime – I will explain later why we had to do this. If your visual object receives data from a column of a database, use the column name as a part of the object name. If the name of the column in the database table is state_code then the list box on your window representing this column can be named state_code_lb. Also name your labels so that the name of the label can be derived from the name of the object. The label of the state_code_lb listbox can be named state_code_la, replacing lb with la. This will help to add the label to error messages in generic functions used to validate data entered into visual objects.

Reward of Naming Standard

Towards the end of the project we found that some of our users type into data base key fields, illegal characters(such as asterisks) which cause problems. Are we to revisit all the programs to put in validations to prevent this and ask the users to retest ?Where is the time and money ?. Because all our key columns end with _code(e.g. state_code) and our visual objects(eg. Text Boxes) reflects this name, we could simply override the keypress event of the base class to not to accept offensive characters for key fields. However, we could solve this problem more elegantly in the SQL class covered later - a stitch in time saves nine !

A3. Make List Boxes Intelligent

A New Era list box provides basic functions to get/set the whole text on a line but for serious database applications, this is not enough. Provide functions to load data from the database by passing the table and column names and the list box will show the descriptions and the foreign key associated with that description is stored in a vector & when you want to save to database, your functions returns it. You can introduce members to indicate whether a list box is mandatory so that blank entries can be inserted for non-mandatory list boxes automatically. These are only a few of many features.

A4. Split your Big Applications into a Number of DLLs

Do not create a single application unless it is small. Group windows belonging to a sub systems into separate dll’s and have a small main executable to drive the dll’s. Windows dll’s are loaded only when functions in them are referenced(really not true in the 16 bit New Era version we used – I hope this will be fixed). Maintain them in a common network drive server and copy them to user’s C: drive when he logs in if the version in his C: drive is older than that of the server. You can do this by writing a program which runs before showing the login/password dialog box(I have written such a program to do this on my home PC, using Visual Basic, Email me for a free copy). This way, loading dll’s will be faster as they are loaded from your C: drive and you have full control of release of dll’s in the server and update them easily knowing very well that when users logs in next time, the latest version is available.

You must create dll’s for your own visual or non visual classes which your developers are obliged to used so that they do not have access to the source code.

A5. Provide Functionality to Hide All Windows at Once

Imagine your user has opened 5 windows in your New Era Application and suddenly wants to open up XL without having to exist the application so that once he finishes the XL session, he can resume his work.Your user’s 5 windows have filled the screen. There are so many ways to do this but the best way is to provide a preferences drop down menu in each window and this menu contains an item called “Hide” so that when the user clicks on it, all the windows will be hidden and a small but prominent window appears on the left top corner of the screen giving the entire space to the user. When the user wants to return to the application, he only has to click on this small window and all 5 windows will pop up restored to its original state instantaneously.

You can do this elegantly by creating a preference drop down menu in the base class window along with a function called within the constructor extension so that every time a new window is created, this function adds the window reference to a global array(i.e. a vector, in a global dll). Then put code in the Hide menu item in the Preferences drop down menu to navigate the window array and call hide event of each window then open up the small prominent window, containing a single button whose activate event navigates the window array and call the show event. All the windows developed by programmers, derived from your base class window, will inherit the preferences drop down menu along with all the functions.

A6. Provide Facility for User to Print Screen

No GUI application is complete without user being able to print a window to show someone else(specially to the programmer who developed it) a screen filled with data or defects on screens such as spelling mistakes of labels missing labels or visual objects not correctly anchored. Unfortunately, New Era does not provide a function to do this and you have to write this using Window APIs (I have written this program on my PC using Visual Basic, which can be called from new era applications with a system call, please ask for a free copy) You may provide this functionality by adding a Print menu item to the preferences menu we discussed above(Hiding Windows) so that all your project’s windows will inherit.

A7. Use of New Era SuperTables and SuperFields

Although New Era super tables(like MS Access) let you write code less database applications, they cannot be used for complex mission critical application, in addition to the fact that they have limitations and problems – and Big memory overhead(Note: In this paper, I am only referring to free form supertable & not grid form ones which we have decided not to use, for various reasons):

* Super fields take up a lot of memory and if you have too many of them, you might get the New Era compilation error “Code segment too Big ” and you will have to split the constructor. If you simply need a text entry box where user can type any text or use a display only text box, having been forced to use a superfield is wasteful.
* A list box inside a super table(and I have used a lot of them) cannot be made a bound control, like a super field bound to a database column. To do this, you must maintain a hidden supper field and a visible list box and manually transfer data between each other. This is wasteful and a burden on the programmer.
* If one of the super fields is a foreign key(say state_code having a value “VIC” for Victoria) and you want the user to see the word Victoria which is read by joining to another table, super table cannot help you. You have to do this manually which reduces the superpower of a supertable to half.
* There are other inherent problems with superfields which I will explain later (and for which we have found solutions so that they have become non problems - but additional work)
* When accessing the database there are extra things(explained in details under the SQL Class later) we may have to do in addition to simply selecting/updating data user wants. Extra things such as automatic logging all database errors to database(if your site decides to do), updating any audit information, applying row/column level security without forcing the programmer to do this. The programmer will simply forget or ignore them to make his life easier and in a large development project, it is not easy to monitor them no matter how hard you try to enforce development standards. Supertables are not the best for these type of implementations.

A superfield, however, has its own strength some of which are built in type checking, and being able to override its validate and data change events in the base class to put your own validations/formatting, saving programmers heaps of coding. I recommend to use superfields only for this purpose, however, as you will see later, you can create a simple frame and use intelligent text boxes and list boxes, to do the same job with much less memory overhead.

A8. Implement All Database Access via a SQL Class

Despite few weaknesses, Connectivity Class of New Era is a piece of art. None of the projects I have been involved in the past, have I been able to develop database applications with so much ease, with minimum or no need to code SQL. This could be done only with the help of a SQL class providing all the database access functions and members holding the name of the table, columns and the key information and master detail information etc. Such a class can cache data retrieved from the database. In fact, the programmer using such a class need not know how to code SQL. The functions within the class generates the SQL dynamically(without syntax errors) and all the basic checks to the data can be done in one (and the best) place before writing to the database. Such a class must provide a series of simple functions(with minimum or no parameters):

o Allow parameters to be passed to the constructor to indicate name of database table, names of columns which your program maintains(indicating which ones form the key) information whether table is a master table or a detail table of a master detail relationship.
o Provide a function to retrieve data from database by constructing the entire SQL with a filter(WHERE clause) built by looking at the information user typed in queriable fields. As I explained earlier, if you name your data entry objects(text boxes etc) to reflect the database column name it represents, and if they have properties such as “UseToFilterRecordsFlag” built into the base class(which you can set in the constructor extension of the window), this function is able to map each database column to screen object and retrieve data and populate the screen. If it retrieves more than one record from a master table, it can bring up a generic window showing a list(build dynamically) for user to select. All the data retrieved is cached within the class. This function must handle all the error reporting so that the programmer does not have to worry about it.
o Provide a function to modify the data in the cache by using data on the screen. Again, the function is smart enough to recognise data in which object on the screen goes to which database column, therefore, it need not have parameters.
o Provide a function to save data cached to the database so that the programmer can call it when the user wants to save.

These are the basic minimum interface from the class to the outside world. There are many other functions which you will figure out when you implement, without my having to tell you now and confuse you.

Advantages of SQL Class

1. Since the SQL class uses dynamic SQL, your application is ODBC compliant(at least theoretically) – this may not be a big deal if you will never change the database engine from Informix, however, this gives the IT manager some comfort !
2. By pasting your visual objects(to which you have built intelligence) on to a simple frame, thereby reducing the overhead of supertables and superfields many times over, the developer can still write “code less” code the same way he can do this using supertables in the same time your having full control over database access.
3. When you access the database, you can do a lot of generic things in addition reading and updating data user is requires. Some of them are:

1. Suppose your site maintains columns in the database for audit purposes(such as who inserted a record and when; who last updated the record and when). And you want to do this without asking the programmer(he may forget or ignore). Every time a record is inserted or updated, you can add the relevant columns to the SQL with complete privacy.
2. You may decide to enforce row and column level security on database records, without depending on the programmer. An example of row level security is that if the employees of the Sales Department are allowed to maintain records of their employees only and when a sales department employee signs on to your system, his profile contains what group he belongs to (in this case Sales) and what kind of restrictions should be applied when he retrieves/updates records – assume that all these information is stored in the database. By looking at this information, you can, in complete privacy, add a where clause to Select & Update SQL(See also window and table level security explained later)
3. A super table handles stale data(i.e. concurrent updates) to let you avoid lost updates. Using a time stamp, which comes as a bonus of part of audit information I mentioned above you can implement this. You can read the time stamp of the record, cache it in the class, and add to the filter when user does the update so that if the timestamp has changed in between the update will fail and you know that data is stale and you can inform the user.
4. You can implement all the database access error and message handling in the class so that even if the developer’s linguistic skills are not the best or even if the SQL class designer’s linguistic skills are in question, text in error messages can be changed in one place. When an error occurs, you have all the information on which field it occurred, what its label is so that you can focus onto the field and add the label to the error message.
5. You can do type checking of data entered, check entries on mandatory/key fields, illegal characters in key fields and duplicate keys etc just before saving a record to the database.
6. Suppose a user brings up a record to a screen having few dozens of visual objects then without changing any entry(or changing one entry and changing his mind and reversing the change) tries to save. You can build the update SQL only to include fields he has changed by comparing with what was retrieved from the database, which is kept in the class. If the user has not made any real changes, show a friendly message and do nothing. Otherwise, build the update SQL length of which is proportional to the number of entries he changed, thereby creating a shorter SQL string to be sent to the UNIX server reducing the network traffic.
7. You can also enforce some special security checks, again in complete privacy, to check if authorised users misuse their power. If Jack has access to run a window which allows to give increment to staff. To make sure that Jack does not exceed his limits and give higher increments to his girl friend thinking that no one will notice, you can build into SQL class to check updates of sensitive columns in a database table such as salary. Every time when this column is updated, SQL class can write to a secret log file in a network drive or log to a database file showing who did and when.

A9. Implementing Menu, Window and Table Level Security

Menu Level Security

Jane is a clerical officer and when she signs on to your system, she will get a menu to call windows which she is allowed to call – “Give Salary Increments” is not one of them, so she does not see it on her menu. But Harry is an officer authorised to give annual increment to staff and when he signs on, he can see the “Give Salary Increments” item on the menu and when he clicks on this, salary increment window displays. This is the menu level security on access to windows – what you see is what you are allowed to see. This can be achieved by assigning groups to users and building hierarchies of menus held in the database with information on which groups have access to them. A generic menu driving window which dynamically creates menus using information in the database when a user signs on can be designed to achieve this. To do this using New Era you need some tricks as the driver program does not know what window to open at compile time – it only knows the name of a variable which at run time contains the name of the window to be called. In New Era, you cannot open up a window using a variable and our trick does this as I will explain under opening a window using a variable – I have to tell you that this trick has some problems which I am investigating.

Table Level Security

A user being allowed to access a maintenance window is one thing and what he is allowed to do inside the window is still another. Mary is answering phone calls from customers and when a customer makes an inquiry about his gas account, Mary opens up a maintenance window, brings up the customer’s account and responds to the query – the story ends there. She cannot change any information on the screen – the data entry fields are display only and save button is hidden. When Marshal, who is a clerical officer, opens the same window, he not only can view the same record but can change data in the fields – the fields are enterable and the Save button is visible. This I call table level security and you can call it whatever you think is better. You can implement this very elegantly by putting code in the constructor extension of your window base class(so that all the windows in your project will inherit). When the window is constructed, code within the constructor looks at the database for permission against the user’s group for the current window and disable fields and hide buttons accordingly. To do this so the programmer will not have to know anything about it, you have to create a vector of all data entry fields, buttons and menus at runtime. I will explain later how to do this.

Column Level Security at Window Level

Suppose in this case the salary of an employee is on the employee maintenance window which also contains other information such as his name and address. When Dick opens this window from a menu, he can see the salary of an employee and can change it as he is authorised to do so. Tom has access to the same window and he can change other things(such as address) but not salary which is display only. When Harry opens the same window he not only cannot change the salary but he cannot see it. Under SQL class, I explained how to enforce column level security in a guaranteed manner. However, visual effects like this make it more user friendly. This can be implemented in similar ways to table level security by building into the base class constructor of the window.

A 10. Provide Generic Windows

The more windows you create, the more windows you have to maintain and bigger will be your machine code which will take more disk space and longer to load. Whenever possible, provide generic windows whose controls are dynamically created at run time. A good example is that if user enters a wild card for a client name which brings several records, which will be displayed on a separate window for user to select the one he is after, use a generic window to which all the display information can be at runtime. Other examples of generic windows are pure table maintenance windows, menu drivers and Report Running windows etc.

A 11. Development Standards.

Development standards are wonderful on paper but implementing them on a large project requires code or peer reviews which is no fun. Programmers will follow standards, without being forced, if the standards make their life easy. If a programmer can develop a maintenance widow in a day by following standards and if the same window requires 3 days if he does not follow standards, he will certainly stick to standards. Furthermore, due to non conformance to standards, the programs will not function as expected or non standard appearance of a window is readily noticeable, then again the programmer will follow the standards. This is the philosophy that we have adopted, by creating visual/SQL classes and generic interfaces and this has paid dividends.

A 12. Compiling Nightmare

Imagine your site has a few dozen applications involving hundreds of windows derived from a single base class. Now if you have made a change to the base class which results in changes to the header file, all the applications should be opened and recompiled. You can avoid manual compilation by writing a batch program which does all the compilation, which you can run overnight. The best solutions to these type of problems is to have automatic source code control system to allow users to check in/out programs and an overnight program takes care of compilation and copying the dll’s to proper places.

A 13. Don’t Do’s in New Era Coding

The list I give below is only a few of many things which I advise you to avoid – this list will grow in time. However, I have to tell you that these are my personal views derived from my own experience and you may have valid, logical reasons to do what I tell you not to do.

* Defining variable as records like a database tables in a database applications is, in my view, enemy number 1, which should never be used:

VARAIBLE MyRecord LIKE MyDatabaseTable.*

You make a declaration like this and later in your code you access the database by building the sql:

SELECT * FROM MyDatabaseTable

After executing this statement and getting an ixRow record, MyRow, you may do this.

LET MyRecord = UNPACKROW MyRow

These 3 statements save a lot of typing(beware: All that Shines is not Gold !). But nobody, may be not even you(after few months) will be able to figure out what is in MyRecord. This kind of code is not readable, which is not the end of the world - the worst is still to come. If some one adds a new column to MyDatabaseTable, which your program need not know, your program has to be forced to recompiled by making some dummy entry such as a blank line to change the time stamp of the 4gl file, otherwise, this code will crash. That is not all, the programmer who has written this code may have left and no one knows which programs to recompile - hunt down all programs or simply wait until the program crashes, when your favourite customer calls for a critical inquiry, and the person answering the phone has to say “Sorry sir, our system is down”. The data independence of relational databases will be thrown out of the window with this kind of coding, which converts your OO program to a COBOL program.

* Another similar pitfall is when you try to insert a record into the database, you may code like this(to save typing):

INSERT INTO MyDataBaseTable VALUES(1,’Harry’, ‘Smith’,…,…)

Instead of specifying the column names like this:

INSERT INTO MyDataBaseTable (Id, FirstName, SurName,…,…)

VALUES(1, ’Harry’, ‘Smith’,…,…)

This will suffer from same flaws as in the case of Record Like(explained above), but in addition, you need to make code change to the insert statement, in addition to just recompiling every time a new column is added to the table, even though the new column is maintained elsewhere - beware of shortcuts, which can be very long.

* It is hard to believe what I am going to say next but believe me this happened. A variable was declared as:

VARIABLE MyVector ixVector() — suppose to instantiate here

When the executable(compiled C code) program containing this line of code ran well on Computer A instantiating the MyVector correctly but the same program run on Computer B did not instantiate leaving MyVector NULL (crashing the program later). The moment we changed the code as follows and recompiled, the program ran successfully on both machines:

VARIABLE MyVector ixVector

LET MyVector = NEW ixVector()

After this experience, I prefer not to use the second type of declaration.

* New Era functions can return more than one parameters:

RETURN A, B, C,………..

The calling program receives the parameters as:

CALL ANY FUNCTION () RETURNING A, B, C,………..

I found this a nuisance when the number of returned values is big and you are required to remember the order. That is my problem but imagine that the function needs to return an additional parameter later which your program is not interested in receiving. Your program needs to be changed and recompiled. To overcome this and many other problems, I declare the function to return an ixVector which contains pairs consisting of parameter name and value and provide another short functions to extract values from the vector. This has made the function, and the code calling it, relatively independent of each other and has proven to be efficient and less cumbersome to programmers.

* Do not have the habit of calling functions like:

LET BoolVar = IsClientOk(VarClienId, “A”, “01/01/1997″)

Call Like this:

LET BoolVar = IsClientOk(

ClientId : VarClienId,

Sataus : “A”,

DueDate : “01/01/1997″

)

The former is dull(lazy), not readable and doomed to fail, specially if you do not include the function prototype, which the compiler will not insist. The compiler will make its own assumptions and if the prototype is not included, the parameters should exactly match by number and position of the actual function.

With the second syntax, code is readable, compiler will force you to include the 4gh file containing function prototype. The order and the number of parameters of the called function is not important as long the parameters you name are present. The greatest advantage is that the called function can add new parameters with defaults(so that you need not pass a value to it) at any position and change the order of the parameters. When this happens, your code will be recompiled due to change in 4gh file with no code changes required.

* Avoid using variables in the window prebody section of a window:

A variable declared in the prebody section of a window (or a module variable in an include file which is included in the prebody section) is in effect a shared variable of the window (although you are not required to use the key word SHARED) and multiple instances of the same window will have only one value for this variable. Unless it is truly shared (in which case declare it as a shared variable or constant within the class extension) do not use it so that you can reference it from within member functions included in the prebody - to save coding. Suppose for instance, to avoid using calls to getWindow() more than once in the “Find Customer” window, you use a variable which holds reference to the window (by calling getWindow() once when the window opens). You declare this variable in the prebody section. Assume your program opens up a second instance of the same window, to find another customer, leaving the first window open. The variable holding the reference now points to the second instance corrupting the first. This example is not very good but gives you the idea. I had heaps of problems with this type of implementations with menu driving generic windows which have multiple instances. To avoid this, use member variables

A14. Allow Runtime Debugging and Diagnostics

Helga calls you and says that when she tries to bring up the customer Paul Klugman, the system says the customer does not exist and she knows that he exists. The programmer who had designed the customer maintenance window has left the project and you are new. You access the database interactively(say using dbaccess) and finds that the PauL Klugman is in the database. How do you know why the SQL in the program fails ? - because the programmer quite correctly added a restriction to the select filter that customer status is active and some one accidentally made Klugman inactive. Problems like this as well as bugs went undetected during testing can be caught by putting a debugging facility. In the preference menu mentioned earlier, you can allow a debug toggle which a user can turn on/off thereby setting up a global debug flag in the memory. In your SQL class or any other base class which implements the database access, you can check if the debug flag is on, if so, display the entire sql which Helga can read out to you or copy and paste into a text file on a public network drive for you to pick up and run interactively. I have implemented this and found it very useful. If you do not want the user to see the SQL for security reasons, you can write it to a temporary file in the public network d