Using the V12 Xtra, Part 2

May 10, 2001
by Phillip Kerman

In last week's introduction to the V12 Xtra, we looked in a general way at the advantages of using a database in your Director movie. In this conclusion, we'll examine the specifics of getting V12 to fetch what you want by creating data, importing it into the V12 Xtra's file format, and using the methods of the V12 Xtra to sort and retrieve.

Under the Hood

I built a sample program for this article that performs some standard database maneuvers on some mocked up data. I built three features that might could be useful in a real project. First, you can sort the current records by clicking on a column heading (like how Windows Explorer works in "details" view). Also, you can quickly jump to the closest match when you start typing into a "quick search" field. Finally, you can reduce the current list of records to only those that match a criteria that the user creates. See figure 3. As it turns out most of the work was just plain old Lingo. Once the V12 file was created and imported, I used a total of 4 methods from the V12DBE!

Figure 3: While it may not look like much, this sample program exhibits some pretty fancy sorting and searching features.

You follow a general process. First, design and populate the database; then, describe the data so you can make a .v12 file; then, import and start performing operations on the database. Here's the details for the process I followed in this sample project.

Populate the Database

Like most database projects a good deal of time is spent designing and populating the database. In this case, I decided each record would have a "firstname", "lastname", "sex", "city", "state", and "idnum". It always makes sense to identify at least one field in a database that will contain a value that remains unique to all other records. Whether you use a social security number or an arbitrarily assigned value it doesn't matter. In my example there could be two John Smiths from Springfield, Oregon but they would each have a unique "idnum". I populated my database with an offline Lingo script that randomly created values for the various fields (any correlation to actual people's names is purely coincidental). The data was saved in a tab-delimited text file containing the description of each column in the first line (Figure 4).

Figure 4: The data for our sample project is just a simple tab delimited text file.

This script was simply used to produce some fake data to populate our database:

allLines=""

maleNames = ["Phil", "Joseph"...]
femaleNames = ["Suzy", "Cindy"...]
lastNames = ["Smith", "Robbins"...]
sexes = ["f", "m"]
cities = ["Springfield","Montgomery"...]
states = ["Alabama", "Arizona"...]
repeat with n=1 to 200
  sex = random (2)
  if sex = 1 then
    first = femaleNames[random (femaleNames.count)]
  else
    first = maleNames[random (maleNames.count)]
  end if

thisLine = n & TAB & first & TAB & lastNames[random (lastNames.count)] & TAB & sexes[sex] & TAB & cities[random (cities.count)] & TAB & states[random (states.count)]

allLines = allLines & RETURN & numToChar(10) & thisLine

end repeat

Render a V12 File

There are a series of scripts you need to run in order to convert a tab-delimited text file into a .v12 file. You can use the utility from Integration New Media, or you can do it by hand. In any case you need to provide a description of the data. At a minimum, you need to specify the name of and data type (like string or integer) for each field in the database. Additionally, you can specify that an index will be created based on whatever criteria you want. For example, if you to speed searching or sorting by last name in alphabetical order you can create an ascending index by last name. Here is the form of such a description:

[TABLE]
tableName

[FIELDS]
idNum integer indexed
firstname string indexed
lastname string indexed
sex string indexed
city string indexed
state string indexed

[END]

This description can be stored in a text file, a text cast member, or simply typed into the utility tool available from Integration New Media. For this example, I'm only making one table (called "tableName") as it's just a flat database. Multiple tables can be defined to create relational databases. Finally, although it's probably not really necessary, I indicated that every field needs to be indexed. Without explaining every detail in the description above I should mention V12's documentation explains it as well as providing some "theory of databases".

With the description file created you execute three basic V12 operations in order to create the .v12 file. In my sample file I keep these scripts in a cast member called "Offline V12 Operations". Specifically, you create an instance of the V12 Xtra by using Lingo's new function so that a reference to the Xtra is stored in a variable (I used gDB). At instantiation time, you designate the name for the .v12 file that you want to create, as well a create parameter to make the file . You identify the description information (stored in a text cast member) with the mReadStructure method, then invoke the mBuild method (which effectively ties the description to the .v12 file just created). You then "let go" of the object by simply assigning it a new value (gDB = 0).

-- Create an instance in "Create" mode
gDB = new (Xtra "V12dbe", the pathname & "myDatabase.v12", "Create", "")

-- Read the database descriptor
mReadDBStructure (gDB, "literal", field "structure")

-- Build the database with this descriptor
mBuild (gDB)

-- Close the database file
gDB=0

Next, you create a table for the new .v12 file ("myDatabase.v12"). Since we're about dump the actual data into the .v12 file we open it in "ReadWrite" mode. The second line below creates a reference pointer to the table "tableName" inside the .v12 file and stores that reference in the global variable gFullTable (used in the next section).

