Requirements

You will need Zope 2.7, python 2.3 and MySQL 4 to use ZSQLCatalog.

Download and Install

Installing ZSQLCatalog with urpmi

Simply type on your MandrakeLinux:

      urpmi ZSQLCatalog

If you plan to use ZSQLCatalog in a Zope CMF, type:

      urpmi ERP5Catalog

Installing ZSQLCatalog from CVS

Go to your Zope Products directory and type:

      $ cvs -d :pserver:anonymous@cvs.erp5.org:/cvsroot login
      CVS password: [Hit enter]
      $ cvs -d :pserver:anonymous@cvs.erp5.org:/cvsroot co ZSQLCatalog

If you plan to use ZSQLCatalog in a Zope CMF, type:

      $ cvs -d :pserver:anonymous@cvs.erp5.org:/cvsroot login
      CVS password: [Hit enter]
      $ cvs -d :pserver:anonymous@cvs.erp5.org:/cvsroot co ERP5Catalog  

Installing ZSQLCatalog CVS snapshot

Download ZSQLCatalog CVS snapshot from here

Untar ZSQLCatalog in your Zope Products directory.

If you plan to use ERP5Catalog in a Zope CMF, also download ERP5Catalog snapshot from here

Untar ERP5Catalog in your Zope Products directory.

Configuration

The configuration example provided here is intended for use with a Zope CMF (1.3 or higher).

Step 1: Create an empty CMF

Go to the ZMI root and use the add a CMF Site named test.

Step 2: Delete default catalog

Go to the newly created CMF Site and delete portal_catalog tool.

Step 3: Create a mysql connectory

Add a valid Z MySQLDA to the root of the newly created CMF Site. This connect should poiunt to a valid database.

Step 4: Create an empty ERP5Catalog

With the ZMI, add an ERP5Catalog tool to the root of the newly created CMF Site.

Step 5: Populate the content ERP5Catalog with SQL methods

First add methods to create each table. At least 3 tables are required in a CMF: catalog, subject and roles_and_users. More tables can be added.

Here is the z_create_catalog method Z SQL Method:

      # Host:
      # Database: test
      # Table: 'catalog'
      #
      CREATE TABLE `catalog` (
        `uid` int(11) NOT NULL default '0',
        `path` varchar(255) NOT NULL default '',
        `relative_url` varchar(255) NOT NULL default '',
        `parent_uid` int(11) default '0',
        `CreationDate` datetime default '0000-00-00 00:00:00',
        `Creator` varchar(30) default '',
        `Date` datetime default '0000-00-00 00:00:00',
        `Description` text,
        `PrincipiaSearchSource` text,
        `SearchableText` text,
        `EffectiveDate` datetime default '0000-00-00 00:00:00',
        `ExpiresDate` datetime default '0000-00-00 00:00:00',
        `ModificationDate` datetime default '0000-00-00 00:00:00',
        `Title` varchar(255) default '',
        `Type` varchar(30) default '',
        `bobobase_modification_time` datetime default '0000-00-00 00:00:00',
        `created` datetime default '0000-00-00 00:00:00',
        `effective` datetime default '0000-00-00 00:00:00',
        `expires` datetime default '0000-00-00 00:00:00',
        `getIcon` varchar(30) default '',
        `id` varchar(30) default '',
        `in_reply_to` varchar(255) default '',
        `meta_type` varchar(30) default '',
        `portal_type` varchar(30) default '',
        `modified` datetime default '0000-00-00 00:00:00',
        `review_state` varchar(30) default '',
        `opportunity_state` varchar(30) default '',
        `default_source_reference` varchar(30) default '',
        `default_destination_reference` varchar(30) default '',
        `default_source_title` varchar(30) default '',
        `default_destination_title` varchar(30) default '',
        `default_source_section_title` varchar(30) default '',
        `default_destination_section_title` varchar(30) default '',
        `default_causality_id` varchar(30) default '',
        `location` varchar(30),
        `ean13_code` varchar(30),
        `validation_state` varchar(30) default '',
        `simulation_state` varchar(30) default '',
        `causality_state` varchar(30) default '',
        `discussion_state` varchar(30) default '',
        `invoice_state` varchar(30) default '',
        `payment_state` varchar(30) default '',
        `event_state` varchar(30) default '',
        `order_id` varchar(30) default '',
        `reference` varchar(30) default '',
        `source_reference` varchar(30) default '',
        `destination_reference` varchar(30) default '',
        `summary` text,
        PRIMARY KEY  (`uid`),
        KEY `Parent` (`parent_uid`),
        KEY `Path` (`path`),
        KEY `relative_url` (`relative_url`),
        KEY `Type` (`meta_type`,`portal_type`),
        KEY `Type1` (`portal_type`),
        KEY `Type2` (`meta_type`),
        KEY `Type3` (`Type`),
        KEY `Id` (`id`),
        KEY `review_state` (`review_state`),
        KEY `opportunity_state` (`opportunity_state`),
        KEY `validation_state` (`validation_state`),
        KEY `simulation_state` (`simulation_state`),
        KEY `causality_state` (`causality_state`),
        KEY `discussion_state` (`discussion_state`),
        KEY `invoice_state` (`invoice_state`),
        KEY `order_id` (`order_id`),
        KEY `payment_state` (`payment_state`),
        KEY `event_state` (`event_state`),
      ) TYPE=InnoDB;        

Now the z_create_roles_and_users Z SQL Method:

      CREATE TABLE roles_and_users (
        uid INT,
        allowedRolesAndUsers VARCHAR(40),
        KEY `uid` (`uid`),
        KEY `allowedRolesAndUsers` (`allowedRolesAndUsers`)
      ) TYPE=InnoDB;    

Now the z_create_subject Z SQL Method:

      CREATE TABLE subject (
        uid INT,
        subject VARCHAR(255),
        KEY `uid` (`uid`),
        KEY `allowedRolesAndUsers` (`Subject`)
      )TYPE=InnoDB;    

We can now add cataloging methods.

Performance

ZSQLCatalog is being used in production to index a ZODB database of more than 1,000,000 documents, with indexing speed close 30 documents / second.