Upgrading to a New Release

(And a trick for installing later releases from scratch!)

Thursday, April 30, 2009Copyright 2006,2008,  R. James Holton. All rights reserved.

 

 Although it is currently a manual process, upgrading to a later release of ESS is fairly straightforward.  You are required to download the release ZIP file and basically move the contents of the ess and ess-app folders to your installation.  You may then be required to add elements to configuration files.  This document covers upgrading from release 4.0.3 to 6.2.0.  This allows you to install the latest ESA appliance and then upgrade the software to the most recent.

 

Trick: A simple and quick way to install ESS from scratch, is to use the installer from release 4.0.3 and then upgrade.  This is much easier than manually installing Apache, Tomcat, MySQL, the connector and finally ESS.

 

Here are the steps required to upgrade from a previous version of ESS to the most current:

 

  1. {www} is the parent folder for the Web server.  {app} is the parent folder for the application server.  {data} is the folder that contains the xmlu, xmlr, and xmls folders.  Make sure you know where these folders are before continuing.  You can consult ConfigurationInfo.txt for this information.  Also, make sure you have the correct security access to the system.  Insure that files are installed so that you application server can run them.  For example, I installed ESS in the c:/ess folder, so the above values would be:

 

a)      {www} = “c:/ess/apache2”

b)      {app} = “c:/ess/jakarta-tomcat”

c)      {data} =  “c:/ess

d)      ConfiugrationInfo.txt = “c:/ess/ConfigurationInfo.txt”

 

  1. Make a complete backup of all parts of the ESS system.  This must include the {www}/htdocs/ess, {app}/webapps/ess-app and {data}/xmls folders.
  2. Download the release ZIP file from SourceForge.net.  The Source Forge ESS project is expense-ss.    If necessary unpack the ZIP file to a folder.  We’ll refer to this folder as {release}.
  3. Copy the entire contents of the {release}/htdocs/ess folder to the {www}/htdocs/ess folder. [Note: some Apache Web server installations use html instead of htdocs.   Other Web servers may use other folders to hold their HTML pages.] 
  4. You now need to copy the contents of {release}/webapps/ess-app to {app}/webapps/ess-app.  However, before you do that, you need to decide if you want to update the support JAR libraries with the release versions.  While the vast majority of installation will want to update the libraries with the release version, you may not want to.  In particular, if you are releasing on a non Windows and Linux platform, you may have used your own versions of these libraries.  In almost all cases, you’d have previously performed a manual install and will be aware of the fact that you did not use the released JAR libraries but used different versions of these libraries.  If you do not want to use the release JAR libraries, you may delete them, or the one you do not want, from the {release}/ess-app/WEB-INF/lib folder in the release folder before preceding.
  5. Copy the entire contents of the {release}/webapps/ess-app to {app}/webapps/ess-app after completing step 5. 
  6. From your current {data}/xmls/Status.xml routing file, record the URL that is being used to login to ESS.  You will need this information below.
  7. With the exception of system.xml, security.xml, and any other configuration file you may have modified, copy the other configuration files from {release}/configurations/shared/xmls and {release}/configurations/mysql/xmls  to {data}/xmls.  Besides system.xml and security.xml, you will need to reconcile configuration files to insure your system performs to your expectations. 
  8. In the Status.xml file, replace the @@webserver@@ strings with the appropriate value for your server.  Note: if you have modified Status.xml, you will have to compare and edit your original file.
  9. The following elements should be taken from {release}/configurations/mysql/xmls/system.xml file and added to the original {data}/xmls/system.xml, which was not replaced.  While doing this, review any links, particularly in the receiptmanagement element, and make sure that they point to valid folders or URLs.

 

    • configuration.currency
    • configuration.transactionlimit
    • configuration.distance
    • configuration.omitforeignguidelines
    • configuration.purposescreen
    • configuration.historycopy
    • configuration.billable_name
    • personneltable.useradminsql
    • currency
    • departtable.departlistsql
    • departtable.departadminsql
    • checksql
    • receiptmanagement

 

 

  1. Create a {www}/htdocs/ess/receipts folder.  This folder is used to hold receipt scans.  Replace @@receipts@@ with the full path to that folder (e.g., c:/ess/Apache2/htdocs/receipts).
  2. Log onto MySQL, use the adisoft database and execute the attached UPDATE.SQL
  3. Stop and restart the Web and application servers.  Consult the “Starting and Stopping Services” document if you need instructions on how to do this.
  4. After logging on the system and producing a test expense report, review expense.log for any [500] errors.
  5. Please send a email message to service@expenseservices.com with any corrections or suggestions regarding these procedures.

 

 

 

Copy of the update.sql

 

 

USE ADISOFT;

 

CREATE TABLE FX ( CURRENCY varchar(20),

                        XDATE date,

                        HOME varchar(20),

                        XRATE numeric(14,4),

                        RATETYPE char(1),

                        XSOURCE char(36),

                        CONSTRAINT PK_RATE PRIMARY KEY (CURRENCY, XDATE, HOME) );

 

CREATE TABLE MILEAGE ( COMPANY varchar(8),

                        EFFECTIVE date,

                        UNITS varchar(10),

                        CURRENCY varchar(20),

                        RATE numeric(12,4),

                        CONSTRAINT PK_MILEAGE PRIMARY KEY (COMPANY, EFFECTIVE) );

 

