Understanding and using the Database Design Report – Part 1

In developing our BaseElements product we had to develop an intimate knowledge of the Database Design Report ( DDR for short ) that you can produce from FileMaker Pro Advanced. However BaseElements isn’t the only reason you might want to know what the DDR contains or how to interpret it. This is the first in a series of articles about what the DDR is and how to use it. We hope you find it useful.

What is the DDR?

First, you’re going to need a copy of FileMaker Pro Advanced to even generate the DDR. It’s one of the options in the “Tools” menu in FMPA, and there are 2 options for the type of report : HTML or XML.

In an of itself, the XML report is much harder to read, and isn’t really intended to be read directly, like the HTML version is. However the HTML version has some serious limitations. To start with, it’s not cross referenced across multiple files. And more importantly, there isn’t any built in searching functions other than searching the HTML output via your browser.

So there isn’t a way to, for example, list all of the fields of a certain type, you’d just have to go through the fields one by one.

The obvious answer to this problem is to put the data into a database, so we can search it, but we need to have something we can import first.

The XML output is actually in a format we can use. XML is a generic data storage format, and it comes with another tool – XSLT – that is used to transform XML amongst different formats. But before we get to that point, lets take a look at some XML from the DDR so we can understand what we’re doing.

What does the XML look like?

What I’ve done here, is to take the XML version of the DDR, and put it into a text editor that allows me to hide and un-hide parts of the report (hidden parts I’ve replaced with a snip ). This lets me view sections of the data at a time. To start with, here is a view of the DDR collapsed down to just 4 lines :

<?xml version="1.0" encoding="UTF-16"?> <FMPReport link="Summary.xml" type="Report" version="8.5v1"> snip </FMPReport>

There are some bits in that I’ve taken out (the snip part, but there’s also others), but essentially it’s as per the XML.

The first line of the XML is a comment line. It’s differentiated by the <?xml at the start, and is a purely information link about what sort of XML document this is. In using the DDR we can ignore this line.

First of all, anyone who’s familiar with HTML will see it uses a familiar notation. The text contains tags – which is the < and > brackets. Every < must be followed by a > somewhere, otherwise the XML won’t be valid. Within each tag – between the brackets – is the data. The first word after the < character is the tag’s name. As you can see from the example above, there are 2 FMPReport tags. One is an opening tag, and the other is the closing tag. The closing tag is distinguished by the / before the name.

As a side note there are also other ways to have a complete open and closed tag, and that is to use a single self closing tag. This would look like this :
<NotEmpty value="False"/>

The difference is just the position of the / character.

In XML terms, a complete opening and closing tag set, and everything inside it, is called a “node”. This is the first important thing to learn about the XML format. A node may be a single line by itself, or it may contain other nodes. Either way, the nodes contain all of the information we need to know about the solution.

Other than the tag name, the other information contained inside the tag are called attributes. Some examples from the code above :

link="Summary.xml"
or
value="False"

In this case, the attributes are stored as what’s called name value pairs. The attribute has a name, “link”, and a value “Summary.xml”. A tag can have either none, one or many attributes.

So, to review the XML DDR so far, we have :

<?xml version="1.0" encoding="UTF-16"?> <FMPReport link="Summary.xml" type="Report" version="8.5v1"> snip </FMPReport>

The first line is a comment.

The second line is an opening FMPReport tag, that also contains some other attribute values.

The third line has been cut for now, but is the rest of the data.

The fourth line is the closing FMPReport tag, that finishes the FMPReport area of the XML.

All of the XML we need to understand is made up of only these elements. Tags containing either attributes or actual data, which are nested inside other tags, themselves containing other data or attributes.

So how does the XML contain information about my solution?

To better understand what the DDR contains, I’ve indented each of the lines in the XML examples. This is actually very similar to what is actually going on in the XML. So in the example below, the “File” node is contained inside the FMPReport node :

<FMPReport link="Summary.xml" type="Report" version="8.5v1"> <File name="CCP_Worksheet" path="192.168.20.4"> snip </File> </FMPReport>

