db2web

# there are 5 basic commands:
LOGIN username pwd database_name localhost
SET variable string
TABLE list table_options
FIELD string field_options
REPORT string [ ON string ] [ START ] string

# the table options can include one or more of:
        HIDE fields
        MAIN_COL fields
        START [PICK]
        SEARCH fields 
        URL fields  
        VERTICAL   
        RENAME string
        EDIT [ GUEST | SPECIAL | ADMIN ]

# the field options can include one or more of:
        RENAME string
        VALUES list
        FORMAT string
	BETWEEN number AND number
        DEFAULT string
        LINK table field
	UPPERCASE
	LOWERCASE

# other definitions are as follows:
string := 'string with spaces', string_without_spaces, 123
tables := ( tabA | tabA, tabB, tabC | 'tab%' | ALL )
fields := ( fieldA | fieldA, fieldB, fieldC | 'field%' | ALL )
list := ( string | string , string , ... )

The LOGIN command

LOGIN username pwd database_name localhost

You'll need to change the username, password and database_name to the values for your MySQL database. By default the program only allows users to read data but it would still be preferable if you use a user name that only has select permissions on your tables. Once the program can login then you can start viewing your data on the web. Most database servers will be on the local host. In future I might add port and the option to have other types of database.

Index

The TABLE command

The table command is followed by a list of tables and then the options that will apply to them. The list could be just a single table name, a list of comma separated table names, a wildcarded string, the keyword ALL or a combintion of these. It is best to have the table commands that apply to all tables before those commands that apply to specific tables. All the table options that follow this command will apply to these tables. Here are some examples of specifying tables :

• TABLE ALL
• TABLE 'tab%'
• TABLE tabA,tabB,tabC
• TABLE tabA

TABLE MyPrices START

TABLE MyPrices START PICK

The 1st command tells the program that this is the main table in the database and that the entries in this table should be listed when the user first comes to the system. Only one table can be the main table. The 2nd version of the command will display just one record, chosen at random, from the main table.

TABLE MyPrices MAIN_COL stock_name

TABLE ALL MAIN_COL '%name'

When listing the records in a table the program guesses that the primary key should be shown. This is fine if the PK is a descriptive field but hopeless if it's an id field. You can force any field to be shown with the above command. The first example makes stock_name the main field in the MyPrices table while the next example makes any field ending in name the main field in all tables. Only one field can be used per table as the show field.

TABLE MyPrices HIDE stock_id

TABLE ALL HIDE stock_id

TABLE ALL HIDE '%_id', '%code'

TABLE ALL MAIN_COL '%name' HIDE '%_id'

The first example hides the stock_id field from a table. The second example hides stock_id from all tables. The third example hides all fields ending in either '_id' or '_code' from all tables. The fourth example combines the above with another table option.

TABLE MyStocks SEARCH 'stock_name'

TABLE ALL SEARCH '%name'

TABLE ALL SEARCH ALL

This option tells the program which fields it can search in. The first example will only search the stock_name field in the table MyStocks. The second example searches all fields ending name though this can be confusing for the user. The third example searches every field in every table but apart from being inefficient it would also show many false matches and confuse the user. If no search command is given then the search box won't appear at the bottom of the screen.

TABLE ALL VERTICAL

The vertical option displays the fields of a single record vertically down the page rather than across the page. It will also display any image field in large format next to the fields.

TABLE MyUsers URL 'user_img', user_email, 'home_page_url'

TABLE ALL URL '%_img', '%_email', '%_url'

The URL option will treat the following fields as either clickable emails, pictures or links to external web pages. If a table is set to vertical and it has a URL field containing an image then this image will be displayed alongside the fields. The first example is telling the program to treat the user_img, user_email and home_page_url fields as special links. The second example does the same for all tables.

TABLE MyPrices EDIT GUEST

TABLE ALL EDIT ADMIN

This option allows users to edit the data in tables. The first example gives everybody the write to insert, update or delete data in the MyPrices table. The second example only gives permission to the admin user. All changes get logged.

TABLE MyPrices RENAME 'Prices'

The program will normally display the names of your tables on screen however these names can be changed to something more user friendly using the above command. It only makes sense having a single table name appearing in the table list for this option.

Index

The FIELD command

The FIELD command is similar to the table command but obviously applies to fields rather than a tables. It gives the ability to rename fields accross the database, to define allowed values or allowed formats, and to set default values. Fields can also be linked to other tables using this command, say to link a user_id field to a table called user and a field called id. Normally only one field will be specified in the list but it is possible to have multiple fields or all fields ie:

• FIELD top_speed, mpg, hp FORMAT NUMBER
• FIELD engine_type VALUES 'petrol 4s', 'petrol 2s', 'diesel'
              RENAME 'Type of engine'
              DEFAULT 'petrol 4s'
• FIELD ALL RENAME Nonsense

FIELD price_usd RENAME 'Price (USD)'

Renames the price_usd field across all tables to Price (USD). The quotes aren't needed if the string contains no spaces.

FIELD gender DEFAULT Male

When inserting new data via the screen a field can be given a default value using this command. In the above example the gender field will default to Male.

FIELD gender VALUES Male, Female

FIELD gender VALUES GENDER

When entering data via the screen the user is given a choice between a set of defined values. There are a number of predefined values available which saves on typing (GENDER, TITLE, MONTH, WEEKDAY, COUNTRY). This list of predefined values will probably be expanded with each new version of the code.

FIELD prod_code FORMAT '[A-Z][0-9][0-9]'

FIELD top_speed FORMAT '[0-9]+'

FIELD top_speed FORMAT NUMBER

FIELD month FORMAT '[A-Z][A-Z][A-Z]'

You can define particular formats for fields using this command. The string is a standard PHP regular expression. A few predefined formats are available such as NUMBER and DATE. This list of predefined values will pobably be expanded with each new version of the code.

FIELD age BETWEEN 0 AND 130

You can define a numeric range for a field. This means that if a value is entered then it must be numeric and within the range specified.

FIELD user_id LINK users.id

The above example links all occurrences of the field user_id to the table users on the field id. Now when a table contains a user_id field then instead of the id field being shown, it will now show the name of the user and provide a link to this record.

Index

The REPORT command

The report command allows you to build standard select statements and present them as easy to run reports. By default they can be run at any time but you can specify a field name which means the report will only be shown when the user has selected an item where this field is the primary key. The id of the current item can be passed to the SQL using the {ID}. Only one id field can be passed.

REPORT 'My Bunnies'
          'select * from Animals
          where type="Rabbit" '

You can create a special named report from standard SQL using the above syntax. The SQL will need to be enclosed in quotes and you'll need to be careful not to use the same type of quote within your SQL.

REPORT 'My Bunnies' START
          'select * from Animals
          where type="Rabbit" '

The START option means this report will run by default when the user first enters the site or hits the home page.

REPORT 'Animal mates' ON animal_id
          'select * from AnimalFriends
          where animal_id={ID}'

Note the ON clause in this command, this means the report will only be shown when you're looking at a table where this field is the key. The {ID} string will be replaced by the id of the current record. Note if the ID is a string then you'll need quote characters around it.

Index

The SET command

SET variable value

There are a number of settings on the system and these are always defaulted to a sensible value however you can change them to values more suited to your system using this command. You cannot create new variables as they'd be no use in a declaritive language like this. The variables used so far are :

Variable	Default value	Description
admin_ip	none	If set then log items for this ip are not shown
admin_email	none	If set then users can contact you via the contact button
txt_size	14	Text font size
txt_font	VERDANA	Text font
txt_col	black	text colour
col_bg	lightgrey	Background colour
col_bg	lightgrey	Background colour
col_buttons	yellow	Button colour
col_tab_bg	lightgreen	Table background colour
col_txt	blue	Text colour
description	none	Optional system description
img_bg	none	Background image
img_x_size	300	Size of images when shown individually
img_x_size_small	30	Size of images when shown in list
img_y_size	300	Size of images when shown individually
img_y_size_small	30	Size of images when shown in list
info	none	Information text at the bottom of the screen
max_all_show	1000	How many items can be shown at one time
max_groups	15	Number of A-F, G-L type groups
max_linked_list	12	Maximum number of linked items that can be shown
max_logs	1000	Maximum number of log records to be held
max_radio_list	4	Show radio buttons rather than pull down below this number
max_recent_items	5	number of recent items remembered
max_show	25	Maximum number of individual items shown
special_pwd	none	Password for special users
tables	none	List of tables that can be viewed
title	db2web	Main title for system
url_base_dir	none	Relative URLs have this added to them to make them absoloute

The tables variable is slightly different to the other variables in that it can hold list values rather than just a string. The display variables like title, description and info can contain any html tags. The img_bg variable may contain the url of an image to be repeated across the screen however there are a number of inbuilt images you can use (ROCK, PAPER, WOOD, WATER and PLANT).

Index

# Change the login details to access your MySQL database
LOGIN username password db_name localhost

# Give your system a title
SET title 'NameForYourSystem'

# Restrict it to view certain tables
SET tables_matching ALL

