Wiki Home

Snippet Factory


Namespace: WIN_COM_API
Reuse Snippets

Code reuse has enormous benefits for maintenance and for construction. When an expression (or formula) is needed in many places in an application, there are few choices for reusing it. You can copy it everywhere, making maintenance and enhancement a problem. You can make a user-defined function (UDF) of it. That’s usually a good thing because you can document it. It also let’s us refer to the formula by name, which can make the calling code easier to follow. Calling a UDF repeatedly can have potentially serious performance impacts. Some also argue that creating a UDF from an expression is overkill. Normally, many opt not to try and reuse these bits of code, despite knowing how important reuse is. A third option would be some way to embed the expression where it’s required. Formulas and expressions may not be the only thing inserted, so let’s allow for something a little bigger and call them snippets. Here’s how snippet insertion might work.

A typical VFP expression

A pretty common need is to produce a string like “Yearwood, Mike”. The formula for this is easy enough, but it must assume the table may be missing any combination of last and first name. It should also specify a length, especially when used in indexes and SQL commands. That length could vary as the formula is used in different parts of the application.

PADR(;
    ALLTRIM(tsf_Last) ;
    + IIF(LENC(ALLTRIM(tsf_Last)) ;
    + LENC(ALLTRIM(tsf_First))=0,'',', ');
    + ALLTRIM(tsf_First),50)


A typical SQL expression

The expression itself might vary depending on the database server. This is possible in SQL Server, but not in VFP.

  CAST(LTRIM(RTRIM(LAST_NAME)) +
  CASE
    WHEN
      LEN(LTRIM(RTRIM(LAST_NAME))) +
      LEN(LTRIM(RTRIM(FIRST_NAME))) = 0
    THEN ''
    ELSE ', '
  END + LTRIM(RTRIM(FIRST_NAME)) AS CHAR(50))


That inline CASE statement qualifies as more than a simple formula or expression, hence the need for the term “snippet”.

A little preparation

When contrasting different approaches to solving a problem, I want a large enough table to make performance effects visible. I often use a table with a million rows for speed trials, but 100,000 rows proved enough. No indexes are required, because we’re just testing the impact of evaluating expressions and not Rushmore optimized filtering.

CREATE TABLE TestSnippet FREE (tsf_Last c(25), tsf_First c(25))
FOR m.lnI = 1 TO 100000
  INSERT INTO TestSnippet ;
      (;
        tsf_Last, ;
        tsf_First;
      ) ;
    VALUES ;
      (;
        "Last" + TRANSFORM(m.lnI),;
        "First" + TRANSFORM(m.lnI);
      )
ENDFOR


Hard-Coded expressions – copy and paste reuse

One of the WORST ways to reuse the snippet is to copy it everywhere. That’s called copy-and-paste reuse. Copying a snippet everywhere has the best possible performance, but the worst possible maintenance. If you forgot a comma in one of these, how will you locate it? How can you be sure you did the formula the same everywhere? I found three different versions of that snippet in one application even though I keep an eye out for such slips. We need a solution that improves maintainability while minimizing performance losses.

As a baseline, we’ll use that snippet in a hard-coded SQL command.

SELECT ;
  PADR(;
    ALLTRIM(tsf_Last) ;
    + IIF(LENC(ALLTRIM(tsf_Last)) ;
    + LENC(ALLTRIM(tsf_First))=0,'',', ');
    + ALLTRIM(tsf_First),50) AS FullName ;
FROM ;
  TestSnippet ;
INTO CURSOR ;
  c_Test ;
READWRITE


The timing I got from running this query was 1.317 seconds.

User-Defined Functions for reuse

Making a UDF out of the snippet is best for maintenance, but the worst for performance. The UDF approach also requires the parameters be passed in sequence. This can be problematic for coding and maintenance as the number of parameters increase. There is a limit of only 26 parameters we can pass to a UDF. It also requires a two-step flow of data per row, the passing of the parameters to the function and the returning of the result. This overhead can add up to a potentially significant problem. Here’s the UDF:

FUNCTION FullNameUDF
*Returns the expression for the person's fullname
LPARAMETERS m.tcLastName, m.tcFirstName
IF VARTYPE(m.tcLastName) # "C" ;
     OR VARTYPE(m.tcFirstName) #"C"
  ERROR 11
