Building a CM SQL Facade
I'm in the middle of an upgrade for a customer for whom I need to recompile an SQL project I previously created. At the same time, another customer needed this exact same type of solution. In both cases they had separate systems which needed to query TRIM/Content Manager via SQL statement. This is not provided out-of-the-box.
Although you can query Content Manger directly via SQL, you most definitely should not. To maintain certification and remain secure, all access to Content Manager should go through the provided SDKs. Therefore, we need scaffold up an interface ontop of the SDK which SQL can leverage.
Programmers call this a facade.
Setting the goal
I'd like to be able to do two things:
- Execute a stored procedure which gives me a list of customers
- Execute a stored procedure which gives me a list of documents for a given customer
To achieve this goal I need to have a record type defined for my documents and a property on them called customers. To keep things simple for the blog posting I'll simply fetch all locations and present those as my customers. Then I'll return all documents that location has authored.
For this blog posting I'm only going to show the list of customers. The logic for documents is something you can test on your end.
Defining the procedures
Based on my goals I'll need to create two procedures: GetCustomers, GetCustomerDocuments. Let's retrieve each customer's URI and name. For the documents, let's retrieve the number, title, and web URL for each record of type document where the record's author matches a given location's uri.
I'll need method signatures which look like this:
[Microsoft.SqlServer.Server.SqlProcedure] public static void GetCustomers() { } [Microsoft.SqlServer.Server.SqlProcedure] public static void GetCustomerDocuments(long customerUri) { }
Coding the procedures
We can output directly to the sql server pipe and send meta-data for each object in Content Manager. To get an object we'll need to execute a search. That requires a connection to the dataset though, so the TRIMSDK must be loaded into the solution (and SQL server). I've chosen the COM SDK because I'm more familiar with it. The .Net SDK, ServiceAPI, or webservice could also be used.
Note: I'm providing the skeleton implementation, which is no where near production ready. Use at your own risk.
[Microsoft.SqlServer.Server.SqlProcedure] public static void GetCustomers() { Database trimDatabase = null; Locations customerLocations = null; try { RemoveLogFile(); Log("Creating Database Object"); trimDatabase = new TRIMSDK.Database(); trimDatabase.Id = "CM"; trimDatabase.WorkgroupServerName = "APPS"; Log("Connecting"); trimDatabase.Connect(); Log("Making Locations"); customerLocations= trimDatabase.MakeLocations(); customerLocations.SearchString = "all"; Location customerLocation = null; if (customerLocations.Count > 0) { SqlMetaData[] md = new SqlMetaData[2]; md[0] = new SqlMetaData("uri", SqlDbType.BigInt); md[1] = new SqlMetaData("customerName", SqlDbType.NVarChar, 50); SqlDataRecord row = new SqlDataRecord(md); SqlContext.Pipe.SendResultsStart(row); while ((customerLocation = customerLocations.Next()) != null) { Log($"Location Found: {customerLocation.FullFormattedName}"); row.SetInt64(0, (long)customerLocation.Uri); row.SetSqlString(1, customerLocation.FullFormattedName); SqlContext.Pipe.SendResultsRow(row); ReleaseObject(customerLocation); customerLocation = null; } SqlContext.Pipe.SendResultsEnd(); Log("Done"); } else { Log("No Locations Found"); } } catch ( Exception ex ) { Log("Error: " + ex.Message); Log(ex.ToString()); } finally { ReleaseObject(customerLocations); customerLocations = null; ReleaseObject(trimDatabase); trimDatabase = null; } }
Installing the assemblies
After compiling the solution you have to load the assembly and COM SDK Interop into the SQL Server database, like shown below:
Add the assemblies with unrestricted access and dbo as owner.
Creating the SQL procedure
After the assemblies have been loaded you can create the SQL CLR procedure by executing the statement below:
CREATE PROCEDURE [dbo].[GetCustomers] WITH EXECUTE AS CALLER AS EXTERNAL NAME [CMFacade].[StoredProcedures].[GetCustomers] GO
Executing the SQL procedure
With everything installed and wired-up, we can execute the procedure by using this statement:
USE [CMFacade] GO DECLARE @return_value int EXEC @return_value = [dbo].[GetCustomers] SELECT 'Return Value' = @return_value GO
Example results:
Retrieving Customer Documents
Now that there's a procedure for getting all of the customers, I applied the same approach and created a procedure returning the documents. As shown in the screen shot below I'm only returning the fields needed. This is exactly why a facade is important: it exposes only what is needed.