# Customise which fields are shown, searched or hidden etc
TABLE ALL SEARCH '%name'
         MAIN_COL '%name'

# Make one table your main starting point
TABLE YourMainTable START MAIN_COL '%name'

LOGIN username password db_name localhost

# alter the look of the screens
SET tables 'scooter%'
SET title 'Scooter Database'
SET info "Feel free to correct data or add new scooters"
SET col_tab_bg lightgrey

# applies to all tables
TABLE ALL MAIN_COL '%name'
      EDIT GUEST 
      URL link, '%img%'
      
# applying to specific tables
TABLE scooter_links MAIN_COL comment
TABLE scooters VERTICAL 
      START PICK  
      SEARCH '%name'
TABLE scooter_maker RENAME "Manufacturer"
TABLE scooter_countries RENAME "Countries"
TABLE scooters RENAME "Scooters"
TABLE scooter_links RENAME  "Links"

# field commands
FIELD gearing VALUES '3 speed', '4 speed' ,CVT  
      RENAME Gearing
      DEFAULT CVT
FIELD engine_type VALUES '2 stroke','4 stroke',Electric,Hybrid 
      RENAME 'Engine type'
FIELD weight_kg, service_interval_km, from_year, top_speed_mph, 
      petrol_tank_ltr, generator_watts, price_usd, engine_cc, max_hp,
      economy_mpg, seat_height_cm, wheel_size_inch FORMAT NUMBER
FIELD carb_type VALUES Carb,EFI RENAME 'Carb type'

# a few reports
REPORT "Scooter range"
       "select name,from_year,
               cast(( economy_mpg * petrol_tank_ltr / 4.55 ) as signed ) miles,
               cast(( ((economy_mpg * petrol_tank_ltr / 4.55 ) *8 ) / 5 ) 
                   as signed ) km
        from scooters
        order by ( economy_mpg * petrol_tank_ltr ) desc"

REPORT "Economic scooters"
       "select name,from_year, economy_mpg, service_interval_km
        from scooters
        order by economy_mpg desc"

LOGIN username password db_name localhost

SET tables 'food_%'
SET title 'Food Nutrient Database'
SET info "data taken from the USDA Nutrient Data Laboratory"

TABLE food_des RENAME Foods
TABLE food_nutr_def RENAME Nutrient
TABLE food_nut_data RENAME Nutrients
TABLE food_group RENAME 'Food Groups' 
TABLE food_group START

TABLE ALL SEARCH '%Desc'
      MAIN_COL FdGrp_Desc, Long_Desc, NutrDesc
      HIDE ComName , Shrt_Desc , ManufacName ,Ref_desc, Survey,
           Num_Data_Pts, Std_Error, Deriv_Cd, Ref_NDB_No, Num_Studies,
           MinVal, MaxVal, Low_EB, Up_EB, Add_Nutr_Mark, Stat_cmt, CC,
           CHO_Factor

FIELD Nutr_Val RENAME Amount
FIELD NDB_No LINK food_des NDB_No
FIELD Nutr_No LINK food_nutr_def Nutr_No

REPORT 'High fibre cereals'
        "select f.NDB_No, f.Long_Desc, n.Nutr_Val
        from food_des f,food_nut_data n
        where f.FdGrp_Cd='0800'
              and n.NDB_No=f.NDB_No
              and n.Nutr_No=291
        order by n.Nutr_Val desc
        limit 15"

REPORT 'High alcohol drinks'
        "select f.NDB_No, f.Long_Desc, n.Nutr_Val
        from food_des f,food_nut_data n
        where f.FdGrp_Cd='1400'
              and n.NDB_No=f.NDB_No
              and n.Nutr_No=221
        order by n.Nutr_Val desc
        limit 25"

REPORT 'Foods high in this nutrient' ON Nutr_No
        "select f.NDB_No, f.Long_Desc, n.Nutr_Val,d.Units
        from food_des f,food_nut_data n, food_nutr_def d
        where f.FdGrp_Cd='1400'
              and n.NDB_No=f.NDB_No
              and n.Nutr_No={ID}
              and d.Nutr_No = n.Nutr_No
        order by n.Nutr_Val desc
        limit 25"

REPORT 'Nutrients in this food' ON NDB_No
"select d.NutrDesc,n.Nutr_No, n.Nutr_Val,d.Units
        from food_des f,food_nut_data n,food_nutr_def d
        where f.NDB_No = {ID}
              and n.NDB_No=f.NDB_No
and d.Nutr_No = n.Nutr_No
order by d.NutrDesc
limit 500"

SET tables 'food_%'
SET tables MyBigTab, MyOtherTab

TABLE ALL HIDE '%_id'