ENDIF
RETURN PADR(ALLTRIM(m.tcLastName) ;
  + IIF(LENC(ALLTRIM(m.tcFirstName)) ;
  + LENC(ALLTRIM(m.tcLastName)) = 0, "", ", ") ;
  + ALLTRIM(m.tcFirstName), 50)


Here’s the query with the call to the UDF:

a=SECONDS()
SELECT ;
  FullNameUDF(tsf_Last, tsf_First) AS FullName ;
FROM ;
  TestSnippet ;
INTO CURSOR ;
  c_Test ;
READWRITE
?"Test 1: Calling a UDF",SECONDS()-m.a


That query took 5.082 seconds to run, almost 5 times longer than the baseline!

A new alternative - Snippet Factory

A new approach is necessary for reusing snippets. As long as it beats the UDF timing, we should be in good shape. The requirements for this new function are:

- calling code should be readable and maintainable – the snippets should be named and kept in a central location, probably a table.
- reduce the position-sensitive nature of UDF parameters
- remove the limitation of number of UDF parameters
- performance should not be adversely affected
- maintenance of the set of snippets should be easy
- the function should allow taking advantage of different database-server specific features

What is a snippet?
A snippet is composed of two parts, the formula and the operand(s) (variables or fields used to produce the result). The operands may differ depending on where the snippet is used in the application. We’ll need a way to replace these in the snippet.

Here’s the formula written as stored in the snippet table.

PADR(ALLTRIM(< < LastNamePlaceHolder > >) ;
+ IIF(LENC(ALLTRIM(< < FirstNamePlaceHolder > >)) ;
+ LENC(ALLTRIM(< < LastNamePlaceHolder > >))=0,"",", ") ;
+ ALLTRIM(< < FirstNamePlaceHolder > >),< < LengthPlaceHolder > >)


Don’t lose the UDF’s advantages – name those parameters!
Looking at the insides of a typical UDF, the parameters name the passed values. This naming is desirable. One way to name values would be to use a 2-column array. Column 1 would have the parameter name and column 2 would hold the value. That can make for some difficult coding. You’d have to find each array row containing the parameter name before you could assign or check the value in that row.

LOCAL lnRow
lnRow = ASCAN(laParameters,”LastNamePlaceHolder”,1,-1,1,15)
IF lnRow=0
  RETURN .F.
ENDIF
laParameters[m.lnRow,2]=”SomeLastName”


Another way to name values is to use properties of an object. Such an object is known as a parameter object. It is possible to pass a large number of parameters in a single parameter object. Further, properties can be populated in any sequence without worry of transposing or missing parameters. We’ll specify the parameter names in the table where we store the snippets. Using a parameter object is much easier than an array.

WITH m.loParameters
  m.loParameters.LastNamePlaceHolder = “SomeLastName”
ENDWITH


The snippet table structure must have a name, a memo field for the snippet and a field for the parameter names. It should at least have an index on the name for speed. After all, nothing is faster than a straight SEEK for finding data.

CREATE TABLE Snippets.DBF FREE (cName c(40),mSnippet M, mParameter M)
INDEX ON UPPER(cName) TAG cName


Create the table in any folder you like. I usually SET PATH to the location of the data tables when the app starts. You might also build the snippet .DBF and .FPT into your .EXE.

Place an order with the Snippet Factory

The final form of the snippet is obtained by taking the parameter values and putting them into the correct places in the formula or expression. This is like a factory where parts are sent in and finished products come out. We’ll create an object that can be sent to the Snippet Factory – a snippet parameter object.

I originally thought to create the parameter object as an instance of the Empty class. As this class cannot be sub-classed, I discarded that idea. I can easily imagine wanting to enhance the class in the future. I decided on the Custom class, even though it’s not as light-weight (conservative of resources) as the Empty class, it is extendable. The drawback is you cannot create snippets that have parameters named the same as any of the native properties and methods of the Custom class. The parameter object will be created first, given the name of a snippet and it will create the set of properties. It will grab these property names from the snippet table mParameter column.

