Exporting Attachments Part 2

In my post Export PeopleSoft Attachments using PL/SQL, I mentioned that PeopleSoft provides the File Attachment API for storing, retrieving, and viewing file attachments. These API functions comprise the recommended method for working with attachments. If you want to process an attachment, use the GetAttachment function to move that attachment into a file so you can access it from your app server. The ViewAttachment function, on the other hand, will send a copy of an attachment to a client browser. The ViewAttachment function is the only method provided by the Attachment API that allows a user to view the contents of an attachment. What if you want more control over the way PeopleSoft displays attachments? For example, let's say you use the File Attachment API to allow selected users to upload audio files (news, recorded training sessions, etc) and you have another page that allows other users to listen to those recordings. Should the "view" page display a "View Attachment" link or should it play the selected clip as embedded audio?

If you store attachments in the database using a RECORD:// style URL, then you can extract those attachments using PeopleCode. Here is an IScript that demonstrates this:

Function IScript_GetAttachment()
   Local any &data;
   Local string &file_name = "attachment.doc";   
   Local SQL &cursor = CreateSQL("SELECT FILE_DATA FROM PS_EO_PE_MENU_FILE WHERE ATTACHSYSFILENAME = :1 ORDER BY FILE_SEQ", &file_name);

   REM Set the following header if you want a download prompt. Don't set it if you want inline content;
   %Response.SetHeader("content-disposition", "attachment;filename=" | &file_name);

   While &cursor.Fetch(&data);
      %Response.WriteBinary(&data);
   End-While;
End-Function;

This method allows you to embed audio/video, provide user configurable background images, display inline PDF files, etc.

If you follow my blog closely, you may remember that my IScripts post claims there is no way to write binary data to an HTTP response. Notice the use of the %Response.WriteBinary method above? I was wrong. I was reading the comments of the post Browse the App Server Filesystem via iScript and noticed Joe's reference to %Response.WriteBinary(). Even though it isn't documented in PeopleBooks, I tried it and it worked. Thanks Joe!

Note that I hard coded my SQL statement. I did this for simplicity in this blog post. For production systems, I encourage you to use SQL definitions. Of course, if you implement this solution, you will need to change the SQL select table name to the name of your attachment table. Likewise, you will probably derive your file name from a parameter rather than hard code it as shown here.

As with any custom development, make sure you write secure code. For example, when executing SQL based on parameters, use SQL binds. Failure to use SQL binds can result in SQL injection flaws. Likewise, if you accept an attachment from a user and then display that attachment using a means other than the provided ViewAttachment function, then make sure you either validate that input or validate the output. For example, let's say you have a page that allows users to upload JavaScript or other HTML that will be executed on other pages. Failure to restrict this type of usage provides malicious users with an opportunity to create HTML injection and cross site scripting vulnerabilities.

Serve those JavaScript Libraries Quickly... and Safely

If at all possible, don't serve JavaScript libraries from PeopleSoft applications (dynamic PeopleCode). The best place to serve a JavaScript library is from a static file residing on the PeopleSoft web server. Many PeopleSoft developers, however, don't have access to their web server file systems. The next logical place to store a JavaScript library is in an HTML definition in app designer. This is where the PeopleTools developers store JavaScript libraries and, therefore, is the preferred location for small JavaScript libraries. Unfortunately, the maximum size of a PeopleTools 8.4x HTML definition is too small to fit a good JavaScript library like jQuery. Enter John and Derek. John and Derek both wrote about a workaround to this limitation. Rather than using HTML Definitions, these innovative developers used the message catalog to store their JavaScript libraries (jQuery, of course). Here are links to those blog posts:

• Ajax and PeopleSoft from John Wagenleitner
• Injecting Javascript & AJAX into a PeopleSoft Page from Derek Tomei

Both of these posts provide an alternative for PeopleSoft developers that want to use a JavaScript library but don't have access to their PeopleSoft web servers' file structure. Building upon John's and Derek's approach, here are some ideas to help improve performance.

