Product Documentation

This configuration is relevant for file attachments, document field display types and image field display types. Attachment files uploaded to ExtraView are, by default, stored as binary objects in the database. The Administrator has the choice to configure the system to store some or all of the uploaded attachments on a file system accessible to the application server. ExtraView keeps track of where all attachments are located within the database. Prior to the conversion, all attachments are marked as being stored “internally” – within the database. As part of this migration process, as each individual file is extracted and copied to its final location on the file system, the system marks that attachment as now being stored “externally”. The migration process may be done as a background task, without impacting the availability of attachments to the end users, at any point.

This means that there is no need for a significant outage period if you have tens or hundreds of thousands of attachments to move from the database to the file system. The Administrator may choose to have all files stored internally in the database, all files stored externally in the file system, or have a mixture of some file types stored within the database, and some file types stored on the file system.

MySQL Database Attachments

MySQL has some special considerations regarding file attachments. MySQL is very inefficient in the way it stores attachments in the database, and the Java driver software that accesses these requires a huge amount of memory for large attachments. To conserve memory, ExtraView constrains the size of MySQL attachments to 16 MBytes. Therefore, if you believe that you are likely to store and retrieve attachments larger than this size with the MySQL database, you should configure the attachments to work with external storage, where only the limitations of the operating system apply.

Configuring External Storage

To configure attachment storage options, sign on to ExtraView with the Administrator role. Click the Admin button on the navigation bar, and then choose the System Controls tab, then select Environment Settings, and edit the following settings:

• ATTACHMENT_REPOSITORY_OPT - This setting controls whether attachments are stored internally within the database, externally on the file system, or in some combination of the two methods. If the value of this setting is INTERNAL (the default value), then all attachments are stored internally within the database. If the value is set to EXTERNAL, then all the attachments are stored on the external file system.

  Alternatively, you may provide a comma separated list of file extensions and then all files with these extensions will be stored in the database while all others will be stored externally in the file repository. For example, if you set the value of this setting to "txt,doc,htm" then files of these types will be stored internally. This strategy leaves files of these types stored internally in the database and they remain searchable by keywords, while image and video files are stored externally. Regardless of the setting, ExtraView will continue to store dynamically generated thumbnail-sized image files in the database as these are relatively small files and must, for performance reasons, be more readily available.

  There is a third value for the setting. If the value is CUSTOM, then user custom code will handle the storage of file attachments.

• ATTACHMENT_REPOSITORY_ROOT – This setting defines the location outside the database where attachments will be stored. You must ensure that the path is valid from the application server(s) that are running ExtraView and that you have all the permissions to read and write to the storage. In a clustered or load-balanced environment, you must ensure that all instances of ExtraView can read and write to the same file system using this path.
   
  Note: If you change this setting, new attachments will be saved to the new location, and existing attachments will still reference the old location. You will need to ensure that the previous file path is still accessible by the ExtraView instance, or these existing attachments will not be available to end users.

  Ensure that you set up a backup method for this external storage as backing up your database will no longer backup the file attachments

• ATTACHMENT_REPOSITORY_DMAX - This setting defines the maximum number of files or directories that are created under one node of the external directory structure. The default for this value is 999. It is not likely that this value needs to be altered.

Sizing the file system

We suggest that you ensure that there is adequate space for future growth when sizing the file system to be used for storing attachments externally. To do this, have your DBA connect to your ExtraView database using Query Analyzer (SQL Server) or SQL Plus (Oracle) and run the following queries:

-- return the total size of the attachments stored in the database, in bytes.
select sum(file_size) from attachment;
-- return the number of attachments in the database
select count(*) from attachment;

Migration Utility Options

In order to allow current customers to easily migrate existing attachments to the file system location, a command line utility is provided to extract the attachment files out of the database and to save them to the file system. This step is only required if you wish to extract the existing attachments from the database and store them on a file system on your network. You must pass several parameters into the ExtraView command line utility:

You can move one attachment by providing either a single attachment ID:

doAttachmentMigration "-id 10000"

or you can move batches of attachments by providing a range of attachment IDs:

doAttachmentMigration "-from 10000 -to 50000"

Attachment ID values can be found by having your DBA run the following SQL query against the ExtraView database:

select min(attachment_id), max(attachment_id) from attachment;

Optional parameters can be added inside the double quotes in the command line parameters:

-report filename  - Generates a specific report file (by default the report will be dumped into ExtraView log file)

-delete - If specified, this deletes the attachment content blob from the database after it has been migrated successfully. We would not generally recommend this if you have a large number of attachments, as it is much more efficient to simply have your DBA truncate the table when you have completed the migration and have validated your results.

-testOnly - This allows a dry run of the attachment migration without any modification to the database or the repository.

-noValidation - This prevents the CRC-32 check on the stored migrated attachments, and may be somewhat faster. Use of this parameter is not recommended, because the extra time to validate the file content vis-à-vis the blob content is nominal, and the consequences of storing a blob in the file system incorrectly due to I/O error or network error could include a loss of valuable data. If the CRC-32 validation fails, the attachment is not migrated and an error is posted to the log and/or report file.

Running the Migration Utility

The migration command line utility is included in the ExtraView application package. It requires Java be installed on the application server where you run it. We provide shell and batch file scripts that you can use to run the utility. If you have deployed ExtraView in a WAR/EAR file, you will need to set up an exploded directory to run this script. The scripts are located in the evj/WEB-INF/data folder on your application server (ie Tomcat, WebLogic).

• Windows Tomcat Edit the doAttachmentMigration.bat file Locate the lines:

  set JAVA_HOME=%1
  set TOMCAT_HOME=%2
  set EV_BASE=%3

  Replace %1 with your path to Java (e.g. C:\ExtraView\jre1.7.0_67)

  Replace %2 with your path to Tomcat (e.g. C:\ExtraView\Tomcat7)

  Replace %3 with your path to ExtraView (e.g. C:\ExtraView\Tomcat7\webapps\evj)

  Save and exit the edit session. Open a command window in the evjWEB-INFdata folder and type the following command:

  doAttachmentMigration "parameters"

  where "parameters" is a double-quote enclosed list of parameters as per the options listed above
   

• Linux Tomcat Edit the doAttachmentMigration.sh file Locate the lines:

  TOMCAT_HOME=/usr/local/extraview/tomcat
  JAVA_HOME=/usr/local/extraview/java

  Set TOMCAT_HOME and JAVA_HOME to your correct paths for your installation.

  Save and exit the edit session.

  At the command prompt, type the following command:

  sh doAttachmentMigration.sh "parameters"

  where "parameters" is a double-quote enclosed list of parameters as per the options listed above
   

• Linux WebLogic Edit the doAttachmentMigrationWeblogic.sh file Locate the lines:

  JAVA_HOME=/usr/local/extraview/java
  WL_HOME=/usr/local/bea/weblogic12c
  EV_BASE=/path/to/extraview/application/evjxxx.jar

  Set JAVA_HOME, WL_HOME and EV_BASE to your correct paths for your installation.

  Save and exit the edit session.

  At the command prompt, type the following command:

  sh doAttachmentMigrationWeblogic.sh "parameters"

  where "parameters" is a double-quote enclosed list of parameters as per the options listed above.

Validation and Completion

While the migration utility runs, it displays information on the console. It will also write detailed log entries to a file in the evj/WEB-INF/log folder (or to the file defined in the -report parameter option). Once the process has completed, you can check the log file to determine if there were any problems. If all attachments were migrated, you can verify that the attachments are available to download and view through the ExtraView GUI. Once you are satisfied, you can have your DBA remove the binary attachments from the database.

If you have selected the option to have all attachments stored in the file system, your DBA can simply run the command:

update attachment set thumbnail_id = null where thumbnail_id is not null;
truncate table attachment_content;

If you have selected the option of having some types of file stored in the database and some files stored externally, your DBA will need to run some variant of:

delete from attachment_content
where attachment_id in
( select attachment_id
  from attachment
  where STORED_INTERNAL = ‘N’ );