"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
• Specialised tables
• Tables related to activities
• Related Articles

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¶