One of the ways to extend the parameter object is to have it store the name of the snippet. This is a flexibility issue. If we have to create a set of parameter objects for different snippets, we should not have to explicitly provide a snippet name parameter when they are eventually passed to the Snippet Factory object.

Here’s the parameter object class definition. I chose to store the snippet parameter and the Snippet Factory class definitions in a single .PRG called Snippet Factory.PRG. That is not absolutely required. The snippet parameter object could be a general purpose parameter object used outside of the Snippet Factory context.

DEFINE CLASS SnippetParameters AS Custom
  *We cannot subclass Empty, so we cannot
  *add inheritable code as done below.
  PROTECTED icSnippetName
  PROCEDURE Init
    LPARAMETERS m.tcSnippetName
    IF VARTYPE(m.tcSnippetName)#"C" ;
         OR EMPTY(m.tcSnippetName)
      ERROR 11
    ENDIF
    THIS.icSnippetName = m.tcSnippetName
    RETURN DODEFAULT()
  ENDPROC
  PROCEDURE GetSnippetName
    RETURN THIS.icSnippetName
  ENDPROC
ENDDEFINE


Touring the Snippet Factory

The Snippet Factory class is based on the VFP Session class. That class encapsulates all the data and SET commands in a separate data session, shielding your application from whatever the Snippet Factory does internally. It accepts the snippet parameter object, takes the value from each property and inserts these values into the snippet, effectively customizing the snippet to your application.

To initialize the Snippet Factory you assign it to a local variable. You could assign it to a public variable when your application starts, so it is available everywhere.

PUBLIC m.oSnippetFactory
m.oSnippetFactory = NEWOBJECT("SnippetFactory","SnippetFactory.PRG")


It opens Snippet.DBF and keeps it open while it is instantiated. Once initialized, you ask the Snippet Factory for a parameter object. This is like asking for an order form from a factory. The factory provides the form and you fill in the blanks. The Snippet Factory class has a GetParameterObject method for this purpose. We pass the name of the Snippet and it creates and populates a parameter object with the correct properties.

LOCAL m.loFullNameParameters
m.loFullNameParameters = m.oSnippetFactory.GetParameterObject("FullNameSnippet")


We populate the parameter names with the field names from your table like this:

WITH m.loFullNameParameters
  .LastNamePlaceHolder = "tsf_Last"
  .FirstNamePlaceHolder = "tsf_First"
  .LengthPlaceHolder = 50
ENDWITH


I used the phrase “PlaceHolder” only to highlight the replaceable parts of the snippet. You can shorten them as desired. Beware that using Add Property and RemoveProperty will and should cause major problems. The snippet has specific names inside it. The must be matched by the parameter object’s properties.

Translating the snippet into an expression is pretty simple:

m.lcFullName = m.oSnippetFactory.GetSnippet(m.loFullNameParameters)
?m.lcFullName


You can test the resulting snippet like this:

SELECT TestSnippet
?EVALUATE(m.lcFullName)


One use for the Snippet Factory

I often build SQL commands programmatically using the TEXT…ENDTEXT command. The Snippet Factory makes that process somewhat easier in that the SQL is easy to read without all the complex expressions.

LOCAL m.lcSQL, m.lcFullName
m.lcFullName = m.oSnippetFactory.GetSnippet(m.loFullNameParameters)
TEXT TO m.lcSQL TEXTMERGE NOSHOW
  SELECT ;
    < < m.lcFullName > > as FullName ;
  FROM ;
    TestSnippet ;
  INTO CURSOR ;
    c_Test ;
  READWRITE
ENDTEXT
m.lcSQL = CHRTRANC(m.lcSQL,";"+CHR(13)+CHR(10)+CHR(9),SPACE(0))
&lcSQL.


In my testing, it takes only 1.619 seconds to get the parameter object, fill in the properties, pass it to the Snippet Factory, receive the formatted snippet and execute the macro-substituted SQL command. It is possible to obtain a series of snippets and embed them in the same manner.

Don’t be tempted to create snippets inside UDFs as that would compound the performance problems. Instantiate the Snippet Factory and parameter object(s) and use the resulting snippet(s) inside of tight loops or SQL commands.

Here’s the code for the Snippet Factory class definition.

