jWWWData: a World Wide Web JDBC Data Base Client
This section provides some basic information on the relational data model used by jWWWData. A good understanding of this data model is necessary to properly operate the system.
The Data Base Analogy
The above picture represents the classical data base analogy. A data base, like the cabinet in the picture, is a collection of files, each file is a collection of records and each record is a collection of data elements.
NOTE: The term "data file" has been seleted instead of "table" not to confuse end users with relational data base specific terminology.
jWWWData supports the following data element types:
NOTE: Data Representation
The actual data representation of these data types depends on the Data Base Management System used by jWWWData.
The use of the data types "Date" and "Time" may cause some problems because of the not perfect match between these SQL types and the Java type "java.util.Date"; therefore it is not recommended.
Some of the numeric data types (e.g. "BIGINT", "REAL;", etc.. are not supported by all Data Base Management Systems.
jWWWData has its own mechanism to handle NULL VALUES. This mechanism does not depend on any particular feature of the underlying Data Base Management System and is completely portable.
NOTE: HTML Reference Type
The HTML reference type may contain any HTML text, e.g.
With this type it is possible to insert in a data file references to external entities like images, audios, other HTML pages and so on. This features gives jWWWData multimedia capabilities. On the other hand checking that these references are correct (i.e. that they point to something valid) is a responsibility of the user. No data integrity check is performed by jWWWData on this type.
A data base Data Dictionary is a table describing all the data elements of that particular data base; for each data element the following information is contained:
The Data Dictionary is the heart of the data base, all the information needed to handle the data elements is stored in it.
A data file PRIMARY KEY is a set of one or more data elements which uniquely identifies a record.
First Name | Family Name | Age |
John | Smith | 30 |
Ann | Smith | 30 |
Ann | Jordan | 30 |
A simple file
For example, in the above file, only the combination FIRST_NAME, FAMILY_NAME is a primary key. Any other key, i.e. combination of data elements, would not be able to uniquely identify the different records.
A data file SECONDARY KEY is a combination of one or more data elements used only to introduce a order relationship across the records of a file. In the above example, the FAMILY_NAME and the AGE data elements may be used as secondary keys to order the records alphabetically or based on the age.
A data file FOREIGN KEY is a combination of one or more data elements of the file which are the primary key of another one.
A small example
Book Id | Book Info |
1 | The Bible, ... |
2 | War and Peace, ... |
... | ... |
1000 | Good morning Charlie Brown, ... |
The Library Books File
Member Id | Member Info |
1 | Albert Alans, ... |
2 | Bob Bertrand, ... |
... | ... |
2000 | Walt Whitehouse, ... |
The Library Members File
Book Id | Member Id | Loan Date |
1 | 2000 | 28/03/1996 |
2 | 1 | 01/04/1996 |
... | ... | ... |
1000 | 2000 | 01/02/1996 |
The Library Loans File
In this small data base there are three files: BOOKS, MEMBERS and
LOANS. The primary key of BOOKS is BOOK_ID, the primary key of MEMBERS
is MEMBER_ID. In the LOANS file the primary key is given by the
combination BOOK_ID, MEMBER_ID (one member of the library may have more
than one book on loan). The LOANS file correlates (via the LOANS
relationship) the books which are on loan with the members who borrowed
them. This relationship is expressed by the fact the BOOK_ID and
MEMBER_ID, the two data elements belonging to the LOANS primary key, are
FOREIGN keys (i.e. they are respectively the primary key of the BOOKS
file and of the MEMBERS file).
Integrity Rules
jWWWData respects the following two integrity rules (the definitions here below have been derived from C. J. Date, "Relational Database - Selected Writings", Addison-Wesley 1986, ISBN 0-201-14196-5):
The first rule underlines the importance of the primary key. In jWWWData the primary key is the only way to address/access a record; without the primary key a record is lost.
The second rule derives from the fact that a foreign key is basically a relationship between two data files. Because this relationship must correlate existing records it is not possible for the foreign key to accept values which do not exist (i.e. do not correspond to some primary key in the referred file). Consider the example presented above. It is not possible to enter a record in the LOANS file where the BOOK_ID does not correspond to a particular book in the BOOKS file (i.e. it is not possible to lend a non existing book). In the same way it is not possible to enter a record in the LOANS file where the MEMBER_ID does not correspond to a particular member in the MEMBERS file (i.e. it is not possible to lend a book to a non existing member).
jWWWData endorses the two integrity rules by adopting the following behaviour:
The SCHEMA is a formal description of the data base. Although various formalisms/notations have been adopted by the different Data Base Systems existing on the market, all these notations must allow to specify at least:
The notation (language) used to specify the schema is usually called DDL (Data Definition Language).
Hereafter the Schema of the Library data base example is presented. The various parts of this schema will be described in the following sections. For now it is enough to note that:
Moreover the language is case sensitive (e.g. #dictionary is a keyword and #DICTIONARY is not).
; Schema for the Library Example ; Schema for the Library System #schema LIBRARY ; Data Elements Dictionary #dictionary BOOK_ID, I, 5 BOOK_INFO, C, 30 MEMBER_PIC, H, 100 MEMBER_ID, I, 5 MEMBER_INFO, C, 30 LOAN_DATE, D, 10 #end dictionary ; File Specifications #file MEMBERS MEMBER_PIC, B MEMBER_ID, B MEMBER_INFO, B #end file #file BOOKS BOOK_ID, C BOOK_INFO, B #end file #file LOANS BOOK_ID, C MEMBER_ID, B LOAN_DATE, B #end file ; Index Specifications #key BOOKS BOOK_ID #key MEMBERS MEMBER_ID #key LOANS BOOK_ID, MEMBER_ID #key LOANS LOAN_DATE ; Users Specifications #users host1.library.edu, A host2.library.edu, A OTHERS, R #end users #lifetime 20 ; Max number of records listed ; in the same HTML table #max-records 3 ; Allow the dump of raw data from the data base #allow-raw-data-dump | ; Specify the separator to be used between label(s) and ; and input field(s) ;#label-separator <BR> ; Web Master, Path name, CGI-URL, Help, Background, Header and Footer #web-master webmaster@library.edu ; Settings for decibel ;#jdbc-driver-name jdbc:decibel ;#data-source c:\swwings\jWWWData\library ;#jdbc-driver-class ncsa.sql.driver.Driver ;#jdbc-user ignore ;#jdbc-password ignore ;#jdbc-no-col-values ;Settings for MS-Access + Jdbc:Odbc #jdbc-driver-name jdbc:odbc: #data-source Library #jdbc-driver-class sun.jdbc.odbc.JdbcOdbcDriver #jdbc-user fred #jdbc-password Fred123 #schema-path c:\swwings\jWWWData\library #cgi-url http://www.library.edu/cgi-bin/library-cgi.bat #help c:\www\jWWWData\jwwwgtw.htm #background BACKGROUND="http://www.library.edu/images/backgr.gif" #header header.txt #footer footer.txt #end schema LIBRARY
Every jWWWData schema must begin with the statement
#schema <SCHEMA_NAME>
The name <SCHEMA_NAME> will be used by jWWWData in the title of all the screens generated by the jWWWData CGI.
In the same way the jWWWData schema must end with the statement
#end schema
The Data Elements Dictionary part of the schema starts with the statement
#dictionary
and ends with the statement
#end dictionary
In the data dictionary there is a line per each data element containing the following information.
Example
#dictionary BOOK_ID, I, 5 BOOK_INFO, A, 30 MEMBER_PIC, H, 100 MEMBER_ID, I, 5 MEMBER_INFO, A, 30 LOAN_DATE, A, 10 #end dictionary
NOTE: jWWWData uses for its own operations a set of data elements, whose names start with the prefix "JD_". User defined data elements cannot start with the same prefix.
The Files Specification section of the schema defines the different data files. Each file is described by:
its name - which will be used by all jWWWData applications;
and a list containing for each data element belonging to the data file:
Although the user can set the "break line option" of the last data field in a file to either B or C, it'll be forced automatically to B by the system.
Example
#file BOOKS BOOK_ID, C BOOK_INFO, B #end file
NOTE: jWWWData uses for its own operations two files, whose names are "JD_SERVICE" and "JD_RECORDS". User defined data files cannot have the same names.
The Keys Specification section defines the key(s) of a data files. Each file must have at least one key (the primary key). The key, in turn, is composed of one or more data elements.
This is the syntax of a key statement.
#key <FILE_NAME> <DATA_ELEMENT_LIST>
The <DATA_ELEMENT_LIST> is a list of data elements names, separated by comma. When the same file has more than one key, then the first one is the primary key and the others are secondary keys (i.e. they are used only to introduce an order relationship across the records of the data file).
Example
#key ASSIGNMENTS CONSULTANT_NO, PROJECT_NO #key ASSIGNMENTS CONSULTANT_NO #key ASSIGNMENTS PROJECT_NO
In these sections the end users of the data base are defined. The end users definition starts with the statement
#users
and ends with the statement
#end users
Each end user is identified by a (partial) internet address. Adopting a partial address allows to identify entire internet domains as single users. The end user identifier (i.e. his partial internet address) is matched by the jWWWData CGI against the environment variables REMOTE_HOST and REMOTE_ADDR. With these mechanism only the end users (i.e. the internet locations) specified in the schema are authorised to access the data.
Together with the end user identifier it is necessary to specify the associated privileges. This is accomplished by associating to each user identifier a character, with the following meaning.
The keyword OTHERS can be used to identify all the users on the network who have not already been defined in the previous lines (i.e. all other users on the Web). This is very useful when some access right needs to be granted to everybody (e.g. a library may want to allow everybody on internet to consult its books catalog). If there is no line with the keyword OTHERS, the WWW Data assumes the following
OTHERS, N
I.E. No access whatsoever is granted to non specified users.
Example
#users host1.library.edu, A host2.library.edu, A OTHERS, R #end users
jWWWData keeps a record of the user preferences during a session. If jWWWData had to keep this record for all the possible users, then the data base would grow in an uncontrolled way. To avoid this problem WWW Data deletes the end user record when the user closes regularly the session. If the user quits suddenly the session without closing it properly, his record is first kept for a given number of transactions (i.e. queries) and then deleted automatically.
The parameter shown here below, "LIFETIME", specifies for how many transactions (queries) the records of end users who have not closed their session have to be kept in the data base. The bigger the number, the longer this information is kept in the data base.
Example
#lifetime 20
The last part of the schema contains some information needed by WWW Data to properly operate.
Max Records
This number specifies how many records can be listed at the same time inside a single HTML table. If the end users enter search conditions selecting a big number of records, the resulting output is partitioned in screens of MAX_RECORDS records. If this entry is not specified jWWWData assumes 20 as default value.
Example
#max-records 3
Raw Data Dump
This command enables a jWWWData CGI function, similar to the List Records, which generates RAW data in the following format:
field11<SEP>field12<SEP>...field1N field21<SEP>field22<SEP>...field2N ... fieldM1<SEP>fieldM2<SEP>...fieldMN
In the above line <SEP> is the separator string (or character) as specified in the command itself.
Example
#allow-raw-data-dump |
Label Separator
This command specifies the separator string that will be put by jWWWData CGI in between a label and its input field.
Example
#label-separator <BR>
Using <BR> as label separator will force all the labels to appear on top of their fields. If no label separator is specifed, the default will be " ".
Web Master
jWWWData is designed to work in a reliable way in different situations. However there may be special conditions (like a power loss on the server machine or an enormous number of end users accessing the data base at the same time) which may lead to problems like data indexes corruption, termination of operation and so on. In this case an error message is presented to the end users. This error message contains an automatic link to the data base administrator so that end users can notify him (via electronic mail) the problems they had.
The data base administrator is known to jWWWData as Web Master. Therefore in the schema there must be a line like the following.
#web-master john@webserver.library.edu
The Web Master must be a proper and complete e-mail address, with the format "user@host.domain".
JDBC Driver Name and Data Source
An actual JDBC Compliant Data Base is identified by a JDBC URL with the following structure:
jdbc:<subprotocol>:<subname>
The actual format of this URL varies a lot depending on the selected Data Base Management System. For users' convenience this URL is split by jWWWData int two strings, according to the ODBC convention:
<jdbc driver name><data source>
The final URL will be the simple concatenation of the two provided strings.
Examples
; Settings for decibel #jdbc-driver-name jdbc:decibel #data-source c:\swwings\jWWWData\library
; Settings fo MS-Access + Jdbc:Odbc #jdbc-driver-name jdbc:odbc: #data-source Library
Note how the jWWWData data source part of the URL corresponds to a directory name in the case of Decibel while it corresponds to an actual ODBC data source in the case of MS-Access.
JDBC Driver Class
The JDBC Driver Class is the Java compliant name of the class containing the JDBC driver for the selected Data Base Management System. This class has to be visible on the WWW Server machine from the environment variable CLASSPATH.
Examples
; Settings for decibel #jdbc-driver-class ncsa.sql.driver.Driver
; Settings fo MS-Access + Jdbc:Odbc #jdbc-driver-class sun.jdbc.odbc.JdbcOdbcDriver
JDBC User Name and Password
Some Data Base Management Systems provide access only to a pre-defined set of users. These two fields allows jWWWData to connect to the Data Base Management System as a particular user.
Examples
; Settings for decibel #jdbc-user ignore #jdbc-password ignore
; Settings fo MS-Access + Jdbc:Odbc #jdbc-user fred #jdbc-password Fred123
Column Names inside Values
jWWWData uses in different places an SQL "update" statement of the form:
update <table name> set <column name> = <column name> + 1;
Not all Data Base Management Systems support this type of statement. By using the instruction listed here below:
#jdbc-no-col-valuesit is possible to force jWWWData not to use column names in the values part of un update statement. This is achieved by performing first a "select", to get the original values, then by modifying these values into the new ones and finally by executing an "update" which uses directly these new values. It is quite clear that this alternative, although more portable, is less efficient/performant.
Schema Path
The data base schema is translated by swwings.jWWWData.jDComp into a Java module (jDSchema.java) that is in turn compiled into a Java class. The schema path variable contains the location (i.e. the directory in the WWW server machine) of this Java class. This way, by using different schema class files (i.e. files called jDSchema.class but located in different directories), jWWWData can work at the same time on different data bases.
Example
#schema-path c:\swwings\jWWWData\library
CGI URL
The jWWWData CGI calls itself (from the automatically generated forms) on a number of occasion. To do so the CGI must know its own Uniform Resource Locator (URL).
Example
#cgi-url http://webserver.library.edu/cgi-bin/lib-cgi.bat
Help File
The Help File is a variable allowing to tell the jWWWData CGI which is the starting HTML file that has to be used to display the on line help. It is recommended to use an intermediate HTML page (a sort of gateway) so that in the other pages it is possible to use relative addresses. The variable is customisable so that users can decide to show their own help file(s) and not the standard ones provided with jWWWData.
Please note that the help file is a real file name in the server machine. If this file name does not belong to the directory hierarchy made available by the WWW server to the outside world, it can only be accessed by the jWWWData CGI, i.e. no unauthorised access or manipulation can be performed.
Example
#help c:\www\jWWWData\jwwwgtw.htm
Body Background
This entry allows to specify which body background image or color has to be used by jWWWData when generating HTML pages.
Example
#background BACKGROUND="http://webserver.library.edu/images/backgr.gif"
Header and Footer
Each form (HTML page) automatically generated by the jWWWData CGI can contain a standard header and a standard footer. In these files it is possible to insert special (advertising) information about the people/organisation who want to publish their data using jWWWData. If they are not found, no action will be performed by jWWWData CGI.
Please note that the header and footer are real file names in the server machine. If this file names do not belong to the directory hierarchy made available by the WWW server to the outside world, they can only be accessed by the jWWWData CGI, i.e. no unauthorised access or manipulation can be performed.
Example
#header myheader.htm #footer myfooter.htm
The jWWWData CGI Man Machine Interface is built up upon a set of simple commands. Each command presents its results to the end users as soon as possible so that they can get an immediate feedback about the system behaviour.
These simple commands, like the operators of a relational Data Manipulation Language (DML), can be combined together to obtain more complex queries.
The jWWWData CGI behaviour depends on its status, characterised by the following parameters:
Active File
While jWWWData is able to handle data bases with more than one data file, its CGI can show and manipulate only one single data file at a time in the same browser's window. It is anyhow possible to open an other window with the browser and from there access another data file. The Active File is the data file visible from the CGI at a given moment in time in a given browser's window. End users can select as Active File every file in the data base.
All the jWWWData CGI commands refer to the currently selected Active File.
Active Key
Each data file may have one or more keys. The Active Key is the currently selected key. The users can change the Active Key at any time. The Active Key establishes an order relationship across the records of the data file (i.e. the physical order in which records have been inserted or modified is not relevant).
Selected Navigation Mode
The jWWWData CGI has two different navigation modes:
Entering search conditions (values) enables the users to select only the records in the data file in which they are interested.
TAKE NOTICE: when end users give the command "Enter Search Values", the current data elements' contents are used as search values. E.G. If the current value of the data element BOOK_INFO in the previous example is "Bi", then, when the command "Enter Search Values" is selected, only the records having the string "Bi" in the data element BOOK_INFO are shown to the users.
For data elements of type CHAR(n) and HTML REFERENCE all the records which contain the entered values as substring are selected. For the other data element types all the records containing a value greater or equal to the search value are selected.
If more search values are inserted in different data elements, then only the records satisfying the search conditions for all such data elements are shown to the users (i.e. conditions are combined with logical AND).
Following is a list of the commands that can be started from the menu. To make jWWWData CGI simulate the menus of a normal Windows (or X-11) application, "HTML Select Elements" enhanced by "Javascript Callbacks" have been used. In browser capable of executing Javascript it is enough to select a menu item to start the corresponding action. In browsers not capable of executing Javascript or with Javascript disabled it is necessary to first select the menu item and then push the button "Exec". By pushing the button "Reset", the menu will be reset to its original status.
The records commands will be used frequently. This is why it is possible to call them via menu selection or via direct buttons.
Description: This tool takes the data base schema written by the data base developer(s) and translates it into a Java module. The tool also generates a set of scripts to make easier the invocation of the other jWWWData's tools. A script (jdcomp.bat or jdcomp.sh) is provided for the invocation of the tool itself.
Command line: jdcomp <schema file> <output name>
Example: jdcomp library.sch library
Output(s):
Description: This tool creates / (re)initialises the physical files/tables/etc.. used by the Data Base Management System.
Command line: <output name>-init[.sh]
Output(s): The required data files/tabels/etc...
Description: This tool allows to "unload" a data base file into a physical text file. Different formats are supported (e.g. EXCEL, 1-2-3, and so on...).
Command line: <output name>-unload[.sh] [-s <separators>] <data file> <output file>
Example: <output name>-unload -s ",\"" books books.txt
Output(s): A text file with a record per line. The data elements are separated by the first separator if any, or by a <TAB>. When a second separator is present, this is used to "surround" alphanumeric fields (labels in 1-2-3 terminology).
Description: This tool makes it possible to "populate" a data base file from a physical text file. Different formats are supported (e.g. EXCEL, 1-2-3, and so on...).
Command line: <output>-load[.sh] [-s <separator>] <data file> <output file>
Example: <output name>-load -s "|" books books.txt
Output(s): A data file populated with the records present in the text file. If no separator is given then the data elements lengths are used to identify the different fields in a text line.
Description: This tool tells the user a special unique identifier of the server machine. This information will be required to register jWWWData.
Command line: java swwings.utils.Identify
Output(s): The machine (computer) Unique Identifier is printed on the screen.
Description: This tool allows the user to promote jWWWData from an unregistered version to a registered version.
Command line: java swings.utils.Register
Output(s): The file (application or object file) specified to the tool is updated with the registration information..
jWWWData is just a powerful and easy to use interface to a JDBC/ODBC compliant Data Base Management System. It is the Data Base Management System in use which defines the limits of jWWWData (e.g. max number of tables, max number of columns per table, max number of records per table etc...).
The RAM memory available on the client machine may impose a limit to the maximum number of rows that an HTML table can contain. This problem can be avoided by selecting a proper value for the maximun number of records that can be listed at the same time inside a single HTML table.