-- Create a new instance in "ReadWrite" mode
gDB=new (Xtra "V12dbe",the pathname & "myDatabase.v12","ReadWrite","")

-- Set gFullTable as a reference to the table called "tableName" in the database
gFullTable=new (Xtra "V12table", mGetRef (gDB), "tableName")

Finally, we just need to dump the data into the .v12 file then let go of the objects:

mImportFile (gFullTable, "TEXT", the pathname & "source.txt", TAB, RETURN)
-- Clear the objects
gDB = 0
gFullTable = 0

This effectively says, take the tab-delimited text data from the file "source.txt" and put it in the gFullTable (which is a reference to the table "tableName", created when we provided the description information). It might seem like an unnecessary layer of work to make a table inside the database, but realize that a single database can include several tables -- we're just using one in this case.

At this point the V12 file is created! Keep in mind the whole process above can be done "offline" -- that is separate from and before you deliver the final movie. Any time you change the database you need to re-run the above scripts to render a new .v12 file. However, this new .v12 can simply replace an older version.

Performing Operations with the Database

As you're about to see, all that preparation work means actually using the database will be a cake walk. First, you can open the .v12 file and make a reference to the table (or tables) you intend to use.

-- Create a new instance in "ReadOnly" mode
gDB=new (Xtra "V12dbe", the pathname & "myDatabase.v12", "ReadOnly", "")

-- Set gFullTable as a reference to the table called "tableName" in the database
gFullTable = new (Xtra "V12table", mGetRef (gDB), "tableName")

This code should look familiar. We're just making a reference to the table with which we intend to work.

At this point, we're practically home free. In order to select some or all of the records in a table V12 uses sort of a two-step process. You first specify the kind of selection you're about to make, and then you make it. Consider this code for effectively selecting all the records:

mSetCriteria (gFullTable, "idnum", ">",0)
mSelect (gFullTable)

The first line specifies a criteria ("idnum > 0") which will effectively match all records. The second line invokes the mSelect method. At this point, all the records are selected. However, we'll likely want to do something with those records. The easiest way to manipulate a lot of data in Director is when that data is stored in a list. There's a great method as part of the V12 Xtra called mGetSelection which lets you take everything currently selected (from the most recent mSelect encountered) and dump it into a property list. The following code places everything selected into my variable gCurrentList

gCurrentList= mGetSelection (gFullTable, "PropertyList")

The mSetCriteria method can also be used to reduce the selection -- as we want when the user wants to narrow the selection to only those records that match a particular first and/or last name. The following code will set a criteria that either "firstname" is "Phillip" or "lastname" is "Kerman":

mSetCriteria(gFullTable, "firstname", "=", "Phillip")
mSetCriteria(gFullTable, "OR", "lastname", "=", "Kerman")

Notice the second time we included an additional parameter "OR" which forced v12 to add this criteria to one established in the first line. You'll want to remember to follow the above lines of code with mSelect (to reselect) and mGetSelection to dump the data into a property list:

mSelect (gFullTable)
gCurrentList= mGetSelection (gFullTable, "PropertyList")

Finally, for the feature that allows the user to change the sort order (when they click a column heading) we can use the mOrderBy method. You simply provide the table reference and the name of the field you want to sort by. So, for example, to sort by the "lastname" field use:

mOrderBy (gFullTable, "lastname"))

(Don't forget to follow this with both mSelect and mGetSelection.)

What else?

That's it! The truth is that once you design your database there's not a whole lot of sophistication involved in sorting through the data. If you poke around in the sample file I made you'll see there is actually a fair bit of Lingo. But really, once the file is opened you'll see nothing more than four V12 methods (orderBy, mSetCriteria, mSelect, and mGetSelection). After all, simply having the selected data in a property list still requires that you figure out how to display it onscreen. It's not rocket science to, for example, place the "lastname" of the first record in gCurrentList into a field member named "lastName":

member ("lastName").text = gCurrentList[1][#lastname]

I only wanted to include the information specific to V12 in this article. I figured it is sort of "your deal" how to handle and display the data. It may turn out to be the largest portion of your project.

Summary

Even if you don't use a database in your project you should always try to separate data from code. The benefit of using a product like V12 (in addition to forcing good code-data separation) is that an enormous amount of data can be accessed quickly. In this way your project can scale to any size. It's not that V12 can do anything by itself--you'll still need plan your project and structure the data. It's just that when the time comes to access the database, V12 makes that interchange quick and easy.

The download for this article contains a projector and Xtras, the source Director file, and a Director movie that generates randomized data, along with the sample V12 database file in Mac (2.6MB) or Windows (1.8MB) format.

Phillip Kerman is an independent programmer, teacher, and writer. His books include Sams Teach Yourself Flash 5 in 24 Hours and ActionScripting in Flash.