Back Previous Next


8 The loans record

The script for the loan record is similar to those for Customer and for Videotape but it serves a somewhat different purpose so there are some important differences. The structure of the database is described by the following declaration:

	specification LSpec
	record Loan
	
!	Set up the specification for a Loan record
	set the table name of LSpec to "Loan"
	set the fields of LSpec to
		id type long
		and customer type long
		and videotape type long
	set the specification of Loan to LSpec

In this record, all the fields are long integers; in fact they are all dates. A Loan record has a unique ID being the date/time at which it was created, and two data fields; the ID of a Customer and the ID of a Videotape. This is a 'linking' record that establishes relationships between Customers and Videotapes.

Adding a new record means putting up a screen that asks for a Customer and a Videotape. It looks like this:

*MESSAGE*

Customer ID or name:

*

Videotape ID or name:

*
 

* = required field

Both Customers and Videotapes can be identified by their unique ID or by their name/title. To make the system as friendly as possible, either will be allowed. A Customer ID is likely to be printed on his/her membership card, while that for the tape is written on the box. But they are rather unfriendly long numbers that are easily mistyped, hence the offer to use the customer name and tape title instead.

This script needs to understand the formats not only of its own records but also those of Customer and Videotape too. So at the top of the file are all three sets of declarations. Because these declarations have now been used in more than one place it would be a good idea to put each into a separate file and use the Linguist include command to read them in where needed. I haven't done that in this example.

When the form arrives back at the LAdd handler (subroutine AddLoan) we have to find out which of the input options were used for each field. For this reason the subroutine is probably the largest in the entire suite:

AddLoan:
	prompt "Add Loan"
	set the title to "Add Loan"
	put parameter "customer" into CustomerParam
	put parameter "videotape" into VideotapeParam
	! Check that all required fields are present
	if CustomerParam is empty or VideotapeParam is empty
	begin
		add template Website cat "servlets/templates/lnew.html"
			where "*MESSAGE*" is "Please fill in both fields."
			and "*CUSTOMER*" is CustomerParam
			and "*VIDEOTAPE*" is VideotapeParam
			and "*SCREENID*" is "LAdd"
			and "*FUNCTION*" is "Add"
		return
	end

	! Create the new record.
	! First check to see if the customer field is an ID or a name
	get Customer id CustomerParam
	if the status is "OK" put CustomerParam into CustomerID
	else
	begin
		get Customer using "WHERE name='" cat CustomerParam cat "'"
		if more Customer
		begin
			get next Customer
			put field "id" of Customer into CustomerID
		end
		else
		begin
			add template Website cat "servlets/templates/lnew.html"
				where "*MESSAGE*" is "Unknown customer '" cat CustomerParam cat "'."
				and "*CUSTOMER*" is CustomerParam
				and "*VIDEOTAPE*" is VideotapeParam
				and "*SCREENID*" is "LAdd"
				and "*FUNCTION*" is "Add"
			return
		end
	end
	! Now check to see if the videotape field is an ID or a name
	get Videotape id VideotapeParam
	if the status is "OK" put VideotapeParam into VideotapeID
	else
	begin
		get Videotape using "WHERE title='" cat VideotapeParam cat "'"
		if more Videotape
		begin
			get next Videotape
			put field "id" of Videotape into VideotapeID
		end
		else
		begin
		prompt "Videotape status: " cat the status
			add template Website cat "servlets/templates/lnew.html"
				where "*MESSAGE*" is "Unknown videotape '" cat VideotapeParam cat "'."
				and "*CUSTOMER*" is CustomerParam
				and "*VIDEOTAPE*" is VideotapeParam
				and "*SCREENID*" is "LAdd"
				and "*FUNCTION*" is "Add"
			return
		end
	end
	
	! Everything checks out so add the record
	put the millisecond into ID
	new transaction
	create Loan where
		field "id" is ID
		field "customer" is CustomerID
		field "videotape" is VideotapeID
	if the status is "OK"
	begin
		commit
		add "New Loan added."
	end
	else
	begin
		set the title to "Internal error"
		add "Can't create record: " cat return
		add the status
	end
	add template the directory cat "servlets/templates/mainmenu.html"
	return

After checking that neither field is empty the script guesses that the Customer field is an ID and tries to read the record from the database. If it succeeds the value is saved, otherwise it tries again, this time with a search on the name field. If a record comes back the ID is taken from it and saved. [Note: This script does not deal with the 'John Smith' case mentioned earlier, so more than one record may match. You should really ask for the address as well and require both to match, but I'll leave that as an exercise.]

Much the same is done for the Videotape field; first a search is done for the ID and if that fails, for the title. Once both fields have been matched up with records in their respective database tables the Loan record can be created.

Listing the Loan records also has a couple of twists. The ListLoans subroutine creates three columns for display; the date of the loan, the name of the customer and the title of the tape. However, none of these items are directly present in the Loans database, so we need to do a little extra work. The date is easy, since the ID for a record is just that - a date. So to display it as such we use

		set the text of Cell to date field "id" of Loan

This is a rather misleading construct as the English syntax suggests it's something it isn't. It's in three parts:

set the text of Cell to

is the basic instruction for assigning a value to a cell, so what follows is the value to be assigned.

date

is a modifier that says 'Take the number that follows and convert it to a date string".

field "id" of Loan

is the ID field to be converted to a string.

Now we come to the Customer name and Videotape title. We have the IDs for each of the relevant records, so we need another SQL table lookup to get those records and extract the fields we want:

		get Customer id field "customer" of Loan
		set the text of Cell to field "name" of Customer
		add Cell to Row

		get Videotape id field "videotape" of Loan
		set the text of Cell to field "title" of Videotape
		add Cell to Row

As you see, in each case we're using the result of one search as the input to another. In the first one,

field "customer" of Loan

returns an ID value that is applied to

get Customer id

Linguist is quite flexible in allowing these nested constructs, but don't go overboard with them as they can be difficult to follow.

That's all I plan to say about the Loans record. Of course, it's a bit minimal and in a real system you would want to add a lot more bells and whistles, but this is where our tutorial will end. The final versions of the scripts are in servlets, ready for use, and also in scripts as Library.ls and Loans.ls.

9 Where to go from here

In these pages I've shown how scripted servlets work and we've built a simple but complete application. For you to go much further you'll need to explore the Linguist reference manual, in which is a description of every command available. Java programmers may be interested to dive into the source files and find out how it all works, and add more script features and exotic keywords. The system is in a fairly early stage of development and there are bound to be bugs here and there; if you come across a repeatable problem let us know. If you feel motivated to fix the problem yourself, also let us know or ask for help. Here are a temporary URL for Linguist and the email address of the author:

http://www.pobox.com/~gt/Linguist

graham@gt-computing.co.uk


Back Previous Next