Most Powerful Open Source ERP

Technical Note on SQL Catalog Structure

showing the structure of the portal catalog SQL tables used in ERP5.
  • Last Update:2016-04-22
  • Version:001
  • Language:en

"uid" attributes are foreign keys of catalog table except if explicitly noted otherwise.

To be catalogged using a given Z SQL Method, the following conditions must be met :

  • The site must be declared as indexable (see ZSQLCatalog/SQLCatalog.py isIndexable method). We don't want to reindex any object when the site is created, and that's the main switch used for that purpose.
  • The object must be declared as indexable : (see ERP5Type/Base.py immediateReindexObject() and _reindexObject() functions) . In some cases, we don't want a special object to get indexed.
  • The Z SQL Method must be defined as an sql_catalog_object_list in manage_propertiesForm available on the SQLCatalog object. This allows to have both methods used to [un]catalog objects and general SQL queries in the SQLCatalog object.
  • The object must match the rules set on manage_catalogFilter management page available on SQLCatalog object. This allows the manager of a site to tweak the configuration for a special install.
  • If applicable, the object must match filters written in DTML in the Z SQL Method itself. This allows the person writing the Z SQL Method to define precisely what should or not be cataloged in a given table.

Before being cataloged, each object get wrapped using ERP5Catalog/CatalogTool.py wrapObject() method to make some special values only used for cataloging available on it. Once wrapped and just before calling a Z SQL Method, the Z SQL Method parameters are gathered on the (wrapped) object in ZSQLCatalog/SQLCatalog.py catalogObjectList() method. This makes adding a catalog column as simple as modifying a bit of SQL and adding a parameter to the Z SQL Method.

Table of Contents

Catalog tables