So you can see that the FMPReport node contains another node : this one called “File”. The File node has some useful attributes, such as name and path that we can see. There is a opening and closing node again so that the File node can also contain other data and nodes.

So now we look one level deeper into the XML :

And I’ve left some details out here, so I hope I didn’t confuse everyone. Assume in the example above that every line has a matching closing tag like :

which I’ve left out to shorten the code a little.

Hopefully some of these node names will be familiar to FileMaker developers. BaseTables, Relationships, Layouts, Scripts and more.

If you think once more about the indenting of the nodes, and what a node contains, then you can see that the FMPReport node contains one and only one File node. And a File node contains a list of other nodes, most ending in “Catalog” as well as an Options node. So what, for example does a BaseTableCatalog node contain? : a list of BaseTables. So lets expand that node, leaving out the others this time, and have another look :

From here on, I’m going to leave out the closing tag, and the “snip” lines for the sake of clarity, so you’ll have to assume they’re there. Obviously there are in a real data file.

So you can see that each BaseTableCatalog node contains a list of BaseTable nodes. And each BaseTable also contains enough information for us to identify the BaseTable, namely an ID and a name, as well as a record count. So what happens if we expand the BaseTable node in our example :

So a BaseTable node contains a FieldCatalog node, which contains a list of Field nodes. Now you can see we’re building up some real information about our FileMaker file. If you go to the next step, and expand the Field node, you’ll see it contains all of the information about the field. In that case, it’s things like field type, calculations, storage, validation, comments etc. Some example nodes from a real DDR field node :

You can see there, that there is enough information to completely recreate this field in a solution. This particular field has an Auto Enter creation date, isn’t being stored and isn’t a global. There is a lot of extra information that appears as “false” and obviously because this isn’t a calculation, we don’t see any of that detail.

All of the rest of the “Catalog” nodes listed above work in the same way. For example the LayoutCatalog node contains a List of Layouts. Each Layout node contains all of the Object nodes to describe the objects in them. Or a ScriptCatalog name contains a list of Script nodes, which contains a list of ScriptStep nodes, etc etc.

There is one other part of the XML DDR I’d like to mention, these are the CDATA sections. They appear as the following :

Essentially the CDATA is a way of having a data section that contains complex text. For example, there is a high likelyhood that some of your calculations use the < or > symbols. If they didn’t appear in a CDATA section, then this would be interpreted by the XML reader as part of a tag and our data couldn’t be imported. So the CDATA section can contain these characters without breaking the XML. It’s bit like the way you have to escape a in a filemaker calculation by using \ or a " by doing ".

I won’t continue further with these examples, but if you understand the basics above you can probably follow along in any DDR file you generate. What I would suggest is that you take one of the DDR files and run it through a text editor that indents XML properly. BBEdit and TextMate on the mac have just such functions, as do any other editors that implement the unix “tidy” command, as it makes the XML much easier to read and follow.

When you’re looking at the XML, just think in terms of the hierarchy and structure of the file. Each node contains other nodes, which in turn contain either data, or other nodes. Some nodes are purely there so that we can read all of the attributes, other nodes like the

Where to from here?

The most important thing to know at this point is that you don’t have to know the entire DDR XML by heart in order to make use of it. In terms of us working with the data, all you need to understand are the concepts of tags and nodes and the way the nodes are structured in a hierarchy with some nodes contained inside others. When you’re working with the DDR, we’ll be tracing a path through that XML from one node to another, but once you figure out what the path is, you don’t need to remember it. And you won’t need to read the XML directly, we have another tool called XSLT that does that for us.

So the next issue we need to tackle is getting this information into a FileMaker database so we can work with it. Can FileMaker import XML? Yes, it can. Can FileMaker import the DDR directly? No. If you’re thinking that’s a huge oversight, it’s not. It’s a function of the way XML works. Every app has it’s own XML format that it understands, and for FileMaker, the DDR isn’t it.

In part 2, I’ll look at what FileMaker can actually import and how you’d setup a file specifically for that. And then in part 3, I’ll demonstrate some XSLT that does the magic of going from one format to another.

Part 2 is now available here.