+1-757-461-3022 x124

Scripting and View Change Follow-up

Monday, June 18, 2012

A few months ago I wrote to warn you that some old database views and script toolbox methods would be removed from the xTuple core product. This work has been completed and will first appear in the 4.0.0Beta2. We don't yet have a date for that beta release.

Several of our partners have already made the necessary changes to their extension packages so they will be ready for the new release. Make sure you are ready, too. As I said in December, "You can start this work now...."

The obvious question is, "What do I need to do?" There are two different answers, depending on how you have customized your xTuple ERP database and application. You may need to change some database queries and you may need to change some JavaScript extension code.

Database Query Changes

If you have written any custom queries, review them. This includes all queries in customized reports, even those you may have copied from xTuple's grade 0 reports. You should also look at any stored procedures, application scripts, and MetaSQL statements (System > Design > MetaSQL) you may have written or modified. If you have xTuple Connect, check the queries used in your EDI Profiles.

In most cases the change is very simple: replace the name of the old view with the name of the underlying table. You might need to change the column list to get the columns from the table but rename them to the old names with AS. Sometimes you'll need to add JOINs to the FROM clause or an extra condition in the WHERE clause.

Here are a few examples from the cust/custinfo pair:

  Change This To This
A simple example

SELECT cust_id, cust_number, cust_name
  FROM cust
WHERE cust_id = <? value('cust_id') ?>;

SELECT cust_id, cust_number, cust_name
  FROM custinfo
WHERE cust_id = <? value('cust_id') ?>;

A slightly harder one with two options

SELECT cust_id, cust_number, cust_corrcontact
  FROM cust
WHERE cust_id = <? value('cust_id') ?>;

-- exactly the same result as with the old view:
SELECT cust_id, cust_number,
       trim(cc.cntct_first_name || ' ' || cc.cntct_last_name) AS cust_corrcontact
  FROM custinfo
  LEFT OUTER JOIN cntct cc ON (cust_corrcntct_id=cc.cntct_id)
WHERE cust_id = <? value('cust_id') ?>;

-- easier to write and slightly nicer output:
SELECT cust_id, cust_number,
       formatCntctName(cust_corrcntct_id) AS cust_corrcontact
  FROM custinfo
WHERE cust_id = <? value('cust_id') ?>;

An even harder one, again with two options

SELECT cust_id, cust_number,
       cust_corraddress1,
       cust_corraddress2,
       cust_corraddress3,
       cust_corrcity,
       cust_corrstate,
       cust_corrzipcode
  FROM cust
WHERE cust_id = <? value('cust_id') ?>;

-- exactly the same result as with the old view:
SELECT cust_id, cust_number,
       ca.addr_line1      AS cust_corraddress1,
       ca.addr_line2      AS cust_corraddress2,
       ca.addr_line3      AS cust_corraddress3,
       ca.addr_city       AS cust_corrcity,
       ca.addr_state      AS cust_corrstate,
       ca.addr_postalcode AS cust_corrzipcode
  FROM custinfo
  LEFT OUTER JOIN cntct cc ON (cust_corrcntct_id=cc.cntct_id)
  LEFT OUTER JOIN addr  ca ON (cc.cntct_addr_id=ca.addr_id)
WHERE cust_id = <? value('cust_id') ?>;

-- easier to write and nicer output but bad for displaying in tables:
SELECT cust_id, cust_number,
       formatAddr(cc.cntct_addr_id) AS corr_address
  FROM custinfo
  LEFT OUTER JOIN cntct cc ON (cust_corrcntct_id=cc.cntct_id)
WHERE cust_id = <? value('cust_id') ?>;

Here again is the list of views to remove and the corresponding tables to use instead. In addition to the view and table names listed last time, this version has more information about the views themselves, including some hints on how you might need to tweak the queries that use them. Everything here can be learned by reading the view definitions, but hopefully this is a more convenient format to get you started.