Packing/Minification/Raw

Based on Julien Lecomte's post Gzip Your Minified JavaScript Files, we can see that Dean Edwards's Packer algorithm produces the smallest compressed file. As Julien notes, however, the benefit of that smaller download is offset by a corresponding increase in execution/evaluation time. Julien points out that other compression techniques like Douglas Crockford's JSMin and Yahoo's YUI Compressor produce larger files that evaluate much faster. Therefore, when using JavaScript compression alone, you, as an analyst or developer, need to determine which method is appropriate for your implementation. Do you have low bandwidth connecting powerful computers to your PeopleSoft instance? If so, then maybe the Packer algorithm is more appropriate for you. If you have good bandwidth (or low bandwidth and low power client computers), then maybe you should consider JSMin or the YUI Compressor. Both of these compression techniques produce compressed text files that can be stored in a message catalog entry as described by John and Derek.

Caching

Browsers are designed to cache static, externally referenced resources. The methods demonstrated in the previously mentioned blogs insert the contents of a JavaScript library into the body of an HTML page. By inserting the contents directly into a page, the browser is not able to cache it with other JavaScripts, stylesheets, and images. An alternative approach is to serve JavaScript libraries stored as message catalog entries from IScripts. Using this approach, you could reference your JavaScript library using HTML like:

<script type="text/javascript" language="JavaScript" src="/psc/portal/EMPLOYEE/EMPL/s/WEBLIB_JS_LIBS.ISCRIPT1.FieldFormula.IScript_jQuery">
</script>

The PeopleCode for this IScript would look something like:

Function IScript_jQuery()
   Local string &jq = MsgGetExplainText(28000, 1, "alert('Unable to load the jQuery JavaScript library!\n\nThe Message Catalog entry (28000, 1) does not exist.');");
   %Response.SetHeader("Cache-Control", "max-age=28800, proxy-revalidate");
   %Response.WriteLine(&jq);
End-Function;

Update November 23, 2009: PeopleSoft actually overwrites the Cache-Control header. I have not found a way to set this through an IScript. The only solution I have found to cache IScript content is to use a ServletFilter to set the Cache-Control header after PeopleSoft returns a response.

The benefit of this approach is that users only download the JavaScript library once, rather than once per page visit. Considering cache, file size is not as important as execution time. Because the Packer algorithm produces a smaller file size, it excels in low bandwidth scenarios. With caching, however, the one time bandwidth hit might not compensate for the on going evaluation cost of the Packer algorithm. If you cache your JavaScript library, then a minification algorithm like JSMin or YUI Compressor may provide better performance.

GZip Compression

The first column of stats on Julien Lecomte's matrix compares the size of jQuery when packed, minified, or YUI Compressed. The second column compares that same content GZip compressed. GZip compressed, all three JavaScript compression methods result in a fairly comparable download size. When GZipped, the main difference between JSMin/YUI and Packer is the browser evaluation time required to "unpack" the packed JavaScript library.

The next performance improvement to explore with our IScript scenario is GZip compression. Fortunately, PeopleSoft takes care of GZip compression for us. If you look at your Web Profile settings, you will notice a check box labeled Compress Responses. If you enable this setting, PeopleSoft will automatically GZip compress the contents of your IScripts (and the contents of any other PeopleSoft page).

Whatever approach you take, be sure to consider security. OWASP listed Injection flaws as the Number 2 security concern in the OWASP 2007 top 10. Injection isn't limited to SQL. If you take user entered values and insert them into the HTML of a page, unescaped, then you have given that user the ability to inject malicious content into a page. If you use a message catalog approach and insert the contents of a message catalog entry into a page, then be sure to secure your message catalog. Failure to do so could have the same impact as the number one security threat in the OWASP 2007 Top 10: Cross Site Scripting (XSS)

Injecting JavaScript Libraries into PeopleSoft Pages