DEFINE CLASS SnippetFactory AS Session
  PROCEDURE Init
  *No need to push/pop SET TALK as it is
  *scoped to this session object.
  SET TALK OFF

  USE Snippets ALIAS Snippets ORDER TAG cName IN 0
  ENDPROC

  PROCEDURE Destroy
  USE IN SELECT("Snippets")
  ENDPROC

  PROCEDURE GetParameterObject
  LPARAMETERS m.tcSnippetName
  *The parameter object will have to be created with
  *the properties required by the particular snippet.
  IF VARTYPE(m.tcSnippetName)#"C"
    ERROR 11
  ENDIF
  IF NOT SEEK(UPPER(m.tcSnippetName),"Snippets")
    ERROR "Snippet name not found!"
  ENDIF
  IF EMPTY(Snippets.mParameter)
    ERROR "Snippet Parameters have not been defined!"
  ENDIF

  LOCAL m.loParameter
  m.loParameter = NEWOBJECT("SnippetParameters","SnippetFactory.PRG","",m.tcSnippetName)
  IF VARTYPE(m.loParameter)#"O"
    ERROR 1313 && Class creation cancelled.
  ENDIF
  LOCAL m.lnX
  FOR m.lnX = 1 TO ALINES(laProperties,Snippets.mParameter,7,",")
    ADDPROPERTY(m.loParameter,laProperties[m.lnX])
  ENDFOR

  RETURN m.loParameter
  ENDPROC

  PROCEDURE GetSnippet
  *Main functionality of the SnippetFactory.
  *Returns a named code snippet with the place
  *holders replaced by the corresponding
  *parameter object properties.
  LPARAMETERS m.toParameter
  IF VARTYPE(m.toParameter)#"O"
    ERROR 11
  ENDIF

  IF NOT PEMSTATUS(m.toParameter,"icSnippetName",5)
    ERROR "Invalid parameter object passed!"
  ENDIF

  LOCAL m.lcSnippetName
  m.lcSnippetName = m.toParameter.GetSnippetName()
  IF NOT SEEK(UPPER(m.lcSnippetName),"Snippets")
    ERROR "Snippet name not found!"
  ENDIF

  LOCAL m.lcSnippet
  *Get rid of extraneous carriage returns,;
  *line feeds, tabs and semicolons.
  m.lcSnippet = CHRTRANC(Snippets.mSnippet,;
  ";"+CHR(13)+CHR(10)+CHR(9),SPACE(0))
  *Replace the parameter names in the snippet
  *with the values from the parameter object.
  LOCAL ARRAY laParameters[1]
  AMEMBERS(laParameters,m.toParameter)
  IF EMPTY(ALLTRIM(laParameters[1]))
    ERROR "Snippet Parameters have not been defined!"
  ENDIF
  LOCAL m.lnX
  WITH m.toParameter
    FOR m.lnX = 1 TO ALEN(laParameters)
      m.lcParameterName = ALLTRIM(laParameters[m.lnX])
      IF OCCURS("<<"+m.lcParameterName+">>",UPPER(m.lcSnippet))=0
        LOOP
      ENDIF
      m.lcParameterValue = EVALUATE("." + m.lcParameterName)
      IF ISNULL(m.lcParameterValue)
        m.lcParameterValue = ".NULL."
      ENDIF
      DO CASE
      CASE INLIST(VARTYPE(m.lcParameterValue),"N","I","D","T","L")
        m.lcParameterValue = TRANSFORM(m.lcParameterValue)
      OTHERWISE
        *Do nothing for Char.
      ENDCASE
      *Case insensitive search, but case sensitive replace!
      *This is important for non-VFP backends where case
      *sensitivity is required.
      m.lcSnippet = ;
        STRTRAN(m.lcSnippet,;
        "<<"+m.lcParameterName+">>",;
        m.lcParameterValue,1,-1,3)
    ENDFOR
  ENDWITH
  RETURN m.lcSnippet
  ENDPROC
ENDDEFINE


Here’s the code that tests the various scenarios discussed in this article.