Tables containing at most one entry per uid. The main table is catalog, but some portal types get additional columns using joints with other tables.

  • catalog
  • This table stores the list of all indexable objects from the ZODB and special reserved rows (used for UID assignment). Reserved rows have their path equal to "reserved", their uid set to the reserved uid value and their id set to an identifier which is unique to a given zope thread even in a zeo environment. The basic purpose of this table is to translate uid into a physical path (to get the object using restricted traverse). The additional columns are present in order to provide more filtering possibilities.

  • full_text
  • This table is separated from the main catalog for a purely technical reason: database storage engines don't all support fulltext search.

    • uid
    • SearchableText
  • compatibility
  • This table is provided for compatibility with CMF's catalog. Its use is disabled by default and is deprecated.

  • alarm
  • See ERP5/Tool/AlarmTool.py, ERP5/Document/Alarm.py and ERP5/PropertySheet/Alarm.py.

    • uid
    • Uid of the alarm object.

    • alarm_date
    • Date at which the alarm must be triggered. As alarm triggering is done by polling the table, all entries older than the current date will be tried. The alarm object will be checked to see if it's still enabled before starting the action.

  • delivery
  • See ERP5/Document/Delivery.py and ERP5/PropertySheet/Delivery.py.

    • uid
    • Delivery.

    • source_uid
    • Source (provider of the resource).

    • destination_uid
    • Destination (receiver of the resource).

    • source_section_uid
    • Juridical owner of the resource. (see FaqOnCoreModel).

    • destination_section_uid
    • Juridical destination of the resource (see FaqOnCoreModel).

    • resource_uid
    • Resource to deliver.

    • start_date
    • Date at which the resource left the source.

    • start_date_range_min
    • Earliest date at which the resource can leave the source.

    • start_date_range_max
    • Latest date at which the resource can leave the source.

    • stop_date
    • Date at which the resource arrived at destination.

    • stop_date_range_min
    • Earliest date at which the resource may arrive at destination.

    • stop_date_range_max
    • Latest date at which the resource may arrive at destination.

  • item
  • This table allows to find where a resource is at a given time. An item is considered as belonging to the section of the source node until it enters the destination node (ie, until the movement et in stopped state).

    See ERP5/Document/Item.py.

    • uid
    • Movement causing the item to change its location.

    • date
    • Date at which the item left the node and the section.

    • node_uid
    • Node at which the item is/was.

    • section_uid
    • Juridical owner of the item (see FaqOnCoreModel).

    • resource_uid
    • Resource the item being searched for is an instance of.

    • aggregate_uid
    • Item.

    • variation_text
    • Variation text of the movement (one of the possible resource variations).

    • simulation_state
    • Simulation state of the movement.

  • movement
  • See ERP5/Document/Movement.py.

    • uid
    • Movement.

    • explanation_uid
    • source_uid
    • Node from which the item will be extracted.

    • destination_uid
    • Node at which the item will be sent.

    • resource_uid
    • The moved item is an instance of this resource.

    • quantity
    • Quantity of resource in the item.

    • start_date
    • Date at which the movement started (item left the source node).

    • stop_date
    • Date at which the movement stopped (item entered the destination node).

    • price
    • is_accountable
    • is_orderable
    • is_deliverable
    • is_divergent
    • variation_text
  • predicate
  • See ERP5/Document/Predicate.py and ERP5/Tool/DomainTool.py. For the following ranges, the following rules apply:

      • If [base name] is defined, the value must match exactly
      • If [base name]_range_min is defined and [base name]_range_max is not, the value must be any value above or equal to [base name]_range_min to match.
      • If [base name]_range_max is defined and [base name]_range_min is not, the value must be any value strictly below [base name]_range_max to match.
      • If both [base name]_range_min and [base name]_range_max are defined, the above two rules must be matched at the same time.
    • uid
    • Uid of the predicate object.

    • quantity
    • quantity_range_min
    • quantity_range_max
    • start_date
    • start_date_range_min
    • start_date_range_max
  • predicate_category
  • See ERP5/Document/Predicate.py. uids in this tables are only uids of objects on which isPredicate() is true (as set in the filter page of the SQLCatalog).

    • uid
    • category_uid
    • base_category_uid
    • category_strict_membership
  • stock
  • On this table, uid is the uid of the related movement.

    uid cannot be the primary key : a single stock modification (movement on an accountable resource) is present in this table the same number of times as the sum of the number of sources plus the number of destination of that movement.

    • uid
    • The movement this is about.

    • node_uid
    • Location of the resource.

    • section_uid
    • Juridical ownership of the resource in that node (see FaqOnCoreModel).

    • payment_uid
    • function_uid
    • Defines a category on the movement (to use when searching the cost/income generated by a given function). Functions can be, for example, selling hardware, selling services,...

    • project_uid
    • Defines a category on the movement (to use when searching the cost/income generated by a given project).

    • mirror_section_uid
    • In the case of a movement causing 2 or more stock movements, this contains the section_uid of the other stock movement.

    • mirror_node_uid
    • In the case of a movement causing 2 or more stock movements, this contains the node_uid of the other stock movement.

    • resource_uid
    • The moved resource.

    • quantity
    • Quantity of moved resource (signed: negative in case of resource neaving the node, positive in the case of resource entering the node).

    • date
    • The date at which the current subpart of the movement was done (date at which the resource left/entered the node).

    • total_price
    • Price estimation of the resource by section. For example if the resource is dollar and the destination of the movement wants euros, this will contain the value of the resource (multiplied by the quantity) converted into euros.

    • portal_type
    • Portal type of the movement.

    • simulation_state
    • Simulation state of the movement.

    • variation_text
    • Variation text for the movement (one of the resource's possible variations).

    • sub_variation_text
    • Subvariation text for the movement (one of the resource's possible variations).

  • subject
  • This table contains keywords for a given object.

    • uid
    • subject
    • One of the keyworkds defined on the related object.

Specialised tables

Those tables contains data which is not directly proportionnal to the number of catalogged objects.

  • category
  • This table contains the list of relations set on an object. All the category levels are cataloged, which means that an object with a relation foo_base_cateogry/a/b will get 2 rows in this table, one with (self.uid, foo_base_category.uid, b.uid) and one with (self.uid, foo_base_category.uid, a.uid).

    • uid
    • Uid of the object on which the category relation is set.

    • category_uid
    • Uid of a portal_type == "Category" object.

    • base_category_uid
    • Uid of a portal_type == "Base Category" object containing the category_uid object.

    • category_strict_membership
    • True when the category_uid is the uid of the deepest category object set in the relation. It means it's true for the (self.uid, foo_base_category.uid, b.uid) tuple in the case described above, but false for the (self.uid, foo_base_category.uid, a.uid) tuple.

  • portal_ids
  • This table is used in ERP5/Tool/IdTool.py to record the last Id delivered by generateNewLengthId() method.

    • id_group
    • Text identifier of the id group (a generated id is unique among its group).

    • last_id
    • Last generated Id.

  • roles_and_users
  • This table is used to store the possible security roles combinations which have the View permission on an object (see catalog table). Multiple roles might have the View permission on an object, and there are as many lines with the same uid as there are roles in a given combination. The same role can also be found in multiple combinations, and so can be present multiple times in this table.

    • uid
    • This attribute is not a foreign key to catalog table.

    • allowedRolesAndUsers
    • This stores roles identifiers.

  • translation
  • This table stores translations of, among others, workflow states to allow translating them and doing searches in a given language.

    • language
    • Language code of the translated message. The format is the same as Localizer language definition displayed in brackets in the list on manage_languages ZMI page.

    • message_context
    • Describes what gets translated : portal_type, validation_state, ...

    • original_message
    • translated_message
  • record
    • uid
    • path
    • catalog
    • played
    • date

Tables related to activities

Those table store the pending activities.

  • message
  • message_queue

Related Articles