Before you start creating Ajax and Dynamic HTML usability enhancements for your PeopleSoft applications, I recommend learning a good JavaScript library. When creating client side usability enhancements, JavaScript libraries significantly reduce the amount of JavaScript you have to write and maintain. The first page of a Google search for JavaScript libraries should display several alternatives. Wikipedia also maintains a list of JavaScript libraries (my favorite JavaScript library is jQuery). After you select a JavaScript library, read the library's documentation, read some tutorials, and write some static HTML test pages. It is a good idea to get to know your chosen JavaScript library before you try to integrate it with PeopleSoft.

After you find and become proficient with a good JavaScript library, you will need a way to inject that library into your PeopleSoft application. If you limit your Ajax/DHTML plans to modifying a select number of delivered pages or enhancing a custom page, then you can inject your JavaScript library by adding a new HTML Area to your target page with the appropriate <script> tag pointing to the location of your JavaScript library. If your plans call for adding a generic usability enhancement to every page, then you will want a different solution. It is not practical or advisable to modify every page of your PeopleSoft application.

One way to make a JavaScript modification available to all pages within your application is to find an Application Designer managed object that can act as a vehicle, carrying that JavaScript modification to the client browser. We could then modify that object, injecting our JavaScript library into all PeopleSoft pages. Now that we have a potential solution, let's use our analytical skills to try to find a managed object that is common to all PeopleSoft pages. An analyst is a lot like a detective; investigating computer systems looking for patterns and solutions. Like any mystery, let's start with what we know - the facts. One thing we know is that the PeopleSoft user interface is comprised of HTML, JavaScript, CSS, and images. Using our browser we can view the source of the PeopleSoft generated HTML, CSS, and JavaScript, looking for patterns and other clues. Browser based tools like Firebug dramatically simplify this type of investigation, allowing us to view all of a page's JavaScript and CSS resources from a drop-down list. Using Firebug, we can see that the cs servlet serves many of the images, CSS, and JavaScript resources used by PeopleSoft pages. Since we know that the PeopleSoft application compiles CSS files from App Designer stylesheets and that images served by PeopleSoft come from Image Definitions stored in App Designer, it stands to reason that those JavaScript files served by the cs servlet would also come from managed objects in App Designer. Making the case stronger, we can see that PeopleSoft applications store JavaScript files in the web server cache directory, the location for all client-side managed objects.

Based on the previously established anecdotal evidence, we will assume that JavaScript files are managed object. If this is true, the next logical question to ask is, "What type of managed object?" Just as CSS files translate to Stylesheet managed objects, there must be some type of managed object that contains JavaScript. Searching PeopleBooks for JavaScript, we can see that the function GetJavaScriptURL() serves an HTML Definition as a JavaScript file. Based on this information, we form the following hypothesis:

JavaScript files served by the cs servlet are HTML Definitions and can be modified in App Designer like any other HTML Definition.

To test this, we can copy the name of a JavaScript file, like PT_PAGESCRIPT, and try to open that item as an HTML Definition. This test passes. Should we now conclude that JavaScript files served by the cs servlet are HTML Definitions? Two more tests:

1. Compare the HTML Definition to the downloaded JavaScript file. Don't expect a perfect match. The HTML Definition may contain Meta-HTML whereas the downloaded JavaScript file will contain the Meta-HTML's resolved value.
2. Modify an HTML Definition and check the results.

Using a file diff utility like WinDiff or jEdit's JDiffPlugin, we can compare the downloaded JavaScript file to the contents of the PT_PAGESCRIPT HTML Definition.

