SBM MODSCRIPT, PART 17 - File Fields

File and URL fields have been added to a recent version of SBM. As such, each field can store one or more entries, either files or URLs. The ModScript interface has not yet been updated to make interaction with these fields simple. However, reading from the fields is not all that difficult, as ModScript has access to the TS_FILE_OBJS table, where all the data is stored. Accessing file contents can be more difficult, as the files can either be on the file system or stored as a blob (this is a system configuration option). Also, interacting with files isn't always easy anyways, as files can contain binary data which would make reading the file or changing the file into questionable use cases. However, I was recently asked how a customer could access file data from a file field, where all files are JSON data. This makes sense, we can do something at the script level with JSON data, so I went down the rabbit hole and came up with the following two options. Keep in mind, if you only require the file names and sizes, there is no need to get the file contents. If you do not need the file contents, simply read the rows from TS_FILE_OBJS and decide what to do with the values.

 

Option 1: Direct access from ModScript

ModScript is fairly powerful, we should be able to simply grab the files, consume their contents, and process the JSON at will. For the most part, we can. The big blocker I ran into is that file attachments, when stored on the file system, can be on a network share and require a certain authenticated user to access them. If you are in this configuration, I'd suggest moving on to Option 2, as I did not find any workaround for network user impersonation in ModScript. Also, this doesn't have to be a network folder, it is possible that it is a local folder that requires specific user permissions. In either case, Option 2 is for you. Option 1 works well with files stored as blobs in the database or files stored with no user authentication requirements.

For direct access, ModScript can read the rows from the TS_FILE_OBJS table. It then iterates the entries, and reads the file contents from the related blob or file. It parses the file contents into a JSON object. At that point, it is ready for whatever JSON processing you wish to do. I simply invoked processJSONObj() from part 14 of this series as an example. 

include("processJSONObj");

def AreAttachmentsStoredInDB() {
  global __AreAttachmentsStoredInDB__; // singleton, init once, access only through this function
  if ( __AreAttachmentsStoredInDB__.is_var_undef() ) {
    var setting = Ext.CreateAppRecord( Ext.TableId("TS_SYSTEMSETTINGS") );
    setting.Read("StoreAttachmentsInDatabase");
    __AreAttachmentsStoredInDB__ = 0 != setting.GetFieldValueInt("LONGVALUE");
  }
  return __AreAttachmentsStoredInDB__;
}

def AttachmentsFileSystemLocation() {
  global __AttachmentsFileSystemLocation__; // singleton, init once, access only through this function
  if ( __AttachmentsFileSystemLocation__.is_var_undef() ) {
    var setting = Ext.CreateAppRecord( Ext.TableId("TS_SYSTEMSETTINGS") );
    setting.Read("WorkCenterAttachDir");
    __AttachmentsFileSystemLocation__ = setting.GetFieldValueString("STRINGVALUE");
  }
  return __AttachmentsFileSystemLocation__;
}

class FileFieldEntry {
  var name;
  var filename;
  var fileSystemName;
  var blobID;
  
  def FileFieldEntry() {
    this.name = "";
    this.filename = "";
    this.fileSystemName = "";
    this.blobID = 0;
  }
}

def GetFileFieldEntries( item, fieldName, outVect ) {
  var fileObjs = Ext.CreateAppRecordList( Ext.TableId("TS_FILE_OBJS") );
  var field = item.Fields().FindField( fieldName );
  fileObjs.ReadWithWhere( "TS_RECORDID=? and TS_TABLEID=? and TS_FIELDID=?",
                          [
                            Pair( DBTypeConstants.INTEGER, item.GetId() ),
                            Pair( DBTypeConstants.INTEGER, item.GetRecTableId() ),
                            Pair( DBTypeConstants.INTEGER, field.GetId() )
                          ] );
  for ( fileObj : fileObjs ) {
    var entry = FileFieldEntry();
    entry.name = fileObj.GetFieldValueString("NAME");
    entry.filename = fileObj.GetFieldValueString("FILENAME");
    entry.fileSystemName = fileObj.GetFieldValueString("CONTENTS");
    entry.blobID = fileObj.GetFieldValueInt64("BLOBID");
    outVect.push_back( entry );
  }
}

def GetFileContents( FileFieldEntry entry ) {
  if ( AreAttachmentsStoredInDB() ) {
    var f = TempFile();
    Shell.Db().WriteBlobToFile( entry.blobID, f.GetFileName() );
    return Ext.ReadTextFile( f.GetFileName() );
  }
  else {
    var path = AttachmentsFileSystemLocation();
    path += '\\';
    path += entry.fileSystemName;
    return Ext.ReadTextFile( path );
  }
}

