<?xml version="1.0"?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" lang="en"><head><title>Twisted Documentation: Twisted Enterprise Row Objects</title><link href="../howto/stylesheet.css" type="text/css" rel="stylesheet" /></head><body bgcolor="white"><h1 class="title">Twisted Enterprise Row Objects</h1><div class="toc"><ol><li><a href="#auto0">Class Definitions</a></li><li><a href="#auto1">Initialization</a></li><li><a href="#auto2">Creating Row Objects</a></li><li><a href="#auto3">Relationships Between Tables</a></li><li><a href="#auto4">Duplicate Row Objects</a></li><li><a href="#auto5">Updating Row Objects</a></li><li><a href="#auto6">Deleting Row Objects</a></li></ol></div><div class="content"><span></span><p>The <code>twisted.enterprise.row</code> module is a method of interfacing simple python objects with rows in relational database tables. It has two components: the <code>RowObject</code> class which developers sub-class for each relational table that their code interacts with, and the <code>Reflector</code> which is responsible for updates, inserts, queries and deletes against the database.</p><p>The row module is intended for applications such as on-line games, and websites that require a back-end database interface. It is not a full functioned object-relational mapper for python - it deals best with simple data types structured in ways that can be easily represented in a relational database. It is well suited to building a python interface to an existing relational database, and slightly less suited to added database persistance to an existing python application.</p><p><em>If row does not fit your model, you will be best off using the <a href="enterprise.html">low-level database API</a> directly, or writing your own object/relational layer on top of it.</em></p><h2>Class Definitions<a name="auto0"></a></h2><p>To interface to relational database tables, the developer must create a class derived from the <code>twisted.enterprise.row.RowObject</code> class for each table. These derived classes must define a number of class attributes which contains information about the database table that class corresponds to. The required class attributes are:</p><ul><li>rowColumns - list of the column names and types in the table with the correct case</li><li>rowKeyColumns - list of key columns in form: <code>[(columnName, typeName)]</code></li><li>rowTableName - the name of the database table</li></ul><p>There are also two optional class attributes that can be specified:</p><ul><li>rowForeignKeys - list of foreign keys to other database tables in the form: <code>[(tableName, [(childColumnName, childColumnType), ...], [(parentColumnName, parentColumnType), ...], containerMethodName, autoLoad]</code></li><li>rowFactoryMethod - a method that creates instances of this class</li></ul><p>For example:</p><pre class="python"> <span class="py-src-keyword">class</span> <span class="py-src-identifier">RoomRow</span><span class="py-src-op">(</span><span class="py-src-parameter">row</span><span class="py-src-op">.</span><span class="py-src-parameter">RowObject</span><span class="py-src-op">)</span><span class="py-src-op">:</span><span class="py-src-newline"> </span><span class="py-src-indent"> </span><span class="py-src-variable">rowColumns</span> <span class="py-src-op">=</span> <span class="py-src-op">[</span><span class="py-src-op">(</span><span class="py-src-string">"roomId"</span><span class="py-src-op">,</span> <span class="py-src-string">"int"</span><span class="py-src-op">)</span><span class="py-src-op">,</span> <span class="py-src-nl"> </span> <span class="py-src-op">(</span><span class="py-src-string">"town_id"</span><span class="py-src-op">,</span> <span class="py-src-string">"int"</span><span class="py-src-op">)</span><span class="py-src-op">,</span><span class="py-src-nl"> </span> <span class="py-src-op">(</span><span class="py-src-string">"name"</span><span class="py-src-op">,</span> <span class="py-src-string">"varchar"</span><span class="py-src-op">)</span><span class="py-src-op">,</span><span class="py-src-nl"> </span> <span class="py-src-op">(</span><span class="py-src-string">"owner"</span><span class="py-src-op">,</span> <span class="py-src-string">"varchar"</span><span class="py-src-op">)</span><span class="py-src-op">,</span><span class="py-src-nl"> </span> <span class="py-src-op">(</span><span class="py-src-string">"posx"</span><span class="py-src-op">,</span> <span class="py-src-string">"int"</span><span class="py-src-op">)</span><span class="py-src-op">,</span><span class="py-src-nl"> </span> <span class="py-src-op">(</span><span class="py-src-string">"posy"</span><span class="py-src-op">,</span> <span class="py-src-string">"int"</span><span class="py-src-op">)</span><span class="py-src-op">,</span><span class="py-src-nl"> </span> <span class="py-src-op">(</span><span class="py-src-string">"width"</span><span class="py-src-op">,</span> <span class="py-src-string">"int"</span><span class="py-src-op">)</span><span class="py-src-op">,</span><span class="py-src-nl"> </span> <span class="py-src-op">(</span><span class="py-src-string">"height"</span><span class="py-src-op">,</span> <span class="py-src-string">"int"</span><span class="py-src-op">)</span><span class="py-src-op">]</span><span class="py-src-newline"> </span> <span class="py-src-variable">rowKeyColumns</span> <span class="py-src-op">=</span> <span class="py-src-op">[</span><span class="py-src-op">(</span><span class="py-src-string">"roomId"</span><span class="py-src-op">,</span> <span class="py-src-string">"int4"</span><span class="py-src-op">)</span><span class="py-src-op">]</span><span class="py-src-newline"> </span> <span class="py-src-variable">rowTableName</span> <span class="py-src-op">=</span> <span class="py-src-string">"testrooms"</span><span class="py-src-newline"> </span> <span class="py-src-variable">rowFactoryMethod</span> <span class="py-src-op">=</span> <span class="py-src-op">[</span><span class="py-src-variable">testRoomFactory</span><span class="py-src-op">]</span><span class="py-src-newline"> </span><span class="py-src-dedent"></span><span class="py-src-endmarker"></span></pre><p>The items in the rowColumns list will become data members of classes of this type when they are created by the Reflector.</p><h2>Initialization<a name="auto1"></a></h2><p>The initialization phase builds the SQL for the database interactions. It uses the system catalogs of the database to do this, but requires some basic information to get started. The class attributes of the classes derived from RowClass are used for this. Those classes are passed to a Reflector when it is created.</p><p>There are currently two available reflectors in Twisted Enterprise, the SQL Reflector for relational databases which uses the python DB API, and the XML Reflector which uses a file system containing XML files. The XML reflector is currently extremely slow.</p><p>An example class list for the RoomRow class we specified above using the SQLReflector:</p><pre class="python"> <span class="py-src-keyword">from</span> <span class="py-src-variable">twisted</span><span class="py-src-op">.</span><span class="py-src-variable">enterprise</span><span class="py-src-op">.</span><span class="py-src-variable">sqlreflector</span> <span class="py-src-keyword">import</span> <span class="py-src-variable">SQLReflector</span><span class="py-src-newline"> </span><span class="py-src-nl"> </span><span class="py-src-variable">dbpool</span> <span class="py-src-op">=</span> <span class="py-src-variable">adbapi</span><span class="py-src-op">.</span><span class="py-src-variable">ConnectionPool</span><span class="py-src-op">(</span><span class="py-src-string">"pyPgSQL.PgSQL"</span><span class="py-src-op">)</span><span class="py-src-newline"> </span><span class="py-src-variable">reflector</span> <span class="py-src-op">=</span> <span class="py-src-variable">SQLReflector</span><span class="py-src-op">(</span> <span class="py-src-variable">dbpool</span><span class="py-src-op">,</span> <span class="py-src-op">[</span><span class="py-src-variable">RoomRow</span><span class="py-src-op">]</span> <span class="py-src-op">)</span><span class="py-src-newline"> </span><span class="py-src-endmarker"></span></pre><h2>Creating Row Objects<a name="auto2"></a></h2><p>There are two methods of creating RowObjects - loading from the database, and creating a new instance ready to be inserted.</p><p>To load rows from the database and create RowObject instances for each of the rows, use the loadObjectsFrom method of the Reflector. This takes a tableName, an optional <q>user data</q> parameter, and an optional <q>where clause</q>. The where clause may be omitted which will retrieve all the rows from the table. For example:</p><pre class="python"> <span class="py-src-keyword">def</span> <span class="py-src-identifier">gotRooms</span><span class="py-src-op">(</span><span class="py-src-parameter">rooms</span><span class="py-src-op">)</span><span class="py-src-op">:</span><span class="py-src-newline"> </span><span class="py-src-indent"> </span><span class="py-src-keyword">for</span> <span class="py-src-variable">room</span> <span class="py-src-keyword">in</span> <span class="py-src-variable">rooms</span><span class="py-src-op">:</span><span class="py-src-newline"> </span><span class="py-src-indent"> </span><span class="py-src-keyword">print</span> <span class="py-src-string">"Got room:"</span><span class="py-src-op">,</span> <span class="py-src-variable">room</span><span class="py-src-op">.</span><span class="py-src-variable">id</span><span class="py-src-newline"> </span><span class="py-src-nl"> </span><span class="py-src-dedent"></span><span class="py-src-dedent"></span><span class="py-src-variable">d</span> <span class="py-src-op">=</span> <span class="py-src-variable">reflector</span><span class="py-src-op">.</span><span class="py-src-variable">loadObjectsFrom</span><span class="py-src-op">(</span><span class="py-src-string">"testrooms"</span><span class="py-src-op">,</span> <span class="py-src-nl"> </span> <span class="py-src-variable">whereClause</span><span class="py-src-op">=</span><span class="py-src-op">[</span><span class="py-src-op">(</span><span class="py-src-string">"id"</span><span class="py-src-op">,</span> <span class="py-src-variable">reflector</span><span class="py-src-op">.</span><span class="py-src-variable">EQUAL</span><span class="py-src-op">,</span> <span class="py-src-number">5</span><span class="py-src-op">)</span><span class="py-src-op">]</span><span class="py-src-op">)</span><span class="py-src-newline"> </span><span class="py-src-variable">d</span><span class="py-src-op">.</span><span class="py-src-variable">addCallback</span><span class="py-src-op">(</span><span class="py-src-variable">gotRooms</span><span class="py-src-op">)</span><span class="py-src-newline"> </span><span class="py-src-endmarker"></span></pre><p>For more advanced RowObject construction, loadObjectsFrom may use a factoryMethod that was specified as a class attribute for the RowClass derived class. This method will be called for each of the rows with the class object, the userData parameter, and a dictionary of data from the database keyed by column name. This factory method should return a fully populated RowObject instance and may be used to do pre-processing, lookups, and data transformations before exposing the data to user code. An example factory method:</p><pre class="python"> <span class="py-src-keyword">def</span> <span class="py-src-identifier">testRoomFactory</span><span class="py-src-op">(</span><span class="py-src-parameter">roomClass</span><span class="py-src-op">,</span> <span class="py-src-parameter">userData</span><span class="py-src-op">,</span> <span class="py-src-parameter">kw</span><span class="py-src-op">)</span><span class="py-src-op">:</span><span class="py-src-newline"> </span><span class="py-src-indent"> </span><span class="py-src-variable">newRoom</span> <span class="py-src-op">=</span> <span class="py-src-variable">roomClass</span><span class="py-src-op">(</span><span class="py-src-variable">userData</span><span class="py-src-op">)</span><span class="py-src-newline"> </span> <span class="py-src-variable">newRoom</span><span class="py-src-op">.</span><span class="py-src-variable">__dict__</span><span class="py-src-op">.</span><span class="py-src-variable">update</span><span class="py-src-op">(</span><span class="py-src-variable">kw</span><span class="py-src-op">)</span><span class="py-src-newline"> </span> <span class="py-src-keyword">return</span> <span class="py-src-variable">newRoom</span><span class="py-src-newline"> </span><span class="py-src-dedent"></span><span class="py-src-endmarker"></span></pre><p>The last method of creating a row object is for new instances that do not already exist in the database table. In this case, create a new instance and assign its primary key attributes and all of its member data attributes, then pass it to the <code>insertRow</code> method of the Reflector. For example:</p><pre class="python"> <span class="py-src-variable">newRoom</span> <span class="py-src-op">=</span> <span class="py-src-variable">RoomRow</span><span class="py-src-op">(</span><span class="py-src-op">)</span><span class="py-src-newline"> </span><span class="py-src-indent"> </span><span class="py-src-variable">newRoom</span><span class="py-src-op">.</span><span class="py-src-variable">assignKeyAttr</span><span class="py-src-op">(</span><span class="py-src-string">"roomI"</span><span class="py-src-op">,</span> <span class="py-src-number">11</span><span class="py-src-op">)</span><span class="py-src-newline"> </span> <span class="py-src-variable">newRoom</span><span class="py-src-op">.</span><span class="py-src-variable">town_id</span> <span class="py-src-op">=</span> <span class="py-src-number">20</span><span class="py-src-newline"> </span> <span class="py-src-variable">newRoom</span><span class="py-src-op">.</span><span class="py-src-variable">name</span> <span class="py-src-op">=</span> <span class="py-src-string">'newRoom1'</span><span class="py-src-newline"> </span> <span class="py-src-variable">newRoom</span><span class="py-src-op">.</span><span class="py-src-variable">owner</span> <span class="py-src-op">=</span> <span class="py-src-string">'fred'</span><span class="py-src-newline"> </span> <span class="py-src-variable">newRoom</span><span class="py-src-op">.</span><span class="py-src-variable">posx</span> <span class="py-src-op">=</span> <span class="py-src-number">100</span><span class="py-src-newline"> </span> <span class="py-src-variable">newRoom</span><span class="py-src-op">.</span><span class="py-src-variable">posy</span> <span class="py-src-op">=</span> <span class="py-src-number">100</span><span class="py-src-newline"> </span> <span class="py-src-variable">newRoom</span><span class="py-src-op">.</span><span class="py-src-variable">width</span> <span class="py-src-op">=</span> <span class="py-src-number">15</span><span class="py-src-newline"> </span> <span class="py-src-variable">newRoom</span><span class="py-src-op">.</span><span class="py-src-variable">height</span> <span class="py-src-op">=</span> <span class="py-src-number">20</span><span class="py-src-newline"> </span> <span class="py-src-variable">reflector</span><span class="py-src-op">.</span><span class="py-src-variable">insertRow</span><span class="py-src-op">(</span><span class="py-src-variable">newRoom</span><span class="py-src-op">)</span><span class="py-src-op">.</span><span class="py-src-variable">addCallback</span><span class="py-src-op">(</span><span class="py-src-variable">onInsert</span><span class="py-src-op">)</span><span class="py-src-newline"> </span><span class="py-src-dedent"></span><span class="py-src-endmarker"></span></pre><p>This will insert a new row into the database table for this new RowObject instance. Note that the <code>assignKeyAttr</code> method must be used to set primary key attributes - regular attribute assignment of a primary key attribute of a rowObject will raise an exception. This prevents the database identity of RowObject from being changed by mistake.</p><h2>Relationships Between Tables<a name="auto3"></a></h2><p>Specifying a foreign key for a RowClass creates a relationship between database tables. When <code class="python">loadObjectsFrom</code> is called for a table, it will automatically load all the children rows for the rows from the specified table. The child rows will be put into a list member variable of the rowObject instance with the name <code>childRows</code> or if a <em>containerMethod</em> is specified for the foreign key relationship, that method will be called on the parent row object for each row that is being added to it as a child.</p><p>The <em>autoLoad</em> member of the foreign key definition is a flag that specifies whether child rows should be auto-loaded for that relationship when a parent row is loaded.</p><h2>Duplicate Row Objects<a name="auto4"></a></h2><p>If a reflector tries to load an instance of a rowObject that is already loaded, it will return a reference to the existing rowObject rather than creating a new instance. The reflector maintains a cache of weak references to all loaded row objects by their unique keys for this purpose.</p><h2>Updating Row Objects<a name="auto5"></a></h2><p>RowObjects have a <code>dirty</code> member attribute that is set to 1 when any of the member attributes of the instance that map to database columns are changed. This dirty flag can be used to tell when RowObjects need to be updated back to the database. In addition, the <code>setDirty</code> method can be overridden to provide more complex automated handling such as dirty lists (be sure to call the base class setDirty though!).</p><p>When it is determined that a RowObject instance is dirty and need to have its state updated into the database, pass that object to the <code>updateRow</code> method of the Reflector. For example:</p><pre class="python"> <span class="py-src-variable">reflector</span><span class="py-src-op">.</span><span class="py-src-variable">updateRow</span><span class="py-src-op">(</span><span class="py-src-variable">room</span><span class="py-src-op">)</span><span class="py-src-op">.</span><span class="py-src-variable">addCallback</span><span class="py-src-op">(</span><span class="py-src-variable">onUpdated</span><span class="py-src-op">)</span><span class="py-src-newline"> </span><span class="py-src-endmarker"></span></pre><p>For more complex behavior, the reflector can generate the SQL for the update but not perform the update. This can be useful for batching up multiple updates into single requests. For example:</p><pre class="python"> <span class="py-src-variable">updateSQL</span> <span class="py-src-op">=</span> <span class="py-src-variable">reflector</span><span class="py-src-op">.</span><span class="py-src-variable">updateRowSQL</span><span class="py-src-op">(</span><span class="py-src-variable">room</span><span class="py-src-op">)</span><span class="py-src-newline"> </span><span class="py-src-endmarker"></span></pre><h2>Deleting Row Objects<a name="auto6"></a></h2><p>To delete a row from a database pass the RowObject instance for that row to the Reflector <code>deleteRow</code> method. Deleting the python Rowobject instance does <em>not</em> automatically delete the row from the database. For example:</p><pre class="python"> <span class="py-src-variable">reflector</span><span class="py-src-op">.</span><span class="py-src-variable">deleteRow</span><span class="py-src-op">(</span><span class="py-src-variable">room</span><span class="py-src-op">)</span><span class="py-src-newline"> </span><span class="py-src-endmarker"></span></pre></div><p><a href="../howto/index.html">Index</a></p><span class="version">Version: 1.3.0</span></body></html>