Mapping the SQL Database

  • By Michael Garrison 2 Years Ago

An updated version of the below routine is now available in the Rock Shop as a free plugin! It's listed in the "General" category and is called SQL Map.
Feel free to follow along below if you want to see how it works, but if all you want is the map of database relationships, head on over to the store package and just install it!

Disclaimer: if you're allergic to SQL, you don't need to read this post =) 

Rock customizers seem to largely fall into two camps- the developer-type who are quite comfortable with C# and the ways that you can access Rock data that getting into the source code offers, and those of us who use a lot of DynamicData blocks and SQL - often with help from the community. (Which, as I hope you've figured out by now, is a pretty stellar community and an incredible resource).

And while the ModelMap is an incredible tool for the C# folks and Lava relationships, it doesn't quite explain how to JOIN the database tables to get from one entity to another. And guessing at it results in some understandable and easy-to-make mistakes like assuming that a table's [PersonAliasId] column should be joined to the [Person].[Id] column (it shouldn't).

With help from the community a few months ago, I came up with a SQL query that examines the defined Foreign Key relationships defined in the database, and provides a "map" that shows the links from one table to another- it's extremely helpful when you're trying to create your own SQL queries.

I've delayed writing this post for awhile because updating the ModelMap block to share database relationships is on a to-do list for core. But enough people have asked for the code now, I think it's probably just best to share the code here so we can point people to this article in the meantime.

Create a new page in your Rock installation (I put mine under Admin Tools) and add a Dynamic Data block to it.

Use this query:

  OBJECT_NAME(f.parent_object_id) AS TableName,
  COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
  OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
  COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName, AS ForeignKey
INTO #tempFKs
  sys.foreign_keys AS f
  INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id;

select 'TableName' , 'ColumnName' , ISNULL(r.[TableName] + '.' + r.[ColumnName], '') 'ReferencedBy' , ISNULL(rb.[ReferenceTableName] + '.' + rb.[ReferenceColumnName], '') 'References' from sys.columns c inner join sys.objects o on c.object_id=o.object_id LEFT JOIN #tempFKs r ON r.[ReferenceTableName]=o.[name] AND r.[ReferenceColumnName]=c.[name] LEFT JOIN #tempFKs rb on rb.[TableName]=o.[name] AND rb.[ColumnName]=c.[name] order by,c.column_id;

And this Formatted Output:

{% assign tableName = '' %}
{% assign columnName = '' %}
{% assign rs = '' %}
{% assign rb = '' %}

{% for row in rows %} <!-- start a new row? --> {% if columnName != row.ColumnName or tableName != row.TableName %} <!— end row if not the first item --> {% if columnName != '' %} <td>{{rb}}</td><td>{{rs}}</td></tr> {% endif %} {% assign columnName = row.ColumnName %} {% assign rs = '' %} {% assign rb = '' %} <!-- start a new table? --> {% if tableName != row.TableName %} <!-- end table if not the first item --> {% if tableName != '' %} </table> {% endif %} {% assign tableName = row.TableName %} <a name="{{ row.TableName }}"></a><h2><a href="#{{ row.TableName }}"><i class="fa fa-chain"></i></a> {{ row.TableName }}</h2> <table class='table table-striped'> <tr> <th style="width:20%;">Column</th><th>Referenced by</th><th>References</th> </tr> {% endif %} <tr><td><a name="{{ row.TableName }}.{{ row.ColumnName }}"></a>{{ row.ColumnName }}</td> {% endif %} {% if row.References != empty %}{% capture rs %}{{ rs }}<a href="#{{ row.References }}">{{ row.References }}</a><br />{% endcapture %}{% endif %} {% if row.ReferencedBy != empty %}{% capture rb %}{{ rb }}<a href="#{{ row.ReferencedBy }}">{{ row.ReferencedBy }}</a><br />{% endcapture %}{% endif %} {% endfor %}


And there you go- now you have a page with a clickable list of known links between the database tables. Note this is probably 95% complete, but there some "many to many" links which won't show up in this list.

Note that you can append #tablename (such as /page/123#person ) to the page's address to jump right to that table.




One of the notable missing relationships has to do with getting Attribute Values- whether it's for a person, a workflow, a group, etc, that's a pretty fundamental lookup that you'll need access to. Here's an example of how to get the value of the BaptismDate attribute for a Person with ID 123:

    p.[NickName] + ' ' + p.[LastName] AS "Name"
    [Person] p
    LEFT JOIN [AttributeValue] av ON p.[Id]=av.[EntityId]
    LEFT JOIN [Attribute] a ON av.[AttributeId]=a.[Id]
    LEFT JOIN [EntityType] e ON a.[EntityTypeId]=e.[Id]
    AND a.[Key]='BaptismDate'
    AND p.[Id]=123

In English, just understand that the AttributeValue's EntityId value points to the ID column in the Person table...but ONLY for person attributes as identified by the Attribute.EntityId column: 15 should be the value to indicate it's a person attribute (as listed in the EntityType table). Attributes can also be workflow attributes, group attributes, etc, which can all share ID numbers (meaning there can be a Group with ID 123 that has nothing to do with the person with ID 123), so be sure that your queries either check the EntityId as in the example above or else that you're otherwise restricting your query to attributes which are definitely the ones you want.

For instance, leaving off the e.[FriendlyName]='Person' filter above would also return workflow attributes if workflow 123 happened to have an attribute with key BaptismDate. If you wanted to omit that check, you could always look up the Attribute ID of the person attribute you wanted (in the case of BaptismDate, it would be a.[Id]=174 so you could include that filter in the WHERE clause instead of the e.[FriendlyName] and a.[Key] filters).

Spark Development Network
Flagstaff, AZ

Michael Garrison recently left his job in Architecture to become one of the "new guys" at Spark (the "Core Team"), but he's still helping out Christ's Church of Flagstaff and other non-profits with tech needs in his off-hours, trying to make computers do what computers do best, so that people are freed to do what we do best: relate with people!