Most Powerful Open Source ERP

How To Add Custom Table to SQL Catalog

showing how to add custom tables to the SQL Catalog to more easily search for custom properties.
  • Last Update:2016-02-11
  • Version:001
  • Language:en

The stock portal_catalog lets you search database by a number of basic properties, like id, uid, portal_type, title, description, validation_state, creation_date and many more. Sometimes, however, you may need to add you own properties to it - if you want to be able to quickly search on a property that you defined, or if you need your custom property to be a sortable and searchable column in a listbox powered by portal_catalog.

Table of Contents

Setup

This is how to achieve this - let's take an example: we need to be able to quickly find an email address, and figure out to whom it belongs. Email adress is stored in a property "url_string" of a portal_type "Email". We can search for it like this:

result=[r.getObject() for r in context.portal_catalog(portal_type='Email')]
adrs=[o for o in result if o.getUrlString()=='what@we.look.for']

But we don't want to do it, because it would give very poor perfomance. The sql catalog is there to make things fast, let's use it.

  • Create a table in MySQL
    The table will have two fields - uid (unsigned bigint(20)) and url_string (varchar). The uid has to be a primary key - this is important! Let's name the table 'email'.
  • Let the catalog search it
    In portal_catalog/erp5_mysql/manage, in 'properties' tab, there is a box 'sql_search_tables', with some tables selected. Add the 'email' table to the selection (Ctrl-click).

For searching, this is it - if you insert into the 'email' table uid and url_string of an existing email address, portal_catalog will be able to find it. What we still need to do is to make sure the table is created and cleared when necessary, and that our email addresses catalog themselves automatically.

  • Set up catalog clearing
    Create a Z SQL method 'z_create_email':
        CREATE TABLE `email` (
          `uid` BIGINT UNSIGNED NOT NULL,
          `url_string` varchar(255),
          PRIMARY KEY `uid` (`uid`),
          KEY `url_string` (`url_string`)
        ) TYPE = InnoDB; 
        
  • Create method 'z0_drop_email':
        DROP TABLE IF EXISTS email;
        
  • In Properties tab, add both methods to those selected in 'sql_clear_catalog' box.

Make portal_catalog record url_string upon reindexing
When you call a 'reindexObject' method on an ERP5 object, you actually tell portal_catalog to execute all z_ methods defined in the erp5_mysql catalog and selected in the 'sql_catalog_object_list' box. So: Create a ZSQL method 'z_catalog_email_list':

arguments: uid, getUrlString, getPortalType

<dtml-let email_list="[]">
  <dtml-in prefix="loop" expr="_.range(_.len(uid))">
    <dtml-if expr="getPortalType[loop_item]=='Email'">
      <dtml-call expr="email_list.append(loop_item)">
    </dtml-if>
  </dtml-in>
  <dtml-if expr="_.len(email_list) > 0">
    REPLACE INTO
      email (`uid`, `url_string`)
    VALUES
      <dtml-in prefix="loop" expr="email_list">
      (
        <dtml-sqlvar expr="uid[loop_item]" type="int">,  
        <dtml-sqlvar expr="getUrlString[loop_item]" type="string" optional>
      )
      <dtml-if sequence-end><dtml-else>,</dtml-if>
    </dtml-in>
  </dtml-if>
</dtml-let>

The tricky part here is that your method is executed every time you catalog anything, so it has to check whether the object is actually and Email address, otherwise it would raise an error. So it filters the list of objects to be catalogged, and, if any are found, puts them into the email table. Add it to the selection in 'sql_catalog_object_list' box. Create a ZSQL method 'z0_uncatalog_email':

arguments: uid

DELETE FROM email WHERE <dtml-sqltest uid op=eq type=int>

Add it to the selection in 'sql_uncatalog_object' box.

Voila - you're done. Now you can search and sort on it in a listbox exactly as you do on title, id or description, and search for it like this:

emails=context.portal_catalog(portal_type='Email',url_string='what@we.look.for')

If you had some emails in your database before, don't forget to reindex them! Also, keep in mind that catalog propeties are cached, so all this may not work until you clear the cache (in portal_caches tool, or using erp5admin Firefox extension).

Related Articles