Articles Archive
Articles Search
Director Wiki
 

Using databases to drive multimedia projects

December 1, 1998
by Michael Geary

Using databases to drive multimedia projects

A recent question on the Direct-L list arose:

"There always seems to be a lot of inquiries about database xtras. My experience with databases is pretty minimal and so I'm wondering what people are storing in their databases and what functionality those databases are bringing to their Director apps. Is the main use text searchable document storage, or am I missing something more interesting?"

'Interesting' is indeed the way to describe it. Databases are not simply text repositories, they allow us multimedia developers to add a great deal of flexibility and simplicity to our projects.

As an example I will discuss a project I am working on. It is a Corporate Design Specifications CD-ROM. This CD, which will be delivered to the client's design agencies, has design specs on every possible aspect of corporate design.

The client had divided their specifications into a number of categories and sub-categories, for example:

Brochures
        2-page
                example 1 (with info and details)
                example 2 (with info and details
                example 3
                example 4
        4-page
                example 1
                example 2
                example 3
                example 4
Posters
        Horizontal
                example 1
                example 2
                example 3
                example 4
        Vertical
                example 1
                example 2
                example 3
                example 4
.
.
.

and so on.

This heirachical structure is an ideal candidate for a database-driven application. Let's convert this quasi-outline form into a structure that relies upon numbers as "addresses":

1       Brochures
        1               2-page
        1               example 1
        2               example 2
        3               example 3
        4               example 4
        2               4-page
        1               example 1
        2               example 2
        3               example 3
        4               example 4
2       Posters
        1               Horizontal
        1               example 1
        2               example 2
        3               example 3
        4               example 4
        2               Vertical
        1               example 1
        2               example 2
        3               example 3
        4               example 4
.
.
.

and so on.

We can now represent any item in this structure with a numbered address. For example, 1-2-1 is Example 1 of a 4-page Brochure. We can also use zeros to get the addresses of headings. For example, 1-0-0 is the supercategory "Brochures". 1-1-0 is the sub-category "2-Page" in the supercategory "Brochures."

For convienience, we will represent this three digit address with a tree metaphor. We will call the topmost category "Tree", the first subcategory we will call "Branch", and the actual item we will call "Twig."

This now gives us a starting point for our database design. Here is a sample record. We can add whatever fields are necessary beyond Tree, Branch, and Twig. The ones that follow are my suggestions:

Field Value Type Indexed?
Tree: 1 Integer Yes
Branch: 1 Integer Yes
Twig: 1 Integer Yes
Name: "example 1" String Full-Indexed

The Status field is a simple switch that I can flick to either "Publish" or "No Publish". This way I can dynamically include or exclude single items or entire chapters of my project by simply switching the Status switch back and forth. Again, for speed's sake this String field could be replaced with an Integer field of 1 and 0, but our database will still be plenty fast as it is.

I have also included the Index information for each field. It is important to note that any field that will or could be searched needs to have an index. Any String field that you wish to use in Full-Text searches needs to be Fully-Indexed.

Okay, we now have a database structure. The next step is to create a complete database. To this end, I recommend you use any user-friendly database, such as Filemaker Pro, or (if you really feel you must) Microsoft Access. Because the information in our databank is not so much "content" as "REFERENCES to content", we can rest assured that our database will be very small and very fast.

Let's take a quick step back now and catch the vision of this way of working. In a Director project, using the V12 database engine, I can now immediately access the information of any of, if necessary, thousands of Corporate Design Specifications by simply requesting a three-digit address.

If I do a Find for Tree=1, for example, I am rewarded with an immediate selection of all of my brochure information. If I do a Find for Tree=1,Branch=2, I immediately receive ALL of my 4-Page Brochure examples. If I do a Find for Tree=2,Branch=1,Twig=3, I am presented with only one record: Example 3 of the Horizontal Posters.

We utilize the Status field by coupling every search with an AND boolean search for Status="Publish". This allows us to exclude any records we want to by simply setting their Status field to "No Publish."

Because our Address and Status Fields are indexed, this searching takes place lighting fast. Our only (very marginal) speed hit comes when and if we need to use Lingo to process the Search Results.

But the advantages of this database-driven structure are not simply speed. Let's suppose that the client suddenly decided to cancel ALL of their brochures, and wanted them immediately removed from their Corporate Design Specifications. Had we (heaven forbid) tried to create this project using frames in the Director Score, we would now be faced with the daunting task of deleting possibly hundreds of frames, and then adjusting all scripts which pointed to those frames.

If you want a sure recipe for a headache, simply try the above.

With our database-driven project, however, all we have to do is a quick search for TREE=1, and either delete all the found records (if we really don't think we'll ever use these records again), or simply set their Status to "No Publish."

That's it. We're done. No headache, no mess. Because all the menus, graphics and navagation elements in our project (we'll get to this in a bit) are dynamically generated based on the database, everything will update itself automatically.

Wow. Now we are starting to see why databases are so much more than simple "Text Repositories."

Okay. Enough pondering for now. Let's actually turn to Director now and make some tangible results.

First we need a database to drive our project. I strongly recommend you use the V12 database Xtra from Integration New Media. It is cross-platform, the calls to the Xtra are easy to understand and use, and the company is nearly legendary for their great support. However, while all code that follows is based on the V12 Xtra, it is certainly possible to adapt the processes to the database Xtra of your choice.

Using the V12 Database Creator, we can quickly and easily create a V12 database, into which we will import the results of our Filemaker Pro database.

I have supplied a database with the data already imported. The Lingo code we are about to make will refer to this database by name, so the name has to be "content.v12." You can either create your own, or use the one supplied (all files for this article are available in Mac or PC format).

To help give an overview, I have also provided the tab-delimited text file which was imported into my database. If you create your own, you will need to export your data from Filemaker Pro as tab-delimited text, and then import it into your V12 database. Again, you can use the V12 Database Creator for this.

Okay, now create a text field on stage. Name it "Tree" and make it as wide as your longest Tree category name. Do the same with a "Branch" and "Twig" field. Now create three more text fields on stage. Name them "TreeFull", "BranchFull" and "TwigFull." These will be the containers of our popup menus. In our finished project, these menus will be located off-stage (hidden), and when their 'parent' is clicked, they will be automatically positioned in the right place. See picture 1 for a snapshot of my menus.

Now you need to add a behavior to the Tree, Branch, and Twig which will automatically read information out of the database and populate a popup-menu, as well as act upon the menu item which is selected. And voila, here is just such a behavior!


property pSpriteNum
property pMyMember
property pPopSprite
property pPopMember
property pMyType
global gCurrentTree
global gCurrentBranch
global gCurrentTwig

on getPropertyDescriptionList
  
  set d = [:]    
  
  addProp d, #pPopSprite, [#default: 1, #format: ¬
    #integer, #comment: "Full Menu Sprite Num"] 
  addProp d, #pMyType, [#default: "tree", #format: ¬
    #string, #range: ["tree", "branch", "twig"], ¬
    #comment: "enter the type"]
  
  return d
  
end

on BeginSprite me
  
  set pSpriteNum = the currentSpriteNum
  set pMyMember = the member of sprite pSpriteNum
  set pPopMember = the member of sprite pPopSprite
  
  --hide the popup menu
  set the locH of sprite pPopSprite = -200
end

on mouseDown
  
  --populate the 'full list' member
  if pMyType = "tree" then
    set the text of field pPopMember = getTrees()
  else if pMyType = "branch" then
    set the text of field pPopMember = ¬
      getBranches(gCurrentTree)
  else if pMyType = "twig" then
    set the text of field pPopMember = getTwigs(¬
      gCurrentTree,gCurrentBranch)
  end if
  
  --place the 'full list' member
  PlacePopmenu(pSpriteNum,1,pPopSprite)
  
  -- track mouse
  set lastSelection = 0
  set len = the number of lines in field pPopMember
  
  repeat while the stillDown
    set mc = the mouseLine
    
    if (mc > 0) and (mc < len) and (rollover¬
      (pPopSprite)) then
      if mc <> lastSelection then
        set lastSelection = mc
        set the forecolor of field pPopMember to 255
        -- black
        set the forecolor of line mc of field ¬
          pPopMember to 35
                -- red
        updateStage
      end if
    else
      -- out of menu area: remove highlight
          
      if mc <> lastSelection then
        set lastSelection = -1
        set the forecolor of field pPopMember to 255
        -- black
        updateStage
      end if
    end if
  end repeat
  
  if (lastSelection <> -1) and (line lastSelection ¬
    of field pPopMember) <> "" then
    
    put line lastSelection of field pPopMember into ¬
      field pMyMember
    set pLastSelectedItem = lastSelection
    
  end if
  
  if lastSelection <> -1 then
    
    if pMyType = "tree" then
      set gCurrentTree = lastSelection
      set gCurrentBranch = 1
      set gCurrentTwig = 1
      set the text of field "BranchFull" = ¬
        GetBranches (lastSelection)
    else if pMyType = "branch" then
      set gCurrentBranch = lastSelection
      set gCurrentTwig = 1
      set the text of field "TwigFull" = GetTwigs ¬
        (gCurrentTree,lastSelection)
    else if pMyType = "twig" then
      set gCurrentTwig = lastSelection
    end if
    
    --update current listings
    GoToAddress(gCurrentTree,gCurrentBranch,¬
      gCurrentTwig)
    
  end if
  
end

on mouseup
  
  --hide the full list sprite
  set the locH of sprite pPopSprite = -200
  
end

Whew. First we need to mention that the menu-item highlighting code comes to us via Integration New Media's example movie "Search by Menu", and the behavior itself was facilitated by Roy Pardi's great "Behavior Writer" Xtra.

Drag this behavior onto your first three fields (menus), and indicate whether said field is a Tree, Branch or Twig. Also indicate which Sprite Channel contains the appropriate "Full" field sprite.

Now take a look at the body of this behavior. In the BeginSprite handler we simply check the ID's of the associated Sprites and Members. In the MouseDown handler, however, things really go crazy. When the menu is clicked, the popup menu (the "full" member we created earlier), is dynamically filled by making calls to the database. In fact, if you trace the program flow, you see that in the split second between when you click on a menu, and when the popup menu appears we actually make several calls to the V12 database. On my computer (Power Mac 8600/200) these lookups and the populating of the popup menu take between 2 and 3 ticks! That's fast.

Once the popup menu is showing, we have a simple mouse-tracking sequence, and once the mouse button is released, we instantly jump to the new Tree/Branch/Leaf that was selected. Again, this step involves several calls to the database, and again, it all happens lightning fast. What I'm trying to stress here is that at least with V12, you don't have to worry about sparing calls to the Xtra. They happen at least as fast as if you were working with a Lingo list.

Now, before we discuss our project too much further, let's look at the rest of the scripts needed to actually make this baby tick.

The movie script handlers which our behavior relies upon are:

GetTrees: this will return a list of all the super-categories, or "trees"
GetBranches: given a "tree", this will return a list of all the sub-categories
GetTwigs: given a tree and branch, this will return a list of all the items
PlacePopMenu: this gives us popup menu functionality
GoToAddress: We can give this handler an address (1,0,1) and it will populate the menus accordingly and display the appropriate information.

So, copy the following into an empty movie script cast member:


---V12 Movie Script
Global gDB, gTable
global gCurrentTree,gCurrentBranch,gCurrentTwig

on GetTrees
  mSetCriteria (gTable,"branch","=",0)
  mSetCriteria (gTable,"AND","twig","=",0)
  mSetCriteria (gTable,"AND","status","=","publish")
  mSelect(gTable)
  set AllTrees = mGetSelection(gTable, "LITERAL", ¬
    1, mSelectCount(gTable), TAB, RETURN, "name")   
  put return after AllTrees
  return AllTrees
end

on GetTreeName whichTree
  mSetCriteria (gTable,"tree","=",whichTree)
  mSetCriteria (gTable,"AND","branch","=",0)
  mSetCriteria (gTable,"AND","twig","=",0)
  mSetCriteria (gTable,"AND","status","=","publish")
  mSelect(gTable)
  set thisTree = mGetSelection(gTable, "LITERAL", ¬
    1, mSelectCount(gTable), TAB, RETURN, "name")
  return thisTree
end

on GetBranches whichTree
  mSetCriteria (gTable,"tree","=",whichTree)
  mSetCriteria (gTable,"AND","branch","<>",0)
  mSetCriteria (gTable,"AND","twig","=",0)
  mSetCriteria (gTable,"AND","status","=","publish")
  mSelect(gTable)
  set AllBranches = mGetSelection(gTable, "LITERAL", ¬
    1, mSelectCount(gTable), TAB, RETURN, "name )
  put return after AllBranches
  return AllBranches
end

on GetBranchName whichTree,whichBranch
  mSetCriteria (gTable,"tree","=",whichTree)
  mSetCriteria (gTable,"AND","branch","=",whichBranch)
  mSetCriteria (gTable,"AND","twig","=",0)
  mSetCriteria (gTable,"AND","status","=","publish")
  mSelect(gTable)
  set ThisBranch = mGetSelection(gTable, "LITERAL", ¬
    1, mSelectCount(gTable), TAB, RETURN, "name")
  return ThisBranch
end

on GetTwigs whichTree,whichBranch
  mSetCriteria (gTable,"tree","=",whichTree)
  mSetCriteria (gTable,"AND","branch","=",whichBranch)
  mSetCriteria (gTable,"AND","twig","<>",0)
  mSetCriteria (gTable,"AND","status","=","publish")
  mSelect(gTable)
  set AllTwigs = mGetSelection(gTable, "LITERAL", 1, ¬
    mSelectCount(gTable), TAB, RETURN, "name")
  put return after AllTwigs
  return AllTwigs
end

on GoToAddress whichTree,whichBranch,whichTwig
  if whichBranch = 0 then set whichBranch = 1
  if whichTwig=0 then set whichTwig = 1
  
  --identify the Tree and Branch
  set the text of field "Tree" = GetTreeName(whichTree)
  set the text of field "Branch" = GetBranchName ¬
    (whichTree,whichBranch)
  
  --identify the Twig
  mSetCriteria (gTable,"tree","=",whichTree)
  mSetCriteria (gTable,"AND","branch","=",whichBranch)
  mSetCriteria (gTable,"AND","twig","=",whichTwig)
  mSetCriteria (gTable,"AND","status","=","publish")
  mSelect(gTable)
  
  set TreeBranches = mGetSelection(gTable, ¬
    "LITERAL", 1, mSelectCount(gTable), TAB, ¬
    RETURN, "name")
  
  set the text of field "Twig" = TreeBranches
  
  --identify how many twigs, and which one this is
  set twigCount = ""
  
  put string(whichTwig) & "/" after twigCount
  
  mSetCriteria (gTable,"tree","=", whichTree)
  mSetCriteria (gTable,"AND","branch","=",whichBranch)
  mSetCriteria (gTable,"AND","status","=","publish")
  mSelect(gTable)
  
  put string(mSelectCount(gTable)-1) after twigCount
  
  
  set the text of field "TwigNum" = twigCount
  
  -- plop in the picture and text
  set theFileLocP = ("t" & whichTree &"b" &  ¬
    whichBranch & "t" & whichTwig & ".pct")
  set theFileLocT = ("t" & whichTree &"b" &  ¬
    whichBranch & "t" & whichTwig & ".txt")
  
  --if the picture exists
  if the number of member theFileLocP of castlib ¬
    "pictures" > 0 then
    set the member of sprite 1 = theFileLocP
  else
    set the member of sprite 1 = "NoGraphic"
  end if
  
  --if the text exists
  if the number of member theFileLocT of castlib ¬
    "texts" > 0 then
    set the member of sprite 2 = theFileLocT
  else
    set the member of sprite 2 = "NoText"
  end if
  
end

---end movie script

And finally we need to supply the open/close movie handlers. These will create an instance of the V12 database, open the database, and go to the first "Twig" in our content. There is also a handler to create a popup menu effect.

Copy this last example into an empty Movie Script cast member, and we'll take a look at it:


---start movie script
Global gDB, gTable
global gCurrentTree
global gCurrentBranch
global gCurrentTwig
on startMovie
  
  InitializeThings()
  
end startMovie

on stopMovie
  ClearGlobals()
  
  set  the text of field "treefull" = ""
  set  the text of field "twigfull" = ""
  set  the text of field "branchfull" = ""
  
end stopMovie

on InitializeThings
  
  ClearGlobals()
  
  OpenDB()
  -- opens DB and initializes gDB and gTb
  
  set gCurrentTree =1
  set gCurrentBranch =1
  set gCurrentTwig =1
  
  gotoAddress(gCurrentTree,gCurrentBranch,¬
    gCurrentTwig)
  
end InitializeThings

on OpenDB
  -- open database in ReadWrite mode
  set gDB = new(xtra "V12dbe", the pathname& ¬
    "Content.V12", "ReadWrite", "")
  -- create the table instance in which
  -- data must be imported
  set gTable = new(xtra "V12table", mGetRef(gDB),¬
    "thetable")
end OpenDB

on PlacePopmenu ParentMenuSprite,ParentLine, ¬
  ChildMenuSprite
  
  --get the position of the parent
  set PLeft = the left of sprite ParentMenuSprite
  set PTop  = the top of sprite ParentMenuSprite
  --set PWidth = the width of sprite ParentMenuSprite
  
  --identify the line height
  set DownFromTop = LineHeight(the member of sprite ¬
    ParentMenuSprite,ParentLine)
  
  --place the submenu
  set the locH of sprite ChildMenuSprite = PLeft
  set the locV of sprite ChildMenuSprite = PTop ¬
    + DownFromTop
  
  updatestage
  
end
---End movie script

Again, portions of this code were provided by the good folks at V12.

Now let's touch bases with our Director Movie to make sure we're on the same base. We've got six fields (all on screen), one behavior in the cast, and two movie scripts in the cast. Our behavior has been dropped on the three "menu" fields, and we have indicated in the behavior properties which sprite should act as popup menu for each menu.

Well, assuming you've got a database named "contents.v12" in the same folder as your project, and assuming you've installed the V12 xtra, you should now be able to navigate the menus of your dynamic-structure project--When you click on the "Tree" menu, a popup menu appears with a list of all the "Tree" categories. Once you select a "Tree", you are taken to the first "twig" of the first "branch." From there you can browse the branches and twigs at leisure. Whee, look at you go!

Now if all we wanted to do was click on dynamic menus all day, we'd be done. Unfortunately, it's hard to get paid to do that, so let's talk about using our database to add text and media to the stage. Here again is where we can reap the benefits of using a database. Because every entry in the database has a unique address, we can add as much or as little media as we want, and all we have to do is tie the media to the address.

For example, make two external Cast Libraries. Name one Text and the other Pictures. Now I can already hear some of you saing "Why should we put text into a castlib, we can just put it into the database!" You're absolutely right. In fact, we'd gain some substantial benefits by doing this because we would be able to perform searches on this text.

However, we'll assume for now that our client refuses to settle for non-antialiased text in their project.

Anyway, back to our castlibs. Now place a number of pictures and text into each library. Any pictures (as long as they fit on stage), and any RTF text (ditto) will do. Now give each picture and text a name which ties it to the DATABASE ADDRESS it represents. For simplicity's sake, I recommend "t1b2t3.pct" as a naming scheme, where 't', 'b', and 't' stand for Tree, Branch, and Twig, and the numbers represent the Address Fields in our database. I recommend avoiding using simply numbers ("123.pct"), because you run into trouble with double-digit numbers. By seperating each address with a letter, we will never be confused. Make sure as you name things that you associate a picture and a text with the addresses 111 and 211, so we can all be sure to have the same results in a minute.

Now create one more picture and one more RTF text member. Name them "NoGraphic" and "NoText" respectively. These members will be displayed on stage when our menu item does not have any media assigned to it. I recommend making the graphic the same color as the stage, and recommend further that you simply have a blank space in the text member. Go ahead and place these members on stage.

So, now we've got pictures and text with names that Lingo can figure out based on any given address. Notice that if we wanted to add sound, movies, or any other additional media, it would be very simple to create new castlibs. We don't have to move anything on in the score at all as long as we stick to our naming scheme.

Okay, time to tie things back to our code. Take a look at the GoToAddress handler in our V12 movie script. Here is the segment I'm talking about:


-- plop in the picture and text
  set theFileLocP = ("t" & whichTree &"b" &  ¬
    whichBranch & "t" & whichTwig & ".pct")
  set theFileLocT = ("t" & whichTree &"b" &  ¬
    whichBranch & "t" & whichTwig & ".txt")
  
  --if the picture exists
  if the number of member theFileLocP of castlib ¬
    "pictures" > 0 then
    set the member of sprite 1 = theFileLocP
  else
    set the member of sprite 1 = "NoGraphic"
  end if
  
  --if the text exists
  if the number of member theFileLocT of castlib ¬
    "texts" > 0 then
    set the member of sprite 2 = theFileLocT
  else
    set the member of sprite 2 = "NoText"
  end if
 

All we're doing here is establishing a name for the media based on the address (ex. "t2b1t1.pct"), and determining whether a cast member with that name exists. If it does exists, the picture (or text) sprite on stage is filled with that member, and if it doesn't, the blank members we just created are substituted.

Let's try it out. Slap a "on exitFrame/go to the frame" script in the first frame of your movie, and click on Play. Your three "popup" fields will disappear (they are being yanked offscreen), and your project will go to the address 1-1-1. If you have a picture and text based on the address 1-1-1, your picture and text will be slapped on stage. Now click on the "Twig" menu, select something, and let go. Voila, your menu is updated, as is your picture and text. Now select the second entry in the "Tree" menu. If you have 2-1-1 text and pictures, they appear.

Okay. We're done. You now have a skeleton project which can be converted into almost any outline-type project in a matter of minutes. Using this skeleton, I was able to completely re-create an existing project that was score-based (containing over 5,000 frames), into a one-frame project that was more flexible, significantly easier to update, and faster. This transformation was simple and fast.

Wide-thinking minds can find all sorts of applications for this technology (remember this is just a start). Because databases are read/write, you can also have the user input any information relevant to a particular item, and store it in a database field. You could even have a user preference assigned to every single record in your database. Users could completely customize their own projects. Whee.

I'd be interested in hearing about your experiences with these ideas, and your improvements upon them. Feel free to drop me a line at Mgeary / at / antwerpes.de.

Special thanks to Dirk Pogrzeba for showing me how useful databases can be.

Michael Geary started working with Director at version 4. His pet technologies include multimedia databases, dynamic PDF generation, Binary file generation and XML. After tromping around the world for a while, he has settled down in Utah again. Boy, those mountains sure are big.... Michael can be reached at michael.geary@seranova.com

Copyright 1997-2017, Director Online. Article content copyright by respective authors.