/sdk/virtualtables

Virtual Tables

Similar to a View in SQL Server, a Virtual Table (VT) allows you to execute a SQL command that returns a predictable set of columns. The use of VTs is particularly useful for handlers that return dynamic columns, so that they can participate with Linked Server.

For example, if a VT called vUSStates has been defined in SharePoint to return data from the USStates SharePoint list, the following command could be executed through Linked Server because the view contains a known set of columns:

SELECT * FROM [localhost,9550].bsc.SharePoint.vUSStates

In addition to enabling support for Linked Server, defining a VT on an adapter enables additional screens in the Enzo Manager application, allowing administrators to easily build views using visual support.

Add Virtual Table Support

The following code artifacts need to be added to support VTs in an adapter:

• Add a Virtual Table Class:
  You will need to add a class to your adapter so that the VT definition itself can be stored.
  
  
• Add Registration Methods:
  You will also need to call two registration methods as part of the RegisterAdapter method so that the necessary VT handlers are added to your adapter: RegisterVirtualTable and SetVirtualTableInfo.
  
  
• Implement VT Handlers and Methods:
  Finally at least one handler (up to 4) need to be defined to accept calls to the Virtual Tables for SELECT, UPDATE, DELETE and INSERT operations.
  
  

Virtual Table Class

The VT class needs to be created within your adapter project. For example, a VT class called VirtualTableDefinition is defined below; the class uses custom attributes to indicate which field represents what. The Description field is used by the Enzo Manager application to display the field name. The following attributes are available:

• VirtualTableName: The name of the view itself (ex: vUSStates)
• RemoteObject: The name of the remote object (ex: USStates)
• Columns: The list of columns that the VT implements (ex: long id,string name, datetime hireDate)
• Owners: The owner field (who owns the definition of the VT)
• SQL: The SQL command to use when executing the view (this attribute is optional).

public class VirtualTableDefinition
{
    [VirtualTable(VirtualFieldType.VirtualTableName, IsRequired = true, Description = "Virtual Table Name")]
    public string Name { get; set; }
    [VirtualTable(VirtualFieldType.RemoteObject, IsRequired = true, Description = "Twitter Object")]
    public string ResourceName { get; set; }
    [VirtualTable(VirtualFieldType.Columns, IsRequired = true, Description = "List of columns")]
    public string Columns { get; set; }
    [VirtualTable(VirtualFieldType.SQL, IsRequired = false)]
    public string SQL { get; set; }
    [VirtualTable(VirtualFieldType.Owners, IsRequired = false)]
    public string Owner { get; set; }
}

Registration Methods

Once the VT definition class has been created, it needs to be registered and added as a Virtual Table. The registration of the VT is done by calling the RegisterVirtualTable method, and additional metadata (used by the Enzo Manager application) is registered by calling the SetVirtualTableInfo method.

// SPObject is a friendly name given to the VT; it can be anything (no special characters)
RegisterVirtualTable
    ("SPObject",
    SubTableNameIs.Configuration,   // helps determine if the @ symbol is used to identify a table or configuration setting
    null,                           // callback method in case the list of columns is dynamic
    VirtualTableCall,               // SELECT adapter callback method
    null,                           // UPDATE adapter callback method (optional)
    null,                           // INSERT adapter callback method (optional)
    null);                          // DELETE adapter callback method (optional)

// Add support for Enzo Manager  
SetVirtualTableInfo("SPObject",
    new VirtualTableInfo(
        "SPObject",         // the friendly name given in the RegisterVirtualTable method
        "Twitter Timeline", // the Enzo Manager display name of the remote object
        "SQL Command",      // The Enzo Manager display name of the SQL command
        "Enter a valid SQL command with a WHERE clause",    // Enzo Manager SQL hint when editing the SQL command
        false,              // Automatically escapes double-quotes when passing the SQL command to a wrapping handler
        "",                 // Wrapping handler call if any (used when the SQL command is not actually a native SQL Server call)
        "SELECT Name FROM twitter.Resources ORDER BY Name", // The handler SQL statememt used by the Manager to retrieve the list of remote resources
        "EXEC Twitter.GetFields '?'", // The SQL statement to retrieve the list of remote resource columns (? will be replaced dynamically by the remote resource name)
        "SELECT * FROM Twitter.Timeline",  // The sample SQL command to fetch a single record; TOP N is added automatically by the Manager
        "TOP",              // The operator to use for the TOP command (can be TOP or LIMIT)
        "",                 // The prefix to use when generating SQL commands (ex: list@) when the SELECT handler supports decorators
        "["                 // The column delimitor understood by the remote system (could be ", { or [})
        ));

