\documentclass[10pt,english]{article}
\usepackage{babel}
\usepackage{shortvrb}
\usepackage[latin1]{inputenc}
\usepackage{tabularx}
\usepackage{longtable}
\setlength{\extrarowheight}{2pt}
\usepackage{amsmath}
\usepackage{graphicx}
\usepackage{color}
\usepackage{multirow}
\usepackage[colorlinks=true,linkcolor=blue,urlcolor=blue]{hyperref}
\usepackage[a4paper,margin=2cm,nohead]{geometry}
%% generator Docutils: http://docutils.sourceforge.net/
\newlength{\admonitionwidth}
\setlength{\admonitionwidth}{0.9\textwidth}
\newlength{\docinfowidth}
\setlength{\docinfowidth}{0.9\textwidth}
\newcommand{\optionlistlabel}[1]{\bf #1 \hfill}
\newenvironment{optionlist}[1]
{\begin{list}{}
  {\setlength{\labelwidth}{#1}
   \setlength{\rightmargin}{1cm}
   \setlength{\leftmargin}{\rightmargin}
   \addtolength{\leftmargin}{\labelwidth}
   \addtolength{\leftmargin}{\labelsep}
   \renewcommand{\makelabel}{\optionlistlabel}}
}{\end{list}}
% begin: floats for footnotes tweaking.
\setlength{\floatsep}{0.5em}
\setlength{\textfloatsep}{\fill}
\addtolength{\textfloatsep}{3em}
\renewcommand{\textfraction}{0.5}
\renewcommand{\topfraction}{0.5}
\renewcommand{\bottomfraction}{0.5}
\setcounter{totalnumber}{50}
\setcounter{topnumber}{50}
\setcounter{bottomnumber}{50}
% end floats for footnotes
% some commands, that could be overwritten in the style file.
\newcommand{\rubric}[1]{\subsection*{~\hfill {\it #1} \hfill ~}}
% end of "some commands"
\title{HOWTO: Using Archetypes SQLStorage and Advanced Tips}
\author{}
\date{}
\hypersetup{
pdftitle={HOWTO: Using Archetypes SQLStorage and Advanced Tips},
pdfauthor={Joel Burton}
}
\raggedbottom
\begin{document}
\maketitle

%___________________________________________________________________________
\begin{center}
\begin{tabularx}{\docinfowidth}{lX}
\textbf{Author}: &
	Joel Burton \\
\textbf{Contact}: &
	\href{mailto:joel@joelburton.com}{joel@joelburton.com} \\
\textbf{Date}: &
	2003-09-02 \\
\textbf{Version}: &
	1.2 \\
\textbf{Web site}: &
	http://sourceforge.net/projects/archetypes \\
\textbf{Covers}: &
	Archetypes 1.0beta \\
\end{tabularx}
\end{center}


\begin{quote}

Where is the knowledge we have lost in information?

\begin{flushright}
---T. S. Eliot, \emph{The Rock}
\end{flushright}
\end{quote}
\hypertarget{contents}{}\subsection*{~\hfill Contents\hfill ~}
\pdfbookmark[0]{Contents}{contents}
\begin{list}{}{}
\item \href{#introduction}{Introduction}
\begin{list}{}{}
\item \href{#typographic-conventions}{Typographic conventions}

\item \href{#comparing-sqlstorage-to-other-relational-database-interface-strategies}{Comparing SQLStorage to other relational database interface strategies}
\begin{list}{}{}
\item \href{#versus-zsql-methods}{Versus ZSQL methods}

\item \href{#versus-ape-formerly-adaptable-storage}{Versus APE (formerly Adaptable Storage)}

\end{list}

\end{list}

\item \href{#creating-a-database-stored-object}{Creating a Database-Stored Object}

\item \href{#testing-our-new-object}{Testing Our New Object}

\item \href{#about-uids}{About UIDs}
\begin{list}{}{}
\item \href{#parent-uid}{Parent UID}

\end{list}

\item \href{#customers-and-orders}{Customers and Orders}
\begin{list}{}{}
\item \href{#creating-in-archetypes}{Creating in Archetypes}

\item \href{#testing-them-out}{Testing Them Out}

\end{list}

\item \href{#working-with-existing-table-structure}{Working With Existing Table Structure}
\begin{list}{}{}
\item \href{#if-you-need-a-very-different-table-structure}{If You Need A Very Different Table Structure}

\item \href{#using-traditional-referential-integrity-for-the-child-table}{Using Traditional Referential Integrity For the Child Table}

\item \href{#referential-integrity-prevention-of-deletions}{Referential Integrity {\&} Prevention of Deletions}
\begin{list}{}{}
\item \href{#cascading}{Cascading}

\end{list}

\end{list}

\item \href{#propagating-direct-db-changes-back-to-zodb}{Propagating Direct DB Changes Back to ZODB}
\begin{list}{}{}
\item \href{#forcing-catalog-reindexes-on-update}{Forcing Catalog Reindexes on Update}
\begin{list}{}{}
\item \href{#using-triggers-to-automate-this}{Using Triggers to Automate This}

\end{list}

\item \href{#syncronizing-deletes-and-inserts}{Syncronizing Deletes and Inserts}
\begin{list}{}{}
\item \href{#inserts}{Inserts}

\item \href{#deletes}{Deletes}

\item \href{#inserting-a-child-record}{Inserting a Child Record}

\end{list}

\end{list}

\item \href{#changing-sqlstorage-s-storage-methods-an-example-with-lists}{Changing SQLStorage's Storage Methods: An Example With Lists}
\begin{list}{}{}
\item \href{#creating-the-types-and-fixing-the-mapping}{Creating the Types and Fixing the Mapping}

\item \href{#fixing-the-translations}{Fixing the Translations}

\item \href{#even-better-turning-into-arrays}{Even Better: Turning Into Arrays}

\item \href{#turning-lists-into-related-child-records}{Turning lists into related child records}
\begin{list}{}{}
\item \href{#delete-rule-versus-referential-integrity}{Delete rule versus referential integrity}

\item \href{#maintaining-order}{Maintaining order}

\end{list}

\end{list}

\item \href{#creating-a-new-storage-class}{Creating a new storage class}
\begin{list}{}{}
\item \href{#creating-our-subclasses-type}{Creating our subclasses type}

\item \href{#improving-fixed-point-type-storage}{Improving fixed point type storage}

\item \href{#fixing-image-storage}{Fixing image storage}

\item \href{#dynamically-changing-storage-types}{Dynamically changing storage types}

\end{list}

\item \href{#integrating-relational-database-schemas-into-archetypes}{Integrating relational database schemas into Archetypes}

\item \href{#the-status-of-sqlstorage}{The status of SQLStorage}

\item \href{#about-this-document}{About this Document}

\end{list}



%___________________________________________________________________________

\hypertarget{introduction}{}
\section*{Introduction}
\pdfbookmark[0]{Introduction}{introduction}

The SQLStorage storage for \href{http://www.sf.net/projects/archetypes}{Archetypes} [\hyperlink{id37}{19}] allows you to transparently
store attributes of your Archetypes objects in an SQL-backed database.

This is useful in many situations, such as:
\begin{itemize}
\item 
You have other applications that need to simultaneous access data in
relational database format.

\item 
You have a lot of existing data in a relational database format.

\item 
You or your boss/client are more comfortable knowing that your data
is accessible in a relational database format.

\end{itemize}

This HOWTO assumes that you are comfortable installing Archetypes, and
using it with non-SQLStorage, and are comfortable administering
Plone/CMF/Zope sites.

This HOWTO explains how you can use relational database features like
triggers and rules to store your data in traditional relational
database parent/child tables, while still accessing it with
traditional Zope accessors.  While the advanced techniques in this
HOWTO can be used with any database that supports triggers and
updatable views, the example code is for \href{http://www.postgresql.org}{PostgreSQL} [\hyperlink{id35}{18}]. It should not be
difficult to translate these ideas to Oracle or other database.  At
the time of this writing, MySQL does not support triggers, views, or
rules, so these ideas could not easily be implemented in MySQL.


%___________________________________________________________________________

\hypertarget{typographic-conventions}{}
\subsection*{Typographic conventions}
\pdfbookmark[1]{Typographic conventions}{typographic-conventions}

In most relational databases, commands are case-insensitive: SELECT
is the same as select. To help you understand the commands, however,
I'll follow the traditional format of putting SQL commands in upper
case.

Database identifiers (field names, table names, etc.) may or may not
be case-sensitive, depending on your database. The examples here will
be using PostgreSQL, which is case-insensitive for identifiers. I'll
show table names with a leading capital letter, and field name in all
lower case. However, you can enter these any way you like.


%___________________________________________________________________________

\hypertarget{comparing-sqlstorage-to-other-relational-database-interface-strategies}{}
\subsection*{Comparing SQLStorage to other relational database interface strategies}
\pdfbookmark[1]{Comparing SQLStorage to other relational database interface strategies}{comparing-sqlstorage-to-other-relational-database-interface-strategies}

The scope of what Archetypes accomplishes is similar to, yet very
different from, other systems of connecting Zope to relational
databases.

Archetypes stores the objects in the ZODB as an archetype object,
having traditional Plone/CMF/Zope methods such as Title(),
absolute{\_}url(), etc. However, individual attributes (such as title,
author, body, etc.) can be looked up in and stored in the relational
database. Since objects are real Zope objects, they work naturally
with acquisition, catalogs, and other Zope technologies.  Since you
can choose which attributes to store in the relational database,
attributes that don't have a naturally tight fit with relational
database structures can be left in the ZODB, as can ones that might
easily fit in a relational database structure, but for which you have
no external relational database access requirements.


%___________________________________________________________________________

\hypertarget{versus-zsql-methods}{}
\subsubsection*{Versus ZSQL methods}
\pdfbookmark[2]{Versus ZSQL methods}{versus-zsql-methods}

A more traditional method of Zope/Relational Database connection has
been to store rows of information in a relational database, and create
ZSQL Methods to look up and display this information. With this
technique, you can associate a Python class with a relational database
row [\hyperlink{id2}{1}], but the objects aren't real persistent Zope objects, and
aren't found during catalog calls. This strategy requires customized
integration to work with key Plone technologies such as acquisition,
workflow, portal{\_}forms, etc.

While there are worthwhile Zope product to simplify some of the
details of traditional relational database storage and Zope (such as
\href{http://www.bluedynamics.org/products/znolk}{Znolk} [\hyperlink{id39}{20}], which auto-generates database forms and storage methods),
these still fall quite short of the interface simplication and power
that Archetypes delivers.

Traditional SQL Method strategies for using Zope with relational
databases are of most use when converting an existing site built using
other web technologies (such as PHP or Perl), and in which you already
have written the SQL statements for insterting, updating, deleting,
viewing, etc., all of your object types.
\begin{figure}[b]\hypertarget{id2}[1]
This technique goes by the colorful name \emph{pluggable brains}.
\end{figure}


%___________________________________________________________________________

\hypertarget{versus-ape-formerly-adaptable-storage}{}
\subsubsection*{Versus APE (formerly Adaptable Storage)}
\pdfbookmark[2]{Versus APE (formerly Adaptable Storage)}{versus-ape-formerly-adaptable-storage}

Shane Hathaway's product \href{http://hathaway.freezope.org/Software/Ape}{APE} [\hyperlink{id41}{21}] (formerly called Adaptable Storage)
allows you to store your Zope objects in different formats (such as in
standard filesystem objects or on a relational database). In this
case, segments of the ZODB space are ``mounted'' from results in a
relational database. This means the entire object is kept in the
relational database--all attributes, etc.  Deleting an object from the
relational database, adding it, or modifying it affects the the ZODB
instantly since the this part of the ZODB is just a mounted pointer
the relational database.

While APE is a technological accomplishment, and very useful for some
projects, it doesn't fit perfectly into an existing natural database
role. All ZODB objects are stored in a few very APE-oriented tables,
rather than being stored in customizable,
traditional-relational-database tables.

In addition, APE works by location, rather than by type (as Archetypes
does).  That is, \emph{everything} in the folder /foo is controlled
(mounted) by APE. If /foo contains all and only objects of a certain
portal{\_}type (like Customers) you could treat these tables as the
``customer relational database'', and work around the unusual
object-to-relational database table structure. However, if there are
different types stored in that directory, you end up with a mishmash
of different types of data stored in the same tables, and don't have
the straightforward setup of a ``customer'' table versus an ``orders''
table, etc.  [\hyperlink{id4}{2}] With Archetypes, each portal{\_}type maps to an
individual table, regardless of where it is stored.

Lastly, APE does not produce the integrated form
production/validation/editing systems that Archetypes does.
\begin{figure}[b]\hypertarget{id4}[2]
It seems as if APE supports custom object marshallers and
unmarshallers to create different table types for different object
types; however, at this point, I haven't been able to find working
examples of this that I could understand and apply.
\end{figure}


%___________________________________________________________________________

\hypertarget{creating-a-database-stored-object}{}
\section*{Creating a Database-Stored Object}
\pdfbookmark[0]{Creating a Database-Stored Object}{creating-a-database-stored-object}

Let's start with a simple Archetypes object, representing a Customer:
\begin{ttfamily}\begin{flushleft}
\mbox{{\#}~Customer.py}\\
\mbox{{\#}~Customer~portal~type~(non-SQL~storage)}\\
\mbox{}\\
\mbox{from~Products.Archetypes.public~import~*}\\
\mbox{from~Products.Archetypes.TemplateMixin~import~TemplateMixin}\\
\mbox{}\\
\mbox{schema~=~BaseSchema~+~Schema((}\\
\mbox{~~~~}\\
\mbox{~~~~TextField('body',}\\
\mbox{~~~~~~~~~~~~~~required=1,}\\
\mbox{~~~~~~~~~~~~~~primary=1,}\\
\mbox{~~~~~~~~~~~~~~searchable=1,}\\
\mbox{~~~~~~~~~~~~~~default{\_}output{\_}type='text/html',}\\
\mbox{~~~~~~~~~~~~~~allowable{\_}content{\_}types=('text/restructured',}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'text/plain',}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'text/html',}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'application/msword'),}\\
\mbox{~~~~~~~~~~~~~~widget=RichWidget,}\\
\mbox{~~~~~~~~~~~~~~),}\\
\mbox{~~~~}\\
\mbox{~~~~StringField("phone",}\\
\mbox{~~~~~~~~~~~~~~~~~index="FieldIndex",}\\
\mbox{~~~~~~~~~~~~~~~~~),}\\
\mbox{~~~~}\\
\mbox{~~~~))~+~TemplateMixin.schema}\\
\mbox{}\\
\mbox{class~Customer(TemplateMixin,~BaseContent):}\\
\mbox{~~~~"""Our~example~object"""}\\
\mbox{~~~~schema~=~schema}\\
\mbox{~~~~archetype{\_}name~=~"Customer"}\\
\mbox{~~~~actions~=~TemplateMixin.actions}\\
\mbox{~~~~~~~~~~~~~~~~~~}\\
\mbox{registerType(Customer)}
\end{flushleft}\end{ttfamily}

This object defines two custom fields, body and phone (plus all
the traditional metadata attributes that are brought in by
BaseSchema). This object would be stored entirely in the ZODB by
Archetypes; however, we can convert this to being stored in a
relational database by making just two simple changes to the object:
\begin{itemize}
\item 
Add an import to the beginning for the appropriate SQL database
storage method.

\item 
Add an attribute storage to the fields we want stored in the
database, and set these to our storage method.

\end{itemize}

Since we're using PostgreSQL in this example, we'll import the
PostgreSQL storage method.

Our new object then becomes:
\begin{ttfamily}\begin{flushleft}
\mbox{{\#}~CustomerSQL.py}\\
\mbox{{\#}~Customer~portal~type~(SQL~storage)}\\
\mbox{}\\
\mbox{from~Products.Archetypes.public~import~*}\\
\mbox{from~Products.Archetypes.TemplateMixin~import~TemplateMixin}\\
\mbox{from~Products.Archetypes.SQLStorage~import~PostgreSQLStorage}\\
\mbox{}\\
\mbox{schema~=~BaseSchema~+~Schema((}\\
\mbox{~~~~}\\
\mbox{~~~~TextField('body',}\\
\mbox{~~~~~~~~~~~~~~required=1,}\\
\mbox{~~~~~~~~~~~~~~primary=1,}\\
\mbox{~~~~~~~~~~~~~~searchable=1,}\\
\mbox{~~~~~~~~~~~~~~storage=PostgreSQLStorage(),}\\
\mbox{~~~~~~~~~~~~~~default{\_}output{\_}type='text/html',}\\
\mbox{~~~~~~~~~~~~~~allowable{\_}content{\_}types=('text/restructured',}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'text/plain',}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'text/html',}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'application/msword'),}\\
\mbox{~~~~~~~~~~~~~~widget=RichWidget,}\\
\mbox{~~~~~~~~~~~~~~),}\\
\mbox{~~~~}\\
\mbox{~~~~StringField("phone",}\\
\mbox{~~~~~~~~~~~~~~~~storage=PostgreSQLStorage(),}\\
\mbox{~~~~~~~~~~~~~~~~index="FieldIndex",}\\
\mbox{~~~~~~~~~~~~~~~~),}\\
\mbox{~~~~}\\
\mbox{~~~~))~+~TemplateMixin.schema}\\
\mbox{}\\
\mbox{class~CustomerSQL(TemplateMixin,~BaseContent):}\\
\mbox{~~~~"""Our~example~object"""}\\
\mbox{~~~~schema~=~schema}\\
\mbox{~~~~archetype{\_}name~=~"Customer~SQL"}\\
\mbox{~~~~actions~=~TemplateMixin.actions}\\
\mbox{~~~~~~~~~~~~~~~~~~}\\
\mbox{registerType(CustomerSQL)}
\end{flushleft}\end{ttfamily}

At this point, you should install our new Archetypes type and register
it with portal{\_}types.

Now, before we can begin using this object, we must do two things:
\begin{itemize}
\item 
Add a database connector (in our case, PostgreSQL) to our site. We
can use any PostgreSQL adapter; however, I've used \href{http://initd.org/software/psycopg}{ZPyscopgDA} [\hyperlink{id43}{22}] for
testing this, as this appears to be the best maintained of the
noncommercial adapters.

\end{itemize}
\begin{itemize}
\item 
In the archetype{\_}tool, under the Connections tab, we need to set
our database connector for this type of object to our new database
connector.  Note that in this tab, we have a default connection,
and we can override this for an portal{\_}type that uses SQLStorage. In
our case, you can either set the default to the new connection, or
the specific connection for our CustomerSQL type. However, since
we'll be adding several other Archetypes types, it will be easier to
point the default setup to your database adapter connection.

\end{itemize}

Before you go any further, make sure that the user you defined in your
database connection has the ability to create tables, and insert,
update, and delete from tables in your database. [\hyperlink{id6}{3}]
\begin{figure}[b]\hypertarget{id6}[3]
In real life, it isn't neccessary that the user have to have
CREATE TABLE privileges, as you can create the table yourself
before Archetypes tries to. However, it's easiest to let Archetypes
create the table for you. In which case a good strategy is to grant
CREATE TABLE permissions for the zope database connector user,
let it create the table, then rescind that right (for security's
sake) once the table has been created.
\end{figure}


%___________________________________________________________________________

\hypertarget{testing-our-new-object}{}
\section*{Testing Our New Object}
\pdfbookmark[0]{Testing Our New Object}{testing-our-new-object}

Now, we can add an instance of our object through the standard Plone
interface. Plone will recommend a unique ID; let's change that to
``new{\_}example''. Put in values for body and phone. Notice that you
can see these values in the view view, and can re-edit them in the
edit view.

Switch to your database monitor (for PostgreSQL, this is psql) and
examine the database:
\begin{ttfamily}\begin{flushleft}
\mbox{database={\#}~\textbf{{\textbackslash}d}}\\
\mbox{~~~~~~~~~~~~~~~~~~List~of~relations}\\
\mbox{~Schema~|~~~~~~~~~~~Name~~~~~~~~~~~|~~~Type~~~|~Owner}\\
\mbox{--------+--------------------------+----------+-------}\\
\mbox{~public~|~customersql~~~~~~~~~~~~~~|~table~~~~|~joel}
\end{flushleft}\end{ttfamily}

Archetypes has created our table for us. Examine the table:
\begin{ttfamily}\begin{flushleft}
\mbox{database={\#}~\textbf{{\textbackslash}d~customersql}}\\
\mbox{~~Table~"public.customersql"}\\
\mbox{~~Column~~~|~Type~|~Modifiers}\\
\mbox{-----------+------+-----------}\\
\mbox{~uid~~~~~~~|~text~|~not~null}\\
\mbox{~parentuid~|~text~|}\\
\mbox{~body~~~~~~|~\emph{text}~|}\\
\mbox{~phone~~~~~|~\emph{text}~|}\\
\mbox{Indexes:~customersql{\_}pkey~primary~key~btree~(uid)}
\end{flushleft}\end{ttfamily}

Notice that Archetypes has created our body field as text field and
the phone field as a text field. These transformations are part of
the PostgreSQLStorage method, and can be easily changed in the source,
should your needs require different mappings. [\hyperlink{id8}{4}] We'll look at
changing those mappings later in this document, in \href{#changing-sqlstorage-s-storage-methods-an-example-with-lists}{Changing
SQLStorage's Storage Methods: An Example With Lists}.

Also, notice that there are two new fields created:
\begin{itemize}
\item 
\textbf{UID} (uid): this is a unique identifier for your object

\item 
\textbf{Parent UID} (parentuid): this is the unique identifier (if any)
for the parent (enclosing) container for your object.

\end{itemize}
\begin{figure}[b]\hypertarget{id8}[4]
Or you can create the table in advanced of Archetypes, and
choose whatever field types you want, as long as your database can
cast Archetypes values into your field types. For instance, in our
example, though text is an acceptable choice for phone numbers,
we might prefer to have this stored as a varchar(20).  We could
have created the table ourselves and made this choice; when
Archetypes goes to insert a text-type value into phone,
PostgreSQL can cast this to varchar(20)
\end{figure}


%___________________________________________________________________________

\hypertarget{about-uids}{}
\section*{About UIDs}
\pdfbookmark[0]{About UIDs}{about-uids}

One of the smartest things about Archetypes is that it introduces the
ideas of unique identifiers into CMF sites. Zope IDs must be unique
within a folder, but need not be unique across a site. Therefore,
keeping track of the fact that you have an object called Eliot isn't
useful, since you may have several objects called that in different
folders.

A common workaround has been to refer to objects by their path (eg,
/animals/cats/Eliot), but this is fragile, since any change to the
object ID, or the IDs of any of the parent objects will change the
path and break these references.

Archetypes assigns each object a unique ID at creation [\hyperlink{id10}{5}], and then
maintains a mapping of that unique ID to the current location of the
object in the ZODB.  If the object is deleted, Archetypes will remove
it from its UID mapping.
\begin{figure}[b]\hypertarget{id10}[5]
The IDs that are created are in the Plone default style, e.g.,
PortalType.2003-07-23.4911
\end{figure}

Please note the difference between the Zope ID (the standard name for
the object returned by getId()) and the Archetypes UID. When our
object was created, Plone assigned it an ID like
CustomerSQL.2003-07-23.4911. Archetypes used this ID as its UID.
Even though we may change the object \emph{ID} to new{\_}example, it will
keep its \emph{UID} for the lifetime of the object. The UID should be
treated as an immutable attribute.

Archetypes also creates a portal{\_}catalog index for the UID field, so
you can easily query the catalog using the UID. It also exposes
several methods in its API for finding an object by its UID (from
ArchetypeTool.py):
\begin{ttfamily}\begin{flushleft}
\mbox{{\#}{\#}~Reference~Engine~Support}\\
\mbox{def~lookupObject(self,~uid):}\\
\mbox{~~~~if~not~uid:}\\
\mbox{~~~~~~~~return~None}\\
\mbox{~~~~object~=~None}\\
\mbox{~~~~catalog~=~getToolByName(self,~'portal{\_}catalog')}\\
\mbox{~~~~result~~=~catalog({\{}'UID'~:~uid{\}})}\\
\mbox{~~~~if~result:}\\
\mbox{~~~~~~~~{\#}This~is~an~awful~workaround~for~the~UID~under~containment}\\
\mbox{~~~~~~~~{\#}problem.~NonRefs~will~aq~there~parents~UID~which~is~so}\\
\mbox{~~~~~~~~{\#}awful~I~am~having~trouble~putting~it~into~words.}\\
\mbox{~~~~~~~~for~object~in~result:}\\
\mbox{~~~~~~~~~~~~o~=~object.getObject()}\\
\mbox{~~~~~~~~~~~~if~o~is~not~None:}\\
\mbox{~~~~~~~~~~~~~~~~if~IReferenceable.isImplementedBy(o):}\\
\mbox{~~~~~~~~~~~~~~~~~~~~return~o}\\
\mbox{~~~~return~None}\\
\mbox{}\\
\mbox{def~getObject(self,~uid):}\\
\mbox{~~~~return~self.lookupObject(uid)}\\
\mbox{}\\
\mbox{def~reference{\_}url(self,~object):}\\
\mbox{~~~~"""Return~a~link~to~the~object~by~reference"""}\\
\mbox{~~~~uid~=~object.UID()}\\
\mbox{~~~~return~"{\%}s/lookupObject?uid={\%}s"~{\%}~(self.absolute{\_}url(),~uid)}
\end{flushleft}\end{ttfamily}

We can use the method lookupObject(uid) to get the actual object by
UID, or use reference{\_}url(object) to generate a ``safe'' URL to an
object that will always find it given its UID.

You can see the list of currently-tracked UIDs and actual objects in
the archetype{\_}tool, UID tab.


%___________________________________________________________________________

\hypertarget{parent-uid}{}
\subsection*{Parent UID}
\pdfbookmark[1]{Parent UID}{parent-uid}

The Parent UID field created in our table is the UID of the container,
if it is an Archetypes object (or some other kind of future object
that might expose a UID).

This is \textbf{very} helpful for creating a simple parent/child
relationship in Plone, as we'll see in the next section.


%___________________________________________________________________________

\hypertarget{customers-and-orders}{}
\section*{Customers and Orders}
\pdfbookmark[0]{Customers and Orders}{customers-and-orders}

For example, a common database example is a database of customers and
orders, where one customer can have several orders. Pseudo-SQL for
this would be:
\begin{ttfamily}\begin{flushleft}
\mbox{CREATE~TABLE~Customer}\\
\mbox{~~(~custid~SERIAL~NOT~NULL~PRIMARY~KEY}\\
\mbox{~~,~custname~TEXT}\\
\mbox{~~...~other~customer~fields~...}\\
\mbox{~~);}\\
\mbox{}\\
\mbox{CREATE~TABLE~Orders}\\
\mbox{~~(~orderid~SERIAL~NOT~NULL~PRIMARY~KEY}\\
\mbox{~~,~custid~INT~REFERENCES~Customer}\\
\mbox{~~...~other~order~fields~...}\\
\mbox{~~);}
\end{flushleft}\end{ttfamily}

The field custid in the orders table is a reference (called a
foreign key) to the field custid in the customer table.

To create a similar structure in Archetypes, we need to create just
two types: CustomerFolder and Orders.  Objects of both of these
types will get UIDs from Archetypes. But if we change our Customer
type to become folderish (ie, derived from Archetypes's BaseFolder
rather than BaseContent), it can contain objects, and we can add
Orders objects inside of it. These Orders objects will have their
Parent UID field set to the CustomerFolder UID, giving us an easy
way to write ZCatalog queries for all orders with a certain customer
UID, or SQL queries asking the same thing.
\begin{center}\begin{sffamily}
\fbox{\colorbox[gray]{0.80}{\parbox{\admonitionwidth}{
\textbf{\large Why \emph{Orders} Rather Than \emph{Order?}}
\smallskip

\emph{Order} is a reserved word in most relational databases (part of the
clause ORDER BY ...) and therefore, many databases can't use it as
a table name or field name without quoting it. At this time,
Archetypes does not properly quote table and field names, and
therefore, we can't use reserved words.

Unless this is changed in Archetypes, when designing your schema, it
would be wise to look at your database's list of reserved words and
avoid these.
}}}
\end{sffamily}
\end{center}


%___________________________________________________________________________

\hypertarget{creating-in-archetypes}{}
\subsection*{Creating in Archetypes}
\pdfbookmark[1]{Creating in Archetypes}{creating-in-archetypes}

Let's create these two new archetypes. First, the CustomerFolder. This
will be exactly the same as CustomerSQL, except using BaseFolder
rather than BaseContent:
\begin{ttfamily}\begin{flushleft}
\mbox{{\#}~CustomerFolder.py}\\
\mbox{{\#}~Customer~portal~type~(SQL~storage,~folderish)}\\
\mbox{}\\
\mbox{from~Products.Archetypes.public~import~*}\\
\mbox{from~Products.Archetypes.TemplateMixin~import~TemplateMixin}\\
\mbox{from~Products.Archetypes.SQLStorage~import~PostgreSQLStorage}\\
\mbox{}\\
\mbox{schema~=~BaseSchema~+~Schema((}\\
\mbox{~~~~}\\
\mbox{~~~~TextField('body',}\\
\mbox{~~~~~~~~~~~~~~required=1,}\\
\mbox{~~~~~~~~~~~~~~primary=1,}\\
\mbox{~~~~~~~~~~~~~~searchable=1,}\\
\mbox{~~~~~~~~~~~~~~storage=PostgreSQLStorage(),}\\
\mbox{~~~~~~~~~~~~~~default{\_}output{\_}type='text/html',}\\
\mbox{~~~~~~~~~~~~~~allowable{\_}content{\_}types=('text/restructured',}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'text/plain',}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'text/html',}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'application/msword'),}\\
\mbox{~~~~~~~~~~~~~~widget=RichWidget,}\\
\mbox{~~~~~~~~~~~~~~),}\\
\mbox{~~~~}\\
\mbox{~~~~StringField("phone",}\\
\mbox{~~~~~~~~~~~~~~~~storage=PostgreSQLStorage(),}\\
\mbox{~~~~~~~~~~~~~~~~index="FieldIndex",}\\
\mbox{~~~~~~~~~~~~~~~~),}\\
\mbox{~~~~}\\
\mbox{~~~~))~+~TemplateMixin.schema}\\
\mbox{}\\
\mbox{class~CustomerFolder(TemplateMixin,~BaseFolder):}\\
\mbox{~~~~"""Our~example~object"""}\\
\mbox{~~~~schema~=~schema}\\
\mbox{~~~~archetype{\_}name~=~"Customer~Folder"}\\
\mbox{~~~~actions~=~TemplateMixin.actions}\\
\mbox{~~~~~~~~~~~~~~~~~~}\\
\mbox{registerType(CustomerFolder)}
\end{flushleft}\end{ttfamily}

Our Order type is straightforward. It will include the cost of an
order, and shipping details:
\begin{ttfamily}\begin{flushleft}
\mbox{{\#}~Orders.py}\\
\mbox{}\\
\mbox{from~Products.Archetypes.public~import~*}\\
\mbox{from~Products.Archetypes.TemplateMixin~import~TemplateMixin}\\
\mbox{from~Products.Archetypes.SQLStorage~import~PostgreSQLStorage}\\
\mbox{}\\
\mbox{schema~=~BaseSchema~+~Schema((}\\
\mbox{~~~~}\\
\mbox{~~~~TextField('shipping{\_}details',}\\
\mbox{~~~~~~~~~~~~~~required=1,}\\
\mbox{~~~~~~~~~~~~~~storage=PostgreSQLStorage()),}\\
\mbox{~~~~FixedPointField('total{\_}cost',}\\
\mbox{~~~~~~~~~~~~~~~~~~~~storage=PostgreSQLStorage())}\\
\mbox{~~~~}\\
\mbox{~~~~))~+~TemplateMixin.schema}\\
\mbox{}\\
\mbox{class~Orders(TemplateMixin,~BaseContent):}\\
\mbox{~~~~"""Our~example~object"""}\\
\mbox{~~~~schema~=~schema}\\
\mbox{~~~~archetype{\_}name~=~"Orders"}\\
\mbox{~~~~actions~=~TemplateMixin.actions}\\
\mbox{~~~~~~~~~~~~~~~~~~}\\
\mbox{registerType(Orders)}
\end{flushleft}\end{ttfamily}


%___________________________________________________________________________

\hypertarget{testing-them-out}{}
\subsection*{Testing Them Out}
\pdfbookmark[1]{Testing Them Out}{testing-them-out}

Register these two new types with portal{\_}types and add a
CustomerFolder object. You should be able to edit this data and see
the resulting information in the table customerfolder without a
problem.

As of the writing of this HOWTO, Archetypes does not expose a ``folder
contents'' tab for folderish objects like our CustomerFolder.
However, you can go to this view manually by visiting the new customer
folder object, and changing the end of the URL to point to
folder{\_}contents. [\hyperlink{id12}{6}]
\begin{figure}[b]\hypertarget{id12}[6]
And you can add this action to the type so that it
automatically shows as a tab up by adding it to the actions for
CustomerFolder in portal{\_}type.
\end{figure}

Inside of the new customer folder, add an Orders object and enter
details.  Then, examine the orders table in the database:
\begin{ttfamily}\begin{flushleft}
\mbox{database={\#}~\textbf{SELECT~*~FROM~Orders;}}\\
\mbox{~~~~~~~~~~uid~~~~~~~~~~~|~~~~~~~~~~~parentuid~~~~~~~~~~~~|~shipping{\_}details~|~total{\_}cost}\\
\mbox{------------------------+--------------------------------+------------------+------------}\\
\mbox{~Orders.2003-07-23.4935~|~CustomerFolder.2003-07-23.4609~|~Shipping~~~~~~~~~|~~~~~~~~~~0}\\
\mbox{(1~rows)}
\end{flushleft}\end{ttfamily}

Notice how we get the parentuid value correctly. From our relational
database, we could write a traditional query now on customers and the
total of the orders as:
\begin{ttfamily}\begin{flushleft}
\mbox{database={\#}~SELECT~C.uid,~C.phone,~SUM(O.total{\_}cost)}\\
\mbox{~~~~~~~~~~~~~FROM~CustomerFolder~as~C}\\
\mbox{~~~~~~~~~~~~~~~INNER~JOIN~Orders~as~O~on~(O.parentuid~=~C.uid)}\\
\mbox{~~~~~~~~~~~~~GROUP~BY~C.uid,~C.phone;}
\end{flushleft}\end{ttfamily}


%___________________________________________________________________________

\hypertarget{working-with-existing-table-structure}{}
\section*{Working With Existing Table Structure}
\pdfbookmark[0]{Working With Existing Table Structure}{working-with-existing-table-structure}

Of course, if you're working with existing tables, or if you want to
work with other SQL tools, chances are you want to use a more
traditional primary key/foreign key setup than the Archetypes UID.
Many databases use a serial column [\hyperlink{id14}{7}] (integers that increase for
each new record) as a primary key.
\begin{figure}[b]\hypertarget{id14}[7]
MySQL uses an integer columns with the autoincrement feature.
\end{figure}

To do this with Archetypes, you can simply either:
\begin{itemize}
\item 
create the table before you insert the first Archetypes record

\end{itemize}

or
\begin{itemize}
\item 
modify the table after Archetypes creates it and starts using it.

\end{itemize}

For example, our customerfolder table was created automatically by
Archetypes, and it contains a UID field, but not a traditional,
numeric primary key. We can fix this by adding this:
\begin{ttfamily}\begin{flushleft}
\mbox{ALTER~TABLE~Customerfolder~ADD~customerid~INT;}\\
\mbox{}\\
\mbox{CREATE~SEQUENCE~customerfolder{\_}customerid{\_}seq;}\\
\mbox{}\\
\mbox{UPDATE~Customerfolder~SET~customerid~=~nextval('customerfolder{\_}customerid{\_}seq');}\\
\mbox{}\\
\mbox{ALTER~TABLE~Customerfolder~ALTER~customerid}\\
\mbox{~~SET~DEFAULT~nextval('customerfolder{\_}customerid{\_}seq');}\\
\mbox{}\\
\mbox{ALTER~TABLE~Customerfolder~ALTER~customerid~SET~NOT~NULL;}\\
\mbox{}\\
\mbox{ALTER~TABLE~Customerfolder~DROP~CONSTRAINT~customerfolder{\_}pkey;}\\
\mbox{}\\
\mbox{ALTER~TABLE~Customerfolder~ADD~PRIMARY~KEY~(~customerid~);}\\
\mbox{}\\
\mbox{ALTER~TABLE~Customerfolder~ADD~UNIQUE~(~uid~);}
\end{flushleft}\end{ttfamily}

Note that syntax for altering tables, adding primary keys, etc.,
varies considerably from one relational database to another, so if
you're not using PostgreSQL, you'll want to research how to do this
with your relational database. Also note that it's rather wordy to
make this changes, whereas having the table setup properly in the
first place is much more succinct:
\begin{ttfamily}\begin{flushleft}
\mbox{CREATE~TABLE~Customerfolder~(~customerid~SERIAL~NOT~NULL~PRIMARY~KEY,}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~...}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~)}
\end{flushleft}\end{ttfamily}

So it may often be to your advantage to create the table before
Archetypes.

Now we have a traditional primary key that is automatically increased,
but since its not part of Archetypes's schema, it will leave it alone.
\begin{center}\begin{sffamily}
\fbox{\parbox{\admonitionwidth}{
\textbf{\large Important}
\vspace{2mm}

Notice that we make the UID field UNIQUE. This guarantees that two
records cannot have the same UID. Even though we're no longer using
the Archetypes UID as our primary key, it is still critical to keep
this field unique.

When Archetypes edits an object, it doesn't know if the object
exists in the relational database yet or not. Therefore, it tries to
insert a record for this object. If this fails, it then updates the
existing record.

This behavior may change in future versions of Archetypes, but,
unless it does, you must make sure UID stays unique or else you'll
have multiple copies of your objects' data in the relational
database, only one of which will be correct.
}}
\end{sffamily}
\end{center}


%___________________________________________________________________________

\hypertarget{if-you-need-a-very-different-table-structure}{}
\subsection*{If You Need A Very Different Table Structure}
\pdfbookmark[1]{If You Need A Very Different Table Structure}{if-you-need-a-very-different-table-structure}

Instead of having Archetypes write to the real table, we can have
Archetypes insert to a view of the table. Such a view can have
fields that looks like those that Archetypes expects, but actually
insert the information in different places and different ways.

This is especially useful if you have existing relational database
tables that have non-Zope-like fields, names, etc.

To do this, let's first move the real table out of the way:
\begin{ttfamily}\begin{flushleft}
\mbox{ALTER~TABLE~customerfolder~RENAME~TO~customerfolder{\_}table;}
\end{flushleft}\end{ttfamily}

This is because Archetypes expects to work with customerfolder, and
we want that to be our view. The actual table name doesn't have to be
customerfolder{\_}table; it can be whatever we want it to be.

Now, let's create our view:
\begin{ttfamily}\begin{flushleft}
\mbox{CREATE~VIEW~customerfolder~AS}\\
\mbox{~~SELECT~uid,~parentuid,~body,~phone}\\
\mbox{~~~~FROM~customerfolder{\_}table;}
\end{flushleft}\end{ttfamily}

Now, we'll make this view updatable so that new records can be
inserted into it. The syntax for this is very relational
database-specific; you'll need to change this for other database
systems. Following is our PostgreSQL syntax:
\begin{ttfamily}\begin{flushleft}
\mbox{CREATE~RULE~customerfolder{\_}ins~AS}\\
\mbox{~~ON~UPDATE~TO~customerfolder~DO~INSTEAD~(}\\
\mbox{~~~~INSERT~INTO~customerfolder{\_}table~(~uid,~parentuid,~body,~phone~)}\\
\mbox{~~~~~~VALUES~(~NEW.uid,~NEW.parentuid,~NEW.body,~NEW.phone~);~);}
\end{flushleft}\end{ttfamily}

Now, Archetypes can insert to customerfolder, assuming that it is a
table, when in fact, we're \emph{rewriting} its work to write to the real
table.

So that Archetypes can do updates and deletes, we'll need to add rules
for that, too:
\begin{ttfamily}\begin{flushleft}
\mbox{CREATE~RULE~customerfolder{\_}del~AS}\\
\mbox{~~ON~DELETE~TO~customerfolder~DO~INSTEAD}\\
\mbox{~~~~DELETE~FROM~customerfolder{\_}table~WHERE~uid=OLD.uid;}\\
\mbox{}\\
\mbox{CREATE~RULE~customerfolder{\_}upd~AS}\\
\mbox{~~ON~UPDATE~TO~customerfolder~DO~INSTEAD}\\
\mbox{~~~~UPDATE~customerfolder{\_}table}\\
\mbox{~~~~~~SET~parentuid~=~NEW.parentuid}\\
\mbox{~~~~~~~~,~body~=~NEW.body}\\
\mbox{~~~~~~~~,~phone~=~NEW.phone;}
\end{flushleft}\end{ttfamily}

In this example, our real table and view are only slightly different,
but this strategy is helpful when dealing with existing tables that
have many fields not of interest to Archetypes, or when our relational
database tables have a different type of structure than is natural to
Archetypes. We'll see advanced uses of this later.

FIXME: Show It Working


%___________________________________________________________________________

\hypertarget{using-traditional-referential-integrity-for-the-child-table}{}
\subsection*{Using Traditional Referential Integrity For the Child Table}
\pdfbookmark[1]{Using Traditional Referential Integrity For the Child Table}{using-traditional-referential-integrity-for-the-child-table}

For our orders table, we can do the same thing to give that a
serial-type primary key that is more traditinal for a relational
database. In addition, though, it's likely that we want to child
orders table to relate to the parent customerfolder table by the new
customerid rather than the Archetypes-oriented Parent UID.

To do this, let's create a customerid field to the order table:
\begin{ttfamily}\begin{flushleft}
\mbox{ALTER~TABLE~Orders~ADD~customerid~INT;}\\
\mbox{}\\
\mbox{UPDATE~orders~}\\
\mbox{~~SET~customerid~=~Customerfolder.customerid}\\
\mbox{~~FROM~Customerfolder}\\
\mbox{~~WHERE~Orders.parentuid~=~Customerfolder.uid;}\\
\mbox{}\\
\mbox{ALTER~TABLE~Orders~ALTER~customerid~SET~NOT~NULL;}\\
\mbox{}\\
\mbox{ALTER~TABLE~Orders~ADD~FOREIGN~KEY~(customerid)~}\\
\mbox{~~REFERENCES~Customerfolder;}
\end{flushleft}\end{ttfamily}

Now we have a traditional primary key/foreign key relationship between
our tables. If we have a orders record for customer {\#}1, we won't be
able to delete this customer until we delete these orders.

We need to set it up so that when we add an order via Plone, we look
up the customerid from the customerfolder table and set it in the
orders table for the new record.

To do this, we'll add a trigger that, before completing an insert on
the order table, figures out the customerid and makes that part of
the insert.

Different database implement triggers in different ways. In
PostgreSQL, a trigger statement is a simple statement that calls a
function. This function can reference and change a record structure
called new which reflects the new record being inserted (or for an
update, the new record to be written). Functions in PostgreSQL can be
written in different languages, including Python; for our example,
however, we'll use PostgreSQL's built-in PL/PgSQL language, a
PL/SQL-like language that is simple to write and understand.

Before you can write PL/PgSQL functions, you must enable this by
adding this language to your database. From the shell:
\begin{ttfamily}\begin{flushleft}
\mbox{{\$}~\textbf{createlang~plpgsql~your{\_}db{\_}name}}
\end{flushleft}\end{ttfamily}

Our trigger function will be:
\begin{ttfamily}\begin{flushleft}
\mbox{CREATE~OR~REPLACE~FUNCTION~order{\_}ins~()~RETURNS~TRIGGER~AS~'}\\
\mbox{~~BEGIN}\\
\mbox{~~NEW.customerid~:=~customerid~}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~FROM~customerfolder~AS~C}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~WHERE~NEW.parentuid~=~C.uid;}\\
\mbox{~~RETURN~NEW;}\\
\mbox{~~END;}\\
\mbox{'~LANGUAGE~plpgsql;}
\end{flushleft}\end{ttfamily}

Now, let's create the trigger:
\begin{ttfamily}\begin{flushleft}
\mbox{CREATE~trigger~order{\_}ins{\_}trig~BEFORE~INSERT~ON~Orders}\\
\mbox{~~FOR~EACH~ROW~EXECUTE~order{\_}ins();}
\end{flushleft}\end{ttfamily}

Our real test is whether this works in Plone, but for a Q{\&}D
simulation, we'll test this in the SQL monitor by manually inserting a
child orders record and seeing if it gets the parent UID (for your
tests, use the real UID of one of your CustomerFolder objects):
\begin{ttfamily}\begin{flushleft}
\mbox{database={\#}~\textbf{insert~into~orders~(uid,parentuid)}}\\
\mbox{~~~~~~~~~~~~~\textbf{values~('test',~'CustomerFolder.2003-07-23.4609');}}\\
\mbox{INSERT~35162~1}\\
\mbox{database={\#}~\textbf{select~uid,~parentuid,~customerid~from~orders;}}\\
\mbox{~~~~~~~~~~uid~~~~~~~~~~~|~~~~~~~~~~~parentuid~~~~~~~~~~~~|~customerid}\\
\mbox{------------------------+--------------------------------+------------}\\
\mbox{~Orders.2003-07-23.4935~|~CustomerFolder.2003-07-23.4609~|~~~~~~~~~~1}\\
\mbox{~test~~~~~~~~~~~~~~~~~~~|~CustomerFolder.2003-07-23.4609~|~~~~~~~~~~1}\\
\mbox{(2~rows)}
\end{flushleft}\end{ttfamily}

In the above output, the second record is our newly inserted record,
and it did get the correct customerid field.


%___________________________________________________________________________

\hypertarget{referential-integrity-prevention-of-deletions}{}
\subsection*{Referential Integrity {\&} Prevention of Deletions}
\pdfbookmark[1]{Referential Integrity & Prevention of Deletions}{referential-integrity-prevention-of-deletions}

Now our traditional referenial integrity is set up. If we try to
delete a customer that has related orders, we'll get the error that we
expect and want:
\begin{ttfamily}\begin{flushleft}
\mbox{database={\#}~\textbf{DELETE~FROM~Customerfolder;}}\\
\mbox{ERROR:~~{\$}1~referential~integrity~violation~-~key~in~customerfolder~still~referenced~from~orders}
\end{flushleft}\end{ttfamily}

However, we can still have problems in Plone.

Our current example has the child order objects nested inside of the
parent customer objects, so it's not possible to delete a customer
without deleting the orders because the customer itself is a folderish
object, so the orders would be deleted automatically.

However, this may not always be the setup. Sometimes, you won't be
able to have a child object contained physically in the parent object,
and you'll connect things using attributes yourself.

For example, we might want to keep track of which staff member handles
this customer. We could do this by nesting the CustomerFolder
objects inside a Staff object, but this might, for different
reasons, not be possible or preferable. Instead, we would create a
staffuid attribute on the CustomerFolder type, and populate this
with the UID of the staff member.

In cases like this, if you have the referential integrity in the
database connected properly, you won't be able to delete the staff
record if related customers exist, but you will be able to delete the
customer \emph{object} in the ZODB without problems--stranding the data in
the relational database and ruining your database connections.

This is because the current version of Archetypes doesn't deal
properly with deletion exceptions. Archetypes issues an SQL delete on
the staff record, but since there are related children, it fails. This
raises an exception, but Zope only stops a deletion by raising and
propagating a particular exception--others just get logged and
ignored. Therefore, the database record can't be deleted (your
database will refuse to do this, regardless of how Zope asks), but the
pointer to it in the ZODB will be deleted. So the staff member won't
be visible on the site, but the data will stay in the relational
database.

To fix this, apply the patch FIXME included with this howto. This
raises the proper exception (BeforeDeleteException) if the SQL
deletion call fails, which causes the Plone object deletion to fail.
Unfortunately, you'll get an standard error message, rather than a
polite, user-friendly explanation, but this is better than silently
ignoring the database failure and moving on. [\hyperlink{id16}{8}]

This patch was developed for the current version of Archetypes. This
fix may be included by the time you read this HOWTO. If so, please let
me know, and I'll update this section.
\begin{figure}[b]\hypertarget{id16}[8]
If you want to make a nicer deletion error message, you could
modify the standard{\_}error{\_}message method.
\end{figure}


%___________________________________________________________________________

\hypertarget{cascading}{}
\subsubsection*{Cascading}
\pdfbookmark[2]{Cascading}{cascading}

PostgreSQL and most other databases that support referential integrity
can handle deletion of parent records in other ways. The default is to
block the deletion of parent with related children, but you can also
opt to automatically delete the children when a related parent is
deleted.

This option is called ``cascading'' a deletion. To set this up, we'd
create our child table differently:
\begin{ttfamily}\begin{flushleft}
\mbox{CREATE~TABLE~Child~(}\\
\mbox{~~childid~SERIAL~NOT~NULL~PRIMARY~KEY,}\\
\mbox{~~parentid~INT~NOT~NULL~REFERENCES~Parent~\emph{ON~DELETE~CASCADE}}\\
\mbox{~~...}\\
\mbox{);}
\end{flushleft}\end{ttfamily}

Now, when the parent is deleted in the database, it will delete the
related child records rather than raising an exception.

Of course, this won't automatically delete the Zope ZODB objects for
the children, but the next section of this tutorial deals with the
question of how to have operations in the database ``notify'' Zope of
changes to make in the ZODB. Using techniques explained there, we'll
be able to have the child ZODB deleted for us.


%___________________________________________________________________________

\hypertarget{propagating-direct-db-changes-back-to-zodb}{}
\section*{Propagating Direct DB Changes Back to ZODB}
\pdfbookmark[0]{Propagating Direct DB Changes Back to ZODB}{propagating-direct-db-changes-back-to-zodb}

Sometimes in Zope projects, the changes all come from the Zope
interface, and the relational DB storage is just to soothe
ZODB-nervous customers, or to allow reporting from standard SQL tools.
In this case, the setup we have would be acceptable.

In cases where changes must propigate to Zope, here are some problems
we need to solve:
\begin{itemize}
\item 
Records that are inserted directly into the database are never
visible to Zope, as ZODB objects aren't instantiated for these
records.

\item 
Records that are deleted directly in the database are never deleted
from Zope. Therefore, objects will remain in the ZODB that point to
records that are no longer in the relational database. The current
version of Archetypes raises an error if you try to view these
objects or get the attributes that are stored in SQLStorage.

\item 
Records that are changed in the database \textbf{are} visible immediately
to Zope, but any Catalog entries won't be updated, making Catalog
queries incorrect.

\end{itemize}


%___________________________________________________________________________

\hypertarget{forcing-catalog-reindexes-on-update}{}
\subsection*{Forcing Catalog Reindexes on Update}
\pdfbookmark[1]{Forcing Catalog Reindexes on Update}{forcing-catalog-reindexes-on-update}

There's no way for our relational database to directly affect the
ZODB.  Instead, we'd have to either make a request that the ZServer
hears and passes on to Plone, or we'd have to write a standalone
Python program that connects to the ZODB to make these requests. [\hyperlink{id18}{9}]
\begin{figure}[b]\hypertarget{id18}[9]
An advanced note on a future possibility: It should be possible
to have PL/PythonU, PostgreSQL's untrusted procedural language
binding for Python, import the Zope module and work directly with
Zope objects. However, when I tried this with Zope 2.7.0b1 and
PostgreSQL 7.4devel (the first version of PostgreSQL to support
PL/PythonU), it hung my PostgreSQL process. If this becomes
possible with other configurations, we'll be able to not use the
wget-based strategy outlined below, and talk directly to Zope.
\end{figure}

The latter can be very slow (starting connecting to the ZODB can take
a while), and would only work on the machine that the ZODB is hosted
on, whereas the first choice is ZEO-friendly, remote database machine
friendly, and generally easier and faster.

By creating a custom function in PostgreSQL, we can execute a web or
XMLRPC request to reindex the catalog.

We'll need a bit of Zope support: Zope will be given the UID for the
record that has changed, and it needs to find the real Zope object,
and call reindexObject() on it.

We could do this by adding a method to ArchetypesTool.py [\hyperlink{id20}{10}], but,
for simplicity's sake, we'll implement it as a PythonScript:
\begin{ttfamily}\begin{flushleft}
\mbox{{\#}~"reindex{\_}by{\_}uid"}\\
\mbox{}\\
\mbox{{\#}{\#}~Parameters:~uid}\\
\mbox{}\\
\mbox{o~=~context.archetype{\_}tool.lookupObject(uid)}\\
\mbox{o.reindexObject()}\\
\mbox{return~"ok"}
\end{flushleft}\end{ttfamily}

You can test calling this by giving it a UID of an existing object.
This should be recataloged; you can see the changed catalog
information by viewing the portal{\_}catalog.
\begin{figure}[b]\hypertarget{id20}[10]
I think it would be a good idea to move helper methods like
this into the Archetypes API. We'll see what happens.
\end{figure}

Functions can be written in several procedural languages in
PostgreSQL, including Python.  However, making a web request is an
``unsafe'' act in PostgreSQL, so we need to use a language that supports
making unsafe calls.  PostgreSQL refers to these languages as
``untrusted'' languages, and traditionally names them with a trailing
\emph{U}. At this time, the Python language is implemented as a trusted
language but not as an untrusted language. The built in, easy-to-use
PL/PgSQL is also implemented as a trusted language only.

This is changing, howver, in PostgreSQL 7.4. Due to RExec
(restricted environment) module being dropped from Python, PL/Python
(trusted) is no longer part of PostgreSQL, and PL/PythonU (a new,
untrusted variant) is being added. It would be easy to write the
functions below as PL/PythonU functions. [\hyperlink{id22}{11}]

Our current options for untrusted languages, though, are PL/tclU (tcl
untrusted), PL/perlU (perl untrusted), and C. We'll use Perl's
untrusted language, plperlu.
\begin{figure}[b]\hypertarget{id22}[11]
In earlier versions of PostgreSQL, you could use PL/Python
(trusted) by recompiling PL/Python after adding the
required-for-import modules to the list of acceptable modules.
However, you'd have to install this modified PL/Python library on
every PostgreSQL server that was going to use these functions.
\end{figure}

Make sure that Perl untrusted functions are enabled for your database:
\begin{ttfamily}\begin{flushleft}
\mbox{{\$}~\textbf{createlang~plperlu~your{\_}db{\_}name}}
\end{flushleft}\end{ttfamily}

Then, in psql, we'll create a function that uses wget, a common,
simple command line http request tool:
\begin{ttfamily}\begin{flushleft}
\mbox{CREATE~OR~REPLACE~FUNCTION~reindex{\_}by{\_}uid~(text)~RETURNS~text~as~'}\\
\mbox{~~{\$}uid~=~shift;}\\
\mbox{}\\
\mbox{~~{\#}~Auth~required~to~run~PythonScript~w/right~role}\\
\mbox{~~{\#}~~~~or,~dont~pass~auth~stuff~and~make~PyScript~proxied~to~role}\\
\mbox{~~{\$}AUTH~=~"--http-user=joel~--http-passwd=foo";}\\
\mbox{}\\
\mbox{~~{\#}~Set~to~your~server~and~portal~name}\\
\mbox{~~{\$}SERVER~=~"http://localhost:8080/arch";}\\
\mbox{}\\
\mbox{~~{\#}~wget~options}\\
\mbox{~~{\#}~~~-q~~~~~is~quiet~(no~status~output)}\\
\mbox{~~{\#}~~~-O~-~~~is~to~send~results~to~standard~output}\\
\mbox{~~{\#}~~~-T~5~~~is~to~timeout~after~5~seconds}\\
\mbox{~~{\$}WGET~=~"/usr/bin/wget~-q~-O~-~-T~5";}\\
\mbox{}\\
\mbox{~~{\$}cmd~=~"{\$}WGET~{\$}AUTH~{\$}SERVER/reindex{\_}by{\_}uid?uid={\$}uid";}\\
\mbox{}\\
\mbox{~~{\#}~output~it~to~psql~log~so~the~user~has~some~idea~whats~going~on}\\
\mbox{~~elog~NOTICE,~{\$}cmd;}\\
\mbox{}\\
\mbox{~~return~`{\$}cmd`;}\\
\mbox{'~LANGUAGE~plperlu;}
\end{flushleft}\end{ttfamily}

As noted in the function comments, above, you can pass authorization
headers via wget (using the {\$}AUTH variable). If you don't want to have
the username and password in a plaintext script on the PostgreSQL
server, or don't want them to travel across the network, you could
instead make the PythonScript usable by anonymous users, and have it
proxied to a high-enough level role that it can reindex all needed
content.

The elog statement in the function outputs the {\$}cmd variable
through the PostgreSQL logging system, making it appear in psql as a
notice. This is useful for debugging and providing feedback, but may
confuse some database front-end systems that don't expect notices to
be raised. In addition, it exposes your username and password to the
log everytime a record is updated. Once you have the function working
for your database, you should probably remove this line.

Now, in PostgreSQL, if we update a record, we can force a reindex by
calling this, as in:
\begin{ttfamily}\begin{flushleft}
\mbox{database~={\#}~\textbf{SELECT~reindex{\_}by{\_}uid('*uid*');}}
\end{flushleft}\end{ttfamily}


%___________________________________________________________________________

\hypertarget{using-triggers-to-automate-this}{}
\subsubsection*{Using Triggers to Automate This}
\pdfbookmark[2]{Using Triggers to Automate This}{using-triggers-to-automate-this}

Of course, we'll want to have this happen automatically when we update
a record, rather than having to execute the select statement.  To do
this, we'll write a trigger in PostgreSQL that triggers whenever an
update is made to our customer table.

To do this, we need a trigger function that is called when our table
is changed. In a perfect world, we could use our Perl function, above.
However, at this time, Perl functions can't be used as trigger
functions (though functions written in PL/Python, PL/PgSQL, C, PL/tcl,
and other procedural languages can). For a simple wrapper function
like this, PL/PgSQL would be the normal choice.

Our trigger function is:
\begin{ttfamily}\begin{flushleft}
\mbox{CREATE~OR~REPLACE~FUNCTION~customer{\_}upd()~RETURNS~TRIGGER~as~'}\\
\mbox{~~BEGIN}\\
\mbox{~~~~PERFORM~reindex{\_}by{\_}uid(OLD.uid);}\\
\mbox{~~~~RETURN~NEW;}\\
\mbox{~~~~END;}\\
\mbox{'~LANGUAGE~plpgsql;}
\end{flushleft}\end{ttfamily}

Then the trigger itself:
\begin{ttfamily}\begin{flushleft}
\mbox{CREATE~TRIGGER~customer{\_}upd~}\\
\mbox{~~AFTER~UPDATE~ON~Customerfolder}\\
\mbox{~~~~FOR~EACH~ROW~EXECUTE~PROCEDURE~customer{\_}upd();}
\end{flushleft}\end{ttfamily}

Now, whenever we make an change to our table, our trigger calls the
PL/PgSQL function customer{\_}upd. This, in turn, calls our general
reindexing function, which makes a simple web request that Zope hears
and calls the reindexing support PythonScript. It seems like a lot of
redirection, but works fine. Go test it out. Make a change to your
object's body field directly via PostgreSQL, then check the catalog
results and see that the appropriate field (in this case,
SearchableText) has been updated.


%___________________________________________________________________________

\hypertarget{syncronizing-deletes-and-inserts}{}
\subsection*{Syncronizing Deletes and Inserts}
\pdfbookmark[1]{Syncronizing Deletes and Inserts}{syncronizing-deletes-and-inserts}

\emph{(For advanced readers, since some of the detail is left to you to
fill in).}

Deletes and inserts are a bit trickier than updates, but also more
critical to get right. If you don't use the update/reindexing
technique, above, everything works fine, except your ZCatalog calls
will be out-of-date, and even those will be fixed for you the next
time you edit the object in Plone or do a manual recataloging. If
inserts and deletes aren't propagated from the relational database to
ZODB, data will be missing and errors will be raised.

For this reason, it may be reasonable to decide that record additions
and deletions should happen only via the Plone interface. You can make
quick data changes in the relational database (helpful for fixing
typos across a series of records, or reformatting a field, etc.), but
never inserts or updates.

However, if you want or need to have insertions/deletions made to the
relational database and propagated into the ZODB, the following
sections explain how to do this.


%___________________________________________________________________________

\hypertarget{inserts}{}
\subsubsection*{Inserts}
\pdfbookmark[2]{Inserts}{inserts}

Inserts would be handled using the saem general concepts as the
update/reindex fix: write a Zope PythonScript that creates the object
when it is passed the different fields required for object creation.
Then write a PL/perlU function that crafts a command line wget
statement that calls our Zope PythonScript, all of this being set into
motion by a trigger on our table.

First, we'll want to create a PythonScript that will create our
content for us. The trickiest part is coming up with a good, unique
UID. If we knew that something in our table that was being inserted
was unique, we could use that (prepended by the type name, to make
sure it was unique across types so that Archetypes could use it).
However, to look and feel consistent with UIDs created through the
web, we'll copy in the same UID-generating code that Plone itself
uses. [\hyperlink{id24}{12}]
\begin{figure}[b]\hypertarget{id24}[12]
It would be smarter, of course, to simply call an API method to
get a unique ID in this format, but I couldn't find a suitable way
to do so given how this has been implemented. If any readers have a
suggestion, please let me know.
\end{figure}

Our script will be called create{\_}customerfolder, and will be:
\begin{ttfamily}\begin{flushleft}
\mbox{{\#}{\#}~create{\_}customerfolder}\\
\mbox{{\#}{\#}~Arguments:~phone,~body}\\
\mbox{}\\
\mbox{{\#}~this~function~ripped~out~of~CMFPlone/FactoryTool.py}\\
\mbox{def~generateId(self,~type):}\\
\mbox{~~~~now~=~DateTime()}\\
\mbox{~~~~name~=~type.replace('~',~'')+'.'+now.strftime('{\%}Y-{\%}m-{\%}d')+'.'+now.strftime('{\%}H{\%}M{\%}S')}\\
\mbox{}\\
\mbox{~~~~{\#}~Reduce~chances~of~an~id~collision~(there~is~a~very~small~chance~that~somebody~will}\\
\mbox{~~~~{\#}~create~another~object~during~this~loop)}\\
\mbox{~~~~base{\_}name~=~name}\\
\mbox{~~~~objectIds~=~self.getParentNode().objectIds()}\\
\mbox{~~~~i~=~1}\\
\mbox{~~~~while~name~in~objectIds:}\\
\mbox{~~~~~~~~name~=~base{\_}name~+~"-"~+~str(i)}\\
\mbox{~~~~~~~~i~=~i~+~1}\\
\mbox{~~~~return~name}\\
\mbox{}\\
\mbox{context.invokeFactory(~"CustomerFolder"}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~,~id=generateId(context,~'CustomerFolder')}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~,~phone=phone}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~,~body=body)}\\
\mbox{return~"ok"}
\end{flushleft}\end{ttfamily}

You can test this script by calling it through the web, or by using
the Test tab on the PythonScript. Give it a body and a phone and
it will create a new CustomerFolder object in the current context.

Now, we'll write a plperlu function that will craft a proper wget
web request to call this script:
\begin{ttfamily}\begin{flushleft}
\mbox{CREATE~OR~REPLACE~FUNCTION~customerfolder{\_}add~(text,text)~RETURNS~text~as~'}\\
\mbox{~~{\$}body~=~shift;}\\
\mbox{~~{\$}phone~=~shift;}\\
\mbox{~~{\$}sec~=~"--http-user=joel~--http-passwd=foo";}\\
\mbox{~~{\$}portal~=~"/arch";}\\
\mbox{~~{\$}server~=~"localhost:8080";}\\
\mbox{~~{\$}wget~=~"/usr/bin/wget";}\\
\mbox{~~{\$}cmd~=~"{\$}wget~{\$}sec~-q~-O~-~http://{\$}server/{\$}portal/create{\_}customer?body={\$}body".''{\textbackslash}{\textbackslash}{\textbackslash}{\&}''."phone={\$}phone";}\\
\mbox{~~return~`{\$}cmd`;}\\
\mbox{'~LANGUAGE~plperlu;}
\end{flushleft}\end{ttfamily}

FIXME -- CLEAN THIS UP LIKE PREVIOUS

The difference is that we don't want to really do an insert in the
database, though--when Zope does its object creation, it will create
the database record itself in Archetypes. So we want our original
direct-in-DB insert to be ignored.

We could do this with a trigger, and have the trigger raise a failure
so the insert didn't happen. This, though, would be confusing for the
user, who would see an error message, and, if we were in the middle of
transaction, would spoil that transaction, aborting it and preventing
other actions from happening in the relational database. A better
solution, then, would be to use a feature of PostgreSQL called
rules, which we saw briefly earlier \href{#if-you-need-a-very-different-table-structure}{If You Need A Very Different
Table Structure}.

Rules are rewritings of a query to do anything instead of the
called-for-query. We'll ``rewrite'' our INSERT query to a SELECT
query, which in this case will select the PL/perlU function that
calls wget to notify the Zope PythonScript to create the object.
Again, it seems like a lot of redirection, but works well.

Rule creation is covered in the PostgreSQL documentation, in \href{http://www.postgresql.org/docs/7.3/static/rules.html}{The Rule
System} [\hyperlink{id45}{23}]. Our rule will be:
\begin{ttfamily}\begin{flushleft}
\mbox{CREATE~RULE~customer{\_}ins~AS~}\\
\mbox{~~ON~INSERT~TO~Customerfolder~}\\
\mbox{~~WHERE~NEW.uid~=~'direct'}\\
\mbox{~~DO~INSTEAD~}\\
\mbox{~~~~SELECT~customerfolder{\_}add~(~NEW.body,~NEW.phone~);}
\end{flushleft}\end{ttfamily}

Now, when you want to insert a record directly, you can do so by:
\begin{ttfamily}\begin{flushleft}
\mbox{INSERT~INTO~customer{\_}ins~(~uid,~body,~phone~)~}\\
\mbox{~~VALUES~(~'direct',~'body~goes~here',~'555~1212'~);}
\end{flushleft}\end{ttfamily}

The WHERE NEW.uid = 'direct' clause is required to prevent Zope's
insertion from triggering our rule which would trigger Zope's
insertion ... and so on into permanent recursion. Any attempt to
insert a record with a UID not equal to ``direct'' will go directly
into the database without triggering any action from Zope. Since Zope
will be inserting a record with real UID, it will always therefore
bypass our rule.


%___________________________________________________________________________

\hypertarget{deletes}{}
\subsubsection*{Deletes}
\pdfbookmark[2]{Deletes}{deletes}

Deletes would be handled like inserts, but our PythonScript would
obviously do the deleting for us instead.

Details here can be figured here by the reader, but you'll need a
PythonScript to handle the deletion, a plperlu function to craft the
proper wget command, and a trigger that handles ON DELETE.

Since we can't stop recursion from happening with a DELETE the way we
can with an INSERT, we should have our trigger call Zope not just as
DO INSTEAD but DO, so the Zope deletion happens and the normal
PostgreSQL deletion happens.  When the Zope deletion tries ...

XXX FIXME XXX


%___________________________________________________________________________

\hypertarget{inserting-a-child-record}{}
\subsubsection*{Inserting a Child Record}
\pdfbookmark[2]{Inserting a Child Record}{inserting-a-child-record}

If we want to allow direct database insertion of the child Orders
objects, we have to consider one additional wrinkle: the Orders
objects are meant to be physically contained in their related parent
Customer object. Therefore, our PythonScript that would add their
child Orders record must make the context for the invokeFactory
call be the context of the enclosing Customer object.

We could accomplish this easily by passing the child Orders
PythonScript add helper the UID of the Customer, and it could
lookup the Customer object (using the API demonstrated earlier for
looking up an object given its UID). Then we could use that context
for our invokeFactory call.

FIXME: show this.


%___________________________________________________________________________

\hypertarget{changing-sqlstorage-s-storage-methods-an-example-with-lists}{}
\section*{Changing SQLStorage's Storage Methods: An Example With Lists}
\pdfbookmark[0]{Changing SQLStorage's Storage Methods: An Example With Lists}{changing-sqlstorage-s-storage-methods-an-example-with-lists}


%___________________________________________________________________________

\hypertarget{creating-the-types-and-fixing-the-mapping}{}
\subsection*{Creating the Types and Fixing the Mapping}
\pdfbookmark[1]{Creating the Types and Fixing the Mapping}{creating-the-types-and-fixing-the-mapping}

If we add a list type to our customer object, we run into a snag with
marshalling and unmarshalling.

Let's add the object type, first as a standard Archetypes object
stored completely in the ZODB:
\begin{ttfamily}\begin{flushleft}
\mbox{{\#}~CustomerList.py}\\
\mbox{}\\
\mbox{from~Products.Archetypes.public~import~*}\\
\mbox{from~Products.Archetypes.TemplateMixin~import~TemplateMixin}\\
\mbox{}\\
\mbox{schema~=~BaseSchema~+~Schema((}\\
\mbox{~~~~}\\
\mbox{~~~~TextField('body',}\\
\mbox{~~~~~~~~~~~~~~required=1,}\\
\mbox{~~~~~~~~~~~~~~primary=1,}\\
\mbox{~~~~~~~~~~~~~~searchable=1,}\\
\mbox{~~~~~~~~~~~~~~default{\_}output{\_}type='text/html',}\\
\mbox{~~~~~~~~~~~~~~allowable{\_}content{\_}types=('text/restructured',}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'text/plain',}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'text/html',}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'application/msword'),}\\
\mbox{~~~~~~~~~~~~~~widget=RichWidget,}\\
\mbox{~~~~~~~~~~~~~~),}\\
\mbox{~~~~}\\
\mbox{~~~~StringField("phone",}\\
\mbox{~~~~~~~~~~~~~~~~~index="FieldIndex",}\\
\mbox{~~~~~~~~~~~~~~~~~),}\\
\mbox{~~~~LinesField("clients"),}\\
\mbox{~~~~}\\
\mbox{~~~~))~+~TemplateMixin.schema}\\
\mbox{}\\
\mbox{class~CustomerList(TemplateMixin,~BaseContent):}\\
\mbox{~~~~"""Our~example~object"""}\\
\mbox{~~~~schema~=~schema}\\
\mbox{~~~~archetype{\_}name~=~"Customer~List"}\\
\mbox{~~~~actions~=~TemplateMixin.actions}\\
\mbox{~~~~~~~~~~~~~~~~~~}\\
\mbox{registerType(CustomerList)}
\end{flushleft}\end{ttfamily}

Put this in the schema and restart Archetypes.

As we're storing this in the ZODB (not in the relational database),
everything works fine. The form widget for the clients field is a
textarea in which the user enters newline-separated entries. These are
converted by Zope to a Python list and stored as an attribute of the
object.

If we create a new Archetypes type that contains this same lines
field, but tries to store it in the relational database, we run into
problems with Archetypes's default behaviors.

First, the object type:
\begin{ttfamily}\begin{flushleft}
\mbox{{\#}~CustomerListSQL.py}\\
\mbox{}\\
\mbox{from~Products.Archetypes.public~import~*}\\
\mbox{from~Products.Archetypes.TemplateMixin~import~TemplateMixin}\\
\mbox{from~Products.Archetypes.SQLStorage~import~PostgreSQLStorage}\\
\mbox{}\\
\mbox{schema~=~BaseSchema~+~Schema((}\\
\mbox{~~~~}\\
\mbox{~~~~TextField('body',}\\
\mbox{~~~~~~~~~~~~~~required=1,}\\
\mbox{~~~~~~~~~~~~~~storage=PostgreSQLStorage(),}\\
\mbox{~~~~~~~~~~~~~~primary=1,}\\
\mbox{~~~~~~~~~~~~~~searchable=1,}\\
\mbox{~~~~~~~~~~~~~~default{\_}output{\_}type='text/html',}\\
\mbox{~~~~~~~~~~~~~~allowable{\_}content{\_}types=('text/restructured',}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'text/plain',}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'text/html',}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'application/msword'),}\\
\mbox{~~~~~~~~~~~~~~widget=RichWidget,}\\
\mbox{~~~~~~~~~~~~~~),}\\
\mbox{~~~~}\\
\mbox{~~~~StringField("phone",}\\
\mbox{~~~~~~~~~~~~~~~~~index="FieldIndex",}\\
\mbox{~~~~~~~~~~~~~~~~~storage=PostgreSQLStorage(),}\\
\mbox{~~~~~~~~~~~~~~~~~),}\\
\mbox{~~~~LinesField("clients",}\\
\mbox{~~~~~~~~~~~~~~storage=PostgreSQLStorage()),}\\
\mbox{~~~~}\\
\mbox{~~~~))~+~TemplateMixin.schema}\\
\mbox{}\\
\mbox{class~CustomerListSQL(TemplateMixin,~BaseContent):}\\
\mbox{~~~~"""Our~example~object"""}\\
\mbox{~~~~schema~=~schema}\\
\mbox{~~~~archetype{\_}name~=~"Customer~List~SQL"}\\
\mbox{~~~~actions~=~TemplateMixin.actions}\\
\mbox{~~~~~~~~~~~~~~~~~~}\\
\mbox{registerType(CustomerListSQL)}
\end{flushleft}\end{ttfamily}

Restart Archetypes, and don't forget to add the new type to
portal{\_}types.

At the time of this writing, Archetypes tries to create the new table
with the field type lines for the clients field. This is not a
valid field type for PostgreSQL (or any other database I know of), and
therefore, the addition of the table fails, and any attempt to add an
object of this type fails since there is no table to store them in.

There are several different ways we could fix this problem.
\begin{itemize}
\item 
\textbf{Create the table before Archetypes does.}

If the table already exists, Archetypes won't create it. We can
easily create the table, and give it a text type for the clients
field.

The table structure would be:
\begin{ttfamily}\begin{flushleft}
\mbox{CREATE~TABLE~Customerlistsql~(}\\
\mbox{~~~~uid~text~NOT~NULL~PRIMARY~KEY,}\\
\mbox{~~~~parentuid~text,}\\
\mbox{~~~~body~text,}\\
\mbox{~~~~phone~text,}\\
\mbox{~~~~clients~text}\\
\mbox{);}
\end{flushleft}\end{ttfamily}

\item 
\textbf{Change the mapping performed by Archetypes.}

We can fix this problem by patching SQLStorage.py to do the right
thing and create a text field by changing the type mapping that
Archetypes does. You can do this either by editing SQLStorage.py
and making changes for your database type, or, if you'd rather not
modify the Archetypes source code, you can subclass your storage
type, make the changes there, and use this new subclassed storage
type. We'll look explicitly at the subclassing strategy later in
this document; for now, we'll make changes directly to
SQLStorage.py.

The change we want is in the dictionary db{\_}type{\_}map, which
translates an Archetypes field type into the relational database
field type. As of this writing, there is no translation for lines,
so Archetypes uses lines as the relational database field type.
We'll add a translation for lines to become text:
\begin{ttfamily}\begin{flushleft}
\mbox{db{\_}type{\_}map~=~{\{}}\\
\mbox{~~~~'object':~'bytea',}\\
\mbox{~~~~'file':~'bytea',}\\
\mbox{~~~~'fixedpoint':~'integer',}\\
\mbox{~~~~'reference':~'text',}\\
\mbox{~~~~'datetime':~'timestamp',}\\
\mbox{~~~~'string':~'text',}\\
\mbox{~~~~'metadata':~'text',~}\\
\mbox{~~~~'lines':'text',~{\#}~THIS~IS~THE~CHANGE}\\
\mbox{~~~~{\}}}
\end{flushleft}\end{ttfamily}

If you restart Archetypes and try to add your object now, it will
create the table and let you create objects.

\item 
\textbf{Create a suitable domain in PostgreSQL.}

PostgreSQL, like many advanced SQL databases, supports the notion of
\emph{domains}. A domain is a custom-defined explanation of a database
type, which can be referenced as if it were a real type.

For example, if you commonly want to use a varchar(20) not null
for telephones in a database, you could create a domain called
telephone that is defined as varchar(20) not null, and then you
can simply create your tables with the field type telephone to get
the right definition and behavior.

We'll create a domain called lines:
\begin{ttfamily}\begin{flushleft}
\mbox{CREATE~DOMAIN~lines~AS~text;}
\end{flushleft}\end{ttfamily}

Domains can contain restrictions (such as CHECK constraints and
NOT NULL requirements), but in this case, we don't want or need
any of these. This simple definition will be enough.

Now, when Archetypes tries to create a field with the type lines,
it will succeed.

In some ways, this is the best strategy of our three, as it lets
other applications and users understand that this is a lines
field. It's still stored as text, and behaves as such, but if you
look at the table structure, you'll see lines, which can remind
you of its newline-separated, list-oriented use.

\end{itemize}


%___________________________________________________________________________

\hypertarget{fixing-the-translations}{}
\subsection*{Fixing the Translations}
\pdfbookmark[1]{Fixing the Translations}{fixing-the-translations}

A serious problem still persists, though.

The newline-separated entries from the form (the ``lines'') are turned
into a Python list by Archetypes, such as:
\begin{ttfamily}\begin{flushleft}
\mbox{[~'cat',~'dog',~'bird'~]}
\end{flushleft}\end{ttfamily}

but SQLStorage attempts to store this list \textbf{directly} in the
database. This ends up as the literal string value
``['cat,'dog','bird']'' which is Archetypes stores in the database:
\begin{ttfamily}\begin{flushleft}
\mbox{database={\#}~SELECT~uid,~clients~FROM~Customerlistsql~;}\\
\mbox{~~~~~~~~~~~~~~~uid~~~~~~~~~~~~~~~|~~~~~~~~clients}\\
\mbox{---------------------------------+------------------------}\\
\mbox{~CustomerListSQL.2003-07-23.1619~|~['cat',~'dog',~'bird']}\\
\mbox{(1~row)}
\end{flushleft}\end{ttfamily}

Unfortunately, this string representation of a Python list is a
difficult format to work with in the database, and not handled
correctly coming out by Archetypes. When Archetypes gets the data back
from the relational database, it sees it as a single string. It tries
to turn this isnto a list, with the following results:
\begin{ttfamily}\begin{flushleft}
\mbox{[}\\
\mbox{'}\\
\mbox{c}\\
\mbox{a}\\
\mbox{t}\\
\mbox{'}\\
\mbox{,}\\
\mbox{}\\
\mbox{'}\\
\mbox{d}\\
\mbox{o}\\
\mbox{g}\\
\mbox{'}\\
\mbox{,}\\
\mbox{}\\
\mbox{'}\\
\mbox{b}\\
\mbox{i}\\
\mbox{r}\\
\mbox{d}\\
\mbox{'}\\
\mbox{]}
\end{flushleft}\end{ttfamily}

As this is the way Python handles being given a string and being told
to treat it like a list.

The solution is that we want to write a custom marshaller and
unmarshaller.  These are the routines that Archetypes will run on a
value before it tries to write them to the database, and after it
retrieves the value from the database.

There are hooks in Archetypes for this: any function called \emph{map{\_}XXX}
is called when storing field type \emph{XXX} and a method called
\emph{unmap{\_}XXX} is called when retrieving field type \emph{XXX}.

Our mapping will convert this list back to a newline-separated string,
and this is the format it will be given to our relational database
as:
\begin{ttfamily}\begin{flushleft}
\mbox{def~map{\_}lines(self,~field,~value):}\\
\mbox{~~~~return~'{\textbackslash}n'.join(value)}
\end{flushleft}\end{ttfamily}

Our unmapping method will convert the newline-separated string back to
a Python list:
\begin{ttfamily}\begin{flushleft}
\mbox{def~unmap{\_}lines(self,~field,~value):}\\
\mbox{~~~~return~value.split('{\textbackslash}n')}
\end{flushleft}\end{ttfamily}

Both of these should go into SQLStorage.py, as methods the class
SQLStorage or as methods of the class for your particular relational
database.  If don't want to (or can't) modify the source to
Archetypes, you could subclass your storage class, add the methods to
the subclass, and have your object schema fields use your new,
subclass storage type. We'll cover this concept of subclassing a
storage class extensively later, when we subclass an improved version
of the PostgreSQL storage class.

Now we can transparently work with our lists: they appear and are
edited on the form as a newline-separated string (so we can easily
edit them in a textarea), they're handled in Zope as a Python list
object (so we can work naturally with them and don't have to be
concerned with how they're stored), and they're stored in the
relational database as a simple newline separated list so we can
access them simply. [\hyperlink{id26}{13}]
\begin{figure}[b]\hypertarget{id26}[13]
Thought PostgreSQL doesn't have any problems arising here, you
should ensure that there are no problems with your database
converting newlines to another character or messing with the text
fields in any way that will prevent our unmap{\_}lines method from
being able to retrieve the individual lines. If some kind of
unhelpful conversion does occur, you should check to see what kind
of binary storage your database offers, as binary storages perform
no conversion at all in a relational database. PostgreSQL offers a
binary storage type, bytea, but we don't need it in this case as
it leaves the newlines alone.
\end{figure}


%___________________________________________________________________________

\hypertarget{even-better-turning-into-arrays}{}
\subsection*{Even Better: Turning Into Arrays}
\pdfbookmark[1]{Even Better: Turning Into Arrays}{even-better-turning-into-arrays}

While our solution above lets Archetypes store the data and get it
back in one piece, it isn't very suitable in the relational database:
most relational database querying programs and reporting programs are
ill-equipped to deal with searching for individual values that are
stuffed into text fields.

To find all customers that have two values, ``fish'' and ``cat'', in
clients, you could write queries like:
\begin{ttfamily}\begin{flushleft}
\mbox{SELECT~*~FROM~Customerlistsql}\\
\mbox{~~WHERE~clients~LIKE~'cat{\textbackslash}n{\%}fish'}\\
\mbox{~~~~OR~clients~LIKE~'cat{\textbackslash}n{\%}fish{\textbackslash}n{\%}'}\\
\mbox{~~~~OR~clients~LIKE~'{\%}{\textbackslash}ncat{\textbackslash}n{\%}fish'}\\
\mbox{~~~~OR~clients~LIKE~'{\%}{\textbackslash}ncat{\textbackslash}n{\%}fish{\textbackslash}n{\%}'}\\
\mbox{~~~~OR~clients~LIKE~'fish{\textbackslash}n{\%}cat'}\\
\mbox{~~~~OR~clients~LIKE~'fish{\textbackslash}n{\%}cat{\textbackslash}n{\%}'}\\
\mbox{~~~~OR~clients~LIKE~'{\%}{\textbackslash}nfish{\textbackslash}n{\%}cat'}\\
\mbox{~~~~OR~clients~LIKE~'{\%}{\textbackslash}nfish{\textbackslash}n{\%}cat{\textbackslash}n{\%}'}
\end{flushleft}\end{ttfamily}

\emph{(and this is still an incomplete example for this!)}

However, this is ugly, slow, unindexable [\hyperlink{id28}{14}], and error-prone,
especially as you add more predicates to the logic.
\begin{figure}[b]\hypertarget{id28}[14]
Actually, in some databases, including PostgreSQL, there are
ways to do a full-text index on a field like this, but this would
still be suboptimal compared to more natural ways to store multiple
values on a field.
\end{figure}

We'll exploit a feature of PostgreSQL that allows us to store arrays
in a field, so that one field holds an array of values. While this is
similar to storing as newline-separated text, there are many functions
in PostgreSQL that can quickly find records having a value in an
array, or count the number of values in an array, and so on--all the
things that would be slow and unwieldy using text.

First, let's change our table structure to use arrays:
\begin{ttfamily}\begin{flushleft}
\mbox{database={\#}~\textbf{ALTER~TABLE~Customerlistsql~DROP~CLIENTS;}}\\
\mbox{ALTER~TABLE}\\
\mbox{}\\
\mbox{database={\#}~\textbf{ALTER~TABLE~Customerlistsql~ADD~CLIENTS~text[];}}\\
\mbox{ALTER~TABLE}
\end{flushleft}\end{ttfamily}

The type text[] is a PostgreSQL type for storing an array of text
values.  We can test out the array storage works directly in
PostgreSQL by updating an existing record and examining it:
\begin{ttfamily}\begin{flushleft}
\mbox{database={\#}~\textbf{UPDATE~Customerlistsql~SET~clients='{\{}cat,dog,bird{\}}';}}\\
\mbox{UPDATE~1}\\
\mbox{}\\
\mbox{database={\#}~\textbf{SELECT~uid,~clients~FROM~Customerlistsql;}}\\
\mbox{~~~~~~~~~~~~~~~uid~~~~~~~~~~~~~~~|~~~~clients}\\
\mbox{---------------------------------+----------------}\\
\mbox{~CustomerListSQL.2003-07-23.1619~|~{\{}cat,dog,bird{\}}}\\
\mbox{(1~row)}\\
\mbox{}\\
\mbox{database={\#}~\textbf{SELECT~uid,~clients[1]~FROM~customerlistsql;}}\\
\mbox{~~~~~~~~~~~~~~~uid~~~~~~~~~~~~~~~|~clients}\\
\mbox{---------------------------------+---------}\\
\mbox{~CustomerListSQL.2003-07-23.1619~|~cat}
\end{flushleft}\end{ttfamily}

Now we can change our map{\_}lines and unmap{\_}lines methods from
above, to write out and retrieve values written in this format:
\begin{ttfamily}\begin{flushleft}
\mbox{def~map{\_}lines(self,~field,~value):}\\
\mbox{~~~~return~"{\{}{\%}s{\}}"~{\%}~','.join(value)}\\
\mbox{}\\
\mbox{def~unmap{\_}lines(self,~field,~value):}\\
\mbox{~~~~return~value.strip("{\{}{\}}").split(',')}
\end{flushleft}\end{ttfamily}
\begin{center}\begin{sffamily}
\fbox{\parbox{\admonitionwidth}{
\textbf{\large Note}
\vspace{2mm}

These are very naive implementations, as they do not deal with the
possibility that a list item might have a comma in it. It would be
quite easy, though, to write versions that quoted the comma and
unquoted it for unmapping.
}}
\end{sffamily}
\end{center}

Restart Archetypes to pick up the changes to the storage type, then
edit an existing or add a new object. Notice how the values you put
into the clients field end up as array in PostgreSQL, and are read
correctly.


%___________________________________________________________________________

\hypertarget{turning-lists-into-related-child-records}{}
\subsection*{Turning lists into related child records}
\pdfbookmark[1]{Turning lists into related child records}{turning-lists-into-related-child-records}

While the last section works wonderfully, not everyone is lucky enough
to work with PostgreSQL, ``The World's Most Advanced Open Source
Database''. Many databases don't support an array type.

In this case, or even when using PostgreSQL, you can store the
individual client items in a related child table. We could, of course,
do this entirely in stock Archetypes: have the Customer object be
folderish (inherited from BaseFolder), and add independent client
objects, as we did earlier for Orders. In some cases, as it did for
Orders, this makes sense: if the child record has more than one
piece of information to it, or if it might need to undergo separate
approval, workflow, etc., you want to have a customizable way for
Archetypes to let users edit this information, etc.

In many cases, though, this would be overkill and annoying for the
user. For keeping track of a simple list of names or such (as our
examples for the clients field have been), having to add separate
objects for each individual client is burdensome from a UI
perspective, and creates additional Zope objects that aren't really
needed.

Rather, we'll keep our simple, clean textarea/lines interface, but
dynamically create and delete child records in a related table as
needed.

We'll store these list items in a related table, called clients:
\begin{ttfamily}\begin{flushleft}
\mbox{CREATE~TABLE~clients~(~uid~TEXT~NOT~NULL}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~,~client~TEXT~NOT~NULL}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~,~PRIMARY~KEY~(~uid,~client~)~);}
\end{flushleft}\end{ttfamily}
\begin{center}\begin{sffamily}
\fbox{\parbox{\admonitionwidth}{
\textbf{\large Note}
\vspace{2mm}

(the last line in this statement makes the primary key a \emph{compound
primary key} composed of both the uid and client fields. We can
have multiple children for each parent UID, and different parents
can have the same client value, but each child can have the same
client only once. If it should be possible in your application that
the same item could appear in the list more than once, remove this
restriction.)
}}
\end{sffamily}
\end{center}

What we now is a incoming function that will take the
newline-separated string and create child records for each line, and
an outgoing function that will turn child records back into a
newline-separated string.

So far, we've written procedural functions in PostgreSQL in both
PL/PgSQL and PL/perlU. We could do this new function in either of
those languages. PL/PgSQL has very poor string handling, though, so
we'll rule that out. Instead of coding more Perl (this is a Zope
HOWTO, after all!), let's take this chance to try out Python coding in
PostgreSQL. The examples below do not require any untrusted language
features, so they'll work with PL/Python (PostgreSQL 7.3 and previous)
or PL/PythonU (PostgreSQL 7.4); however, if you're using PL/PythonU,
you'll need to change the LANGUAGE declaration at the end of the
function definition to be plpythonu.

First, make sure that plpython is enabled for your database:
\begin{ttfamily}\begin{flushleft}
\mbox{{\$}~\textbf{createdb~plpython~your{\_}db{\_}name}}
\end{flushleft}\end{ttfamily}

Then, let's add a Python function that, given the uid and
newline-separated clients field, adds the child records:
\begin{ttfamily}\begin{flushleft}
\mbox{/*~split~newline-sep~text~and~insert~into~child~table~*/}\\
\mbox{}\\
\mbox{CREATE~OR~REPLACE~FUNCTION~lines{\_}to{\_}clients~(text,~text)~RETURNS~integer~as~'}\\
\mbox{~~~plan~=~plpy.prepare(~"INSERT~INTO~Clients~VALUES~(~{\$}1,~{\$}2~)",~}\\
\mbox{~~~~~[~"text",~"text"~]~)}\\
\mbox{~~~plpy.execute("DELETE~FROM~Clients~WHERE~uid=''{\%}s''"~{\%}~args[0])}\\
\mbox{~~~for~ln~in~args[1].split("{\textbackslash}{\textbackslash}n"):}\\
\mbox{~~~~~~~plpy.execute(plan,~[~args[0],~ln~])}\\
\mbox{~~~return~1'}\\
\mbox{~LANGUAGE~plpython;}
\end{flushleft}\end{ttfamily}

First, we delete any existing clients associated with this uid.
Then, we iterate over the list, and inserts a client for each entry.
You can find out full information about using PL/Python with
PostgreSQL at \href{http://www.postgresql.org/docs/7.3/interactive/plpython.html}{PL/Python} [\hyperlink{id47}{24}].

We can try out our function to make sure it works:
\begin{ttfamily}\begin{flushleft}
\mbox{database={\#}~\textbf{SELECT~lines{\_}to{\_}clients('a','one{\textbackslash}ntwo{\textbackslash}nthree');}}\\
\mbox{~lines{\_}to{\_}clients}\\
\mbox{------------------}\\
\mbox{~~~~~~~~~~~~~~~~1}\\
\mbox{(1~row)}\\
\mbox{}\\
\mbox{database={\#}~\textbf{SELECT~*~FROM~Clients;}}\\
\mbox{~uid~|~client}\\
\mbox{-----+--------}\\
\mbox{~a~~~|~one}\\
\mbox{~a~~~|~two}\\
\mbox{~a~~~|~three}\\
\mbox{(3~rows)}
\end{flushleft}\end{ttfamily}

You can ignore the return value of the first SELECT; functions must
return a value, so the one is just a throwaway value. The second
SELECT, however, demonstrates that our function is working.

Now, a function to pull together all of the children and return as a
newline-separated string:
\begin{ttfamily}\begin{flushleft}
\mbox{/*~get~child~records~and~concatenate~into~newline-sep~text~*/}\\
\mbox{}\\
\mbox{CREATE~OR~REPLACE~FUNCTION~clients{\_}to{\_}lines~(text)~RETURNS~text~as~'}\\
\mbox{~~~rv~=~plpy.execute("SELECT~client~FROM~Clients~WHERE~uid=''{\%}s''"~{\%}~args[0])}\\
\mbox{~~~return~"{\textbackslash}{\textbackslash}n".join([~ln["client"]~for~ln~in~rv~])}\\
\mbox{'}\\
\mbox{~~LANGUAGE~plpython;}
\end{flushleft}\end{ttfamily}

And we can test that:
\begin{ttfamily}\begin{flushleft}
\mbox{database={\#}~\textbf{select~clients{\_}to{\_}lines('a');}}\\
\mbox{~clients{\_}to{\_}lines}\\
\mbox{------------------}\\
\mbox{~one}\\
\mbox{three}\\
\mbox{two}\\
\mbox{(1~row)}
\end{flushleft}\end{ttfamily}

We can see that our individual records are returned as one row of
newline-separated text. (There isn't actually a space at the start of
the ``one'' record; this is simply a mirage created by the formatting
psql does of query results).

To use this, though, we'll need for PostgreSQL to return the result of
clients{\_}to{\_}lines when Archetypes selects from the table--so we'll
need to use a view instead of our table. Views are discussed earlier
in this document, in \href{#if-you-need-a-very-different-table-structure}{If You Need A Very Different Table Structure}.
[\hyperlink{id30}{15}]
\begin{figure}[b]\hypertarget{id30}[15]
In some databases, you can change create fields that have
values that are always calculated. If we were working with a
database that had this capability, we wouldn't have to create a
view; instead, we could define our clients column as calculated
by clients{\_}to{\_}lines().
\end{figure}

First, we'll move the table to a new name:
\begin{ttfamily}\begin{flushleft}
\mbox{ALTER~TABLE~customerlistsql~RENAME~TO~customerlistsql{\_}table;}
\end{flushleft}\end{ttfamily}

Then create our view:
\begin{ttfamily}\begin{flushleft}
\mbox{CREATE~VIEW~customerlistsql~AS}\\
\mbox{SELECT~uid}\\
\mbox{~~~~~,~parentuid}\\
\mbox{~~~~~,~body}\\
\mbox{~~~~~,~phone}\\
\mbox{~~~~~,~clients{\_}to{\_}lines(uid)~AS~clients}\\
\mbox{~~FROM~customerlistsql{\_}table;}
\end{flushleft}\end{ttfamily}

Note that we explicitly tell our relational database to call the final
column in the view by the name clients. Otherwise, our relational
database wouldn't know what to call this field and would give it a
generic name, and Archetypes couldn't find it.

Next, we'll create the update rule. This is what will be called when
you try to update the view. It performs a normal update to the
behind-the-scenes customerlistsql{\_}table table, then calls our
inserting function function for the clients:
\begin{ttfamily}\begin{flushleft}
\mbox{CREATE~RULE~customerlistsql{\_}upd~AS~}\\
\mbox{~~ON~UPDATE~TO~customerlistsql~DO~INSTEAD~(~}\\
\mbox{~~~~UPDATE~customerlistsql{\_}table}\\
\mbox{~~~~~~~SET~uid=NEW.uid}\\
\mbox{~~~~~~~~~,~parentuid=NEW.parentuid}\\
\mbox{~~~~~~~~~,~body=NEW.body}\\
\mbox{~~~~~~~~~,~phone=NEW.phone}\\
\mbox{~~~~~~~WHERE~uid=OLD.uid;~}\\
\mbox{~~~~SELECT~lines{\_}to{\_}clients(NEW.uid,~NEW.clients);~}\\
\mbox{~~);}
\end{flushleft}\end{ttfamily}

We can test this out:
\begin{ttfamily}\begin{flushleft}
\mbox{database={\#}~\textbf{UPDATE~Customerlistsql~SET~clients='dog{\textbackslash}ncat';}}\\
\mbox{~lines{\_}to{\_}clients}\\
\mbox{------------------}\\
\mbox{~~~~~~~~~~~~~~~~1}\\
\mbox{(1~row)}\\
\mbox{}\\
\mbox{database={\#}~\textbf{SELECT~uid,~clients~FROM~Customerlistsql;}}\\
\mbox{~~~~~~~~~~~~~~~uid~~~~~~~~~~~~~~~|~clients}\\
\mbox{---------------------------------+---------}\\
\mbox{~CustomerListSQL.2003-07-23.1619~|~cat}\\
\mbox{dog}\\
\mbox{(1~row)}
\end{flushleft}\end{ttfamily}

Again, we can ignore the SELECT results we get from our UPDATE.
This is because our function is returning some information, even
though it's not useful to us. [\hyperlink{id32}{16}]
\begin{figure}[b]\hypertarget{id32}[16]
If you're really obsessive about such things, or you have other
frontends to the database that throw a fit about receiving results
back from an INSERT statement, this can be fixed. You could write
the INSERT rule to just do the insert to the
customerlistsql{\_}table table, then add a trigger to that that
table which calls our lines{\_}to{\_}clients function.  You'll have to
change the lines{\_}to{\_}cilents function to work as a trigger
function (it needs to return type trigger and such), but at the
end of this process, you'll have an INSERT without any selecting
results, which will trigger the desired child-insertion features.
This is probably a step that most of us won't need, though.
\end{figure}

Our insert rule is similar and straightforward:
\begin{ttfamily}\begin{flushleft}
\mbox{/*~when~inserting~into~the~view,~break~apart~the~new~mylines}\\
\mbox{~~~value~and~insert}\\
\mbox{*/}\\
\mbox{}\\
\mbox{CREATE~RULE~customerlistsql{\_}ins~AS~}\\
\mbox{~~ON~INSERT~TO~customerlistsql~DO~INSTEAD~(~}\\
\mbox{~~~~INSERT~INTO~customerlistsql{\_}table~(~uid}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~,~parentuid}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~,~phone}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~,~body}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~)~}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~VALUES~(~NEW.uid}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~,~NEW.parentuid}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~,~NEW.phone}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~,~NEW.body}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~);~}\\
\mbox{~~~~SELECT~lines{\_}to{\_}clients(~NEW.uid,~NEW.clients~);~}\\
\mbox{~~);}
\end{flushleft}\end{ttfamily}

We can test this in PostgreSQL to see that it works. First, our
insert:
\begin{ttfamily}\begin{flushleft}
\mbox{INSERT~INTO~customerlistsql~(~uid}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~,~parentuid}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~,~phone}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~,~body}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~,~clients~)~}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~values~(~'b'}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~,~'c'}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~,~'555-1212'}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~,~'body'}\\
\mbox{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~,~'one{\textbackslash}ntwo{\textbackslash}npickle{\textbackslash}nshoe'~);}
\end{flushleft}\end{ttfamily}

Now, let's examine the results:
\begin{ttfamily}\begin{flushleft}
\mbox{database={\#}~\textbf{SELECT~*~FROM~customerlistsql~WHERE~uid='b';}}\\
\mbox{~uid~|~parentuid~|~body~|~~phone~~~|~~~~~~~clients}\\
\mbox{-----+-----------+------+----------+---------------------}\\
\mbox{~b~~~|~c~~~~~~~~~|~body~|~555-1212~|~one}\\
\mbox{pickle}\\
\mbox{shoe}\\
\mbox{two}\\
\mbox{(1~row)}\\
\mbox{}\\
\mbox{database={\#}~\textbf{SELECT~*~FROM~clients~WHERE~uid='b';}}\\
\mbox{~uid~|~client}\\
\mbox{-----+--------}\\
\mbox{~b~~~|~one}\\
\mbox{~b~~~|~pickle}\\
\mbox{~b~~~|~shoe}\\
\mbox{~b~~~|~two}\\
\mbox{(4~rows)}
\end{flushleft}\end{ttfamily}
\begin{center}\begin{sffamily}
\fbox{\parbox{\admonitionwidth}{
\textbf{\large Note}
\vspace{2mm}

In most relational databases (including PostgreSQL), there's no
guarantee that the order records were inserted is the order they
will be selected back out (that's what the ORDER BY clause of the
SELECT statement is for.) In this last example, we inserted
one{\textbackslash}ntwo{\textbackslash}npickle{\textbackslash}nshoe but got back the re-ordered [one,
pickle, shoe, two]. If the order of items is important to your
database application, see \href{#maintaining-order}{Maintaining order} ,below.
}}
\end{sffamily}
\end{center}

And, finally, our DELETE rule:
\begin{ttfamily}\begin{flushleft}
\mbox{/*~when~deleting~from~the~view,~delete~the~child~records~as~well~*/}\\
\mbox{}\\
\mbox{/*~note:~this~could~be~handled~automatically~if~we~used~referential}\\
\mbox{~~~integrity~and~has~'on~delete~cascade'~in~our~table~definition}\\
\mbox{~~~for~the~child~table.~}\\
\mbox{*/}\\
\mbox{}\\
\mbox{CREATE~RULE~customerlistsql{\_}del~AS~}\\
\mbox{~~ON~DELETE~TO~customerlistsql~DO~INSTEAD~(~}\\
\mbox{~~~~DELETE~FROM~clients~}\\
\mbox{~~~~~~WHERE~uid=old.uid;~}\\
\mbox{~~~~DELETE~FROM~customerlistsql{\_}table~}\\
\mbox{~~~~~~WHERE~uid=old.uid;~}\\
\mbox{~~);}
\end{flushleft}\end{ttfamily}

And we can test this last piece in PostgreSQL:
\begin{ttfamily}\begin{flushleft}
\mbox{database={\#}~\textbf{DELETE~FROM~customerlistsql~WHERE~uid='b';}}\\
\mbox{DELETE~1}\\
\mbox{}\\
\mbox{database={\#}~\textbf{SELECT~*~FROM~customerlistsql~WHERE~uid='b';}}\\
\mbox{~uid~|~parentuid~|~body~|~phone~|~clients}\\
\mbox{-----+-----------+------+-------+---------}\\
\mbox{(0~rows)}\\
\mbox{}\\
\mbox{database={\#}~\textbf{SELECT~*~FROM~clients~WHERE~uid='b';}}\\
\mbox{~uid~|~client}\\
\mbox{-----+--------}\\
\mbox{(0~rows)}
\end{flushleft}\end{ttfamily}


%___________________________________________________________________________

\hypertarget{delete-rule-versus-referential-integrity}{}
\subsubsection*{Delete rule versus referential integrity}
\pdfbookmark[2]{Delete rule versus referential integrity}{delete-rule-versus-referential-integrity}

As mentioned in the comments, the delete rule would not be neccessary
if you have referential integrity on the customerlistsql and
clients tables. In this case, you could either block deleting a
CustomerListSQL object if it had any related clients, or you could
cascade the deletion. Since this is a small, related table that is
meant to change everytime someone edits the CustomerListSQL object,
cascading seems more likely to be your choice.

We could set this up as:
\begin{ttfamily}\begin{flushleft}
\mbox{ALTER~TABLE~Clients~ADD~FOREIGN~KEY~(uid)}\\
\mbox{~~REFERENCES~customerlistsql{\_}table~ON~DELETE~CASCADE;}
\end{flushleft}\end{ttfamily}

And drop our deletion rule and recreate it in a simpler form:
\begin{ttfamily}\begin{flushleft}
\mbox{DROP~RULE~customerlistsql{\_}del~ON~customerlistsql;}\\
\mbox{}\\
\mbox{CREATE~RULE~customerlistsql{\_}del~AS}\\
\mbox{~~ON~DELETE~TO~customerlist{\_}sql~DO~INSTEAD~}\\
\mbox{~~~~DELETE~FROM~customerlistsql{\_}table}\\
\mbox{~~~~~~WHERE~uid=OLD.uid;}
\end{flushleft}\end{ttfamily}

Note that even though our deletion rule is as straightfoward as
possible, we still need it, since PostgreSQL (and most relational
databases) won't let you delete from a view unless you have a rule
explaining how to do it.

The referential integrity version is slightly nicer as it protects you
in case you deleted directly from customerlistsql{\_}table.


%___________________________________________________________________________

\hypertarget{maintaining-order}{}
\subsubsection*{Maintaining order}
\pdfbookmark[2]{Maintaining order}{maintaining-order}

As we saw in the above section, the order of the child client lines as
retrieved may not match the order they were inserted.

We can solve this, if we need to, by adding a SERIAL field to our
client table and using this same field to order the selections.

Let's add the field to our table:
\begin{ttfamily}\begin{flushleft}
\mbox{CREATE~SEQUENCE~clients{\_}sort{\_}seq;}\\
\mbox{}\\
\mbox{ALTER~TABLE~clients~ADD~sort~INT;}\\
\mbox{}\\
\mbox{UPDATE~client~SET~sort~=~nextval('clients{\_}sort{\_}seq');}\\
\mbox{}\\
\mbox{ALTER~TABLE~clients~ALTER~sort~SET~NOT~NULL;}\\
\mbox{}\\
\mbox{ALTER~TABLE~clients~ALTER~sort~SET~DEFAULT~nextval('clients{\_}sort{\_}seq');}
\end{flushleft}\end{ttfamily}

(or, we could do this when we create the table by just adding the
field then, which is much easier):
\begin{ttfamily}\begin{flushleft}
\mbox{...}\\
\mbox{~~sort~SERIAL~NOT~NULL}\\
\mbox{...}
\end{flushleft}\end{ttfamily}

Then we'll make a simple change to our clients{\_}to{\_}lines function:
\begin{ttfamily}\begin{flushleft}
\mbox{/*~get~child~records~and~concatenate~into~newline-sep~text~*/}\\
\mbox{}\\
\mbox{CREATE~OR~REPLACE~FUNCTION~clients{\_}to{\_}lines~(text)~RETURNS~text~as~'}\\
\mbox{~~~rv~=~plpy.execute(~"SELECT~client"}\\
\mbox{~~~~~~~~~~~~~~~~~~~~.~"~FROM~Clients~WHERE~uid=''{\%}s''"}\\
\mbox{~~~~~~~~~~~~~~~~~~~~.~"~ORDER~BY~sort"~{\%}~args[0])}\\
\mbox{~~~return~"{\textbackslash}{\textbackslash}n".join([~ln["client"]~for~ln~in~rv~])}\\
\mbox{'}\\
\mbox{~~LANGUAGE~plpython;}
\end{flushleft}\end{ttfamily}

Now we're guaranteed to records back in the same order we inserted
them.


%___________________________________________________________________________

\hypertarget{creating-a-new-storage-class}{}
\section*{Creating a new storage class}
\pdfbookmark[0]{Creating a new storage class}{creating-a-new-storage-class}

FIXME


%___________________________________________________________________________

\hypertarget{creating-our-subclasses-type}{}
\subsection*{Creating our subclasses type}
\pdfbookmark[1]{Creating our subclasses type}{creating-our-subclasses-type}

FIXME


%___________________________________________________________________________

\hypertarget{improving-fixed-point-type-storage}{}
\subsection*{Improving fixed point type storage}
\pdfbookmark[1]{Improving fixed point type storage}{improving-fixed-point-type-storage}

FIXME


%___________________________________________________________________________

\hypertarget{fixing-image-storage}{}
\subsection*{Fixing image storage}
\pdfbookmark[1]{Fixing image storage}{fixing-image-storage}

FIXME


%___________________________________________________________________________

\hypertarget{dynamically-changing-storage-types}{}
\subsection*{Dynamically changing storage types}
\pdfbookmark[1]{Dynamically changing storage types}{dynamically-changing-storage-types}

FIXME, but for now:
\begin{ttfamily}\begin{flushleft}
\mbox{|~You~add~"storage=PostgreSQLStorage()"~on~fields~that~you~want}\\
\mbox{|~in~the~PostgreSQL~database.~This~makes~it~easy~to~have~some}\\
\mbox{|~fields~stored~in~a~PostgreSQL~database~and~others~in~a~Sybase}\\
\mbox{|~database.~I~realise~this.}\\
\mbox{|}\\
\mbox{|~Let's~say~that~I~store~everything~in~PostgreSQL~and~then~one~day}\\
\mbox{|~I~want~to~switch~to~Sybase.~Then~I'd~have~to~change~every}\\
\mbox{|~"storage=PostgreSQLStorage"~to~"storage=SybaseStorage".}\\
\mbox{|~What~if~the~class~had~a~'storage'~attribute~that~you~could~set}\\
\mbox{|~instead?~Then~you~could~only~specify~"storage=SQLStorage".}\\
\mbox{|}\\
\mbox{|~I~think~most~people~only~use~one~RDBMS?~So~then~this~would}\\
\mbox{|~be~the~simplest~thing.~And~also~if~you~want~your~new~type~public,}\\
\mbox{|~everyone~won't~probably~use~the~same~database.~Then~you'd}\\
\mbox{|~like~to~change~a~field~in~the~ZMI~to~say~that~all~relational~database}\\
\mbox{|~fields~should~be~stored~with~Sybase.~Anyone~agree?}\\
\mbox{|}\\
\mbox{|~But~I~may~be~ranting~about~something~that~already~exists?}\\
\mbox{}\\
\mbox{In~fact~there~is~an~(undocumented)~method~to~change~storages.~It~is}\\
\mbox{called~(guess~what?)~setStorage,~and~it's~a~method~of~the~Field}\\
\mbox{class.~So,~if~you~have~an~existing~object,~and~you~want~to~migrate}\\
\mbox{from~one~storage~to~another,~you~need~to~do~something~like~this~(in}\\
\mbox{an~External~Method,~of~course):}\\
\mbox{}\\
\mbox{----------------------------------------------------------------------}\\
\mbox{from~Products.Archetypes.Storage~import~SybaseStorage~{\#}~Does~not~exist~currently}\\
\mbox{}\\
\mbox{results~=~root.portal{\_}catalog(portal{\_}type='MyObjectType')}\\
\mbox{for~r~in~results:}\\
\mbox{~~~r.getObject().Schema()['fieldname'].setStorage(SybaseStorage())}\\
\mbox{---------------------------------------------------------------------}\\
\mbox{}\\
\mbox{Then~you~would~shut~down~your~zope~instance~and~adjust~the~schema}\\
\mbox{definitions~accordingly.~NOTE:~After~migrating~your~objects.}\\
\mbox{}\\
\mbox{You~also~need~to~make~sure~the~column~already~exists~in~your}\\
\mbox{databases.}
\end{flushleft}\end{ttfamily}

from mail on the Archetypes-devel mailing ilst by Sidnei da Silva.


%___________________________________________________________________________

\hypertarget{integrating-relational-database-schemas-into-archetypes}{}
\section*{Integrating relational database schemas into Archetypes}
\pdfbookmark[0]{Integrating relational database schemas into Archetypes}{integrating-relational-database-schemas-into-archetypes}

One interesting trick that could benefit us is using \emph{schemas} with
Archetypes.

While many documents refer to a database schema as the definition of
its tables, views, etc., I'm referring here to the feature of
PostgreSQL that give a database multiple namespaces.

For example, in the database mydb, we could have only one table
called ideas. But if we create a new namespace (schema), we can put
this a new ideas table in the new namespace.

For example:
\begin{ttfamily}\begin{flushleft}
\mbox{database={\#}~\textbf{CREATE~TABLE~Ideas~(~idea~text~);}}\\
\mbox{CREATE~TABLE}\\
\mbox{}\\
\mbox{database={\#}~\textbf{CREATE~TABLE~Ideas~(~idea~text~);}}\\
\mbox{ERROR:~~Relation~'ideas'~already~exists}\\
\mbox{}\\
\mbox{database={\#}~\textbf{CREATE~SCHEMA~More;}}\\
\mbox{CREATE~SCHEMA}\\
\mbox{}\\
\mbox{database={\#}~\textbf{CREATE~TABLE~More.Ideas~(~idea~text~);}}\\
\mbox{CREATE~TABLE}
\end{flushleft}\end{ttfamily}

Now we have two independent tables called ideas, and can choose
which one to use by referring to it by namespace:
\begin{ttfamily}\begin{flushleft}
\mbox{database={\#}~\textbf{INSERT~INTO~Ideas~VALUES~(~'commute~to~work.'~);}}\\
\mbox{INSERT~116491~1}\\
\mbox{}\\
\mbox{database={\#}~\textbf{INSERT~INTO~More.Ideas~values~(~'go~out~with~friends.'~);}}\\
\mbox{INSERT~116492~1}
\end{flushleft}\end{ttfamily}

The tables are completely separate:
\begin{ttfamily}\begin{flushleft}
\mbox{database={\#}~\textbf{SELECT~*~FROM~Ideas;}}\\
\mbox{~~~~~~~idea}\\
\mbox{------------------}\\
\mbox{~commute~to~work.}\\
\mbox{(1~row)}\\
\mbox{}\\
\mbox{database={\#}~\textbf{SELECT~*~FROM~More.Ideas;}}\\
\mbox{~~~~~~~~~idea}\\
\mbox{----------------------}\\
\mbox{~go~out~with~friends.}\\
\mbox{(1~row)}
\end{flushleft}\end{ttfamily}

If you don't name a schema explicitly, PostgreSQL uses your \emph{schema
path} to determine your schema. So, even before you knew you were
using schemas, you were! The default schema in most PostgreSQL setups
is the same name as the user, so I could see the ``unnamed'' ideas table
above as joel.ideas.

The interesting idea here is that we could have different database
adapters that have different connecting PostgreSQL users, each having
a different schema path. Then, depending on which database adapter I
used, I could see different results.

Or, even more interesting, we can ask PostgreSQL to change our schema
path for us by executing the SQL command SET search{\_}path=XXX, where
\emph{XXX} is our new, comma-separated search path. If we set our search
path differently, we could get different results. Useful ideas would
be to have the search path set differently based on who the logged in
user was, or what her or his roles were, etc.

Using this ideas we could implemented Archetypes-stored objects that
provided versioning or security capabilities that were dependant on
users or roles: the user june would see results from one table,
where rebecca would see results from another. All this would happen
transparently and quickly at the PostgreSQL level, without having to
put code for this in all your accessors in Archetypes.

Of course, we'd have to make sure inserts and deletes kept the tables
in sync, otherwise june would get an error trying to look at a ZODB
object for which there is no corresponding record in her schema's
table. But this syncronization would be fairly straightforward to
create using triggers on the tables.

There are no hooks currently in Archetypes SQLStorage to execute the
required SET command before the various SQL calls, but it would be
easy to implement this.

And, of course, this trick isn't specific to Archetypes--it's also
useful when working with relational databases in Zope the traditional
way with SQL Methods. It's particularly cool as an idea with APE--you
could make the ZODB reconfigure itself depending who the logged in
user is! [\hyperlink{id34}{17}]
\begin{figure}[b]\hypertarget{id34}[17]
In truth, though, I haven't tried this with APE, and I suspect
you'd to test it thoroughly--there might be lots of nasty
surprises if APE finds the ZODB changing underneath of it in
unexpected ways. Or maybe not. Let me know if you try this out.
\end{figure}


%___________________________________________________________________________

\hypertarget{the-status-of-sqlstorage}{}
\section*{The status of SQLStorage}
\pdfbookmark[0]{The status of SQLStorage}{the-status-of-sqlstorage}

SQLStorage is newer than Archetypes itself, and does not appear to be
as soundly developed and tested. In email to me, Alan Runyan said
that:
\begin{quote}

NOTE: SQLStorage is incredibly inefficient.  It works quite well and
we have done a project with it (that why it exists).  But really it
should be rewritten if you are going to use it in a very large scale
production environment.   I would consider the implementation
'alpha' but stable.
\end{quote}

I have not had a chance to audit the code to see what the
inefficiencies are that he is referring to; however, as seen here,
there are several buglets that prevent SQLStorage from working
correctly (failing to catch deletion errors, failing to map lists
correctly, etc.)

By the time you read this, these errors may be corrected and
SQLStorage may be better-tested and more efficiently implemented. Stay
tuned!


%___________________________________________________________________________

\hypertarget{about-this-document}{}
\section*{About this Document}
\pdfbookmark[0]{About this Document}{about-this-document}

This document was written by \href{mailto:joel@joelburton.com}{Joel Burton} [\hyperlink{id49}{25}]. It is covered under GNU
Free Documentation License, with one invariant section: this section,
\href{#about-this-document}{About this document}, must remain unchanged. Otherwise, you can
distribute it, make changes to it, etc.

If you have any comments, corrections, or changes, please let me know.
Thanks!
\begin{figure}[b]\hypertarget{id35}[18]
\href{http://www.postgresql.org}{http://www.postgresql.org}
\end{figure}
\begin{figure}[b]\hypertarget{id37}[19]
\href{http://www.sf.net/projects/archetypes}{http://www.sf.net/projects/archetypes}
\end{figure}
\begin{figure}[b]\hypertarget{id39}[20]
\href{http://www.bluedynamics.org/products/znolk}{http://www.bluedynamics.org/products/znolk}
\end{figure}
\begin{figure}[b]\hypertarget{id41}[21]
\href{http://hathaway.freezope.org/Software/Ape}{http://hathaway.freezope.org/Software/Ape}
\end{figure}
\begin{figure}[b]\hypertarget{id43}[22]
\href{http://initd.org/software/psycopg}{http://initd.org/software/psycopg}
\end{figure}
\begin{figure}[b]\hypertarget{id45}[23]
\href{http://www.postgresql.org/docs/7.3/static/rules.html}{http://www.postgresql.org/docs/7.3/static/rules.html}
\end{figure}
\begin{figure}[b]\hypertarget{id47}[24]
\href{http://www.postgresql.org/docs/7.3/interactive/plpython.html}{http://www.postgresql.org/docs/7.3/interactive/plpython.html}
\end{figure}
\begin{figure}[b]\hypertarget{id49}[25]
\href{mailto:joel@joelburton.com}{mailto:joel@joelburton.com}
\end{figure}
% # vim:tw=70:ai:fo+=2
% Local Variables:
% mode: rst
% indent-tabs-mode: nil
% sentence-end-double-space: t
% fill-column: 70
% End: 

\end{document}
