© Andy Wilson, 1998

Tips and Tricks: Roll Your Own Director Database

A common requirement for Director projects is that your application be able to link to a database of information that can’t be fixed at design time. For instance, you may have a project where you need to be able to read a database of products, along with part numbers, prices and so on. By putting this information in an external file, and then reading the file from Director, it’s possible to build applications that can be kept up to date simply by updating or replacing the database. This means that you can build an application to be distributed on a CD, and then keep the information up to date just by sending out a new database file rather than having to rebuild the whole project and send out a new CD.

This kind of project can be even more exciting if you build a hybrid Director project that downloads the updated information from the internet in the background – but that’s a topic for another day. Today I want to consider the simpler scenario in which you need an external database of information for your Director application to read from and write to.

One way of doing this is to use one of the database Xtras such as FileFlex, V12 or dataBind. I’ve used all of these in the past, and in many cases they are just what’s called for. However, there are also times when the use of such Xtras is actually overkill. What I’m going to look at this month is a way for you to build your own database system entirely within Director that lets you read in data, manipulate it and then save it back to disc. Of course, if you need complex relational database functionality, or want to deal with many thousands of records, then you may as well use the Xtras I mentioned since these have been designed and optimised to do precisely that kind of job. However, if your requirements are more limited you might like to build the required database functionality directly into your application.

The code that follows will show you how to build a Director application that lets you store and manipulate a table of data – which in many cases is really all that is required. It is up to you to judge when such an approach would be justified and when, on the other hand, you need to use an Xtra.

For the sake of argument, let’s say we need to maintain a record of users; their name, email PRE and phone number. Let’s assume also that you can ship your application with the details of several of these users already in the database. We’ll begin by creating a field cast member called ‘userData’ to contain the data. In the terminology of databases, our field cast member is a ‘table’ and each line in it represents a ‘record’;

-- field member ‘userData’
Jonathan Richardson,jon@digital.co.uk,0904-307-7626
Andy Wilson,andyw@dircon.co.uk,0171-729-6485
...

In general terms, what we are about to do is build a lingo object for every record in the table, plus another, master object to manage these records. The code for the record object is extremely simple. To write it, just create a new script cast member, set it’s type to parent (click the information button on the script toolbar and then select ‘Parent’ from the type dropdown box), name the script ‘Users’, then type in the following code;

-- script member ‘Users’
property name, email, phone
property nextRecord

on new me
  set nextRecord = EMPTY
  return me
end

All this code does, when we use it as a parent script, is create an object with name, email and phone properties we can set and read as required. The other property, nextRecord, is a little more interesting. We are going to use this to ‘chain’ all of our record objects together. The way this works is that we make each record point to the next. We can then pass right along the chain by starting with the first object, then moving to the object it points to, then to the object that that one points to, and so on until we reach the last record. In formal programming terms this is what’s called a ‘linked list’.

The ‘users’ script as described above is fairly minimal. The real work in this example is done by our master database object, created by the next script we will write, which we’ll call ‘dbase’. Once again, don’t forget to make it a parent object. The code starts as follows;

property firstRecord, lastRecord, currentRecord
property numRecords
property sourceDataField

on new me, dataField
  set sourceDataField = dataField
  set firstRecord = EMPTY
  set lastRecord = EMPTY
  set currentRecord = EMPTY
  set numRecords = 0
  set data = the text of field dataField
  repeat with x = 1 to the maxInteger
    set recordData = line x of data
    if recordData = EMPTY then exit repeat
    set newRecord = new (script "user", recordData)
    set the itemDelimiter = ","
    set the name of newRecord = item 1 of recordData
    set the email of newRecord = item 2 of recordData
    set the phone of newRecord = item 3 of recordData
    
    if not objectP (firstRecord) then
	  set firstRecord = newRecord
	end if
    set lastRecord = newRecord
    
    if objectP (currentRecord) then
	  set the nextRecord of currentRecord = newRecord
	end if
    set currentRecord = newRecord
    
    set numRecords = numRecords + 1
  end repeat
  return me
end

The dbase script has three properties, firstRecord, lastRecord and currentRecord which it uses to navigate it’s way around it’s records, and the new handler begins by initialising these properties to EMPTY. Then we set the numRecords property to 0 – we are going to increment this every time we add a new record. Notice that the new handler accepts an argument we’ve called dataField, which is the name of the field cast member that contains the data we are going to use to initialise the database – in our case, we’ve called the field ‘userData’. That means that we would create our database object using the code;

set dbObj = new (script "dbase", "userData")

The new handler reads the contents of the userData field (set data = the text of field dataField), then it uses each line of data to create a new record object. It does this by creating a repeat loop, and reads each line of data in (set recordData = line x of data) until it finds an empty line, at which point it exits the repeat loop (if recordData = EMPTY then exit repeat). If it reads a line of data successfully it creates a new user object (set newRecord = new (script "user", recordData)) and uses the data it has read to set the name, email and phone properties of the new object. The next few lines of code set the values of the database object’s record pointers, so that it maintains a pointer to the first, last and current objects in the chain it creates. Also, it makes sure that each record objects itself points to the next object in the chain (set the nextRecord of currentRecord = newRecord). Finally, the numRecords counter is incremented.

What we have achieved so far is to read in the data from the text field and use it to initialise both the master database object and a separate object for every record in the table. These record objects are built so as to form a chain, so that each points to the next in the chain.

Now, let’s say we want to search the database for a particular record. I’m going to assume that we only need to be able to search the records for a particular name, but of course you could write similar code to search any of the fields. The search code looks like this;

on findRecordByName me, nameToFind
  set currentRecord = firstRecord
  repeat while TRUE
    if the name of currentRecord = nameToFind then return #ok
    if the nextRecord of currentRecord = EMPTY then return #notFound
    set currentRecord = the nextRecord of currentRecord
  end repeat
