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.
Copyright 1997-2024, Director Online. Article content copyright by respective authors.