INSERT INTO MILEAGE VALUES ( '001','2001-01-01','Miles','US Dollars',0.4250);

 

DROP TABLE COMPANY;

 

CREATE TABLE COMPANY ( COMPANY varchar(8),

                       DESCRIP varchar(30),

                       PAYROLL varchar(3),

                       CURRENCY varchar(20),

                       UNITS varchar(20),

                       PARENT varchar(100),

                       LOGOFILE varchar(100),

                       HEIGHT varchar(3),

                       WIDTH varchar(3),

                       CLIENT varchar(8),

                       EXPENSE varchar(8),

                       PAYMENT varchar(8),

                       MERCHANT varchar(8),

                       LOCATION varchar(8),

                       GUIDE varchar(12),

                       MILEAGE varchar(8),

                       PROJECT varchar(8),

                       OFFSETS varchar(20),

                       STEPS varchar(20),

                       AUDITOR varchar(12),

                       LANGUAGE varchar(10),

                       MAPPING varchar(100),

                       ACTIVE char(1),

                       GL_COMPANY varchar(8),

                       PURPOSESCREEN varchar(30),

                       CONSTRAINT PK_COMPANY PRIMARY KEY (COMPANY) );

 

 

INSERT INTO COMPANY VALUES ( '001','General Company','','US Dollar','Miles','ess-app/Report.jsp','ERaaS.gif','50','150','001','001','001','001','001','STD','001','001','generaloffset','steps','','English','','1','001','Head2b');

 

 

ALTER TABLE account ADD COMPANY VARCHAR(8);

ALTER TABLE charge ADD COMPANY VARCHAR(8);

ALTER TABLE client ADD COMPANY VARCHAR(8);

ALTER TABLE location ADD COMPANY VARCHAR(8);

ALTER TABLE project ADD COMPANY VARCHAR(8);

ALTER TABLE merchant ADD COMPANY VARCHAR(8);

ALTER TABLE user ADD COMPANY VARCHAR(8) AFTER DEPART;

ALTER TABLE report ADD COMPANY VARCHAR(8) AFTER DEPART;

ALTER TABLE report ADD CURRENCY VARCHAR(20);

ALTER TABLE report ADD HISTORY VARCHAR(8);

 

ALTER TABLE user ADD CURRENCY VARCHAR(20);

ALTER TABLE user ADD XFUNCTION VARCHAR(10);

ALTER TABLE user ADD LANGUAGE VARCHAR(10);

 

 

UPDATE account SET COMPANY = '001';

UPDATE charge SET COMPANY = '001';

UPDATE client SET COMPANY = '001';

UPDATE location SET COMPANY = '001';

UPDATE project SET COMPANY = '001';

UPDATE merchant SET COMPANY = '001';

UPDATE user SET COMPANY = '001';

UPDATE report SET COMPANY = '001';

 

UPDATE user SET CURRENCY = 'US Dollar';

UPDATE user SET XFUNCTION = 'Auditor';

UPDATE user SET LANGUAGE = 'English';

 

INSERT INTO SYSTEM VALUES ( 'ESS','COMPANY_SEQUENCE','100');

INSERT INTO SYSTEM VALUES ( 'ESS','PERSNUM_SEQUENCE','100');

INSERT INTO SYSTEM VALUES ( 'ESS','SCAN_SEQUENCE','100');

 

CREATE TABLE CURRENCY ( CURRENCY varchar(20),

                        XLIMIT numeric(12,2),

                        COMMENT varchar(60),

                        XSHOW char(1),

                        SHOWLIST char(1),

                        CONSTRAINT PK_CURRENCY PRIMARY KEY (CURRENCY) );

 

INSERT INTO CURRENCY VALUES ( 'US Dollar',10000,'','1','1');

INSERT INTO CURRENCY VALUES ( 'Canada Dollar',10000,'','1','2');

INSERT INTO CURRENCY VALUES ( 'Mexico Peso',10000,'','1','3');

INSERT INTO CURRENCY VALUES ( 'ECC Euro',10000,'','1','4');

INSERT INTO CURRENCY VALUES ( 'Argentina Peso',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Australia Dollar',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Bahrain Dinar',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Brazil Real',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Chile Peso',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'China Renminbi',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Colombia Peso',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Czech Koruna',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Denmark Krone',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Egypt Pound',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Hong Kong Dollar',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Hungary Forint',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'India Rupee',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Indonesia Rupiah',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Isreal Shekel',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Japan Yen',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Jordan Dinar',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Kuwait Dinar',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Lebanon Pound',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Malaysia Ringgit',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Malta Lira',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'New Zealand Dollar',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Norway Krone',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Pakistan Rupee',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Peru new Sol',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Phillippines Peso',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Poland Zloty',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Russia Ruble',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Saudia Arabia Riyals',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Singapore Dollar',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Slovak Koruna',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'South Africa Rand',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'South Korea Won',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Sweden Krona',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Switzerland Franc',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Taiwan Dollar',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Thailand Baht',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Turkey Lira',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'UK Pound',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'United Arab Dirham',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Uraguay Peso',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Venezuela Bolivar',10000,'','1','5');

INSERT INTO CURRENCY VALUES ( 'Specify in Source',10000,'','1','9');

 

 

###