end

What happens here is that the currentRecord property is set to point at the first record in the chain (set currentRecord = firstRecord). This record is examined to see whether the name matches the name we are looking for, and if so it returns #ok (if the name of currentRecord = nameToFind then return #ok). If the record is not what we are looking for, we simply move on to look at the next record in the chain by setting the currentRecord to equal the nextRecord of the currentRecord. If the nextRecord property is equal to EMPTY, then we know we are at the end of the chain and we return the #notFound value. Otherwise we carry on looping our way through the chain until we find what we want.

If the search is successful, the database object’s currentRecord value will be pointing at a record that matches our search criteria. Now we need a way of getting at the information in the current record. We can do this as follows;

on getCurrentRecord me
  return [the name of currentRecord, ¬
    the email of currentRecord, ¬
	the phone of currentRecord]
end

So, we can now search our database by issuing the following lingo commands;

set dbObj = new (script "dbase", "userData")
set searchResult = findRecordByName (dbObj, "Andy Wilson")
if searchResult = #ok then put getCurrentRecord (dbObj)
-- ["Andy Wilson", ¬
  andyw@dircon.co.uk,¬
  "0171-729-6485"]

So far, so good. Now, however, we need to write code that allows us to add new records to the database. Perhaps we collected the new data by asking the user to input it from the keyboard, and now we want to add it to the record chain. If you’ve followed me this far, I think you’ll agree that this is quite simple to do;

on addRecord me, iname, iemail, iphone
  set newRecord = new (script "user")
  set the name of newRecord = iname
  set the email of newRecord = iemail
  set the phone of newRecord = iphone
  set the nextRecord of lastRecord = newRecord
  set lastRecord = newRecord
  set numRecords = numRecords + 1
end

Here we simply create a new record object just as we did when we initialised the database, and then add the object to the end of the record chain with the lines ‘set the nextRecord of lastRecord = newRecord’ and ‘set lastRecord = newRecord’. All that is left to do is to increment the record counter. We can call this handler as follows;

put the numRecords of dbObj
-- 2
addRecord (dbObj, "Sophie Clare","sophie@dircon.co.uk","0171-729-6485")
put the numRecords of dbObj
-- 3

Our last task would be to write a function which saves all of the data back to the field cast member – which we could then use to initialise the database object the next time we run the application. To save the data, all we need to do is write a line to the field cast member for every record in the database;

on saveData me
  set txt = EMPTY
  set currentRecord = firstRecord
  repeat while TRUE
    put the name of currentRecord & "," after txt
    put the email of currentRecord & "," after txt
    put the phone of currentRecord after txt
    if the nextRecord of currentRecord = EMPTY then exit repeat
    put RETURN after txt
    set currentRecord = the nextRecord of currentRecord
  end repeat
  set the text of field sourceDataField = txt
end

The function begins by initialising a local txt variable to EMPTY. The repeat loop that follows works rather like the search function in that it starts by winding back to the first object in the record chain (set currentRecord = firstRecord), and then loops it’s way through the rest of the chain. For each object, it writes this record to a new line in the txt variable. On completing the loop it writes the resulting txt into the field sourceDataField, where sourceDataField is a text property that is initialised by the database object’s new handler to equal the name of the field used to initialise the database. Of course, it would be easy to make the code write to a completely new field instead, if that is what you want to do.

The most obvious way to use this code would be to put the initialisation text field into a separate cast library which you install onto the user’s hard drive. Then, every time you save new data to the field you could use the save castlib lingo handler to save the updated data.

Before you decide to use this method in a real project you’ll need to consider a few things. First, note that between initialising the database and saving it back to the file, all data is held live in memory. This has the important disadvantage that it limits the amount of data you can hold in a database to whatever it is practical to load into the user’s memory. However, if the data you are storing is all text, integers and floats, you may be surprised at how many records you can safely use in this way. Nevertheless, you need to be confident that the database is never going to be expected to grow beyond whatever the target machine’s memory can handle.

On the other hand, this technique of loading all the database records into memory as separate record objects and then chaining the objects together is extremely efficient when it comes to searching the database. Because all of the objects are in memory it is both easy and very quick for the application to search the entire database - in a simple test my database was able to search it’s way through 2000 records in only a fraction of a second.

The final thing you need to consider is what would need to be done to make this code useable in a real-world application. Certainly you would want to input validation on the fields to make sure that the input data was in the correct format. There are also many things you could do to enhance the functionality of the code. As an exercise you might like to think about how you might implement the following improvements;

• Give all the records a previousObject property so that you can search backwards through the chain as well as forwards.

• Modify the code so that the first line of the userData text field describes the data type of each field in each record (so that a field may be, say, a string, integer or float). Now modify the user script code so that the data in each field is cast to the correct data type in the object. For example, if the second field of each record is supposed to be an integer, a string value of "10023" in the input field is cast into the integer 10023 in the object.

• Add a field to the database that refers to a bitmap in the cast library (perhaps using the name of the cast member) or the path to an external image file, so that different records could refer to different images.

• Build a handler in the database object so that you can bookmark any particular record.

• Write a handler for the database object that allows you to sort the record chain according to any field you choose.

• Write a handler that creates a record subset of all records that match a certain criterion.

You’ve probably got the idea by now – with enough ingenuity it is possible to build fairly complex functionality into your Director database. The important thing is that you realise both the strengths and limitations of using Director in this way. But if you are sure you won’t need the power of a database Xtra then you should find that you are able to achieve some very sophisticated data manipulation effects in Director alone.

As usual I’ll finish by reminding you that comments on the series and suggestions for future articles can be mailed to me at andyw@dircon.co.uk.