IntentTo be able to index, create a set of lookup keys, and provide a means of data retrieval for Schema Fields (above) and the Poly Model composite pattern. ProblemWhen using the Poly Model pattern, unless you know the transaction ID key of the stored record, you will have a difficult time retrieving that data. Traversing each record, reading its XML instance data, then performing an XPath query on the returned XML would take a prohibitive amount of time. The Schema Indexer, a child pattern to the Poly Model, performs this task by adding an additional step to each transaction's save operation. If you recalled during the StoreTransaction operation in the first section, there was a call to StoreKeys(). (StoreKeys() will be shown in the implementation section below). The Schema Indexer solves the Poly Model lookup problem by storing, in a separate table, all required key field values from the XML instance data, then referencing the original transaction ID. It references the transaction ID from the DATA table such that it can be the basis for lookup later, utilizing predefined meta-information for determining which fields to use. With both the field value used for lookup and the transaction ID from the XML instance data, the data can be retrieved using an INDEX table. The transaction ID of the INDEX table acts as a foreign key to the DATA table. The fields and the values used are dynamically configurable, and I provide a means to save this information at runtime. The Schema Indexer is undoubtedly coupled to the Poly Model and the schema using the implementation provided. However, this pattern can be applied in other scenarios, such as caching, and thus stands on its own as well. There are several ways to solve the problem of allowing field queries on Poly Models, and this is only one solution to that problem. You will find, however, that this can quickly become a very complicated feature. However, simplicity is the key here, and the Schema Indexer should fit the mold of each pattern in the chapter by remaining simple to implement and I hope simple to understand, as well. The reason transaction retrievals were not immediately displayed in the Poly Model pattern is that I felt this pattern must first be understood as a composite. This is also due to the fact that during most query operations, unique values such as transaction IDs may not be known. This pattern rounds out the Poly Model by building a dynamic index during each save operation. Once built, the index can then be used to look up the appropriate transaction key that points to the XML instance data stored in the DATA table. If you've ever built your own database or worked with indexes in the past, this should be second nature, and you may even want to develop your own means of field lookup. For those who have not, the Schema Indexer provides a simple means for providing lookup functionality for your Poly Model implementation. ForcesUse the Schema Indexer pattern when:
StructureThe structure simply consists of three methods and one schema element. DeleteKeys(), StoreKeys(), and StoreKey() are the methods used to store the actual lookup key information. LOOKUP (Listing 5.24) is the XML element used as the structure by which its instance data will contain what actual tables and columns will be used to look up information from other tables (in your data store) or from other XML elements. These methods are typically provided as part of the Poly Model classes, such as those shown in the Schema Indexer class diagram (Figure 5.6). Figure 5.6. Schema Indexer pattern class diagram.
Once you understand the premise and examine the code behind the StoreKey methods, the Schema Indexer pattern is rather self-explanatory. The only other structural element in this pattern is the XML entity used to hold the lookup or reference meta-information. Next you must define what fields will be stored in the INDEX table. This is where the LOOKUP schema element comes into play. This XML item can be added to the Customer's schema (shown in the Poly Model section) or it may reside in its own schema. Its instance can be stored separately or as part of the main schema itself. The structure is rather simple, as you can see: Listing 5.24 Sample LOOKUP element structure to be used for lookup column instance data.<xs:element name="LOOKUP"> <xs:complexType> <xs:sequence> <xs:element name="TABLE" type="xs:string" minOccurs="0" /> <xs:element name="COLUMN" type="xs:string" minOccurs="0" /> </xs:sequence> </xs:complexType> </xs:element> The important thing is that somehow the LOOKUP element and its associated instance data must be accessible during transaction save operations. In the implementation of this pattern, the lookup instance data is stored along with the entire schema used for saving Customer information. In this case, the instance data of the LOOKUP element will look something like Listing 5.25 (notice that both the LOOKUP structure and its diffgram data are part of the same XML document): Listing 5.25 Sample Data schema with LOOKUP element and sample LOOKUP instance data. [View full width] <?xml version="1.0" encoding="utf-8" ?> <DataSet xmlns="http://tempuri.org/"> <xs:schema id="Customers" targetNamespace="http://tempuri.org/Customers.xsd" xmlns :mstns="http://tempuri.org/Customers.xsd" xmlns="http://tempuri.org/Customers.xsd" xmlns :xs="http://www.w3.org/2001/xmlschema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="qualified"> <xs:element name="Customers" msdata:IsDataSet="true"> <xs:complexType> <xs:choice maxOccurs="unbounded"> <xs:element name="Customer"> <xs:complexType> <xs:sequence> <xs:element name="CustomerID" type="xs:string" /> <xs:element name="CompanyName" type="xs:string" /> <xs:element name="ContactName" type="xs:string" minOccurs="0" /> <xs:element name="ContactTitle" type="xs:string" minOccurs="0" /> <xs:element name="Address" type="xs:string" minOccurs="0" /> <xs:element name="City" type="xs:string" minOccurs="0" /> <xs:element name="Region" type="xs:string" minOccurs="0" /> <xs:element name="PostalCode" type="xs:string" minOccurs="0" /> <xs:element name="Country" type="xs:string" minOccurs="0" /> <xs:element name="Phone" type="xs:string" minOccurs="0" /> <xs:element name="Fax" type="xs:string" minOccurs="0" /> </xs:sequence> </xs:complexType> </xs:element> . . . <xs:element name="LOOKUP"> <xs:complexType> <xs:sequence> <xs:element name="TABLE" type="xs:string" minOccurs="0" /> <xs:element name="COLUMN" type="xs:string" minOccurs="0" /> </xs:sequence> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> <xs:unique name="CustomersKey1" msdata:PrimaryKey="true"> <xs:selector xpath=".//mstns:Customer" /> <xs:field xpath="mstns:CustomerID" /> </xs:unique> </xs:element> </xs:schema> <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn :schemas-microsoft-com:xml-diffgram-v1"> <Customers xmlns="http://tempuri.org/Customers.xsd"> <LOOKUP diffgr:id="LOOKUP1" msdata:rowOrder="0"> <TABLE> Customer </TABLE> <COLUMN> CompanyName </COLUMN> </LOOKUP> <LOOKUP diffgr:id="LOOKUP2" msdata:rowOrder="1"> <TABLE> Customer </TABLE> <COLUMN> ContactName </COLUMN> </LOOKUP> </Customers> </diffgr:diffgram> </DataSet> During a call to StoreTransaction() in the PolyModel child class, StoreKeys() will be called to get the lookup information (above) and store its referenced fields as part of the INDEX table. This is a two-phase operation, and it will slightly impact performance because of that fact, compared with the single table insert you would get with traditional database I/O. The other methods, StoreKey() and RemoveKeys(), are helper methods used by StoreKeys() to delete the keys initially and store each key, respectively. Using this schema element and instance data to pass to the StoreKeys method, you can dynamically build your own cross-reference index table. Any future data retrieval can query the INDEX table, find the queried fields, and look up the reference transaction ID. Again, this will be a two-step operation. Once the transaction ID is found on the queried INDEX record, the actual XML instance from the DATA table can then be queried. It must be accomplished in that order. All of these steps seem like a lot of work. However, the benefits you receive by implementing such a model will pay huge dividends . Each time you need fields, tables, or any other data element, you avoid having to consistently maintain your data access layer once the framework is built. The flexibility of this solution makes up for the fact that you will have two operations instead of one for storage and retrieval. Everything is a tradeoff but the benefits to systems such as the one featured in Chapter 6 make this pattern a viable choice. Consequences
ImplementationIn the Poly Model pattern implementation above, one call is made for each corresponding call to the StoreTransaction method. After the XML instance data is stored, we must store the lookup keys for fields we will be looking up in the future. These fields must be defined at some point. Typically, they are defined during some configuration ceremony, such as application setup. They can even be set up dynamically, based on an implementation-specific algorithm. For my purposes, I use a separate schema definition called a Lookup schema to define the structure of what makes up the Lookup fields referenced below. The Lookup schema should be set up and persisted so that at runtime it can be passed along with the instance data each time a transaction must be stored. A convenient way to transport the Lookup schema reference along with any other form of metadata for the packet is to use the Abstract Packet pattern, as mentioned in Chapter 4 The Schema Indexer code does just that! In Listing 5.26, the StoreKeys method receives a packet containing the following items that will be used to save the index information. This method then stores the following information to be later referenced by your retrieve methods or as part of an ad hoc query facility defined by you:
Listing 5.26 Method called to save lookup column when saving all transactions in the Poly Model.public override bool StoreKeys(Packet oTrans) { try { if(oTrans == null) return false; // Delete all existing keys bool bRemoveKeys = false; bRemoveKeys = RemoveAllKeys(oTrans); string sxmlschema = null; sxmlschema = GetschemaAsString( oTrans.ProductLookupschemaID); if(sxmlschema.Length == 0) return false; PolyModelData oDB = ModelFactory.CreatePolyModelData(); oDB.ReadXml(sxmlschema, XmlReadMode.Readschema); if(oDB.Contains("LOOKUP") == false) return false; int iRowCount = 0; iRowCount = oDB.Data. Tables[LOOKUP_DATATABLENAME].Rows.Count; for(int i = 0; i < iRowCount; i++) { string sTable = string.Empty; sTable = Convert.ToString(oDB[LOOKUP_DATATABLENAME, LOOKUP_DATACOLUMN_TABLE, i]); if(sTable.Length == 0) continue; string sColumn = string.Empty; sColumn = Convert.ToString( oDB[LOOKUP_DATATABLENAME, LOOKUP_DATACOLUMN_COLUMN, i]); if(sColumn.Length == 0) continue; string sValue = string.Empty; sValue = oTrans.RawData.Tables[sTable] .Rows[0][sColumn].ToString(); if(sValue.Length == 0) continue; bool bResult = false; bResult = StoreKey( oTrans.TransactionID, sColumn, sValue); } return true; } . . . The RemoveAllKeys method (Listing 5.27) is the cleanup method called during each StoreKeys() invocation. This ensures that we are refreshing the lookup information and keeps us from duplicating index data. Listing 5.27 Cleans up all lookup keys from the INDEX table ”typically right before the StoreKey operation.public override bool RemoveAllKeys(Packet oTrans) { try { Profiler.BeginInstrumentation(); // Check argument if(oTrans == null) return false; // There must be a Transaction ID to use to lookup // all corresponding keys if(oTrans.TransactionID == 0) return false; StringBuilder sbSQL = new StringBuilder(); // Define the query to execute sbSQL.Append("execute sp_executesql N'DELETE FROM " + "dbo.DATA_IDX WHERE TRANS_ID = " + "@TransID', "); sbSQL.AppendFormat( "N'@TransID BIGINT', @TransID = {0}", oTrans.TransactionID); PolyModelData oDB = null; oDB = ModelFactory.CreatePolyModelData(); if(oDB == null) return false; // Execute the query int iRowCount = 0; iRowCount = Convert.ToInt32(oDB.ExecuteNonQuery(sbSQL)); return true; } . . . Finally, StoreKey() is called by StoreKeys() for each key to store, and the information is stored in the index using the stored procedure DNUpdateDataIdx (Listing 5.28). This method takes the transaction ID and saves it to the index table. The transaction ID will later be treated as a foreign key to look up the original DATA record stored. The other two columns stored in the INDEX table are the column name and the column value from the actual instance data. Listing 5.28 Helper method to store the lookup key ”rarely called publicly .public override bool StoreKey(long lTransID, string sKeyName, string sValue) { try { if(lTransID == 0) return false; if(sKeyName.Length == 0) return false; if(sValue.Length == 0) return false; PolyModelData oDB = null; oDB = ModelFactory.CreatePolyModelData( "sp_DNUpdateDataIdx", CommandType.StoredProcedure); if(oDB == null) return false; // Bind input stored procedure parameter SqlParameter oInParm1 = null; oInParm1 = (SqlParameter) oDB.AddDataParameter("@TransID", SqlDbType.BigInt, ParameterDirection.Input); if(oInParm1 == null) return false; oInParm1.Value = lTransID; // Bind input stored procedure parameter SqlParameter oInParm2 = null; oInParm2 = (SqlParameter) oDB.AddDataParameter("@Name", SqlDbType.VarChar, 64, ParameterDirection.Input); if(oInParm2 == null) return false; oInParm2.Value = sKeyName; // Bind input stored procedure parameter SqlParameter oInParm3 = null; oInParm3 = (SqlParameter) oDB.AddDataParameter("@Value", SqlDbType.VarChar, 255, ParameterDirection.Input); if(oInParm3 == null) return false; oInParm3.Value = sValue; // Bind output stored procedure parameter (holds T- // SQL RETURN(x) value) SqlParameter oOutParm = null; oOutParm = (SqlParameter) oDB.AddDataParameter("@RowCount", SqlDbType.Int, ParameterDirection.ReturnValue); if(oOutParm == null) return false; // Execute the stored procedure int iRowCount = 0; iRowCount = oDB.ExecuteNonQuery(); // Get the result of the stored procedure int iCount = 0; iCount = Convert.ToInt32( oDB.GetDataParameterValue("@RowCount")); if(iCount == 1) return true; } . . . The stored procedure to update the INDEX table is as follows in Listing 5.29 (some of the nonapplicable implementation is not shown). Listing 5.29 Stored procedure source for storing lookup values to the INDEX table. [View full width] CREATE PROCEDURE dbo.sp_DNUpdateDataIdx @TransID BIGINT, @Name VARCHAR(64), @Value VARCHAR(255) AS . . . IF EXISTS (SELECT * FROM dbo.[DATA_IDX] WHERE TRANS_ID = @TransID AND NAME = @Name) BEGIN UPDATE dbo.[DATA_IDX] WITH (HOLDLOCK) SET TRANS_ID = @TransID, NAME = @Name, VALUE = @Value, TIME_STAMP = GETDATE() WHERE TRANS_ID = @TransID AND NAME = @Name END ELSE BEGIN INSERT INTO dbo.[DATA_IDX] (TRANS_ID, NAME, VALUE, TIME_STAMP) VALUES (@TransID, @Name, @Value, GETDATE()) END . . . GO Once the XML instance data has been stored its index information should also be stored and you are now ready to query the data using a "Poly query," if you will. The method of querying depends a lot on how the Schema Index was created and is very implementation-specific. For example, queries can be built dynamically or they can be canned. The queries can return a single table or all corresponding tables in the instance data. The return rows can be packaged as individual packets in a sort of "packet collection" or all records can be contained in one XML stream. The choices are many and probably would warrant a book of their own. One implementation of a Poly Model query is shown in the Listing 5.30 code snippet. Here, only the table in question is returned in its own XML instance data as part of a packet. This is only one solution option, so I'll leave it up to you how to decide to implement yours. In fact, the Abstract Packet pattern was used as part of the means to contain the DataSet to keep metadata such as transaction ID separated in each packet for each row returned. These routines are implemented as part of a Web method used to return any row based on query logic as part of a where clause, using a corresponding Where object to build the query. The following Web method simply gets the ball rolling by generating a new schema to be stored (for details, see the Schema Field pattern section in this chapter). In addition to the schema, lookup instance data using the LOOKUP entity shown earlier is added to a schema diffgram. This allows you to find the lookup tables and columns while storing transactions using StoreKeys by incorporating the referenced schema with the instance data for the lookup. There are other ways you can design this but the following is one of the easiest ways to get up and running: Listing 5.30 Sample schema generator coupled with sample LOOKUP instance data for convenience.[WebMethod] public DataSet GenerateCustomerschema(string sProduct, string sVersion, string sDescription, bool bStore) { Customers dsCustomers = null; try { dsCustomers = new Customers(); // add lookup instance data at this time... Customers.LOOKUPRow oRow = dsCustomers.LOOKUP.NewLOOKUPRow(); // solely for testing and showing instance data.... oRow.COLUMN = "CompanyName"; oRow.TABLE = "Customer"; dsCustomers.LOOKUP.AddLOOKUPRow(oRow); dsCustomers.LOOKUP.AcceptChanges(); oRow = dsCustomers.LOOKUP.NewLOOKUPRow(); oRow.COLUMN = "ContactName"; oRow.TABLE = "Customer"; dsCustomers.LOOKUP.AddLOOKUPRow(oRow); dsCustomers.LOOKUP.AcceptChanges(); if (bStore) Storeschema(sProduct, sVersion, sDescription, dsCustomers); } catch(Exception ex) { throw new BaseException(Utilities.BuildErrorMessage( "Exception thrown", ex.Message), true); } return dsCustomers; } The following Web method was used only for testing and shows how a WhereCollection object can used to build a query using the Poly Model and Schema Indexer as a prerequisite. This an ad hoc query-testing method used to build a query and send it to the Poly Model for data retrieval. If the lookup columns have been set up and data has been saved, data can be retrieved using something like the code in Listing 5.31. Listing 5.31 Sample ad hoc query method used for testing Poly Model queries.[WebMethod] public DataSet GetTransactionByWhereCollectionEx(string sColumn1, string sValue1, string sColumn2, string sValue2, string sColumn3, string sValue3) { PolyModelWhereCollection colWhere = ModelFactory.CreatePolyModelWhereCollection(); if (sColumn1 != string.Empty) colWhere.Add(sColumn1, sValue1); if (sColumn2 != string.Empty) colWhere.Add(sColumn2, sValue2); if (sColumn3 != string.Empty) colWhere.Add(sColumn3, sValue3); return ModelFactory.CreatePolyModel() .GetTransaction(colWhere).RawData; } The following is called from the above test method and immediately passed to DBGetTransactionBySQL(), extracting a custom SQL command built using the WhereCollection object: Listing 5.32 Sample transaction retrieval method using a WhereCollection SQL builder.public virtual Packet GetTransaction( PolyModelWhereCollection colWhere) { StringBuilder sb = null; try { // Check argument if(colWhere == null) return null; Packet oOut = null; int iNumTries = 0; while(iNumTries < DTPConstants.MAX_DB_ATTEMPTS) { oOut = DBGetTransactionBySQL(colWhere.GetSQL()); if(oOut != null) { if(iNumTries > 0) { . . . } return oOut; } . . . iNumTries++; } . . . The following is one of the primary Poly Model helper methods used to retrieve the schema and its instance data, and to fill the Abstract Packet with meta-information, such as the lookup ID, for later reference. Listing 5.33 Helper method used by most calls to GetTransaction() in the Poly Model.public override Packet DBGetTransactionBySQL(string sSQL) { try { Profiler.BeginInstrumentation(); if(sSQL.Length == 0) return null; PolyModelData oDB = null; oDB = ModelFactory.CreatePolyModelData(); if(oDB == null) return null; int iResult = 0; iResult = oDB.ExecuteFillQuery(sSQL); if(iResult == 0) return null; // See if the DataTable table exists if(oDB.Contains(DEFAULT_TABLENAME) == false) return null; long lschemaID = 0; lschemaID = (long) oDB[DEFAULT_TABLENAME, DEFAULT_SCHEMA_ID_COLUMN_NAME]; string sxmlschema = string.Empty; sxmlschema = GetSchemaAsString(lschemaID); if(sxmlschema.Length == 0) return null; PolyModelData oDB2 = ModelFactory.CreatePolyModelData(); if(oDB2 == null) vreturn null; oDB2.ReadXmlschema(sxmlschema); // Extract xml for transaction from the DataTable string sxml = string.Empty; byte[] baxml = null; baxml = (byte[]) oDB[DEFAULT_TABLENAME, DEFAULT_XML_COLUMN_NAME]; if(baxml != null) sxml = Encoding.ASCII.GetString(baxml); if(sxml.Length == 0) return null; oDB2.ReadXml(sxml); Packet oPacket = new Packet(); oPacket.RawData = oDB2.Data; // Extract transaction id and the schema id oPacket.TransactionID = (long) oDB[DEFAULT_TABLENAME, DEFAULT_TRANS_ID_NAME]; oPacket.ProductschemaID = lschemaID; // Set the schema lookup id oPacket.ProductLookupSchemaID = (long) GetCurrentLookupSchemaID( oPacket.ProductSchemaID); return oPacket; } . . . If you already have the transaction ID of the original transaction, there is no need to build a query. You simply call the following: Listing 5.34 Simple GetTransaction method uses the transaction ID directly (rarely used).public virtual Packet GetTransaction(Packet oPacket) { try { Profiler.BeginInstrumentation(); if(oPacket != null) return GetTransaction(oPacket.TransactionID); return null; } finally { . . . } GetTransaction() drives the lower level database I/O method. The signature of this Poly Model operation should remain consistent from design to design. The retry logic implemented here is optional. Listing 5.35 Method used for all calls using a transaction ID directly.public virtual Packet GetTransaction(long lTransID) { StringBuilder sb = null; try { // Check argument if(lTransID == 0) return null; Packet oOut = null; int iNumTries = 0; while(iNumTries < DTPConstants.MAX_DB_ATTEMPTS) { // Read the transaction from the database oOut = DBGetTransactionRecord(lTransID); if(oOut != null) { if(iNumTries > 0) { sb = new StringBuilder(); sb.AppendFormat( "Retries have reached maximum", iNumTries, lTransID); } } . . . iNumTries++; . . . This low-level database I/O operation can have many forms (e.g., stored procedure, transactional, etc.). This is shown only to frame and complete the implementation. Listing 5.36 Database-specific helper method for GetTransaction (transaction ID).public override Packet DBGetTransactionRecord(long lTransID) { try { if(lTransID == 0) return null; // load it from the database. if(DoesTransactionExist(lTransID) == false) return null; StringBuilder sb = new StringBuilder(); // Define query to execute sb.Append("execute sp_executesql N'SELECT " + "* FROM dbo.DATA WHERE TRANS_ID" + " = @TransID', "); sb.AppendFormat("N'@TransID BIGINT', @TransID = {0}", lTransID); return DBGetTransactionBySQL(sb.ToString()); } . . . That is it for the Schema Indexer and Poly Models. This pattern contains a lot of code. Much of this is due to the fact that many of the practices for this pattern are very implementation-specific and I wanted to show how one version of a Schema Indexer was implemented. I believe the code is the best means of giving you the quickest way to visualize the implementation. The Schema Indexer belongs as a rather tightly coupled child pattern to the overall composite, Poly Model. The idea of using dynamic, "hand-rolled" indexing is of necessity and can have several implementations . Through many years of data access development, database developers have taken for granted the automatic indexing power of the database back end and most likely have never had to write one themselves . They aren't difficult to write; they just require a little design and a lot of creativity for their implementation. I hope you got a taste of that here. Related Patterns
|