View Base Table to Use Other Considerations
apchk checkhead The old apchk view only showed checks written to Vendors, so you may need to limit some checkhead queries withcheckhead_recip_type='V'
apchkitem checkitem Same here -- you may need to join the checkitem back to the checkhead table and restrict the query to Vendors
coship shipitem The coship view only showed items that shipped for Sales Orders, so you may need to join the shipitem table back to shiphead and restrict the query to Sales Orders (see cosmisc)
cosmisc shiphead The old cosmisc view only showed shipments for Sales Orders, so you may need to limit some cosmisc queries with shiphead_order_type='SO'
cust custinfo The cust view did 4 outer joins to collect information about the correspondence and billing contacts if they exist. To get the billing contact information, it joined custinfo.cust_cntct_id with the cntct table, then that Contact record with the addr table. For the correspondence contact, the cust view joined custinfo.cust_corrcntct_id with the cntct table and then that record with the addr table.
porecv recv The porecv view pulled information from the pg_user, pohead, itemsite, and item tables, and also restricted its results torecv_order_type='PO' to get only Purchase Order Receipts.
shipto shiptoinfo Like the cust view, the shipto view joined with the cntct and addr tables to get contact information. Unlike Customers, Ship-Tos only have one contact and address.
sopack pack Like the cosmisc view, sopack restricted its results to Sales Orders only with pack_head_type='SO'
vend vendinfo For reasons lost in the mists of time, the vend view joined the vendinfo table with the cntct twice, once for the primary contact and again for the secondary contact, but joined directly with the addr table for the vendor's address. This is different from the cust view, which got its addresses through its contacts.
vendaddr vendaddrinfo Like the vend view, vendaddr joined with the cntct table to get its Contact name, then separately to addr to get its Address.
warehous whsinfo warehous was like vend and vendaddr, joining with cntct and addr separately to get Contact and Address information.

Application Script Changes

Please review any JavaScript customizations you have written to make sure you aren't using any of the old deprecated methods in the ScriptToolbox. These are documented in the xTuple ERP Programmer Reference at least as far back as release 3.5.0. The Deprecated List and the ScriptToolbox description both show what to use instead. Here are examples of the most frequent changes we had to make to xTuple's scripts:

Old Method Name Replace This With This
messageBox

toolbox.messageBox("critical", mywindow,  qsTr("Database Error"), qry.lastError().text);

QMessageBox.critical(mywindow, qsTr("Database Error"), qry.lastError().text);

menuAddAction

var tmpact = toolbox.menuAddAction(pMenu, qsTr("Create BOO..."), privileges.check("MaintainBOOs"));

var tmpact = pMenu.addAction(qsTr("Create BOO..."));
tmpact.enabled = privileges.check("MaintainBOOs");

layoutGridAddWidget

var layout = toolbox.widgetGetLayout(_calendar);
toolbox.layoutGridAddWidget(layout, _bufferMgt, 2, 0);

var layout = toolbox.widgetGetLayout(_calendar);
layout.addWidget(_bufferMgt, 2, 0);

As you can see, these changes are not hard to make.

How Do I Find Things to Change?

That's a problem, isn't it?

If you keep copies of your extension scripts, reports, metasql, and stored procedures outside your database, this is easy. We recommend using a source code control system for all of these files so you can track changes over time and have backup copies in case of emergency. In case you do have text file versions of your changes, you can easily use a command line tool like *NIX grep or Windowsfind to search for uses. grep makes this easy by allowing for whole-word searches, including recursing through all of the files in an entire directory tree.

For example:

$ grep -w -r cust dbscripts

will search through all files in the directory 'dbscripts' or any of its subdirectories to find the whole word 'cust'. Then look carefully at the file to see if it needs to be changed or if 'cust' is being used for something other than a view name. You can do the same thing with 'toolbox.menuAddAction' to find scripts that call this old JavaScript method.

If you don't have copies of these files outside the database, you can still search for them inside the database. This will be harder, since you'll have to filter the results manually to find your instances as opposed to xTuple's changes. Using a tool like pgAdmin or psql, you can run regular expression queries on the report, script, and metasql tables to find potential items in need of change. For example:

SELECT report_name FROM reports WHERE report_grade > 0 and report_source ~* '\Wcust\W';

will look for custom reports that contain the word 'cust' by itself.

Gil Moskowitz

Director Software Development
Gil joined xTuple in 2005 to develop the first version of multi-currency support in our products. He helped xTuple transition from its original closed source OpenMFG product to the commercial open source company we are today. Before coming to xTuple, Gil worked for several large and small software companies in a variety of roles, including Informix Software, where he managed the database backup/restore utility group. He always advocates for, and delivers, high-quality products through improvements to the software development process. Ask about his other jobs next time you see him — ! He has a B.A. in Biology from Reed College and an M.S. in Computer Science from Old Dominion University.