LOCAL m.lnI
IF NOT FILE("TestSnippet.DBF")
  CREATE TABLE TestSnippet FREE (tsf_Last c(25), tsf_First c(25))
  FOR m.lnI = 1 TO 100000
    INSERT INTO TestSnippet ;
        (;
          tsf_Last, ;
          tsf_First;
        ) ;
      VALUES ;
        (;
          "Last" + TRANSFORM(m.lnI),;
          "First" + TRANSFORM(m.lnI);
        )
  ENDFOR
ENDIF


*Test in a SQL command versus calling a UDF.

*Preload caches.
a=SECONDS()
SELECT * FROM TestSnippet INTO CURSOR c_Test READWRITE
SELECT * FROM TestSnippet INTO CURSOR c_Test READWRITE

*Establish baseline
a=SECONDS()
SELECT ;
  PADR(ALLTRIM(tsf_Last) ;
  +IIF(LENC(ALLTRIM(tsf_Last)) ;
  + LENC(ALLTRIM(tsf_First))=0,"",", ");
  +ALLTRIM(tsf_First),50) ;
FROM ;
  TestSnippet ;
INTO CURSOR ;
  c_Test ;
READWRITE
?"Baseline: Explicit Expression",SECONDS()-m.a

*Now try it in a UDF.
a=SECONDS()
SELECT ;
  FullNameUDF(tsf_Last, tsf_First) AS FullName ;
FROM ;
  TestSnippet ;
INTO CURSOR ;
  c_Test ;
READWRITE
?"Test 1: Calling a UDF",SECONDS()-m.a


USE IN c_Test

*Assuming it would be added to your application
*at startup or integrated with your framework.
PUBLIC m.oSnippetFactory
m.oSnippetFactory = NEWOBJECT("SnippetFactory","SnippetFactory.PRG")

LOCAL m.loFullNameParameters, m.lcFullName
m.loFullNameParameters = ;
  m.oSnippetFactory.GetParameterObject("FullNameSnippet")
WITH m.loFullNameParameters
  .LastNamePlaceHolder = "tsf_Last"
  .FirstNamePlaceHolder = "tsf_First"
  .LengthPlaceHolder = 50
ENDWITH

m.lcFullName = m.oSnippetFactory.GetSnippet(m.loFullNameParameters)
?m.lcFullName

SELECT TestSnippet

*Test the resulting expression against real data.
*If it crashes, alter the original expression.
?EVALUATE(m.lcFullName)

a=SECONDS()
*Regular usage.
LOCAL m.loFullNameParameters
m.loFullNameParameters = ;
m.oSnippetFactory.GetParameterObject("FullNameSnippet")
WITH m.loFullNameParameters
  .LastNamePlaceHolder = "tsf_Last"
  .FirstNamePlaceHolder = "tsf_First"
  .LengthPlaceHolder = 50
ENDWITH

LOCAL m.lcSQL, m.lcFullName
m.lcFullName = m.oSnippetFactory.GetSnippet(m.loFullNameParameters)
TEXT TO m.lcSQL TEXTMERGE NOSHOW
  SELECT ;
    < < m.lcFullName > > as FullName ;
  FROM ;
    TestSnippet ;
  INTO CURSOR ;
    c_Test ;
  READWRITE
ENDTEXT
lcSQL = CHRTRANC(m.lcSQL,";"+CHR(13)+CHR(10)+CHR(9),SPACE(0))

&lcSQL.
?"Inserted Expression",SECONDS()-m.a

FUNCTION FullNameUDF
*Returns the expression for the person's fullname
LPARAMETERS m.tcLastName, m.tcFirstName
IF VARTYPE(m.tcLastName) # "C" ;
     OR VARTYPE(m.tcFirstName) #"C"
  ERROR 11
ENDIF
RETURN PADR(ALLTRIM(m.tcLastName) ;
  + IIF(LENC(ALLTRIM(m.tcFirstName)) ;
  + LENC(ALLTRIM(m.tcLastName))=0, "", ", ") ;
  + ALLTRIM(m.tcFirstName), 50)


Conclusion

Code reuse has tremendous benefits to rapid construction and maintenance. There are many ways to reuse code in VFP with classes, functions and procedures. However, formulas and expressions were often hand-coded and believed not really suited to reuse. The power and flexibility of VFP let us break that barrier.
( Topic last updated: 2014.07.21 05:12:54 PM )