To satisfy the modification test, I suggest adding a short comment to the end of PT_PAGESCRIPT, something like /* XXX */. Since both of these tests pass, we have very strong evidence that JavaScript files served by the cs servlet are, in fact, HTML Definitions and can be modified using App Designer. Based on this investigation, we have discovered an object type that can we can use as a vehicle to carry our global customizations to our users' browsers. Since we have been using PT_PAGESCRIPT for testing, it would seem logical to continue with that HTML Definition, customizing it as needed to add additional JavaScript based DHTML usability enhancements. I tried this once. After writing a few lines of code and saving, App Designer displayed an error telling me that the HTML Definition had exceeded the maximum size and would be truncated. Generally speaking, the actual size limitation is not relevant. What is relevant is that we know a size limitation exists. Considering the size of PT_PAGESCRIPT without any modifications, there is no room for us to add additional JavaScript to PT_PAGESCRIPT. Looking through the list of JavaScript files common to all PeopleSoft pages, I suggest PT_COPYURL. PT_COPYURL appears to contain the JavaScript required to make the copy URL button work. The copy URL button is that double paper/carbon copy button in the page bar at the top of most PeopleSoft pages. I don't think early 8.4x versions of PeopleTools had this button. I don't remember when it was added, but I do remember seeing it as early as PT 8.46. If you have that button, then chances are, you have the PT_COPYURL HTML Definition. If you don't, then you may have to find a different HTML Definition to modify.

Once you identify an HTML Definition, add JavaScript similar to the following to the end of the delivered HTML Definition:

/* Conditionally include a JavaScript/Ajax libary */
if(!window.jQuery) {
  document.write("<scr" + "ipt id='jq' " + "src='/scripts/jquery.js'><\/script>");
}

/* Unconditionally insert a JavaScript file */
document.write("<scr" + "ipt id='xxx_ui' " + "src='/scripts/ui.js'><\/script>");

The first 3 lines demonstrate how to insert a static JavaScript file into all PeopleSoft pages conditioned upon the existence of an object. We typically display PeopleSoft pages in a frameset where the content frame only contains the HTML, JavaScript, and CSS required for that page. It is possible, however, to use a display template that proxies a page's content into the same HTML page as the header. The HOMEPAGE_DESIGNER_TEMPLATE is an example of this type of HTML template. If your header also contains a reference to your JavaScript library, then, best case, you will have multiple instances of your JavaScript library in memory. Worst case, your page will quit working. One way to work around this issue is to test for the existence of your JavaScript library prior to inserting it into a page. Since I use jquery, my code tests for the existence of the jQuery object.

The last line of the example above shows how to blindly insert a static JavaScript file into a PeopleSoft page. This approach works well for static JavaScript that you know isn't used by your header.

If you don't have access to your web server to install static JavaScript files or if your JavaScript needs to be dynamic, then take a look at John's post AJAX and PeopleSoft. In the post and comments, you can read about alternative ways of storing and serving JavaScript to PeopleSoft pages.

Changing a delivered HTML Definition is considered a modification. Like all modifications, you will need to consider compatibility and upgrade issues. To manage this modification through PeopleTools upgrades and patches, make sure you adequately document your modifications with code comments, project comments, and additional project management documentation. When considering upgrades, your documentation goal is to identify your modification and point the person applying an upgrade to any documentation related to this modification. Because of size limitations, you may not be able to document your entire modification inline. You will, however, be able to point other people at your documentation for this modification. For an effective, short, inline comment, I suggest something like:

<!% BEGIN xxx_1234, 13-DEC-2008, you@yourcompany.com -->
Your modified code goes here...
<!% END xxx_1234, 13-DEC-2008, you@yourcompany.com -->

With this comment, I have documented the start and end of this modification, the project name of the modification (xxx_1234), the date of the modification (13-DEC-2008), and the developer that made the modification (you@yourcompany.com). I have applied several patches over other developers' modified code. Without this type of START/END comment, it is impossible to differentiate between delivered code and modified code. Likewise, sharing a common prefix for all modifications (xxx_ in this case), dramatically simplifies searching for and identifying modifications.

Any time you modify a delivered object, you risk rendering that object unusable. If you modify a delivered PeopleTools object like PT_PAGESCRIPT, ensure that the delivered code works the same as it did before you modified it.