Here are additional considerations when adding support for Virtual Tables:

• Some remote systems support a modified version of SQL commands, in which case you may need to create a handler that wraps the final call to the remote system. For example, SalesForce support SOQL queries; while SOQL queries look like SQL commands, the are not T-SQL compliant. As a result, a handler that executes a SOQL query is needed, and the wrapping handler property would be specified in the VirtualTableInfo call.
  
  
• The TableName prefix is used when calling the handler that makes the actual call to the remote system supports decorators (which is usually the case); the above example is empty because the Twitter adapter supports a single remote table: timeline.

The following example shows how the SharePoint registration looks like:

RegisterVirtualTable("SPObject",
    SubTableNameIs.Configuration,
    null,
    VirtualTableCall,
    VirtualUpdateCall,
    VirtualInsertCall,
    VirtualDeleteCall);

SetVirtualTableInfo("SPObject",
    new VirtualTableInfo(
        "SPObject",
        "SharePoint List",
        "SQL Command",
        "Enter a valid SQL command",
        false,
        "",
        "SELECT Title FROM SharePoint.Lists ORDER BY Title",
        "SELECT distinct title, mappedType FROM SharePoint.Fields WHERE title='?' AND InternalName <> 'ContentVersion' AND Hidden = 0 ORDER BY Title",
        "SELECT * FROM SharePoint.[List@?]",  // Auto-column replacement only works with SELECT * - not SELECT top N *
        "TOP",
        "List@",
        "["
        ));

Handler Implementation

After defining the VT definition and adding the registration methods, it is time to add the actual methods that will handle the calls to the remote system.

Although the VT handlers are automatically registered by Enzo, the implementation methods need to be defined. The name of the view being called is available in the TableName property of the HandlerCfg object: e.HandlerCfg.TableName. For example, if a view called sUSStates is defined, this property will return sUSStates when this command is executed: SELECT * FROM MyHandler.vUSStates. Once we know the name of the view being called, we can extract its registration information.

internal EventResult VirtualTableCall(object sender, ExecEventArgs e)
{
    EventResult retval = new EventResult(e);

    string tableName = e.HandlerCfg.TableName;  // Get the name of the view being called

    if (e.IsArgValidation)
        return retval;

    // Get the view definition (an instance of the VT Class defined in the adapter)
    VirtualTableDefinition td = (VirtualTableDefinition)GetVirtualTableDefinition("SPObject", tableName);

    // The view exists?
    if (td != null)
    {
        List columns = new List();

        // Get the list of columns from this view in their raw Enzo format
        // Ex:  string name|||r, long id||0, datetime dateOfBirth...
        // Column names need to be cleaned up in preparation of the actual call - no data type/comment...
        // This code can be centralized 
        string[] col = td.Columns.Split(',');
        foreach (string c in col)
        {
            string tmpCol = c.Split('|')[0].Trim();
            if (tmpCol.IndexOf(' ') > 0)
            {
                if (tmpCol.IndexOf(' ') < tmpCol.IndexOf('[')
                    || tmpCol.IndexOf('[') < 0 )
                    tmpCol = tmpCol.Substring(tmpCol.IndexOf(' ')).Trim();
            }
            tmpCol = tmpCol.Replace("[", "").Replace("]", "");
            columns.Add(tmpCol);
        }

        // Call a central method that actually performs the GET operation against the remote system
        // In this case we are passing the remote object name, the list of columns requested, and the ExecEventArgs object
        // Every adapter will do this differently, but a DataTable needs to be set dynamically for this call 
        // returning the expected columns as specified to work with Linked Server
        retval = GetListItems(sender, e, td.ResourceName, string.Join(",", columns));
    }
    else
        retval.SetResultCode("Table {0} was not found in the list of virtual tables.", tableName);
            
    return retval;

}