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 |
SELECT cust_id, cust_number, cust_name |
| A slightly harder one with two options |
SELECT cust_id, cust_number, cust_corrcontact |
-- exactly the same result as with the old view: -- easier to write and slightly nicer output: |
| An even harder one, again with two options |
SELECT cust_id, cust_number, |
-- exactly the same result as with the old view: -- easier to write and nicer output but bad for displaying in tables: |
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...")); |
| layoutGridAddWidget |
var layout = toolbox.widgetGetLayout(_calendar); |
var layout = toolbox.widgetGetLayout(_calendar); |
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
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.