var fileFieldEntries = [];
GetFileFieldEntries( Shell.Item(), "JSON_FILES", fileFieldEntries );
for ( fileFieldEntry : fileFieldEntries ) {
  var fileContents = GetFileContents( fileFieldEntry );
  Ext.WriteStream( fileContents );
  var json = fileContents.from_json();
  processJSONObj( json );
}

 

Option 2: Access file/url field values via REST call to JSONAPI

In many ways, this option is far simpler than Option 1, as you don't have to do it yourself. All we need to do is take advantage of the JSONAPI "GetFileField" function, which will provide the full file contents for our field. The one part that made this hard was that the JSONAPI uses BASE64 encoding on the file contents, and I needed some way to decode the contents as text so that I could work with it. This is why I wrote Part 16, where I provided a way to decode BASE64 values to text. First, you will need to add a RESTDataSource to your process app. Call it "SBM_JSONAPI" and point it to "http://localhost/workcenter/tmtrack.dll?JSONPage&command=jsonapi&JSON_Func=". It may be helpful to create an Endpoint for this, which allows you to customize the URL in AR for various environments. When ModScript invokes this URL, it needs to be able to get in to SBM AE, so the url may need to be https and could need to point to a specific AE runtime server or load balancer. The point here is that ModScript is invoking SBM AE's REST JSONAPI, so the URL has to help ModScript get there. The authentication type "Security Token" will probably work for handling auth, but this may also need to be fiddled with.

Once you have a RESTDataSource that will be used by ModScript, we can proceed:

include("processJSONObj");

def GetFileFieldEntries( item, fieldName ) {
  var REST = Ext.CreateAppRecord( Ext.TableId( "TS_RESTDATASOURCE" ) );
  REST.Read("SBM_JSONAPI");
  var out = "";
  if ( !REST.Get( out, [ 
                         Pair( "JSON_Func", "GetFileField" ),
                         Pair( "JSON_P1", item.GetRecTableId() ),
                         Pair( "JSON_P2", item.GetId() ),
                         Pair( "JSON_P3", fieldName )
                       ] ) ) {
    Ext.WriteStream( Shell.GetLastErrorMessage() );
    ExitScript();
  }
  return out;
}

add_global_const("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/", "CONST_BASE64TABLE");
def Base64DecodeAsText( string input ) { // assumes output is valid text (not binary)
  var sOut = "";
   
  var buf = [uint8_t(),uint8_t(), uint8_t(), uint8_t()];
  var encoded = int( input.size() );
  var count = 3 * ( encoded / 4 );
  var i = 0;
  var j = 0;
  
  while ( sOut.size() < count ) {
    // Get the next group of four characters
    //    'xx==' decodes to  8 bits
    //    'xxx=' decodes to 16 bits
    //    'xxxx' decodes to 24 bits
    for_each( buf, fun( entry ){ entry = 0; } ); // zero out buffer
    var stop = min( encoded - i + 1, 4 );
    for ( j = 0; j < stop; ++j ) {
      if ( input[i] == '=' ) {
        // '=' indicates less than 24 bits
        buf[j] = 0;
        --j;
        break;
      }

      // find the index_of inside CONST_BASE64TABLE for our value
      buf[j] = fun( s, c ) {
        for ( var i = 0; i < s.size(); ++i ) {
          if ( s[i] == c ) {
            return i;
          }
        }
        return string_npos;
      }( CONST_BASE64TABLE, input[i] );
      ++i;
    }
	
    // Assign value to output buffer
    sOut += char(buf[0] << 2 | buf[1] >> 4);
    if ( sOut.size() == count || j == 1 ) {
      break;
    }
    
    sOut += char(buf[1] << 4 | buf[2] >> 2);
    if ( sOut.size() == count || j == 2 ) {
      break;
    }
	
    sOut += char(buf[2] << 6 | buf[3]);
  }
  
  return sOut;
}

var fileFieldValue = GetFileFieldEntries( Shell.Item(), "JSON_FILES" );
var fileFieldValueJSON = fileFieldValue.from_json()["fieldFileObj"]["fileObjList"];
for ( fileFieldEntry : fileFieldValueJSON ) {
  var fileContents = Base64DecodeAsText( fileFieldEntry["contentsBase64"]["data"] );
  var json = fileContents.from_json();
  processJSONObj( json );
}

 

SBM ModScript - Table of Contents

SBM MODSCRIPT, PART 16 - BASE64DECODE
SBM MODSCRIPT, PART 18 - USING RANGE FOR EASY LIST...

Related Posts

Comments

 
No comments yet

Recent Tweets